Tyrone 的博客

MySQL 索引总结

发表于 更新于 分类于
本文字数： 7.3k 阅读时长 ≈ 7 分钟
MySQL 索引总结

什么是 索引？

索引（ MySQL 中也叫做键(key)） 是存储引擎用于快速找到记录的一种数据结构。本文主要学习的是基于 InnoDB 存储引擎。
索引对于良好的性能非常关键，对于较大的数据量来说，索引的创建愈发重要。

索引的类型

索引有很多种类型，可以为不同的场景提供更好的性能。

从存储结构上分

  • Btree 索引 （B+tree、B-tree）
  • full-index 全文索引
  • RTree

从应用层次上划分

  • 普通索引：即一个索引只包含单个列，一个表可以有多个单列索引
  • 唯一索引：索引列的值必须唯一，但允许有空值
  • 复合索引：一个索引包含多个列

从表记录的存储形式上来划分

  • 聚簇索引：表记录的排列顺序和索引的排列顺序一致
  • 非聚簇索引：表记录的排列顺序和索引的排列顺序不一致

聚簇索引和非聚簇索引

在 InnoDB 中，表数据文件本身就是按照 B+Tree 组织的一个索引结构。
聚簇索引就是按照每张表的主键构造一颗 B+ 树，同时叶子节点中存放的就是整张表的行记录数据，也将聚簇索引的叶子节点称为数据页。
一般建表会用一个自增主键做聚簇索引，没有的话 MySQL 会默认创建
在日常的开发任务中，根据实际情况自行添加的索引都是辅助索引，辅助索引就是一个为了需要找主键索引的二级索引，先找到主键索引再通过主键索引找到数据。

聚簇索引

聚簇索引并不是一种单独的索引类型，而是一种数据存储方式。

聚簇索引就是按照每张表的主键构建一颗 B+ 树，同时叶子节点存放的就是整张表的行记录数据，也将聚簇索引的叶子节点称为数据页。这个特性决定了索引组织表中的数据也是索引的一部分，每张表只能拥有一个聚簇索引。
direct

聚簇索引的优点

  • 数据访问更快，因为**聚簇索引是将索引和数据保存在同一个 B+ 树中，因此从聚簇索引中获取数据比非聚簇索引更快。
  • 聚簇索引对于主键的排序查找和范围查找速度更快。
  • 使用覆盖索引扫描的查询可以直接使用页节点中的主键值

聚簇索引的缺点

  • 插入速度严重依赖插入顺序，按照主键的顺序插入是最快的方式。一般定义一个自增的 ID 列作为主键，但是如果不是按照主键顺序加载数据，那么在jia
  • 更新主键的代价很高，因为将会导致被更新的行移动，一般对于 InnoDB 表，定义主键不可更新
  • 二级索引访问需要两次索引查找，第一次找到主键值，第二次根据主键值找到行数据。

非聚簇索引

在聚簇索引之上创建的索引称之为辅助索引，辅助索引访问数据总是需要二次查找。辅助索引叶子节点存放的不再是行的物理位置，而是主键值。
通过辅助索引找到主键值，再通过主键值找到数据行的数据页，再通过数据页中的 Page Directory 找到数据行。

InnoDB 辅助索引的叶子节点并不包含行记录的全部数据，叶子节点包含键值外，还包含了相应行数据的聚簇索引键。

B-Tree 索引

特点：每一个叶子节点都包含指向下一个叶子节点的指针，从而方便叶子节点的范围遍历

B-Tree 通常意味着所有的值都是按照顺序存储的，并且每一个叶子页到根的距离相同。
img.png

B-Tree 索引检索数据的原理

存储引擎不再需要进行全表扫描来获取需要的数据，取而代之的是从索引的根节点开始进行搜索。
根节点的槽中存放了指向子节点的指针，存储引擎根据这些指针向下层查找。这些指针实际上定义了子节点页中值的上限和下限，通过比较节点页中的值和要查找的值可以找到合适的指针进入下层子节点。
最终存储引擎的查找只会有两种情况：1. 找到对应的值。2. 记录不存在。

叶子节点指针指向的是被索引的数据，而不是其他节点页。

树的深度和表的大小直接相关

B-Tree 对索引列是顺序组织存储的，所以很适合查找范围数据。

B-Tree 索引适用于全值键、键值范围或键前缀查找。键前缀查找只适用于最左前缀查找

1
2
3
4
5
6
7
8
9
10
11
12
13
CREATE TABLE People (
    last_name varchar(50) not null DEFAULT '' comment '姓',
    first_name varchar(50) not null DEFAULT '' comment '名',
    dob date not null DEFAULT '' comment '出生日期',
    gender enum(0, 1) not null comment '',
    key(last_name, first_name, dob)
);

INSERT INTO People values ('Cuba', 'Allen', '1960-01-01'),
('Angelina', 'Astaire', '1980-03-04'),
('Julla', 'Barrymore', '2000-05-16'),
('Christian', 'Akroyd', '1958-12-07'),
('Akroyd', 'Klrsten', '1978-11-02')

