目录

索引

索引

索引简介

索引是什么

MySQL官方对索引的定义为:索引(INDEX)是帮助MySQL高效获取数据的数据结构。

从而可以获得索引的本质:索引是排好序的快速查找数据结构。

索引的目的在于提高查询效率,可以类比字典的目录。如果要查mysql这个这个单词,我们肯定要先定位到m字母,然后从上往下找y字母,再找剩下的sql。如果没有索引,那么可能需要a---z,这样全字典扫描,如果我想找Java开头的单词呢?如果我想找Oracle开头的单词呢???

重点:索引会影响到MySQL查找(WHERE的查询条件)和排序(ORDER BY)两大功能!

除了数据本身之外,数据库还维护着一个满足特定查找算法的数据结构,这些数据结构以某种方式指向数据,这样就可以在这些数据结构的基础上实现高级查找算法,这种数据结构就是索引。

https://gitee.com/lienhui68/picStore/raw/master/null/image-20200930210612660.png

左边是数据表,一共有两列七条记录,最左边的是数据记录的物理地址 。 为了加快 Col2 的查找,可以维护一个 右边所示的二叉查找树,每个节点分别包含索引键值和一个指向对应数据记录物理地址的指 针,这样就可以运用 二叉查找在一定的复杂度内获取到相应数据,从而快速的检索出符合条件的记录。

上述描述的是b树,mysql使用的b+树,只有叶子节点才保存数据。

一般来说,索引本身也很大,不可能全部存储在内存中,因此索引往往以索引文件的形式存储在磁盘上。

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
# Linux下查看磁盘空间命令 df -h 
root@9b694d1e7e5c:/# df -h
Filesystem      Size  Used Avail Use% Mounted on
overlay          59G  4.5G   51G   9% /
tmpfs            64M     0   64M   0% /dev
tmpfs           996M     0  996M   0% /sys/fs/cgroup
shm              64M     0   64M   0% /dev/shm
osxfs           234G  108G  110G  50% /etc/mysql
/dev/vda1        59G  4.5G   51G   9% /etc/hosts
tmpfs           996M     0  996M   0% /proc/acpi
tmpfs           996M     0  996M   0% /sys/firmware

我们平时所说的索引,如果没有特别指明,都是指B树(多路搜索树,并不一定是二叉的)结构组织的索引。其中聚集索引,次要索引,覆盖索引,复合索引,前缀索引,唯一索引默认都是使用B+树索引,统称索引。当然,除了B+树这种数据结构的索引之外,还有哈希索引(Hash Index)等。

索引的优势和劣势

优势:

  • 查找:类似大学图书馆的书目索引,提高数据检索的效率,降低数据库的IO成本。
  • 排序:通过索引対数据进行排序,降低数据排序的成本,降低了CPU的消耗。

劣势:

  • 实际上索引也是一张表,该表保存了主键与索引字段,并指向实体表的记录,所以索引列也是要占用空间的。
  • 虽然索引大大提高了查询速度,但是同时会降低表的更新速度,例如对表频繁的进行INSERTUPDATEDELETE。因为更新表的时候,MySQL不仅要保存数据,还要保存一下索引文件每次更新添加的索引列的字段,都会调整因为更新所带来的键值变化后的索引信息。
  • 索引只是提高效率的一个因素,如果MySQL有大数据量的表,就需要花时间研究建立最优秀的索引。

身份证号,银行卡号

Mysql索引分类

索引分类:

  • 单值索引:一个索引只包含单个列,一个表可以有多个单列索引。

  • 唯一索引:索引列的值必须唯一,但是允许空值。

  • 主键索引:设定为主键后数据库会自动建立索引, innodb为聚簇索引

  • 复合索引:一个索引包含多个字段。

    https://gitee.com/lienhui68/picStore/raw/master/null/20201001001228.png

    上面是根据身高年龄建立的组合索引(height,age)

    最左匹配原则,如上图中间的叶子节点,虽然3个身高都是176,但是是3条索引记录。

    如何建立复合索引

    1. 常用的字段放在最前面

      现在要建立组合索引(phone_number,provice),phone_number肯定是经常差的,要放在前边,provice不经常查,放在后边

    2. 等值条件尽量在前边

      等值条件尽量在前边,在扫描的时候,找到的索引块都是有效数据。若非等值条件放在前边,那么需要进行索引跳跃扫描,或者范围扫描,这是就扫描了很多无效的索引,非等值条件放在后边可以,减少无效的索引扫描,提高查找效率。

    3. 离散值较高的字段往前放

      1
      2
      3
      4
      5
      
      SELECT 
      COUNT(DISTINCT phone_number)/COUNT(*) phone_number, 
      COUNT(DISTINCT union_id)/COUNT(*) union_id ,
      COUNT(DISTINCT open_id)/COUNT(*) open_id 
      from mc_k12_wechat_user_info
      

