0%

《Mysql》Mysql理论知识

MySQL 基础架构

Mysql 逻辑架构图主要分三层:

  • 第一层负责连接处理,授权认证,安全等等
  • 第二层负责编译并优化 SQL
  • 第三层是存储引擎。

存储引擎

存储引擎在 Mysql 架构中被视作插件,很多三方致力于开发属于自己的存储引擎,所以出现了众多开源的存储引擎,比较知名是官方实现的引擎 MyISAM 以及三方实现后来被官方收购的 InnoDB

下面是各种引擎的功能点

InnoDB 引擎内存结构

InnoDB 引擎中大量使用内存,为了缓冲 cpu 与磁盘的速率不对等性。checkpoint 下刷新内存中的更改到磁盘

InnoDB 引擎逻辑存储结构

从 InnoDB 存储引擎的逻辑存储结构看,所有数据都被逻辑地存放在一个空间中,称之为表空间(tablespace)。表空间又由段(segment)、区(extent)、页(page)组成

图中的 Leaf nodesegment 就是数据段,存放具体数据,Non-leaf node segment 是索引段,存放索引数据

区是由连续页组成的空间,在任何情况下每个区的大小都为 1MB

为了保证区中页的连续性,InnoDB 存储引擎一次从磁盘申请 4~5 个区(这 4~5 个区就是物理连续的,区中的页是物理连续的,页中的行也是物理连续的)。在默认情况下,InnoDB 存储引擎页的大小为 16KB,即一个区中一共有 64 个连续的页

每个页被加载到内存后变成了一个 B+TREE 的节点,数据段中的数据页变成聚簇索引的叶子节点,索引段中的索引页变成聚簇索引的非叶子结点

注意:页也经常被称为块。数据页=磁盘块

下图是数据页的存储结构

表空间在物理上体现为一个 idb 文件,Mysql 可以通过 innodb_file_per_table 配置成每个表一个表空间

但是会有一个共享表空间,由所有表共享,会保存 回滚(undo)信息、插入缓冲索引页、系统事务信息、二次写缓冲(Double write buffer)等信息

聚簇索引中,一个页中的行是按照主键顺序存放的,但是相邻节点所在的页却不一定在物理上连续

聚簇索引中,一个页中的行是按照组合索引的顺序存放的,相邻节点所在的页不一定在物理上连续

InnoDB 的插入缓冲(性能提升)

在进行插入操作时,数据页中每条记录的存放是按主键进行顺序连续存放的(因为是一个页一个页操作磁盘的。而磁盘中数据页不是按顺序连续存放的),所以自增 id 作为主键能够有很好的插入性能(磁盘的顺序写,而不是随机写)

但是对于非聚集索引叶子节点的插入不一定是顺序的了,这时就需要离散地访问非聚集索引页,由于随机读取的存在而导致了插入操作性能下降

当然,比如 created_at 这种字段作为非聚集索引的时候,对其叶子节点的插入还是顺序的,属于磁盘的顺序写

基于上面的问题,引入了插入缓冲

对于非聚集索引的插入或更新操作,不是每一次直接插入到索引页中,而是先判断插入的非聚集索引页是否在缓冲池中,若在,则直接插入(这是插入内存中,会持续落盘);

若不在,则先放入到一个 Insert Buffer 对象(就是一个内存缓存)中,如果不在还去插入的话就需要随机读取磁盘,将非聚集索引页加载到缓冲池,每次都这样的话,会导致大量随机读写(缓冲池中只会保留热点索引页,同一个索引页可能会被反复从磁盘加载到缓冲池),就会有上面的问题

Insert Buffer 对象(也是一颗 B+tree 树,全局唯一)在下面条件下,会被批量处理,处于同一个索引页的插入可以被一次处理掉,在大量插入的场景下能提供性能

  1. 索引页被读取到缓冲池中时,这个页中的记录顺带会被处理
  2. Insert Buffer Bitmap 页用来追踪每个辅助索引页的可用空间,并至少有 1/32 页的空间。若插入辅助索引记录时检测到插入记录后可用空间会小于 1/32 页,则会强制进行一个合并操作,即强制读取辅助索引页,将 Insert Buffer B+ 树中该页的记录及待插入的记录插入到辅助索引页中。防止这个页数据溢出(这个页实际数据加上 Insert Buffer 中数据已经满了,但是因为 Insert Buffer 中数据没有写进去,这个页被认为还有空间,导致又往这个页里塞)
  3. Master Thread 线程中每秒或每 10 秒会进行一次 Merge Insert Buffer 的操作

当然,应用插入缓存的功能有两个条件:

  1. 索引是辅助索引。主键索引 B+tree 叶子结点的插入是顺序写的,没有这个问题
  2. 索引不是唯一的。如果索引是唯一的,插入缓存的设计不能保证唯一性,因为插入缓存是不会检查唯一性的,如果检查的话,就避免不了随机读写,就失去了插入缓存的作用,解决不了随机读写的问题。

插入缓冲在后来的 InnoDB 版本中,被进化成增删改查缓冲,不仅仅是对插入进行缓冲

InnoDB 两次写特性(数据页的可靠性)

缓冲池中的脏页(缓冲池中页发生了改变,与磁盘中不一致了,需要同步到磁盘的页)在刷新到磁盘时,并不是简单的写入磁盘,然后调用 fsync

因为磁盘中数据页不是按顺序连续存放的,多个脏页刷新到磁盘会进行随机读写,耗时较长,如果刷新过程中数据库宕机了,比如有部分页没有更新到磁盘,这种情况可以通过 redo log 进行恢复。

但是如果是某个 16k 的页只写了 4k,另外 12k 丢失了,这个页就损坏了,里面的数据可能已经乱套了。而 redo log 记录的是这个页的某个偏移写了什么数据,现在这个页都损坏了,按照 redo log 恢复后这个页还是损坏的,数据还是乱套的

所以需要引入页的备份,故引入了两次写的功能,来保障数据页的可靠性

做法就是,在脏页刷新到磁盘的过程中引入一个 2M 的内存缓存 doublewrite buffer,脏页全部先 copy 到这个缓存中,然后 2M 缓存分成两次,每次 1M 写入到共享表空间(磁盘空间)中的 doublewrite 页(也是 2M 磁盘空间)中并调用 fsync,因为 doublewrite 页是连续的磁盘空间,所以这个写过程的顺序写,开销不大。

