高性能MySQL(3)——创建高性能索引

栗筝i

发布于 2022-12-01 21:07:59

1.3K0

发布于 2022-12-01 21:07:59

文章被收录于专栏：迁移内容迁移内容

索引对于良好的性能非常关键。尤其是当表中的数据量越来越大时，索引对性能的影响愈发重要。

一、索引的类型

在MySQL中，索引是在存储引擎层而不是服务器层实现的。所以没用统一的索引标准，不同存储引擎的索引工作方式并不相同。

1.1、B-Tree

B-Tree索引即使用B-Tree数据结构来存储数据。B-Tree通常意味着所有值都是按顺序存储的，并且每个叶子页到根的距离相同。存储引擎已不同的方式来使用B-Tree索引，性能也各不相同。

可以使用B-Tree索引的查询类型——全键值、键值范围和键前缀查找。其中键前缀查找只适用于根据最左前缀查找。

1.2、哈希索引

哈希索引基于哈希表实现，只有精确匹配索引的所有列的查询才有效。在MySQL中，只有Memory引擎显示支持哈希索引，这也是Memory引擎的默认索引类型。

对于每一行数据，存储引擎都会对所有的索引列计算一个哈希码，如果多个列的哈希码相同，索引会以链表的方式存放多个记录指针到同一个哈希条目中。哈希索引将所有的哈希码存储在索引中，同时在哈希表中保存指向每个数据行的指针。

1.3、全文索引

全文索引是一种特殊类型的索引，它查找的是文本中的关键词，而不是直接比较索引中的值。全文搜索和其他几类索引的匹配方式完全不一样。它有许多需要注意的细节，如停用词、词干和复数、布尔搜索等。全文索引更类似于搜索引擎做的事情，而不是简单的WHERE条件匹配。

在相同的列上同时创建全文索引和基于值的B-Tree索引不会有冲突，全文索引适用于 MATCH AGAINST操作，而不是普通的WHERE条件操作。

1.4、其他索引类别

还有很多第三方的存储引擎使用不同类型的数据结构来存储索引。例如TokuDB使用分形树索引(fractal tree index),这是一类较新开发的数据结构，既有B-Tree的很多优点，也避免了 B.Tree的一些缺点。如果通读完本章，可以看到很多关于InnoDB的主题，包括聚簇索引、覆盖索引等。多数情况下，针对InnoDB的讨论也都适用于TokuDB。

二、索引的优点

索引可以让服务器快速地定位到表的指定位置。但是这并不是索引的唯一作用，到目前为止可以看到，根据创建索引的数据结构不同，索引也有一些其他的附加作用。总结下来索引的三大优点：

索引大大减少了服务器需要扫描的数据量；
索引可以帮助服务器避免排序和临时表；
索引可以将随机I/O变为顺序I/O。

索引是最好的解决方案吗？索引并不总是最好的工具。总的来说，只有当索引帮助存储引擎快速查找到记录带来的好处大于其带来的额外工作时，索引才是有效的。对于非常小的表，大部分情况下简单的全表扫描更高效。对于中到大型的表，索引就非常有效。但对于特大型的表，建立和使用索引的代价将随之增长。这种情况下，则需要一种技术可以直接区分出查询需要的一组数据，而不是一条记录一条记录地匹配。

三、高性能的索引策略

3.1、独立的列

索引列不能是表达式的一部分，也不能是函数的参数。

例如：SELECT actor_id FROM actor WHERE actor_id + 1 = 5; 或者：SELECT actor_id FROM actor WHERE f(actor_id) = 5;

3.2、前缀索引和索引选择性

有时候需要索引很长的字符列，这会让索引变得很大且很慢。此时可以有两个策略，一个是自定义哈希索引，另一个就是前缀索引。

前缀索引能大大节约索引空间，从而提高索引效率，但这样也会降低索引的选择性（索引选择性——不重复的索引值和数据表记录总数的比值）；
索引前缀长度的选择——计算法。例如：LELECT COUNT(DISTINCT city)/COUNT() AS sel1, COUNT(DISTINCT LEFT(city, 3))/COUNT() AS sel2, …; 如果前缀的选择性接近sel1就可以使用了。有时候只看平均选择型也不靠谱，还需要做进一步判断。
缺点：MySQL无法使用前缀索引做ORDER BY和GROUP BY，也无法使用前缀索引做覆盖扫描；
有时候也可以使用前缀索引——可将对应列的字符串反序存储，并创建前缀索引。

3.3、多列索引

为多列创建合适的索引

