MySQL索引从入门到深入学习

ゝ一世哀愁。 2023-09-28 08:36 79阅读 0赞

**目录**

从操作系统角度简单理解一下磁盘IO

局部性原理与磁盘预读

索引学习

简单了解一下为什么要使用索引

索引使用的数据结构 B+树

索引的分类

聚簇索引和非聚簇索引（重点）

主键索引 （primary key）

普通索引 （常规索引）(normal)

唯一索引（UNIQUE ）

全文索引（FULLTEXT）（了解）

复合索引（联合索引）超重要

hash索引（了解）

使用聚簇索引的好处

为什么 主键建议使用自增id作为聚簇索引

--------------------

## 从操作系统角度简单理解一下磁盘IO ##

首先，我们知道MySQL中的所有数据最后都是要存储到磁盘中的，所以我们有必要简单了解一下磁盘IO相关的知识；

mysql的数据一般是以文件形式存储在磁盘上，检索是需要磁盘I/O操作的，就是把磁盘中的数据读取到内存中，或者是把内存中的数据写到磁盘中；与主存不同，磁盘I/O存在机械运动耗费(存在 **寻道时间+旋转延迟**\+数据传输时间，前面两个时间是主要的时间花费)，因此磁盘I/O (对磁盘进行读写文件) 的时间消耗是巨大的。因此在对数据的处理的时候**要尽量规避磁盘IO**；

当需要从磁盘读取数据时，系统会将**数据地址**传给磁盘，即确定要读的数据在哪个磁道，哪个扇区。为了读取这个扇区的数据，需要将磁头放到这个扇区上方，为了实现这一点，磁头需要移动对准相应磁道，这个过程叫做【寻道】，所耗费时间叫做【**寻道时间**】，然后磁盘旋转将目标扇区旋转到磁头下，这个过程耗费的时间叫做**旋转时间**。

简单了解一下什么是页？

page（页）： 是内存与操作系统之间操作的**最小单元**。每个存储块称为一页，页的最小存储数据为4k , 不同的操作系统可能不同，但是一定是4k的倍数，比如，innodb中的一页是16k数据；

### 局部性原理与磁盘预读 ###

由于存储介质的特性，磁盘本身存取就比主存慢很多，再加上机械运动耗费，**磁盘的存取速度往往是主存的十万分之一**，因此为了提高效率，要**尽量减少磁盘I/O次数（作为软件开发人员我们是不可能从硬件的层面去优化这个磁盘IO的，所以我们应该考虑怎么尽可能地减少磁盘io的次数）**。【为了达到这个目的，磁盘往往不是严格按需读取，而是每次都会预读，即使只需要一个字节，磁盘也会从这个位置开始，**顺序向后读取**一定长度的数据放入内存】。这样做的理论依据是计算机科学中著名的**局部性原理**：（顺序读比随机读取快多了，少了寻道时间，而且也只需要很少的旋转时间）

*  当一个数据被用到时，其附近的数据也通常会马上被使用。
 *  程序运行期间所需要的数据通常比较集中。

由于磁盘**顺序读取**的效率很高（不需要寻道时间，只需很少的旋转时间），因此对于具有局部性的程序来说，预读可以提高I/O效率。

预读的长度一般为**页（page）**的整倍数。页是计算机管理存储器的逻辑块，硬件及操作系统往往将主存和磁盘存储区分割为连续的大小相等的块，每个存储块称为一页（在许多操作系统中，页的大小通常为4k），**主存和磁盘以页为单位交换数据**。当程序要读取的数据不在主存中时，会触发一个缺页异常，此时系统会向磁盘发出读盘信号，磁盘会找到数据的起始位置并向后连续读取一页或几页载入内存中，然后异常返回，程序继续运行。

## 索引学习 ##

### 简单了解一下为什么要使用索引 ###

mysql的数据是存在磁盘上的，磁盘的读取速度是比较慢的，但是使用主键的读取方式会很快，当数据来了个太大非主键的【字段查询】是会特别耗时。一条语句可能查询10秒以上(此时数据库进入全表查询了，导致非常耗时)。

### 索引使用的数据结构 ###

