21优化

索引优化

5.1. 性能分析（explain）

很多现象需要在5.5才能演示成功。

5.1.1. explain是什么?

模拟优化器查看执行计划

使用EXPLAIN关键字可以模拟优化器执行SQL查询语句，从而知道MySQL是如何处理你的SQL语句的。分析你的查询语句或是表结构的性能瓶颈

5.1.2. explain能干什么？

表的读取顺序
那些索引可以使用
数据读取操作的操作类型
那些索引被实际使用
表之间的引用
每张表有多少行被物理查询

5.1.3. explain怎么玩？

explain + SQL语句

5.1.4. 各字段解释

5.1.4.1. id查询序列号

select查询的序列号，包含一组数字，表示查询中执行select子句或操作表的顺序

关注点：每个id号码，表示一趟独立的查询。一个sql 的查询趟数越少越好。

四种情况：

id相同，执行顺序由上至下。例如上图
id不同，如果是子查询，id的序号会递增，id值越大优先级越高，越先被执行
id既有相同又有不同
id为null最后执行

5.1.4.2. select_type

查询的类型，主要是用于区别普通查询、联合查询、子查询等的复杂查询

查询类型	描述
`SIMPLE`	简单查询，查询中不包含子查询或者UNION。
`PRIMARY`	主查询，查询中若包含子查询，则最外层查询被标记为PRIMARY 案例1
`DERIVED`	在包含派生表（子查询在from子句中）的查询中，MySQL会递归执行这些子查询，把结果放在临时表里。EXPLAIN select * from t1, (select 2) as v;
`SUBQUERY`	子查询，在SELECT或WHERE列表中包含了子查询。案例2
`DEPENDENT SUBQUERY`	如果包含了子查询，查询语句不能被优化器转换为连接查询，并且子查询是相关子查询（子查询基于外部数据列)。案例3
`UNCACHEABLE SUBQUERY`	表示一个子查询（subquery）被标记为无法缓存。在某些数据库管理系统中，查询优化器会尝试将查询结果缓存起来以提高性能，但对于被标记为UNCACHEABLE的子查询，查询优化器将不会缓存其结果。案例4
`UNION`	对于包含UNION或者UNION ALL的查询语句，除了最左边的查询是PRIMARY，其余的查询都是UNION。案例5
`UNION RESULT`	UNION会对查询结果进行查询去重，MYSQL会使用临时表来完成UNION查询的去重工作，针对这个临时表的查询就是”UNION RESULT”。案例5

5.1.4.3. table

显示这一行的数据是关于哪张表的

5.1.4.4. partitions

代表分区表中的命中情况，非分区表，该项为null

5.1.4.5. type*

😽😽😽

type显示的是访问类型，用于描述查询引擎在执行查询时使用的访问方法。是较为重要的一个指标

一般来说，保证查询至少达到range级别，最好能达到ref。

常见：system > const > eq_ref > ref > range > index > ALL

system：表示只有一行数据的表，这是最快的访问方式

const：针对主键或唯一索引的等值查询扫描，最多只返回一行数据。const 查询速度非常快, 因为它仅仅读取一次即可。例如：

1	`explain select * from t1 where t1.id=1`

eq_ref：表示使用了连接（join）查询，并且连接条件是通过唯一索引进行的等值比较。例如：

1	`explain select * from t1,t2 where t1.id=t2.id`

ref：表示使用了非唯一索引进行的等值比较，可能返回多个匹配的行。例如：（content1列创建了单值索引）

1	`explain select * from t4 where t4.content1 = ''`

range：只检索给定范围的行，一般就是在你的where语句中出现了between、<、>、!=等的查询。这种范围扫描索引扫描比全表扫描要好，因为它只需要开始于索引的某一点，而结束语另一点，不用扫描全部索引。例如：

1	`explain select * from t1 where t1.id<10`

index：表示全索引扫描，即遍历整个索引树来获取结果，而不需要回表查找数据。

1	`explain select id from t1`

all：Full Table Scan，将遍历全表以找到匹配的行。

1	`explain select * from t2`

5.1.4.6. possible_keys

显示当前查询可能用到的索引，一个或多个。查询涉及到的字段上若存在索引则列出，但不一定被查询实际使用

5.1.4.7. key*

keys表示实际使用的索引。如果为NULL，则没有使用索引

5.1.4.8. key_len*

表示索引使用的字节数，根据这个值可以判断索引的使用情况，检查是否充分利用了索引，针对联合索引值越大越好。

如何计算：

先看索引上字段的类型 + 长度。比如 int=4；varchar(20) =20；char(20) =20

字符串：char(n)=n；varchar(n)=n

数值类型：tinyint=1；smallint=2；int=4；bigint=8

时间类型：date=3；timestamp=4；datetime=8
如果是varchar或者char这种字符串字段，视字符集要乘不同的值。比如：utf-8要乘 3或者4，GBK要乘2
varchar要额外加2个字节
允许为NULL的字段额外加1个字节

索引字段最好不要为NULL，因为NULL让统计更加复杂，并且需要额外一个字节的存储空间。

备注：key_len不包含order by/group by使用到的索引列。

5.1.4.9. ref

ref字段表示连接操作中使用的索引列

5.1.4.10. rows*

