深入浅出 MySQL：SQL 优化 - 常用的 SQL 优化
内容导读
互联网集市收集整理的这篇技术教程文章主要介绍了深入浅出 MySQL：SQL 优化 - 常用的 SQL 优化，小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含3980字，纯文字阅读大概需要6分钟。
内容图文
一、大批量插入数据
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 < xxx(第410行的id) order by abc desc limit 10;
八、使用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，尽管效率很低
内容总结
以上是互联网集市为您收集整理的深入浅出 MySQL：SQL 优化 - 常用的 SQL 优化全部内容，希望文章能够帮你解决深入浅出 MySQL：SQL 优化 - 常用的 SQL 优化所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错，欢迎将互联网集市网站推荐给程序员好友。
内容备注
版权声明：本文内容由互联网用户自发贡献，该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 gblab@vip.qq.com 举报，一经查实，本站将立刻删除。
内容手机端
扫描二维码推送至手机访问。