Mysql 数据库问题汇总

最新推荐文章于 2024-05-30 15:55:33 发布

马各阿牛

最新推荐文章于 2024-05-30 15:55:33 发布

阅读量781

点赞数 2

分类专栏： mysql 文章标签： mysql

原文链接：https://blog.csdn.net/joy0921/article/details/80125563

版权

mysql 专栏收录该内容

4 篇文章 0 订阅

订阅专栏

一、什么是回表

下面是知乎大佬的回答。

MySQL innodb的主键索引是簇集索引，也就是索引的叶子节点存的是整个单条记录的所有字段值，不是主键索引的就是非簇集索引，非簇集索引的叶子节点存的是主键字段的值。回表是什么意思？就是你执行一条sql语句，需要从两个b+索引中去取数据。举个例子：

表tbl有a,b,c三个字段，其中a是主键，b上建了索引，然后编写sql语句

SELECT * FROM tbl WHERE a=1

这样不会产生回表，因为所有的数据在a的索引树中均能找到

SELECT * FROM tbl WHERE b=1

这样就会产生回表，因为where条件是b字段，那么会去b的索引树里查找数据，但b的索引里面只有a,b两个字段的值，没有c，那么这个查询为了取到c字段，就要取出主键a的值，然后去a的索引树去找c字段的数据。查了两个索引树，这就叫回表。

索引覆盖就是查这个索引能查到你所需要的所有数据，不需要去另外的数据结构去查。其实就是不用回表。

怎么避免？不是必须的字段就不要出现在SELECT里面。或者b,c建联合索引。但具体情况要具体分析，索引字段多了，存储和插入数据时的消耗会更大。这是个平衡问题。

二、MySQL排序内部原理

a. 如何查看是否排序

我们通过explain查看MySQL执行计划时，经常会看到在Extra列中显示Using filesort。

其实这种情况就说明MySQL使用了排序。

Using filesort经常出现在order by、group by、distinct、join等情况下。

b. 索引优化排序

看到排序，我们的DBA首先想到的肯定是，是否可以利用索引来优化。

InnoDB默认采用的是B+ tree索引，B+ tree索引本身就是有序的，如果有一个查询如下：

select * from film where actor_name='苍老师' order by prod_time;

那么只需要加一个(actor_name,prod_time)的索引就能够利用B tree的特性来避免额外排序。

如下图所示：

图片描述

通过B+-tree查找到actor_name=’苍老师’演员为苍老师的数据以后，只需要按序往右查找就可以了，不需要额外排序操作。

c. 排序模式概览

row ID是指主键

· Ⅰ 回表排序模式

  根据索引或者全表扫描，按照过滤条件获得需要查询的排序字段值和row ID；
  将要排序字段值和row ID组成键值对，存入sort buffer中；
  如果sort buffer内存大于这些键值对的内存，就不需要创建临时文件了。否则，每次sort buffer填满以后，需要直接用qsort(快速排序算法)在内存中排好序，并写到临时文件中；
  重复上述步骤，直到所有的行数据都正常读取了完成；
  用到了临时文件的，需要利用磁盘外部排序，将row id写入到结果文件中；
  根据结果文件中的row ID按序读取用户需要返回的数据。由于row ID不是顺序的，导致回表时是随机IO，为了进一步优化性能（变成顺序IO），MySQL会读一批row ID，并将读到的数据按排序字段顺序插入缓存区中(内存大小read_rnd_buffer_size)。

· Ⅱ 不回表排序模式

根据索引或者全表扫描，按照过滤条件获得需要查询的数据；
将要排序的列值和用户需要返回的字段组成键值对，存入sort buffer中；
如果sort buffer内存大于这些键值对的内存，就不需要创建临时文件了。否则，每次sort buffer填满以后，需要直接用qsort(快速排序算法)在内存中排好序，并写到临时文件中；
重复上述步骤，直到所有的行数据都正常读取了完成；
用到了临时文件的，需要利用磁盘外部排序，将排序后的数据写入到结果文件中；
直接从结果文件中返回用户需要的字段数据，而不是根据row ID再次回表查询。

· Ⅲ 打包数据排序模式

第三种排序模式的改进仅仅在于将char和varchar字段存到sort buffer中时，更加紧缩。

