主要针对where,group by, order by子句优化
优化GROUP BY语句
默认情况下,MySQL对所有GROUP BY col1,col2...的字段进行排序。这与在查询中指定ORDER BY col1,col2...类似。因此,如果显式包括一个包含相同的列的ORDER BY子句,则对MySQL的实际执行性能没有什么影响。 如果查询包括GROUP BY 但用户想要避免排序结果的消耗,则可以指定ORDER By NULL禁止排序,例如:
explain select id, sum(moneys) from sales2 group by id \G explain select id, sum(moneys) from sales2 group by id order by null \G你可以通过比较发现第一条语句会比第二句在Extra:里面多了Using filesort.而恰恰filesort是最耗时的。
优化ORDER BY语句
在某些情况中,MySQL可以使用一个索引来满足ORDER BY子句,而不需要额外的排序。WHERE 条件和 ORDER BY使用相同的索引,并且ORDER BY的顺序和索引顺序相同,并且ORDER BY的字段都是升序或者都是降序。
例如:
SELECT * FROM t1 ORDER BY key_part1,key_part2,....: SELECT * FROM t1 WHERE key_part1 = 1 ORDER BY key_part1 DESC,key_part2 DESC; SELECT * FROM t1 ORDER BY key_part1 DESC, key_part2 DESC;但是以下的情况不使用索引:
SELECT * FROM t1 ORDER BY key_part1 DESC, key_part2 ASC; --ORDER by的字段混合ASC 和 DESC SELECT * FROM t1 WHERE key2=constant ORDER BY key1; ----用于查询行的关键字与ORDER BY 中所使用的不相同 SELECT * FROM t1 ORDER BY key1, key2; ----对不同的关键字使用ORDER BY
mysql > explain select A . id , A . title , B . title from jos_content A left join jos_categories B on A . catid = B . id left join jos_sections C on A . sectionid = C . id order by B . id ; +----+-------------+-------+--------+---------------+---------+---------+-------------------------+-------+---------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+--------+---------------+---------+---------+-------------------------+-------+---------------------------------+ | 1 | SIMPLE | A | ALL | NULL | NULL | NULL | NULL | 46585 | Using temporary ; Using filesort | | 1 | SIMPLE | B | eq_ref | PRIMARY | PRIMARY | 4 | joomla_test . A . catid | 1 | | | 1 | SIMPLE | C | eq_ref | PRIMARY | PRIMARY | 4 | joomla_test . A . sectionid | 1 | Using index | +----+-------------+-------+--------+---------------+---------+---------+-------------------------+-------+---------------------------------+ 3 rows in set ( 0.00 sec ) mysql > explain select A . id , A . title , B . title from jos_content A left join jos_categories B on A . catid = B . id left join jos_sections C on A . sectionid = C . id order by A . id ; +----+-------------+-------+--------+---------------+---------+---------+-------------------------+-------+----------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+--------+---------------+---------+---------+-------------------------+-------+----------------+ | 1 | SIMPLE | A | ALL | NULL | NULL | NULL | NULL | 46585 | Using filesort | | 1 | SIMPLE | B | eq_ref | PRIMARY | PRIMARY | 4 | joomla_test . A . catid | 1 | | | 1 | SIMPLE | C | eq_ref | PRIMARY | PRIMARY | 4 | joomla_test . A . sectionid | 1 | Using index | +----+-------------+-------+--------+---------------+---------+---------+-------------------------+-------+----------------+对于上面两条语句,只是修改了一下排序字段,而第一个使用了Using temporary,而第二个却没有。在日常的网站维护中,如果有Using temporary出现,说明需要做一些优化措施了。
而为什么第一个用了临时表,而第二个没有用呢?
因为如果有ORDER BY子句和一个不同的GROUP BY子句,或者如果ORDER BY或GROUP BY中的字段都来自其他的表而非连接顺序中的第一个表的话,就会创建一个临时表了。
那么,对于上面例子中的第一条语句,我们需要对jos_categories的id进行排序,可以将SQL做如下改动:
mysql > explain select B . id , B . title , A . title from jos_categories A left join jos_content B on A . id = B . catid left join jos_sections C on B . sectionid = C . id order by A . id ; +----+-------------+-------+--------+---------------+-----------+---------+-------------------------+------+----------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+--------+---------------+-----------+---------+-------------------------+------+----------------+ | 1 | SIMPLE | A | ALL | NULL | NULL | NULL | NULL | 18 | Using filesort | | 1 | SIMPLE | B | ref | idx_catid | idx_catid | 4 | joomla_test . A . id | 3328 | | | 1 | SIMPLE | C | eq_ref | PRIMARY | PRIMARY | 4 | joomla_test . B . sectionid | 1 | Using index | +----+-------------+-------+--------+---------------+-----------+---------+-------------------------+------+----------------+ 3 rows in set ( 0.00 sec )
这样我们发现,不会再有Using temporary了,而且在查询jos_content时,查询的记录明显有了数量级的降低,这是因为jos_content的idx_catid起了作用。
所以结论是:
尽量对第一个表的索引键进行排序,这样效率是高的。
我们还会发现,在排序的语句中都出现了Using filesort,字面意思可能会被理解为:使用文件进行排序或中文件中进行排序。实际上这是不正确的,这是一个让人产生误解的词语。
当我们试图对一个没有索引的字段进行排序时,就是filesoft。它跟文件没有任何关系,实际上是内部的一个快速排序。
然而,当我们回过头来再看上面运行过的一个SQL的时候会有以下发现:
mysql > explain select A . id , A . title , B . title from jos_content A , jos_categories B , jos_sections C where A . catid = B . id and A . sectionid = C . id order by C . id ; +----+-------------+-------+--------+-----------------------+-------------+---------+---------------------+-------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+--------+-----------------------+-------------+---------+---------------------+-------+-------------+ | 1 | SIMPLE | C | index | PRIMARY | PRIMARY | 4 | NULL | 1 | Using index | | 1 | SIMPLE | A | ref | idx_catid , idx_section | idx_section | 4 | joomla_test . C . id | 23293 | Using where | | 1 | SIMPLE | B | eq_ref | PRIMARY | PRIMARY | 4 | joomla_test . A . catid | 1 | Using where | +----+-------------+-------+--------+-----------------------+-------------+---------+---------------------+-------+-------------+ 3 rows in set ( 0.00 sec )
这是我们刚才运行过的一条语句,只是加了一个排序,而这条语句中C表的主键对排序起了作用,我们会发现Using filesort没有了。
而尽管在上面的语句中也是对第一个表的主键进行排序,却没有得到想要的效果(第一个表的主键没有用到),这是为什么呢?实际上以上运行过的所有left join的语句中,第一个表的索引都没有用到,尽管对第一个表的主键进行了排序也无济于事。不免有些奇怪!
于是我们继续测试了下一条SQL:
mysql > explain select A . id , A . title , B . title from jos_content A left join jos_categories B on A . catid = B . id left join jos_sections C on A . sectionid = C . id where A . id < 100 ; +----+-------------+-------+--------+----------------+---------+---------+-------------------------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+--------+----------------+---------+---------+-------------------------+------+-------------+ | 1 | SIMPLE | A | range | PRIMARY | PRIMARY | 4 | NULL | 90 | Using where | | 1 | SIMPLE | B | eq_ref | PRIMARY | PRIMARY | 4 | joomla_test . A . catid | 1 | | | 1 | SIMPLE | C | eq_ref | PRIMARY | PRIMARY | 4 | joomla_test . A . sectionid | 1 | Using index | +----+-------------+-------+--------+----------------+---------+---------+-------------------------+------+-------------+ 3 rows in set ( 0.05 sec )
然后,当再次进行排序操作的时候,Using filesoft也没有再出现
mysql > explain select A . id , A . title , B . title from jos_content A left join jos_categories B on A . catid = B . id left join jos_sections C on A . sectionid = C . id where A . id < 100 order by A . id ; +----+-------------+-------+--------+---------------+---------+---------+-------------------------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+--------+---------------+---------+---------+-------------------------+------+-------------+ | 1 | SIMPLE | A | range | PRIMARY | PRIMARY | 4 | NULL | 105 | Using where | | 1 | SIMPLE | B | eq_ref | PRIMARY | PRIMARY | 4 | joomla_test . A . catid | 1 | | | 1 | SIMPLE | C | eq_ref | PRIMARY | PRIMARY | 4 | joomla_test . A . sectionid | 1 | Using index | +----+-------------+-------+--------+---------------+---------+---------+-------------------------+------+-------------+ 3 rows in set ( 0.00 sec )
这个结果表明:对where条件里涉及到的字段,Mysql会使用索引进行搜索,而这个索引的使用也对排序的效率有很好的提升。
写了段程序测试了一下,分别让以下两个SQL语句执行200次:
select A . id , A . title , B . title from jos_content A left join jos_categories B on A . catid = B . id left join jos_sections C on A . sectionid = C . id select A . id , A . title , B . title from jos_content A , jos_categories B , jos_sections C where A . catid = B . id and A . sectionid = C . id select A . id , A . title , B . title from jos_content A left join jos_categories B on A . catid = B . id left join jos_sections C on A . sectionid = C . id order by rand () limit 10 select A . id from jos_content A left join jos_categories B on B . id = A . catid left join jos_sections C on A . sectionid = C . id order by A . id
结果是第(1)条平均用时20s ,第(2)条平均用时44s ,第(3)条平均用时70s ,第(4)条平均用时2s 。而且假如我们用explain观察第(3)条语句的执行情况,会发现它创建了temporary表来进行排序。
综上所述,可以得出如下结论:
1. 对需要查询和排序的字段要加索引。
2. 在一定环境下,left join还是比普通连接查询效率要高,但是要尽量少地连接表,并且在做连接查询时注意观察索引是否起了作用。
3. 排序尽量对第一个表的索引字段进行,可以避免mysql创建临时表,这是非常耗资源的。
4. 对where条件里涉及到的字段,应适当地添加索引,这样会对排序操作有优化的作用。
5. 在做随机抽取数据的需求时,避免使用order by rand(),从上面的例子可以看出,这种是很浪费数据库资源的,在执行过程中用show processlist查看,会发现第(3)条有Copying to tmp table on disk。而对(3)和(4)的对比得知,如果要实现这个功能,最好另辟奚径,来减轻Mysql的压力。
6. 从第4点可以看出,如果说在分页时我们能先得到主键,再根据主键查询相关内容,也能得到查询的优化效果。通过国外《High Performance MySQL》专家组的测试可以看出,根据主键进行查询的类似“SELECT ... FROM... WHERE id = ...”的SQL语句(其中id为PRIMARYKEY),每秒钟能够处理10000次 以上的查询,而普通的SELECT查询每秒只能处理几十次到几百次 。涉及到分页的查询效率问题,网上的可用资源越来越多,查询功能也体现出了它的重要性。也便是sphinx、lucene这些第三方搜索引擎的用武之地了。
7. 在平时的作业中,可以打开Mysql的Slow queries功能,经常检查一下是哪些语句降低的Mysql的执行效率,并进行定期优化。
相关推荐
文章目录测试数据1 sql执行顺序2 order by 和 group by什么时候会出现Using filesort — 理论3 order by 和 group by什么时候会出现Using filesort — 实践3.1 不会出现 Using filesort的情况 — 符合最佳左前缀法则...
SQL优化 Explain的使用详解 mysql
SQL语句性能分析之explain
语法格式如下 EXPLAIN tbl_name 或者: EXPLAIN SELECT select_options EXPLAIN 语句可以被当作 DESCRIBE 的同义词来用,也可以用来获取一个MySQL要执行的 SELECT 语句的相关信息。 EXPLAIN tbl_name 语法和 DESCRIBE...
Mysql数据库优化实战,里面有一些小的例子来说明执行计划的讲解。
4.EXPLAIN验证SQL是否走索引 5.结合autotrace创建并验证函数索引 6.sql trace分析工具--TKPROF详细讲解 7.V$SQL视图详解加几个实例 8.autotrace验证压缩表性能 9.autotrace验证消除子查询后的性能 10.基于基本的优化...
而我们在不考虑进行分库分表的操作时,进行SQL语句优化是一个很好的解决办法,下面介绍explain关键词分析SQL语句,及使用索引进行优化查询。 explain关键字使用 explain使用格式 EXPLAIN SELECT * FROM SCORE ...
102 透彻研究通过explain命令得到的SQL执行计划(3).pdf
1.如何分析SQL语句 2.选用适合的ORACLE优化器 3.用EXPLAIN PLAN 分析SQL语句 4.使用TKPROF 工具来查询SQL性能状态 5.表分区的应用
2. SQL优化 2.1优化SQL的一般步骤 2.2 索引问题. 2.3两个常用的优化技巧 2.4常用SQL优化 2.5常用SQL技巧 3.优化数据库对象 3.1优化表的数据类型逆规范化 3.2提高查询速度 4.锁问题 4.1MyISQM表锁 4.2...
看懂MySQL的SQL EXPLAIN
面试官:不会看 Explain执行计划,简历敢写 SQL 优化?.mhtml
知识点标签:explain、sql优化、索引、sql性能问题 题目描述 MySQL执行计划及SQL优化 1.SQL语句表头运行一个explain时,执行后所显示的表头字段如下: id : select查询的序列号,包含一组数字,表示查询中执行select...
SQL_clear_explain_2nd.rar 学习sql排名第二的书
explain分析sql具体字段含义脑图
100 透彻研究通过explain命令得到的SQL执行计划(1).pdf
1 性能优化 1.1 避免频繁 commit,尤其是把 commit 写在循环体中每次循环都进行commit。 1.2 使用绑定变量,避免常量的直接引用。 示例:以下书写不符合本规范。 INSERT INTO sm_users (user_id, user_name, ...
MySQL 性能优化 Explain ,MySQL 性能优化 ExplainMySQL 性能优化 ExplainMySQL 性能优化 ExplainMySQL 性能优化 ExplainMySQL 性能优化 ExplainMySQL 性能优化 ExplainMySQL 性能优化 Explain
非常全面的ORACLE SQL性能优化介绍及实例:优化器的选择、共享SQL语句、访问Table的方式、减少访问数据库的次数、使用DECODE函数、减少对表的查询、通过内部函数提高SQL效率、用NOT EXISTS替代NOT IN、使用EXPLAIN ...