mysql 表分区性能问题

最近同事说表数据量比较大，想使用分区来解决查询慢的问题。

        听到这个想法后，直观感觉是可行的。比如把100W数据按时间范围分到5个分区表里，每个分区表中才20万数据，如果我们根据日期去范围查询时，会被路由到部分分区中扫描数据，肯定比扫描全量数据100万要快。

但是，经过思考，感觉好像有点问题。

        分区表主要作用是根据分区字段来确定要查询的分区，以此来减少扫描记录数。但索引不也是干这个事儿的嘛？那如果我们在日期上（也是分区字段）加上索引，引擎会使用索引树查询还是用表分区查询呢。如果是按照索引去查询，那使用分区就没有意义，无法解决上面查询慢问题。

        然后我使用EXPLAIN 去查看执行计划，结果显示即使用部分分区（说明走了分区），又使用了日期字段上的索引。。。有点晕，莫非每一个分区都有一个独立的索引？

        如果是这样的话那先走分区后，然后再进行索引查询会提高查询速度吗？我的猜测是不会，因为100万数据量创建的索引树和20万创建的索引（即便是上千万）树的高度是一样的。如果使用索引查询的话两者查询耗时应该几乎相同。还有可能先走分区再走索引查询会更慢，因为多个分区内都要去索引树查询一遍，然后结果合并等处理，可能更加耗时。

百度没找到相关的说明。好吧，那就自己造数据来验证吧！

测试说明：

创建两张表 t_rannge_1 、 t_rannge_2   

        t_rannge_1    按birthday字段进行了分区并建立索引。

        t_rannge_2    未分区，在birthday字段建立索引

表结构如下：

       t_rannge_1

t_rannge_2

每张表插入5000W的数据

t_rannge_1

测试：

情景一

根据 birthday 和 name 组合查询：

注：两张表 name字段都未加索引，birthday有索引

查询耗时基本一样，结论： 在加索引的情况下区分并没有卵用。

注意： 这里如果只使用name条件检索会更快一些，因为当我们使用birthday 和 name 查询时会走birthday的二级索引，然后产生大量的回表查询，效率非常低，时间范围大的情况下，还不如全表扫描快。

情景二

根据birthday 查询

注：两张表birthday 都有索引

结论同上，耗时时间基本一样。分区表的还稍微慢一些，这里就不贴图了。

情景三

根据 birthday 和 name 组合查询：

注：两张表 birthday 都未加索引，name增加索引。 （注意：这里和情景一的不同是索引有变化）

结论： 查询耗时相同。这里就不贴图了。

总结：

      从上面的结论可以看出，海量数据下使用分区来提高查询效率是不可行的。因为会走索引查询，分区减少的数据量在索引树结构下显得微不足道。

那分区就没有一点用嘛？？思考了下，在某些场景下还是可以用的，例如，在表的数据量过于庞大时我们一般会采取历史数据迁移手段。将早期的数据迁移到另外一个库，对这些历史数据的查询效率要求不是特别高，所以我们可以对历史库的表进行分区，把每个分区的记录数控制在百万内，并将不同分区数据存到不同的磁盘中（表分区时可以指定数据目录，不同分区目录最好挂载到多个磁盘，并行读取）。表不需要建立任何的索引。每次查询都是在分区内全表扫描，只要数据量不特别多，查询还是挺快的。这种方案   相比中间件 + 分库分表要简单很多。