MySQL实战45讲基础篇笔记

基础架构与查询

连接器

1、root用户更改普通用户的权限将在下次普通用户重连时生效，即不会影响到当前连接的用户。

2、空闲状态的链接经过设置的等待时间后会断开连接，下次使用需要重连。

3、长连接和短连接：建立连接的过程比较复杂-->少使用短连接

4、长连接使用时间MySQL内存占用变高-->MySQL执行中使用的临时内存收到链接对象的管理，OOM可能导致异常退出

​ 解决方法：定时断开连接重连 5.7之后可以执行mysql_reset_connection重新初始化连接资源

查询缓存

1、查询的语句和结果会以K-V的形式存储在查询缓存中

​ 优点：命中了快 缺点：对应的表一旦更新就会删除相应的所有缓存，导致命中率较低

2、mysql8.0 开始取消了CACHE

​ 8.0之前可以使用参数 query_cache_type 设置成 DEMAND按需使用是否查询缓存

select SQL_CACHE * from T where ID=10；

分析器

1、进行词法分析：将“字符串”对相应到某个表、某个列

2、进行语法分析：验证sql语法

优化器

​ 确定执行的方案

1、在表里面有多个索引的时候，决定使用哪个索引；

2、在一个语句有多表关联（join）的时候，决定各个表的连接顺序。

select * from t1 join t2 using(ID) where t1.c=10 and t2.d=20;

执行器

1、执行之前先进行权限检查

​ 连接权限和操作权限。

​ IP或域名的访问限制、连接密码验证

​ 若命中缓存，则会在返回缓存结果的时候做权限验证

​ 查询在优化器之前调用 precheck 验证权限

2、有权限-->打开表继续执行

​ select * from T where ID=10; ID没有索引

（1）调用引擎接口从第一行开始判断 ID 是否为 10，如果不是则跳过，否则存入结果集中；

（2）调用引擎接口取下一行直到最后一行；

（3）将符合条件的结果集返回客户端。

更新与日志系统

redolog 重做日志

Write-Ahead Logging 先写日志再写磁盘

​ InnoDB 引擎特有的日志

​ 当有一条记录需要更新，InnoDB先记录到redo中并更新内存，空闲时在把数据更新到磁盘中。

​ redo大小固定 一组 4 个文件，每个文件的大小是 1GB-->能够记录4GB的操作。

​ write pos记录当前的位置，checkpoint当前要擦除的位置，之间空着的表示能够记录新操作，当 write pos 追上 checkpoint 表示不能进行新的更新，必须擦除记录。

binlog 归档日志

​ Server 自己的日志：Mysql最开始没有InnoDB引擎，自己的MyISAM 没有 crash-safe 的能力，InnoDB是以插件形式引入

1、redolog是InnoDB特有的，binlog是Server实现的，所有引擎都能用。

2、redolog物理日志-->在某个数据页上做了什么修改

​ binlog逻辑日志-->语句的原始逻辑

​ Redo Log更加底层，记录了具体的物理修改，而Binlog更加高层，记录了执行的SQL语句或者语句的逻辑表示。

​ Redo Log主要用于物理恢复，通过重新应用物理操作来恢复数据的一致性；而Binlog主要用于逻辑恢复，通过重新执行SQL语句或者语句的逻辑表示来恢复数据的逻辑状态。

3、redolog循环写，空间会用完

​ binlog写完一个文件会切换到下一个，不覆盖

update T set c=c+1 where ID=2;

执行过程：

（1）执行器找引擎取到id=2这一行，本就在内存?返回给执行器:从磁盘读入内存；

（2）执行器拿到行数据把值+1，获得新的行数据调用引擎接口写入这个新的行数据；

（3）引擎将这行数据更新到内存中，将这个更新记录到redolog，redo处于prepare告诉执行器可以提交事务；

（4）执行器生成binlog，把binlog写入磁盘；

（5）执行器调用引擎的提交事务接口，把刚写入的redo改成prepare状态表示更新完成。

两阶段提交

让两份日志之间的逻辑一致

准备阶段：更新时将修改数据记录到redo并更新内存，不立即写入磁盘-->记录了事务的物理操作，但数据库的数据页没有正更新 。

提交阶段：事务已经成功执行，并且数据库将数据页的修改持久化到磁盘，即将redo应用到磁盘上，同时redolog也被以日志的方式存下来。

事务隔离

