Mysql分区对大表查询效率的影响

本实验主要测试在没有任何索引的情况下分区对大表查询效率的影响

实验环境：

rhel 6.5

mysql5.5.58

表结构

 1 mysql> show create table users\G
 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]#

 

转载于:https://www.cnblogs.com/iaknehc/p/7900960.html