doublewrite 页写完之后,再将 doublewrite buffer 中的脏页写入各个表空间文件(最终的磁盘空间)中,此时的写入则是离散的

doublewrite 页中的数据作为了一个 2M 的副本,当发生了宕机,会先从 doublewrite 页中将副本拷贝到磁盘恢复这个页,然后应用 redo log 做恢复

InnoDB 事务

  • 事务中的 update 执行后只会在内存中改变,不会真正的写入数据库,commit 了才会进数据库
  • mysql 的自动提交功能,只限于没有开启事务,如果开启了事务,则不会自动提交,commit 后才能提交
  • 同一连接下,begin 和 start transaction 一样,相当于两条命令 commit;begin; ,所以一个连接同时只能有一个事务
  • 每个连接中的事务是隔离的,这个连接中的 commit 不会影响另一个连接中的事务
  • 事务中,insert 在提交之前,只有本连接才能读到未提交数据,其他连接读不到,只能等提交了才能读到,如果读到了就是脏读
  • mysql 在默认隔离级别下
    • 事务 a 开启 -> 事务 b 开启 -> 事务 b 读取数据 A(数据 A 已经存在) -> 事务 a 更新或删除数据 A -> 事务 A 提交 -> 事务 b 读取数据 A(不会读取到更改)-> 事务 b 提交或回滚 -> 再读取数据 A(可以读取到更改),这种现象就是不可重复读
    • 事务 a 开启 -> 事务 b 开启 -> 事务 b 读取数据 A(数据 A 不存在)-> 事务 a 插入数据 A -> 事务 A 提交 -> 事务 b 读取数据 A(不会读取到插入的数据)-> 事务 b 提交或回滚 -> 再读取数据 A(可以读取到插入),这就是幻读现象
  • 数据库更新的并发问题的几个解决方案:
    • 以悲观锁的方式,insert 举例,将 select 和 insert 包成事务,且将 select 使用 for update 加锁(命中索引的话,没查到数据则不产生锁,没命中索引就会加表锁),使事务具有排他性
    • 使用表的唯一索引,使两个插入只有一个会成功
    • 使用分布式锁(redis 或 zookeeper 等等)
    • 跟 MVCC 机制一样使用乐观锁

4 大特性(ACID):

  1. 原子性: 事务作为一个整体被执行,包含在其中的对数据库的操作要么全部被执行,要么都不执行。使用 undo log 来实现的,如果事务执行过程中出错或者用户执行了 rollback,系统通过 undo log 日志返回事务开始的状态
  2. 一致性: 指在事务开始之前和事务结束以后,数据不会被破坏,假如 A 账户给B账户转 10 块钱,不管成功与否,A 和 B 的总金额是不变的。通过回滚、恢复,以及并发情况下的隔离性,从而实现一致性。
  3. 隔离性: 多个事务并发访问时,事务之间是相互隔离的,即一个事务不影响其它事务运行效果。通过锁以及 MVCC,使事务相互隔离开
  4. 持久性: 表示事务完成以后,该事务对数据库所作的操作更改,将持久地保存在数据库之中。使用 redo log 来实现,只要 redo log 日志持久化了,当系统崩溃,即可通过 redo log 把数据恢复

索引

InnoDB 存储引擎使用的 B+Tree,每个表多个 B+Tree 树(有几个索引就有几颗树),索引默认使用的也是 B+Tree(还有一种就是 Hash 索引)

InnoDB 存储引擎中页的大小为 16KB,一般表的主键类型为 INT(占用 4 个字节)或 BIGINT(占用 8 个字节),指针类型也一般为 4 或 8 个字节,也就是说一个页(B+Tree 中的一个节点)中大概存储 16KB/(8B+8B)=1K 个键值(因为是估值,为方便计算,这里的 K 取值为 10^3)。也就是说一个深度为 3 的 B+Tree 索引可以维护 10^3 * 10^3 * 10^3 = 10 亿条记录。

实际情况中每个节点可能没有填充满,因此在数据库中,B+Tree 的高度一般都在 24 层。mysql 的 InnoDB 存储引擎在设计时是将根节点常驻内存的,也就是说查找某一键值的行记录时最多只需要 13 次磁盘 I/O 操作。

树越矮胖,磁盘的 IO 次数就越少,效率就越高。矮胖程度:B+tree > B-tree > 平衡二叉树

Mysql 的 B+tree 中所有的叶子结点(叶子结点上既有索引 key 也有值)按顺序形成双向链表,这样的话才能查找到一个范围的值

比如触发索引的查询 where a > 4 and a < 10,先定位到大于 4 的第一个叶子结点,然后根据双向链表往后查找,直到找到 >=10 的叶子结点终止,将这些叶子结点上的 pk 数组汇集起来,再去回表查询

聚集索引(clustered index),也就是主键

叶子结点存放的是一整行记录的信息

辅助索引(secondary index),为表加一个索引就是新建一个 B+Tree 树

叶子结点存放的不是一整行记录的信息,而是聚集索引的 key 值(也就是主键值)的数组

当通过辅助索引来寻找数据时,InnoDB 存储引擎会搜索辅助索引树得到主键索引的 key,然后再通过搜索聚集索引树来找到一个完整的行记录。

联合辅助索引

当我们的 SQL 语言可以应用到索引的时候,比如

1
select * from T1 where b = 12 and c = 14 and d = 3;
  1. 存储引擎首先从根节点(一般常驻内存)开始查找,第一个索引的第一个索引列为 1, 12 大于 1,第二个索引的第一个索引列为 56, 12 小于 56,于是从这俩索引的中间读到下一个节点的磁盘文件地址,从磁盘上 Load 这个节点,通常伴随一次磁盘 IO,然后在内存里去查找
  2. 当 Load 叶子节点的第二个节点时又是一次磁盘 IO,比较第一个元素,b=12, c=14, d=3完全符合,于是找到该索引下的 data 元素即 ID 值
  3. 从主键索引树上找到最终数据

从上面的查询步骤可以看出:
创建的 (b,c,d) 索引,相当于创建了 (b)、(b、c)(b、c、d)三个索引,也就是最左匹配原则

下面案例是使用索引情况

1
2
3
4
5
6
select * from T1 where b = 12 and c = 14 and d = 3;-- 全值索引匹配 三列都用到
select * from T1 where b = 12 and c = 14 and e = 'xml';-- 应用到两列索引
select * from T1 where b = 12 and e = 'xml';-- 应用到一列索引
select * from T1 where b = 12 and c >= 14 and e = 'xml';-- 应用到一列索引及索引条件下推优化
select * from T1 where b = 12 and d = 3;-- 应用到一列索引 因为不能跨列使用索引 没有c列 连不上
select * from T1 where c = 14 and d = 3;-- 无法应用索引。d在最后一位,很明显无法查找(这就是最左前缀原则)

