Mysql全总结

翻斗花园小恶霸图图
212 阅读24分钟

MySQL

1.MySQL 的基础架构

Server层

包括 连接器、查询缓存、分析器、优化器、执行器，Server层提供了 MySQL 大部分核心服务，存储过程、触发器、视图、内置函数、跨存储引擎等功能都在Server层实现。

存储引擎层

负责数据的存储和提取，它支持 InnoDB、MyISAM、Memory 等多个存储引擎，最常用的存储引擎是 InnoDB，建表时默认使用 InnoDB，也可以选择别的引擎，不同存储引擎的表数据存取方式、支持的功能也不同，不同的存储引擎共用一个Server 层。

2.一条查询语句的执行过程

  1. 连接器：首先客户端要通过 TCP连接 发送 连接请求 给 MySQL连接器，连接器负责跟客户端 建立连接、认证权限、管理连接。
  2. 查询缓存：如果是8.0之前的版本，MySQL 收到查询请求后会先 查询缓存 ，若缓存命中并且当前用户具备查询权限就返回结果，结束查询，查询缓存 失效频繁、效率低下，所以该功能在 MySQL8.0 被删除。
  3. 分析器：要执行语句，首先要通过 分析器 作语法分析（还会判断表、列是否存在），若语法正确那么 MySQL 就知道要查什么东西了，若语法不对MySQL会返回错误提醒。
  4. 优化器：语句在执行前还要经过优化器进行优化，通过索引生成最快的查找方案。
  5. 执行器：现在语句终于能交给执行器来执行了，执行前先判断用户是否有查询权限，若没有则返回没有权限的错误，若有则继续执行，执行器会使用表的引擎提供的接口进行查询，并最终将结果集返回给客户端。至此，查询语句就执行完了。

3.一条更新语句的执行过程

  1. 更新语句的执行过程大体跟查询语句的执行过程相同（执行语句首先得通过连接器连接上数据库->然后分析器分析语法->优化器选择最优查询方案->执行器确认权限后调用存储引擎的接口来具体执行，执行阶段为了数据的安全和可追溯性需要记录 redo log 和 binlog 两个日志。

  2. 执行器与InnoDB引擎 如何执行更新(即原子提交协议redo log的两阶段提交)：

    1. 执行器向引擎索要对应的行，引擎要保证对应的数据页在内存中，并返回对应的行
    2. 执行器拿到对应的行后执行更新，再调用引擎的写入接口
    3. 引擎会将这行数据在内存中更新，然后记录redo log，redo log进入prepare状态
    4. 执行器记录binlog
    5. 执行器调用引擎的提交事务接口，redo log进入commit状态，更新完成

  3. redo log的两阶段提交意义：两阶段提交被称作原子提交协议，redo log的两个状态保证了事务的原子性，要么一起记录该事务的日志，要么都不记录，这就避免了redo log 和 binlog 不一致的问题。

alt

4.redo log(重做日志：InnoDB引擎层的日志) :

  1. 概念：redo log 是 存储引擎层(innodb)生成的日志，记录了某一行更新后的数据，InnoDB 引擎执行更新语句时会先更新内存并把更新记录到 redo log 里，在系统空闲时落盘（这种 先写日志，再写磁盘 的做法就是MySQL中常说的WAL技术---Write Ahead Log）。
  2. 作用：提高MySQL的性能，提供崩溃恢复的能力（保证了事务的持久性）。
  3. 实现方式：redo log的的具体实现是一个数据环，上面两个指针 write pos 和 check point 分别指向当前记录的位置和要擦除的位置，如果write pos追上check point那就不得不进行写磁盘操作，移动check point指针。
  4. crash safe（崩溃恢复） ：MySQL在崩溃后也可以通过查询磁盘中的redo log日志文件恢复记录。
  • redo log 虽然也是文件需要落盘，但redo log是顺序写，不需要查找位置就能直接落盘，而更新数据的落盘首先要查找位置，因此速度很慢。

