星期日, 2018-03-18 00:13 — adminshiping1

先看一段sql：

[sql] view plain copy
<span style="font-size:18px;">SELECT  
    *  
FROM  
    rank_user AS rankUser  
LEFT JOIN rank_user_level AS userLevel ON rankUser.id = userLevel.user_id  
LEFT JOIN rank_product AS product ON userLevel.new_level = product.level_id  
LEFT JOIN rank_product_fee AS fee ON userLevel.fee_id = fee.fee_id  
LEFT JOIN rank_user_login_stat AS userLoginInfo ON rankUser.id = userLoginInfo.user_id  
ORDER BY  
     rankUser.create_time DESC  
LIMIT 10 OFFSET 0</span>  

介绍一下这段sql的表的构成：一张主表：rank_user；两张跟rank_user直接关联（多张表通过同一字段最好是主键进行关联）的表：rank_user_level ，rank_user_login_stat ；两张跟rank_user非直接关联的表：rank_product ，rank_product_fee 。这段sql看似简单，但是执行时间却很长，我们来看一下执行计划：

执行时间1.45s，可以看到，这段不仅仅扫描全表，而且使用了临时表，进行了文件排序。

为了找到原因，我们把排序去掉看一下：

[sql] view plain copy
SELECT  
    *  
FROM  
    rank_user AS rankUser  
LEFT JOIN rank_user_level AS userLevel ON rankUser.id = userLevel.user_id  
LEFT JOIN rank_product AS product ON userLevel.new_level = product.level_id  
LEFT JOIN rank_product_fee AS fee ON userLevel.fee_id = fee.fee_id  
LEFT JOIN rank_user_login_stat AS userLoginInfo ON rankUser.id = userLoginInfo.user_id  
-- ORDER BY  
    -- rankUser.create_time DESC  
LIMIT 10 OFFSET 0  

执行时间0.015s，扫描行数67452，果然是排序惹的祸。但是仅仅是排序惹的祸吗？别忘了这里有两张非直接关联的表，这样的查询，如果有查询条件或者排序分组的时候往往都需要创建临时表（这个没有办法，想想也知道）。为了验证这个观点，我们把两张非直接关联的表去掉看一下：

[sql] view plain copy
SELECT  
    *  
FROM  
    rank_user AS rankUser  
LEFT JOIN rank_user_level AS userLevel ON rankUser.id = userLevel.user_id  
-- LEFT JOIN rank_product AS product ON userLevel.new_level = product.level_id  
-- LEFT JOIN rank_product_fee AS fee ON userLevel.fee_id = fee.fee_id  
LEFT JOIN rank_user_login_stat AS userLoginInfo ON rankUser.id = userLoginInfo.user_id  
ORDER BY  
    rankUser.create_time DESC  
LIMIT 10 OFFSET 0  

执行时间0.003s,扫描行数10，屌爆了有木有，mysql多表直接关联在没有其他筛选条件的情况下，查询速度大大提升，而且排序可以使用create_time这个索引，直接取到前十条。

到了这里，我想大家应该已经明白第一条sql查询时间很长的原因了：多表非直接关联的前提下还要排序。mysql查询往往最需要优化的地方就是临时表和文件排序了。这里总结一下教训：

1.mysql查询存在直接关联和非直接关联的问题，这两种查询效率差别很大；

2.mysql排序尽量使用索引；

3.mysql多表关联left join其他表的时候，如果以其他表的字段作为查询条件都会产生临时表；

4.mysql在非直接关联的基础上进行排序会很慢，需要进行优化；

知道了问题，我们就好优化了，这里我给出了两种方案：

第一种（子查询，适合子查询部分不作为查询条件）：

[sql] view plain copy
SELECT  
            rankUser.id, rankUser.qq, rankUser.phone, rankUser.regip, rankUser.channel, rankUser.create_time, rankUser.qudao_key, rankUser.qq_openid, rankUser.wechat_openid,  
            userLevel.recommend_count,userLevel.end_time,userLevel.new_level,userLevel.`level`,userLevel.new_recommend_count,userLevel.`is_limited`,  
            (case when userLevel.new_level > 1 then 1 else 0 end) is_official_user,  
            (select product_name from rank_product where level_id = userLevel.new_level) product_name,  
            (select period from rank_product_fee where fee_id = userLevel.fee_id) period,  
            userLoginInfo.last_login, userLoginInfo.login_count, userLoginInfo.login_seconds  
        FROM rank_user AS rankUser  
        LEFT JOIN rank_user_level as userLevel on userLevel.user_id=rankUser.id  
        LEFT JOIN rank_user_login_stat as userLoginInfo ON rankUser.id = userLoginInfo.user_id  
ORDER BY  
    rankUser.create_time DESC  
LIMIT 10 OFFSET 0  