direct

B-Tree 索引的查询类型

explain 分析 SQL 的执行计划
需要重点关注 type、rows、filtered、extra。
type 由上至下，效率越来越高

  • ALL 全表扫描
  • index 索引全扫描
  • range 索引范围扫描，常用语<,<=,>,=>,between, in like 等操作
  • ref 使用非唯一索引扫描或唯一索引前缀扫描，返回单条记录，常出现在关联查询中
  • eq_ref 类似 ref,区别在于使用的是唯一索引，使用主键的关联查询
  • const/system 单条记录，系统会把匹配行中的其它列作为常数处理，如主键或唯一索引查询
  • null MySQL 不访问任何表或索引，直接返回结果

虽然上至下，效率越来越高，但是根据cost模型，假设有两个索引idx1(a, b, c),idx2(a, c)，SQL为select * from t where a = 1 and b in (1, 2) order by c;如果走idx1，那么是type为range，如果走idx2，那么type是ref；当需要扫描的行数，使用idx2大约是idx1的5倍以上时，会用idx1，否则会用idx2

Extra

  • Using filesort：MySQL需要额外的一次传递，以找出如何按排序顺序检索行。通过根据联接类型浏览所有行并为所有匹配WHERE子句的行保存排序关键字和行的指针来完成排序。然后关键字被排序，并按排序顺序检索行。
  • Using temporary：使用了临时表保存中间结果，性能特别差，需要重点优化
  • Using index：表示相应的 select 操作中使用了覆盖索引（Coveing Index）,避免访问了表的数据行，效率不错！如果同时出现 using where，意味着无法直接通过索引查找来查询到符合条件的数据。
  • Using index condition：MySQL5.6之后新增的ICP，using index condtion就是使用了ICP（索引下推），在存储引擎层进行数据过滤，而不是在服务层过滤，利用索引现有的数据减少回表的数据。
  • 全值匹配
    **全值匹配指的是和索引中所有列进行匹配**
    例如:
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    explain select * from People
    where last_name='Cuba' and first_name='Allen' and dob='1960-01-01'\G;
    ```  
    output:
    ```markdown
    id            | 1
    select_type   | SIMPLE
    table         | People
    partitions    | <null>
    type          | ref
    possible_keys | last_name
    key           | last_name
    key_len       | 107
    ref           | const,const,const
    rows          | 1
    filtered      | 100.0
    Extra         | <null>

这里的 type 为 ref, ref 表示使用非唯一索引扫描或唯一索引前缀扫描，返回单条记录，常出现在关联查询中

  • 匹配索引最左前缀
    查找所有姓为 Allen 的人，例如 explain select * from People where last_name='Cuba'\G;
    output:

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    id            | 1
    select_type   | SIMPLE
    table         | People
    partitions    | <null>
    type          | ref
    possible_keys | last_name
    key           | last_name
    key_len       | 52
    ref           | const
    rows          | 2
    filtered      | 100.0
    Extra         | <null>

  • 匹配列前缀
    查找只匹配某一列的值的开头部分，只使用到了索引的第一列。
    例如 explain select * from People where last_name like 'C%'\G;

output:

1
2
3
4
5
6
7
8
9
10
11
12
id            | 1
select_type   | SIMPLE
table         | People
partitions    | <null>
type          | range
possible_keys | last_name
key           | last_name
key_len       | 52
ref           | <null>
rows          | 2
filtered      | 100.0
Extra         | Using index condition

这里的 type 为 range, key 为 last_name, range 表示索引范围扫描，常用语<,<=,>,=>,between, in like 等操作

  • 匹配范围值
    可用于查找姓在 Angelina 和 Julla 之间的人
    例如：

    1
    2
    select last_name from People
    where last_name between 'Angelina' and 'Julla';

    output:

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    id            | 1
    select_type   | SIMPLE
    table         | People
    partitions    | <null>
    type          | range
    possible_keys | last_name
    key           | last_name
    key_len       | 52
    ref           | <null>
    rows          | 3
    filtered      | 100.0
    Extra         | Using where; Using index

    这里 key 使用为 last_name, type 为 range 范围查询。

  • 精确匹配某一列并范围匹配另一列, 即第一列全匹配，第二列 first_name 范围匹配
    例如：

    1
    2
    explain select * from People
    where last_name='Cuba' and first_name between 'Allen' and 'zxxx';
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    id            | 1
    select_type   | SIMPLE
    table         | People
    partitions    | <null>
    type          | range
    possible_keys | last_name
    key           | last_name
    key_len       | 104
    ref           | <null>
    rows          | 2
    filtered      | 100.0
    Extra         | Using index condition

    这里索引用到了 last_name, type 为 range 范围查询。

  • 只访问索引的查询
    B-Tree 通常可以支持 “只访问索引的查询”，即查询只需要访问索引，无需访问数据行。
    例如：

    1
    2
    select last_name from People
    where last_name='Allen';

  • 索引也可以用到 order by 排序操作，order by 子句满足前面列出的几种查询类型。

