您的位置: 首页  >  文章  >  高性能MYSQL--索引（三）

高性能MYSQL--索引（三）

分类: 文章 • 2025-02-15 20:54:46

上面几节中我们介绍了BTREE索引和HASH索引。本章节我们主要讲解下索引策略。

高性能的索引策略

这里我们主要来学习如何高效的使用索引，如何设定索引顺序，怎么选择合适的索引，应该在哪些列上建立索引。

1.1 独立的列

查询中的列如果不是独立的列，索引将无法使用。独立的列指的是索引不能是表达式的一部分，也不能是函数的参数

SELECT actor_id FROM saklia.actor WHERE actor_id + 1 = 5;
SELECT ... WHERE TO_DAYS(CURRENT_DATE) - TO_DAYS(date_col) <= 10;

上面的两个sql都是无法使用索引的，虽然我们将索引建立在actor_id和date_col上，但是where条件中actor_id和date_col并不是一个独立的列，要么在表达式中，要么在函数中。

1.2 前缀索引和索引选择性

这种索引是用来解决索引是很长的字符列，这会让索引变得又大又慢。其中一个解决策略是哈希索引，但是这么做还是不够的。通常情况下，我们只索引开始的部分字符，这样可以节约索引空间，提高索引效率。但是这样会降低索引的选择性，简单的说就是使索引部分的重复值增加。

索引的选择性：不重复的索引值（基数）/ 数据表的记录总数的比值，该比值越大，越接近1，效率越好

  • 注意：
    对于BLOB，TEXT或者很长的VARCHAR类型的列，必须使用前缀索引，MYSQL不允许索引这些列的完整长度。

  • 选择前缀的合适长度：前缀的基数应该基于完整列的基数。
    举个例子说明：

SELECT COUNT(*) AS cnt, city FROM sakila.city_demo GROUP BY city ORDER BY cnt DESC LIMIT 10;

结果如下：
高性能MYSQL--索引（三）
注意到，上面的每个值都出现了很多次45-65次。现在我们尝试来定他的前缀索引，注意应该尽量让前缀索引的基数等于原来数据的索引的基数。

（1）前缀取3个字母

SELECT COUNT(*) AS cnt, LEFT(city, 3) as pref FROM saklia.city_demo GROUP BY pref ORDER BY cnt DESC LIMIT 10;

结果：
高性能MYSQL--索引（三）
看到其重复的次数可以发现与之前的偏差较大，所以基数（不重复的次数）也会偏差较大，因此不符合要求，我们需要增加前缀的长度。

（2）前缀取7个字符

SELECT COUNT(*) AS cnt, LEFT(city, 7) AS pref FROM sakila.city_demo GROUP BY pref ORDER BY cnt DESC LIMIT 10;

高性能MYSQL--索引（三）
从上面的结果看到重复的次数在47-70之间，不用前缀的情况下重复实在45-65之间。索引该表的前缀索引选择7比较合适。

查看索引的选择性：
COUNT(DISTINCT 列名)/COUNT(*)
表示的是选取不重复的列/列的总数

eg.
高性能MYSQL--索引（三）
上图分别描述了前缀取3，4，5，6，7时候的索引选择性大小，越接近1表示性能越好

注意：
我们处理观察索引的可选择性之外，还应该考虑下数据的分布情况，分布也不能太不均匀，数据分布不均匀也会影响选择的效率：

如：
高性能MYSQL--索引（三）
如果以4位作为前缀索引，那么可以看到其分布，前缀是SAN 和SANT的数据最多，分布并不均匀，所以当我们查找SAN和SANT开头的数据，效率就会很差。

  • 如何创建前缀索引

ALTER TABLE saklia.city_demo ADD KEY (city(7));

总结

前缀索引是一种能使索引更小，更快的有效办法，但是也有缺陷：MYSQL无法使用前缀索引做ORDER BY和GROUP BY，因为order by后面都跟的是完整的列名，也无法使用前缀索引做覆盖索引.

1.3 多列索引

多列索引一定要注意其创建的顺序。注意一点，索引中不能使用or以及in后面有多个列也会是索引失效
多列索引并不是在多个列上建立独立的索引，通常一个sql只会使用一个索引，但是在5.0版本后出现了索引合并的策略，即EXPLAIN中的type是merge Index，合并了多个列的索引，在一定程度上可以使用多个单列索引来定位指定的行。但是在更早的版本中MYSQL只能够使用其中一个单列索引。

例如：
在表film_actor中的字段film_id和actor_id上各有一个单列索引。查看下面的sql

SELECT film_id, actor_id FROM sakila.film_actor WHERE actor_id = 1 OR film_id = 1;
  • MYSQL5.0之前：
    上面的sql在老的MYSQL版本中会使用全表扫描不会使用任何的索引，OR会使索引失效，除非将其改为
