MySQL高级 —— 高性能索引

深碍√TFBOYSˉ_ 2023-02-11 07:15 118阅读 0赞

# 引言 #

最近一直在抱着《高性能MySQL（第三版）》研究MySQL相关热点问题，诸如索引、查询优化等，这阶段的学习是前一段时间MySQL基础与官方的“阅读理解”的进一步延伸。

书中第五章详细阐述了如何设计高性能的索引，以及索引的诸多注意事项。

之前关于索引的学习，包括《[MySQL 高级 —— 复合索引简介（多列索引）][MySQL _ _]》、《[MySQL 高级 —— 索引实现的思考][MySQL _ _ 1]》、《[MySQL 优化 —— MySQL 如何使用索引][MySQL _ _ MySQL]》这三篇文章，着实费了一番功夫，但随着对书中第五章内容的逐步学习，也越发觉得自己关于索引的理解和学习还不够充分，**遂以此篇《高性能索引》作以总结**，本篇博客可能会与之前的这三篇有部分内容重合，重合的部分，我会一笔带过或做一些必要的总结，多数内容都是新领悟到的知识。未来如果还有索引的新认识，应该不会再开新的文章了，而是会在这些文章的内容之上，进行更新，以免造成整体博客的质量下降。

# 一、索引的常识 #

索引是**存储引擎层实现**的一种数据结构，因此，索引的实现非常依赖于具体的存储引擎。

> **索引的三大优点**
> 
> 1、大大减少了服务器需要扫描的数据量  
> 2、帮助服务器避免排序和临时表  
> 3、索引可以将随机IO变为顺序IO

> **评价索引优劣的三星标准**
> 
> 1、索引将相关记录放到一起可以获得一星  
> 2、索引中的数据顺序可以满足排序需求则获得二星  
> 3、索引中的列包含了查询中需要的全部列可获得三星。能够获得此三星的索引，也叫"三星索引"。

# 二、B树索引 #

大多数场景的索引，都是指 InnoDB 存储引擎中的 B+Tree索引，这是最常见的索引。

详细的B树结构分析可以参考《[MySQL 高级 —— 索引实现的思考][MySQL _ _ 1]》

在学习B-Tree 索引的时候，重点是要理解 B+Tree这种数据存储结构，它的很多特性都是由这种特定的存储结构决定的。为什么会有最左前缀原则？为什么索引可以直接排序数据？这些特性都要求我们深入了解 B+树详细存储结构，明白非叶子节点和叶子节点的差别，明白叶子节点中存储的内容等等，只有深入了解了B+Tree的存储结构，才能更容易地记住各种复杂的索引特性，和索引使用限制。

**B-Tree对索引列是顺序存储的**，**即索引的节点是有序的**。它的另一个重要特点是，或者说与基本的B树索引的区别是，**每个叶子节点都包含指向下一个叶子节点的指针**（实际上是一个双向指针），叶子节点之间可以看做是一个有序链表的结构，这是为了方便叶子节点的范围遍历。

# 三、复合索引（或叫多列索引） #

参考《[MySQL 高级 —— 复合索引简介（多列索引）][MySQL _ _]》

> **多列索引支持的查询类型：**
> 
> 1、全列匹配  
> 2、最左前缀列匹配  
> 3、最左前缀列的最左前缀匹配：比如，idx\_name\_birth(last\_name, first\_name, birthday) 可以帮助查找last\_name的前缀字符，比如last\_name以'A'开头。  
> 4、最左前缀列的范围匹配。  
> 5、索引覆盖查询：只访问索引的查询，而无需访问数据行。
> 
> 前四点，主要是围绕B+Tree索引的**"最左前缀原则"**。

另外，**索引的另一个重要用途是排序**。如果 ORDER BY 子句满足前面列出的几种查询类型，那么索引也是可以直接排序数据的。

> 对于最左前缀原则，有一个小技巧，因为某些原因，查询中可能不会涉及到索引中靠前的列，那么可以**使用 IN()函数，将左前缀的列变为多个等值条件，以符合最左前缀原则**，避免让索引失效。但这种方法不适合可选列表太大的情况。

# 四、哈希索引 #

**哈希索引是基于哈希表实现，只能精确匹配索引所有列的查询才有效。**因此使用范围非常有限，但如果确定适合哈希索引，那么性能将会有质的飞跃。存储引擎会为每行数据对应的索引列计算一个哈希码，哈希码是一个较小的值，哈希索引保存哈希码和指向每个数据行的指针。

