深入浅出MySQL：SQL优化常用的SQL优化

一、大批量插入数据

MyISAM 表的插入优化，插入前先关闭索引更新。

alter table xxx DISABLE KEYS; -- 关闭索引更新
... -- 插入数据
alter table xxx ENABLE KEYS;

InnoDB 表的插入优化：

1）导入的数据按主键排列，效率更高。因为 Innodb 数据是按主键顺序排列的

2）关闭唯一性校验

SET UNIQUE_CHECKS = 0;
-- 插入数据
SET UNIQUE_CHECKS = 1;

3）如果应用使用自动提交（事务）的方式，则插入前关闭自动提交，有助于提高效率

SET AUTOCOMMIT = 0;
-- 插入数据
SET AUTOCOMMIT = 1;

二、优化 insert 语句

1）数据量较大时，采用批量插入代替多条insert语句

insert into xx values(1, 2), (3, 4), (5, 6) ...;

2）如果从不同客户端插入多行时，使用 insert delayed 提高速度。该语句让 insert 马上执行，数据放入内存的队列中，并没有真正写入磁盘，比每条语句插入更快。

3）将索引与数据文件分在不同磁盘存放（通过建表的选项）

4）对于MyISAM，增加 bulk_insert_buffer_size 值提高批量插入速度

5）Load data infile 比多条 insert 快近 20 倍。

三、优化 order by 语句

1）MySQL 两种排序方式

a. 有序索引顺序扫描直接返回有序数据

select customer_id from customer order by store_id; -- Using index 效率高

b. 对返回结果进行排序

select * from customer order by store_id; -- Using Filesort

通过explain查看Extra是否为Using index，Using index效率高于Using Filesort

Filesort 将取得的数据在 sort_buffer_size 系统变量设置的内存排序区进行排序，若内存装不下，将磁盘的数据分块，再对各个数据块进行排序，然后各个块合并成有序结果集。 sort_buffer_size 设置的排序区是每个线程独占的，在同一时刻，MySQL 存在多个 sort buffer 排序区

优化目标：尽量减少额外排序，通过索引直接返回有效数据。

下列 order by 语句将使用索引：

select * from xxx order by key_part1, key_part2 ...; -- 同属于一个联合索引
select * from xxx where key_part1 = xxx order by key_part1 desc, key_part2 desc, ...;

下列 order by 语句将不使用索引：

select * from xxx order by key_part1 desc, key_part2 asc; -- 混合desc与asc
select * from xxx where key_part2 = xxx order by key_part1; -- where与order by的字段不一致
select * from xxx order by key1, key2; -- key1与key2属于两个不同的关键字

2）filesort 优化

对于 filesort 存在两种排序算法

两次扫描算法：第一次获取排序字段和行指针信息，第二次根据行指针获取记录（产生大量随机I/O），该算法内存开销较小。

一次扫描算法：一次取出所有满足条件的行，在排序区进行排序后直接输出结果。内存开销大，效率高。

a）适当加大 max_length_for_sort_data 使其使用更优化的排序算法（一次扫描）

b）适当加大 sort_buffer_size 尽量让排序在内存中完成，而不是通过创建临时表放在文件中进行。

c）select 具体字段 代替 select * ，可减少排序区的使用

四、优化 group by 语句

默认情况下，MySQL会对group by的所有字段进行排序，可禁止该排序：

select xxx from xxx group by xxx order by null;

五、优化嵌套查询

select * from customer where customer_id not in (select customer_id from payment);
select * from customer a left join payment b on a.customer_id = b.customer_id where b.customer_id is null;
-- join代替子查询效率更好，因为join不需要在内存中创建临时表。

六、优化Or条件

对OR的每个字段增加索引，否则执行时涉及的索引将都不使用

七、优化分页查询

limit 1000,20 ：MySQL 会对所有进行排序，但仅需要返回20条，明显查询的代价很高

1）优化思路一：在索引上完成排序分页，然后通过主键再从元表查找其他所需要的字段

select film_id, description from film order by title limit 50,5; -- Using Filesort
select a.film_id, a.description from film a inner join (select film_id from film order by title limit 50,5) b on a.film_id = b.film_id;
-- Using index
因为--
select film_id from film order by title limit 50,5 会使用索引
select film_id, description from film order by title limit 50,5; 不会使用索引

2）将Limit n,m 转为 Limit m （适用于排序字段不会出现重复值）

select * from xxx order by abc limit 400, 10;
select * from xxx where id

八、使用SQL提示


select count(*) from xxx use index (xxx); -- 建议MySQL使用索引XXX
select count(*) from xxx ignore index (xxx); -- 让MySQL忽略使用索引XXX
select count(*) from xxx force index (xxx); -- 强制让MySQL使用索引XXX，尽管效率很低