MySQL索引

作者: Snipers_onk | 来源:发表于2019-12-11 18:05 被阅读0次

欢迎访问我的个人博客：MySQL索引
索引是存储引擎用于快速查找记录的一种数据结构，索引也是查询性能优化最有效的手段。

索引基础

索引可以包含一个或多个列的值，如果索引包含多个列，那么列的顺序也十分重要，因为MySQL只能高效的使用索引的最左前缀列，创建一个包含两个列的索引和两个包含一列的索引是大不同的。

索引的优点

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

索引的类型

B-Tree索引

一般我们说的索引，没有特别指定类型，都是说的B-Tree索引，它使用B-Tree数据结构来存储数据。大多数MySQL引擎都支持这种索引（Archive除外）。B类树详解及B+树在MYSQL索引的应用

存储引擎以不同的方式使用B-Tree索引，性能也各有不同。MyISAM索引使用前缀压缩技术使得索引更小，InnoDB按照原数据格式进行存储；MyISAM索引通过数据的物理位置引用被索引的行，而InnoDB则根据主键引用被索引的行。

B-Tree通常意味着所有的值按照顺序存储，每一个叶子节点到根节点的距离相同。

B-Tree索引能够加快访问数据的速度，因为存储引擎不再需要全表扫描，而是从索引的根节点开始进行搜索。通过比较节点页的值和要查找的值，找到合适的指针进入下层节点，最终找到对应的值或者记录不存在。

B-Tree对索引是顺序存储的，所以适合查找范围数据。

哈希索引

哈希索引基于哈希表实现，只有精确匹配索引所有列才有效。对于每一行数据，存储引擎都会对所有索引计算出一个哈希码，哈希索引将哈希码存储在索引中，哈希码对应的是每个数据行的指针。

在Mysql中，只要Memory引擎显示的支持哈希索引。

空间数据索引（R-Tree）

不常用，不写了。

全文索引

全文索引查找的是文本中的关键字，而不是直接比较索引中的值，适用于MATCH AGAINST操作，而不是普通的WHERE条件查询。

ALTER TABLE `user` ADD FULL INDEX(`column`)

常用的索引策略

独立的列

如果查询的列不是独立的，则MySQL不会使用索引。虽然很容易判断i=4，但是MySQL无法自动解析这个方程式。

select id from score where id + 1 = 5;

前缀索引和索引选择性

有时候需要索引很长的字符列，这会让索引变的大且慢，可以使用前面提到过的哈希索引，但也可以索引字符串的部分开始字节，大大节省索引空间，提高索引效率。

但这样做也会降低索引的选择性（不重复的索引值和记录总数的比值），选择性越高的索引效率越高，反之越低。唯一索引的选择性是1，所以是性能最好的索引。

select count(DINSTINCT LEFT(CONTENT,3))/COUNT(*) AS SEL3,
count(DINSTINCT LEFT(CONTENT,4))/COUNT(*) AS SEL4,
count(DINSTINCT LEFT(CONTENT,5))/COUNT(*) AS SEL5,
count(DINSTINCT LEFT(CONTENT,6))/COUNT(*) AS SEL6,
count(DINSTINCT LEFT(CONTENT,7))/COUNT(*) AS SEL7
FROM TABLE_A;

//实例结果：0.13 0.19 0.21 0.26 0.26

通过比较结果，当选择性提升幅度很小时，就可以以这个长度作为前缀索引长度。

多列索引

ALTER TABLE `user` ADD INDEX indexName(`column1`,`column2`,`column3`)

一个很常见的索引错误，就是为每个列建立独立的索引或者按照错误的顺序创建多列索引。

在多个列建立独立的单独索引大部分情况下并不能提高查询性能。MySQL5.0引入了索引合并策略，一定程度上可以使用表上的多个单列索引来定位指定的行，索引合并策略有时候是一种优化的结果，但实际上证明了表上的索引建立的很差。

当服务器对多个索引做相交操作，如 id1=? AND id2=?，需要的是一个包含相关列的多列索引，而不是多个单列索引
当服务器对多个索引做联合操作，如 id1=? OR id2=?，如果使用多个独立的单列索引，通常需要耗费大量CPU和内存资源在算法的缓存，排序和合并操作上。如果其中有些单列索引选择性不高，消耗会更大。
优化器不会把这些计算到查询成本（cost）中，因为优化器只关心随机页面存取。这样使得从查询成本被‘低估’，导致该执行计划还不如直接走全表扫描。

