MySQL索引

MySQL索引

一、概述

索引（Index）是帮助MySQL高效获取数据的数据结构。在数据之外，数据库系统还维护着满足特定查找算法的数据结构，这种数据结构以某种方式指向数据，从而实现开始的查找数据。

优点：提高数据检索效率，降低数据库IO成本，降低CPU消耗。

缺点：索引需要占用空间，降低插入、更新、修改表的效率。

二、索引结构

索引结构 描述
B+Tree 最常见的索引
Hash 基于Hash表实现，用于精确匹配，不支持范围查询
R-Tree 空间索引是MyISAM的特殊索引类型，用于地理空间数据类型
Full-Tree 通过建立倒排索引，快速匹配文档

B+Tree

二叉树的缺点：顺序插入时，会形成一个链表，查询性能降低，在大数据量的情况下，子树节点层级会较深，检索速度慢。

红黑树：有效解决了顺序插入问题，但是在大数据量的情况下，子树节点层级还是会较深，检索速度慢。

B-Tree：多路平衡查找树

B+Tree：所有数据都会出现在叶子结点，同时形成一个单向链表。

在MySQL中，对B+Tree做了优化，增加了一个指向相邻叶子节点的链表指针，形成了带有顺序指针的B+Tree，从而提高了区间的访问性能。

Hash

哈希索引使用Hash算法将key转为新的hash值，将原始的key映射到新的槽位上，存储在hash表中。产生哈希碰撞的话就通过链表去解决。

特点：

  1. Hash只能用于对等比较，不支持范围查询。
  2. 无法利用索引完成排序操作
  3. 查询效率高，在不出现哈希碰撞的情况下通常只需要一次检索。

在MySQL中，Memory引擎支持hash索引，InnoDB具有自适应的hash功能，hash索引是存储引擎根据B+Tree索引在指定条件下自动构建的。

三、索引分类

分类 含义 特点 关键字
主键索引 对于表中主键创建的索引 默认创建，只能有一个 primary
唯一索引 避免同一张表中列数据值重复 可以有多个 unique
常规索引 快速定位特定数据 可以有多个
全文索引 快速查找文本中的关键字 可以有多个 fulltext

在InnoDB中，索引可以分为下面两类

分类 含义 特点
聚集索引 将数据和索引放到一起，索引结构的叶子结点保存了行数据 必须，只能有一个
二级索引 将数据和索引分开来，索引结构的叶子结点管理的是对应的主键 可以存在多个

聚集索引选取规则：

  • 默认主键为聚集索引。

  • 如果不存在主键，将第一个唯一索引作为聚集索引。

  • 如果没有主键也没有唯一索引，则InnoDB会自动生成一个rowid作为隐藏的经济适用。

二级索引查询：先通过二级索引查到对应的主键，再通过主键回表查询聚集索引保存的行数据

四、索引语法

创建索引

1
create [unique|fulltext]index index_name on table_name (index_col_name...);

查看索引

1
show index table_name;

删除索引

1
drop index index_name ont table_name;