在MySQL中，只有Memory存储引擎显式支持哈希索引，是默认索引类型。

Memory引擎支持非唯一哈希索引，如果多个列的哈希值相同，索引会以链表的方式存放多个记录指针到同一个哈希码下。

> **哈希索引的结构：**
> 
> 槽(slot) 值(value)  
> 2323     指向第1行指针  
> 2458     指向第4行指针  
> 7437     指向第100行指针  
> ...      ...

哈希索引的结构本身也是一种key-value 结构，key就是索引列的哈希码（哈希码通过一个哈希函数来生成），值就是指针。在哈希索引中，**保存key的结构成为slot——槽**，由于哈希码是整数，所以**槽是有序的，但指针是无序的**。

## 4.1 哈希索引在查询时的工作过程 ##

假设下面的查询中 fname 列上有哈希索引：

SELECT ... FROM testhash WHERE fname = 'Peter';

那么上面的查询在执行时，MySQL会先计算'Peter'的哈希码，然后在哈希索引中找到对应的哈希码，以及其对应的行指针，最后比较行中的索引值是否等于'Peter'。

## 4.2 哈希索引为什么快？ ##

哈希索引的工作原理非常简单，因为**本身只存储整数型的哈希值**，其索引结构非常紧凑，因此命中目标的速度非常快。

## 4.3 哈希索引的缺点（限制） ##

> 1、哈希索引本身并不存储行数据，所以不可避免地需要读取行数据。不过这点对性能影响不大。  
> 2、哈希索引无法用于排序  
> 3、哈希索引不支持部分索引列匹配查找。记住，哈希索引始终是使用索引列的全部内容来计算哈希值的。  
> 4、哈希索引只支持等值比较。提示，IN()也是等值比较。  
> 5、哈希冲突，性能下降。哈希冲突是影响哈希索引性能的关键因素，发生哈希冲突的行的指针，会以链表的形式存储于同一个哈希码之下，因此存储  
> 引擎需要逐行比较才能最终确定结果，因此，哈希冲突越多，哈希索引性能越低。  
> 6、哈希冲突，维护成本更高。当删除数据行或者增加数据行时，如果发生哈希冲突，那么就需要遍历冲突行指针链表，增加维护成本。

## 4.4 InnoDB与哈希索引 ##

InnoDB引擎有一个叫做"**自适应哈希索引**"的特殊功能。当InnoDB发现某个索引值使用非常频繁时，会在内存中**基于B-Tree索引之上再创建一个哈希索引**。不过**这个功能是完全自动的**，用户无法配置或控制，但可以关闭。

## 4.5 自定义哈希索引 ##

我们可以自定义一种"伪哈希"索引。

其主要应用场景是：**为长字符串（如url）创建很小的哈希索引，既节省索引存储空间，又能更快查询**。如果有针对URL进行搜索的需求，那么非常适合建立这种"伪哈希索引"。

**经典案例**

某InnoDB表有一个url字段，如果正常对该列索引，并不是一个很明智的选择，因为url一般都很长，且毫无顺序。

改造方法是，在表中建立一个 url\_crc 列，该列用于存储 url 列的由MySQL内建函数 CRC32()生成的哈希码，那么查询的时候只需：

SELECT ... FROM tburl 
    WHERE url = "http://www.mysql.com" AND url_crc = CRC32('http://www.mysql.com');

这样做性能会非常高。但其实仔细观察可以发现这种伪哈希索引的本质，其实就是在表中保存url的哈希码，用B-Tree再去索引哈希码。

但注意，**查询需要同时指定哈希码和原列值**，这是**为了伪哈希索引模拟哈希索引内部解决哈希冲突的操作**。

另外，**哈希列做索引还需要注意维护**工作，一般可以使用**触发器**在**插入或更新时自动维护哈希列值**。

哈希值避免使用SHA1()或MD5()作为哈希函数。因为这两个函数计算出来的哈希值是非常长的字符串。它们都是强加密函数，设计目的就是最大限度消除冲突，但这里并不需要这么高的要求。简单哈希函数的冲突在一个可接受的范围内就可以。

# 五、高性能索引策略 #

## 5.1 过滤条件中要使用独立的索引列 ##

索引列不能是表达式的一部分，也不能是函数的参数，诸如：WHERE act\_id + 1 = 5;这样的形式，act\_id 是无法使用索引。要养成简化WHERE子句的习惯，始终将索引列单独放在比较符的一侧。

## 5.2 前缀索引 ##

