索引优化

索引覆盖

尽量使用索引覆盖，减少回表操作，提高查询效率。

索引覆盖查询只通过索引就可以得到所需的数据，而不必读取数据行。

回表概念

为了更好地理解回表这个概念，我们首先需要了解InnoDB的索引结构。

InnoDB使用B+树索引，并且通常会有两种类型的索引：

主键索引（聚簇索引）：每张InnoDB表都有一个特殊的索引称为聚簇索引，用于存储行的数据。聚簇索引通常就是主键索引，但如果没有定义主键，InnoDB会选择一个唯一的非空索引代替，否则隐式创建一个主键作为聚簇索引。
辅助索引（非聚簇索引）：除了聚簇索引之外的所有索引都是辅助索引。辅助索引的叶子节点存储的是主键值，而不是行的数据。

回表查询发生在这样的场景中：

当你通过辅助索引进行查询时，MySQL可以快速定位到对应的索引条目。
然而，由于辅助索引的叶子节点不包含完整的行数据，仅包含主键值，所以MySQL需要再次使用这些主键值在聚簇索引中查找完整的行数据。这个“再次查找”的过程就称为“回表”。

简单来说，回表查询就是先通过辅助索引找到主键值，再通过主键值在聚簇索引中找到完整的行记录。

索引覆盖

索引覆盖（Index Covering）指的是一个索引包含了查询所需的所有列，从而使得数据库系统在执行查询时能够仅通过索引就能够得到查询结果，而无需访问数据表中的实际行数据。这种情况下，查询的性能可以得到显著提升，因为它减少了磁盘I/O操作。

在MySQL中，索引覆盖可以应用于辅助索引（非聚簇索引）。当查询的SELECT子句中的所有列都包含在同一个辅助索引中时，MySQL就可以使用这个索引来直接获取查询结果，这就是索引覆盖。

下面通过一个例子来说明索引覆盖的概念：

假设有一个名为users的表，其中包含以下列：id（主键），username，email，last_login_time。这个表上有两个索引：一个是以id为主键的聚簇索引，另一个是以username和email为列的辅助索引。

sql

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50),
    email VARCHAR(100),
    last_login_time TIMESTAMP
);
CREATE INDEX idx_username_email ON users (username, email);

现在，我们执行以下查询：

sql

SELECT username, email FROM users WHERE username = 'john';

这个查询只需要username和email两列的数据，而这两个列恰好都包含在辅助索引idx_username_email中。因此，MySQL可以仅通过扫描这个索引来获取查询结果，而无需访问users表中的实际行数据。这种情况就是索引覆盖。

分析索引覆盖

查看查询执行计划：
- 使用EXPLAIN语句是分析索引覆盖的第一步。EXPLAIN会显示MySQL如何执行查询，包括它是否使用了索引，以及是否进行了索引覆盖。
- 例如：EXPLAIN SELECT username, email FROM users WHERE username = 'john';
检查索引使用情况：
- 在EXPLAIN的结果中，查看type列，如果值是index，则表示MySQL使用了索引来检索数据，但这并不一定意味着发生了索引覆盖。如果是range或ref，则可能发生了索引覆盖，具体取决于查询条件。
- 查看key列，确定查询实际使用了哪个索引。
- 查看Extra列，如果出现了Using index，则表示查询使用了索引覆盖。
检查索引列与查询列的匹配情况：
- 确认查询中使用的列是否都包含在索引中。如果查询的所有列都包含在同一个索引中，那么索引覆盖就可能发生。
- 如果查询中有不在索引中的列，那么就需要回表查询来获取这些列的值，这就不是索引覆盖。

索引覆盖优化场景

仅包含索引列的聚合查询：
对于只涉及索引列的聚合函数查询（如COUNT、SUM、AVG等），如果索引包含了聚合函数所需的所有列，则可以使用索引覆盖。
仅包含索引列的排序和分组查询：
当查询需要对索引列进行排序（ORDER BY）或分组（GROUP BY）时，如果索引包含了排序或分组所需的所有列，则可以使用索引覆盖。
EXISTS 和 IN 子查询：
在某些情况下， EXISTS 和 IN 子查询可以使用索引覆盖。如果子查询能够仅通过索引列来执行，那么可以避免访问主表的数据行。
JOIN 操作：
在JOIN操作中，如果关联的列是索引的一部分，并且查询只选择索引列，那么可以使用索引覆盖来优化JOIN。
限制条件的查询：
当查询包含限制条件（如WHERE子句中的等值或范围条件），并且这些条件列是索引的一部分时，可以使用索引覆盖。

索引下推

索引下推（Index Condition Pushdown，ICP）是MySQL 5.6及其以后版本引入的一种优化查询的技术。它允许在存储引擎层面对索引进行过滤，从而减少从存储引擎检索的数据量，提高查询性能。

在没有索引下推的情况下，当一个查询使用辅助索引（非聚簇索引）时，存储引擎会根据索引条件检索出所有匹配的索引条目，然后根据这些索引条目回表（即聚簇索引）来获取完整的行数据。接着，服务器层会根据查询中的所有条件对获取到的行数据进行进一步的过滤。

