mysql 的索引

mysql 索引

一. 了解索引树(B+ 树)

mysql 的索引是由 B+ 树实现的,在研究索引之前,先了解 1 下 B+ 树的进化之路

1. 二叉查找树

   二叉查找树是有序的树

   如下图:(理想状态)

   

   缺点:

   在插入的时候容易变成如下形状

   

2. AVL 树

   自平衡二叉查找树

   带有平衡条件的二叉查找树, 通过左旋和右旋, 会强制维护树变成图一的理想状态, 高度差不会大于 1,所以随便插入几条数据, 就要进行左旋右旋调整

   缺点: 插入效率低下, 并且每个节点只有 2 个子节点, 数据量上去之后树的高度过高, 高度高了代表 IO 次数的增加

3. 红黑树,SB 树

   再次优化了平衡性,没有那么高的平衡性要求, 但是仍然没有解决子节点数量的问题, 一般这 2 种树在内存中使用

4. B-树(B 树,不是 B 减树)

   优化了子节点数量

   缺点: 所有键值分布在整颗树中**（索引值和具体 data 都在每个节点里）** ,mysql 默认每个节点 16K, 假设每条数据 1K, 每个节点只能存 6 条数据, 所以百万数据就能让树达到 9 层 想找到对应的数据, 磁盘 IO 次数过多, 而且范围查询只能通过中序遍历来定位最小值和最大值

   如下图:(网上找的,自己画累死了)

   

5. B+ 树

   优化了数据只放在叶子节点, 非叶子节点只是索引值, 并且叶子节点有指针指向下一个节点

   mysql 每个索引节点 16K (默认,可以设置) , 如果只是 1 个 int4 个字节的索引 + 6 个字节的指针, 每个节点能存 1638 个索引数据, (假设每条数据 1K) 所以 3 层 B+ 树就能包含 1638 * 1638 * 4=268 万条数据,当数据量没有超过 268 万的时候,只要经过 3 次 IO 就能找到数据

   如下图: (网上找的,自己画累死了)

   

二. 聚簇索引 和 非聚簇索引

1. 聚簇索引顺序和物理存储顺序是一致的

   innodb 默认主键是聚簇索引, 如果没有主键, mysql 会选择非空的唯一索引当主键,并且用作聚簇索引, 多个非空的唯一索引会优先选择第一个, 如果也没有非空的唯一索引, 会生成 1 个隐藏的列,DB_ROW_ID 当主键

   总结: 主键一定是聚簇索引, 聚簇索引不一定是主键

2. 非聚簇索引的顺序和物理存储无关, 非聚簇索引叶子节点存储的是聚簇索引的值, 然后回表再去聚簇索引查出真正的记录在哪

三. 覆盖索引

• 覆盖索引就是 select 查询的字段 from table where xxx 其中查询的那些字段 在索引中已经存在了, 所以不需要回表去找到真正的记录

  举例:

  创建组合索引 age,salary,name

  现在你想要找到所有年龄大于等于 30 岁,工资小于 10000 的员工

  select age , salary , name from emp where age >= 30 and salary < 10000

  由于在索引中已经包含了 age , salary , name 的值, 所以无须回表

四. 哪些情况索引会失效

1. is null 和 is not null 和 !=, <> 不会走索引

2. 隐式类型转换, 比如字符串不加引号

3. 索引列做计算或者使用函数 比如: select * from user where id - 1 = 8

4. like 百分号开头, 比如 select * from user where name like "%XX"

5. 组合索引不满足最左匹配原则,

   比如 索引是**(gender, age,salary)** 查询语句如下

   select * from age= 20 and salary = 20000 (不走索引)
   select * from gender = 1 and salary = 20000 (只走了gender的索引)
   

6. 组合索引前面的列使用范围查询 >, <, like, 后面的字段不走索引, 但是 >=, <= 可以

   比如 索引是**(gender, age,salary)** 查询语句如下

   select * from gender >= 0 and age = 20 and salary = 20000 (走索引)
   select * from gender > 0 and age = 20 and salary = 20000 (只走了gender的索引)
   select * from gender >= 0 and age >= 20 and salary > 20000 (全走)
   

7. 关联表的时候,关联字段长度不一样,比如都是 name 字段,A 表 name 是 varchar(20),B 表 name 是 varchar(22), 编码不一致也会导致失效

8. 使用 or,除非 or 的字段都是索引, 不然会导致索引失效

9. 执行器误判, 执行器有概率误判, 觉得走索引比全表扫描还慢, 此时可以强制走索引

   select * from table force index(PRI);(强制使用主键)
   
   select * from table force index(indexName);(强制使用索引"indexName")
   

五. 索引的类型

1. 主键索引

2. 唯一索引

3. 普通索引

4. 组合索引(可以是唯一, 也可以是普通)

5. 全文索引(从来没用过, 有需要的自己去研究)

6. hash 索引

   在 mysql 中, 只有 memory 才支持 hash 索引, 只能支持 = , in , <=> **(和 <>, != 不一样)**

7. 空间索引**(R-Tree)**

   myisam 支持空间索引，可以用作地理数据存储，R-Tree 无须前缀索引。空间索引会从所有维度来索引数据。查询时，可以有效地使用任意维度来组合查询。(完全没用过,随便网上抄的)

六. 索引下推

尽可能的过滤不符合条件的记录，哪怕不符合最左匹配原则

简单来说: 早期版本, 组合索引只使用了部分字段,另外部分字段由于各种原因没有用到(比如破坏了最左匹配原则), 但是 where 条件有, 会把记录查出来之后再过滤, 同样情况下, 新版本(5.6)会先过滤了再回表查出数据

举例:

组合索引(salary,name)

查询语句: select * from user where salary > 20000 and name like "% 张 %"

这条语句虽然只用到了 salary 索引字段, 在 mysql5.6 之前, 会把所有 salary 大于 20000 的记录主键拿出来,回表查出记录, 再去判断是否满足 like "% 张 %", 在 mysql5.6 后, 因为索引中有 name 字段, 所以在索引里先判断是否满足 like "% 张 %" ,如果满足才把主键拿去回表查, 少了很多回表操作, 回表频繁会产生大量的 IO