当需要索引很长的字符串时，可以考虑前缀索引、伪哈希索引的设计组合。这样做的时候，需要考虑索引空间以及索引选择性的问题。具有较好选择性的前缀索引，可以在存储空间和性能之间寻找平衡点，简言之，要选择足够长的前缀以保证较高的选择性，同时又不能太长（节约空间）

> 什么是**索引的选择性**？
> 
> 基数(即可选值)和数据表记录总数的比值。范围是0到1，唯一索引的选择性是1，性能最好。

前缀的长度选择（平衡点），有两种考量方式：

1、前缀重复次数，接近于全字符串重复次数的时候。

2、直接计算选择性(平均选择性)。如：

SELECT  COUNT(DISTINCT city)/COUNT(*) AS origin,
            COUNT(DISTINCT LEFT(city, 3))/COUNT(*) AS sel3,
            COUNT(DISTINCT LEFT(city, 4))/COUNT(*) AS sel4,
            COUNT(DISTINCT LEFT(city, 5))/COUNT(*) AS sel5,
            COUNT(DISTINCT LEFT(city, 6))/COUNT(*) AS sel6,
            COUNT(DISTINCT LEFT(city, 7))/COUNT(*) AS sel7
    FROM city;

但注意，第二种方法计算的是**平均选择性**，必须要在几个候选项上，结合第一种方法，观察具体重复的频率是否真的接近原字符串。

另外，前缀索引无法用于排序或分组，也无法做索引覆盖扫描。这都是因为索引中存储的是原字符串的前缀而不是原字符串。前缀索引的常见应用是针对十六进制唯一ID，一般采用长度为8的前缀索引通常能够显著提升性能。

## 5.3 优先使用多列索引 ##

大多数情况，多列索引（复合索引）的性能要优于单列索引。

## 5.4 合适的索引顺序 ##

定义索引（B-Tree索引）时索引列的先后顺序很重要。一般的法则是：将列值选择性高的索引列放在靠前位置。这个法则非常适用于优化WHERE 条件的查找。但也需要结合值的分布（即具体值）进一步考量。这和前缀索引需要考虑的问题类似。可能还需要根据那些频率最高的查询来调整索引列的顺序。

具体操作的方法是统计一下指定常量对应的数量：

SELECT SUM(stuff_id = 2), SUM(customer_id = 584) FROM tb;

当观察到对应的重复数量后，应该将重复更少的列放到多列索引靠前的位置，但由于上面的统计很依赖于具体的值，所以在此之前还需要考察一件事，即平均选择性（参考前面的总结），例如：

SELECT 
        COUNT(DISTINCT staff_id) / COUNT(*) AS staff_id_selectivity,
        COUNT(DISTINCT customer_id) / COUNT(*) AS customer_id_selectivity,
        COUNT(*)
    FROM tb;

只要结合这两方面考量，大多数情况下的查询，索引列的顺序都是比较合适的。

## 5.5 聚簇索引 ##

这是一种数据存储方式。用B-Tree结构同时保存索引和数据行。聚簇索引中，数据行保存在索引的叶子节点中。  
因为聚簇索引会直接用于存储数据行全部数据，所以一个表只能有一个聚簇索引（但覆盖索引可以模拟多个聚簇索引）。叶子节点包含数据行全部数据，普通节点只包含索引。

在MySQL中只支持主键的聚簇索引。因此InnoDB会建议用户定义主键来做聚簇索引，如果表中没有主键，InnoDB会选择一个非空且唯一索引代替，如果也没有，InnoDB会隐式地定义一个主键来做聚簇索引。

> **聚簇索引的优点：**
> 
> 1、数据访问更快。
> 
> 2、使用覆盖索引扫描的查询可以直接使用叶子节点中的主键值。

> **聚簇索引的缺点**：
> 
> 1、插入速度严重依赖于插入顺序。按照主键顺序插入是加载数据到InnoDB表中速度最快的方式。
> 
> 2、更新聚簇索引列的代价很高。因为会强制InnoDB将每个被更新的行移动到新的位置。
> 
> 3、基于聚簇索引的表在插入新行，或者主键被更新需要移动行的时候，可能会出现"页分裂"的问题。当行的主键值要求必须将这一行插入到某个已满的页中时，存储引擎会将该页分裂成两个页面来容纳该行，也分裂会导致表占用更多的磁盘空间。
> 
> 4、聚簇索引可能导致全表扫描变慢，尤其是行比较稀疏的时候，或者由于页分裂导致数据存储不连续的时候。
> 
> 5、二级索引（聚簇索引表的非聚簇索引）可能比想象的要更大，因为在二级索引的叶子节点中包含了引用行的主键列。
> 
> 6、二级索引需要两次索引查找。二级索引中保存的"行指针"的本质，是主键值，而不是数据行的物理地址指针。对于InnoDB，自适应哈希索引能够减少这样的重复工作。