rows字段的值是一个估计值，表示查询操作在执行时可能会扫描的行数。这个估计值是根据统计信息和查询优化器的算法得出的，并不是实际执行时的准确值。它可以用来帮助我们评估查询的性能和效率。rows字段的值越小越好!

5.1.4.11. filtered

最后查询出来的数据占所有服务器端（server）检查行数（rows）的百分比。值越大越好。

5.1.4.12. extra*

不适合在其他列中显示但十分重要的额外信息

using filesort和 Backward index scan 是Order By常见的两种排序算法

using filesort：当查询涉及到排序操作时，MySQL可能会使用filesort算法来对结果进行排序。

这通常发生在没有使用索引或无法使用索引进行排序的情况下。filesort会在内存中或磁盘上创建临时文件，并对结果进行排序操作。这可能会导致性能较差，特别是对于大型结果集和复杂的排序操作。
Backward index scan：当查询涉及到反向索引扫描时，MySQL可能会使用Backward index scan进行索引访问。反向索引扫描是指按照索引的逆序进行扫描，通常用于ORDER BY DESC等逆序排序操作。这在某些情况下可能会导致较慢的查询性能，因为索引的物理结构并不适合逆序扫描。

在实际的查询优化中，应尽量避免或减少出现using filesort和Backward index scan的情况，以提高查询性能

Using temporary：使用了临时表保存中间结果，常见于排序 order by 和分组查询 group by。group by和order by同时存在，且作用于不同的字段时，就会建立临时表，以便计算出最终的结果集。

ALTER table t_emp DROP index idx_age_name;
SHOW INDEX FROM t_emp;

explain  select deptId,count(*) from t_emp where deptId=3 group by name order by deptId;

USING index：使用了覆盖索引，表示直接访问索引就足够获取到所需要的数据，不需要通过索引回表（非常好）

Using where：使用了where，但在where上有字段没有创建索引。也可以理解为如果数据从引擎层被返回到server层进行过滤，那么就是Usingwhere。

impossible where：where子句的值总是false。

1	`EXPLAIN SELECT * FROM t_emp WHERE 1 != 1;`

索引下推

Using index condition: 叫作Index Condition Pushdown Optimization （索引下推优化）

什么是索引下推:

是MySQL中的一种查询优化技术，用于提高查询性能和减少回表次数。

ICP 开启时 (默认)：MySQL 会把二级索引里能过滤的条件下推到存储引擎，在索引层就过滤一部分数据，减少回表次数。也就是说直接在索引扫描阶段就完成过滤

ICP 关闭时：所有条件都在 Server 层判断，InnoDB 只返回索引匹配的数据页，Server 再去逐行判断，回表更多。

