MySQL查询优化LIKE术语％ORDER BY int

问题描述：

我的问题是关于在使用前缀匹配时在VARCHAR上处理MySQL索引以及使用int COLUMN。
，例如如果我有这样的查询：

My question is regarding the handling of MySQL index on VARCHAR combined with an int COLUMN when using prefix matching. e.g. if I have such a query:

SELECT * FROM tbl WHERE name LIKE 'query%' ORDER BY weight DESC LIMIT 5

考虑到我有一个索引一个名称 - >权重，该索引是否需要查找前缀的所有外观查询然后是ORDER BY，或者即使使用前缀匹配（％），他也会保持交叉计算的索引。我很困扰它，因为对于流行的名字（例如查询=约翰），我可能会发现自己长时间搜索约翰的所有外表，这将使限制无用，并且查询变得缓慢，因为我正在处理使用大型数据集。

Considering I have one index one name->weight, does that index need to find all apperances of the prefix query and then ORDER BY, or does he keeps the cross calculation indexed even with the use of prefix matching (%). I'm troubled by it, because for popular names (e.g. query=john) I might find myself searching for a long time for all appearances of john, and that will make the limit useless and the query to become slow as I'm dealing with a large dataset.

答

如果'query'是长度等于或短于名称的索引前缀：

Provided that 'query' is of equal or shorter length than the indexed prefix of name:

A复合 BTREE 上的索引（名称，重量）将按 name $ c排序$ c>然后重量。从概念上讲：

A composite BTREE index on (name, weight) will be ordered by name then weight. Conceptually:


+---------+--------+---------+
| name(7) | weight | address |
+---------+--------+---------+
| queryaa |    500 | 0x1.... |
| queryaa |    500 | 0xe.... |
| queryaa |    498 | 0x8.... |
| queryaa |    491 | 0xb.... |
| queryaa |    486 | 0xc.... |
| queryaa |    430 | 0x3.... |
| queryab |    600 | 0x2.... |
| queryab |    592 | 0x7.... |
| queryab |    550 | 0x4.... |
| queryab |    321 | 0xa.... |
| queryab |    321 | 0x6.... |
| queryab |    304 | 0x9.... |
| queryab |    297 | 0x5.... |
| querybc |    800 | 0xd.... |
:         :        :         :

MySQL可以非常快速地遍历这样的索引，找到每个索引的前5个权重过滤器名称LIKE'查询％'定义的范围内的前缀（我不确定它是否执行此步骤，但如果不这样做，我会感到惊讶）：

MySQL can very quickly traverse such an index to find the top 5 weights for each indexed prefix within the range defined by the filter name LIKE 'query%' (I'm not certain that it does this step, but I'd be surprised if it did not):


+---------+--------+---------+
| name(7) | weight | address |
+---------+--------+---------+
| queryaa |    500 | 0x1.... |
| queryaa |    500 | 0xe.... |
| queryaa |    498 | 0x8.... |
| queryaa |    491 | 0xb.... |
| queryaa |    486 | 0xc.... |
| queryab |    600 | 0x2.... |
| queryab |    592 | 0x7.... |
| queryab |    550 | 0x4.... |
| queryab |    321 | 0xa.... |
| queryab |    321 | 0x6.... |
| querybc |    800 | 0xd.... |
:         :        :         :

此时，MySQL必须对结果执行文件排序：

At this point, MySQL must perform a filesort on the results:


+---------+--------+---------+
| name(7) | weight | address |
+---------+--------+---------+
| querybc |    800 | 0xd.... |
| queryab |    600 | 0x2.... |
| queryab |    592 | 0x7.... |
| queryab |    550 | 0x4.... |
| queryaa |    500 | 0x1.... |
| queryaa |    500 | 0xe.... |
| queryaa |    498 | 0x8.... |
| queryaa |    491 | 0xb.... |
| queryaa |    486 | 0xc.... |
| queryab |    321 | 0xa.... |
| queryab |    321 | 0x6.... |
:         :        :         :

只有这样才能使用前5个结果从中获取相关记录表：

And only then can it use the top 5 results to fetch the associated records from the table:


+---------+--------+---------+
| name(7) | weight | address |
+---------+--------+---------+
| querybc |    800 | 0xd.... |  --> fetch from table
| queryab |    600 | 0x2.... |  --> fetch from table
| queryab |    592 | 0x7.... |  --> fetch from table
| queryab |    550 | 0x4.... |  --> fetch from table
| queryaa |    500 | 0x1.... |  --> fetch from table
+---------+--------+---------+

如果'query' 更长在 name 的索引前缀之后，MySQL必须在上面的步骤1中对表进行查找，以便充分过滤随后订购的记录。

If 'query' is longer than the indexed prefix of name, then MySQL must perform lookups into the table in step 1 above in order to adequately filter the records which are subsequently ordered.

MySQL查询优化LIKE术语％ORDER BY int

相关推荐