5.binlog (归档日志：Server层的日志)

  1. 概念：binlog是逻辑日志，记录的是如何更新，而redo log记录的是更新后的值。
  2. 作用：binlog一般用来配合整库备份进行主从库复制和归档。 （比如今天误删了一个表，需要找回数据，那我们先找到之前的整库备份，把这个备份恢复到临时库，然后从备份的时间点读 binlog 将备份更新到删表之前的时刻）
  3. 不同点1：redo log 是 InnoDB 引擎特有的；binlog 是 Server 层实现的，保证了数据库使用不同引擎时的一致性。
  4. 不同点2：redo log 是有固定空间的数据环，binlog 没有空间限制，redo log中的记录都是未落盘的（注意redolog也是真实存在于磁盘中的文件,这里指的未落盘指的是redolog中的记录没落到磁盘数据页），所以在发生crash时读redo log就能恢复数据库，而binlog则没法判断记录是否已经落盘自然无法得知哪些记录要用于恢复了，崩溃恢复也就无从谈起。

6.undo log（回滚日志）

  1. 作用：保存了事务发生之前的数据的一个版本，可以用于回滚，同时可以提供多版本并发控制（MVCC）的快照读。

7.事务

  1. 概念：事务是一组数据库操作，要么都执行，要么都不执行。
事务特性：ACID
  1. 原子性：事务是不可分割的最小执行单位，保证事务要么都完成，要么都不完成。（通过原子提交协议——二阶段提交实现）
  2. 一致性：数据库总是从一个一致性的状态转换到另一个一致性的状态。（依靠Server层的binlog在MySQL使用不同引擎时都能使用的特点实现）
  3. 隔离性：保证并发访问数据库的事务之间互不影响，隔离性也有不同的级别，但一般来说，一个事务在提交之前对其他事务是不可见的。
  4. 持久性：事务一旦提交，对数据库的改变是持久的。（依靠redo log提供的crashsafe能力实现）
隔离级别（概念及每个隔离级别会出现的问题）

  1. 读未提交：事务中的修改，即使没有提交，对其他事务也都是可见的；会出现的问题是脏读：事务读取未提交的数据的行为

  2. 读提交：事务只能看见已提交的事务所做的修改；这种级别也叫不可重复读：因为两次执行同样的查询可能得到不一样的结果

  3. 可重复读：事务在执行期间看到的数据是不变的，重复读取数据结果均相同；根据SQL标准的定义，可重复读隔离级别会导致幻读的问题，但在实际的实现中，每种存储引擎实现的隔离级别是不相同的，InnoDB就使用 MVCC+next-key lock 完全解决了幻读的问题

    1. 会出现的问题是幻读：事务A 在读取某个范围内的数据时，事务B 在该范围插入了新的数据，事务A再次读取该范围数据时，读到了事务B 插入的数据 称为幻读。InnoDB通过 MVCC和next-key锁 解决了幻读的问题，完全保证了事务隔离级别，已经达到了SQL标准的串行化隔离级别。

    2. InnoDB幻读的解决方法：

      1. 在快照读读情况下通过MVCC来避免幻读
      2. 在当前读读情况下通过next-key lock来避免幻读

  4. 串行化：通过强制事务排序，使之不可能发生冲突，从而解决幻读问题。

隔离性的实现 （MVCC+next-key lock）

  1. InnoDB引擎可以在可重复读级别就实现了完全的事务隔离（即SQL标准的串行化级别），其实现简单来说就是 快照读时用MVCC 和 当前读时用next-key lock。

    1. 快照读的实现：针对普通的select查询使用快照读，InnoDB利用多版本并发控制（MVCC）为每行记录添加了一个版本号（row trx_id），使事务通过回滚读取版本号<=当前版本号的数据，确保事务读取到的数据，要么是在事务开始之前已经存在的，要么是事务自身增删改的，解决了不可重复读，并且在快照读情况下避免了幻读问题。补充一句，事务通过快照来判断一条数据是否可见（若版本号小于快照中的低水位或等于自己事务的版本号则可见，否则不可见）可重复读级别下快照在每个事务的第一个select语句运行时产生，由此保证了一致性，而读提交在每一条SQL语句执行时都产生快照，这就导致不可重复读。
    2. 当前读的实现：针对 select … lock in share mode、select … for update、insert、update、delete操作，我们必须要使用当前读取得最新版本的记录，而为了保证当前读的一致性，我们要阻塞其他事务的当前读，所以要对数据行进行加锁（事务提交后释放），使用范围索引时要使用next-key lock避免出现幻读现象。

