Mysql（二）索引原理及其SQL优化

星河九天

已于 2022-11-06 16:45:24 修改

阅读量299

点赞数

文章标签： mysql sql 数据库

于 2022-10-28 00:24:03 首次发布

本文链接：https://blog.csdn.net/m0_70418130/article/details/127542422

版权

索引

索引分类：主键索引、唯一索引、普通索引、组合索引、以及全文索引（elasticsearch）；

主键索引

非空唯一索引，一个表只有一个主键索引；在 innodb 中，主键索引的 B+ 树包含表数据信息；

PRIMARY KEY(key)

唯一索引

不可以出现相同的值，可以有 NULL 值；

UNIQUE(key)

普通索引

允许出现相同的索引内容；

INDEX(key)
-- OR
KEY(key[,...])

组合索引

对表上的多个列进行索引

INDEX idx(key1,key2[,...]);
UNIQUE(key1,key2[,...]);
PRIMARY KEY(key1,key2[,...]);

使用组合索引可以减少开销，因为分开的话，一个索引需要建立一个B+树，而组合索引只需要一颗B+树。

全文索引

将存储在数据库当中的整本书和整篇文章中的任意内容信息查找出来的技术；关键词 FULLTEXT;
在短字符串中用 LIKE % ；在全文索引中用 match 和against ；

FULLTEXT(KEY)
SELECT * FROM article WHERE MATCH(title，content)AGAIN('查询字符串');

上面的几种索引部分可以分为两种类型：聚集索引和辅助索引，在之后讨论mysql事务的锁机制时，一般是以这两类索引去统一分别。

聚集索引

主键索引就是聚集索引，一张表中只有一个聚集索引（因为也只有一个主键索引）。

特点：
按照主键构造的 B+ 树；叶子节点中存放数据页；
聚集索引中数据也是索引的一部分；

辅助索引

除了聚集索引（主键索引）的其他索引都是辅助索引；例如唯一索引，普通索引等。

特点：
叶子节点不包含行记录的全部数据；
辅助索引的叶子节点中，除了用来排序的 key 还包含一个 bookmark ；该书签存储了聚集索引的key；

例子：S0001是辅助索引，生成的B+树的叶子节点存的是主键的值，通过辅助索引查数据时需要先找到主键，再在主键的表中找到相应的数据。
在这里插入图片描述

主键选择

innodb 中表是索引组织表，每张表有且仅有一个主键；

如果显示设置 PRIMARY KEY ，则该设置的 key 为该表的主键；
如果没有显示设置，则从非空唯一索引中选择；
只有一个非空唯一索引，则选择该索引为主键；
有多个非空唯一索引，则选择声明的第一个为主键；

没有非空唯一索引，则自动生成一个 6 字节的 _rowid 作为主键；

约束

为了实现数据的完整性，对于 innodb，提供了以下几种约束，primary key，unique key，foreign key，default，not null；

外键约束

外键用来关联两个表，来保证参照完整性；MyISAM 存储引擎本身并不支持外键，只起到注释作用；而 innodb 完整支持外键，并具备事务性；

create table parent (
id int not null,
primary key(id)
) engine=innodb;
create table child (
id int,
parent_id int,
foreign key(parent_id) references parent(id)
ON DELETE CASCADE ON UPDATE CASCADE
) engine=innodb;
-- 被引用的表为父表，引用的表称为子表；
-- 外键定义时，可以设置行为 ON DELETE 和 ON UPDATE，行
为发生时的操作可选择：
-- CASCADE 子表做同样的行为
-- SET NULL 更新子表相应字段为 NULL
-- NO ACTION 父类做相应行为报错
-- RESTRICT 同 NO ACTION
INSERT INTO parent VALUES (1);
INSERT INTO parent VALUES (2);
INSERT INTO child VALUES (10, 1);
INSERT INTO child VALUES (20, 2);
DELETE FROM parent WHERE id = 1;

约束与索引的区别

创建主键索引或者唯一索引的时候同时创建了相应的约束；
约束时逻辑上的概念；
索引是一个数据结构既包含逻辑的概念也包含物理的存储方式；

索引存储

innodb 由段、区、页组成；段分为数据段、索引段、回滚段等；区大小为 1 MB（一个区由 64 个连续页构成）；页的默认值为 16k；页为逻辑页，磁盘物理页大小一般为 4K 或者 8K；为了保证区中的页的连续，存储引擎一般一次从磁盘中申请4~5 个区；
在这里插入图片描述

页

页是 innodb 磁盘管理的最小单位；默认16K，可通过innodb_page_size 参数来修改；

特点：
B+ 树的一个节点的大小就是该页的值；
主键B+树非叶子节点中的页数据存储的是索引，叶子节点存储的是索引和表的行数据。

