读mysql45讲-自我总结(部分)

最新推荐文章于 2024-07-11 10:26:16 发布

西埃斯迪恩-霖

最新推荐文章于 2024-07-11 10:26:16 发布

阅读量225

点赞数

分类专栏：待删除文章标签： mysql 数据库 sql

本文链接：https://blog.csdn.net/zrl1996/article/details/124552276

版权

待删除专栏收录该内容

82 篇文章 0 订阅

订阅专栏

sql执行过程

连接器

负责和客户端建立连接，并且在建立连接之后会去权限表查询当前连接用户拥有的所有权限，将这个权限集合放入到当前连接对象中，之后sql过程中涉及到的权限判断都使用这个权限集合。如果在这个连接有效的时候，用超管账号修改了这个连接的用户的权限，也是不会生效的，只有重新连接之后才会生效。

在连接完成之后，执行完sql之后，当前连接就会处于空闲状态；使用show processlist;命令查看就是Command那一列是Sleep值；

在这里插入图片描述

客户端的连接在长时间没有操作后，连接会自动断开，配置这个时间的参数是wait_timeout,默认是2个小时，我查询这个参数值是7200，按秒算的话就是2个小时。

长连接就是在建立连接成功之后，多个查询请求都是使用这个长连接；短连接则是每次执行完几个查询请求之后就断开连接，下次再有查询请求就再新建连接。
新建连接耗费资源，所以优先长连接，但是长连接可能会引起内存不足。因为MYSQL在执行过程中使用到的内存都是存放在连接对象中的，这些资源只有在连接被断开的时候才会释放。
查询请求越来越多，使用的内存也会越来越多，长连接一直不断开的话就会一直占着内存，内存占用的过大，就可能会被系统强行杀掉(OOM)。

解决长连接占用过多内存的方法可以在执行完一个耗费资源的sql之后手动去断开连接；或者使用mysql_reset_connection来重新初始化连接资源，这个过程不需要重连和重新做权限验证，但是会将连接恢复到刚刚创建完时的状态。

查询缓存

MYSQL在拿到一个查询请求之后，会先查看这个sql在缓存中有没有，key是查询的语句，value是查询的结果。查询缓存的失效非常频繁，只要有对一个表的更新，这个表上所有的查询缓存都会被清空，所以如果表很少有更新操作的话适合用缓存。

query_cache_type=0(off) 表示关闭缓存功能
query_cache_type=1(on) 表示开启缓存功能
query_cache_type=2(DEMAND) 默认的sql是不使用缓存，如果想使用缓存，在sql中用SQL_CACHE显式指定使用缓存，比如：select SQL_CACHE * from T where ID=10；

MySQL 8.0版本直接将查询缓存的整块功能删掉了，也就是说8.0开始彻底没有这个功能了。

分析器

分析器会先对sql语句做 “词法分析”，也就是识别出关键字，select就是一个查询语句，select后面的就是查询列名，from 后面要识别为表名等等；做完词法分析就要做语法分析，根据词法分析的结果，语法分析器会根据语法规则，判断你输入的这个SQL语句是否满足MySQL语法。

elect * from t 这样的语句会报错；提示少了s；这样的错是属于语法错误，那我理解就是词法分析中只是没有识别到elect应该是select，但是在语法分析就会分析为应该是select。

SQL语句的分析分为词法分析与语法分析，mysql的词法分析由MySQLLex[MySQL自己实现的]完成，语法分析由Bison生成。那么除了Bison外，Java当中也有开源的词法结构分析工具例如Antlr4，ANTLR从语法生成一个解析器，可以构建和遍历解析树。

优化器

在真正开始执行sql之前，还需要经过优化器。优化器是在表里面有多个索引的时候，决定使用哪个索引；或者在一个语句有多表关联（join）的时候，决定各个表的连接顺序。比如下面的这个sql：

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

可以先用t1.c=10先到t1表中取数据，然后再用t1的ID和t2表进行关联，再取t2.d=20的数据
也可以先用t2.d=20先到t2表中取数据，然后再用t2的ID和t1表进行关联，再取出t1.c=10的数据

