ROP 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的索引和数据一起保存在表空间里。