前言

索引是一种数据之间的映射关系，在MySQL中也可以称为Key（键），Key是从数据当中提取的用于标识、检索数据的特定内容。数据库设计中合理的索引可以帮助进行快速的数据查找，但是过多的索引也会造成存储负担，严重的甚至会影响数据库使用性能。

索引分类

索引按照不同的分类标准可以分为多种类型，下面列举了在MySQL中常见的几种分类形式：

  • 从数据结构的角度对索引进行分类
    • B+Tree索引：顾名思义这种索引采用的是B+Tree的结构实现的索引，也是InnoDB引擎中通常使用较多的算法。
    • Hash索引：这种索引的实现使用的是Hash算法，在内存中效率较快，例如MySQL的Memory存储引擎使用的就是Hash索引，时间复杂度为O(1)，适合等值等值查找场景（字典）。
    • 全文索引：全文索引一般用来进行复杂条件检索，使用倒排索引（nverted index）实现，也常被称为反向索引、置入档案或反向档案，是一种索引方法，被用来存储在全文搜索下某个单词在一个文档或者一组文档中的存储位置的映射。它是文档检索系统（如ES）中最常用的数据结构。
  • 从物理存储的角度对索引进行分类
    • 聚簇索引：聚簇索引的功能是存储引擎实现的，并非所有的存储引擎都可以支持聚簇索引。聚簇表示的含义为数据行和相邻的键值紧凑的存储在一起，找到了索引也就找到了数据的，因为无法把数据行同时存放在两个地方，因此一张表只能包含一个聚簇索引。聚簇索引一般通过主键建立，如果没有设置主键，存储引擎会自动选取非空列作为聚簇索引。
    • 非聚簇索引：建立在聚簇索引之上的索引也叫做二级索引或者辅助索引，索引和数据存储进行了分离，其叶子节点存储的为数据记录所在的主键值，对于具体数据通常需要进行二次查找。
  • 从索引字段特性角度分类
    • 主键索引：表结构中主键所在的列会被自动维护一个聚簇索引进行数据的真实物理存储。
    • 唯一索引：建立唯一索引的列值不允许存在重复（如主键），在数据进行插入的时候会进行唯一性校验，事实上数据的唯一性是通过索引来实现的。此外在进行数据检索时，唯一索引在检索到符合条件的值后不会继续检索，而普通索引会继续进行范围检索，但是在数据插入时唯一索引会多一次数据检索，至于使用普通索引还是唯一索引，需要根据实际场景进行决策。
    • 普通索引：这里所说的普通索引泛指一般意义上的索引，即没有主键、唯一等特性的索引。而主键、唯一也只是区分于普通索引的一种说法。
    • 前缀索引：可以看作普通索引的一种，对于一些比较特殊或者进行索引维护意义不大的数据（如身份证），可以对数据信息的前缀进行索引维护提高查询效率。
  • 从组成索引的字段数量角度分类
    • 单列索引：对于数据库中的某个高频使用条件进行索引维护。
    • 联合索引：又称为复合索引，对于数据库中两个或以上的列创建索引，当创建索引（a，b，c）时会满足a|ab|abc三种查找条件，满足最左匹配原则不支持bc检索。对于Where条件中每个列创建单列索引是一种错误的方式，关于联合索引的最佳使用效果需要结合EXPLAIN进行具体分析。
    • 覆盖索引：覆盖索引从分类上来说并不是一种具体的索引类型，而是一种满足特定条件的使用方式。如果一个查询中，使用到的索引覆盖到所有需要查找的列就可以称之为覆盖索引，这种情况下不需要回表进行二次查找，这也是设计索引的一种优化手段。

      说明：对于聚簇索引，非叶子节点中存储的数据的指针，叶子节点中存储的是真实数据，通过聚簇索引查找数据的方式可以通过覆盖索引进行模拟；非聚簇索引其叶子节点不是数据存储的真实物理页而是主键的值，因此使用非聚簇索引又不满足覆盖索引的场景下，需要进行二次回表查找。

索引实现

关于索引按照不同的分类进行了简单介绍，下面从索引使用的算法或者数据结构角度来对MySQL中常见的几种索引实现进行简单分析。

Hash

B+Tree

索引和锁

使用索引可以让数据查询过程中避免全表扫描，尽可能减少数据检索行数，在并发读写过程中索引也可以使得锁定的行数更少，提高数据的检索效率。无论是提高查询效率和并发操作使用索引的好处都是显而易见的。
InnoDB中在使用二级索引时会使用共享（读）锁，在访问主键索引时不可避免的使用排他（写）锁，因此在使用SELECT…FOR UPDATE时要比使用LOCK IN SHARE MODLE要慢的多。

查询优化

数据库中的优化原则除去数据库参数调优、表结构设计、字段定义等方面的内容，最主要的就是在索引方面的优化，关于索引的优化也可以分为两部分，一部分是索引的创建、重建、碎片化维护等，另一部分就是SQL语句对于索引的匹配效率。这里重点进行后者的一些原则介绍。

