Sorry, your browser cannot access this site
This page requires browser support (enable) JavaScript
Learn more >

MySQL学习

后端mySQL

发布于：Nov 26, 2023

次浏览

数据库概述

名称 全称 简称
数据库 存储数据的仓库，数据有组织的进行存储 DataBase
数据库管理系统 操纵和管理数据库的大型系统 DBMS(DataBase Management System)
SQL 关系型数据库操作语言，定义了一套操作关系型数据库统一标准 SQL(Structed Query Language)

SQL操作数据库管理系统，从而实现对数据库中的数据进行操作

MySQL安装

MySQL默认端口号为3306

MySQL在windows下的默认系统服务名称为mysql80

MySQL连接命令

1
mysql [-h 127.0.0.1] [-P 3306] -u root -p

事物

数据库事务

  1. 事务是一个逻辑单元需要执行的一系列操作，使得数据从一种状态变为另一种
  2. 处理原则：
    1. 所有操作都被提交，使得数据被永久修改
    2. 所有操作都被放弃，数据回滚到初始状态
  3. 事物具有四大特性（ACID）：
    1. 原子性A
    2. 一致性C
    3. 隔离性I
    4. 持久性D
1
2
3
4
5
6
7
8
9
10
11
12
--开启事务,手动开启的事务需要手动提交
START TRANSACTION
BEGIN

--查看事务提交方式，1为自动提交，0为手动提交
SELECT @@AUTOCOMMITE
SET @@AUTOCOMMITE = 0


--提交/回滚事务
COMMIT
ROLLBACK

MySQL中的事务默认为自动提交

数据库四大特性ACID

原子性（Automicity）

事务是一个不可分割的原子工作单元，要么全被执行，要么全被拒绝

  • 由undo log (回滚日志)保证

一致性（consistency）

事务执行后，数据由一个合法状态转换为另一个合法状态

隔离性（isolation）

并发事务对数据的修改对于其它并发事务而言是隔离的，不被其他事务干扰

  • 由MVCC（多版本并发控制）或锁机制保证

持久性（durability）

事务一旦被提交，其对数据库中数据的修改时永久性的，接下来其他操作以及数据库故障不应对其有任何影响

  • 由redo log (重做日志)保证

事务状态

事务总共包含5个状态：

  1. 活动的(activie)：事务对应的数据库操作正在执行过程中
  2. 部分提交的(partially committed)：活动的事务的最后一个操作完成，由于操作均发生在内存中，造成的影响并未刷新到硬盘中去
  3. 失败的(failed)：活动的事务或部分提交的事务遇到错误（数据库自身报错，操作系统报错，断电），或手动中止而无法执行时，停止当前事务的执行
  4. 提交的（committed）：当处于部分提交状态的事务将修改过的数据都同步到磁盘上时，该事务处于提交状态
  5. 中止的（aborted）：当处于失败状态的事务把已经修改过的事务中的操作还原到事务执行之前的状态，撤销失败任务对当前数据库造成的影响，即回滚完成后事务所处的状态

并行事务引起的问题

  • 为什么：
    • MySQL服务端允许同时连接多个客户端，这意味着MySQL会同时处理多个事务
  • 引起的问题：
    • 脏读：一个事务中读到其他事务还未提交的数据
    • 不可重复读：一个事务中前后两次读取的数据不一致，不可重复读的重点是update和delete，同样的条件，读取过的数据，再次读取出来发现值不一样
    • 幻读：一个事务前后读取的数据数量不一致，例如：
      • 事务1对表的Primary key=n进行查询，若查询结果为无，则增加一条key为n的数据
      • 同时，第二个事务直接向表中插入一条key=n的数据，并提交
      • 那么，如果不可重复读问题被结果，则事务1的查询结果仍未无，事务1执行插入操作时会发生主键重复错误
  • 问题严重程度：
    • 脏读 > 不可重复读 > 幻读

为什么加锁不能解决幻读

因为InnoDB是加的行锁，行锁无法锁住之前事务中没有存在的行。

事务隔离级别

