Ⅳ.sql原理

最新推荐文章于 2024-01-14 12:26:59 发布

唐騦忆

最新推荐文章于 2024-01-14 12:26:59 发布

阅读量92

点赞数

本文链接：https://blog.csdn.net/weixin_44230495/article/details/103198080

版权

一、mysql架构

mysql架构

Server层涵盖MySQL的大多数核心服务功能，以及所有的内置函数（如日期、时间、数学和加密函数等），所有跨存储引擎的功能都在这一层实现，比如存储过程、触发器、视图等。
而存储引擎层负责数据的存储和提取。其架构模式是插件式的，支持InnoDB、MyISAM、Memory等多个存储引擎。现在最常用的存储引擎是InnoDB。

1. 连接器

连接器负责跟客户端建立连接、获取权限、维持和管理连接。

mysql -h$ip -P$port -u$user -p

如果用户名密码认证通过，连接器会到权限表里面查出你拥有的权限。之后，这个连接里面的权限判断逻辑，都将依赖于此时读到的权限。这就意味着，一个用户成功建立连接后，即使你用管理员账号对这个用户的权限做了修改，也不会影响已经存在连接的权限。修改完成后，只有再新建的连接才会使用新的权限设置。
数据库里面，长连接是指连接成功后，如果客户端持续有请求，则一直使用同一个连接。短连接则是指每次执行完很少的几次查询就断开连接，下次查询再重新建立一个。
mysql连接默认为长连接，时长为8小时。
MySQL在执行过程中临时使用的内存是管理在连接对象里面的。这些资源会在连接断开的时候才释放。所以如果长连接累积下来，可能导致内存占用太大，被系统强行杀掉（OOM）。
解决方案：

定期断开长连接。使用一段时间，或者程序里面判断执行过一个占用内存的大查询后，断开连接，之后要查询再重连。
如果你用的是MySQL 5.7或更新版本，可以在每次执行一个比较大的操作后，通过执行 mysql_reset_connection来重新初始化连接资源。这个过程不需要重连和重新做权限验证，但是会将连接恢复到刚刚创建完时的状态。

2. 查询缓存

mysql将之间查询过的语句以key-value形式保存在内存中，其中key表示查询语句，value表示查询结果。在之后的查找中如果发现相同的select会直接将value返回，以加快查询效率。
缺点：只要表上有任何一条数据更新，整张表上的缓存都会被清空，这意味着查询缓存的命中率非常低，因此整体上查询缓存弊大于利，一般只会在静态表中才会使用。

select SQL_CACHE * from T where ID=10；

MySQL 8.0版本直接将查询缓存的整块功能删掉了。

3.分析器

做什么
mysql对输入进来的sql语句进行词法分析与语法分析。
词法分析判断select，表名，字段等信息。
语法分析判断输入语法是否正确，如果不正确会报错。

4. 优化器

怎么做
优化器是在表里面有多个索引的时候，决定使用哪个索引；或者在一个语句有多表关联（join）的时候，决定各个表的连接顺序。优化器的作用就是决定选择使用哪一个方案。

5.执行器

着手做
判断是否有对对应表的执行权限，若有，进行下一步；若没有，返回错误信息。如果命中查询缓存，会在查询缓存放回结果的时候，做权限验证。
根据引擎定义，使用引擎接口进行操作

调用InnoDB引擎接口取这个表的第一行，判断ID值是不是10，如果不是则跳过，如果是则将这行存在结果集中；
调用引擎接口取“下一行”，重复相同的判断逻辑，直到取到这个表的最后一行。
执行器将上述遍历过程中所有满足条件的行组成的记录集作为结果集返回给客户端。

二、日志

WAL技术，WAL的全称是Write-Ahead Logging，它的关键点就是先写日志(redo log)，再写磁盘(binlog)，也就是先写粉板，等不忙的时候再写账本。

日志

write pos是当前记录的位置，一边写一边后移，写到第3号文件末尾后就回到0号文件开头。checkpoint是当前要擦除的位置，也是往后推移并且循环的，擦除记录前要把记录更新到数据文件。
write pos和checkpoint之间的是“粉板”上还空着的部分，可以用来记录新的操作。如果write pos追上checkpoint，表示“粉板”满了，这时候不能再执行新的更新，得停下来先擦掉一些记录，把checkpoint推进一下。

