# 索引

  • MySQL 中的索引称为 index、key ,索引中的每条数据称为条目(entry)。
    • 用户可以对一张数据表的任意个字段建立任意个单列索引、组合索引,然后在 WHERE 子句中,刻意使用已建立索引的字段作为查询条件,MySQL 便会自动使用索引加速查询,避免全表扫描。
    • 创建索引之后,MySQL 会自动保存、更新索引,不需要用户手动编辑。

# 单列索引

  • 可以对一张数据表的单个列字段建立一个索引,称为单列索引(column index)。
    • 适合只考虑一个字段的查询语句,比如:WHERE name='one'
  • 例:
    CREATE TABLE tb1 (..., INDEX index1(name)); -- 创建表 tb1 时,对字段 name 建立索引,命名为 index1
    
    CREATE INDEX index1 ON tb1(name);           -- 给表添加索引
    ALTER TABLE tb1 ADD  INDEX index1(name);    -- 另一种添加索引的语法
    ALTER TABLE tb1 DROP INDEX index1;          -- 删除表的索引
    
    SHOW INDEX FROM tb1;                        -- 显示表的所有索引
    

# 组合索引

  • 可以对一张数据表的多个列字段建立一个索引,称为组合索引(composite index)。

    • 组合索引适合同时考虑多个字段的查询语句,比如:WHERE id>1 AND name='one'
  • 例:

    ALTER TABLE tb1 ADD INDEX index1(id, name(10), age);
    
  • WHERE 子句中同时查询多个字段时,比如 WHERE a=xx AND b=xx; ,MySQL 会将这些字段组合为一个 group ,比如 (a,b) ,然后检查能否使用 composite index :

    • 如果 group 中存在 composite index 的第一个字段,并且 group 中的所有字段都包含于 composite index ,则可以使用该 composite index 。
  • 假设 tb1 表中只有组合索引 index1(a, b, c) ,分析以下查询语句:

    -- 以下查询会使用 index1
    EXPLAIN SELECT * FROM tb1 WHERE a=xx;
    EXPLAIN SELECT * FROM tb1 WHERE a=xx AND b=xx;
    EXPLAIN SELECT * FROM tb1 WHERE b=xx AND a=xx;  -- AND 两侧的字段顺序不重要, (a,b) 和 (b,a) 两种字段组合的效果一样
    EXPLAIN SELECT * FROM tb1 WHERE a=xx AND c=xx;  -- a、c 不是 index1(a, b, c) 中的连续字段,也可以使用组合索引
    
    -- 以下查询不会使用 index1
    EXPLAIN SELECT * FROM tb1 WHERE b=xx;           -- 字段 b 不是 index1(a, b, c) 中的第一个字段
    EXPLAIN SELECT * FROM tb1 WHERE b=xx AND c=xx;  -- 字段组合 (b, c) 不包含 index1(a, b, c) 中的第一个字段
    EXPLAIN SELECT * FROM tb1 WHERE a=xx OR b=xx;   -- OR 两侧的字段不能组合为一个 group ,不能使用组合索引。虽然 WHERE a=xx 可使用租户索引,但这里 SELECT * 需要回表查询,因此不使用索引,查询类型为 all
    

# 前缀索引

  • 对 char、varchar 等字符串型的字段建立索引时,可以限制只对左侧连续 n 个字符建立索引,称为前缀索引(prefix index)。
    • text、blob 类型的字段可能长度很大,因此只允许使用前缀索引。
  • 例:
    ALTER TABLE tb1 ADD INDEX index1(name(5));
    
  • n 越小,索引体积越小。
    • n 不能过小,避免查询结果的精度过低,查询一条数据却返回多条数据。
    • 一般的 InnoDB 表,index prefix 最大存储长度为 767 bytes 。存储 utf8mb4 字符时每个字符占 4 bytes ,因此 n 最大取值为 191 。
    • 如果查询语句中的值,长度超过前缀索引的 n ,比如 WHERE name LIKE 'one%' 。则先查询索引,找到一批前缀匹配的数据,再从原表读取这些数据的完整值,进行查询。

# 唯一索引

  • 创建索引时,可以加上 unique 关键字,保证索引中每条数据的取值都不同,称为唯一索引(unique index)。
    • 如果用户向数据表写入一条索引值重复的数据,则会报错:Duplicate entry
    • 支持将前缀索引声明为 unique 。
  • 例:
    ALTER TABLE tb1 ADD UNIQUE INDEX index1(name(5));
    

# 全文索引

  • 创建索引时,可加上 FULLTEXT 关键字,对 char、varchar、text 类型的字段建立全文索引(fulltext index),从而提供全文搜索功能。
    • 全文索引属于倒排索引,而不是 B+ tree 等正排索引。
    • 不支持将前缀索引声明为全文索引。
    • MySQL 的全文搜索功能比较简单,比 ElasticSearch 的功能少、性能低。
  • 例:创建全文索引
    ALTER TABLE books ADD FULLTEXT INDEX index1(title, content);
    
    然后进行全文搜索:
    SELECT * FROM books WHERE match(title, content) against('test')
    
    如果有某行数据的 title 或 content 字段包含 test 单词,则返回这行数据。