CREATE TABLE `test_index` (
  `id` int NOT NULL,
  `a` varchar(255) DEFAULT NULL,
  `b` varchar(255) DEFAULT NULL,
  `c` varchar(255) DEFAULT NULL,
  `extra` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_a_b_c` (`a`,`b`,`c`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

INSERT INTO test_index (id, a, b, c, extra) VALUES
(1, 'x', 'y1', 'z1', 'extra1'),
(2, 'x', 'y2', 'z2', 'extra2'),
(3, 'x', 'y3', 'z3', 'extra3'),
(4, 'm', 'n1', 'p1', 'extra4'),
(5, 'm', 'n2', 'p2', 'extra5'),
(6, 'm', 'n3', 'p3', 'extra6');
# 执行如下SQL语句
EXPLAIN SELECT * FROM test_index WHERE a = 'x' AND b LIKE 'y%' AND c = 'z2';

分析:

没有 ICP MySQL 在通过索引扫描时，只能用 a = ‘x’ AND b LIKE ‘y%’ 在索引层面做筛选；至于 c = ‘z2’ 这个条件，要回表之后再判断意味着会拿到更多的候选行，再去表里逐个比对，效率低

有 ICP MySQL 可以在存储引擎层就利用联合索引 (a, b, c)，把 c = ‘z2’ 也提前判断掉，减少了回表的次数也就是说，ABC 三个条件都能在索引扫描时用上

5.1.5. 小结

表的读取顺序：id（趟数越少越好）

那些索引可以使用：possible_keys

数据读取操作的操作类型：type（system > const > eq_ref > ref > range > index > ALL）

哪些索引被实际使用：key

哪些索引列被实际使用：key_len

表之间的引用：table

每张表有多少行被物理查询：rows（越小越好）

额外的重要信息：extra

避免：Using filesort（排序）、Using temporary（分组）

5.1.6. explain的局限性

EXPLAIN不考虑各种Cache
EXPLAIN不能显示MySQL在执行查询时所作的优化工作
EXPLAIN不会告诉你关于触发器、存储过程的信息或用户自定义函数对查询的影响情况
部分统计信息是估算的，并非精确值

5.3. 单表优化

MySQL中提高性能的一个最有效的方式是对数据表设计合理的索引。索引提供了高效访问数据的方法，并且加快查询的速度，因此索引对查询的速度有着至关重要的影响。

我们创建索引后，用不用索引，最终是优化器说了算。优化器会基于开销选择索引，怎么开销小就怎么来。不是基于规则，也不是基于语义。

另外SQL语句是否使用索引，和数据库的版本、数据量、数据选择度（查询中选择的列数）运行环境都有关系

5.3.1. 索引优化原则

在索引列上有其他操作（计算、函数、(自动or手动)类型转换）导致索引失效
like以通配符开头（’%abc…’）导致索引失效
**不等于（!=或者<>）**导致索引失效
is not null 也无法使用索引，但是is null是可以使用索引的
字符串不加单引号导致索引失效
1. 以下两个sql，哪个写法更好：
1
2
3
4
# 创建索引 create index idx_name on emp(name); EXPLAIN SELECT * FROM emp WHERE emp.name LIKE 'abc%'; EXPLAIN SELECT * FROM emp WHERE LEFT(emp.name,3)='abc';
sql访问类型range > ALL；使用索引idx_emp_name > NULL；使用索引长度63 > NULL; 扫描行数25 < 498951
把第一个sql的like查询条件改成‘%abc%’，会怎样呢？
1
EXPLAIN SELECT * FROM emp WHERE emp.name LIKE '%abc%';
可以发现改成’%abc%’之后，第一个sql失去了索引优势，走了全表扫描。

注意：Alibaba《Java开发手册》【强制】页面搜索严禁左模糊或者全模糊，如果需要请走搜索引擎来解决。

再来看这两个sql：不等于(!=或者<>)

1
2
3

create index idx_age on emp(age);
EXPLAIN SELECT  * FROM emp WHERE emp.age=30;
EXPLAIN SELECT  * FROM emp WHERE emp.age!=30;

is not null和is null

1 2	`EXPLAIN SELECT * FROM emp WHERE emp.name is null ; EXPLAIN SELECT * FROM emp WHERE emp.name is not null;`

注意： 当数据库中的数据的索引列的NULL值达到比较高的比例的时候，即使在IS NOT NULL 的情况下 MySQL的查询优化器会选择使用索引，

此时type的值是range（范围查询）

-- 将 id>20000 的数据的 name 值改为 NULL
   UPDATE emp SET `name` = NULL WHERE `id` > 20000;
   -- 执行查询分析，可以发现 IS NOT NULL 使用了索引
   -- 具体多少条记录的值为NULL可以使索引在IS NOT NULL的情况下生效，由查询优化器的算法决定
   EXPLAIN SELECT * FROM emp WHERE emp.name IS NOT NULL;

测试完将name的值改回来

1	UPDATE emp SET `name` = rand_string(6) WHERE `id` > 20000;

字符串加引号( 类型转换导致索引失效)

1
2
3

EXPLAIN SELECT  * FROM emp WHERE emp.name = '123';

EXPLAIN SELECT  * FROM emp WHERE emp.name = 123;

5.3.2. 组合索引原则

全值匹配我最爱
符合最左原则：不跳过索引中的列。
如果where条件中是OR关系，加索引不起作用
存储引擎不能使用索引中范围条件右边的列

首先删除之前创建的索引：

1 2	`-- 尽量在navicat执行直接在linux执行会很慢 CALL proc_drop_index("fydb","emp");`

全值匹配我最爱

1
2
3

SELECT * FROM emp WHERE age=30 and deptId=1 and name='abc';
create index idx_age_deptId_name on emp(age, deptId, name);
SELECT * FROM emp WHERE age=30 and deptId=1 and name='abc';

最左匹配原则

1 2	`SELECT * FROM emp WHERE age=30 and deptId=1; SELECT * FROM emp WHERE deptId=1 and name='abc';`

OR关联

1
2
3

explain SELECT * FROM emp WHERE age=30 and deptId=1 and name='abc';

explain SELECT * FROM emp WHERE age=30 and deptId=1 OR name='abc';

范围条件右边的列

1
2
3

explain SELECT * FROM emp WHERE age=30 and deptId=1 and name='abc';

explain SELECT * FROM emp WHERE age=30 and deptId>10000 and name='abc';

5.3.3. 小结

一般性建议：

对于单键索引，尽量选择针对当前query过滤性更好的索引
在选择组合索引的时候，当前Query中过滤性最好的字段在索引字段顺序中，位置越靠前越好。
在选择组合索引的时候，尽量选择可以能够包含当前query中的where字句中更多字段的索引
在选择组合索引的时候，如果某个字段可能出现范围查询时，尽量把这个字段放在索引次序的最后面
书写sql语句时，尽量避免造成索引失效的情况

5.4. 关联查询优化

接下来再次创建两张表，并分别导入数据：

CREATE TABLE IF NOT EXISTS `class` (
	`id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
	`card` INT(10) UNSIGNED NOT NULL,
	PRIMARY KEY (`id`)
);
CREATE TABLE IF NOT EXISTS `book` (
	`bookid` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
	`card` INT(10) UNSIGNED NOT NULL,
	PRIMARY KEY (`bookid`)
);

INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
 
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));

此时class有16条数据，book有20条数据。

在MySQL中，驱动表和被驱动表的选择依赖于查询的类型。例如，在LEFT JOIN操作中，左表（left table）是驱动表，而右表（right table）是被驱动表。相反，在RIGHT JOIN中，右表成为驱动表，左表则是被驱动表。对于INNER JOIN，MySQL通常会选择数据量较小的表作为驱动表，因为这样可以减少查询时的循环次数，提高效率。

5.4.1. 关联案例

