InnoDB索引的相关知识点

索引

MySQL InnoDB以主键的值构造成一颗树，叶子节点存放着该主键对应的整行数据。此为聚簇索引。
其他的索引为辅助索引，叶子节点存放着索引字段的值及对应的主键值。

主键的选择

由于每个非主键索引的叶子节点上都是主键的值。如果用身份证号做主键，那么每个二级索引的叶子节点占用约 20 个字节，而如果用整型做主键，则只要 4 个字节，如果是长整型（bigint）则是 8 个字节。

显然，主键长度越小，普通索引的叶子节点就越小，普通索引占用的空间也就越小。

所以，从性能和存储空间方面考量，自增主键往往是更合理的选择。

有没有什么场景适合用业务字段直接做主键的呢？还是有的。比如，有些业务的场景需求是这样的：

• 只有一个索引；
• 该索引必须是唯一索引。

你一定看出来了，这就是典型的 KV 场景。由于没有其他索引，所以也就不用考虑其他索引的叶子节点大小的问题。这时候我们就要优先考虑上一段提到的“尽量使用主键查询”原则，直接将这个索引设置为主键，可以避免每次查询需要搜索两棵树。

利用 explain 分析 索引的执行过程

explain查看执行计划

使用explain查看sql的执行计划，分析索引的执行过程，

explain结果字段分析

• select_type

  simple：表示不需要union操作或者不包含子查询的简单select语句。有连接查询时，外层的查询 为simple且只有一个。

  primary：一个需要union操作或者含有子查询的select，位于最外层的单位查询的select_type即 为primary且只有一个。

  union：union连接的两个select查询，除了第一个表外，第二个以后的表的select_type都是 union。

  union result：包含union的结果集，在union和union all语句中，因为它不需要参与查询，所以id 字段为null。

• table

  显示查询的表名； 如果不涉及对数据库操作，这里显示null；

  如果显示为尖括号就表示这是个临时表，后边的N就是执行计划中的id，表示结果来自于这个查询 产生的；

  如果是尖括号括起来也是一个临时表，表示这个结果来自于union查询的id为M，N 的结果集；

• type

  const：使用唯一索引或者主键，返回记录一定是1行记录的等值where条件时，通常type就是 const。

  ref：常见于辅助索引的等值查找，或者多列主键、唯一索引中，使用第一个列之外的列作为等值 查找会出现；返回数据不唯一的等值查找也会出现。

  range：索引范围扫描，常见于使用<、>、is null、between、in、like等运算符的查询中。

  index：索引全表扫描，把索引从头到尾扫一遍；常见于使用索引列就可以处理不需要读取数据文件的查询，可以使用索引排序或者分组的查询。

  all：全表扫描数据文件，然后在server层进行过滤返回符合要求的记录

• possible_keys

  查询可能使用到的索引都会在这里列出来

• key

  查询真正使用到的索引，select_type为index_merge时，这里可能出现两个以上的索引，其他的select_type这里只会出现一个。

• ref

  如果使用常数等值查询，这里显示const；

  如果是连接查询，被驱动表的执行计划这里会显示驱动表的关联字段；

• Extra

  using filesort：排序时无法用到索引，常见于order by和group by语句中。

  using index：查询时不需要回表查询，直接通过索引就可以获取查询的数据。
  若没显示"Using index"表示读取了表数据。

  Using where
  表示 MySQL 服务器从存储引擎收到行后再进行“后过滤”（Post-filter）。所谓“后过滤”，就是先读取整行数据，再检查此行是否符合 where 句的条件，符合就留下，不符合便丢弃。因为检查是在读取行后才进行的，所以称为“后过滤”。

  Using temporary
  使用到临时表

慢查询日志

1. 慢查询日志！！ 设置合理的、业务可以接受的慢查询数据！！
   slow_query_log
2. 压测执行各种业务！！
3. 查看慢查询日志，找出所有耗时的sql
4. 用explain分析这些耗时的sql
5. 举例子

MySQL可以设置慢查询日志，当SQL执行的时间超过我们设定的时间，那么这些SQL就会被记录在慢查询日志当中，然后我们通过查看日志，用explain分析这些SQL的执行计划，来判定为什么效率低下，是没有使用到索引？还是索引本身创建的有问题？或者是索引使用到了，但是由于表的数据量太大，花费的时间就是很长，那么此时我们可以把表分成n个小表，比如订单表按年份分成多个小表等。

