MySQL-索引

概述

索引（Index）是帮助MySQL高效获取数据的数据结构（有序）。在数据之外，数据库系统还维护着满足特定查找算法的数据结构，这些数据结构以某种方式引用（指向）数据，这样就可以在这些数据结构上实现高级查找算法，这种数据结构就是索引。

一般来说索引本身也很大，不可能全部存储在内存中，因此索引往往以索引文件的形式存储在磁盘上。索引是数据库中用来提高性能的最常用工具。

优缺点

优势

• 类似于书籍的目录索引，提高书籍检索的效率，降低数据库的 IO 成本
• 通过索引列对数据进行排序，降低数据排序的成本，降低 CPU 的消耗

劣势

• 实际上索引也是一张表，该表中保存了主键与索引字段，并指向实体类的记录，所以索引也是要占用空间的
• 虽然索引大大提高了查询效率，同时也降低了更新表的速度，如对表进行 INSERT、UPDATE、DELETE。因为更新操作，MySQL 不仅要保存数据，还要保存索引文件。每次更新添加了索引列的字段，都会调整因为更新所带来的键值变化后的索引信息。

索引类型

按应用层次划分

• 普通索引
  最基本的索引类型，基于普通字段建立的索引，没有任何限制
• 唯一索引
  与“普通索引”类似，不同的是：索引字段的值必须唯一，但允许有空值。在创建或修改表时追加唯一约束，会自动创建对应的唯一索引。
• 主键索引
  它是一种特殊的唯一索引，不允许有空值。在创建或修改表时追加主键约束即可，每个表只能有一个主键
• 复合索引
  复合索引（组合索引）就是在多列上建立索引。复合索引可以代替多个单一索引，相比多个单一索引，复合索引所需的开销更小

复合索引使用注意事项：

• 何时使用复合索引，要根据 WHERE 条件建索引，不要过多使用索引，过多会对更新操作效率有影响
• 如果表已经建立了（col1, col2），就没必要再单独建立（col1）;如果现有（col1）索引，如果查询需要 col1 和 col2 条件，可以建立（col1, col2）复合索引，对查询有一定提高

索引有两个概念

• 窄索引：指索引列1~2列的索引
• 宽索引：指索引列超过2列的索引

TIPS：索引设计的一个重要原则是能用窄索引不用宽索引，因为窄索引往往比宽索引更有效

按存储结构划分

索引是在 MySQL 的存储引擎层中实现的，而不是在服务器层实现的。所以每种存储引擎的索引都不一定完全相同，也不是所有的存储引擎都支持所有的索引类型。

BTREE 索引

最常见的索引类型，大部分引擎都支持B+树索引

HASH 索引

Hash 底层是由 Hash 表来实现的，是根据键值<key, value>存储数据的结构。非常适合根据 key 查找 value 值，也就是等值查询。但是对于范围查找就需要全表扫描。

Hash 索引在 MySQL 中主要应用在 Memeory 引擎和 InnoDB 自适应哈希索引。使用场景简单

R-Tree 索引

空间索引是 MyISAM 引擎的一个特殊索引类型，主要用于地理空间数据类型，通常使用较少。

Full-text 索引

全文索引是一个特殊索引类型，主要用于全文索引。

查询操作在数据量比较少时，可以使用 like 模糊查询，但是对于大量的文本数据检索，效率很低。如果使用全文索引，查询效率会比 like 快很多倍。在 MySQL 5.6以前，只有 MyISAM 存储引擎支持全文索引，从 MySQL 5.6 开始 MyISAM 和 InnoDB 存储引擎均支持

我们平常所说的索引，如果没有特别指明，都是指 B+ 树（多路搜索树）组织结构的索引。其中聚集索引、复合索引、前缀索引、唯一索引默认都是使用 B+ tree 树索引，统称为索引。

聚簇索引和辅助索引

按键值类型分为主键索引和辅助索引：键值存放的是主键字段值就属于主键索引；如果存放的是非主键值就属于辅助索引