SELECT film_id, actor_id FROM sakila.film_actor WHERE actor_id = 1
UNION ALL 
SELECT film_id, actor_id FROM sakila.film_actor WHERE actor_id = 1 AND actor_id <> 1;
  • MYSQL5.1之后：
    上面的查询可以同时使用这两个索引，因为索引合并。这种情况有三个变种：
    （1）有OR关键字
    （2）有AND关键字
    （3）有OR和AND同时出现
    如：
    高性能MYSQL--索引（三）
    看到上面的EXPLAIN分析，可以看到type用的是index_merge使用了索引合并，key同时使用了PRIMARY和idx_fk_film_id这两个索引。

索引合并有时候是一种优化的结果，但是也说明了一点，目前表上的索引建立的并不好
（1）当服务器多个索引做AND操作的时候，通常需要一个包含所有相关列的多列索引。而不是多个单独的单列索引。
（2）当服务器多个索引做OR操作的时候，效率通常会降低，多列索引也会失效，会消耗大量的CPU和内存资源在算法的缓存，排序合并操作上。
（3）优化器只关心随机页面的读取，并不会关系“查询成本”（比如说CPU的缓存查询结果等）

1.4 选择合适的索引列的顺序

选择合适的索引顺序其实就是关注哪些列需要建立索引，关注索引建立的顺序和索引的可选择性，就是基数（索引中不重复的个数）/ 总数。

对于如何选择索引的列的顺序有一个经验法则：将选择最高的列放到索引的最前列；这个法则可以适用部分场景。但是如果不考虑排序和分组的话，这个经验法则就很好。
所以：当不考虑分组和排序的情况下，应该考虑其选择性高的并且还需要考虑值得分布是否平均，类似于前缀索引。

举例说明：

SELECT * FROM payment WHERE staff_id = 2 AND customer_id = 584;

首先我们需要建立的是多列索引，其中有staff_id和customer_id但是顺序该怎么决定呢？
我们先来查看下这两个列的基数

SELECT SUM(staff_id = 2), SUM(customer_id = 584) FROM payment;

高性能MYSQL--索引（三）
可以看到staff_id是2的时候重复的行数很多，所以基数小，从这里面看应该是选取customer_id作为多列索引的第一个。但是这个判断依赖于具体的值：即staff_id是2，customer_id是584。因此这么判断是不准确的。
我们应该：

SELECT COUNT(DISTINCT staff_id)/COUNT(*) AS staff_id_selectivity,
COUNT(DISTINCT customer_id)/count(*) AS customer_id_selectivity,
COUNT(*)
FROM payment

高性能MYSQL--索引（三）
通过上面可以看到，这样查询的结果不依赖于具体的值，可以看到索引的可选择性customer_id更高。所以我们建立的多列索引应该是(customer_id, staff_id)

注意：
考虑索引的时候一定要考虑索引的可选择性，就是说重复的行数不能太多，否则建立索引没有太大的用处。

比如：

SELECT COUNT（DISTINCT threadID） AS COUNT_VALUE
FROM message
WHERE (groupId = 10317) AND (userId = 1288826) AND (anonymous = 0)
ORDER BY priority DESC, modifinedDate DESC

我们在groupId和userId上面建立多列索引，这样子乍一看没什么问题，我们来EXPLAIN下该语句
高性能MYSQL--索引（三）
我们可以看到索引正常的使用了，但是遍历的行数有1251162行，这是什么情况？？
我们来查看下索引的可选择性：
高性能MYSQL--索引（三）
看到这里我们应该已经找到了问题。重复的行数太多了，也就是说索引基本没有什么用，即使建立了索引，但是索引的可选择性不够。

1.5 聚簇索引

首先我们简单的理解下什么是聚簇索引和非聚簇索引。

聚簇索引：
翻一本书，我们不用目录直接查看，如果要看第九章，先翻到了第五章，所以我们会继续向后翻看，又翻到了第10章，所以向前翻看，直到翻到第九章。
非聚簇索引：
先找目录，第九章在哪一页，我们直接找其对应的页数就行了。

上面的理解可以说是比较通俗易懂的了，是参考别人的理解的。

  • 聚簇索引其实并不是一种索引类型，它应该是一种数据的存储方式， InnoDB的聚簇索引实际上在同一个结构中保存了BTREE索引和数据行。也就是说我们将索引和具体数据直接存放在一起就是聚簇索引

  • 当表有聚簇索引的时候，他的数据行实际上是存放在索引的叶子页中的。因为一张表我们无法将数据存放在两个不同的地方所以一个表只能有一个聚簇索引

  • 不是每一个存储引擎都支持聚簇索引的，InnoDB是支持的。

