乐趣区

关于数据库:MySQL表分区配置指南

jiezi

在一些零碎中有时某张表会呈现百万或者千万的数据量，只管其中应用了索引，查问速度也不肯定会很快。这时候可能就须要通过分库，分表，分区来解决这些性能瓶颈。

一. 抉择适合的解决办法

1. 分库分表。

分库分表从名字上就明确是须要创立额定的新数据库或新表，能够建在其余的机器上也能够是和以后数据库同一台机器。在优化查问上可能须要买新机器或者要批改逻辑代码了。比方一张 user 表，当数据超过 10W，就能够创立新的 user 表，假如是 user2。通过接管的 UID，和分组 10W(假设)，取模就是对应的 user 表名的索引数字。

2. 分区。

分区这里是指表分区，mysql 数据库管理系统提供的表性能，分区后逻辑上是同一张表，物理上数据存储是离开的。是否优化查问还取决于在查问中是否应用到了分区字段，这个就和索引的应用有点相似，然而益处就是这个不像分库分表，能够简直不批改业务逻辑代码就能够晋升速度。上面再总结一下 mysql 数据保留格局和 innodb，myisam。

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 工具增加或创立。

平时我应用下面的工具比拟多，所以他也有一个增加分区的性能。” 新建表 ” 或者是 ” 设计表 ”，点击 ” 选项 ”，“宰割区”就能够进入分区的创立了。

四. 分区查问的试验后果。

我就拿了一张几百万数据的表备份了正本，其中一张创立了分区，并应用 id 分区字段进行查问。

从下面两张截图能够看出应用了分区的查问速度要比未应用分区快差不多 1 倍，然而如果不应用 id 为查问条件或没有应用到，速度二者是一样的，甚至有时分区过的还要慢于未分区的，所以在应用上还需联合以后业务做正当的抉择。explain partitions select * from table_name ……       
能够查看以后查问是否应用了分区，分区应用的是哪几个等等信息。

退出移动版