MySQL-索引优化

前言

本文通过创建含有大量数据的数据表，测试并总结MySQL在单表、关联表、子查询、排序分组时对应的索引创建规则。

准备工作

创建表

-- 创建数据表，大量数据的表
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;

开启自定义函数功能

1
2
3

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

创建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$$

插入数据

-- 执行存储过程，往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);

创建索引的建议

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

关联查询优化

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

子查询优化

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

1	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

排序分组优化

无过滤不索引
如果SQL语句没有过滤条件，索引失效。

1 2	explain select SQL_NO_CACHE * from emp order by age,deptid; explain select SQL_NO_CACHE * from emp order by age,deptid limit 10;

顺序错，必排序
如果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;

方向反必排序
如果order by后的字段的排序方式不同，Extra会出现useing filesort。要保证排序方式全部相同。

1 2	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 ;

索引选择
当范围条件和group by 或者 order by 的字段出现二选一时，优先观察条件字段的过滤数量，如果过滤的数据足够多，而需要排序的数据并不多时，优先把索引放在范围字段上。反之，亦然。
1
2
3
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);
分组通排序
group by 使用索引的原则几乎跟order by一致，唯一区别是groupby 即使没有过滤条件用到索引，也可以直接使用索引。

覆盖索引

什么是覆盖索引？
简单说就是，select 到 from 之间查询的列 <=使用的索引列+主键