建议:一张表建的索引最好不要超过5个!

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
/* 基本语法 */

/* 1、创建索引 [UNIQUE]可以省略*/
/* 如果只写一个字段就是单值索引,写多个字段就是复合索引 */
CREATE [UNIQUE] INDEX indexName ON tabName(columnName(length));

/* 2、删除索引 */
DROP INDEX [indexName] ON tabName;

/* 3、查看索引 */
/* 加上\G就可以以列的形式查看了 不加\G就是以表的形式查看 */
SHOW INDEX FROM tabName \G;

使用ALTER命令来为数据表添加索引

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
/* 1、该语句添加一个主键,这意味着索引值必须是唯一的,并且不能为NULL */
ALTER TABLE tabName ADD PRIMARY KEY(column_list);

/* 2、该语句创建索引的键值必须是唯一的(除了NULL之外,NULL可能会出现多次) */
ALTER TABLE tabName ADD UNIQUE indexName(column_list);

/* 3、该语句创建普通索引,索引值可以出现多次 */
ALTER TABLE tabName ADD INDEX indexName(column_list);

/* 4、该语句指定了索引为FULLTEXT,用于全文检索 */
ALTER TABLE tabName ADD FULLTEXT indexName(column_list);

MySQL索引数据结构

索引数据结构:

  • BTree索引。
  • Hash索引。
  • Full-text全文索引。
  • R-Tree索引。

BTree索引检索原理:

https://gitee.com/lienhui68/picStore/raw/master/null/20200930210444.png

【初始化介绍】

一颗 b 树,浅蓝色的块我们称之为一个磁盘块,可以看到每个磁盘块包含几个数据项(深蓝色所示)和指针(黄色所示),

如磁盘块 1 包含数据项 17 和 35, 包含指针 P1、P2、P3, P1 表示小于 17 的磁盘块, P2 表示在 17 和 35 之间的磁盘块, P3 表示大于 35 的磁盘块。 真实的数据存在于叶子节点即 3、5、9、10、13、15、28、29、36、60、75、79、90、99。 非叶子节点只不存储真实的数据,只存储指引搜索方向的数据项(关键字),如 17、35 并不真实存在于数据表中。

【查找过程】 如果要查找数据项 29, 那么首先会把磁盘块 1 由磁盘加载到内存,此时发生一次 IO, 在内存中用二分查找确定 29 在 17 和 35 之间,锁定磁盘块 1 的 P2 指针,内存时间因为非常短(相比磁盘的 IO)可以忽略不计, 通过磁盘块 1 的 P2 指针的磁盘地址把磁盘块 3 由磁盘加载到内存,发生第二次 IO,29 在 26 和 30 之间,锁定磁盘块 3 的 P2 指 针,通过指针加载磁盘块 8 到内存,发生第三次 IO, 同时内存中做二分查找找到 29, 结束查询,总计三次 IO。

真实的情况是, 3 层的 b+树可以表示上百万的数据, 如果上百万的数据查找只需要三次 IO, 性能提高将是巨大的, 如果没有索引,每个数据项都要发生一次 IO, 那么总共需要百万次的 IO, 显然成本非常非常高。

Mysql索引 B+树

索引是一种数据结构,用于帮助我们在大量数据中快速定位到我们想要查找的数据。 索引最形象的比喻就是图书的目录了。注意这里的大量,数据量大了索引才显得有意义,如果我想要在 [1,2,3,4] 中找到 4 这个数据,直接对全数据检索也很快,没有必要费力气建索引再去查找。

索引在 MySQL 数据库中分三类:

  • B+ 树索引
  • Hash 索引
  • 全文索引

我们今天要介绍的是工作开发中最常接触到的 InnoDB 存储引擎中的 B+ 树索引。要介绍 B+ 树索引,就不得不提二叉查找树,平衡二叉树和 B 树这三种数据结构。B+ 树就是从他们仨演化来的。

