22年第14篇 mysql表分区对查询性能提升到底有多大帮助？讲一个反例
最近，看密集型数据库应用设计，里面有关分区的存储设计。
如果你对某一张业务表数据量增长过快感到担忧，一开始就可以设计成分区表，这样是最好的。
另外，当表中的数据量不断增大，查询数据的速度就会变慢，应用程序的性能就会下降，这时就应该考虑对表进行分区。表进行分区后，逻辑上表仍然是一张完整的表，只是将表中的数据在物理上存放到多个表空间(物理文件上)，这样查询数据时，不至于每次都扫描整张表。
总结一下分区表的优点：
1、改善查询性能：对分区对象的查询可以仅搜索自己关心的分区，提高检索速度。
2、增强可用性：如果表的某个分区出现故障，表在其他分区的数据仍然可用；
3、维护方便：如果表的某个分区出现故障，需要修复数据，只修复该分区即可；
4、均衡I/O：可以把不同的分区映射到磁盘以平衡I/O，改善整个系统性能。
缺点是什么？
对分区表设计者能力有一定要求，如果分区数据不能比较均衡，很有可能性能并没有明显提升。
我现在建一张简单的表试试看。 因为要验证分区对查询性能有多大提升，所以非主键字段没有加索引。
create database test;
use test;
CREATE TABLE `user` (
`id` bigint NOT NULL AUTO_INCREMENT,
`name` varchar(256) COLLATE utf8mb4_general_ci NOT NULL,
`phone` varchar(32) COLLATE utf8mb4_general_ci NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
给它插入2000万条数据；
，试试性能:
1, select count(1) from user;
08:13:14 select count(1) from user LIMIT 0, 10001 row(s) returned 9.532 sec / 0.000 sec
2. select * from user where name = 'fjkutlcehgni' and phone = '3165297804'
08:19:50select * from user where name = 'fjkutlcehgni' and phone = '3165297804' LIMIT 0, 1000 0 row(s) returned10.110 sec / 0.000 sec
对该表做分区，以id值做hash分区，分16个partion，原则上每个partion数据量不超过500w；
alter table user PARTITION BY HASH(id) partitions 16;
再看查询时间：
08:29:33select count(1) from user LIMIT 0, 10001 row(s) returned15.438 sec / 0.000 sec
08:31:10select * from user where name = 'fjkutlcehgni' and phone = '3165297804' LIMIT 0, 10000 row(s) returned 28.172 sec / 0.000 sec
翻车了？
查询性能越来越差了？
所以这种分区是不对的。
贴几个dba的分析：
先纠正一个误区，分区一定会提高查询性能，某种程度来说，是错的。。1000个分区并不是1000个并发，而是一个，单块盘io就那么大，分区真正想提升，分区放置在不同的盘符。。更主要的，如果没有用到分区键，大部分情况，就是会变慢。。需要增加判断，索引树也被切割，变慢正常。。只有在用到分区键的时候，才能勉强提升速度。。
分区表是一种粗粒度,简易的索引策略,适用于大数据的过滤场景.最适合的场景是,没有合适的索引时,对其中几个分区表进行全表扫描.或者只有一个分区表和索引是热点,而且这个分区和索引能够全部存储在内存中。限制单表分区数不要超过150个,并且注意某些导致无法做分区过滤的细节,分区表对于单条记录的查询没有优势。
只是从某一个分区中查询数据，看起来好像速度会更快，但实际上你会发现速度提高几乎没有，有时候甚至是更慢，为什么呢？因为采用分区后，提高的性能还没有由于采用分区而带来的开销大，所以反而分区会更慢，关于分区带来的开销，主要是更加sql的where条件，来判断你的sql到底是会从哪个分区中查询数据的开销。
首先你要明白分区的特点是什么，然后再去根据实际情况来运用：1. 提高可用性。某个分区损坏，不会导致整个表不能进行select、 2、提高可维护性。你可以对单个分区进行维护，而不会影响其他分区。特别是在加载数据，删除数据的时候。3、提高IO的并发性。原来是1个硬盘，现在如果你有10个分区，分别放到10个物理硬盘上，那么当你查询多个分区时，可以同时启动多个物理硬盘，极大的提高查询速度。如果你还是单个硬盘，就没什么效果。4、分区消除。就是加快sql的查询速度，但只限于查询1、2个分区的时候，如果你要查 所有分区，速度会比单个表更慢，当然如果是由N个硬盘，速度还是会快的。另外，分区也会降低性能，比如每个语句要先解析判断查询条件是针对那个分区，这个需要读取系统表的元数据，当你有大量并发请求时，整个系统表就成了瓶颈，大家都等待。
nnoDB引擎是聚簇索引加上非聚集索引。第一个是因为磁盘IO问题吗？1000个分区类似1000个并发，IO卡住了？分区之后，你如果是求范围值，会在不同的分区里查找，所以比单个表慢，如果你的数值在一个分区里，就会快了 第二个呢？分区就会有这个问题吗？因为公司ID应该是主键是聚簇索引，所以会快点，如果没有使用主键索引则mysql会在找到了这个值所在的主键值然后重新走一遍聚簇索引来获得数值，再加上分区就又要查找几个分区数据。所以更慢。