<output id="r87xx"></output>
    1. 

      <mark id="r87xx"><thead id="r87xx"><input id="r87xx"></input></thead></mark>
      3. 

           

          
	
           
		
          
		   
		
           
		
		
	
          

          

          
  

           
 	
           
		
	
	
          
	
          
	    
		
           
			
          
				
				
          

              
           

                 
          

                    
           

                           當(dāng)前位置：首頁>軟件介紹>MySQL的分頁的優(yōu)化					 
					  
					  查詢：
					  
					  
          		
                    
             
                  
           
                  
          

                    
           

                       

                       

                       

                    
          

                    
            
                       
						
            
						
							
           
								 
								MySQL的分頁的優(yōu)化
								  
							
          
							
           
								 
									
                  關(guān)于分頁的優(yōu)化。 
                  我們知道，在MySQL中分頁很簡單，直接LIMIT page_no,page_total 就可以了。 
                  可是當(dāng)記錄數(shù)慢慢增大時(shí)，她就不那么好使了。 
                  這里我們創(chuàng)建摘要表來記錄頁碼和原表之間的關(guān)聯(lián)。 
                  下面為測試數(shù)據(jù)。 
                  原表: 
                  CREATE TABLE `t_group` ( 
                  `id` int(11) NOT NULL auto_increment, 
                  `money` decimal(10,2) NOT NULL, 
                  `user_name` varchar(20) NOT NULL, 
                  `create_time` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, 
                  PRIMARY KEY (`id`), 
                  KEY `idx_combination1` (`user_name`,`money`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8; 
                  原表總記錄數(shù): 
                  mysql> select count(*) from t_group; 
                  +----------+ 
                  | count(*) | 
                  +----------+ 
                  | 10485760 | 
                  +----------+ 
                  1 row in set (0.00 sec) 
                  分頁表: 
                  CREATE TABLE `t_group_ids` ( 
                  `id` int(11) NOT NULL, 
                  `group_id` int(11) NOT NULL, 
                  PRIMARY KEY (`id`,`group_id`), 
                  KEY `idx_id` (`id`), 
                  KEY `idx_group_id` (`group_id`) 
                  ) ENGINE=InnoDB DEFAULT CHARSET=utf8;  
                  插入分頁表數(shù)據(jù)。當(dāng)然這里如果你的表主鍵不是ID,那你得自己想辦法搞這個分頁表的數(shù)據(jù)了。這個好實(shí)現(xiàn)，就不說了。 mysql> insert into t_group_ids select ceil(id/20),id from t_group; 
                  Query OK, 10485760 rows affected (2 min 56.19 sec) Records: 10485760 Duplicates: 0 Warnings: 0 
                  現(xiàn)在來看看對比數(shù)據(jù)。 
                  用普通LIMIT來實(shí)現(xiàn)分頁。 
                  mysql> select * from t_group where 1 limit 20; 
                  +----+--------+-----------+---------------------+
                   | id | money | user_name | create_time | 
                  +----+--------+-----------+---------------------+ 
                  | 1 | 50.23 | david | 2008-10-23 12:55:49 | | 2 | 55.23 | livia | 2008-10-23 10:02:09 | | 3 | 100.83 | leo | 2008-10-23 10:02:22 | | 4 | 99.99 | lucy | 2008-10-23 10:02:39 | | 5 | 299.99 | simon | 2008-10-23 10:02:52 | | 6 | 599.99 | sony | 2008-10-23 10:03:03 | | 7 | 599.99 | rick | 2008-10-23 10:03:12 | | 8 | 9.99 | anne | 2008-10-23 10:03:47 | | 9 | 9.99 | sarah | 2008-10-23 10:04:31 | | 10 | 900.99 | john | 2008-10-23 10:04:50 | | 11 | 0.23 | david | 2008-10-23 10:05:31 | | 12 | 5.23 | livia | 2008-10-23 10:05:31 | | 13 | 50.83 | leo | 2008-10-23 10:05:31 | | 14 | 49.99 | lucy | 2008-10-23 10:05:31 | | 15 | 249.99 | simon | 2008-10-23 10:05:31 | | 16 | 549.99 | sony | 2008-10-23 10:05:31 | | 17 | 549.99 | rick | 2008-10-23 10:05:31 | | 18 | -40.01 | anne | 2008-10-23 10:05:31 | | 19 | -40.01 | sarah | 2008-10-23 10:05:31 | | 20 | 850.99 | john | 2008-10-23 10:05:31 | 
                  +----+--------+-----------+---------------------+
                   20 rows in set (0.01 sec) 
                  用分頁表來實(shí)現(xiàn)分頁。 
                  mysql> select a.* from t_group as a inner join t_group_ids as b where a.id = b.g 
                  roup_id and b.id = 1; 
                  +----+--------+-----------+---------------------+
                   | id | money | user_name | create_time | 
                  +----+--------+-----------+---------------------+ 
                  | 1 | 50.23 | david | 2008-10-23 12:55:49 | | 2 | 55.23 | livia | 2008-10-23 10:02:09 | | 3 | 100.83 | leo | 2008-10-23 10:02:22 | | 4 | 99.99 | lucy | 2008-10-23 10:02:39 | | 5 | 299.99 | simon | 2008-10-23 10:02:52 | | 6 | 599.99 | sony | 2008-10-23 10:03:03 | | 7 | 599.99 | rick | 2008-10-23 10:03:12 | | 8 | 9.99 | anne | 2008-10-23 10:03:47 | | 9 | 9.99 | sarah | 2008-10-23 10:04:31 | | 10 | 900.99 | john | 2008-10-23 10:04:50 | | 11 | 0.23 | david | 2008-10-23 10:05:31 | | 12 | 5.23 | livia | 2008-10-23 10:05:31 | | 13 | 50.83 | leo | 2008-10-23 10:05:31 | | 14 | 49.99 | lucy | 2008-10-23 10:05:31 | | 15 | 249.99 | simon | 2008-10-23 10:05:31 | | 16 | 549.99 | sony | 2008-10-23 10:05:31 | | 17 | 549.99 | rick | 2008-10-23 10:05:31 | 
                  | 18 | -40.01 | anne | 2008-10-23 10:05:31 | | 19 | -40.01 | sarah | 2008-10-23 10:05:31 | | 20 | 850.99 | john | 2008-10-23 10:05:31 | 
                  +----+--------+-----------+---------------------+ 
                  20 rows in set (0.00 sec) 
                  取第50W頁的數(shù)據(jù)。 
                  原來表: 
                  mysql> select * from t_group where 1 limit 9999980,20; 
                  +----------+---------+-----------+---------------------+
                   | id | money | user_name | create_time | 
                  +----------+---------+-----------+---------------------+
                   | 9999981 | 810.13 | david | 2008-10-23 10:09:24 | | 9999982 | 815.13 | livia | 2008-10-23 10:09:24 | | 9999983 | 860.73 | leo | 2008-10-23 10:09:24 | | 9999984 | 859.89 | lucy | 2008-10-23 10:09:24 | | 9999985 | 1059.89 | simon | 2008-10-23 10:09:24 | | 9999986 | 1359.89 | sony | 2008-10-23 10:09:24 | | 9999987 | 1359.89 | rick | 2008-10-23 10:09:24 | | 9999988 | 769.89 | anne | 2008-10-23 10:09:24 | | 9999989 | 769.89 | sarah | 2008-10-23 10:09:24 | | 9999990 | 1660.89 | john | 2008-10-23 10:09:24 | | 9999991 | 760.13 | david | 2008-10-23 10:09:24 | | 9999992 | 765.13 | livia | 2008-10-23 10:09:24 | | 9999993 | 810.73 | leo | 2008-10-23 10:09:24 | | 9999994 | 809.89 | lucy | 2008-10-23 10:09:24 | | 9999995 | 1009.89 | simon | 2008-10-23 10:09:24 | | 9999996 | 1309.89 | sony | 2008-10-23 10:09:24 | | 9999997 | 1309.89 | rick | 2008-10-23 10:09:24 | | 9999998 | 719.89 | anne | 2008-10-23 10:09:24 | | 9999999 | 719.89 | sarah | 2008-10-23 10:09:24 | | 10000000 | 1610.89 | john | 2008-10-23 10:09:24 |
                   +----------+---------+-----------+---------------------+
                   20 rows in set (4.21 sec) 
                  分頁表: 
                  mysql> select a.* from t_group as a inner join t_group_ids as b where a.id = b.g 
                  roup_id and b.id = 500000; 
                  +----------+---------+-----------+---------------------+ 
                  | id | money | user_name | create_time | 
                  +----------+---------+-----------+---------------------+
                   | 9999981 | 810.13 | david | 2008-10-23 10:09:24 | | 9999982 | 815.13 | livia | 2008-10-23 10:09:24 | | 9999983 | 860.73 | leo | 2008-10-23 10:09:24 | | 9999984 | 859.89 | lucy | 2008-10-23 10:09:24 | 
                  | 9999985 | 1059.89 | simon | 2008-10-23 10:09:24 | | 9999986 | 1359.89 | sony | 2008-10-23 10:09:24 | | 9999987 | 1359.89 | rick | 2008-10-23 10:09:24 | | 9999988 | 769.89 | anne | 2008-10-23 10:09:24 | | 9999989 | 769.89 | sarah | 2008-10-23 10:09:24 | | 9999990 | 1660.89 | john | 2008-10-23 10:09:24 | | 9999991 | 760.13 | david | 2008-10-23 10:09:24 | | 9999992 | 765.13 | livia | 2008-10-23 10:09:24 | | 9999993 | 810.73 | leo | 2008-10-23 10:09:24 | | 9999994 | 809.89 | lucy | 2008-10-23 10:09:24 | | 9999995 | 1009.89 | simon | 2008-10-23 10:09:24 | | 9999996 | 1309.89 | sony | 2008-10-23 10:09:24 | | 9999997 | 1309.89 | rick | 2008-10-23 10:09:24 | | 9999998 | 719.89 | anne | 2008-10-23 10:09:24 | | 9999999 | 719.89 | sarah | 2008-10-23 10:09:24 | | 10000000 | 1610.89 | john | 2008-10-23 10:09:24 | 
                  +----------+---------+-----------+---------------------+ 
                  20 rows in set (0.03 sec) 
                  我們來取最后一頁的數(shù)據(jù)。 
                  原表: 
                  mysql> select * from t_group where 1 limit 10485740,20; 
                  +----------+---------+-----------+---------------------+
                   | id | money | user_name | create_time |
                   +----------+---------+-----------+---------------------+
                   | 10485741 | 1935.42 | david | 2008-10-23 10:09:24 | | 10485742 | 1955.42 | livia | 2008-10-23 10:09:24 | | 10485743 | 2137.82 | leo | 2008-10-23 10:09:24 | | 10485744 | 2134.46 | lucy | 2008-10-23 10:09:24 | | 10485745 | 2934.46 | simon | 2008-10-23 10:09:24 | | 10485746 | 4134.46 | sony | 2008-10-23 10:09:24 | | 10485747 | 4134.46 | rick | 2008-10-23 10:09:24 | | 10485748 | 1774.46 | anne | 2008-10-23 10:09:24 | | 10485749 | 1774.46 | sarah | 2008-10-23 10:09:24 | | 10485750 | 5338.46 | john | 2008-10-23 10:09:24 | | 10485751 | 1735.42 | david | 2008-10-23 10:09:24 | | 10485752 | 1755.42 | livia | 2008-10-23 10:09:24 | | 10485753 | 1937.82 | leo | 2008-10-23 10:09:24 | | 10485754 | 1934.46 | lucy | 2008-10-23 10:09:24 | | 10485755 | 2734.46 | simon | 2008-10-23 10:09:24 | | 10485756 | 3934.46 | sony | 2008-10-23 10:09:24 | | 10485757 | 3934.46 | rick | 2008-10-23 10:09:24 | | 10485758 | 1574.46 | anne | 2008-10-23 10:09:24 | | 10485759 | 1574.46 | sarah | 2008-10-23 10:09:24 | | 10485760 | 5138.46 | john | 2008-10-23 10:09:24 |
                  ----+---------+-----------+---------------------+ 
                  20 rows in set (4.88 sec) 
                  分頁表: 
                  mysql> select a.* from t_group as a inner join t_group_ids as b where a.id = b.g 
                  roup_id and b.id = 524288; 
                  +----------+---------+-----------+---------------------+
                   | id | money | user_name | create_time | 
                  +----------+---------+-----------+---------------------+
           | 10485741 | 1935.42 | david | 2008-10-23 10:09:24 | | 10485742 | 1955.42 | livia | 2008-10-23 10:09:24 | | 10485743 | 2137.82 | leo | 2008-10-23 10:09:24 | | 10485744 | 2134.46 | lucy | 2008-10-23 10:09:24 | | 10485745 | 2934.46 | simon | 2008-10-23 10:09:24 | | 10485746 | 4134.46 | sony | 2008-10-23 10:09:24 | | 10485747 | 4134.46 | rick | 2008-10-23 10:09:24 | | 10485748 | 1774.46 | anne | 2008-10-23 10:09:24 | | 10485749 | 1774.46 | sarah | 2008-10-23 10:09:24 | | 10485750 | 5338.46 | john | 2008-10-23 10:09:24 | | 10485751 | 1735.42 | david | 2008-10-23 10:09:24 | | 10485752 | 1755.42 | livia | 2008-10-23 10:09:24 | | 10485753 | 1937.82 | leo | 2008-10-23 10:09:24 | | 10485754 | 1934.46 | lucy | 2008-10-23 10:09:24 | | 10485755 | 2734.46 | simon | 2008-10-23 10:09:24 | | 10485756 | 3934.46 | sony | 2008-10-23 10:09:24 | | 10485757 | 3934.46 | rick | 2008-10-23 10:09:24 | | 10485758 | 1574.46 | anne | 2008-10-23 10:09:24 | | 10485759 | 1574.46 | sarah | 2008-10-23 10:09:24 | | 10485760 | 5138.46 | john | 2008-10-23 10:09:24 | 
                  +----------+---------+-----------+---------------------+ 
                  20 rows in set (0.01 sec) 
                  
                                              
          
                                    
          
                                    
          MySQL分區(qū)表測試MySQL數(shù)據(jù)庫遷移常用方法
          MySQL集群方案MySQL配置優(yōu)化
          MySQL的計(jì)劃任務(wù)MySQL優(yōu)化策略
          Infobright基于MySQL的數(shù)據(jù)倉庫MySQL常用命令
          MySQL數(shù)據(jù)庫同步MySQL常用語句
          常用基本MySQL語句MySQL創(chuàng)建用戶
          MySQL學(xué)習(xí)分享MySQL常用命令用法
          MySQL的部分用法MySQL讀寫分離Amoeba的實(shí)現(xiàn) 
                                              
          
                                    信息發(fā)布：廣州名易軟件有限公司 http://m.jetlc.com
								          		  
							
          
							
           
								 
									
                                    
								  
							
          
						
           
					           		
					
          
				
          
		          		
	
            
    
          
 
    
          
    	
          
        
            
		
				
          
				
           
		
          

           
 
  


 
 



          
  
          
    
          
    
          
      
          
      
            
        
          • 勁爆價(jià):
            
不限功能
            
不限用戶
            
1998元/年
            •  


          • 微信客服
            • 

          •  

          

          
    
          
  
          

          

 


            <output id="r87xx"></output>
          1. 

            <mark id="r87xx"><thead id="r87xx"><input id="r87xx"></input></thead></mark>
            3. 

                
中国免费av
|
亚洲三级网站在线观看
|
天天操人人操
|
亚洲综合国产
|
肏逼福利影城
|
能免费看的黄色电影
|
香蕉伊人在线
|
亚洲视频一区二区
|
草欧美骚逼
|
日韩拍拍拍
|