按数据存储和索引逻辑关系分为聚簇索引和非聚簇索引：叶子节点存放主键索引值和行记录就属于聚簇索引；如果索引值和行记录分开存放就属于非聚簇索引

• 聚簇索引
  聚簇索引是一种数据存储方式，InnoDB 的聚簇索引就是按照主键顺序构建 B+Tree 结构。B+Tree 的叶子节点就是行记录，行记录和主键值紧凑地存储在一起。这也意味着 InnoDB 的主键索引就是数据表本身，它按主键顺序存放了整张表的数据，占用的空间就是整个表数据量的大小。通常说的“主键索引”就是聚簇索引

InnoDB 表要求必须有聚簇索引

• 如果表定义了主键，则主键索引就是聚簇索引
• 如果表没有定义主键，则第一个非空 unique 列作为聚簇索引
• 否则 InnoDB 会重建一个隐藏的 row-id 作为聚簇索引

• 辅助索引
  InnoDB 辅助索引也叫作二级索引，是根据索引列构建 B+Tree 结构。在 B+Tree 的叶子节点中只存了索引列和主键的信息。二级索引占用的空间会比聚簇索引小很多，通常创建辅助索引就是为了提升查询效率。InnoDB 只能创建一个聚簇索引，但可以创建多个辅助索引
  
• 非聚簇索引
  与 InnoDB 表存储不同，MyISAM数据表的索引文件和数据文件是分开的，被称之为非聚簇索引结构
  

索引设计原则

列的离散型
离散型计算公式：count(distinct column_name):count(*)，就是用去重后的列值个数比总个数。
离散值越高，通过索引最终确认的范围越小，最终扫描的行数也就越少，选择性越好。

最左匹配原则
对于索引中的关键字进行对比时，一定是从左往右依次对比，且不可跳过。SQL查询中使用 like %xx时候索引会失效，因为%表示全匹配，如果已经全匹配就不需要索引，还不如直接全表扫描。

最少空间原则
当关键字占用的空间越小，则每个节点保存的关键字个数就越多，每次加载进内存的关键字个数就越多，检索效率就越高。创建索引的关键字要尽可能占用空间小。

索引的设计可以遵循一些已有的原则，创建索引的时候尽量考虑符合这些原则，便于提升索引的使用效率，更高效的使用索引。

• 对查询频次较高，且数据量比较大的表建立索引
• 索引字段的选择，最佳候选列应当从where子句的条件中提取，如果where子句中的组合比较多，那么应当挑选最常用、过滤效果最好的列的组合
• 使用唯一索引，区分度越高，使用索引的效率越高
• 索引列的数据长度满足业务的情况下能少则少
• 表中的索引并不是越多越好，冗余或者无用索引会占用磁盘空间并且会影响增删改的效率
• Where条件中，like 9%，like %9%，like %9 三种方式都用不到索引。后两种方式对于索引是无效的。第一种9%是不确定的，决定于列的离散性，结论上讲可以用到，如果发现离散情况特别差的情况下，查询优化器觉得走索引查询性能更差，还不如全表扫描
• Where条件中IN可以使用索引，NOT IN无法使用索引
• 多用指定查询，只返回自己想要的列，少用select *
• 查询条件中使用函数，索引将会失效，这和列的离散性有关，一旦使用到函数，函数具有不确定性
• 联合索引中，如果不是按照索引最左列开始查找，无法使用索引
• 对联合索引精准匹配最左前列并范围匹配另一列，可以使用到索引
• 联合索引中，如果查询有某个列的范围查询，其右边所有的列都无法使用索引

索引分析与优化

EXPLAIN

MySQL 提供了一个 EXPLAIN 命令，它可以对 SELECT 语句进行分析，并输出 SELECT 执行的详细信息，供开发人员有针对性的优化

回表查询

InnoDB 索引有聚簇索引和辅助索引。聚簇索引的叶子节点存储行记录，InnoDB 必须要有，且只有一个。辅助索引的叶子节点存储的是主键值和索引字段值，通过辅助索引无法直接定位行记录，通常情况下，需要扫描两遍索引树。先通过辅助索引定位主键值，然后在通过聚簇索引定位行记录，这种操作叫做“回表查询”