## 5.6 覆盖索引 ##

MySQL可以通过索引来获取列的数据。**覆盖索引**就是旨在**直接从索引中获取数据，**从而避免读取行，**极大地提升查询的性能**。

> 如果一个索引包含（或者说覆盖）所有需要查询的字段值，我们就称之为——**覆盖索引**。SELECT 和 WHERE条件中的字段都出现在索引中，即为覆盖索引

> **覆盖索引的优点：**
> 
> 1、索引条目通常远小于数据行大小。更容易全部放入内存中。
> 
> 2、索引是按列值顺序存储的，对于IO密集型的范围查找会比随机从磁盘读取每一行数据的IO要少得多。
> 
> 3、一些存储引擎如 MyISAM 在内存中只缓存索引，数据则依赖于操作系统来缓存，因此要访问数据需要一次系统调用。
> 
> 4、覆盖索引对于支持聚簇索引的InnoDB表非常有用。InnoDB的二级索引在叶子节点中保存了行的主键值，所以如果二级索引能够覆盖查询，则可以避免对主键的二次查询。

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

> **当发起一个被索引覆盖的查询（也叫做索引覆盖查询）时，在EXPLAIN 的 Extra 列可以看到“Using index”的信息**。

有时候，很容易把Extra 列的“Using index”和type列的“index”混淆。其实两者完全不同，**type列和覆盖索引毫无关系。**它只是表示这个查询访问数据的方式，或者说是MySQL查询行的方式，MySQL手册中称之为“连接方式（join type）”

**MySQL查询优化器会在执行查询之前判断是否有一个索引能进行覆盖。**

思考下面这条上SQL能否覆盖索引：

SELECT * FROM products
    WHERE actor = 'SEAN CARREY'
    AND title LIKE '%APOLLO%';

两个原因该条SQL无法实现覆盖索引，第一点就是 “SELECT \* ”，不可能有某个索引覆盖所有的列值。

第二点是 LIKE，MySQL不能在索引中执行 LIKE 操作。这是底层存储引擎API的限制。MySQL能在索引中做最左前缀匹配的LIKE比较，因为该操作可以转换为简单的比较操作。但如果是**通配符开头的LIKE 查询，存储引擎就无法做比较匹配。这种情况下，MySQL只能提取数据行的值而不是索引值来做比较**。

改进办法是，先将索引扩展覆盖至三个列：**(actor, title, prod\_id)**，然后按照如下方式重写SQL：

SELECT * FROM products
    JOIN (
        SELECT prod_id
        FROM products
        WHERE actor = 'SEAN CARREY' AND title LIKE '%APOLLO%'
    ) AS t1 
    ON t1.prod_id = products.prod_id;

这种查询方式叫做——**延迟关联**，因为延迟了对列的访问。子查询中只涉及了三个索引列，因此可以使用覆盖索引完成查询，外层的查询利用覆盖索引查询得出的 prod\_id 去完成其余列的查询。虽然无法使用索引覆盖整个查询，但性能已经提升很多。

另外，由于二级索引的叶子节点也包含主键值，因此，有时候查询中如果包含主键，即便主键并没有定义在索引中，也可以完成覆盖索引查询。

还有一种有意思的现象就是，有时候 EXPLAIN 的 possible\_key 为 NULL，但是key却有索引，这是为什么？例如：

CREATE TABLE `teacher` (
      `teacher_id` int(11) NOT NULL AUTO_INCREMENT COMMENT '教师id',
      ...
      PRIMARY KEY (`teacher_id`),
      KEY `idx_name_salary_age` (`teacher_name`,`salary`,`age`)
    ) ENGINE=InnoDB

执行下面这条查询，就可以出现这样的现象：

EXPLAIN SELECT t.`teacher_name`,t.`age`, t.`salary` FROM teacher t 
    WHERE t.`salary` > 4000

![20200524130827125.png][]

这种情况就多发生在索引覆盖查询，possible\_key 为 NULL 说明用不上索引的树形查找。但如果二级索引包含了所有要查找的数据，就像之前说的，**MySQL查询优化器会在执行查询之前判断是否有一个索引能进行覆盖**。二级索引往往比聚簇索引小，所以MySQL就会选择顺序遍历这个二级索引，然后直接返回。因此才会出现这种情况。

