MySql：索引

索引可以包含一个或多个列的值。如果索引包含多个列，那么Mysql只能高效地使用索引的最左前缀列。
在Mysql中，索引在存储引擎层而不是服务器层实现。Mysql的不同存储引擎支持不同类型的索引，例如InnoDB支持B+树索引、NDB支持T树索引、Memory引擎支持Hash索引、MyISAM引擎支持空间数据索引（R树，可以存储地理空间信息）。
由于在生产环境我们主要使用InnoDB作为表的存储引擎，因此这里主要介绍InnoDB相关的索引知识。

1 InnoDB的B+树索引

InnoDB使用B+树索引。根据B+树的性质，通常意味着所有值都是按顺序存储的，并且每个叶子到根的距离相同，非叶子节点的指针指向其他非叶子节点，而叶子节点的指针则指向被索引的数据。
在查找数据时，从B+树索引的根节点进行搜索，根节点中存储了指向子节点的指针，存储引擎根据这些指针不断向下层查找，最终存储引擎要么找到相应的值，要么找不到。B+树索引按照顺序存储数据，所以Mysql可以支持ORDER BY和GROUP BY操作，也就很适合查找范围数据。

B+树索引适应的查询。

• 全值匹配，指的是和索引中所有列进行匹配。
• 匹配最左前缀。
• 匹配列前缀。
• 匹配范围值。
• 精确匹配某一列并范围匹配另一列。

B+树索引的优点。

• 索引大大减少了服务器需要扫描的数据数量。
• 索引可以帮助服务器避免排序和临时表。
• 索引可以将随机IO变成顺序IO

B+树索引的一些限制。

• 如果不是按照索引的最左列开始查找，则无法使用索引。
• 不能跳过索引中的列。
• 如果查询中有某个列的范围查询，则其右边所有列都无法使用索引优化查找。

自适应哈希索引。
InnoDB有一项功能是自适应哈希索引（Adaptive Hash Index）。当InnoDB注意到某些索引值被使用的非常频繁时，它会在内存中基于B+树索引之上再创建一个哈希索引，这样就使得B+树索引也具有哈希索引的一些优点，比如快速的哈希查找。

2 正确的创建和使用索引

2.1 索引不生效的情况

• 索引列是表达式的一部分，或是函数的一部分。
  SELECT book_d FROM book WHERE book_id+1=5; 索引book_id是表达式的一部分
  SELECT book_id FROM book WHERE TO_DAYS(CURRENT_DATE)-TO_DAYS(gmt_create)<=10 索引gmt_create是函数的一部分

• 隐式类型转换。
  SELECT * FROM tradelog WHERE tradeid=10000;
  tradelog表中tradeid字段是varchar(32)类型，执行上面的sql会隐式地将字符串转成整数，即命中了2.1的规则
  SELECT * FROM tradelog WHERE CAST(tradeid AS signed int)=10000;

• 隐式编码转换。
  查询跨两张表，两张表的CHARSET不同，例如一个是utf8，一个是utf8mb4，因为utf8mb4是utf8的超集，所以会自动将utf8转成utf8mb4，命中第一条规则

2.2 前缀索引和索引选择性

• 前缀索引。对于BLOB、TEXT或者很长的VARCHAR类型的列，必须使用前缀索引。一般情况下前缀索引的选择性也是足够高的，足以满足性能。
  在创建前缀索引时，前缀长度需要着重考量，既要使前缀足够长，从而保证较高的选择性，又不能太长以便节省空间。Mysql无法使用前缀索引做ORDER BY和GROUP BY，也无法使用前缀索引做覆盖扫描。
  可以通过ALTER TABLE a ADD KEY (b(7))在表a的b字段上添加一个长度是7的前缀索引。

• 索引选择性。不重复的索引值（基数，cardinality）和数据表的记录总数#T的比值，范围是[1/#T, 1]。索引选择性越高则查询效率越高，因为选择性高的索引可以让Mysql在查找时过滤掉更多的行。唯一索引的选择性是1，这是最好的索引选择性，性能也是最好的。

2.3 多索引列

选择索引列顺序的一个经验法则：在不需要考虑排序和分组时，将选择性最高的列放在索引最前列。
需要注意的是，查询的性能不只和所有列的选择性有关，也和查询条件的具体值以及值的分布有关。

2.4 聚簇索引

聚簇索引并不是一种单独的索引类型，而是一种数据存储方式。InnoDB的聚簇索引在同一个结构中保存了B+树索引和数据行。当表有聚簇索引时，它的数据实际上存储在索引的叶子节点中。“聚簇”表示数据和键紧凑地存储在一起。因为无法同时把数据存放在两个不同的地方，所以一个表只能有一个聚簇索引。

上图展示了聚簇索引中的记录是如何存放的。可以看到叶子节点包含了行的所有数据，但是非叶子节点只包含了索引列。