0%

查缺补漏-MySQL

本章是整理知识内容,为强化知识长期更新。

Mysql基础架构图

mysql基础架构图

简单介绍上图相关模块功能

  • 连接器:管理连接,权限验证

  • 查询缓存:命中缓存则直接返回结果

  • 语法解析:词法分析,语法分析

  • 查询优化:执行计划生成,索引选择

  • 执行器:操作引擎,返回结果

  • 存储引擎:存储数据,提供读写结构。

连接器

我们需要连接到mysql服务端才能进行各种操作。

1
2
# 使用密码登录到时候我们会使用这种格式。
mysql -h$ip -P$port -u$user -p

执行上述命令会提示输入密码。输入完密码后就进入了mysql。

  • 如果用户名或者密码输入错误,会提示一个Access denied for user到错误,然后客户端程序结束执行。

  • 如果用户名和密码认证通过,连接器会去权限表中查询该用户拥有的所有权限。此后该连接里面的权限逻辑判断都会依赖认证成功时候读取到的权限。

因此当修改用户的权限之后,如果被修改用户的在登录状态是不会修改已经连接的权限。需要让被修改权限用户从新登录。

  • 实际上连接的方式有多种,上面通过密码的方式只是其中的一种方式。当客户端与服务端mysql进程进程建立连接,服务器的进程就会创建一个单独的线程来专门处理与这个客户端的交互,当该客户断开与服务端连接时,服务端并不会马上销毁对应的交互线程,而是缓存起来。当另一个新的客户端连接进来的时候,在把这个缓存的线程分配给新的客户端。这样就避免的频繁创建和修改线程,节省系统的开销。但是连接线程多了也会影响服务端,所以也有默认的参数限制客户端的连接数量。
1
2
# 这里是查询服务端存在的连接。
SHOW PROCESSLIST;

我使用的阿里云的数据库,这里展示下结果集。这里需要注意,登录的账号需要有PROCESS的权限否则只能看到自己的连接信息(线程),我这里采用的root所以看到全部到。

连接完成后如果没有执行后续操作则 Command会显示Sleep的状态,长时间Sleep会导致连接自动断开。默认的时间是8小时,也就是参数 wait_timeout。当客户端连接断开后,若客户端再次发出请求就会提示一个

Lost connection to MySQL server during query,此时就只能重新建立连接了。

半双工
  • MySQL客户端/服务端通信协议是“半双工”的:在任一时刻,要么是服务器向客户端发送数据,要么是客户端向服务器发送数据,这两个动作不能同时发生。一旦一端开始发送消息,另一端要接收完整个消息才能响应它,所以我们无法也无须将一个消息切成小块独立发送,也没有办法进行流量控制。
  • 服务器响应给用户的数据通常会很多,由多个数据包组成。但是当服务器响应客户端请求时,客户端必须完整的接收整个返回结果,而不能简单的只取前面几条结果,然后让服务器停止发送。因而在实际开发中,尽量保持查询简单且只返回必需的数据,减小通信间数据包的大小和数量是一个非常好的习惯,这也是查询中尽量避免使用SELECT *以及加上LIMIT限制的原因之一。
长连接与短连接
  • 在数据库里面,长连接是指数据库建立连接,如果客户端有持续的请求则使用同一个连接,短连接是指每次执行几次短的查询就断开连接,下次查询再次创建连接。由于创建的过程复杂,一般来说尽量减少创建连接的动作,尽可能使用长连接。特别是在开发的时候出现大伙突然连接不上测试库,此时就检查下连接是不是太多了。因为连接多了会导致内存消耗特别快。

    • mysql的执行过程中临时使用的内存管理是在连接线程对象里面,服务端断开的时候才会释放,为了避免内存消耗过大,每次执行较大的操作之后,可以通过mysql_reset_connection来重新初始化连接资源。这个操作是不需要重新做权限,只是恢复到创建的初始状态。
  • MySQL mysql_reset_connection 官方文档

查询缓存

一个不被建议使用的功能,在新版的8.0中已经被删除了。总结就是弊大于利。

  • 建立完成后,假设执行一次SELECT语句,执行逻辑就会到第二部查询缓存。mysql拿到一个查询之后,会先到缓存中寻找释放有完全对应的,因为之前执行的查询语句和结果集会直接被缓存起来,以Keys-value的形式。keys是查询语句-value是查询结果集。如果能命中这个key则直接返回value。咋一看挺有用的,但实际上查询缓存的实效的太频繁。并且keys的命中条件太不聪明了,如果两次查询语句在任何的字符上存在不通(空格、大小写)都不会缓存命中。为什么会频繁失效,因为如果对查询语句中的表进行更新,就会导致缓存失效。比如INSERT、UPDATE 、DELETE 、TRUNCATE TABLE 、ALTER TABLE 等等就会导致缓存失效。

