MySQL:关于 INDEX 使用的 LIKE 123 和 = 123 之间的区别

Question

我遇到了一个非常奇怪的行为，结果证明这是在我的 where 条件下使用正确的运算符的问题。

假设下面的表结构有几百万个条目:

CREATE TABLE `obj` (
  `obj__id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `obj__obj_type__id` int(10) unsigned DEFAULT NULL,
  `obj__title` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `obj__const` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `obj__description` text COLLATE utf8_unicode_ci,
  `obj__created` datetime DEFAULT NULL,
  `obj__created_by` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `obj__updated` datetime DEFAULT NULL,
  `obj__updated_by` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `obj__property` int(10) unsigned DEFAULT '0',
  `obj__status` int(10) unsigned DEFAULT '1',
  `obj__sysid` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `obj__scantime` datetime DEFAULT NULL,
  `obj__imported` datetime DEFAULT NULL,
  `obj__hostname` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `obj__undeletable` int(1) unsigned NOT NULL DEFAULT '0',
  `obj__rt_cf__id` int(11) unsigned DEFAULT NULL,
  `obj__cmdb_status__id` int(10) unsigned DEFAULT NULL,
  PRIMARY KEY (`obj__id`),
  KEY `obj_FKIndex1` (`obj__obj_type__id`),
  KEY `obj_ibfk_2` (`obj__cmdb_status__id`),
  KEY `obj__sysid` (`obj__sysid`),
  KEY `obj__title` (`obj__title`),
  KEY `obj__const` (`obj__const`),
  KEY `obj__hostname` (`obj__hostname`),
  KEY `obj__status` (`obj__status`),
  KEY `obj__updated_by` (`obj__updated_by`)
) ENGINE=InnoDB AUTO_INCREMENT=7640131 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

一个非常简单的选择，有两个条件，按 obj__title 排序，限制为 500，执行缓慢(500 毫秒):

SELECT SQL_NO_CACHE * FROM obj WHERE (obj__status = 2) AND (obj__obj_type__id = 59) ORDER BY obj__title ASC LIMIT 0, 500;

没有“ORDER BY obj__title”，它运行起来就像一个魅力(<1ms)。

EXPLAIN SELECT 告诉我 MySQL 正在执行文件排序而不是使用 obj__title 索引。所以，好吧，很明显这个查询很慢:

id  select_type table   type    possible_keys   key key_len ref rows    Extra
1   SIMPLE  obj index_merge obj_FKIndex1,obj__status    obj_FKIndex1,obj__status    5,5 NULL    1336    Using intersect(obj_FKIndex1,obj__status); Using where; Using filesort

当我强制索引 obj__title 与 FORCE 或 USE INDEX 一起使用时，mysql 没有使用其他索引，再次导致性能非常差。但没关系，很明显，性能不佳与这两个条件的组合和 order by 有关。

既然我花了几个小时来研究优化这个查询，我想出了一个非常简单的交换:我将我的条件运算符从 = 交换为 LIKE。所以我的查询是这样的:

EXPLAIN SELECT SQL_NO_CACHE * FROM obj WHERE (obj__status LIKE 2) AND (obj__obj_type__id LIKE 59) ORDER BY obj__title ASC LIMIT 0, 500;

事情是这样的..

id  select_type table   type    possible_keys   key key_len ref rows    Extra
1   SIMPLE  obj index   obj_FKIndex1,obj__status    obj__title  768 NULL    500 Using where

查询性能为 150 毫秒。我真的很震惊。

我对速度不是很满意，但至少它表现不错。

但我真正想知道的是，为什么 LIKE 使用索引，而 = 不使用索引？我没有在 MySQL 文档中找到任何相关提示。关于 LIKE 不区分大小写和 LIKE 对于 VARCHARS > 255 或任何其他 CHAR 或 TEXT 字段的行为略有不同的注意事项。没有关于它的整数行为的单个词。

有人可以阐明这种情况吗？也欢迎任何数据库设计或查询技巧来加快查询速度!

Answer 1

对于这个查询:

SELECT SQL_NO_CACHE *
FROM obj
WHERE (obj__status = 2) AND (obj__obj_type__id = 59)
ORDER BY obj__title ASC
LIMIT 0, 500;

最好的索引是obj(obj__status, obj__obj_type__id, obj__title)。

否则，我希望在两个 where 字段之一上有一个索引。

但是，当您使用like 时，您是在将数字与字符串进行比较。这通常会阻止使用索引。唯一可能的索引是 order by，它恰好适用于您的情况。

但是，适当的索引应该有更好的性能。