explain分析一下几个sql：其中左外连接中驱动表是左表右外连接驱动表是右表

EXPLAIN SELECT * FROM class LEFT JOIN book ON class.card = book.card;

EXPLAIN SELECT * FROM class RIGHT JOIN book ON class.card = book.card;

EXPLAIN SELECT * FROM class INNER JOIN book ON class.card = book.card;

没有创建索引前的测试：进行了进行了全表扫描，查询次数为16*20

给book.card创建索引：

1	`create index idx_book_card on book(card);`

然后explain分析：

删除旧索引，添加新索引：

# 删除旧索引 + 新建 + 第3次explain
call proc_drop_index("fydb","book");

create index idx_class_card on class(card);

再次explain分析：

同时给两张表的card字段添加索引：（class(card)索引已有：index_class_card，只需给book（card）添加索引）

1	`create index idx_book_card on book(card);`

最后explain分析：

结论：针对两张表的连接条件涉及的列，索引要创建在被驱动表上，驱动表尽量是小表

关于查询方式的选择

需求: 求所有人物对应的掌门名称(此处使用第四章创建的t_emp 和 t_dept )

方式一:三表左连接方式

-- 员工表(t_emp)、部门表(t_dept)、ceo(t_emp)表 关联查询
EXPLAIN SELECT emp.name, ceo.name AS ceoname
FROM t_emp emp
LEFT JOIN t_dept dept ON emp.deptid = dept.id
LEFT JOIN t_emp ceo ON dept.ceo = ceo.id;

一趟查询，用到了主键索引，效果最佳

方式二: 子查询方式

explain SELECT
emp.name,
(SELECT ceo.name FROM t_emp ceo WHERE ceo.id = dept.ceo) AS ceoname
FROM t_emp emp
LEFT JOIN t_dept dept ON emp.deptid = dept.id;

两趟查询，用到了主键索引，跟第一种比，效果稍微差点。

总结：能够直接多表关联的尽量直接关联，不用子查询。减少查询趟数

5.4.2. 优化建议

保证被驱动表的join字段已经被索引
left/right join 时，选择小表作为驱动表，大表作为被驱动表。
inner join 时，mysql会自己帮你把小结果集的表选为驱动表。
子查询尽量不要放在被驱动表，有可能使用不到索引。
能够直接多表关联的尽量直接关联，不用子查询。
索引要创建在被驱动表上

MySQL驱动表详解

驱动表是MySQL多表连接查询（JOIN）中的一个重要概念，指的是在JOIN操作中首先被读取和处理的表。

驱动表的核心定义

驱动表（Driving Table）：查询中首先被扫描的表，它的每一行都会去被驱动表中查找匹配数据
被驱动表（Driven Table）：查询中后续被扫描的表，依赖驱动表的中间结果进行匹配

为什么需要驱动表？

MySQL在执行JOIN查询时，通常使用嵌套循环连接（Nested Loop Join）算法：

for each row in 驱动表 {      -- 外层循环
    for each row in 被驱动表 {  -- 内层循环
        if (row满足连接条件) {
            输出匹配结果
        }
    }
}

关键点：驱动表的行数决定了外层循环的次数，因此选择合适驱动表可以大幅减少查询成本。

驱动表选择原则

1. 优先选择”小表”作为驱动表

核心原则：永远用结果集小的表驱动结果集大的表
例如：
- user表10,000条记录（小表）
- class表20条记录（大表）
- 选择class表作为驱动表，只需循环20次
- 选择user表作为驱动表，需要循环10,000次

2. 选择驱动表的判断依据

情况	选择依据
未指定联接条件	行数少的表作为驱动表
指定了联接条件	过滤后结果集小的表作为驱动表
有WHERE条件	WHERE条件过滤后结果集小的表作为驱动表

如何确定驱动表？

通过EXPLAIN查看执行计划：
1
EXPLAIN SELECT * FROM tableA JOIN tableB ON tableA.id = tableB.id;
- 排在第一行的表是驱动表
- rows列显示扫描的行数，越小越好
使用STRAIGHT_JOIN强制指定：
1
SELECT * FROM tableB STRAIGHT_JOIN tableA ON tableB.id = tableA.id;
- 强制指定tableB为驱动表，tableA为被驱动表

为什么驱动表选择如此重要？

性能差异巨大：使用大表作为驱动表可能导致查询时间从几毫秒增加到几秒甚至几分钟
I/O成本：驱动表的行数决定需要执行的内层循环次数
索引利用：被驱动表的关联字段必须有索引才能高效查询

实际案例

-- 错误示例：大表作为驱动表
SELECT * FROM users u LEFT JOIN orders o ON u.id = o.user_id;

-- 正确示例：小表作为驱动表
SELECT * FROM orders o LEFT JOIN users u ON o.user_id = u.id;

如果orders表有10万条记录，users表有1000条记录：

错误示例：需要10万次循环（每次在users表中查找匹配）
正确示例：只需1000次循环（每次在orders表中查找匹配）

优化建议

小表驱动大表：永远优先选择结果集小的表作为驱动表
被驱动表建立索引：确保关联字段有索引
使用EXPLAIN分析：查看执行计划，确认驱动表是否合理
必要时使用STRAIGHT_JOIN：当优化器选择错误时手动干预

