【详解】 MySQL创建高性能的索引

1. 索引基础

索引的类型

B+树和全文索引详情请见：https://blog.csdn.net/qq_43040688/article/details/105419053

索引的下面查询类型有效

–组合索引（名、姓、生日）

• 全值匹配：只对索引中的所有列进行匹配，例如查找姓名为Cuba Allen、出生于1960-01-01 的人。
• 匹配最左前缀：查找名叫Allen 的人，只用到了索引第一列
• 匹配列前缀：查找姓以J开头的的人
• 匹配范围值：查找姓在Allen和Barrymore之间的人
• 精确匹配某一列并范围匹配另外一列：前面提到的索引也可用于查找所有姓为Allen,并且名字是字母K开头( 比如Kim、Karl等)的人。即第一列last name 全匹配，第二列first_ name 范围匹配。
• 只访问索引的查询：查询只需要访问索引，而不需要访问数据行
• 分组和排序：由于索引时有序的

索引失效的问题

• 如果不是单单查找最左索引，则会失效
• 不能跳过索引的列，如只使用第一个列索引和第三个列索引，则只用第一个有效
• 如果出现范围的查找，即在第二列出现了 like ‘J%’ , 第三个列及其以后的索引都会失效
• 所以索引列的顺序很重要，同时减少范围查询

最左前缀原则

• 第一个列不能不用
• 中间不能断
• 遇范围终止

2. 索引的优点

• 减少了服务器所要扫描的数据量
• 帮助服务器避免排序和建立临时表
• 可以将随机I/O变成顺序I/O

3. 高性能索引策略

3.1 独立的列

• 指索引列不能是表达式的一部分
  
  .
• 所以需要简化where语句

3.2 前序索引和索引选择性

• 如果索引很长，这样会造成很大的开销
• 可以选择前几个列作为索引，减少索引的数目
• 对于BLOB、 TEXT或者很长的VARCHAR类型的列，必须使用前缀索引，因为MySQL不允许索引这些列的完整长度，只索引开始的部分字符。
• 不重复的索引要优先选择

3.3 多列索引

• 在需要使用多个列作为条件进行查询时，使用多列索引比使用多个单列索引性能更好。例如下面的语句中，最好把 actor_id 和 film_id 设置为多列索引。

SELECT film_id, actor_ id FROM sakila.film_actor
WHERE actor_id = 1 AND film_id = 1;

3.4 索引列的顺序

• 让选择性最强的索引列放在前面。
• 索引的选择性是指：不重复的索引值和记录总数的比值。最大值为 1，此时每个记录都有唯一的索引与其对应。选择性越高，每个记录的区分度越高，查询效率也越高

3.5 覆盖索引

• 索引包含所有需要查询的字段的值。
  具有以下优点：
• 索引通常远小于数据行的大小，只读取索引能大大减少数据访问量。
• 一些存储引擎（例如 MyISAM）在内存中只缓存索引，而数据依赖于操作系统来缓存。因此，只访问索引可以不使用系统调用（通常比较费时）。
• 对于 InnoDB 引擎，若辅助索引能够覆盖查询，则无需访问主索引。

3.6 聚簇索引

• 聚簇索引是指列包含的是数据，可以减少对磁盘的I/O
• InnoDB会将主键作为聚簇索引，如果没有主键会选择没有没有重复值的列作为聚簇索引

3.7 按照主键顺序插入数据

• 如果主键的是随机的，会是对聚簇索引的插入时随机的，造成性能变差

3.8 覆盖索引

• 索引包含查询所需的全部列
  具有以下优点：
• 索引通常远小于数据行的大小，只读取索引能大大减少数据访问量。
• 一些存储引擎（例如 MyISAM）在内存中只缓存索引，而数据依赖于操作系统来缓存。因此，只访问索引可以不使用系统调用（通常比较费时）。
• 对于 InnoDB 引擎，若辅助索引能够覆盖查询，则无需访问主索引。

3.9 避免建立重复索引

• 重复的索引还让MySQL单独的维护每一个，同时在选择的时候还会有所考虑，影响性能

3.10 索引和锁

• 查询索引可以减少InnoDB访问的行数，减少锁的开销

4. 索引的使用条件

• 对于非常小的表、大部分情况下简单的全表扫描比建立索引更高效；
• 对于中到大型的表，索引就非常有效；
• 但是对于特大型的表，建立和维护索引的代价将会随之增长。这种情况下，需要用到一种技术可以直接区分出需要查询的一组数据，而不是一条记录一条记录地匹配，例如可以使用分区技术

5. Mysql索引ICP

• Index Condition Pushdown(ICP)是MySQL 5.6中新特性，是一种在存储引擎层使用索引过滤数据的一种 优化方式。ICP可以减少存储引擎访问基表的次数以及MySQL服务器访问存储引擎的次数。

如果查询SQL：select * from t where b=1 andc>1 and d=1;

没有使用ICP

• storage层：只将满足索引返回。如返回1542这个数据
• server 层：对返回的数据，使用后面的where条件过滤，直至返回最后一行，最后结果为空
  

使用ICP

• storage层：首先将index key条件满足的索引记录区间确定，然后在索引上使用index filter进行过滤；将满足的index filter条件的索引记录才去回表取出整行记录返回server层；不满足index filter条件的索引记录丢弃，不回表、也不会返回server层。如直接返回空。
• server 层：对返回的数据，使用table filter条件做最后的过滤。

使用ICP的好处

• 减少IO
• 返回Service层的结果数据集比较少
• 使用ICP后，直接就去掉了不满足index filter条件的记录

6. 总结