Mysql索引优化实战1
示例表：
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.联合索引，如果第一个字段如果是范围查询不走索引，mysql认为数据量大，宁愿走all。范围查询放在中间就走了。如果是>=又走了后面的字段（前提是不能在第一个字段）。
explain select * from employees where name > 'LiLei' and age = 22 and position = 'manager'
explain select * from employees force index（idx_name_age_position） where name > 'LiLei' and age = 22 and position = 'manager'
‐‐关闭查询缓存 set global query_cache_size=0; set global query_cache_type=0; ‐‐ 执行时间0.333s SELECT * FROM employees WHERE name > 'LiLei'; ‐‐ 执行时间0.444s 7SELECT * FROM employees force index(idx_name_age_position) WHERE name > 'LiLei';
EXPLAIN SELECT * FROM employees WHERE name in ('LiLei','HanMeimei','Lucy') AND age = 22 AND position='manager';索引下推：mysql 5.6以后引入的优化， like查询开头的字段都用到了索引下推。
概念：对于辅助的联合索引(name,age,position)，正常情况按照最左前缀原则，比如：
SELECT * FROM employees WHERE name like 'LiLei%' AND age = 22 AND position ='manager' 这种情况只会走name字段索引
，因为根据name字段过滤完，得到的索引行里的age和position是无序的，无法很好的利用索引。
在Mysql5.6之前，它会在联合索引里根据name在辅助索引里查询到id的结果集，逐个回表至聚集索引，再去过滤age及position两个字段。
在5.6之后，它会每过滤一条name条件的，同时去比较下age及position两个字段，符合的话就就把id找出来，再回表，可以有效的减少回表次数。
trace工具用法：
mysql> set session optimizer_trace="enabled=on",end_markers_in_json=on; ‐‐开启trace mysql> select * from employees where name > 'a' order by position; mysql> SELECT * FROM information_schema.OPTIMIZER_TRACE;
查看trace字段：
Case1、age走没走索引？要看Extra，Extra的值是 Using index condition，所以是走索引的。key_len只是统计where后以及order by 前的字段。
Case2：position不走索引，因为中间跳过了age，position不是有序的。
case3：order by 后面 是age，position，也走了索引。
Case4：索引的创建顺序为name,age,position，但是排序的时候age和position颠倒位置了。
Case5：因为age是常量，Order by后面相当于没有age，所以走索引。
Case6：虽然排序的字段列与索引顺序一样，且order by默认升序，这里position desc变成了降序，导致与索引的排序方式不同，从而产生Using filesort。所以MySql8.0以下，比如我们用的5.6，5.7是不走索引的。Mysql8以上版本有降序索引可以支持该种查询方式、
Case7：对于排序来说，多个相等条件也是范围查询，后面两个字段是无序的。
Case8:可能是数据量太大了，它帮你使用了文件排序，走了全表扫描，没用索引。
可以用覆盖索引优化
1.Mysql 支持两种方式的排序filesort和index，Using index是指Mysql扫描索引本身完成排序。index效率高，filesort效率低。
2.order by 满足两种情况会使用Using index。
1order by 语句使用索引最左前列。
2使用where子句与order by 子句条件列组合满足索引最左前列。
3.尽量在索引列上完成排序，遵循索引建立（索引创建的顺序）时的最左前缀法则
4.如果order by 的条件不在索引列上，就会产生Using filesort。
5.能用覆盖索引尽量用覆盖索引
6.group by 与order by很类似，其实质是先排序后分组，遵照索引创建顺序的最左前缀法则。对于group by的优化如果不需要排序的可以加上order by null.注意，where高于having，能写再where中的限定条件就不要去havin限定了。
filesort文件排序方式
单路排序：是一次性取出满足条件行的所有字段，然后在sort buffer中进行排序；用trace工具可以看到sort_mode信息里显示< sort_key, additional_fields >或者< sort_key,packed_additional_fields >
双路排序（又叫回表排序模式）：是首先根据相应的条件取出相应的排序字段和可以直接定位行数据的行 ID，然后在 sort buffer 中进行排序，排序完后需要再次取回其它需要的字段；用trace工具可以看到sort_mode信息里显示< sort_key, rowid >
MySQL 通过比较系统变量 max_length_for_sort_data(默认1024字节) 的大小和需要查询的字段总大小来判断使用哪种排序模式。
如果 字段的总长度小于max_length_for_sort_data ，那么使用 单路排序模式；
如果 字段的总长度大于max_length_for_sort_data ，那么使用 双路排序模∙式。
1、代码先行，索引后上索引。
不知大家一般是怎么给数据表建立索引的，是建完表马上就建立索引吗？
这其实是不对的，一般应该等到主体业务功能开发完毕，把涉及到该表相关sql都要拿出来分析之后再建立
2、联合索引尽量覆盖条件
比如可以设计一个或者两三个联合索引(尽量少建单值索引)，让每一个联合索引都尽量去包含sql语句里的
where、order by、group by的字段，还要确保这些联合索引的字段顺序尽量满足sql查询的最左前缀原则。
3、不要在小基数字段上建立索引
索引基数是指这个字段在表里总共有多少个不同的值，比如一张表总共100万行记录，其中有个性别字段其值不是男就是女，那么该字段的基数就是2。
如果对这种小基数字段建立索引的话，还不如全表扫描了，因为你的索引树里就包含男和女两种值，根本没法进行快速的二分查找，那用索引就没有太大的意义了。
一般建立索引，尽量使用那些基数比较大的字段，就是值比较多的字段，那么才能发挥出B+树快速二分查找的优势来。
4、长字符串我们可以采用前缀索引
尽量对字段类型较小的列设计索引，比如说什么tinyint之类的，因为字段类型较小的话，占用磁盘空间也会比较小，此时你在搜索的时候性能也会比较好一点。
当然，这个所谓的字段类型小一点的列，也不是绝对的，很多时候你就是要针对varchar(255)这种字段建立索引，哪怕多占用一些磁盘空间也是有必要的。
对于这种varchar(255)的大字段可能会比较占用磁盘空间，可以稍微优化下，比如针对这个字段的前20个字符建立索引，就是说，对这个字段里的每个值的前20个字符放在索引树里，类似于 KEYindex(name(20),age,position)。
此时你在where条件里搜索的时候，如果是根据name字段来搜索，那么此时就会先到索引树里根据name字段的前20个字符去搜索，定位到之后前20个字符的前缀匹配的部分数据之后，再回到聚簇索引提取出来完整的name字段值进行比对。
但是假如你要是order by name，那么此时你的name因为在索引树里仅仅包含了前20个字符，所以这个排序是没法用上索引的， group by也是同理。所以这里大家要对前缀索引有一个了解。
5、where与order by冲突时优先where
在where和order by出现索引设计冲突时，到底是针对where去设计索引，还是针对order by设计索引？到底是让where去用上索引，还是让order by用上索引?
一般这种时候往往都是让where条件去使用索引来快速筛选出来一部分指定的数据，接着再进行排序。
因为大多数情况基于索引进行where筛选往往可以最快速度筛选出你要的少部分数据，然后做排序的成本可能会小很多。
6、基于慢sql查询做优化
可以根据监控后台的一些慢sql，针对这些慢sql查询做特定的索引优化。
如果读多写少：多加索引，可以三到四个。如果读多写多，建1个就行。
本文原创，转载必追究版权。