SQL标准提出了四种隔离级来避免上述问题，隔离级别越高，效率越低

  • 读未提交：最低级隔离，在这一隔离级别下，三种问题都可能出现，但性能最高
    • 指一个事务还没提交时，它做的变更就能被其他事务看到；
  • 读提交：Oracle默认事务隔离级别，这一隔离级别下不会出现脏读问题
    • 指一个事务提交之后，它做的变更才能被其他事务看到；
  • 可重复读：MySQL默认事务隔离级别，这一隔离级别下不会出现脏读，不可重复读问题
    • 指一个事务执行过程中看到的数据，一直跟这个事务启动时看到的数据是一致的，MySQL InnoDB 引擎的默认隔离级别；
  • 串行化：这一隔离级别下，三种问题都不会出现，但性能最低
    • 实现机制：所有的读操作均为当前读，读加读锁 (S锁)，写加写锁 (X锁)。 Serializable隔离级别下，读写冲突，因此并发度急剧下降，在MySQL/InnoDB下不建议使用。
    • 会对记录加上读写锁，在多个事务对这条记录进行读写操作时，如果发生了读写冲突的时候，后访问的事务必须等前一个事务执行完成，才能继续执行；
隔离级别 脏读 不可重复读 幻读
Read Uncommitted √ √ √
Read Committed × √ √
Repeatable Read × × √
Serializable × × × 
1
2
3
4
5
--查看事务隔离级别
SELECT @@TRANSACTION_ISOLATION

--设置事务隔离级别
SET [ SESSION | GLOBAL ] TRANSACTION ISOLATION LEVEL {READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE}

隔离级别实现

  • 读未提交：直接读取最新数据
  • 读已提交，可重复读：通过Read View实现，Read View可理解为一个快照
    • 读已提交在每个操作执行之前生成一个Read View
    • 可重复读在开启事务时创建一个Read View，整个事务期间使用同一个Read View
  • 串行化：通过加读写锁实现

注意，执行「开始事务」命令，并不意味着启动了事务。在 MySQL 有两种开启事务的命令，分别是：

  • 第一种：begin/start transaction 命令；
  • 第二种：start transaction with consistent snapshot 命令；

这两种开启事务的命令，事务的启动时机是不同的：

  • 执行了 begin/start transaction 命令后，并不代表事务启动了。只有在执行这个命令后，执行了第一条 select 语句，才是事务真正启动的时机；
  • 执行了 start transaction with consistent snapshot 命令，就会马上启动事务。

Read View在MVCC中如何工作

MVCC是什么

MVCC-多版本并发控制，为了提高数据库读写效率，在有效解决读写冲突的同时，不用对数据加锁阻塞读写操作提高数据库并发读写性能

可以有效解决脏读、不可重复读、幻读等隔离问题，但不能解决更新丢失问题

MVCC的核心

  • undo log
    • insert undo log
      • 代表事务中insert数据而产生的undo log，只有事务回滚的时候需要，事务提交后即可被清除
    • update undo log
      • 代表事务中update或delete时产生的undo log，不仅事务回滚时需要，==快照读==时也同样需要，不能随便删除，只有在快照读或事件回滚不涉及该日志时，才会被purge线程统一清除
  • ReadView

当前读和快照读

  • 在RR隔离等级中，select为通过MVCC实现的快照读，即不会加锁
  • 在RR隔离等级中，select ... for update则是当前读，会为访问的数据加上next-key lock锁

ReadView

  • ReadView中包含四个字段
  • 聚簇索引（主键索引）记录中两个跟事务有关的隐藏列
四字段
  • m_ids：创建ReadView时，数据库中“活跃且未提交”的事务id列表
  • min_trx_id：创建ReadView时，当前数据库中“活跃且未提交”的事务id中的最小值，即m_ids中的最小值
  • max_trx_id：创建ReadView时，当前数据库中应该给下一个事务的id值，即最大事务id+1
  • creator_trx_id：创建该ReadView的事务的事务id
