Mysql索引

发表于 | 分类于
字数统计: 4,511 | 阅读时长 ≈ 16

前言

  1. 索引分为主键 Primary Key、普通索引 Index、唯一索引 Unique、全文索引 FullText、空间索引 Spatial
  2. Mysql的存储引擎分为InnoDB、MyISAM、Memory
  3. 其中InnoDB和MyISAM普遍使用B+Tree实现索引，但是InnoDB的索引结构就是数据文件本身，MyISAM的索引文件与数据分离
  4. 索引的最左匹配原则和索引使用策略
  5. 使用 Explain分析查询语句🖊

什么是索引

我们知道，MySQL 查询数据是从第一条记录开始依次查找，直到读完整个表或者找到匹配的行。数据库表的数据量越大，MySQL 查询所花费的时间就越多。索引的出现就是为了改善查询性能的。MySQL 官方对索引的定义为：索引（Index）是帮助 MySQL 高效获取数据的数据结构。从本质上来讲，索引是一种以空间换时间的技术。因为索引的建立是需要占用磁盘物理空间的。

索引的目的在于提高查询效率，这个过程可以类比查字典：假如我们需要查询 mysql 这个单词，我们通常会先在字母表里找到字母 m，这样我们就定位到了 m 开头的单词在词典的位置，然后根据定位到的位置从下往下找到 y 字母，最后找到剩下的 sql。索引就相当于字典里的字母表。如果不使用字母表，想要找到mysql 这个单词，我们就需要把字典从头开始翻一遍，直到翻到m 开头的单词在词典的位置，这个过程重复而浪费时间。

通过使用索引技术，MySQL 可以大大减少存储引擎需要扫描的数据量，把磁盘 IO 的随机读取变成顺序读取，从而加快查询效率。

索引分类

主键索引（Primary Key）

特殊的唯一索引，不允许有空值。一个表只能由一个主键。一般在建表同时指定

普通索引（INDEX）

MySQL 允许对两个或两个以上的列上创建索引，我们习惯上称之为联合索引（又叫复合索引，或者组合索引）。实际上，单列索引可以看成联合索引元素数为1的特例。

这是最基本的索引，它没有任何限制。可以在创建表的时候指定，也可以在修改表结构的时候指定。

唯一索引（Unique）

唯一索引列的值必须唯一，但可以为空值。可以是联合索引，但列值的组合必须唯一。

全文索引（FullText）

全文索引是一种特殊类型的索引，通过建立倒排索引，快速匹配文档的方式。它查找的是文本中的关键字，而不是直接比较索引中的值。全文索引跟其它几种索引类型的匹配方式完全不一样。它有许多需要注意的细节，比如停用词、词干和复数、布尔搜索等。另外，只有字段类型为 char、varchar 和 text的字段才能设置全文索引。

空间索引（Spatial）

空间索引用于地理数据存储，无需前缀查询，而是会从所有维度来索引数据。

下表是 MySQL 中常见的存储引擎对索引类型的支持情况：

索引类型 InnoDB MyISAM Memory
PRIMARY KEY 支持 支持 支持
INDEX 支持 支持 支持
Unique 支持 支持 支持
FULLTEXT 支持 支持 不支持
SPATIAL 支持 支持 不支持

索引数据结构类型（Index Type）

前面说到，不同存储引擎的索引的工作方式并不一样，这是因为存储引擎底层使用的数据结构及算法不一样。在 MySQL 中，某些存储引擎允许在创建索引时指定索引的数据结构类型，下表是常见存储引擎支持的索引的数据结构类型：

存储引擎 允许的数据结构类型
InnoDB BTREE（聚集索引实现：数据文件本身就是索引文件）
MyISAM BTREE（非聚集实现：通过索引文件定位数据文件）
Memory HASH（默认）, BTREE

需要注意的是，用户不能为全文索引和空间索引指定数据结构类型：全文索引的实现取决于存储引擎，而空间索引被实现为 RTREE 数据结构。

MyISAM 索引实现

