MySQL中慢SQL优化的不同方式介绍！
MySQL中慢SQL优化的不同方式介绍！
慢 SQL 的优化，主要从两个方面考虑，SQL 语句本身的优化，以及数据库设计的优化。
避免不必要的列
SQL 查询的时候，应该只查询需要的列，而不是包含额外的列，像select *这种写法应该尽量避免。
分页优化
在数据量比较大，分页比较深的情况下，需要考虑分页的优化。
|
1
|
select * from tabel where type = 2 and level = 9 order by id asc limit 100000,10; |
延迟关联
先通过where条件提取出主键，在将该表与原数据表关联，通过主键 id 提取数据行，而不是通过原来的二级索引提取数据行
|
1
2
3
|
select a.* from table a,(select id from table where type = 2 and level = 9 order by id asc limit 100000,10) bwhere a.id = b.id; |
id 偏移量
偏移量就是找到limit第一个参数对应的主键值，根据这个主键值再去过滤并limit
|
1
2
|
select * from table where id >(select id from table where type = 2 and level = 9 order by id asc limit 190 ); |
索引优化
合理的设计和使用索引，是优化慢 SQL 的利器。
1.利用覆盖索引
InnoDB 使用二级索引查询数据时会回表，但是如果索引的叶节点中已经包含要查询的字段，那它没有必要再回表查询了，这就叫覆盖索引，还有一个简单的理解查询列都是索引列。
|
1
|
select b from test where a = "wanna"; |
|
1
|
alter table test add index idx_a_b (a,b); |
2.避免使用 or 查询
在 MySQL 5.0之前的版本要尽量避免使用or查询，可以使用union或者子查询来替代，因为早期的 MySQL 版本使用or查询可能会导致索引失效，高版本引入了索引合并，解决了这个问题，不过建议大家在实际使用中还是规范写法，能不用就少用。
3.避免使用 != 或者 <> 操作符
SQL 中，不等于操作符会导致查询引擎放弃查询索引，引起全表扫描，即时比较的字段上有索引
解决方法：通过把不等于操作符改成or，可以使用索引，避免全表扫描
|
1
|
id <> 'aaa' ===> id > 'aaa' or id < 'aaa' |
4.适当使用前缀索引
适当的使用前缀索引，可以降低索引的控件占用，提高索引的查询效率。
比如，邮箱的后缀都是固定的@xxx.com，那么类似这种后面几位为固定值的字段就非常适合定义为前缀索引
|
1
|
alter table test add index dix_emaile_prefix (email(6)); |
需要注意的是，前缀索引也存在缺点，MySQL 无法利用前缀索引做order by和group by操作，也无法作为覆盖索引。
5.避免列上函数运算
要避免在列字段上进行算术运算符或其他表达式运算，否则可能会导致存储引擎无法正确的使用索引，从而影响了查询的效率。
|
1
2
|
select * from test where id + 1 = 50;select * from test where month(updateTime) = 7; |
6.正确的使用联合索引
使用联合索引的时候，注意最左匹配原则。
JOIN 的优化
优化子查询
尽量使用join语句来替代子查询，因为子查询是嵌套查询，而嵌套查询会新建创建一张临时表，而临时表的创建与销毁会占用一定的系统资源以及花费一定的时间，同时对于返回结果集比较大的子查询，其对查询性能的影响更大。
小表驱动大表
关联查询的时候要拿小表去驱动大表，因为关联的时候，MySQL 内部会遍历驱动表，再去连接被驱动表。
|
1
|
select name from 小表 left join 大表; |
适当增加冗余字段
增加冗余字段可以减少大量的连表查询，因为多张表的连表查询性能很低，所有可以适当的增加冗余字段，以减少多张表的关联查询，这是以空间换时间的优化策略。
避免使用 JOIN 关联太多表
《阿里巴巴 Java 开发手册》规定不要join超过三张表，第一join太多降低查询的速度，第二join的 buffer 会占用更多的内存。
排序优化
利用索引扫描做排序
MySQL 有两种方式生成有序结果：一是对结果集进行排序的操作，而是按照索引顺序扫描得出的结果，索引是排好序的数据结果，自然是有序的。
但是如果索引不能覆盖查询所需列（覆盖索引），就会没扫描一条记录回表查询一次（逐个获取），这个读操作是随机 IO，通常会比顺序全表扫描还慢，有时会直接放弃使用索引转为全表扫描。
因此，在设计索引时，尽可能使用同一个索引既满足排序又用于查找行。
|
1
2
|
-- 索引(a,b,c)select b,c from test where a like 'aa%' order by b,c; |
只有当索引的列顺序和order by子句的顺序完全一致，并且所有列的排序方向都一样时，才能够使用索引来对结果做排序。
UNION 优化
条件下推
MySQL 处理 union 的策略是先创建临时表，然后将各个查询结果填充到临时表中最后再来做查询，很多优化策略在union查询中都会失效，因为它无法利用索引。
所以需要将where、limit等子句下推到union的各个子查询中，以便优化器可以充分利用这些条件进行优化。
此外，除非确实需要服务器去推，一定要试用union all，如果不加all关键字，MySQL 会给临时表加上distinct选项，这会导致对整个临时表做唯一性检查，代价很高。
到此这篇关于MySQL中慢SQL优化的不同方式介绍的文章就介绍到这了。
学习资料见知识星球。
以上就是今天要分享的技巧，你学会了吗？若有什么问题，欢迎在下方留言。
快来试试吧，小琥 my21ke007。获取 1000个免费 Excel模板福利！
更多技巧， www.excelbook.cn
欢迎 加入 零售创新 知识星球，知识星球主要以数据分析、报告分享、数据工具讨论为主；
1、价值上万元的专业的PPT报告模板。
2、专业案例分析和解读笔记。
3、实用的Excel、Word、PPT技巧。
4、VIP讨论群，共享资源。
5、优惠的会员商品。
6、一次付费只需129元，即可下载本站文章涉及的文件和软件。