而在启用了索引下推的情况下，服务器层会将部分WHERE子句的条件“下推”到存储引擎层面。

存储引擎在访问索引时，会先使用这些条件对索引条目进行过滤，只有满足条件的索引条目才会被用来回表获取完整的行数据。这样，回表操作的数量就会减少，因为很多不满足条件的行在早期阶段就被过滤掉了。

下面通过一个例子来说明索引下推的概念：

假设有一个名为users的表，其中包含以下列：id（主键），username，email，age。这个表上有两个索引：一个是以id为主键的聚簇索引，另一个是以username和age为列的辅助索引。

sql

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50),
    email VARCHAR(100),
    age INT
);
CREATE INDEX idx_username_age ON users (username, age);

现在，我们执行以下查询：

sql

SELECT * FROM users WHERE username = 'john' AND age > 30;

在启用索引下推的情况下，存储引擎会先使用username = 'john'这个条件在辅助索引上过滤出所有username为'john'的索引条目。然后，存储引擎会使用age > 30这个条件对这些索引条目进行进一步的过滤。只有满足这两个条件的索引条目才会被用来回表获取完整的行数据。

索引下推的分析

分析索引下推（Index Condition Pushdown，ICP）的使用和效果涉及以下几个步骤：

使用EXPLAIN语句：
使用EXPLAIN语句来查看查询的执行计划。EXPLAIN会显示MySQL如何执行查询，包括是否使用了索引下推。
例如：EXPLAIN SELECT * FROM users WHERE username = 'john' AND age > 30;
检查执行计划：
在EXPLAIN的结果中，查看Extra列。如果出现了Using index condition，则表示查询使用了索引下推。
查看key列，确定查询实际使用了哪个索引。
分析索引使用情况：
查看type列，了解MySQL是如何访问索引的（例如，range、ref、eq_ref等）。这可以帮助你理解索引下推是如何影响查询性能的。
比较性能：
使用EXPLAIN ANALYZE（MySQL 8.0及以上版本）或EXPLAIN EXTENDED（MySQL 5.6和7.x版本）来获取更详细的执行信息，包括实际执行的行数和花费的时间。

比较开启和关闭索引下推时的查询性能。可以通过设置系统变量optimizer_switch来控制索引下推的开启和关闭。

参考资料：
https://zhuanlan.zhihu.com/p/401198674

最左前缀组合索引

创建组合索引时，要遵循最左前缀原则，避免索引失效。

t_user_role 表已经有一个由 USER_ID 和 ROLE_ID 两列组成的组合索引 user_role_idx

符合最左前缀原则的查询SQL语句：

sql

SELECT * FROM t_user_role WHERE USER_ID = 'user_100' AND ROLE_ID = 'role_10';

这个查询首先使用了 USER_ID，然后是 ROLE_ID，这与组合索引 user_role_idx 的列顺序完全一致，因此将能够有效利用索引。

不符合最左前缀原则的查询SQL语句：

sql

SELECT * FROM t_user_role WHERE ROLE_ID = 'role_10';

索引跳跃扫描

索引跳跃扫描是MySQL 8.0.13版本引入的一项优化特性，这项技术允许在不满足最左前缀原则的情况下，仍然能够利用联合索引进行查询，从而减少不必要的全表扫描，提高查询效率。

当查询不包含联合索引的最左列时，MySQL优化器会内部执行对最左列的DISTINCT操作，然后为每个唯一的最左列值分别执行查询，并使用UNION合并结果。

索引跳跃扫描特别适用于联合索引中左侧列的唯一值较少的情况。例如，在一个用户表中，性别和年龄组成的联合索引，性别列只有“男”和“女”两个唯一值，此时即使查询条件只包含年龄，也可以利用索引跳跃扫描优化查询。

假设有一个联合索引（gender, age），其中gender的唯一值很少。查询SELECT * FROM users WHERE age > 25; 会被MySQL优化器转换为：

sql

SELECT * FROM users WHERE gender = 'Male' AND age > 25
UNION ALL
SELECT * FROM users WHERE gender = 'Female' AND age > 25;

最左列的唯一值（基数）不能太多，否则索引跳跃扫描的效率会大幅下降，甚至不如全表扫描。一般来说，如果最左列的唯一值超过几百个，这个优化可能就不再适用。

索引跳跃扫描不能用于涉及GROUP BY和ORDER BY的查询，也不能跨表使用。同时，查询条件必须是常量，包括使用IN()运算符的情况。

避免索引失效

避免在查询中使用函数、计算等操作，导致索引失效。

避免在WHERE子句中使用函数：

错误示例（使用了函数）：

sql

SELECT * FROM t_user WHERE LEFT(USERNAME, 3) = 'abc';

正确示例（直接使用列）：

sql

SELECT * FROM t_user WHERE USERNAME LIKE 'abc%';

