数据库-->浅谈Mysql索引类型以及不同索引之间的区别

简介

索引是一种数据结构，能够快速检索数据库中的数据

常见Mysql索引类型

  1、FullText 全文索引
  只有在MyISAM上支持
  只有char/varchar/text列上可以创建全文索引
  
  2、Hash 哈希索引
  Hash索引底层是哈希表  
  对于精准查询非常高效，即在‘=’、‘in’条件下高效
  无法通过索引做区间查询，只能扫描全表
  范围查询、排序、组合索引效率不高
  
  3、B+Tree 
  InnoDB引擎默认索引模型
  B+Tree是一种多路平衡二叉树的结构，每次查询都是从根节点root开始，遍历子节点
  可以存储整行数据（存储整行数据的叫主键索引，也叫聚簇索引），也可以是主键值（存储主键值叫非主键索引，也叫非聚簇索引）
  >InnoDB默认对主键建立聚簇索引，如果不指定主键，InnoDB会用一个非空值的索引代替
  >一般来说聚簇索引查询比非聚簇索引更快，因为主键索引树的叶子结点就是我们要查的整行数据。而非主键索引需要回表查询多次。
  >但是有个例外情况，就是索引覆盖的情况。覆盖索引是指一个查询语句的执行从索引中就可以获取，不必从数据表读取。避免了回表操作，
  >减少I/O操作。
  
  4、RTree
  RTree很少使用
  仅支持geometry数据类型
  
  常见面试题：
  为什么mysql选择B+Tree索引而不用Hash索引？
  1、Hash索引适用于等值查询，无法进行区间查询
  2、Hash索引无法用过索引排序
  3、Hash索引不支持联合索引的最左匹配规则
  4、如果有大量重复键值，可能会出现哈希碰撞，到会索引效率低下

索引种类

  1、普通索引：仅仅提高查询速率
  2、唯一索引：提高查询速率+列值唯一（可以null）
  3、主键索引：提高查询速率+列值唯一（不可以null）
  4、组合索引：多个字段组合的索引，效率大于索引合并
  5、全文索引：对文本内容分词搜索

> 组合索引也称联合索引，联合索引遵循最左前缀匹配规则
> 左前缀原则：
> 如abcd_index:abcd联合索引。where条件按照索引建立的顺序来使用，如果中间某列没有值或使用like会导致后面的列不走索引

索引优化

Mysql在5.6时对索引进行了优化

引入索引下推优化，默认开始（关闭命令：SET optimizer_switch = ‘index_condition_pushdown=off’;）
引入索引下推优化，可以在like条件查询下，减少回表次数

查询优化器优化过程：

  1、根据搜索条件，找出所有可能使用的索引
  2、计算全表扫描的代价
  3、计算使用不同索引执行查询的代价
  4、对比各种方案，找出最小代价的那个执行

索引失效场景总结

1. or两边的字段中，如果有一个不是索引字段，而其他条件也不是索引字段，会造成该查询不走索引的情况。
2. where子句中对字段进行值判断会导致引擎放弃使用索引而进行全表扫描
3. 使用%前缀模糊查询会导致索引失效
4. 在where子句中对字段进行表达式操作 会导致索引失效
5. 联合索引来如果存在范围查询，比如between、>、<等条件时，会造成后面的索引字段失效。

持续更新中...

转载于:https://my.oschina.net/qrainly/blog/3075313