跳到主要内容

MySQL 篇

什么是事务？事务的四大特性？​

事务是一组操作的集合，它是一个不可分割的工作单位，这些操作要么同时成功，要么同时失败。

事务的四大特性是：ACID

  • Atomicity 原子性：事务是不可分割的最小操作单元，要么全部成功，要么全部失败。

    原子性是通过 undo log （回滚日志）来保证的，因为 undo log 日志记录的是数据修改前的信息。

    比如：

    insert 一条数据，undo log 会记录对应一条 delete 日志。

    update 一条数据时，undo log 会记录之前 旧值 的 update 日志。

    delete 一条数据时，undo log 会记录这条数据的 insert 日志。

    如果执行事务出现了异常情况，就会回滚，利用 undo log 记录数据，来恢复到事务之前的状态。

  • Consistency 一致性：事务完成时，必须所有的数据保持一致的状态。

    一致性是我们使用事务的目的，隔离性、持久性、原子性都是我们保障一致性的手段。

  • Isolation 隔离性：数据库提供隔离机制，保证事务在不受外部并发操作影响的独立环境下运行。

    如果多个事务操作同一个数据，那么就会产生脏读、不可重复读、幻读的问题。

    于是，事务与事务之间需要一定的隔离。在 InnoDB 引擎中，定义了四种隔离级别供我们使用。

  • Durability 持久性：事务一旦提交或回滚，它对数据库中的数据的改变就是永久的。

    持久性由 redo log （重做日志）来保证，redo log 分为重做日志缓冲和重做日志文件。

    当我们修改数据的时候，MySQL 先把这条记录所在的页找到，然后把该页加载到内存中，将对应记录修改。为了防止内存修改完了，MySQL 就挂掉了，就会写一份 redo log，记录这次在某个页上做了什么修改，即使 MySQL 在中途挂了，我们还可以根据 redo log 进行数据恢复。

    redo log 是顺序写的，写入速度很快。并且它记录的是物理修改，文件的体积很小，恢复速度很快。

事务的隔离级别有哪些?​

事务的隔离级别：==读未提交==、==读已提交==、==可重复读==和==串行化==，MySQL 默认是==可重复读==。

隔离级别脏读不可重复读幻读
Read uncommited 读未提交√√√
Read committed 读已提交×√√
Repeatable read 可重复读××√
Serializable 串行化×××
  • 事务的隔离级别越高，隔离性越好，数据越安全，但是性能越低。

并发事务问题：==脏读==、==不可重复读==、==幻读==。

问题描述
脏读一个事务读到了另一个事务还没有提交的数据。
不可重复读一个事务先后读取同一条数据，两次数据结果不同。
幻读一个事务按照条件查询数据时，没有对应行，但是在插入数据时，又发现这行数据存在，好像出现了“幻影”。

内连接与左外连接的区别是什么?​

内连接，左表和右表不符合条件的数据都不会在结果中。

左外连接，不符合条件时，左表的数据会在结果中，这些数据右表的结果为 null。

常用的存储引擎？​

常用的存储引擎：==InnoDB==、==MyISAM==、==Memory==。

InnoDB 与 MyISAM 的区别？​

InnoDB​

  • 兼顾高可靠性和高性能的通用存储引擎，在 MySQL5.5 之后默认。
  • 支持事务。
  • 支持行级锁，提高并发访问性能。
  • 支持外键 Foreign key 约束，保证数据的完整性和正确性。

MyISAM​

  • 早起的默认存储引擎。
  • 不支持事务，不支持外键。
  • 支持表锁，不支持行级锁。
  • 访问速度快。

存储引擎的选择​

  • 对事务的完整性要求比较高，并发情况下要求数据一致性，数据操作除了插入和查询，还有很多更新、删除操作，选 InnoDB。
  • 主要是查询和插入操作，很少更新和修改，对事务完整性、并发性要求不高，选 MyISAM。
  • 将所有数据保存在内存中，临时表，选择 Memory。

InnoDB 引擎的索引是什么数据结构?​

索引：索引是帮助 MySQL 高效获取数据的数据结构。