基本原则

这部分主要介绍，对于开发人员在使用MySQL中，需要掌握的索引相关的基本优化原则。

  1. 避免表达式

在使用SQL查询时应该避免使用列参与表达式计算。
例如：explain select * from employee where emp_no + 1 = 10002，查看执行计划，这种情况下会导致数据进行全表扫描。

select_type table partitions type id possible_keys key key_len ref rows filtered Extra
SIMPLE employee NULL ALL 1 NULL NULL NULL NULL 299210 100 Using where

直接使用等值结果进行查询 explain select * from employee where emp_no = 10001，查看执行计划，使用了索引查询，type为ref，实际检索行rows为1。

id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 SIMPLE employee NULL ref idx_n_f_l idx_n_f_l 4 const 1 100 NULL
  1. 使用主键索引

MySQL中会根据主键索引自动生成聚簇索引，这种情况下，索引与数据共同存放，找到了索引也就定位到了数据，因此在查询中尽可能使用主键索引，避免二次回表。

  1. 使用覆盖索引

原理同主键索引，如果所查询的列刚好全部被索引覆盖到，那么就不需要进行回表，减少了IO次数，因此在查询中，尽可能的匹配覆盖索引，但是为了满足覆盖索引的条件过度创建索引，本身也是一种磁盘负担。

  1. 使用前缀索引

对于一些特殊场景，列值比较长又不适合对数据进行完整索引，比如身份证信息，可以使用前缀索引。但是对于这种前缀索引会降低索引选择性，索引的选择性是指不重复的索引值和数据表记录总数的比值。索引的选择性越高则查询效率越高，选择性更高的索引可以让MySQL在查找的时候过滤掉更多的行。
数据库中BLOB、TEXT、VARCHAR（一般MySQL5.6限制为255字节，MySQL5.7为3072字节）类型是不允许索引这些列的完整长度，必须要使用前缀索引。因此需要选择足够长的前缀以保证较高的选择性，但又不能太长而影响效率。�

  1. 使用索引排序


  1. union all、or、in
  2. 最左匹配原则
  3. 注意范围查找
  4. 避免类型转换
  5. 创建合适列索引
  6. 非空索引列
  7. 注意多表join
  8. 合理使用limit

在可以确定需要检索数据的记录数时使用limit会减少查询的行数，如果limit满足记录后会立即返回数据，不再继续进行查找。

  1. 单表索引数限制

单表索引数量建议控制在5个以内，太多的索引会导致磁盘空间的浪费，如果实际场景下确实需要很多索引，建立多个索引影响也不大。

  1. 组合索引长度限制

建立组合索引时，组合的列要尽量避免超过5个。因为有最左匹配的规则，索引组合索引列非常多时，可能会造成磁盘负担，索引失效等，需要根据实际场景，合理拆分建立联合索引。

  1. 索引使用误区

索引的建立和使用不能脱离业务实际场景，使用索引的目的是为了减少IO次数，提高查询效率，常见误区如下：

  • 误区一：单列索引越多越好（过多的索引会造成磁盘消耗，数据插入修改成本）
  • 误区二：联合索引列越多越好（除了磁盘消耗，根据最左匹配原则，还会导致索引失效，根据实际拆分）
  • 误区三：不了解系统的情况下进行优化（脱离业务场景的索引往往有可能是无效索引）
  • 误区四：所有的查询优化都可以依赖于索引（减少IO的有效措施更应该降低IO，合理设计表结构及字段）

    优化策略

    这部分介绍MySQL中自身所进行的一些关于词法、筛选、缓存等方面的优化策略，简单说明一些基本概念。
  1. 查询缓存
  2. 解析器和预处理
  3. 优化器
  4. 静态优化
  5. 动态优化
  6. 排序优化
  7. 等值传播
  8. 子查询优化
  9. 覆盖索引
  10. 索引下推

ICP（Index Condition Pushdown）是在MySQL 5.6版本上推出的查询优化策略，把本来由Server层做的索引条件检查下推给存储引擎层来做，以降低回表和访问存储引擎的次数，提高查询效率。

  1. 函数优化
  2. 等价变换
  3. 关联查询

    总结

  • 索引的主要目的用来在大量数据中进行高效的数据检索，索引按照不同的维度可以分为多种类型，MySQL中通常意义上所说和使用的索引也都是B+Tree索引，其他索引在特定的存储引擎或场合才会使用。
  • 聚簇索引和非聚簇索引是按照数据存储的结构形式进行划分的，聚簇索引索引和数据同时存储在一起，非聚簇索引查询数据需要进行二次查找。
  • 索引可以减少查找数据检索的行数，使用索引也可以减少锁定的行数，提高数据库的并发性能，InnoDB只有访问行才会对数据进行加锁，二级索引使用读锁，主键使用写锁，因此这也消除了使用覆盖索引的可能。
  • 根据实际业务场景进行合理的表结构设计及索引建立，在使用SQL进行查询时尽可能的去匹配索引规则是数据库慢查询有效的优化手段。

参考资料：