mysql分区与索引的区别（mysql四种分区及其性能比较）
mysql分区与索引的区别（mysql四种分区及其性能比较）注意： 主键必须要在分区字段中四、KEY 分区：类似于按HASH分区，区别在于KEY分区只支持计算一列或多列；三、HASH分区：基于用户定义的表达式的返回值来进行选择的分区，该表达式使用将要插入到表中的这些行的列值进行计算；（1） 由于哈希分区每次更新、插入、删除一行数据，这个表达式都需要去计算一次，那就意味着非常复杂的表达式可能引擎性能的问题。尤其是在执行批量插入语句的时候。（2） 最有效的哈希函数是只针对单个列进行计算，这个列的值最好随着列值进行增加，比如上述例子中使用的是int类型的列，这样哈希之后的数据分布的更加的均匀。因为这考虑了在分区范围内的修建，也就是说表达式值和他基于的列的值变化越接近，就更有效地使用该表达式进行哈希分区。
分区类型:一、range 分区：基于属于一个给定连续区间的列值，把多行分配给分区；
二、LIST 分区：类似于按RANGE分区，区别在于LIST分区是基于列值匹配一个离散值集合中的某个值来进行选择；
该分区最大的弊端就是分区中的集合必须进行定义 如果数据不在自定义的集合中插入会报错
List分区可以使用自身项目中的分类等不经常变化的数据
三、HASH分区：基于用户定义的表达式的返回值来进行选择的分区，该表达式使用将要插入到表中的这些行的列值进行计算；
（1） 由于哈希分区每次更新、插入、删除一行数据，这个表达式都需要去计算一次，那就意味着非常复杂的表达式可能引擎性能的问题。尤其是在执行批量插入语句的时候。
（2） 最有效的哈希函数是只针对单个列进行计算，这个列的值最好随着列值进行增加，比如上述例子中使用的是int类型的列，这样哈希之后的数据分布的更加的均匀。因为这考虑了在分区范围内的修建，也就是说表达式值和他基于的列的值变化越接近，就更有效地使用该表达式进行哈希分区。
四、KEY 分区：类似于按HASH分区，区别在于KEY分区只支持计算一列或多列；
- 哈希分区不同的是，创建key分区表的时候，可以不用指定分区键，默认会选择使用主键或者唯一键作为分区键。如果没有主键或者唯一键则需要指定分区键。Key分区说白了就是按照主键分区
注意： 主键必须要在分区字段中
Mysql5.5版本前的RANGE、List、Hash分区要求分区键必须是int类型，随后的版本支持非整数的Range和List分区，即RANGE columns和LIST columns。
性能分析比较list:分区CREATE TABLE `cn_area_list` (
`id` mediumint(7) unsigned NOT NULL
`level` tinyint(1) unsigned NOT NULL COMMENT '级别'
`parent_code` bigint(14) unsigned NOT NULL DEFAULT '0' COMMENT '父级行政代码'
`area_code` bigint(14) unsigned NOT NULL DEFAULT '0' COMMENT '行政代码'
`zip_code` mediumint(6) unsigned zerofill NOT NULL DEFAULT '000000' COMMENT '邮政编码'
`city_code` char(6) NOT NULL DEFAULT '' COMMENT '区号'
`name` varchar(50) NOT NULL DEFAULT '' COMMENT '名称'
`short_name` varchar(50) NOT NULL DEFAULT '' COMMENT '简称'
`merger_name` varchar(50) NOT NULL DEFAULT '' COMMENT '组合名'
`pinyin` varchar(30) NOT NULL DEFAULT '' COMMENT '拼音'
`lng` decimal(10 6) NOT NULL DEFAULT '0.000000' COMMENT '经度'
`lat` decimal(10 6) NOT NULL DEFAULT '0.000000' COMMENT '纬度'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='中国行政地区表'
PARTITION BY LIST(level)(
PARTITION p1 VALUES IN (0 2 4)
PARTITION p2 VALUES IN (1 3));结果集
758049条数据insert耗时21s
- cn_area全表查询
- select * from cn_area; -- 758049条数据耗时1.308s
- cn_area_list全表查询
- select * from cn_area_list; -- 758049条数据耗时1.425s
CREATE TABLE `cn_area_range` (
`id` mediumint(7) unsigned NOT NULL AUTO_INCREMENT
`level` tinyint(1) unsigned NOT NULL COMMENT '级别'
`parent_code` bigint(14) unsigned NOT NULL DEFAULT '0' COMMENT '父级行政代码'
`area_code` bigint(14) unsigned NOT NULL DEFAULT '0' COMMENT '行政代码'
`zip_code` mediumint(6) unsigned zerofill NOT NULL DEFAULT '000000' COMMENT '邮政编码'
`city_code` char(6) NOT NULL DEFAULT '' COMMENT '区号'
`name` varchar(50) NOT NULL DEFAULT '' COMMENT '名称'
`short_name` varchar(50) NOT NULL DEFAULT '' COMMENT '简称'
`merger_name` varchar(50) NOT NULL DEFAULT '' COMMENT '组合名'
`pinyin` varchar(30) NOT NULL DEFAULT '' COMMENT '拼音'
`lng` decimal(10 6) NOT NULL DEFAULT '0.000000' COMMENT '经度'
`lat` decimal(10 6) NOT NULL DEFAULT '0.000000' COMMENT '纬度'
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='中国行政地区表'
PARTITION BY RANGE (id) (
PARTITION p1 VALUES LESS THAN (200000)
PARTITION p2 VALUES LESS THAN (400000)
PARTITION p3 VALUES LESS THAN (600000)
PARTITION p4 VALUES LESS THAN maxvalue
);结果集
758049条数据insert耗时20s
- cn_area全表查询
- select * from cn_area; -- 758049条数据耗时1.186s
- cn_area_range全表查询
- select * from cn_area_range; -- 758049条数据耗时1.255s 连续多次查询比不分区更快些
CREATE TABLE `cn_area_hash` (
`id` mediumint(7) unsigned NOT NULL AUTO_INCREMENT
`level` tinyint(1) unsigned NOT NULL COMMENT '级别'
`parent_code` bigint(14) unsigned NOT NULL DEFAULT '0' COMMENT '父级行政代码'
`area_code` bigint(14) unsigned NOT NULL DEFAULT '0' COMMENT '行政代码'
`zip_code` mediumint(6) unsigned zerofill NOT NULL DEFAULT '000000' COMMENT '邮政编码'
`city_code` char(6) NOT NULL DEFAULT '' COMMENT '区号'
`name` varchar(50) NOT NULL DEFAULT '' COMMENT '名称'
`short_name` varchar(50) NOT NULL DEFAULT '' COMMENT '简称'
`merger_name` varchar(50) NOT NULL DEFAULT '' COMMENT '组合名'
`pinyin` varchar(30) NOT NULL DEFAULT '' COMMENT '拼音'
`lng` decimal(10 6) NOT NULL DEFAULT '0.000000' COMMENT '经度'
`lat` decimal(10 6) NOT NULL DEFAULT '0.000000' COMMENT '纬度'
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='中国行政地区表'
PARTITION BY HASH(id)
PARTITIONS 4;结果集
758049条数据insert耗时20s
- cn_area全表查询
- select * from cn_area; -- 758049条数据耗时1.357s
- cn_area_hash全表查询
- select * from cn_area_hash; -- 758049条数据耗时1.319s 连续多次查询比不分区更快些
CREATE TABLE `cn_area_key` (
`id` mediumint(7) unsigned NOT NULL AUTO_INCREMENT
`level` tinyint(1) unsigned NOT NULL COMMENT '级别'
`parent_code` bigint(14) unsigned NOT NULL DEFAULT '0' COMMENT '父级行政代码'
`area_code` bigint(14) unsigned NOT NULL DEFAULT '0' COMMENT '行政代码'
`zip_code` mediumint(6) unsigned zerofill NOT NULL DEFAULT '000000' COMMENT '邮政编码'
`city_code` char(6) NOT NULL DEFAULT '' COMMENT '区号'
`name` varchar(50) NOT NULL DEFAULT '' COMMENT '名称'
`short_name` varchar(50) NOT NULL DEFAULT '' COMMENT '简称'
`merger_name` varchar(50) NOT NULL DEFAULT '' COMMENT '组合名'
`pinyin` varchar(30) NOT NULL DEFAULT '' COMMENT '拼音'
`lng` decimal(10 6) NOT NULL DEFAULT '0.000000' COMMENT '经度'
`lat` decimal(10 6) NOT NULL DEFAULT '0.000000' COMMENT '纬度'
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='中国行政地区表'
partition by linear key(id)
partitions 4;结果集
758049条数据insert耗时20s
- cn_area全表查询
- select * from cn_area; -- 758049条数据耗时1.242s
- cn_area_hash全表查询
- select * from cn_area_key; -- 758049条数据耗时1.193s 连续多次查询比不分区更快些
range分区查询最快，其次hash，另外两种使用较少。
表查询SQL
select * from cn_area_list;
select * from cn_area;
EXPLAIN select * from cn_area_range PARTITION(p1);
EXPLAIN select * from cn_area_range where id =610000;
EXPLAIN select * from cn_area_list where id =610000;
EXPLAIN select * from cn_area_hash where id =610000;
EXPLAIN select * from cn_area_key where id =610000;
select * from cn_area_hash;
select * from cn_area_key;
