mysql手册10_SQL优化

mysql手册10_SQL优化

---

1. 大批量插入数据的优化：

• 按照主键顺序插入
• 关闭唯一性校验

导入数据前执行SET UNIQUE_CHECKS=0，关闭唯一性校验
导入结束后执行SET UNIQUE_CHECKS=1，恢复唯一性校验

• 手动提交事务

导入数据前执行SET AUTOCOMMIT=0，关闭自动提交
导入结束后执行SET AUTOCOMMIT=1，开启自动提交

---

2. insert语句的优化：

• 整合多个insert语句，减少连接次数：

原始方案：
insert into test values(1, 'zhangsan');
insert into test values(2, 'lisi');
insert into test values(1, 'wangwu');

优化方案：
insert into test values(1,'zhangsan'),(2,'lisi'),(3,'wangwu');

• 使用手动事务提交

start transaction;
insert into test values(1,'zhangsan'),(2,'lisi'),(3,'wangwu');
...
commit;

• 按照主键顺序插入

---

3. order by 语句的优化（尽可能使用Using index，避免使用Using filesort）：

• 查询的字段应只包含索引字段和主键

原始方案(Using filesort)查询效率低：
explain select * from city order by city_name;

+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra          |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
|  1 | SIMPLE      | city  | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    4 |   100.00 | Using filesort |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+

优化方案(Using index)：
explain select city_id, city_name from city order by city_name;

+----+-------------+-------+------------+-------+---------------+---------------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key           | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+---------------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | city  | NULL       | index | NULL          | idx_city_name | 152     | NULL |    4 |   100.00 | Using index |
+----+-------------+-------+------------+-------+---------------+---------------+---------+------+------+----------+-------------+

• 使用多个索引字段作为排序条件，要么全部升序，要么全部降序，且排序顺序要和复合索引的定义顺序一致

seller表的复合索引：seller(name,status,address);

案例1：
select id,name,status,address from seller order by name desc, status asc;
结果：Using index; Using filesort 效率低

案例2：
select id,name,status,address from seller order by name desc, status desc;
结果：Using index 效率高

案例3：
select id,name,status,address from seller order by status desc, name desc;
结果：Using index; Using filesort 效率低

Filesort优化：

注：Using filesort 是Mysql里一种速度比较慢的外部排序，应尽量避免
对于Filesort，Mysql有两种排序算法：

• 两次扫描算法： 首先根据条件取出排序字段和行指针。在排序区sort buffer 中排序，在临时表中存储排序结果，完成排序后，根据行指针回表取出结果，该操作会导致大量随机I/O操作，效率低。
• 一次扫描算法：一次性取出满足条件的所有字段，然后在排序区排序后直接输出结果集，该算法内存开销大，但效率比两次扫描算法高。

MYSQL会比较 系统变量max_length_for_sort_data 的大小和Query语句取出的字段总大小来判断用哪种算法，如果max_length_for_sort_data更大，则会使用一次扫描算法，否则使用两次扫描算法。

可以适当提高 sort_buffer_size 和 max_length_for_sort_data 来增大排序区的大小以提高排序效率。

---

4. group by 语句的优化

group by 子句在进行分组时一般（如果没有合适的索引可用）是先扫描整个表并创建一个新的临时表，然后按照group by 指定的列进行排序, 在创建的临时表中每个组的所有行应为连续的，然后使用该临时表来找到组并执行聚合函数(如果有)。

优化方案1是在 group by 语句后加上order by null，取消排序行为。

此外，给group by 指定的列创建索引也可以提高效率。

---

5. 嵌套查询的优化

使用多表联查替换子查询

原始方案：
select * from user where id in (select user_id from role);
优化方案：
select * from user u, role r where u.id = r.user_id;

---

6. OR的优化

or的每个条件列都必须用到索引，且不能用到复合索引，否则索引失效。

select * from user where id = 1 or name = 'zhangsan';

优化方案：给name添加单列索引

select * from user where id = 1 or id = 10;

优化方案：使用union替换or
select * from user where id = 1 union select * from user where id = 10;

7. 分页查询的优化

查询第一页，每页10条数据，耗时0.00s：
select * from testdata limit 10;

查询第二页，每页10条数据，耗时0.00s：
select * from testdata limit 10,10;

查询第25001页，每页10条数据，耗时0.15s:
select * from testdata limit 250000,10;

优化思路1，从索引中先查出第25001页的数据id，再回表查询数据，耗时0.11s：
select * from testdata t,(select id from testdata order by id limit 250000,10) a where t.id = a.id;

优化思路2，耗时0.01s：
select * from testdata where id > 250000 limit 10;
注：该方案只适合主键自增的列，且自增主键不能出现断层。

---

8. 使用SQL提示：

select * from user where name = 'zhangsan';

使用 use index 指定参考使用索引，mysql将不再考虑其他可用的索引：
select * from user use index(idx_name_age_address) where name = 'zhangsan';

使用 ignore index 指定忽略索引，mysql将不再考虑使用该索引：
select * from user ignore index(idx_name) where name = 'zhangsan';

使用 force index 强制使用某个索引，即使该索引查询效率比扫表慢也要用：
select * from user force index(idx_address) where address = '中国北京';