MySQL调优-索引

MySQL调优-索引

一：什么是索引？

​ 索引是一种数据结构，用来加快查询速度，索引条目的作用类似于指向表行的指针，从而使查询可以快速确定哪些行与WHERE子句中的条件匹配，并检索这些行的其他列值。所有MySQL数据类型都可以建立索引。

​ 我们可以将其理解为书的目录，没有索引时MYSQL查找数据是逐行查找，构建索引后查找数据是根据索引到一个小范围中逐行查找（这个小范围称之为页）。

二：索引的数据结构：

​ InnoDB存储引擎支持两种常见的索引：B+树索引和哈希索引

• ​ 哈希索引：InnoDB支持自适应的哈希索引

• ​ B+树索引：

​ B+树索引读取到的并不是数据的具体行，而是被查找数据所在的页， 数据库会将该页读取到内存中，在内存中查找到数据具体行。

B+树介绍：

概念
B+树是B树的一个升级版，相对于B树来说B+树更充分的利用了节点的空间，让查询速度更加稳定，其速度完全接近于二分法查找。

规则
（1）B+跟B树不同B+树的非叶子节点不保存关键字记录的指针，只进行数据索引，这样使得B+树每个非叶子节点所能保存的关键字大大增加；
（2）B+树叶子节点保存了父节点的所有关键字记录的指针，所有数据地址必须要到叶子节点才能获取到。所以每次数据查询的次数都一样；
（3）B+树叶子节点的关键字从小到大有序排列，左边结尾数据都会保存右边节点开始数据的指针。

从B+树中看出，通过索引定位到数据在的页，再将该页从硬盘读取到内存中，大大加快了查询速度。

• 页的大小（16k）
  使用show global status like ‘Innodb_page_size’;语句查看页的大小
  

• 3层B+树能存储多少数据？（实际情况下B+树一般为1~3层）
  我们假设叶子节点（数据节点）存储的每个数据为1k，那么每个叶子节点能存储16个数据，现在考虑前两层的指针节点，我们假设主键ID为bigint类型（不考虑反人类的varchar作主键），长度为8字节，而指针大小在InnoDB源码中设置为6字节，这样一共14字节，那么每页数据量16384/14 =1170，这样三层数据量为1170 * 1170 * 16 =21902400，即2000万左右。

三：提问：为什么Mysql（InnoDB引擎）不使用其他数据结构构建索引？

引言：Mysql通过IO次数衡量查询效率，一次IO读取的最小单位是页

• 哈希表：缺点很明显，不支持范围查找，不支持索引order by排序，不能确保每一列存储的数据量均衡，数据量大时哈希冲突概率高（需要较好的哈希冲突算法），但是注意MEMORY存储引擎使用的是哈希索引。
• 二叉树（AVL,红黑树）：数据量大时，树的深度过大，查询数据时需要频繁IO。
• B树：与B+树不同的是，B树每个节点都存储数据域，这导致了B树每个节点（页）存储的数据少于B+树，而一次IO的量是指定的，查询相同数据时B树无疑要进行更多的IO操作，且保存相同数据时，B树的深度更大。

四：索引的类型

• 普通索引：仅加速查询

• 唯一索引（Unique index）：加速查询 + 列值唯一（可以有null）

• 主键索引：加速查询 + 列值唯一（不可以有null）+ 表中只有一个

• 组合索引：多列值组成一个索引，专门用于组合搜索，其效率大于索引合并

• 全文索引：对文本的内容进行分词，进行搜索

  ps.索引合并，使用多个单列索引组合搜索

五：索引中关键词介绍

​ 聚集索引：

​ 当MySQL表中没有显示定义主键时：

1. 首先判断表中是否有非空的整形唯一索引,如果有,则该列为主键

2. 如果没有符合条件的则会自动创建一个6字节的主键（rowid）

聚集索引为 ：主键 或 没有主键时的整形唯一索引 或 rowid

​

​ 回表：

​ 这里需要了解一下聚集索引与非聚集索引在B+树中存储的内容

​ 例表：
​

​ 表中索引信息（主键id ，唯一索引account，普通索引 index_username ）：
​

• ​ 聚集索引：

  ​ B+树中每一个叶子节点存储的数据为 该聚集索引（此时为主键id）所对应的所有信息：