mysql原理、索引、优化-【公司内部培训】

一壶明月

已于 2022-06-30 17:05:06 修改

阅读量2.9k

点赞数 12

分类专栏： mysql 文章标签： mysql 数据库 database

于 2022-03-01 18:50:29 首次发布

本文链接：https://blog.csdn.net/weixin_44032384/article/details/123198956

版权

mysql 专栏收录该内容

4 篇文章 0 订阅

订阅专栏

一、sql的执行过程

在这里插入图片描述

注意：mysql8.0 已经移除了查询缓存的功能

在这里插入图片描述

引擎层

引擎的选择是可以针对表级别的，每个表都可以单独设置存储引擎；

最常见两种： InnoDB（默认）、MyISAM
其它：Memory、CSV、Archive

特点：
InnoDB：支持行锁、表锁、事务；
MyISAM：支持表锁，不支持行锁，不支持事务；

应用场景：
InnoDB适合：经常更新的表，存在并发读写或者有事务处理的业务系统。
MyISAM适合：应用范围比较小，通常用于只读或以读为主的工作。

扩展
怎么快速向数据库插入 100 万条数据？
我们有一种先用 MyISAM 插入数据，然后修改存储引擎为 InnoDB 的操作。

二、索引

索引存储模型推演

二分查找（Binary Search）

二分查找的一种思想，也叫折半查找，每一次，我们都把候选数据缩小了一半。如果数据已经排过序的话，这种方式效率比较高。

二叉查找树（BST）

（BST Binary Search Tree）
左子树所有的节点都小于父节点，右子树所有的节点都大于父节点。在这里插入图片描述

二叉查找树既能够实现快速查找，又能够实现快速插入。但是二叉查找树有一个问题：
树层级会越来越深，效率也不够高；

https://www.cs.usfca.edu/~galles/visualization/BST.html
插入的数据刚好是有序的，2、6、11、13、17、22它会变成链表（我们把这种树叫做“斜树”），这种情况下不能达到加快检索速度的目的，和顺序查找效率是没有区别的。
在这里插入图片描述

因为左右子树深度差太大，这棵树的左子树根本没有节点——也就是它不够平衡。
所以，我们有没有左右子树深度相差不是那么大，更加平衡的树呢？

平衡二叉树（AVL Tree）

（左旋、右旋）
AVL Trees (Balanced binary search trees)
平衡二叉树的定义：左右子树深度差绝对值不能超过 1。

多路平衡查找树（B-Tree ）

（分裂、合并）
注意:之前有看到有很多文章把B树和 B-tree理解成了两种不同类别的树，其实这两个是同一种树;
B 树在枝节点和叶子节点存储键值、数据地址、节点引用。
比如我们画的这棵树，每个节点存储两个关键字，那么就会有三个指针指向三个子节点。在这里插入图片描述

BTree演示地址： https://www.cs.usfca.edu/~galles/visualization/BTree.html

max Degree限制的是路数，也就是最大子节点数量，所以每个节点的存储的关键字数量会比路数小1；在这里插入图片描述

在这里插入图片描述

从这个里面我们也能看到，在更新索引的时候会有大量的索引的结构的调整；
插入数据时，从叶子节点开始添加，往父节点提取，进行分裂和合并操作，直至传递到根节点也满了，就会增加层高，让根节点又回到只存储一个关键字，然后继续这样一直分裂合并下去；

相对比平衡二叉树来说，同样的数据量，这种结构已经能极大的降低层高了；

特点：一个节点存多个关键字，分叉数（路数）永远比关键字数多 1；而且数据和索引关键字放一起；

B+树（B+Tree）

（加强版多路平衡查找树）
B+树是B树的一个升级版，而且更加充分的利用了节点空间；

特点：

1、非叶子节点不存数据，这样使得每个非叶子节点可以保存更多的关键字，树层级更小；
2、叶子节点冗余了所有父节点的关键字，而且只有在叶子节点才会存放数据；
3、分叉路数与关键字数量相等；

# InnoDB 的 B+Tree中（还做了些优化）：
4、所有叶子结点，数据页之间是双向链表，每一页内部数据是单向链表；
5、主键索引的叶子结点存数据，辅助索引存的是主键值；

结构图：

