建立高性能MySQL索引策略

时间：2022-05-16 20:08:38

索引永远是最好的查询解决方案嘛？

索引并不总是最好的工具。总的来说，只有当索引帮助存储引擎快速查找到记录带来的好处大于其带来的额外工作(比如插入操作后索引的维护)时，索引才是高效的。

  • 对于非常小的表：大部分情况下简单的全表扫描更高效。
  • 中到大型表：索引非常高效。
  • 特大型表：建立和使用索引的代价非常高，可以使用分库分表或分区技术代替。

高性能的索引策略

索引的选择性越高则查询效率越高，因为选择性高的索引可以让MySQL在查找时过滤掉更多的行。唯一索引的选择性是1，这是最好的索引选择性，性能也是最好的。索引的选择性是指：不重复的索引值和数据表的记录总数(T)的比值，范围从1/T到1之间。

索引列不能是表达式一部分

如果查询中的列不是独立的，则MySQL就不会使用索引。“独立”是指索引列不能是表达式的一部分，也不能是函数的参数。

例如，下面这个查询就无法使用actor_id列的索引。

mysql> SELECT actor_id FROM actor WHERE actor_id + 1 = 5

另一个常见错误：

SELECT ... WHERE TO_DAYS(CURRENT_DATE) - TO_DAYS(date_col) <= 10 ;

前缀索引和索引选择性

对于BLOB、TEXT或者很长的VARCHAR类型的列，如果索引很长的字符列，会让索引变得大且慢。这时候，通常我们就可以只索引开始的部分字符，但是这样就会降低索引的选择性。所以，我们就要在索引选择性和空间消耗之间保持平衡。

如何判定前缀索引的长度？
当前缀的选择性越接近全列选择性的时候，索引效果越好。

-- 全列选择性
SELECT COUNT(DISTINCT column_name) / COUNT(*) FROM table_name;

-- 测试某一长度前缀的选择性
SELECT COUNT(DISTINCT LEFT(column_name, prefix_length)) / COUNT(*) FROM table_name;

创建前缀索引

alter table table_name add index index_name (field_name(length))

案例：通过实例认识MySQL中前缀索引的用法

个人看法：如果需要被索引的列非常长，且对该列有实时性较高的查询需求，还不如放到Lucene或者solr等搜索引擎中。

多列索引

“把WHERE条件里面的列都建上索引”是非常错误的。

在多个列上建立独立的单列索引大部分情况下并不能提高MySQL的查询性能。MySQL5.0和更新版本引入了一种叫“索引合并(index merge)”的策略，一定程度上可以使用表上的多个单列索引来定位指定的行。

但是索引合并策略有时候是一种优化的结果，但实际上更多时候说明了表上的索引建得很糟糕：

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

如果在EXPLAIN中看到有索引合并，应该好好检查一下查询和表的结构。

总之，如果查询中出现多个字段相交或联合操作，应该建立一个多列索引而不是多个单例索引。

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

在确定索引列顺序的时候既要考虑使用该索引的查询性能，同时也要考虑如何更好地满足排序和分组的需要。在一个多列B-Tree索引中，索引列的顺序意味着索引首先按照最左列进行排序，其次是第二列，等等。所以，索引可以按照升序或者降序进行扫描，以满足符合顺序的ORDER BY、GROUP BY 和DISTINCT等子句的查询需求。

经验法则：

  1. 不考虑排序和分组的情况下，将选择性最高的列放在前面通常是非常好的策略。因为此时索引的作用只是用于优化WHERE条件的查找。
  2. 要考虑WHERE子句中的排序、分组和范围条件等其他因素。
  3. 查询性能也和值的分布有关，所以要兼顾极端的值分布问题。

索引选择性计算方式:

mysql> SELECT COUNT(DISTINCT field1) /COUNT(*) as field1_selectivity , 
     > COUNT(DISTINCT field2) / COUNT(*) as field2_selectivity FROM table_name

查询结果越大，则不重复的值就越多，则选择性就越高。

聚簇索引

聚簇索引不是一种单独的索引类型，而是一种数据存储方式。当表中有聚簇索引时，它的数据行实际上存放在索引的叶子页上。术语“聚簇”表示数据行和相邻的键值紧凑地存储在一起。因为无法同时把数据行存放在两个不同的地方，所以一个表只能有一个聚簇索引。

建立高性能MySQL索引策略

从上面的图展示了聚簇索引的大致结构。从图中，我们可以知道叶子页包含了行的全部数据，但是节点页只包含了索引列。在InnoDB中默认是通过主键聚簇索引，如果没有定义主键，InnoDB会选择一个唯一的非空索引代替。如果没有这样的索引，InnoDB会隐式定义一个主键来作为聚簇索引。InnoDB只聚集在同一个页面中的记录。包含相邻键值的页面可能会相距很远，因为可能不在同一个页中。

页分裂问题：
基于聚簇索引的表在插入新行，或者主键被更新导致需要移动的时候，可能面临“页分裂”的问题。当行的主键值要求必须将这一行插入到某个已满的页中时，存储引擎会将该页分裂成两个页面来容纳该行，这就是一次页分裂操作。页分裂会导致表占用更多的磁盘空间。聚簇索引可能导致全表扫描变慢，尤其是行比较稀疏，或者由于页分裂导致数据存储不连续的时候。

二级索引访问需要两次索引查找问题：

二级索引( 除聚簇索引外的其他全部索引类型 )叶子节点保存的不是指向行的物理位置的指针，而是行的主键值。

关于聚簇索引在MyISAM和InnoDB中存储方式不同，详见我的另外博文。

覆盖索引

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

覆盖索引的好处：

  • 单条索引条目通常远小于单个数据行大小，而覆盖索引只需要读取索引，就可以完成查询操作，极大地减少数据访问量；索引数据很小，更易全部放入内存中。
  • 索引是按照列值顺序存储的，相对于随机访问，只需要更少的I/O。
  • 一些存储引擎如MyISAM在内存中只缓存索引，数据则依赖于操作系统来缓存，因此要访问数据需要一次系统调用。这可能会导致严重的性能问题，但是对于覆盖索引，数据全部在内存中，可以避免
  • 由于InnoDB的聚簇索引，覆盖索引对InnoDB表特别有用。InnoDB的二级索引在叶子节点中保存了行的主键值，所以如果二级主键能够覆盖查询，可以避免对主键索引的二次查询。

不是所有类型的索引都可以成为覆盖索引。覆盖索引必须要存储索引列的值，而哈希索引、空间索引和全文索引等都不存储索引列的值，所有MySQL只能使用B-Tree索引做覆盖索引。

当发起一个被索引覆盖的查询时，在EXPLAIN的Extra列可以看到“Using Index”的信息。在大多数存储引擎中，覆盖索引只能覆盖那些只访问索引中部分列的查询。但是，InnoDB不限于此，InnoDB的二级索引在叶子节点中存储了primary key的值。因此InnoDB的二级索引可以有效地利用这些“额外”的主键列来覆盖查询。白话点说，就是主键也可以算作覆盖索引中的一部分。

标签：

相关文章