第二种（非直接关联转变成直接关联，这个要根据业务来定，我这里rank_product和rank_product_fee两张表只是为了查询rank_user_level表中的产品和产品费用信息，而rank_user_level是一张直接关联的表，故这里可以先将这三张表进行合并，然后再和rank_user表进行联合查询）：

[sql] view plain copy
SELECT  
    *  
FROM  
    rank_user AS rankUser  
LEFT JOIN (  
        select   
        l.*,p.product_name,f.period   
        from   
        rank_user_level l,rank_product p,rank_product_fee f  
        where   
        l.new_level = p.level_id   
        and l.fee_id = f.fee_id  
) AS userLevel ON rankUser.id = userLevel.user_id  
LEFT JOIN rank_user_login_stat AS userLoginInfo ON rankUser.id = userLoginInfo.user_id  
ORDER BY  
    rankUser.create_time DESC  
LIMIT 10 OFFSET 0  

来自 http://blog.csdn.net/shandalue/article/details/51658920

MYSQL EXPLAIN解析一 EXTRA中的USING INDEX，USING WHERE，USING INDEX CONDITION

1.简单介绍

using index 和using where只要使用了索引我们基本都能经常看到，而using index condition则是在mysql5.6后新加的新特性，我们先来看看mysql文档对using index condition的描述

附上mysql文档链接：https://dev.mysql.com/doc/refman/5.7/en/index-condition-pushdown-optimization.html

简单来说，mysql开启了ICP的话，可以减少存储引擎访问基表的次数

下面来简单的介绍一下这三者的区别

using index ：使用覆盖索引的时候就会出现

using where：在查找使用索引的情况下，需要回表去查询所需的数据

using index condition：查找使用了索引，但是需要回表查询数据

using index & using where：查找使用了索引，但是需要的数据都在索引列中能找到，所以不需要回表查询数据

以上四点就能看出它们之前的区别，或许有部分人都存在疑惑 using index & using where 和using index condition那个比较好，从上面的的解释中就能看出是前者比较好，毕竟不需要回表查询数据，效率上应该比较快的

下面是在stackoverflow中找到的答案：

附上stackoverflow链接：https://stackoverflow.com/questions/28759576/mysql-using-index-condition-vs-using-where-using-index

2.测试验证

（1）建立一个userinfo表，其字段信息如下图所示：

（2）查询测试

（2.1）测试Using index

覆盖索引，但是没有使用查询条件，所以只有Using index

（2.2）测试Using index & Using where

覆盖索引，但是后面带了查询条件，所以也用了Using where查询索引

（2.3）测试Using index condition

因为本人用的是mysql版本是5.5，所以显示了Using where，如果用5.6以上的版本应该就会显示Using index condition，个人认为，Using index condition其实就是优化了Using where这种情况，有条件的也可以自己写个demo测试一下

以上皆为个人理解，如果有理解错的地方，欢迎指出

来自 https://www.cnblogs.com/zhp-king/p/7250810.html

EXPLAIN sql优化方法（2） Using temporary ; Using filesort

博客分类：

软件设计

SQL MySQL C C++C#

优化GROUP BY语句

默认情况下，MySQL对所有GROUP BY col1，col2...的字段进行排序。这与在查询中指定ORDER BY col1,col2...类似。因此，如果显式包括一个包含相同的列的ORDER BY子句，则对MySQL的实际执行性能没有什么影响。如果查询包括GROUP BY 但用户想要避免排序结果的消耗，则可以指定ORDER By NULL禁止排序，例如：

Java代码  
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的字段都是升序或者都是降序。

例如：

Java代码  
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;  

但是以下的情况不使用索引：

Java代码  
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  

Java代码  
 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做如下改动：

Java代码  
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的时候会有以下发现：

Java代码  
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：

Java代码  
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也没有再出现

Java代码  
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次：

Java代码  
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的执行效率，并进行定期优化。

来自 http://hudeyong926.iteye.com/blog/785181

执行计划中Using filesort,Using temporary相关语句的优化解决
2013-01-09 13:05:15 0 个评论
收藏我要投稿

昨天听开发人员提到，相关的彩票网页当中一个页面刷新的很慢，特别是在提取数据的时候，
今天早上一到，便去找开发人员要去相关的也没进行浏览，窥探哪些数据出现了问题，开发人员
使用PHP开发，所以我用IE很容易就可以窥探到哪些sql执行的很慢，比如下；
www.2cto.com

这个图上列出了，也没中取sql语句的相关执行时间预估比例，以此我可以探查到大概哪些语句会
影响到我们的业务系统！首先看到了有个500,200毫秒的问题，熟话说，枪打出头鸟，哈哈，优化
也一样，先把大的问题解决了，在来收拾小的问题（小的问题，也有可能受到大问题的干预造成），
于是我便把该语句找出来；如下；

