高性能的索引

最新推荐文章于 2022-10-20 13:00:35 发布

githubshareing

最新推荐文章于 2022-10-20 13:00:35 发布

阅读量449

点赞数

分类专栏： MySQL MySQL高性能

本文链接：https://blog.csdn.net/qq_18377515/article/details/81023093

版权

MySQL 同时被 2 个专栏收录

35 篇文章 0 订阅

订阅专栏

MySQL高性能

33 篇文章 3 订阅

订阅专栏

总述
独立的列
前缀索引和索引选择性
多列索引
选择合适的索引列顺序
聚簇索引
覆盖索引
使用索引扫描来做排序
压缩（前缀压缩）索引
冗余和重复索引
索引和锁
支持多种过滤条件
避免多个范围条件
优化排序

总述

正确地创建和使用索引是实现高性能查询的基础。高效地选择和使用索引有很多种方式，其中有些事针对特殊案例的优化方法，有些则是针对特定行为的优化。使用哪个索引，以及如何评估选择不同的性能影响的技巧，则需要持续不断地学习。

独立的列

索引不能是表达式的一部分，也不能是函数的参数。如：select actor_id from salcila.actor where actor_id +1=5; 在这里mysql不能解析这个方程式。，我们应该养成简化where条件的习惯，始终将索引列单独放在比较符号的一侧。

前缀索引和索引选择性

有时候需要索引很长的字符列，这会让索引变大且慢。这种情况呢，我们通常可以索引开始的部分字符，这样可以大大节约索引空间，从而提高索引效率。但这样也会降低索引的选择性。索引选择性是指，不重复的索引值和数据表的记录总数的比值。当然索引的选择性越高则查询效率越高，因为选择性高的索引可以让mysql在查找时过滤更多的行。唯一索引的选择性是1，这是最好的索引选择性，性能也是最好的。

多列索引

在多列上建立独立的单列索引大部分情况下并不能提高mysql的查询性能。

选择合适的索引列顺序

在一个多列B-Tree索引中，索引列的顺序意味着索引首先按照最左列进行排序，尤其是第二列，等等。所以，索引可以按照升序或者降序进行扫描，以满足精确符合列顺序的order by 、Group by 和 distinct 等子句的查询需求。
当不需要考虑排序和分组时，将选择性最高的列放在前面通常是很好的。这时候索引作用只是用于优化where条件的查询。在这种情况下，这样设计的索引确实能够最快地过滤出需要的行，对于在where子句中使用了索引部分前缀列的查询来说也更高。然而，性能不只是依赖于所有索引列的选择性，也和查询条件的具体值有关，也就是和值得分布有关。
如：select * from payment where staff_id=2 and customer_id=584
是应该创建一个（staff_id,customer_id）索引还是应该颠倒一下顺序？可以跑一些查询来确定在这个表中值得分布情况，并确定哪个列的选择性更高。先用下面的查询预测一下，看看各个where条件的分支对应的数据基数有多大：
select sum(staff_id=2),sum(customer_id=584) from payment；
sum(staff_id):7992
sum(customer_id=584)：16
从这里看应该将索引列customer_id放到前面，因为对应条件值得customer_id数量更小。这样做有一个地方需要注意，查询的结果非常依赖于选定的具体值。如果按上述办法优化，可能对其他一些条件值得查询不公平，服务器的整体性能可能变得更糟，或者其他某些查询的运行变得不如预期。

聚簇索引

聚簇索引并不是一种单独的索引类型，而是一种数据存储方式。当表有聚簇索引时，它的数据行实际上存放在索引的叶子页中。因为无法同时把数据存放在两个不同地方，所以一个表只能有一个聚簇索引。
聚集的数据有一些重要的优点：

可以把相关数据保存在一起。例如实现电子邮箱时，可以根据用户id来聚集数据。这样只需要从磁盘读取少数的数据页就能获取某个用户的全部邮件。如果没有使用聚簇索引，则每封邮件都可能导致一次磁盘I/O。
数据访问更快。聚簇索引将索引和数据保存在同一个B-Tree中，因此从聚簇索引中获取数据通常比在非聚簇索引中查找要快。
使用覆盖索引扫描的查询可以直接使用页节点中的主键值。
缺点：
聚簇数据最大限度地提高I/O密集型应用的性能，但如果数据全部都放在内存中，则访问的顺序就没那么重要了，聚簇索引也就没什么优势了。
插入速度严重依赖插入顺序。
更新聚簇索引列的代价很高，因为会强制将每个被更新的行移动到新的位置。
基于聚簇索引的表插入新行，或者主键被更新导致需要移动行的时候，可能面临“页分裂”的问题。当行的主键值要求必须将这一行插入到某个已满的页中时，存储引擎会将该页分裂成两个页面容纳该行，这就是一次页分裂操作。页分裂会导致表占用更多的磁盘空间。
聚簇索引可能导致全表扫描变慢，尤其是行比较稀疏，或者由于页分裂导致数据存储不连续的时候。
二级索引（非聚簇索引）可能比想象的要更大，因为在二级索引的叶子节点包含了引用行的主键列。
二级索引访问需要两次索引查找，而不是一次。

覆盖索引

如果一个索引包含或者说覆盖所有需要查询的字段的值，我们就称之为“覆盖索引”
覆盖索引是非常有用的工具，能够极大地提高性能。考虑一下如果查询只需要扫描索引而无须回表，会带来多少好处：
- 索引条目通常远小于数据行大小，所以会极大的减少数据访问量。对缓存的负载非常重要。覆盖索引对I/O密集型的应用也有帮助，因为索引比数据更小，更容易全部放入内存中。
- 因为索引是按照列值顺序存储的（至少在单页内是如此），所以对于I/O密集型的范围查询会比随机从磁盘读取每一行数据的I/O要少的多。等等

使用索引扫描来做排序

如果explain 出来的type列的值为“index”，则说明mysql使用了索引扫描做排序。只有当索引的列顺序和order by子句的顺序完全一致，并且所有列的排序方向都一样时，mysql才能够使用索引来对结果做排序。如果查询需要关联多张表，则只有当order by子句引用的字段全部为第一个表时，才能使用索引做排序。order by子句和查找哦型查询限制是一样的，需要满足索引的最左前缀的要求。否则mysql都需要执行排序操作，而无法利用索引排序。有一种情况下order by子句可以不满足索引的最左前缀的要求，就是前导列为常量的时候。如果where子句或者join子句中对这些列指定了常亮，就可以“弥补”索引的不足。