进阶功法：SQL 优化指南

SQL 优化指南

1. 插入数据优化

1.1 批量插入数据

在插入数据时，可以一次插入多条数据以进行 SQL 优化。通常建议一次插入 500 到 1000 条数据。

INSERT INTO tb_test VALUES (1, 'TOM'), (2, 'JERRY'), ...;

1.2 手动提交事务

SQL 在每条语句后都进行提交会影响整体性能。可以通过手动提交事务来减轻负担。

START TRANSACTION;

INSERT INTO tb_test VALUES (1, 'TOM'), (2, 'JERRY');
INSERT INTO tb_test VALUES (3, 'TaM'), (4, 'JyRRY');
INSERT INTO tb_test VALUES (5, 'TeM'), (6, 'JiRRY');

COMMIT;

1.3 主键顺序插入

主键的顺序插入会减轻 SQL 排序操作，直接插入加快速度。

示例：

• 主键插入：1, 2, 3, 6, 9, 12, 40, 60…

1.4 大批量插入数据

如果一次性插入超大量数据，INSERT 语句的插入性能会很低。可以使用 LOAD DATA INFILE 方法插入数据。

步骤：

1. 客户端连接服务端时，加上参数 --local-infile。

   mysql --local-infile -u root -p
   

2. 设置全局参数 local_infile 为 1，开启从本地加载文件导入数据的开关。

   SET GLOBAL local_infile = 1;
   

3. 执行 LOAD DATA INFILE 指令将准备好的数据加载到表结构中。

   LOAD DATA LOCAL INFILE '/root/sql1.log' INTO TABLE tb_user FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n';
   

2. 主键优化

在 InnoDB 存储引擎中，表数据都是根据主键顺序组织存放的，这种存储方式的表称为索引组织表。

主键设计原则

1. 降低主键长度：满足业务需求的情况下，尽量降低主键的长度。
2. 顺序插入：插入数据时，尽量选择顺序插入，使用 AUTO_INCREMENT 自增主键。
3. 避免使用 UUID 或自然主键：如身份证号等。
4. 避免修改主键：业务操作时，避免对主键的修改。

拓展知识

• 页合并和页分裂

3. ORDER BY 优化

ORDER BY 排序具有两种排序方式：

3.1 Using filesort

通过表的索引或全表扫描，读取满足条件的数据行，然后在排序缓冲区 sortbuffer 中完成排序操作。所有不是通过索引直接返回排序结果的排序都叫 FileSort 排序。

3.2 Using index

通过有序索引顺序扫描直接返回有序数据，这种情况即为 using index，不需要额外排序，操作效率高。

示例

-- 使用 Using filesort
EXPLAIN SELECT id, age, phone FROM tb_user ORDER BY name;

-- 使用 Using index
CREATE INDEX idx_user_age_phone_aa ON tb_user(age, phone);

EXPLAIN SELECT id, age, phone FROM tb_user ORDER BY age, phone;
EXPLAIN SELECT id, age, phone FROM tb_user ORDER BY age DESC, phone DESC;

-- 创建混合排序索引
CREATE INDEX idx_user_age_phone_aa ON tb_user(age ASC, phone DESC);

EXPLAIN SELECT id, age, phone FROM tb_user ORDER BY age ASC, phone DESC;

3.3 ORDER BY 优化原则

1. 建立合适的索引：根据排序字段建立合适的索引，多字段排序时，遵循最左前缀法则。
2. 覆盖索引：尽量使用覆盖索引。
3. 多字段排序：一个升序一个降序时，注意联合索引在创建时的规则（ASC/DESC）。
4. 增大排序缓冲区：如果不可避免地出现 FileSort，大数据量排序时，可以适当增大排序缓冲区大小 sort_buffer_size（默认 256k）。

4. GROUP BY 优化

GROUP BY 优化同样借助索引进行优化。

示例

-- 效率较低
EXPLAIN SELECT profession, COUNT(*) FROM tb_user GROUP BY profession;

-- 建立索引后，效率提升
CREATE INDEX idx_user_pro_age_sta ON tb_user(profession, age, status);

EXPLAIN SELECT profession, COUNT(*) FROM tb_user GROUP BY profession;

4.1 GROUP BY 优化原则

1. 通过索引提高效率：在分组操作时，可以通过索引来提高效率。
2. 最左前缀法则：分组操作时，索引的使用也是满足最左前缀法则的。

5. LIMIT 优化

LIMIT 用于分页操作，当数据量较大时，需要进行优化。

示例

# 当我们希望获得第900000个数据后的十个数据，就需要完全获得前9000000个数据才可以，这会损耗许多时间

# 优化思路：
# 我们通过select只获得第9000000个后的十个数据的id
# 然后通过id对比来获得整行数据：

EXPLAIN SELECT * FROM tb_sku t, (SELECT id FROM tb_sku ORDER BY id LIMIT 900000, 10) a WHERE t.id = a.id;

6. COUNT 优化

不同存储引擎处理 COUNT 的方式不同：

6.1 MyISAM

直接把表的总行数存储在磁盘中，运行 COUNT(*) 时直接输出。

6.2 InnoDB

需要一行一行读取数据，进行累加。

6.3 优化思路

在添加数据和删除数据时，同时存储其数据数量。

6.4 COUNT 的四种常见情况

COUNT 用法	含义
COUNT(主键)	InnoDB 引擎会遍历整张表，把每一行的主键值都取出来，返回给服务层。服务层按行进行累加（主键不可能为 null）。
COUNT(字段)	没有 NOT NULL 约束：InnoDB 引擎会遍历整张表，把每一行的字段值都取出来，返回给服务层，服务层判断是否为 null，不为 null 计数累加。 有 NOT NULL 约束：InnoDB 引擎会遍历整张表，把每一行的字段值都取出来，返回给服务层，直接按行进行累加。
COUNT(1)	InnoDB 引擎遍历整张表，但不取值。服务层对于返回的每一行，放一个数字“1”进去，直接按行进行累加。
COUNT(*)	InnoDB 引擎并不会把全部字段取出来，而是专门做了优化，不取值，服务层直接按行进行累加。

6.5 注意事项

• COUNT(1) 和 COUNT(*) 速度基本相近，均为最快速度。
• 按照效率排序：COUNT(字段) < COUNT(主键 id) < COUNT(1) ≈ COUNT(*)，所以尽量使用 COUNT(*)。

7. UPDATE 优化

InnoDB 的行锁是针对索引加的锁，不是针对记录加的锁，并且该索引不能失效，否则会从行锁升级为表锁。

示例

-- 采用行锁
UPDATE course SET name = 'javaEE' WHERE id = 1;

-- 采用表锁
UPDATE course SET name = 'SpringBoot' WHERE name = 'PHP';

7.1 UPDATE 优化原则

1. 使用索引：更新操作尽量采用索引进行改变，这样锁就会变成行锁，只控制这一行数据。
2. 避免表锁：如果更新操作没有使用索引，那么会采用表锁，导致整个表的数据都无法改变，影响其他人同步修改该表。

版权声明：本文内容由网络用户投稿，版权归原作者所有，本站不拥有其著作权，亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容，请联系我们处理，核实后本网站将在24小时内删除侵权内容。