背景
上线后，发现服务器的实例陆续挂掉，通过查看日志没有发现什么异常的地方，可以排除内存溢出的问题。查看数据库的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
- SIMPLE ：查询不包括子查询和UNION
- PRIMARY:查询有任何复杂的子部分，最外层部分标记为这个值
- SUBQUERY:select列表中的子查询
- DERIVED :表示包含在from子句的子查询的select
- UNION:在UNION中的第二个和随后的select 被标记为UNION
6.UNION RESULT:用来从UNION的匿名临时表检索结果的select 被标记为union result
table
- 通常是表示该行的表名或者别名
- 派生表和联合市以<derivedN>的形式，其中N表示子查询的id
type
这个是指 mysql如何查询表中的行，依次从最差到最优：
-
ALL
全表扫描 -
index
这个跟全表扫描一样，只是扫描的时候是按索引进行，而不是按行。它主要是避免了排序；如果是按随机次序访问行的话，开销会比较大。
如果是在Extra列中看到“Using index”,说明正在使用覆盖索引，它只是扫描索引的数据，开销比按索引次序全表扫描的开销少很多。 -
range
范围扫描就是一个有限制的索引扫描，一般查询里面带between,或者>,< in,or 会显示这个。开销跟索引类型相当 -
ref
索引访问，索引跟某个参考值相比较，这个参考值或者是一个常数，或者是来自夺标查询前一个表里的结果值 -
eq_ref
使用这种索引查找，数据库知道最多只返回一条符合条件的记录。 -
const,system
当数据库能对查询的某部分进行优化并将其转换成一个常量时，就会使用这种类型 -
NULL
这种访问方式意味着数据库可以在优化阶段分解查询语句，在执行阶段设置用不着再访问表或者索引
possible_keys
着一列显示了查询可以使用哪些索引，这是基于查询访的列和使用的比较操作符来判断
key
显示数据库采用那个索引来优化对该表的访问。possible_keys 揭示了哪个索引能有助于高效的行查找。key显示的是优化采用哪一个索引可以最小化查询成本。
key_len
key_len列显示了在索引字段中可能的最大长度，而不是表中数据使用的实际字节数。
ref
这一列显示了之前的表在key列记录的索引中查找值所用的列或常量。
rows
这一列是数据库为了找到所需的行而要读取的行数，而不是结果集里面的行数。
Extra
这一列包含的是不适合在其他列显示的额外信息。
- Using index :数据库将使用覆盖索引，以避免访问表。
- Using where: 意味服务器将在存储引擎检索行后再进行过滤，暗示查询可收益于不同的索引。
- Using temporary:对查询结果排序时会使用一个临时表
- Using filesort:对结果使用一个外部索引排序，而不是按索引次序从表里读取行。
- 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行）到一个排序文件中。
结论
两个语句都是用了临时文件进行排序的方式，该语句的查询频率比较高，由于只有一个排序的字段的内容只有几个值，而第二个语句是通过该字段，还有主键进行排序，造成这样的结果猜测：
- mysql内部进行优化的查询
- 由于该查询频率比较，每次查询多出的一百多毫秒变成压死骆驼的最后一根稻草。
网友评论