MySQL 优化

索引合并

MySQL 中的索引合并是一种优化查询的方法，其主要作用是针对多个查询条件的情况，将多个索引合并起来使用，以提高查询效率。当执行查询时，MySQL 会根据 WHERE 条件中涉及到的字段来选择合适的索引，如果 WHERE 条件中有多个字段，则 MySQL 可能需要将多个索引合并起来使用，以加快查询。

索引合并能够减少 I/O 操作次数，并减少查询所需的 CPU 资源。在实际应用中，索引合并通常发生在涉及到多个列的复杂查询中，如包含多个 AND、OR 关键字的查询语句。

索引合并的过程并不是自动发生的，需要 MySQL 优化器来决定是否合并索引，如果优化器认为合并索引会更快，则会通过索引合并来执行查询，否则就会选择单一的索引进行查询。

需要注意的是，虽然索引合并能够提高查询性能，但是在某些情况下，它可能会降低查询效率，如数据量较小或者涉及到过多的索引等。因此，在实际应用中，我们需要仔细评估索引合并的效果，以获得最优的查询性能。

回表

MySQL 回表是指在使用非聚集索引查询时，由于非聚集索引中没有包含查询结果所需的所有字段，因此需要返回到主键索引或者数据文件中查询额外的数据块，从而得到完整的查询结果。这个过程中，MySQL 需要再次进行随机 I/O 操作，降低了查询效率。

产生回表的原因是因为非聚集索引只包含索引列，而不是整个数据行，因此如果查询结果需要访问非索引字段，就需要回到原始数据行中获取这些字段的值。例如，如果需要查询 student 表中所有英语成绩大于 80 分的学生信息，可以使用如下查询语句：

1

SELECT * FROM student WHERE english_score > 80;

假设 student 表中包含三个字段：id、name 和 english_score，我们可以为 english_score 字段创建一个单独的非聚集索引：

1

CREATE INDEX idx_english ON student(english_score);

当执行以上查询语句时，由于非聚集索引只包含 english_score 字段，MySQL 需要回到原始数据行中获取 id 和 name 等其他字段的值，这会导致额外的 I/O 操作，降低查询效率。

为了避免回表现象，可以使用 “索引覆盖” 的方式来优化查询，即尽可能地让查询结果可以直接从索引中获取，而不需要回到原始数据行中查询额外的数据块。在以上的例子中，我们可以选择只查询需要的字段：

1

SELECT id, name FROM student WHERE english_score > 80;

这样，MySQL 就可以直接从 idx_english 索引中获取 id 和 name 字段的值，而不需要回到原始数据行中执行额外的 I/O 操作。这种方式可以有效地避免回表现象，提高查询效率。

索引覆盖

MySQL 索引覆盖是指通过查询索引就能够获取所有需要的数据，而不必回到数据表中进行进一步的查询操作。 索引覆盖可以减少 I/O 操作次数和 CPU 资源的使用，提高查询效率。

MySQL 索引覆盖出现的时间即在查询语句中只包含需要查询的字段和已经创建的索引中的字段时，就可能出现索引覆盖现象。例如，在 student 表中，如果为 id 和 name 字段创建一个联合索引 idx_id_name，那么下面的查询语句就可以使用索引覆盖：

1

SELECT id, name FROM student WHERE id = 100;

由于查询语句中只查询了 id 和 name 两个字段，并且这两个字段已经在 idx_id_name 索引中出现，因此 MySQL 可以直接从该索引中获取所有需要的数据，而无需回到数据表中进行进一步的查询。

索引覆盖的作用在于减少了数据读取和 I/O 操作次数，提高了查询效率。对于大型的数据库系统和高并发的应用场景，使用索引覆盖可以明显地减少查询响应时间和系统负载，提升用户体验。

