mysql底层算法_SQL解析顺序与MySQL底层实现

排序：

ORDER BY

LIMIT

这里是指拿到结果之后的排序，而通常这种是性能比较低的情况，通过explain分析SQL，如果Extra显示Using filesort则是这种情况。

一般会借助有序索引来排序，即如果order by的列是加了有序索引(MySQL的B-TREE)，则可以在存储引擎层就完成了排序。但是这里有个前提条件是该列必须是where中的其中一个条件并且该条件符合使用索引的规则，如下：

order by的列trade_date不在where中，extra显示Using filesort，表示使用外部排序，不能使用有序索引来排序：

mysql> explain select trade_date from store_order_day where ul='11111' order by trade_date

-> ;

+----+-------------+-------------------+------------+------+------------------------+------------------------+---------+-------+------+----------+------------------------------------------+

| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |

+----+-------------+-------------------+------------+------+------------------------+------------------------+---------+-------+------+----------+------------------------------------------+

| 1 | SIMPLE | store_order_day | NULL | ref | idx_ul_type_order_price | idx_ul_type_order_price | 768 | const | 5 | 100.00 | Using where; Using index; Using filesort |

+----+-------------+-------------------+------------+------+------------------------+------------------------+---------+-------+------+----------+------------------------------------------+

1 row in set, 1 warning (0.00 sec)

当把order by的列trade_date加到where查询条件时，则extra不再存在Using filesort，可以使用索引排序了，故不会走到这步拿到结果再排序：

mysql> explain select trade_date from store_order_day where ul='11111' and trade_date='2019-03-25' order by trade_date;

+----+-------------+-------------------+------------+------+--------------------------------+---------+---------+-------+------+----------+-------------+

| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |

+----+-------------+-------------------+------------+------+--------------------------------+---------+---------+-------+------+----------+-------------+

| 1 | SIMPLE | store_order_day | NULL | ref | PRIMARY,idx_ul_type_order_price | PRIMARY | 3 | const | 1 | 16.67 | Using where |

+----+-------------+-------------------+------------+------+--------------------------------+---------+---------+-------+------+----------+-------------+

1 row in set, 1 warning (0.00 sec)