在之前的两种模式中，存储了“yes”3个字符的定义为VARCHAR(255)的列会在内存中申请255个字符内存空间，但是5.7.3改进后，只需要存储2个字节的字段长度和3个字符内存空间（用于保存”yes”这三个字符）就够了，内存空间整整压缩了50多倍,可以让更多的键值对保存在sort buffer中。

· 三种模式比较

第二种模式是第一种模式的改进，避免了二次回表，采用的是用空间换时间的方法。

但是由于sort buffer就那么大，如果用户要查询的数据非常大的话，很多时间浪费在多次磁盘外部排序，导致更多的IO操作，效率可能还不如第一种方式。

所以，MySQL给用户提供了一个max_length_for_sort_data的参数。当“排序的键值对大小” > max_length_for_sort_data时，MySQL认为磁盘外部排序的IO效率不如回表的效率，会选择第一种排序模式；反之，会选择第二种不回表的模式。

第三种模式主要是解决变长字符数据存储空间浪费的问题，对于实际数据不多，字段定义较长的改进效果会更加明显。

很多文章写到这里可能就差不多了，但是大家忘记关注一个问题了：“如果排序的数据不能完全放在sort buffer内存里面，是怎么通过外部排序完成整个排序过程的呢？”

要解决这个问题，我们首先需要简单查看一下外部排序到底是怎么做的。

d.MySQL外部排序

Ⅰ MySQL外部排序算法

那MySQL使用的外部排序是怎么样的呢，我们以回表排序模式为例：

根据索引或者全表扫描，按照过滤条件获得需要查询的数据；

将要排序的列值和row ID组成键值对，存入sort buffer中；

如果sort buffer内存大于这些键值对的内存，就不需要创建临时文件了。否则，每次sort buffer填满以后，需要直接用qsort(快速排序模式)在内存中排好序，作为一个block写到临时文件中。跟正常的外部排序写到多个文件中不一样，MySQL只会写到一个临时文件中，并通过保存文件偏移量的方式来模拟多个文件归并排序；

重复上述步骤，直到所有的行数据都正常读取了完成；

每MERGEBUFF (7) 个block抽取一批数据进行排序，归并排序到另外一个临时文件中，直到所有的数据都排序好到新的临时文件中；

重复以上归并排序过程，直到剩下不到MERGEBUFF2 (15)个block。

通俗一点解释：
第一次循环中，一个block对应一个sort buffer（大小为sort_buffer_size）排序好的数据；每7个做一个归并。
第二次循环中，一个block对应MERGEBUFF (7) 个sort buffer的数据，每7个做一个归并。
…
直到所有的block数量小于MERGEBUFF2 (15)。

最后一轮循环，仅将row ID写入到结果文件中；

根据结果文件中的row ID按序读取用户需要返回的数据。为了进一步优化性能，MySQL会读一批row ID，并将读到的数据按排序字段要求插入缓存区中(内存大小read_rnd_buffer_size)。

这里我们需要注意的是：

MySQL把外部排序好的分片写入同一个文件中，通过保存文件偏移量的方式来区别各个分片位置；
MySQL每MERGEBUFF (7)个分片做一个归并，最终分片数达到MERGEBUFF2 (15)时，做最后一次归并。这两个值都写死在代码中了……

Ⅱ sort_merge_passes

MySQL手册中对Sort_merge_passes的描述只有一句话

Sort_merge_passes
The number of merge passes that the sort algorithm has had to do. If this value is large,
 you should consider increasing the value of the sort_buffer_size system variable.

这段话并没有把sort_merge_passes到底是什么，该值比较大时说明了什么，通过什么方式可以缓解这个问题。

我们把上面MySQL的外部排序算法搞清楚了，这个问题就清楚了。

其实sort_merge_passes对应的就是MySQL做归并排序的次数，也就是说，如果sort_merge_passes值比较大，说明sort_buffer和要排序的数据差距越大，我们可以通过增大sort_buffer_size或者让填入sort_buffer_size的键值对更小来缓解sort_merge_passes归并排序的次数。

对应的，我们可以在源码中看到证据。

上述MySQL外部排序的算法中第5到第7步，是通过sql/filesort.cc文件中merge_many_buff()函数来实现，第5步单次归并使用merge_buffers()实现，源码摘录如下：