InnoDB中，假设每个磁盘块能存储3个键值及指针信息

主键索引：在这里插入图片描述

辅助索引：在这里插入图片描述

联合索引（复合索引）：
在这里插入图片描述

优点：

可能上图数据较少，看不出B+Tree的优点，下面做一个推算：
InnoDB存储引擎中页的大小为16KB，一般表的主键类型为INT（占用4个字节）或BIGINT（占用8个字节），指针类型也一般为4或8个字节，也就是说一个页（B+Tree中的一个节点）中大概存储16KB/(8B+8B)=1K个键值（因为是估值，为方便计算，这里的K取值为〖10〗^3）。

也就是说一个深度为3的 B+Tree 辅助索引可以维护10^3 * 10^3 * 10^3 = 10亿 条记录。

实际情况中每个节点可能不能填充满，因此在数据库中，B+Tree的高度一般都在2~4层。
mysql 的InnoDB存储引擎在设计时是将根节点常驻内存的，也就是说查找某一键值的行记录时最多只需要1~3次磁盘I/O操作。

衍生出来的概念：
从上图可以看出，如果走辅助索引的查询，还只能得到索引字段，和主键，
如果包含了需要查询的字段，这就叫做覆盖索引，
否则还需要通过主键值去主键索引拿到其它字段的数据，这个过程就叫做回表；

注意：索引覆盖，不等于覆盖索引，索引覆盖是指一个select语句，所有的查询条件都走了索引。
所以索引覆盖应该是包含了覆盖索引的；

总结

总体来看，都是采用二分法和数据平衡策略来提升查找速度；
在演变过程中，通过IO磁盘读取数据的原理，进行一步步的演变，让节点的空间更合理的运用起来，从而使树的层级减少，达到快速查找数据的目的；

目前大部分数据库系统及文件系统都采用B-Tree或其变种B+Tree作为索引结构；

关于更多索引知识，推荐文章：
https://blog.csdn.net/u013967628/article/details/84305511
https://blog.csdn.net/zhibo_lv/article/details/117807830
https://zhuanlan.zhihu.com/p/364295145

索引的各种概念

主键索引

字面意思，就是由主键建立的索引；

扩展：

# mysql，innodb中，如果没有设置主键，会怎么样？
mysql会选择表中一个唯一且不为空的字段作为主键，
否则还是会维护一个默认的，不可见的，长度为6的 row_id 自增值作为主键；
# 如果自增主键ID达到最大值了，会怎么样？
达到最大值后，又会从0开始，所以会导致主键冲突的问题；
如果业务数据量有这么大，可以考虑使用雪花ID；

聚集索引、非聚集索引（聚簇索引、非聚簇索引）

叶子节点存所有字段数据，就是聚集索引，比如InnoDB的主键索引；
索引文件和数据文件是分离的，叶子节点只存磁盘地址，就是非聚集索引，比如MyiSAM，以及InnoDB中其他的非主键索引（存的是主键值）；

前缀索引

字符串太长会导致索引数据太大，所以可以使用前缀（比如前20个字符）建立索引；
它的查询过程是：使用前缀先过滤出一部分数据，再回表拿到完整的字段值再次进行比较，进一步筛选数据；

覆盖索引、回表

已经在B+Tree 中解释了，这里就不多说了；

页分裂

页分裂，其实就是字面意思那么简单；
如果一页的数据满了，就会申请下一页的磁盘空间，但如果这个时候需要在该页的中间插入数据，那么就会导致页分裂；

扩展点：

# 使用自增主键的好处：
1、在主键索引中，由于主键是自增长的，所以每次插入数据都是追加操作，不会触发页分裂，不涉及到挪动其它数据；
2、另外，主键值越小，那么辅助索引中的叶子结点占用的空间也就小，当然也不能完全最求这个，还是要结合业务来看，可以使用雪花ID，但是不推荐无序的UUID作为主键；

# 哪些情况不用普通自增id
1、容易导致主键重复的场景，比如数据迁移，或者导入数据时，容易出现主键重复异常；
2、考虑到数据库扩展的情况，分库分表也会有主键重复的问题；
所以可以使用雪花ID，既不会重复，在同一节点上也是保持自增的；

最左匹配原则