覆盖索引

What is a covering index?
A covering index is a non-clustered index which includes all columns referenced in the query and therefore, the optimizer does not have to perform an additional lookup to the table in order to retrieve the data requested. As the data requested is all indexed by the covering index, it is a faster operation.

只需要在一棵索引树上就能获取 SQL 所需的所有列数据，无需回表，速度更快，这就叫做“索引覆盖”
实现索引覆盖最常见的方法就是：将被查询的字段，建立到组合索引

最左前缀原则

复合索引使用时遵循最左前缀原则，顾名思义：就是最左优先，即查询中使用到最左边的列，那么查询就会使用到索引，如果从索引的第二列开始查找，索引将失效

NULL查询

对 MySQL 来说，NULL 是一个特殊的值，从概念上讲，NULL 意味着“一个未知值”，它的处理方式与其他值有些不同。比如：不能使用 =，<，> 这样的运算法，对 NULL 做算术运算的结果都是 NULL，count 时不会包含 NULL 行等，NULL 比空字符串需要更多的存储空间等。

NULL 列需要增加额外空间来记录其值是否为 NULL。对于 MyISAM 表，每一个空列额外占用一位，四舍五入到最接近的字节

虽然 MySQL 可以在含有 NULL 的列上使用索引，但 NULL 和其他数据还是有区别的，不建议列上允许为 NULL。最好设置 NOT NULL，并给一个默认值，比如 0和空字符串等。对于datetime类型可以设置系统当前时间或某个固定的特殊值，例如'1971-01-01 00:00:00'

索引与排序

MySQL 查询支持 filesort 和 index 两种方式的排序，filesort 是先把结果查出，然后再缓存或磁盘进行排序操作，效率较低。使用 index 是指利用索引自动实现排序，不需另做排序操作，效率会比较高

filesort 有两种排序算法

• 双路排序
  需要两次磁盘扫描读取，最终得到用户数据。第一次将排序字段读取出来然后排序；第二次去读取其他字段数据
• 单路排序
  从磁盘查询所需的所有列数据，然后再内存排序将结果返回。如果查询数据超出缓存 sort_buffer，会导致多次磁盘读取操作，并创建临时表，最后产生很多次IO，反而会增加负担。
  解决方案：少用 select *; 增加 sort_buffer_size 容量 和 max_length_for_sort_data 容量

用 EXPLAIN 分析 SQL，结果中 Extra 属性显示 Using filesort，表示使用了 filesort 排序方式，需要优化；如果 Extra 属性显示 Using Index 时，表示覆盖索引，所有操作在索引上完成；建议尽可能采用覆盖索引

以下情况会使用 index 方式排序

# ORDER BY 子句索引列组合满足索引最左前列
explain select id from user order by id; // 对应(id)、(id, name)索引有效

# WHERE 子句 +  ORDER BY 子句索引列组合满足索引最左前列
explain select id from user where age = 18 order by name; // 对应 (age, name)索引

以下情况会使用 filesort 方式的排序

# 对索引列同时使用了 ASC 和 DESC
explain select id from user order by age asc, name desc; // 对应(age, name)索引

# WHERE 子句和 ORDER BY 子句满足最左前缀，但 WHERE 子句使用了范围查询（例如>、<、in等）
explain select id from user where age > 10 order by name; // 对应(age, name)索引

# ORDER BY 或 WHERE + ORDER BY 索引列没有满足索引最左前列
explain select id from user order by name;  // 对应(age, name)索引

# 使用了不同的索引，MySQL 每次只采用一个索引，ORDER BY 涉及了两个索引
explain select id from user order by name, age;  // 对应(name)、(age)两个索引

# WHERE 子句与 ORDER BY 子句，使用了不同的索引
explain select id from user where name = 'tom' order by age;  // 对应(name)、(age)索引

# WHERE 子句或 ORDER BY 子句中索引列使用了表达式，包含函数表达式
explain select id from user order by abs(age); // 对应(age)索引