3.2 不遵守最左前缀匹配原则
运行如下sql
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE student.age=30 AND student.name='abcd';
将使用索引idx_age。
下面的sql不会使用索引，因为我没没有创建classId或者name的索引。或
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE student.classId=4 AND student.name='abcd';
在 MySQL 建立联合索引时会遵守最佳左前缀匹配原则，即最左优先，在检索数据时从联合索引的最左边开始匹配。
MySQL 可以为多个字段创建索引，一个索引可以包括 16 个字段，对于多列字段，过滤条件要使用所以那必须按照索引建立时的顺序，依次满足，一旦跳过某个字段，索引后面的字段都无法使用。如果查询条件中没有使用这些字段中的第一个字段时，多列索引不会被使用
下面的sql查询就是遵守这一原则的正确打开方式。
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE student.age = 30 AND student.classId=4 AND student.name='abcd';
思考：下面sql会不会使用索引呢？
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE student.classId=4 AND student.age = 30 AND student.name='abcd'; • 1
答案是会！因为优化器会执行优化的哦，会调整查询条件的顺序。不过在开发过程中我们还是要保持良好的开发习惯哟。
思考：删去索引idx_age_classid和idx_age,只保留idx_age_classid_name
DROP INDEX idx_age_classid ON student; DROP INDEX idx_age ON student;
执行如下sql，会不会使用索引？
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE student.age = 30 AND student.name='abcd'; • 1
案是会，但是只会用一部分。看看执行结果。
使用了idx_age_classid_name，但是key_len是5，也就是说只使用了age部分的排序，因为age是int类型，4个字节加上null值列表一共5个字节哦。想想就知道，B+树是先按照age排序，再按照classid排序，最后按照name排序，因此不能跳过classId的排序直接就使用name的排序哦。
3.3 不按照递增顺序插入主键
对于一个使用 InnoDB 存储引擎的表来说，在我们没有显式的创建索引时，表中的数据实际上都是存储在 聚簇索引 的叶子节点的。而记录又是存储在数据页中，数据页和记录又是按照 记录主键值从小到大 的顺序进行排序，所以如果我们 插入 的记录的 主键是依次增大 的话，那我们每插满一个数据页就换到下一个数据页继续插，而如果我们插入的 主键值忽大忽小 的话，就比较麻烦了，假设某个数据页存储的记录已经满了，它存储的主键值在 1~100 之间
如果此时再插入一条主键值为 9 的记录，那它插入的位置就如下图:
可这个数据页已经满了，再插进来咋办呢?我们需要把当前 页面分裂 成两个页面，把本页中的一些记录移动到新创建的这个页中。页面分裂和记录移位意味着什么？意味着：性能损耗！所以如果我们想尽量避免这样无谓的性能损耗，最好让插入的记录的 主键值依次递增 ，这样就不会发生这样的性能损耗了。 所以我们建议：让主键具有 AUTO_INCREMENT ，让存储引擎自己为表生成主键，而不是我们手动插入
我们自定义的主键列 id 拥有 AUTO_INCREMENT 属性，在插入记录时存储引擎会自动为我们填入自增的主键值。这样的主键占用空间小，顺序写入，减少页分裂。
🎀Tips：
我们一般将主键策略设置为自动递增AUTO_INCREMENT哦！(核心业务表除外，后面会介绍这种情况)
3.4 计算、函数、类型转换（自动或手动）导致索引失效
思考：这两条 sql 哪种写法更好
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE student.name LIKE 'abc%'; EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE LEFT(student.name,3) = 'abc';
从执行结果上说，上面sql执行结果没有区别。但是从运行效率上说，第1条sql比之后的要好，因为第一条可以使用上索引！而因为第二条使用了函数，即使建立索引也会导致索引失效。
为何使用函数时优化器会使索引失效呢？您想想，我们只是对student.name字段建立了索引，但并没有对LEFT(student.name，3)建立索引，使用函数后的关键字跟我们建立的B+树可对应不来，怎么能使用B+树优化查询呢？
3.5 类型转换导致索引失效
# 未使用到索引 EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE name=123; # 使用到索引 EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE name='123';
创建联合索引。
CREATE INDEX idx_age_classId_name ON student(age,classId,NAME); • 1
执行结果如下。
注意到key_len是10，说明值使用到了idx_age_classId_name索引中的age与classId部分，而name则没有用上。这是因为classId>20是范围查询，导致其右边的列索引失效。
如果想要完全使用到索引，需要按如下方式创建索引：先写等值查询的列，再写范围查询的列
create index idx_age_name_classid on student(age,name,classid);
🎨Q：为什么条件查询会导致范围条件后面的列索引失效？
比如说有三个字段 a b c，建立复合索引a_b_c
此时叶子节点的数据排序后可能为
(a=1 b=1 c=1) (a=1 b=2 c=1) (a=1 b=2 c=3)
(a=2 b=2 c=3) (a=2 b=2 c=5) (a=2 b=5 c=1) (a=2 b=5 c=2)
(a=3 b=0 c=1) (a=3 b=3 c=5) (a=3 b=8 c=6)
假设查找 select a,b,c from table where a = 2 and b = 5 and c = 2
此时先根据a = 2找到第二行的四条数据
(a=2 b=2 c=3) (a=2 b=2 c=5) (a=2 b=5 c=1) (a=2 b=5 c=2)
然后根据b=5查到两条
(a=2 b=5 c=1) (a=2 b=5 c=2)
最后根据c=2查到目标数据
(a=2 b=5 c=2)
接下来 假设使用了范围条件
select a,b,c from table where a = 2 and b >1 and c = 2
此时先根据a = 2找到第二行的四条数据
(a=2 b=2 c=3) (a=2 b=2 c=5) (a=2 b=5 c=1) (a=2 b=5 c=2)
然后根据b>1查到四条数据
(a=2 b=2 c=3) (a=2 b=2 c=5) (a=2 b=5 c=1) (a=2 b=5 c=2)
此时要查找c=2了 但是我们发现 这四条数据的c分别是
3,5,1,2 是无序的 所以索引失效了
总结：
因为前一个条件相同的情况下，后续列才会是有序的。
🎃Tips：
应用开发中范围查询，例如：金额查询，日期查询往往都是范围查询。应将查询条件放置where语句最后。（创建的联合索引中，务必把范围设计到的字段写在最后）