​ 事务支持是在引擎层实现的，原生的MyISAM不支持事务。

​ ACID（Atomicity、Consistency、Isolation、Durability，即原子性、一致性、隔离性、持久性）

并发事务的问题

​ 当数据库上有多个事务同时执行的时候，就可能出现脏读（dirty read）、不可重复读 （non-repeatable read）、幻读（phantom read）的问题。

脏读（Dirty Read）：一个事务读取了另一个事务尚未提交的数据。A修改未提交但B读了提交后的数据，A回滚则B的数据无效。

幻读（Phantom Read）：一个事务在两次查询之间，另一个事务插入或删除了符合第一个事务查询条件的数据，导致第一个事务产生了额外的数据行。A的两次查询之间被B插入了符合条件的新数据。

不可重复读（Non-repeatable Read）：一个事务在多次读取同一数据时，得到了不同的结果。A第一次读取得到一个结果，B将这个数据改了，再次读的时候两次结果不一样。

隔离性与隔离级别

隔离得越严实，效率就会越低

读未提交（read uncommitted）：，一个事务还没提交时，它做的变更就能被别的事务看到。

读提交（read committed）：一个事务提交之后，它做的变更才会被其他事务看到。

可重复读（repeatable read）：一个事务执行过程中看到的数据，总是跟这个事务在启动时看到的数据是一致的。当然在可重复读隔离级别下，未提交变更对其他事务也是不可见的。

串行化 （serializable ）：对于同一行记录，“写”会加“写锁”，“读”会加“读锁”。当出现读写锁冲突的时候，后访问的事务必须等前一个事务执行完成，才能继续执行。

读未提交：V1=1，V2=2，V3=2

读提交：V1=1，V2=2，V3=2

可重复读：V1=1，V2=1，V3=2

串行化：V1=1，V2=1，V3=2

事务隔离的实现

​ MVCC（Multi-Version Concurrency Control）是MySQL中一种并发控制机制，用于处理并发读写操作，提高数据库的并发性能和数据一致性。MVCC通过为每个事务创建不同的数据版本来实现并发控制，从而避免了传统的锁机制带来的阻塞和冲突。

​ 在MVCC中，每个数据行都会保存多个版本，每个版本都有一个时间戳来标识其创建时间。当一个事务开始时，它会创建一个Read View，该视图记录了事务开始时数据库中已提交的数据版本。在事务执行期间，Read View保持不变，以确保事务读取的数据是一致的。

​ 对于读操作，MVCC会根据事务的Read View来确定可见的数据版本。只有那些在事务开始之前已提交的数据版本才对当前事务可见，未提交的数据或者在事务开始后修改的数据对当前事务是不可见的。这样可以实现读操作的并发性，多个事务可以同时读取数据库的不同版本。

​ 对于写操作，MVCC使用写时复制（Copy-On-Write）的方式。当一个事务对数据进行修改时，不会直接在原始数据上进行修改，而是创建一个新的数据版本，并将修改后的数据写入新版本中。这样可以保证其他事务仍然可以读取到原始数据版本，不会受到正在进行的写操作的影响。

​ MVCC的优点是提高了并发性能，避免了锁带来的阻塞和冲突。同时，MVCC也保证了数据的一致性和隔离性，每个事务读取的数据都是一致的，并且不会受到其他事务的修改影响。

​ 需要注意的是，MVCC只适用于读操作的并发控制，对于写操作仍然需要使用锁机制来保证数据的一致性。不同的存储引擎在实现MVCC时可能会有一些细微的差异，但基本原理是相同的。

事务的启动方式

长事务-->系统中有很多老的事务视图，占用空间

​ -->占用锁资源

1、显式启动事务语句， begin 或 start transaction。提交 commit/回滚rollback。

2、set autocommit=0 关闭这个线程的自动提交，只执行一个select就启动，事务持续到自主commit/rollback，或断开连接。

​ 有些客户端连接默认设置-->所有查询都在一个事务，若是长连接则导致长事务。

​ 建议使用 set autocommit=1, 通过显式语句的方式来启动事务。

深入浅出索引（上）

索引-->数据库的目录

索引的常见模型

1、哈希表

​ key-value形式的键值对：把值放在数组里，用一个哈希函数把 key 换算成一个确定的位置，然后把 value 放在数组的这个位置。

哈希冲突