int merge_many_buff(Sort_param *param, Sort_buffer sort_buffer,
                    Merge_chunk_array chunk_array,
                    size_t *p_num_chunks, IO_CACHE *t_file)
{
...

    for (i=0 ; i < num_chunks - MERGEBUFF * 3 / 2 ; i+= MERGEBUFF)
    {
      if (merge_buffers(param,                  // param
                        from_file,              // from_file
                        to_file,                // to_file
                        sort_buffer,            // sort_buffer
                        last_chunk++,           // last_chunk [out]
                        Merge_chunk_array(&chunk_array[i], MERGEBUFF),
                        0))                     // flag
      goto cleanup;
    }
    if (merge_buffers(param,
                      from_file,
                      to_file,
                      sort_buffer,
                      last_chunk++,
                      Merge_chunk_array(&chunk_array[i], num_chunks - i),
                      0))
      break;                                    /* purecov: inspected */
...
}

截取部分merge_buffers()的代码如下，

int merge_buffers(Sort_param *param, IO_CACHE *from_file,
                  IO_CACHE *to_file, Sort_buffer sort_buffer,
                  Merge_chunk *last_chunk,
                  Merge_chunk_array chunk_array,
                  int flag)
{
...
  current_thd->inc_status_sort_merge_passes();
...
}

可以看到：每个merge_buffers()都会增加sort_merge_passes，也就是说每一次对MERGEBUFF (7)个block归并排序都会让sort_merge_passes加一，sort_merge_passes越多表示排序的数据太多，需要多次merge pass。解决的方案无非就是缩减要排序数据的大小或者增加sort_buffer_size。

打个小广告，在我们的qmonitor中就有sort_merge_pass的性能指标和参数值过大的报警设置。

e.trace结果解释

说明白了三种排序模式和外部排序的方法，我们回过头来看一下trace的结果。

Ⅰ 是否存在磁盘外部排序

"number_of_tmp_files": 0,

number_of_tmp_files表示有多少个分片，如果number_of_tmp_files不等于0，表示一个sort_buffer_size大小的内存无法保存所有的键值对，也就是说，MySQL在排序中使用到了磁盘来排序。

Ⅱ 是否存在优先队列优化排序

由于我们的这个SQL里面没有对数据进行分页限制，所以filesort_priority_queue_optimization并没有启用

    "filesort_priority_queue_optimization": {
              "usable": false,
              "cause": "not applicable (no LIMIT)"
     },

而正常情况下，使用了Limit会启用优先队列的优化。优先队列类似于FIFO先进先出队列。

算法稍微有点改变，以回表排序模式为例。

sort_buffer_size足够大

如果Limit限制返回N条数据，并且N条数据比sort_buffer_size小，那么MySQL会把sort buffer作为priority queue，在第二步插入priority queue时会按序插入队列；在第三步，队列满了以后，并不会写入外部磁盘文件，而是直接淘汰最尾端的一条数据，直到所有的数据都正常读取完成。

算法如下：

· 根据索引或者全表扫描，按照过滤条件获得需要查询的数据
· 将要排序的列值和row ID组成键值对，按序存入中priority queue中
· 如果priority queue满了，直接淘汰最尾端记录。
· 重复上述步骤，直到所有的行数据都正常读取了完成
· 最后一轮循环，仅将row ID写入到结果文件中
· 根据结果文件中的row ID按序读取用户需要返回的数据。为了进一步优化性能，MySQL会读一批row ID，并将读到的数据按排序字段要求插入缓存区中(内存大小read_rnd_buffer_size)。

sort_buffer_size不够大

否则，N条数据比sort_buffer_size大的情况下，MySQL无法直接利用sort buffer作为priority queue，正常的文件外部排序还是一样的，只是在最后返回结果时，只根据N个row ID将数据返回出来。具体的算法我们就不列举了。

这里MySQL到底是否选择priority queue是在sql/filesort.cc的check_if_pq_applicable()函数中确定的，具体的代码细节这里就不展开了。

另外，我们也没有讨论Limit m,n的情况，如果是Limit m,n，上面对应的“N个row ID”就是“M+N个row ID”了，MySQL的Limit m,n 其实是取m+n行数据，最后把M条数据丢掉。