因为索引是一个排好序的数据结构，聚集索引是多个字段按其字段顺序建立的排序，所以是先匹配到左边的字段，才能去匹配右边的字段；

索引下推

在辅助联合索引中，正常情况是按最左前缀原则，如果开头字段的判断条件是左模糊匹配，那么一般来说就是只能匹配到开头的字段，然后回表，拿到数据后再次过滤其它字段；

-- 举例：
-- a表有联合索引：  index_name_age_position
select * form a   where name like "Lilei%"   and age=10    and position="manager";

mysql5.6之前是这样的，5.6就引入了索引下推优化，就是对于这种情况，会直接对联合索引中包含的所有字段先做判断，过滤好了之后再回表，这样就有效的减少了回表次数；

三、事务

事务的ACID属性

-- 原子性（Atomicity）  # 使用 undo log来实现的，如果事务执行过程中出错或者用户执行了rollback，系统通过undo log日志返回事务开始的状态
-- 一致性（Consistent）# 使用 redo log来实现，只要redo log日志持久化了，当系统崩溃，即可通过redo log把数据恢复。
-- 隔离性（Isolation） # 通过锁以及MVCC,使事务相互隔离开。
-- 持久性（Durable）   # 通过回滚、恢复，以及并发情况下的隔离性，从而实现一致性。

并发事务带来的问题

-- 脏写： 更新丢失；
-- 脏读：读取到了其它事务修改但还没有提交的数据；
-- 不可重复读： 同一个事务里先后读取某条数据结果不一致（中途被其他事物修改了）；
-- 幻读： 读取到了其它事务已提交的新增数据；

事务隔离级别

读未提交：
 	-- 其它事务修改但未提交，我也可见；
 	-- 导致脏读；
读已提交： 	
	-- 其它事务提交了，我才可见；
	-- 解决了脏读问题；
可重复读：（mysql默认）
	-- 同一条数据先后查询时，结果是一致的；
	-- 当然，这也会导致其它事务提交的修改，我无法及时得知，比如：另一个事物和我插入同一条数据，我查不到，但是我插入却失败，就像有幻觉一样，所以这就还会有幻读的问题；
可串行化：
	-- 强制事务串行，避免了幻读问题；
	-- 它会在读取的每一行数据上加锁，又会导致大量的锁等待，所以实际生产环境很少用这种隔离级别；

隔离级别	脏读	不可重复读	幻读
读未提交（Read uncommitted）	√	√	√
读已提交（Read committed）	x	√	√
可重复读（Repeatable read）	x	x	√
可串行化（Serializable）	x	x	x

Mysql的InnoDB默认的事务隔离级别是可重复读
可重复读的隔离级别下使用了MVCC(multi-version concurrency control)机制，select读取的是快照，但是更新插入删除操作，就是操作的当前版本；

四、sql执行原理

order by 的Using filesort文件排序原理

单路排序：
	-- 一次性取出所有字段，在sort buffer中排序，直接返回结果；
双路排序：
	如果某一条数据，需要查询的字段总内存过大，则采用此方法：
	-- 先将排序字段和id取出来，在sort buffer中排序，然后回表去取数据；
	
-- mysql通过比较系统变量 max_length_for_sort_data（默认1024字节）的大小和需要查询的字段总大小来判断使用那种模式；
-- sort buffer默认1M， 如果满了，则会写入临时文件进行归并排序；

Join关联查询的过程

1、确定驱动表与被驱动表：
	-- 两表join查询时，mysql会自动选择，数据量小的表为驱动表，大表则是被驱动表；
	-- left join： 左表为驱动表；
	-- rigth join：右表为驱动表；

2、算法的选择： join查询常见有两种算法：
	1、NLJ算法： 嵌套循环连接 Nested-Loop Join
		-- 遍历小表，取关联字段值作为条件，去大表中查询数据，将结果拼接后返回；
	2、BNL算法：基于块的循环连接 Block Nested-Loop Join
		-- 先将小表存join_buffer中，然后遍历大表，取关联字段值作为条件，去join_buffer中查询数据，拼接结果返回；
		-- join_buffer配置项为： join_buffer_size  默认值256K
		-- 如果join_buffer一次装不下小表，则将小表分段处理，过程同上，最后将结果汇总返回，所以这将会导致大表多次遍历；
		
	-- 在被驱动表（大表）的关联字段有索引时，会选择NLJ算法；
		-- 如果没有索引，那么遍历小表，去查询大表每次都是磁盘全表扫描，效率就会很低，所以此时就会选择第二种算法（BNL算法），在内存中扫描；

