MySQL 优化场景实战

准备工作

一张500万数据量的表

CREATE TABLE `user` (
	`id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键Id',
	`user_id` VARCHAR(36) NOT NULL COMMENT '用户Id',
	`user_name` VARCHAR(30) NOT NULL COMMENT '用户名称',
	`phone` VARCHAR(20) NOT NULL COMMENT '手机号码',
	`lan_id` INT(9) NOT NULL COMMENT '本地网',
	`region_id` INT(9) NOT NULL COMMENT '区域',
	`create_time` datetime NOT NULL COMMENT '创建时间',
	PRIMARY KEY (`id`),
	KEY `idx_user_id` (`user_id`)
) ENGINE=INNODB AUTO_INCREMENT=2057960 DEFAULT CHARSET=utf8mb4

表的数据量为500万，无索引

SQL优化方向

索引层面优化

场景一：等值查询

SELECT * FROM `user` WHERE phone = '59207212709' AND lan_id = '787161448' AND region_id = '938860528';

执行该SQL，查看执行时间

执行SQL所用时间超过1S，该SQL即为慢查询

执行 SQL 查询时可以使用 EXPLAIN 关键字来获取查询的执行计划信息

EXPLAIN SELECT user_name FROM `user` WHERE phone = '59207212709' AND lan_id = '787161448' AND region_id = '938860528';

执行计划如下所示：

type 字段 为 ALL 表示使用了全表扫描

rows 字段表示扫描了482万行

Extra 的字段为 Using where 表示 MySQL 在执行查询时使用了 WHERE 子句来过滤数据

EXPLAN 执行计划信息详解

当使用 EXPLAIN 关键字查询 SQL 的执行计划时，会返回一些列字段，每个字段都提供了关于查询执行的重要信息。以下是这些字段的解释：

1. id: 查询中的 SELECT 语句的序列号，如果查询中包含子查询，那么每个子查询会有一个唯一的 ID。
2. select_type: 查询的类型，常见的取值有：SIMPLE（简单的 SELECT 查询），PRIMARY（主查询，不包含子查询），SUBQUERY（子查询），DERIVED（派生表的结果），UNION（UNION 中的第二个或后续的 SELECT 语句）等。
3. table: 显示了查询涉及的表的名称。
4. partitions: 显示了查询涉及的分区。
5. type: 表示访问类型，即 MySQL 在表中找到所需行的方式。 常见的取值有：ALL（全表扫描），index（通过索引进行扫描），range（通过索引进行范围扫描），ref（非唯一索引扫描）等。
6. possible_keys: 显示了可能应用在这张表中的索引。如果为空，表示没有可用的索引。
7. key: 实际使用的索引。如果为 NULL，则没有使用索引。
8. key_len: 表示 MySQL 在索引中使用的字节数。
9. ref: 显示了哪个列或常数与 key 一起被使用。
10. rows: 表示 MySQL 估计必须检查的行数。这是一个估计值。
11. filtered: 表示返回结果前，表中有多少行符合条件的百分比。
12. Extra: 包含了关于查询的额外信息，常见值有 Using index、Using where、Using temporary、Using filesort 等，表示查询执行中使用了的一些特殊操作。

优化方案--添加索引

针对要查询的条件：phone，lan_id，region_id，添加联合索引

ALTER TABLE `user` ADD INDEX idx_phone_lan_region(phone,lan_id,region_id)

添加索引后，再次执行 SQL 语句，发现执行时间为 0.025s，提升了108倍

此时再查询该SQL的执行计划信息如下：

• ref  的值为 const，const，const 这表示用到了三个常量去查找
• possible_key 的值为 idx_phone_lan_region 表示可能用到了 idx_phone_lan_region 这个索引去查找
• key 的值为 idx_phone_lan_region 表示真正用到了 idx_phone_lan_region 这个索引去查找
• key_len 的值为 90 表示使用索引的长度为 90 （在不损失精度的条件下，该值越小越好）
• rows 的值为 1 表示只扫描了一行 （rows 的值是越小越好）

索引优化要点

要点一：最左前缀法则

如果建立的时复合索引，索引的顺序要按照建立时的顺序，即从左到右，如：A -> B -> C

无效索引举例：

• A -> C ：A有效，C无效
• B -> C：B，C 都无效
• C：C 无效

SQL 案例：

key_len 删除之前是90，删除之后变成了82，说明索引的字段变短了，而且 ref 用三个 const 变成了一个 const 说明只用到了一个索引，这是因为没有遵循最左前缀法则，region_id 这个字段没有用上索引。

要点二：不要对索引做以下处理

以下用法会导致索引失效：

• 计算，如：+，-，*，/，!=，<>，is null，is not null，or
• 函数，如：sum()，round()等等
• 手动/自动类型转换，如：id = "1"，本来是数字，给写成字符串了

SQL 案例：

要点三：索引不要放在范围查询右边

举例：

比如复合索引：A -> B -> C，当 where a = "xx" and b > 10 and C = "xx"，这时候只能用到 A和 B 两个索引， C 用不到索引，因为在范围查询之后索引都失效。

SQL 案例：

可以看到 ref 的值为 range，表示用到了范围索引，key_len 的值为 86，说明后面的 region_id 没有用到索引，为了验证 region_id 没有用到索引，可以把 region_id 这个条件删除再执行 SQL ，会发现 key_len 的值还是 86。

要点四：减少 select * 的使用

使用覆盖索引：

即：select 查询字段和 where 中使用的索引字段一致

比如 select 查询 A 和 B 两个字段，where 的条件也是这两个字段

要点五：Like 模糊搜索

失效情况：

• like "%张三%"
• like "%张三"

SQL 案例：

解决方案:

• 使用复合索引，即 like 字段是 select 的查询字段，如：select name from tabke where name like "%张三%"
• 使用 like "张三%"

要点六：order by 优化

当查询语句中使用 order by 进行排序时，如果没用使用索引进行排序，会出现 filesort 文件内排序，这种情况在数据量大或者高并发的时候，会有性能问题，需要优化。

filesort 出现的情况举例：

• order by 字段不是索引字段
• order by 字段是索引字段，但是 select 中没有使用覆盖索引，如：select * from table order by age asc
• order by 中同时存在 ASC 升序排序 DESC 降序排序，如：select a，b from table order by a asc，b asc

SQL 案例：

可以看到 Extra 的值为 Using filesort，用到了文件内排序，文件内排序是一个非常可怕的事情，它没有按照索引去排序，而是再内存中开辟了一个空间，然后把数据复制了一份，放到了这个空间里面，然后在这个空间里面进行排序，这个时候内存就被多占用了一份，内存在服务器中是非常宝贵的，这样会十分影响性能，所以这种排序查询一般都是由 Java 来做。