苟哥的笔记本
首页
文章归档
关于
文章归档
关于
首页
编程
正文
Mysql数据库表分区深入详解
苟哥
2022-07-23 PM
1169℃
0条
> 引言:mysql数据库分区的由来? 1)传统不分区数据库痛点 mysql数据库中的数据是以文件的形势存在磁盘上的,默认放在/mysql/data下面(可以通过my.cnf中的datadir来查看), 一张表主要对应着三个文件,一个是frm存放表结构的,一个是myd存放表数据的,一个是myi存表索引的。 ```shell [root@laoyang test]# ls -al 总用量 1811444 drwx------ 2 mysql mysql 4096 10月 17 15:12 . drwxr-xr-x 4 mysql mysql 4096 10月 17 14:37 .. -rw-rw---- 1 mysql mysql 8962 10月 10 17:45 bz_info.frm -rw-rw---- 1 mysql mysql 347727032 10月 17 15:16 bz_info.MYD -rw-rw---- 1 mysql mysql 56341504 10月 17 15:16 bz_info.MYI -rw-rw---- 1 mysql mysql 8962 10月 10 17:44 dz_info.frm -rw-rw---- 1 mysql mysql 418645764 10月 17 15:15 dz_info.MYD -rw-rw---- 1 mysql mysql 81381376 10月 17 15:15 dz_info.MYI ``` 2)数据库分区处理 如果一张表的数据量太大的话,那么myd,myi就会变的很大,查找数据就会变的很慢,这个时候我们可以利用mysql的分区功能,在物理上将这一张表对应的三个文件,分割成许多个小块,这样呢,我们查找一条数据时,就不用全部查找了,只要知道这条数据在哪一块,然后在那一块找就行了。如果表的数据太大,可能一个磁盘放不下,这个时候,我们可以把数据分配到不同的磁盘里面去。 表分区是Mysql被Oracle收购后推出的一个新特性。 ## 一、表分区通俗解释 通俗地讲表分区是将一大表,根据条件分割成若干个小表。mysql5.1开始支持数据表分区了。 如:某用户表的记录超过了600万条,那么就可以根据入库日期将表分区,也可以根据所在地将表分区。当然也可根据其他的条件分区。 ## 二、为什么要对表进行分区? 为了改善大型表以及具有各种访问模式的表的可伸缩性,可管理性和提高数据库效率。 #### 2.1 表分区要解决的问题: 当表非常大,或者表中有大量的历史记录,而“热数据”却位于表的末尾。如日志系统、新闻。。此时就可以考虑分区表。【注:此处也可以使用分表,但是会增加业务的复杂性。】 #### 2.2 表分区有如下优点: 1)与单个磁盘或文件系统分区相比,可以存储更多的数据。 2)对于那些已经失去保存意义的数据,通常可以通过删除与那些数据有关的分区,很容易地删除那些数据。 相反地,在某些情况下,添加新数据的过程又可以通过为那些新数据专门增加一个新的分区,来很方便地实现。 同样的,你可以很快的通过删除分区来移除旧数据。你还可以优化、检查、修复个别分区。 3)一些查询可以得到极大的优化。 可以把一些归类的数据放在一个分区中,可以减少服务器检查数据的数量加快查询。 这主要是借助于满足一个给定WHERE语句的数据可以只保存在一个或多个分区内,这样在查找时就不用查找其他剩余的分区。 PS:因为分区可以在创建了分区表后进行修改,所以在第一次配置分区方案时还不曾这么做时,可以重新组织数据,来提高那些常用查询的效率。 4)涉及到例如SUM()和COUNT()这样聚合函数的查询,可以很容易地进行并行处理。 这种查询的一个简单例子如 ```shell “SELECT salesperson_id, COUNT (orders) as order_total FROM sales GROUP BY salesperson_id;”。 ``` 通过“并行”,这意味着该查询可以在每个分区上同时进行,最终结果只需通过总计所有分区得到的结果。 5)通过跨多个磁盘来分散数据查询,来获得更大的查询吞吐量。 ## 三、mysql分区类型 根据所使用的不同分区规则可以分成几大分区类型。 ![](http://images.kuryun.com/blog/typecho/1658589594.png) #### 3.1 RANGE 分区: 基于属于一个给定连续区间的列值,把多行分配给分区。 举例: ```shell create table foo_range ( id int not null auto_increment, created DATETIME, primary key (id, created) ) engine = innodb partition by range (TO_DAYS(created))( PARTITION foo_1 VALUES LESS THAN (TO_DAYS('2016-10-18')), PARTITION foo_2 VALUES LESS THAN (TO_DAYS('2017-01-01')) ); //新增一个分区 ALTER TABLE foo_range ADD PARTITION( PARTITION foo_3 VALUES LESS THAN (TO_DAYS('2017-10-18')) ); //插入数据 insert into `foo_range` (`id`, `created`) values (1, '2016-10-17'),(2, '2016-10-20'),(3, '2016-1-25'); //查询 explain partitions select * from foo_range where created = '2016-10-20'; //查询结果: mysql> explain partitions select * from foo_range where created = '2016-10-20'; +----+-------------+-----------+------------+-------+---------------+---------+---------+------+------+--------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-----------+------------+-------+---------------+---------+---------+------+------+--------------------------+ | 1 | SIMPLE | foo_range | foo_2 | index | NULL | PRIMARY | 12 | NULL | 2 | Using where; Using index | +----+-------------+-----------+------------+-------+---------------+---------+---------+------+------+--------------------------+ ``` #### 3.2 LIST 分区: 类似于按RANGE分区,区别在于LIST分区是基于列值匹配一个离散值集合中的某个值来进行选择。 ```shell create table foo_list (empno varchar(20) not null , empname varchar(20), deptno int, birthdate date not null, salary int ) partition by list(deptno) ( partition p1 values in (10), partition p2 values in (20), partition p3 values in (30) ); ``` 以上显示,以部门号为分区依据,每个部门一个分区。 #### 3.3 HASH分区: 基于用户定义的表达式的返回值来进行选择的分区,该表达式使用将要插入到表中的这些行的列值进行计算。这个函数可以包含MySQL中有效的、产生非负整数值的任何表达式。 HASH分区主要用来确保数据在预先确定数目的分区中平均分布。在RANGE和LIST分区中,必须明确指定一个给定的列值或列值集合应该保存在哪个分区中。 在HASH分区中,MySQL 自动完成这些工作,你所要做的只是基于将要被哈希的列值指定一个列值或表达式,以及指定被分区的表将要被分割成的分区数量。 ```shell create table foo_hash (empno varchar(20) not null , empname varchar(20), deptno int, birthdate date not null, salary int ) partition by hash(year(birthdate)) partitions 4; ``` 以上创建了4个分区。 #### 3.4 KEY分区: 类似于按HASH分区,区别在于KEY分区只支持计算一列或多列,且MySQL服务器提供其自身的哈希函数。必须有一列或多列包含整数值。 ```shell create table foo_key (empno varchar(20) not null , empname varchar(20), deptno int, birthdate date not null, salary int ) partition by key(birthdate) partitions 4; ``` #### 3.5 复合分区: 基于RANGE/LIST 类型的分区表中每个分区的再次分割。子分区可以是 HASH/KEY 等类型。 ## 四、常见分区操作 ![](http://images.kuryun.com/blog/typecho/1658589744.png) 修改已有表举例: ```shell ALTER TABLE bj_info PARTITION BY RANGE(id) PARTITIONS 14( PARTITION part_00yntai VALUES LESS THAN (610001), PARTITION part_01shxia VALUES LESS THAN (1220001), PARTITION part_02zhfu VALUES LESS THAN (1830001), PARTITION part_03fuhan VALUES LESS THAN (2440001), PARTITION part_04mping VALUES LESS THAN (3660001), PARTITION part_06chngdao VALUES LESS THAN (4270001), PARTITION part_07lonkou VALUES LESS THAN (4880001), PARTITION part_08layang VALUES LESS THAN (5490001), PARTITION part_09laihou VALUES LESS THAN (6100001), PARTITION part_10peglai VALUES LESS THAN (6710001), PARTITION part_11zhoyuan VALUES LESS THAN (7320001), PARTITION part_12qixa VALUES LESS THAN (7930001), PARTITION part_13haiyng VALUES LESS THAN (8540000), PARTITION part_05laisan VALUES LESS THAN MAXVALUE ); ``` ## 五、获取分区表信息的方法 #### 5.1 show create table 表名 可以查看创建分区表的create语句 举例: ```shell mysql> show create table foo_list; +----------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +----------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | foo_list | CREATE TABLE `foo_list` ( `empno` varchar(20) NOT NULL, `empname` varchar(20) DEFAULT NULL, `deptno` int(11) DEFAULT NULL, `birthdate` date NOT NULL, `salary` int(11) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=utf8 /*!50100 PARTITION BY LIST (deptno) (PARTITION p1 VALUES IN (10) ENGINE = MyISAM, PARTITION p2 VALUES IN (20) ENGINE = MyISAM, PARTITION p3 VALUES IN (30) ENGINE = MyISAM) */ | +----------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.01 sec) 5. 2 show table status ``` 可以查看表是不是分区表 举例: ```shell SHOW TABLE STATUS LIKE ‘foo_range’; ``` 结果如红色部分所示: ![](http://images.kuryun.com/blog/typecho/1658589805.png) #### 5.3 查看information_schema.partitions表 如下命令可以查看表具有哪几个分区、分区的方法、分区中数据的记录数等信息 ```shell mysql> select -> partition_name part, -> partition_expression expr, -> partition_description descr, -> table_rows -> from information_schema.partitions where -> table_schema = schema() -> and table_name='foo_range'; +-------+------------------+--------+------------+ | part | expr | descr | table_rows | +-------+------------------+--------+------------+ | foo_1 | TO_DAYS(created) | 736620 | 2 | | foo_2 | TO_DAYS(created) | 736695 | 1 | | foo_3 | TO_DAYS(created) | 736985 | 0 | +-------+------------------+--------+------------+ 3 rows in set (0.00 sec) ``` #### 5.4 explain partitions select语句 通过此语句来显示扫描哪些分区,及他们是如何使用的. 举例如下: ```shell mysql> explain partitions select * from foo_range; +----+-------------+-----------+-------------------+-------+---------------+---------+---------+------+------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-----------+-------------------+-------+---------------+---------+---------+------+------+-------------+ | 1 | SIMPLE | foo_range | foo_1,foo_2,foo_3 | index | NULL | PRIMARY | 12 | NULL | 4 | Using index | +----+-------------+-----------+-------------------+-------+---------------+---------+---------+------+------+-------------+ 1 row in set (0.00 sec) ``` ## 六、性能对比(分区表和非分区表) 步骤一:创建两张表: part_tab(分区表),no_part_tab(普通表) ```shell CREATE TABLE part_tab (c1 int default NULL, c2 varchar(30) default NULL, c3 date not null) PARTITION BY RANGE(year(c3)) (PARTITION p0 VALUES LESS THAN (1995), PARTITION p1 VALUES LESS THAN (1996) , PARTITION p2 VALUES LESS THAN (1997) , PARTITION p3 VALUES LESS THAN (1998) , PARTITION p4 VALUES LESS THAN (1999) , PARTITION p5 VALUES LESS THAN (2000) , PARTITION p6 VALUES LESS THAN (2001) , PARTITION p7 VALUES LESS THAN (2002) , PARTITION p8 VALUES LESS THAN (2003) , PARTITION p9 VALUES LESS THAN (2004) , PARTITION p10 VALUES LESS THAN (2010), PARTITION p11 VALUES LESS THAN (MAXVALUE) ); CREATE TABLE no_part_tab(c1 int default NULL, c2 varchar(30) default NULL, c3 date not null); ``` 步骤二:创建存储过程。 ```shell CREATE PROCEDURE load_part_tab() begin declare v int default 0; while v < 8000000 do insert into part_tab values (v,'testingpartitions',adddate('1995-01-01',(rand(v)*36520)mod 3652)); set v = v + 1; end while; end; //调用存储过程,插入数据 call load_part_tab(); //从 part_tab 导入数据到 no_part_tab insert into no_part_tab select * from part_tab; ``` 步骤三:执行查询速度比对 ```shell select count(*) from part_tab where c3 > date '1995-01-01' and c3 < date '1995-12-31'; 耗时:0.407s select count(*) from no_part_tab where c3 > date '1995-01-01'and c3 < date '1995-12-31'; 耗时:3.716s:3.716/0.407=9.13倍。 扫描次数对比: mysql> explain select count(*) from part_tab where c3 > date '1995-01-01'and c3 < date '1995-12-31'; +----+-------------+----------+------+---------------+------+---------+------+--------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+----------+------+---------------+------+---------+------+--------+-------------+ | 1 | SIMPLE | part_tab | ALL | NULL | NULL | NULL | NULL | 798458 | Using where | +----+-------------+----------+------+---------------+------+---------+------+--------+-------------+ 1 row in set (0.00 sec) mysql> explain select count(*) from no_part_tab where c3 > date '1995-01-01'and c3 < date '1995-12-31'; +----+-------------+-------------+------+---------------+------+---------+------+---------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------------+------+---------------+------+---------+------+---------+-------------+ | 1 | SIMPLE | no_part_tab | ALL | NULL | NULL | NULL | NULL | 8000000 | Using where | +----+-------------+-------------+------+---------------+------+---------+------+---------+-------------+ 1 row in set (0.00 sec) ``` 如上:普通表扫描了 8000000次, 分区表扫描了798458次。 分区表扫描比例是普通表的:798458/ 8000000 = 9.98%。 ## 七、分区适用场景 #### 7.1常见使用场景 1)当数据量很大(过T)时,肯定不能把数据再如到内存中,这样查询一个或一定范围的item是很耗时。另外一般这情况下,历史数据或不常访问的数据占很大部分,最新或热点数据占的比例不是很大。这时可以根据有些条件进行表分区。 2)分区表的更易管理,比如删除过去某一时间的历史数据,直接执行truncate,或者狠点drop整个分区,这比detele删除效率更高 3)当数据量很大,或者将来很大的,但单块磁盘的容量不够,或者想提升IO效率的时候,可以把没分区中的子分区挂载到不同的磁盘上。 4)使用分区表可避免某些特殊的瓶颈,例如Innodb的单个索引的互斥访问.. 5)单个分区表的备份很恢复会更有效率,在某些场景下 总结:可伸缩性,可管理性,提高数据库查询效率。 #### 7.2 业务场景举例 项目中需要动态新建、删除分区。如新闻表,按照时间维度中的月份对其分区,为了防止新闻表过大,只保留最近6个月的分区,同时预建后面3个月的分区,这个删除、预建分区的过程就是分区表的动态管理。 参考: http://blog.51yip.com/mysql/1029.html http://blog.51yip.com/mysql/949.html http://blog.51yip.com/mysql/1013.html http://blog.csdn.net/feihong247/article/details/7885199 http://www.wiquan.com/article/669 分区坑: http://www.simlinux.com/archives/133.html 创建路径: http://dev.mysql.com/doc/refman/5.7/en/create-table.html 5.6版本才支持: http://www.linuxidc.com/Linux/2014-01/95725.htm https://dev.mysql.com/doc/refman/5.6/en/tablespace-placing.html 作者:铭毅天下 原文地址: http://blog.csdn.net/laoyang360/article/details/52886987
标签:
mysql
,
分区
非特殊说明,本博所有文章均为博主原创。
如若转载,请注明出处:
http://www.i366211.com/archives/291/
上一篇
git常用命令
下一篇
docker用法
取消回复
评论啦~
提交评论
栏目分类
软件安装
10
开发工具
8
算法
2
测试
1
架构
3
填坑记
2
开源
6
科普
6
私域
2
读书笔记
4
编程
48
运营
3
管理
1
标签云
算法
C程序设计语言
C语言
Java
mysql
PHP
ffmpeg
golang
VueJs
脚手架
VueJs实战项目
Intellij IDEA
Centos7
Hyperf
抖音运营
杰克韦尔奇
跌荡一百年
生成海量测试数据
企业管理
习题2-3
习题2-4
习题2-6
异常分类
File
习题2-7
习题2-8
习题2-9
习题3-3
习题3-4
习题3-5
友情链接
申请
SaaS引擎
机器人框架
京东捡漏