【mysql】表分区(partition)功能实验2

最新推荐文章于 2021-01-18 20:04:31 发布

lapiggy

最新推荐文章于 2021-01-18 20:04:31 发布

阅读量1.2k

点赞数

分类专栏：技术文章标签： mysql less null table 引擎磁盘

本文链接：https://blog.csdn.net/lapiggy/article/details/6448180

版权

本文通过实验对比分析了MySQL5.5的表分区功能，包括HASH分区和RANGE分区，展示了分区如何提高查询性能。实验表明，分区能显著提升查询效率，尤其是在范围查询和部分条件匹配时。同时，RANGE分区比HASH分区的性能更优。此外，还探讨了分区在数据容量扩展和已建索引字段的情况下的应用策略。

摘要由CSDN通过智能技术生成

MySQL版本：5.5.11

实验1

1、建表：

create table `t_part_test`(
`id` int NOT NULL default 0,
`detail` char(32) NOT NULL default '',
primary key (id)
)default charset=gbk
PARTITION BY HASH(id) PARTITIONS 10;

create table `t_nopart`(
`id` int NOT NULL default 0,
`detail` char(32) NOT NULL default '',
primary key (id)
)default charset=gbk;

CREATE TABLE `t_part_test_range` (
`id` int(11) NOT NULL DEFAULT '0',
`detail` char(32) NOT NULL DEFAULT '',
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=gbk
PARTITION BY RANGE (id)
(PARTITION p01 VALUES LESS THAN (1000000),
PARTITION p02 VALUES LESS THAN (2000000),
PARTITION p03 VALUES LESS THAN (3000000),
PARTITION p04 VALUES LESS THAN (4000000),
PARTITION p05 VALUES LESS THAN (5000000),
PARTITION p06 VALUES LESS THAN (6000000),
PARTITION p07 VALUES LESS THAN (7000000),
PARTITION p08 VALUES LESS THAN (8000000),
PARTITION p09 VALUES LESS THAN (9000000),
PARTITION p10 VALUES LESS THAN MAXVALUE);

注意：5.5默认存储引擎是InnoDB

2、向三张表中分别注入10000000条数据

mysql> select count(*) from t_nopart;
+----------+
| count(*) |
+----------+
| 10000000 |
+----------+
1 row in set (4.68 sec)

mysql> select count(*) from t_part_test;
+----------+
| count(*) |
+----------+
| 10000000 |
+----------+
1 row in set (3.89 sec)

mysql> select count(*) from t_part_test_range;
+----------+
| count(*) |
+----------+
| 10000000 |
+----------+
1 row in set (3.15 sec)

3、进行查询实验

mysql> select count(*) from t_nopart where id>=3000000 and id<=8099900;
+----------+
| count(*) |
+----------+
| 5099901 |
+----------+
1 row in set (3.33 sec)

mysql> select count(*) from t_part_test where id>=3