MySQL性能优化（二）

1. MySQL索引分区、分块的操作与效率提升
   1.1 MySQL分区，分块概念
   1.2 分区类型
   1.3 创建分区的原则
   1.4 分区的操作
   1.5 建立分区表后对效率的影响分析
   1.6 MySQL索引分区的效率提升分析
   1.7 分区优点分析
2. MySQL索引分区的应用原则与需要综合考虑的设计问题
   2.1 MySQL索引分区、分块的应用原则
   2.2 MySQL分区需要综合考虑的设计问题

1、 MySQL索引分区、分块的操作与效率提升
1.1 MySQL分区概念：
MySQL自5.1开始对分区(Partition)有支持。通俗地讲表分区是将一大表，根据条件分割成若干个小表，是将数据分段划分在多个位置存放，可以是同一块磁盘也可以在不同的机器。分区后，表面上还是一张表，但数据散列到多个位置了。
1.2 分区类型：
1)RANGE 分区：基于属于一个给定连续区间的列值，把多行分配给分区。（区间时间段）
2)LIST 分区：类似于按RANGE分区，区别在于LIST分区是基于列值匹配一个离散值集合中的某个值来进行选择。（离散状态值）
3)HASH分区：基于用户定义的表达式的返回值来进行选择的分区，该表达式使用 将要插入到表中的这些行的列值进行计算。这个函数可以包含MySQL中有效的、产生非负整数值的任何表达式。（表达式返回值分区）
4)KEY分区：类似于按HASH分区，区别在于KEY分区只支持计算一列或多列，且MySQL服务器提供其自身的哈希函数。必须有一列或多列包含整数值。 （整数值按key分区，MySQL提供自身函数）
1.3 创建分区的原则
1)做分区时，要么不定义主键，要么把分区字段加入到主键中。
2)分区字段不能为NULL，要不然怎么确定分区范围呢，所以尽量NOT NULL。
1.4 分区的操作
1.4.1 通过一个实例来讲解MySQL分区的操作。
考虑这样一张数据表：
初始化时，这张表有1809662 (select count(1) from tab_sell_r_order_detail; )条数据。
1.4.2 创建分区表
1)创建range分区表
CREATE TABLE t_range(
seq_id INT(11) NOT NULL AUTO_INCREMENT,
userid INT(11) NOT NULL,
orderid INT(11) NOT NULL,
create_time datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
sale_status INT(4) DEFAULT NULL,
PRIMARY KEY(seq_id,create_time)
)
partition by range(to_days(create_time))
(
partition p1501 values less than (to_days(‘2015-01-01’)),
partition p1504 values less than (to_days(‘2015-04-01’)),
partition p1507 values less than (to_days(‘2015-07-01’)),
partition p1510 values less than (to_days(‘2015-10-01’)),
partition p1601 values less than (to_days(‘2016-01-01’)),
partition p1604 values less than (to_days(‘2016-04-01’))
);
将数据导入：INSERT INTO t_range SELECT * FROM tab_sell_r_order_detail_partition ;
2)创建list分区表
CREATE TABLE t_list(
seq_id INT(11) NOT NULL AUTO_INCREMENT,
userid INT(11) NOT NULL,
orderid INT(11) NOT NULL,
create_time datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
sale_status INT(4) NOT NULL,
PRIMARY KEY(seq_id,sale_status)
)
partition by list(sale_status)
(
partition p0 values in (0,1,3,5,7,9),
partition p1 values in (2,4,6,8,10)
);
将数据导入INSERT INTO t_list SELECT * FROM tab_sell_r_order_detail_partition ;
3)创建hash分区表
CREATE TABLE t_hash(
seq_id INT(11) NOT NULL AUTO_INCREMENT,
userid INT(11) NOT NULL,
orderid INT(11) NOT NULL,
create_time datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
sale_status INT(4) NOT NULL,
PRIMARY KEY(seq_id,create_time)
)
partition by hash(YEAR(create_time))
partitions 4;
将数据导入INSERT INTO t_hash SELECT * FROM tab_sell_r_order_detail_partition ;
4)创建key分区表
CREATE TABLE t_key(
seq_id INT(11) NOT NULL AUTO_INCREMENT,
userid INT(11) NOT NULL,
orderid INT(11) NOT NULL,
create_time datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
sale_status INT(4) NOT NULL,
PRIMARY KEY(seq_id,sale_status)
)
partition by key(sale_status)
partitions 4;
将数据导入INSERT INTO t_key SELECT * FROM tab_sell_r_order_detail_partition ;
1.4.3 新增分区
ALTER TABLE t_range ADD PARTITION (PARTITION p1605 VALUES LESS THAN (to_days(‘2016-05-01’)));
1.4.4 删除分区
当删除了一个分区，也同时删除了该分区中所有的数据。ALTER TABLE t_range DROP PARTITION p1605;
1.4.5 分区的合并
下面的sql将p1501- p1604六个分区，合并为p150104，p150710，p160104三个分区。
ALTER TABLE t_range
REORGANIZE PARTITION p1501,p1504,p1507,p1510,p1601,p1604 INTO
(
PARTITION p150104 VALUES LESS THAN (to_days(‘2015-04-01’)),
PARTITION p150710 VALUES LESS THAN (to_days(‘2015-10-01’)),
PARTITION p160104 VALUES LESS THAN (to_days(‘2016-04-01’))
);
1.5 建立分区表后对效率的影响分析
表tab_sell_r_order_detail_partition是未建立分区的表，表t_range，t_list，t_hash，t_key相当于表tab_sell_r_order_detail_partition的分区表副本
SELECT SQL_NO_CACHE t.*FROM
tab_sell_r_order_detail_partition t
WHERE
t.create_time < ‘2016-01-01’AND t.create_time > ‘2015-10-10’
耗时990ms。
通过分析查询计划 ，该sql执行查询时进行了全表扫描 。接下来再来看t_range分区表，通过分析查询计划，耗时480ms。
可以看出，通过分区表进行查询由于查询结果集位于p1501和p1601两个分区，所以扫描行数为这两个分区的行数之和。
由此可见，经过range分区后，查询效率提升了206%。
1.6 MySQL索引分区的效率提升分析
1.6.1 Mysql索引分区的概念与设计问题
1)分区表建立的索引即分区索引，属于本地索引（local index）。如果不是分区表，则索引不能分区 。
2)因为有全局约束的问题，MySQL分区表明确不支持外键，并且主键和唯一键必须要包含所有分区列 。
1.6.2 分析分区表和索引分区前后的效率提升
未分区和分区表前后的效率提升我们已经分析过了，下面来通过实际场景的分析分区表和分区索引表以及索引表和分
区索引表前后的效率提升。
1.6.2.1分区表和分区索引表的查询效率比较
先考虑这样一张分区表：
CREATE TABLE t_index_range(
seq_id INT(11) NOT NULL AUTO_INCREMENT,
userid INT(11) NOT NULL,
orderid INT(11) NOT NULL,
create_time datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
sale_status INT(4) DEFAULT NULL,
PRIMARY KEY(seq_id,orderid)
)
partition by range(orderid)
(
partition p1 values less than (50000),
partition p2 values less than (100000),
partition p3 values less than (150000),
partition p4 values less than (200000),
partition p5 values less than (250000),
partition p6 values less than (300000),
partition p7 values less than (350000),
partition p8 values less than (400000),
partition p9 values less than (450000),
partition p10 values less than (500000)
);
根据销售记录id来分成10个分区，每个分区包含5w条销售记录信息。
SELECT SQL_NO_CACHE t.* FROM t_index_range t WHERE t.orderid IN(13, 17);耗时110ms。
因为被查找的订单编号的行数位于p1分区内，所以对分区表p1分区做全表扫描查询，被扫描行数为281625行。
现在来对分区表t_index_range的orderid列加索引
对分区索引表执行相同的查询操作


