[关闭]
@boothsun 2018-04-30T05:56:23.000000Z 字数 4364 阅读 971

MySQL建立高性能索引策略

MySQL

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

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

高性能的索引策略

索引的选择性越高则查询效率越高

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

  1. select count(DISTINCT column_name) / COUNT(*) from table_name;

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

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

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

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

另一个常见错误：

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

选择合适索引的前缀长度

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

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

  1. -- 全列选择性
  2. SELECT COUNT(DISTINCT column_name) / COUNT(*) FROM table_name;
  4. -- 测试某一长度前缀的选择性
  5. SELECT COUNT(DISTINCT LEFT(column_name, prefix_length)) / COUNT(*) FROM table_name;

创建前缀索引

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

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

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

多列索引

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

在多个列上建立独立的单列索引大部分情况下并不能提高MySQL的查询性能，因为索引的维护成本也很大，越多的索引字段 在修改数据时会造成索引重建和页分裂等问题。虽然，MySQL5.0及以上版本引入了一种叫“索引合并(index merge)”的策略，一定程度上可以使用表上的多个单列索引来定位指定的行；但是索引合并策略虽然是一种优化的结果，但实际上更多时候说明了表上的索引建得很糟糕：

所以，如果在EXPLAIN中看到有索引合并，应该好好检查一下查询和表的结构，也应该考虑建立一个多列索引而不是多个单例索引。

多列索引需要考虑选择合适的索引列顺序：

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

经验法则：

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

索引选择性计算方式:

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

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

聚簇索引

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

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

聚簇索引的缺点：

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

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

二级索引( 除聚簇索引外的其他全部索引类型 )叶子节点保存的不是指向行的物理位置的指针，而是行的主键值。这里就可以避免页分裂数据移动时，指针变动问题。

在InnoDB表中应该按照主键单调顺序插入行

如上图所示，因为主键的值是顺序的，所以InnoDB把每一条记录都存储在上一条记录的后面。当达到页的最大填充因子时（InnoDB默认的最大填充因子是页大小的15/16，留出部分空间用于以后修改），下一条记录就会写入新的页中。如果数据都按照这种顺序的方式加载，主键页就会近似于被顺序的记录填满，这也是我们所期望的结果。

但是如果是随机不重复的主键值，则InnoDB就无法简单地总是把新行插入到索引的最后，而是需要为新的行寻找合适的位置 —— 通常是已有数据的中间位置 —— 并且分配空间。这会增加很多的额外工作，并导致数据分布不够优化。下面就是非单调主键的缺点：

覆盖索引

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

覆盖索引的好处：

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

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

前缀压缩索引

MyISAM使用前缀压缩技术来减少索引的大小，从而让更多的索引可以放入内存中。默认只压缩字符串，但可以通过参数设置也可以对整数做压缩。

MyISAM压缩每个索引块的方法是，先完成保存索引块中的第一个值，然后将其他值和第一个值进行比较得到相同前缀的字节数和剩余的不同后缀部分，把这不同部分存储起来即可。例如，索引块中的第一个值是“perform”，第二个值是“performance”，那么第二个值的前缀压缩后存储的是类似于“7,ance”这样的形式。MyISAM对行指针也采用类似的前缀压缩方式。

压缩索引块可以使用更少的空间，代价是某些操作可能更慢。因为每个值的压缩前缀都依赖于前面的值。所以MyISAM查找时无法在索引块中使用二分查找而只能从头开始扫描。正序的扫描速度还不错，但是如果是倒序扫描 —— 例如ORDER BY DESC —— 就不是很好了。所有在块中查找某一行的操作平均都需要扫描半个索引块。

冗余和重复索引

什么是重复索引？
重复索引是指在相同的列上按照相同的顺序创建相同类型的索引。

常见重复索引的场景：
给主键加普通索引或者唯一索引。

什么是冗余索引：
多个相同类型的索引有交集。例如：如果创建了索引(A,B)，再创建索引(A)，则后者就是冗余索引，因为前者已经完全包含后者。

常见冗余索引的场景：
冗余索引通常发生在为表添加新索引的时候。例如，有人可能会增加一个新的索引(A，B) 而不是扩展已有的索引（A）。还有一种情况是将一个索引扩展为（A，ID），其中ID是主键，对于InnoDB来说主键列已经包含在二级索引中了，索引这也是冗余的。

冗余索引和重复带来的问题：
MySQL在数据INSERT、UPDATE、DELETE等操作时需要维护索引，所以索引会降低MySQL数据操作速度。

未使用索引

删除未使用索引

添加新批注
在作者公开此批注前，只有你和作者可见。
回复批注