聚簇索引记录隐藏字段
  • DB_TRX_ID：存储最近一次对聚簇索引记录进行修改的事务ID
  • DB_ROLL_PTR：每当事务对聚簇索引进行修改时，旧版本会被记录在undo log中，该字段指向undo log中的该聚簇索引记录的旧版本记录

实际上聚簇索引还有另一个隐藏字段DB_ROW_ID：隐藏自增ID，如果数据没有主键，InnoDB会自动以DB_ROW_ID产生一个聚簇索引

一个事务去访问记录id为db_trx_id的记录时，除了自己的更新记录总是可见的以外，还需经过以下判断：

  1. if(db_trx_id < min_trx_id)则说明在当前事务创建ReadView之前，该事务已经提交，因此记录为可见
  2. if(db_trx_id >= max_trx_id)则说明当前事务创建ReadView之后，该事务才创建，还未提交，因此记录为不可见，则沿记录的DB_ROLL_PTR查找旧版本的记录，直到记录为可见为止
  3. if(db_trx_id >= min_trx_id && db_trx_id < max_trx_id)
    • if(m_ids.contains(db_trx_id))则说明当前事务创建ReadView时，该事务仍处于活跃状态（未提交），因此记录为不可见则沿记录的DB_ROLL_PTR查找旧版本的记录，直到记录为可见为止
    • else则说明当前事务创建ReadView时，该事务已经提交，因此记录为可见

这种通过版本链来控制并发事务访问同一记录时的行为就叫MVCC

可重复读是如何实现的

可重复读（RR）指一个事务的处理过程中看到的数据，一直和事务启动时看到的相同

实现原理：开启一个事物时即创建了一个ReadView，整个事物的过程中都使用这一个ReadView

读提交时如何实现的

读提交（RC）指一个事务只有提交后他对数据的修改才能被其他事务看见

实现原理：在事务的每次读取操作开始前生成一个新的ReadView

RR模式能解决幻读问题吗

虽然MySQL InnoDB的默认隔离等级RR是可重复读级别，但也可以在很大程度上避免幻读

  • 针对快照读，通过MVCC方式解决了幻读，其他事务插入的事务在当前事务中是不可见的
  • 针对当前读，通过next-key lock锁的方式解决了幻读，在执行select... for update时会加上next-key lock锁，其他事务在next-key lock范围内插入数据会被阻塞

幻读（Phantom Read）：同一个事物中同一查询在不同时间产生了不同结果集（不同行数）

  • 快照都的解决方案：
    • RR模式下的快照读，由于在事物开始时就创建了ReadView，即使过程中有其他事物插入了新事物，当前事务的ReadView没变，因此不会产生幻读
  • 当前都的解决方案：
    • 使用next-key lock（间隙锁+记忆锁）实现
    • 当前事务进行select...for update时，会对查询的范围加上next-key lock
    • 在此期间，其他事务想向next-key lock范围内插入新的数据时，会生成一个意向锁，进入等待状态，直到上锁事务提交

但RR并不能完全解决幻读的问题：

  • 场景一
    • 事务A创建事务，执行查询操作，查询id=5的数据（为空）
    • 事务B创建事务，执行插入操作，插入id=5的数据（将数据的DB_TRX_ID设为Bid）
    • 事务B提交
    • 事务A更新id=5的数据（进行当前读，获取最新数据，并将数据的DB_TRX_ID设为Aid）
    • 事务A执行查询id=5（此时由于DB_TRX_ID与自身相同，认为数据可以查看，结果能够查询到id=5的数据）
  • 场景二
    • 事务A创建事务，执行查询操作，查询id=5的数据（为空）
    • 事物B创建事物，执行插入操作，插入id=5的数据（将数据的DB_TRX_ID设为Bid）
    • 事物B提交
    • 事物A使用select...for update触发当前读，获取最新数据。结果能够查询到id=5的数据

因此如果想要尽量避免触发这种特殊情况，尽量在开启事务后立即进行select...for update操作，为数据上next-key lock锁，从而阻塞其他事务的插入操作

索引

定义

