目录
01 索引类型
1. 哈希索引:适合等值查询不适合范围查询
2. 有序数组索引:移动数据
3. 二叉搜索树:数据多时树会很高,需要多次磁盘 I/O
4. 多叉树:树更矮胖,减少磁盘 I/O 次数
02 InnoDB 实现索引的底层结构
B+ 树,一个索引对应一棵 B+ 树
1. 主键索引的 B+ 树:非叶子结点存主键的值,叶子结点存主键,对应一条完整的记录
2. 普通索引的 B+ 树:非叶子结点存普通索引的值,叶子结点存(普通索引,主键值),如果要查完整的记录需要回表
03 如何选择索引?
1. 主键和非主键:主键,减少回表(如果使用非主键但是有索引覆盖,哪个性能更好?)
2. 普通索引和唯一索引:查询语句使用两种索引的性能差不多
04 索引失效的场景及原理
1. 对索引使用左右模糊匹配:如 like %xx、like %xx%,没有遵循联合索引最左前缀匹配
- 原因:因为索引 B+ 树是按照「索引值」有序排列存储的,只能根据前缀进行比较
2. 对索引使用函数计算
- 举个🌰:select * from t_user where length(name)=6;
- 原因:因为索引保存的是索引字段的原始值,而不是经过函数计算后的值
3. 对索引进行表达式计算
- 举个🌰:select * from t_user where id + 1 = 10; 对 id 进行了计算是不会走索引的
- 原因:失效原因跟对索引使用函数差不多,因为索引保存的是索引字段的原始值,而不是 id + 1 表达式计算后的值,所以无法走索引,只能通过把索引字段的取值都取出来,然后依次进行表达式的计算来进行条件判断,因此不会走索引而是采用全表扫描的方式
4. 对索引隐式转换:如果索引字段是字符串类型,但是在条件查询中,输入的参数是整型的话,不会走索引而是走全表扫描;但是如果索引字段是整型类型,查询条件中的输入参数即使是字符串类型,也不会导致索引失效
- 举个🌰:select * from t_user where phone = 1300000001; 相当于 select * from t_user where CAST(phone AS signed int) = 1300000001;
- 原因:MySQL 在遇到字符串和数字比较的时候,会自动把字符串转为数字,然后再进行比较,使用了 CAST 函数对索引进行转换,根据第二条规则,不会走索引
5. 联合索引非最左匹配:正确使用联合索引需要符合最左匹配原则,也就是按照最左优先的方式进行匹配
- 举个🌰:联合索引 (a, b, c),如果查询条件是 where a=1;where a=1 and b=2 and c=3;where a=1 and b=2;(a 的顺序不重要,查询优化器会进行优化)这几种都能正确使用到联合索引;如果查询条件是 where b=2;where c=3;where b=2 and c=3;都不会走联合索引,因为不符合最左匹配原则
- 例外:where a = 1 and c = 3; 这个查询条件,在 MySQL 5.5 之前,a 字段会走索引,然后通过回表将记录返回给 Server 层,Server 层判断 c 字段是否符合查询条件。在 MySQL 5.6 之后,有一个索引下推的功能,会直接在引擎层过滤掉联合索引字段中不符合查询条件的记录,再回表返回给 Server 层,Server 层再去判断其他的条件是否满足
- 原因:使用联合索引的情况下,数据是按照索引第一列排序,第一列数据相同时才会按照第二列排序,如果直接从第二列数据开始查,第二列数据是无序的,无法使用索引
6. WHERE 子句的 OR:在 WHERE 子句中,如果在 OR 前的条件列是索引列,而在 OR 后的条件列不是索引列,索引就会失效
- 原因:OR 的含义就是两个只要满足一个即可,因此只有一个条件列是索引列是没有意义的,只要有条件列不是索引列,就会进行全表扫描而不是走索引
05 索引优化
1. 前缀索引:在一些大字符串的字段作为索引时,使用前缀索引可以帮助我们减小索引项的大小
2. 覆盖索引:使用普通索引进行查询时,要查询的字段已经覆盖了查询需求,不需要回表(回到主键索引的 B+ 树查找对应的完整记录),减少了搜索树的次数,提升查询性能,可以通过建立联合索引以满足覆盖索引,减少回表次数
3. 索引下推:WHERE 子句要判断的字段包含在联合索引中,引擎层直接过滤即可,不需要回表查询整条记录后再发送给 server 层做判断过滤
4. 主键自增:每次插入一条新记录,都是追加操作,不需要重新移动数据,当页面写满了就会开辟一个新页面;如果索引是非自增的,插入一条数据需要移动其他数据,可能会导致页分裂,还有可能造成的大量内存碎片
5. 扩展:什么时候不用要求主键自增?
6. 主键字段不要太大:减少二级索引项的大小
7. 索引最好设置为 NOT NULL
- NULL 值的存在会让查询器在进行索引选择时更加复杂,更难优化
- NULL 值无意义,而且会占用物理空间,占用行记录的空间来存储 NULL 值列表
8. 防止索引失效(参照 4.)