MySQL中慢SQL优化的不同方式介绍

慢 SQL 的优化，主要从两个方面考虑，SQL 语句本身的优化，以及数据库设计的优化。

避免不必要的列

SQL 查询的时候，应该只查询需要的列，而不是包含额外的列，像select *这种写法应该尽量避免。

分页优化

在数据量比较大，分页比较深的情况下，需要考虑分页的优化。

 select * from tabel where type = 2 and level = 9 order by id asc limit 100000,10;

延迟关联

先通过where条件提取出主键，在将该表与原数据表关联，通过主键 id 提取数据行，而不是通过原来的二级索引提取数据行

 select a.* from table a,
 (select id from table where type = 2 and level = 9 order by id ascandroid limit 100000,10) bLMPQHIW
 where a.id = b.id;

id 偏移量

偏移量就是找到 limit 第一个参数对应的主键值，根据这个主键值再去过滤并 limit

 select * from table where id >
 (select id from table where type = 2 and level = 9 order by id asc limit 190 );

索引优化

合理的设计和使用索引，是优化慢 SQL 的利器。

1.利用覆盖索引

InnoDB 使用二级索引查询数据时会回表，但是如果索引的叶节点中已经包含要查询的字段，那它没有必要再回表查询了，这就叫覆盖索引，还有一个简单的理解查询列都是索引列。

 select b from test where a = "wanna";

 alter table test add index idx_a_b (a,b);

2.避免使用 or 查询

在 mysql 5.0之前的版本要尽量避免使用 or 查询，可以使用 union 或者子查询来替代，因为早期的 MySQL 版本使用 or 查询可能会导致索引失效，高版本引入了索引合并，解决了这个问题，不过建议大家在实际使用中还是规范写法，能不用就少用。

3.避免使用 != 或者 <> 操作符

SQL 中，不等于操作符会导致查询引擎放弃查询索引，引起全表扫描，即时比较的字段上有索引

解决方法：通过把不等于操作符改成 or，可以使用索引，避免全表扫描

 id <> 'aaa' ===> id > 'aaa' or id < 'aaChina编程a'

4.适当使用前缀索引

适当的使用前缀索引，可以降低索引的控件占用，提高索引的查询效率。

比如，邮箱的后缀都是固定的@xxx.com，那么类似这种后面几位为固定值的字段就非常适合定义China编程为前缀索引

 alter table test add index dix_emaile_prefix (email(6));

需要注意的是，前缀索引也存在缺点，MySQL 无法利用前缀索引做 order by和group by操作，也无法作为覆盖索引。

5.避免列上函数运算

要避免在列字段上进行算术运算符或其他表达式运算，否则可能会导致存储引擎无法正确的使用索引，从而影响了查询的效率。

 select * from test where id + 1 = 50;
 select * from test where month(updateTime) = 7;

6.正确的使用联合索引

使用联合索引的时候，注意最左匹配原则。

JOIN 的优化

优化子查询

尽量使用 join 语句来替代子查询，因为子查询是嵌套查询，而嵌套查询会新建创建一张临时表，而临时表的创建与销毁会占用一定的系统资源以及花费一定的时间，同时对于返回结果集比较大的子查询，其对查询性能的影响更大。

小表驱动大表

关联查询的时候要拿小表去驱动大表，因为关联的时候，MySQL 内部会遍历驱动表，再去连接php被驱动表。

select name from 小表 left join 大表;

适当增加冗余字段

增加冗余字段可以减少大量的连表查询，因为多张表的连表查询性能很低，所有可以适当的增加冗余字段，以减少多张表的关联查询，这是以空间换时间的优化策略。

避免使用 JOIN 关联太多表

《阿里巴巴 Java 开发手册》规定不要 join 超过三张表，第一 join 太多降低查询的速度，第二 join 的 buffer 会占用更多的内存。

排序优化

利用索引扫描做排序

MySQL 有两种方式生成有序结果：一是对结果集进行排序的操作，而是按照索引顺序扫描得出的结果，索引是排好序的数据结果，自然是有序的。

但是如果索引不能覆盖查询所需列（覆盖索引），就会没扫描一条记录回表查询一次（逐个获取），这个读操作是随机 IO，通常会比顺序全表扫描还慢，有时会直接放弃使用索引转为全表扫描。

因此，在设计索引时，尽可能使用同一个索引既满足排序又用于查找行。

-- 索引(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优化的不同方式介绍的文章就介绍到这了,更多相关MySQL慢SQL优化内容请搜索China编程(www.chinasem.cn)以前的文章或继续浏览下面的相关文章希望大家以后多多支持编程China编程(www.chinasem.cn)！