MySQL索引优化

一、准备工作

1. 创建表

-- 创建数据表，大量数据的表
CREATE TABLE `dept` (
                        `id` INT(11) NOT NULL AUTO_INCREMENT,
                        `deptName` VARCHAR(30) DEFAULT NULL,
                        `address` VARCHAR(40) DEFAULT NULL,
                        ceo INT NULL ,
                        PRIMARY KEY (`id`)
) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

CREATE TABLE `emp` (
                       `id` INT(11) NOT NULL AUTO_INCREMENT,
                       `empno` INT NOT NULL ,
                       `name` VARCHAR(20) DEFAULT NULL,
                       `age` INT(3) DEFAULT NULL,
                       `deptId` INT(11) DEFAULT NULL,
                       PRIMARY KEY (`id`)
    #CONSTRAINT `fk_dept_id` FOREIGN KEY (`deptId`) REFERENCES `t_dept` (`id`)
) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

2. 开启自定义MySQL函数

-- 开启mysql二进制日志，可以做主从复制。如不开启不能自定义mysql函数
show variables like 'log_bin_trust_function_creators';
set global log_bin_trust_function_creators=1;

3. 创建MySQL函数

-- 随机产生字符串，DELIMITER $$:以$为开始结束符,代替;
DELIMITER $$
CREATE FUNCTION rand_string(n INT) RETURNS VARCHAR(255)
BEGIN
    DECLARE chars_str VARCHAR(100) DEFAULT 'abcdefghijklmnopqrstuvwxyzABCDEFJHIJKLMNOPQRSTUVWXYZ';
    DECLARE return_str VARCHAR(255) DEFAULT '';
    DECLARE i INT DEFAULT 0;
    WHILE i < n DO
            SET return_str =CONCAT(return_str,SUBSTRING(chars_str,FLOOR(1+RAND()*52),1));
            SET i = i + 1;
        END WHILE;
    RETURN return_str;
END $$

-- 随机产生部门编号
DELIMITER $$
CREATE FUNCTION  rand_num (from_num INT ,to_num INT) RETURNS INT(11)
BEGIN
    DECLARE i INT DEFAULT 0;
    SET i = FLOOR(from_num +RAND()*(to_num -from_num+1))   ;
    RETURN i;
END$$

-- 假如要删除
-- drop function rand_num;

-- 创建往emp表中插入数据的存储过程
DELIMITER $$
CREATE PROCEDURE  insert_emp(  START INT ,  max_num INT )
BEGIN
    DECLARE i INT DEFAULT 0;
#set autocommit =0 把autocommit设置成0
    SET autocommit = 0;
    REPEAT
        SET i = i + 1;
        INSERT INTO emp (empno, NAME ,age ,deptid ) VALUES ((START+i) ,rand_string(6)   , rand_num(30,50),rand_num(1,10000));
    UNTIL i = max_num
        END REPEAT;
    COMMIT;
END$$
-- 创建往dept表中插入数据的存储过程
DELIMITER $$
CREATE PROCEDURE `insert_dept`(  max_num INT )
BEGIN
    DECLARE i INT DEFAULT 0;
    SET autocommit = 0;
    REPEAT
        SET i = i + 1;
        INSERT INTO dept ( deptname,address,ceo ) VALUES (rand_string(8),rand_string(10),rand_num(1,500000));
    UNTIL i = max_num
        END REPEAT;
    COMMIT;
END$$

-- 批量删除某个表上的所有索引
DELIMITER $$
CREATE  PROCEDURE `proc_drop_index`(dbname VARCHAR(200),tablename VARCHAR(200))
BEGIN
    DECLARE done INT DEFAULT 0;
    DECLARE ct INT DEFAULT 0;
    DECLARE _index VARCHAR(200) DEFAULT '';
    DECLARE _cur CURSOR FOR  SELECT   index_name   FROM information_schema.STATISTICS   WHERE table_schema=dbname AND table_name=tablename AND seq_in_index=1 AND    index_name <>'PRIMARY'  ;
    DECLARE  CONTINUE HANDLER FOR NOT FOUND set done=2 ;
    OPEN _cur;
    FETCH   _cur INTO _index;
    WHILE  _index<>'' DO
            SET @str = CONCAT("drop index ",_index," on ",tablename );
            PREPARE sql_str FROM @str ;
            EXECUTE  sql_str;
            DEALLOCATE PREPARE sql_str;
            SET _index='';
            FETCH   _cur INTO _index;
        END WHILE;
    CLOSE _cur;
END$$

4. 插入数据

-- 执行存储过程，往dept表添加1万条数据
DELIMITER ;
CALL insert_dept(10000);
-- 执行存储过程，往emp表添加50万条数据
CALL insert_emp(100000,500000);

select count(*) from emp;
select count(*) from dept;

-- 删除数据库中表的索引
-- CALL proc_drop_index("dbname","tablename");

二、单表索引优化

  • 索引失效规则