避免在WHERE子句中对列进行计算：
错误示例（进行了计算操作）：
sql
```
SELECT * FROM t_user WHERE AGE + 5 = 30;
```
正确示例（直接使用列值）：
sql
```
SELECT * FROM t_user WHERE AGE = 25;
```
避免在WHERE子句中使用非SARGable表达式：
SARGable是一个数据库查询优化术语，它是"Search ARGument ABLE"的缩写，指的是那些可以被数据库搜索引擎用作搜索参数的表达式。简单来说，如果一个表达式是SARGable的，那么它可以有效地利用索引来提高查询性能。
错误示例（使用了非SARGable表达式，即不能直接使用索引的表达式）：
sql
```
SELECT * FROM t_user WHERE SUBSTRING(USER_ID, 1, 5) = '12345';
```
正确示例（直接使用列值）：
sql
```
SELECT * FROM t_user WHERE USER_ID LIKE '12345%';
```

避免在JOIN条件中使用函数：

错误示例（在JOIN条件中使用函数）：

sql

SELECT * FROM t_user u JOIN t_user_role ur ON u.USER_ID = ur.USER_ID AND CHAR_LENGTH(u.USER_ID) = 32;

正确示例（直接使用列值进行JOIN）：

sql

SELECT * FROM t_user u JOIN t_user_role ur ON u.USER_ID = ur.USER_ID;

使用索引友好的操作符：
当需要进行模糊查询时，应尽可能使用前缀匹配，这样可以使用到索引：
sql
```
SELECT * FROM t_user WHERE USERNAME LIKE 'John%';
```
而不是使用全模糊匹配：
sql
```
SELECT * FROM t_user WHERE USERNAME LIKE '%John%';
```
少用不等空值和OR： 应尽量避免使用不等空值（IS NULL或IS NOT NULL）和OR操作符，因为这些操作通常会使得索引失效，导致全表扫描，从而降低查询效率。
避免使用不等空值： 错误示例（使用了不等空值）：
sql
```
SELECT * FROM t_user WHERE AGE IS NULL;
```
考虑改写为使用COALESCE或者CASE语句，如果可能的话，将NULL值转换为一个具体的值，这样可能可以利用索引：
sql
```
SELECT * FROM t_user WHERE COALESCE(AGE, 0) = 0;
```
或者，在设计数据库时，可以考虑使用默认值，避免出现NULL。
避免使用OR操作符：
错误示例（使用了OR操作符）：
sql
```
SELECT * FROM t_user WHERE AGE = 25 OR AGE = 30;
```
如果可能，可以使用IN操作符来改写查询，IN操作符通常比OR操作符更有效：
sql
```
SELECT * FROM t_user WHERE AGE IN (25, 30);
```
如果OR条件涉及不同的列，那么可能需要考虑使用UNION来替代：
sql
```
SELECT * FROM t_user WHERE AGE = 25
UNION
SELECT * FROM t_user WHERE AGE = 30;
```

非唯一索引change buffer优化

对于写多读少的场景，可以选择普通索引而不要唯一索引，因为普通索引可以使用change buffer进行优化。Change Buffer可以通过延迟索引页的写入，减少磁盘I/O，从而提升写入性能。

Change buffer是一种特殊的缓冲区，位于内存中，用于缓存对二级索引（非主键索引）的变更操作，这些变更操作包括INSERT、UPDATE、DELETE。

当一个二级索引页不在缓冲池（Buffer Pool）中时，对它的写操作不会直接在磁盘上进行。相反，这些操作会被记录在change buffer中。

当后续的读操作需要访问这个索引页时，或者系统空闲时，change buffer中的记录会被合并到实际的索引页中，这时才会发生磁盘I/O。

通过这种方式，change buffer减少了立即写入磁盘的需要，从而减少了磁盘I/O次数，尤其是在批量写入操作时。

唯一索引要求索引列中的每个值都是唯一的，在执行INSERT或UPDATE操作时，数据库必须立即检查索引列的唯一性约束，确保没有重复的值。由于需要立即验证唯一性，唯一索引的写操作不能利用change buffer来延迟磁盘I/O。

SQL语句优化

分页查询优化

标签法

通常分页查询会使用LIMIT和OFFSET子句来实现。例如，如果我们想获取用户表t_user中的第2页数据，每页显示10条记录，SQL查询可能是这样的：

sql

SELECT * FROM t_user ORDER BY USER_ID LIMIT 10 OFFSET 10;

在主键自增的情况下，我们可以通过记录上一次查询的最大主键值来优化。假设我们已经知道上一页的最大USER_ID是'user_100'，那么查询可以改写为：

sql

SELECT * FROM t_user WHERE USER_ID > 'user_100' ORDER BY USER_ID LIMIT 10;

这种方法通常会比使用OFFSET更高效，因为它避免了数据库从头开始扫描所有行。

延迟关联法

延迟关联是一种通过两步查询来优化分页性能的方法，特别适用于大数据量的情况。它的核心思想是先使用索引查询出分页所需的主键 ID，然后再通过这些 ID 获取完整的记录。这避免了直接使用 OFFSET 带来的性能问题。

以下SQL语句看似从表中检索10条记录，但实际上它从表中检索100010条记录，并丢弃前100000条记录以获得所需的10条记录：

