MySQL-创建好性能的索引

基础

索引是存储引擎用于快速找到记录的一种数据结构。

如果使用ORM，是否还需要关心索引？
ORM工具能够生产符合逻辑的，合法的查询(多数时候)，除非是只生成非常基本的查询(例如是根据主键查询)，否则它很难生成合适索引的查询。所以使用ORM任然需要关心索引

索引的类型

索引是在存储引擎层而不是服务层实现的。不同的存储引擎的索引的工作方式并不一样，也不是所有的存储引擎都支持索引。

• B-Tree : MyISAM使用前缀压缩技术使得索引更小，但InnoDB则按照原数数据格式进行存储。MyISAM索引通过数据的物理位置引用索引的行，而 InnoDB则根据主键引用被索引的行。一些限制：
  • 如果不是按照索引的最左列开始查找，则无法使用索引。
  • 不能跳过索引中的列。
  • 如果查询中有某个列是范围查询，则其右边的所有列都无法使用索引优化查找。
• 哈希索引：基于哈希表实现，只有精确匹配索引所有列查询才有效。因为索引自身只需存储对应的哈希值，所以索引的结构十分紧凑，这也让哈希索引查找的速度非常快。但是，哈希索引也有它的限制：
  • 哈希索引只包含哈希值和行指针，而不存储字段值，所以不能使用索引中的值来避免读取行。
  • 无法用来排序
  • 不支持部分索引列匹配查询
  • 只支持等值比较查询
  • 当出现哈希冲突的时候，存储引擎必须遍历链表中所有的行指针，逐行进行比较，直到找到符合条件的行
  • 如果哈希冲突很多的话，一些索引的维护操作的代价也会很高
• 空间数据索引：MyISAM表支持空间索引，可以用作地理数据存储。空间索引会从多维度来维护索引数据。
• 全文索引：类似于搜索引擎做的事情，多用于MyISAM引擎FULLTEXT

索引的优点

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

高性能的索引策略

独立的列

索引的列不能是表达式的一部分，也不能是函数的参数。例如

SELECT age FROM demo.user WHERE age + 1 = 23; 
-- or
SELECT ... WHERE TO_DAYS(CURRENT_DATE) - TO_DAYS(data_col) <= 10;

前缀索引和索引选择性

对于像BLOB、TEXT或很长的VARCHAR类型的类，必须使用前缀索引。但是前缀索引无法做ORDER BY 和GROUP BY，也无法使用前缀索引做覆盖扫描。

多列索引

多列索引解决的问题：

• 当服务器对多个索引做相交操作时，通常意味着需要一个包含所有相关列的索引，而不是多个独立的单列索引
• 当服务器需要对多个索引做联合操作的时候，需要消耗大量的CPU和内存资源的缓存、排序和合并操作上。特别是当其中有些索引的选择性不高，需要合并扫描返回大量的数据的时候

选择合适的索引列顺序

当不需要考虑排序和分组时，将选择性最高的列放在最前面。

聚簇索引

并不是一种单独的索引类型，而是一种数据存储方式。数据存放在索引的叶子页中。InnoDB通过主键聚集数据，如果没有定义主键，InnoDB会选择一个唯一的非空索引代替，如果没有则会隐式的定义一个主键来作为聚簇索引。
优点：

• 可以把相关的数据保存在一起。(减少部分I/O)
• 数据访问更快
• 使用覆盖索引扫描的查询可以直接使用页节点中的主键值

缺点：

• 如果数据都放在内存中，则没有必要
• 插入速度严重依赖于插入顺序
• 更新聚簇索引列的代价很高，因为会强制InnoDB将每个被更新的行移动到新的位置
• 基于聚簇索引的表插入新行，或者主键被更新导致需要移动行的时候，可能面临页分裂的问题
• 可能会导致全表扫描变慢，尤其是行稀疏或者是页分裂导致的数据不连续
• 二级索引可能比想象的要大，因为二级索引在叶子节点包含了引用行的主键
• 二级索引访问需要两次索引查找

在InnoDB表中按照主键顺序插入行，主键最好是自增长的。但是在分布式中，并发的插入可能会导致间隙锁竞争，以及AUTO_INCREAMENT锁机制。

覆盖索引

优点：

• 减少数据访问量，对缓存的负载非常重要
• 因为索引是按照列值顺序存储的，所以对于I/O密集型的范围查询回避随机从磁盘读取的i/o要少
• InnoDB的二级索引在叶子节点保存了行的主键值，如果二级索引能够覆盖查询，可以避免对主键索引的二次查询
• 一些引擎比如MyISAM在内存中只缓存索引，数据依赖于操作系统来缓存，因此要访问数据需要一次系统调用

EXPLAIN 中的 Extra中 Using index 说明使用了覆盖索引

使用索引扫描来做排序

如果EXPLAIN出来的type为index，则说明MYSQL使用了索引扫描来做排序。
设计索引的时候尽量即满足排序，又用于查找行。
只有当索引列的顺序和ORDER BY 子句的顺序完全一致，并且所有列的排序方向都一样时，MySQL才能够使用索引来对结果排序。如果查询需要关联多张表，则只有当ORDER BY子句引用的字段全部为第一个表时，才能使用索引做排序

有一种情况下ORDER BY子句可以不满足索引的最左前缀要求，那就是前导列为常量的时候。例如

SELECT user_id,staff_id FROM demo.user WHERE date = '2021-03-09' ORDER BY invertory_id,invertory_id
-- KEY index(date,invertory_id,invertory_id)

如果查询在date列上有多个等于的条件(in(x,y))，对于排序来说也是一种范围查询

压缩索引

使用了更少的空间，但是代价是可能变慢。因为每个值的压缩前缀都依赖于前面的值，所有查找时无法在索引块使用二分查找而只能从头开始扫描。可以在CREATE TABLE的时候使用PACK KEYS参数来控制索引压缩的方式

冗杂和重复的索引

在相同的列上按照相同的顺序创建了相同类型的索引。应该避免。
例如对于索引(A,B)，如果已存在索引(A),可以直接扩展索引(A)而不是重新新建一个索引(A,B)。

未使用的索引

可以使用工具帮忙排除(MariaDB)。

索引和锁

索引可以让查询锁定更少的行。InnoDB只有在访问行的时候才会对其加锁，而索引能够减少InnoDB访问的行数。