本文共 2437 字，大约阅读时间需要 8 分钟。

SQL 优化

1. 大批量插入数据

环境准备

创建数据库表 tb_user_2，结构如下：

CREATE TABLE `tb_user_2` (    `id` int(11) NOT NULL AUTO_INCREMENT,    `username` varchar(45) NOT NULL,    `password` varchar(96) NOT NULL,    `name` varchar(45) NOT NULL,    `birthday` datetime DEFAULT NULL,    `sex` char(1) DEFAULT NULL,    `email` varchar(45) DEFAULT NULL,    `phone` varchar(45) DEFAULT NULL,    `qq` varchar(32) DEFAULT NULL,    `status` varchar(32) NOT NULL COMMENT '用户状态',    `create_time` datetime NOT NULL,    `update_time` datetime DEFAULT NULL,    PRIMARY KEY (`id`),    UNIQUE KEY `unique_user_username` (`username`)) ENGINE=InnoDB DEFAULT CHARSET=utf8;

导入数据优化

使用 LOAD DATA INFILE 命令导入数据，建议：

• 按主键顺序插入：InnoDB 表按主键顺序存储数据，按顺序插入可提高效率。
• 关闭唯一性校验：执行 SET UNIQUE_CHECKS=0，导入结束后恢复为 1。
• 手动提交事务：关闭自动提交，执行 SET AUTOCOMMIT=0，导入完成后恢复为 1。

示例

# 有序插入insert into tb_user_2 values(1,'Tom'),(2,'Cat'),(3,'Jerry');# 无序插入insert into tb_user_2 values(3,'Luci'),(1,'Tom'),(2,'Cat');

2. 优化 INSERT 语句

• 多值插入：将多个插入合并为一条语句，减少客户端连接次数。
• 事务化插入：使用 START TRANSACTION 和 COMMIT 控制事务，提升效率。

示例

# 原始方式insert into tb_test values(1,'Tom');insert into tb_test values(2,'Cat');insert into tb_test values(3,'Jerry');# 优化后insert into tb_test values(1,'Tom'),(2,'Cat'),(3,'Jerry');

3. 优化 ORDER BY 语句

3.1 环境准备

创建表 emp 和索引 idx_emp_age_salary：

CREATE INDEX idx_emp_age_salary ON emp(age, salary);

3.2 排序优化

• 使用索引排序：确保 ORDER BY 和 WHERE 中的字段一致，并按升序或降序排序。
• 避免 Filesort：通过合理设计索引，减少排序开销。

3.3 Filesort 优化

• 排序算法：使用一次扫描算法（默认优化）。
• 优化参数：调整 sort_buffer_size 和 max_length_for_sort_data。

4. 优化 GROUP BY 语句

• 避免 Filesort：在 GROUP BY 中禁用排序，执行 ORDER BY NULL。
• 使用索引：确保 GROUP BY 和 WHERE 中的字段有优化索引。

示例

# 需要 Filesortselect age, count(*) from emp group by age;# 禁用 Filesortselect age, count(*) from emp group by age order by null;

5. 优化嵌套查询

• 替换子查询：使用多表连接替代嵌套查询，提升效率。
• 优化性能：通过索引和查询计划优化，减少资源消耗。

示例

# 子查询select * from t_user where id in (select user_id from user_role);# 联合查询select * from t_user u    join user_role ur on u.id = ur.user_id;

6. 优化 OR 条件

• 避免全表扫描：确保每个 OR 条件字段有优化索引。
• 使用 UNION 替代 OR：提高效率。

示例

# 不优化select * from emp where id=1 or id=10;# 优化select * from emp where id=1 union select * from emp where id=10;

7. 优化分页查询

• 覆盖索引：在排序字段上创建覆盖索引，减少分页开销。
• 主键自增表：通过主键定位分页结果，提升效率。

示例

# 优化分页select * from tb_item where id > 2000000 limit 10;

8. 使用 SQL 提示

• USE INDEX：指定使用特定索引。
• IGNORE INDEX：忽略不需要的索引。
• FORCE INDEX：强制使用指定索引。

示例

# 使用特定索引explain select * from tb_seller use index(idx_seller_name_sta_addr) where name='小米科技';

通过以上优化方法，可以显著提升数据库性能，适用于大批量数据处理、复杂查询优化等场景。

转载地址：http://lhdfk.baihongyu.com/