8.锁的种类

全局读锁

  1. Flush tables with read lock (FTWRL)加全局读锁，让整库处于只读状态。场景：做全库逻辑备份

  2. InnoDB可以用快照实现一致性读，但MyISAM不支持事务，为保证备份的一致性只好使用全局读锁

  3. 使用 全局读锁(FTWRL全局读锁) 和 只读(set global readonly=true) 的区别

    1. readonly 的值在某些系统中可能会被用作其他逻辑，最好不要动它
    2. 全局读锁在客户端发生异常断开连接时被释放，readonly则不会在发生异常断开时自动将值修改回去，这导致readonly的风险更高
表级锁

  1. 表锁：顾名思义就是一张表的锁，有读锁有写锁。**语法是 lock tables … read/write。**与 全局读锁FTWRL 类似，可以用 unlock tables 主动释放锁，也可以在客户端断开的时候自动释放。

  2. 元数据锁MetaDataLock：为防止DDL操作对DML操作的正确性产生影响的 表级锁。（DDL操作：表结构变更；DML操作：增删改查） 2. 元数据锁 不需要显式使用，会被自动加上，其加锁解锁的执行方式符合两阶段锁协议，对一个表做 DML操作 加 元数据读锁、做DDL操作 加 元数据写锁，在事务提交之后释放。

    1. 为避免 拿 元数据写锁 的操作长时间阻塞其他事务拿 元数据读锁 导致该表无法读写，这会导致严重后果，所以我们要知道如何安全地给小表加字段

      1. kill掉长事务，自然就能更快拿到 元数据写锁 了。
      2. 设定等待时间，一段时间拿不到就暂时放弃，不阻塞后面的DML操作，之后再重复这个步骤就好了，这种方式在解决死锁问题上也有所应用。
行锁
  1. 概念：顾名思义就是行的锁，有读锁有写锁的。他的加锁解锁方式也是符合两阶段锁协议，两阶段锁协议即指需要用到锁的时候加锁，事务提交的时候统一释放所有的锁，按照这个特性我们知道在一个事务中应该尽量将 最可能发生资源竞争的语句 放在后面，这样一个事务持有该资源的时间就会减少，提高并发
死锁

  1. 概念：当A事务拥有锁1需要锁2，同时B事务拥有锁2需要锁1的时候，两个线程就进入了天荒地老的循环等待，这就是死锁。

  2. 预防死锁：两种办法

    1. 设定锁等待时间：如果超过一定时间没有获取到锁就暂时放弃，然后回滚，此时会自动释放当前事务持有的锁
    2. 被锁时发起死锁检测：当一个线程被锁的时候就要找到他正在等待释放锁的线程，再递归着去找这个线程等待的线程，如果发现环就证明死锁了，那该回退就回退、该释放就释放。死锁检测还是挺耗时的，如果是简单的两个事务成环那两下就判断出来了，但一般没死锁要递归判断很多次，几乎是个O(n)的操作，这导致死锁检测占用很多CPU资源，虽然没死锁了但是事务又处理不了几个。

  3. 场景题：怎么解决热点行更新导致的性能问题？三个办法

    1. 关掉死锁检测：如果你能确保这个业务一定不会出现死锁，可以临时关闭死锁检测
    2. 控制并发度：控制更新同一行的线程的数量，降低死锁检测的成本
    3. 一个数据行改成逻辑上的多个数据行来减少锁冲突。比如银行账户余额这一行记录，可以分成10行记录的和，冲突概率就减小为 1/10，降低发生冲突的线程数，降低死锁检测的成本。

  4. 写一个死锁的例子

