【七】高性能MySql笔记——高性能索引策略

    正确的索引策略对性能优化相当重要。似乎没有什么事情是完美的，好的索引可以提高性能，坏的索引也可以拉低性能。所以在选择合适的索引策略应该具有通观全局的思维。我们应该充分了解每种索引策略，以使我们可以灵活运用、灵活选择。

    独立的列

    在MySql查询语句中条件不是独立的列，则不会使用索引。即列不能是表达式的一部分，也不能是函数的参数。我们应该遵循始终将索引列放在表达式的一侧。

    前缀索引和索引选择性

    前缀索引就是对一列的前面一部分字符串构建索引。索引很长的字符列会使性能变差。经过统计，往往我们只需要索引开始部分的列就可以满足需求，同时也可以提高效率。不过这种方式会降低索引的选择性。索引的选择性是指不重复的索引值和数据表的记录总数的比值。索引选择性越高效率越高。MySql不允许索引BLOB、TEXT和很长的VARCHAR类型的列，所以对于这类列我们必须使用前缀索引。为了找出最优的索引选择性所对应的索引字符串长度这里有一个诀窍，首先前缀应该足够长；其次找出最常见的值的列表和最常见的前缀列表进行比较；最后通过不断的增加前缀数量逐步逼近完整列的选择性即为前缀长度。对于数据量较大的表，我们可以通过逐步测试的方式选择合适的前缀数值。当发现再增加前缀数值已经不能提高选择性时即出现前缀最佳值。前缀索引的缺点：MySql不能使用前缀索引进行ORDER BY和GROUP BY，也无法使用前缀索引做覆盖扫描。后缀索引是与前缀索引相反的索引类型，可以通过将前缀索引倒序存储获得。

    多列索引

    我们在创建索引时应该集中精力优化索引列的顺序，或者创建一个全覆盖索引。而不应该为数据表中的每一列都创建单独的索引。虽然在新版本的MySql中可以通过“索引合并”策略，在一定程度上使用多个单列索引定位行。但这并不是一个好的方法，只是用于数据库优化门外汉使用。我们可以使用optimizer switch来关闭索引合并功能。或者使用IGNORE INDEX提示让优化器忽略掉某些索引。

    下面是识别不合适的多列索引的方法：

        ①查询服务对多个索引有相交操作时；

        ②查询服务对多个索引进行联合操作时（有多个OR条件）；

        ③不合适的多列索引会误导优化器，优化器会对查询成本低估，同时会影响并发性。

    选择合适的索引列顺序

    选择合适的索引列顺序可以覆盖更多的查询语句，也会更好的满足排序和分组的需要。索引列顺序是从最左侧开始的。优化时应首先考虑顺序、I/O和排序，再使用将选择性最高的列放最左列的方法。性能除了和索引列的选择性有关，也和查询条件的具体值有关（值得分布）。我们需要根据运行频率最高的查询来调整索引列的顺序。将数据类型总量更小的值列放在最左列可以快速缩小查询范围，提升查询效率。这种方法的缺点是可能会使其他的查询请求性能变差。即时我们选择的比较合适的索引列顺序也需要注意特殊情况下的查询请求，往往这种查询请求会摧毁整个应用的性能。在优化索引列顺序提升性能时也需要注意WHERE子句中的排序、分组和范围条件等其他因素，这些因素可能对查询的性能造成非常大的影响。

    聚簇索引（Oracle索引组织表）

    聚簇的意思是数据行和相邻的键值紧凑的存储在一起。聚簇索引是一种数据存储方式。由存储引擎实现聚簇索引。每张表只能有一个聚簇索引。其在InnoDB中的实现是保存了B-Tree索引和数据行。当存在聚簇索引时，数据行实际上存放在索引的叶子页中。原理：InnoDB会选择主键，没有主键选择唯一的非空索引，没有唯一的非空索引隐式定义一个主键进行聚集数据。InnoDB只聚集同一个页面中的数据，所以相邻的键值可能会相距很远（出于不同的页面）。聚簇主键并非总是对性能有益。

    优点：

        ①把数据保存在一起；

        ②数据访问更快；

        ③覆盖索引扫描的值可以直接使用页节点中的数据。

    缺点：

        ①聚簇索引对内存有更高要求；

        ②插入速度严重依赖插入顺序（按照主键的顺序是插入最快的方式，非主键方式插入完成之后最好使用OPTIMIZE TABLE命令重新组织表）；

        ③聚簇索引更新代价大；

        ④插入行或更新主键可能会面临“页分裂”，从而占用更多的磁盘空间；

        ⑤聚簇索引会导致全表扫描变慢，尤其是行比较稀疏，或者页分裂导致数据不连续的时候；

        ⑥二级索引（非聚簇索引）因为叶子节点包含了引用行的主键列，可能比想象的要更大；

        ⑦二级索引访问需要两次索引查找（因为叶子节点保存的是行的主键值）。

    注意：聚簇索引中的叶子节点保存的是行的主键值，目的是为了减少当出现行移动或者数据页分裂时二级索引的维护工作。为了更好的使用聚簇索引，我们建议即使用不到也为表增加一个自增的主键列，这样可以保证数据行是按顺序写入，对于根据主键做关联操作的性能也会更好。使用UUID作为聚簇索引会很糟糕，因为会使聚簇索引的插入完全随机。在高并发的情况下，顺序索引可能会造成明显的主键争用，争用热点发生在主键间隙的上界，可能导致间隙锁竞争。间隙锁定义可参考之前的文章，或自行Google。

    覆盖索引

    设计优秀的索引应该考虑整个查询。覆盖索引是索引中包含所有需要查询的字段的值。MySql只能使用B-Tree索引做覆盖索引。MySql不能在索引中执行LIKE操作，所以有LIKE操作的查询语句不可以使用覆盖索引。

    好处：

        ①索引条目通常远小于数据行大小，减少数据访问量；

        ②对于I/O密集型的范围查询有较好的性能提升；

        ③由于InnoDB的聚簇索引，覆盖索引效果更好。