可以看出，因为被查询的数据行存在于P1分区，而且在P1分区查询时使用了建立的索引index_orderid，扫描的行数只有8行。
通过实例可以看出，在这个案例下分区索引表的查询效率比分区表的效率提高了10倍！
1.6.2.2 索引表和分区索引表的查询效率比较
考虑这样一个数据结构：

表t_index为销售人员跟踪关联表，tab_sell_order_attr为销售订单属性表。
考虑这样一个实际业务场景，要求查询出从2014年10月10日到2015年7月10日这段时间内的，销售记录id为（1,7,9,11,13,15,17,20,21,52000,101000）这类产品的机型名称、销售总数、销售占比。
t_index表在orderid列上建立索引index_orderid。
执行查询：
SELECT SQL_NO_CACHE
t2.productname productname,
count(1)AS total,
ROUND(
(
count(1)/(
SELECT
count(1)cnt
FROM
t_index t1
LEFT JOIN tab_sell_order_attr t2 ON t1.orderid = t2.orderid
WHERE
t1.create_time > ‘2014-10-10’
AND t1.create_time < ‘2015-07-10’
AND t1.orderid IN(
1,7,9,11,13,15,17,20,21,52000,101000
)
)
)* 100,
2
)AS percent
FROM
t_index t1
LEFT JOIN tab_sell_order_attr t2 ON t1.orderid = t2.orderid
WHERE
t1.create_time > ‘2014-10-10’
AND t1.create_time < ‘2015-07-10’
AND t1.orderid IN(
1,7,9,11,13,15,17,20,21,52000,101000
)
GROUP BY
t2.productname
ORDER BY
percent DESC
耗时7.65s。
分析查询计划：