最左前缀原则:在创建多列索引时,要根据业务需求,where 子句中使用最频繁的一列放在最左边。

覆盖索引(查询列要被所建的索引覆盖)

覆盖索引是指:查询的数据可以直接从辅助索引树中找到,不需要再根据主键去主键索引树中查找记录(称为回表查询),大大节约了时间。这样的索引就是覆盖索引,是辅助索引的一种

还是上面的案例:

1
select c, d from T1 where b = 12  // 这里就用到了覆盖索引,c和d本来就在辅助索引树中

explain 结果的 Extra 字段中,如果有 Using index,表明用到了覆盖索引

select count(*) 这种查询,没有查询任何数据,如果不带 where 条件,那么只要存在辅助索引,就会使用那个索引,因为辅助索引树远比主键索引树瘦小,可以减少 IO 查询,并且能得到想要的结果

索引条件下推 ICP ( Index Condition Pushdown )

索引条件下推是指:在联合索引下,当没有用到全部列时,没有用到的列的判断直接放在辅助索引树判断了,不放到主键索引树判断

1
select * from T1 where b = 12 and c = 14 and d <> 3;

b = 12 and c = 14 会用到索引,但是 d <> 3 无法用到,但是因为 d 是联合索引中的最右边的字段,符合最左前缀原则(否则是无法使用索引条件下推的)

可以在辅助索引树中查出符合 b = 12 and c = 14 的记录后,再对这些记录判断 d <> 3 条件,得出最后的所有主键。这就是索引条件下推

如果使用 SET optimizer_switch = ‘index_condition_pushdown=off’; 关闭索引条件下推优化,那么过程将是这样的:

  1. 在辅助索引树中查出符合 b = 12 and c = 14 的记录(所有主键值)
  2. 根据所有主键值从主键索引树中找到所有记录
  3. 使用条件 d <> 3 依次判断这些记录

执行计划中 Extra 有 Using index condition 的提示,表明使用了 ICP

前缀索引

有时候需要索引很长的字符列,这会让索引变得大且慢。

通常可以索引开始的部分字符,这样可以大大节约索引空间,从而提高索引效率。

但这样也会降低索引的选择性。索引的选择性是指不重复的索引值(也称为基数,cardinality)和数据表的记录总数的比值

索引的选择性越高则查询效率越高,因为选择性高的索引可以让 MySQL 在查找时过滤掉更多的行。

唯一索引的选择性是 1,这是最好的索引选择性,性能也是最好的。

如何选择前缀索引的长度:

1
2
select count(distinct city) / count(*) from city_demo; // 值记为 a
select count(distinct left(city,3))/count(*) as sel3,count(distinct left(city,4))/count(*) as sel4,count(distinct left(city,5))/count(*) as sel5 from city_demo; // 查看 3、4、5 的值跟 a 相比谁最接近,4 最接近,长度就选择 4

查询的数据量很大时

如果查询的数据量很大,即使存在对应的辅助索引,优化器可能也会选择使用主键索引。因为主键索引是顺序读写,而回表查询时是随机读写,查询的数据量小时,辅助索引是有用的,但如果很大,随机读写的成本已经大过了辅助索引的优势,那么优化器会选择全表扫描

强制使用索引

有时候优化器错误的选择了索引(很少很少发生),可以使用 force index 强制使用索引,如 select * from t force index(a) where a = 1 and b = 2

这样的话优化器不进行分析,而是直接使用指定的索引,顺带节省了优化分析耗时

MMR(Multi-Range Read) 优化

MMR 优化是指:通过辅助索引树查到主键值后,先放入缓存,缓存中进行排序,然后按顺序去主键索引树查找记录,将随机读写变成了顺序读写,大大提高效率

执行计划中 Extra 有 Using MMR 的提示,表明使用了 MMR

自适应哈希索引特性

缓冲池中所有的页都是放入 hash 表 Map 中的,key 是索引树中节点的值(主键索引树就是主键值),value 是数据页的位置(树节点是由一个值以及一个指向数据页的指针组成)

InnoDB 存储引擎会自动根据访问的频率和模式来自动地为某些热点页建立哈希索引

比如 where a=XXX 连续访问了 100 次,a 就会被自动创建哈希索引,以提高查询效率

哈希索引就是一个 Map,不是 B+tree,它能够通过值直接得到所在的页,复杂度是 O(1),效率非常高,但是只能用于等值查询

InnoDB 支持表锁和行锁,使用索引作为检索条件修改数据时采用行锁,否则采用表锁

事务是并发执行的(除了 serializable 隔离级别)

行锁分为两种类型:

  1. 共享行锁 S(读锁)
  2. 排它行锁 X(写锁)

按照行锁的颗粒度分为三种:

  1. record lock (对找到的记录加锁)
  2. gap lock (对间隙加锁)
  3. next-key lock (前两种的组合,既对记录加锁,也对间隙加锁)

在 mysql 默认隔离级别 repeatable read 下:

语句 申请锁
select 一致性非锁定读(就是快照读),不请求锁
update 排它锁 X(next-key)
insert 排它锁 X
replace 排它锁 X(next-key)
delete 排它锁 X(next-key)
select .. lock in share mode 一致性锁定读(本地读),共享锁 S(next-key)
select .. for update 一致性锁定读(本地读),排它锁 X(next-key)
  • 锁是对索引加锁,不是对记录加锁,也不是对表加锁
  • InnoDB 引擎在加锁的时候,只有通过索引进行检索的时候才会使用行级锁,否则会使用表级锁,因为要读全表数据。
  • 锁是实例级别的,不是数据库级别的,也不是事务级别的
  • 事务也是实例级别的,不是数据库级别,一个事务中可以查询不同数据库的数据
  • 同一个连接下,不会锁住自己,因为一个连接同时只能有一个事务
  • 持有锁的连接如果断掉,mysql 会自动释放这个锁,给其他连接获取锁,从而避免死锁

next-key 锁

假设一个表中 test 是索引