## 5.7 使用索引扫描来做排序 ##

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

如果EXPLAIN 的 type 列值为“index”，则说明 MySQL 使用了索引扫描来做排序。

索引扫描本身很快，但如果索引不能覆盖查询所需的全部列，那就不得不每扫描一条索引条目就得回表查询一次对应行。这基本都是随机IO，因此**按照索引顺序读取数据的速度通常要比顺序地全表扫描慢**。

MySQL 可以**使用同一个索引既满足排序，又用于查找行**。因此，在设计索引时，应该尽可能同时满足这两种任务。

> **注意，只有当索引列的顺序和 ORDER BY 子句的顺序完全一致，并且所有列的排序方向（DESC或ASC）都一样时，MySQL才能够使用索引来对结果进行排序。如果查询需要关联多张表，则只有当 ORDER BY 子句引用的字段全部为第一张表时，才能使用索引进行排序。**ORDER BY 子句和查找型查询的限制是一样的：需要满足索引的最左前缀要求。否则，MySQL都需要执行排序操作，而无法利用索引排序。

有一种情况下 ORDER BY 子句可以不满足索引的最左前缀要求，就是前导列为常量的时候。**WHERE 或 JOIN 子句中对索引的前导列指定了常量，可以“弥补” ORDER BY 子句中不符合“最左前缀原则”的问题**。

例如，如下一张 租赁表 rental ：

CREATE TABLE rental(
        ...
        PRIMARY KEY (rental_id),
        UNIQUE KEY rental_date (rental_date, inventory_id, customer_id),
        KEY idx_fk_inventory_id (inventory_id),
        KEY idx_fk_customer_id (customer_id),
        KEY idx_fk_staff_id (staff_id),
        ...
    )

那么如下SQL，即便ORDER BY 子句并没有严格的使用“最左前缀”，但依然可以完成索引排序：

SELECT rental_id, staff_id FROM rental
    WHERE rental_date = '2020-05-24'
    ORDER BY inventory_id, customer_id;

这是因为在 WHERE 子句中，索引 rental\_date 的前导列 rental\_date 被指定了一个常量，因此，即使 ORDER BY 子句本身不满足索引的最左前缀要求，但也可以用于查询排序。JOIN 子句也是同样的道理，它可以将常量的列进行传递。

下面的一些排序也是同样可以索引排序的：

SELECT... WHERE rental_date = '2020-05-24' ORDER BY inventory_id DESC;
    SELECT... WHERE rental_date > '2020-05-24' ORDER BY rental_date, inventory_id;

> **下面是一些不能使用索引排序的例子**：
> 
> 1、查询使用了两种不同的排序方向，但是索引列都是正序排序的
> 
>     ...WHERE rental_date = ‘2020-05-24’ ORDER BY inventory_id DESC, customer_id ASC;
> 
> 2、查询中的ORDER BY 子句引用了一个不在索引中的列
> 
>     ... WHERE rental_date = ‘2020-05-24’ ORDER BY inventory_id, staff_id;
> 
> 3、查询中的前导列有范围查询
> 
>     ... WHERE rental_date = ‘2020-05-24’ 
>     AND inventory_id IN(1, 2) 
>     ORDER BY customer_id ;

## 5.8 压缩（前缀压缩）索引 ##

MyISAM使用前缀压缩来减少索引的大小，从而让更多的索引可以放入内存中，这在某些情况下能极大提升性能。

默认只压缩字符串，但通过参数设置也可以对整数进行压缩。

MyISAM压缩每个索引块的方法是，**先完全保存索引块中的第一个值，然后将其他值和第一个值进行比较得到相同前缀的字节数和剩余的不同后缀部分，把这部分存储起来即可**。

例如，索引块中的第一个值是“perform”，第二个值是“performance”，那么第二个值的前缀压缩后存储的是类似“7, ance”这样的形式。

MyISAM对行指针也采用类似的前缀压缩方式。

> 压缩块使用更少的空间，代价是某些操作可能更慢。
> 
> 因为每个值的压缩前缀都依赖前面的值，所以MyISAM查找时无法在索引块使用二分查找而只能从头开始扫描。正序的扫描速度还不错，但是如果是倒序扫描——例如ORDER BY DESC 就不是很好。所有在块中查找某一行的操作平均都需要扫描半个索引块。
> 
> 测试表明，对于CPU 密集型应用，因为扫描需要随机查找，压缩索引使得MyISAM在索引查找上要慢好几倍，而压缩索引的倒序扫描就更慢了。
> 
> 压缩索引需要在CPU内存资源和磁盘之间做权衡。可以在CREATE TABLE 语句中指定PACK\_KEYS参数来控制索引压缩的方式。

