在数据库查询出现性能瓶颈时，索引是提升查询性能的绝佳手段。它能轻松缩小一个数量级的查询时间。

索引的原理

索引常用的数据结构：

  • 哈希表
  • 二叉树
  • 多叉树
  • 其他
哈希表索引

哈希表索引的索引存在一些局限性：

  • 只能进行“=” “IN” 和 “<=>” 这类等值查询，而不能进行区间查询，对于需要排序、比较、区间查找并不适合
  • 哈希碰撞会降低系统的查询效率
BTree索引

利用B-树或者B+树来读数据表中的指定属性建立索引。

  • B-树的所有节点都存储数据，B+树只在叶子节点存储数据
  • MyISAM、InnoDB等都使用B+树来实现索引

BTree数据是排序的，因此支持区间，比较等查询方式。

位图索引

属性的可选值是有限的。 位图索引基本原理是向量化和位逻辑运算

位图索引只适合建立在不常发生变动的属性上

索引分析

为了及时发现索引失效的情况，可以在检索SQL语句前增加EXPLAIN或者DESCRIBE关键字来分析索引的生效过程。

EXPLAIN给出结果各字段含义如下：

  • id：此次查询到唯一性标识

  • select_type：查询操作的类型，如不含UNION的简单查询操作、最外层的查询操作、子查询操作、UNION查询操作等

    • simple：简单查询
    • primary：复杂查询中最外层的select
    • subquery：子查询
    • derived：派生表、临时表
    • union
    • union result

  • table：查询所涉及到表名称

  • partitions：查询所涉及到表分区，如果表未分区，为null

  • type：连接类型，这个字段十分重要，依次从最优到最差为：system，const，eq_ref，ref，fulltext，ref_or_null，unique_subquery，index_subquery，range，index_merge，index，ALL

    • null:MySQL不用访问表或者索引，直接就能够得到结果
    • system：表中只有一行数据或者是空表，且只能用于myisam和memory表。如果是Innodb引擎表，type列在这个情况通常都是all或者index
    • const：根据主键或者唯一二级索引列与常数进行等值匹配, 表最多有一个匹配行，该行在查询开始时读取。const表非常快，因为它们只读取一次。当根据主键索引一条记录时，便是const类型的查询
    • eq_ref：在关联查询时，如果被驱动表是通过主键或者唯一二级索引列等值匹配的方式进行访问的，则对该被驱动表的访问方法就是eq_ref（唯一索引扫描）
    • ref：通过普通的二级索引列与常量进行等值匹配时来查询某个表（非唯一索引扫描）
    • fulltext：连接使用全文索引执行的
    • ref_or_null：类似于ref，但是mysql会额外搜索包含空值的行
    • unique_subquery：用于where中的in形式子查询，子查询返回不重复值唯一值
    • index_subquery：用于in形式子查询使用到了辅助索引或者in常数列表，子查询可能返回重复值，可以使用索引将子查询去重。
    • range：在一定范围内查询索引表（索引范围扫描）
    • index_merge：表示查询使用了两个以上的索引，最后取交集或者并集，常见and ，or的条件使用了不同的索引，官方排序这个在ref_or_null之后，但是实际上由于要读取所个索引，性能可能大部分时间都不如range
    • index：对索引表（聚簇索引、非聚簇索引都算）进行整体遍历，虽然用到了索引但效率仍然较低（索引全扫描）
    • ALL：全表扫描，完全没有用到任何索引，效率最低的一种情况

  • possible_keys：该查询操作可能利用的索引

  • key：最终查询操作使用的索引

    • null：没有使用索引

  • key_len：根据这个值可以判断索引的使用情况，特别是在组合索引的时候，判断该索引有多少部分被使用到非常重要，值越大索引的效果越好——因为值越大说明索引被利用的越充分

  • ref：它显示了与key字段中的索引进行比对的是哪些列或者常量

  • rows：估算出结果集行数，表示MySQL根据表统计信息及索引选用情况，估算的找到所需的记录所需要读取的行数。从优化 SQL 语句的角度来说，这个值通常越小越好。

  • filtered：经查询条件中的where选项过滤后，剩余的数据占总数据量的百分比的估计值，注意，这时一个估计值，通过存储引擎从硬盘加载数据到服务层时，受限于内存空间，有可能只能加载一部分数据。filtered 字段显示的值是：已加载数据 / 全部数据 的百分比

  • Extra：额外信息

    • Using index：使用覆盖索引，即只通过索引搜索就能拿到结果（没有回表查询）
    • Using where：where子句中除索引字段 还包含索引之外的字段（进行了回表查询）
    • Using index condition：查询到列不完全被索引覆盖，如：like ‘%abc’左侧字符不确定
    • Using temporary：表示使用了临时表存储中间结果。临时表可以是内存临时表和磁盘临时表，常见于排序order by和分组查询group by，出现这种情况需要进行优化
    • Using filesort：当语句中包含 order by 操作，而且无法利用索引完成的排序操作称为“文件排序” 。这种情况需要考虑使用索引优化。
    • Select tables optimized away：在没有GROUPBY子句的情况下，基于索引优化MIN/MAX操作或者对于MyISAM存储引擎优化COUNT(*)操作，不必等到执行阶段再进行计算，查询执行计划生成的阶段即完成优化
    • Using join buffer：表明使用了连接缓存

总结：

  • type列：判断查询的类型，得保证查询达到range级别，最好达到ref，否则需要索引优化
  • Extra列 ：判断select后面的列是否完全被索引覆盖，需要保证为Using index或者Using index condition，否则需要索引优化

索引的使用

索引物理存储分类：

  • 聚集索引：聚集索引就是以主键创建的索引，在叶子节点存储的是表中的数据
  • 非聚集索引：非聚集索引就是以非主键创建的索引，在叶子节点存储的是主键和索引列

索引逻辑分类：

  • 唯一索引：索引列中的值必须是唯一的，但是允许为空值
  • 主键索引：如果数据表中的某个属性被定义为主键，则会自动为其创建索引，主键索引必须是唯一索引,不允许有空值
  • 联合索引：多个字段创建的索引，使用时遵循最左前缀原则
  • 普通索引：MySQL中基本索引类型，允许空值和重复值
  • 全文索引：建立全文索引的属性，会对该属性值进行分词，并对分词结果分别建立索引。

覆盖索引：在查询的数据列里面，不需要回表去查，直接从索引列就能取到想要的结果。换句话说，你SQL用到的索引列数据，覆盖了查询结果的列，就算是覆盖索引了

索引失效情况
  • 计算与类型转化引发的索引失效
  • 模糊匹配，如like通配符等引发的索引失效
  • 索引字段上使用(!=, <>, not)会导致索引失效
  • 索引字段上使用is NULL， is not NULL，=NULL，!=NULL导致索引失效，因为null值无法通过索引查找，可以通过设置默认值方式避免出现null值
  • 索引字段上使用in、not in会导致索引失效，如果是连续值，请使用between代替in
  • 查询条件包含or语句，会导致索引失效
  • 使用mysql内置函数，会导致索引失效
  • 字符串类型的where条件，需放在引号内，否则导致索引失效
  • 联合索引失效：联合索引遵循最左前缀原理，如果不是，索引失效
  • mysql如果判断全表扫描比使用索引快，则不使用索引

索引的利弊

利：

  • 快速检索数据

弊：

  • 索引占用一定的存储空间
  • 每次插入，更新时都需要更新索引，这可能拖慢数据库写操作

本质上索引是利用空间换时间。