redo log使得InnoDB可以保证即使数据库发生异常重启，之前提交的记录都不会丢失，这个能力称为crash-safe。
redo log是InnoDB引擎特有的；binlog是MySQL的Server层实现的，所有引擎都可以使用。
redo log是物理日志，记录的是“在某个数据页上做了什么修改”；binlog是逻辑日志，记录的是这个语句的原始逻辑，比如“给ID=2这一行的c字段加1 ”。
redo log是循环写的，空间固定会用完；binlog是可以追加写入的。“追加写”是指binlog文件写到一定大小后会切换到下一个，并不会覆盖以前的日志。

Binlog有两种模式，statement 格式的话是记sql语句， row格式会记录行的内容，记两条，更新前和更新后都有。

插入语句执行过程

两阶段提交：利用事务机制保证数据的一致性

先写redo log后写binlog。假设在redo log写完，binlog还没有写完的时候，MySQL进程异常重启。由于我们前面说过的，redo log写完之后，系统即使崩溃，仍然能够把数据恢复回来，所以恢复后这一行c的值是1。
但是由于binlog没写完就crash了，这时候binlog里面就没有记录这个语句。因此，之后备份日志的时候，存起来的binlog里面就没有这条语句。
然后你会发现，如果需要用这个binlog来恢复临时库的话，由于这个语句的binlog丢失，这个临时库就会少了这一次更新，恢复出来的这一行c的值就是0，与原库的值不同。
先写binlog后写redo log。如果在binlog写完之后crash，由于redo log还没写，崩溃恢复以后这个事务无效，所以这一行c的值是0。但是binlog里面已经记录了“把c从0改成1”这个日志。所以，在之后用binlog来恢复的时候就多了一个事务出来，恢复出来的这一行c的值就是1，与原库的值不同。

三、事务隔离

当数据库上有多个事务同时执行的时候，就可能出现脏读（dirty read）、不可重复读（non-repeatable read）、幻读（phantom read）的问题，为了解决这些问题，就有了“隔离级别”的概念。

脏读

脏读是指一个事务还未提交，就被另一个事务读出，如果提交前回滚事务，将导致严重后果。

不可重复读

不可重复读是指一个事务还未提交，涉及到的行数据被另一个事务修改，导致不能重复读出来。

幻读

幻读是指在一个事务的修改过程中，另一个事务对表有插入，导致事务认为有行没有做修改。

隔离得越严实，效率就会越低。因此很多时候，我们都要在二者之间寻找一个平衡点。SQL标准的事务隔离级别包括：读未提交（read uncommitted）、读提交（read committed）、可重复读（repeatable read）和串行化（serializable ）。

读未提交是指，一个事务还没提交时，它做的变更就能被别的事务看到。
读提交是指，一个事务提交之后，它做的变更才会被其他事务看到。
可重复读是指，一个事务执行过程中看到的数据，总是跟这个事务在启动时看到的数据是一致的。当然在可重复读隔离级别下，未提交变更对其他事务也是不可见的。
串行化，顾名思义是对于同一行记录，“写”会加“写锁”，“读”会加“读锁”。当出现读写锁冲突的时候，后访问的事务必须等前一个事务执行完成，才能继续执行

mysql> create table T(c int) engine=InnoDB;
insert into T(c) values(1);

事务执行过程

若隔离级别是“读未提交”，则V1的值就是2。这时候事务B虽然还没有提交，但是结果已经被A看到了。因此，V2、V3也都是2。
若隔离级别是“读提交”，则V1是1，V2的值是2。事务B的更新在提交后才能被A看到。所以， V3的值也是2。
若隔离级别是“可重复读”，则V1、V2是1，V3是2。之所以V2还是1，遵循的就是这个要求：事务在执行期间看到的数据前后必须是一致的。
若隔离级别是“串行化”，则在事务B执行“将1改成2”的时候，会被锁住。直到事务A提交后，事务B才可以继续执行。所以从A的角度看， V1、V2值是1，V3的值是2。

在实现上，数据库里面会创建一个视图，访问的时候以视图的逻辑结果为准。在“可重复读”隔离级别下，这个视图是在事务启动时创建的，整个事务存在期间都用这个视图。在“读提交”隔离级别下，这个视图是在每个SQL语句开始执行的时候创建的。这里需要注意的是，“读未提交”隔离级别下直接返回记录上的最新值，没有视图概念；而“串行化”隔离级别下直接用加锁的方式来避免并行访问。
可以用show variables来查看当前的状态。

可重复读的实现：事务回滚
在MySQL中，实际上每条记录在更新的时候都会同时记录一条回滚操作。记录上的最新值，通过回滚操作，都可以得到前一个状态的值。

