DDR爱好者之家

MySQL中利用索引对数据进行排序的基础教程
站长资源 2024/11/26 佚名
31 1538
DDR爱好者之家 Design By 杰米

MySQL中，有两种方式生成有序结果集：一是使用filesort，二是按索引顺序扫描。利用索引进行排序操作是非常快的，而且可以利用同一索引同时进行查找和排序操作。当索引的顺序与ORDER BY中的列顺序相同且所有的列是同一方向(全部升序或者全部降序)时，可以使用索引来排序。如果查询是连接多个表，仅当ORDER BY中的所有列都是第一个表的列时才会使用索引。其它情况都会使用filesort。

MySQL索引通常是被用于提高WHERE条件的数据行匹配或者执行联结操作时匹配其它表的数据行的搜索速度。
MySQL也能利用索引来快速地执行ORDER BY和GROUP BY语句的排序和分组操作。
通过索引优化来实现MySQL的ORDER BY语句优化：

create table actor(

actor_id int unsigned NOT NULL AUTO_INCREMENT,

name   varchar(16) NOT NULL DEFAULT '',

password    varchar(16) NOT NULL DEFAULT '',

PRIMARY KEY(actor_id),

 KEY   (name)

) ENGINE=InnoDB

insert into actor(name,password) values('cat01','1234567');

insert into actor(name,password) values('cat02','1234567');

insert into actor(name,password) values('ddddd','1234567');

insert into actor(name,password) values('aaaaa','1234567');

 

mysql> explain select actor_id from actor order by actor_id \G


*************************** 1. row ***************************

      id: 1

 select_type: SIMPLE

    table: actor

     type: index

possible_keys: NULL

     key: PRIMARY

   key_len: 4

     ref: NULL

     rows: 4

    Extra: Using index

1 row in set (0.00 sec)

 

mysql> explain select actor_id from actor order by password \G


*************************** 1. row ***************************

      id: 1

 select_type: SIMPLE

    table: actor

     type: ALL

possible_keys: NULL

     key: NULL

   key_len: NULL

     ref: NULL

     rows: 4

    Extra: Using filesort

1 row in set (0.00 sec)

 

mysql> explain select actor_id from actor order by name \G


*************************** 1. row ***************************

      id: 1

 select_type: SIMPLE

    table: actor

     type: index

possible_keys: NULL

     key: name

   key_len: 18

     ref: NULL

     rows: 4

    Extra: Using index

1 row in set (0.00 sec)

下面来罗列一些常见的索引对ORFER BY的优化情况：

1、如果一个SQL语句形如：

SELECT [column1],[column2],…. FROM [TABLE] ORDER BY [sort]"htmlcode">



SELECT [column1],[column2],…. FROM [TABLE] WHERE [columnX] = [value] ORDER BY [sort]"htmlcode">



SELECT [column1],[column2],…. FROM [TABLE] WHERE [columnX] IN ([value1],[value2],…) ORDER BY[sort]"htmlcode">



SELECT * FROM [table] WHERE uid=1 ORDER x,y LIMIT 0,10"htmlcode">



SELECT * FROM t1 ORDER BY key1, key2"htmlcode">



SELECT * FROM t1 WHERE key2=constant ORDER BY key_part2"htmlcode">



SELECT * FROM t1 ORDER BY key_part1 DESC, key_part2 ASC"htmlcode">



SELECT * FROM t1 WHERE key2=constant ORDER BY key1"htmlcode">



SELECT * FROM t1 ORDER BY YEAR(logindate) LIMIT 0,10;








当MySQL不能使用索引进行排序时，就会利用自己的排序算法(快速排序算法)在内存(sort buffer)中对数据进行排序，如果内存装载不下，它会将磁盘上的数据进行分块，再对各个数据块进行排序，然后将各个块合并成有序的结果集（实际上就是外排序）。对于filesort，MySQL有两种排序算法。

1.两遍扫描算法(Two passes)

实现方式是先将须要排序的字段和可以直接定位到相关行数据的指针信息取出，然后在设定的内存（通过参数sort_buffer_size设定）中进行排序，完成排序之后再次通过行指针信息取出所需的Columns。

注：该算法是4.1之前采用的算法，它需要两次访问数据，尤其是第二次读取操作会导致大量的随机I/O操作。另一方面，内存开销较小。

2. 一次扫描算法(single pass)

该算法一次性将所需的Columns全部取出，在内存中排序后直接将结果输出。

注：从 MySQL 4.1 版本开始使用该算法。它减少了I/O的次数，效率较高，但是内存开销也较大。如果我们将并不需要的Columns也取出来，就会极大地浪费排序过程所需要的内存。在 MySQL 4.1 之后的版本中，可以通过设置 max_length_for_sort_data 参数来控制 MySQL 选择第一种排序算法还是第二种。当取出的所有大字段总大小大于 max_length_for_sort_data 的设置时，MySQL 就会选择使用第一种排序算法，反之，则会选择第二种。为了尽可能地提高排序性能，我们自然更希望使用第二种排序算法，所以在 Query 中仅仅取出需要的 Columns 是非常有必要的。


当对连接操作进行排序时，如果ORDER BY仅仅引用第一个表的列，MySQL对该表进行filesort操作，然后进行连接处理，此时，EXPLAIN输出“Using filesort”；否则，MySQL必须将查询的结果集生成一个临时表，在连接完成之后进行filesort操作，此时，EXPLAIN输出“Using temporary;Using filesort”。


                                

                                

                                     

                                
DDR爱好者之家 Design By 杰米

                            

                        

                        

                            

                                广告合作：本站广告合作请联系QQ：858582 申请时备注：广告合作（否则不回）

                                免责声明：本站资源来自互联网收集,仅供用于学习和交流,请遵循相关法律法规,本站一切资源不代表本站立场,如有侵权、后门、不妥请联系本站删除！
                            

                        

                        

                            
                            
                        

                        
                        
DDR爱好者之家 Design By 杰米

                        
                        
                    

                    

                        

                            

                                

                                    

                                        

                                    

                                    

                                        

                                            
                                        

                                    

                                

                                

                                    
ddrfans.com
                                            
                                                DDR爱好者之家 
                                    

                                    

                                        

                                            
                                        

                                        

                                            
                                        

                                        

                                            
                                        

                                        

                                            
                                        

                                    

                                    

                                    

                                        

                                            8,675无损音乐
                                        

                                        

                                            1,324高清电影
                                        

                                        

                                            213破解软件
                                        

                                        

                                            70,141收录资讯
                                        

                                    

                                

                            

                            
                            
                        

                    

                

            

            
稳了！魔兽国服回归的3条重磅消息！官宣时间再确认！
昨天有一位朋友在大神群里分享，自己亚服账号被封号之后居然弹出了国服的封号信息对话框。
这里面让他访问的是一个国服的战网网址，com.cn和后面的zh都非常明白地表明这就是国服战网。
而他在复制这个网址并且进行登录之后，确实是网易的网址，也就是我们熟悉的停服之后国服发布的暴雪游戏产品运营到期开放退款的说明。这是一件比较奇怪的事情，因为以前都没有出现这样的情况，现在突然提示跳转到国服战网的网址，是不是说明了简体中文客户端已经开始进行更新了呢？

            

                

                    

                        

                            
更新日志

                        

                        

                            
                        

                    

                

            

            

                

                    友情链接 

                

                    

                        杰晶网络
                        DDR爱好者之家
                        南强小屋
                        黑松山资源网
                        白云城资源网
                    

                

            

            
DDR爱好者之家 Design By 杰米