在磁盘上存储数据那肯定不能随便存，肯定是要有一定的结构来存储，这样可以保证存储效果的最大，数据存在磁盘上有了结构这就叫数据结构；那么究竟使用什么样的数据结构才能高效的存储这些数据？

1.**数组和链表:**

肯定不能选，这种最基本的数据结构，各自的劣势太明显了：

*  数据库对查询的要求是非常高的 , 所以链表这种查询需要全表遍历的基本数据结构是不能用的，因为当数据库中的表非常大的时候，比如几千万条数据的时候，那么全表遍历的时间可能需要几十秒甚至更加久；
 *  数组这种结构在添加数据时成本太大，插入数据时太过于频繁是会导致数组的下标频繁的移动或者是扩容。

2.**hash：**

hash表不支持范围查询（需要挨个匹配，时间复杂度高），并且如果hash的算法不好的话，会导致hash冲突导致数据散列不均匀；而且hash表不是有序的！

3.**二叉树，AVL树，红黑树**

虽然这三个也都是树，但是**它们的分支有且仅有两个**，当你想要存储更多的数据的时候这个**树的层次就会变高，IO次数就会变多**； 为了减少IO我们应该在查询数据的时候就要尽可能的减少要读取的数据量 或者是减少数据访问的次数 ，明显这些树都不合适；

4.**B-树**

注意:这个B-树，不是b减树，就是b树，别闹出笑话来了；

之所以不使用B树，那是因为B树在根节点和叶子节点都会存储data(innodb是以 【行】 为单位来储存数据的)，那么就意味着b树的节点存储的索引的数量不会太多（容量固定，你又存储了data，又存储了指针，那么再存储索引的数量肯定就会变少了），也就是说你这个b树的节点存储的索引的 检索范围必然不会太宽，那么就是说你这个树的层次可能会比较高（意味着IO的次数也会比较高）；

**5.B+树**

B+树的**数据全部在叶子节点**，非叶子节点只有 指针(存储子节点的地址信息)和键值(一般是表中记录的主键), 那么也就意味着**b+树的非叶子节点可以储存更多的索引**，并且索引的检索范围也会更大，那就意味着树的层次不会太高(想要存储千万级别的数据只需要3-4层b+树就行)； 这里又引出另一个问题了，那么我们这个非叶子节点存储的主键是应该存储什么类型的数据作为主键比较好？---越短越好，越短就意味着存储的数据的范围越大，树的层次就越低，io次数就越少；

灰色部分代表指向子节点的地址值，蓝色代表索引值；

![a6a5ac03b0164c0dbaa834227e3a401f.png][]

### 索引的分类 ###

#### 聚簇索引和非聚簇索引（重点） ####

![9df4374ddf6b464994fa2148d430f979.png][]

**（这里的数据是innodb中一行一行的数据,不是列数据）数据跟索引放在一起的叫聚簇索引**，数据跟索引没有放在一起的叫做非聚簇索引；

innodb数据存储的格式：【compact行记录】是在MySQL 5.0时被引入的，其设计目标是能高效存放数据；

![cf3bd157753d449182396af3cac28691.png][]

在innodb 存储引擎中，即存在聚簇索引，又存在非聚簇索引；

在myisam存储引擎中，只有非聚簇索引；

1. InnoDB使用的是聚簇索引（**聚簇索引默认使用主键作为其索引**），将主键组织到一棵B+树中，而**行数据**就储存在叶子节点上(一张表所有的数据都会在叶子节点，所以这也说明其实建立索引也是蛮耗空间的)，若使用"where id = 14"这样的条件查找主键，则按照B+树的检索算法即可查找到对应的叶节点，之后获得行数据。

2.若对上面的图中的Name列进行条件搜索，则需要两个步骤：

第一步**在辅助索引**(只要不是聚簇索引，那么都可以叫辅助索引)B+树中检索Name，到达其叶子节点**获取对应的主键**。

第二步使用主键在聚簇索引B+树种再执行一次B+树检索操作，最终到达叶子节点即可获取整行数据。（重点在于需要通过其他列建立辅助索引）

**注意事项：**

