背景

上线后，发现服务器的实例陆续挂掉，通过查看日志没有发现什么异常的地方，可以排除内存溢出的问题。查看数据库的show processlist 发现有一个sql锁住了。该sql 是最近一次才上线，通过对比两次上线的内容，发现这次是多了一个排序而已，怎么多了一个排序就导致服务器挂了。

修改前：

select * from table ORDER BY non_index_column limit 10;

修改后：

select * from table ORDER BY non_index_column ,index_column limit 10;

---

explain

首先先想到通过explain命令看一下这个sql是怎么执行，怎么会导致数据库锁住了。

查询结果

select_type

1. SIMPLE ：查询不包括子查询和UNION
2. PRIMARY:查询有任何复杂的子部分，最外层部分标记为这个值
3. SUBQUERY:select列表中的子查询
4. DERIVED :表示包含在from子句的子查询的select
5. UNION:在UNION中的第二个和随后的select 被标记为UNION
   6.UNION RESULT:用来从UNION的匿名临时表检索结果的select 被标记为union result

table

1. 通常是表示该行的表名或者别名
2. 派生表和联合市以<derivedN>的形式，其中N表示子查询的id

type

这个是指 mysql如何查询表中的行，依次从最差到最优：

1. ALL
   全表扫描

2. index
   这个跟全表扫描一样，只是扫描的时候是按索引进行，而不是按行。它主要是避免了排序；如果是按随机次序访问行的话，开销会比较大。
   如果是在Extra列中看到“Using index”,说明正在使用覆盖索引，它只是扫描索引的数据，开销比按索引次序全表扫描的开销少很多。

3. range
   范围扫描就是一个有限制的索引扫描，一般查询里面带between,或者>,< in,or 会显示这个。开销跟索引类型相当

4. ref
   索引访问，索引跟某个参考值相比较，这个参考值或者是一个常数，或者是来自夺标查询前一个表里的结果值

5. eq_ref
   使用这种索引查找，数据库知道最多只返回一条符合条件的记录。

6. const,system
   当数据库能对查询的某部分进行优化并将其转换成一个常量时，就会使用这种类型

7. NULL
   这种访问方式意味着数据库可以在优化阶段分解查询语句，在执行阶段设置用不着再访问表或者索引

possible_keys

着一列显示了查询可以使用哪些索引，这是基于查询访的列和使用的比较操作符来判断

key

显示数据库采用那个索引来优化对该表的访问。possible_keys 揭示了哪个索引能有助于高效的行查找。key显示的是优化采用哪一个索引可以最小化查询成本。

key_len

key_len列显示了在索引字段中可能的最大长度，而不是表中数据使用的实际字节数。

ref

这一列显示了之前的表在key列记录的索引中查找值所用的列或常量。

rows

这一列是数据库为了找到所需的行而要读取的行数，而不是结果集里面的行数。

Extra

这一列包含的是不适合在其他列显示的额外信息。

1. Using index :数据库将使用覆盖索引，以避免访问表。
2. Using where: 意味服务器将在存储引擎检索行后再进行过滤，暗示查询可收益于不同的索引。
3. Using temporary:对查询结果排序时会使用一个临时表
4. Using filesort:对结果使用一个外部索引排序，而不是按索引次序从表里读取行。
5. Range checked for each record(index map:N):没有好用的索引，新的索引将在联接的每一行上重新估算。

---

profiles

前面两个语句执行后的结果都是“Using filesort”的，这个时候只能通过profiles分析一下sql的性能.

type:  
    ALL                --显示所有的开销信息  
  | BLOCK IO           --显示块IO相关开销  
  | CONTEXT SWITCHES   --上下文切换相关开销  
  | CPU                --显示CPU相关开销信息  
  | IPC                --显示发送和接收相关开销信息  
  | MEMORY             --显示内存相关开销信息  
  | PAGE FAULTS        --显示页面错误相关开销信息  
  | SOURCE             --显示和Source_function，Source_file，Source_line相关的开销信息  
  | SWAPS              --显示交换次数相关开销的信息  

查看设置

SHOW VARIABLES LIKE '%pro%';

查询结果

打开开关

set profiling=1;

执行语句

先清空缓存
reset query cache;
查看执行结果
show profiles;
查看某一条执行结果
show profile for query 5;

结果

通过两个语句的性能比较，有2个字段进行排序的时间比一个排序的时间只是多了一点的，查看数据库的缓存：

show variables like '%max_length_for_sort_data%';
1024
show variables like '%read_rnd_buffer_size%';
16M

---

order by limit 优化

Order by和limit一起使用的优化原理

从MySQL5.6.2版本以后，优化器将更加智能地处理下面形式的查询了

SELECT ... FROM single_table ... ORDER BY non_index_column [DESC] LIMIT [M,]N;

这种在很大的结果集中只返回很少的行数的查询类型在web应用中非常常见，比如

SELECT col1, ... FROM t1 ... ORDER BY name LIMIT 10;

SELECT col1, ... FROM t1 ... ORDER BY RAND() LIMIT 15;

排序缓存有一个参数是sort_buffer_size，如果这个参数大小足够上面范例中的N行的排序结果集(如果M也被定义，那就是M+N行的结果集大小)，那么服务器将会避免一个文件排序操作，使得排序完全在内存中完成。

内存排序+limit原理

1 扫描表，在内存中插入那些被选择排序的列的数据到一个排好序的队列中，比如order by col1，col2，则插入col1和col2列的数据。如果队列满了，则挤出排序在末尾的数据。

2 返回队列中的前N行记录，如果M也被定义，则调到第M行开始返回后续的N行记录。

文件排序+limit原理

1扫描表，重复步骤2和3，直到表的结尾

2选中这些行数直到排序缓存被填满

3在排序缓存中写入第一个N行（如果M被定义，则M+N行）到一个排序文件中。

结论

两个语句都是用了临时文件进行排序的方式，该语句的查询频率比较高，由于只有一个排序的字段的内容只有几个值，而第二个语句是通过该字段，还有主键进行排序，造成这样的结果猜测：

1. mysql内部进行优化的查询
2. 由于该查询频率比较，每次查询多出的一百多毫秒变成压死骆驼的最后一根稻草。