public class DeadLock implements Runnable{
    private static final Object lock1=new Object();
    private static final Object lock2=new Object();
    private int flag;

    public DeadLock(int flag) {
        this.flag = flag;
    }

    @Override
    public void run() {
        if(flag==1)
        {
            synchronized (lock1)
            {
                System.out.println(Thread.currentThread().getName()+"获取lock1并希望获得lock2");
                try {
                    Thread.sleep(10000);
                } catch (InterruptedException e) {
                    e.printStackTrace();
                }
                synchronized (lock2)
                {
                    System.out.println(Thread.currentThread().getName()+"获取lock2");
                }
            }
        }
        else
        {
            synchronized (lock2)
            {
                System.out.println(Thread.currentThread().getName()+"获取lock2并希望获得lock1");
                try {
                    Thread.sleep(10000);
                } catch (InterruptedException e) {
                    e.printStackTrace();
                }
                synchronized (lock1)
                {
                    System.out.println(Thread.currentThread().getName()+"获取lock1");
                }
            }
        }
    }

    public static void main(String [] args){
        DeadLock d1=new DeadLock(0);
        DeadLock d2=new DeadLock(1);
        Thread t1=new Thread(d1);
        Thread t2=new Thread(d2);
//        t1.run();
//        t2.run();
//        不可以用run()，因为run()会等待线程执行完才能执行下一个线程，不是并发的
        t1.start();
        t2.start();
    }
//输出结果
//Thread-0获取lock2并希望获得lock1
//Thread-1获取lock1并希望获得lock2
}
乐观锁和悲观锁（另一个分类角度）
  1. 乐观锁：乐观锁是一种思想，简单来说就是不认为并发修改经常发生，其不真正给数据加锁而是依靠CAS指令作为核心算法来实现事务的原子性，作为一条 CPU 指令，CAS 指令本身（指 比较和赋值）是能够保证原子性的。常见的乐观锁实现是版本号机制，简单来说就是 在读取数据的时候将version一并读出，然后计算好将要更新的值，此时再把version读出来，如果没变那就将新值赋值给数据，变了那就不能修改，然后你可以选择通过自旋的方式把数据改成功了为止，也可以就此放弃，那是开发者的事。
  2. 悲观锁：悲观锁的思想就是认为数据经常会被外界修改，实现上就是锁机制，绝对保证数据访问的排他性。

9.数据库三大范式与反范式 的概念及解决的问题

  1. 概念：范式是设计表结构的标准。高阶范式在低阶范式的基础上冗余度更低，一般要让表的设计尽量满足第三范式（3NF），但有时为提高某些查询性能，要破坏范式，也即反范式。

  2. 第一范式：表中的每个字段属性都是原子性的，不可分割；

    （比方讲你要记录地点，那你不能设计一个字段是 省+市 的格式，应该拆开来）

  3. 第二范式：要求非主属性都要完全依赖于完整的（联合）主键；一张表只表达一个独立的意思。

    （比如说记录选手战绩的 选手战绩表，他的主键是 选手id+比赛id ，其他字段有 姓名、比赛时间、得分，这其中就只有得分是完全依赖于主键的，而 姓名 和 比赛时间就没必要在这重复的记录造成冗余，到 选手表 和 比赛表 去查就是了）

  4. 第三范式：任何非主属性不依赖于主键外的字段；

    （比如说记录选手信息的 选手表，他的主键当然是选手id，其他字段有 队伍id， 比赛id ，这个 比赛id 就不依赖于主键，而是依赖 队伍id 的，如果放在这不仅会造成数据冗余，你要插入一条 比赛id 原本是件很简单的事情只要在对应的队伍表那里插入就好了，现在要在每一个 参赛选手 的选手表里都插入一下，而且如果举办方如果不能知道参赛队伍有哪些选手参赛，那岂不是连插入信息都做不了了，这就是潜在的异常）

  5. 解决的问题：三大范式既减少数据冗余，也避免了一些更新数据时的异常。

  6. 反范式：为了性能和读取效率违反范式化的原则，以空间换时间的策略允许适当的冗余数据在表中；

    （比如说记录选手战绩的 选手战机表，他的主键是 选手id+比赛id，其他字段就一个 得分 ，我们要是需要选手 姓名 和 比赛时间 ，就得联合其他表来查，当然会比较慢了，你要是想快那就可以违反范式化的原则，把 姓名 和 比赛时间 存在选手战绩表中）