从上面我们也可以看到sort_buffer_size足够大对Limit数据比较小的情况，优化效果是很明显的。

f. MySQL其他相关排序参数

Ⅰ max_sort_length

这里需要区别max_sort_length和max_length_for_sort_data。

max_length_for_sort_data是为了让MySQL选择< sort_key, rowid >还是< sort_key, additional_fields >的模式。

而max_sort_length是键值对的大小无法确定时（比如用户要查询的数据包含了 SUBSTRING_INDEX(col1, ‘.’,2)）MySQL会对每个键值对分配max_sort_length个字节的内存，这样导致内存空间浪费，磁盘外部排序次数过多。

Ⅱ innodb_disable_sort_file_cache

innodb_disable_sort_file_cache设置为ON的话，表示在排序中生成的临时文件不会用到文件系统的缓存，类似于O_DIRECT打开文件。

Ⅲ innodb_sort_buffer_size

这个参数其实跟我们这里讨论的SQL排序没有什么关系。innodb_sort_buffer_size设置的是在创建InnoDB索引时，使用到的sort buffer的大小。

以前写死为1M，现在开放出来，允许用户自定义设置这个参数了。

g.MySQL排序优化总结

最后整理一下优化MySQL排序的手段

    1. 排序和查询的字段尽量少。只查询你用到的字段，不要使用select *；使用Limit查询必要的行数据；
    2. 要排序或者查询的字段，尽量不要用不确定字符函数，避免MySQL直接分配max_sort_length，导致sort buffer空间不足；
    3. 使用索引来优化或者避免排序；
    4. 增加sort_buffer_size大小，避免磁盘排序；
    5. 不得不使用original排序算法时，增加read_rnd_buffer_size；
    6. 字段长度定义合适就好（避免过长）；
    7. tmpdir建议独立存放，放在高速存储设备上。

三、mysql事务原理以及锁

a. ACID

其实AC是一个概念，就是要么一起执行，要么都不执行，只是看问题的指标不同而已，一个侧重过程，一个侧重结果

A（atomic）：原子性，联级操作，要么一起执行，要么一起回滚，不存在执行了操作一，但是操作二失败了，操作一、二是一个整体, 使用 undo log ，从而达到回滚
C（consistency）：一致性，要么处于修改都成功，要么处于修改都失败，一致性的状态。（可以指单节点的一个事务下的系列操作，也可以指集群状态下所有节点的数据状态，比如zk集群，又分强一致性，弱一致性，最终一致性），通过回滚，以及恢复，和在并发环境下的隔离做到一致性
I（Isolation）：隔离性，比如A 和 B同时开启事务，A操作了数据a，B读取a会读取之前的a，而不是刚被A操作的a（好的隔离性可以避免：脏读、不可重复读、幻读)，使用锁以及MVCC,运用的优化思想有读写分离，读读并行，读写并行
D（Durability）：持久性，提交过的事务，会持久性的保存在数据库当中，即使宕机还有效，实现原理：redo log

https://www.jianshu.com/p/336e4995b9b8

b. 并发容易引发的问题

更新丢失：同时修改同一数据，A先修改完，B再修改，A的修改结果丢失。
脏读：更改但未提交的数据，被其他人查询到了未提交的数据，脏读
不可重复读：一个事务在读取某些数据后的某个时间，再次读取以前读过的数据，却发现其读出的数据已经发生了改变、或某些记录已经被删除了！这种现象就叫做“不可重复读”。但是可能会查到之前不存在的数据，幻读。

说人话就是，同一个事务里，第一次读取和第二次读取的结果不一致，其他事务修改或删除

幻读（Phantom Reads）：一个事务按相同的查询条件重新读取以前检索过的数据，却发现其他事务插入了满足其查询条件的新数据，这种现象就称为“幻读”。

这里给出 mysql 幻读的比较形象的场景：

users： id 主键

1、T1：select * from users where id = 1;

2、T2：insert into `users`(`id`, `name`) values (1, 'big cat');

3、T1：insert into `users`(`id`, `name`) values (1, 'big cat');

T1 ：主事务，检测表中是否有 id 为 1 的记录，没有则插入，这是我们期望的正常业务逻辑。

T2 ：干扰事务，目的在于扰乱 T1 的正常的事务执行。

