我有一个有550000条记录的表
SELECT * FROM logs WHERE user = 'user1' ORDER BY date DESC LIMIT 0, 25
该查询需要0.0171秒。如果没有LIMIT,则有3537个结果
SELECT * FROM logs WHERE user = 'user2' ORDER BY date DESC LIMIT 0, 25
该查询需要3.0868秒。如果没有LIMIT,则有13个结果
表键为:
PRIMARY KEY (`id`),
KEY `date` (`date`)
当使用"LIMIT 0,25"时,如果记录少于25,则查询速度会减慢。我该如何解决这个问题?
使用limit 25
可以使查询在找到25行时停止。
如果550000行中有3537行匹配,则假设分布相等,在检查按date
(date
上的索引(排序的列表或根本没有排序的列表中的550.000/3537*25 rows = 3887 rows
后,平均会找到25行。
如果在550000行中有13行匹配,limit 25
将必须检查所有550000行(这是行数的141倍(,因此我们期望0.0171 sec * 141 = 2.4s
。显然还有其他因素决定了运行时间,但数量级是合适的。
还有一个额外的影响。不幸的是,date
的索引不包含user
的值,因此MySQL必须在原始表中来回跳转来查找该值(因为数据本身是按主键排序的(。这比直接读取无序表要慢。
所以实际上,如果有很多行要读,那么根本不使用索引可能比使用索引更快。您可以通过使用例如FROM logs IGNORE INDEX (date)
来强制MySQL不使用它,但这将产生这样的效果,即它现在必须在任何情况下读取整个表:最后一行可能是最新的,因此必须在结果集中,因为您是按date
排序的。因此,它可能会减慢您的第一个查询速度——快速读取整个550000行可能比来回跳跃缓慢读取3887行慢。(MySQL事先也不知道这一点,所以它做出了选择——第二个查询显然是错误的(。
那么如何获得更快的结果呢?
有一个按user
排序的索引。然后,对'user2'
的查询可以在13行之后停止,因为它知道没有更多的行了。现在,这将比'user1'
的查询更快,后者必须查找3537行,然后按date
排序。
因此,查询的最佳索引是user, date
,因为它知道何时停止查找更多的行,并且列表已经按照您想要的方式排序(在所有情况下都超过0.0171(。
索引也需要一些资源(例如,更新表时更新索引的hdd空间和时间(,因此为每个查询添加完美的索引有时可能会对整个系统产生反作用。