语法解析

如果没有命中查询缓存,就要开始执行sql语句了。mysql需要做什么,因此需要对sql进行解析。MySQL通过关键字将SQL语句进行解析,并生成一颗对应的解析树。这个过程解析器主要通过语法规则来验证和解析。比如SQL中是否使用了错误的关键字或者关键字的顺序是否正确等等。预处理则会根据MySQL规则进一步检查解析树是否合法。比如检查要查询的数据表和数据列是否存在等等。

  • 本质上发送过来的是一个文本信息,这里就需要对该文本信息进行编译,涉及到词法解析语法解析语义解析等阶段。需要注意的是,这里会对sql语句进行一些检查,比如设计的相关表、表字段。这里检查通过的才会进入查询优化模块。

查询优化

通过之前的语法解析,基本可以判定语法树是合法的,此时mysql就知道文本内容要做什么。

  • mysql会对查询语句进行优化,优化的结果就是生成一个执行计划,这个计划会表明使用了那些查询索引,表之间的连接是什么样子的。有时候出现多种执行方式,只是效率不通,优化器会决定使用哪一个执行方案,这个时候就涉及到EXPLAIN语句,该语句可以查看sql到执行计划,这将涉及到查询优化。
  • MySQL的查询优化器是一个非常复杂的部件,它使用了非常多的优化策略来生成一个最优的执行计划:
    • 重新定义表的关联顺序(多张表关联查询时,并不一定按照SQL中指定的顺序进行,但有一些技巧可以指定关联顺序)
    • 优化函数
    • 提前终止查询(比如:使用Limit时,查找到满足数量的结果集后会立即终止查询)
    • 优化排序(在老版本MySQL会使用两次传输排序,即先读取行指针和需要排序的字段在内存中对其排序,然后再根据排序结果去读取数据行,而新版本采用的是单次传输排序,也就是一次读取所有的数据行,然后根据给定的列排序。对于I/O密集型应用,效率会高很多。

执行器

进入到执行sql到环节。

  • 通过之前到查询优化,进入执行阶段,此时会先判断该连接用户是否有相关表的权限,如果没有会返回权限错误。如果有权限就会打开表,执行器会根据表定义的引擎去使用这个引擎提供的接口完成流程。

存储引擎

关于存储引擎,这里明确提醒。不是三种也不是四种。而是多种因为随时可能出现新的引擎。

  • 我们常用的就是InnoDBMyISAM,其它的不常见就不做过多介绍。mysql现在默认的引擎是InnoDB所以主要也是了解InnoDB
1
2
# 查看当前服务支持的存储引擎
SHOW ENGINES;

索引

目前工作中主要使用的数据库引擎就是InnoDB,所以关于索引的知识必不可少。

  • 在了解索引之前,大概理解下mysql存储数据的大小划分表空间 -> 段 -> 区 -> 数据页

  • 索引的优点:1. 天生排序。2. 快速查找;索引的缺点:1. 占用空间。2. 降低更新表的速度。

  • 注意点:

    • 小表使用全表扫描更快,中大表才使用索引。超级大表索引基本无效。

    • 索引实现,分成 2 种:聚集索引和辅助索引(也叫二级索引或者非聚集索引)

    • 功能区分,分为 6 种:普通索引,唯一索引,主键索引,复合索引,外键索引,全文索引。

      1、普通索引:最基本的索引,没有任何约束。
      2、唯一索引:与普通索引类似,但具有唯一性约束。
      3、主键索引:特殊的唯一索引,不允许有空值
      4、复合索引:将多个列组合在一起创建索引,可以覆盖多个列。
      5、外键索引:只有InnoDB类型的表才可以使用外键索引,保证数据的一致性、完整性和实现级联操作。
      6、全文索引:MySQL 自带的全文索引只能用于 InnoDB、MyISAM ,并且只能对英文进行全文检索,一般使用全文索引引擎(ES,Solr)。

  • 注意:主键就是唯一索引,但是唯一索引不一定是主键,唯一索引可以为空,但是空值只能有一个,主键不能为空。

事物隔离

ACID模式是一组数据库设计原则强调的是,对于业务数据和关键任务应用重要的可靠性方面。MySQL包含诸如的组件InnoDB存储引擎与ACID模型紧密结合,因此数据不会损坏,并且不会因软件崩溃和硬件故障等特殊情况而导致结果失真。当您依赖符合ACID的功能时,您无需重新发明一致性检查和崩溃恢复机制。如果您有其他软件安全措施,超可靠硬件或可以容忍少量数据丢失或不一致的应用程序,您可以调整MySQL设置以交换一些ACID可靠性以获得更高的性能或吞吐量。

  • Mysql默认的InnoDB事物隔离级别是repeatable-read可重复读

  • 原子性 Atomicity :一个事务(transaction)中的所有操作,或者全部完成,或者全部不完成,不会结束在中间某个环节。事务在执行过程中发生错误,会被恢复(Rollback)到事务开始前的状态,就像这个事务从来没有执行过一样。即,事务不可分割、不可约简。

  • 一致性 Consistency :在事务开始之前和事务结束以后,数据库的完整性没有被破坏。这表示写入的资料必须完全符合所有的预设约束、触发器、级联回滚等。

  • 隔离性 Isolation :数据库允许多个并发事务同时对其数据进行读写和修改的能力,隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。事务隔离分为不同级别,包括读未提交(Read uncommitted)、读提交(read committed)、可重复读(repeatable read)和串行化(Serializable)。

  • 持久性 Durability :事务处理结束后,对数据的修改就是永久的,即便系统故障也不会丢失。

  • 四个特性,最重要的就是一致性。而一致性由原子性,隔离性,持久性来保证。

    • 原子性由 Undo log 保证。Undo Log 会保存每次变更之前的记录,从而在发生错误时进行回滚。
    • 隔离性由 MVCC 和 Lock 保证。这个后面说。
    • 持久性由 Redo Log 保证。每次真正修改数据之前,都会将记录写到 Redo Log 中,只有 Redo Log 写入成功,才会真正的写入到 B+ 树中,如果提交之前断电,就可以通过 Redo Log 恢复记录。
事务隔离级别 脏读 不可重复读 幻读
读未提交(read-uncommitted) 1
读已提交(read-committed) 2
可重复读(repeatable-read) 3 是(x)
串行化(serializable) 4
  • 读未提交,一个事物未提交的时候,它做的数据变更就能被其它食物看到。
  • 读提交,一个事物提交后,它做的数据变更才能被其它事物看到。
  • 可重复读,一个事物执行的过程中看到的数据,总是跟这个事物在启动时候看的数据一致。
  • 串行化,对同一行数据 会加写锁,会加读锁。当出现读写冲突的时候,必须按数据执行事务。

并发事务的问题

脏读

一个事务正在对一条记录进行修改,在这个事务完成并提交前,这条记录的数据就处于不一致状态。这时,另一个事务也来读取同一条记录,如果不加控制,第二个事务读取了这些“脏”数据,并据此做进一步的处理,就会产生未提交的数据依赖关系。

时间 事务A 事务B
T1 开启事务 开启事务
T2 查询账户余额为1000
T3 充值500,余额修改为1500
T4 查询余额为1500
T5 撤销事务,余额改回1000
T6 汇入500,余额修改为2000
T7 提交事务

不可重复读

一个事务在读取某些数据后的某个时间,再次读取以前读过的数据,却发现其读出的数据已经发生了变更、或者某些记录已经被删除了。

时间 事务A 事务B
T1 开启事务 开启事务
T2 select * from user where user_id=100 假设为小明的用户信息
T3 将user_id=100的用户信息对应的年龄修改为18
T4 提交事务
T5 再次查询发现用户的年龄变更
T6
T7 提交事务

幻读

一个事务按相同的查询条件重新读取以前检索过的数据,却发现其它事务插入了满足其查询条件的新数据。

时间 事务A 事务B
T1 开启事务 开启事务
T2 select * from user where age=18 假设得到两条记录
T3 向user表插入一条age=18的新记录
T4 提交事务
T5 再次查询得到三条记录
T6 ..
T7 提交事务

幻读和不可重复读的区别

  • 不可重复读的重点是修改:在同一事务中,相同的条件,第一次和第二次读到的数据不一致(中间有其它事务提交了修改)。
  • 幻读的重点是新增或者删除:在同一事务中,相同的条件,第一次和第二次读到的记录数不一样(中间有其它事务提交了新增或者删除)。

这很重要不管是面试工作,还是排查异常,出现问题的时候需要发现、定位、解决能力。也是开发人员必须掌握的一项能力。

理解下锁几种维度

1. 类型维度

  • 共享锁(读锁 / S 锁)

  • 排它锁(写锁 / X 锁)类型细分:

    • 意向共享锁

    • 意向排他(互斥)锁

  • 悲观锁(使用锁,即 for update)

  • 乐观锁(使用版本号字段,类似 CAS 机制,即用户自己控制。缺点:并发很高的时候,多了很多无用的重试)

2. 锁的粒度(粒度维度)

  • 表锁
  • 页锁(Mysql BerkeleyDB 引擎)
  • 行锁(InnoDB)

3. 锁的算法(算法维度)

  • Record Lock(单行记录)
  • Gap Lock(间隙锁,锁定一个范围,但不包含锁定记录)
  • Next-Key Lock(Record Lock + Gap Lock,锁定一个范围,并且锁定记录本身, MySql 防止幻读,就是使用此锁实现)

InnDb架构

  • 这是官方文档中的架构图

innodb-architecture

Buffer Pool

在使用InnoDB的时候,索引都是以页的形式存在在表空间中的,本质上是存储在硬盘上的。设计InnoDB的大叔为了缓存磁盘中的页,在MySQL服务器启动的时候就向操作系统申请了一片连续的内存,他们给这片内存起了个名,叫做Buffer Pool(中文名是缓冲池

1
2
3
[server]
innodb_buffer_pool_size = 268435456
-- 268435456的单位是字节,也就是我指定Buffer Pool的大小为256M。需要注意的是,Buffer Pool也不能太小。--

Mysql log

日志是mysql中非常重要的组成部分,记录着数据库运行期间的各种状态信息。Mysql的日志主要包括错误日志查询日志慢查询日志事物日志、二进制日志大类。

binlog

binlog用于记录数据库执行的写入性操作(不包括查询)的信息、以二进制的信息保保存在磁盘中,binlog是Mysql的逻辑日志,又server层进行记录。使用任何的存储引擎都会进行记录。

  • 逻辑日志:记录sql语句的日志。
  • 物理日志:mysql innodb数据是保存在数据页中,物理记录的就是数据页变更。

binlog是通过追加的方式写入,当文件达到一定大小值以后,会生成全新文件来保存日志。

  • 主从复制:Master端开启binlog,然后将binlog发送到Slave端,Slave端通过重放binlog达到数据一致。
  • 数据恢复:通过mysqlbinlog工具来恢复数据库。

Innodb下只有在事物提交的情况下才会记录binlog,此时记录还在内存中。mysql通过sync_binlog参数控制binlog刷盘时机。取值范围0-n

  • 0:不去强制要求,由系统自行判断何时写入磁盘;
  • 1:每次 commit 的时候都要将 binlog 写入磁盘;
  • N:每N个事务,才会将 binlog 写入磁盘。

binlog日志格式

MySQL 5.7.7 之前,默认的格式是 STATEMENTMySQL 5.7.7 之后,默认值是 ROW。日志格式通过 binlog-format 指定。

  • STATMENT:基于SQL 语句的复制( statement-based replication, SBR ),每一条会修改数据的sql语句会记录到binlog 中 。

    • 优点:不需要记录每一行的变化,减少了 binlog 日志量,节约了 IO , 从而提高了性能;
    • 缺点:在某些情况下会导致主从数据不一致,比如执行sysdate() 、 slepp() 等 。
  • ROW:基于行的复制(row-based replication, RBR ),不记录每条sql语句的上下文信息,仅需记录哪条数据被修改了 。

    • 优点:不会出现某些特定情况下的存储过程、或function、或trigger的调用和触发无法被正确复制的问题 ;
    • 缺点:会产生大量的日志,尤其是alter table 的时候会让日志暴涨
  • MIXED:基于STATMENTROW 两种模式的混合复制(mixed-based replication, MBR ),一般的复制使用STATEMENT 模式保存 binlog ,对于 STATEMENT 模式无法复制的操作使用 ROW 模式保存 binlog

redo Log

事务的四大特性里面有一个是 持久性 ,具体来说就是只要事务提交成功,那么对数据库做的修改就被永久保存下来了,不可能因为任何原因再回到原来的状态

  • 保持一致性

最简单的做法是在每次事务提交的时候,将该事务涉及修改的数据页全部刷新到磁盘中。但是这么做会有严重的性能问题,主要体现在两个方面:

  1. 因为 Innodb 是以 为单位进行磁盘交互的,而一个事务很可能只修改一个数据页里面的几个字节,这个时候将完整的数据页刷到磁盘的话,太浪费资源了!
  2. 一个事务可能涉及修改多个数据页,并且这些数据页在物理上并不连续,使用随机IO写入性能太差!

因此 mysql 设计了 redo log具体来说就是只记录事务对数据页做了哪些修改,这样就能完美地解决性能问题了(相对而言文件更小并且是顺序IO)。

undo Log

MVCC