mysql中查询数字字符串很慢,数字列上的MySQL索引使查询速度变慢

我有一个相当大的表(~1.7M行)的优化问题.

选择行时会使用两列,我们称之为colA和colB.它们都是’double’类型(小数点后5位),范围从：

colA：-90~90

colB：-180~180

没有索引,任何形式的查询：

SELECT * FROM table where colA BETWEEEN a and b AND colB BETWEEN c and d

无论(a,b)和(c,d)的范围如何(因为MySQL必须检查每一行),运行大约相同的时间(~1秒).

如果我向colA和colB添加索引,则会发生两件事：查询(a,b)& (c,d)范围很小,例如：

SELECT * FROM table where colA BETWEEEN -4 and 4 AND colB BETWEEN 3 and 7

跑得很快(约1/10秒).但是,执行时间随查询值之间的范围而增加.例如：

SELECT * FROM table where colA BETWEEEN -80 and 80 AND colB BETWEEN -150 and 150

大约需要一分钟才能执行.

我知道B树如何用于字符串,但是当数据是数字并且使用范围进行查询时,我不确定机制.

如果有人可以建议如何优化此查询,我将不胜感激.一种想法是使用小范围的索引并告诉MySQL不要将它用于较大的范围,但是我找不到允许它的命令.

谢谢

编辑：解释

有些事我愚蠢地忘了提及.结果按rand()排序 – 我知道这是多么低效,但我没有办法从表中随机获取有限数量的行.

添加rand()不会影响没有索引时的执行时间,但会大大增加时间.

EDIT2：这是使用复合索引.

小范围：

“从表格中解释select *,其中colA介于35和38之间,colB介于-10和5之间,ORDER BY RAND()LIMIT 20”

9783行

没有指数(快)

+----+-------------+-------+------+---------------+------+---------+------+---------+-------------+

| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |

+----+-------------+-------+------+---------------+------+---------+------+---------+-------------+

| 1 | SIMPLE | table | ALL | NULL | NULL | NULL | NULL | 1673784 | Using where |

+----+-------------+-------+------+---------------+------+---------+------+---------+-------------+

WITH INDEX(非常快)

+----+-------------+-------+-------+---------------+------+---------+------+--------+-------------+

| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |

+----+-------------+-------+-------+---------------+------+---------+------+--------+-------------+

| 1 | SIMPLE | table | range | test | test | 18 | NULL | 136222 | Using where |

+----+-------------+-------+-------+---------------+------+---------+------+--------+-------------+

大范围：

“从表中解释select *,其中colA介于-80和80之间,colB介于-150和150之间,ORDER BY RAND()LIMIT 20;”

1631862行

没有指数(快)

+----+-------------+-------+------+---------------+------+---------+------+---------+-------------+

| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |

+----+-------------+-------+------+---------------+------+---------+------+---------+-------------+

| 1 | SIMPLE | table | ALL | NULL | NULL | NULL | NULL | 1673784 | Using where |

+----+-------------+-------+------+---------------+------+---------+------+---------+-------------+

带索引(非常慢：> 60秒)

+----+-------------+-------+------+---------------+------+---------+------+---------+-------------+

| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |

+----+-------------+-------+------+---------------+------+---------+------+---------+-------------+

| 1 | SIMPLE | table | ALL | test | NULL | NULL | NULL | 1673784 | Using where |

+----+-------------+-------+------+---------------+------+---------+------+---------+-------------+

EDIT3：

总结一下:(所有查询仅限返回20行)

rand()的大范围与索引：45秒

没有rand()的大范围,索引：0.003秒

范围大,没有指数：1秒

没有兰特的大范围,没有指数：0.003秒

异常是：“大范围与兰特()与指数,45秒”.

解决方法:

I know how B-trees works for strings, but I’m not sure of the mechanism when the data is numeric and the query is conducted using a range.

它们对数字的工作方式与对字符串的工作方式相同.

Without the index the query takes approximately the same time to run (~ 1 second), regardless of the range of (a,b), and (c,d)

全表扫描的运行时间不会随WHERE条件的内容而显着变化.索引访问路径所花费的时间与返回的行数成比例.如果查询选择表的重要部分,则使用索引总是比不使用索引慢.

索引访问路径仅在索引选择性足够时才有效,即检索的行数很小(有人说最多10％).执行时间将与返回的行数大致成比例,并且最终可能比全表扫描慢.

One thought is to use the index for small ranges and tell MySQL not to use it for larger ones, however I couldn’t find a command which allows this.

查询优化器必须使用统计信息和启发式方法来确定是否应使用索引.也许您需要使用OPTIMIZE TABLE更新这些统计信息.如果仍然无法做出正确的决定,您可以使用hints来帮助它.

SELECT * FROM table

IGNORE INDEX (the_index)

where colA BETWEEEN -80 and 80 AND colB BETWEEN -150 and 150

其他选项可能是删除索引(如果你从未看到它的任何好处,一个恒定的一秒响应时间可能就足够了),或者在两列上尝试复合索引(也只有当查询产生的记录数量是小).

现在您提到LIMIT 20,它开始变得更有意义：

big range with rand() with index: 45 seconds

NESTED LOOP有许多结果SORT

获取索引中的所有记录(在范围内),从表中逐个获取它们,然后排序,然后限制为20

big range without rand(), with index: 0.003 seconds

NESTED LOOP中止了20条记录

从索引中获取20条记录,从表中逐个获取它们并返回它们.没有排序,实际上没有大范围.

big range with rand, no index: 1 second

全表扫描排序

仔细阅读整个表格,保留范围内的内容,然后排序,然后限制为20

big range without rand, no index: 0.003 seconds

全表扫描,中止了20条记录

从表中开始阅读,保持在范围内,当你有20并且返回时停止.

标签：optimization,mysql,indexing

来源： https://codeday.me/bug/20190621/1256605.html