本实验主要测试在没有任何索引的情况下分区对大表查询效率的影响
实验环境：
rhel 6.5
mysql5.5.58
表结构
1 mysql> show create table usersG 2 *************************** 1. row *************************** 3 Table: users 4 Create Table: CREATE TABLE `users` ( 5 `id` int(10) unsigned NOT NULL, 6 `name` varchar(32) NOT NULL DEFAULT '', 7 `age` int(10) unsigned DEFAULT NULL, 8 `email` varchar(32) NOT NULL DEFAULT '', 9 `account` varchar(64) NOT NULL DEFAULT '', 10 `address` varchar(64) NOT NULL DEFAULT '' 11 ) ENGINE=MyISAM DEFAULT CHARSET=utf8 12 1 row in set (0.00 sec)
表中的数据有10000000记录
1 mysql> select count(*) from users; 2 +----------+ 3 | count(*) | 4 +----------+ 5 | 10000000 | 6 +----------+ 7 1 row in set (0.09 sec)
首先随便选取一条记录查询，看看mysql服务器需要的时间
1 mysql> select * from users where id=2478945; 2 +---------+------------------+------+--------------------------+------------------+---------------------------------------------------+ 3 | id | name | age | email | account | address | 4 +---------+------------------+------+--------------------------+------------------+---------------------------------------------------+ 5 | 2478945 | Nina Oberbrunner | 8 | beatty.martina@gmail.com | 6579978004257241 | 99802 Dietrich Mountain 6 New Pinkie, WA 66024-5962 | 7 +---------+------------------+------+--------------------------+------------------+---------------------------------------------------+ 8 1 row in set (18.25 sec)
很显然该查询进行了全表扫描
1 mysql> explain select * from users where id=2478945 G 2 *************************** 1. row *************************** 3 id: 1 4 select_type: SIMPLE 5 table: users 6 type: ALL 7 possible_keys: NULL 8 key: NULL 9 key_len: NULL 10 ref: NULL 11 rows: 10000000 12 Extra: Using where 13 1 row in set (0.04 sec)
现在对大表进行分区
1 mysql> alter table users partition by hash(id) partitions 5; 2 Query OK, 10000000 rows affected (37.24 sec) 3 Records: 10000000 Duplicates: 0 Warnings: 0
测试一下在没有建任何索引的情况下，对大表进行分区是否会影响查询的速度
1 mysql> select * from users where id=2478945; 2 +---------+------------------+------+--------------------------+------------------+---------------------------------------------------+ 3 | id | name | age | email | account | address | 4 +---------+------------------+------+--------------------------+------------------+---------------------------------------------------+ 5 | 2478945 | Nina Oberbrunner | 8 | beatty.martina@gmail.com | 6579978004257241 | 99802 Dietrich Mountain 6 New Pinkie, WA 66024-5962 | 7 +---------+------------------+------+--------------------------+------------------+---------------------------------------------------+ 8 1 row in set (2.36 sec)
可以看到查询同一条记录从开始的18.25s变为2.36秒，说明分区对大表查询有一定的优化
查询过程和上面一样，进行全表扫描，但是分区后数据文件变小，扫描的行数变为2000000，提高了速度
1 mysql> explain select * from users where id=2478945 G 2 *************************** 1. row *************************** 3 id: 1 4 select_type: SIMPLE 5 table: users 6 type: ALL 7 possible_keys: NULL 8 key: NULL 9 key_len: NULL 10 ref: NULL 11 rows: 2000000 12 Extra: Using where 13 1 row in set (0.06 sec)
分区后，每个分区数据文件是200多兆,没分区时一个文件1G多
1 [root@localhost test]# pwd 2 /usr/local/mysql/data/test 3 [root@localhost test]# ll -h 4 total 1.1G 5 -rw-r--r-- 1 mysql mysql 65 9月 13 23:20 db.opt 6 -rw-rw---- 1 mysql mysql 8.6K 11月 26 22:39 users.frm 7 -rw-rw---- 1 mysql mysql 40 11月 26 22:39 users.par 8 -rw-rw---- 1 mysql mysql 221M 11月 26 22:40 users#P#p0.MYD 9 -rw-rw---- 1 mysql mysql 1.0K 11月 26 22:40 users#P#p0.MYI 10 -rw-rw---- 1 mysql mysql 221M 11月 26 22:40 users#P#p1.MYD 11 -rw-rw---- 1 mysql mysql 1.0K 11月 26 22:40 users#P#p1.MYI 12 -rw-rw---- 1 mysql mysql 221M 11月 26 22:40 users#P#p2.MYD 13 -rw-rw---- 1 mysql mysql 1.0K 11月 26 22:40 users#P#p2.MYI 14 -rw-rw---- 1 mysql mysql 221M 11月 26 22:40 users#P#p3.MYD 15 -rw-rw---- 1 mysql mysql 1.0K 11月 26 22:40 users#P#p3.MYI 16 -rw-rw---- 1 mysql mysql 221M 11月 26 22:40 users#P#p4.MYD 17 -rw-rw---- 1 mysql mysql 1.0K 11月 26 22:40 users#P#p4.MYI 18 [root@localhost test]#