💡 一句话记住：驱动表是”起点”，被驱动表是”终点”。选择小结果集作为起点，能大幅减少查询成本。

理解驱动表的概念并正确应用，是优化MySQL查询性能的关键一步。在实际开发中，养成使用EXPLAIN分析查询的习惯，能帮助你写出更高效的SQL。😊

5.5. 子查询优化

尽量不要使用not in 或者 not exists (此处使用第四章关联建表语句和数据)

需求：查询非掌门人的信息

方式一：

-- 查询员工，这些员工的id没在（掌门人id列表中）
-- 【查询不是CEO的员工】
explain SELECT * FROM t_emp emp WHERE emp.id NOT IN
(SELECT dept.ceo FROM t_dept dept WHERE dept.ceo IS NOT NULL);
-- mysql8之后 做了优化  演示效果不明显
explain SELECT * FROM t_emp emp WHERE emp.id  IN
(SELECT dept.ceo FROM t_dept dept WHERE dept.ceo IS NOT NULL);

5.6. 排序及分组优化

5.6.1. 无过滤不索引

准备工作

1
2
3

-- 删除采用存储过程导入数据的 emp表   中的所有的索引
-- 创建新的索引结构
CREATE INDEX idx_age_deptid_name ON emp (age,deptid,`name`);

sql演示

-- 没有使用索引：
EXPLAIN SELECT * FROM emp ORDER BY age,deptid;
-- 使用了索引：order by想使用索引，必须有过滤条件，索引才能生效，limit也可以看作是过滤条件
EXPLAIN SELECT * FROM emp ORDER BY age,deptid LIMIT 10;

执行结果如下

5.6.2. 顺序错不索引

sql演示

-- 创建了一个索引 idx_age_deptId_name  理论key_len=73

-- 会走索引type=ref  key_len = 5   extra中没有using filesort           
EXPLAIN SELECT * FROM emp WHERE age=45 ORDER BY deptid; 

-- 会走索引type=ref  key_len = 5   extra中没有using filesort   
EXPLAIN SELECT * FROM emp WHERE age=45 ORDER BY deptid, `name`; 

-- 会走索引type=ref  key_len = 5   extra出现了using filesort  获取数据的时候走索引,但是排序的时候没有走
EXPLAIN SELECT * FROM emp WHERE age=45 ORDER BY deptid, empno;

-- 会走索引type=ref  key_len = 5   extra出现了using filesort  获取数据的时候走索引,但是排序的时候没有走
EXPLAIN SELECT * FROM emp WHERE age=45 ORDER BY `name`, deptid;

-- 不会走索引type=all  key_len = 0   extra出现了using filesort using where  获取数据的时候走索引,但是--- 排序的时候没有走
EXPLAIN SELECT * FROM emp WHERE deptid=45 ORDER BY age;

5.6.3. 方向反不索引

sql演示：

-- 排序使用了索引：排序条件和索引一致，并方向相同，可以使用索引
EXPLAIN SELECT * FROM emp WHERE age=45 ORDER BY deptid DESC, `name` DESC;

-- 没有使用索引：两个排序条件方向相反
EXPLAIN SELECT * FROM emp WHERE age=45 ORDER BY deptid ASC, `name` DESC;

5.6.4. 优化演示

ORDER BY子句，尽量使用Index方式排序，避免使用FileSort方式排序

执行案例前先清除emp上的索引，只留主键

1 2	`# 查询年龄为30岁的，且员工编号小于101000的用户，按用户名称排序 SELECT * FROM emp WHERE age =30 AND empno <101000 ORDER BY NAME;`

结论：type 是 ALL,即最坏的情况。Extra 里还出现了 Using filesort,也是最坏的情况。优化是必须的。

优化思路：尽量让where的过滤条件和排序使用上索引。

现在过滤条件使用了两个字段（age，empno）排序使用了name。

我们建一个三个字段的组合索引可否？

1	`CREATE INDEX idx_age_empno_name ON emp(age,empno,NAME);`

再次explain测试：

我们发现using filesort 依然存在，所以name 并没有用到索引。

原因是因为empno是一个范围过滤，对于获取数据而言,是可以通过索引下推的方式减少回表,来快速获取要排序的数据但是在对这些数据进行排序操作的时候,没有用到索引

所以我们建一个3值索引是没有意义的
那么我们先删掉这个索引：

1	`DROP INDEX idx_age_empno_name ON emp`

为了去掉filesort我们可以把索引建成

1	`CREATE INDEX idx_age_name ON emp(age,NAME);`

也就是说empno 和name这个两个字段只能二选其一。
这样我们优化掉了 using filesort。

执行一下sql：

速度果然提高了4倍。

假如：选择创建age和empno会速度会怎样呢，自己试试有惊喜！

虽然出现了usingfilesort 表示排序的数据没有用索引进行排序 type是range表示获取数据用到了索引

1	`CREATE INDEX idx_age_name ON emp(age,empno);`

结果竟然有 filesort的 sql 运行速度，超过了已经优化掉 filesort的 sql ，而且快了好多倍。何故