多列索引。例如：key(col1, col2, col3)；
MySQL5.0之后的版本引入了“索引合并”的策略，一定程度上可以使用表上的多个单列索引来定位表中的行；
索引合并策略有时候是一种优化后的结果，但实际上更说明表上的索引建得很糟糕。
- 当出现服务器对多个索引做相交操作时（多个AND），通常意味着需要一个包含相关列的多列索引，而不是多个独立的单列索引;
- 当服务器需要对多个索引做联合操作时（多个OR），通常需要耗费大量的CPU和内存在算法的缓存、排序和合并上。

3.4、选择合适的索引顺序

正确的索引顺序依赖于使用该索引的查询，并且同时需要考虑如何更好的满足排序和分组的需要；
索引可以按照升序或者降序进行扫描，以满足精确符合列顺序的ORDER BY 、GROUP BY和DISTINCT等子句的查询需求；
索引列顺序的选择——在不考虑分组和排序的情况下，将选择性最高的列放到索引最前面（经验法则）；
避免随机I/O和排序；
对于某些特殊用户和分组，避免其使用普通的索引查询。

3.5、聚簇索引

聚簇索引就是按照每张表的主键构造一颗B+树，同时叶子节点中存放的就是整张表的行记录数据，也将聚集索引的叶子节点称为数据页。这个特性决定了索引组织表中数据也是索引的一部分，每张表只能拥有一个聚簇索引。

Innodb通过主键聚集数据，如果没有定义主键，innodb会选择非空的唯一索引代替。如果没有这样的索引，innodb会隐式的定义一个主键来作为聚簇索引。

聚簇索引的优点：

数据访问更快，因为聚簇索引将索引和数据保存在同一个B+树中，因此从聚簇索引中获取数据比非聚簇索引更快
聚簇索引对于主键的排序查找和范围查找速度非常快

聚簇索引的缺点：

插入速度严重依赖于插入顺序，按照主键的顺序插入是最快的方式，否则将会出现页分裂，严重影响性能。因此，对于InnoDB表，我们一般都会定义一个自增的ID列为主键
更新主键的代价很高，因为将会导致被更新的行移动。因此，对于InnoDB表，我们一般定义主键为不可更新。
二级索引访问需要两次索引查找，第一次找到主键值，第二次根据主键值找到行数据。

3.6、覆盖索引

通常开发人员会根据查询的where条件来创建合适的索引，但是优秀的索引设计应该考虑到整个查询。其实mysql可以使用索引来直接获取列的数据。如果索引的叶子节点包含了要查询的数据，那么就不用回表查询了，也就是说这种索引包含（亦称覆盖）所有需要查询的字段的值，我们称这种索引为覆盖索引

3.7、使用索引扫描排序

MySQL有两种方式可以生成有序结果：通过排序操作；按照索引顺序扫描。

只有当索引的列顺序和ORDER BY子句的顺序完全一致，并且所有列的排序方向（升序/降序）都一样时，MySQL才能使用索引来对结果做排序；
当查询需要关联多张表时，只有当ORDER BY子句引用的字段全部来自第一张表时，才能使用索引排序；
ORDER BY子句中的字段需要满足索引的最左前缀的要求，才能使用索引排序；
当索引的前导列为常量时，ORDER BY子句可以不满足索引的最左前缀要求也能使用索引排序。例如：key(rental_date, inventory_id, customer_id)；… where rental_data=‘2018-01-08’ ORDER BY inventory_id DESC;

四、维护索引和表

维护表有三个目的：找到并修复损坏的表；维护准确的索引统计信息；减少碎片

4.1、更新索引统计信息

MySQL的查询优化器会通过两个API来了解存储引擎的索引值的分布信息，已决定如何使用索引信息。

records_in_range();
info()。如果存储引擎向优化器提供的索引统计信息不准确，就会导致优化器做出错误的优化决定，这会严重影响查询性能。可通过执行ANALYZE TABLE 来重新生成统计信息以解决这个问题。

4.2、减少索引和数据的碎片

B-Tree索引可能会碎片化，碎片化的索引可能会以很差或无序的方式存储在磁盘上，这会降低查询效率；
表数据存储也可能碎片化。主要有行碎片、行间碎片、剩余空间碎片三种。对于MyISAM表，这三类碎片都可能发生，但InnoDB不会出现短小的行碎片，InnoDB会移动短小的行，并重写到一个片段中。
【维护方法】可通过执行POTIMIZE TABLE或者导出再导入来重新整理数据；对于那些不支持POTIMIZE TABLE命令的引擎，可以执行ALTER TABLE操作来重建表。只需要将表的存储引擎改为当前的引擎即可。例如：ALTER TABLE <table> ENGINE=<engine>;