1.索引
什么是索引 索引是一种,单独的,物理层面的数据结构,其作用是用于加速查询 生活中的索引 查字典: 先查目录 可以根据 笔画 偏旁 拼音 等方式 来快速定位需要查询的字
为什么需要索引
mysql把数据存储到硬盘中,硬盘读写速度非常慢,
一个应用程序,本质上就是再对数据进行增删改查
一旦数据量比较大时,硬盘的响应变慢,给用户的感觉时应用程序非常的慢
查询操作时应用程序使用中频率最高的操作 如果有一百次数据操作 可能90次是查询
索引最终目的就是要提升查询速度,因为对数据库的修改频率低
索引带来的问题:
例如800页数据 可能需要10页索引
1.添加索引后,整体的数据更大了 (占用额外的磁盘空间)
2.由于有了索引,多数数据的添加修改删除,都会引发索引的重建 (效率降低)
误区:
索引不是越多越好
索引的实现:
查询之所以慢的原因,数据量大是一个问题
最核心的问题就是硬盘的IO速度问题
mysql 通过B+树结构来组织数据
由于硬盘设计原理 导致每一次读取数据 必须花费9ms时间来查找地址
9ms是固定 没办法降低 只能想办法降低io次数 来提高速度
操作有一个预读取的优化机制
比如你要读取地址为10 的数据 操作系统会把相邻的数据也读取到内存 来减少读取次数
mysql 通过B+树这种结构来减少IO次数
B+树中每一个磁盘快 有两个数据项 一个三个地址 在查询时 会会比较大小
如果小于左边 就访问p1地址 如果大于右边 就访问p3的地址 否则就访问中间p2的地址
与二分法原理相同 只不过 每次把数据分成三段
树的结构越低越好 所以建议把数据量小的作为索引 来降低高度
聚集索引(主键)
主键索引,速度快 因为只要根据id找到叶子节点 那么改行的所有数据都拿到了
innoDB 需要用主键索引来建立数据结构
所以每一个表都应该有主键
辅助索引
除了主键索引之外的所有的索引都是辅助索引
辅助索引 会单独创建树结构,其中存储索引数据本身以及改数据对应主键值
查找过程中肯出现的情况
覆盖索引:是在当前树结构中就拿到了所有需要的数据
回表:是在辅助索引中没有查询到需要的数 就需要拿着id回到主键索引中查找
查询速度
主键 > 覆盖索引 > 回表
编写sql时 如果有主键值 优先使用主键来查询
如果没有主键值 需要用辅助索引 这时候 尽量少查字段 最好保证需要的数据就在辅助索引中
索引的优化 分为两方面
1.索引结构的优化
数据量小的
重复度低的
2.sql语句的优化
sql语句中条件应该是索引字段
避免在模糊匹配中 在最前面使用% 需要全表扫描
select count(*) from usr where name like "%asasasa";
不要在对主键进行运算 例如 where id*10 = 100;
先算出具体的值在来查询 where id = 100 / 10;
3.and 与 or
在and 语句中 mysql 会优先查询带有索引的字段 无论书写为止 在前还是在后
name 有索引 但是重复度高 id 没有索引 email 有 会跳过id直接查email
select count(*) from usr where name = "jack" and id = 1 and email = "xxxx";
and 语句没有优化的余地
or语句中 不会自动选择有索引的 是依次执行 无论是否有条件成立 都查一遍
所以一定避免使用or语句
避免 select *