需要注意的是，索引覆盖并不是在所有情况下都适用的。 当查询结果需要返回的列较多或者涉及到多个数据表时，使用索引覆盖可能会导致索引过大，影响查询效率 。因此，在实际应用中，我们需要根据具体的业务场景和查询需求来选择是否使用索引覆盖。

最左匹配原则

MySQL 的最左匹配原则是指在使用联合索引时，如果查询语句只涉及到联合索引的最左边的若干个字段，那么 MySQL 就可以利用该联合索引进行匹配。这样可以有效地利用索引提高查询效率。

最左匹配原则的作用是帮助 MySQL 优化器更好地利用索引，在联合索引中，从左到右依次匹配查询条件中的字段。这样可以减少需要扫描的数据块，提高查询效率。当查询条件不包含联合索引的最左边的字段时，MySQL 就无法利用联合索引进行匹配，需要进行全表扫描，查询效率会降低。

MySQL 的优化器可以使用最左匹配原则进行优化，当查询语句中包含联合索引的最左边的若干个字段时，MySQL 会优先使用该联合索引进行匹配，并且尽可能地利用索引进行查询。如果查询语句中的条件不包括联合索引的最左边的若干个字段，MySQL 无法使用索引覆盖，需要回表或者进行全表扫描，降低了查询效率。

举一个例子，假设我们有一个 student 表，包含 id、name 和 gender 三个字段，此时我们创建如下的联合索引：

1

CREATE INDEX idx_student_gender ON student(gender, name);

当我们查询 gender 为 1 的所有学生姓名时，可以使用如下的查询语句：

1

SELECT name FROM student WHERE gender = 1;

由于查询条件中只涉及到索引的最左边的字段 gender，MySQL 可以直接利用该联合索引进行匹配，从而提高查询效率。

尽管 MySQL 的最左匹配原则可以优化查询性能，但是在一些情况下，该原则可能会失效。以下是一些可能导致最左匹配原则失效的情况：

1. 查询条件中存在 OR 关系

   如果查询语句中包含 OR 操作符，并且 OR 前后没有涉及到相同的字段，那么 MySQL 就无法使用最左匹配原则进行优化。例如：

   1	SELECT name FROM student WHERE gender = 1 OR age = 18;

   该查询语句无法使用联合索引进行优化，因为 OR 前后涉及到不同的字段。

2. 查询条件中存在函数、类型转换等操作

   如果查询条件中包含函数、类型转换或者其他复杂的操作，那么 MySQL 就无法使用最左匹配原则进行优化。例如：

   1	SELECT name FROM student WHERE gender = 1 AND UPPER(name) = '张三';

   该查询语句中使用了 UPPER 函数对 name 字段进行转换，导致 MySQL 无法使用联合索引进行匹配。

3. 查询条件中包含范围查询

   如果查询条件涉及到了范围查询（例如 BETWEEN、IN 等），那么 MySQL 就无法使用最左匹配原则进行优化。例如：

   1	SELECT name FROM student WHERE gender = 1 AND age BETWEEN 18 AND 25;

   该查询语句中 age 字段的范围查询导致 MySQL 无法使用联合索引进行匹配。

4. 联合索引中包含较长的字段

   如果联合索引中包含了较长的字段（例如 TEXT 或者 BLOB 类型），那么 MySQL 就无法使用最左匹配原则进行匹配。因为 MySQL 无法在内存中使用太长的索引字段，需要回表或者进行全表扫描。

总之，最左匹配原则只是 MySQL 优化查询的一个原则，并非万能的。在实际应用中，我们需要根据具体的业务场景和查询需求来选择是否使用联合索引以及如何定义联合索引。

索引下推

MySQL 索引下推（Index Condition Pushdown，简称 ICP）是一种优化查询性能的技术。它可以将表达式的计算移动到索引存储引擎层级中，在索引层级对数据进行过滤，减少了需要返回给服务器层面的数据量，从而提高了查询性能。