通过表t_index索引建立，能够快速定位到销售记录id的记录行数,但是因为表tab_sell_order_attr表未建立索引，所以需要进行全表扫描。
表t_range为销售人员跟踪关联表的分区索引表，对字段create_time进行分区，分为6个分区，同时对orderid建立索引index_orderid。
执行查询 ：
SELECT SQL_NO_CACHE
t2.productname productname,
count(1)AS total,
ROUND(
(
count(1)/(
SELECT
count(1)cnt
FROM
t_range t1
LEFT JOIN tab_sell_order_attr t2 ON t1.orderid = t2.orderid
WHERE t1.create_time > ‘2014-10-10’
AND t1.create_time < ‘2015-07-10’
AND t1.orderid IN(1,7,9,11,13,15,17,20,21, 52000, 101000)
)
)* 100,
2
)AS percent
FROM t_range t1
LEFT JOIN tab_sell_order_attr t2 ON t1.orderid = t2.orderid
WHERE t1.create_time > ‘2014-10-10’
AND t1.create_time < ‘2015-07-10’
AND t1.orderid IN(1,7,9,11,13,15,17,20,21,52000, 101000)
GROUP BY t2.productname
ORDER BY percent DESC
耗时7.53s。
通过分析查询计划：

可见，由于表t_range对create_time建立了分区所以只查询记录所在分区的行数，并且因为对orderid建立了索引所以能快速定位要搜索的记录的行数。
通过2个案例的分析，可见，分区索引表比索引表效率仅仅提高了5%左右。
我的分析是，从数据库的角度上来讲，分区表侧重于表的块状范围划定，而索引的建立是更侧重于目标记录的快速定位。所以单从查询效率上来讲，在分区表上建立索引和未分区表上建立索引的差别不大。但是还是要考虑比较复杂的业务场景，某些场景下二者可能会有较大的差别。
1.7 分区优点分析
1) 分区可以分在多个磁盘，存储更大一点。
2) 根据查找条件，也就是where后面的条件，查找只查找相应的分区不用全部查找了。
3) 进行大数据搜索时可以进行并行处理。
4) 跨多个磁盘来分散数据查询，来获得更大的查询吞吐量。
5) 对于那些已经失去保存意义的数据，通常可以通过删除与那些数据有关的分区，很容易地删除那些数据。相反地，在某些情况下，添加新数据的过程又可以通过为那些新数据专门增加一个新的分区，来很方便地实现。
6) 涉及到例如SUM()和COUNT()这样聚合函数的查询，可以很容易地进行并行处理。

2.MySQL索引分区、分块的应用原则与需要综合考虑的设计问题
2.1 MySQL索引分区、分块的应用原则
2.1.1分区键，主键，唯一键
规则如下：在分区表中所有作为分区的列必须是唯一键的一部分。换句话说，表中的每个唯一键必须用在表中分区表达式的列上。（包含唯一主键）
错误示例：
CREATE TABLE t1 (
col1 INT NOT NULL,
col2 DATE NOT NULL,
col3 INT NOT NULL,
col4 INT NOT NULL,
UNIQUE KEY (col1, col2)
)
PARTITION BY HASH(col3)
PARTITIONS 4;

CREATE TABLE t2 (    col1 INT NOT NULL,    col2 DATE NOT NULL,    col3 INT NOT NULL,    col4 INT NOT NULL,    UNIQUE KEY (col1),    UNIQUE KEY (col3))PARTITION BY HASH(col1 + col3)PARTITIONS 4;至少有一个唯一键没有包含在分区表达式的所有列上。 

正确示例：
CREATE TABLE t1 (
col1 INT NOT NULL,
col2 DATE NOT NULL,
col3 INT NOT NULL,
col4 INT NOT NULL,
UNIQUE KEY (col1, col2, col3)
)
PARTITION BY HASH(col3)
PARTITIONS 4;

CREATE TABLE t2 (    col1 INT NOT NULL,    col2 DATE NOT NULL,    col3 INT NOT NULL,    col4 INT NOT NULL,    UNIQUE KEY (col1, col3))PARTITION BY HASH(col1 + col3)PARTITIONS 4;

