Question

sql分区后如何提高查询效率？

我现在把数据库Demo 里一张表 tbl 根据tm(datetime)字段 按年份分了4个区，并把4个分区文件放在了不同的磁盘里，第一个分区里储存的是2008年的有300万行数据，第二，三，四个分区分别是09年，10年，11年的数据各有100万行，
然后我新建了一张表tbl1没有分区。数据和表tbl里的数据相同。
现在我要查询两年的所有数据
select * from tbl where tm between '2009-01-01' and '2010-01-01'
select * from tbl1 where tm between '2009-01-01' and '2010-01-01'
这两句查询出来的都是200万行数据 耗时也相同
那就没有体现出分区可以提高效率啊~
或者是我的sql语句有问题？又或者是别的原因？ 求解~

Answer 1

你可能没有理解分区的目的。
如果只是纯粹的为了 分区而分区。那就没什么意义了。

首先。看了一下你的分区方式，总体上是没太大问题的。

如果不分区
那么当执行
SELECT * FROM tbl1 WHERE tbl = '2009-12-31'
的时候。
数据库需要从一个很大的索引里面去检索数据 （包含08年--11年 600W行）

如果分区了。
那么当执行
SELECT * FROM tbl WHERE tbl = '2009-12-31'
的时候。
数据库仅仅需要从一个较小的索引里面去检索数据 （09年 100W行）

举个简单的例子来说，也就是：
如果不分区，好比大海捞针的话。
那么分区了，好比从某条河里面捞针。

注意：如果查询的条件，不包含分区条件。
就好比要从河里面捞针，但是具体哪条河不知道，要每一条河都去捞一遍
（这就是 “如果跨区反而更慢 ” ）

=================

下面再来看看 你的查询两年的数据的 SQL。

select * from tbl where tm between '2009-01-01' and '2010-01-01'
select * from tbl1 where tm between '2009-01-01' and '2010-01-01'

你这2个SQL，基本上数据库在分析完毕以后，要看索引的类型。
理论上是不使用非聚集索引的。
如果有聚集索引，那么采用聚集索引，没有的话，就直接全表扫描的。

对于分区的表
数据库顶多可以分析到，本次检索，可以不去检索 08年的分区与 11年的分区。
但是要去全部检索 09年的分区 与 10年的分区。

对于未分区的表
前面已说明，具体查询策略取决于索引类型。

追问

那为什么 即便我只查一个分区的 效率也不会提高呢？
例如：
select * from tbl where tm between '2009-01-01' and '2009-12-01'
select * from tbl1 where tm between '2009-01-01' and '2009-12-01'
这样耗时还是一样的
tm 就是分区条件 这样只从09年的分区里找了吧？ 但是为什么耗时还是一样的呢？
我没做索引~ 是要在tm上做索引么？

追答

前面不是说了么？ 检索全部的话，非聚集索引用不上的。
细节信息可参考下贴：
http://hi.baidu.com/wangzhiqing999/blog/item/ea57b6932325457854fb9634.html

你先对比一下， 查询 1 天的数据， 看看时间上面有差别么？

因为你这个情况，100W行数据，花在检索上面的时间也不多。搞不好大量的时间，花费在磁盘I/O 与 传输上面。

举个例子嘛。
好比现在有2家快递公司。
一家分区了，检索货物速度很快1小时内处理完。
一家没分区，检索货物速度稍慢，要1小时15分钟内处理完。

然后你向这2家快递公司，各发了一个货物，要求从 广东 发送到 西藏。
然后你接受到货物的时候， 有疑问，怎么接受到的时间，都一样的呢？
不是说 分区 的查询速度快么？

Answer 2

把tm设置成聚集索引，不用分区。

Answer 3

小数据而已啦
看不出时间上明显的差别

Answer 4

如果跨区反而更慢