复合索引最左列中的通配符是否意味着索引查找(MySQL)中未使用索引中的其余列? [英] Does wildcard in left-most column of composite index mean remaining columns in index aren't used in index lookup (MySQL)?

查看：100
本文介绍了复合索引最左列中的通配符是否意味着索引查找(MySQL)中未使用索引中的其余列?的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

假设您的主复合索引为last_name,first_name.然后，您搜索了WHERE first_name LIKE 'joh%' AND last_name LIKE 'smi%'.

Imagine you have a primary composite index of last_name,first_name. Then you performed a search of WHERE first_name LIKE 'joh%' AND last_name LIKE 'smi%'.

在last_name条件中使用通配符是否意味着在进一步帮助MySQL查找索引时不会使用first_name条件?换句话说，通过在last_name条件上放置通配符，MySQL将仅执行部分索引查找(并忽略last_name右侧列中给出的条件)?

Does the wildcard used in the last_name condition mean that the first_name condition will not be used in further helping MySQL find indexes? In other words, by putting a wildcard on the last_name condition MySQL will only do a partial index lookup (and ignores conditions given in the columns that are to the right of last_name)?

进一步澄清我的要求

示例1:主键为last_name, first_name.
示例2:主键为last_name.

Example-1: Primary key is last_name, first_name.
Example-2: Primary key is last_name.

使用此WHERE子句:WHERE first_name LIKE 'joh%' AND last_name LIKE 'smi%'，示例1会比示例2快吗?

Using this WHERE clause:WHERE first_name LIKE 'joh%' AND last_name LIKE 'smi%', would Example-1 be faster than Example-2?

更新

这是一个sqlfiddle: http://sqlfiddle.com/#!9/6e0154/3

Here is an sqlfiddle: http://sqlfiddle.com/#!9/6e0154/3

CREATE TABLE `people1` (
    `id` INT(11),
    `first_name` VARCHAR(255) NOT NULL,
    `middle_name` VARCHAR(255) NOT NULL,
    `last_name` VARCHAR(255) NOT NULL,
    PRIMARY KEY (`id`),
    INDEX `name` (`last_name`(15), `first_name`(10))
  )
COLLATE='latin1_swedish_ci'
ENGINE=InnoDB;

CREATE TABLE `people2` (
    `id` INT(11),
    `first_name` VARCHAR(255) NOT NULL,
    `middle_name` VARCHAR(255) NOT NULL,
    `last_name` VARCHAR(255) NOT NULL,
    PRIMARY KEY (`id`),
    INDEX `name` (`last_name`(15))
  )
COLLATE='latin1_swedish_ci'
ENGINE=InnoDB;

INSERT INTO `people1` VALUES
(1,'John','','Smith'),(2,'Joe','','Smith'),(3,'Tom','','Smith'),(4,'George','','Washington');
INSERT INTO `people2` VALUES
(1,'John','','Smith'),(2,'Joe','','Smith'),(3,'Tom','','Smith'),(4,'George','','Washington');

# Query 1A
EXPLAIN SELECT * FROM `people1` WHERE `first_name` LIKE 'joh%' AND `last_name` LIKE 'smi%';
# Query 1B
EXPLAIN SELECT * FROM `people1` WHERE `first_name` LIKE 'joh%' AND `last_name` LIKE 'john';

# Query 2A
EXPLAIN SELECT * FROM `people2` WHERE `first_name` LIKE 'joh%' AND `last_name` LIKE 'smi%';
# Query 2B
EXPLAIN SELECT * FROM `people2` WHERE `first_name` LIKE 'joh%' AND `last_name` LIKE 'john';

推荐答案

