MySQL知识整理

发布于 — 2019 年 09 月 19 日
#MySQL

推荐看一下极客时间上面的一篇教程，《MySQL实战 45 讲》。

基本架构

MySQL基本架构

引擎层采用插件式。

连接器

负责跟客户端建立连接，获取权限、维持和管理连接。客户端连接后长时间没动静，连接器会自动将他断开，这个时间是由参数wait_timeout控制的，默认值是 8 小时。

分析器

这里面判断输入的语句是对什么表，什么列，做什么操作。如果语句不对，则会收到错误提示。

🌰：select * from user where id=1;

这个语句它会分析出对user表，id列进行查询操作。如果select单词拼写错误，或者sql 语法错误时，它会返回错误信息。

优化器

在表里面有多个索引时候，它来决定使用哪个索引；或者在一个语句有多表关联（join）时，决定各个表的连接顺序。

执行器

先进行权限判断，判断操作用户是否有对表的操作权限。

如果有权限，就打开表继续执行。

🌰：select * from user where id=10;

以这个语句为例，当字段没有索引时，执行器的执行流程是这样的：

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

如果有索引的表，第一步调用的是“取满足条件的第一行”这个接口，之后循环取“满足条件的下一行”这个接口、

引擎

  1. InnoDB

从 5.5.5 版本开始作为默认的引擎

支持事务，支持行级锁，支持外键

事务的基本要素（ACID）:

  • 原子性（Atomicity）

事务开始后的所有操作，要么全部完成，要么全部不做，不可能部分完成。事务执行过程中出错会回滚到事务开始的状态，所有的操作就像没发生一样。

  • 一致性（Consistency）

事务开始前和结束后，数据库的完整性约束没有被破坏。

  • 隔离性（Isolation）

同一时间，只允许一个事务请求同一数据，不同事务之间批次没有任何干扰。

  • 持久性（Durability）

事务完成后，事务对数据库的所有更新被保存到数据库中，不能回滚，不会改变。

  1. MyIsam

之前的默认引擎，不支持事务，只支持表级锁，不支持外键

事务

事务的隔离级别

在MySQL中有4种隔离级别

  1. READ UNCOMMITTED

读未提交：一个事务还没提交时，它做的变更就能被其他事务看到。会出现脏读。

别人改数据的事务尚未提交，我在我的事务中就能读到

  1. READ COMMITTED

读提交：一个事务提交后，它做的变更就能被其他事务看见。可以避免脏读，但不能避免重复读和幻读的情况。

别人改数据的事务提交后，我在我的事务中才能读到

  1. REPEATABLE READ

可重复读：（MySQL的默认隔离级别）。一个事务执行过程中看到的数据，总是跟这个事务在启动时看到的数据一致。未提交的变更对其他事务也不可见。它可以避免脏读，不可重复读的问题。理论上会出现幻读的情况，但是MySQL的存储引擎通过多版本并发控制机制（mvcc）解决了该问题，因此该级别是可以避免幻读的。

别人该数据的事务已经提交，我在我的事务里也不去读

  1. SERIALIZABLE

可串行化：对于同一行记录，“写”会加“写锁”，“读”会加“读锁”。当出现读写锁冲突时，后访问的事务必须等待前一个事务执行完成，才能继续执行。实际运行时是在每个读的数据行上加锁。所以可能导致大量的超时现象和锁竞争。

我的事务尚未提交，别人就别想该数据

级别从低到高依次为：读未提交->读已提交->可重复读->可串行化。

名词解释：

脏读：一个事务读取了另外一个事务未提交的数据。

重复读：在事务内重复读取了别的线程已经提交的数据，但是两次读取的结果不一致，原因是在两次读取的过程中其他事务做了更新操作。

幻读：在一个事务内两次查询的数据条数不一致，原因是在查询的过程中其他事务做了添加操作。

索引

数据结构

对于 InnoDB 引擎来说，它存储所以采用的是 B+树的数据结构。

B+树

使用 b+ 树作为索引的原因：

数据库的数据放到磁盘中，对于程序而言是外存，进行读取的时候涉及到磁盘 io 操作，而 io 操作又比较耗时，所以为了减少耗时，不采用二叉树的数据结构（对于树来说，查找的效率取决于树高）。采用 b+ 树，它将所有数据存储到叶子节点中，父节点只存储索引（所以相同的数据大小，能存储更多的节点）。叶子节点构成一个有序链表（便于连续查询）。

B+树的磁盘读写代价更低 B+树的内部结点并没有指向关键字具体信息的指针。因此其内部结点相对B 树更小。如果把所有同一内部结点的关键字存放在同一盘块中，那么盘块所能容纳的关键字数量也越多。一次性读入内存中的需要查找的关键字也就越多。相对来说IO读写次数也就降低了。 举个例子，假设磁盘中的一个盘块容纳16bytes，而一个关键字2bytes，一个关键字具体信息指针2bytes。一棵9阶B-tree(一个结点最多8个关键字)的内部结点需要2个盘快。而B+树内部结点只需要1个盘快。当需要把内部结点读入内存中的时候，B 树就比B+树多一次盘块查找时间(在磁盘中就是盘片旋转的时间)。

B+树的查询效率更加稳定 由于非终结点并不是最终指向文件内容的结点，而只是叶子结点中关键字的索引。所以任何关键字的查找必须走一条从根结点到叶子结点的路。所有关键字查询的路径长度相同，导致每一个数据的查询效率相当。

使用

索引类型分为主键索引和非主键索引，主键索引也被称为聚簇索引，叶子节点存的是整行数据。非主键索引也被称为二级索引，叶子节点内容是主键的值。所以使用非主键索引时需要再次使用主键索引进行回表查询。

覆盖索引

上面提到，当使用非主键索引时需要回表查询，但是如果查询结果只有主键的值，这时所需的结果不需要进行回表就能得到。也就是说，在这个查询里面，非主键索引已经“覆盖了”我们的查询需求，我们称为覆盖索引。

最左前缀原则

由于使用的是 b+树的数据结构，所以可以使用最左前缀。

当运行select * from user where name like '张%'这条语句时。可以从 name索引上先找到张的位置，然后再从张的位置向后查找，这样比从头开始查找更有效率。

并且当我们建立a,b,c三个字段的联合索引时，查询条件只有a或者a.b时也可以用到这个a,b,c的联合索引

索引下推

有一条语句：select * from user where name like '张%' and age=10 and ismale = 1;。查询姓张的，年龄为 10 岁，并且为男性的用户。

这个表有一个name,age的联合索引。

这种情况下，它找到满足 name 条件的结果后，会在索引里面进行 age 的判断（因为是 name，age的联合索引）。然后再去回表，这样能减少回表的次数。提高效率。

count()的效率

对于 count 函数，主要有count(*),count(1),count(主键 id),count(字段)这几种用法。

  1. count(字段)：遍历整张表，将每一行的这个字段拿出来，判断不为 null 时进行累加。
  2. count(主键id)：遍历整张表，把每一行的 id 取出来， 判断不为空，就按行累加
  3. count(1)：遍历整张表，但不取值，对于返回的每一行，按行累加。
  4. count(*)：按行累加。

整体效率：count(*)≈count(1)>count(主键 id)>count(字段)

参考：

  1. https://blog.csdn.net/qq_35008624/article/details/81947773
  2. 极客时间《MySQL 实战 45 讲》