MyISAM 引擎使用 B+Tree 作为索引结构，叶节点的 data 域存放的是数据记录的地址。下图是 MyISAM 索引的原理图：

图片描述

这里设表一共有三列，假设我们以 Col1 为主键，则上图是一个 MyISAM 表的主索引（Primary key）示意。可以看出 MyISAM 的索引文件仅仅保存数据记录的地址。在 MyISAM 中，主索引和辅助索引（Secondary key）在结构上没有任何区别，只是主索引要求 key 是唯一的，而辅助索引的 key 可以重复。如果我们在 Col2 上建立一个辅助索引，则此索引的结构如下图所示：

图片描述

同样也是一颗 B+Tree，data 域保存数据记录的地址。因此，MyISAM 中索引检索的算法为首先按照 B+Tree 搜索算法搜索索引，如果指定的 Key 存在，则取出其 data 域的值，然后以 data 域的值为地址，读取相应数据记录。

MyISAM 的索引方式也叫做“非聚集”的，之所以这么称呼是为了与 InnoDB 的聚集索引区分。

InnoDB 索引实现

虽然 InnoDB 也使用 B+Tree 作为索引结构，但具体实现方式却与 MyISAM 截然不同。

第一个重大区别是 InnoDB 的数据文件本身就是索引文件。从上文知道，MyISAM 索引文件和数据文件是分离的，索引文件仅保存数据记录的地址。而在 InnoDB 中，表数据文件本身就是按 B+Tree 组织的一个索引结构，这棵树的叶节点 data 域保存了完整的数据记录。这个索引的 key 是数据表的主键，因此 InnoDB 表数据文件本身就是主索引。

图片描述

上图是 InnoDB 主索引（同时也是数据文件）的示意图，可以看到叶节点包含了完整的数据记录。这种索引叫做聚集索引。因为 InnoDB 的数据文件本身要按主键聚集，所以 InnoDB 要求表必须有主键（MyISAM 可以没有），如果没有显式指定，则 MySQL 系统会自动选择一个可以唯一标识数据记录的列作为主键，如果不存在这种列，则 MySQL 自动为 InnoDB 表生成一个隐含字段作为主键，这个字段长度为6个字节，类型为长整形。

第二个与 MyISAM 索引的不同是 InnoDB 的辅助索引 data 域存储相应记录主键的值而不是地址。换句话说，InnoDB 的所有辅助索引都引用主键作为 data 域。例如，下图为定义在 Col3 上的一个辅助索引：

图片描述

这里以英文字符的ASCII码作为比较准则。聚集索引这种实现方式使得按主键的搜索十分高效，但是辅助索引搜索需要检索两遍索引：首先检索辅助索引获得主键，然后用主键到主索引中检索获得记录。

索引操作

Mysql索引查询语法：

1
SHOW INDEX FROM table_name

创建

1
CREATE INDEX index_name ON table_name(col_name)

Alter

1
ALTER TABLE table_name ADD INDEX index_name (col_name)

Mysql建表语法：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name
    [index_type]
    ON tbl_name (index_col_name,...)
    [index_option]
    [algorithm_option | lock_option] ...

# 下面是创建说明
index_col_name:
    col_name [(length)] [ASC | DESC]

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

index_type:
    USING {BTREE | HASH}

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

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

Create

删除

Drop

1
DROP INDEX index_name ON table_name

Alter

1
ALTER TABLE table_name DROP INDEX index_name

索引使用策略

1. 最左前缀原理*

MySQL 对联合索引的顺序是敏感的。例如我们建立了一个 ( a, b, c, d ) 顺序的索引，假如现在需要执行这样一个 a = 1 and b = 2 and c > 3 and d = 4 WHERE 查询语句：

  • MySQL 会一直向右匹配直到遇到范围查询（>、<、between、like）就停止索引的匹配。因此 d 字段是没有办法使用索引的。
  • 查询条件用到了索引中列的精确匹配，但是中间某个条件未提供，此时也是没有办法使用索引的。