​ 链表法（上图）：哈希桶维护一个链表或其他数据结构，当发生哈希冲突时，将冲突的键值对添加到链表中。这样，多个键值对可以共享同一个哈希桶，形成一个链表。

​ 链表长度达到一定阈值时，将链表转换为平衡二叉搜索树

​ 开放寻址：检测到下一个哈希值放入。

​ 二次哈希：使用两个哈希函数。

​ 这种结构适用于只有等值查询的场景。

2、有序数组

​ 在等值查询和范围查询场景中的性能就都非常优秀。

身份证号递增的顺序保存-->二分查找复杂度O(log(N))

有序数组索引只适用于静态存储引擎

3、搜索树

二叉树-->同等数据树更高，查询需要的数据块越多，查询慢

n叉树-->n取决于数据块的大小；innoDB中N差不多1200

InnoDB索引模型

B+树 每一个索引在 InnoDB 里面对应一棵 B+ 树

表都是根据主键顺序以索引的形式存放-->索引组织表

mysql> 
create table T( 
    id int primary key,  
    k int not null,  
    name varchar(16), 
	index (k)
)engine=InnoDB;

表中 R1~R5 的 (ID,k) 值分别为 (100,1)、(200,2)、(300,3)、(500,5) 和 (600,6)

主键索引-->聚簇索引 叶子节点存的是整行数据

非主键索引-->二级索引 叶子节点存的是主键的值

通过主键查询：select * from T where ID=500 只需要搜索ID这个B+树

非主键查询：select * from T where k=5 先搜索k索引树，得到ID值为500，再到ID索引树搜索一次 ---> 回表

索引维护

​ 插入ID值为700->直接在记录后面插入一个新纪录

​ 插入ID值为400-->挪动后面的数据位置，空出位置

​ 页分裂-->若R5所在的数据页满了，申请一个新的数据页挪动部分数据过去

​ 页合并-->相邻的两个页由于删除了数据利用率变得很低，将数据页做合并

自增主键NOT NULL PRIMARY KEY AUTO_INCREMENT

​ 当前ID的最大值+1，作为下一条记录的ID值-->不涉及挪动其他记录，不会触发叶子节点分裂

​ 从存储空间考虑：唯一字段是身份证号，身份证做主键，则二级索引叶子节点占用20个字节；整形做主键，二级索引只需要4个字节

​ 主键长度越小，普通索引的叶子节点就越小，普通索引站的空间就越小。-->

从性能和存储空间方面考量，自增主键往往是更合理的选择。

​ 1、只有一个索引

​ 2、该索引必须是唯一索引

​ 考虑上一段提到的“尽量使用主键查询”原则，直接将这个索引设置为 主键，可以避免每次查询需要搜索两棵树。

深入浅出索引（下）

select * from T where k between 3 and 5 执行几次树的搜索，扫描多少行

mysql> create table T (
ID int primary key,
k int NOT NULL DEFAULT 0,
s varchar(16) NOT NULL DEFAULT '',
index k(k))
engine=InnoDB;

insert into T values(100,1, 'aa'),(200,2,'bb'),(300,3,'cc'),(500,5,'ee'),(600,6,'ff'),(700,7,'gg');

​ 执行流程：

（1）k索引树上找到k=3取到ID=300

（2）到ID做引述找ID=300找到R3

（3）k索引树上找到k=5取到ID=500

（4）到ID做引述找ID=500找到R4

（5）在 k 索引树取下一个值 k=6，不满足，循环结束

由于查询结果所需要的数据只在主键索引上有，所以不得不回表

覆盖索引

​ select ID from T where k between 3 and 5只需要查ID的值，ID已经在k索引树上，所以直接提供结果-->索引k已经覆盖了我们的查询需求

​ 由于覆盖索引可以减少树的搜索次数，显著提升查询性能，所以使用覆盖索引是一个常用的性能优化手段。

信息表上是否有必要将身份证号和名字建立联合索引

CREATE TABLE `tuser` (
 `id` int(11) NOT NULL,
 `id_card` varchar(32) DEFAULT NULL,
 `name` varchar(32) DEFAULT NULL,
 `age` int(11) DEFAULT NULL,
 `ismale` tinyint(1) DEFAULT NULL,
 PRIMARY KEY (`id`),
 KEY `id_card` (`id_card`),
 KEY `name_age` (`name`,`age`)
) ENGINE=InnoDB

​ 根据身份证号查询市民信息的需求-->只要在身份证号字段上建立索引就够了

