索引的行为

1. 索引覆盖

覆盖索引：要查询所需的所有数据列，是索引数据列的子集，即只需要一次索引即可返回所需数据；

覆盖索引只需一次索引，不需要回表，减少磁盘IO，效率高；

2. 回表

要查询的数据列，不能全部从当前的索引页中获取，需要拿到主键，再到主键索引中，获取剩余的列数据；

回标需要额外的IO操作；
回表的性能影响取决于回表的数据量；少数据量的回表无伤大雅；
大数据量的回表，不仅影响当前查询，还会导致缓存池脏页增多，对数据库整体性能产生影响；

3. 联合索引：最左前缀匹配

由于多列的联合索引的排序方式，的只有前一个索引值确定，后一个索引才是有序；因此，联合索引的使用必须满足：最左前缀匹配原则；

假设有联合索引：a, b, c

idx_a_b_c(a, b, c)

那么：

a = 3
；满足；可以使用到联合索引中的：a索引列；
a = 1 AND b = 2
：满足；可以使用到联合索引中的：a，b索引列；
b = 1 AND a = 2
：不满足；但优化器通常会自动优化为：
a = 2 AND b = 1
c = 1 AND b = 2
：不满足；且无法优化，a不确定，无法使用到b，c；
a >=1 AND a <= 3 AND b = 2
：不满足，只能用到a索引列，且无法优化；a值不确定的情况下，b无法有序，则只能使用到a索引列；

4. 索引下推 ICP

当数据要使用主键回表查询其他

SELECT

字段时，有些id可以根据查询条件提前判断出是不需要的，那么在回表之前将这些数据给过滤掉，就是索引下推；

ICP仅用于二级索引，目的是减少回表数据量，进而减少IO；
对于聚簇索引，不存在回表，不会使用ICP；
触发索引下推，在EXPLAIN中，表现为：
using index condition
；
一句话：尽可能用到联合索引的所有列，将过滤操作从Server层下推到存储引擎层；以此减少回表数据量；

无索引下推

例如：

一个普通二级索引
idx_test
，包含列
(col1, col2)
；
主键为：id；执行：

SELECT * FROM t 
WHERE col1 > 10 AND col1 < 20 AND col2 = 7 and col3 = 8

通过
idx_test
的索引树，只能根据
col1
的条件来进行查询，最终遍历完
idx_text
索引后，获得的数据为：id, col1, col2

SELECT id, col1, col2 FROM t 
WHERE col1 > 10 AND col1 < 20

根据主键id进行回表，遍历一边主键索引，获取所有满足查询出的id的数据；

SELECT id, col1, col2, col3 FROM t 
WHERE id in (xxx, xxxx)

Server层拿到所有的结果，再根据条件：
col2 = 7 and col3 = 8
，过滤出最终结果

开启索引下推

通过
idx_test
的索引树，只能根据
col1
的条件来进行查询，最终遍历完
idx_text
索引后，获得id、col1、col2；
回表前，再次根据col2的条件，进行过滤；减少回表的主键数量；
回表；
Server层根据col3再次过滤，返回结果集；

索引创建原则

1. 命名规范

主键：
pk_[column]
唯一索引：
uk_[column]_[column]
联合索引：
idx_[column]_[column]

2. 优选区分度高的列

区分度即：某个列的数据在全部数据中的重复比例；比如gender字段，只有男女，那么它的重复度为50%；

区分度 =

count(distinct col) / count(*)

重复比例越高，区分度越低，作为索引列，越没用。比如某个字段只有1个数据（重复度100%），那作为索引就没有意义；

3. 组合索引使用原则

区分度比较高的字段，往前放；
最多用来查询的字段，往前放；尽量让更多的查询SQL使用到，减少索引的数量；
已经有了组合索引，就不用单独创建这个字段了；比如有了idx_a_b_c，就不用创建a索引了；
需要范围查询的字段，尽量往后放，只有一个范围查询条件能够用到索引，之后的就会失效；
排序场景的索引，将常用来排序的字段放在组合索引的末尾，可以有效提高排序效率；
- 由于B+树索引的顺序性，查询出来的数据默认就是排序的，不需要额外的排序；
- 如果排序的字段不是索引字段，则需要回表，查询出所有数据，执行排序；（也就是
  using filesort
  产生临时表），如果内存
  sort_buffer_size
  不够，就会使用磁盘临时存储，IO增大；

4. 注意索引大小

同一个索引上，索引列的长度过大，或列过多，导致单个索引太大，会导致一个索引块能够存放的索引数下降，读取索引时，就可能需要遍历更多的索引块，可能增加更多的IO操作；

5. 索引并不是越多越好

每插入一行数据，就要将相关的索引都重新计算、维护一遍，如果是批量插入，则按条数维护；

因此：

相关索引数 x 插入行数 x 10ms ≈ 插入耗时

增加索引的代价是写入时延；除非表是多读写少，否则要考虑索引数量；

6. 更新语句务必走索引

否则锁表；

索引优化原则

前提：如果SQL的查询条件锁定的记录数很多，超过30%甚至50%，索引优化是没有用的。优化业务吧；

减少SQL返回的数据量：只查询必要的列；禁用
SELECT *
；
- 减少数据量，可减少网络带宽占用、增大传输效率；
- 污染Buffer Pool：过多的数据查询，挤占Buffer Pool内存，导致其他缓存失效；
分解大连接查询；
- 大的连接查询的缓存容易失效；其中一个表变化了，整个缓存失效；
- 多个单表缓存，互相之间不影响；
- 尽量少用连表查询：不利于分库分表、服务切分；
优化索引前先考虑优化SQL书写；
- 比如避免
  select *
  、分页慢；
优化索引
- 区分度：将区分度高的列尽可能放在复合索引前部，会让索引更高效；
  - 计算区分度：
    count(distinct col) / count(*)
- 排序字段尽量使用索引字段；否则需要创建临时表来获取额外的排序列；

索引失效场景

不满足联合索引最左前缀匹配，可能导致部分或全部索引失效；
在索引列上进行计算可能导致索引失效；
- 不走索引：
  from_unixtime(create_time) = '2019-12-01'
- 走索引：
  create_time = unix_timestamp('2019-12-01')
LIKE的中缀和后缀查询索引失效；
- 中缀：
  LIKE '%xxxx%'
- 后缀：
  LIKE '%xxxx'
列的类型转换可能会导致索引失效；
OR条件；
- 参与
  or
  的列中，同时有索引列、非索引列，则会导致索引失效；
!=
、
NOT IN
索引失效；

MySQL索引使用和优化