2024年最新通过栗子来学习MySQL高级知识点（学习，复习，面试都可）(4)，2024年最新阿里P7大佬手把手教你

本文链接：https://blog.csdn.net/2401_84975387/article/details/138859581

网上学习资料一大堆，但如果学到的知识不成体系，遇到问题时只是浅尝辄止，不再深入研究，那么很难做到真正的技术提升。

一个人可以走的很快，但一群人才能走的更远！不论你是正从事IT行业的老鸟或是对IT行业感兴趣的新人，都欢迎加入我们的的圈子（技术交流、学习资源、职场吐槽、大厂内推、面试辅导），让我们一起学习成长！

在这里插入图片描述

B+树优于B树原因

b+树的中间节点不保存数据，可以容纳更多的节点元素
所有的叶子结点使用链表相连，有助于区间查找和遍历
B+树的内部节点只存放键，不存放值，因此，一次读取，可以在内存页中获取更多的键，有利于更快地缩小查找范围。 B+树的叶节点由一条链相连，因此，当需要进行一次全数据遍历的时候，B+树只需要使用O(logN)时间找到最小的一个节点，然后通过链进行O(N)的顺序遍历即可。而B树则需要对树的每一层进行遍历，这会需要更多的内存置换次数，因此也就需要花费更多的时间

B树的话，就需要进行每一层的递归遍历
相邻的元素可能在内存中不相邻，所以缓存命中性没有B+树好
在这里插入图片描述

什么是覆盖索引

索引是高效找到行的一个方法，但是一般数据库也能使用索引找到一个列的数据，因此它不必读取整个行。b+索引叶子节点存储了它们索引的数据；当能通过读取索引就可以得到想要的数据，那就不需要读取行了。一个索引包含了（或覆盖了）满足查询结果的数据就叫做覆盖索引。

1.覆盖索引是一种数据查询方式，不是索引类型
2.在索引数据结构中，通过索引值可以直接找到要查询字段的值，而不需要通过主键值回表查询，那么就叫覆盖索引
3.查询的字段被使用到的索引树全部覆盖到

举个栗子：

在这里插入图片描述
在上图中，id为主键索引，name为唯一索引
假如你执行命令

select id,name from eclass where id=1;

此时由于id和name字段都在索引树中，所以这就是覆盖索引查询

在这里插入图片描述

什么是聚簇索引

聚簇索引是物理索引，数据表就是按顺序存储的，物理上是连续的。
一般情况下主键会默认创建聚簇索引
将索引与数据放在一起，当你找到索引后，也就找到对应的数据了。每张表只能建立一个聚簇索引，但是该索引可以包含多个列（一般使用的是主键等不经常更新的列）

非聚簇索引：数据储存于索引分开，叶节点指向了对应的数据行。辅助索引访问数据时需要二次查找。辅助索引存储的不是行的物理位置，而是主键的值。而通过辅助索引首先找到的就是主键的值，再通过主键的值找到数据行对应的数据页，最后才能找到对应行。
在这里插入图片描述

联合索引

我们都知道索引的底层是一颗B+树，那么联合索引当然还是一颗B+树，只不过联合索引的健值数量不是一个，而是多个。构建一颗B+树只能根据一个值来构建，因此数据库依据联合索引最左的字段来构建B+树。使用联合索引，如果想要命中索引，需要按照建立索引时的字段顺序挨个使用，否则无法命中索引。。
在这里插入图片描述

最左匹配原则

顾名思义：最左优先，以最左边的为起点任何连续的索引都能匹配上。同时遇到范围查询(>、<、between、like)就会停⽌匹配。
mysql创建复合索引的规则是⾸先对复合索引最左边的字段的数据进⾏排序，在此基础上，再对后⾯的字段进⾏排序，这样第⼀个字段是绝对有序的，后⾯的字段就是⽆序的了，⼀般情况下第⼆个字段进⾏条件判断是⽤不到索引的，可能出现type是index类型的，这就是mysql 最左前缀的原因。
举个栗子：假设创建了顺序为（a，b，c）的索引
1.当查询条件为a=1 and b=1 或b=1 and a=1（查询优化器会调换a与b的位置），这时候都可以走索引。
当查询条件为a=1 and b>1 and c=1 时，由于是范围查找，a和b走索引，c不走索引
在这里插入图片描述

创建索引的原则

创建索引，肯定是有利于我们的查询效率的，如果无效地创建索引，只会浪费我们的内存和执行程序的效率，因此创建索引是有原则的