sql

EXPLAIN SELECT * FROM Students ORDER BY name LIMIT 100000, 10;

随着结果集的深入，查询性能会逐渐下降。为了优化这种查询，我们可以先使用覆盖索引查询出所需的10条记录的主键，然后用这10条记录主键进行连接查询。优化后的SQL语句如下：（name建有索引）

sql

EXPLAIN SELECT * FROM Students t1 JOIN 
(SELECT id FROM Students ORDER BY `name` LIMIT 100000, 10) t2 
ON t1.id = t2.id;

优化原理讲解：

覆盖索引：在上述优化中，我们首先使用覆盖索引查询出10条记录的主键。覆盖索引是指查询的列恰好是索引的一部分（这里是主键，必是），这样数据库就不需要回表来检索其他列。
连接查询：在获取到10条记录后，我们使用这些记录的ID与原表进行连接查询。由于ID字段是主键，所以这个查询会非常快。

避免使用 `SELECT *`

如果表结构发生变化（例如添加或删除列），使用 SELECT * 的查询可能会返回不同的结果集，这可能会导致应用程序出现问题。指定列可以避免这种不确定性。

无论应用程序是否使用这些列，都需要将它们接收到内存中，可能会无谓地消耗大量内存。

使用SELECT *的查询语句可能导致优化器放弃使用覆盖索引策略，需要进行回表或全表扫描。

使用SELECT *会降低查询的可读性，开发人员需要查看表定义来确定查询了哪些数据。

合并insert语句

当需要插入多条记录时，合并多条INSERT语句为一条。例如，我们想要向t_user表中插入三条新记录，可以这样做：

sql

INSERT INTO t_user (USER_ID, USERNAME, PASSWORD, AGE) VALUES
('user_100', 'Alice', 'password_1', 25),
('user_101', 'Bob', 'password_2', 30),
('user_102', 'Charlie', 'password_3', 22);

另外，如果我们按照主键的顺序插入记录，可以避免索引页的分裂，从而提高插入效率。

小表驱动大表

大小表概念

当我们执行两个表的连接操作时，数据库优化器需要决定哪个表作为驱动表（也称为外层循环表）来遍历，以及哪个表作为被驱动表（也称为内层循环表）来匹配。

小表：指的是在连接操作中预计返回较少行数的表。
大表：指的是在连接操作中预计返回较多行数的表。

小表驱动大表意味着让行数较少的表（小表）作为驱动表，而让行数较多的表（大表）作为被驱动表。

连接查询驱动

在使用LEFT JOIN、RIGHT JOIN时，SQL标准明确指定了左表和右表分别为驱动表和被驱动表。而在INNER JOIN或未明确指定的情况下，MySQL这样的数据库系统会默认选择数据量较小的表作为驱动表。

在连接查询中，即便驱动表有索引，也不会被使用，而是直接遍历，可以认为是 $O (n)$ 。

但是，被驱动表如果建立索引，并且该索引能被连接条件利用，那么查询效率会大幅提升，可以认为是 $O (l o g n)$ 。通过EXPLAIN工具分析查询，可以看到只有被驱动表在使用索引。

假设小表数据量是 $n$ ，大表数据量是 $N$ 。

如果小表是驱动表，那么对于小表的每一行，数据库优化器都会去大表中 $O (l o g N)$ 查找匹配的行。因为小表的行数少 $O (n)$ ，所以总的匹配次数会少 $O (n l o g N) < O (N l o g n)$ 。

IN 操作符驱动

IN 操作符允许我们在 WHERE 子句中指定多个可能的匹配值。

当使用 IN 时，数据库通常会执行以下步骤：

执行子查询：如果 IN 后面跟的是一个子查询，数据库首先会执行这个子查询，并将结果集放入内存中。
匹配主查询：然后，数据库会将主查询的每一条记录与子查询的结果集进行比较，筛选出符合的记录。

例子：

sql

SELECT *
FROM A
WHERE A.id IN (SELECT id FROM B);

子查询作为驱动表，先被执行，外表作为被驱动表。

当 IN 子句中的表（B）数据量较少时，将小表放在 IN 后面，可以减少对大表（A）的扫描次数。

如果 IN 子句中的子查询能够返回一个小的结果集，并且大表（A）在相关列上有索引，那么数据库可以利用这个索引快速进行匹配。

EXISTS 操作符驱动

EXISTS 是一个布尔操作符，用于检查子查询是否返回任何行。

如果子查询返回至少一行，EXISTS 返回 TRUE，否则返回 FALSE。

当使用 EXISTS 时，数据库通常会执行以下步骤：

遍历主查询：数据库会遍历主查询的结果集。
执行子查询：对于主查询中的每一行，数据库都会执行一次子查询。

例子：

sql

SELECT *
FROM A
WHERE EXISTS (SELECT 1 FROM B WHERE B.id = A.id);

使用EXISTS时，外表作为驱动表，先被访问，子查询作为被驱动表。

当主查询的表（A）数据量较少时，使用 EXISTS 可以更高效，因为数据库会针对主查询的每一行执行子查询，如果子查询的大表（B）在相关列上有索引，那么数据库可以利用这个索引快速进行匹配。