在精确匹配的情况下,比如 select … where test = 4 for update

  1. 如果表中存在 id=4 的记录,比如 test in (1, 4, 7, 8)
    1. 如果触发的是唯一索引,那么行锁就是 record lock(next-key lock 降级为 record lock),只会锁住这一行。因为唯一索引本身就不允许再插入一条 test=4 的记录,所以不可能有幻读,自然没必要上 next-key lock
    2. 如果触发的不是唯一索引,那么行锁就是 next-key lock,会锁住 (1,4] 以及 (4,7],也就是 (1,7]。是为了防止再插入一条 test=4 的记录,造成幻读。
  2. 如果表中不存在 id=4 的记录,比如 test in (1, 5, 7, 8),那么行锁就是 next-key lock,会锁住(1,5)范围的记录。是为了防止再插入一条 test=4 的记录,造成幻读。

如果是 select … where test >= 4 for update,那么行锁就是 next-key lock,会锁住 [4,无穷大)范围的记录。是为了防止插入一条 test>=4 的记录,造成幻读。

总之 next-key 锁的作用就是防止一部分幻读问题,Repeatable Read 级别下就会使用 next-key 锁

悲观锁

就是利用事务加锁。Mysql 中所有锁都是悲观锁,而 MVCC 多版本并发控制是乐观锁的一种实现

类似于 Golang 中多个协程对同一个全局变量 a+1,使用 mutex.Lock 锁住 a+1 的动作

乐观锁

乐观锁是一种思想,它其实并不是一种真正的锁,它会先尝试对资源进行修改,在写回时判断资源是否进行了改变,如果没有发生改变就会写回,否则就会进行重试,在整个的执行过程中其实都没有对数据进行加锁;

不加锁,表中加入一个版本号的字段,先查出这条记录的信息以及版本号

更新的时候使用

1
update test set remain=remain-1, version=<查出来的版本号>+1 where version=<查出来的版本号>

也可以不使用 version 字段

1
update test set remain=remain-1 where remain=<查出来的remain>

update 语句具有原子性,这里就类似于 Golang 中多个协程对同一个全局变量 a+1,使用 atomic.AddInt32 原子操作的方法

where version=<查出来的版本号> 的效果就是将 a+=1 变成了 atomic.AddInt32(a, 1),让这次改变具有原子性(将 where 查询和 update 更改两个动作原子化,防止重入)

很明显利用 mutex.Lock 锁住的话,其他地方读取 a 也会阻塞(Mysql 利用 MVCC 使用乐观锁的机制使得记录被排他锁锁住的时候,还是可以 select)

而且 atomic.AddInt32 原子操作比 mutex.Lock 锁性能强很多,毕竟 mutex.Lock 锁的上锁以及释放都要成本

看一个场景:秒杀 1000 部手机,每人只能秒杀一部

新建两个表,一个库存表 remain(就一个自增 id 字段和一个库存字段 remain),一个秒杀成功用户表 bingo(就一个自增 id 字段和一个 user_id 字段)

先分析可能出现的问题:总共就 1000 部手机,上万人秒杀,请求并发量非常高,会不会导致卖出去了 1000+ 步手机,同一个用户会不会秒杀到两部

方法如下:

  1. begin
  2. select id from bingo where user_id = <用户id> for update // user_id 不是索引,没查到会锁表,其他请求都会阻塞等待,而且其他地方对这张表的加写锁请求都会阻塞。这里就是所有用户公用一把锁。如果 user_id 是索引,会加写锁,同一个用户不可能同时进入
  3. 如果 id 查到了,说明这个用户已经秒杀到了,直接 rollback 然后返回已经秒杀成功。如果没有查到,则往下走
  4. select remain from remain where id = 1;
  5. 如果 remain<=0,则 rollback 然后返回已售罄。否则往下走
  6. update remain set remain = remain - 1 where id = 1;
  7. insert bingo (user_id) values (<用户 id >)
  8. commit

这种方法问题很明显

  1. user_id 不是索引不是索引的时候,锁粒度太大,所有用户公用了一把锁,同时只能有一个用户能进入这段逻辑,而且其他用户在其他地方对这张表的改动也都要等待这把锁释放,大大降低了系统的并发能力

改进 1:利用 redis 分布式锁,缩小锁的粒度,改成每个用户一把锁。redis 中锁的 key 设置成 lock_ 就可以实现。redis.setnx(lock_),过期设置为 20s 并自动续锁(防止锁没释放就过期了导致同一用户重入)。这是一个较小颗粒的悲观锁
改进 2:给 user_id 加上索引

每个用户一把锁的话,不同用户可以重入这段逻辑,并发度提高了

  1. 在减库存的时候,库存是所有用户的共同资源,同时修改会导致一些问题

比如用户 a 查到库存是 1,这时候 b 也查到库存是 1,a 和 b 都发现库存够,然后 a 库存减 1 变成 0 ,b 也库存减 1 ,最后库存就是 -1 了

所以上面要使用乐观锁:第 6 步改成 update remain set remain = remain - 1 where id = 1 and remain = <上面查到的remain>;

用户 b 会发现库存减 1 失败,进入重试流程(重新查库存然后减库存,直到成功),但是会发现库存是 0 了,直接返回 b 已售罄

当然,where remain = <上面查到的remain> 的乐观锁方式也可以使用一个所有用户公用的分布式锁代替,锁住查询库存到更新库存这一段即可,不过这就成了悲观锁了,会降低性能,而且加锁释放锁都是有成本的

当然,也可以不使用 remain 作为乐观锁的 key ,新增一个 version 字段也可以,但是这里没必要

  1. 但这样还是有个问题:会导致大量用户进入重试流程,数据库压力剧增。所以一般如果减库存失败的话,直接返回秒杀失败请重试

所以最终的实现是:

  1. select id from bingo where user_id = <用户id> for update ( user_id 是索引)
  2. 如果id查到了,说明这个用户已经秒杀到了,直接返回已经秒杀成功。如果没有查到,则往下走
  3. select remain from remain where id = 1;
  4. 如果remain<=0,则返回已售罄。否则往下走
  5. begin
  6. update remain set remain = remain - 1 where id = 1 and remain = <上面查到的remain>;
  7. 如果发现更新失败,直接返回秒杀失败
  8. insert bingo (user_id) values (<用户id>)
  9. commit

InnoDB 的 4 种隔离级别:

Read Uncommited 读未提交

可以读取未提交记录,造成脏读。

事务 a 更新记录 1 ,未提交,此时事务 b 能读到 a 更新后的记录 1 ,这种现象叫脏读(读到了其他事务还没提交的脏数据)

Read Committed (RC) 读已提交

