sql中索引的使用分析

主要学习和记录sql中索引的使用

1.批量在库里插入了27W条数据

CREATE DEFINER=`root`@`%` PROCEDURE `批量插入`()
BEGIN
    #Routine body goes here...
            DECLARE i int;
SET i=1;
WHILE (i<100000) DO
    insert into kucun_info (shop_name,shop_code,shop_price,shop_inventory,shop_source) VALUES(972,875,3445,37655,654);
    set i = i+1;
END WHILE;
END 

2.EXPLAIN +sql语句的分析

EXPLAIN SELECT * FROM kucun_info WHERE kucunid = 3 --  const级别
EXPLAIN SELECT * FROM kucun_info WHERE shop_code = "XJ_001"
EXPLAIN SELECT * FROM kucun_info WHERE shop_name = "香蕉001"--  ref 级别

3.分析
--   27W条数据   
SET profiling = 1;
SELECT * FROM kucun_info WHERE shop_code = "XJ_001" -- 没加索引 0.086S
SELECT * FROM kucun_info WHERE shop_name = "香蕉001"-- 添加索引 0.015S
SELECT kucunid FROM kucun_info WHERE shop_code = "PT_001"
SELECT * FROM kucun_info WHERE shop_name = "葡萄001"
SHOW PROFILES; -- 查看执行sql语句的时间

可以分析出：27W条数据 加索引能快5倍