MySQL 表分区的选择与实践小结

2023-05-11
广东
本文字数：1601 字
阅读完需：约 5 分钟

在一些系统中有时某张表会出现百万或者千万的数据量，尽管其中使用了索引，查询速度也不一定会很快。这时候可能就需要通过分库，分表，分区来解决这些性能瓶颈。

一. 选择合适的解决方法

分库分表。
分区。

2.1. innodb 存储引擎。

innodb，支持事务处理，外来键，在查询方面要慢于 myisam。对并发友好，支持行锁和表锁，行锁的形成要看查询条件。有共享空间结构和独立空间结构，保存的格式有 frm 和 ibddata1(共享结构)，ibd(独立结构)。

2.1.1.共享空间结构。

共享结构为 innodb 默认的结构，除了 frm 保存 innodb 表结构外，整个数据库所有表的索引和数据源都保存在 ibdata 中。可以通过在 mysql-ini 中添加 innodb_file_per_table=1 设置为独立空间结构。

2.1.2.独立空间结构。

独立空间结构就是每个对应的表保存对应的数据源和索引在一个后缀为 ibd 的文件中，表结构同样也保存在 frm 中。

2.2. myisam 存储引擎

myisam 是 mysql 默认存储引擎，不支持事务，但是会对 I/O 进行平均分配，相较于 innodb 查询速度要快，对并发不友好，支持表锁。格式 frm 同样也是表结构，myd 为表的数据源，myi 表的索引储存(所以一张表的索引不是越多越好，因为在添加和修改数据时也需要对索引库进行修改和添加)

二. 表分区的几种分区类型。

1. RANGE 分区：基于属于一个给定连续区间的列值，把多行分配给分区。

2. LIST 分区：类似于按 RANGE 分区，区别在于 LIST 分区是基于列值匹配一个离散值集合中的某个值来进行选择。

3. HASH 分区：基于用户定义的表达式的返回值来进行选择的分区，该表达式使用将要插入到表中的这些行的列值进行计算。这个函数可以包含 MySQL 中有效的、产生非负整数值的任何表达式。

4. KEY 分区：类似于按 HASH 分区，区别在于 KEY 分区只支持计算一列或多列，且 MySQL 服务器提供其自身的哈希函数。必须有一列或多列包含整数值。

具体介绍可以搜索以下"mysql 表分区的分区类型"。

三. 表分区的创建。

par 格式为保存的是分区的规则，ibd 就是分别为每一块分区后的数据源，以下为 innodb 分区，myisam 的是有多个 myd 文件同时也存在 par。

1. 新建表时添加分区。

比如以下创建一张 employees 的表，并创建了 list 类型的 4 个分区，以 store_id 字段为分区字段。

CREATE TABLE employees (    id INT NOT NULL,    fname VARCHAR(30),    lname VARCHAR(30),    hired DATE NOT NULL DEFAULT '1970-01-01',    separated DATE NOT NULL DEFAULT '9999-12-31',    job_code INT,    store_id INT)
PARTITION BY LIST(store_id)    PARTITION pNorth VALUES IN (3,5,6,9,17),    PARTITION pEast VALUES IN (1,2,10,11,19,20),    PARTITION pWest VALUES IN (4,12,13,14,18),    PARTITION pCentral VALUES IN (7,8,15,16));

复制代码

2. 存在的表添加分区

将原来的数据以当前的分区规则对原来数据进行规整，以下是对 ztest 表添加 range 类型的 3 个分区，以 id 字段为分区字段。

alter table rm_ztest partition by RANGE (id)   (	PARTITION p0 VALUES LESS THAN (948),  	PARTITION p1 VALUES LESS THAN (960),	PARTITION p3 VALUES LESS THAN MAXVALUE)

复制代码

3. navicat for mysql 工具添加或创建。

平时我使用上面的工具比较多，所以他也有一个添加分区的功能。"新建表"或者是"设计表"，点击"选项"，“分割区”就可以进入分区的创建了。