MySQL日常（1）——初窥门径

coding_waling
85 阅读14分钟

最近在看高性能MySQL这本书，主要是想要利用这本书去了解一下数据库的运作方式与一些设计模型，日后也会持续更新MySQL相关的学习日记等内容，期待大家关注与大佬指点！！

第一章−MySQL架构与历史第一章-MySQL架构与历史

MySQL的体现灵活性主要体现在：支持自主配置、具备优秀的存储引擎架构（InnoDB或者是MyISAM)，基于架构设计使得MySQL可以实现查询、数据存储与系统操作实现分离处理。以分离处理或是存储分离的设计保证了在使用时可以根据性能、特性或者是其他存储需求来选择数据存储的方式。

MySQL架构层次

  • 客户端：实现认证授权、与服务连接处理、安全等
  • 连接/线程处理器、缓存、语句解析器、优化器及MySQL核心服务--解析、分析、优化、缓存以及所有的内置函数，在MySQL中所有的存储引擎功能都在这一层实现：存储过程、触发器、视图等。
  • 存储引擎：存储引擎不会解析SQL，只会单独与SQL服务器进行交互，负责数据的存储和提取。内部包含SQL的事务函数--InnoDB，不同的存储引擎之间不会互相通信。
连接与安全性连接与安全性

在MySQL中，客户端与服务器之间每有一个客户端就具备一个线程，当前连接的查询只能够在当前线程中执行，线程会在随机CPU中轮流执行。而服务器会将客户端的连接线程进行缓存，当客户端再次使用当前线程时，直接从缓存中获取线程即可。MySQL-5.5以后允许线程池插件用于管理线程。

MySQL的安全认证是基于用户名、密码与服务器SSL连接认证，在认证后，当客户端连接后执行操作时，会再次校验当前登录用户是否具备某个库表的操作权限。

优化与执行优化与执行

在MySQL中，针对查询语句，优化器会进行语句优化，包括：

  • 重写查询
  • 表的读取顺序
  • 合适索引选择
  • 查询数据缓存

对于SELECT语句的执行，MySQL的服务器会优先查询缓存Query Cache，如果缓存存在，则直接从缓存中获取返回，不再执行SQL语句的查询解析、优化与执行的过程（优化器无效）。

  1. 🐸什么时候MySQL的优化器无效？
 在SELECT语句进入MySQL服务器前，会首先进入查询缓存区获取当前查询结果，如果缓存中存在当前查询结果集，则优化器执行步骤不再执行，直接返回缓存数据。
并发控制并发控制

MySQL在多个查询语句操作同一个数据行时，针对并发控制问题添加了锁机制——此处可以看作MySQL内置的读锁与写锁——读锁与写锁本质上是共享锁与排他锁，但是使用时经常称之为读锁和写锁。

  • 读锁：共享锁的表现，在进行读操作时，不进行加锁操作，在读锁场景下，客户端的读取行为是互不干扰的。
  • 写锁：排他锁的表现，当客户端语句针对同一行数据进行写操作时，MySQL会自动为该行添加写锁，写锁保证了当前行只能够有一个线程执行写操作，其他线程需要等待前一个执行线程释放锁，但也会导致性能问题。

MySQL在锁粒度方面支持行锁、表锁与间隙锁。加锁所导致的性能问题-锁操作、获得锁、检查锁、锁释放等场景都会造成一部分的性能开销，因此MySQL支持每个存储引擎都可以实现自己的锁策略与锁粒度。常见的锁粒度为行锁与表锁。

  • 表锁：表锁会锁定整张表，是MySQL中开销最小的锁策略。MySQL在进行ALTER TABLE之类的操作语句就会使用表所，而忽略当前存储引擎所具备的锁机制。——可以理解为针对表操作的时候MySQL会使用表锁。
  • 行锁：行锁可以最大程度支持并发处理，锁粒度较小。当针对MySQL中的一行进行写操作时，会使用行锁，但是行锁只是在存储引擎层面的实现，MySQL服务器层面并没有针对行锁进行实现。