慢查询日志相关的参数如下所示：

mysql> show variables like '%slow_query%';
+---------------------+-------------------------------------------------+
| Variable_name | Value |
+---------------------+-------------------------------------------------+
| slow_query_log | OFF |
| slow_query_log_file | /usr/local/mysql/data/LeideMacBook-Pro-slow.log |
+---------------------+-------------------------------------------------+
2 rows in set (0.00 sec)

慢查询日志记录了包含所有执行时间超过参数 long_query_time（单位：秒）所设置值的 SQL语句的日 志，在MySQL上用命令可以查看，如下：

mysql> show variables like 'long%';
+-----------------+-----------+
| Variable_name | Value |
+-----------------+-----------+
| long_query_time | 10.000000 |
+-----------------+-----------+
1 row in set (0.00 sec)

这个值是可以修改的，如下：

mysql> set long_query_time = 1; #注意，单位是秒
Query OK, 0 rows affected (0.00 sec)

现在修改成超过1秒的SQL都会被记录在慢查询日志当中！可以设置为0.01秒，表示10毫秒。

慢查询日志，默认名称是host_name-slow.log，存放在MySQL的数据路径下，内容格式显示大致如 下：

Query_time: 0.012000 Lock_time: 0.000000 Rows_sent: 0 Rows_examined: 139
use tuluneducation;
SET timestamp=1534527397;
select id,author from subject where content like '%linux%' and title like '%c++
linux%';

通过查询慢查询日志，发现项目运行过程中，上面这条SQL语句的执行时间超过了设定的慢查询时间， 那么接下来就需要用explain分析一下该SQL的执行计划了，根据具体情况找出SQL和索引该怎么去优 化。

show profiles命令可有查看sql具体的运行时间，全局变量的名字是：profiling

索引的优化

回表

表里有 age，uid，name，sex字段

回表 —— Innodb 从 二级索引树上拿到对应的主键uid，回到主键索引树上搜索uid那一行的记录

如果只拿 主键 和 原索引 不需要回表（这里回表拿 age，sex字段）

覆盖索引

覆盖索引：select的数据列只用从索引中就能够取得，不必读取数据行，换句话说查询列要被所建的索引覆盖

由于覆盖索引可以减少树的搜索次数，显著提升查询性能，所以使用覆盖索引是一个常用的性能优化手段。

基于上面覆盖索引的说明，我们来讨论一个问题：在一个市民信息表上，是否有必要将身份证号和名字建立联合索引？

我们知道，身份证号是市民的唯一标识。也就是说，如果有根据身份证号查询市民信息的需求，我们只要在身份证号字段上建立索引就够了。

而再建立一个（身份证号、姓名）的联合索引，是不是浪费空间？

如果现在有一个高频请求，要根据市民的身份证号查询他的姓名，这个联合索引就有意义了。

它可以在这个高频请求上用到覆盖索引，不再需要回表查整行记录，减少语句的执行时间。当然，索引字段的维护总是有代价的。因此，在建立冗余索引来支持覆盖索引时就需要权衡考虑了。这正是业务 DBA，或者称为业务数据架构师的工作。

利用最左前缀原则

如果你要查的是所有名字第一个字是“张”的人，你的 SQL 语句的条件是"where name like ‘张 %’"。

这时，你也能够用上这个索引，查找到第一个符合条件的记录是 ID3，然后向后遍历，直到不满足条件为止。可以看到，不只是索引的全部定义，只要满足最左前缀，就可以利用索引来加速检索。

这个最左前缀可以是联合索引的最左 N 个字段，也可以是字符串索引的最左 M 个字符。

简而言之

联合索引，叶子节点存储的顺序以创建时指定的顺序为准，因此区分度高的放左边，能被多个查询复用到的放左边

同时，第一原则是，如果通过调整顺序，可以少维护一个索引，那么这个顺序往往就是需要优先考虑采用的。

索引下堆

【索引下推】Index Condition Pushdown，简称 ICP。 是Mysql 5.6版本引入的技术优化。

• 旨在 在“仅能利用最左前缀索的场景”下（而不是能利用全部联合索引），对不在最左前缀索引中的其他联合索引字段加以利用——在遍历索引时，就用这些其他字段进行过滤(where条件里的匹配)。 过滤会减少遍历索引查出的主键条数，从而减少回表次数，提示整体性能。