这是您的问题.复数.通过改写它们(用换句话说")，它们只是不同的问题.这样做并不一定会使响应者更加容易.相反.

Here are your questions. Plural. By rephrasing them (with "in other words") they are just different questions. Doing so does not make it easier for responders necessarily. On the contrary.

Q1:[标题问题]复合索引最左列中的通配符是否意味着索引查找(MySQL)中不使用索引中的其余列?

Q1: [Title question] Does wildcard in left-most column of composite index mean remaining columns in index aren't used in index lookup (MySQL)?

A1:不，这并不意味着.

A1: No, it does not mean that.

Q2:last_name条件中使用的通配符是否表示将不会在进一步帮助MySQL查找索引时使用first_name条件?

Q2: Does the wildcard used in the last_name condition mean that the first_name condition will not be used in further helping MySQL find indexes?

A2:不，这并不意味着.再加上这个问题的尾巴是模棱两可的.它已经知道使用哪种索引可能是解决此类模糊性的一个分支.

A2: No, it does not mean that. Plus the tail of that question is ambiguous. It already knows what Index to use could be one offshoot answer to such vagueness.

Q3:换句话说，通过在last_name条件上放置通配符，MySQL将仅执行部分索引查找(并忽略last_name右侧列中给出的条件)?

Q3: In other words, by putting a wildcard on the last_name condition MySQL will only do a partial index lookup (and ignores conditions given in the columns that are to the right of last_name)?

A3:否.最右边的列从索引中提供，类似于覆盖索引策略，这得益于数据页面查找的缓慢.

A3: No. The right-most columns are served from the index similar to a covering index strategy benefiting from the slowness of data page lookup.

Q4:...示例1是否比示例2更快?

Q4: ...would Example-1 be faster than Example-2?

A4:是的.它是关于这些列的覆盖索引.请参阅覆盖索引.

A4: Yes. It is a covering index in regards to those columns. See covering indexes.

关于Q4的问题.是PK还是非PK都无关紧要.可能有很多原因使PK无法满足您的应用要求.

As an aside concerning Q4. It is irrelevant if it is a PK or non-PK. There are probably a dozen reasons why that as a PK would be dreadful for your application.

以下原始答案:

仅 (last_name,first_name)上的复合键 和您提到的查询

with only a composite key on (last_name,first_name) and a query as you mention

WHERE first_name LIKE 'joh%'

...它根本不会使用索引.它将进行表扫描.由于缺少

... It won't use the index at all. It will do a table scan. Due to the absence of

  • first_name
    • 上的单列键
  • 具有first_name 最左
    • 的组合键
  • a single column key on first_name
  • a composite key with first_name left-most

表扫描到了.

请参见手册页多列索引了解更多.并专注于它的left-most概念.实际上，转到该页面，然后在单词left上进行搜索.

Please see the Manual page Multiple-Column Indexes to read more. And focus on the left-most concept of it. In fact, go to that page, and search on the word left.

请参见mysql中解释工具上的手册页.还有文章使用解释编写更好的MySQL查询

See the Manual Page on the Explain facility in mysql. Also the article Using Explain to Write Better Mysql Queries.

自从我在一两个小时前来到这里以来，对该问题进行了一些编辑.我将为您提供以下内容.通过解释运行您的实际查询，并通过上面的Using Explain ...链接或其他参考文献

There have been a few edits to the question since I was here an hour or two ago. I will leave you with the following. Run your actual query thru explain, and decipher thru the Using Explain ... link above or another reference

drop table myNames;
create table myNames
(   id int auto_increment primary key,
    lastname varchar(100) not null,
    firstname varchar(100) not null,
    col4 int not null,
    key(lastname,firstname)
);
truncate table myNames;
insert myNames (lastName,firstName,col4) values
('Smith','John',1),('Smithers','JohnSomeone',1),('Smith3','John4324',1),('Smi','Jonathan',1),('Smith123x$FA','Joh',1),('Smi3jfif','jkdid',1),('r3','fe2',1);

insert myNames (lastName,firstName,col4) select lastname,firstname,col4 from mynames;
insert myNames (lastName,firstName,col4) select lastname,firstname,col4 from mynames;
insert myNames (lastName,firstName,col4) select lastname,firstname,col4 from mynames;
insert myNames (lastName,firstName,col4) select lastname,firstname,col4 from mynames;
insert myNames (lastName,firstName,col4) select lastname,firstname,col4 from mynames;
insert myNames (lastName,firstName,col4) select lastname,firstname,col4 from mynames;
insert myNames (lastName,firstName,col4) select lastname,firstname,col4 from mynames;
insert myNames (lastName,firstName,col4) select lastname,firstname,col4 from mynames;
insert myNames (lastName,firstName,col4) select lastname,firstname,col4 from mynames;
insert myNames (lastName,firstName,col4) select lastname,firstname,col4 from mynames;
insert myNames (lastName,firstName,col4) select lastname,firstname,col4 from mynames;
insert myNames (lastName,firstName,col4) select lastname,firstname,col4 from mynames;
insert myNames (lastName,firstName,col4) select lastname,firstname,col4 from mynames;
insert myNames (lastName,firstName,col4) select lastname,firstname,col4 from mynames;
insert myNames (lastName,firstName,col4) select lastname,firstname,col4 from mynames;
insert myNames (lastName,firstName,col4) select lastname,firstname,col4 from mynames;

select count(*) from myNames; 
-- 458k rows

select count(*)
from myNames
where lastname like 'smi%';
-- 393216 rows

select count(*)
from myNames
where lastname like 'smi%' and firstname like 'joh%';
-- 262144 rows

Explain呈现rows的伏都教数字.巫毒教?是的，因为查询可能会运行一个小时，所以您要求explain给您一个模糊计数，而不是运行它，并在2秒或更短的时间内给出答案.在没有explain的情况下真实运行时，不要将这些视为标准的真实计数.

Explain renders voodoo numbers for rows. Voodoo? Yes, because a query that will potentially run for an hour, you are asking explain to give you a fuzzy count, not run it, and give you that answer in 2 seconds or less. Don't consider these to be real count #'s for criteria when it is run for real, without explain.

explain 
select count(*) 
from myNames 
where lastname like 'smi%';
+----+-------------+---------+-------+---------------+----------+---------+------+--------+--------------------------+
| id | select_type | table   | type  | possible_keys | key      | key_len | ref  | rows   | Extra                    |
+----+-------------+---------+-------+---------------+----------+---------+------+--------+--------------------------+
|  1 | SIMPLE      | myNames | range | lastname      | lastname | 302     | NULL | 233627 | Using where; Using index |
+----+-------------+---------+-------+---------------+----------+---------+------+--------+--------------------------+

explain 
select count(*) 
from myNames 
where lastname like 'smi%' and firstname like 'joh%' and col4=1;
+----+-------------+---------+-------+---------------+----------+---------+------+--------+--------------------------+
| id | select_type | table   | type  | possible_keys | key      | key_len | ref  | rows   | Extra                    |
+----+-------------+---------+-------+---------------+----------+---------+------+--------+--------------------------+
|  1 | SIMPLE      | myNames | range | lastname      | lastname | 604     | NULL | 233627 | Using where; Using index |
+----+-------------+---------+-------+---------------+----------+---------+------+--------+--------------------------+


-- the below chunk is interest. Look at the Extra column

explain 
select count(*) 
from myNames 
where lastname like 'smi%' and firstname like 'joh%' and col4=1;
+----+-------------+---------+------+---------------+------+---------+------+--------+-------------+
| id | select_type | table   | type | possible_keys | key  | key_len | ref  | rows   | Extra       |
+----+-------------+---------+------+---------------+------+---------+------+--------+-------------+
|  1 | SIMPLE      | myNames | ALL  | lastname      | NULL | NULL    | NULL | 457932 | Using where |
+----+-------------+---------+------+---------------+------+---------+------+--------+-------------+

explain 
select count(*) 
from myNames 
where firstname like 'joh%';
+----+-------------+---------+-------+---------------+----------+---------+------+--------+--------------------------+
| id | select_type | table   | type  | possible_keys | key      | key_len | ref  | rows   | Extra                    |
+----+-------------+---------+-------+---------------+----------+---------+------+--------+--------------------------+
|  1 | SIMPLE      | myNames | index | NULL          | lastname | 604     | NULL | 453601 | Using where; Using index |
+----+-------------+---------+-------+---------------+----------+---------+------+--------+--------------------------+


analyze table myNames;
+----------------------+---------+----------+----------+
| Table                | Op      | Msg_type | Msg_text |
+----------------------+---------+----------+----------+
| so_gibberish.mynames | analyze | status   | OK       |
+----------------------+---------+----------+----------+

select count(*) 
from myNames where left(lastname,3)='smi';
-- 393216 -- the REAL #
select count(*) 
from myNames where left(lastname,3)='smi' and left(firstname,3)='joh';
-- 262144 -- the REAL #

explain 
select lastname,firstname 
from myNames  
where lastname like 'smi%' and firstname like 'joh%';
+----+-------------+---------+-------+---------------+----------+---------+------+--------+--------------------------+
| id | select_type | table   | type  | possible_keys | key      | key_len | ref  | rows   | Extra                    |
+----+-------------+---------+-------+---------------+----------+---------+------+--------+--------------------------+
|  1 | SIMPLE      | myNames | range | lastname      | lastname | 604     | NULL | 226800 | Using where; Using index |
+----+-------------+---------+-------+---------------+----------+---------+------+--------+--------------------------+

这篇关于复合索引最左列中的通配符是否意味着索引查找(MySQL)中未使用索引中的其余列?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！

查看全文
相关文章
数据库最新文章
热门教程
热门工具
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