为什么mysql不使用密钥进行简单的选择？ [英] Why doesnt mysql use the key for a simple select?

问题描述

以下是我的所作所为：

MariaDB []> explain select * from blabla where name like '%poor%';
+------+-------------+--------------+------+---------------+------+---------+------+---------+-------------+
| id   | select_type | table        | type | possible_keys | key  | key_len | ref  | rows    | Extra       |
+------+-------------+--------------+------+---------------+------+---------+------+---------+-------------+
|    1 | SIMPLE      | blabla       | ALL  | NULL          | NULL | NULL    | NULL | 8358772 | Using where |
+------+-------------+--------------+------+---------------+------+---------+------+---------+-------------+
1 row in set (0.01 sec)

MariaDB [bd]>show create table blabla;
| blabla | CREATE TABLE `blabla` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `name` text NOT NULL,
  PRIMARY KEY (`id`),
  KEY `name` (`name`(252))
) ENGINE=InnoDB AUTO_INCREMENT=8372852 DEFAULT CHARSET=utf8

所以，如果你看：'name'上有一个键，解释告诉我possible_keys - > NULL。我该怎么做才能加快速度：

So if you watch: there's a key on 'name' and explain tells me "possible_keys" -> NULL. What shall I do to make this faster:

MariaDB []>select * from blabla where name like '%poor%';
********
********
491 rows in set (26.20 sec)
MariaDB []>WHAT??? 26 seconds on a 8 373 116 rows table? ARE YOU KIDDIN'?
ERROR 1064 (42000): You have an error

推荐答案

MySQL（和MariaDB）无法有效使用索引来满足谓词。

MySQL (and MariaDB) cannot make effective use of an index to satisfy the predicate.

name LIKE '%poor%'

考虑 name 的可能值将满足该条件，以及它们可能出现在索引中的位置。例如，值'aaapoor'和'zzzpoor'满足条件。满足条件的值可以在索引中显示为 where ，作为索引中的第一个值/行，或者作为索引中的最后一个值/行，或者介于两者之间的任何位置。

Consider the possible value of name that would satisfy that condition, and where they could appear in the index. For example, values 'aaapoor' and 'zzzpoor' satisfy the condition. A value that satisfies the condition could appear anywhere in the index, as the first value/row in the index, or as the last value/row in the index, or anywhere in between.

因此，MySQL必须为表中的每个行评估该谓词。必须检查每一行。

So, MySQL has to evaluate that predicate for every row in the table. Every single row has to be checked.

MySQL不能使用索引来消除任何行的检查。索引提高谓词性能的方式，例如 col ='foo'，MySQL可以非常快速地消除不的大量行em>需要检查，因为它们出现在索引中的位置。 MySQL可以快速消除大量行，并缩小需要检查的索引页面。这就是索引如何提高性能。

MySQL can't use an index to eliminate any rows from being checked. The way that an index would improve performance for a predicate such as col = 'foo' is that MySQL can very quickly eliminate vast swaths of rows that do not need to be checked, because of where they appear in the index. MySQL can quickly eliminate a boatload of rows, and narrow in on the index pages that do need to be checked. That's how an index improves performance.

并且MySQL无法使用查询中的谓词来做到这一点。这就是没有使用索引的原因。

And MySQL can't do that with the predicate in your query. That's why no index is being used.

如果查询有覆盖索引，MySQL可能会使用该索引。例如，如果您只选择 name 列，MySQL可以仅从索引页面满足查询，而无需访问基础数据页。

If there was a "covering" index for the query, MySQL might use that. For example, if you were selecting just the name column, MySQL could satisfy the query from just the index pages, without a need to visit the underlying data pages.

或者，如果您在 name （或索引中的一个或多个前导列）上有order by，MySQL可能会使用索引来避免使用filesort操作。

Or, if you had an "order by" on name (or a leading column or columns in an index), MySQL might make use of an index to avoid a "Using filesort" operation.

如果你想搜索单词那么出现在VARCHAR列中，您可能需要考虑使用FULLTEXT索引。在旧版本的MySQL中，仅在MyISAM表上支持这些版本。较新版本（> = 5.6？）的MySQL也支持InnoDB表上的FULLTEXT索引。

If you want to search for "words" that appear in a VARCHAR column, you might want to consider using a FULLTEXT index. In older versions of MySQL, those were supported only on MyISAM tables. Newer versions (>=5.6?) of MySQL support FULLTEXT indexes on InnoDB tables as well.

全文搜索在谓词中使用不同的操作（它的不是 a LIKE 比较。）

A fulltext search uses a different operation in the predicate (it's not a LIKE comparison.)

这篇关于为什么mysql不使用密钥进行简单的选择？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！