1. **聚簇索引具有唯一性**，由于聚簇索引是将**数据(一行一行的数据)跟索引结构放到一块**，因此一个表仅有一个聚簇索引，其他辅助索引可能是只有几个列的数据和索引放在一起！

2. **聚簇索引中的索引默认是主键**，如果表中没有定义主键，InnoDB 会选择一个唯一且非空的索引代替。如果没有这样的索引，InnoDB 会**隐式定义一个6个字节大小的row\_id来作为主键**，这个主键会默认作为聚簇索引中的索引。

也就是说创建表的时候系统会默认帮你创建聚簇索引，如果已经设置了主键为聚簇索引又希望再单独设置聚簇索引，必须先删除主键，这样innodb就会去选择唯一且非空的索引代替，最后恢复设置主键即可。

#### 主键索引 （primary key） ####

也简称主键。它可以提高查询效率，并提供唯一性约束。一张表中只能有一个主键。【**被标志为自动增长的字段一定是主键，但主键不一定是自动增长**】。一般把主键定义在无意义的字段上（如：编号），主键的数据类型最好是数值。 **主键索引就是聚簇索引中的一种；**

一种方法在创建表的时候创建，再次是修改表：

ALTER TABLE tbl_name ADD PRIMARY KEY (column_list)

索引也可以在创建表时指定： 使用可视化工具那就更加简单了；

create table student_score(
     id int(10) auto_increment,
     student_id int(10),
     subject_id int(10),
     score int(10),
     primary key (id),
     index idx_student_id (student_id),
     index idx_subject_id (subject_id)
    );

小tips：

int(n)括号里面的数字表示的显示宽度，不是代表能存放多少位数，int是**始终占用4个字节的空间，**int(M) 跟 int 数据类型是相同的。int(M) 只有跟 zerofill 结合起来，才能使我们清楚的看到不同之处；

1.创建表t

create table t(id int(5) zerofill);

2.插入数据

insert into t(id) values(10);

3.显示select \* from t

结果：00010

#### 普通索引 （常规索引）(normal) ####

就是普普通通的索引，没有什么特殊要求，理论上任何列都可以创建索引（创建索引是非常耗时间的，比如一个G的索引的创建时间可能要20多秒）

create index idx_myDeptIndex on detail(dept_id);   -- 要求可以看懂这种sql语句

注意：有的列【数据量比较大】，使用前几个字符就能【很快标识】出来一行数据，那我们就可以使用这种方式建立索引，比如我们的邮箱，邮箱很多后缀是相同的我们完全可以忽略。

创建email列的索引，索引可以截取length长度，只使用这一列的前几个字符：

create index idx_email on user(email(5));
     -- 对user表中的email列中的前5个字符建立索引 如果直接使用列名那么就会把列的全数据作为索引，我们也可以使用列的前面几个字符串作为索引  使用Email的前5个字符串作为索引

#### 唯一索引（UNIQUE ） ####

索引的值不能重复；

CREATE UNIQUE INDEX ux_indexName ON mytable(username(length))

唯一索引和主键的区别：

*  唯一索引列允许空值，而主键列不允许为空值。
 *  主键列在创建时，已经默认为非空值 + 唯一索引了。
 *  主键可以被其他表引用为外键，而唯一索引不能。
 *  一个表最多只能创建一个主键，但可以创建多个唯一索引。
 *  主键更适合那些不容易更改的唯一标识，如自动递增列、身份证号等。

唯一约束和唯一索引的区别：

*  唯一约束和唯一索引，都可以实现列数据的唯一，列值可以为null。
 *  创建唯一约束，会自动创建一个同名的唯一索引，该索引不能单独删除，删除约束会自动删除索引。唯一约束是通过唯一索引来实现数据唯一。
 *  创建一个唯一索引，这个索引就是独立的索引，可以单独删除。
 *  如果一个列上想有约束和索引，且两者可以单独的删除。可以先建唯一索引，再建同名的唯一约束。

#### 全文索引（FULLTEXT）（了解） ####

**做全文检索使用的索引，我们有更好的替代品 ElacticSearch，所以实际使用不多，只当了解。**