原因：是所有的排序都是在条件过滤之后才执行的，所以如果条件过滤了大部分数据的话，几百几千条数据进行排序其实并不是很消耗性能，即使索引优化了排序但实际提升性能很有限。相对的 empno<101000 这个条件如果没有用到索引的话，要对几万条的数据进行扫描，这是非常消耗性能的，所以索引放在这个字段上性价比最高，是最优选择。

结论：当【范围条件】和【group by 或者 order by】的字段出现二选一时，优先观察条件字段的过滤数量，如果过滤的数据足够多，而需要排序的数据并不多时，优先把索引放在范围字段上。也可以将选择权交给MySQL：索引同时存在，mysql自动选择最优的方案：（对于这个例子，mysql选择idx_age_empno），但是，随着数据量的变化，选择的索引也会随之变化的。

5.6.5. 双路排序和单路排序

如果排序没有使用索引，引起了filesort，那么filesort有两种算法

1、双路排序

2、单路排序

5.6.5.1 双路排序

原理：**第一遍扫描出需要排序的字段，然后进行排序后，根据排序结果，第二遍再扫描一下需要select的列数据。**这样会引起大量的随机IO，效率不高，但是节约内存。排序使用quick sort，但是如果内存不够则会按照block进行排序，将排序结果写入磁盘文件，然后再将结果合并。

具体过程：

1、读取所有满足条件的记录。

2、对于每一行，存储一对值到缓冲区（排序列，行记录指针），一个是排序的索引列的值，即order by用到的列值，和指向该行数据的行指针。

3、当缓冲区满后，运行一个快速排序（qsort）来将缓冲区中数据排序，并将排序完的数据存储到一个临时文件，并保存一个存储块的指针，当然如果缓冲区不满，则不会创建临时文件了。

4、重复以上步骤，直到将所有行读完，并建立相应的有序的临时文件。

5、对块级进行排序，这个类似于归并排序算法，只通过两个临时文件的指针来不断交换数据，最终达到两个文件，都是有序的。

6、重复5直到所有的数据都排序完毕。

7、采取顺序读的方式，将每行数据读入内存，并取出数据传到客户端，这里读取数据时并不是一行一行读，读取缓存大小由read_rnd_buffer_size来指定。特点: 采取的方法为：快速排序 + 归并排序。

但有一个问题，就是，一行数据会被读两次，第一次是where条件过滤时，第二个是排完序后还得用行指针去读一次，一个优化的方法是，直接读入数据，排序的时候也根据这个排序，排序完成后，就直接发送到客户端了。

5.6.5.2 单路排序

在MySQL4.1版本之前只有第一种排序算法双路排序，第二种算法是从MySQL4.1开始的改进算法，主要目的是为了减少第一次算法中需要两次访问表数据的IO操作，将两次变成了一次，但相应也会耗用更多的sort buffer空间。当然，MySQL4.1开始的以后所有版本同时也支持第一种算法。

具体过程：

1、读取满足条件的记录

2、对于每一行，记录排序的key和数据行指针，并且把要查询的列也读出来

3、根据索引key排序

4、读取排序完成的文件，并直接根据数据位置读取数据返回客户端，而不是去访问表

特点：

1、单路排序一次性将结果读取出来，然后在sort buffer中排序，避免了双路排序的两次读的随机IO。

2、这也有一个问题：当获取的列很多的时候，排序起来就很占空间

结论及引申出的问题

1、单路比多路要多占用更多内存空间

2、因为单路是把所有字段都取出，所以有可能取出的数据的总大小超出了sort_buffer_size的容量，导致每次只能取sort_buffer_size容量大小的数据，进行排序（创建tmp文件，多路合并），排完再取sort_buffer容量大小，再排……从而多次I/O。

3、单路本来想省一次I/O操作，反而导致了大量的I/O操作，反而得不偿失。

5.6.6. 排序优化策略

优化策略：

1、减少select 后面的查询的字段：order by时select * 是一个大忌。

2、查询字段过多会占用sort_buffer_size的容量。增大sort_buffer_size参数的设置：当然，要根据系统的能力去提高，因为这个参数是针对每个进程（connection）的 1M-8M之间调整。 MySQL8.0，InnoDB存储引擎默认值是1048576字节，1MB。

1	`SHOW VARIABLES LIKE '%sort_buffer_size%'; -- 默认1MB`

查看结果如下所示：

3、增大max_length_for_sort_data参数的设置：MySQL根据max_length_for_sort_data变量来确定使用哪种算法，默认值是4096字节，如果需要返回的列的总长度大于max_length_for_sort_data，使用双路排序算法，否则使用单路排序算法。但是如果设的太高，数据总容量超出sort_buffer_size的概率就增大，明显症状是高的磁盘I/O活动和低的处理器使用率。1024-8192之间调整。

1	`SHOW VARIABLES LIKE '%max_length_for_sort_data%'; -- 默认4K`

查看结果如下所示：

举例：

1、如果数据总量很小（单路一次就可以读取所有数据），单条记录大小很大（大于4K，默认会使用双路排序），此时，可以增加max_length_for_sort_data的值，增加sort_buffer_size的值，让服务器默认使用单路排序。

2、如果数据总量很大（单路很多次IO才可以），单条记录大小很小（小于4K，默认会使用单路排序），此时，可以减小max_length_for_sort_data的值，让服务器默认使用双路排序。

5.6.7. 分组优化

