Mysql索引

发表于 | 分类于 ，

《DDL建表操作和相关约束》中有部分索引内容。

基础知识

局部性原理

时间局部性
空间局部性

磁盘预读

  • 减少I/O：一个是减少访问的次数，一个是减少IO的量。

  • 一般顺序读写，随机读写效率非常低。

  • 每次读一个小单元（页通常为4k）
    预读的长度一般为页（page）的整数倍

  • 索引放到磁盘（文件系统）中的

  • 索引是帮助 MySQL 高效获取数据的数据结构

  • 索引的文件存储形式与存储引擎有关
    在这里插入图片描述
    注：不同的存储引擎，数据文件和索引文件存放的位置是不同的。因此有了分类：

    1. 聚簇索引：数据和文件放在一起：innodb
      ▪ create table tid(id int) engine=innodb 默认innodb
      ▪ .frm：存放的是表结构
      ▪ .ibd：存放数据文件和索引文件
      ▪ mysql的innodb存储引擎默认情况下会把所有的数据文件放到表空间中（D:\mysql-5.7.26-winx64\data\ibdata1），不会为每个单独的表保存一份数据文件，如果需要将每一个表单独使用文件保存，设置如下属性：set global innodb_file_par_table=on;

      表空间，类似高中教师后面的柜子，每个学生有一个小格子。

    2. 非聚簇索引：数据和索引单独一个文件：MyISAM
      ▪ .frm：存放的是表结构
      ▪ .MYI：存放索引数据
      ▪ .MYD：存放实际数据

  • 索引文件的结构
    – hash
    – 二叉树
    – B树
    – B+树

哈希表：哈希冲突

  • key对应过来就是列名的值。value可能表示一整行数据。
  • 哈希表可以完成索引的存储。每次在添加索引的时候，需要计算指定列的哈希值，取模运算后计算出下标，将元素插入下标位置即可。
  • 适合场景：
    等值查询
    表中的数据是无序数据（范围查找的时候比较浪费时间，需要遍历操作）
不合适的理由:
  1. 在企业中多数的查询时范围查询还是等值查询？
    范围查询。所以hashb表不是特别合适。
  2. 哈希表使用时，需要将全部数据加载到内存，比较耗费内存的空间，也不是很合适。

树

在树的结构中，左子树必须小于根节点，右子树必须大于根节点。如果是多叉树，从左到右是有序的。
在这里插入图片描述
注：二叉树及其变种都不能支持索引，因为树的深度无法控制或者插入数据的性能比较低。

B树（B-树）
本质是多叉树

一、B树特点

键值（key），即表中记录的主键，列的值放到这个地方
指针，存储子节点地址信息
数据（data），即表记录中除主键外的数据，一整行的数据文件

  1. 所有键值分布在整棵树中
  2. 搜索有可能在非叶子节点结束，在关键字全集内做一次查找，性能逼近二分查找
  3. m阶的B树每个节点最多拥有m个子树
  4. 根节点至少有2个子树
  5. 分支节点（除根节点和叶子节点）至少拥有m/2棵子树
  6. 叶子节点在同一层，每个节点最多可以有m-1个key

在这里插入图片描述

  1. 从磁盘读取的4k整数倍空间，反映到图上一个节点就是一个4k磁盘块。
  2. 假如读28，先读入1个4k空间（磁盘块1），发现是在16和34之间，于是再读入一个4k空间（磁盘块3），发现是在25和31之间，于是再读入一个4k空间（磁盘块8），有28，里面正好有数据，读出。所以一个读了3次，12k数据。这个过程是非常快的。
  3. 存在问题：每个磁盘块只有4k，同时还包括了一整行的数据文件，而这个数据文件大小是没法预估的，假如这个数据文件大小是1KB，键值和指针忽略不计，那么一个磁盘块只能存4条数据。一个3层的B树，最多只能存64条数据。所以如果数据多，那么树的深度就会过大，IO次数和IO的量过多

二、能存索引，缺点

  1. 每个节点都有key，同时也包含data，而每个页存储空间是有限的，如果data比较大的话，会导致每个节点存储的key数量变小，那么区间越不明确，查询深度越高。
  2. 当存储的数量很大的时候，会导致深度较大，增大查询时磁盘I/O次数，对查询性能产生影响。
B+树

B+ Tree是B Tree的优化。
一、B+树特点

  1. B+树每个节点可以包含更多的节点，这个做的原因有两个，第一个是为了降低树的高度，第二个是将数据范围变为多个区间，区间越多，检索越来越快
  2. 叶子节点放数据和key，非叶子节点放指针和key
  3. 叶子节点两两指针相互连接（符合磁盘预读），顺序查询性能更高

在这里插入图片描述

  1. 假如读28，还是12k数据空间，3次读取，好处是存储数据量变多。假设一个p1和28占10个字节，那一个磁盘块（4k=4096字节）看成4000字节，就能放400条数据。三层结构就能放4004004=64万条记录。（一般情况下，三层结构百万级别数据肯定没有问题）
  2. 如果是范围查询，
  3. 在B+树有两个头指针。一个指向根节点，一个指向关键字最小的叶子节点。而且所有叶子节点之间是一种链式环结构，因此可以对B+树进行两种查找运算：满足对于主键的范围查找和分页查找，也满足从根节点开始的随机查找。

