赞
踩
子查询是mysql的一项重要的功能,可以帮助我们通过一个sql语句实现比较复杂的查询。但是,子查询的执行效率不高。原因是:
在mysql中,可以使用连接(join)查询来替代子查询。连接查询不需要建立临时表,其速度比子查询要快。如果使用索引的话,性能要更好。
尽量不要使用NOT IN 或者NOT EXISTS,用LEFT JOIN TABLEX ON FIELDY WHERE FIELDY IS NULL进行替代。
在mysql中,支持2种排序方式,分别是FileSort和Index排序:
优化建议:
以联合索引IDEX a_b_c(a,b,c)进行举例说明:
1)order by 能使用最左前缀
ORDER BY a
ORDER BY a,b
ORDER BY a,b,c
ORDER BY a DESC, b DESC, c DESC
2)如果WHERE使用索引的最左前缀定义常量,则order by能使用索引
WHERE a = const ORDER BY b, c
WHERE a = const AND b = const ORDER BY c
WHERE a = const AND b > const ORDER BY b, c
3)不能使用索引进行排序的情况
ORDER BY a ASC, b DESC -- 排序不一致
WHERE g = const ORDER BY b, c -- b字段前面丢失最左前缀索引a
WHERE a = const ORDER BY c -- c字段前面丢失最左前缀索引b
WHERE a = const ORDER BY a, d -- d不是索引
WHERE a IN (...) ORDER BY b, c -- 对于排序来说,多个相等条件也是范围查找
排序的字段如果不在索引列上,则filesort会有2种算法:双路排序、单路排序。
1)双路排序(慢)
取一批数据,要对磁盘进行两次扫描。众所周知,IO是非常耗时的,所以在mysql4.1之后,出现了第二种改进的算法,即单路排序。
2)单路排序(快)
从磁盘读取查询需要的所有列,按照order by列在buffer中进行排序,然后扫描排序后的列表进行输出。
优化策略:
1)尝试提高 sort_buffer_size
不管使用哪种算法,提高这个参数都会提高效率。要根据系统的能力去提高,因为这个参数针对每个进程的1M-8M之间调整。Mysql 5.7中,Innodb存储引擎默认的是1MB
mysql> show variables like '%sort_buffer_size%';
+-------------------------+---------+
| Variable_name | Value |
+-------------------------+---------+
| innodb_sort_buffer_size | 1048576 |
| myisam_sort_buffer_size | 8388608 |
| sort_buffer_size | 262144 |
+-------------------------+---------+
3 rows in set (0.00 sec)
2)order by 时使用 select * 是一个大忌,最好只查询需要的字段。
一般在分页查询时,通过创建覆盖索引能够比较好的提高性能。
比如:
SELECT * FROM student LIMIT 2000000, 10;
可以考虑在索引上先完成排序分页操作,然后再根据主键关联回原表来查询所需内容:
SELECT * FROM student t, (SELECT id FROM student ORDER BY id LIMIT 2000000, 10) a
WHERE t.id = a.id;
Copyright © 2003-2013 www.wpsshop.cn 版权所有,并保留所有权利。