## 5.9 重复索引、冗余和未使用的索引 ##

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

但如果**索引类型不同，并不算重复索引**，如 KEY(col) 和 FULLTEXT KEY(col) 。

MySQL允许在相同列上创建多个索引，无论是有意的还是无意的。但 MySQL 需要单独维护重复的索引，并且**优化器在优化查询时也需要逐个进行考虑，这会影响性能**。因此，应该避免这样创建索引，发现之后应该立即移除。

有时候可能是由于不清楚各种索引的特点导致误创建重复索引，例如下面，其实在ID 列创建了三个重复的索引，要避免这么做：

CREATE TABLE test(
        ID INT NOT NULL PRIMARY KEY,
        ...
        UNIQUE(ID),
        INDEX(ID),
    ) ENGINE=InnoDB;

冗余索引和重复索引不同，如果创建了索引 (A, B) ，再创建索引 (A) 就是冗余索引。但是如果再创建索引 (B, A) ，则不是冗余索引，索引 (B) 也不是。另外，其他类型的索引，如 哈希、全文索引等，也不会是 B 树索引的冗余索引。

**大多数情况下，都不需要冗余索引**。如果发现索引无法满足查询对性能的要求，那么只需要扩展对应索引，增加索引列即可。但有时候，如果扩展已有的索引会导致变化太大，从而影响其他使用该索引的查询性能，可能就需要冗余索引，但无论如何这么做一定是会增加维护成本的。

**如果决定了要删除某个索引，一定要非常小心**。二级索引的叶子节点包含主键值，所以刚刚的例子中，索引 (A) 相当于 (A, ID) 。如果像 WHERE A = 5 ORDER BY ID 这样的查询，这个索引会很有作用。但如果将索引扩展为 (A, B) ，则实际上就变成了 (A, B, ID)，那么上面查询的 ORDER BY 子句就无法使用该索引做排序了，只能使用文件排序。

未使用的索引完全是累赘，建议删除。

有两个工具可以帮助定位未使用的索引。

最简单有效的办法是在Percona Server或者MariaDB中先打开userstates服务变量（默认是关闭的），然后让服务器正常运行一段时间，再通过查询INFORMATION\_SCHEMA.INDEX\_STATISTICS就能查到每个索引的使用频率。

另外，还可以使用Percona Toolkit 中的pt-index-usage，该工具可以读取查询日志，并对日志中的每条查询进行EXPLAIN 操作，然后打印出相关索引和查询报告。

## 5.10 索引和锁 ##

**索引可以让查询锁定更少的行。**

如果你的查询从不访问那些不需要的行，那么就会锁定更少的行，从两个方面来看这对性能都有好处。首先，虽然InnoDB的行锁效率很高，内存使用也很少，但是锁定行仍然会带来额外的开销。其次，锁定超过需要的行会怎增加锁争用，降低并发性。

**InnoDB只有在访问行的时候才会加锁**，而索引能够减少InnoDB访问的行数，从而减少锁的数量。**但这只有当InnoDB在存储引擎层能够过滤掉所有不需要的行时才有效。**如果索引无法过滤掉无效的行，那么在InnoDB检索到数据并返回给服务器层以后，MySQL服务器才能应用WHERE 子句。这时已经无法避免锁定行了：InnoDB已经锁住了这些行，到适当的时候才释放。在MySQL5.1 和更新的版本中，InnoDB可以在服务器端过滤掉行后就释放锁，但是在旧的版本中，InnoDB只有在事务提交后才能释放锁。

# 六、索引案例 #

这一节分享书中介绍的一个典型索引设计案例——设计一个在线约会网站。

用户信息表包含很多列，包括**国家、地区、城市、性别、眼睛颜色**等。网站必须支持上述这些特征的**各种组合**来搜索用户。

第一件需要考虑的问题是需要**用索引来排序**，还是**先检索数据再排序**？使用索引排序会严格限制索引和查询的设计，换来的是排序性能的提升。

## 6.1 如何设计支持多种过滤条件的索引 ##

