注册 登录  
 加关注
   显示下一条  |  关闭
温馨提示!由于新浪微博认证机制调整,您的新浪微博帐号绑定已过期,请重新绑定!立即重新绑定新浪微博》  |  关闭

onefish资料库

成功要素---耐得住孤独!

 
 
 

日志

 
 

MySQL 文件排序 &索引排序  

2016-06-06 18:06:34|  分类: 数据库 |  标签: |举报 |字号 订阅

  下载LOFTER 我的照片书  |

排序操作是非常消耗cpu的操作,当系统设置不当或query取出的字段过多时,还可以造成MySQL不得不放弃优化后的排序算法,而使用较为古老的需要两次IO的读取表数据的排序算法,使排序效率非常低下。

利用索引进行排序操作,主要是利用了索引的有序性。在通过索引进行检索的过程中,就已经得到了有序的数据访问顺序,依次读取结果数据后就不需要进行排序操作,进而避免了此操作,提高了排序结果集的query性能。

很简单,尽量使用索引排序,这就对了。

做个实验,表结构如下

[sql] view plain copy
 在CODE上查看代码片派生到我的代码片
  1. mysql> show create table artist \G  
  2. *************************** 1. row ***************************  
  3.        Table: artist  
  4. Create TableCREATE TABLE `artist` (  
  5.   `artist_id` int(10) unsigned NOT NULL,  
  6.   `type` enum('Band','Person','Unknown','Combination'NOT NULL,  
  7.   `namevarchar(255) NOT NULL,  
  8.   `gender` enum('Male','Female'DEFAULT NULL,  
  9.   `founded` year(4) DEFAULT NULL,  
  10.   `country_id` smallint(5) unsigned DEFAULT NULL,  
  11.   PRIMARY KEY (`artist_id`),  
  12.   UNIQUE KEY `name` (`name`)  
  13. ) ENGINE=InnoDB DEFAULT CHARSET=latin1  
  14. 1 row in set (0.00 sec)  

使用文件排序:

[sql] view plain copy
 在CODE上查看代码片派生到我的代码片
  1. mysql> Explain select name, founded from artist where name like 'AUSTRALIA%' order by founded \G  
  2. *************************** 1. row ***************************  
  3.            id: 1  
  4.   select_type: SIMPLE  
  5.         table: artist  
  6.          type: range  
  7. possible_keys: name  
  8.           keyname  
  9.       key_len: 257  
  10.           ref: NULL  
  11.          rows: 22  
  12.         Extra: Using index condition; Using filesort  
  13. 1 row in set (0.00 sec)  

查看结果:

[sql] view plain copy
 在CODE上查看代码片派生到我的代码片
  1. mysql> show session status like '%sort%';  
  2. +-------------------+-------+  
  3. | Variable_name     | Value |  
  4. +-------------------+-------+  
  5. | Sort_merge_passes | 0     |  
  6. | Sort_range        | 0     |  
  7. | Sort_rows         | 0     |  
  8. | Sort_scan         | 0     |  
  9. +-------------------+-------+  
  10. rows in set (0.00 sec)  
  11.   
  12. mysql> select name, founded from artist where name like 'AUSTRALIA%' order by founded \G  
  13. ......  
  14. 22 rows in set (0.00 sec)  
  15.   
  16. mysql> show session status like '%sort%';  
  17. +-------------------+-------+  
  18. | Variable_name     | Value |  
  19. +-------------------+-------+  
  20. | Sort_merge_passes | 0     |  
  21. | Sort_range        | 1     |  
  22. | Sort_rows         | 22    |  
  23. | Sort_scan         | 0     |  
  24. +-------------------+-------+  
  25. rows in set (0.00 sec)  


几个参数

sort_merge_passes  由于sort buffer不够大,不得不将需要排序的数据进行分段,然后再通过sort merge的算法完成整个过程的merge总次数,一般整个参数用来参考sort buffer size 是否足够。

sort range session/global级别(单位:次) 通过range scan完成的排序总次数。

sort rows   session/global 级别(单位:row) 排序的总行数。

sort scan   通过扫描表完成的排序总次数。


索引排序,其中extra中的filesort不见了

[sql] view plain copy
 在CODE上查看代码片派生到我的代码片
  1. mysql> Explain select name, founded from artist where name like 'AUSTRALIA%' order by name \G  
  2. *************************** 1. row ***************************  
  3.            id: 1  
  4.   select_type: SIMPLE  
  5.         table: artist  
  6.          type: range  
  7. possible_keys: name  
  8.           keyname  
  9.       key_len: 257  
  10.           ref: NULL  
  11.          rows: 22  
  12.         Extra: Using index condition  
  13. 1 row in set (0.00 sec)  


结果:

[sql] view plain copy
 在CODE上查看代码片派生到我的代码片
  1. mysql> show session status like '%sort%';  
  2. +-------------------+-------+  
  3. | Variable_name     | Value |  
  4. +-------------------+-------+  
  5. | Sort_merge_passes | 0     |  
  6. | Sort_range        | 0     |  
  7. | Sort_rows         | 0     |  
  8. | Sort_scan         | 0     |  
  9. +-------------------+-------+  
  10. rows in set (0.00 sec)  
  11.   
  12. mysql> select name, founded from artist where name like 'AUSTRALIA%' order by name \G  
  13. ......  
  14. 22 rows in set (0.00 sec)  
  15.   
  16. mysql> show session status like '%sort%';  
  17. +-------------------+-------+  
  18. | Variable_name     | Value |  
  19. +-------------------+-------+  
  20. | Sort_merge_passes | 0     |  
  21. | Sort_range        | 0     |  
  22. | Sort_rows         | 0     |  
  23. | Sort_scan         | 0     |  
  24. +-------------------+-------+  
  25. rows in set (0.00 sec)  

这次所有的参数都没有改变,这就是索引排序的力量,呵呵。
  评论这张
 
阅读(41)| 评论(0)
推荐 转载

历史上的今天

在LOFTER的更多文章

评论

<#--最新日志,群博日志--> <#--推荐日志--> <#--引用记录--> <#--博主推荐--> <#--随机阅读--> <#--首页推荐--> <#--历史上的今天--> <#--被推荐日志--> <#--上一篇,下一篇--> <#-- 热度 --> <#-- 网易新闻广告 --> <#--右边模块结构--> <#--评论模块结构--> <#--引用模块结构--> <#--博主发起的投票-->
 
 
 
 
 
 
 
 
 
 
 
 
 
 

页脚

网易公司版权所有 ©1997-2017