B树

因为内存的易失性。一般情况下,我们都会选择将 user 表中的数据和索引存储在磁盘这种外围设备中。但是和内存相比,从磁盘中读取数据的速度会慢上百倍千倍甚至万倍,所以,我们应当尽量减少从磁盘中读取数据的次数。另外,从磁盘中读取数据时,都是按照磁盘块来读取的,并不是一条一条的读。如果我们能把尽量多的数据放进磁盘块中,那一次磁盘读取操作就会读取更多数据,那我们查找数据的时间也会大幅度降低。如果我们用树这种数据结构作为索引的数据结构,那我们每查找一次数据就需要从磁盘中读取一个节点,也就是我们说的一个磁盘块。我们都知道平衡二叉树可是每个节点只存储一个键值和数据的。那说明什么?说明每个磁盘块仅仅存储一个键值和数据!那如果我们要存储海量的数据呢?

可以想象到二叉树的节点将会非常多,高度也会极其高,我们查找数据时也会进行很多次磁盘 IO,我们查找数据的效率将会极低!

https://gitee.com/lienhui68/picStore/raw/master/null/20201001212441.png

为了解决平衡二叉树的这个弊端,我们应该寻找一种单个节点可以存储多个键值和数据的平衡树。也就是我们接下来要说的 B 树。

B 树(Balance Tree)即为平衡树的意思,下图即是一棵 B 树:

https://gitee.com/lienhui68/picStore/raw/master/null/20201001212454.png

图中的 p 节点为指向子节点的指针,二叉查找树和平衡二叉树其实也有,因为图的美观性,被省略了。

图中的每个节点称为页,页就是我们上面说的磁盘块,在 MySQL 中数据读取的基本单位都是页,所以我们这里叫做页更符合 MySQL 中索引的底层数据结构。

从上图可以看出,B 树相对于平衡二叉树,每个节点存储了更多的键值(key)和数据(data),并且每个节点拥有更多的子节点,子节点的个数一般称为阶,上述图中的 B 树为 3 阶 B 树,高度也会很低。

基于这个特性,B 树查找数据读取磁盘的次数将会很少,数据的查找效率也会比平衡二叉树高很多。

假如我们要查找 id=28 的用户信息,那么我们在上图 B 树中查找的流程如下:

  • 先找到根节点也就是页 1,判断 28 在键值 17 和 35 之间,那么我们根据页 1 中的指针 p2 找到页 3。
  • 将 28 和页 3 中的键值相比较,28 在 26 和 30 之间,我们根据页 3 中的指针 p2 找到页 8。
  • 将 28 和页 8 中的键值相比较,发现有匹配的键值 28,键值 28 对应的用户信息为(28,bv)。

B+树

B+ 树是对 B 树的进一步优化。让我们先来看下 B+ 树的结构图:

https://gitee.com/lienhui68/picStore/raw/master/null/20201001212519.png

根据上图我们来看下 B+ 树和 B 树有什么不同:

  1. B+ 树非叶子节点上是不存储数据的,仅存储键值,而 B 树节点中不仅存储键值,也会存储数据。

    之所以这么做是因为在数据库中页的大小是固定的,InnoDB 中页的默认大小是 16KB。

    如果不存储数据,那么就会存储更多的键值,相应的树的阶数(节点的子节点树)就会更大,树就会更矮更胖,如此一来我们查找数据进行磁盘的 IO 次数又会再次减少,数据查询的效率也会更快。

    另外,B+ 树的阶数是等于键值的数量的,如果我们的 B+ 树一个节点可以存储 1000 个键值,那么 3 层 B+ 树可以存储 1000×1000×1000=10 亿个数据。

    一般根节点是常驻内存的,所以一般我们查找 10 亿数据,只需要 2 次磁盘 IO。

  2. 因为 B+ 树索引的所有数据均存储在叶子节点,而且数据是按照顺序排列的。

    那么 B+ 树使得范围查找,排序查找,分组查找以及去重查找变得异常简单。而 B 树因为数据分散在各个节点,要实现这一点是很不容易的。

    有心的读者可能还发现上图 B+ 树中各个页之间是通过双向链表连接的,叶子节点中的数据是通过单向链表连接的。

    其实上面的 B 树我们也可以对各个节点加上链表。这些不是它们之前的区别,是因为在 MySQL 的 InnoDB 存储引擎中,索引就是这样存储的。

    也就是说上图中的 B+ 树索引就是 InnoDB 中 B+ 树索引真正的实现方式,准确的说应该是聚集索引(聚集索引和非聚集索引下面会讲到)。

    通过上图可以看到,在 InnoDB 中,我们通过数据页之间通过双向链表连接以及叶子节点中数据之间通过单向链表连接的方式可以找到表中所有的数据。

    MyISAM 中的 B+ 树索引实现与 InnoDB 中的略有不同。在 MyISAM 中,B+ 树索引的叶子节点并不存储数据,而是存储数据的文件地址。

