建表:
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格式。
为什么加了索引却慢了?
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条. 需要考虑这么多记录本身的输出显示到屏幕上所需要时间。
可以测试
select count(*) FROM tb_test WHERE fval LIKE '%ab';
select count(*) FROM tb_test WHERE fval LIKE 'ab%'
建表:
create table tb_test(fval varchar(50));
----------------------------------------------
插入测试数据:
DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `P_teset`()
BEGIN
DECLARE v_val V……
几个知识点:
1:like '%ab',这样通配符在左边的,MYSQL是不会用到索引的,MYSQL使用索引用的是最左前缀的方式。
2:like 'ab%',这样可能用到索引,你需要在查询语句前面加EXPLAIN关键字看看是否用到了索引。
3:检查一个SQL的效率是否变化了,需要排除出QUERY_CACHE,在查询时这样写
select sql_no_cache * from ... where ...这样就不会从QUERY_CACHE取数据了。多运行几次取平均值比较靠谱。