例如我们有一个 (a,b,c) 顺序的索引，假如现在需要执行一个 a = 1 and c = 2 的 WHERE 查询，因为 b 未提供，所以查询只用到了索引的第一列，而后面的 c 虽然也在索引中，但是由于 b 不存在而无法和左前缀连接，因此 c 字段也是没有办法使用索引的。

有一点需要注意，在精确匹配（= 和 IN）中索引的顺序可以是任意的，MySQL 的查询优化器会帮用户优化成索引可以识别的形式。比如对于 (a,b,c) 顺序的索引来说，a = 1 and b = 2 and c = 3 和 a = 1 and c = 3 and b = 2、c = 3 and a = 1 and b = 2 等 SQL 语句效果都是一样的。

前缀匹配是可以用到索引的（like ‘xxx%’）

2. 选择区分度高的列作为索引

尽量选择区分度高的列作为索引，区分度的公式是 count(distinct col)/count(*)，表示字段不重复的比例，比例越大我们扫描的记录数越少，唯一键的区分度是1，而一些状态、性别字段可能在大数据面前区分度就是0，那可能有人会问，这个比例有什么经验值吗？使用场景不同，这个值也很难确定，一般需要 join 的字段我们都要求是0.1以上，即平均1条扫描10条记录。

3. 更新非常频繁的字段不适合创建索引

维护索引是需要空间和时间成本的，对于更新非常频繁的字段，例如通过该字段的查询并不是很多，可能几个小时或是更长才会执行一次，但是更新反而比查询更频繁，此时创建索引就失去了意义，反而降低了 MySQL 的性能。

4. 尽量的扩展索引，不要新建索引

尽量的扩展索引，不要新建索引。比如表中已经有 a 的索引，现在要加 (a,b) 的索引，那么只需要修改原来的索引即可。

5. 数据量少的表不建立索引

数据量小的表最好不要使用索引，因为由于数据较少，可能查询全部数据花费的时间比遍历索引的时间还要短，索引就可能不会产生优化效果；

InnoDB的主键选择与插入优化

  • 在使用InnoDB存储引擎时，如果没有特别的需要，请永远使用一个与业务无关的自增字段作为主键。

相比业务，从数据库索引优化角度看，使用InnoDB引擎而不使用自增主键绝对是一个糟糕的主意。

上文讨论过InnoDB的索引实现，InnoDB使用聚集索引，数据记录本身被存于主索引（一颗B+Tree）的叶子节点上。这就要求同一个叶子节点内（大小为一个内存页或磁盘页）的各条数据记录按主键顺序存放，因此每当有一条新的记录插入时，MySQL会根据其主键将其插入适当的节点和位置，如果页面达到装载因子（InnoDB默认为15/16），则开辟一个新的页（节点）。

如果表使用自增主键，那么每次插入新的记录，记录就会顺序添加到当前索引节点的后续位置，当一页写满，就会自动开辟一个新的页。如下图所示：

img

这样就会形成一个紧凑的索引结构，近似顺序填满。由于每次插入时也不需要移动已有数据，因此效率很高，也不会增加很多开销在维护索引上。

如果使用非自增主键（如果身份证号或学号等），由于每次插入主键的值近似于随机，因此每次新纪录都要被插到现有索引页得中间某个位置：

img

此时MySQL不得不为了将新记录插到合适位置而移动数据，甚至目标页面可能已经被回写到磁盘上而从缓存中清掉，此时又要从磁盘上读回来，这增加了很多开销，同时频繁的移动、分页操作造成了大量的碎片，得到了不够紧凑的索引结构，后续不得不通过OPTIMIZE TABLE来重建表并优化填充页面。

因此，只要可以，请尽量在InnoDB上采用自增字段做主键。

索引的缺点

  1. 创建索引和维护索引要耗费时间，并且随着数据量的增加所耗费的时间也会增加

  2. 索引也需要占空间，我们知道数据表中的数据也会有最大上线设置的，如果我们有大量的索引，索引文件可能会比数据文件更快达到上线值

  3. 当对表中的数据进行增加、删除、修改时，索引也需要动态的维护，降低了数据的维护速度