通过数值比较、范围过滤等就可以完成绝大多数我们需要的查询，但是，如果希望通过关键字的匹配来进行查询过滤，那么就需要基于相似度的查询，而不是原来的精确数值比较。全文索引就是为这种场景设计的。

你可能会说，用 like + % 就可以实现模糊匹配了，为什么还要全文索引？like + % 在文本比较少时是合适的，但是对于大量的文本数据检索，是不可想象的。全文索引在大量的数据面前，能比 like + % 快 N 倍，速度不是一个数量级，但是全文索引可能存在精度问题。

> 全文索引的版本支持

1.  MySQL 5.6 以前的版本，只有 MyISAM 存储引擎支持全文索引；
2.  MySQL 5.6 及以后的版本，MyISAM 和 InnoDB 存储引擎均支持全文索引;
3.  只有字段的数据类型为 char、varchar、text 及其系列才可以建全文索引。

> 使用全文索引的注意

1.  使用全文索引前，搞清楚版本支持情况；
2.  全文索引比 like + % 快 N 倍，但是可能存在精度问题；
3.  如果需要全文索引的是大量数据，建议先添加数据，再创建索引；
4.  对于中文，可以使用 MySQL 5.7.6 之后的版本，或者第三方插件。

#### 复合索引（联合索引）重要 ####

当有多个查询条件时，我们推荐使用复合索引。索引的`组合使用`（索引合并）效率是**低于**`复合索引`的。

比如：我们经常按照 A列 B列 C列进行查询时，通常的做法是建立一个由三个列共同组成的复合索引而不是对每一个列建立普通索引。

创建方式： **复合索引中的索引的顺序是非常重要的； （后面会讲）**

alert table test add idx_a1_a2_a3 table (a1,a2,a3)

使用复合索引可以极大的减少回表的次数；(下面使用复合索引的优点中有解释为什么会大大减少回表的次数)

**当你建立了一个（a,b,c）的复合索引，实际上是会去创建（a），（a,b），（a,b,c）这三个索引的，**你会发现它们都是基于a索引的； (并不会单独的创建(a,c)这个索引，所以如果你查询的时候跳过了a列，那么本次查询是不会走索引的，这也叫索引失效)

**为什么要使用复合索引？**

**减少开销。**建一个联合索引`(a,b,c)`，实际相当于建了`(a)、(a,b)、(a,b,c)`三个索引。每多一个索引，都会增加写操作的开销和磁盘空间的开销。对于大量数据的表，使用联合索引会大大的减少开销！

**覆盖索引。**对联合索引`(a,b,c)`，如果有如下的sql: select a`,b,c` from student where a=1 and b=2。那么MySQL可以直接通过遍历索引取得数据，而无需回表，这减少了很多的随机io操作。**减少io操作，特别的随机io其实是dba主要的优化策略**。所以，在真正的实际应用中，覆盖索引是主要的提升性能的优化手段之一。

**效率高。**索引列越多，**通过索引筛选出的数据越少**。有1000W条数据的表，有如下sql:select from table where a=1 and b=2 and c=3,假设假设每个条件可以筛选出10%的数据，如果只有单值索引，那么通过该索引能筛选出1000W10%=100w条数据，然后再回表从100w条数据中找到符合b=2 and c= 3的数据，然后再排序，再分页；如果是联合索引，通过索引筛选出1000w10% 10% \*10%=1w，效率提升可想而知！

**缺点。**联合索引越多，索引列越多，则创建的索引越多，索引都是存储在磁盘里的，通过索引算法来查找数据，的确可以极大的提高查询效率，**但是与此同时增删改的同时，需要更新索引，同样是需要花时间的，并且索引所占的磁盘空间也不小。**

**建议。**单表尽可能不要超过一个联合索引，单个联合索引不超过3个列;

**最左前缀匹配原则：（重点）**

**原文博客链接：https://blog.csdn.net/sinat\_41917109/article/details/88944290**

索引的底层是一颗B+树，那么联合索引自然也是一颗B+树，只不过联合索引的**健值数量不是一个，而是多个**。构建一颗B+树只能根据一个**值**来构建，因此数据库依据联合索引最左的字段来构建B+树。