事务回滚

在查询这条记录的时候，不同时刻启动的事务会有不同的read-view。如图中看到的，在视图A、B、C里面，这一个记录的值分别是1、2、4，同一条记录在系统中可以存在多个版本，就是数据库的多版本并发控制（MVCC）。
回滚日志在不需要的时候会被删除，当系统里没有比这个回滚日志更早的read-view的时候，回滚就会消失。
长事务意味着系统里面会存在很老的事务视图。由于这些事务随时可能访问数据库里面的任何数据，所以这个事务提交之前，数据库里面它可能用到的回滚记录都必须保留，这就会导致大量占用存储空间。除了对回滚段的影响，长事务还占用锁资源，也可能拖垮整个库，因此要尽量避免使用长事务。

事务的启动方式

显式启动事务语句， begin 或 start transaction。配套的提交语句是commit，回滚语句是rollback。
set autocommit=0，这个命令会将这个线程的自动提交关掉。意味着如果你只执行一个select语句，这个事务就启动了，而且并不会自动提交。这个事务持续存在直到你主动执行commit 或 rollback 语句，或者断开连接。
commit work and chain，提交事务并自动启动下一个事务，这样也省去了再次执行begin语句的开销。同时带来的好处是从程序开发的角度明确地知道每个语句是否处于事务中。

四、索引

索引的出现是为了提升数据查询的效率，类似于书的目录。
索引模型：哈希表HashMap、有序数组、二叉树

1.哈希表

哈希表模型

哈希表的特点是指定值查询写入(key-value)速度非常快，但范围读写时，如果需要遍历链表速度非常慢。

2. 有序数组

有序数组模型

查询时采用二分法查询，时间复杂度O(LOG(N))；数据插入时需要移动后面所有记录，时间复杂度O(N)。因此，有序数组只适合作为静态存储引擎。

3. 二叉树

二叉树

查询时间复杂度O(LOG(N))，写入时间复杂度O(LOG(N))。
这种索引模型看起来是最优办法，但实际中却不使用。原因是，索引不仅存在于内存中，还可能保存在磁盘上，从磁盘上读数据相当慢（10ms），二叉树过高意味着需要大量从磁盘上读取数据，花费时间非常长。因此实际中的数据库使用N叉树代替。

3.1 B+树

innodb引擎采用B+树作为索引，这里只做简单介绍，详细见数据结构。根据叶子节点的内容，索引类型分为主键索引和非主键索引。
主键索引的叶子节点存的是整行数据。在InnoDB里，主键索引也被称为聚簇索引（clustered index）。
非主键索引的叶子节点内容是主键的值。在InnoDB里，非主键索引也被称为二级索引（secondary index）。
二者的区别举例：

如果语句是select * from T where ID=500，即主键查询方式，则只需要搜索ID这棵B+树；
如果语句是select * from T where k=5，即普通索引查询方式，则需要先搜索k索引树，得到ID的值为500，再到ID索引树搜索一次。这个过程称为回表。

3.2 索引维护

B+树为了维护索引有序性，在插入新值的时候需要做必要的维护。在序列后面添加数据可以做到直接添加；在序列中间添加数据需要挪动后面所有的数据，如果当前数据页已满，还需要新建页，并把多出来的数据进行迁移，这会导致索引性能下降，空间利用率降低。
基于以上情况，一般数据库在建表时最好设置自增主键，防止中间插入和页面分裂。

特殊情况，如K-V场景：

只有一个索引
该索引为唯一索引
可不设置自增主键，避免每次查询都需要搜索两棵树。

3.3 覆盖索引

回表会导致多次索引查询，减少回表可以加快查询。如：

select ID from T where k between 3 and 5

不需要再查一次主键ID就可以完成

覆盖索引是减少回表的常见方法
通过建立联合索引

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

可以做到根据name直接查询age而不使用回表，且不用查询整行记录。

3.4 最左前缀

联合索引的最左N个字段或字符串索引的最左M个字符，可以作为最左索引，用于其他查询。所以当已经有了(a,b)这个联合索引后，一般就不需要单独在a上建立索引了。因此，第一原则是，如果通过调整顺序，可以少维护一个索引，那么这个顺序往往就是需要优先考虑采用的。
当既需要(a,b)联合索引，又需要a与b单独索引时，优先考虑空间因素，单独将站空间较小的字段设为独立索引。

3.5 索引下推

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

name时联合索引的最左，可以索引查询，其余查询条件