如果在explain中看到由合并索引，最好检查一下查询和表的结构。

合适的索引顺序

合适的索引顺序依赖于使用该索引的查询，并且同时满足排序和分组的需要（只适用于按顺序存储的B-Tree索引）。在一个多列索引中，索引首先按照最左列进行排序，其次是第二列，等等。所以索引可以按照升序或者降序进行扫描，以满足order by、group by、dinstinct等子句的查询需求。

当不需要考虑排序和分组时，将选择性最高的列放在前面，这时候索引只是优化where条件的查找；但性能不只依赖于所有索引的选择性，也和查询条件的具体值有关，也就是和值的分布性有关。

聚簇索引

聚簇索引并不是一种单独的索引类型，而是一种数据存储方式。具体的细节依赖于其实现方式，InnoDB的聚簇索引实际上在同一个结构中保存了B-Tree索引和数据行。

当表有聚簇索引时，它的数据行实际上存放在索引的叶子页中（不懂的童鞋去学习一下B树），“聚簇”表示数据行和相邻的键值紧凑的存储在一起。一个表中数据行无法存放在两个地方，索引一个表中只存在一个聚簇索引。

InnoDB通过主键聚集数据。如果没有定义主键，InnoDB会选择一个唯一的非空索引代替，如果还没有，会隐式的定义一个主键作为聚簇索引。

优点：

可以把数据保存在一起，减少磁盘IO
数据访问更快

缺点：

聚簇索引最大限度的提升了I/O密集型应用的性能，但如果数据全部放在内存中，那么访问的顺序也没那么重要了，优势也就不存在了。
插入速度严重依赖于插入顺序，按照主键的顺序插入时速度最快的方式，如果不是按照主键顺序，在加载完后最好使用OPTIMIZE TABLE重新组织一下表
更新聚簇索引代价很高，因为会强制InnoDB将被更新的行移动到新的位置
聚簇索引在插入新的行时，或者主键被更新移动行的适合，会可能导致页分裂，导致占用更多空间
聚簇索引可能导致全表扫描变慢，尤其时行数据比较稀疏时，数据存储不连续的时候
...

覆盖索引

通常开发人员会根据查询的WHERE条件来创建合适的索引，这只是索引优化的一个方面。设计优秀的索引应该考虑到整个查询，而不单单是WHERE条件部门。如果一个索引覆盖所有需要查询的字段的值，就称为“覆盖索引”。

使用覆盖索引的好处是只需要扫描索引而无需回表，好处非常多：

索引条目通常远小于数据行大小，如果只需要读取索引，MySQL会大大减少数据的访问量。对于缓存的负载来说，无需花费大量时间再数据拷贝上。对于I/O密集的操作来说，索引比数据行更小，更容易全部放入内存中。
索引是按照列值顺序存储的，使用范围查询会比随机从磁盘读取的I/O要少得多。
由于InnoDB的聚簇索引，覆盖索引对InnoDB特别有用。InnoDB的二级索引在叶子节点保存了行的主键值，所以如果二级主键能够覆盖查询，则可以避免对主键索引的二次查询。

聚簇索引的叶子节点存放的是主键值和数据行，支持覆盖索引；二级索引的叶子节点存放的是主键值或指向数据行的指针。

InnoDB中的二级索引存放的是主键值，如果需要查询对应的数据行，需要回表查询，即在聚簇索引中进一步查找对应的数据行。二级索引也被成为辅助索引。

MyISAM中无论是主键索引还是二级索引，索引的叶子节点存放的都是指向数据行的指针，保证可以通过索引进而查找到对应的数据行，只需要对索引进行一遍查找。这样会存在页分裂问题。

不是所有索引都可以成为覆盖索引，因为需要存储索引列的值，所以哈希索引，全文索引等都不适用，只能使用B-Tree索引。当发起一个覆盖查询时，EXPLAIN的EXTRA列可以看到USING INDEX。

覆盖索引有一些情况会导致无法覆盖索引，MySQL优化器会在执行查询前判断是否有一个索引能进行覆盖，如果索引覆盖了WHERE条件中的字段，但没有覆盖查询中字段，MySQL会回表获取数据行。