例子：假如创建一个（a,b)的联合索引，那么它的索引树是这样的：

![d6c6830e4e084866a04185230614eeaf.png][]

可以看到a的值是有顺序的，1，1，2，2，3，3，而b的值是没有顺序的1，2，1，4，1，2。所以b = 2这种查询条件没有办法利用索引，因为联合索引首先是按a排序的，b是无序的。

同时我们还可以发现**在a值相等的情况下，b值又是按顺序排列的，但是这种顺序是相对的**。所以最左匹配原则遇上范围查询就会停止，剩下的字段都无法使用索引。例如a = 1 and b = 2 a,b字段都可以使用索引，因为在a值确定的情况下b是相对有序的，而a>1and b=2，a字段可以匹配上索引，但b值不可以，因为a的值是一个范围，在这个范围中b是无序的。

最左匹配原则：最左优先，以最左边的为起点任何连续的索引都能匹配上。同时遇到范围查询(>、<、between、like)就会停止匹配。

**注意：** **当表中的全部字段都是索引列的时候，无论进行什么样的查询都是会用到索引的；【即便不满足最左匹配原则的复合索引也是会使用索引来进行查询；】而且有时候本来按照理论来讲某条查询是要走索引的，但是没有走，也不要大惊小怪，那是因为MySQL认为你这个查询语句走索引的速度可能不如全表查询，那么它就会走全表而不是索引，这是MySQL自己的优化机制；**

**索引并不是越多越好，也不需要在全部列上添加索引！！**

**思考：**为什么联合（复合）索引的性能会比索引的组合使用效率高？

因为复合索引在建立的时候它不仅仅是创建一个索引，比如我们要创建一个（a,b,c）的复合索引，MySQL会创建（a），（a,b）, (a,b,c) 这三个索引，比如在（a,b）索引的查询出来的数据中,在a相同的情况下，b列的数据会是有序的，在 (a,b,c) 这个索引查询出来的数据中如果a相同，b相同,那么c列的数据是有序的，这样在查询它们的时候不需要对它们进行全部扫描，这是快的原因之一；

还有就是使用复合索引查询出来的的数据是更加少的（这样回表的时候就只需要携带更少的id来进行回表），有点像是在索引a查询出来的数据的基础上再进行条件筛选；而使用组合索引它是把索引查询出来的数据取并集，那就意味着它回表的时候需要携带更多的id去查询，这也会导致二者的效率差距增大；

#### hash索引（了解） ####

hash天然快（最快o(1),最慢o(n),树化(lon(n))），但是天然无序；

hash相信大家应该都很熟悉，hash是一种key-value形式的数据结构。实现一般是数组+链表的结构，通过hash函数计算出key在数组中的位置，然后如果出现hash冲突就通过链表来解决。当然还有其他的解决hash冲突的方法。hash这种数据结构是很常用的，比如我们系统使用HashMap来构建热点数据缓存，存取效率很好。

hash结构存数据首先通过计算key的hash值来确定其在数组中的位置，如果有冲突就在该数组位置建一个链表。这样很明显有几个问题：

*  即使是具有相同特征的key计算出来的位置可能相隔很远，连续查询效率低下。即`不支持范围查询`。
 *  hash索引存储的是计算得到的hash值和行指针，而不存储具体的行值，所以通过hash索引查询数据需要进行两次查询(首先查询行的位置，然后找到具体的数据)
 *  hash索引查询数据的前提就是计算hash值，也就是要求key为一个能准确指向一条数据的key，所以对于like等一类的匹配查询是不支持的。
 *  **所以我们可以知道的是hash索引适用于快速选取某一行的数据，超级大表中定位某一行特别快。**
 *  **只要是只需要做等值比较查询，而不包含排序或范围查询的需求，都适合使用哈希索引。**

####  ####

create index index_test using hash on test1(id);

你会发现创建了也没有用，因为**InnoDB和myIsam都不支持hash索引。**

### 使用聚簇索引的好处 ###