-- 1.全值匹配我最爱
explain select sql_no_cache * from emp where emp.age=30 and emp.deptId=4 and emp.name='abcd';
create index idx_age_depid_name on emp(age,deptId,name);

-- 2.最佳左前缀法则，如果索引了多列，要遵守最左前缀法则。指的是查询从索引的最左前列开始并且不跳过索引中的列。(带头大哥不能死，中间兄弟不能少)
EXPLAIN SELECT SQL_NO_CACHE * FROM emp WHERE emp.age=30   AND emp.name = 'abcd';
EXPLAIN SELECT SQL_NO_CACHE * FROM emp WHERE emp.deptid=1   AND emp.name = 'abcd';

-- 3.在索引列上做任何操作（计算、函数、(自动or手动)类型转换），都会导致索引失效而转向全表扫描
EXPLAIN SELECT SQL_NO_CACHE * FROM emp WHERE emp.name LIKE 'abc%';
EXPLAIN SELECT SQL_NO_CACHE * FROM emp WHERE LEFT(emp.name,3) = 'abc';
create index idx_name on emp(name);

-- 4.存储引擎不能使用索引中范围条件右边的列。范围索引创建时应放在最后面
EXPLAIN SELECT SQL_NO_CACHE * FROM emp WHERE emp.age=30 AND emp.deptId>20 AND emp.name = 'abc';
create index idx_age_deptid_name on emp(age,deptId,name); -- 部分索引有用
create index idx_age_name_deptid on emp(age,name,deptId); -- 全部索引有用

-- 5.mysql 在使用不等于(!= 或者<>)的时候无法使用索引会导致全表扫描
EXPLAIN SELECT SQL_NO_CACHE * FROM emp WHERE emp.name <> 'abc';
CREATE INDEX idx_name ON emp(NAME);

-- 6.is not null 也无法使用索引,但是is null是可以使用索引的
EXPLAIN SELECT SQL_NO_CACHE * FROM emp WHERE age IS NULL;
EXPLAIN SELECT SQL_NO_CACHE * FROM emp WHERE age IS NOT NULL;
create index idx_age  on emp(age);

-- 7.like以通配符开头('%abc...')mysql索引失效会变成全表扫描的操作
EXPLAIN SELECT SQL_NO_CACHE * FROM emp WHERE emp.name LIKE '%abc%';
create index idx_name on emp(name);

-- 8.字符串不加单引号索引失效，因为myslq会自动类型转化，即3的问题
EXPLAIN SELECT SQL_NO_CACHE * FROM emp WHERE emp.name = 123;
create index idx_name on emp(name);

  • 创建索引的建议

  1. 对于单键索引，尽量选择针对当前query过滤性更好的索引。
  2. 在选择组合索引的时候，当前Query中过滤性最好的字段在索引字段顺序中，位置越靠前越好。
  3. 在选择组合索引的时候，尽量选择可以能够包含当前query中的where字句中更多字段的索引。
  4. 在选择组合索引的时候，如果某个字段可能出现范围查询时，尽量把这个字段放在索引次序的最后面。
  5. 书写sql语句时，尽量避免造成索引失效的情况。

三、关联查询优化

  1. 保证被驱动表的join字段已经被索引。
  2. left join 时，选择小表作为驱动表，大表作为被驱动表。
  3. inner join 时，mysql会自己帮你把小结果集的表选为驱动表。
  4. 子查询尽量不要放在被驱动表，有可能使用不到索引。
  5. 能够直接多表关联的尽量直接关联，不用子查询。

四、子查询优化

尽量不要使用not in 或者 not exists，用left outer join on xxx is null 替代。

 EXPLAIN SELECT SQL_NO_CACHE age,count(*) FROM  emp a LEFT OUTER JOIN dept b ON a.id =b.ceo WHERE b.ceo IS NULL

五、排序分组优化

  1. 无过滤 不索引
    如果SQL语句没有过滤条件，索引失效。
explain select SQL_NO_CACHE * from emp order by age,deptid;
explain select SQL_NO_CACHE * from emp order by age,deptid limit 10;
  1. 顺序错，必排序
    如果order by后的字段的顺序和索引创建顺序不同，Extra会出现useing filesort。
explain  select * from emp where age=45 order by   deptid,name; 
explain  select * from emp where age=45 order by  deptid,empno;
explain  select * from emp where age=45 order by  name,deptid;
explain select * from emp where deptid=45 order by age;
  1. 方向反 必排序
    如果order by后的字段的排序方式不同，Extra会出现useing filesort。要保证排序方式全部相同。
explain select * from emp where age=45 order by  deptid desc, name desc ;
explain select * from emp where age=45 order by  deptid asc, name desc ;
  1. 索引选择
    当范围条件和group by 或者 order by 的字段出现二选一时 ，优先观察条件字段的过滤数量，如果过滤的数据足够多，而需要排序的数据并不多时，优先把索引放在范围字段上。反之，亦然。
