高性能MySQL（五）—创建高性能索引（2）

时间：2022-06-04 00:22:59

创建高性能索引

高性能的索引策略

正确的创建和使用索引是实现高性能查询的基础。

独立的列

指索引列不能是表达式的一部分，也不能是函数的参数，否则MySQL就不会使用索引；如：select actor_id from sakila.actor where actor_id +1 = 5;

前缀索引和索引选择性

索引的选择性是指，不重复的索引值和数据表的记录总数（T）的比值，范围在 1/T 到 1 之间。不重复的索引值也称为基数（cardinality）。因为选择性高的索引可以让Mysql在查找时过滤掉更多的行，所以索引的选择性越高则查询效率越高。唯一索引的基数即为数据的条数，其选择性为1，所以唯一索引的性能最好。

对于TEXT或是VARCHAR类型的列，当这个列中的值长度很大又必须利用其进行查询时，就必须使用这个列的前几位值以作索引，即前缀索引，因为整个列的值当做索引时B+tree会占用非常大的空间，查找也不方便。

这有一种寻找最佳前缀索引的方式，即根据选择性的定义来进行计算其完整列的选择性及其前缀的选择性以便对比。

  1. 假设：有一个表中的某一列，名为session，其值为十六进制的ID

  2. 首先进行完整列的选择性的计算

    mysql> SELECT COUNT(DISTINCT session) / COUNT( * ) FROM table;

    如果是唯一ID，则其值应为1.

  3. 然后计算这个列的前缀长度为x的选择性，

    mysql> SELECT COUNT(DISTINCT LEFT( session , x )) / COUNT( * ) FROM table;
    得到一个小数值，可以改变x的值来计算不同前缀的选择性，最后在多个值中，综合考虑选择性接近性和前缀长度的两个方面，可以选出一个较为合适的前缀索引。

  4. 创建一个前缀长度为x的索引：

    mysql> ALTER TABLE table ADD KEY (session (x) );

总结来讲：

  1. 当需要索引很长的字符列时，会使索引变得大且慢，解决方式是只索引开始的部分字符，这样可以大大节约索引空间，从而提高索引效率，但仍需注意索引选择性的降低；
  2. 索引的选择性是指，不重复的索引值和数据表的记录总数的比值，范围从0-1之间；
  3. 索引的选择性越高则查询效率越高，因为选择性高的索引可以让MySQL在查找时过滤掉更多的行；
  4. 当选择了合适长度的前缀，使得前缀索引的选择性接近于索引整个列的选择性时，就可以使用此前缀索引了；

多列索引

在Mysql执行查询时，如果是使用多列索引，则会先查询符合第一列索引的数据集，然后再在这一部分数据集中查询出符合第二列的数据，以此类推，这样在不用扫描数据的情况下就能选出数据；

而如果一个多列索引拆分成多个单列索引的话，Mysql在执行查询时，只会从中选出一个限制最严格的索引以供使用，其他的索引就浪费了，所以在上述情况中多列索引性能要好

注：在Mysql 5.0之后，Mysql添加了‘索引合并’策略，一定程度上可以使用表上的多个单列索引来定位数据。实际上，Mysql5.0之后有种算法可以查询能够同时使用这两个单列索引进行扫描，并将结果合并。

这种算法的三个变种: or条件的联合(union)， and条件的相交(intersection) 及 组合前两种情况的联合及相交。
高性能MySQL（五）—创建高性能索引（2）

可见在Extra列中，显示为两种索引的相交优化。
虽说如此，这种处理会带来一些负面影响:

  1. 当出现服务器对多个索引做相交操作时（通常有多个AND条件），通常意味着需要建立一个包含了所有相关列的多列索引，而不是多个独立的单列索引。
  2. 当索引需要对多个索引做联合操作时（通常有多个OR条件），通常需要耗费大量的CPU和内存资源在算法的缓存、排序和合并操作上。特别是当其中的有些索引的选择性不高，需要合并扫描返回的大量数据的时候。
  3. 这些优化计算所消耗的成本是不会被优化器计入“查询成本”中。

此外，这种优化有使用限制。也只有在查询这两列的值的情况下才会运用到优化，当查询这两列之外的值时就无法使用优化。
如果在EXPLAIN的时候看到有索引合并，应该检查一下查询和表的结构，看是不是应该建立多列索引。

选择合适的索引列顺序

适用于B-Tree索引，哈希索引和其他类型的索引不会像B-Tree索引一样按顺序存储数据。在一个多列的B-Tree索引中，索引列的顺序意味着索引首先按照最左列进行排序，其次是第二列。索引可以按照升序或者降序进行扫描，以满足ORDER BY, Group by和distinct等子句的查询请求。对于选择索引列的顺序有一个经验法则：将选择性高的列放在索引的最前列。根据场景的不同，这条经验法则并不是完全准确的，在某些场景下，可能需要根据运行频率最高的查询来调整索引列的顺序。

使用字段选择性高的的字段来建立索引 show index from table 查看表上的索引。

Table 表的名称
Non_unique 如果索引不能包括重复词，则为0。如果可以，则为1。
Key_name 索引的名称。
Seq_in_index 索引中的列序列号，从1开始
Column_name 列名称。
Collation 列以什么方式存储在索引中。在MySQL中，有值‘A’（升序）或NULL（无分类）。
Cardinality 索引中唯一值的数目的估计值。通过运行ANALYZE TABLE或myisamchk -a可以更新。基数根据被存储为整数的统计数据来计数，所以即使对于小型表，该值也没有必要是精确的。基数越大，当进行联合时，MySQL使用该索引的机会就越大
Sub_part 如果列只是被部分地编入索引，则为被编入索引的字符的数目。如果整列被编入索引，则为NULL。
Packed 指示关键字如何被压缩。如果没有被压缩，则为NULL。
Null 如果列含有NULL，则含有YES。如果没有，则该列含有NO。
Index_type 用过的索引方法（BTREE, FULLTEXT, HASH, RTREE）。