解决脏读问题(写锁和 MVCC 快照读解决的)

存在幻读、不可重复读现象,不存在脏读。

事务 a 更新或删除记录 1 ,事务 b 读取记录 1 ,事务 a 提交,事务 b 再次读记录 1 ,发现记录 1 变了,这种现象叫不可重复读(两次读的结果不一样,第二次读到了其他事务的更改结果)

Repeatable Read (RR) 可重复读(默认隔离级别)

解决不可重复读问题( MVCC 解决的。快照读和本地读 )

存在幻读,不存在脏读、不可重复读现象

事务 a 插入记录 1 ,事务 b 读取所有记录发现没有 1 ,事务 a 提交,事务 b 再次读所有记录,依然没有这条记录 1 ,但是 b 插入记录 1 的时候发现会失败了,好像前面的查询是幻觉一样,这种现象叫幻读

使用 next key 锁可以避免部分幻读现象,但可重复读级别下,是依然存在幻读的,例如

事务1 事务2
begin;
begin;
select * from people where id = 1;
update people set name = “bb” where id = 1;
select * from people where id = 1;(这里读不出来变更,能读出来就是脏读现象了)
commit;
select * from people where id = 1;(这里读不出来变更,能读出来就是不可重复读现象了)
update people set name = “cc” where name = “aa”;(对于事务1来讲,这里理应更改成功的,因为事务1看到的就是name=aa,结果却是更新不成功,发生幻读,看到的name=aa是幻觉)
commit;

Serializable 串行化

解决幻读问题

Serializable 隔离级别下,事务间串行执行,不存在脏读、不可重复读、幻读,同时并发度急剧下降,不建议使用。

该级别下读写串行化,且所有的 select 语句后都自动加上 lock in share mode ,即使用了共享锁。因此在该隔离级别下,使用的是当前读,而不是快照读。

SQL 性能优化

SQL 性能优化一般是

  1. 通过慢查询日志定位那些执行效率较低的 sql 语句
  2. explain 分析低效 sql 的执行计划

核心是减少随机读写的次数

show profiles

查看当前连接使用的所有语句的执行时间

功能默认是关闭的,通过下面语句打开(打开当前连接的 profile 功能,其他连接不受影响)

1
set profiling=1;

下面语句查看连接 id :

1
select connection_id();

慢查询日志

查看功能开启状态

1
2
3
4
show variables like '%slow_query_log%';

# slow_query_log ON
# slow_query_log_file /mysql/logs/slow.log

查看或修改慢查询阈值(单位s):

1
2
3
4
5
show variables like '%long_query_time%';

# long_query_time 10.000000

set global long_query_time=4;

使用 select sleep(5); 就可以测试阈值修改效果

expain 执行计划

发现慢查询后就可以使用 explain 分析慢的原因,一般都是索引的使用问题

下面看 explain 结果各个字段的含义

type

表示 MySQL 在表中找到所需行的方式,又称“访问类型”。

常用的类型有:

  • ALL:Full Table Scan, MySQL 将遍历全表以找到匹配的行
  • index: Full Index Scan,index 与 ALL 区别为 index 类型只遍历索引树
  • range: 只检索给定范围的行,使用一个索引来选择行
  • ref: 表示上述表的连接匹配条件,即哪些列或常量被用于查找索引列上的值
  • eq_ref: 类似 ref,区别就在使用的索引是唯一索引,对于每个索引键值,表中只有一条记录匹配,简单来说,就是多表连接中使用 primary key 或者 unique key 作为关联条件
  • const、system: 当 MySQL 对查询某部分进行优化,并转换为一个常量时,使用这些类型访问。如将主键置于 where 列表中,MySQL 就能将该查询转换为一个常量, system 是 const 类型的特例,当查询的表只有一行的情况下,使用 system
  • NULL: MySQL 在优化过程中分解语句,执行时甚至不用访问表或索引,例如从一个索引列里选取最小值可以通过单独索引查找完成。

possible_keys

指出 MySQL 能使用哪些索引在表中找到记录,查询涉及到的字段上若存在索引,则该索引将被列出,但不一定被查询使用

该列完全独立于 EXPLAIN 输出所示的表的次序。这意味着在 possible_keys 中的某些键实际上不能按生成的表次序使用。
如果该列是 NULL ,则没有相关的索引。在这种情况下,可以通过检查 WHERE 子句看是否它引用某些列或适合索引的列来提高你的查询性能。如果是这样,创造一个适当的索引并且再次用 EXPLAIN 检查查询

Key

key 列显示 MySQL 实际决定使用的索引

如果没有选择索引,键是 NULL 。要想强制 MySQL 使用或忽视 possible_keys 列中的索引,在查询中使用 FORCE INDEX 、USE INDEX 或者 IGNORE INDEX。

key_len

表示索引中使用的字节数,可通过该列计算查询中使用的索引的长度( key_len 显示的值为索引字段的最大可能长度,并非实际使用长度,即 key_len 是根据表定义计算而得,不是通过表内检索出的)

不损失精确性的情况下,长度越短越好

ref

表示上述表的连接匹配条件,即哪些列或常量被用于查找索引列上的值

rows

表示 MySQL 根据表统计信息及索引选用情况,估算的找到所需的记录所需要读取的行数

Extra

该列包含 MySQL 解决查询的详细信息,有以下几种情况:

  • Using where: 列数据是从仅仅使用了索引中的信息而没有读取实际的行动的表返回的,这发生在对表的全部的请求列都是同一个索引的部分的时候,表示 mysql 服务器将在存储引擎检索行后再进行过滤
  • Using temporary:表示 MySQL 需要使用临时表来存储结果集,常见于排序和分组查询
  • Using filesort:MySQL 中无法利用索引完成的排序操作称为“文件排序”
  • Using join buffer:改值强调了在获取连接条件时没有使用索引,并且需要连接缓冲区来存储中间结果。如果出现了这个值,那应该注意,根据查询的具体情况可能需要添加索引来改进能。
  • Impossible where:这个值强调了 where 语句会导致没有符合条件的行。
  • Select tables optimized away:这个值意味着仅通过使用索引,优化器可能仅从聚合函数结果中返回一行
  • Using index:表明用到了覆盖索引

主从复制