在 “5.2 前缀索引” 一节引入了 “基数” 的概念，它描述的是数据列中不同值的多寡情况。为了设计一个可以支持多种过滤条件的索引，现在需要考虑的问题就是：**哪些列拥有很多不同的值**。

一般来说，MySQL可以通过基数更大的列更有效地过滤掉不需要的行。

country 列的选择性并不高，但很多查询都会用到，sex 的选择性肯定非常低，但也会在很多查询中用到。所以这里就需要考虑真实场景中使用频率的问题，所以，建议在创建不同组合索引的时候将(sex, country) 作为索引前缀。

> 但是为什么要在这两个选择性低的列上创建索引呢？
> 
> 1、因为根据软件的需要，可能绝大多数的查询都需要用到sex列。所以使用频率非常高，在sex列上建立索引不会有坏处。
> 
> 2、即便查询并没有用到 sex 列，也可以通过这样的诀窍绕开：如果查询不限制性别，可以在查询中增加 AND sex IN(‘m’, ‘f’)来让MySQL选择该索引，MySQL依然能够匹配索引的最左前缀。

第二点，如果列有太多不同的值，就会让IN()列表太长，这样做就不是很好。

这个案例显示了一个**基本原则1：考虑表上所有的选项**。当设计索引时，不要只为现有的查询考虑需要哪些索引，还需要考虑对查询进行优化。

如果发现某些查询需要创建新的索引，但是这个索引又会降低另一些查询的效率，那么应该想一下**是否能优化原来的查询，应该同时优化查询和索引以找到最佳的平衡**，而不是闭门造车去设计最完美的索引。

接着需要考虑的是，其他**常见WHERE 条件的组合**，并需要了解哪些组合在没有合适索引的情况下会变慢。

> (sex, country, age)上的索引就是一个明显的选择，另外很有可能还需要(sex, country, region, age) 和 (sex, country, region, city, age) 这样的组合索引。
> 
> 这样就会需要大量的索引。如果想尽可能重用索引而不是建立大量的组合索引，可以使用前面提到的IN()的技巧来避免同时需要(sex, country, age)和(sex, country, region, age)的索引。像前面说的，如果IN()中的不同值较多，那么就需要定义一个全部国家列表，或者国家的全部地区列表，来确保索引前缀有同样的约束（组合所有国家、地区、性别将会是一个非常大的条件）。

上述条件是比较常见的搜索条件，那如何为一些生僻的搜索条件（比如has\_picture, eye\_color, hair\_color, education）来设计索引呢？这些列的选择性高、使用也不频繁，可以选择忽略它们，让MySQL多扫描一些额外的行即可。另一个办法是在age前加上这些列，在不涉及这些筛选条件的时候，依然是使用 IN() 来处理查询。

但是，**为什么age 始终要放在最后？**

因为，**age列多半是范围查询**。根据最左前缀原则——查询只能使用索引的最左前缀，直到遇到第一个范围条件列。因此，这里的**基本原则2**是：**尽可能将需要做范围查询的列放到索引后面，以便优化器能使用尽可能多的索引列**。

关于 IN() 覆盖那些不在WHERE 子句中的列，这种技巧也不能滥用。因为每额外增加一个 IN()条件，优化器需要做的组合都将以指数形式增加，最终可能极大的降低查询性能。

## 6.2 避免多个范围条件 ##

什么是范围条件？从EXPLAIN 的输出很难区分 MySQL 是要查询**范围值**，还是查询**列表值**。EXPLAIN 使用同样的 “range” 来描述这两种情况。例如，从 type 列来看，MySQL会把下面这种查询当做 range 类型：

EXPLAIN SELECT actor_id FROM actor WHERE actor_id > 45;

而列表查询的 type 也是 range：

EXPLAIN SELECT actor_id FROM actor WHERE actor_id IN(1, 4, 99) ;

EXPLAIN 无法区分两者。但实际上，第二个查询就是多个等值查询，这两种访问效率是不同的。**对于范围条件查询，MySQL无法再使用范围列后面的其他索引列了，但对于“多个等值条件查询”则没有这个限制。**

## 6.3 优化排序 ##

使用文件排序对小数据集是很快的，但如果一个查询匹配的结果有上百万行，例如 WHERE 子句中只有 sex 列，该如何排序？

**对于那些选择性非常低的列，可以增加一些特殊的索引来做排序**。例如，可以创建(sex, rating)索引用于下面的查询：

SELECT ... FROM profiles WHERE sex = ‘M’ ORDER BY rating LIMIT 10;

