MySQL分区表的正确使用方法
mysql分区表概述
我们经常遇到一张表里面保存了上亿甚至过十亿的记录，这些表里面保存了大量的历史记录。 对于这些历史数据的清理是一个非常头疼事情，由于所有的数据都一个普通的表里。所以只能是启用一个或多个带where条件的delete语句去删除（一般where条件是时间）。 这对数据库的造成了很大压力。即使我们把这些删除了，但底层的数据文件并没有变小。面对这类问题，最有效的方法就是在使用分区表。最常见的分区方法就是按照时间进行分区。
分区一个最大的优点就是可以非常高效的进行历史数据的清理。
1. 确认mysql服务器是否支持分区表
命令：
show plugins;
2. mysql分区表的特点
在逻辑上为一个表，在物理上存储在多个文件中
hash分区（hash）
hash分区的特点
- 根据mod(分区键，分区数)的值把数据行存储到表的不同分区中
- 数据可以平均的分布在各个分区中
- hash分区的键值必须是一个int类型的值，或是通过函数可以转为int类型
如何建立hash分区表
以int类型字段 customer_id为分区键
create table `customer_login_log` ( `customer_id` int(10) unsigned not null comment '登录用户id', `login_time` timestamp not null default current_timestamp on update current_timestamp comment '用户登录时间', `login_ip` int(10) unsigned not null comment '登录ip', `login_type` tinyint(4) not null comment '登录类型:0未成功 1成功' ) engine=innodb default charset=utf8 comment='用户登录日志表' partition by hash(customer_id) partitions 4;
以非int类型字段 login_time 为分区键(需要先转换成int类型)
create table `customer_login_log` ( `customer_id` int(10) unsigned not null comment '登录用户id', `login_time` timestamp not null default current_timestamp on update current_timestamp comment '用户登录时间', `login_ip` int(10) unsigned not null comment '登录ip', `login_type` tinyint(4) not null comment '登录类型:0未成功 1成功' ) engine=innodb default charset=utf8 comment='用户登录日志表' partition by hash(unix_timestamp(login_time)) partitions 4;
customer_login_log 表如果不分区，在物理磁盘上文件为
customer_login_log.frm # 存储表原数据信息 customer_login_log.ibd # innodb数据文件
如果按上面的建hash分区表，则有五个文件
customer_login_log.frm customer_login_log#p#p0.ibd customer_login_log#p#p1.ibd customer_login_log#p#p2.ibd customer_login_log#p#p3.ibd
演示
使用起来和不分区是一样的，看起来只有一个数据库，其实有多个分区文件，比如我们要插入一条数据，不需要指定分区，mysql会自动帮我们处理
查询
范围分区（range）
range分区特点
- 根据分区键值的范围把数据行存储到表的不同分区中
- 多个分区的范围要连续，但是不能重叠
- 默认情况下使用values less than属性，即每个分区不包括指定的那个值
如何建立range分区
如果没有定义p3分区，当插入的customer_id大于29999时会报错，定义了则超过的数据都存入p3中
range分区的适用场景
- 分区键为日期或是时间类型 （可以使得各个分区表的数据比较均衡，如果按上面的例子中以整型id为分区键，假如活跃用户集中在10000-19999之间，则p1中的数据量就会比其他分区的数据量大很多，这就失去了分区的意义；而且按时间类型分区，如果要按时间顺序进行数据的归档，则只需要对某一个分区进行归档就可以了）
- 所有查询中都包括分区键（避免跨分区查询）
- 定期按分区范围清理历史数据
list分区
list分区的特点
- 按分区键取值的列表进行分区
- 同范围分区一样，各分区的列表值不能重复
- 每一行数据必须能找到对应的分区列表，否则数据插入失败
如何建立list分区
如果插入一条login_type为10的数据行，则会报错
3. 如何为登录日志表（customer_login_log）分区
业务场景
- 用户每次登录都会记录customer_login_log日志
- 用户登录日志保存一年，1年后可以删除或者归档
登录日志表的分区类型及分区键
- 使用range分区
- 以login_time为分区键
分区后的用户登录日志表
按年份分区存储，所以用year函数进行了转化
create table `customer_login_log` ( `customer_id` int(10) unsigned not null comment '登录用户id', `login_time` datetime not null comment '用户登录时间', `login_ip` int(10) unsigned not null comment '登录ip', `login_type` tinyint(4) not null comment '登录类型:0未成功 1成功' ) engine=innodb partition by range (year(login_time))( partition p0 values less than (2017), partition p1 values less than (2018), partition p2 values less than (2019) )
插入并查询数据
查询指定表中的分区数据情况
select table_name,partition_name,partition_description,table_rows from information_schema.`partitions` where table_name = 'customer_login_log';
再插入2条18年的日志，会存入p2表中
之前说过建立分区表时，最好建立一个maxvalue的分区，这里之所以没有建立，是为了数据维护的方便，如果我们建立了maxvalue分区，很容易忽视一个问题，当我们2019年有的数据插入时，会自动存入那个maxvalue分区中，之后在做数据维护时会不方便，所以没有建立maxvalue分区
而是通过计划任务的方式，在每年年底的时候增加这个分区，比如我们现在在2018年年底，我们需要在日志表中为2019年建立日志分区，否则2019年的日志都会插入失败
我们可以通过下面语句
增加分区
alter table customer_login_log add partition (partition p3 values less than(2020))
增加分区，并插入数据
删除分区
假如我们现在要删除2016年到2017年间一年的数据，因为我们已经做了分区，所以只需要通过一条语句，删除p0分区即可
alter table customer_login_log drop partition p0;
可以发现p0分区已被删除，且2016年的日志全部被清除了
归档分区历史数据
我们可能有另一种需求对数据进行归档
mysql版本>=5.7，归档分区历史数据非常方便，提供了一个交换分区的方法
分区数据归档迁移条件：
- mysql>=5.7
- 结构相同
- 归档到的数据表一定要是非分区表
- 非临时表；不能有外键约束
- 归档引擎要是：archive
建表并交换分区
create table `arch_customer_login_log` ( `customer_id` int unsigned not null comment '登录用户id', `login_time` datetime not null comment '用户登录时间', `login_ip` int unsigned not null comment '登录ip', `login_type` tinyint not null comment '登录类型:0未成功 1成功' ) engine=innodb ; alter table customer_login_log exchange partition p1 with table arch_customer_login_log;
可以发现，原customer_login_log表中的2017年的数据（p1分区中的数据）已转移到了arch_customer_login_log表中，但是p1分区未删除，只是数据转移了,所以我们还需要执行drop命令删除分区，以免有数据插入其中
将归档数据的存储引擎改为归档引擎
最后我们将归档数据的存储引擎改为归档引擎，命令为
alter table customer_login_log engine=archive;
使用归档引擎的好处是：它比innodb所占用的空间更少，但是归档引擎只能进行查询操作，不能进行写操作
4. 使用分区表的主要事项
- 结合业务场景选择分区键，避免跨分区查询
- 对分区表进行查询最好在where从句中包含分区键
- 具有主键或唯一索引的表，主键或唯一索引必须是分区键的一部分（这也是为什么我们上面分区时去掉了主键登录日志id(login_id)的原因，不然就无法按照上面的按年份进行分区，所以分区表其实更适合在myisam引擎中）
关于myisam和innodb的索引区别
1.关于自动增长
myisam引擎的自动增长列必须是索引，如果是组合索引，自动增长可以不是第一列，他可以根据前面几列进行排序后递增。
innodb引擎的自动增长咧必须是索引，如果是组合索引也必须是组合索引的第一列。
2.关于主键
myisam允许没有任何索引和主键的表存在，
myisam的索引都是保存行的地址。
innodb引擎如果没有设定主键或者非空唯一索引，就会自动生成一个6字节的主键(用户不可见)
innodb的数据是主索引的一部分，附加索引保存的是主索引的值。
3.关于count()函数
myisam保存有表的总行数，如果select count(*) from table;会直接取出出该值
innodb没有保存表的总行数，如果使用select count(*) from table；就会遍历整个表，消耗相当大，但是在加了wehre 条件后，myisam和innodb处理的方式都一样。
4.全文索引
myisam支持 fulltext类型的全文索引
innodb不支持fulltext类型的全文索引，但是innodb可以使用sphinx插件支持全文索引，并且效果更好。（sphinx 是一个开源软件，提供多种语言的api接口，可以优化mysql的各种查询）
5.delete from table
使用这条命令时，innodb不会从新建立表，而是一条一条的删除数据，在innodb上如果要清空保存有大量数据的表，最 好不要使用这个命令。(推荐使用truncate table，不过需要用户有drop此表的权限)
6.索引保存位置
myisam的索引以表名+.myi文件分别保存。
innodb的索引和数据一起保存在表空间里。
总结
以上就是这篇文章的全部内容了，希望本文的内容对大家的学习或者工作具有一定的参考学习价值，如果有疑问大家可以留言交流，谢谢大家对的支持。