帮助MySQL高效获取数据的数据结构

索引分类

  • 按数据结构分：B+树索引，Hash索引，Full-Text索引
  • 按物理结构分：聚簇索引（主键索引），二级索引（辅助索引）
  • 按字段特性分：主键索引，唯一索引，普通索引，前缀索引
  • 按字段个数分：单列索引，联合索引

按数据结构分

MySQL常见存储引擎对索引的支持：

索引类型 InnoDB MyISAM Memory
B+树索引 Yes Yes Yes
Hash索引 No（不支持但内存结构中有一个自适应hash索引） No Yes
Full-Text索引 Yes（MySQL5.6之后） Yes No

B+Tree是MySQL存储引擎采用最多的索引类型。

创建表时InnoDB根据不同场景选择不同的列作为索引：

  • 如果有主键，则使用主键作为聚簇索引的索引键
  • 如果没有，则选择第一个不包含Null值的唯一列作为聚簇索引的索引键
  • 以上两个都不满足，则会使用隐含的自增id列作为聚簇索引的索引键

其余索引都属于辅助索引（二级索引，非聚簇索引），创建的主键索引和二级索引默认使用B+Tree索引

B+Tree 是一种多叉树，叶子节点才存放数据，非叶子节点只存放索引，而且每个节点里的数据是按主键顺序存放的。每一层父节点的索引值都会出现在下层子节点的索引值中，因此在叶子节点中，包括了所有的索引值信息，并且每一个叶子节点都有两个指针，分别指向下一个叶子节点和上一个叶子节点，形成一个双向链表。

主键索引和辅助索引的区别

B+tree中主键索引和辅助索引区别如下

  • 主键索引的B+tree中，叶子节点存储的是实际数据
  • 辅助索引的B+tree中，叶子节点存储的是主键值

回表：使用辅助索引查询数据时，先查询辅助索引B+tree得到主键后在通过查询主键B+tree来得到数据的过程称为回表

覆盖索引：使用辅助索引查询主键时，可直接在辅助索引B+tree中查到，则不需要再查询主键B+tree，这个过程称为覆盖索引

为什么使用B+树

B+Tree VS BTree

  1. 单节点数据量小：B+树非叶子节点并没有指向关键字具体信息的指针，内部节点更小，能够一次读入内存的量更多，减小了IO消耗
  2. 查询更稳定：只有叶子节点包含数据，因此任何查询都要走到叶子节点，而叶子节点位于同一层，因此每个查询效率相当
  3. 便与扫库：数据均为叶子节点连接的双向链表中，只需扫一遍叶子节点即可

B+ Tree VS 二叉树

B+Tree的搜索复杂度为O(logdN)其中d为节点允许的最大子节点个数，N为叶子节点数，由于二叉树只允许存在两个叶子节点，因此随着数据量的增加其高度会不断增加，导致查询效率下降，而B+Tree在实际应用中通常采用100以上的子节点个数，即使千万级的数据，也能将高度维持在3-4层

B+Tree VS Hash

B+Tree比Hash表更适合做范围查询

按物理结构分

聚簇索引（主键索引）和二级索引（辅助索引）

主要区别：

  • 聚簇索引的B+Tree中存放数据
  • 二级索引的B+Tree中存放主键

查询数据需要回表，查询主键只需覆盖索引

按字段特性分

  • 主键索引
    • 建立在主键字段上的索引，通常在建表时一起创建
    • 不能为空
    • 一张表最多一个
  • 唯一索引
    • 建立在UNIQUE字段上的索引
    • 不能重复
    • 一张表可以有多个
  • 普通索引
    • 建立在普通字段上的索引
    • 可以不为主键，可以重复
  • 前缀索引
    • 对字符串类型字段的前几个字符建立的索引
    • 可建立在字段类型为char，varchar，binary，varbinary上
    • 目的时为了减少索引占用的空间，提高查询效率