这个查询同时使用了 ORDER BY 和 LIMIT ，如果没有索引的话会很慢。由于索引中增加了 rating (用户等级) 这个适合排序的字段，当然也可以是其他适合排序的列，那么根据前面提到的，由于 WHERE 子句中给 sex 指定了常量，因此 ORDER BY 子句可以根据最左前缀原则来实现索引排序。

对于一些涉及到 LIMIT 子句的分页场景，这种排序操作尤为重要，如果能使用索引来完成排序，性能将会是一个不错的提升。

但是，即使有了索引，如果翻页翻到比较靠后时查询也可能非常慢。例如：

SELECT ... FROM profiles WHERE sex = ‘M’ ORDER BY rating LIMIT 100000, 10;

无论如何创建索引，这种查询都是个严重的问题。因为随着偏移量的增加，MySQL需要花费大量的时间来扫描需要丢弃的数据。

**反范式化、预先计算和缓存可能是解决这类查询的仅有策略**。一个更好的办法是限制用户能够翻页的数量，实际上这对用户体验的影响不大，因为很少有用户真正在乎搜索结果的第10000页。

优化这类索引的另一个比较好的策略是使用**延迟关联**（前面有提到），通过使用覆盖索引查询返回需要的主键，再根据这些主键关联原表获得需要的行。这可以减少MySQL扫描那些需要丢弃的行数。下面这个查询显示了如何高效地利用(sex, rating)索引进行排序和分页：

SELECT ... FROM profiles 
    INNER JOIN(
        SELECT <primary key cols> FROM profiles
        WHERE x.sex = ‘M’ ORDER BY rating LIMIT 100000, 10
    ) AS x USING(<primary key cols>);

# 七、维护索引和表 #

有了表和合适的索引后，就需要我们具备维护它们知识，保证它们可以正常工作。

> 维护表的三个主要目的：
> 
> 1、找到并修复损坏的表
> 
> 2、维护准确的索引统计信息
> 
> 3、减少碎片

## 7.1 找到并修复损坏的表 ##

表损坏是最糟糕的事情。对于MyISAM存储引擎，表损坏通常是系统奔溃导致的。

其他的存储引擎也会由于硬件的问题、MySQL本身的缺陷或者操作系统导致损坏。损坏的索引会导致查询返回错误的结果或者莫须有的主键冲突问题，严重时甚至还会导致数据库的崩溃。

如果遇到古怪的问题，可以尝试运行 **CHECK TABLE 来检查是否发生表损坏**（注意有些存储引擎不支持该命令。），它通常可以帮助找出大多数表和索引的错误。

可以使用 **REPAIR TABLE 命令来修复损坏的表**，但同样不是所有存储引擎都支持该命令。

**如果存储引擎不支持，也可以通过一个不做任何操作的ALTER操作来重建表**。例如，修改表的存储引擎为当前引擎：

ALTER TABLE innodb_tb1 ENGINE=INNODB;

如果是InnoDB引擎的表出现了损坏，那么一定是发生了严重的错误，需要立刻调查一下原因。InnoDB一般不会出现损坏。InnoDB的设计保证了它并不容易损坏。

如果发生损坏，一般要么是数据库硬件问题例如内存或磁盘问题（有可能），要么是由于数据库管理员的错误例如在MySQL外部操作了数据文件（有可能），亦或是InnoDB本身的缺陷（不太可能）。不存在什么查询能够让InnoDB 表损坏，也不用担心暗处有“陷阱”。如果某条查询导致InnoDB数据的损坏，那一定是遇到了bug，而不是查询的问题。

## 7.2 更新索引统计信息 ##

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

第一个API 是records\_in\_range()，通过向存储引擎传入两个边界值获取在这个范围大概有多少条记录。对于某些存储引擎，该接口返回精确值，例如MyISAM，但对于另一些存储引擎则是一个估算值，例如InnoDB。

第二个API是info()，该接口返回各种类型的数据，包括引擎的基数（每个键值有多少条记录）。

如果存储引擎向优化器提供的扫描行数信息是不准确的数据，或者执行计划本身太复杂以致无法准确获取各个阶段匹配的行数，那么优化器会使用搜索统计信息来估算扫描行数。

[MySQL _ _]: https://blog.csdn.net/u014745069/article/details/104027362
[MySQL _ _ 1]: https://blog.csdn.net/u014745069/article/details/104091956
[MySQL _ _ MySQL]: https://blog.csdn.net/u014745069/article/details/104079373
[20200524130827125.png]: https://img-blog.csdnimg.cn/20200524130827125.png