​ 高频请求根据市民的身份证号查姓名-->在这个高频请求上用到联合索引，不用回表

最左前缀原则

B+树这种索引结构可以利用索引的最左前缀定位记录。

​ （name，age）联合索引

​ 索引项是按照索引定义里面出现的字段顺序排序的-->查询“张三”时可以快速定义到ID4，向后遍历得到所有需要的结果。

在建立联合索引的时候，如何安排索引内的字段顺序

​ 如果通过调整顺序，可以少维护一个索引，那么这个顺序往往就是需要优先考虑采用的。有了（a,b）这个联合索引，不需要在a上建立索引了。

​ 既有联合查询又有单独查询，条件查询只有b的语句-->同时维护（a,b）和（b）-->考虑原则就是空间

​ （name,age）联合索引+（age）单字段索引-->age占用字段更少

索引下推

​ 联合索引（name，age），要求找名字第一个字是张，年龄是10岁的所有男孩

mysql> select * from tuser where name like '张 %' and age=10 and ismale=1;

​ 对索引中包含的字段先做判断，直接过滤掉不符合条件的记录，减少回表次数。

全局锁和表锁

全局锁

​ 全局锁就是对整个数据库实例加锁。Flush tables with read lock (FTWRL)让整个库处于只读状态，其他线程的更新、定义和更新/提交事务的语句会被阻塞。

​ 经典应用场景-->全库逻辑备份 把整库每个表都 select 出来存成文本。

​ 问题：当用户购买商品，逻辑上扣除用户余额，商品加入

​ 若先备份余额，然后用户购买，然后备份用户商品-->余额没扣商品多了

​ 解决方法：可重复读

表级锁

​ lock tables … read/write 用 unlock tables 主动 释放锁，也可以在客户端断开的时候自动释放-->除了会限制别的线程的读写外，也限定了本线程接下来的操作对象。

​ 线程 A 中执行lock tables t1 read, t2 write; -->线程A获取到t1的读锁和t2的写锁-->其他线程对t1不能读，对t2不能读写

表级锁-元数据锁

​ MDL 不需要显式使用，在访问一个表的时候会被自动加上。MySQL5.5引入

​ MDL读锁主要用于管理元数据（metadata），而不是数据本身。元数据包括表的结构、索引信息、表的引擎类型等，而不是表中的实际数据。

​ 作用：保证读写的正确性

​ 当对一个表做增删改查操作的时候，加 MDL 读锁；当要对表做结构变更操作的时候，加 MDL 写锁

​ 读锁之间不互斥，因此你可以有多个线程同时对一张表增删改查。

​ 读写锁之间、写锁之间是互斥的，用来保证变更表结构操作的安全性。

​ MDL 会直到事务提交才释放，在做表结构变更的时候，你一定要小心不要导致锁住线上查询和更新-->设定等待时间，不要阻塞后面的业务语句

行锁功过

​ 行锁是在引擎层由各个引擎自己实现的，并不是所有引擎都支持行锁 .

​ MyISAM不支持-->只能用表锁，同一时刻一张表上只能有一个更新在执行

两阶段协议

​ B的updat会被阻塞直到A执行commit

​ 在 InnoDB 事务中，行锁是在需要的时候才加上的，但并不是不需要了就立刻 释放，而是要等到事务结束时才释放。

​ -->如果需要锁多行，把最可能造成冲突影响并发度的锁往后放。

（1）A余额减少 （2）B余额增加 （3）记录交易日志

现在C给B转账，冲突在语句（2）

​ 优化顺序（3）（1）（2）减少锁的等待

死锁和死锁检测

​ 并发系统中不同线程出现循环资源依赖，涉及的线程都在等待别的线程释放资源时，就会 导致这几个线程都进入无限等待的状态。

​ 发现死锁后的策略：

​ -->直接进入等待，直到超时。这个超时时间可以通过参数 innodb_lock_wait_timeout 来设置。

​ -->发起死锁检测，发现死锁后，主动回滚死锁链条中的某一个事务，让其他事务得以继续执行。将参数 innodb_deadlock_detect 设置为 on，表示开启这个逻辑。

热点更新导致的性能问题

​ 主动回滚意味着杜宇每个新来的被堵住的线程都要判断是否因为自己的加入导致死锁。

1、确保一定不出现死锁，将死锁检测关闭

2、控制并发度