四、mysql索引优化实战

一、myql索引底层数据结构与算法
二、mysql——Explain工具介绍
三、mysql——索引最佳实践
四、mysql索引优化实战
五、mysql——常见sql语句优化
六、mysql索引优化实战二
七、mysql事务隔离机制和锁机制

存储过程

CREATE TABLE `employees` ( 
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(24) NOT NULL DEFAULT '' COMMENT '姓名', 
`age` int(11) NOT NULL DEFAULT '0' COMMENT '年龄',
`position` varchar(20) NOT NULL DEFAULT '' COMMENT '职位',
`hire_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '入职时间', 
 PRIMARY KEY (`id`), 
KEY `idx_name_age_position` (`name`,`age`,`position`) USING BTREE 
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT='员工记录表'; 

INSERT INTO employees(name,age,position,hire_time) VALUES('LiLei',22,'manager',NOW()); 
INSERT INTO employees(name,age,position,hire_time) VALUES('HanMeimei', 23,'dev',NOW()); 
INSERT INTO employees(name,age,position,hire_time) VALUES('Lucy',23,'dev',NOW()); 

‐‐ 插入一些示例数据 
drop procedure if exists insert_emp; 
delimiter ;; 
create procedure insert_emp() 
begin 
declare i int; 
set i=1; 
while(i<=100000)do 
insert into employees(name,age,position) values(CONCAT('zhuge',i),i,'dev'); 
set i=i+1; 
end while; 
end;; 
delimiter ; 
call insert_emp();

1、联合索引第一个字段用范围因为数据量太大，不会走索引

EXPLAIN SELECT * FROM employees WHERE name > 'LiLei' AND age = 22 AND position ='manager';

通过覆盖索引优化

EXPLAIN SELECT name,age,position FROM employees WHERE name > 'LiLei' AND age = 22 AND position ='manager';

2、in和or在表数据量大的情况下会走索引

EXPLAIN SELECT * FROM employees WHERE name in ('lilei','hanmeimei','lucy') AND age = 22 AND position ='manager';

EXPLAIN SELECT * FROM employees WHERE (name='lilei' or name='hanmeimei' or name='lucy') AND age = 22 AND position ='manager';

3、like KK%这种形式一定会走索引（前提是存在索引）

EXPLAIN SELECT * FROM employees WHERE name like "lile%" AND age = 22 AND position ='manager';

索引下推

概念：mysql5.6之后，在索引遍历的过程中，对索引中包含的所有字段先作判断，过滤掉不符合条件的记录之后再回表，可以有效的减少回表次数。只能用于innodb的二级索引。

trace工具使用

mysql的trace跟踪

mysql深入优化

常见sql深入优化