导航
导航
文章目录
  1. 索引概述
    1. 索引的本质及作用
    2. 索引的优缺点
  2. 索引的类型
    1. 三种常用存储引擎索引支持情况
    2. B-Tree 索引
  3. 索引的操作
    1. 创建
    2. 修改
    3. 删除
    4. 查看
  4. 索引的选择
    1. 索引选择原则
    2. 索引选择原则细述
    3. 索引选择注意事项
  5. 索引是最好的解决方案？

MySQL 之索引部分

2018.04.16 zhouxelf 后端  热度 ℃

索引概述

索引的本质及作用

索引（也称「键」）是为了加速表中数据行的检索而创建的一种分散的数据结构。

数据库查询是数据库的最主要功能之一。我们都希望查询数据的速度能尽可能的快，因此数据库系统的设计者会从查询算法的角度进行优化。最基本的查询算法当然是顺序查找（linear search），这种复杂度为 O(n) 的算法在数据量很大时显然是糟糕的，好在计算机科学的发展提供了很多更优秀的查找算法，例如二分查找（binary search）、二叉树查找（binary tree search）等。如果稍微分析一下会发现，每种查找算法都只能应用于特定的数据结构之上，例如二分查找要求被检索数据有序，而二叉树查找只能应用于二叉查找树上，但是数据本身的组织结构不可能完全满足各种数据结构（例如，理论上不可能同时将两列都按顺序进行组织），所以，在数据之外，数据库系统还维护着满足特定查找算法的数据结构，这些数据结构以某种方式引用（指向）数据，这样就可以在这些数据结构上实现高级查找算法。这种数据结构，就是索引。

索引的优缺点

优点

  1. 大大加快数据的检索速度；
  2. 创建唯一性索引，能保证数据库表中每一行数据的唯一性；
  3. 加速表与表之间的连接；
  4. 在使用分组和排序子句进行数据检索时，可以显著减少查询中分组和排序的时间。

缺点

  1. 索引需要占用额外的物理空间；
  2. 当对表中的数据进行增加、删除和修改时，索引也需要动态的维护，降低了数据的维护速度。

索引的类型

在 MySQL 中，索引是在存储引擎层而不是服务器层实现的，所以，并没有统一的索引标准。不同存储引擎的索引的工作方式并不一样，也不是所有的存储引擎都支持所有类型的索引。即使多个存储引擎支持同一种类型的索引，其底层的实现方式也可能不同。

目前 MySQL 提供了常用的四种索引：

  • B-Tree 索引：最常见的索引类型，大部分引擎都支持 B-Tree 索引；
  • Hash 索引：只有 Memory 引擎支持 Hash 索引，基于 Hash 表实现，使用场景简单；
  • R-Tree：空间数据索引，MyISAM 引擎支持，主要用于地理数据存储；
  • 全文索引：一种特殊类型的索引，类似于搜索引擎的功能。

说明： 平时讨论的索引，如果没有特别指明，一般都是指 B-Tree 索引，而且从技术上说，底层实现方式其实是 B+Tree。 B-Tree 索引又分为几种，比如普通索引、唯一索引、主键索引等等。关于全文索引，详见 MySQL 之全文索引

三种常用存储引擎索引支持情况

索引 MyISAM InnoDB Memory
B-Tree Yes Yes Yes
Hash 索引 No No Yes
R-Tree 索引 Yes No No
全文索引 Yes 5.6 之后 No

B-Tree 索引

  • 普通索引：创建普通索引时，不限制任何条件（唯一、非空等限制），该类型的索引可以创建在任何数据类型上。
  • 唯一索引：UNIQUE，唯一索引，就是限制被索引的值必须是唯一的。当为表中某个字段设置主键或唯一性约束时，系统会自动创建关联该字段的唯一索引。
  • 主键索引：PRIMARY，主键索引，一种特殊的唯一索引，不允许有空值。

说明： 关于唯一索引和唯一约束的探讨，详见 谈谈唯一约束和唯一索引

索引的操作

创建

三种创建方式