1、group by 使用索引的原则几乎跟order by一致。但是group by 即使没有过滤条件用到索引，也可以直接使用索引（Order By 必须有过滤条件才能使用上索引）

2、包含了order by、group by、distinct这些查询的语句，where条件过滤出来的结果集请保持在1000行以内，否则SQL会很慢。

5.8. 覆盖索引

最后使用索引的手段：覆盖索引

5.7.1. 什么是覆盖索引

**理解方式一：**索引是高效找到行的一个方法，但是一般数据库也能使用索引找到一个列的数据，因此它不必读取整个行。毕竟索引叶子节点存储了它们索引的数据；当能通过读取索引就可以得到想要的数据，那就不需要回表读取了。一个索引包含了满足查询结果的数据就叫做覆盖索引。

**理解方式二：**非聚簇复合索引的一种形式，它包括在查询里的SELECT、JOIN和WHERE子句用到的所有列（即建索引的字段正好是覆盖查询条件中所涉及的字段）。

简单说就是：select 到 from 之间查询的列 <= 使用的索引列 + 主键

好处：

避免Innodb表进行索引的二次查询（回表）
可以把随机IO变成顺序IO加快查询效率

**缺点：**索引字段的维护总是有代价的。因此，在建立冗余索引来支持覆盖索引时就需要权衡考虑了。

补充

怎么决定建立哪些索引?

什么时候不需要创建索引？

WHERE 条件，GROUP BY，ORDER BY 里用不到的字段，索引的价值是快速定位，如果起不到定位的字段通常是不需要创建索引的，因为索引是会占用物理空间的。
字段中存在大量重复数据，不需要创建索引，比如性别字段，只有男女，如果数据库表中，男女的记录分布均匀，那么无论搜索哪个值都可能得到一半的数据。在这些情况下，还不如不要索引，因为 MySQL 还有一个查询优化器，查询优化器发现某个值出现在表的数据行中的百分比很高的时候，它一般会忽略索引，进行全表扫描。
表数据太少的时候，不需要创建索引；
经常更新的字段不用创建索引，比如不要对电商项目的用户余额建立索引，因为索引字段频繁修改，由

索引优化详细讲讲

常见优化索引的方法：

前缀索引优化：使用前缀索引是为了减小索引字段大小，可以增加一个索引页中存储的索引值，有效提高索引的查询速度。在一些大字符串的字段作为索引时，使用前缀索引可以帮助我们减小索引项的大小。
覆盖索引优化：覆盖索引是指 SQL 中 query 的所有字段，在索引 B+Tree 的叶子节点上都能找得到的那些索引，从二级索引中查询得到记录，而不需要通过聚簇索引查询获得，可以避免回表的操作。
主键索引最好是自增的：
- 如果我们使用自增主键，那么每次插入的新数据就会按顺序添加到当前索引节点的位置，不需要移动已有的数据，当页面写满，就会自动开辟一个新页面。因为每次插入一条新记录，都是追加操作，不需要重新移动数据，因此这种插入数据的方法效率非常高。
- 如果我们使用非自增主键，由于每次插入主键的索引值都是随机的，因此每次插入新的数据时，就可能会插入到现有数据页中间的某个位置，这将不得不移动其它数据来满足新数据的插入，甚至需要从一个页面复制数据到另外一个页面，我们通常将这种情况称为页分裂。页分裂还有可能会造成大量的内存碎片，导致索引结构不紧凑，从而影响查询效率。
防止索引失效：
- 当我们使用左或者左右模糊匹配的时候，也就是 like %xx 或者 like %xx%这两种方式都会造成索引失效；
- 当我们在查询条件中对索引列做了计算、函数、类型转换操作，这些情况下都会造成索引失效；
- 联合索引要能正确使用需要遵循最左匹配原则，也就是按照最左优先的方式进行索引的匹配，否则就会导致索引失效。
- 在 WHERE 子句中，如果在 OR 前的条件列是索引列，而在 OR 后的条件列不是索引列，那么索引会失效。

了解过前缀索引吗？

使用前缀索引是为了减小索引字段大小，可以增加一个索引页中存储的索引值，有效提高索引的查询速度。在一些大字符串的字段作为索引时，使用前缀索引可以帮助我们减小索引项的大小。

什么是前缀索引？

前缀索引（Prefix Index） 是MySQL中的一种索引优化技术，只对字符串列的前N个字符创建索引，而不是对整个字符串列。
语法示例：

1 2	`-- 为name列的前10个字符创建索引 CREATE INDEX idx_name_prefix ON users(name(10));`

💡 核心思想：
“用更短的索引长度，换取更小的索引体积，从而提升查询性能”

为什么需要前缀索引？（问题背景）

问题：长字符串列的索引问题

列类型	示例值	完整索引大小	问题
`VARCHAR(255)`	`"[email protected]"`	约255字节/行	索引过大，占用大量磁盘/内存
`VARCHAR(255)`	100万行	~255MB	无法完全缓存到Buffer Pool

优化目标：

减少索引大小 → 降低磁盘占用
提升内存缓存率 → 减少磁盘I/O
保持查询效率 → 通过合理选择前缀长度

前缀索引优化的核心步骤

步骤1：分析列值分布（关键！）