InnoDB 引擎的索引是==B+树==索引，是一种==多路搜索树==，所有数据都放在叶子节点，叶子节点形成双向链表。

  • 相对于==二叉树==，每个节点可以存放更多数据，树的高度会比二叉搜索树低，检索速度快。

  • 相对于==B 树==，它的非叶子节点不存放数据，在相同数据量情下，B+树更加矮壮。

  • 相对于 ==Hash 索引==，B+树支持范围匹配和排序操作。

索引的分类​

分类含义特点关键字
主键索引针对表中主键创建的索引默认自动创建，只能有一个PRIMARY
唯一索引避免同一个表中某数据列值重复可以有多个UNIQUE
常规索引快速定位特定数据可以有多个
全文索引全文索引查找的是文本中的关键字，不比较索引的值可以有多个FULLTEXT

按存储形式，分两种

分类含义特点
聚集索引将数据和索引放到一起，索引结构的叶子节点保存了整行的数据必须有，只且有一个
二级索引将数据和索引分开存储，索引结构的叶子节点关联的是对应的主键可以有多个

聚集索引选取规则：

  • 如果存在主键，主键就是聚集索引
  • 否则，如果存在唯一索引，使用第一个创建的唯一索引作为聚集索引。
  • 否则，InnoDB 会生成一个 rowid 作为隐藏的聚集索引。

什么是回表查询?​

回表查询：当我们使用索引查询数据时，查询出来的结果包含非索引字段的其他列，走的索引树叶子节点只能查到当前列值和主键 ID，所以需要根据 ID 再次查询一遍数据，得到 SQL 所有所需的字段列。

避免回表，可以使用覆盖索引。

覆盖索引：查的字段都刚好在叶子节点都存在，也就是这些字段都在一个索引里面。

如何查看 MySQL 的执行计划?​

explain SQL 语句;

索引失效的情况有哪些?​

  1. 联合索引时，没有满足最左前缀法则，查询没有从索引的最左列开始，或者跳过了某列，如果跳过后面的字段将会失效。
  2. 联合索引中使用 >、< 等进行范围查询，范围查询右侧的列失效。
  3. 在索引列上进行运算操作，索引失效。
  4. 在字符串类型使用时不加引号，索引失效。
  5. 头部模糊匹配会导致索引失效。
  6. or 连接分割的条件，or 前面的列有索引，or 后面的列没有索引，or 前面的索引会失效。
  7. 如果 MySQL 评估使用索引比全表扫描更慢，就不会使用索引。

SQL 优化​

  1. 插入优化：尽量批量插入数据，并且手动控制事务，主键顺序插入。大批量数据插入时用 load data local infile。
  2. 主键优化：主键长度尽量短，顺序自增。
  3. order by 优化：尽量使用索引，多字段满足最左前缀法则，file sort -> index sort。
  4. group by 优化：尽量使用索引，多字段满足最左前缀法则。
  5. limit 优化：分页查询时，尽量使用覆盖索引 + 子查询的方式来优化。通过子查询先查出记录的 ID，再通过 ID 查询需要的数据。
  6. count 优化：尽量使用 count(*)或 count(1)，而不是 count 字段。
  7. update 优化：根据主键字段和索引字段进行更新，因为 InnoDB 的行锁都是对索引字段加锁，不这样做会升级为表锁。

锁​

1. 全局锁​

锁住整个数据库实例，一般是做全库备份。

2. 表级锁​

每次操作锁住整张表。锁的粒度大，发生锁冲突的概率高，并发度低。

表级锁分为 表锁、元数据锁、意向锁。

表锁​

  • 表共享读锁：不会阻塞读，会阻塞写。lock tables 表名 read/write;

  • 表共享写锁：阻塞读又阻塞写。unlock tables 表名，或者客户端断开连接。

元数据锁（meta data lock）​

自动加锁，为了保证元数据的数据一致性，当表上有活动事务时，不允许对元数据写入，避免 DML 与 DDL 冲突。

意向锁​

避免在 DML 执行时，加的行锁与表锁冲突，引入了意向锁，使得表锁不用检查每行数据是否加锁，使用意向锁减少表锁的检查。

  • 意向共享锁（IS）：与表锁共享锁兼容，与表锁排它锁互斥。select ... lock in share mode;
  • 意向排它锁（IX）：与表锁共享锁兼容，与表锁排它锁互斥。insert、update、delete、select ... for update 添加。

