文章目录

  • 0.什么是索引
  • 1. MySQL是如何利用索引进行优化
  • 2. 优化主键，外键
  • 2.1 主键
  • 2.2 外键
  • 3. 单列索引和多列索引
  • 3.1 单列索引（Column Indexes）
  • 3.2 多列索引（Multiple-Cloumns Indexes）
  • 3.3 如何知道本次查询使用了哪些索引？
  • 4. InnoDB索引分析集合（Index Statistics Collection)
  • 4.1 前置知识
  • 4.2 average value group size的作用
  • 4.3 innodb_stats_method
  • 5. B-Tree索引和Hash索引


0.什么是索引

MySQL索引就像汉语词典的偏旁部首索引一样，用来加快查查询速度。假设你现在不知道”草“字怎么读，想要在收录了几十万字的《现代汉语词典中》找出“草”字，无疑是大海捞针。MySQL也一样，如果想在一张存有大量数据的表中，迅速查找出我们想要的那一行，利用好索引，效率将大大提高。

1. MySQL是如何利用索引进行优化

想要提高SELECT语句的查询效率，建立索引是一个非常好的选择。如果我们的查询条件中没有使用到索引，那么MySQL将会使用全表扫描的方式来获取结果，导致效率低下。

虽说索引极为强大，但索引也不是越多越好。过多的索引会占用大量的磁盘空间，而且，insert,update的时候也得更新相关索引，这些都会带来不必要的开销。试想一下，如果你刚买来的《现代汉语词典》，10W页有5W页是偏旁部首索引，那你作何感想？

2. 优化主键，外键
2.1 主键

在MySQL中，主键天生就带有索引，无需我们自己创建。主键都是NOT NULL的，这也能加快查询效率。而且InnoDB在将表中的数据进行物理存储时，会按照主键进行整理。在查找和排序时，有主键的表明显快于无主键的表。

2.2 外键

MySQL在存储数据时，会倾向于将有关联的数据存储在存储在一起。也就是说，当A表的主键是B表的外键时，MySQL会倾向于将它们的数据存储在一起。这可以减少查询时的磁盘IO和内存占用。

3. 单列索引和多列索引
3.1 单列索引（Column Indexes）

只有一列的所有就是单列索引。单列索引可用于WHERE条件中的 =, <=, >=, >, <, BETWEEN, IN 等操作符。
如果WHERE条件中用到了两个单列索引WHERE key1 = 1 AND key2 = 2，MySQL会有两种方式来解决：

  1. 选择扫描行数较少的索引；
  2. 使用Index Merge（这个我们后面再谈）。
3.2 多列索引（Multiple-Cloumns Indexes）

多列索引就是一个索引上有多列的索引。
最左匹配原则：WHERE条件中如果包含了多列索引的全部列，或者第一列，或者前二列，或者前三列，等等，那么该索引都可以被使用到。
换句话说，如果你现在建立了一个多列索引（col1, col2, col3），那么相当于你建立了三个索引（col1）和（col1, col2）和（col1, col2, col3）。当然，实际上只有一个索引（col1，col2，col3），只是这个索引的想过相当于三个索引。

3.3 如何知道本次查询使用了哪些索引？

EXPLAIN能够告诉你答案，关于EXPLAIN，我们后面再谈。

4. InnoDB索引分析集合（Index Statistics Collection)
4.1 前置知识
  1. MySQL引擎会存储关于表的分析资料，用于优化SQL语句。有个叫average value group size的变量对优化起到至关重要的作用；
    MySQL引擎会将索引的列值相同的数据归为一组（value group)，每一组都有它的size，几个组的平均的size就是average value group size；
  2. <=>和=唯一不同点：NULL = NULL结果为false，NULL <=> NULL结果为true。
4.2 average value group size的作用
  1. EXPLAIN会有一列ref，average value group size能够用于预估每一个ref需要读取多少行数据；
  2. 预估partial join会产生多少行数据，一个partial join的例子：
(...) JOIN tbl_name ON tbl_name.key = expr
4.3 innodb_stats_method

我们通过SHOW VARIABLES like 'innodb_stats_method'可以查看数据库innodb_stats_method的值，它的作用是：再进行value group分组时，如果innodb_stats_method等于

  1. nulls_equal：两个值都是NULL会认为两个值相等；
    如果表中的数据有很多NULL值，使用nulls_equals会让average value group size的值较大（value group的数量变少了)。当你使用=作为判断条件的时候，可能导致MySQL引擎错误估计需要读取的行数，用错甚至放弃使用索引；
  2. nulls_unequal：两个值都是NULL会认为两个值不相等；
    如果表中的数据很多都是NOT NULL，使用nulls_unequals会让average value group size的值较小（value group变多了，每个NULL值都会被当作一个group），MySQL会误认为这个索引的效率很高，从而使用这个索引；
  3. nulls_ignored：不对有NULL值的行进行分组。

总结：join的条件使用<=>比较多，那么nulls_equals是个比较好的选择；如果使用=比较多，nulls_unequals是个比较好的选择。

5. B-Tree索引和Hash索引

B-Tree索引应用范围较广，=，>, >=, <, <=, BETWEEN都可以，甚至like都可以（只要不是通配符开头）。在WHERE查询条件中，只要每一个AND GROUP都被索引覆盖，那么该查询就可以使用索引。A OR B，A和B是两个AND GROUP。例如：

### 使用了索引
... WHERE index_part1=1 AND index_part2=2 AND other_column=3

    /* index = 1 OR index = 2 */
... WHERE index=1 OR A=10 AND index=2

    /* optimized like "index_part1='hello'" */
... WHERE index_part1='hello' AND index_part3=5

    /* Can use index on index1 but not on index2 or index3 */
... WHERE index1=1 AND index2=2 OR index1=3 AND index3=3;

### 没有使用索引
    /* index_part1 is not used */
... WHERE index_part2=1 AND index_part3=2

    /*  Index is not used in both parts of the WHERE clause  */
... WHERE index=1 OR A=10

    /* No index spans all rows  */
... WHERE index_part1=1 OR index_part2=10