在看本篇文章时一起跟着操作更有感觉呦～

前期准备

# 创建测试表
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’;
   

   结论:联合索引第一个字段就用范围查找不会走索引，mysql内部可能觉得第一个字段就用范围查找 结果集应该很大，回表效率不高，还不如就全表扫描快。

2. 强制走索引(force index)
   EXPLAIN SELECT * FROM employees force index(idx_name_age_position) WHERE name > ‘LiLei’
   

• 实验:全表扫描 vs 索引 (10w数据)

  #关闭查询缓存 set global query_cache_size=0; set global query_cache_type=0; #执行时间 5.752s SELECT * FROM employees WHERE name > 'LiLei'; # 执行时间6.349 SELECT * FROM employees force index(idx_name_age_position) WHERE name > 'LiLei';

  结论:虽然使用了强制走索引让联合索引第一个字段范围查找也走索引，扫描的行rows看上去也少了点，但是最终查找效率不一定比全表 扫描高，因为回表效率不高

3. 覆盖索引优化

   EXPLAIN SELECT name,age,position FROM employees WHERE name > ‘LiLei’;
   

   覆盖索引不需要回表，效率高。查找时最好不要使用 select * ；

4. in和or在表数据量比较大的情况会走索引，在表记录不多的情况下会选择全表扫描

• # 表总数量10w

  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’) AND age = 22 AND position =’manager’
  

• # copy employees 表 保留三条记录 表总数量 3 条

  EXPLAIN SELECT * FROM employees_copy1 WHERE name in (‘LiLei’,’HanMeimei’,’Lucy’) AND age = 22 AND position =’manager’;
  

  EXPLAIN SELECT * FROM employees_copy1 WHERE (name = ‘LiLei’ or name = ‘HanMeimei’) AND age = 22 AND position =’manager’;
  

5. like KK% 一般情况都会走索引（注意索引长度）

   EXPLAIN SELECT * FROM employees WHERE name like ‘LiLei%’ AND age = 22 AND position =’manager’;
   

   上面结果使用到索引长度为140，说明使用到了索引中到三个字段都使用到了，按照之前的 这条语句索引只会使用到name字段,长度应该为74。

   那为什么会使用到三个字段呢？

   这里有个索引下推的概念 like KK%其实就是用到了索引下推优化。

• 索引下推

  对于辅助的联合索引(name,age,position)，正常情况按照最左前缀原则，SELECT * FROM employees WHERE name like ‘LiLei%’ AND age = 22 AND position =’manager’ 这种情况只会走name字段索引，因为根据name字段过滤完，得到的索引行里的age和 position是无序的，无法很好的利用索引。

  在MySQL5.6之前的版本，这个查询只能在联合索引里匹配到名字是 ‘LiLei’ 开头的索引，然后拿这些索引对应的主键逐个回表，到主键索 引上找出相应的记录，再比对age和position这两个字段的值是否符合。

  MySQL 5.6引入了索引下推优化，可以在索引遍历过程中，对索引中包含的所有字段先做判断，过滤掉不符合条件的记录之后再回表，可 以有效的减少回表次数。使用了索引下推优化后，上面那个查询在联合索引里匹配到名字是 ‘LiLei’ 开头的索引之后，同时还会在索引里过 滤age和position这两个字段，拿着过滤完剩下的索引对应的主键id再回表查整行数据。

  索引下推会减少回表次数，对于innodb引擎的表索引下推只能用于二级索引，innodb的主键索引(聚簇索引)树叶子节点上保存的是全 行数据，所以这个时候索引下推并不会起到减少查询全行数据的效果。

  为什么范围查找Mysql没有用索引下推优化?

  估计应该是Mysql认为范围查找过滤的结果集过大，like KK% 在绝大多数情况来看，过滤后的结果集比较小，所以这里Mysql选择给 like KK% 用了索引下推优化，当然这也不是绝对的，有时like KK% 也不一定就会走索引下推。

mysql的索引选择

传送门——–> MySQL Optimizer 分析（trace 工具）

索引设计原则

1. 代码先行，索引后上

   不知大家一般是怎么给数据表建立索引的，是建完表马上就建立索引吗? 这其实是不对的，一般应该等到主体业务功能开发完毕，把涉及到该表相关sql都要拿出来分析之后再建立 索引。

2. 联合索引尽量覆盖条件

   比如可以设计一个或者两三个联合索引(尽量少建单值索引)，让每一个联合索引都尽量去包含sql语句里的 where、order by、group by的字段，还要确保这些联合索引的字段顺序尽量满足sql查询的最左前缀原 则。

3. 不要在小基数字段上建立索引

   索引基数是指这个字段在表里总共有多少个不同的值，比如一张表总共100万行记录，其中有个性别字段， 其值不是男就是女，那么该字段的基数就是2。 如果对这种小基数字段建立索引的话，还不如全表扫描了，因为你的索引树里就包含男和女两种值，根本没 法进行快速的二分查找，那用索引就没有太大的意义了。 一般建立索引，尽量使用那些基数比较大的字段，就是值比较多的字段，那么才能发挥出B+树快速二分查 找的优势来。

4. 长字符串我们可以采用前缀索引

   尽量对字段类型较小的列设计索引，比如说什么tinyint之类的，因为字段类型较小的话，占用磁盘空间也会 比较小，此时你在搜索的时候性能也会比较好一点。 当然，这个所谓的字段类型小一点的列，也不是绝对的，很多时候你就是要针对varchar(255)这种字段建立 索引，哪怕多占用一些磁盘空间也是有必要的。 对于这种varchar(255)的大字段可能会比较占用磁盘空间，可以稍微优化下，比如针对这个字段的前20个 字符建立索引，就是说，对这个字段里的每个值的前20个字符放在索引树里，类似于 KEY index(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筛选往往可以最快速度筛选出你要的少部分数据，然后做排序的成本可 能会小很多。