SELECT SQL_NO_CACHE * FROM emp WHERE age =30 AND empno <101000 ORDER BY NAME ;
CREATE INDEX idx_age_name ON emp(age,NAME);
create index idx_age_eno on emp(age,empno);
  1. 分组通排序
    group by 使用索引的原则几乎跟order by一致 ，唯一区别是groupby 即使没有过滤条件用到索引，也可以直接使用索引。

六、覆盖索引

  • 什么是覆盖索引？
    简单说就是，select 到 from 之间查询的列 <=使用的索引列+主键
最后编辑于 ：
©著作权归作者所有,转载或内容合作请联系作者
  • 人面猴
    序言：七十年代末，一起剥皮案震惊了整个滨河市，随后出现的几起案子，更是在滨河造成了极大的恐慌，老刑警刘岩，带你破解...
    沈念sama阅读 222,252评论 6赞 516
  • 死咒
    序言：滨河连续发生了三起死亡事件，死亡现场离奇诡异，居然都是意外死亡，警方通过查阅死者的电脑和手机，发现死者居然都...
    沈念sama阅读 94,886评论 3赞 399
  • 救了他两次的神仙让他今天三更去死
    文/潘晓璐 我一进店门，熙熙楼的掌柜王于贵愁眉苦脸地迎上来，“玉大人，你说我怎么就摊上这事。” “怎么了？”我有些...
    开封第一讲书人阅读 168,814评论 0赞 361
  • 道士缉凶录：失踪的卖姜人
    文/不坏的土叔 我叫张陵，是天一观的道长。 经常有香客问我，道长，这世上最难降的妖魔是什么？ 我笑而不...
    开封第一讲书人阅读 59,869评论 1赞 299
  • 港岛之恋（遗憾婚礼）
    正文 为了忘掉前任，我火速办了婚礼，结果婚礼上，老公的妹妹穿的比我还像新娘。我一直安慰自己，他们只是感情好，可当我...
    茶点故事阅读 68,888评论 6赞 398
  • 恶毒庶女顶嫁案：这布局不是一般人想出来的
    文/花漫 我一把揭开白布。 她就那样静静地躺着，像睡着了一般。 火红的嫁衣衬着肌肤如雪。 梳的纹丝不乱的头发上，一...
    开封第一讲书人阅读 52,475评论 1赞 312
  • 城市分裂传说
    那天，我揣着相机与录音，去河边找鬼。 笑死，一个胖子当着我的面吹牛，可吹牛的内容都是我干的。 我是一名探鬼主播，决...
    沈念sama阅读 41,010评论 3赞 422
  • 双鸳鸯连环套：你想象不到人心有多黑
    文/苍兰香墨 我猛地睁开眼，长吁一口气：“原来是场噩梦啊……” “哼！你这毒妇竟也来了？” 一声冷哼从身侧响起，我...
    开封第一讲书人阅读 39,924评论 0赞 277
  • 万荣杀人案实录
    序言：老挝万荣一对情侣失踪，失踪者是张志新（化名）和其女友刘颖，没想到半个月后，有当地人在树林里发现了一具尸体，经...
    沈念sama阅读 46,469评论 1赞 319
  • 护林员之死
    正文 独居荒郊野岭守林人离奇死亡，尸身上长有42处带血的脓包…… 初始之章·张勋 以下内容为张勋视角 年9月15日...
    茶点故事阅读 38,552评论 3赞 342
  • 白月光启示录
    正文 我和宋清朗相恋三年，在试婚纱的时候发现自己被绿了。 大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
    茶点故事阅读 40,680评论 1赞 353
  • 活死人
    序言：一个原本活蹦乱跳的男人离奇死亡，死状恐怖，灵堂内的尸体忽然破棺而出，到底是诈尸还是另有隐情，我是刑警宁泽，带...
    沈念sama阅读 36,362评论 5赞 351
  • 日本核电站爆炸内幕
    正文 年R本政府宣布，位于F岛的核电站，受9级特大地震影响，放射性物质发生泄漏。R本人自食恶果不足惜，却给世界环境...
    茶点故事阅读 42,037评论 3赞 335
  • 男人毒药：我在死后第九天来索命
    文/蒙蒙 一、第九天 我趴在偏房一处隐蔽的房顶上张望。 院中可真热闹，春花似锦、人声如沸。这庄子的主人今日做“春日...
    开封第一讲书人阅读 32,519评论 0赞 25
  • 一桩弑父案，背后竟有这般阴谋
    文/苍兰香墨 我抬头看了看天上的太阳。三九已至，却和暖如春，着一层夹袄步出监牢的瞬间，已是汗流浃背。 一阵脚步声响...
    开封第一讲书人阅读 33,621评论 1赞 274
  • 情欲美人皮
    我被黑心中介骗来泰国打工， 没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留，地道东北人。 一个月前我还...
    沈念sama阅读 49,099评论 3赞 378
  • 代替公主和亲
    正文 我出身青楼，却偏偏与公主长得像，于是被迫代替她去往敌国和亲。 传闻我的和亲对象是个残疾皇子，可洞房花烛夜当晚...
    茶点故事阅读 45,691评论 2赞 361