[mysql]SQL语句性能优化--Order by中加DESC慢很多的原因调查与处理

1. mysql在数据量较大的时候、使用order by查询结果集时速度很慢的原因可能有以下几种：

1） 排序字段不在同一张表中

2） 排序字段没有建索引

3）排序字段加DESC后索引没有起作用（如何让索引起作用才是关键、且听下文分解）

4）排序字段中加函数导致索引不起作用（这种一定要避免、本文不对这种情况展开说明）

5）排序字段中含有TEXT或CLOB字段（改成VARCHAR字段）

2. 用实例说明排序字段中增加DESC后索引不起作用、查询速度很慢

1）例如以下SQL、执行起来需要5秒左右、太慢不可接受：

SELECT T.WK_ID, T.WK_NAME, C.CR_CODE, T.AT_BEGIN_TIME, T.WK_BOOK
  FROM BO_COPYRIGHT C
 INNER JOIN ES_WORKS_INFO T
    ON T.CR_ID = C.CR_ID
   AND C.WK_ID_VALID = T.WK_ID
   AND T. DELETEFLAG = 0
 ORDER BY T.AT_BEGIN_TIME DESC, T.WK_BOOK

 LIMIT 1, 100

其中 ES_WORKS_INFO 的AT_BEGIN_TIME 和WK_BOOK建有联合索引

2）性能慢的原因分析

实际上查看执行计划后发现索引没有起作用、Using where; Using filesort。

执行计划如下：

SIMPLE  t  ALL        PRIMARY,idx_CR_ID                                                         480006   Using where; Using filesort

SIMPLE  c  eq_ref     PRIMARY,idx_WK_VALID   PRIMARY  4   nrps.t.CR_ID   1             Using where

Using filesort。是的，看到它，说明我们的查询需要优化了：文件排序是通过相应的排序算法，将取得的数据在内存中进行排序。联合索引没有起作用。将ORDER BY中的DESC去掉后执行完只要0.3秒。那为什么加了DESC后会变慢呢？业务要求必须加上DESC怎么办呢？

先来分析一下SQL执行慢的底层原因是什么？

我先查了一下复合索引的字段顺序和order by中的字段顺序是一致的、那为什么还是这么慢呢？为什么还是Using filesort？为什么索引不起作用呢？

MySql 索引创建手册里如是说：
索引列的定义可以跟随 ASC 或者 DESC。这些关键字允许为未来扩展用于指定升序或降序索引值存储。这个语法会被解析但却被忽略。索引列总是以升序排列。——也就是说你写了不会报错，但写了白写。

这样看来，我们的复合索引没起排序作用，原因就在于我们的索引中各字段 asc 存储， order by 里 desc 和 asc(默认是 asc) 混用。为了验证这个说法，我们把该 order by 各个字段换为一致的 desc试试:

SELECT T.WK_ID, T.WK_NAME, C.CR_CODE, T.AT_BEGIN_TIME, T.WK_BOOK
  FROM BO_COPYRIGHT C
 INNER JOIN ES_WORKS_INFO T
    ON T.CR_ID = C.CR_ID
   AND C.WK_ID_VALID = T.WK_ID
   AND T. DELETEFLAG = 0
 ORDER BY T.AT_BEGIN_TIME DESC, T.WK_BOOK DESC

 LIMIT 1, 100

果然性能一下子提高了、执行时间0.3秒结束。

但是有个遗憾如果业务上必须要求一个字段DESC另一个字段ASC的话、这个SQL语句怎么优化能？有哪个大牛知道的话、请赐教！szwangdf@163.com

3.关于ORDER BY慢的情况下可以从以下几点进行优化：

1）ORDER BY的字段改到一种表、不要夸表（设计表结构时需注意这一点）

2）OEDER BY字段建索引、多个字段时建联合索引（联合索引的字段顺序要与ORSER BY中的字段顺序一致）

3）ORDER BY中字段中联合索引的所有字段DESC或ASC要统一，否则索引不起作用

4）不要对TEXT字段或者CLOB字段进行排序

4.以下是另一个技术大牛【陈小峰_iefreer】整理的跟ORDER BY有关的知识点

原文地址：https://blog.csdn.net/iefreer/article/details/12622097