1
2
3
4
5
6
7
8
9
10
11
12
13
14
// 1. 建表时创建
create table 表名 (
    id int(11),
    xxx xxx,
    [索引类型] index|key [索引名] (属性名1 [(长度)] [ASC|DESC]) 
);
      
// 2. 直接创建
create index 索引名
    on 表名 (属性名 [(长度)] [ASC|DESC]);
           
// 3. 修改表创建
alter table 表名
    add [索引类型] index|key [索引名] (属性名1 [(长度)] [ASC|DESC]);

注意： 不能使用第二种方式创建主键索引

修改

没必要修改，直接删除重建即可。

删除

两种删除方式

1
2
3
4
5
6
7
// 1. 直接删除
drop index 索引名
    on 表名;
          
// 2. 修改表删除
alter table 表名
    drop index 索引名;

查看

1
show index|keys from 表名

列的意义：

  • Table：表名
  • Non_unique：如果索引值唯一，则为 0，否则为 1
  • Key_name：索引名
  • Seq_in_index：索引中列的序列号，从 1 开始
  • Column_name：列名
  • Collation：列在索引中的存储形式，在 MySQL 中，值「A」（升序）或 NULL（无分类）
  • Cardinality：索引中唯一值数目的估计值
  • Sub_part：如果列只是被部分地编入索引，则为被编入索引的字符的数目，如果整列被编入索引，则为 NULL
  • Packed：说明关键字如何被压缩，如果没有被压缩，则为 NULL
  • Null：如果列含有 NULL，则值为 YES，如果没有，则该列为 NO
  • Index_type：索引类型（BTREE，FULLTEXT，HASH，RTREE）
  • Comment：更多评论

索引的选择

索引选择原则

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

索引选择原则细述

  • 性能优化过程中，选择在哪个列上创建索引是最非常重要的。可以考虑使用索引的主要有两种类型的列：在 where 子句中出现的列，在 join 子句中出现的列， 而不是在 select 关键字后选择列表的列。
  • 索引列的基数越大，索引的效果越好。例如，存放出生日期的列具有不同的值，很容易区分行，而用来记录性别的列，只有「男」和「女」，对此进行索引没有多大用处，因为不管搜索哪个值，都会得出大约一半的行。
  • 使用短索引，如果对字符串列进行索引，应该指定一个前缀长度，可节省大量索引空间，提升查询速度。
  • 利用最左前缀原则。

索引选择注意事项

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

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

  1. 表记录比较少，例如一两千条甚至只有几百条记录的表，没必要建索引，让查询做全表扫描就好了；
  2. 索引的选择性较低，所谓索引的选择性（Selectivity），是指不重复的索引值（也叫基数，Cardinality）与表记录数（#T）的比值：Index Selectivity = Cardinality / #T 显然选择性的取值范围为 (0, 1]，选择性越高的索引价值越大，这是由 B+Tree 的性质决定的。

除此之外，还有两个点需要注意：

  1. MySQL 只对以下操作符才使用索引：<、<=、=、>、>=、between、in，以及某些时候的 like（不以通配符 % 或 _ 开头)；
  2. 不要过度索引，只保持需要的索引。每个额外的索引都要占用额外的磁盘空间，并降低写操作的性能。在修改表的内容时，索引必须进行更新，有时可能需要重构，因此，索引越多，所花的时间越长。

索引是最好的解决方案？

索引并不总是最好的解决方案。总的来说，只有当索引帮助存储引擎快速查找到记录带来的好处大于其带来的额外工作时，索引才是有效的。对于非常小的表，大部分情况下简单的全表扫描更高效。对于中到大型的表，索引就非常有效。但对于特大型的表，建立和使用索引的代价将随之增长。这种情况下，则需要一种技术可以直接区分出查询需要的一组数据，而不是一条记录一条记录地匹配。这是就要使用分表、分库等分区技术了。

参考文章

MySQL索引背后的数据结构及算法原理
索引的利弊与如何判定，是否需要索引
高性能 MySQL（第三版）