在MySQL中为BINARY LIKE操作建立索引 [英] Indexing for BINARY LIKE operations in MySQL

问题描述

我知道Postgresql中存在varchar_pattern_ops以便在LIKE查询中进行基于索引的快速搜索，但是MySQL是否有类似的功能?

I know that varchar_pattern_ops exists in Postgresql for fast, index-based searches in a LIKE query, but is there any similar functionality for MySQL?

我目前有一个Django-MySQL设置，其中有这个查询，该查询在非索引字段上运行，并带有BINARY LIKE操作，并且需要一分钟才能完成.

I currently have a Django-MySQL setup where I have this query which runs on a non-indexed field and with a BINARY LIKE operation, and it takes over a minute to complete.

我的查询是从文本开头-text%进行的部分搜索.

My query is a partial search from the beginning of the text - text%.

这是表结构.该表实际上包含20多个字段，但是我只包含了主键和要搜索的字段

This is the table structure. The table actually contains over 20 fields, but I've included just the primary key and the field I'm searching on

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

| Field   | Type          | Null | Key | Default | Extra |

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

| id      | varchar(255)  | NO   | PRI | NULL    |       |

| mid     | varchar(255)  | NO   | MUL | NULL    |       |

这是查询-

select count(*) from table where mid binary like 'text%';

这些是索引-

PRIMARY KEY index has cardinality 102820460
mid index has cardinality 756032

推荐答案

执行MySQL索引字符串左侧的事实.

Do the fact that MySQL indexes the left side of a string.

然后，如果查询右侧使用通配符，则字符串列可以使用索引:

Then a string column can use the index if the query use wildcard right side :

 SELECT * FROM your_table WHERE field LIKE "text%" # can use an index

但是请记住，对于索引，限制为767个字节

but remember that for index there is a limit of 767 bytes

从Mysql DOC

From Mysql DOC

B树索引可用于以下表达式中的列比较: 使用=，>，> =，<，< =或BETWEEN运算符.索引也可以是 如果LIKE的参数是常量字符串，则用于LIKE比较 不能以通配符开头.

A B-tree index can be used for column comparisons in expressions that use the =, >, >=, <, <=, or BETWEEN operators. The index also can be used for LIKE comparisons if the argument to LIKE is a constant string that does not start with a wildcard character.

https://dev.mysql.com/doc/refman/8.0/zh-CN/index-btree-hash.html

这篇关于在MySQL中为BINARY LIKE操作建立索引的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！