例如我在user100w_optimizition表中添加了100W条数据，然后执行以下操作

-- 添加多列索引
ALTER TABLE `user100w_optimizition` ADD INDEX threeIndex (`first_name`,`last_name`,`id`);

-- 查询
SELECT * FROM user100w_optimizition
where first_name='高' and last_name like '%三%'

在MySQL5.6中解释如下：

1576030633885.png

Using index condition：在5.6版本后加入的新特性（Index Condition Pushdown）;Using index condition 会先条件过滤索引，过滤完索引后找到所有符合索引条件的数据行，随后用 WHERE 子句中的其他条件去过滤这些数据行；

如果在MySQL5.5及以下版本中，解释如下:

1576031392116.png

MySQL会回表查询数据。可以使用延迟关联的方式进行改善，第一部会使用覆盖索引。

select * from user100w_optimizition
join (
 select id from user100w_optimizition
 where first_name='高' and last_name like '%三%'
)AS t ON t.id = user100w_optimizition.id

1576031776260.png

这样优化的效果并不是绝对的，主要取决于结果集数量的大小。如果第一个条件first_name就已经可以过滤出很少的数据，那么对整个查询效率反而会降低；如果第一步（id=2）查询的结果集很大，那么大部分时间都用来读取和发送数据了。

5.6版本对于5.5及之前还是有很大的优化，所以尽量使用5.6及以上版本吧。

冗余和重复索引

MySQL允许在相同列上创建多个索引，但是这些索引是需要单独维护的，优化器在优化查询的时候会逐个索引考虑，所以会影响性能。

重复索引

在建立表的时候，如果在主键上又添加上了唯一索引和普通索引，其实就是创建了3个重复的索引，一般情况下没必要这么做，除非在一列上加上不同类型的索引。

冗余索引

如果创建了索引(A,B)，再创建索引(A)就是冗余索引，因为后者是前者的前缀索引；但如果创建索引(B,A)，或者(B)，就不是冗余索引，其他类型的索引也不是冗余索引。

冗余索引通常发生在为表添加新索引的时候，比如存在索引(A)，再添加(A,B)，或者添加(A,Id)。对于InnoDB来说，主键Id已经存在二级索引中。所以尽量去扩展原有的索引而不是创建新索引，除非扩展原有索引会导致变得很大，影响其他使用该索引的查询性能。

除了上述两个索引外，还有一些服务器永远用不到的索引，这些索引是需要删除的。

索引和锁

索引可以让查询锁锁定更少的行，只需要锁定那些你需要的行。虽然InnoDB行锁的性能很高，内存使用也很小，但是仍然会带来一些开销，另外一方面，减少锁定的行会锁竞争和并发性。

优化分页

使用文件排序对小数据量时很快的，但如果一个查询匹配的结果由上百万行，就需要做出一些优化了。

对于那些选择性非常低的列，可以增加一些特殊的索引来做排序。

select id,first_name,last_name,score
    from user100w_optimizition where sex='1' order by score limit 100000,10;

这个查询同时使用了order by和limit，如果没有索引会很慢。即使有索引，如果需要翻页，那么翻页靠后时也可能很慢，查询第10W条开始的数据，无论如何创建索引，这种查询都是一个很严重的问题，随着偏移量的增加，MySQL需要花费大量时间扫描需要丢弃的数据。反范式化，预先计算和缓存可能时解决这类问题的仅有策略。更好的办法是限制用户能够翻页的数量，这种方式对用户体验影响不大，因为很少会有用户翻到10000页。

-- 建立索引
ALTER TABLE `user100w_optimizition` ADD INDEX fourIndex (`sex`,`score`,`id`);
-- 执行查询
select id,first_name,last_name,score
from user100w_optimizition where sex='1' order by score limit 100000,10;
/*
时间: 0.234s
*/</pre>

1576043472812.png

优化这类索引的另一个比较好的方法是使用延迟关联，通过使用覆盖索引查询返回需要的主键，再根据这些主键关联原表获得所需要的行。这减少了MySQL扫描哪些需要丢弃的行数。

 select t.id,t.first_name,t.last_name,t.score from user100w_optimizition t INNER JOIN(
 select id from user100w_optimizition
 where sex='1' order by score desc limit 100000,10
 ) as x on x.id = t.id
 /*
 时间: 0.125s
 */

1576043271871.png