在 RR 隔离级别下，1、2 是会正常执行的，3 则会报错主键冲突，对于 T1 的业务来说是执行失败的，这里 T1 就是发生了幻读，因为T1读取的数据状态并不能支持他的下一步的业务，见鬼了一样。

注：Serializable 隔离级别下 select 操作并会对当前记录加锁，这就造成了其他会话事务可能插入记录，当前事务再插入时，产生了数据冲突。其实 RR 也是可以尽可能避免幻读的，通过对 select 操作手动加锁（select ... for update 这也正是 Serializable 隔离级别下会隐式为你做的事情），还需要理解，即便当前记录不存在，比如 id = 1 是不存在的，但当前事务也会获得一把记录锁（因为锁定的是索引，故记录实体存在与否没关系），其他事务无法插入此索引的记录，故不会产生幻读。

在 Serializable 隔离级别下，1 执行时是会隐式的添加 gap 共享锁的，从而 2 会被阻塞，3 会正常执行，对于 T1 来说业务是正确的，成功的扼杀了扰乱业务的T2，对于T1来说他读取的状态是可以拿来支持业务的。

所以 mysql 的幻读并非什么读取两次返回结果集不同，而是事务在插入事先检测不存在的记录时，惊奇的发现这些数据已经存在了，之前的检测读获取到的数据如同鬼影一般。

这里要灵活的理解读取的意思，第一次select是读取，第二次的 insert 其实也属于隐式的读取，只不过是在 mysql 的机制中读取的，插入数据也是要先读取一下有没有主键冲突才能决定是否执行插入。

不可重复读侧重表达读-读，幻读则是说读-写，用写来证实读的是鬼影。

c. 事务级别（默认 REPEATABLE-READ，可能会幻读的)

READ UNCOMMITTED ，可以读到其他事务修改甚至未提交的，-->引发脏读
READ COMMITTED ，其他事务对数据库的修改,只要已经提交,其修改的结果就是可见的,与这两个事务开始的先后顺序无关-->不可重复读，在一个事务中两次读取不一致
REPEATABLE READ，可重复读，完全适用MVCC，只能读取在它开始之前已经提交的事务对数据库的修改,在它开始以后,所有其他事务对数据库的修改对它来说均不可见
Serializable(可串行化)：一条数据加锁了之后，其他事务既无法读取也无法修改一条数据，牺牲性能换数据安全性

事务A

start transaction;
select * from user_t lock in share mode;

事务B

start transaction;
INSERT user_t values(null,'huangnew','huangnew',50);

因为隔离级别是可串行化，事务A查询的时候把所有的查询行都加了共享锁，所以事务B阻塞，从而避免了幻读的可能性。

RR不能解决幻读的解释
因为可重复读和提交读本身就是冲突的，默认可重复读是会出现幻读的，除非加共享锁取读取，就可以读取提交的最新的数据。
只有串行化才可以保证数据的绝对不冲突。

d.锁机制

Ⅰ共享锁（IS锁）和排他锁（IX锁）

共享锁又称为读锁，简称S锁，顾名思义，共享锁就是多个事务对于同一数据可以共享一把锁，都能访问到数据，但是只能读不能修改。

排他锁又称为写锁，简称X锁，顾名思义，排他锁就是不能与其他所并存，如一个事务获取了一个数据行的排他锁，
其他事务就不能再获取该行的其他锁，包括共享锁和排他锁，但是获取排他锁的事务是可以对数据就行读取和修改。

不带任何锁的普通查询，不管有没有锁，直接读老数据。
排他锁指的是一个事务在一行数据加上排他锁后，其他事务不能再在其上加其他的锁。
mysql InnoDB引擎默认的修改数据语句，update,delete,insert都会自动给涉及到的数据加上排他锁，
select语句默认不会加任何锁类型，如果加排他锁可以使用select ...for update语句，
加共享锁可以使用select ... lock in share mode语句。所以加过排他锁的数据行在其他事务中
是不能修改数据的，也不能通过for update和lock in share mode锁的方式查询数据，
但可以直接通过select ...from...查询数据，因为普通查询没有任何锁机制

开启事务未提交的DML操作都会自动加排他锁，这个时候只能进行不带锁的普通查询，而且只能查到老数据