（注：type的index和Extra的Using index完全不通，type值只表示MySql执行查询的方式）

    索引排序

    MySql实现排序的方法有：排序操作和索引顺序扫描。MySql可以使用同一个索引既满足排序，又用于查找行。如果要进行倒序，则需要自行维护倒序列表数据。ORDER BY也需要满足最左前缀要求（如果WHERE或JOIN子句中左侧为常量则也可以使用索引）。

    不能使用索引排序的情况：

        ①两种不同的排序方向（即DESC和ASC同时存在）；

        ②ORDER BY 引用了不在索引中的列；

        ③WHERE和ORDER BY 的列无法组成索引最左前缀；

        ④在索引的第一列上是范围条件；

        ⑤对排序多个等于条件也是一种范围查询。

    压缩（前缀压缩）索引

    MyISAM引擎使用前缀压缩来缩小索引的大小。默认只压缩字符串，通过参数设置也可以对整数做压缩。压缩块使用更少的空间，代价是某些操作可能更慢。索引查找必须从头扫描，倒序扫描（ORDER BY DESC）性能会很差。对于CPU密集应用，压缩索引会慢很多。好处是对于I/O密集型应用效果可能会好很多。通过在CREATE TABLE 中指定PACK KEYS参数来控制索引压缩方式。

    冗余和重复索引

    重复索引是指在相同的列上按照相同的顺序创建的相同类型的索引。需要注意MySql的唯一限制和主键都是通过索引实现的。如果索引中有一个组合索引包含了一个单列索引，那么这个单列索引就是重复索引（这里同样参考最左前列法则）。冗余索引通常发生在新增索引中，如新增（A,B）索引，而原本就有一个（A）索引。或者新增（A,ID）索引，ID为主键，主键保存在二级索引中，所以也是冗余索引。单纯的统计尽量使用MySql函数COUNT（），这样可以不访问原始数据提高性能。如果需要尽可能使用组合索引，而不是使用两个或多个独立索引，组合索引性能更优。检查重复索引：通过访问INFORMATION_SCHEMA表的查询。解决：删除冗余和重复索引。也可通过common_schema中的一些视图来定位。其中common_schema是一系列安装到服务器上的常用存储和视图。

    未使用的索引

    经常有些朋友在对数据库索引不了解的情况下为了优化一些查询建立的一些索引却并没有真正用到，这些索引不仅对优化没有作用，而且有时还会使得我们的其他查询效率减慢，因此这类未使用的索引是需要删除的。我们可以通过Percona Server或者MariaDB的userstates服务器变量进行一些监控，首先我们需要打开这个变量（默认是关闭的），然后让服务器正常运行一段时间，再通过查询INFORMATION_SCHEMA.INDEX_STATISTICS就可以查到每个索引的使用频率。我们也可以使用Percona Toolkit中的pt-index-usage工具查询日志，并对日志中的每条查询进行EXPLAIN操作，然后打印出关于索引和查询的报告、

    索引和锁

    数据库有表级和行级的锁，每种级别的锁都会给系统带来额外的开销，同时也会减少并发性。索引可以让查询锁定更少的行。这里我们只对最常用的InnoDB做一个提示：InnoDB在二级索引上使用共享锁，但访问主键索引需要排他锁。所以就不能使用覆盖索引了（覆盖索引包含了需要查询的值，因此排他锁就不可以使用覆盖索引），并且使得SELECT FOR UPDATE比LOCK IN SHARE MODE或非锁定查询要慢的多。