test1表是未分區
test2表是hash分區
以下是兩個表的表結構:
mysql> show create table test1;
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
| test1 | CREATE TABLE `test1` (
`id` int(11) NOT NULL,
`tid` int(11) DEFAULT NULL,
`name` varchar(10) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `tid` (`tid`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
1 row in set (0.00 sec)
mysql> show create table test2;
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
| test2 | CREATE TABLE `test2` (
) ENGINE=InnoDB DEFAULT CHARSET=latin1
/*!50100 PARTITION BY HASH (id)
PARTITIONS 10 */ |
1 row in set (0.01 sec)
在主鍵id列上用上hash分區。
如果是在主鍵上查詢,那麼分區要比不分區性能高,例如:
mysql> explain partitions select * from test1 where id in (86,777,432345);
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | Extra |
| 1 | SIMPLE | test1 | NULL | range | PRIMARY | PRIMARY | 4 | NULL | 3 | Using where |
1 row in set (0.06 sec)
mysql> explain partitions select * from test2 where id in (86,777,432345);
| 1 | SIMPLE | test2 | p5,p6,p7 | range | PRIMARY | PRIMARY | 4 | NULL | 3 | Using where |
隻在分區P5/6/7上就掃描到了記錄,減少了過多的磁盤IO。
但如果在索引tid上查詢,性能就很低了。
mysql> explain partitions select * from test1 where tid = 86419804;
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | Extra |
| 1 | SIMPLE | test1 | NULL | ref | tid | tid | 5 | const | 1 | Using where |
mysql> explain partitions select * from test2 where tid = 86419804;
+----+-------------+-------+-------------------------------+------+---------------+------+---------+-------+------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | Extra |
| 1 | SIMPLE | test2 | p0,p1,p2,p3,p4,p5,p6,p7,p8,p9 | ref | tid | tid | 5 | const | 10 | Using where |
要掃描10個分區,才能得到結果。
mysql> select sql_no_cache * from test1 where tid=86419804;
+----+----------+------+
| id | tid | name |
| 11 | 86419804 | abc2 |
1 row in set (0.40 sec)
mysql> select sql_no_cache * from test2 where tid=86419804;
1 row in set (1.98 sec)
分區後的查詢結果要比未分區的慢很多。
結論:分區規則僅限制在主鍵上使用,性能會有提高,在其他字段上使用,即使有索引,性能也會很差。
本文轉自 liang3391 51CTO部落格,原文連結:http://blog.51cto.com/liang3391/844523