MySQL索引

概述

用来加快查询的技术很多,其中最重要的是索引。通常索引能够快速提高查询速度。如果不适用索引,MySQL 必须从第一条记录开始然后读完整个表直到找出相关的行。表越大,花费的时间越多。但也不全是这样。

什么是索引

MySQL 官方对索引的定义为:索引(Index)是帮助 MySQL 高效获取数据的数据结构。提取句子主干,就可以得到索引的本质:索引是数据结构。

数据库查询是数据库的最主要功能之一。我们都希望查询数据的速度能尽可能的快,因此数据库系统的设计者会从查询算法的角度进行优化。最基本的查询算法当然是顺序查找(linear search),这种复杂度为 O(n) 的算法在数据量很大时显然是糟糕的,好在计算机科学的发展提供了很多更优秀的查找算法,例如二分查找(binary search)、二叉树查找(binary tree search)等。

如果稍微分析一下会发现,每种查找算法都只能应用于特定的数据结构之上,例如二分查找要求被检索数据有序,而二叉树查找只能应用于二叉查找树上,但是数据本身的组织结构不可能完全满足各种数据结构(例如,理论上不可能同时将两列都按顺序进行组织),所以,在数据之外,数据库系统还维护着满足特定查找算法的数据结构,这些数据结构以某种方式引用(指向)数据,这样就可以在这些数据结构上实现高级查找算法。这种数据结构,就是索引。

索引分类

分类

索引是在 MySQL 的存储引擎层中实现的,而不是在服务层实现的。所以每种存储引擎的索引都不一定完全相同,也不是所有的存储引擎都支持所有的索引类型。MySQL 目前提供了以下 4 种索引:

  • B-Tree 索引: 最常见的索引类型,大部分引擎都支持 B 树索引。
  • HASH 索引: 只有 Memory 引擎支持,使用场景简单。
  • R-Tree 索引(空间索引): 空间索引是 MyISAM 的一种特殊索引类型,主要用于地理空间数据类型。
  • Full-text (全文索引): 全文索引也是 MyISAM 的一种特殊索引类型,主要用于全文索引,InnoDB 从 MYSQL5.6 版本提供对全文索引的支持。

引擎支持

索引 MyISAM引擎 InnoDB引擎 Memory引擎
B-Tree 索引 支持 支持 支持
HASH 索引 不支持 不支持 支持
R-Tree 索引 支持 不支持 不支持
Full-text 索引 不支持 暂不支持 不支持

B-TREE索引类型

普通索引

这是最基本的索引类型,而且它没有唯一性之类的限制。普通索引可以通过以下几种方式创建:

  1. 创建索引:

    CREATE INDEX 索引名 ON 表名(列名1,列名2,...);
  2. 修改表:

    ALTER TABLE 表名ADD INDEX 索引名 (列名1,列名2,...);
  3. 创建表时指定索引:

    CREATE TABLE 表名 ( [...], INDEX 索引名 (列名1,列名 2,...) );

UNIQUE索引

表示唯一的,不允许重复的索引,如果该字段信息保证不会重复例如身份证号用作索引时,可设置为 unique:

  1. 创建索引:

    CREATE UNIQUE INDEX 索引名 ON 表名(列的列表);
  2. 修改表:

    ALTER TABLE 表名ADD UNIQUE 索引名 (列的列表);
  3. 创建表时指定索引:

    CREATE TABLE 表名( [...], UNIQUE 索引名 (列的列表) );

主键:PRIMARY KEY索引

主键是一种唯一性索引,但它必须指定为 “PRIMARY KEY”。

  1. 主键一般在创建表的时候指定:

    CREATE TABLE 表名( [...], PRIMARY KEY (列的列表) );
  2. 但是,我们也可以通过修改表的方式加入主键:

    ALTER TABLE 表名ADD PRIMARY KEY (列的列表);

每个表只能有一个主键。 (主键相当于聚合索引,是查找最快的索引)

索引操作

设置索引

在执行 CREATE TABLE 语句时可以创建索引,也可以单独用 CREATE INDEX 或 ALTER TABLE 来为表增加索引。

ALTER TABLE

ALTER TABLE 用来创建普通索引、UNIQUE 索引或 PRIMARY KEY 索引,语法如下:

# 普通索引 ALTER TABLE table_name ADD INDEX index_name (column_list) # 唯一索引 ALTER TABLE table_name ADD UNIQUE (column_list) # 主键索引 ALTER TABLE table_name ADD PRIMARY KEY (column_list)

CREATE INDEX

CREATE INDEX 可对表增加普通索引或 UNIQUE 索引,语法如下:

# 普通索引 CREATE INDEX index_name ON table_name (column_list) # 唯一索引 CREATE UNIQUE INDEX index_name ON table_name (column_list)

删除索引

可利用 ALTER TABLE 或 DROP INDEX 语句来删除索引。类似于 CREATE INDEX 语句,DROP INDEX 可以在 ALTER TABLE 内部作为一条语句处理,语法如下:

DROP INDEX index_name ON talbe_name ALTER TABLE table_name DROP INDEX index_name ALTER TABLE table_name DROP PRIMARY KEY

其中,前两条语句是等价的,删除掉 table_name 中的索引 index_name。

第 3 条语句只在删除 PRIMARY KEY 索引时使用,因为一个表只可能有一个 PRIMARY KEY 索引,因此不需要指定索引名。如果没有创建 PRIMARY KEY 索引,但表具有一个或多个 UNIQUE 索引,则 MySQL 将删除第一个 UNIQUE 索引。

如果从表中删除了某列,则索引会受到影响。对于多列组合的索引,如果删除其中的某列,则该列也会从索引中删除。如果删除组成索引的所有列,则整个索引将被删除。

查看索引

mysql> show index from tblname; mysql> show keys from tblname;

具体显示的字段解释如下:

字段 描述
Table 表的名称
Non_unique 如果索引不能包括重复词,则为0。如果可以,则为1
Key_name 索引的名称
Seq_in_index 索引中的列序列号,从1开始
Column_name 列名称
Collation 列以什么方式存储在索引中。在 MySQL 中,有值 ‘A’(升序)或 NULL(无分类)。
Cardinality 索引中唯一值的数目的估计值。通过运行 ANALYZE TABLE 或 myisamchk -a 可以更新。基数根据被存储为整数的统计数据来计数,所以即使对于小型表,该值也没有必要是精确的。基数越大,当进行联合时,MySQL 使用该索引的机会就越大。
Sub_part 如果列只是被部分地编入索引,则为被编入索引的字符的数目。如果整列被编入索引,则为 NULL。
Packed 指示关键字如何被压缩。如果没有被压缩,则为 NULL。
Null 如果列含有 NULL,则含有 YES。如果没有,则该列含有 NO。
Index_type 用过的索引方法(BTREE, FULLTEXT, HASH, RTREE)。
Comment 更多评注。

索引优缺点

优点

  • 索引大大减小了服务器需要扫描的数据量。
  • 索引可以帮助服务器避免排序和临时表。
  • 索引可以将随机 IO 变成顺序 IO。
  • 索引对于 InnoDB(对索引支持行级锁)非常重要,因为它可以让查询锁更少的元组。在 MySQL5.1 和更新的版本中,InnoDB 可以在服务器端过滤掉行后就释放锁,但在早期的 MySQL 版本中,InnoDB 直到事务提交时才会解锁。对不需要的元组的加锁,会增加锁的开销,降低并发性。 InnoDB 仅对需要访问的元组加锁,而索引能够减少 InnoDB 访问的元组数。但是只有在存储引擎层过滤掉那些不需要的数据才能达到这种目的。一旦索引不允许 InnoDB 那样做(即索引达不到过滤的目的),MySQL 服务器只能对 InnoDB 返回的数据进行 WHERE 操作,此时,已经无法避免对那些元组加锁了。如果查询不能使用索引,MySQL 会进行全表扫描,并锁住每一个元组,不管是否真正需要。
  • 关于 InnoDB、索引和锁:InnoDB 在二级索引上使用共享锁(读锁),但访问主键索引需要排他锁(写锁)。

缺点

  • 虽然索引大大提高了查询速度,同时却会降低更新表的速度,如对表进行 INSERT、UPDATE 和 DELETE。因为更新表时,MySQL 不仅要保存数据,还要保存索引文件。
  • 建立索引会占用磁盘空间的索引文件。一般情况这个问题不太严重,但如果你在一个大表上创建了多种组合索引,索引文件的会膨胀很快。
  • 如果某个数据列包含许多重复的内容,为它建立索引就没有太大的实际效果。
  • 对于非常小的表,大部分情况下简单的全表扫描更高效。

