MySQL查询话语执行过程及性能优化-查询过程及优化方法（JOIN/ORDER BY）

MySQL查询语句执行过程及性能优化-查询过程及优化方法（JOIN/ORDER BY）

在上一篇文章MySQL查询语句执行过程及性能优化-基本概念和EXPLAIN语句简介中介绍了EXPLAIN语句，并举了一个慢查询例子：

第二种方式用于ORDER BY所依赖的列全部属于第一张查询表且没有索引，那么我们可以先对第一张表的记录进行filesort（模式可能是模式1也可能是模式2），得到有序行索引，然后再做关联查询，filesort的结果可能是在内存中，也可能在硬盘上，这取决于系统变量sort_buffer_size（一般为2M左右）：

第三种方法用于当ORDER BY的元素不属于第一张表时，需要把关联查询的结果放入临时表，最后对临时表进行filesort：

MySQL查询话语执行过程及性能优化-查询过程及优化方法（JOIN/ORDER BY）

第三种方法中的临时表，可能是在内存中(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结果如下：

mysql&gt; 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倍。

by iefreer

1楼iefreer9小时前: reference link:n1. http://dev.mysql.com/doc/refman/5.5/en/order-by-optimization.htmln2. http://s.petrunia.net/blog/?p=24 (mysql core developer's blog, now moved to mariadb)n3. http://s.petrunia.net/blog/?cat=3n4. http://dev.mysql.com/doc/refman/5.0/en/explain-output.htmln5. http://www.mysqlfaqs.net/mysql-faqs/Indexes/What-is-partial-column-and-prefixed-column-index-or-key-in-MySQLn6. http://openquery.com/blog/partial-indexes-string-columnsn7. http://www.xaprb.com/blog/2009/04/01/how-mysql-really-executes-a-query/n8. http://dev.mysql.com/doc/refman/5.1/en/show-processlist.html

MySQL查询话语执行过程及性能优化-查询

热点推荐