在mysql 5.6之前需要查询name一个个回表，在主键上找出数据行作比较。
在mysql 5.6之后引入索引下推优化，可以在索引遍历中，对联合索引内部包含字段(name,age)做判断，直接过滤掉不满足条件的记录，减少回表次数。

总之，在满足语句要求，索引的目的就是尽可能少的查询。在设计表结构时，也要以减少资源消耗作为目标。

注：重建主键方法：

 alter table T engine=InnoDB

这样可以清楚索引缓存记录，不能直接删除再添加，否则会引起全表重建。
如果查询条件使用的是普通索引（或是联合索引的最左原则字段），查询结果是联合索引的字段或是主键，不用回表操作，直接返回结果，减少IO磁盘读写读取正行数据

五、锁

数据库锁

乐观锁：默认相信没有冲突，不加锁，每次读会记录数据版本（版本号或时间戳），更新时做数据验证，相同执行并更新数据版本，不同认为是过期数据。
悲观锁：默认相信有冲突，加锁。
innodb采用表锁和行锁
myisam和memory使用表锁

5.1 库锁

Flush tables with read lock

风险非常大，如：

如果你在主库上备份，那么在备份期间都不能执行更新，业务基本上就得停摆；
如果你在从库上备份，那么备份期间从库不能执行主库同步过来的binlog，会导致主从延迟。

5.2 表锁

命中非索引字段，锁住整张表，锁冲突几率比较高，不会出现死锁。

5.3 行锁

行锁符合两阶段锁协议，行锁在需要时添加，在事务提交时消失，因此事务如果需要锁多行，要把冲突率较高的行尽可能放在后面。
基于索引存在，只有命中索引才会出现行锁，锁中where指向行。
特征：锁冲突概率低，并发性高，会出现死锁。

死锁

死锁处理方法：

添加超时，等待直到超时，通过参数innodb_lock_wait_timeout（默认50s）来设置。
发起死锁检测，发现死锁后，主动回滚死锁链条中的某一个事务，让其他事务得以继续执行。将参数innodb_deadlock_detect设置为on。
控制并发度或关闭索引。

5.3.1 记录锁

命中索引且索引是唯一索引，如主键ID，只会锁住命中行。

5.3.2 间隙锁

范围查询索引但未命中结果，锁住查询区间，左开右闭。
间隙锁可防止幻读。

#TABLE
#ID 1 3 5 7 PRIMARY KEY
SELECT *FROM TABLE WHERE ID>1 AND ID<3

锁住区间( 1,3 ]

5.3.3 临键锁

范围查询索引有查询到结果，但查询边界没有对应索引，锁住到下一个索引范围。

#TABLE
#ID 1 3 5 7 PRIMARY KEY
SELECT *FROM TABLE WHERE ID>1 AND ID<8
#锁住区间( 1 , +∞)

#TABLE
#ID 1 3 5 7 PRIMARY KEY
SELECT *FROM TABLE WHERE ID>1 AND ID<6
#锁住区间( 1 , 7]

5.4 共享锁排它锁

共享锁又称读锁，读锁可以叠加，但不能再加排它锁，即不能修改。

select * from table lock in share mode

排它锁又称写锁，写锁不能叠加任何其他锁。

select * from table for update

注意：MDL锁只有在事务结束时才会释放，所以在大批量读表中添加字段要格外小心，防止读写锁累加。最好在添加前检测是否有读的长事务或添加等待时间，超时则放弃更改。

5.5 意向共享锁意向排它锁

事务如果要给整表加共享锁需要先取得表的意向共享锁；
事务如果要给整表加排它锁需要先取得表的意向排它锁。
原因行级排它锁与表级排它锁表级共享锁不兼容；
存在行级共享锁也与表级排它锁不兼容。
所以加表级锁时需要先进行所有锁确认，过程十分繁琐。
innodb为了简化过程，加入了意向的概念，加了行锁之后添加意向
不能再添加表锁，避免索引扫描。

唐騦忆

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
Ⅳ.sql原理

一、mysql架构mysql架构Server层涵盖MySQL的大多数核心服务功能，以及所有的内置函数（如日期、时间、数学和加密函数等），所有跨存储引擎的功能都在这一层实现，比如存储过程、触发器、视图等。而存储引擎层负责数据的存储和提取。其架构模式是插件式的，支持InnoDB、MyISAM、Memory等多个存储引擎。现在最常用的存储引擎是InnoDB。1. 连...
复制链接

扫一扫