语句在后面

单表例题
EXPLAIN select SQL_NO_CACHE * FROM emp WHERE emp.age = 30
create INDEX idx_age on emp(age); #创建索引

EXPLAIN SELECT SQL_NO_CACHE * FROM emp where emp.age =30 AND deptId = 4 ;
create INDEX idx_age_deptid ON emp(age,deptId);

explain select SQL_NO_CACHE * from emp where emp.age = 30 and deptId = 4 AND name = ‘abcd’ ;
create INDEX idx_age_deptid_name ON emp(age,deptId,NAME);

全值匹配我最爱

create INDEX idx_age_deptid_name ON emp(age,deptId,’name’);
explain select SQL_NO_CACHE * from emp where deptId = 4 and emp.age = 30 AND name = ‘abcd’ ; #条件顺序调换了,但是仍然使用了索引

explain select SQL_NO_CACHE * from emp where emp.age = 30 AND name = ‘abcd’ ; #命中了age

explain select SQL_NO_CACHE * from emp where emp.deptid=4 AND name = ‘abcd’ ;#没有命中

最佳左前缀法则 :

如果索引了多列，要遵守最左前缀法则。指的是查询从索引的最左前列开始并且不跳过索引中的列。

这两条sql哪种写法更好
① : 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’ #不要在在索引列上做任何操作（计算、函数、(自动or手动)类型转换），会导致索引失效而转向全表扫描

create INDEX idx_name ON emp(NAME); #添加名字的索引 , 没有添加索引都差不多,添加了索引①更快

(原因 : 给字段加函数,计算,加类型转换,索引会失效 等)

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); #应该把范围索引建在最后面

EXPLAIN SELECT SQL_NO_CACHE * FROM emp WHERE emp.NAME <> ‘abc’ ;

create INDEX idx_name on emp(NAME); #创建索引没有使用成功

mysql 在使用不等于(!= 或者<>)的时候无法使用索引会导致全表扫描

① : 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); #创建索引①成功使用,而②没有成功使用 ,

is not null 也无法使用索引,但是is null是可以使用索引的

EXPLAIN SELECT SQL_NO_CACHE * FROM emp WHERE emp.name LIKE ‘%abc%’;

create INDEX idx_name on emp(name);#索引没有使用,

like以通配符开头(‘%abc…’)mysql索引失效会变成全表扫描的操作

EXPLAIN SELECT SQL_NO_CACHE * FROM emp WHERE emp.name = 123 ;

create INDEX idx_name on emp(name);

# 字符串不加单引号索引失效

小练习 :

假设index(a,b,c)

#建表

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

DELIMITER ;
#执行操作
CALL insert_dept(10000);

CALL insert_emp(100000,500000);

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

show INDEX FROM t_emp;

查询表中的索引
SELECT index_name FROM information_schema.STATISTICS WHERE table_name=’emp’ AND table_schema=’ssm’
AND index_name <>’PRIMARY’ AND seq_in_index = 1

批量删除某个表全部索引的函数

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 (‘ssm’,’emp’);