二、优点

  1. 满足范围查找，也可以满足顺序查找。
  2. 支持比较大的数据量。

问题：如果存了1万条记录，然后进行了一次更新（比如加一条记录），更新完了之后怎么办？索引需要变吗？

索引的创建

 1、自动：当在表上定义一个PRIMARY KEY 或者UNIQUE 约束条件时,Oracle数据库自动创建一个对应的唯一索引
 2、手动：用户可以创建索引以加速查询
在每次读取的时候，一旦用了索引，相当于做了两件事：先去遍历索引文件，再去遍历数据文件（和存储引擎有关）。所以数据量很小，没必要建索引。

建议把主键设置为自增的，并且主键列是一个索引列。这关系到索引的维护。

mysql innodb—B+Tree，叶子节点直接放置数据

存储引擎是innodb时，索引和数据文件放在一起的

在这里插入图片描述
一、如果对主键创建索引（id）。根据索引找到位置，直接返回数据。
二、如果我们给name创建索引，上图（A索引）改变，图中key就不再是主键1~6那些数字了，而是变成了teacher.lu……，叶子节点存放数据不再是整条记录。而是变成了主键id1~6。所以需要两次，第一次找普通列的B+树，找到id后，再到上图A索引中查找主键列的B+树，然后取出数据。这个过程就称为回表
回表操作一般针对于innodb。

mysql MyISAM—B+Tree

存储引擎是MyISAM时，索引和数据文件分开放

和innodb区别是：
最后叶子节点上存放的不是整行记录了，而是地址值。
在这里插入图片描述

索引的分类

▪ mysql索引的五种类型：主键索引、唯一索引、普通索引和全文索引、组合索引。
▪ 通过给字段添加索引可以提高数据的读取速度，提高项目的并发能力和抗压能力。

自增id怎么维护？
自增锁来维护自增性，保证id不会乱。

一、主键索引
主键是一种唯一性索引，但它必须指定为PRIMARY KEY，每个表只能有一个主键。
主键不是一个列，最好生成自增。因为页分裂和页合并都是很浪费性能的。

当定位到叶子节点时候，有n多个磁盘页。现在要往里面插入数据：
如果是自增，那么每次插入的就是一个较大值，直接在后面插入即可，几乎没有成本。
而如果不是自增的话，假设要插入的磁盘块已经满了，就需要申请一个新的空间，然后把原来磁盘块上的数据分成两份（页分裂），如果之后不再插入数据到那个块，空间就浪费了。如果之后删除了数据，那么就又会涉及到页合并。
都涉及到IO，有性能损耗。而且浪费了空间。
【索引维护时候的情况分类】

二、唯一索引
索引列的所有值都只能出现一次，即必须唯一，值可以为空。

如果确定了是唯一约束的话，就是系统自动帮我们建索引；
但如果一个列name数据库层面没有给定约束（unique），但是代码层面就限定好了是唯一的，那此时给这个列建索引的时候，我们是建成唯一索引还是普通索引？
性能方面有差别，需要考虑。普通索引需要回表。

三、普通索引 覆盖索引
基本的索引类型，值可以为空，没有唯一性的限制。（覆盖索引，是回表中最基本的一个优化点）

当采取这种索引的时候，有可能会产生覆盖索引。有可能的话，尽量使用覆盖索引。
在这里插入图片描述
本来查询的时候必须要进行回表，遍历两次b+树，但是由于查的是id，可以把这个过程简化为1次。这就叫覆盖索引。使用有局限性。必须要查询对应的主键的时候才能使用。

四、全文索引
全文索引就是检索对应关键字。MyISAM支持，Innodb在5.6之后支持。但一般不用，因为全文检索有专门工具，es，solr等。
全文索引的索引类型为FULLTEXT。全文索引可以在varchar、char、text类型的列上创建
搜索引擎 倒排索引

五、组合索引
多列值组成一个索引，专门用于组合搜索
如果查询时候总是那几个列的话，可以把一些列组合到一起，创建索引。创建规则：最左匹配原则。
比如 id name age address ，要给name和age创建索引，首先要确定是哪个在前。如果是name在前，那意味着匹配的时候必须要先匹配name，name有了值后再去匹配age。

如果有一类查询是查name和age，有一类查询只查age。那么现在除了给name和age创建联合索引（name放前面），要不要给age单独创建索引？需要。因为最左匹配原则，如果没有name，age根本查不了。但如果把age放到前面，就不需要再维护一套索引。
尽量减少索引创建，因为索引是磁盘存储的，索引越多，维护越麻烦。这些都需要衡量。

索引下推
在回表之前，就做了一次判断筛选，这样能提高执行的效率。
比如下面搜索语句，name和age建立组合索引，本来没有索引下推时，根据name=zhangsan回表会返回这四条记录，有了索引下推，提前判断age是否为10，回表只返回1和4。
在这里插入图片描述

Mysql存储引擎

memory虽然快，性能高，但是一旦断电就会出现数据丢失，持久化存在问题。
在这里插入图片描述
Mysql默认innodb，如果想换可以从my.ini中修改

索引面试题

▪ 为什么加索引能优化慢查询？
▪ 你知道哪些数据结构可以提高查询速度？
▪ 那这些数据结构既然都能优化查询速度，Mysql为何选择使用B+
树？