Ⅱ 意向锁 Share Intent Exclusive Lock，简称SIX锁

意向锁是放置在资源层次结构的一个级别上的锁，以保护较低级别资源上的共享锁或排它锁。例如，在 SQL Server 2000 数据库引擎任务应用表内的共享或排它行锁之前，在该表上放置意向锁。

如果另一个任务试图在该表级别上应用共享或排它锁，则受到由第一个任务控制的表级别意向锁的阻塞。第二个任务在锁定该表前不必检查各个页或行锁，而只需检查表上的意向锁。

针对Innodb的优化，存在行锁的时候，对表中某一行或者几行数据加锁的时候，都会给表加表级别的意向锁，这样，事务B要给表加表锁的使用因为该表以及是IS 或者IX了，所以直接加锁失败，而不需要遍历表中的所有记录是否有锁。

Ⅲ mysql的行级锁
开始事务update一条数据的话，可以用共享锁和排他锁的方式读取非这条数据。适合并发的写入的业务场景

锁命令

lock tables t1 write|read;
UNLOCK TABLES;
SELECT * FROM `user_t` for update;

Ⅳ 悲观锁

特点是先获取锁，再进行业务操作，即“悲观”的认为获取锁是非常有可能失败的，因此要先确保获取锁成功再进行业务操作。通常所说的“一锁二查三更新”即指的是使用悲观锁。
select … for update操作来实现悲观锁，并发进行的时候，下一个事务如果有行交集的话就会阻塞。
也是串行化级别的采取的策略

Ⅵ 乐观锁

乐观锁的区别在于乐观的认为获取锁是很有可能成功的，如果真的不成功，被别人改过数据了，则回滚。
乐观锁在数据库上的实现完全是逻辑的，不需要数据库提供特殊的支持。一般的做法是在需要锁的数据上增加一个版本号，或者时间戳
乐观锁 + MYISAM 可以实现带事务控制的同时支持高性能读取，又支持不频繁带事务控制的写。适用读多写少，且回滚开销不是特别大的场景。InnoDB就是乐观锁+版本控制管理，才实现的高并发的RR事务级别。

区别

    乐观锁是否在事务中其实都是无所谓的，悲观锁一定要有事务控制，
乐观锁在不发生取锁失败的情况下开销比悲观锁小，但是一旦发生失败回滚开销则比较大，
特别是多个DML一起操作，所有执行成功的DML都要回滚，因此适合用在取锁失败概率比较小的场景，
可以提升系统并发性能。因为锁表锁行是需要数据库开销的，即使未阻塞，也是有开销的。
    但是每次计算都是有效的，不存在计算之后数据回滚的风险。
悲观锁适合高并发，锁竞争激烈的业务场景，至少可以保证一次有1个成功，争取锁失败会等待。
而乐观锁更适合竞争没有那么激烈的业务场景，减少了锁的开销，更轻量，但是竞争激烈时，会反复的修改数据失败。

e.死锁

死锁一般发生在表和表之间，其实也可以发生在行于行之间。
Mysql自带等待锁超时的时间，默认50s，超时自动释放锁
事务A占用了数据1 准备更新数据2时发现事务B已经占用了数据2，所以事务A等待事务B解锁数据2.
事务B占用了数据2准备更新数据1时，发现事务A已经占用了数据1，所以事务B等待事务A解锁诗句1.
这个时候就是死锁了。

如何避免死锁：业务上做优化，或者在并发度上做妥协

尽量用行锁别用表锁，为表添加合理的索引。如果不走索引将会为表的每一行记录添加上锁，死锁的概率大大增大。
大事务拆小。大事务更倾向于死锁，如果业务允许，将大事务拆小，可以提交的独立提交。
在同一个事务中，尽可能做到一次锁定所需要的所有资源，减少死锁概率。
以固定的顺序访问表和行。即按顺序申请锁，这样就不会造成互相等待的场面。编程的时候要注意。
降低隔离级别。如果业务允许，将隔离级别调低也是较好的选择，比如将隔离级别从RR调整为RC，可以避免掉很多因为gap锁造成的死锁。

f.MVCC

Multi-Version Concurrency Control 多版本并发控制，MVCC 是一种并发控制的方法，一般在数据库管理系统中，实现对数据库的并发访问；在编程语言中实现事务内存。