B-Tree 索引的限制

  • 需要满足最左原则，即若不是按照索引的最左列开始查找的话，无法使用索引；
    例如：
    1
    2
    3
    4
    5
    explain select * from People
    where first_name='Allen'\G;

    explain select * from People
    where dob='1960-01-01'\G;
    output:
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    id            | 1
    select_type   | SIMPLE
    table         | People
    partitions    | <null>
    type          | ALL
    possible_keys | <null>
    key           | <null>
    key_len       | <null>
    ref           | <null>
    rows          | 7
    filtered      | 14.29
    Extra         | Using where
  • 不能跳过索引中的列。例如：
    1
    2
    explain select * from People
    where last_name='Cuba' and dob='1960-01-01'\G;
    output:
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    id            | 1
    select_type   | SIMPLE
    table         | People
    partitions    | <null>
    type          | ref
    possible_keys | last_name
    key           | last_name
    key_len       | 52
    ref           | const
    rows          | 2
    filtered      | 14.29
    Extra         | Using index condition
    这样只是使用到了索引 last_name，
    1
    2
    explain select * from People
    where last_name='Cuba' and first_name='Allen' and dob='1960-01-01'\G;
1
2
3
4
5
6
7
8
9
10
11
12
id            | 1
select_type   | SIMPLE
table         | People
partitions    | <null>
type          | ref
possible_keys | last_name
key           | last_name
key_len       | 107
ref           | const,const,const
rows          | 1
filtered      | 100.0
Extra         | <null>

通过 ref 的 const, const, const 可以知道，前者只用到了 last_name index，而后者都用到了
这里可以看到 type 类型为 ALL 表示全表扫描；

  • 若查询中有某个列的范围查询，则右边所有列都无法使用索引优化查找。
    例如：
    1
    2
    explain select * from People
    where last_name='Cuba' and first_name like 'A%' and dob='1960-01-01'\G;
    output:
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    id            | 1
    select_type   | SIMPLE
    table         | People
    partitions    | <null>
    type          | range
    possible_keys | last_name
    key           | last_name
    key_len       | 107
    ref           | <null>
    rows          | 1
    filtered      | 14.29
    Extra         | Using index condition

    索引的优点

  • 索引大大减少了服务器需要扫描的数据量
  • 索引可以帮助服务器避免排序和临时表
  • 索引可以将随机 I/O 变为顺序 I/O
  • 索引对于 InnoDB (对索引支持行级锁) 非常重要，因为它可以让查询锁更少的元祖。
    在 MySQL 5.1 和更新的版本中，InnoDB 可以在服务器端过滤掉行后就释放锁，但在早期的 MySQL 版本中，InnoDB 直到事务提交时才会解锁。对不需要的元组的加锁，会增加锁的开销，降低并发性。
    InnoDB 仅对需要访问的元组加锁，而索引能够减少 InnoDB 访问的元组数。但是只有在存储引擎层过滤掉那些不需要的数据才能达到这种目的。
  • 关于 InnoDB、索引和锁：InnoDB在二级索引上使用共享锁（读锁），但是访问主键索引需要排他锁（写锁）

缺点

  • 由于索引的存在，在进行`INSERT、UPDATE、DELETE 时，需要进行数据的更新修改，会降低更新表的速度。MySQL 不仅要保存数据，还要保存索引文件。
  • 建立索引会占用磁盘空间的索引文件。当数据量比较大的表及组合索引较多时，索引文件会膨胀的很快
  • 如果某个数据列包含许多重复的内容，建立索引就没有实际效果。
  • 对于数据量非常小的表，全表扫描更高效。

综上：建立一个好的索引是非常考究的，需要根据业务和实际场景不断的优化。

索引的使用

普通索引

这类索引是最基本的索引，普通索引的唯一任务就是加快对数据的访问速度。
这类索引应该只为最经常出现在查询条件（where column = ）或 排序条件（order by column）中的数据列创建索引。

  • 创建
    1
    ALTER TABLE table_name ADD INDEX index_name (column)

    唯一索引

    普通索引允许被索引的数据列包含重复的值，而唯一索引列的值必须唯一，但允许有空值如果是组合索引，则列值的组合必须唯一
1
CREATE UNIQUE INDEX idx_xxx ON my_table (column)

主键索引

它是一种特殊的唯一索引，不允许有空值。一个表只能有一个主键。

一般在创建主键的时候，会同时创建。

组合索引

组合索引也可称为联合索引，为表的多个列创建索引；
为了进一步榨取 MySQL 的效率，就要考虑建立组合索引。

例如：

1
ALTER TABLE my_table ADD INDEX idx_name_city_age (name, city, age)

建立这样的组合索引，其实是相当于分别建立了下面三组组合索引：
username, city, age
username, city
username

参考

「 高性能的索引 」

-------------THANKS FOR READING-------------