索引只是提高效率的一个因素,如果你的MySQL有大数据量的表,就需要花时间研究建立最优秀的索引,或优化查询语句。因此应该只为最经常查询和最经常排序的数据列建立索引。

MySQL 里同一个数据表里的索引总数限制为 16 个。

索引使用

索引选择原则

  1. 较频繁的作为查询条件的字段应该创建索引。
  2. 唯一性太差的字段不适合单独创建索引,即使频繁作为查询条件。
  3. 更新非常频繁的字段不适合创建索引。
  4. 不会出现在 WHERE 子句中的字段不该创建索引。

说明

  1. 性能优化过程中,选择在哪个列上创建索引是最非常重要的。可以考虑使用索引的主要有两种类型的列:在 where 子句中出现的列,在 join 子句中出现的列,而不是在 SELECT 关键字后选择列表的列;
  2. 索引列的基数越大,索引的效果越好。例如,存放出生日期的列具有不同的值,很容易区分行,而用来记录性别的列,只有 “M” 和 “F”,则对此进行索引没有多大用处,因此不管搜索哪个值,都会得出大约一半的行,( 见索引选择性注意事项对选择性解释;)
  3. 使用短索引,如果对字符串列进行索引,应该指定一个前缀长度,可节省大量索引空间,提升查询速度;
  4. 利用最左前缀

注意事项

既然索引可以加快查询速度,那么是不是只要是查询语句需要,就建上索引?答案是否定的。因为索引虽然加快了查询速度,但索引也是有代价的:索引文件本身要消耗存储空间,同时索引会加重插入、删除和修改记录时的负担,另外,MySQL 在运行时也要消耗资源维护索引,因此索引并不是越多越好。

一般两种情况下不建议建索引:

  1. 表记录比较少,例如一两千条甚至只有几百条记录的表,没必要建索引,让查询做全表扫描就好了。

  2. 索引的选择性较低。所谓索引的选择性(Selectivity),是指不重复的索引值(也叫基数,Cardinality)与表记录数(#T)的比值:

    Index Selectivity = Cardinality / #T

    显然选择性的取值范围为 (0, 1],选择性越高的索引价值越大,这是由 B+Tree 的性质决定的。

  3. MySQL 只对以下操作符才使用索引:<, <=, =, >, >=, between, in, 以及某些时候的 like(不以通配符 % 或 _ 开头的情形)。

  4. 不要过度索引,只保持所需的索引。每个额外的索引都要占用额外的磁盘空间,并降低写操作的性能。 在修改表的内容时,索引必须进行更新,有时可能需要重构,因此,索引越多,所花的时间越长。

建索引的几大原则

  1. 最左前缀匹配原则,非常重要的原则,mysql 会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配,比如 a = 1 and b = 2 and c > 3 and d = 4 如果建立 (a,b,c,d) 顺序的索引,d 是用不到索引的,如果建立 (a,b,d,c)的索引则都可以用到,a,b,d 的顺序可以任意调整。
  2. = 和 in 可以乱序,比如 a = 1 and b = 2 and c = 3 建立 (a,b,c) 索引可以任意顺序,mysql 的查询优化器会帮你优化成索引可以识别的形式。
  3. 尽量选择区分度高的列作为索引,区分度的公式是 count(distinct col)/count(*),表示字段不重复的比例,比例越大我们扫描的记录数越少,唯一键的区分度是 1,而一些状态、性别字段可能在大数据面前区分度就是 0,那可能有人会问,这个比例有什么经验值吗?使用场景不同,这个值也很难确定,一般需要 join 的字段我们都要求是 0.1 以上,即平均 1 条扫描 10 条记录。
  4. 索引列不能参与计算,保持列 “干净”,比如 from_unixtime(create_time) = ’2021-05-29’ 就不能使用到索引,原因很简单,b+ 树中存的都是数据表中的字段值,但进行检索时,需要把所有元素都应用函数才能比较,显然成本太大。所以语句应该写成 create_time = unix_timestamp(’2021-05-29’)。
  5. 尽量的扩展索引,不要新建索引。比如表中已经有 a 的索引,现在要加 (a,b) 的索引,那么只需要修改原来的索引即可。