首先应考虑对where 和 order by 涉及到的列上建立索引
对一个存在大量更新操作的表，所建索引的数目一般不超过3个，最多不超过5个，索引虽说提高了访问速度，但太多索引会影响数据的更新操作,并且索引本身会占用存储空间。
建立唯一索引。唯一索引能够更快速地帮助我们进行数据定位；
为经常需要进行查询操作的字段建立索引；
更新频繁的列不宜设置索引，索引列不能参与计算
数据量小的表不要使用索引
重复数据多的字段不宜设置索引，如性别男和女。
在建立索引的时候，要考虑索引的最左匹配原则（在使用SQL语句时，如果where部分的条件不符合最左匹配原则，可能导致索引失效，或者不能完全发挥建立的索引的功效）；
如果建立的单个索引查询数据很多，查询得到的数据的区分度不大，则考虑建立合适的联合索引；
尽量考虑字段值长度较短的字段建立索引，如果字段值太长，会降低索引的效率

创建索引的优缺点

优点

大大加快数据的查询速度
使用分组和排序进行数据查询时，可以显著减少查询时分组和排序的时间
创建唯一索引，能够保证数据库表中每一行数据的唯一性
在实现数据的参考完整性方面，可以加速表和表之间的连接
通过使用索引，可以在查询的过程中，使用优化隐藏器，提高系统的性能。

缺点

创建索引和维护索引需要消耗时间并且随着数据量的增加，时间也会增加
索引需要占据磁盘空间
对数据表中的数据进行增加，修改，删除时，索引也要动态的维护，降低了维护的速度

在这里插入图片描述

MySQL存储引擎

数据库存储引擎是数据库底层软件组织，数据库管理系统(DBMS)使用数据引擎进行创建、查询、更新和删除数据。
不同的存储引擎提供不同的存储机制、索引技巧、锁定水平等功能。现在许多不同的数据库管理系统都支持多种不同的数据引擎。MySQL的核心就是存储引擎。
用户可以根据不同的需求为数据表选择不同的存储引擎
可以使用 SHOW ENGINES命令 可以查看Mysql的所有执行引擎我们可以到默认的执行引擎是innoDB支持事务，行级锁定和外键。
MySQL默认的存储引擎是InnoDB。

InnoDB 和 MylSAM存储引擎区别

InnoDB支持事务，MyISAM不支持，这一点是非常之重要。事务是一种高级的处理方式，如在一些列增删改中只要哪个出错还可以回滚还原，而MyISAM就不可以了。
MyISAM适合查询以及插入为主的应用，InnoDB适合频繁修改以及涉及到安全性较高的应用
InnoDB支持外键，MyISAM不支持
从MySQL5.5.5以后，InnoDB是默认引擎
InnoDB不支持FULLTEXT类型的索引
InnoDB中不保存表的行数，如select count() from table时，InnoDB需要扫描一遍整个表来计算有多少行，但是MyISAM只要简单的读出保存好的行数即可。注意的是，当count()语句包含where条件时MyISAM也需要扫描整个表
对于自增长的字段，InnoDB中必须包含只有该字段的索引，但是在MyISAM表中可以和其他字段一起建立联合索引
清空整个表时，InnoDB是一行一行的删除，效率非常慢。MyISAM则会重建表
InnoDB支持行锁（某些情况下还是锁整表）

图片表示

在这里插入图片描述

MySQL事务

在这里插入图片描述

什么是事务

在MySQL中的事务Transaction是由存储引擎实现的，在MySQL中，只有InnoDB存储引擎才支持事务。
事务处理可以用来维护数据库的完整性，保证成批的SQL语句要么全部执行，要么全部不执行。事务用来管理DDL、DML、DCL操作，比如insert，update，delete语句，默认是自动提交的。

事务操作

开启事务：Start Transaction
任何一条DML语句(insert、update、delete)执行，标志事务的开启命令：BEGIN或 START TRANSACTION
提交事务：Commit Transaction
成功的结束，将所有的DML语句操作历史记录和底层硬盘数据来一次同步命令：COMMIT
回滚事务：Rollback Transaction
失败的结束，将所有的DML语句操作历史记录全部清空
命令：ROLLBACK

set autocommit =0 禁止自动提交事务
set autocommit =1 开启自动提交事务

在这里插入图片描述

事务的特性

原子性（Atomicity）：事务是一个不可分割的整体，事务开始后的所有操作，要么全部完
成，要么全部不做
一致性（Consistency）：系统从一个正确的状态，迁移到另一个正确的状态
隔离性（Isolation）：每个事务的对象对其他事务的操作对象互相分离，事务提交前对其
他事务不可见
持久性（Durability）：事务一旦提交，则其结果是永久性的

事务的隔离级别