按字段个数分

  • 单列索引
    • 建立在单列上的索引
  • 联合索引
    • 建立在多列上的索引
    • 最左匹配原则：
      • 按照最左字段优先的方式进行排序和索引匹配
      • 当最左字段相同适，依次右移一个字段进行排序和索引匹配
    • 联合索引最左匹配的利用：
      • 只有索引中的key是有序排列的时才能利用索引
      • 在遇到范围查询时（>、<、between、like），会停止匹配，范围列可以用到联合索引，但是范围列后面的列无法用到联合索引
    • 联合索引可以优化一些排序操作，例如对where state = 1 order by time asc，可以换成对state和time构建联合索引，利用索引的有序性进行查询，以避免排序中的文件排序操作，提高效率
  • 索引下推优化（index condition）
    • 对于联合索引而言使用主键进行索引判断后，有两个先择：1. 直接在联合索引中判断联合索引的后续字段是否匹配；2. 回表到主键表中在判断后续字段是否匹配
    • MySQL5.6后引入索引下推优化，可以在联合索引便利的过程中，对联合索引包含的字段先做判断，过滤不满足条件的记录，减少回表次数
  • 索引区分度
    • $区分度 = \frac{distinct(column)}{count(*)}$即用字段中不同值的个数除以总行数
    • 建立联合索引时，应尽量将区分度大的字段放在左侧

什么时候需要/不需要索引

  • 优点
    • （高效查询）提高数据库检索效率，降低数据库IO的成本（创建索引的主要原因）
    • （唯一性）创建唯一索引，保证数据库中数据的唯一性
    • （连接）在实现数据的参考完整性方面，加速表与表之间的连接
    • （排序分组）索引有助于减少排序，分组等操作的CPU消耗
  • 缺点
    • （创建耗时）创建索引和维护索引需要花费额外的时间
    • （额外占空间）索引需要占据额外的磁盘空间
    • （更新耗时）降低数据库更新的效率，对数据库的增删改，都需要同时对索引进行维护

需要使用索引的场景

  • 具有唯一性限制的字段
  • 常用于作为WHERE查询的条件的字段，如果不是字段，可以考虑使用联合索引
  • 常需要GROUP BY或ORDER BY的字段

不适合使用索引的场景

  • 不需要用于WHERE查找或GROUP BY，ORDER BY的字段
  • 区分度低的字段（有大量重复值）
    • 因为MySQL存在查询优化器，当某一值的占比很高时，即忽略索引进行全表扫描
  • 经常更新的字段
  • 数据表数据很少时不需要创建索引

索引优化

前缀索引优化

对字符字段值的前 n个字符创建索引，减小索引字段的大小，从而增加一个索引页存储的索引值，有效提高索引的查询效率

局限性：

  • ORDER BY无法利用前缀索引
  • 前缀索引无法用于覆盖索引

覆盖索引优化

对一些字段建立二级索引或联合索引，从而减少回表的操作提高效率

主键索引最好自增

使用自增索引保证了数据按照主键递增顺序排列，可以使得新的数据插入时，直接插入到最后一条数据之后，从而避免了页分裂等数据挪动

页分裂：

  • 可能造成大量内存碎片
  • 使得索引结构不紧凑降低效率

索引最好设置为Not Null

主键为Null的缺点：

  • 主键中包含Null导致优化器在做索引选择的时候更加复杂，更加难以优化，例如count会忽略掉为null的值，导致计数不准确
  • 为Null的值没有意义，但同样需要占据1字节的空间

索引失效

  1. 使用左或者左右模糊匹配时，即like %xx或者like %xx%会引起索引失效
  2. 使用联合索引时，不符合向左匹配原则，会引起索引失效
  3. 查找时对索引进行了函数，数学，类型转换等操作时会引起索引失效
  4. 查找时or之前的条件是索引列而之后的不是，则会引起索引失效

锁

锁的分类

  1. 全局锁
  2. 表级锁
  3. 行级锁

全局锁

全局锁使用

1
2
3
4
--加锁
flush tables with read lock
--解锁
unlock tables

全局锁会锁定整个数据库

处于上锁状态的数据库无法进行:

  1. 对数据的增删更新操作insert,update,delete等语句
  2. 对数据表的增删更新操作alter table, drop table等语句