EXISTS 只关心子查询是否有返回结果，而不是结果本身，因此它不需要检索子查询中的所有列，只需返回一个布尔值即可。

用连接查询代替子查询

子查询通常会产生一个中间结果集，然后主查询再使用这个结果集进行进一步的处理。相比之下，连接查询直接在多个表之间进行数据匹配，不产生中间结果集。

使用子查询时，数据库可能需要执行多次查询才能得出最终结果。而连接查询则是在一次查询中完成所有必要的操作。

以下子查询为例：

sql

SELECT column1
FROM A
WHERE column2 IN (SELECT key FROM B);

替换为连接查询：

sql

SELECT a.column1
FROM A a
INNER JOIN B b ON a.column2 = b.key;

数据库结构优化

拆分表

对于大型数据库，特别是字段非常多的表，将表分解成多个表可以提高性能。

t_user 表可能因为字段的使用频率不同而需要拆分。

例如，如果 USERNAME 和 PASSWORD 是频繁访问的字段，而 AGE 访问频率较低，则可以将 AGE 字段移动到一个单独的表中。这样做可以减少单个表的磁盘I/O，特别是在执行大量读操作时。

创建一个新的用户信息表 t_user_info 来存储不经常访问的字段：

sql

CREATE TABLE `t_user_info` (
    `USER_ID` VARCHAR(32) NOT NULL COMMENT '用户ID，主键',
    `AGE` TINYINT UNSIGNED DEFAULT NULL COMMENT '用户年龄',
    PRIMARY KEY (`USER_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='用户信息表';

从原始的 t_user 表中移除 AGE 字段：

sql

ALTER TABLE `t_user` DROP COLUMN `AGE`;

这种做法的缺点是，当需要同时访问 t_user 和 t_user_info 的数据时需要进行表连接操作，这可能会稍微增加查询的复杂性。但是由于减少了单个表的体积，读取和写入操作会变得更加高效。

建立中间表

对于经常联合查询的表，可以考虑建立中间表，提高查询效率。

例如，如果经常需要查询用户及其角色的信息，可以考虑创建一个中间表，将 t_user 和 t_role 表的信息结合起来。

创建一个新的中间表 t_user_role_info 来存储用户和角色的联合信息：

sql

CREATE TABLE `t_user_role_info` (
    `USER_ID` VARCHAR(32) NOT NULL COMMENT '用户ID',
    `USERNAME` VARCHAR(50) DEFAULT NULL COMMENT '用户名',
    `ROLE_ID` VARCHAR(32) NOT NULL COMMENT '角色ID',
    `ROLE_NAME` VARCHAR(50) NOT NULL COMMENT '角色名称',
    PRIMARY KEY (`USER_ID`, `ROLE_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='用户角色信息中间表';

将 t_user 和 t_role 表的数据插入到中间表 t_user_role_info 中：

sql

INSERT INTO `t_user_role_info` (`USER_ID`, `USERNAME`, `ROLE_ID`, `ROLE_NAME`)
SELECT u.USER_ID, u.USERNAME, r.ROLE_ID, r.ROLE_NAME
FROM t_user u
JOIN t_user_role ur ON u.USER_ID = ur.USER_ID
JOIN t_role r ON ur.ROLE_ID = r.ROLE_ID;

现在，当我们需要查询用户及其角色信息时，可以直接查询 t_user_role_info 表，而不需要进行联接操作，这样可以显著提高查询效率。

需要注意的是，中间表的数据需要定期更新，以反映原始表中的最新数据。这可以通过触发器、定时任务或其他机制来实现。如果数据更新频繁，维护中间表的成本可能会增加，因此在进行此类优化之前，应该仔细评估查询性能的提升与数据维护成本之间的权衡。

创建索引

在数据库设计中，索引是提高查询性能的关键因素之一。然而，索引的创建并不是一件简单的事情。

适合创建索引的情况

字段的数值有唯一性限制：
- 唯一性索引可以确保字段中的每个值都是唯一的，这有助于快速识别和访问特定的记录，同时避免了数据重复。
频繁作为WHERE查询条件的字段：
- 如果某个字段经常被用于查询条件，为其创建索引可以大幅提高查询效率，因为索引可以快速定位到满足条件的记录。
UPDATE或DELETE操作中的WHERE条件列：
- 在更新或删除操作中，如果WHERE子句中包含索引列，数据库可以更快地找到需要修改或删除的记录。
经常用于GROUP BY或ORDER BY的列：
- 对于需要排序或分组的列，索引可以减少排序和分组操作的成本，因为这些操作可以通过索引结构直接完成。
DISTINCT字段需要创建索引：
- 当需要对某个字段执行DISTINCT操作时，索引可以加快去重的过程，因为它可以快速识别唯一的值。
多表JOIN连接操作时，对WHERE条件创建索引，以及对用于连接的字段创建索引：
- 在多表连接查询中，对连接条件中的字段创建索引可以显著提高查询性能，因为数据库可以快速匹配表之间的关系。
使用列的类型较小的创建索引：
- 较小的数据类型（如整数）作为索引时，索引本身占用的空间更小，因此可以更快地读取和比较。
使用字符串前缀创建索引：
- 对于很长的字符串字段，创建整个字段的索引可能不划算。相反，创建字符串的前几个字符的索引可以节省空间并提高效率。
区分度高的列适合作为索引：
- 区分度高的列意味着该列中的每个值都是唯一的或者接近唯一，这样的列作为索引可以更有效地缩小搜索范围。
使用越频繁的列应放在联合索引的左侧：
- 联合索引中列的顺序很重要。最频繁使用的列放在左侧可以使得索引更加有效，因为数据库通常会从左到右使用索引。
在多个字段都要创建索引的情况下，联合索引优于单值索引：
- 联合索引覆盖了多个字段，可以在查询中同时使用这些字段，减少了数据库需要维护的索引数量，并且可以更有效地执行查询。

不适合创建索引的情况

数据量小的表最好不要使用索引：
- 对于数据量很小的表，全表扫描可能比使用索引更快，因为索引本身也需要占用空间并增加额外的查找时间。在小数据量的情况下，这种开销可能不值得。
有大量重复数据的列上不要建立索引：
- 如果一个列中有大量重复的值，那么索引的效果会很差。因为索引的目的是帮助快速定位到唯一的记录，而大量重复的值意味着索引无法有效地缩小搜索范围。
避免对经常更新的表创建过多的索引：
- 索引可以提高查询速度，但也会降低插入、更新和删除操作的速度，因为这些操作需要同时更新索引。如果一个表经常被更新，过多的索引会导致性能下降。
不建议用无序的值作为索引：
- 索引通常是基于有序的数据结构，如果索引的值是无序的，那么索引的性能会受到影响。有序的索引可以更高效地进行范围查询和排序操作。
不要定义冗余或重复的索引：
- 冗余或重复的索引是指多个索引覆盖了相同的列，这会导致不必要的磁盘空间占用和性能开销。例如，如果已经有一个联合索引包含了某个单列索引的所有列，那么单列索引就是冗余的。

`VARCHAR`字段索引优化

索引长度与区分度的关系

在创建索引时，我们通常不需要对整个VARCHAR字段进行索引。相反，我们只需要对字段的前缀进行索引。这是因为，通常情况下，字符串的前几个字符就足以区分大部分的记录。索引的长度与区分度之间存在一种矛盾关系。如果索引太短，那么它的区分度可能会降低，导致查询效率不高。如果索引太长，虽然区分度可能很高，但会消耗更多的存储空间，并且可能会减慢插入和更新操作的速度。

确定索引长度的方法

为了确定合适的索引长度，我们可以使用一个统计方法。这个方法是通过计算不同前缀长度的数量与表中总行数的比例来确定。这个比例可以用来估计索引的区分度。如果这个比例很高（比如90%以上），那么这个索引长度可能就是一个不错的选择。

以下是一个例子，假设我们有一个名为users的表，其中有一个VARCHAR(255)类型的字段email。我们想要为这个字段建立索引以提高查询效率。我们可以使用以下SQL语句来确定一个合适的索引长度：

sql

SELECT COUNT(DISTINCT LEFT(email, 20)) / COUNT(*) AS distinctiveness
FROM users;

这个查询会计算email字段前20个字符不同的比例。如果这个比例非常高（比如接近或超过90%），那么我们就可以认为为email字段的前20个字符建立索引是合理的。

创建前缀索引

一旦我们确定了合适的索引长度，我们就可以创建一个前缀索引。以下是一个例子，如何为email字段的前20个字符创建索引：

sql

CREATE INDEX idx_email ON users (email(20));

这个索引只会包含每个email字段的前20个字符，这样可以在节省空间的同时提高查询效率。

优化器优化

开启MRR优化

开启MRR（Multi-Range Read）优化，将随机磁盘读转化为顺序磁盘读，提高索引查询性能。

在MySQL中，索引通常分为聚簇索引（主索引）和辅助索引（非主索引）。聚簇索引的叶子节点包含了完整的数据行，而辅助索引的叶子节点则包含了索引列和主键列。

当我们使用辅助索引进行查询时，MySQL需要先通过辅助索引找到主键，然后再通过主键去聚簇索引中找到完整的数据行，这个过程称为“回表”。

在没有MRR优化时，如果查询需要检索多个辅助索引记录，MySQL会为每个记录单独执行一次回表操作，这些回表操作是随机磁盘I/O，因为它们访问的是不同的数据页。随机I/O比顺序I/O慢得多，因为它涉及到磁盘寻址的时间。

MRR优化通过以下步骤提高了索引查询性能：

排序键值：首先，MySQL会将查询到的辅助索引记录的主键值（或其他排序键值）提取出来，并进行排序。
顺序访问：排序后，MySQL会按照排序后的键值顺序进行回表操作。因为键值是排序的，所以MySQL可以顺序地访问磁盘上的数据页，而不是随机访问。这样可以减少磁盘I/O的次数，提高查询效率。
减少回表次数：由于排序后的键值可能是相邻的，MySQL可以利用这一点来减少回表的次数。例如，如果连续的几个键值指向同一个数据页，MySQL只需要访问一次该数据页即可获取所有相关的记录。

在MySQL中，可以通过设置系统变量optimizer_switch来控制MRR的开启和关闭：

sql

SET optimizer_switch='mrr=on'; -- 开启MRR优化
SET optimizer_switch='mrr=off'; -- 关闭MRR优化

从MySQL 8.0开始，MRR默认是开启的，所以通常不需要手动开启。

当查询使用辅助索引并且需要按照某种顺序读取数据时，MRR优化会首先按照辅助索引的顺序读取数据行，然后根据主键排序这些行，最后进行一致性回表读取数据，这样做可以将随机I/O转换为顺序I/O。

假设我们有一个使用到USER_ID的查询，这个USER_ID在t_user_role表中有辅助索引。查询看起来像这样：

sql

SELECT * FROM t_user_role WHERE USER_ID IN (/* 一系列的用户ID */);

在这种情况下，MySQL可以使用USER_ID上的辅助索引来检索数据行。由于查询中使用了IN条件，MySQL可以首先按照辅助索引的顺序读取所有的USER_ID，然后使用主键来对结果进行排序，最后进行一致性读。

如果想要查看MRR是否被使用，可以在查询前执行EXPLAIN命令：

sql

EXPLAIN SELECT * FROM t_user_role WHERE USER_ID IN (/* 一系列的用户ID */);

在EXPLAIN的结果中，我们可以看到Extra列中是否有Using MRR的信息，如果有，那就表示查询使用了MRR优化。

`JOIN BUFFER`

在MySQL中，JOIN BUFFER 是一个内存区域，用于存储在执行某些类型的连接（JOIN）操作时需要的数据。它主要用于全表扫描的情况，尤其是在没有合适的索引可用时，MySQL可能需要将整个表或部分数据加载到 JOIN BUFFER 中以完成连接操作。这种机制可以帮助减少磁盘I/O，并加速查询处理。

`JOIN BUFFER`的类型

Block Nested-Loop Join（BNL）

在标准的Nested-Loop Join（NLJ）算法中，对于驱动表（外部循环）中的每一行，数据库都会扫描被驱动表（内部循环）以找到匹配的行。如果被驱动表很大且没有索引可用，这将导致大量的磁盘I/O。

BNL算法通过使用JOIN BUFFER来减少这种磁盘I/O。它不是对驱动表的每一行都扫描被驱动表，而是将驱动表的一部分行读入JOIN BUFFER，然后一次性与被驱动表进行比较。

工作原理：

选择驱动表：MySQL选择一个较小的表作为驱动表，因为驱动表中的每一行都需要在JOIN BUFFER中暂存。
读取驱动表行到JOIN BUFFER：MySQL开始读取驱动表中的行，并将它们存储到JOIN BUFFER中。这个过程会持续直到JOIN BUFFER被填满或者驱动表的所有行都被读取。
排序和去重（可选）：为了提高效率，MySQL可能会对JOIN BUFFER中的行进行排序和去重，这取决于具体的查询和优化器决策。
扫描被驱动表：一旦JOIN BUFFER被填满，MySQL开始扫描被驱动表。对于JOIN BUFFER中的每一行，MySQL会在被驱动表上执行全表扫描，寻找匹配的行。
重复过程：如果驱动表有更多的行需要处理，MySQL会清空JOIN BUFFER，并重复上述步骤，直到所有驱动表的行都被处理。

优点：减少了被驱动表的扫描次数，从而减少了磁盘I/O。

缺点：JOIN BUFFER的大小限制了可以一次性处理的行数，可能导致多次迭代。

Batched Key Access (BKA)

BKA算法结合了索引查找和JOIN BUFFER。当JOIN条件部分可以使用索引时，BKA算法可以减少对索引的查找次数。

BKA首先使用索引来找到驱动表中的一批行，然后将这些行的键值读入JOIN BUFFER。接着，使用JOIN BUFFER中的键值批量访问被驱动表。

工作原理：

索引查找：MySQL使用索引在驱动表中找到一批行。
读取键值到JOIN BUFFER：这些行的键值被读取并存储到JOIN BUFFER中。
批量访问被驱动表：MySQL使用JOIN BUFFER中的键值对被驱动表进行批量访问，这通常是通过在JOIN BUFFER中的键值上执行范围扫描实现的。
匹配行：被驱动表返回的行与JOIN BUFFER中的行进行匹配。
重复过程：如果还有更多的行需要处理，MySQL会重复上述步骤。

优点：减少了索引查找的次数，提高了索引的使用效率。

缺点：需要驱动表和被驱动表都有可用的索引。

Hash Join（从MySQL 8.0.18开始支持）

Hash Join是一种在两个表之间执行JOIN操作的算法，它通过在内存中创建哈希表来实现快速匹配。

MySQL使用JOIN BUFFER来构建哈希表，该哈希表基于驱动表中的JOIN列。

工作原理：

读取驱动表到JOIN BUFFER：MySQL选择一个较小的表作为驱动表，并读取其所有行到JOIN BUFFER。
构建哈希表：使用JOIN BUFFER中的行构建一个哈希表。哈希表是基于JOIN条件中的列构建的。
扫描被驱动表：MySQL开始扫描被驱动表，对于每一行，使用哈希函数计算哈希值，然后在哈希表中查找匹配的行。
JOIN操作：将被驱动表的行与哈希表中的匹配行进行JOIN操作。

优点：对于大数据集，Hash Join通常比BNL和BKA更高效，因为它减少了比较次数。

缺点：哈希表需要足够的内存来存储，如果JOIN BUFFER不足以容纳整个驱动表，则可能无法使用Hash Join。

`JOIN BUFFER` 的配置

join_buffer_size
- 定义：该参数控制每个线程用于处理无索引连接操作时的 JOIN BUFFER 大小。
- 默认值：通常情况下，默认值是 256KB 或者 4KB，具体取决于 MySQL 版本和配置。
- 调整建议：
  - 如果查询经常需要执行全表扫描或没有合适的索引来支持连接条件，可以适当增加 join_buffer_size。
  - 增加 join_buffer_size 可以减少磁盘 I/O 和 CPU 消耗，但同时也会增加内存使用量。因此，要根据服务器可用内存和并发连接数来决定最佳大小。
  - 一般可以从 4MB 开始尝试，并根据实际效果逐步调整。
max_join_size
- 定义：限制一个连接操作可以处理的最大行数。当超过这个限制时，MySQL 会拒绝执行查询并返回错误。
- 默认值：默认值通常是 18446744073709551615（即 BIGINT 类型的最大值），意味着没有实际限制。
- 调整建议：
  - 如果担心某些查询可能会消耗过多资源，可以设置一个合理的上限。
  - 但是，大多数情况下不需要修改此参数，除非有特定的安全或性能需求。
tmp_table_size 和 max_heap_table_size
- 定义：这两个参数分别控制临时表在内存中的最大大小。如果临时表超过了这些限制，它们将被转换为基于磁盘的表。
- 默认值：通常情况下，tmp_table_size 默认值是 16MB，而 max_heap_table_size 默认值是 16MB 或者与 tmp_table_size 相同。
- 调整建议：
  - 如果查询频繁创建临时表，并且这些临时表很大，可以考虑增加这两个参数的值。
  - 这些参数应该根据系统总内存和并发用户数量进行调整。一般来说，不要让它们占用太多内存，以免影响其他操作。
  - 通常建议 tmp_table_size 和 max_heap_table_size 设置为相同的值，以保持一致性。

架构优化

采用读写分离，主库负责写操作，从库负责读操作，提高系统吞吐量。

索引优化 ​

索引覆盖 ​

回表概念 ​

索引覆盖 ​

分析索引覆盖 ​

索引覆盖优化场景 ​

索引下推 ​

索引下推的分析 ​

最左前缀组合索引 ​

索引跳跃扫描 ​

避免索引失效 ​

非唯一索引change buffer优化 ​

SQL语句优化 ​

分页查询优化 ​

标签法 ​

延迟关联法 ​

避免使用 SELECT * ​

合并insert语句 ​

小表驱动大表 ​

大小表概念 ​

连接查询驱动 ​

IN 操作符驱动 ​

EXISTS 操作符驱动 ​

用连接查询代替子查询 ​

数据库结构优化 ​

拆分表 ​

建立中间表 ​

创建索引 ​

适合创建索引的情况 ​

不适合创建索引的情况 ​

VARCHAR字段索引优化 ​

索引长度与区分度的关系 ​

确定索引长度的方法 ​

创建前缀索引 ​

优化器优化 ​

开启MRR优化 ​

JOIN BUFFER ​

JOIN BUFFER的类型 ​

Block Nested-Loop Join（BNL） ​

Batched Key Access (BKA) ​

Hash Join（从MySQL 8.0.18开始支持） ​

JOIN BUFFER 的配置 ​

架构优化 ​

索引优化

索引覆盖

回表概念

索引覆盖

分析索引覆盖

索引覆盖优化场景

索引下推

索引下推的分析

最左前缀组合索引

索引跳跃扫描

避免索引失效

非唯一索引change buffer优化

SQL语句优化

分页查询优化

标签法

延迟关联法

避免使用 `SELECT *`

合并insert语句

小表驱动大表

大小表概念

连接查询驱动

IN 操作符驱动

EXISTS 操作符驱动

用连接查询代替子查询

数据库结构优化

拆分表

建立中间表

创建索引

适合创建索引的情况

不适合创建索引的情况

`VARCHAR`字段索引优化

索引长度与区分度的关系

确定索引长度的方法

创建前缀索引

优化器优化

开启MRR优化

`JOIN BUFFER`

`JOIN BUFFER`的类型

Block Nested-Loop Join（BNL）

Batched Key Access (BKA)

Hash Join（从MySQL 8.0.18开始支持）

`JOIN BUFFER` 的配置

架构优化