MySQL 5.5 分区性能测试之索引使用情况

转 http://blog.csdn.net/m582445672/article/details/7800694 1.创建一个测试表 [sql] view plaincopyprint? CREATE TABLE test( id VARCHAR (20) NOT NULL , name VARCHAR (20) NOT NULL , submit_timeDATETIME NOT NULL , index time_index(submit_time),

转 http://blog.csdn.net/m582445672/article/details/7800694

1.创建一个测试表

[sql] view plaincopyprint?

1. CREATE TABLE test (   
2.   id VARCHAR(20) NOT NULL,  
3.   name VARCHAR(20) NOT NULL,  
4.   submit_time DATETIME NOT NULL,  
5.   index time_index (submit_time),  
6.   index id_index (id)  
7. )ENGINE=MyISAM  
8. PARTITION BY RANGE COLUMNS(submit_time)  
9. (  
10. PARTITION p1 VALUES LESS THAN ('2010-02-01'),  
11. PARTITION p2 VALUES LESS THAN ('2010-03-01'),  
12. PARTITION p3 VALUES LESS THAN ('2010-04-01'),  
13. PARTITION p4 VALUES LESS THAN ('2010-05-01'),  
14. PARTITION p5 VALUES LESS THAN ('2010-06-01'),  
15. PARTITION p6 VALUES LESS THAN ('2010-07-01'),  
16. PARTITION p7 VALUES LESS THAN ('2010-08-01'),  
17. PARTITION p8 VALUES LESS THAN ('2010-09-01'),  
18. PARTITION p9 VALUES LESS THAN ('2010-10-01'),  
19. PARTITION p10 VALUES LESS THAN ('2010-11-01'),  
20. PARTITION p11 VALUES LESS THAN ('2010-12-01')    
21. );  

CREATE TABLE test ( 
  id VARCHAR(20) NOT NULL,
  name VARCHAR(20) NOT NULL,
  submit_time DATETIME NOT NULL,
  index time_index (submit_time),
  index id_index (id)
)ENGINE=MyISAM
PARTITION BY RANGE COLUMNS(submit_time)
(
PARTITION p1 VALUES LESS THAN ('2010-02-01'),
PARTITION p2 VALUES LESS THAN ('2010-03-01'),
PARTITION p3 VALUES LESS THAN ('2010-04-01'),
PARTITION p4 VALUES LESS THAN ('2010-05-01'),
PARTITION p5 VALUES LESS THAN ('2010-06-01'),
PARTITION p6 VALUES LESS THAN ('2010-07-01'),
PARTITION p7 VALUES LESS THAN ('2010-08-01'),
PARTITION p8 VALUES LESS THAN ('2010-09-01'),
PARTITION p9 VALUES LESS THAN ('2010-10-01'),
PARTITION p10 VALUES LESS THAN ('2010-11-01'),
PARTITION p11 VALUES LESS THAN ('2010-12-01')  
);

2.写一个存储过程,插入数据

[sql] view plaincopyprint?

1. delimiter //    
2. CREATE PROCEDURE mark_test()  
3. begin   
4.     declare v int default 0;   
5.     while v 
6.     do   
7.        insert into test values (v,'testing partitions',adddate('2010-01-01', INTERVAL v hour));  
8.        set v = v + 1;  
9.     end while;  
10. end //  
11. delimiter ;  

delimiter //  
CREATE PROCEDURE mark_test()
begin 
    declare v int default 0; 
    while v 
<p><br>
3.实验开始<span><br>
</span></p>



<p><img  src="/inc/test.jsp?url=http%3A%2F%2Fmy.csdn.net%2Fuploads%2F201207%2F30%2F1343613328_7293.jpg&refer=http%3A%2F%2Fblog.csdn.net%2Fhuanglei0809%2Farticle%2Fdetails%2F12178931" alt="MySQL 5.5 分区性能测试之索引使用情况" ></p>
<p><span> 上面可以看到,这个是查某一个分区里面的某一些内容,所以完全可以用到index.效果很好..</span></p>
<p> </p>
<p><img  src="/inc/test.jsp?url=http%3A%2F%2Fmy.csdn.net%2Fuploads%2F201207%2F30%2F1343652145_1375.jpg&refer=http%3A%2F%2Fblog.csdn.net%2Fhuanglei0809%2Farticle%2Fdetails%2F12178931" alt="MySQL 5.5 分区性能测试之索引使用情况" ></p>
<p><span> 上面可以看到,跨分区查询,效果也非常不错.</span></p>
<p> </p>
<p><span><img  src="/inc/test.jsp?url=http%3A%2F%2Fmy.csdn.net%2Fuploads%2F201207%2F30%2F1343652248_3042.jpg&refer=http%3A%2F%2Fblog.csdn.net%2Fhuanglei0809%2Farticle%2Fdetails%2F12178931" alt="MySQL 5.5 分区性能测试之索引使用情况" ></span></p>
<p><span> 上面可以到看,跨分区查询是,如果某个分区没有用到索引(p4就是全表扫描),整个也没有用到index.但好的是,只扫描需要的分区</span></p>
<p> </p>
<p> </p>
<p><span><img  src="/inc/test.jsp?url=http%3A%2F%2Fmy.csdn.net%2Fuploads%2F201207%2F30%2F1343652825_9317.jpg&refer=http%3A%2F%2Fblog.csdn.net%2Fhuanglei0809%2Farticle%2Fdetails%2F12178931" alt="MySQL 5.5 分区性能测试之索引使用情况" ></span></p>
<p>
</p><p><span> <span>上面可以看到,如果你不用分区的字段查询,是很杯具的,因为MySQL不知道你分区的index是分别存放到哪个分区上,所以要全index扫描, </span></span></p>
<p> </p>
<p><span>3.顺便看看表结构</span></p>

<p><img  src="/inc/test.jsp?url=http%3A%2F%2Fmy.csdn.net%2Fuploads%2F201207%2F30%2F1343653279_7150.jpg&refer=http%3A%2F%2Fblog.csdn.net%2Fhuanglei0809%2Farticle%2Fdetails%2F12178931" alt="MySQL 5.5 分区性能测试之索引使用情况" ></p>
<p>a. 图中test3 是innodb的存储引擎,  </p>
<p>    test3.frm是表结构.</p>
<p>    test3.par是分区表的信息. </p>
<p>    数据和索引都是存放在表空间里面在<br>
</p>
<p>b.图中test是myisam的存储引擎,  </p>
<p>   test.frm是表结构,</p>
<p>   test.par是分区表的信息. </p>
<p>   test#P#p10.MYD是数据文件之一,</p>
<p>   test#P#p10.MYI是索引文件之一 </p>
<p> </p>
<p> </p>
<p><br>
</p>
<p> </p>