mysql like索引_MySQL 中like的使用对于索引的影响

今天看了一篇对于like使用对索引的影响的文章，发现自己实践的跟文章得出结论不大一样。所以还是建议自己在看别人文章的时候，感觉有疑问的话最好自己亲自动手实践一下。以免学到不全面的知识。

列子：

先建立一张表：

-- 创建表

CREATE TABLE test(

id INT(11) NOT NULL AUTO_INCREMENT,

uname VARCHAR(255),

PRIMARY KEY(id)

);

再在uname字段上创建索引：

-- 添加索引

ALTER TABLE test ADD INDEX uname_index (uname);

-- 查看索引

SHOW INDEX FROM test;


-- 添加记录

INSERT INTO test VALUES((1,'jay'),(2,'ja'),(3,'bril'),(4,'aybar');

检测结果：

EXPLAIN SELECT * FROM test WHERE uname LIKE 'j';

EXPLAIN SELECT * FROM test WHERE uname LIKE 'j%';

EXPLAIN SELECT * FROM test WHERE uname LIKE '%j';

EXPLAIN SELECT * FROM test WHERE uname LIKE '%j%';

以上是select的两个字段都为索引时，%号使用的效果；

--添加一个非索引字段

ALTER TABLE `test`

ADD COLUMN `status` int(11) UNSIGNED NULL DEFAULT 1 AFTER `uname`

再次检测：

EXPLAIN SELECT * FROM test WHERE uname LIKE 'j';

EXPLAIN SELECT * FROM test WHERE uname LIKE 'j%';

EXPLAIN SELECT * FROM test WHERE uname LIKE '%j';

EXPLAIN SELECT * FROM test WHERE uname LIKE '%j%';

这次的结果是： like后直接跟%号的匹配的查询不使用索引；like后先跟匹配值%,就会使用到索引。

疑问： 那么为啥加了一个字段后会有这样的区别呢？

这是因为：前提在where后的字段是索引字段

图1-2：当使用'j'和'j%'时，会优先使用到范围查询,此时type为range；

图3-4：之前的查询字段是id,uname，当使用'%j'和'%j%'时，无法达到缩小查询范围的效果；又由于select的两个字段都是属于索引字段，所以直接扫描索引就能达到查询需求，就不用全表扫描了，所以type是index。

加了一个非索引字段：status

图5-6：当使用'j'和'j%'时，会优先使用到范围查询,此时type为range；

图7-8：当使用'%j'和'%j%'，无法使用范围查询；同时select查询的字段不全是索引字段时，就需要进行全表扫描，所以type为all。

查询效率为：all

具体的还希望自己动手试一下哈。听到的不如看到的；看到的不如自己亲手实践出来的。

本人也在学习阶段，若有不正确不恰当的地方还希望能多多指教_！