# 主索引

  • 每个 InnoDB 数据表在创建时,都会自动建立一个聚集索引(clustered index),又称为主索引(primary index),用于将数据表的所有数据行按 B+ tree 等数据结构存储。

    • 如果数据表定义了主键(PRIMARY KEY),则会根据主键建立主索引。
      • 假设主键为 id 字段,如果查询 WHERE id=xx ,则能很快地在主索引中定位这条数据。如果查询 WHERE name=xx ,则只能遍历全部数据,才能找到符合查询条件的数据。
      • 因此建议为每个数据表定义主键,以便建立主索引。
    • 如果数据表未定义主键:
      • 如果存在一个唯一索引,其中所有字段都要求 NOT NULL ,则由第一个这样的唯一索引担任主索引。
      • 如果不存在这样的唯一索引,则在数据表中添加一个隐藏的列,取值为 AUTO_INCREMENT ,用于建立主索引。
  • 每个数据表有且仅有一个主索引。用户可建立其它索引,统称为非聚集索引(non-clustered index)、二级索引(secondary index)。

    • 执行 SHOW INDEX FROM <table>会显示主索引、所有二级索引。
    • 假设数据表的主键为 id 字段,建立了一个单列索引 index1(name) ,则存储结构如下:
      • 主索引的 B+ tree 中,在叶子节点存储数据行的全部字段,可根据 id 值定位每条数据。
      • 二级索引的 B+ tree 中,在叶子节点存储每条数据的 id 字段和 name 字段。
        • 查询 WHERE name=xx 时,先从二级索引找到目标数据,然后根据 id 值,从主索引读取这条数据的完整字段,即回表查询,会先后查询两个索引树。
    • 主索引中,如果几条数据的主键取值连续,则它们在磁盘的存储地址也是连续的,因此访问磁盘时属于顺序读写。
      • 二级索引中,每条数据在磁盘的存储地址是离散的,因此访问磁盘时属于随机读写,读取大量数据时比主索引慢。

# EXPLAIN

  • 如果在执行 SQL 命令时加上 EXPLAIN 关键字作为前缀,则会在执行 SQL 之后,分析查询过程。如下:

  • 例:tb1 表中 id 字段为主键并建立了索引,分析查询过程如下

    mysql> EXPLAIN SELECT * FROM tb1 WHERE id='214540bee2e1dde14eec8bdcae6d3f6d';
    +----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
    | id | SELECT_type | table | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra |
    +----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
    |  1 | SIMPLE      | tb1   | NULL       | const | PRIMARY,id    | PRIMARY | 202     | const | 1    |   100.00 |       |
    +----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
    1 row in set, 1 warning (0.00 sec)
    

    显示的各列含义如下:

    • id :EXPLAIN 显示结果的第几行。
    • partitions :使用了分区表中的哪个分区。
    • type :查询类型。查询速度从高到低依次分为:
      null        # 不必读取表或索引。比如类似 SELECT 1+1 的纯运算;数据表为空;索引字段会被 B+ tree 排序,因此类似 SELECT max(id) FROM tb1 能直接找到最大值、最小值
      system      # 表中只包含一行数据
      const       # 查询取值唯一的索引字段,找到一个匹配结果就立即返回,不必检查剩下的数据。比如 primary 或 unique 索引字段
      eq_ref      # 联表查询时,比如 SELECT * FROM tb1,tb2 WHERE tb1.id=tb.id ,如果 id 是 primary 或 unique 索引字段,则属于 eq_ref 类型
      ref         # 联表查询时,如果 id 不是 primary 或 unique 索引字段,使得 tb1 中的一条数据可能对应 tb2 中的多条数据,则属于 ref 类型
      index_merge # 使用了两个或更多索引,最后合并它们的查询结果
      range       # 查询了索引中某个取值范围的数据。比如 WHERE id>'214540bee2e1dde14eec8bdcae6d3f6d'; ,类似的运算符有 <、>、!=、between、in、is null
      index       # 遍历了索引表,检查了其中所有条数据
      all         # 遍历了原表,即全表扫描
      
    • possible_keys :当前查询可能使用哪些索引。如果 WHERE 子句查询了多个字段,其中某些字段存在于已建立的索引中,则考虑是否使用这些索引。
    • key :实际使用的索引名,可能有多个。
    • key_len :索引表中,每条数据预计读取的长度。必须从头开始读取字节,可能不必读取到最后一个字节。
    • ref :表示跟索引字段相比较的对象。比如一个常量、联表查询时另一张表的字段名。
    • rows :预计要读取多少条数据,才能找到目标数据。
    • Extra 记录一些额外信息,例如:
      Using index       # 只读取了索引表就返回查询结果,不必读取原表,即覆盖查询。这种情况下,查询效率最高
      Using WHERE       # 查询到数据之后,需要经过 WHERE 子句过滤,才返回给用户。这说明查询到了额外的数据,可能需要优化 SQL
      Using temporary   # 查询到数据之后,需要建立中间表来暂存。使用 group by 时通常会这样
      
  • describe 关键字等价于 EXPLAIN ,缩写为 desc 。

  • 可以主动控制是否使用索引,便于测试索引的效果:

    EXPLAIN SELECT * FROM tb1 USE    INDEX(index1) WHERE name='one';  -- 只使用某些索引
    EXPLAIN SELECT * FROM tb1 IGNORE INDEX(index1) WHERE name='one';  -- 忽略某些索引
    EXPLAIN SELECT * FROM tb1 FORCE  INDEX(index1) WHERE name='one';  -- 强制使用某些索引
    

