Skip to content

索引

在数据库的使用过程中,查询是占据了绝大部分,即使是更新删除,也得先查到对应的数据,所以如何加快查找速度是数据库要解决的一个重大问题。

想要加快查询的速度,可以往前一步思考:如何存储数据,才能在查询的时候更快?

所以 如何加快查询速度 问题就变成一个 如何组织数据的结构 问题。

索引 就是一种解决方法。换言之:索引,是一种数据结构。

  • 索引的优点:
    • 加快查询速度
  • 索引的缺点:
    • 维护索引需要消耗数据库资源
    • 索引需要占用磁盘空间
    • 当对标的数据进行增删改的时候,因为需要维护索引,速度会受影响

索引分类

MySQL 中的索引分为:

  • InnoDB (主键索引、唯一索引、单值索引、复合索引):
    • 主键索引:一个列被设定为主键后会自动建立主键索引,不允许 NULL;InnoDB为聚簇索引
    • 唯一索引:列的值必须唯一,但允许 NULL
    • 单值索引(普通索引):即一个索引只包含一个列,一个表可以有多个单值索引
    • 复合索引:即一个索引包含多个列
  • MyISAM (主键索引、唯一索引、单值索引、复合索引、全文索引)
    • 全文索引 Full Text:

Tip

单值索引 与 复合索引:

给 name 和 age 分别建立了单值索引,在 WHERE name 或者 WHERE age 的时候会使用上索引。 但是 WHERE name AND age 的时候就用不上索引。

如果给 name 和 age 一起建立了复合索引,就能用上索引。

索引基本操作

主键索引

主键索引在建表时创建主键时自动创建。

如果建表的时候不指定主键,MySQL 会默认创建一个 row_id 作为主键并创建索引。

mysql> CREATE TABLE t_user(
    ->    id varchar(20) primary key, 
    ->    name varchar(20)
    -> );
Query OK, 0 rows affected (0.04 sec)

mysql> show index from t_user;
+--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table  | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| t_user |          0 | PRIMARY  |            1 | id          | A         |           0 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
+--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
1 row in set (0.01 sec)

唯一索引

  • 建表时创建索引:

    mysql> CREATE TABLE t_person(
        ->     id varchar(20),
        ->     name varchar(20),
        -> 
        ->     UNIQUE(name)
        -> );
    Query OK, 0 rows affected (0.05 sec)
    
    mysql> show index from t_person;
    +----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
    | Table    | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
    +----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
    | t_person |          0 | name     |            1 | name        | A         |           0 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
    +----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
    1 row in set (0.01 sec)
    

    即使没有指定索引,MySQL 也会默认为指定了 UNIQUE 约束的列创建唯一索引。

    mysql> CREATE TABLE t_user(
        ->     id varchar(20) UNIQUE,
        ->     name varchar(20)
        -> );
    Query OK, 0 rows affected (0.05 sec)
    
    mysql> show index from t_user;
    +--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
    | Table  | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
    +--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
    | t_user |          0 | id       |            1 | id          | A         |           0 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
    +--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
    1 row in set (0.01 sec)
    
  • 建表后创建索引:

    mysql> CREATE UNIQUE INDEX 索引名 ON 表名(列名);
    

单值索引

  • 建表时创建索引:

    mysql> CREATE TABLE t_user(
        ->     id varchar(20),
        ->     name varchar(20),
        -> 
        ->     KEY(name)
        -> );
    Query OK, 0 rows affected (0.06 sec)
    
    mysql> show index from t_user;
    +--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
    | Table  | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
    +--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
    | t_user |          1 | name     |            1 | name        | A         |           0 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
    +--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
    1 row in set (0.01 sec)
    
    建表时创建的索引无法命名,其索引名与列名一致。

  • 建表后创建索引:

    mysql> CREATE INDEX 索引名 ON 表名(列名);
    
    mysql> CREATE INDEX idx_id ON t_user(id);
    Query OK, 0 rows affected (0.03 sec)
    Records: 0  Duplicates: 0  Warnings: 0
    
    mysql> show index from t_user;
    +--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
    | Table  | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
    +--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
    | t_user |          1 | name     |            1 | name        | A         |           0 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
    | t_user |          1 | idx_id   |            1 | id          | A         |           0 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
    +--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
    2 rows in set (0.01 sec)
    

  • 删除索引:

    mysql> DROP INDEX 索引名 ON 表名;
    
    mysql> DROP INDEX idx_id ON t_user;
    Query OK, 0 rows affected (0.03 sec)
    Records: 0  Duplicates: 0  Warnings: 0
    
    mysql> show index from t_user;
    +--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
    | Table  | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
    +--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
    | t_user |          1 | name     |            1 | name        | A         |           0 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
    +--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
    1 row in set (0.00 sec)
    

复合索引

  • 建表时创建索引:

    mysql> CREATE TABLE t_user(
        ->     id varchar(20),
        ->     name varchar(20),
        ->     age int,
        -> 
        ->    KEY(name, age)
        -> );
    Query OK, 0 rows affected (0.05 sec)
    
    mysql> show index from t_user;
    +--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
    | Table  | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
    +--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
    | t_user |          1 | name     |            1 | name        | A         |           0 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
    | t_user |          1 | name     |            2 | age         | A         |           0 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
    +--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
    2 rows in set (0.01 sec)
    

  • 建表后创建索引:

    CREATE INDEX 索引名称 ON 表名(列名1, 列名2, ...);
    

Tip

命中索引的原则:

给 name 和 age 建立复合索引,在查询时,遵循:

  1. 最左匹配原则:
    • WHERE name, age, bir -> 可以 使用索引
    • WHERE name, bir, age -> 无法 使用索引
    • WHERE bir, age, name -> 无法 使用索引
    • WHERE age, bir -> 无法 使用索引
    • WHERE name, bir -> 无法 使用索引
  2. MySQL 优化器会动态调整字段顺序以顺利使用索引,所以最终是以下情况(只要包含复合索引的字段就能命中):
    • WHERE name, age, bir -> 可以 使用索引
    • WHERE name, bir, age -> 可以 使用索引,会被调整为 name, age, bir
    • WHERE bir, age, name -> 可以 使用索引,会被调整为 name, age, bir
    • WHERE age, bir -> 无法使用索引
    • WHERE name, bir -> 可以 使用索引,会先查询 name

索引结构

MySQL 的索引使用的是 B+树。这是一种自平衡数据结构。

b+tree

  • 记录

一条 记录 由「主键 + 数据 + next指针」组成,多条数据形成链表。

  • 数据页

N 条数据构成一张 数据页。N 具体是多少看记录的大小。InnoDB 一页默认是 16KB,那么一张数据页可以存:

  16K B
-  38 B (File Header) 
-  56 B (Page Header) 
-  26 B (Infimum + supremum) 
-   8 B (File Tailer)
-   ? B (Page Directory)
-   ? B (Free Space)
÷  记录大小
-----------------------
= 一张数据页可以存储的记录条数

  • 页目录

这些页处于 B+树的叶子结点这一层,在叶子结点往上,是 页目录

页目录不存储记录的数据,只存储每一页的第一条记录的 「主键 + 指向记录的指针」。

页目录的大小也是一页默认 16KB。这意味着一张页目录,可以记录很多很多数据页的地址。

  • 顶层页

页目录之上,还可以有更高一层页目录,最顶层的页目录,也就是 B+树的根结点,称为 顶层页

顶层页是常驻内存的,所以查找一个数据时,从内存中找到顶层页,比对主键得到下一层的地址,然后去磁盘中把一整页的数据拖出来。

如果这一页是页目录,就继续找到下一层的地址,如果是数据页就从第一条记录遍历对比主键,直到找到数据返回。

func (p Page)find(id int) (Node, error) {
    var i int
    for i = 0; i < len(p.Data); i++ {  // Data 按 Primary 从小到大排序
        if id >= p.Data[i].Primary {    // 确定下一层地址
            break
        }
    }

    page := loadPage(p.Data[i].Addr)    // 从磁盘中加载整页数据
    if page.Type == dir {   // 如果是页目录
        pageDir.find(id)    // 记录查找
    }

    // 如果是数据页
    node := page.First // 从第一条记录开始
    for node != nil {
        if node.Id != id {  // 遍历记录链表,不是就下一个
            node = node.Next
            continue
        }
        return node, nil    // z找到了返回
    }
    return nil, errors.New("not found!")    // 遍历完还没找到,返回找不到
}
  • B+树的特点是数据都在叶子结点,这样使得 B+树适合范围查找。
  • 在叶子结点的上一层是页目录,页目录不存储数据,这样使得页目录可以存储更多数据页的信息。
  • 顶层页常驻内存,可以减少一次 IO。
  • 基于这些特点,MySQL 才能在索引方面表现的非常出色。

聚簇索引 和 非聚簇索引

  • 聚簇索引:数据和索引放在一起,找到索引就找到了数据
  • 非聚簇索引:数据和索引不放在一起,找到索引后,索引指向数据对应的行,得自行访问。

在 InnoDB 中:

  • 默认为主键创建聚簇索引,即使不指定主键,也会找一个有非空约束和唯一约束的列创建聚簇索引,如果这种列也没有,会创建一个影藏的主键 row_id,然后建立聚簇索引。
  • 在聚簇索引上创建的索引称之为辅助索引,辅助索引中存放的是「列的值 + 主键」,在查询是找到列的值以后,还需要去主键索引上二次查找,拿到数据。

MySQL 中的 InnoDB 引擎处理索引的方式是,将主键组织到一棵 B+树中,行数据就存储在叶子结点上,所以这是聚簇索引,如果使用了 WHERE id = 14 这样的条件查询的时候,则直接去主键索引树上查找,然后获得行数据。

如果使用 WHERE name = 'Boii' 这样的条件查询,则先在辅助索引上找到行,得到 Boii 对应的主键,然后拿着主键去主键索引上二次查找,最终找到整行数据。

而 MySQL 中的 MyISAM 引擎使用的是非聚簇索引,主键索引还是辅助索引都不存储数据,不管是通过 WHERE id = 14 使用主键索引还是通过 WHERE name = 'Boii' 都是直接检索到对应的物理地址,从而访问到整行数据。

  • 为什么 InnoDB 中辅助索引不直接存储地址而是主键?为了数据一直性和可维护性。如果存地址,那主键索引上的数据有变动的时候还得去维护所有辅助索引,而主键是不变的,所以选择存主键。
  • 为什么用 B+树而不是 B树?因为 B+树的数据都在叶子结点上,适合范围查找,B树不是所有数据都在叶子结点上的,所以范围查找的时候很麻烦。
  • 什么时候用 InnoDB,什么时候用 MyISAM?通常用 InnoDB,因为 MyISAM 不支持事务,她的设计比较偏向查询。如果一张表只用来查询,那么可以考虑 MyISAM。