SELECT
a.user_name as username,
a.order_date as ordertime,
a.bonus_value as bonus,
cm.name_1 as lname
FROM
lot_sellform AS a
INNER JOIN
code_mst AS cm ON a.lottery_id = cm.cd AND a.lottery_type = cm.lot_type
WHERE
a.bonus_value > 0
ORDER BY
a.order_date DESC
limit
10
www.2cto.com
基本上改弄的索引信息都弄到了，但是我在页面中却看到了这样的情况；如图；

看到type类型基本都走了索引，而且extra列内还有using temporary，using filesort，他们用到了
临时表和在文件内进行了排序，才返回出来，这肯定不是按照我们原先设计的最优路线来走的，
而且相关的索引路线都没走上，这里我有查了相关的资料，在官网上，看到如下内容；（我用蓝色
来表名相关的信息）

在某些情况中，MySQL可以使用一个索引来满足ORDER BY子句，而不需要额外的排序。

www.2cto.com

即使ORDER BY不确切匹配索引，只要WHERE子句中的所有未使用的索引部分和所有额外的
ORDER BY 列为常数，就可以使用索引。下面的查询使用索引来解决ORDER BY部分：

SELECT * FROM t1

ORDER BY key_part1,key_part2,... ;

SELECT * FROM t1

WHERE key_part1=constant

ORDER BY key_part2;

SELECT * FROM t1

ORDER BY key_part1 DESC, key_part2 DESC;

SELECT * FROM t1

WHERE key_part1=1

ORDER BY key_part1 DESC, key_part2 DESC;

这几句话严重勾起了我的兴趣，爱好！哈，在排序中，去查看没有进行索引，而且我在日期列上
添加了btree索引了！怎么会没走呢？以下是图信息；
www.2cto.com

从上图可以看出，排序仍然是在临时表，和文件中进行了，而且type还是ALL比较耗时的操作，
这里我又会想起前面官网中提及到的，key_part1,key_part2这两列，在where语句中，和order by中
出现的比率这么频繁，而且上面说，如果where语句中只要为啥用索引语句列的部分，和所有order by
列的数据如果为常数，可以使用索引路线来走，那如果我对两者来进行彼此的绑定了，比如；让其
来做个组合索引！
www.2cto.com
首先where条件中bonus_value的值，我们取得是常数，而且在进行排序的时候，我们选择的是
order_date日期的列值，如果彼此来进行绑定组合，sql在选择路线的窥探中首先会尝试，组合索
引中位于第一列的数列，进行handle的锁定，遍历到数值后会继续留住该handle的位于LRU列表
头中，接着继续进行数值的排序遍历结果集合，直到handle列被挤出index维护的元头之外！

其实这个不是让其走我们的bonus_value，order_date索引路径，而且让其走到我们前面INNER JOIN
中的索引路线，避免了让数据在临时表中出现，或者在磁盘文件中排序，其实就是增大了，我们在链接
条件中我们设计索引路线的概率问题！有点声东击西的概念！哈！以下图供参考：

以此看到走了我们需要的索引路径了！

来自 https://www.2cto.com/database/201301/181965.html

EXPLAIN sql优化方法（2） Using temporary ; Using filesort

航空母舰 2016-05-12 11:20:33 浏览2640 评论0

摘要：优化GROUP BY语句默认情况下，MySQL对所有GROUP BY col1，col2...的字段进行排序。这与在查询中指定ORDER BY col1,col2...类似。因此，如果显式包括一个包含相同的列的ORDER BY子句，则对MySQL的实际执行性能没有什么影响。

优化GROUP BY语句

Java代码  
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语句

例如：

Java代码  
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;  

但是以下的情况不使用索引：

Java代码  
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  

Java代码  
 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     |  
+----+-------------+-------+--------+---------------+---------+---------+-------------------------+-------+----------------+  

Java代码  
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 )  

Java代码  
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 )  

Java代码  
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也没有再出现

Java代码  
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 )  

Java代码  
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  

来自 https://yq.aliyun.com/articles/42450

普通分类:

mysql

You are here

mysql查询优化--临时表和文件排序（Using temporary; Using filesort问题解决）有大用有大大用

MYSQL EXPLAIN解析一 EXTRA中的USING INDEX，USING WHERE，USING INDEX CONDITION

EXPLAIN sql优化方法（2） Using temporary ; Using filesort

EXPLAIN sql优化方法（2） Using temporary ; Using filesort

友情链接

搜索表单

用户登录

You are here

mysql查询优化--临时表和文件排序（Using temporary; Using filesort问题解决） 有大用 有大大用

MYSQL EXPLAIN解析一 EXTRA中的USING INDEX，USING WHERE，USING INDEX CONDITION

EXPLAIN sql优化方法（2） Using temporary ; Using filesort

EXPLAIN sql优化方法（2） Using temporary ; Using filesort

友情链接

mysql查询优化--临时表和文件排序（Using temporary; Using filesort问题解决）有大用有大大用