100w测试数据，为什么加了索引查询反而变慢了？

[yjl49]

建表：
create table tb_test(fval varchar(50));
----------------------------------------------
插入测试数据：
DELIMITER $$

CREATE DEFINER=`root`@`localhost` PROCEDURE `P_teset`()
BEGIN
DECLARE v_val VARCHAR(20);
DECLARE v_str VARCHAR(20);
DECLARE v_i INT ;
DECLARE v_j INT;

SET v_str ='abcdefghijklmnopqrstuvwxyz';
SET v_i=0;
SET v_j=0;

WHILE v_i<600000 DO
SET v_val ='';

WHILE v_j< 13 DO
SET v_val= CONCAT(v_val,SUBSTRING(v_str,1,FLOOR(1+RAND()*26)));
SET v_j=v_j+1;
END WHILE;
SET v_j=0;
INSERT INTO tb_test(fval)
VALUES(v_val);

SET v_i =v_i+1;
END WHILE;
END$$

DELIMITER ;
----------------------------------------------
测试like：

SELECT *
FROM tb_test
WHERE fval LIKE '%ab';

70231条 用时0.531s

SELECT *
FROM tb_test
WHERE fval LIKE 'ab%'

961406条用时1.422s
---------------------------------------------
加索引：
ALTER TABLE tb_test ADD INDEX my_index(fval);
---------------------------------------------
再测试：
SELECT *
FROM tb_test
WHERE fval LIKE '%ab';

70231条 用时1.094s

SELECT *
FROM tb_test
WHERE fval LIKE 'ab%'

961406条用时1.485s

表为MyISAM格式。

为什么加了索引却慢了？

[rucypli]

LIKE '%ab';不走索引

[yjl49]

[Quote=引用 1 楼 rucypli 的回复:]

LIKE '%ab';不走索引
[/Quote]
我想证明的正是这个。网上说like 'ab%' 用到了索引，而 like '%ab'则不会。
如果是这样 like 'ab%' 的时间应该大幅减少，而like '%ab'则变化不大才对。
事实是 加了索引后 like 'ab%' 变化不大，而like '%ab' 却性能下降。。。

[rucypli]

这个时间和缓存也有关系，你最好每次执行sql都重启下数据库已清空缓存

[ACMAIN_CHM]

[Quote]SELECT *
FROM tb_test
WHERE fval LIKE '%ab';

70231条 用时0.531s

SELECT *
FROM tb_test
WHERE fval LIKE 'ab%'
961406条用时1.422s[/Quote]

select * 本身输出也需要时间，第二个查询的结果是 961406 条，而第一个是70231条. 需要考虑这么多记录本身的输出显示到屏幕上所需要时间。

可以测试

select count(*) FROM tb_test WHERE fval LIKE '%ab';
select count(*) FROM tb_test WHERE fval LIKE 'ab%'

[yjl49]

[Quote=引用 4 楼 acmain_chm 的回复:]

引用SELECT *
FROM tb_test
WHERE fval LIKE '%ab';

70231条 用时0.531s

SELECT *
FROM tb_test
WHERE fval LIKE 'ab%'
961406条用时1.422s

select * 本身输出也需要时间，第二个查询的结果是 961406 条，而第一个是70231条. 需要考虑这么……
[/Quote]

测试结果显示，count(*) 时不用索引也是更快。 那么这个例子中索引为什么会拖慢查询速度呢？

[kikiboy1987]

like是不走索引的，所以你找其他原因看看吧，别怀疑是这个索引把你拖慢了...
建议explain一下你的语句看该语句走的是什么

[yjl49]

[Quote=引用 8 楼 acmain_chm 的回复:]

贴出你的

explain select count(*) FROM tb_test WHERE fval LIKE 'ab%';
show index from tb_test ;
[/Quote]

http://blog.csdn.net/yjl49/article/details/7239022

里面有截图。

[yjl49]

[Quote=引用 9 楼 zuoxingyu2012 的回复:]

引用楼主 yjl49 的回复:
建表：
create table tb_test(fval varchar(50));
----------------------------------------------
插入测试数据：
DELIMITER $$

CREATE DEFINER=`root`@`localhost` PROCEDURE `P_teset`()
BEGIN
……
[/Quote]
测试了之后，like 'ab%'加快了速度:

SELECT SQL_NO_CACHE COUNT(*) FROM tb_test WHERE fval LIKE 'ab%';
查询961406条，用时0.875s

SELECT SQL_NO_CACHE COUNT(*) FROM tb_test WHERE fval LIKE '%ab';
查询70231条，用时1.062s

[ACMAIN_CHM]

[Quote]引用 8 楼 acmain_chm 的回复:

贴出你的

explain select count(*) FROM tb_test WHERE fval LIKE 'ab%';
show index from tb_test ;


http://blog.csdn.net/yjl49/article/details/7239022

里面有截图。
[/Quote]你的show index 贴在哪儿了？
另外尽可能把结果直接贴在这个贴子中以便别人分析。

[php_aga]

执行一条重启一下MYSQL看看，记录多的话，估计显示也占时间。按理说like 'ab%'是走索引，like '%ab'则不走。但估摸前者的效率也不会好到哪里去。

[ACMAIN_CHM]

贴出你的

explain select count(*) FROM tb_test WHERE fval LIKE 'ab%';
show index from tb_test ;

[珠海-天堂]

[Quote=引用楼主 yjl49 的回复:]
建表：
create table tb_test(fval varchar(50));
----------------------------------------------
插入测试数据：
DELIMITER $$

CREATE DEFINER=`root`@`localhost` PROCEDURE `P_teset`()
BEGIN
DECLARE v_val V……
[/Quote]

几个知识点：
1：like '%ab',这样通配符在左边的，MYSQL是不会用到索引的,MYSQL使用索引用的是最左前缀的方式。
2：like 'ab%',这样可能用到索引，你需要在查询语句前面加EXPLAIN关键字看看是否用到了索引。
3：检查一个SQL的效率是否变化了，需要排除出QUERY_CACHE，在查询时这样写
select sql_no_cache * from ... where ...这样就不会从QUERY_CACHE取数据了。多运行几次取平均值比较靠谱。

[libo_sina]

你可以用 explain 分析你的sql语句,看你的索引是否利用到了！添加索引会产生磁盘碎片,占用资源空间,在插入或修改时都会操作索引!所以建索引一般都是在where条件后面的字段做索引!

[coky20009]

这个和数据也有很大关系的。一个ab%，用索引的效果不明显。试试5-7个字符看看。