表使用索引及常见的索引失效的情况（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;

#设置参数

show variables like 'log_bin_trust_function_creators';
set global log_bin_trust_function_creators=1;

#随机产生字符串

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));  #每次在chars_str里面取一个字符
 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$$

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

#执行存储过程，往dept表添加1万条数据

DELIMITER ;
CALL insert_dept(10000);

#执行存储过程，往emp表添加50万条数据

DELIMITER ;
CALL insert_emp(100000,500000);

#删除索引的存储过程

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

#调用批量删除非主键索引的存储过程

CALL proc_drop_index("myemployees","emp");

#创建单值索引

create index idx_age on emp(age);
create index idx_name on emp(name);

#创建复合索引

create index idx_age_deptID on emp(age,deptId);
create index idx_age_deptId_name on emp(age,deptId,name)
create index idx_age_name_deptId on emp(age,name,deptId)

"全值匹配我最爱"：查询的字段按照顺序在索引中都可以匹配到。
/*举例：*/下面第一条用了一个，第二条用两个，第三条用三个。
EXPLAIN SELECT SQL_NO_CACHE * FROM emp WHERE emp.age=30  #SQL_NO_CACHE不查询缓存
EXPLAIN SELECT SQL_NO_CACHE * FROM emp WHERE emp.age=30 and deptid=4
EXPLAIN SELECT SQL_NO_CACHE * FROM emp WHERE emp.age=30 and deptid=4 AND emp.name = 'abcd'  


"最佳左前缀法则"：如果索引了多列，要遵守最左前缀法则。指的是查询从索引的最左前列开始并且不跳过索引中的列。
过滤条件要使用索引必须按照索引建立时的顺序，依次满足，一旦跳过某个字段，索引后面的字段都无法被使用。
/*举例：*/如果创建了索引idx_age_deptId_name，第一条语句将用不上，第二条语句将用上age，第三条语句将用上age、deptid。
EXPLAIN SELECT SQL_NO_CACHE * FROM emp WHERE deptid=4 AND emp.name = 'abcd' 
EXPLAIN SELECT SQL_NO_CACHE * FROM emp WHERE emp.age=30 AND emp.name = 'abcd' 
EXPLAIN SELECT SQL_NO_CACHE * FROM emp WHERE emp.age=30 AND deptid=4

"索引列上少计算"，"var引号不能丢"：不在索引列上做任何操作（计算、函数、(自动or手动)类型转换），会导致索引失效而转向全表扫描。
/*举例：*/如果创建了索引idx_age，第一条语句将用上，第二条语句将用不上。如果创建了索引idx_name,第三条语句用的上，第四条语句用不上。
EXPLAIN SELECT SQL_NO_CACHE * FROM emp WHERE age=30;
EXPLAIN SELECT SQL_NO_CACHE * FROM emp WHERE LEFT(age,3)=30;
EXPLAIN SELECT SQL_NO_CACHE * FROM emp WHERE NAME='123';
EXPLAIN SELECT SQL_NO_CACHE * FROM emp WHERE NAME=123; #会自动转换123为varchar类型，不会报错


"范围之后全失效"：索引列上有范围查询时，范围条件右边的列将失效。
/*举例：*/如果创建了索引idx_age_deptId_name，第一条语句将用上age、deptId、name，第二、三条语句将用上age、deptId，因为deptid有范围所以索引deptid后面的name就失效了，如果创建了索引idx_age_name_deptId，则第一、二、三条语句可以全部用上。
explain SELECT SQL_NO_CACHE * FROM emp WHERE emp.age=30 and deptid=5 AND emp.name = 'abcd';
explain SELECT SQL_NO_CACHE * FROM emp WHERE emp.age=30 and deptid<5 AND emp.name = 'abcd';
explain SELECT SQL_NO_CACHE * FROM emp WHERE emp.age=30 and emp.name = 'abcd' AND deptid<5 ;


"LIKE百分写最右"：like以通配符%或_开头索引失效
/*举例：*/如果创建了索引idx_name，第一条语句用不上，第二条语句用得上。
explain SELECT SQL_NO_CACHE * FROM emp WHERE emp.name like '%abc';
explain SELECT SQL_NO_CACHE * FROM emp WHERE emp.name like 'abc%';


"覆盖索引不写*"：即查询列和索引列一致，不要写 select * ，覆盖索引是select的数据列只用从索引中就能够取得，不必读取数据行，换句话说查询列要被所建的索引覆盖
/*举例：*/
explain  SELECT SQL_NO_CACHE * FROM emp WHERE emp.age=30  and deptId=4 and name='XamgXt';
explain  SELECT SQL_NO_CACHE age,deptId,name  FROM emp WHERE emp.age=30  and deptId=4 and name='XamgXt';


"不等空值还有OR"：减少or的使用，使用union all或者union来替代，is not null 不能使用索引,is null可以使用索引,使用不等于(!= 或者<>)的时候索引失效。
/*举例：*/第一条语句是all类型，使用了union第二条语句是ref类型；如果创建了索引idx_age，第三语句用得上，第四语句用不上。如果创建了索引idx_age,第五条语句也用不上。
explain select * from emp where age=30 or name='40';
explain select * from emp where age=30 union all select * from emp where name='40';
explain SELECT SQL_NO_CACHE * FROM emp WHERE emp.age is null;
explain SELECT SQL_NO_CACHE * FROM emp WHERE emp.age is not null;
explain SELECT SQL_NO_CACHE * FROM emp WHERE emp.age<>30;

口诀要领：

全值匹配我最爱，最左前缀要遵守
带头大哥不能死，中间兄弟不能断
索引列上少计算，范围之后全失效
like百分写最右，覆盖索引不写*
不等空值还有or ， 索引影响要注意
VAR引号不能丢，sql优化有诀窍

小练习：

假设index(a,b,c)；

Where语句

1、where a = 3
2、where a = 3 and b = 5
3、where a = 3 and b = 5 and c =4
4、where b = 3 或者 where b = 3 and c = 4 或者 where c = 4
5、where a = 3and c = 5
6、where a = 3 and b > 4 and c = 5
7、where a is null and b is not null
8、where a <> 3
9、where abs(a) =3
10、where a = 3 and b like ‘kk%’ and c = 4
11、where a = 3 and b like ‘%kk’ and c = 4
12、where a = 3 and b like ‘%kk%’ and c = 4
13、where a = 3 and b like ‘k%kk%’ and c = 4

索引是否被使用

1、Y,使用到a
2、Y,使用到a，b
3、Y,使用到a,b,c
4、N
5、使用到a， 但是c不可以，b中间断了
6、使用到a和b，c不能用在范围之后，b断了
7、is null 支持索引 但是is not null 不支持,所以 a 可以使用索引,但是 b不可以使用
8、不能使用索引
9、不能使用 索引
10、Y,使用到a,b,c
11、Y,只用到a
12、Y,只用到a
13、Y,使用到a,b,c