10.索引

种类
以数据结构分类：
  1. B-树索引（Balance Tree平衡多叉搜索树）：由于平衡二叉搜索树log2n的查询性能较低，所以B-树是M叉树，时间复杂度降为logMn，快了很多。

alt

  1. B+树索引（对比B树来记）：B+树 是 B树 的改进版。

    1. B+树 和 B树 的根本差距：就在于 B+树 仅在叶子节点存储数据，一改 B树 中每个节点既存索引又存数据的做法，因此 B+树 的实现相较 B树 会略有不同。（比如在B+树中 关键字数量=儿子数量M，并且关键字也会在子结点中存储，这样传递下去所有关键字都会在子结点中出现，叶子节点构成一个有序列表；B树 每个节点中 关键字数量=儿子数量M-1，节点中出现过的关键字在子结点中不会再出现）
    2. B+树的优点：B+树这种数据在叶节点中以有序链表相连的结构大大增加了范围查询的性能；根据局部性原理：当一个数据被用到时，其附近的数据也通常会马上被使用，B+树的结构非常有利于基于局部性原理的预读，IO一次会把一个数据页 读到内存（InnoDB 中的数据页16KB，磁盘页4KB，这得区分开来），这也大大提升了查询性能。

alt

  1. 哈希索引：基于哈希表实现，不像B+树索引还能使用不完全的字段遵从最左前缀原则来使用索引，哈希表必须要精确匹配所有的列才能查询，引擎会通过 所有的索引列 计算一个哈希码，哈希表的键就是哈希码，对应的值就是指向数据行的指针，数据行存放在内存中读取非常快，在Memory引擎中的默认索引类型就是哈希索引，Memory在发生哈希冲突时会用拉链法拉个链表，InnoDB的自适应哈希索引则会在察觉到某些索引被频繁使用时创建哈希索引。
以功能逻辑分类：
  1. 主键索引、普通索引、唯一索引：在InnoDB中主键索引就是聚簇索引，它会按照主键值的排序将数据存储在磁盘中，其叶子节点中存储的是数据，也因为数据只能按照一种顺序存储在磁盘中所以一张表只有一个主键索引；普通索引也按照键值来排序，但其存储的是数据对应的主键值，要到主键索引中再查一次才能查出数据，但InnoDB引擎自带的自适应哈希索引可以减少这种重复的工作；唯一索引意思就是在普通索引的基础上增加了对数据唯一性的约束。
  2. 全文索引：全文索引是中查找文本中关键词的特殊索引，它把每个词语出现的 频率和位置 按顺序归纳，对文件建立一个以词库为目录的索引，快速定位到词语出现的位置。
以物理实现方式分类：
  1. 聚簇索引：聚簇索引是一种数据存储方式，意思是主键值相邻的数据行紧凑存储在一起，聚簇索引会按照键值来排序，其叶子节点存储的是数据，正因为数据只能按一种顺序存储在磁盘中，所以一张表里只有一个聚簇索引，在InnoDB里主键索引就是聚簇索引，在MyISAM里索引和数据是分离的所以其主键索引不是聚簇索引，所以说 **不能完全说 主键索引=聚簇索引 **。
  2. 非聚簇索引：非聚簇索引一般就是非主键索引的普通索引，我们也管它叫二级索引、辅助索引，它也会按键值来排序，只不过其叶子节点存储的不是数据而是数据的主键值，然后要根据主键值到聚簇索引中再查找一次，这就做了重复的工作，但InnoDB引擎自带的自适应哈希索引则可以减少这种重复的工作。
