欢迎来到Doc100.Net免费学习资源知识分享平台!
您的位置:首页 > 程序异常 >

mysql查询话语执行过程及性能优化-查询过程及优化方法(join/order by)

更新时间: 2014-01-05 02:09:21 责任编辑: Author_N1

 

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

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

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有三种方式来处理关联查询和数据排序:

方法	                                                    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。

注1:关于什么是非常量表,请参考阅读MySQL开发手册:Consts and Constant Tables,
注2:什么是filesort呢,这不是字面意思的文件排序,filesort有两种模式:
1、模式1:排序后的元素涵盖了要输出的数据。排序结果是一串有序序列元素组,不再需要额外的记录读取;
2、模式2:排序结果是<sort_key,row_id>键值对序列,通过这些row_ids再去读取记录(随机读取,效率低下);
注3:关于什么是临时表,请参考阅读MySQL开发手册:How MySQL Uses Internal Temporary Tables

第一种方法用于第一个非常量表中存在ORDER BY所依赖的列的索引,那就可直接使用已经有序的索引来查找关联表的数据,这种方式是性能最优的,因为不需要额外的排序动作:

diagram

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

diagram

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

diagram

第三种方法中的临时表,可能是在内存中(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> 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
上一篇:上一篇
下一篇:下一篇

 

随机推荐程序问答结果

 

 

如对文章有任何疑问请提交到问题反馈,或者您对内容不满意,请您反馈给我们DOC100.NET论坛发贴求解。
DOC100.NET资源网,机器学习分类整理更新日期::2014-01-05 02:09:21
如需转载,请注明文章出处和来源网址:http://www.doc100.net/bugs/t/6300/
本文WWW.DOC100.NET DOC100.NET版权所有。