MYSQL 8 分区表 靠谱吗 ？ 2 细致性能分析 及业务场景应用

接上期，这边2个 1000万的表people people_1, 与一个range 的分区表people_range 1000万左右的数据表，分别进行JOIN 的运算

select * from people as p1 inner join people_1 as p2 on p1.id = p2.id where p1.age = 30;

select * from people_range as p1 inner join people_1 as p2 on p1.id = p2.id where p1.age = 30;

select * from people as p1 inner join people_range as p2 on p1.id = p2.id and p2.age = 30;

同样的语句，在操作中我们可以看到异同点，现在来浅层次的总结

1 两张1000万的大表进行普通条件的查询并存在大表之间JOIN 的情况下性能在5.77 秒完成，最耗费时间的操作在executing 操作中

2 两张1000万的大表在分区表作为驱动表的情况下，操作时间在38秒，相对于上面的操作慢了8倍左右

3 同样还是1000万的大表，分区表在后的操作中，操作的时间变为了4.79秒。

这里有些同学会提出疑问是不是缓存起作用了，这里为了测试的严谨性，每次执行语句均对MYSQL数据库进行了重新启动,所以测试的结果真实有效。

所以分区表使用还是的具体看业务的使用方式，在来决定是否使用分区表，上面的测试中，如果经常需要对分区表全局与其他表做JOIN的运算，那么分区表无论是放到前面还是后面，性能对比实际上与两个单表之间进行整体查询都还是有差异的。

所以对于分区表的操作必然要针对分区中单独的分区进行操作，性能的优势才能体现。下面就的讨论讨论业务的场景了， 分区表主要分为几种类型， list , range ,hash 等等， 在MYSQL中针对与业务来说，分区主要针对以上几种分区类型来说使用分区表的业务场景有哪些。

1 list 分区方式，表承载量大，并查询时这对某一类的数据进行查询，如业务中的表明确有地域性质的，或业务客户分类性质的，可以将数据进行分类并提供分区字段的都可以使用 list 的方式来进行数据的分割。

此类业务场景分区后的表主要有以下几个特性

1.1 分区后的数据分布不均匀，部分表通过分区键+其他条件查询查询速度快，部分表在分区后，由于业务特性和数据分布，对于查询的速度并无提高。

1.2 分区后，需要对分区后的数据进行整体的分析，性能降低，尤其对于表与其他表的JOIN 操作,建议分区表放置在查询中的非驱动表位置。

2 Range 分区类型，range 的分区类型主要还是以时间的方式进行数据的分割，实际上与list 有类似的部分，但不同的是分区后的数据的倾斜性的问题可能比LIST的方式的分区比较不明显。 查询时随着时间值的范围扩大，查询数据的性能会呈现递减的状态。

3 Hash 分区类型，此种类型在MYSQL中的使用应该尽量的减少，实际上hash分区的查询如因为整体的表数据量多而引起，应该考虑的解决方案可能更适应于，冷热数据分离，MYSQL 数据库分布式中间件，乃至分布式数据库。

对于MYSQL 8 的分区操作中，还可以通过指定分区的位置，降低查询条件跨多分区遍历的问题。

如：下表

需要查询数据大于9500000，下面分别

采用分区表定位的方式，分区表非定位方式，以及全表查询得方式并且我们采用两次查询的方式避免第一次第一个查询由于数据不再缓存中而测试不公平的情况。

上面的图可以清晰的看到第一次信号线中，的确指定

通过上面的查询可以通过人为的手段快速定位到p17分区并搜寻到指定的数据，也就是通过人为指向P17分区，在利用local index 的方式查询数据。

通过打开profile 注意 5 6 7 query 的跟踪，可以看到，

1 在1000万数据的单表，分区表，分区表指定分区查询，这三种查询中第一次查询中排名：

单表的查询Duration NO.1

指定分区查询Duration NO.2

不指定分区查询Duration NO.3

2 在第二次的查询中，查询的排名中

指定分区查询Duration NO.1

单表查询的Duration NO.2

不指定分区查询Duration NO.3

得到的结果中，不指定分区的分区在主键查询的性能是最差，单表的查询性能是稳定的，如果在指定分区查询比较不指定分区查询的性能有很大的提升。

这里分析整体的插件主要在open tables 的消耗，并且也要注意statistics 和freeing items.

我们看看着三个到底做什么

open tables 在MYSQL 中操作表是对于操作来说，本身表也是文件，那么打开文件，通过LINUX 获得句柄，这就是open tables 需要做的事情。

statistics 是在查询中对于统计信息的收集，如果时间比较长的情况下，就的考虑此事正在进行statistics的收集了

freeing items 主要看上一个命令在做什么，closing tables, 所以针对非指定分区的分区表查询时最慢的。

在第二次操作中，消耗最大的还是上面的三个项目，这里为什么第二次的查询指定分区查询的效率高了

原因

1 statistics 的统计信息已经稳定了，上次的劣势已经消除

2 指定分区查询中最消耗的 freeing items 指定分区查询比其他的不指定分区和单表查询打开的表要小，就节省了时间。

经过查询的分析，这里建议在MYSQL使用分区表，如果使用hash的分区的方式进行查询，这并不是一个好主意，性能在一定量级上不及单表，而数据量大了，应该就采用其他的方案来解决了。而如果是range 或者 list 的方式来进行查询，在指定分区的基础上，这的确是一个好主意。可以尝试使用。

纵观，MYSQL中使用分区表本身使用者就少并且这样的解决方案还是需要积累更多的实际的经验。