在使用索引下推的情况下，MySQL 不仅会利用索引定位需要查询的数据行，还会在索引层级上对数据行进行过滤，并只返回符合查询条件的数据行，减少了需要传回给服务器层面的数据量，降低了服务器的负担和通信开销。通过这种方式，MySQL 可以充分利用索引的优势，提高查询性能。

下面举一个例子说明索引下推的作用：

假设我们有一个 employee 表，包含 id、name 和 age 三个字段，同时我们创建了如下的联合索引：

1

CREATE INDEX idx_employee_age ON employee(age, name);

现在我们需要查询年龄为 20 岁的员工姓名和 ID，查询语句如下：

1

SELECT id, name FROM employee WHERE age = 20;

在没有使用索引下推的情况下，MySQL 需要按照查询条件从索引中定位数据行，然后再回到表格中查找 ID 和姓名，数据量会很大，效率较低。

但是当使用索引下推时，MySQL 可以将 age 字段的过滤操作下推到索引层级上进行过滤，只返回符合条件的数据行，这样可以减少需要回到表格中查找 ID 和姓名的数据量，提高查询效率。

1

SELECT id, name FROM employee WHERE age = 20 AND name LIKE '张 %';

再比如上面这个语句, 如果使用 where 条件中的 like 条件作为最左前缀条件，MySQL 可以通过 ICP 只检索带有指定前缀的索引项，跳过一些没必要检索的索引项，可以大大优化查询性能。

总之，索引下推技术可以通过将表达式的计算下推到索引层级上进行过滤，减少需要返回给服务器层面的数据量，提高查询性能。

索引失效的情况

MySQL 索引优化是提高查询效率的重要手段，但是在某些情况下，索引可能会失效。以下是一些可能导致索引失效的情况，以及它们的原因和举例说明。

1. 条件中使用了函数

   如果查询条件中使用了函数，那么 MySQL 无法使用索引进行优化。例如：

   1	SELECT * FROM employee WHERE YEAR(birthday) = 1990;

   以上查询语句使用了 YEAR 函数对 birthday 字段进行处理，导致 MySQL 无法利用索引对数据进行过滤。这个时候我们可以改写为：

   1	SELECT * FROM employee WHERE birthday BETWEEN '1990-01-01' AND '1990-12-31';

   这样 MySQL 就可以使用索引进行优化。

2. 条件中使用了不等于操作符（<>、!=）

   如果查询条件中使用了不等于操作符（<>、!=），那么 MySQL 无法使用索引进行优化。例如：

   1	SELECT * FROM employee WHERE age <> 20;

   以上查询语句无法使用索引进行优化，因为 MySQL 无法使用索引区分 age 不等于 20 和 age 等于 21、22 等等。

3. 使用 OR 操作符

   如果查询语句中包含 OR 操作符，并且 OR 前后涉及到了不同的字段，那么 MySQL 无法使用索引进行优化。例如：

   1	SELECT * FROM employee WHERE age = 20 OR salary = 10000;

   以上查询语句无法使用索引进行优化，因为 OR 前后涉及到了不同的字段。

4. LIKE 操作符以通配符开头

   如果查询条件中使用了 LIKE 操作符，并且通配符在开头，那么 MySQL 无法使用索引进行优化。例如：

   1	SELECT * FROM employee WHERE name LIKE '% 张三';

   以上查询语句无法使用索引进行优化，因为通配符在开头，不满足最左匹配原则。

5. 字符串类型字段与数字进行比较

   如果查询条件中将字符串类型字段与数字进行比较，那么 MySQL 无法使用索引进行优化。例如：

   1	SELECT * FROM employee WHERE name = 123;

   以上查询语句无法使用索引进行优化，因为 name 字段是字符串类型，而 123 是一个数字。

总之，了解这些索引失效的情况对于我们优化数据库查询至关重要，在实际应用中，我们需要根据具体的业务场景来选择是否使用索引以及如何定义索引。