读未提交(Read uncommitted)
一个事务可以读取另一个未提交事务的数据，最低级别，任何情况都无法保证，会造成脏读。
读已提交(Read committed)
一个事务要等另一个事务提交后才能读取数据，可避免脏读的发生，会造成不可重复读。
可重复读(Repeatable read) I
就是在开始读取数据(事务开启)时，不再允许修改操作，可避免脏读、不可重复读的发生，但是会造成幻读。
串行(Serializable)
是最高的事务隔离级别，在该级别下，事务串行化顺序执行，可以避免脏读、不可重复读与幻读。但是这种事务隔离级别效率低下，比较耗数据库性能，一般不使用。

Mysql的默认隔离级别是Repeatable read （可重复读）。

脏读：强调的是第二个事务读到的数据不够新
栗子：假设老板今天给你发这个月的工资，本来要给你发2w元，结果手一抖发了20w元，这时老板还没有提交事务，而你查询到你的账户里多了20w元，老板总觉得不对劲，总感觉少了些什么，最后发现后回滚事务，给你发了2w，然后这时你打开账户发现只有2w块钱。
不可重复读：同一事务，两次读取到的数据不一样
栗子：前提同第一个，老板给你发了2w元工资，你看到账户里多了2w块钱，此时老板觉得你这个月做的还不错，给你加了1w块钱奖励金并提交了事务，而此时你再次查询时发现是3w块钱，这就造成了在一个事务中读取到的事务不一致。
幻读：重点在于新增或删除，同样的条件，第一次和第二次读出来的记录数不一样
栗子：和你相同薪资的人有10人，此时读数据读到的事务为10人，此时突然增加了一条工资和你们一样的人，提交事务后记录为11人，因此产生了幻读。

在这里插入图片描述

MySQL 锁机制

锁是计算机协调多个进程或线程并发访问某一资源的机制(避免争抢)。
在数据库中，除传统的计算资源(如CPU、RAM、I/O等)的争用以外，数据也是一种供许多用户共享的资如何保证数据并发访问的一致性、有效性是所有数据库必须解决的一个问题，锁冲突也是影响数据库并发访问性能的一个重要因素。从这个角度来说，锁对数据库而言显得尤其重要，也更加复杂。

从对数据操作的粒度分：
1.表锁：操作时，会锁定整个表。
2.行锁：操作时，会锁定当前操作行。
从对数据操作的类型分：
1.读锁(共享锁)：针对同一份数据，多个读操作可以同时进行而不会互相影响。
2.写锁(排它锁)：当前操作没有完成之前，它会阻断其他写锁和读锁。

其中，InnoDB表锁和行锁都支持，而MyISAM只支持表锁

MySQL锁的特性

表级锁 偏向MyISAM存储引擎，开销小，加锁快不会出现死锁：锁定粒度大，发生锁冲突的概率最高，并发度最低。
行级锁 偏向InnoDB存储引擎，开销大，加锁慢；会出现死；锁定粒度最小，发生锁冲突的概率最低，并发度也最高。
从上述特点可见，很难笼统地说哪种锁更好，只能就具体应用的特点来说哪种锁更合适！仅从锁的角度来说：表级锁更适合于以查询为主，只有少量按索引条件更新数据的应用，如Web应用；
而行级锁则更适合于有大量按索引条件并发更新少量不同数据，同时又有并查询的应用，如一些在线事务处理(OLTP)系统。

如何加锁

MyISAM表锁
MyISAM存储引擎只支持表锁
如何加表锁
MyISAM在执行查询语句(SELECT)前，会自动给涉及的所有表加读锁，在执行更新操作(UPDATE、DELETE、INSERT等)前，会自动给涉及的表加写锁，这个过程并不需要用户干预，因此，用户一般不需要直接用LOCK TABLE 命令给MyISAM表显式加锁。
加读锁： lock table table_name read;
加写锁：lock table table_name write;

在这里插入图片描述

InnoDB行锁：
行锁特点：偏向InnoDB存储引擎，开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度也最高。
InnoDB与MyISAM的最大不同有两点：一是支持事务；二是采用了行级锁。
行锁模式：
InnoDB 实现了以下两种类型的行锁。
共享锁(S) 又称为读锁，简称S锁，共享锁就是多个事务对于同一数据可以共享一把锁，都能访问到数据，但是只能读不能修改。
排他锁（X）又称为写锁，简称x锁，排他锁就是不能与其他锁并存，如一个事务获取了一个数据行的排他锁，其他事务就不能再获取该行的其他锁，包括共享锁和排他锁，但是获取排他锁的事务是可以对数据就行读取和修改。
对于UPDATE、DELETE和INSERT语句，InnoDB会自动给涉及数据集加排他锁(X)；
对于普通SELECT语句，InnoDB不会加任何锁；
共享锁（S）：SELECT ★ FROM table name WHERE ... LOCK IN SHARE MODE
排他锁（X）：SELECT ★ FROM table name WHERE ... FOR UPDATE