综合示例
示例数据
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()),('HanMeimei', 23,'dev',NOW()),('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();
#复制表
CREATE TABLE emp1 AS SELECT * FROM employees LIMIT 3;
ALTER TABLE emp1 ADD KEY `idx_name_age_position` (`name`,`age`,`position`)
综合案例
1.联合索引第一个字段用范围不会走索引
EXPLAIN SELECT * FROM employees WHERE name > 'LiLei' AND age = 22 AND position ='manager';
+----+-------------+-----------+------------+------+-----------------------+------+---------+------+--------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-----------+------------+------+-----------------------+------+---------+------+--------+----------+-------------+
| 1 | SIMPLE | employees | NULL | ALL | idx_name_age_position | NULL | NULL | NULL | 100120 | 0.50 | Using where |
+----+-------------+-----------+------------+------+-----------------------+------+---------+------+--------+----------+-------------+
2.强制走索引
#使用force index() 强制使用索引
EXPLAIN SELECT * FROM employees force index(idx_name_age_position) WHERE name > 'LiLei' AND age = 22 AND position ='manager';
+----+-------------+-----------+------------+-------+-----------------------+-----------------------+---------+------+-------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-----------+------------+-------+-----------------------+-----------------------+---------+------+-------+----------+-----------------------+
| 1 | SIMPLE | employees | NULL | range | idx_name_age_position | idx_name_age_position | 98 | NULL | 50060 | 1.00 | Using index condition |
+----+-------------+-----------+------------+-------+-----------------------+-----------------------+---------+------+-------+----------+-----------------------+
SELECT * FROM employees WHERE name > 'LiLei';#100001 rows in set (0.09 sec)
SELECT * FROM employees force index(idx_name_age_position) WHERE name > 'LiLei';#100001 rows in set (0.22 sec)
#强制使用索引查询的时间反而更长
3.覆盖索引优化
EXPLAIN SELECT age,position,name FROM employees WHERE name > 'LiLei' AND age = 22 AND position ='manager';
+----+-------------+-----------+------------+-------+-----------------------+-----------------------+---------+------+-------+----------+--------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-----------+------------+-------+-----------------------+-----------------------+---------+------+-------+----------+--------------------------+
| 1 | SIMPLE | employees | NULL | range | idx_name_age_position | idx_name_age_position | 98 | NULL | 50060 | 1.00 | Using where; Using index |
+----+-------------+-----------+------------+-------+-----------------------+-----------------------+---------+------+-------+----------+--------------------------+
#使用索引中包含的字段就不会回表
4.in和or在表数据量比较大的情况会走索引，在表记录不多的情况下会选择全表扫描
EXPLAIN SELECT * FROM employees WHERE name in ('LiLei','HanMeimei','Zhuge') AND age = 22 AND position ='manager';
+----+-------------+-----------+------------+-------+-----------------------+-----------------------+---------+------+------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-----------+------------+-------+-----------------------+-----------------------+---------+------+------+----------+-----------------------+
| 1 | SIMPLE | employees | NULL | range | idx_name_age_position | idx_name_age_position | 185 | NULL | 3 | 100.00 | Using index condition |
+----+-------------+-----------+------------+-------+-----------------------+-----------------------+---------+------+------+----------+-----------------------+
EXPLAIN SELECT * FROM emp1 WHERE name in ('LiLei','HanMeimei','Zhuge') AND age = 22 AND position ='manager';
+----+-------------+-------+------------+-------+-----------------------+-----------------------+---------+------+------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+-----------------------+-----------------------+---------+------+------+----------+-----------------------+
| 1 | SIMPLE | emp1 | NULL | range | idx_name_age_position | idx_name_age_position | 185 | NULL | 3 | 100.00 | Using index condition |
+----+-------------+-------+------------+-------+-----------------------+-----------------------+---------+------+------+----------+-----------------------+
#8.0.33并没有出现这个情况,都会使用索引
5.like KK% 一般情况都会走索引
EXPLAIN SELECT * FROM employees WHERE name like 'LiLei%' AND age = 22 AND position ='manager';
+----+-------------+-----------+------------+-------+-----------------------+-----------------------+---------+------+------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-----------+------------+-------+-----------------------+-----------------------+---------+------+------+----------+-----------------------+
| 1 | SIMPLE | employees | NULL | range | idx_name_age_position | idx_name_age_position | 185 | NULL | 1 | 5.00 | Using index condition |
+----+-------------+-----------+------------+-------+-----------------------+-----------------------+---------+------+------+----------+-----------------------+
EXPLAIN SELECT * FROM employees WHERE name like 'zhuge%' AND age = 22 AND position ='manager';
+----+-------------+-----------+------------+------+-----------------------+------+---------+------+--------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-----------+------------+------+-----------------------+------+---------+------+--------+----------+-------------+
| 1 | SIMPLE | employees | NULL | ALL | idx_name_age_position | NULL | NULL | NULL | 100120 | 0.50 | Using where |
+----+-------------+-----------+------------+------+-----------------------+------+---------+------+--------+----------+-------------+
#查询条件结构相同,条件值不同,结果集不同,是否使用索引和结果集大小有直接关系
索引下推（Index Condition Pushdown，ICP）
MySQL 5.6引入了索引下推优化，可以在索引遍历过程中，对索引中包含的所有字段先做判断，过滤掉不符合条件的记录之后再回表，可以有效的减少回表次数。
Mysql如何选择合适的索引
trace工具用法
# 开启trace
set session optimizer_trace="enabled=off";
# 执行需要分析的sql
select * from employees where name > 'a' order by position;
# 查看分析结果
SELECT * FROM information_schema.OPTIMIZER_TRACE;
# 关闭trace
set session optimizer_trace="enabled=off";
# 通过计算cost值,决定会不会使用索引
常见sql深入优化
Order by与Group by优化
优化总结
- MySQL支持两种方式的排序filesort和index，Using index是指MySQL扫描索引本身完成排序。index 效率高，filesort效率低。
- 尽量在索引列上完成排序，遵循索引建立（索引创建的顺序）时的最左前缀法则。
- 如果order by的条件不在索引列上，就会产生Using filesort。
- 单路排序 根据主键 id 取出整行，取出所有字段的值，存入 sort_buffer 中
- 双路排序 根据主键 id 取出整行，把排序字段 position 和主键 id 这两个字段放到 sort buffer 中
索引设计原则
1、代码先行，索引后上
2、联合索引尽量覆盖条件
3、不要在小基数字段上建立索引
4、长字符串我们可以采用前缀索引
5、where与order by冲突时优先where
6、基于慢sql查询做优化
索引设计实战
核心思想就是，尽量利用一两个复杂的多字段联合索引，抗下80%以上的查询，然后用一两个辅助索引尽量抗下剩余的一些非典型查询
