MySQL分区建索引以及分区介绍总结

2024-04-02 19:55

短信预约 -IT技能 免费直播动态提醒

MySQL 分区建索引介绍

mysql分区后每个分区成了独立的文件，虽然从逻辑上还是一张表其实已经分成了多张独立的表，从“information_schema.INNODB_SYS_TABLES”系统表可以看到每个分区都存在独立的TABLE_ID,由于Innodb数据和索引都是保存在".ibd"文件当中（从INNODB_SYS_INDEXES系统表中也可以得到每个索引都是对应各自的分区(primary key和unique也不例外）），所以分区表的索引也是随着各个分区单独存储。

在INNODB_SYS_INDEXES系统表中type代表索引的类型;0:一般的索引,1:(GEN_CLUST_INDEX)不存在主键索引的表,会自动生成一个6个字节的标示值，2:unique索引,3:primary索引;所以当我们在分区表中创建索引时其实也是在每个分区中创建索引，每个分区维护各自的索引（其实也就是local index）；对于一般的索引(非主键或者唯一)没什么问题由于索引树中只保留了索引key和主键key(如果存在主键则是主键的key否则就是系统自动生成的6个的key)不受分区的影响；但是如果表中存在主键就不一样了，虽然在每个分区文件中都存在主键索引但是主键索引需要保证全局的唯一性就是所有分区中的主键的值都必须唯一（唯一键也是一样的道理），所以在创建分区时如果表中存在主键或者唯一键那么分区列必须包含主键或者唯一键的部分或者全部列（全部列还好理解，部分列也可以个人猜测是为了各个分区和主键建立关系），由于需要保证全局性又要保证插入数据更新数据到具体的分区所以就需要将分区和主键建立关系,由于通过一般的索引进行查找其它非索引字段需要通过主键如果主键不能保证全局唯一性的话那么就需要去每个分区查找了，这样性能可想而知。

To enforce the uniqueness we only allow mapping of each unique/primary key value to one partition.If we removed this limitation it would mean that for every insert/update we need to check in every partition to verify that it is unique. Also PK-only lookups would need to look into every partition.

索引方式：

性能依次降低

1.主键分区

主键分区即字段是主键同时也是分区字段，性能最好

2. 部分主键+分区索引

使用组合主键里面的部分字段作为分区字段，同时将分区字段建索引

3.分区索引

没有主键，只有分区字段且分区字段建索引

4.分区+分区字段没有索引

只建了分区，但是分区字段没有建索引

MySQL 分区介绍介绍

分区是指根据一定的规则将一个大表分解成多个更小的部分，这里的规则一般就是利用分区规则将表进行水平切分；逻辑上没有发生变化但实际上表已经被拆分成了多个物理对象，每个分成被划分成了一个独立的对象。相对于没有分区的当个表而言分区的表有很多的优势包括：并发统计查询、快速归档删除分区数据、分散存储、查询性能更佳。

mysql5.7以后查询语句支持指定分区例如：“ SELECT * FROM t PARTITION (p0,p1) WHERE c < 5 ”指定分区同样适用DELETE, INSERT, REPLACE, UPDATE, and LOAD DATA, LOAD XML.

数据库版本：mysql5.7.12

是否支持分区

SHOW PLUGINS ;

查询partition的的状态是active就代表支持分区，如果是源码安装的话在编译的过程中要添加“-DWITH_PARTITION_STORAGE_ENGINE=1 \”。

注意： MERGE, CSV, or FEDERATED存储引擎不支持分区，同一个表所有的分区必须使用相同的存储引擎，不能分区1使用MYISAM分区2又使用INNODB；不同的分区表可以是不同的存储引擎。

分区介绍

目前mysql可用的分区类型主要有以下几种：

RANGE分区：基于一个给定的连续区间范围，RANGE主要是基于整数的分区，对于非整形的字段需要利用表达式将其转换成整形。

LIST分区：是基于列出的枚举值列表进行分区。

COLUMNS分区：可以无需通过表达式进行转换直接对非整形字段进行分区，同时COLUMNS分区还支持多个字段组合分区，只有RANGELIST存在COLUMNS分区，COLUMNS是RANGE和LIST分区的升级。

HASH分区：基于给定的分区个数，将数据分配到不同的分区，HASH分区只能针对整数进行HASH，对于非整形的字段只能通过表达式将其转换成整数。

KEY分区：支持除text和BLOB之外的所有数据类型的分区,key分区可以直接基于字段做分区无需转换成整数。

说明

1.注意分区名的大小写敏感问题，和关键字问题。

2.无论哪种分区类型，要么分区表中没有主键或唯一键，要么主键或唯一键包含在分区列里面，对于存在主键或者唯一键的表不能使用主键或者唯一键之外的字段作为分区字段。

3.5.7以前的版本显示分区的执行计划使用：explain PARTITIONS；5.7以后直接执行：explain

4.没有强制要求分区列非空，建议分区的列为NOT NULL的列；在RANGE 分区中如果往分区列中插入NULL值会被当作最小的值来处理，在LIST分区中NULL值必须在枚举列表中否则插入失败，在HASH/KEY分区中NULL值会被当作0来处理。

5.基于时间类型的字段的转换函数mysql提供了"YEAR(),MONTH(),DAY(),TO_DAYS(),TO_SECONDS(),WEEKDAY(),DAYOFYEAR()"

6.拆分合并分区后会导致修改的分区的统计信息失效,没有修改的分区的统计信息还在,不影响新插入的值加入到统计信息；这时需要对表执行Analyze操作.

7.针对非整形字段进行RANG\LIST分区建议使用COLUMNS分区。

删除增加分区

在每个分区内容介绍中详细介绍了每种分区的用法，但是都是介绍在创建表的时候创建分区和修改删除分区单个，也可以在一张已经存在的表中加入分区，可以一次性删除整个表的分区。

1.移除表的分区

ALTER TABLE tablename
REMOVE PARTITIONING ;

注意：使用remove移除分区是仅仅移除分区的定义，并不会删除数据和drop PARTITION不一样，后者会连同数据一起删除

2.对已经存在记录的表创建分区，以增加range分区为例，和创建表建分区的语法一样。

ALTER TABLE `tb_partition`.`tb_varchar` 
PARTITION BY RANGE(id) PARTITIONS 3( PARTITION part0 VALUES LESS THAN (5000),  PARTITION part1 VALUES LESS THAN (10000),  PARTITION part2 VALUES LESS THAN (MAXVALUE)) ;

注意：对已有的表创建分区之后，数据会按照分区的定义分布到各个分区文件当中

分区系列文章：

RANGE分区：https://www.jb51.net/article/244269.htm

COLUMN分区：https://www.jb51.net/article/96515.htm

LIST分区：https://www.jb51.net/article/244256.htm

HASH分区：https://www.jb51.net/article/244277.htm

KEY分区：https://www.jb51.net/article/244282.htm

子分区：https://www.jb51.net/article/244294.htm

指定各分区路径：https://www.jb51.net/article/244296.htm

分区索引以及分区介绍总结：https://www.jb51.net/article/244300.htm