B+树

全称：多路平衡搜索树，减少磁盘访问次数；用来组织磁盘数据，以页为单位，物理磁盘页一般为 4K，innodb 默认页大小为16K；对页的访问是一次磁盘 IO，缓存中会缓存常访问的页；

特征：
非叶子节点只存储索引信息，叶子节点存储具体数据信息；
叶子节点之间互相连接，方便范围查询；
每个索引对应着一个 B+ 树；

使用B+树的原因：

树的高度低，减少磁盘IO次数
范围查找复杂度低

其他支持范围查找的数据结构：红黑树（复杂度高），跳表。

Buffer pool

目的：buffer pool是为了减少磁盘IO的读写次数。

假如没有buffer pool，则每次查询都会从磁盘中读取，进行IO操作。
因此会在内存中专门取一大块区域用作Buffer pool用来保存一些已经读过的页和周围的页数据（空间局部性），这样的的话当下次查询数据时会在Buffer pool查询是否存在需要的页，减少读写IO次数。
当修改数据时，同样会先修改buffer pool中的数据，被修改的页称为脏页，一般会采用redo log持久化机制，将脏页统一写入到磁盘中。

buffer pool涉及到三个链表：
在这里插入图片描述

free list 组织 buffer pool 中未使用的缓存页；
flush list 组织buffer pool 中脏页，也就是待刷盘的页；
lru list 组织 buffer pool 中冷热数据，当 buffer pool 没有空闲页，将从 lru list 中最久未使用的数据进行淘汰；
buffer pool 优化了LRU方法：
在这里插入图片描述
如图可以看到Buffer pool中5/8的区域为new sublist，3/8的区域为old sublist。有以下两种优化方案：

当数据页进入pool缓冲池，优先进入old sublist，页被访问时会进入new sublist，以解决预读失效的问题。
页被访问，且在old sublist停留时间超过配置阈值的，才进入new sublist，以解决批量数据访问，大量热数据淘汰的问题。

Change buffer

假如一个需要修改的页数据没有在buffer pool中，我们需要怎么操作：

将数据页调入到buffer pool中，一次随机磁盘IO
更新buffer pool中的数据，一次内存IO
将修改写入redo log，一次磁盘顺序写IO

看起来操作还行，但是如果在写多读少的场景下的话，我们有更好的方法，也就是innodb的change buffer。

Change buffer 缓存非唯一索引的数据变更（DML 操作，只记录操作，不记录结果），当访问这个数据页或者定期时间到达Change buffer 中的数据将会异步 merge 到磁盘当中；

因此对于刚刚的需要修改的页数据没有在buffer pool中，使用change buffer的操作：

将修改的操作写入到change buffer中，一次内存IO
写入日志到redolog中，等待触发merge，一次磁盘顺序写IO
可以看到使用change buffer的话在写多读少的场景下会节省最耗时的磁盘IO读写的次数。

change buffer不适用的场景：

唯一索引的场景：唯一索引需要判断是否冲突，也就是是否唯一，这个判断需要全局扫描，需要从磁盘读数据，change buffer也就没什么用了。
写少读多的场景：因为修改数据页后读取该页会触发merge，而我们的目的就是为了把多次数据页的修改通过一次merge更新到磁盘中，如果读数据的场景多了，那么merge的次数多，也不会减少IO操作次数了。

当数据不在buffer pool中，修改页数据后然后读取数据的步骤，理解一下buffer pool怎么和change buffer工作的：
修改：

在buffer poo匹配不到页数据；
在change buffer中记录该数据的修改操作；
读取：
在buffer pool 中匹配不到页数据；
在磁盘中读取页数据；
change buffer中读取做merge操作，放回buffer pool中；

自增id

超过类型最大值会报错；
bigint 类型的范围： $2^{63},2^{63}-1)$ ;
假设采用 bigint 类型的话，1 秒插入 1 亿条数据，大概需要 5849 年才会用完索引；

最左匹配原则

对于组合索引，从左到右依次匹配，遇到 > < between like就停止匹配会采用遍历查询。索引组合索引时尽量避免使用范围查询。

覆盖索引

从辅助索引中就能找到数据，而不需通过聚集索引查找；利用辅助索引树高度一般低于聚集索引树；较少磁盘 IO；所以用select时，尽量避免使用 select * from …。应该将所需要的列都标明。

索引下推

为了减少回表次数，提升查询效率；在 MySQL 5.6 的版本开始推出；
MySQL 架构分为 server 层和存储引擎层；
没有索引下推机制之前，server 层向存储引擎层请求数据，在server 层根据索引条件判断进行数据过滤；
有索引下推机制之后，将部分索引条件判断下推到存储引擎中过滤数据；最终由存储引擎将数据汇总返回给 server 层；