InnoDB：通过为每一行记录添加两个额外的隐藏的值来实现MVCC，这两个值一个记录这行数据何时被创建，另外一个记录这行数据何时过期（或者被删除）。但是InnoDB并不存储这些事件发生时的实际时间，相反它只存储这些事件发生时的系统版本号。这是一个随着事务的创建而不断增长的数字。每个事务在事务开始时会记录它自己的系统版本号。每个查询必须去检查每行数据的版本号与事务的版本号是否相同。

让我们来看看当隔离级别是REPEATABLE READ时这种策略是如何应用到特定的操作的：

SELECT InnoDB必须每行数据来保证它符合两个条件：

1、InnoDB必须找到一个行的版本，它至少要和事务的版本一样老(也即它的版本号不大于事务的版本号)。这保证了不管是事务开始之前，或者事务创建时，或者修改了这行数据的时候，这行数据是存在的。

2、这行数据的删除版本必须是未定义的或者比事务版本要大。这可以保证在事务开始之前这行数据没有被删除。这里的不是真正的删除数据，而是标志出来的删除。真正意义的删除是在commit的时候。

符合这两个条件的行可能会被当作查询结果而返回。

INSERT：InnoDB为这个新行记录当前的系统版本号。

DELETE：InnoDB将当前的系统版本号设置为这一行的删除ID。

UPDATE：InnoDB会写一个这行数据的新拷贝，这个拷贝的版本为当前的系统版本号。它同时也会将这个版本号写到旧行的删除版本里。

这种额外的记录所带来的结果就是对于大多数查询来说根本就不需要获得一个锁。他们只是简单地以最快的速度来读取数据，确保只选择符合条件的行。这个方案的缺点在于存储引擎必须为每一行存储更多的数据，做更多的检查工作，处理更多的善后操作。

MVCC只工作在REPEATABLE READ和READ COMMITED隔离级别下。READ UNCOMMITED不是MVCC兼容的，因为查询不能找到适合他们事务版本的行版本；它们每次都只能读到最新的版本。SERIABLABLE也不与MVCC兼容，因为读操作会锁定他们返回的每一行数据。

五、MySQL事务隔离级别和实现原理

本文所说的 MySQL 事务都是指在 InnoDB 引擎下，MyISAM 引擎是不支持事务的。

数据库事务指的是一组数据操作，事务内的操作要么就是全部成功，要么就是全部失败，什么都不做，其实不是没做，是可能做了一部分但是只要有一步失败，就要回滚所有操作，有点一不做二不休的意思。

概念说明

以下几个概念是事务隔离级别要实际解决的问题，所以需要搞清楚都是什么意思。

脏读（READ UNCOMMITTED）

脏读指的是读到了其他事务未提交的数据，未提交意味着这些数据可能会回滚，也就是可能最终不会存到数据库中，也就是不存在的数据。读到了并一定最终存在的数据，这就是脏读。

不可重复读（READ COMMITTED）

对比可重复读，不可重复读指的是在同一事务内，不同的时刻读到的同一批数据可能是不一样的，可能会受到其他事务的影响，比如其他事务改了这批数据并提交了。通常针对数据更新（UPDATE）操作。

可重复读（REPEATABLE READ）

可重复读指的是在一个事务内，最开始读到的数据和事务结束前的任意时刻读到的同一批数据都是一致的。通常针对数据更新（UPDATE）操作。

幻读

幻读是针对数据插入（INSERT）操作来说的。假设事务A对某些行的内容作了更改，但是还未提交，此时事务B插入了与事务A更改前的记录相同的记录行，并且在事务A提交之前先提交了，而这时，在事务A中查询，会发现好像刚刚的更改对于某些数据未起作用，但其实是事务B刚插入进来的，让用户感觉很魔幻，感觉出现了幻觉，这就叫幻读。

事务隔离级别

SQL 标准定义了四种隔离级别，MySQL 全都支持。这四种隔离级别分别是：

读未提交（READ UNCOMMITTED）
读提交（READ COMMITTED）
可重复读（REPEATABLE READ）
串行化（SERIALIZABLE）

从上往下，隔离强度逐渐增强，性能逐渐变差。采用哪种隔离级别要根据系统需求权衡决定，其中，可重复读是 MySQL 的默认级别。