-- 计算不同前缀长度的唯一值比例
SELECT 
  COUNT(DISTINCT LEFT(name, 5)) / COUNT(*) AS uniq_5,
  COUNT(DISTINCT LEFT(name, 10)) / COUNT(*) AS uniq_10,
  COUNT(DISTINCT LEFT(name, 15)) / COUNT(*) AS uniq_15
FROM users;

输出示例：

前缀长度	唯一值比例	说明
5	0.85	15%重复，可能不够区分
10	0.98	2%重复，足够好
15	0.999	0.1%重复，但索引更大

💡 选择原则：
选择唯一值比例≥95%的最小前缀长度（例如10）

步骤2：创建前缀索引

1 2	`-- 基于分析结果，选择10字符作为前缀 CREATE INDEX idx_name_prefix ON users(name(10));`

步骤3：验证效果

-- 检查索引是否被使用
EXPLAIN SELECT * FROM users WHERE name LIKE 'John%';

-- 查看索引大小（优化前 vs 优化后）
SHOW TABLE STATUS LIKE 'users';

前缀索引 vs 完整索引：性能对比

指标	完整索引（`name(255)`）	前缀索引（`name(10)`）	优化效果
索引大小	255字节/行	10字节/行	↓ 96%
磁盘占用	255MB (100万行)	10MB (100万行)	↓ 96%
Buffer Pool命中率	70%	98%	↑ 28%
查询速度	2.1ms	0.3ms	↑ 6倍

💡 为什么更快？
因为索引更小，更多索引页能常驻内存，减少磁盘I/O。

前缀索引的陷阱与注意事项

陷阱1：前缀长度不足导致重复

-- 问题：前缀长度太短
CREATE INDEX idx_email ON users(email(5)); 

-- 问题：所有以"abc"开头的邮箱都归为同一索引
SELECT * FROM users WHERE email = '[email protected]'; 
-- 实际可能返回多个结果（需回表验证）

解决方案：

通过COUNT(DISTINCT LEFT(email, 5))确保唯一值比例>95%
对于邮箱，通常需要10-20字符（如email(15)）

陷阱2：无法用于`ORDER BY`和`GROUP BY`

1
2
3

-- 前缀索引无法优化以下查询
SELECT * FROM users ORDER BY name; 
-- 会触发文件排序（filesort）

解决方案：

对于ORDER BY，使用完整索引或ORDER BY字段本身
前缀索引仅适用于WHERE条件（尤其是LIKE 'prefix%'）

Mysql优化

在面试中，建议按优先级依次介绍慢 SQL 定位、索引优化、表结构设计和 SQL 优化等内容。架构层面的优化，如读写分离和分库分表、数据冷热分离应作为最后的手段，除非在特定场景下有明显的性能瓶颈，否则不应轻易使用，因其引入的复杂性会带来额外的维护成

end

#Mysql

21优化

http://example.com/2025/11/15/21索引优化/

作者

無鎏雲

发布于

2025年11月15日

许可协议

22Mysql日志上一篇

20Mysql架构和索引下一篇

21优化

索引优化

5.1. 性能分析（explain）

5.1.1. explain是什么?

模拟优化器查看执行计划

5.1.2. explain能干什么？

5.1.3. explain怎么玩？

5.1.4. 各字段解释

5.1.4.1. id查询序列号

5.1.4.2. select_type

5.1.4.3. table

5.1.4.4. partitions

5.1.4.5. type*

5.1.4.6. possible_keys

5.1.4.7. key*

5.1.4.8. key_len*

5.1.4.9. ref

5.1.4.10. rows*

5.1.4.11. filtered

5.1.4.12. extra*

索引下推

5.1.5. 小结

5.1.6. explain的局限性

5.3. 单表优化

5.3.1. 索引优化原则

5.3.2. 组合索引原则

5.3.3. 小结

5.4. 关联查询优化

5.4.1. 关联案例

5.4.2. 优化建议

MySQL驱动表详解

驱动表的核心定义

为什么需要驱动表？

驱动表选择原则

1. 优先选择”小表”作为驱动表

2. 选择驱动表的判断依据

如何确定驱动表？

为什么驱动表选择如此重要？

实际案例

优化建议

5.5. 子查询优化

5.6. 排序及分组优化

5.6.1. 无过滤 不索引

5.6.2. 顺序错不索引

5.6.3. 方向反不索引

5.6.4. 优化演示

5.6.5. 双路排序和单路排序

5.6.5.1 双路排序

5.6.5.2 单路排序

5.6.6. 排序优化策略

5.6.7. 分组优化

5.8. 覆盖索引

5.7.1. 什么是覆盖索引

补充

怎么决定建立哪些索引?

索引优化详细讲讲

了解过前缀索引吗？

什么是前缀索引？

为什么需要前缀索引？（问题背景）

问题：长字符串列的索引问题

优化目标：

前缀索引优化的核心步骤

步骤1：分析列值分布（关键！）

步骤2：创建前缀索引

步骤3：验证效果

前缀索引 vs 完整索引：性能对比

前缀索引的陷阱与注意事项

陷阱1：前缀长度不足导致重复

解决方案：

陷阱2：无法用于ORDER BY和GROUP BY

解决方案：

Mysql优化

5.6.1. 无过滤不索引

陷阱2：无法用于`ORDER BY`和`GROUP BY`