bitscn.com
mysql查询语句执行过程及性能优化-查询过程及优化方法（join/order by）
[plain] mysql> explain select user.username, user_profile.nickname, user_profile.gender, user_profile.meet_receive from user_profile join users on users.id = user_profile.user_id where user_profile.`display` = '1' order by user_profile.fl_no limit 50;
+----+-------------+---------------------+--------+---------------+---------+---------+----------------------------------------+-------+----------------------------------------------+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | extra |+----+-------------+---------------------+--------+---------------+---------+---------+----------------------------------------+-------+----------------------------------------------+| 1 | simple | user_profile | all | null | null | null | null | 14399 | using where; using temporary; using filesort || 1 | simple | users | eq_ref | primary | primary | 8 | icbd_cmsdb.user_profile.user_id | 1 | using where |+----+-------------+---------------------+--------+---------------+---------+---------+----------------------------------------+-------+----------------------------------------------+2 rows in set (0.00 sec)
可以看到上述的查询需要检查1万多记录，并且使用了临时表和filesort排序，这样的查询在用户数快速增长后将成为噩梦。
在优化这个语句之前，我们先了解下sql查询的基本执行过程：
*）应用通过mysql api把查询命令发送给mysql服务器，然后被解析
*）检查权限、mysql optimizer进行优化，经过解析和优化后的查询命令被编译为cpu可运行的二进制形式的查询计划（query plan），并可以被缓存
*）如果存在索引，那么先扫描索引，如果数据被索引覆盖，那么不需要额外的查找，如果不是，根据索引查找和读取对应的记录
*）如果有关联查询，查询次序是扫描第一张表找到满足条件的记录，按照第一张表和第二张表的关联键值，扫描第二张表查找满足条件的记录，按此顺序循环
*）输出查询结果，并记录binary logs
显然合适的索引将大大简化和加速查找。再看一下上面那条查询语句，除了条件查询外，还有关联查询以及order by即排序操作，
那么让我们进一步了解下关联查询（join）和order by是怎么工作的，mysql有三种方式来处理关联查询和数据排序：
[plain] 方法 explain结果显示 use index-based access method that produces ordered output 不会出现filesort use filesort() on 1st non-constant table “using filesort”出现在第一行extra中 put join result into a temporary table and use filesort() on it “using temporary; using filesort”出现在第一行extra中
第一种方法是基于索引，第二种是对第一个非常量表进行filesort（quicksort），还有一种是把联合查询的结果放入临时表，然后进行filesort。
filesort有两种模式：
1、模式1：排序后的元素涵盖了要输出的数据。排序结果是一串有序序列元素组，不再需要额外的记录读取；
2、模式2：排序结果是键值对序列，通过这些row_ids再去读取记录（随机读取，效率低下）；
第一种方法用于第一个非常量表中存在order by所依赖的列的索引，那就可直接使用已经有序的索引来查找关联表的数据，这种方式是性能最优的，因为不需要额外的排序动作：
第二种方式用于order by所依赖的列全部属于第一张查询表且没有索引，那么我们可以先对第一张表的记录进行filesort（模式可能是模式1也可能是模式2），得到有序行索引，然后再做关联查询，filesort的结果可能是在内存中，也可能在硬盘上，这取决于系统变量sort_buffer_size（一般为2m左右）：
第三种方法用于当order by的元素不属于第一张表时，需要把关联查询的结果放入临时表，最后对临时表进行filesort：
第三种方法中的临时表，可能是在内存中(in-memory table)，也可能是在硬盘上，一般是下面两种情况会使用硬盘（on-disk table）：
（1）使用了blob，text类型的数据
（2）内存表占用超过了系统变量tmp_table_size/max_heap_table_size的限定（一般为16m左右），只能放在硬盘上
从上面的查询执行过程和方式，我们应该可以清楚的知道为什么using filesort，using temporary会严重的影响查询性能，因为如果数据类型或者字段设计有问题，
在需要查询的表以及结果中存在大数据的字段，而没有合适的索引可用时，都可能会导致产生大量的io操作，这就是查询性能缓慢的根源所在。
回到文章开头所举的查询实例，它显然是使用了效率最低的第三种方法，我们需要做和尝试的优化手段有：
1、为users.fl_no添加索引，为select和where所使用的字段建立索引
2、把users.fl_no转移到或者作为冗余字段添加到表user_profile中
3、去除text类型的字段，text可以替换为varchar（65535）或对于中文而言varchar（20000）
4、如果实在无法消除using filesort，那么提高sort_buffer_size，以减少io操作负担
5、尽量使用第一张表所覆盖的索引进行排序，实在不行，可以把排序逻辑从mysql中移到php/java程序中执行
实施1、2、3的优化方法后，explain结果如下：
[plain] mysql> explain select user.username, user_profile.nickname, user_profile.gender, user_profile.meet_receive from user_profile join users on users.id = user_profile.user_id where user_profile.`display` = '1' order by user_profile.fl_no limit 50; +----+-------------+---------------+--------+---------------+---------+---------+---------------------------------+------+--------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | extra | +----+-------------+---------------+--------+---------------+---------+---------+---------------------------------+------+--------------------------+ | 1 | simple | user_profile1 | index | null | fl_no | 4 | null | 50 | using where | | 1 | simple | users | eq_ref | primary | primary | 8 | slowquery.user_profile1.user_id | 1 | using where | +----+-------------+---------------+--------+---------------+---------+---------+---------------------------------+------+--------------------------+ 2 rows in set (0.00 sec)
备注：编写简单的php应用，用siege测试，查询效率提高>3倍。
bitscn.com