手机版
你好,游客 登录 注册
背景:
阅读新闻

MySQL转换分区表&索引重用

[日期:2015-08-07] 来源:Linux社区  作者:wangwenan6 [字体: ]

背景:生产环境下,大表数据量剧增,影响到了SQL的执行效率;业务越来越多,陆陆续续增加的索引并不是很合理,为了提高索引的使用率,需要把不必要的索引合并起来,减少索引的数量,提高索引的使用率

 方法:大表水平切分-->分区表转换;综合利用联合索引的特点,去掉一些多余的单列索引和一些重复的联合索引

 这篇博文的主要内容:
 转换分区表的方法:直接alter即可(http://www.linuxidc.com/Linux/2015-08/121268.htm
 分区表效率上的提升:一直以来好奇提升程度有多少这次顺便验证一下(*/ω\*);
 索引合并的策略;

-------------------------------------正文-------------------------------------

分区表效率上的提升,依然采用了sysbench-0.5来进行测试,
 虚拟机:
CPU:Intel(R) Xeon(R) CPU E5-2630 v3 @ 2.40GHz,逻辑核心8个

内存:32GB

硬盘:250G

采用五张表,每张表2000W数据,做两组对比:单表  vs  分区表(十个分区,每个分区200W数据)
 测试脚本需要注意的地方:跨分区的查询较少,大部分都是在一个分区内;读写混合,包含order by,count(*)等操作;所有查询均用到索引;
 测试时间两个小时,结果如下:
 总体延时对比(ms)

 QPS对比

 虽说基准测试的结果倾向于理想状况,不过在中高负载下,响应时间降低了超过30%还是挺吓人的Σ( ° △ °|||)︴)
 不过这也证明了,DB最大的瓶颈还是在IO~(顺序读最佳)

 索引合并的策略:
 这里简单写写~
MySQL本身有二级索引和merge_index的特性,这些留在以后再详细写(有生之年系列+1)
 生产环境的索引如图(为了效果就不打码了,领导看到了不要打我~_(:з」∠)_)

 箭头所指就是这次修改索引的目标,可以看到这三个索引分别是idx1<shop_id>, idx2<shop_id,pay_time>, idx3<order_status,shop_id,pay_time>
 MySQL的索引利用有如下几个特点:一张表只能用上一个索引(或者是merge_index);如果where条件中包含联合索引的前置列,那么联合索引也能利用起来

 比如说:有idx2存在的情况下,如果where条件只有shop_id,idx2也会被MySQL使用,同样的,where条件包含了shop_id,pay_time,还有其他列的(比如使用idx3的情况),也能用这个联合索引,
如果where条件中没有shop_id这个前置列的话,这个联合索引就不能被利用了~

注意:where条件只有shop_id的情况,使用idx2可能会比idx1要有更多的开销(联合索引体积更大),所以要权衡好“精简索引”和“列使用频率”孰轻孰重,做出正确的选择(当然绝大多数时候这种开销是可以忽略不计的)。

------------------------------------------------------------------------------------------------------------分区表相关的其他操作---------------------------------------------------------------------------------------------------------------
分区表的管理操作

删除分区:

alter table emp drop partition p1;

不可以删除hash或者key分区。

一次性删除多个分区,alter table emp drop partition p1,p2;

增加分区:

alter table emp add partition (partition p3 values less than (4000));

alter table empl add partition (partition p3 values in (40));

分解分区:

Reorganizepartition关键字可以对表的部分分区或全部分区进行修改,并且不会丢失数据。分解前后分区的整体范围应该一致。

alter table te

reorganize partition p1 into

(

partition p1 values less than (100),

partition p3 values less than (1000)

); ----不会丢失数据

合并分区:

Merge分区:把2个分区合并为一个。
alter table te

reorganize partition p1,p3 into

(partition p1 values less than (1000));

----不会丢失数据

重新定义hash分区表:

Alter table emp partition by hash(salary)partitions 7;

----不会丢失数据

重新定义range分区表:

Alter table emp partitionbyrange(salary)

(

partition p1 values less than (2000),

partition p2 values less than (4000)

); ----不会丢失数据

删除表的所有分区:

Alter table emp removepartitioning;--不会丢失数据

重建分区:

这和先删除保存在分区中的所有记录,然后重新插入它们,具有同样的效果。它可用于整理分区碎片。

ALTER TABLE emp rebuild partitionp1,p2;

优化分区:

如果从分区中删除了大量的行,或者对一个带有可变长度的行(也就是说,有VARCHAR,BLOB,或TEXT类型的列)作了许多修改,可以使用“ALTER TABLE ... OPTIMIZE PARTITION”来收回没有使用的空间,并整理分区数据文件的碎片。

ALTER TABLE emp optimize partition p1,p2;

分析分区:

读取并保存分区的键分布。

ALTER TABLE emp analyze partition p1,p2;

修补分区:

修补被破坏的分区。

ALTER TABLE emp repairpartition p1,p2;

检查分区:

可以使用几乎与对非分区表使用CHECK TABLE 相同的方式检查分区。

ALTER TABLE emp CHECK partition p1,p2;

这个命令可以告诉你表emp的分区p1,p2中的数据或索引是否已经被破坏。如果发生了这种情况,使用“ALTER TABLE ... REPAIR PARTITION”来修补该分区。

本文永久更新链接地址http://www.linuxidc.com/Linux/2015-08/121269.htm

linux
相关资讯       MySQL分区表  MySQL转换分区表 
本文评论   查看全部评论 (0)
表情: 表情 姓名: 字数

       

评论声明
  • 尊重网上道德,遵守中华人民共和国的各项有关法律法规
  • 承担一切因您的行为而直接或间接导致的民事或刑事法律责任
  • 本站管理人员有权保留或删除其管辖留言中的任意内容
  • 本站有权在网站内转载或引用您的评论
  • 参与本评论即表明您已经阅读并接受上述条款