2.1.2 存储引擎和分区限制的关系
MERGE存储引擎：用MERGE作为存储引擎的表不能分区。
FEDERATED存储引擎：用FEDERATED作为存储引擎的表不能分区。
CSV存储引擎：用CSV作为存储引擎的表不能分区。
InnoDB存储引擎：InnoDB存储引擎存储引擎表可以分区。但是表中不能有外键约束或者被外键约束。
用户自定义分区和MySQL集群引擎（NDB存储引擎）：通过分区键（线性KEY）分区是 MySQL集群引擎支持的唯一方式。线性KEY分区概念和线性HASH分区一样。
2.1.3 分区限制和函数的关系
只有下列的分区函数能够用在分区表达式里：

为什么只能是这些函数？主要原因是基于分区条件的限制。反例：sum()，substr()
2.1.4 分区和表锁的关系
在MySQL5.6.5或更早的时候，当MyISAM这类的存储引擎执行DML或DDL语句时，会触发表级别的锁，这样的语句会影响分区表和表锁融为一体。即，所有分区被锁定直到语句结束。直到MySQL 5.6.6实现分区锁修剪，在许多情况下消除了不必要的锁。在MySQL 5.6.6之后，大多数语句读取或更新分区的MyISAM表只造成影响的分区被锁定。MySQL 5.6.6之后，只有实际上包含行满足SELECT语句的WHERE条件的那些分区会被锁定。
对DML语句的影响：
1)select语句（包含unions or joins）现在只锁定那些实际需要读取的分区。
2)更新修剪锁只在那些没有被分区的列上被更新。
3)替换和插入现在只锁定那些要插入或替换的行的分区。
4)insert、select现在只锁定需要读取的源表中的这些分区，尽管目标表中的所有分区都已锁定。
对DDL语句的影响：
1)创建视图不再导致任何锁。
2)修改表…交换分区会触发锁，只有被修改的表和被交换分区被锁定。
3)修改表，删除分区会触发锁，只有清空分区时会被锁。
4)alter table语句仍然在表级别上使用元数据锁。
全部参考自MySQL官方文档：
（https://dev.mysql.com/doc/refman/5.6/en/partitioning-limitations.html）
2.2 MySQL分区需要综合考虑的设计问题
2.2.1MySQL分区的两种形式
1)水平分区（Horizontal Partition）
这种MySQL分区形式是对表的行进行分区，通过这样的方式不同分组里面的物理列分割的数据集得以组合，从而进行个体分割（单分区）或集体分割（1个或多个分区）。所有在表中定义的列在每个数据集中都能找到，所以表的特性依然得以保持。
举个简单例子：一个包含十年发票记录的表可以被分区为十个不同的分区，每个分区包含的是其中一年的记录。
2)垂直分区（Vertical Partition）
这种MySQL分区方式一般来说是通过对表的垂直划分来减少目标表的宽度，使某些特定的列被划分到特定的分区，每个分区都包含了其中的列所对应的行。
举个简单例子：一个包含了大text和BLOB列的表，这些text和BLOB列又不经常被访问，这时候就要把这些不经常使用的text和BLOB了划分到另一个分区，在保证它们数据相关性的同时还能提高访问速度。
2.2.2 分区表类型设计和子分区
1)range分区
例如表中存储的数据可以以时间为维度划分，即有一列为时间（必须是date或datetime类型）。并且查询条件中有以时间列为条件的查询需求。
CREATE TABLE t_range(
seq_id INT(11) NOT NULL AUTO_INCREMENT,
userid INT(11) NOT NULL,
orderid INT(11) NOT NULL,
create_time datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
sale_status INT(4) DEFAULT NULL,
PRIMARY KEY(seq_id,create_time)
)
partition by range(to_days(create_time))
(
partition p1501 values less than (to_days(‘2015-01-01’)),
partition p1504 values less than (to_days(‘2015-04-01’)),
partition p1507 values less than (to_days(‘2015-07-01’)),
partition p1510 values less than (to_days(‘2015-10-01’)),
partition p1601 values less than (to_days(‘2016-01-01’)),
partition p1604 values less than (to_days(‘2016-04-01’))
);
例如如上分区表，可以按时间分成6个区，每个分区存储3个月的数据。
2)list分区
list分区主要是基于列值匹配一个离散值集合中的某个值来进行选择。
例如如下分区：
CREATE TABLE t_list(
seq_id INT(11) NOT NULL AUTO_INCREMENT,
userid INT(11) NOT NULL,
orderid INT(11) NOT NULL,
create_time datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
sale_status INT(4) NOT NULL,
PRIMARY KEY(seq_id,sale_status)
)
partition by list(sale_status)
(
partition p0 values in (1,3,5,7,9),
partition p1 values in (2,4,6,8,10)
);
表中sale_status表示销售状态码，包括出厂，已销售，未销售，已退货，销售异常等等状态信息。销售状态码的是一个离散的集合。这样可以通过list分区，分成2个区，每个分区存储拥有一部分销售状态码的数据列。
3)HASH 分区
基于用户定义的表达式的返回值来进行选择的分区，该表达式使用将要插入到表中的这些行的列值进行计算。这个函数可以包含MySQL中有效的、产生非负整数值的任何表达式。
例如：
CREATE TABLE t_hash(
seq_id INT(11) NOT NULL AUTO_INCREMENT,
userid INT(11) NOT NULL,
orderid INT(11) NOT NULL,
create_time datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
sale_status INT(4) NOT NULL,
PRIMARY KEY(seq_id,create_time)
)
partition by hash(YEAR(create_time))
partitions 4;
在如上的分区表中，根据经过YEAR函数映射创建日期进行分区。YEAR(create_time)会返回年份，则分区表会按照数据列所属年份进行分区。
4)KEY分区
按照KEY进行分区类似于按照HASH分区，除了HASH分区使用的用户定义的表达式，而KEY分区的哈希函数是由MySQL 服务器提供。分区需以表中的主键或唯一列来作为分区的关键字指定，否则将不能成功设置。关键字的类型并不一定是需要正整数，字符型的列也可以作为关键字。示例如下：
CREATE TABLE t_key(
seq_id INT(11) NOT NULL AUTO_INCREMENT,
userid INT(11) NOT NULL,
orderid INT(11) NOT NULL,
create_time datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
sale_status INT(4) NOT NULL,
PRIMARY KEY(seq_id,sale_status)
)
partition by key(sale_status)
partitions 4;
5)子分区
子分区是分区表中每个分区的再次分割，子分区既可以使用HASH分区，也可以使用KEY分区。这也被称为复合分区（composite partition）。将每个分区继续细分，变成更小的分区。有这种需求的，可能是单表数据量非常大的场景。
应用原则：
1，如果一个分区中创建了子分区，其他分区也要有子分区
2，如果创建了子分区，每个分区中的子分区数必然相同
3，同一分区内的子分区，名字不相同，不同分区内的子分区名子可以相同（5.1.50不适用）
例如如下分区表：
CREATE TABLE t_sub_partition(
seq_id INT(11) NOT NULL AUTO_INCREMENT,
userid INT(11) NOT NULL,
orderid INT(11) NOT NULL,
create_time datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
sale_status INT(4) DEFAULT NULL,
PRIMARY KEY(seq_id,create_time)
)
partition by range(year(create_time)) SUBPARTITION BY HASH(TO_DAYS(create_time))
(
partition p0 values less than (2014)(SUBPARTITION s0,SUBPARTITION s1,SUBPARTITION s2),
partition p1 values less than (2015)(SUBPARTITION s3,SUBPARTITION s4,SUBPARTITION s5),
partition p2 values less than MAXVALUE(SUBPARTITION tank0,SUBPARTITION tank1,SUBPARTITION tank3)
);
主分区按年份分区，子分区按天进行hash分区。
6)列分区
列分区就是利用多个列值进行分区，range分区与list分区都支持列分区。列分区不支持表达式，只支持列名，但列名可以是多个。列分区是通过比较多个列值形成的元组进行分区。
例如：
CREATE TABLE t_column_partition(
seq_id INT(11)NOT NULL AUTO_INCREMENT,
userid INT(11)NOT NULL,
orderid INT(11)NOT NULL,
create_time datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
sale_status INT(4)DEFAULT NULL,
PRIMARY KEY(seq_id,orderid,create_time)
)PARTITION BY RANGE COLUMNS(orderid, create_time)(
PARTITION p0 VALUES less than(500000, ‘2015-01-01’),
PARTITION p1 VALUES less than(1000000, ‘2015-12-12’),
PARTITION p2 VALUES less than(MAXVALUE, MAXVALUE)
);
分区表t_column_partition在列orderid和create_time两列上进行范围分区。值得注意的是，当写入数据时，会进行数据元组的判断来确定该行数据位于哪个分区以保证分区的完备性原则。元组判断规则这里不再赘述。