MySQL - 索引

最新推荐文章于 2024-09-07 15:36:16 发布

乐小鑫

最新推荐文章于 2024-09-07 15:36:16 发布

阅读量966

点赞数 40

分类专栏： MySQL 文章标签：数据库 MySQL

本文链接：https://blog.csdn.net/m0_74059961/article/details/140819785

版权

3 篇文章 0 订阅

订阅专栏

01 索引类型

1. 哈希索引：适合等值查询不适合范围查询

2. 有序数组索引：移动数据

3. 二叉搜索树：数据多时树会很高，需要多次磁盘 I/O

4. 多叉树：树更矮胖，减少磁盘 I/O 次数

B+ 树，一个索引对应一棵 B+ 树

1. 主键索引的 B+ 树：非叶子结点存主键的值，叶子结点存主键，对应一条完整的记录

2. 普通索引的 B+ 树：非叶子结点存普通索引的值，叶子结点存（普通索引，主键值），如果要查完整的记录需要回表

1. 主键和非主键：主键，减少回表（如果使用非主键但是有索引覆盖，哪个性能更好？）

2. 普通索引和唯一索引：查询语句使用两种索引的性能差不多

1. 对索引使用左右模糊匹配：如 like %xx、like %xx%，没有遵循联合索引最左前缀匹配

2. 对索引使用函数计算

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 后的条件列不是索引列，索引就会失效