MySQL 复习笔记（一）------ 索引 [1/3] - 掘金 (juejin.cn)
MySQL 复习笔记（一）------ 索引 [2/3] - 掘金 (juejin.cn)
1 索引失效情况
1.1 最左前缀法则
定义：如果使用了联合索引，要遵循最左前缀法则。其是指索引从索引的最左列开始，并且不跳过索引中的列。如果跳过了，则部分索引失效（后面的索引失效）
例如创建了一个联合索引：
create index idx_name_sex_age on user(name,sex,age)
要想使用到该索引，就得保证最左侧的列（user列）存在，并且假设当前该表只有这一个索引（不考虑其他索引对这三个字段的影响）
1. 当查询语句为：
select * from user where name = '张三' and sex = '男' and age = 18
索引全部生效
2. 当查询语句为：
select * from user where name = '张三' and sex = '男'
部分索引生效，但是age索引没有用到
3. 当查询条件为 :
select * from user where name = '张三' age = 18
部分索引生效，sex 和 age 的索引没有用到，因为中间跳过了sex字段，导致age索引也失效
4. 当查询条件为 :
select * from user where sex = '男' and age = 18
不使用索引，使用全表扫描
1.2 索引列运算
不要在索引列上进行运算，否则该列索引将失效
例如：
select * from user where substring(phone,10,2)='15'
获取手机号后两位是15的行。
此时索引会失效。
1.3 字符串不加引号
查询的列如果是字符串并且不加引号，导致索引失效
例如：
select * from user where name = '张三'
与
select * from user where name = 张三
其查询的结果都是一样的，但是在不加引号的时候发生了隐式类型转换，导致索引失效
1.4 模糊匹配
如果仅仅是在尾部加了%，则索引仍然生效。
如果是在中间或者前面加了%，则索引失效。
例如：
select * from user where name = '张%'
与
select * from user where name = '%三'
第一条会走索引，第二条不会
1.5 or连接
如果or连接的条件前后字段都有索引，则索引生效
如果or连接的条件存在一个没有索引，则整个查询语句不走索引
例如 假设 id有索引，phone有索引，name没有索引：
select * from user where id = 1 or phone = '13800138000'
与
select * from user where id = 1 and name = '张三'
则第一条会走索引，第二条不会
1.6 其他情况
数据分布情况：如果MySQL认为全表扫描比使用索引的效率更高，则不走索引
如果某个字段有多个索引，则MySQL会自动选择合适的那一个索引
2 索引使用规则
2.1 SQL提示
SQL提示简单来说是在SQL语句中加入一些认为的提示来达到优化操作的目的
use index
select * from user use index(idx_name) where name = '张三'
建议Mysql使用idx_name索引来执行这条sql语句，但最终怎么使用还是Mysql说的算
ignore index
select * from user ignore index(idx_name) where name = '张三'
告诉MySQL在执行的时候不使用idx_name这条索引
force index
select * from user force index(idx_name) where name = '张三'
告诉MySQL在执行的时候强制使用idx_name这条索引
2.2 覆盖索引
尽量使用覆盖索引（查询使用了索引，并且需要返回的列在该索引中能找到），减少select *
假设 id 是聚集索引，phone是二级索引
select id,phone from user where phone = '13800138000'
由B+Tree的结构可以知道，当用phone查询到对应的id，从而不需要再进行回表查询
使用explain 查看执行结果可以看到extra列中的内容：
using index condition :查找使用了索引，但是需要回表查询数据
using where;using index :查找使用了索引，但是需要的数据都在索引列中能找到，不需要回表查询数据
2.3 前缀索引
当字段类型为字符串（varchar，text等）时，有时候需要索引很长的字符串，会让索引变得很大，查询时浪费时间，此时可以只将字符串的一部分前缀建立索引，大大节约索引空间。
例如：
create index idx_phone on user(phone(5))
对phone列的前5个字符建立索引。
但是如果有相同的值，会一条条的进行比对。（PS：在单列索引中，如果只有一列有索引而另一列没有索引，则会回表查询）