这两种执行方法的逻辑结果是一样的，但是执行的效率会有不同，而优化器的作用就是决定选择哪一种方案。

执行器

开始执行的时候，要先判断一下你对这个表T有没有执行查询的权限。对于一个普通的sql比如：select * from t where id = 10,id是没有索引的，执行过程大概如下：

调用引擎层接口查询第一行数据，判断id是不是等于10，如果是的话就放到结果集中，不是就跳过。
调用引擎层接口取下一行，因为每条记录都会有next_record这个属性，可以快速定位到下一条数据的位置，再次进行上面的判断
直到查询到数据库的最后一条数据。

在数据库的慢查询日志中有一个rows_examined的字段，表示这个语句执行过程中扫描了多少行。这个值就是在执行器每次调用引擎获取数据行的时候累加的。在有些场景下，执行器调用一次，在引擎内部则扫描了多行，所以引擎扫描行数不一定和rows_examined数值是相等的。

日志

MYSQL里经常涉及到的WAL技术，全程是Write-ahead logging, 实现上就是先写日志，再操作磁盘。当有更新请求的时候，会先将操作记录到日志里，在空闲时间或者日志写满的情况下再去写入到磁盘中。就不用每次有更新数据的请求的时候都去操作磁盘，有了日志的话就可以批量将更新数据的请求写入到磁盘中，提高效率。

binlog日志是server层的，redo log日志是Innodb引擎层的；
binlog相当于逻辑日志，记载的是更新sql的逻辑，但是有多种不同的记录方式，有直接记录sql语句的，也有记载具体的更新字段值的方式；redo log相当于物理日志，记载的是某个数据页上做了什么修改；
binlog是追加写入的方式，就是写满一个文件后再写入另一个文件；但是redo log是循环写入的，也就是redo log的大小是固定的，写满了之后就要擦除掉之前写的才可以新写入。

使用how variables like '%innodb_log%'命令查看到有两个文件，每个文件大小是524288000KB（我猜的是kb，换算下来一个文件500M）。

在这里插入图片描述

上面图中的write pos就是当前写入日志的位置，checkpoint就是需要擦除日志的位置，根据黄色顺时针方向，write pos到checkpoint之间的部分就是空白的，是可以继续写入的，而checkpoint到write pos之间就是已经被写过的日志；当write pos和checkpoint重叠之后就会将一部分日志记录的内容更新到磁盘中，然后就可以擦除掉这部分内容，checkpoint就可以往前走一部分。

有了redo log，Innodb就可以保证mysql在发生异常重启后，会保证事务的完整性，已经提交的数据不会丢失，未提交的数据会回滚掉。这个能力称之为crash-safe。

根据一个简单的更新sql来画一个日志的简易流程(update t set x = x +1 where id = 1)：

两阶段提交

在redo log中有两次操作，这就是两阶段提交，两阶段提交就是为了保证两个日志之间的逻辑一致。

如果是在第一次写入redo log后发生了崩溃，那就是事务还是prepare状态，并且没有写入binlog，那就回滚
如果是在写完binglog后发生了崩溃，那就是说在redo log中根据XID是可以在binglog中找到对应的事务，那只需要将这个事务提交就可以。

参数innodb_flush_log_at_trx_commit设置为1表示每个事务的redo log日志都会持久化到磁盘中，参数sync_binlog设置为1表示每次binlog日志都会持久化到磁盘中。

刷脏页

有时候平常执行比较快的更新sql突然就执行慢了，可能是因为mysql正在刷脏页。当内存中的数据页和磁盘中的数据页不一致的时候，称这个内存页为脏页，内存中的数据页写入到磁盘后，内存和磁盘的数据页一致了，就称内存中的数据页为干净页，这样的过程叫做刷脏页。刷脏页可能是因为:

