首页 / MYSQL / MySQL转换分区表索引重用
MySQL转换分区表索引重用
内容导读
互联网集市收集整理的这篇技术教程文章主要介绍了MySQL转换分区表索引重用，小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含4246字，纯文字阅读大概需要7分钟。
内容图文
生产环境下，大表数据量剧增，影响到了SQL的执行效率；业务越来越多，陆陆续续增加的索引并不是很合理，为了提高索引的使用率，需
背景：生产环境下，大表数据量剧增，影响到了SQL的执行效率；业务越来越多，陆陆续续增加的索引并不是很合理，为了提高索引的使用率，需要把不必要的索引合并起来，减少索引的数量，提高索引的使用率
方法：大表水平切分-->分区表转换；综合利用联合索引的特点，去掉一些多余的单列索引和一些重复的联合索引
这篇博文的主要内容：
转换分区表的方法：直接alter即可（；
分区表效率上的提升：一直以来好奇提升程度有多少这次顺便验证一下(*/ω＼*)；
索引合并的策略；
-------------------------------------正文-------------------------------------
分区表效率上的提升，依然采用了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
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”来修补该分区。
本文永久更新链接地址：
内容总结
以上是互联网集市为您收集整理的MySQL转换分区表索引重用全部内容，希望文章能够帮你解决MySQL转换分区表索引重用所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错，欢迎将互联网集市网站推荐给程序员好友。
内容备注
版权声明：本文内容由互联网用户自发贡献，该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 gblab@vip.qq.com 举报，一经查实，本站将立刻删除。
内容手机端
扫描二维码推送至手机访问。