用Explain分析查询

格式：

1
EXPLAIN select * from table_name

分析字段：

img

Explain解释

id：SELECT识别符。这是SELECT的查询序列号,也就是一条语句中，该select是第几次出现。

select_type：所使用的SELECT查询类型

  • SIMPLE： 表示此查询不包含 UNION 查询或子查询
  • PRIMARY： 表示此查询是最外层的查询
  • SUBQUERY： 子查询中的第一个 SELECT
  • UNION： 表示此查询是 UNION 的第二或随后的查询
  • DEPENDENT UNION： UNION 中的第二个或后面的查询语句, 取决于外面的查询
  • UNION RESULT, UNION 的结果
  • DEPENDENT SUBQUERY: 子查询中的第一个 SELECT, 取决于外面的查询. 即子查询依赖于外层查询的结果.
  • DERIVED：衍生，表示导出表的SELECT（FROM子句的子查询）

table：被查询数据表的名字，按被读取的先后顺序排列

type：通过 type 字段，我们判断此次查询是 全表扫描 还是 索引扫描等。

  • system: 表中只有一条数据， 这个类型是特殊的 const 类型。

  • const: 针对主键或唯一索引的等值查询扫描，最多只返回一行数据。 const 查询速度非常快， 因为它仅仅读取一次即可。例如下面的这个查询，它使用了主键索引，因此 type 就是 const 类型的：explain select * from user_info where id = 2；

  • eq_ref: 此类型通常出现在多表的 join 查询，表示对于前表的每一个结果，都只能匹配到后表的一行结果。并且查询的比较操作通常是 =，查询效率较高。例如：explain select * from user_info, order_info where user_info.id = order_info.user_id;

  • ref: 此类型通常出现在多表的 join 查询，针对于非唯一或非主键索引，或者是使用了 最左前缀 规则索引的查询。例如下面这个例子中， 就使用到了 ref 类型的查询：explain select * from user_info, order_info where user_info.id = order_info.user_id AND order_info.user_id = 5

  • range: 表示使用索引范围查询，通过索引字段范围获取表中部分数据记录。这个类型通常出现在 =, <>, >, >=, <, <=, IS NULL, <=>, BETWEEN, IN() 操作中。例如下面的例子就是一个范围查询：explain select * from user_info where id between 2 and 8；

  • index: 表示全索引扫描(full index scan)，和 ALL 类型类似，只不过 ALL 类型是全表扫描，而 index 类型则仅仅扫描所有的索引， 而不扫描数据。index 类型通常出现在：所要查询的数据直接在索引树中就可以获取到, 而不需要扫描数据。当是这种情况时，Extra 字段 会显示 Using index。

  • ALL: 表示全表扫描，这个类型的查询是性能最差的查询之一。通常来说， 我们的查询不应该出现 ALL 类型的查询，因为这样的查询在数据量大的情况下，对数据库的性能是巨大的灾难。 如一个查询是 ALL 类型查询， 那么一般来说可以对相应的字段添加索引来避免。

    通常来说, 不同的 type 类型的性能关系如下:

    ALL < index < range ~ index_merge < ref < eq_ref < const < system

    ALL 类型因为是全表扫描， 因此在相同的查询条件下，它是速度最慢的。而 index 类型的查询虽然不是全表扫描，但是它扫描了所有的索引，因此比 ALL 类型的稍快.后面的几种类型都是利用了索引来查询数据，因此可以过滤部分或大部分数据，因此查询效率就比较高了。

possible_keys：MySQL在搜索数据记录时可以选用的各个索引

key：实际选用的索引

key_len：表示查询优化器使用了索引的字节数（也就是使用的索引个数），这个字段可以评估组合索引是否完全被使用。

ref：这个表示显示索引的哪一列被使用了 。也可能是const常量。

rows：MySQL在估算本次sql要查找到结果集需要扫描读取的数据行数 。原则上越少越好

extra：提供了与关联操作有关的信息，没有则什么都不写。　　　

以上内容来自网络，参考文章：