更新操作需要写入redo log；redo log是有限的，写满之后就不能再写入了，就需要刷脏页了。
内存满了，当其他查询需要从磁盘读入新的数据页到内存中的时候，如果内存不够了，就需要将最久未使用的数据页淘汰掉，这个时候如果淘汰的是脏页，就需要进行刷脏页。
mysql认为系统空闲的时候。
mysql在正常关闭的时候。

如果机器的性能比较低，也就是刷脏页的速度比生成脏页的速度要慢，就会导致整个数据库堵塞住。

事务

事务的四大特性ACID（Atomicity、Consistency、Isolation、Durability，即原子性、一致性、隔离性、持久性）

SQL中的事务隔离界级别：

读未提交：一个事务在未提交之前，对数据的变更可以被其他事务看见
读提交：一个事务在未提交之前，对数据的变更对其他事务是不可见的
可重复读：一个事务在执行过程中，看到的数据总是和事务启动的时候的数据保持一致，对数据的变更在未提交之前也是对其他事务是不可见的。
串行化：对同一行记录的读操作会加读锁，写操作会加写锁，当出现读写锁冲突的时候，需要等前者释放。

使用命令show variables like '%transaction_isolation%'可以查看当前设置的事务隔离级别。

可重复读的实现上是在事务启动的时候创建一个视图，相当于对整个库的数据进行一次快照，这样在事务过程中需要用到的数据都是从整个快照中看到的，就可以保证在事务过程中数据都是一致的。在读提交事务隔离级别下，这个视图是在每个sql执行的时候创建的，读未提交每次都是直接读取数据，没有视图的概念；串行化则是通过加锁的方式避免并行访问。

同一条记录在系统中可以存在多个版本，就是数据库的多版本并发控制（MVCC）(multi version concurrency control)

start transaction / begin transaction 并不是开启事务的起点，真正开启事务是在执行到事务中第一个sql的时候；如果想直接就启动事务的话，可以使用命令start transaction / begin transaction with consistent snapshot;

在可重复读隔离界别下，启动事务的时候会对整个库做一个快照，但是这个快照并不是就是所有库的数据copy一份；而是通过版本号的方式来实现的。每个事务在启动时候会申请一个transaction_id，这个id是在事务开始的时候向InnoDB的事务系统申请的，并且严格按照申请顺序递增的。

mysql中的每行数据都是多个版本的，每次事务更新数据的时候都会更新这行的版本，并且把这个事务的transaction_id记录到这行数据版本中，记录为row trx_id。

也就是说，数据表中的一行记录，其实可能有多个版本(row)，每个版本有自己的rowtrx_id。

row_trx
v3是最新版本的数据，v1，v2是物理上是不存在的，而是通过redo log中记录的更新数据的操作来计算当前数据回退数据到v2或者v1版本。

一个数据版本对于可重复读创建的视图可不可见有三种情况：

版本未提交，不可见。
版本已提交，已提交版本对应的事务创建在视图创建之后，不可见
版本已提交，已提交的版本对应的事务创建在事务创建之前，可见。

在下图中使用到的表t中有一条数据，(id=1,k=1);

在这里插入图片描述

按照时间来梳理：

事务A开启一致性视图，启动事务
事务B开启一次性视图，启动事务
事务C开启事务,并且更新完数据，自动提交(auto_commit=1)
事务B准备更新id=1的数据，虽然在事务C更新数据之前就已经创建了事务B并开启一致性视图，但是更新操作都是当前读，不然那其他事务更新完的数据不可见的话当前事务在更新的时候就会丢失数据；所以事务B的更新操作读到的数据是k=2；更新完之后k=3；在事务B中的select操作中，读取的数据最新版本中的row trx_id是自身的事务id，所以可见，读取到k=3。
事务A进行select操作，事务A创建时视图是在事务B和事务C之前的，所以读取到的数据是k=1。

如果在select语句中加上lock in share mode或者for update也是实现当前读。（但是我试了下如果直接在事务A的查询中加加了lock in share mode是阻塞的,应该是因为更新的读锁要等整个事务提交之后才会释放，所以阻塞了。）