注意：
InnoDB默认主键就是聚簇索引，但是目前聚簇索引都是默认定义好的，一般不需要我们去重新定义，并且一个表只能有一个聚簇索引。
默认就是主键。
如果没有定义主键：
1. InnoDB会默认选取一个唯一的非空索引代替。
2. 如果没有这样的索引，InnoDB会隐式定义一个主键作为聚簇索引
  • 聚簇索引的优点 ：
  1. 可以把相关的数据存放在一起。
  2. 访问数据更快，聚簇索引将索引和数据保存在同一个B-Tree中，因此从聚簇索引中获取数据比在非聚簇索引中获取数据快。
  3. 使用覆盖索引扫描的查询可以直接使用页节点中的主键值。
  • 聚簇索引的缺点：
  1. 聚簇索引提高了IO密集型应用的性能，但是将数据放到内存中，访问顺序就不是那么重要，因此聚簇索引也就没什么优势。
  2. 插入速度依赖于插入顺序，如果是主键的顺序插入，那么速度很快，如果不是主键的顺序，速度会受到影响。
  3. 更新聚簇索引的代价很高，会强制的将InnoDB的更新的行移动到新的位置。
  4. 基于聚簇索引的表在插入新行，或者主键被更新的时候导致需要移动行的时候，可能面临着“页分裂”的问题，就是将一页分成两页来存储该行的数据，占用更多的磁盘空间。
  5. 聚簇索引可能导致全表扫描变慢。
  6. 二级索引（非聚簇索引）可能比想象的大。因为在二级索引的叶子节点包含了引用行的主键列。
  7. 二级索引（非聚簇索引）需要两次索引查找，因为二级索引存放的是主键，先找到主键，然后依靠主键查找到具体的数据
  • InnoDB和MYISAM的数据分布对比
InnoDB NYISAM
聚簇索引 INNODB支持，叶子页存储了索引和具体的数据 不支持
非聚簇索引 叶子页存放了主键，所以需要再次查找（二级索引需要查找两次） 叶子页存档的是指向行的指针

MYIASM按照数据的插入顺序存储在磁盘上：
高性能MYSQL--索引（三）
InnoDB的数据分布，InnoDB支持聚簇索引：
高性能MYSQL--索引（三）
可以看到，改图不止是保存了索引，还保存了所有的表，聚簇索引就是整个表，所以不像MyISAM需要独立存储行数据

  • InnoDB和MYISAM保存数据和索引的区别
  • 高性能MYSQL--索引（三）
  • 注意：
    • InnoDb的主键索引（用的是聚簇索引）还存储了具体的数据，二级索引（非聚簇索引）默认存的是主键。
    • MYISM的主键索引和二级索引默认存放的都是指向具体行数据的指针

1.6 覆盖索引

如果一个索引包含了（或者说是覆盖）所有需要查询的字段的值，我们就称之为“覆盖索引“

覆盖索引是非常有用的工具，能够极大的提高性能。覆盖索引使得查询只需要扫描索引，而无需回表

  • 好处：
  1. 索引条目通常小于数据行的大小，所以只需要读取索引，那么MYSQL会极大地减少数据访问量。
  2. 索引按照列值顺序存储，对于IO密集型的范围查询会比随机从磁盘上读取每一行数据的IO要少的多。
  3. 可以利用内存来缓存索引，以此提高查询速率。
  4. 覆盖索引对InnoDB的二级索引特别有用，如果InnDB的表可以做到覆盖索引，那么我们就无需二级索引的二次查找，因为只要索引覆盖了需要查询的所有的列，那么就不需要回表查询，直接取值。
注意：
不是所有的列都可以成为覆盖索引，覆盖索引必须要存储索引列的值，而哈希索引，空间索引和全文索引都不存储索引列的值，
所以MYSQL只能使用BTREE来做覆盖索引。

eg. 我们建立了索引(store_id和film_id)

  1. 索引覆盖了要查询的所有字段，使用覆盖索引，EXTRA为Using INDEX
    高性能MYSQL--索引（三）
  2. 索引没有覆盖全部的待查询条件
    此时不会使用覆盖索引，如果索引覆盖了所有的where但是没有覆盖所有的查询涉及的字段，那么也总是会回表查询数据，而不会直接利用索引取数据
    高性能MYSQL--索引（三）
    我们观察上面的sql，发现没有使用覆盖索引，这是有两个原因造成的
    （1）没有任何索引能够覆盖这个查询。
    （2）MYSQL不能再索引中执行LIKE的左占位符操作。

1.7 使用索引扫描来做排序

MYSQL有两种方式可以生成有序的结果，通过排序操作；或者按照索引的顺序排序扫描，如果EXPLAIN出来的type列的值是"index"，则说明了MYSQL使用的是索引来做扫描排序。

只有当索引的列顺序和ORDER BY字句的顺序完全一致，并且所有的列的排序方向(倒序，正序)都一样的时候，MYSQL才能够使用索引来做排序；如果查询需要关联多个表的时候，则只有当ORDER BY子句引用的字段全部为第一个表的时候，才能使用索引做排序。
当索引覆盖所有的查询字段的时候可以做覆盖索引，无需回表
所以我们建立索引的时候最好是可以直接覆盖select查询的字段以及order by排序的字段

注意一点：
  • 如果查询为索引的第一列提供了常量条件，而按顺序使用后面的索引进行排序，则两列组合在一起就是索引的最左前缀

eg.
高性能MYSQL--索引（三）

高性能MYSQL--索引（三）
自己试验了下发现：

  1. 如果没有where，select不是order by后面的列，则不会使用索引进行order
    也就是说只有select后面的字段是order by后面的字段的时候，才可以正常使用索引。
  2. 有where的情况下，只有where满足了索引的使用的时候，才会考虑order by后面的索引是否满足跟索引一模一样的情况

仅供自己学习，如有维权联系我删除

相关推荐