大白话 mysql 之深入浅出索引原理 - 上
大白话 mysql 之深入浅出索引原理 - 下
什么是索引？索引类似书本的目录，查询书中的指定内容时，先在目录上查找，之后可快速定位到内容位置。在数据库中通常通过B树/B+树数据结构实现。
主键索引和非主键索引有什么区别？主键索引树中叶子节点存储的是整行数据，而非主键索引叶子节点上保存的是主键的值。使用非主键索引时，先从非主键索引获取到行对应主键ID，之后再根据id在主键索引树上搜索对应行数据，这个过程也被称为回表。
一般使用什么字段作为主键，为什么？一般使用innodb的自增整数类型作为主键：
因为自增，容易保证主键索引的有序性，同时还能避免新数据中间位置插入时导致的页分裂；
二级索引叶子节点上保存的是主键值，整数类型主键长度较小，二级索引树占用的空间较小。
索引使用场景 where为查询条件字段创建索引，以达到快速过滤指定条件数据的目的。
order by当使用order by将查询结果按某个字段排序时，可考虑为该字段创建索引。没有索引时，会先将查询结果放到内存中进行排序（若内存空间不足，会利用磁盘辅助排序），比较影响查询效率。
索引本身是有序的，可以直接按索引的顺序逐条回表取出数据即可。如果是分页查询，效果更好，这时候只需要取出某个范围的索引对应的数据，而不需要取出所有满足条件的数据排序后再截取返回分页数据。
使用join时，为被驱动表的关联字段创建索引，可以有效提高查询效率。比如select * from t1 straight_join t2 on (t1.a=t2.a) where t1.b = 'xxxx'; t2的字段a上有索引，查询过程会是先从表1中依次取出满足条件的行数据，之后用行数据中的a字段去t2上匹配后将两表字段拼接返回，此时能使用到t2.a的索引，避免了t2全表扫描。
索引覆盖如果select字段+where字段字段列数不太多且查询频繁时，可以考虑为select和where字段创建联合索引，避免查询时回表，提高查询效率。比如select a from t where b = ‘xx’, 创建联合索引(b, a)， 此时扫描索引树后，就已经得到需要查询的字段a了，不需要再回表。需要注意的是联合索引字段的顺序，这个语句无法使用到索引(a, b)。
创建索引需要注意的地方
最左前缀匹配原则，联合索引需要注意索引字段的顺序，mysql 会一直向右匹配直到遇到范围查询 (>、<、between、like) 就停止匹配，比如 a = 1 and b = 2 and c > 3 and d = 4 ,如果建立 (a,b,c,d) 顺序的索引，d 是用不到索引的。
字段是否用到索引的意思是字段是否能利用字段在索引中的有序性进行快速过滤。索引(a,b,c,d), 在索引树上是先按a进行排序，再按b进行排序，以此类推，排序规则类似order by a,b,c,d。上面查询条件中，a定值，b是有序的；b定值，c是有序的；c范围查询，剩下的d是无序的。所以d无法使用到该索引。
基数小，区分度低的不适合创建索引。比如性别，最多基数最多总共就3个，此时索引过滤性能不高，查完索引后还需回表，可能比直接全表扫描效率更低。
更新频繁的字段创建索引时要权衡索引维护成本。
尽量扩展索引，比如已经有a索引，现在要加 (a,b) 的索引，那么只需要修改原来的索引即可。
避免对text大字段创建索引，会导致索引树太大，查询效率不高。如果大字段前n个字符区分度较高，可以考虑创建前缀索引，只索引开始的部分字符，这样可以节约索引空间，提高索引效率。其缺点是不能用于ORDER BY和GROUP BY操作，也不能用于覆盖索引(因为前缀索引树上只有字段的部分内容，需要进行回表)。
什么时候索引会失效？
模糊查询时查询条件以”%”开头无法使用到索引
使用or查询时，只有当所有的查询条件字段都有索引才能使用到，比如a=1 or b = 2,只有当a和b都有索引才能使用到索引。
数据类型出现隐式转换，如varchar不加单引号的时候可能会自动转换为int类型，这个时候索引失效。
在索引列上使用IS NULL或者 IS NOT NULL 时候，索引失效，因为索引不会索引空值。
在索引字段上使用”NOT、 <>、!=、NOT IN “时是不会使用索引的，这时只会进行全表扫描。
对索引字段进行计算操作，函数操作时不会使用索引。
当优化器觉得全表扫描速度比索引速度快的时候不会使用索引。一般出现在全表数据比较少的情况下，这时全表扫描比在非主键索引上查找后再回表速度可能更快。
联合索引时，查找不满足最左匹配规则，无法使用到联合索引。
innodb使用b+树作为索引模型的原因