wildcard - 索引查找( MySQL ) 中不使用复合索引的左大多数列中的通配符( 索引中的剩余列)?

  显示原文与译文双语对照的内容
63 0

假设你有一个 last_name,first_name的主复合索引。 然后你进行了搜索 WHERE first_name LIKE 'joh%' AND last_name LIKE 'smi%'

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

我所要求的

Example-1: 主键为 last_name, first_name
Example-2: 主键为 last_name

使用这里where子句: WHERE first_name LIKE 'joh%' AND last_name LIKE 'smi%' Example-1 是否比Example-2快?

更新

这里是一个 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';



时间: 原作者:

142 4

这是你的问题。复数。 把他们用它的他词重写( 带"就是不同的问题。 这样做不会让响应者更容易。 恰恰相反.

在索引查找( MySQL ) 中,复合索引的最大列中的[Title question] 完成通配符:索引查找中不使用它的余?

A1: 不,这并不意味着。

在last_name条件中使用的通配符是否意味着first_name条件将不会在进一步帮助MySQL查找索引时使用。

A2: 不,这并不意味着。 加上那个问题的尾部是模糊的。 它已经知道使用什么索引可以是对这种模糊的一个分支答案。

换句话说,通过在last_name条件上放一个通配符,MySQL只会做部分索引查找( 忽略last_name右侧列中给出的条件)?

of: 不是,正确的列是从索引类似的索引中提供的,这些索引从数据页查找缓慢。

Q4: 。Example-1 比Example-2快?

是的。它是关于那些列的覆盖索引。 查看覆盖索引。

Q4无关。 如果是PK或者非 PK,它是不相关的。 可以能有十个原因,为什么为你的应用程序,for会变得非常糟糕。

下面是原始答案:

使用 ,在上只有一个组合键和一个查询,如你所提到


WHERE first_name LIKE 'joh%'



。它根本不使用索引。 它会做一个表格扫描。 因为没有

  • first_name 上的单个列密钥
  • 一个带有 first_name 的组合键,最左侧的键。

,我们来这儿。

请参见手册页多个列索引以阅读更多信息。 并专注于 left-most的概念。 实际上,去那个页面,搜索单词 left

参见mysql中的解释插件工具的手册页。 还有文章使用解释编写更好的Mysql查询。

编辑

一个小时或者两个小时前我一直在这个问题上对这个问题进行了一些编辑。 我将给你留下以下信息。 通过解释运行实际查询,并通过上面的Using Explain.. . 链接或者其他引用来解释


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



Explainrows 提供巫术数字。 是的,因为一个可以能会运行一个小时的查询,你要求 Explain 给你一个模糊数,不要在 2秒内。 不要考虑这些是真实的计数 #'s 运行时的准则,没有 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 |


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



原作者:
...