1.由于行数据和聚簇索引的叶子节点存储在一起，同一页(innodb一页为16k)中会有多条行数据，**访问同一数据页不同行记录时，已经把页加载到了Buffer中（缓存器），再次访问时，会在内存中完成访问，不必访问磁盘。**这样主键和行数据是一起被载入内存的，找到叶子节点就可以立刻将行数据返回了，如果是按照主键Id来组织数据，那么获得数据更快。

2.**辅助索引的叶子节点，存储主键值，而不是数据的存放地址**。好处是当行数据放生变化时，索引树的节点也需要分裂变化；或者是我们需要查找的数据，在上一次IO读写的缓存中没有，需要发生一次新的IO操作时，**可以避免对辅助索引的维护工作**，只需要维护聚簇索引树就好了。另一个好处是，**因为辅助索引存放的是主键值，减少了辅助索引占用的存储空间大小。**

注：我们知道一次io读写，可以获取到16K大小的资源，我们称之为读取到的数据区域为Page。而我们的B树，B+树的索引结构，叶子节点上存放好多个关键字（索引值）和对应的数据，都会在一次IO操作中被读取到缓存中，所以**在访问同一个页中的不同记录时，会在内存里操作，而不用再次进行IO操作了**。除非发生了页的分裂，即要查询的行数据不在上次IO操作的缓存里，才会触发新的IO操作。

3.因为MyISAM的主索引并**非聚簇索引，那么他的数据的物理地址必然是凌乱的，拿到这些物理地址，按照合适的算法进行I/O读取，于是开始不停的寻道不停的旋转。聚簇索引则只需一次I/O**。（强烈的对比）

4.不过，如果涉及到大数据量的排序、全表扫描、count之类的操作的话，还是MyISAM占优势些，因为索引所占空间小，这些操作是需要在内存中完成的。

### 为什么 主键建议使用自增id作为聚簇索引 ###

1. 主键最好不要使用uuid，因为uuid的值太过离散，不适合排序且可能出现新增加记录的uuid，会插入在索引树中间的位置，**出现页分裂（比如之前的索引已经紧凑的排列在一起了，你此时需要在已经紧凑排列好的数据中插入数据就会导致前面已经排好序的索引出现松动和重构排序，但是使用自增id就不会出现这种情况了，因为新来的数据的id都是比前面的大，就会直接放在后面就行** **），**导致索引树调整复杂度变大，消耗更多的时间和资源。

**但是在分布式环境中，是不能使用自增id来作为主键，容易产生并发安全问题；**

补充：什么叫做页分裂：就是在操作系统中读取数据都是按页来读取的，不同的操作系统对页定义的大小不同，但**都是4k的倍数**，innodb引擎读取数据是一次读取16k,也就是说在innodb 的b+树中，一个节点的大小是16k，当你存储的key不是自增大主键的时候，就会导致你原来已经紧凑的页中需要重新插入新的数据，那么原来的页存储的数据就会被打乱，而且如果当前要插入数据的页的空间内存已经不够现在要存储的数据所需要的空间，那么就会进行页分裂，创建新的页来存放数据，并且会打乱原来存储好的数据

2. 聚簇索引的数据的物理存放顺序与索引顺序是一致的，即：只要索引是相邻的，那么对应的数据一定也是相邻地存放在磁盘上的。**如果主键不是自增id，它会不断地调整数据的物理地址、分页，当然也有其他一些措施来减少这些操作，但却无法彻底避免**。但如果是自增的id，它只需要一 页一页地写，索引结构相对紧凑，磁盘碎片少，效率也高。

###  ###

[a6a5ac03b0164c0dbaa834227e3a401f.png]: https://img-blog.csdnimg.cn/a6a5ac03b0164c0dbaa834227e3a401f.png
[9df4374ddf6b464994fa2148d430f979.png]: https://img-blog.csdnimg.cn/9df4374ddf6b464994fa2148d430f979.png
[cf3bd157753d449182396af3cac28691.png]: https://img-blog.csdnimg.cn/cf3bd157753d449182396af3cac28691.png
[d6c6830e4e084866a04185230614eeaf.png]: https://img-blog.csdnimg.cn/d6c6830e4e084866a04185230614eeaf.png