原理

  1. 主库的更新事件 (update、insert、delete) 被写到 binlog
  2. 从库发起连接,连接到主库。
  3. 此时主库创建一个 binlog dump thread,把 binlog 的内容发送到从库。
  4. 从库启动之后,创建一个 I/O 线程,读取主库传过来的 binlog 内容并写入到 relay log
  5. 还会创建一个 SQL 线程,从 relay log 里面读取内容,从 Exec_Master_Log_Pos 位置开始执行读取到的更新事件,将更新内容写入到 slave 的 db

主从同步延迟

延迟主要是发生在从服务器执行 relay log 的时候,因为从服务器执行 relay log 的线程仅有一个,如果某个 sql 执行时间很长,就会导致不一致的数据增多

解决办法:

  1. 选择更好的硬件设备作为 slave。
  2. 把一台从服务器单独作为备份使用,而不提供查询,那边他的负载下来了,执行 relay log 里面的 SQL 效率自然就高了。
  3. 增加从服务器,这个目的还是分散读的压力,从而降低从服务器负载。

客户端连接池

客户端和 Mysql 服务端交互可以有 3 种方案:

  1. 不复用连接,每次 CRUD 都建立连接,用完后关闭连接。缺点是每次 TCP 连接耗时很长,影响效率(跟 HTTP1.0 一样的问题),而且容易造成服务端并发太高、连接耗尽
  2. 一直复用同一个连接。建立一个连接,一直不关闭,重复使用。缺点是:如果客户端有多个线程需要同时 CRUD,那么这几个线程只能相互竞争这一个连接,影响效率
  3. 连接池。一开始建立多个连接,放入池中,每次 CRUD 从池中取出连接执行,执行完放回去。相对上面两种是最完美的方案

3 种 log

redo log

实际上 Mysql 的基本存储结构是页(记录都存在页里边),所以 MySQL 是先把这条记录所在的页找到,然后把该页加载到内存中,将对应记录进行修改。

现在就可能存在一个问题:如果在内存中把数据改了,还没来得及落磁盘,而此时的数据库挂了怎么办?显然这次更改就丢了。

如果每个请求都需要将数据立马落磁盘之后,那速度会很慢,MySQL 可能也顶不住。所以 MySQL 是怎么做的呢?

MySQL 引入了 redo log,事务提交后,先写一份 redo log(redo log 也是具有内存缓冲的,也需要刷新到磁盘。每个事务提交时,会触发 redo log 内存变更刷新到磁盘,只有刷新到磁盘完成了,commit 才会返回),然后写内存,这份 redo log 记载着这次在某个页上做了什么修改

redo log 内存变更刷新到磁盘不只是事务提交时才会触发,有下面几种情况

  1. Master Thread 每一秒将重做日志缓冲刷新到重做日志文件;
  2. 每个事务提交时会将重做日志缓冲刷新到重做日志文件;
  3. 当重做日志缓冲池剩余空间小于 1/2 时,重做日志缓冲刷新到重做日志文件。

写 redo log 也是需要写磁盘的,但它的好处就是顺序 IO(我们都知道顺序 IO 比随机 IO 快非常多)。

所以,redo log 的存在为了:当我们修改的时候,写完内存了,但数据还没真正写到磁盘的时候。此时我们的数据库挂了,我们可以根据 redo log 来对数据进行恢复。因为 redo log 是顺序 IO,所以写入的速度很快,并且 redo log 记载的是物理变化(xxxx 页做了 xxx 修改),文件的体积很小,恢复速度很快。

binlog

binlog 记录了数据库表结构和表数据变更,比如 update/delete/insert/truncate/create。它不会记录 select(因为这没有对表没有进行变更)

binlog 我们可以简单理解为:存储着每条变更的 SQL 语句(当然从下面的图看来看,不止 SQL,还有 XID「事务 Id」等等)

因为 binlog 记录了数据库表的变更,所以我们可以用 binlog 进行复制(主从复制)和恢复数据。

undo log

undo log 主要有两个作用:回滚和多版本控制 (MVCC)

在数据修改的时候,不仅记录了 redo log,还记录 undo log,如果因为某些原因导致事务失败或回滚了,可以用 undo log 进行回滚

undo log 主要存储的也是逻辑日志,比如我们要 insert 一条数据了,那 undo log 会记录的一条对应的 delete 日志。我们要 update 一条记录时,它会记录一条对应相反的 update 记录。

这也应该容易理解,毕竟回滚嘛,跟需要修改的操作相反就好,这样就能达到回滚的目的。因为支持回滚操作,所以我们就能保证:“一个事务包含多个操作,这些操作要么全部执行,要么全都不执行”。【原子性】

binlog 和 redo log 的区别

binlog 记载的是 update/delete/insert 这样的 SQL 语句,而 redo log 记载的是物理修改的内容(xxxx 页修改了 xxx)。

所以在搜索资料的时候会有这样的说法:redo log 记录的是数据的物理变化,binlog 记录的是数据的逻辑变化

binlog 和 redo log 写入的细节

redo log 是 MySQL 的 InnoDB 引擎所产生的。

binlog 无论 MySQL 用什么引擎,都会有的。

上面也提到,在修改的数据的时候,binlog 会记载着变更的类容,redo log 也会记载着变更的内容。(只不过一个存储的是物理变化,一个存储的是逻辑变化)。那他们的写入顺序是什么样的呢?

redo log 事务开始的时候,就开始记录每次的变更信息,而 binlog 是在事务提交的时候才记录。

于是新有的问题又出现了:我写其中的某一个 log,失败了,那会怎么办?现在我们的前提是先写 redo log,再写 binlog,我们来看看:

如果写 redo log 失败了,那我们就认为这次事务有问题,回滚,不再写 binlog。
如果写 redo log 成功了,写 binlog,写 binlog 写一半了,但失败了怎么办?我们还是会对这次的事务回滚,将无效的 binlog 给删除(因为 binlog 会影响从库的数据,所以需要做删除操作)
如果写 redo log 和 binlog 都成功了,那这次算是事务才会真正成功。
简单来说:MySQL 需要保证 redo log 和 binlog 的数据是一致的,如果不一致,那就乱套了。

分区分表分库

分区

将整个数据库比作图书馆,一张表就是一本书。当要在一本书中查找某项内容时,如果不分章节,查找的效率将会下降。而同理,在数据库中就是分区。

整个目录就是所有的索引(既有主键索引又有主键索引),页码就是 B+tree 的节点号,目录中的标题就是索引字段(主键索引中就是主键值,辅助索引中就是辅助索引值),页中内容要么是按照辅助索引排序的主键值(辅助索引中)要么是行数据(主键索引中),分区就是分章节(目录和内容都在章节里)。

