MySQL索引简述

索引定义

索引是帮助MySQL高效获取数据的数据结构。

CREATE TABLE table_name [col_name data type] [unique|fulltext|spatial][index|key] [index_name](col_name[length]) [asc|desc]

索引分类

按数据结构分类可分为：B+tree索引、Hash索引、Full-text索引。 按物理存储分类可分为：聚簇索引、二级索引（辅助索引）。 按字段特性分类可分为：主键索引、普通索引、前缀索引。 按字段个数分类可分为：单列索引、联合索引（复合索引、组合索引）。

索引实现的数据结构分类

• B-Tree索引 索引列的顺序影响者是否使用索引。
• 哈希索引
  • 无法用于排序。
  • 只支持全部匹配，等值比较。
  • 有很多哈希冲突时，效率不太高。
• 全文检索 查找文本中的关键词，类似于搜索引擎做的事情。

物理存储类型索引分类

聚集索引：索引和数据存在一块。

非聚簇索引：索引存放的是主键值，通过主键值能找到数据

MySQL 索引实现

• MyISAM 索引文件和数据文件是分离,非聚集索引。
• InnoDB 叶节点包含了完整的数据记录，聚集索引。根据主键聚集。

具体索引类型介绍

单列索引：

• 普通索引
• 唯一索引
• 前缀索引
  • 对于较大的 Varchar 类型，需要建立索引时必须使用前缀索引，但是不能使用 Group|Order|覆盖查询
• 主键索引 在 InnoDB 引擎中很重要

组合引擎：多个字段上创建的索引，复合索引时遵循最左前缀原则。

• 查询中某个列有范围查询，则其右边的所有列都无法使用查询

MySQL B+ Tree 介绍

为了尽量少的调用磁盘 I/O，需要尽量减少树的层数

B-Tree 的结构定义

• 根节点至少有2个子树。
• 每个非叶子节点由n-1个key和n个指针组成。
• 分支节点至少拥有m/2颗子树，最多拥有m个子树。（除根节点和叶子结点外）
• 所有叶节点具有相同的深度，等于树高 h。
• 每个叶子节点最少包含一个key和两个指针，最多包含2d-1个key和2d个指针。

MySQL B+Tree 基于 B-Tree 做出了下面优化

• 非叶子节点不存储 data，只存储key
• 叶子节点使用双向链表连接，实现 B+ 树的区间查找功能

使用索引的注意事项

• 等值匹配，最佳左前缀原则。
  • 不在索引列上进行任何操作（计算、函数）。例如 left(name,4)
  • 使用!=或<>操作符导致全表扫描。
  • is null ，is not null 也无法使用索引（使用默认值代替）select id from t where num is null。
  • 范围之后全失效
  • 字符串不加单引号索引失效。
  • 使用 or 时，索引失效，可以用union来查询。
• 使用 范围查询
  • 索引不能使用索引中范围条件右边的列。【范围】

使用 like 时可以使用 覆盖索引（只访问索引的查询） 查询索引的字段。

order by 的使用技巧 (排序使用索引字段)

• 单路（读取查询需要的列）排序，双路排序，在 buffer 中对其进行排序。
• 增大 sort_buffer_size 参数的设置
• 增大 max_length_for_sort_data 参数的设置
• 少些 select *

建立索引的注意事项

1. 较频繁的作为查询条件的字段应该创建索引
2. 唯一性太差的字段不适合单独创建索引，即使频繁作为查询条件
3. 更新非常频繁的字段不适合创建索引