索引是最好的解决方案吗？

索引并不总是最好的工具。总的来说，只有当索引帮助存储引擎快速查找到记录带来的好处大于其带来的额外工作时，索引才是有效的。对于非常小的表，大部分情况下简单的全表扫描更高效。对于中到大型的表，索引就非常有效。但对于特大型的表，建立和使用索引的代价将随之增长。这种情况下，则需要一种技术可以直接区分出查询需要的一组数据，而不是一条记录一条记录地匹配。

如果表的数量特别多，可以建立一个元数据信息表，用来查询需要用到的某些特性。例如执行那些需要聚合多个应用分布在多个表的数据的查询，则需要记录“哪个用户的信息存储在哪个表中”的元数据，这样在查询时就可以直接忽略那些不包含指定用户信息的表。对于大型系统，这是一个常用的技巧。事实上，Infobright就是使用类似的实现。对于TB级别的数据，定位单条记录的意义不大，所以经常会使用块级别元数据技术来替代索引。

来自 《高性能MySQL》

概述

索引是存储引擎用于快速找到记录的一种数据结构。这是索引的基本功能，除此之外，本章还将讨论索引其他一些方面有用的属性。

索引数据结构

索引有不同的数据结构，可以为不同的场景提供更好的性能。

B-Tree索引（👍）

B-TREE 是一个多叉数，每个节点都是一个二元数组: [key, data]，所有节点都可以存储数据。key为索引key,data为除key之外的数据，B-TREE可以满足等值查询、范围查询的要求，但是插入删除新的数据记录会破坏B-Tree的性质，因此在插入删除时，需要对树进行一个分裂、合并、转移等操作以保持B-Tree性质。造成IO操作频繁。因为所有节点都存储了数据，区间查找可能需要返回上层节点重复遍历，IO操作繁琐。B-Tree的结构如下图所示：

B+TREE在B-TREE的基础上做了以下改进：

• 非叶子节点不存储数据，只存储索引key，只有叶子节点存储数据，只利用索引快速定位数据索引范围，先定位索引再通过索引高效快速定位数据。
• 叶子节点存储了指向下一个节点的指针，在范围查询时可加快查询性能。
  

以主键索引为例，检索过程如下所示：

• 等值检索

  
  查找id为23记录，确定定位条件, 找到根节点Page No, 根节点读到内存, 逐层向下查找, 读取叶子节点Page,通过 二分查找找到记录或未命中

• 范围查找

  

  查找id为18到23的记录：读取根节点至内存, 确定索引定位条件id=18, 找到满足条件第一个叶节点, 顺序扫描所有结果, 直到终止条件满足id >=21

• 全表扫描

  直接读取叶节点头结点， 顺序扫描， 返回符合条件记录， 到最终节点结束。

非主键索引查找:

非主键索引存的是主键值，查到主键值后再查询数据。

联合索引：

与单个索引不一样的是存储的key包含多个值，按照顺序存放，因此使用唯一索引需要遵从最左匹配原则。

全文索引

FULLTEXT是一种特殊类型的索引，它查找的是文本中的关键词，而不是直接比较索引中的值。全文索引和其他几类索引的匹配方式完全不一样。它有许多需要注意的细节，如停用词、词干、复数、布尔搜索等。全文索引更类似于搜索引擎做的事情，而不是简单的WHERE条件匹配。（一般不用）

哈希索引

哈希索引(hash index)基于哈希表实现，只有精确匹配索引所有列的查询才有效进。对于每一行数据，存储引擎都会对所有的索引列计算一个哈希码,哈希码是一个较小的值，并且不同键值的行计算出来的哈希码也不一样。哈希索引将所有的哈希码存储在索引中，同时在哈希表中保存指向每个数据行的指针。在MySQL中，只有Memory引擎显式支持哈希索引。这也是Memory引擎表的默认索引类型，Memory引擎同时也支持B-Tree索引。值得一提的是，Memory引擎是支持非唯一哈希索引的，这在数据库世界里面是比较与众不同的。如果多个列的哈希值相同，索引会以链表的方式存放多个记录指针到同一个哈希条目中。

索引类型

聚簇索引

在InnoDB中聚簇索引实际上在同一个结构中保存了B-tree索引和数据行，一个表只能有一个聚簇索引。如图标记所示，索引跟数据存储在一起。

优点：

• 访问数据速度更快。因为聚簇索引将索引和数据保存在同一个B-tree中。

缺点：

• 更新聚簇索引列的代价很高，因为它会强制InnoDB将每个被更新的行移动到新的位置。
• 插入速度严重依赖于插入顺序。按照主键的顺序插入行是将数据加载到InnoDB表中最快的方式。一般使用自增主键，避免使用随机聚簇索引，因为索引要保证顺序性，使用随机索引会导致频繁的页分裂和碎片。