一个区中一共有 64 个物理上连续的大小为 16k 的页,每页里面有很多行按照主键索引排序的数据。

通俗地讲,就是将一个表中的数据以及索引按照一定规则进行分类放到一个个区中,每个区存放独立的数据以及索引(针对区中数据的主键索引和辅助索引)

分区只支持水平分区(是指将一个表中不同行存放到不同的物理文件中),不支持垂直分区(是指将一个表中不同列存放到不同的物理文件中)

就是把一张表的数据分成N个区块,在逻辑上看最终只是一张表,但底层是由N个物理区块组成的

作用主要是提高查询效率,也可以通过将分区放到不同的磁盘,提高数据库的整体磁盘吞吐量

什么情况下考虑分区:

  1. 表中的数据是分段的,比如今年的数据作为一段,明年的数据作为另一段
  2. 对数据的操作往往只涉及一部分数据,而不是所有的数据。这样的话只需要扫描某几个分区的数据,提高了查询效率

但分区不一定能提高效率,相反,使用不当可能导致严重影响查询效率。

1000w 的表按照主键 id 进行 hash 分区,假设分成了 10 个区,那么根据主键查询的语句可能效率能有所提升(1000w 的主键索引可能有3层,而 100w 索引数只有两层,那么减少一次 IO,可以提高效率。但是如果 100w 也是 3 层,则不会有任何帮助)。

但是如果是根据主键以外的某个字段查询,比如 name(假设 name 是辅助索引),这个时候就需要查询 10 个分区的辅助索引树,IO 次数比不分区多了 10 倍,效率影响可想而知

RANGE 分区

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
create table employees (
id int not null,
fname varchar(30),
lname varchar(30),
hired date not null default '1970-01-01',
separated date not null default '9999-12-31',
job_code int not null,
store_id int not null
) partition by range (store_id) (
partition p0 values less than (6),
partition p1 values less than (11),
partition p2 values less than (16),
partition p3 values less than (21),
partition p3 values less than maxvalue
);

LIST 分区

1
2
3
4
5
6
7
8
9
10
11
12
13
14
create table employees (
id int not null,
fname varchar(30),
lname varchar(30),
hired date not null default '1970-01-01',
separated date not null default '9999-12-31',
job_code int not null,
store_id int not null
) partition by list(store_id)
partition pNorth values in (3,5,6,9,17),
partition pEast values in (1,2,10,11,19,20),
partition pWest values in (4,12,13,14,18),
partition pCentral values in (7,8,15,16)
);

HASH 分区

1
2
3
4
5
6
7
8
9
10
create table employees (
id int not null,
fname varchar(30),
lname varchar(30),
hired date not null default '1970-01-01',
separated date not null default '9999-12-31',
job_code int not null,
store_id int not null
) partition by hash(store_id)
partitions 4

KEY 分区

1
2
3
4
5
6
create table tk (
col1 int not null,
col2 char(5),
col3 date
) partition by linear key (col1)
partitions 3;

删除表分区(删除这个分区后,这个分区中的数据都会被删除。这个特性可以用来分区管理数据)

1
ALTER TABLE sale_data DROP PARTITION s20100406 ;

查看分区

1
SELECT PARTITION_NAME,TABLE_ROWS FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_NAME = 'sale_data';

分表

就是把一张表按一定的规则分解成 N 个具有独立存储空间的实体表。系统读写时需要根据定义好的规则得到对应的字表明,然后操作它。

分区和分表的区别与联系

  1. 分区和分表的目的都是减少数据库的负担,提高表的增删改查效率。
  2. 分区只是一张表中的数据的存储位置发生改变,分表是将一张表分成多张表。
  3. 当访问量大,且表数据比较大时,两种方式可以互相配合使用。
  4. 当访问量不大,但表数据比较多时,可以只进行分区。

常见分表的规则策略

  1. Hash 之后按照分表个数取模
  2. 建立一个 DB,这个 DB 单独保存 user_id 到 DB 的映射关系

分库(分实例)

什么时候考虑使用分库?

  1. 单台 DB 的存储空间不够
  2. 随着查询量的增加单台数据库服务器已经没办法支撑

其主要目的是为突破单节点数据库服务器的 I/O 能力限制,解决数据库扩展性问题。

垂直拆分(就是微服务架构中各个独立数据库)

将系统中不存在关联关系或者需要 join 的表可以放在不同的数据库不同的服务器中。

按照业务垂直划分。比如:可以按照业务分为资金、会员、订单三个数据库。

需要解决的问题:跨数据库的事务、分布式事务、join 查询等问题。

水平拆分

例如,大部分的站点。数据都是和用户有关,那么可以根据用户,将数据按照用户水平拆分。

按照规则划分,一般水平分库是在垂直分库之后的。比如每天处理的订单数量是海量的,可以按照一定的规则水平划分。

需要解决的问题:数据路由、组装。

比如根据 user_id 分库,user_id 是采用 SnowFlake 雪花算法生成 id 的分布式主键

假设 123、456 在不同的库,user_id in (123, 456) 怎么查询?

分布式主键方案

当水平分表或水平分库时,比如一张用户表拆分成多个用户表,如果还是使用自增 id 加步长 1 的话,就会存在不同表中 user id 一样的 user,大问题

所以需要有确定分布式主键的方案,主要方案有以下几种:

  1. 数据库自增长 id,步长设置为分表个数。缺点是严重依赖分表个数,后期水平扩展非常困难
  2. UUID。使用 uuid 算法生成,缺点是值太长且无序,不适合做主键,会影响效率(无序会导致插入新记录时需要随机写磁盘,相比顺序写很影响效率)
  3. Redis 生成 ID。用 Redis 的原子操作 INCR 和 INCRBY 实现。假如一个集群中有 5 台 Redis。可以初始化每台 Redis 的值分别是 1,2,3,4,5,然后步长都是 5
  4. Twitter 的 snowflake 算法。缺点是依赖于系统时钟的一致性。如果某台机器的系统时钟回拨,有可能造成 ID 冲突,或者 ID 乱序
  5. 利用 Zookeeper 生成唯一 ID
  6. MongoDB 的 ObjectId

开源实现 Vitess 数据库中间件

数据库拆分后面临一些问题:

  1. 事务的支持,分库分表,就变成了分布式事务
  2. join 时跨库,跨表的问题
  3. 分库分表,读写分离使用了分布式,分布式为了保证强一致性,必然带来延迟,导致性能降低,系统的复杂度变高。