# 使用索引

  • 适合创建索引的情况:

    • 经常在 WHERE 子句中查询的字段。
    • 经常用 ORDER BY 排序的列。
      • 大部分类型的索引采用 B+ tree 存储结构,在存储时根据建立索引的字段的取值进行了排序,因此方便查找一个值、多个值、一个范围内的值。
  • 不适合创建索引的情况:

    • 字段数量不多的表。因为查询时加速效果不明显。
    • 数据表经常增删数据,或者建立索引的字段的值经常修改。因为启用索引的效果是,读操作更快,写操作更慢。
    • 取值容易重复的列。因为索引的查询结果可能包含很多条数据,回表查询的耗时较久。
      • 通常字段的长度越大,取值重复率越低,但是索引体积也会越大,占用更多内存。
      • 例如字段取值为递增编号,或哈希值前几位时,长度小,并且取值几乎不会重复。但是取值缺乏意义,不方便人记忆。
  • 用户执行查询语句时,MySQL 会自动判断是否使用索引,流程如下:

    1. 找到与查询字段匹配的所有索引,记作 possible_keys 。
    2. 比较 possible_keys 中的各个索引,考虑使用哪个索引。
      • 如果预期使用索引的效率比全表扫描更低,则不会使用索引。这是一条大致的原则,不一定准确,建议用 EXPLAIN 检查 SQL 是否使用了索引。
      • 如果一个字段同时匹配多个索引,比如一个单列索引、一个组合索引,则使用开销最小的那个索引。
      • 如果同时查询多个字段,这些字段匹配不同的索引,则可以分别查询这些索引,然后合并查询结果。
  • 假设 tb1 表中 id 字段为主键,其它字段未建立索引,下面分析使用索引的常见情况:

    • 例:
      EXPLAIN SELECT id FROM tb1;   -- 没有 WHERE 查询条件,但 SELECT id 只需要读取主索引的索引键,不需要读取其它字段,因此查询类型为 index
      EXPLAIN SELECT name FROM tb1; -- 查询类型为 all ,因为 SELECT 读取的字段不是索引键
      EXPLAIN SELECT * FROM tb1;    -- 查询类型为 all ,与上一条同理
      
      EXPLAIN SELECT * FROM tb1 WHERE id='214540bee2e1dde14eec8bdcae6d3f6d';  -- 查询类型为 const
      EXPLAIN SELECT * FROM tb1 WHERE id>'214540bee2e1dde14eec8bdcae6d3f6d';  -- 查询类型为 range
      EXPLAIN SELECT * FROM tb1 WHERE id>1; -- 查询类型为 all ,因为 1 与 id 的数据类型不兼容,相当于没有查询条件,预计匹配所有数据,而 SELECT 的字段不支持覆盖索引,此时使用索引不如全表扫描
      EXPLAIN SELECT * FROM tb1 WHERE id is not null;   -- 查询类型为 all ,与上一条同理
      
    • 查询语句像 WHERE id LIKE A 时,如果 A 是字符串常量、且不以 % 开头,才会使用索引。
    • 查询语句像 WHERE ... AND ... 时,比如 WHEREr a=1 AND b=2 ,如果只有字段 a 或 b 建立了索引,则 MySQL 会先查询该字段,筛选出数据之后再执行其它字段的查询条件。如下:
      -- 这两条查询语句的效果一样,查询类型为 range
      EXPLAIN SELECT id FROM tb1 WHERE id>'214540bee2e1dde14eec8bdcae6d3f6d' AND name='one';
      EXPLAIN SELECT id FROM tb1 WHERE name='one' AND id>'214540bee2e1dde14eec8bdcae6d3f6d';
      
    • 查询语句像 WHERE ... OR ... AND ... 时,如果 OR 左侧的查询没有使用索引,进行了一次全表扫描,则右侧的查询也不会使用索引。如果 OR 左侧或右侧没有使用索引,则 AND 之后的查询都不会使用索引。