何时使用哪种mysql分区模型 [英] When to use which mysql partitioning model
问题描述
好吧，刚开始是在mySQL中对一些表进行分区.有两种不同的描述方式，但是我找不到更实用的方法. -每种分区方式对哪种类型的数据效果最好?
Ok guys, just starting out with partitioning some tables in mySQL. There's a couple of different ways describing this, but what I cant find is a more practical approach. - Which type of data does each way of partitioning have the best effect on?
不是真的重要吗?
参考: http://dev.mysql.com/tech -resources/articles/performance-partitioning.html
推荐答案
这完全取决于您遇到的性能问题.
It depends exactly what kind of performance problem you're having.
MySQL 5.1的分区仅允许您对主键的第一位进行分区.这意味着，如果您想使用任何有用的分区形式，通常希望有一个复合主键.
MySQL 5.1's partitioning only allows you to partition the first bit of the primary key. This means that if you want to use any useful form of partitioning, it is often desirable to have a composite primary key.
最常见的用例之一是过期的旧数据，在未分区的情况下这可能会非常昂贵.在这种情况下，您必须使主键以日期/时间开头并对其进行分区.
One of the most common use-cases is for expiring old data, which can be very expensive in non-partitioned cases. In that case you'd have to make the primary key start with a date/time and partition on that.
然后，您可以通过编程删除旧分区来使行过期.
Then you can expire rows by programmatically dropping old partitions.
在其他情况下，常用查询可以从分区修剪中受益.
Other cases are where your common queries are able to benefit from partition pruning.
请记住，您无法通过分区解决所有性能问题；这不是魔术.对于无法从分区修剪中受益的查询，必须查询每个分区. MySQL不会并行执行此操作，因此它通常与使用未分区表一样慢.
Remember that you cannot fix every performance problem with partitioning; it is not magic. For queries which don't benefit from partition pruning, every partition must be queried. MySQL does not do this in parallel, so it is typically as slow as using a non-paritioned table.
- 准确确定您的性能问题
- 确定要进行多少改进才能修复它们
- 确定在其他用例中可以忍受的性能下降的数量
- 在各种不同设置下，在生产级硬件上再次测试，测试和测试您的生产规模数据
- 重复直到满意为止.
- 执行相关功能测试；释放！
这篇关于何时使用哪种mysql分区模型的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