一些中间件针对这些问题实现了各自的解决方案

下面是 vitness 的架构图

vitess 使用二阶段提交解决分布式事务

使用 Sequences 表提供分布式 id 实现水平分表(vitess 中叫分片,分表的规则策略称为 vindex)

vittess 具有以下特性:

  1. Vitess 集 Mysql 数据库的很多重要特性和 NoSQL 数据库的可扩展性于一体。其内建拆分分片功能使您能够对您的 MySQL 数据库集群无限水平扩展,同时无需为应用添加分片逻辑。
  2. Vitess 自动重写对数据库性能有损害的查询。它还使用缓存机制来调解查询,并防止重复查询同时到达您的数据库
  3. Vitess 可以支持自动处理主故障转移和备份等功能。它使用分布式元数据服务来跟踪和管理服务器,使您的应用程序无需关心数据库拓扑变化。
  4. Vitess 避免了 MySQL 连接的高内存开销。Vitess 服务器轻松地一次处理数千个连接。
  5. MySQL 本身并不提供拆分分片功能,但是您的业务数据量增大到一定程度是您是需要增加集群的。Vitess 提供在线拆分功能,只需要很少的时间就完成新集群的切换,无需您在应用程序中添加任何拆分逻辑。
  6. Vitess 会跟踪有关集群配置的所有元数据,以便集群拓扑始终是最新的,对不同的客户端保持一致。

一条 SQL 语句在 MySQL 中如何执行的

  1. 先检查该语句是否有权限(增删改查权限)
  2. 如果没有权限,直接返回错误信息
  3. 如果有权限,在 MySQL8.0 版本以前,会先查询缓存。
  4. 如果没有缓存,分析器进行词法分析,提取 sql 语句 select 等的关键元素。然后判断 sql 语句是否有语法错误,比如关键词是否正确等等。
  5. 优化器进行确定执行方案
  6. 进行权限校验,如果没有权限就直接返回错误信息,如果有权限就会调用数据库引擎接口,返回执行结果。

Mysql 时区

Mysql 时区设置会影响 now() 或者 curtime() 等函数的返回值,以及 created_at、updated_at 的自动生成的值

但不影响 insert test (name, created_at) values (“haha”, “2020-06-18 17:58:37”); 设置的值,设置的是哪个日期,存进数据库的就是哪个,时区由客户端自己区分

查看 mysql 时区

方法1
select now(); // 显示 UTC 时间,时区设置就是 UTC

方法2
show variables like “%time_zone%”;
time_zone: SYSTEM 说明mysql使用system的时区
system_time_zone: CST 说明system使用CST时区 system_time_zone: UTC 说明system使用UTC时区

方法3:
SELECT @@global.time_zone, @@session.time_zone;

修改时区

set global time_zone = ‘+8:00’; ## 修改 mysql 全局时区为北京时间,即我们所在的东8区。对所有连接生效
set time_zone = ‘+8:00’; ## 修改当前连接时区
flush privileges; # 立即生效

Mysql 有关权限的表

MySQL 服务器通过权限表来控制用户对数据库的访问,权限表存放在 mysql 数据库里,由 mysql_install_db 脚本初始化。这些权限表分别 user,db,table_priv,columns_priv 和 host。

  • user 权限表:记录允许连接到服务器的用户帐号信息,里面的权限是全局级的。
  • db 权限表:记录各个帐号在各个数据库上的操作权限。
  • table_priv 权限表:记录数据表级的操作权限。
  • columns_priv 权限表:记录数据列级的操作权限。
  • host 权限表:配合 db 权限表对给定主机上数据库级操作权限作更细致的控制。这个权限表不受 GRANT 和 REVOKE 语句的影响。

SQL 语句的执行顺序

  1. from
  2. join on
  3. where
  4. group by
  5. having
  6. select
  7. distinct
  8. order by
  9. limit

MVCC 多版本并发控制

MVCC 多版本并发控制是指每行记录具有多个版本,行正在被写,而 select 读的时候可以不申请任何锁,去读快照版本(这种操作叫非锁定一致性读,也叫快照读),大大提高并发性能

类似于实现了一个乐观锁

只在 REPEATABLE READ 和 READ COMMITTED 两个隔离级别下会使用 MVCC

所以 READ UNCOMMITTED 会存在脏读,总是读取最新的数据行,而不是符合当前事务版本的数据行

而 SERIALIZABLE 会对所有读取到的行都加锁,事务间串行

MVCC 解决了在 REPEATABLE READ 和 READ COMMITTED 两个隔离级别下两个事务读同一行和写同一行的并发

考虑下面场景

下面是事务中查询每一行都执行的操作流程(可见性算法)

  • alive_trx_list:read-view 生成时刻系统中正在活跃的事务 id 。
  • up_limit_id:记录上面的 alive_trx_list 中的最小事务 id 。
  • max_trx_id:目前已出现的事务 ID 的最大值
  • low_limit_id:read-view 生成时刻,max_trx_id + 1。
  • current_trx_id:执行查操作的事务 id

每个表都有两个隐藏字段:

  1. DB_TRX_ID:最近修改(更新/删除/插入)该记录的事务 ID。
  2. DB_ROLL_PTR:回滚指针,指向这条记录的上一个版本。

事务中对每一行的每个增删改都会创建一个新视图,新视图包含了增删改后的结果,以及两个隐藏字段的更新( DB_TRX_ID 变成执行此次操作的事务 id ,DB_ROLL_PTR 指向本体)

快照读(一致性非锁定读)

select 采用的是快照读( lock in share mode 以及 for update 除外),可以无需加读锁,提高性能。由 MVCC 实现

本地读(一致性锁定读)

下面的语句都是采用的本地读。在执行这几个操作时会读取最新的记录,即使是别的事务提交的数据也可以查询到。

1
2
3
4
5
select * from table where ? lock in share mode; 
select * from table where ? for update;
insert;
update;
delete;

其他

  • 不能在同一个 sql 语句中,先 select 同一个表的某些值,然后再 update 这个表

    1
    2
    3
    4
    delete from withdraw_address where id not in (
    select max(id) from withdraw_address
    group by user_id, currency, address having id is not null
    );
  • 查看数据库访问信息

    1
    select * from information_schema.processlist where USER = "wallet";

    等效于:

    1
    show processlist
  • sql 语句中使用字符串跟整型字段相比较时,会自动截取字符串中前面的所有数字来进行比较




微信关注我,及时接收最新技术文章