当会话断开时全局锁会自动解锁

使用场景

当需要对数据库进行全库逻辑备份时会用到，这样在数据库备份期间就不会因为数据或数据表的变化导致备份数据与预期结果不同的情况了

全局锁的缺点

对数据库加全局锁就意味着数据库在加锁期间只能进行读操作，无法进行更新，这将导致大量业务停滞

如何避免备份过程导致的业务停滞

如果数据库引擎支持RR（可重复度模式）可以利用RR模式而不是加锁来解决这个问题

再进行备份之前在RR模式下开启事务，那么整个备份的过程中都会采用事务开启时创建的Read View进行备份

同时其他事务也能继续进行业务而不影响备份事务的Read View

备份数据库常用工具

mysqldump，在使用时加上-single-transaction就可以在备份开始之前开启事务，但这一方法只能在支持RR隔离等级的数据库引擎上使用

MyISAM由于不支持事务，因此只能使用全局锁来完成备份

表级锁

  1. 表锁

    1. 可以通过如下命令对数据表进行加锁

    2. --加共享锁
lock tables my_table read
--加排他锁
lock tables my_table write

-- 解锁
unlock tables

    3. 表锁分为共享表锁（读锁）和排他表锁（写锁）两类

    4. 共享锁会阻塞接下来的写操作，排他锁会阻塞接下来的读写操作

    5. 需要注意的是即使是同一个事务，表锁也会对有序操作生效

    6. 使用InnoDB数据引擎的表不建议使用表锁，会影响效率，InnoDB提供了更为强大的行锁

  2. 元数据锁（MDL）

    1. MDL锁是什么
      1. 元数据锁不需要显示的上锁，当某一事物对数据表进行操作时会自动加上MDL，其目的是为了防止在用户对表中数据进行操作的过程中表的结构发生变化
      2. 对一张数据表进行CRUD操作时，会加MDL读锁，阻塞其他对数据表结构进行变更的操作的
      3. 对一张数据表做结构变更操作时，会加MDL写锁，阻塞其他CRUD操作
    2. MDL锁什么时候注销
      1. MDL只有当事物提交时才会注销
      2. 那么会导致一个问题：
        1. 如果一个事物对数据表进行读操作后，长时间不提交
        2. 那么当有一个事物想要对数据表结构进行变更时会被阻塞
        3. 后续其他线程想要对数据表进行CRUD操作时，同样会被阻塞，导致长时间的阻塞
      3. 造成这一现象的原因是申请DML锁的操作会形成一个队列，队列中写锁获取优先级高于读锁
      4. 因此在对数据表结构进行变更时，为了安全变更，需要先判断是否有长时间未提交的事务对表上了MDL读锁，如果有则考虑kill掉这个长时间任务然后再对表结构进行变更

  3. 意向锁

    1. 定义
      1. 在InnoDB引擎的表中对某些数据行（记录）加上共享锁之前，会对整个表加上一个意向共享锁
      2. 在InnoDB引擎的表中对某些数据行加（记录）上排他锁之前，会对整个表加上一个意向排他锁
    2. 作用
      1. 由于行锁和表锁是满足：读读共享，读写互斥，写写互斥的
      2. 因此当对表加上共享表锁的时候，需要便利表中的每一个条目，判断是否有数据行（记录）存在排他锁，这导致效率很慢
      3. 因此在加上表级意向锁后，直接查询表是否包含意向排他锁即可，提升了数据库效率
    3. 意向锁是表级的，不会和行级的共享锁和排他锁发生冲突，意向锁之间也不会发生冲突，但意向锁和共享表锁和排他表锁之间会发生冲突

  4. AUTO-INC锁

数据库三大范式

博客内容遵循 署名-非商业性使用-相同方式共享 4.0 国际 (CC BY-NC-SA 4.0) 协议

本文永久链接是：http://enderxiao.github.io/2023/11/26/MySQL学习/

更新于：Apr 7, 2024

数据库

mySQL

评论