事务隔离其实就是为了解决上面提到的脏读、不可重复读、幻读这几个问题，下面展示了 4 种隔离级别对这三个问题的解决程度。

只有串行化的隔离级别解决了全部这 3 个问题，其他的 3 个隔离级别都有缺陷。

如何设置隔离级别

我们可以通过以下语句查看当前数据库的隔离级别，通过下面语句可以看出我使用的 MySQL 的隔离级别是 REPEATABLE-READ，也就是可重复读，这也是 MySQL 的默认级别。

六、mysql索引

1. 普通索引和唯一索引的区别

查询过程

普通索引：先找到第一个满足的数据，再找到第一个不满足的数据。
唯一索引：直接找到第一个满足的数据。但是两者差别不大
数据库加载直接加载数据页，即使是只查询一条记录，也是加入整一页数据到内存。

更新过程

如果数据页在内存中，直接更新
如果数据页不在内存中，先把更新操作放入change buffer中.
唯一索引：需要把数据页加载到内存页，判断有没有冲突，再更新
普通索引：直接更新
唯一索引更新操作会加大磁盘io负载

N、 Mysql应用层问题

一、insert select带来的问题

当使用 insert...select...进行记录的插入时，如果select的表是innodb类型的，不论insert的表是什么类型的表，都会对select的表的纪录进行锁定。对于那些从oracle迁移过来的应用，需要特别的注意，因为oracle并不存在类似的问题，所以在oracle的应用中insert...select...操作非常的常见。例如：有时候会对比较多的纪录进行统计分析，然后将统计的中间结果插入到另外一个表，这样的操作因为进行的非常少，所以可能并没有设置相应的索引。如果迁移到mysql数据库后不进行相应的调整，那么在进行这个操作期间，对需要select的表实际上是进行的全表扫描导致的所有记录的锁定，将会对应用的其他操作造成非常严重的影响。

究其主要原因，是因为 mysql 在实现复制的机制时和 oracle 是不同的，如果不进行 select 表的锁定，则可能造成从数据库在恢复期间插入结果集的不同，造成主从数据的不一致。如果不采用主从复制，关闭 binlog 并不能避免对 select 纪录的锁定，某些文档中提到可以通过设置innodb_locks_unsafe_for_binlog来避免这个现象，当这个参数设置为true的时候，将不会对select的结果集加锁，但是这样的设置将可能带来非常严重的隐患。如果使用这个binlog进行从数据库的恢复，或者进行主数据库的灾难恢复，都将可能和主数据库的执行效果不同。

因此，推荐通过设置这个参数来避免 insert...select...导致的锁，如果需要进行可能会扫描大量数据的 insert...select 操作,推荐使用select...into outfile 和 load data infile 的组合来实现，这样是不会对纪录进行锁定的。

二、带where条件的delete数据之后，数据文件大小不变

在 InnoDB 中，你的 delete 操作，并不会真的把数据删除，mysql 实际上只是给删除的数据打了个标记，标记为删除，因此你使用 delete 删除表中的数据，表文件在磁盘上所占空间不会变小，我们这里暂且称之为假删除。

如果delete不加WHERE子句，那么它和truncate table是一样的，但它们有一点不同，那就是delete可以返回被删除的记录数，而truncate table返回的是0。

参考链接：

https://www.zhihu.com/question/347087093/answer/830934717

https://blog.csdn.net/joy0921/article/details/80125563

https://www.jianshu.com/p/dffb04ee1a7c

https://www.zhihu.com/question/47007926

https://www.cnblogs.com/kismetv/p/10331633.html

马各阿牛

关注

2
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
Mysql 数据库问题汇总

一、什么是回表下面是知乎大佬的回答。 MySQL innodb的主键索引是簇集索引，也就是索引的叶子节点存的是整个单条记录的所有字段值，不是主键索引的就是非簇集索引，非簇集索引的叶子节点存的是主键字段的值。回表是什么意思？就是你执行一条sql语句，需要从两个b+索引中去取数据。举个例子：表tbl有a,b,c三个字段，其中a是主键，b上建了索引，然后编写sql语句 SELECT * FROM tbl WHERE a=1 这样不会产生回表，因为所有的数据在a...
复制链接

扫一扫