∗事务*事务

  1. 什么叫做事务？

    事务指的是针对数据库的一组原子性操作，要么全部成功，要么全部失败，支持回滚的行为叫做事务。

    在此可以举一个例子：

    银行转账案例：A向B转账，B接收失败，则转账失败，此时A的钱需要原路返回，B的转账金额还是为以前的金额。这样的一组操作称之为原子性操作。

    MySQL中开启事务可以使用START TRANSACTION语句，语句执行后使用COMMIT语句针对当前事务操作结果进行提交。

     START TRANSACTION;
 SELECT field FROM table_name WHERE condition;
 UPDATE table_name SET field = result WHERE field = condition;
 UPDATE table_name SET field = result WHERE field = condition;
 COMMIT;

  2. 事务的ACID属性

    • A：原子性：原子性指的是在一个事务必须看为不可再次分割的最小工作单元，不管内部需要执行多少SQL语句，对于本次事务来说，所有的SQL执行都属于一个不可拆分的整体，而不是只执行其中一部分操作。
    • C：一致性：一致性可以理解为，针对A与B的操作，A变化了多少，而B就要变化多少。
    • I：隔离性：当前事务结果对于其他事务是不可见的，此处与MySQL中的又一个重要概念即--事务的隔离级别有关。
    • D：持久性：事务提交后，数据不会再发生改变，永久存储在当前数据库中，及时数据库服务器出现宕机，当前数据也不会发生变化。

  3. 事务的隔离级别

    隔离级别规定了在不同级别下事务的提交状态——当前事务的可见性等。低级别的隔离级别可以提升MySQL的性能，但是会造成数据的安全性与一致性降低。

    • 读未提交（READ UNCOMMITTED）：在READ UNCOMMITTED级别下，事务之间不具备隔离性，每个事务对于其他事务都是可见的。因此会出现当上一个事务未完成时，读到上一个事务还未提交的数据——脏读。读未提交会导致很多问题，但是性能是最好的。
    • 读已提交（READ COMMITTED）:大多数数据库常见的默认隔离级别（MySQL不是），在读已提交的情况下，一个事务开始时只能看到自己的已经提交的事务，所有的修改对其他事务不可见。因此就造成了当出现并发场景时，两次同样的查询可能查询到不一样的结果——不可重复读。
    • 可重复读（REPEATABLE READ）：当前级别解决了脏读的问题，也保证了在同一个事务中多次读取同样记录的结果是一样的，但是可能出现幻读问题——当一个事务读取某个范围数据时，另外一个事务在当前范围插入了一条新记录，那么第一个事务读取时就可能发生与事务开始时不一样的数据。在MySQL中，InnoDB与XtraDB都使用了MVCC（多版本并发控制）来解决幻读问题。可重复读也是MySQL默认的隔离级别
    • 串行化（SERIALIZABLE）：最高隔离级别，可以看做是单线程，每个事务都必须等待前一个事务提交后在执行。在串行化级别中，即使在进行读操作的时候也会针对每一行都上锁，会导致大量的超时与锁竞争问题。

  4. 死锁

    死锁是导致服务崩溃/服务不可用的常见异常之一，那么是如何造成死锁的呢？

    例：

    •    # sql-1
   START TRANSACTION
   UPDATE table_name SET field WHERE field = 4 and field = '20';
   UPDATE table_name SET field WHERE field = 5 and field = '20';
   COMMIT;

    •    # sql-2
   START TRANSACTION
   UPDATE table_name SET field WHERE field = 5 and field = '20';
   UPDATE table_name SET field WHERE field = 4 and field = '20';
   COMMIT;

      以上便可能出现死锁情况，当两条事务同时提交，1执行第一条更新语句，对field=4的行进行上锁操作、2执行第一条更新语句，对field=5的字段进行上锁操作，在第一条语句执行结束后，都在互相等待对方释放当前的行锁从而进行下一步操作，此时就发生了锁的循环依赖，造成了死锁的现象。

    MySQL是如何解决死锁问题的？

    • 在数据库中，实现了死锁检测与死锁超时机制，在InnoDB引擎中，当存储引擎检测到当前出现死锁的循环依赖情况时，会立即返回一个错误并回滚其中占据行锁最少的事务。这种方式可以有效解决死锁。在死锁发生时，数据库层面完全回滚其中一个事务才可以打破死锁状态，因此常见的解决方案就如InnoDB的存储引擎一样，将事务涉及少量数据的死锁事务进行回滚，待上一个事务执行结束后再重新执行的当次事务即可。

  5. 事务日志

    MySQL的事务日志就是我们在开发层面上所常说的binlog日志，其记录数据库的增量数据，然后针对当前增量数据进行存储，生成预写式日志，从而实现数据的保存，当MySQL出现服务崩溃时，也可以基于当前binlog日志进行数据的重新写入。

    在MySQL中，日志可以分为undo-log（用于回滚事务）、redo-log（用于完成数据持久化）与bin-log（数据备份与主从复制）。在后面的学习中会总结到。

  6. 支持事务的存储引擎与自动提交

    MySQL中提供了InnoDB与NDB cluster两种支持事务操作的存储引擎，InnoDB最常用。在MySQL中，事务的提交方式默认为自动提交（Auto Commit），在实际使用时可以通过设置AutoCommit变量来修改属性（0/OFF——关闭、1/ON——开启），在关闭自动提交时，需要使用者自己手动Commit（提交）事务与手动控制事务RollBack（回滚），如果没有手动Commit，则当前事务未结束。

  7. 事务执行的锁定（显示-隐式）

    在InnoDB存储引擎中可实现显示锁定的SQL语句

     SELECT ... LOCK IN SHARE MODE
 SELECT ... FOR UPDATE

    （3.14日————基础概念理解及整理）

  8. MVCC（多版本并发控制）

    解决问题：解决了读写间事务问题，有效解决了脏读、幻读与不可重复读的问题，但是并没有解决更新丢失的问题（MySQL使用了悲观锁实现更新丢失的问题，用于实现写写操作间的并发隔离。）

    MVCC可以理解为：在每行的数据后默认添加了以时间戳为基准的快照版本号，在进行常规SELECT操作时，读操作会读取当前行的上一个时间戳版本内容（快照读），而进行诸如SELECT ... LOCK IN SHARE MODE(共享锁)或者是SELECT ... FOR UPDATE的语句进行查询操作时，则会将快照读转换为当前读（读取最新当前行版本号内容）。

    MySQL中具体MVCC实现如下：

    nameageDB_ROW_IDDB_TRX_IDDB_ROLL_RTP
    Petty24110x14567886
    • DB_TRX_ID：占位6byte，用于表示当前行最新修改（修改或是插入）事务ID，记录当前记录创建或者是最后一次修改的事务ID
    • DB_ROW_ID：隐含的自增ID，如表未设置主键，InnoDB存储引擎会根据隐含ID自动生成聚簇索引用于查询。
    • DB_ROLL_RTP：回滚指针，指向当前行的上一个版本号（这里还没有学得非常深入，后续会针对MVCC出一篇具体的文章进行分享--主要是学艺不精、害怕班门弄斧）。

    书中解释：这里讲在可重复读隔离级别下的MVCC所执行的操作

    1. 对于SELECT，InnoDB只会查询比当前事务版本号早的数据行，对于版本号未定义/大于当前事务版本号的行，只有满足以上两条的数据才会进行返回。（这部分其实不大理解，如果有大佬愿意解答，洗耳恭听！！！）

    MySQL页存储内容.png

    1. 写操作：所有的写操作InnoDB都会保存当前的系统版本号作为行版本号，在删除定义中，也同样如此。

    2. 重点：MVCC只会在可重复读与读已提交两个隔离级别下才会工作，其他两个隔离级别与MVCC是不兼容的，因为读未提交只会读取最新数据行，而可串行化则是直接将快照读取消，对所有读操作都加锁，隔离了读写的并发可能。

  9. 存储引擎

    1. InnoDB：InnoDB基于页结构实现数据存储，在InnoDB中，每页默认数据量为16KB（记住这个16KB，我个人认为还是蛮重要的），在进行磁盘存储时，会以16KB每页的内容进行刷盘存储写入到磁盘中。

      image-20240315211628040转存失败，建议直接上传图片文件

      注意，这里的索引位置，索引数量与数据数量是对应的（很久没画了，如果有错误或许会进行改正。）

    2. InnoDB基于MVCC来实现并发支持，内置上文提及的事务隔离机制（四个，分别为读未提交、读已提交、可重复读与串行化），并且使用了间隙锁来防止幻读的出现。间隙锁在后续的内容会提及。

    3. InnoDB的表是基于聚簇索引建立的，说到这里想起来之前面试时的一道面试题，大家也来一起思考一下：在进行查询时，是聚簇索引快，还是非聚簇索引快呢？为什么？我当时的解答是聚簇索引更快，但是不知道为什么，这也是我深入学习MySQL的一大原因。然后这里的面试题比较多，比如：回表问题、InnoDB索引存储结构，为什么这种存储结构更快，存储结构有什么优点等等。我也会积极总结，争取早日整理为易理解，好记忆的面试题文章发出！！

    4. InnoDB的优化：InnoDB在进行磁盘读取时使用了可预测性预读操作，可以自动在内存中创建hash索引来加速读操作的自适应hash，并且这种方法可以加速插入操作的插入缓冲区。

    5. 支持事务，行锁，支持多种索引结构，页结构存储等都属于是InnoDB存储引擎的特点。

  10. MyISAM

    1. MyISAM在这里不做特别多的扩充，列举几个MyISAM的特点————不支持事务，表锁，支持全文索引，支持空间函数等特点。不支持事务的特点使得MyISAM在进行数据操作时不需要做多余的操作逻辑，因此速度更快，但是会出现数据一致性问题。MyISAM在进行存储时，会将表存储在两个文件中，分别是：索引文件以及数据文件，文件拓展名为.myd和.myi。

    2. 压缩表：当数据进行导入或是创建之后，不会再进行修改等写操作，就可以使用MyISAM压缩表进行存储。压缩表的一大特点就是不可修改，压缩表可以极大减少磁盘占用，因此可以减少查询时的磁盘IO次数，那么大家知道进行一次单表查询的IO次数是多少吗？希望知道的小伙伴一起解答一下哦！在压缩表中，支持索引，但同样是只读状态。

    3. 性能：不支持事务并且存储结构简单提升了性能，但是表锁的特性又影响了表的性能，因此对于只读数据，可以考虑选用MyISAM存储引擎。

（3.15日——补充存储引擎及MVCC等概念）

日后的学习中也会根据书中的内容总结一些容易理解且有趣得笔记内容进行分享！最后祝愿各位学习编程/数据库知识的小伙伴一起进步，早日上岸大厂！！！

avatar
文章
阅读
粉丝