介绍

索引（index）是为了帮助 MySQL 高效获取数据而生的一种有序数据结构，避免在查询过程中进行全表扫描，进而提高查询效率。很多资料中都喜欢把索引比作是书的目录。

索引优点：

• 提高查询效率，降低 IO 成本
• 通过索引可以降低数据排序的成本，进而降低 CPU 的消耗

索引缺点：

• 索引要额外占用空间
• 索引可以提升查询效率，但是会相对降低表的更新效率（insert、update、delete）

提示

推荐阅读美团技术团队的MySQL 索引原理及慢查询优化 (opens new window)

索引分类

MySQL 中的索引类型分为：

• 主键索引（PRIMARY）：针对表中的主键创建的索引，默认自动创建，只能有一个。
• 唯一索引（UNIQUE）：避免同一个表中某列数据重复，可以有多个。
• 常规索引：快速定位特定数据，可以有多个。
• 全文索引（FULLTEXT）：查找的是文本中的关键词，而不是比较索引中的值，可以有多个。

在 InnoDB 引擎中，根据索引的存储形式，又可以分为：

• 聚集索引（Clustered Index）：将数据存储与索引放到一起，索引结构的叶子节点爆粗怒了行数据。必须有且只有一个。
• 二级索引（Secondary Index）：将数据与索引分开存储，索引结构的叶子结点关联的是对应的主键。可以存在多个

聚集索引选取规则:

• 如果存在主键，主键索引就是聚集索引
• 如果不存在主键，将使用第一个唯一（UNIQUE）索引作为聚集索引
• 如果表没有主键，或没有合适的唯一索引，则 InnoDB 会自动生成一个 rowid 作为隐藏的聚集索引

索引实现结构主要分为以下几种：

• B+Tree 索引：最常见，大部分引擎都支持
• Hash 索引：底层数据结构使用 Hash 表实现，只有精确匹配索引列的查询才有效，不支持范围查询
• R-Tree：空间索引是 MyISAM 引擎的一个特殊索引类型，主要用于地理空间数据类型，使用较少
• Full-text 索引：是一种通过建立倒排索引快速匹配文档的方式

InnoDB、MyISAM 和 Memory 对各索引结构的支持情况：

索引	InnoDB	MyISAM	Memory
B+tree	支持	支持	支持
Hash	不支持	不支持	支持
R-Tree	不支持	支持	不支持
Full-text	5.6 以后支持	支持	不支持

B+Tree 索引

提示

1. 为什么不用二叉树？
   • 如果主键是顺序插入的，则会形成一个单向链表，查询性能大大降低
   • 数据量大的情况下，层级非常深，检测非常慢
2. 为什么不用红黑树？
   • 红黑树虽然是自平衡二叉树，但是当数据量较大的情况下，也避免不了层级较深的问题
3. 为什么不用 B-Tree？
   • 相对 B-Tree 来说，B+Tree 只有叶子节点才存储数据，非叶子节点只存储索引，所以 B+Tree 相对更矮更胖，可以减少 IO 次数
   • 由于 B+Tree 更矮更胖，所以查询效率更高
   • B+Tree 的所有叶子节点可以构成一个双向有序链表，更有利于排序和范围查询

比较复杂，后续补充。。。

Hash 索引

Hash 索引就是采用一定的 hash 算法，将键值换算成新的 hash 值，映射到对应的槽位上，然后存储在 Hash 表中。

如果两个或两个以上的 Hash 值相同，则会产生 Hash 冲突（也称 Hash 碰撞），此时可以通过在该 Hash 位置创建一个链表来解决，可以对比 Java 中的 HashMap 来理解。

特点：

• Hash 索引只能用于基础比较，比如=、in，不能用于范围查（>、<、between等）
• 无法利用索引排序
• 查询效率高于 B+Tree，如果不存在 Hash 冲突只需要一次检索

语法

创建索引

CREATE [UNIQUE | FULLTEXT | SPATIAL] INDEX index_name
    [index_type]
    ON tbl_name (key_part,...)
    [index_option]
    [algorithm_option | lock_option] ...

key_part: {col_name [(length)] | (expr)} [ASC | DESC]

index_option: {
    KEY_BLOCK_SIZE [=] value
  | index_type
  | WITH PARSER parser_name
  | COMMENT 'string'
  | {VISIBLE | INVISIBLE}
  | ENGINE_ATTRIBUTE [=] 'string'
  | SECONDARY_ENGINE_ATTRIBUTE [=] 'string'
}

index_type:
    USING {BTREE | HASH}

algorithm_option:
    ALGORITHM [=] {DEFAULT | INPLACE | COPY}

lock_option:
    LOCK [=] {DEFAULT | NONE | SHARED | EXCLUSIVE}

查看索引

SHOW INDEX FROM table_name ;

删除索引

DROP INDEX index_name ON table_name;