• 如果查询利用到了索引下推ICP技术，在Explain输出的Extra字段中会有“Using index condition”。即代表本次查询会利用到索引，且会利用到索引下推。

• 索引下推技术的实现——在遍历索引的那一步，由只传入可以利用到的字段值，改成了多传入下推字段值。

Changer Buffer 和 唯一索引

当需要更新一个数据页时，如果数据页在内存中就直接更新，而如果这个数据页还没有在内存中的话，在不影响数据一致性的前提下，InnoDB 会将这些更新操作缓存在 change buffer 中，这样就不需要从磁盘中读入这个数据页了。

在下次查询需要访问这个数据页的时候，将数据页读入内存，然后执行 change buffer 中与这个页有关的操作。通过这种方式就能保证这个数据逻辑的正确性。

使用这个的条件是使用的是辅助索引且不能是唯一的

辅助索引不能是唯一的，因为在插入缓冲时，数据库并不去查找索引页来判断插入的记录的唯一性。如果去查找肯定又会有离散读取的情况发生（不在内存中是），从而导致Changer Buffer失去了意义。

补充：

首先，业务正确性优先。咱们这篇文章的前提是“业务代码已经保证不会写入重复数据”的情况下，讨论性能问题。如果业务不能保证，或者业务就是要求数据库来做约束，那么没得选，必须创建唯一索引。这种情况下，本篇文章的意义在于，如果碰上了大量插入数据慢、内存命中率低的时候，可以给你多提供一个排查思路。

然后，在一些“归档库”的场景，你是可以考虑使用普通索引的。比如，线上数据只需要保留半年，然后历史数据保存在归档库。这时候，归档数据已经是确保没有唯一键冲突了。要提高归档效率，可以考虑把表里面的唯一索引改成普通索引。

使用索引就一定能用到吗 —— force index

给字段作索引，使用该字段作过滤就一定能用到索引吗？

不一定，MySql 的优化器 先作一个分析，发现如果过滤的数据到达70%左右，会直接全盘扫描（优化），因为用索引也会造成性能耗损（操作步骤，磁盘IO，内存等），全盘扫描更快

其次如果考虑到回表，过滤的数据到达30%（不准确），也会全盘扫描

问题是这个分析值是个估计值，所以存在误判的空间

这时候就需要用到force index，强制使用索引

如何判断是否使用了索引 —— explain分析的不过准确，每考虑优化器，需要通过慢查询日志的扫描行数自行判断

字符串字段创建索引

1. 直接创建完整索引，这样可能比较占用空间；
2. 创建前缀索引，节省空间，但会增加查询扫描次数，并且不能使用覆盖索引；
3. 倒序存储，再创建前缀索引，用于绕过字符串本身前缀的区分度不够的问题；
4. 创建 hash 字段索引，查询性能稳定，有额外的存储和计算消耗，跟第三种方式一样，都不支持范围扫描。

索引常见问题

强制索引：force index(index名)

explain select
from student force index (ageidx)
where name= ' chenwei '
and age=22;

not in 一般用不到索引，除非MySql优化了，等价与 < 20 or 20

如下两条语句

这里select * 的话就MySql不会优化了

索引字段涉及类型强转、mysql函数调用、表达式计算等，索引就用不上了 —— 索引失效
如 where md5(password) = “xxxxx”;
where mobile = 12345678; (这里mobile是字符串)

当你被问道: SQL和索引的优化问题时，怎么切入? ? ?
explain分析sql
项目——>很多业务——>各种各样的sql干条.万条
流程:从什么地方能够获取哪些运行时间长， 耗性能的sql; 然后再用explain去分析它!

总结

1. MysQL以主键的值构造成一颗树，叶子节点存放着该主键对应的整行数据。此为聚簇索引。

2. 其他的索引为辅助索引，叶子节点存放着索引字段的值及对应的主键值。

3. —般情况下，—次查询只能使用一条索引

4. 对查询where条件中区分度高的字段加索引

5. 联合索引，叶子节点存储的顺序以创建时指定的顺序为准，因此区分度高的放左边，能被多个查
   询复用到的放左边

6. 只select需要用到的字段，尽量避免select *

7. 如有必要，可使用FORCE INDEX强制索引

8. 多表JoIN，先按各表的查询条件比较哪个开销小，从小表取出所有符合条件的，到大表循环查找（有待考证）

9. 以下情况无法使用到索引，like通配符在最左，not in，!=，<>，对列做函数运算，隐式数据类型
   转换，OR子句