聚簇索引

聚簇索引并不是一种单独的索引类型，而是一种数据存储方式。InnoDB的聚簇索引实际上在同一个结构中保存了B-Tree索引和数据行（索引的顺序与数据的物理存放位置一致，“聚簇”表示数据行和相应的键值紧凑地存储在一起），因为无法同时把数据行存放在两个不同的地方，所以一个表只能有一个聚簇索引。并不是所有的存储引擎都支持聚簇索引。在Oracle数据库中，索引组织表与“聚簇索引”是一样的意思。InnoDB通关过主键聚集数据。如果没有定义主键，InnoDB会选择一个唯一的非空索引作为替代。如果没有这样的索引，InnoDB会隐式定义一个主键来作为聚簇索引。InnoDB只聚集在同一个页面中的记录，包含相邻键值的页面可能会相距很远。

总结来讲：

  1. 并非一种单独的索引类型，而是一种数据存储方式；
  2. 因为是存储引擎负责实现索引，故并不是所有的存储引擎都支持聚簇索引，但对InnoDB是适用的；
  3. InnoDB的聚簇索引在同一个结构中保存了B-Tree索引和数据行；
  4. 当表有聚簇索引时，它的数据行实际上存放在索引的叶子页中，所谓“聚簇”，即表示数据行和相邻的键值紧凑的存储在一起；
  5. 一个表只能有一个聚簇索引；
  6. InnoDB通过主键聚集数据；
  7. 如果没有定义主键，InnoDB会选择一个唯一的非空索引代替；如果没有这样的索引，则会隐式定义一个主键来作为聚簇索引；
  8. InnoDB只聚集在同一页面中的记录，包含相邻键值的页面可能会相距甚远；

聚簇索引的数据分布如下：

高性能MySQL（五）—创建高性能索引（2）

聚簇索引的优点：

  1. 可以把相关数据保存在一起；
  2. 数据访问更快，因为聚簇索引将索引和数据保存在同一个B-Tree中，因此从聚簇索引中取数据通常比在非聚簇索引中查找要快；
  3. 使用覆盖索引扫描的查询可以直接使用页节点中的主键值；

聚簇索引的缺点：

  1. 聚簇索引最大限度的提高了I/O密集型应用的性能，但如果数据全部都放在内存中，则访问速度就没那么重要了，聚簇索引也就没什么优势了；
  2. 插入速度严重依赖于插入顺序，按照主键的顺序插入是加载数据到InnoDB表中速度最快的方式；
  3. 更新聚簇索引的代价很高，因为会强制InnoDB将每个被更新的行移动到新的位置；
  4. 基于聚簇索引的表在插入新行，或者主键被更新导致需要移动行的时候，可能面临“也分裂”问题；
  5. 聚簇索引可能导致全表扫描变慢，尤其是行比较疏松，或者由于页分裂导致数据存储不连续的时候；
  6. 二级索引(非聚簇索引)可能比想象的要更大；且二级索引访问需要两次索引查找；

聚簇索引和非聚簇索引的区别：

  1. 聚簇索引的顺序就是数据的物理存储顺序，叶节点就是数据节点；
  2. 非聚簇索引的顺序与数据物理排序顺序无关，叶节点仍然是索引节点，只不过有一个指针指向对应的数据块；

聚簇索引和非聚簇索引的对比图如下：

高性能MySQL（五）—创建高性能索引（2）

B+Tree索引

目前大部分数据库系统及文件系统都采用B-Tree或其变种B+Tree作为索引结构。

B+Tree的结构：

高性能MySQL（五）—创建高性能索引（2）

多叉平衡查找树，结构:

高度为h, 度数为t (t>=2)
每个非叶子节点由n-1个key和n个指针组成，其中t<=n<=2t。
每个叶子节点最少包含一个key，最多包含2d-1个key。
一个节点中的key从左到右非递减排列。
所以的叶子节点具有相同的高度。
叶子节点的中，存在指向下一个叶子节点的指针
非叶子节点之存储关键字，数据都存储在叶子节点中

选择B+Tree的原因：

一般来说，索引本身也很大，不可能全部存储在内存中，因此索引往往以索引文件的形式存储的磁盘上。这样的话，索引查找过程中就要产生磁盘I/O消耗，相对于内存存取，I/O存取的消耗要高几个数量级，所以评价一个数据结构作为索引的优劣最重要的指标就是在查找过程中磁盘I/O操作次数。
CPU以页为单位从磁盘获取数据，页大小默认为16k。所以树的高度直接决定了磁盘IO的次数。一般实际应用中，出度d是非常大的数字，因此h非常小。而红黑树等二叉树的h要大的多。

B+Tree索引特点：

  1. 支持全值匹配（所有列）
  2. 支持匹配最左前缀（索引的前若干列）
  3. 支持匹配列前缀 （列值的开头部分）
  4. 支持匹配范围值
  5. 只访问索引的查询，无需访问数据行
  6. 支持排序查找
  7. 多列索引必须从最左列开始，无法跳过某一列。
  8. 如果某个列是范围查询，则右边的列不能使用索引。

参考资料：
高性能MySQL（第3版）
备注：
转载请注明出处：http://blog.csdn.net/wsyw126/article/details/70313726
作者：WSYW126
标签：

相关文章