注意

在InnoDB中，主键为聚簇索引，如果没有定义主键，InnoDB会选择一个唯一的非空索引代替，如果没有则会隐性定义一个主键作为聚簇索引，这样会导致所有需要使用这种隐藏主键的表都依赖一个单点的主键自增，这可能会导致非常高的锁竞争，从而出现性能问题。

覆盖索引

覆盖索引（Covering Index）是一种特殊类型的索引，它包含了查询所需的所有数据列，从而避免了数据库查询时访问实际数据行，直接通过索引本身就能够满足查询的需求。通过使用覆盖索引，可以显著提高查询性能，减少查询的执行时间和资源消耗。

优点：

• 减少IO操作：由于覆盖索引直接包含了查询所需的数据列，查询可以在索引层完成，减少了对实际数据行的IO操作，从而提高了查询性能。

缺点：

• 更新代价：当表的数据发生变化时（插入、更新、删除），覆盖索引也需要进行相应的更新。

高性能索引

• 独立的列，索引不能是表达式的一部分，也不能是函数的参数。

• 选择区分度高的字段去做索引。

• 组合索引选择合适的索引列顺序，将选择性最高的列放到最前面通常是最好的。

• 使用覆盖索引，如果一个索引包含（或者说覆盖）所有需要查询的字段的值，我们就称之为覆盖索引。

• **避免使用select, **select *不会走覆盖索引，会出现大量的回表操作，而从导致查询sql的性能很低，多查出来的数据，通过网络IO传输的过程中，也会增加数据传输的时间。

• 用union all代替union，union关键字后，可以获取排重后的数据，union all关键字，可以获取所有数据，包含重复的数据，排重的过程需要遍历、排序和比较，它更耗时，更消耗cpu资源。

• 小表驱动大表。

  • in 适用于左边大表，右边小表。如果sql语句中包含了in关键字，则它会优先执行in里面的子查询语句，然后再执行in外面的语句。如果in里面的数据量很少，作为条件查询速度更快
  • exists 适用于左边小表，右边大表。exists优先执行exists左边的语句（即主查询语句）。然后把它作为条件，去跟右边的语句匹配。

• join的表不宜过多，根据阿里巴巴开发者手册的规定，join表的数量不应该超过3个。

• join注意事项

  join使用最多的是left join和inner join。

  • left join：求两个表的交集外加左表剩下的数据。mysql会默认用left join关键字左边的表，去驱动它右边的表。如果左边的表数据很多时，就会出现性能问题

  • inner join：求两个表交集的数据。inner join关联，mysql会自动选择两张表中的小表，去驱动大表，所以性能上不会有太大的问题

    注意事项

    要特别注意的是在用left join关联查询时，左边要用小表，右边可以用大表。如果能用inner join的地方，尽量少用left join。

• 单表的索引数量应该尽量控制在5个以内，并且单个索引中的字段数不超过5个

• 联合索引注意最左匹配原则：按照从左到右的顺序匹配，MySQL会一直向右匹配索引直到遇到范围查询(>、<、between、like)然后停止匹配。

• 避免非必要的类型转换，字符串字段使用数值进行比较的时候会导致索引无效。

索引失效常见原因

EXPLAIN索引优化

EXPLAIN语句提供了MySQL如何执行语句的信息。EXPLAIN适用于SELECT、DELETE、INSERT、REPLACE和UPDATE语句。相关字段如下：

id列

• 序号相同从上到下顺序执行
• 序号不相同的从大到小顺序执行
• 既有相同又有不同的，先按照序号从大到小执行，相同的从上到下执行

select_type

table列

所引用表的名称

partitions列

匹配记录的分区

type列

possible_keys列

该列表示可能的索引选择。

key列

该列表示实际用到的索引。

key_len列

该列表示使用索引的长度。上面的key列可以看出有没有使用索引，key_len列则可以更进一步看出索引使用是否充分。

ref列

该列表示索引命中的列或者常量。

rows列

该列表示MySQL认为执行查询必须检查的行数。

filtered列

该列表示按表条件过滤的表行的估计百分比。最大值为100，这表示未过滤行。值从100减小表示过滤量增加。

Extra列

• Impossible WHERE，表示WHERE后面的条件一直都是false

• Using filesort，表示按文件排序，一般是在指定的排序和索引排序不一致的情况才会出现

• Using index，表示是否用了覆盖索引，说白了它表示是否所有获取的列都走了索引。

• Using temporary，临时表

• Using where，where条件过滤

• 其他类型参考 Extra描述

索引命名规范

参考文章

• MySQL explain官方文档
• 索引优化的这把绝世好剑，你真的会用吗？
• 阿里Java开发规范