创建高效的索引

索引是存储引擎用于快速找到记录的一种数据结构，类似一本书的目录。创建索引，需要特别关注索引的最左匹配原则。

**索引长度限制：对于innodb，组合索引的长度，跟各索引字段的长度和无关，跟各列长度有关，最大255  -- utf8编码

无法使用索引的场景：

对于复合索引(表的多个列组成的索引)，查询条件没有匹配第一个索引列，即使覆盖了其他索引列，也无法利用索引。因为复合索引是按从左到右依次匹配。从第一个索引列开始，一直到最后一个索引列，中间如果出现字段不能匹配，则后续的索引字段不会再匹配。  （顺序可以不一致，因为优化器会进行优化）

查询条件最左使用通配符%开始

查询条件使用is null 或者is not null

not in <>不使用索引，< <= > >= between in使用索引

in 可能会走索引，但是当范围逐渐变大，就走全表扫描。

查询条件在字段上进行数学运算或者函数运算（包括隐式数据转换）

索引不能包含null值的列，唯一索引允许有空值？？？--可以插入多条null的值，因为唯一约束对null无效，null代表未知

查询条件使用了范围匹配，则后面的索引列失效？？？不懂

限制：

一个联机表上的索引不超过5个

一个索引涉及的字段不超过5个

mysql对check约束支持不完善，不应使用check约束

不适合使用索引的场景：

对于更新量特别大的表，索引的维护成本会特别高

如果其检索需求少，而且对检索效率并没有非常高的要求，不宜创建索引，或者尽量减少索引

对于表数据量较小，通过索引检索速度比不上全表扫描的，不宜使用索引

不应对类型TEXT等大型字段创建索引，否则会占用过多的存储空间

** 对于varchar，如果长度太大，可能建立索引失败

适合使用索引的场景：

当sql语句返回的行数占整个表总行数的比例<=5%，

频繁进行排序order by或者分组group by的列

频繁使用distinct关键字进行查询的列

进行表连接时，在连接字段上宜建立索引

写完sql语句，检查where、order by、group by后面的列，多表关联的列是否已加索引，优先考虑组合索引   --比如 where a = '1' order by b;添加ab的组合索引

另外

复合索引，区分度高的字段应在前

冗余索引：主键字段不应该再建立索引；不应再针对当前索引已覆盖到的前导字段再创建索引

前缀索引：对于超过20个字节的字符串列，如需要建立索引，宜建立前缀索引，缺点是对这个列进行rder by或者group by时，用不到前缀索引

 

最左前缀法则：从最左前列开始不能跳过联合索引中的列。 where条件不按照联合索引的顺序，优化器会做优化的

select * from table where a = 1 and b > 2 and c = 3; --abc是联合索引，但是c没走索引   --查看所有吓退

索引列排序：如果where条件使用了索引，order by就不会使用索引。多列排序，可以考虑使用联合索引。或者where条件和order by使用联合索引

mysql查询：只能使用一个索引

亲测： >号不一定使用范围索引。先将数据排序，当返回的数据比较少时，才会走范围索引。

or：多数情况下索引失效。--多列做or操作，是失效的。即使是联合索引的中两个列做or操作，也是全表扫描，可怕！  --单列的值or还有索引。

前缀索引：占用空间小，会增加额外的记录扫描次数。  --按照wiki说的，先走二级索引，找到一个就查表，再回来走二级索引，继续找下一个符合的~

name字段是比age字段大的 ，那我就建议你创建一个（name,age)的联合索引和一个(age)的单字段索引

 

索引下推

参考：https://www.jianshu.com/p/1265e41fe268

无所有下推的执行流程图

有了

这两个图里面，每一个虚线箭头表示回表一次。

第一个图中，在(name,age)索引里面我特意去掉了age的值，这个过程InnoDB并不会去看age的值，只是按顺序把“name第一个字是’张’”的记录一条条取出来回表。因此，需要回表4次。

他们的区别是，InnoDB在(name,age)索引内部就判断了age是否等于10，对于不等于10的记录，直接判断并跳过。在这个例子中，只需要对ID4、ID5这两条记录回表取数据判断，就只需要回表2次。

查看索引下推开关

面试八股文

https://www.jianshu.com/p/5f864ec1257e