mysql mssql 性能对比_理解MySQL表分区(partition)，创建表分区，对比分析查询性能...-CSDN博客

分区就是把一个数据表的文件和索引分散存储在不同的物理文件中。

用户所选择的、实现数据分割的规则被称为分区函数，这在MySQL中它可以是模数，或者是简单的匹配一个连续的数值区间或数值列表，或者是一个内部HASH函数，或一个线性HASH函数。

先验证MySql 是否支持分区？

MySQL从5.1开始支持分区功能：

SHOW VARIABLES LIKE '%partition%';

Empty set (0.00 sec)

如果查询结果显示Empty，表示不支持分区。但是上面的查询方法只是针对MySQL5.6 以下版本。

如果MySQL5.6以及以上版本，需要使用下面的查询命令：

show plugins;

上面的查询方法会显示所有插件，如果有红色框的记录(ACTIVE)，表示支持分区。

分区的几种模式

(1)Range(范围)

这种模式允许DBA将数据划分不同范围。例如DBA可以将一个表通过separated年份划分成4个分区，2011前的数据，2011-2014 年的数据，2015-2019年的数据，以及任何在2020年(包括2020年)后的数据。

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 RANGE (YEAR(separated)) (

PARTITION p0 VALUES LESS THAN (2011),

PARTITION p1 VALUES LESS THAN (2015),

PARTITION p2 VALUES LESS THAN (2020),

PARTITION p3 VALUES LESS THAN MAXVALUE

);

执行完成之后，打开MySQL 的数据目录：

可以看到 employees#p#p0.ibd到employees#p#p3.ibd 文件，这些是分区的表空间文件。employees.frm 存放表结构。

由此可见，MySQL 通过分区把数据保存到不同的文件里，同时索引也是分区的。

相对于未分区的表来说，分区后单独的数据文件和索引文件的大小都明显降低，效率则明显提升。

(2)Hash(哈希)

这种模式允许DBA通过对表的一个或多个列的Hash Key进行计算，最后通过这个Hash码不同数值对应的数据区域进行分区。例如DBA可以建立一个对表主键进行分区的表。

CREATE TABLE storeworkersstoreworkers (

id INT NOT NULL,

fname VARCHAR(30),

lname VARCHAR(30),

job_code INT,

store_id INT

)

PARTITION BY HASH(store_id)

PARTITIONS 4

(3)Key(键值)

上面Hash模式的一种延伸，这里的Hash Key是MySQL系统产生的。

CREATE TABLE user (

id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,

name VARCHAR(30) NOT NULL DEFAULT '',

email VARCHAR(30) NOT NULL DEFAULT ''

)

PARTITION BY KEY (id) PARTITIONS 4 (

PARTITION p0,

PARTITION p1,

PARTITION p2,

PARTITION p3

);

(4)List(预定义列表)

这种模式允许系统通过DBA定义的列表的值所对应的行数据进行分割。例如：DBA建立了一个横跨三个分区的表，分别根据2004年2005年和2006年值所对应的数据。

CREATE TABLE workers (

id INT NOT NULL,

fname VARCHAR(30),

lname VARCHAR(30),

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)

);

(5)Composite(复合模式)

是以上模式的组合使用而已。举例：在初始化已经进行了Range范围分区的表上，我们可以对其中一个分区再进行hash哈希分区。

创建存储过程

下面存储过程，并调用该存储过程往前面的分区表中批量插入实验数据。

drop procedure if exists load_employees

DELIMITER //

CREATE PROCEDURE load_employees()

begin

declare v int default 0;

while v < 10000

do

insert into employees(id, fname, lname, hired, separated, job_code, store_id)

values (v,'rickie', 'lee',

adddate('2008-01-01',(RAND(v)*36520) mod 3652),

adddate('2010-10-01',(RAND(v)*36520) mod 3652),

CEIL(RAND()*15), CEIL(RAND()*100));

set v = v + 1;

end while;

end

//

DELIMITER ; #将语句的结束符号恢复为分号

执行存储过程，往employees 表中插入10000条数据：

call load_employees();

另外创建一个未分区表 employees_nopartition，同时也插入 10000条数据。

测试sql性能

分别查询分区表和未分区表，查看执行时间。

select count(*) from employees where separated > date '2011-01-01'and separated < date '2011-12-31';

select count(*) from employees_nopartition where separated > date '2011-01-01'and separated < date '2011-12-31';

分区表的执行时间比普通表明显小。

通过explain语句来分析执行情况

EXPLAIN select count(*) from employees where separated > date '2011-01-01'and separated < date '2011-12-31';

EXPLAIN select count(*) from employees_nopartition where separated > date '2011-01-01'and separated < date '2011-12-31';