3.行级锁​

行级锁：每次操作锁住对应行的数据，锁的粒度最小，发生锁冲突的概率最低，并发度最高。

行锁​

锁定单个行记录的锁，防止其他事务对此进行 update 和 delete。在 RC、RR 隔离级别都支持。

  • 共享锁（S）：允许一个事务去读一行，阻止其他事务获得相同数据集的排他锁。
  • 排他锁（X）：允许获取排他锁的事务更新数据，阻止其他事务获得相同数据集的共享锁和排他锁。

间隙锁​

锁定索引记录间隙，确保索引记录间隙不变，防止其他事务在这个间隙进行 insert，产生幻读。RR 隔离级别支持。

临键锁​

行锁和间隙锁组合，同事锁住数据，并锁住数据前面的间隙。在 RR 隔离级别下支持。

什么是 MVCC?​

当前读：读取的数据是记录的最新版本，读取时还要保证其他并发事务不能修改当前记录，会对读取的记录进行枷锁。

快照读：简单的 select 就是快照读，快照读读取的是数据的可见版本，又可能是历史数据，不加锁，是非阻塞读。

  • RC：每次 select 都生成一个快照读。
  • RR：每次开启事务第一个 select 语句才是快照读的地方。
  • Serializable：快照读会退化为当前读。

MVCC：多版本并发控制。指维护一个数据的多个版本，使得读写操作没有冲突。快照读提供了一个非阻塞读的功能。具体实现依赖三个隐式字段、undo log 日志、readview。

  • ==隐藏字段==：

    • DB_TRX_ID：最近修改事务 ID，记录插入这条记录或者最后一次修改该记录的事务 ID。
    • DB_ROLL_PTR：回滚指针，指向这条记录的上一个版本，用于配合 undo log ，指向上一个版本。
    • DB_ROW_ID：隐藏主键，如果表结构没有指定主键，将会生成该隐藏字段。

  • ==undo log==：回滚日志，在 insert、update、delete 的时候产生便于数据回滚的日志。

    当 insert 的时候，产生的 undo log 只在回滚时需要，在事务提交后，可被立即删除。

    而 update、delete 的时候，产生的 undo log 日志不仅在回滚时需要，在快照读时也需要，不会被立即删除。

    undo log 版本链：不同事务或相同事务对同一条记录进行修改，会导致该记录的 undo log 生成一条记录版本链表，链表的头部是最新的旧记录，链表尾部是最早的旧记录。

  • ==readview==：读视图是 快照读 SQL 执行时 MVCC 提供数据的依据，记录并且维护系统当前活跃事务的 ID。读视图中包含了四个核心字段：

    • m_ids：当前活跃的事务 ID 集合

    • min_trx_id：最小活跃事务 ID

    • max_trx_id：预分配事务 ID，当前最大事务 ID+1

    • creator_trx_id：readview 创建者的事务 ID

      • trx_id == creator_trx_id ，说明数据是当前事务修改

      • trx_id < min_trx_id ，说明数据已经提交了

      • trx_id > max_trx_id ，不可以访问，说明当前事务是在 raedview 生成后开启

      • min_trx_id <= trx_id <= max_trx_id ， 如果 trx_id 不在 m_ids 中是可以访问该版本的，成立说明数据已提交

      readview 生成时机

      • RC：事务中每次执行快照读生成 ReadView。
      • RR：在事务中第一次执行快照读的时候生成 ReadView，后续复用。

bin log​

binlog 用于记录数据库执行的写入性操作(不包括查询)信息，以二进制的形式保存在磁盘中。binlog 是 mysql的逻辑日志，并且由 Server 层进行记录，使用任何存储引擎的 mysql 数据库都会记录 binlog 日志。

在实际应用中， binlog 的主要使用场景有两个，分别是 主从复制 和 数据恢复 。

  1. 主从复制 ：在 Master 端开启 binlog ，然后将 binlog发送到各个 Slave 端， Slave 端重放 binlog 从而达到主从数据一致。
  2. 数据恢复 ：通过使用 mysqlbinlog 工具来恢复数据。

连接池​

Druid、C3P0、DBCP