什么情况下索引失效：常见的有这些
  1. 对索引进行了表达式计算会失效
  2. 对索引使用函数会失效
  3. or前用索引or后不用索引会失效
  4. 不按最左前缀原则使用联合索引会失效（包括使用’%'开头的表达式进行模糊查询在我理解中也算不遵守最左前缀原则）
  5. 最左前缀原则：在使用联合索引的时候要注意符合最左前缀原则，只能从左到右的查字段，比如可以这样：精确查询 最左字段+第二字段、范围查询 最左字段、精确查询 最左字段并范围查询第二字段，你要是跳过 左边的字段查后面的字段那索引就失效了。
覆盖索引

  1. 概念:普通索引（普通索引的字段和主键的字段）已经覆盖了所有需要查询的字段，这就叫覆盖索引，这种情况下根本不需要回表了，这对性能是极大的提升。

    比如说有一张 学生表，主键是自增主键id，其他字段有姓名和学号，如果我们要频繁根据学号查询学生姓名，那我们可以将 学号和姓名 设为联合索引，那我们再利用最左前缀原则就可以高效并且不用回表的查得 学号+姓名(索引自身的列)+id(主键的列) 这三个字段了，那查姓名当然就很快了。

11.数据库性能系列（了解即可，面试不问）

  1. 明明应该很快的SQL语句偶尔会执行很慢怎么回事：由于数据库的写操作仅更新了内存页，然后写redo log和binlog（两阶段提交）就算修改完成了，所以数据库总要找时间把内存中的数据写回磁盘（同时将redo log的check point指针推进），所以有时候SQL语句会变慢可能就是因为在刷脏页。
  2. InnoDB由于MVCC的缘故没法记录每个版本的行数所以count(*)要一行一行数性能很差，如果我们要快速读count(*)那我们可以建一张表专门记录各表的行数，而且这是可以保证一致性的，其实count(*)已经优化过了比`count(字段)要快了。

12.数据库优化系列

通过性能分析工具可以让我们了解执行慢的 SQL 都有哪些，查看具体的 SQL 执行计划，甚至是 SQL 执行中的每一步的成本代价，这样才能定位问题所在，找到了问题，再采取相应的行动。

优化 SQL 语句的步骤

  1. 通过开启慢查询日志来收集慢查询

    1. 开启慢查询日志set global slow_query_log='ON';
    2. 设置慢查询时间set global long_query_time=2;
    3. 设置慢查询日志的文件位置set global slow_query_log_file='/var/lib/mysql/instance-1-slow.log';

  2. 通过慢查询日志我们就知道是哪些SQL慢了，然后我们就针对性地用 EXPLAIN 查看这条语句的执行计划，我们于是知道了数据表的访问类型，用到的索引，扫描的行数，一目了然！

    1. type：数据表的访问类型，比如all就是全表扫描；possible_keys：可以使用的索引；key：实际使用的索引；rows：扫描行数

    alt

  3. 使用 SHOW PROFILE 可以查看语句的具体执行成本

    1. PROFILE默认关闭，开启她set profiling = 'ON';
    2. 然后show profile;查看有哪些查询，用show profile for query ID可以查看某个查询每一步的执行时间
慢 SQL 语句的几种常见诱因
  1. 索引设计的不好，我们要好好的重新设计，比如说我们设计一个联合索引以索引覆盖的形式避免回表
  2. MySQL语句写的不好，慢查询日志查一下哪条语句出问题，想办法把它改好，比如说没遵守最左前缀原则导致索引失效，再比如说InnoDB的count(*)傻傻的一行行数实在太慢了那我们可以建一个表专门存各个表的count(*)
  3. 锁等待时间太长，我们一般把容易产生资源竞争的锁往后放，减少一个事务占用这把锁的时间
  4. MySQL选错索引了，我们可以用force index强制MySQL选择某个索引，比如说强制MySQL使用id这个索引select * from id force index(id) where id between 10000 and 20000;
avatar
文章
阅读
粉丝