读提交和可重复读在创建一致性视图的区别是：

读提交是在每个sql执行的时候才会创建视图
可重复读是在开启事务的时候就创建视图

索引

在Innodb中，表中的数据都是根据主键顺序以索引的方式存放的，这种存储方式的表叫做索引组织表。Inndb使用了B+树索引模型，所以数据都是存储在B+树上的。

主键索引和二级索引

Innodb中的索引分为主键索引和非主键索引；主键索引中的叶子节点存放的是整行的数据，也叫做聚簇索引；普通索引的叶子节点存放的是主键的值，也称为二级索引。

主键索引查询和普通索引查询的区别：

select * from t where id= 100,即主键索引查询，直接在主键索引树上找到id=100的记录，然后直接取整行的数据。
select * from t where c = 100，即二级索引查询，先在索引c树上找到c=100的记录，然后根据存放的主键值再去到主键索引树上取整行的数据。这个过程称为回表。

所以非主键索引的查询会比主键索引扫描多一个查询树的过程。

select ID from t where c = 100 这样一个查询就不会回表，已经主键id的值已经在c索引树上了，因此可以直接提供查询结果，索引c也称为覆盖索引。

最左前缀原则

这个最左前缀可以是联合索引的最左N个字段，也可以是字符串索引的最左M个字符。

如果既有字段a和字段b的联合查询，也有字段a和字段b各自的查询，那建立联合索引(a,b)的话，如果查询条件只有b的话，是不符合最左前缀原则的，也就不会走(a,b)的联合索引。那就需要维护一个联合索引(a,b)，再维护一个索引(b)，或者可以维护一个(b,a)的联合索引和一个(a)单独索引；
上面建立索引的不同方式可以根据字段a和字段b的大小来区分，如果字段a是8个字节的，字段b是16个字节的，那么就可以考虑建立联合索引(b,a)和普通索引(a), 这样索引占的空间能小一点。

索引下推

select * from t where a >= 10 and b <= 100

有一个联合索引(a,b);那么会先根据a索引找到所有a值大于等于10的，并且因为b也是联合索引的一部分，所以也会在a索引树上找到的记录里过滤掉b小于等于100的树，然后再根据id值去回表查询整行数据，因为查询的是*。

尽量少地访问数据是索引设计的重要原则之一。

change buffer

当需要更新一个数据的时候，如果数据在内存的数据页中，那就直接更新，如果不在当前数据页中，就会先将更新操作记录在change buffer中，这样就节省了每次更新都要写入磁盘的操作。当需要读内存中没有的数据，并且数据在change buffer中有记录的时候，就需要先从磁盘中将数据读入内存的数据页中，然后将change buffer中的更新操作应用到数据上，这样可以保证数据的一致性。
将change buffer中的操作应用到数据页总的过程叫做merge；除了查询会触发merge，数据库关闭也会触发merge操作，后台线程也会定时去merge。
但是merge操作不适用于唯一索引，因为唯一索引需要判断插入的值不能在整个表中出现过，那就需要读入整个表的数据到内存中，已经都读入到内存中了就没有必要使用change buffer了。
因为查询更新的数据会触发merge操作，所以change buffer适合的场景是读少写多的情况。

在explain sql过程中如果发现row扫描行数和实际预估的行数差距很大，可以通过analyze table t来重新统计索引信息。

给字符串加索引

在给字符串字段加索引的时候，可以指定长度。alter table tableName add index idx_column(columnName(3));
这样虽然比普通索引占的空间小，但是代价就是扫描行数会变多。普通索引的索引树存的name值是xxyy；而上面指定索引长度的所引树的name值就是xxy；
如果有个字段专门存身份证号码的话，现在的要求是只要查询出对应的出生年月，那就可以在这个索引上指定长度，就会节省一定的空间。
前面又说到过覆盖索引，比如一个sql：
select id，name from t where name = ‘xxx’
如果name字段没有指定长度，就是全长度，那就不需要再回表，可以直接取到id的值；但是如果name字段是前缀索引指定了长度，那就需要根据id索引再查询name值，因为name索引项中的name不是完整的。

