MySQL性能优化分区之实战 _MySQL

一: 分区简介分区是根据一定的规则，数据库把一个表分解成多个更小的、更容易管理的部分。就访问数据库应用而言，逻辑上就只有一个表或者一个索引，但实际上这个表可能有N个物理分区对象组成，每个分区都是一个独立的对象，可以独立处理，可以作为表的一部分进行处理。分区对应用来说是完全透明的，不影响应用的业务逻辑。
分区有利于管理非常大的表，它采用分而治之的逻辑，分区引入了分区键的概念，分区键用于根据某个区间值(或者范围值)、特定值列表或者hash函数值执行数据的聚集，让数据根据规则分布在不同的分区中，让一个大对象碧昂城一些小对象。
MySQL分区即可以对数据进行分区也可以对索引进行分区。
分区类型

range分区：基于一个给定的连续区间范围(区间要求连续并且不能重叠)，把数据分配到不同的分区
list分区：类似于range分区，区别在于list分区是居于枚举出的值列表分区，range是基于给定的连续区间范围分区
hash分区：基于给定的分区个数，把数据分配到不同的分区
key分区：类似于hash分区

注意：无论哪种分区，要么你分区表上没有主键/唯一键，要么分区表的主键/唯一键都必须包含分区键，也就是说不能使用主键/唯一键字段之外的其它字段分区。
MySQL分区的有限主要包括以下4个方面：

和单个磁盘或者文件系统分区相比，可以存储更多数据
优化查询。在where子句中包含分区条件时，可以只扫描必要的一个或者多个分区来提高查询效率；同时在涉及sum()和count()这类聚合函数的查询时，可以容易的在每个分区上并行处理，最终只需要汇总所有分区得到的结果
对于已经过期或者不需要保存的数据，可以通过删除与这些数据有关的分区来快速删除数据
跨多个磁盘来分散数据查询，以获得更大的查询吞吐量

分区和水平分表功能类似，将一个大表的数据分割到多张小表中去，由于查询不需要全表扫描了，只需要扫描某些分区，所以分区能提高查询速度。

水平分表需要用户预先手动显式创建出多张分表(如tbl_user0, tbl_user1, tbl_user2)，在物理上实实在在的创建多张表，通过客户端代理(Sharding-JDBC等)或者中间件代理(Mycat等)来实现分表逻辑。
分区是MySQL的一个插件Plugin功能，将一张大表的数据在数据库底层分成多个分区文件(如tbl_user#P#p0.ibd, tbl_user#P#p1.ibd, tbl_user#P#p2.ibd)，和水平分表不同的是分区不需要显式的创建“分表”,数据库会自动创建分区文件的，用户看到的只是一张普通的表，其实是对应的是多个分区，这个是对用户是屏蔽的、透明的，在使用上和使用一张表完全一样，不需要借助任何功能来实现。分区是一种逻辑上的水平分表，在物理层面还是一张表。

二：数据库文件CREATE TABLE `tbl_user_innodb` ( `id` int(11) NOT NULL AUTO_INCREMENT, `username` varchar(255) DEFAULT NULL, `email` varchar(20) DEFAULT NULL, `age` tinyint(4) DEFAULT NULL, `type` int(11) DEFAULT NULL, `create_time` datetime DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=5100002 DEFAULT CHARSET=utf8;CREATE TABLE `tbl_user_myisam` ( `id` int(11) NOT NULL AUTO_INCREMENT, `username` varchar(255) DEFAULT NULL, `email` varchar(20) DEFAULT NULL, `age` tinyint(4) DEFAULT NULL, `type` int(11) DEFAULT NULL, `create_time` datetime DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (`id`) ) ENGINE=myisam AUTO_INCREMENT=5100002 DEFAULT CHARSET=utf8;通过show variables like ‘%datadir%’;命令查看mysql的data存放目录，进入所在的数据库目录(如test)，不同的引擎数据库文件格式不同

myisam
.frm : 存储表结构
.MYD: 存储表数据
.MYI: 存储索引文件
innodb: 只有设置成独立表空间才能做成功表分区
.frm : 表结构
.ibd : 数据 + 索引

文章插图

三：插入500W条数据CREATE TABLE `tbl_user_no_part` ( `id` int(11) NOT NULL AUTO_INCREMENT, `username` varchar(255) DEFAULT NULL, `email` varchar(20) DEFAULT NULL, `age` tinyint(4) DEFAULT NULL, `type` int(11) DEFAULT NULL, `create_time` datetime DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (`id`)) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;-- 修改mysql默认的结束符号，默认是分号；但是在函数和存储过程中会使用到分号导致解析不正确delimiter $$-- 随机生成一个指定长度的字符串create function rand_string(n int) returns varchar(255) begin# 定义三个变量 declare chars_str varchar(100) default 'abcdefghijklmnopqrstuvwxyzABCDEFJHIJKLMNOPQRSTUVWXYZ'; declare return_str varchar(255) default ''; declare i int default 0; while i < n doset return_str = concat(return_str, substring(chars_str, floor(1+rand()*52), 1)); set i = i + 1; end while; return return_str;end $$-- 创建插入的存储过程create procedure insert_user(in start int(10), in max_num int(10))begin declare i int default 0;set autocommit = 0;repeat set i = i + 1; insert into tbl_user_no_part values ((start+i) ,rand_string(8), concat(rand_string(6), '@random.com'), 1+FLOOR(RAND()*100), 3, now()); until i = max_num end repeat; commit;end $$-- 将命令结束符修改回来delimiter ;-- 调用存储过程，插入500万数据，需要等待一会时间，等待执行完成call insert_user(100001,5000000);-- Query OK, 0 rows affected (7 min 49.89 sec) 我的macbook Pro i5 8G内存用了8分钟才执行完select count(*) from tbl_user_no_part;