面试问题

SQL优化的一般步骤是什么，怎么看执行计划（explain），如何理解其中各个字段的含义？
如何写sql能够有效的使用到复合索引？
一条sql执行过长的时间，你如何优化，从哪些方面入手？
什么是最左前缀原则？什么是最左匹配原则？

1 哪些情况需要创建索引

主键自动建立唯一索引
频繁作为查询条件的字段
查询中与其他表关联的字段，外键关系建立索引
单键/组合索引的选择问题，高并发下倾向创建组合索引
查询中排序的字段，排序字段通过索引访问大幅提高排序速度
查询中统计或分组字段

2 哪些情况不要创建索引
表记录太少
经常增删改的表
数据重复且分布均匀的表字段，只应该为最经常查询和最经常排序的数据列建立索引（如果某个数据类包含太多的重复数据，建立索引没有太大意义）
频繁更新的字段不适合创建索引（会加重IO负担）
where条件里用不到的字段不创建索引

3 三星索引

4 真的需要新增索引？

1）最左前缀匹配原则，组合索引非常重要的原则，mysql会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配，比如a = 1 and b = 2 and c > 3 and d = 4 如果建立(a,b,c,d)顺序的索引，d是用不到索引的，如果建立(a,b,d,c)的索引则都可以用到，a,b,d的顺序可以任意调整。

2）较频繁作为查询条件的字段才去创建索引

3）更新频繁字段不适合创建索引

4）若是不能有效区分数据的列不适合做索引列(如性别，男女未知，最多也就三种，区分度实在太低)

5）尽量的扩展索引，不要新建索引。比如表中已经有a的索引，现在要加(a,b)的索引，那么只需要修改原来的索引即可。

6）定义有外键的数据列一定要建立索引。

7）对于那些查询中很少涉及的列，重复值比较多的列不要建立索引。

8）对于定义为text、image和bit的数据类型的列不要建立索引。

5 索引规范

单表索引数目不能超过5个

注：聚簇索引造成的存储和查询成本当索引过多时，性能降低很快

根据业务需求设计索引，如果没有查询需求，干嘛要去建索引呢
一个字段的值范围很小，不要设置索引，索引不生效同事浪费插入性能

注：当索引的值就三五个，范围很小时，数据库进行的基本是全表扫描，没必要建索引

如果有幂等性需求，对需要幂等字段或字段组合，设置数据库唯一索引
null值对索引是一大伤害，所以不要让索引的列有null值存在
尽量加索引的字段的数据类型小，也就是能用整数不用varchar能用短的varchar不用长的varchar，不要在text上设置索引
一个索引包含的字段数不能超过3个
尽量在静态数据上建立索引，频繁变动数据建索引，每次db都要考虑是否重建B+树

注：

（1）如账户，userId设置为唯一索引，一个人只能有一个铜板账户，静态数据

（2）收支流水表的userID设置普通索，查询效率高，查询需求

（3）create_time 默认加索引

（4）a,b,c 组合索引，索引构建是用a+b+c进行构建的索引，如果是btree索引，查询的时候 abc|ab|a三种查询条件都会走索引，不需要对a和ab重复创建索引，但是 like ‘%b’|’%bc’|’%c’不会走索引，所以，组合索引，一定使用频率高的放在最左边

6 避免索引失效

不在索引列上做任何操作（计算、函数、(自动or手动)类型转换），会导致索引失效而转向全表扫描
存储引擎不能使用索引中范围条件右边的列
尽量使用覆盖索引(只访问索引的查询(索引列和查询列一致))，减少select
is null ,is not null 也无法使用索引
like “xxxx%” 是可以用到索引的，like “%xxxx” 则不行(like “%xxx%” 同理)。like以通配符开头(‘%abc…’)索引失效会变成全表扫描的操作，
字符串不加单引号索引失效
少用or，用它来连接时会索引失效
<，<=，=，>，>=，BETWEEN，IN 可用到索引，<>，not in ，!= 则不行，会导致全表扫描

7 一般建议

对于单键索引，尽量选择针对当前query过滤性更好的索引
在选择组合索引的时候，当前Query中过滤性最好的字段在索引字段顺序中，位置越靠前越好。
在选择组合索引的时候，尽量选择可以能够包含当前query中的where字句中更多字段的索引
尽可能通过分析统计信息和调整query的写法来达到选择合适索引的目的
少用Hint强制索引

mysql原生

7.1 索引设计