count(*)

在使用count(*)查询一个表有多少数据的时候，mysql会用索引去统计数量，因为在主键索引中索引项存的是整行的数据，普通索引存的是id，所以普通索引占的空间相对来说要小一点，所以mysql会选择普通索引；在实现上mysql优化器会找到最小的索引树来遍历。

排序

在普通需要排序的select语句中，mysql会给每个线程分配一个排序使用的内存块sort_buffer 。比如：

select  id,name,job_number ,entry_date 
from myhr_user mu 
where gender  = 'FEMALE' 
order by entry_date 
limit 1000

如果按照全字段排序的算法来排序的话，过程大概是这样的：

初始化sort_buffer，确定要放入的字段是id，name，job_number，entry_date
根据gender = ‘FEMALE’ 取出符合条件的id
然后根据id求查询id，name，job_number，entry_date字段的值放入到sort_buffer中
在sort_buffer中，对entry_date进行排序
取前1000条记录

如果select中的字段和order中的字段很多的话。也就是mysql认为放入到sort_buffer中单行的字段太多，占的空间太大，就会使用rowid排序算法。大概过程类似：

初始化sort_buffer，确定要放入的字段是id，entry_date
根据gender = ‘FEMALE’ 取出符合条件的id
然后根据id求查询entry_date字段的值放入到sort_buffer中
在sort_buffer中，对entry_date进行排序
取前1000个结果，然后用id再回表查询name，job_number，entry_date字段的值返回

两者的区别就是初始化中确定要放入的字段不同，前者是全部放入，后者则是只放入必需字段，也就是排序字段和id，但是后者再最后需要根据id值再回表查询select中需要的字段。

锁

全局锁

全局锁就是对整个数据库实例加锁。MySQL提供了一个加全局读锁的方法，命令是 Flush tables with read lock (FTWRL)。之后其他线程的以下语句会被阻塞：数据更新语句（数据的增删改）、数据定义语句（包括建表、修改表结构等）和更新类事务的提交语句。

官方自带的逻辑备份工具是mysqldump，当mysqldump使用命令参数-single-transaction的时候，会在导数据之前开启一个事务，确定拿到一个一致性视图，由于MVCC的支持，这个过程是可以更新数据的。-single-transaction适合支持事务引擎的数据库，FTWRL可以使用不支持事务引擎的库。

表级锁

表锁的语法是lock table tableNameA read/write, tableNameB read/write…,释放锁的语法是unlock tables 。

lock table语法锁表，会锁住其他线程访问，也会限制当前线程访问表的方式。

lock table tableA read,tableB write;其他线程是不能对tableA 进行写操作，也不能对tableB进行读操作；同样的，在当前线程unlock tables 之前，当前线程也不能对tableA 进行写操作，只能进行读操作，但是在当前线程中可以对tableB进行读写操作的。

另一类表级的锁是MDL(meta data lock),这个锁不需要显示的去加，而是隐式的自动就会加上。当对一个表做增删改查的操作的时候，会加上一个MDL读锁；在修改表结构的时候会加上一个MDL写锁。

MDL读锁和MDL读锁之间是不会冲突的，可以有多个线程对同一个表进行增删改查
MDL读锁和MDL写锁之间是冲突的，在修改表结构的时候，是不让进行增删改查的操作的；同样的，在进行增删改查的时候，也不会让进行表结构的修改的。

我理解的就是MDL锁其实是对表结构修改加的锁。

如果对一个表的数据进行delete操作，并不会完全释放delete数据占的空间大小，部分delete的数据的空间会被标记为可复用的。可以通过alter table t engine=Innodb 命令来重建表，才能让delete数据占的空间完全被释放。这条命令的背后其实就是新建一个和原表结构一样的临时表，然后将原表的数据的数据按照主键的顺序一行一行的插入，这样数据就会紧凑，不会有空格。然后再将临时表替换为原表。