一道MySQL优化查询的面试题

Question

前几天面试碰到一个数据库（MySQL）优化查询的问题：

说一张表里有1千万条数据，有一个字段status有两个值（1待审核、2审核通过），然后呢有两个列表即待审核列表与审核通过的列表，那么如何优化查询SQL使其列表的查询速度达到最快？

我没答上来，其实我本来想说给status字段加索引，但细想由于它的值重复性太多，即使加了索引效果也不明显，所以我不知道如何去优化这样的查询。

Answer 1

1.primary(id,stauts)
2.更直接的是用redis的bitmap只有0和1刚好对应两个状态。

Answer 2

用redis的bitmap应该适合这个场景

Answer 3

看到这个问题后，我也迟疑了一会，针对如此大的数据量，问题的关键在于你如何去获取数据，总不可能一次获取获取全部吧！所以这里的优化重点在于业务方，尽量的压缩查询返回数据，只要查询的数据量小（如果业务不复杂，数据库不身是不适合处理复杂计算的），查询性能才能更高，至于楼上回答的分表／分区，个人感觉还是要看业务，综合来看，总不能说审核通过的记录从这个表在移动到另外一个表的，另外如果有需要获取全部的需求，那岂不是要连表查询，性能更低。
至于如何让查询的数据量小，有很多做法，比如结合缓存(redis-nosql),保证mysql只处理我们需要的数据或者只是保存数据，往往业务的性能瓶颈并不在数据库，所以不要把压力放到数据库这里

Answer 4

这其实得看到底考的是啥，莫名其妙的

正常的做法加索引，但这种索引只有一个status的条件，显然是没有什么意义的。

只是用db搜索的话，我觉得可以用分区分表的方式解决这个问题，待审核和审核过的分表处理，2个列表查询全拿就好了。

Answer 5

可以考虑加字段，比如添加时间ctime,where的时候加上时间。

补充下：force index 或许可以

Answer 6

不知所云，

说一张表里有1千万条数据，有一个字段status有两个值（1待审核、2审核通过），然后呢有两个列表即待审核列表与审核通过的列表，那么如何优化查询SQL使其列表的查询速度达到最快？

查什么？怎么查？都没说清楚怎么优化么？待审核表与审核通过表又是啥（视图么？ 表的话怎么关联的）？

我想的是，既然题中只有两个状态，为什么不分表？

题外：我自己做的审批系统内是这样处理的，所有发生的事件都会把(id,event_id,status,desc,url,time) 这些东西，单独弄个内存表（cache），status符合一定条件或者超时，就从这个表里删除（按时间顺序归档）了，所以不可能出现那么大的表。

Answer 7

撇开这道题，大部分情况下我们只需要用到前几页，我觉得缓存一个id范围，查询的时候带上即可！

Answer 8

只有更快，没有最快，这里说的是优化SQL，没说让你动表结构

Answer 9

我记得上学学哲学的时候就说是一切是相对的，没有绝对论，所以这里的“最快”不是绝对的

Answer 10

给status加哈希索引，sql语句只能是where status = 1，外加limit了

Answer 11

mysql 没想到好的答案。。在一个表中，加索引重复太高了。。。。请高人指点吧。。。

一般都是把这状态放到redis的list中。。。。

Answer 12

如果是myisam表的话，是不是可以考虑组合索引？id和status作为主键。

Answer 13

使用SQL的查询缓存试试？

Answer 14

分表，如将其他字段根据尾数进行分表，这样查询的时候根据其尾数决定去哪个表查询。

不过这有一个前提就是分表的字段值分布够均匀，这样前面的分表方式就可以将记录均分到各个表。

Answer 15

1.分表
2.我隐约记得以前看过一本书 上面写这种字段要定义成SET.然后加上索引, 可以改善存储性能和查询速度.不过我自己没有亲自试验, 所以只是一个建议.

Answer 16

状态分表，条目不物理删除，查询时分页与否都只用id查询。

Answer 17

妥妥的分表啊！

Answer 18

楼主的意思应该是，将有1千万数据的原始表，按照status字段统计到审核表和待审核两个表中吧。
如果是这样，其实sql是没太大的优化余地， 就select a, b from xxxtb where status = 0. 就像你说的，status索引用了和没用差不多了，所以，我认为sql优化，没啥好优化的。
其他思路提供：
1、分表统计
2、利用计划任务，用程序统计。

Answer 19

不是必要，基本不考虑分表，千万级别的数据分表所带来的操作逻辑复杂程度远大于提升的效率，对于这种数据查询一般的解决方案分批次处理数据，比如按时间或性别等一部分一部分处理，建好适当的主键，其次就是减少不必要的返回字段

Answer 20

我有一个思路，就是分表，再加两张表，一张status_1一张status_2，两张表存储主表的id，然后直接做关联查询，这样理论上效率要高一点吧。有高手可以帮忙测试一下

Answer 21

提升颗粒度，是不是可以试试？
现在是一次申请一条数据，代码中增加逻辑，同步增加一个小时、一天、一周、一月等等颗粒度的的统计记录。
后续查询根据需求的时间颗粒读取查对应统计表。
现有数据记录下最大的表id，记为a，为这部分数据增加各颗粒度统计表的记录，处理完后，再处理记录从a到现有最新的数据。