聚簇索引和非聚簇索引

在上节介绍 B+ 树索引的时候,我们提到了图中的索引其实是聚集索引的实现方式。

那什么是聚集索引呢?在 MySQL 中,B+ 树索引按照存储方式的不同分为聚集索引和非聚集索引。

这里我们着重介绍 InnoDB 中的聚集索引和非聚集索引:

聚集索引(聚簇索引):以 InnoDB 作为存储引擎的表,表中的数据都会有一个主键,即使你不创建主键,系统也会帮你创建一个隐式的主键。

这是因为 InnoDB 是把数据存放在 B+ 树中的,而 B+ 树的键值就是主键,在 B+ 树的叶子节点中,存储了表中所有的数据。

这种以主键作为 B+ 树索引的键值而构建的 B+ 树索引,我们称之为聚集索引。

非聚集索引(非聚簇索引):以主键以外的列值作为键值构建的 B+ 树索引,我们称之为非聚集索引。

非聚集索引与聚集索引的区别在于非聚集索引的叶子节点不存储表中的数据,而是存储该列对应的主键,想要查找数据我们还需要根据主键再去聚集索引中进行查找,这个再根据聚集索引查找数据的过程,我们称为回表。

明白了聚集索引和非聚集索引的定义,我们应该明白这样一句话:数据即索引,索引即数据。

参考:MySQL索引-B+树(看完你就明白了)

哪些情况需要建索引

  • 主键自动建立主键索引(唯一 + 非空)。

    2000条以下不需要建索引,单表不要超过500w。

    单表记录条数多少合适?

    这个数值和实际记录的条数无关,而与 MySQL 的配置以及机器的硬件有关。因为,MySQL 为了提高性能,会将表的索引装载到内存中。InnoDB buffer size 足够的情况下,其能完成全加载进内存,查询不会有问题。但是,当单表数据库到达某个量级的上限时,导致内存无法存储其索引,使得之后的 SQL 查询会产生磁盘 IO,从而导致性能下降。当然,这个还有具体的表结构的设计有关,最终导致的问题都是内存限制。这里,增加硬件配置,可能会带来立竿见影的性能提升哈。

    那么,我对于分库分表的观点是,需要结合实际需求,不宜过度设计,在项目一开始不采用分库与分表设计,而是随着业务的增长,在无法继续优化的情况下,再考虑分库与分表提高系统的性能。对此,阿里巴巴《Java 开发手册》补充到:如果预计三年后的数据量根本达不到这个级别,请不要在创建表时就分库分表。那么,回到一开始的问题,你觉得这个数值多少才合适呢?我的建议是,根据自身的机器的情况综合评估,如果心里没有标准,那么暂时以 500 万行作为一个统一的标准,相对而言算是一个比较折中的数值。

  • 频繁作为查询条件的字段应该创建索引。

  • 查询中与其他表关联的字段,外键关系建立索引。

  • 单键/组合索引的选择问题, 组合索引性价比更高

  • 查询中排序的字段,排序字段若通过索引去访问将大大提高排序速度。

  • 查询中统计或者分组字段(group by也和索引有关)。

    分组的前提是必排序,也就是说group by也跟索引息息相关

哪些情况不需要建索引

  • 记录太少的表。
  • 经常增删改的表。
  • 频繁更新的字段不适合创建索引。
  • Where条件里用不到的字段不创建索引。
  • 假如一个表有10万行记录,有一个字段A只有true和false两种值,并且每个值的分布概率大约为50%,那么对A字段建索引一般不会提高数据库的查询速度。索引的选择性是指索引列中不同值的数目与表中记录数的比。如果一个表中有2000条记录,表索引列有1980个不同的值,那么这个索引的选择性就是1980/2000=0.99。一个索引的选择性越接近于1,这个索引的效率就越高。