五、优化

索引覆盖规则

1、单个字段模糊查询时，最左前缀匹配，才可以走索引； -- like "startWith%"
	--  根本原因就是索引是排好了序的，开头的字符确定，才能与索引值比较大小，然后直接通过叶子节点进行区间访问；否则只能挨个判断是否包含该字符串，那么就是全表扫描了；
2、联合索引也是最左字段匹配；
	-- 要求从最左边的字段开始完全比较（如果是模糊匹配就不行），才能继续下个字段的比较，但是sql语句里面的条件字段的抒写顺序不影响，这个msyql的优化器会给我们优化；
	
3、什么时候明明可以走索引却还是放弃走索引？
	-- 符合条件的数据量占比比较大时，因为索引还需要回表查询数据，所以mysql就干脆全表扫描了；
	-- 字段值的离散度太低，可能也会放弃索引，根本原因同上，因为离散度低很可能出现结果集数据量占比太大；

索引设计原则

1、代码先行，索引后上，基于慢sql做优化；
 	-- 建立联合索引，尽量覆盖查询条件；
 	
2、联合索引尽量覆盖条件，一张表可以建立三两个联合索引，去满足不同的查询场景；
	-- 注意，索引越多，查询索引覆盖率越高，但是数据增删改如果涉及到索引字段，还需要同步索引数据，效率降低；所以要结合实际场景把握好这个度；
	
3、不要在小基数字段上建立索引；
	-- 也叫数据的离散度太低，可能导致mysql自己放弃走索引，比如del字段，就只有0和1两种值；
	
4、长字符串可以采用前缀索引；
	-- 字符串太长会导致索引数据太大，所以可以使用前缀（比如前20个字符）建立索引；
	-- 它的查询过程是：使用前缀先过滤出一部分数据，再回表拿到完整的字段值再次进行比较，进一步筛选数据；
	
5、where与order by 冲突时，一般都是优先满足where，走索引筛选出少部分数据，然后再做排序；

EXPLAIN分析sql

EXPLAIN简介

优化总结

1、select *
	-- select * 会多一次表字段查询，但其实效率影响不大，是否应该使用，应该从“字段的不确定性，每次都是查询最新的所有字段”的角度来考虑；

2、where 与 order by 
	-- 后面的条件需要符合索引最左前缀法则，尽量覆盖索引；
	-- order by如果未覆盖索引，会出现Using filesort

3、group by
	-- 与 order by 类似， 本质就是先排序后分组，所以，如果不需要排序则可以加上 order by null禁止排序；
	-- where高于having, 尽量使用where条件；

4、in 和 exists 
	-- 原则：遍历小表，效率更高
	B表数据量小的时候:   select * from A where id in( select id from B); 执行过程：遍历B表，取id值去A表查询；
	A表数据量小的时候： select * from A where exists( select 1 from B where B.id=A.id ); 执行过程： 遍历A表，取id值去查询B表中是否存在；
 
5、limit 
	-- limit 10000,10; 查询大表靠后的数据分页时，它要先把符合条件的数据都查出来，然后只取后面10条数据，执行效率非常低，优化：
	select * from order t1 join 
	(select id from order limit 10000,10) t2 
	on(t1.id=t2.id);  -- 先通过走索引查询到分页后的10条主键索引值，然后自连接查询
6、join
	-- 给关联字段加索引（准确的说是被驱动表的关联字段），有利于让mysql做json时选择 NLJ 算法；
	
7、count(*)
	count(*) 与 count(1) 
		-- 效率差不多，count(*)其实并不会取所有字段，这个mysql做了优化，不取值，直接按行累加计数；
	count(字段) 与 count(id)
		-- 如果是简单的cout查询，字段有索引，则cont(字段)效率更高，因为二级索引只存索引值和主键id，数据页会比主键索引的数据页更少，IO就会更少，所以会更快；
		-- 注意：count(字段) 的统计是非空计数（非空值的计数）；