Mysql 面试题整理

Mysql索引

Mysql 索引分类

  • B+ 树索引(包括普通索引、唯一索引、主键索引)
  • 哈希索引
  • 全文索引
  • R-TREE 索引(空间索引,主要用于地理空间数据类型,很少使用)。

Mysql 传统意义上的索引为B+ 树索引,B+ 树索引的本质就是B+ 树在数据库中的实现,由于B+ 树的高扇出性,数据库中的B+ 树的高一般为2-4层,因此查找某一键值的行记录只需2-4次IO,大概0.02~0.04秒。

(扇出性:是指该模块直接调用的下级模块的个数。扇出大表示模块的复杂度高,需要控制和协调过多的下级模块

聚集索引和辅助索引

  • 聚集索引:

    是根据每张表的主键建造的一棵B+ 树,叶子节点中存放的是整张表的行记录。一张表只能有一个聚集索引。因为聚集索引在逻辑上是连续的,所以它对于主键的排序查找和范围查找速度非常快。

  • 辅助索引:

    与聚集索引不同的地方在于,辅助索引不是唯一的,它的叶子节点只包含行记录的部分数据以及对应聚集索引的节点位置。通过辅助索引来查找数据时,先遍历辅助索引找到对应主键索引,再通过主键索引查找对应记录。

在MYISAM 中主键索引和辅助索引都相当上述辅助索引,索引页中存放的是主键和指向数据页的偏移量,数据页中存放的是主键和该主键所属行记录的地址空间。唯一的区别是MYISAM 中主键索引不能重复,辅助索引可以。

联合索引和覆盖索引

  • 联合索引:

    是指对表上的多个列进行索引。它对对应多个列的指定获取比较快。另外一个好处是联合索引对第二个键已经排好序了,所以对两个列的排序获取可以避免多做一次排序操作。

  • 覆盖索引:

    其实更算一种思想,能够从辅助索引中获取信息,就不需要查询聚集索引中的数据。使用辅助索引的好处在于辅助索引包含的信息少,所以大小远小于聚集索引,因此可以大大减少IO 操作。

Mysql 联合索引

联合索引的最左前缀

MySQL的user表中,对a,b,c三个字段建立联合索引,那么查询时使用其中的2个作为查询条件,是否还会走索引?

根据查询字段的位置不同来决定,如查询a, a,b a,b,c a,c 都可以走索引的,其他条件的查询不能走索引。

组合索引 有“最左前缀”原则。就是只从最左面的开始组合,并不是所有只要含有这三列存在的字段的查询都会用到该组合索引。

联合索引的存储方式

  1. 先把各个记录和页按照从左往右第一列进行排序。

  2. 在记录的前一列相同的情况下,采用下一列进行排序

  3. 依此类推

    通过以上操作,保证了所有索引数据是按照索引列的值从小到大的顺序排好序的。
    当我们要查找匹配索引条件时,只需从左往右依次匹配。

范围查询对联合索引的影响

在组合索引中,使用between、>、<、like等进行匹配都会导致后面的列无法继续走联合索引,因为通过以上方式匹配到的数据是不可知的,后面的列无法确定根据哪些数据继续向下进行索引。

在group by 和 order by 字段上使用联合索引

创建联合索引, c1234(c1,c2,c3,c4)

1、只有where的情况,遵从最左原则

条件必须有左边的字段,才会用到索引,中间如果断开了,则都不会用到后面的索引,

例子: where c1 = ‘1’ and c2 = ‘1’ and c4 = ‘1’,这种情况因为c3没有在条件中,所以只会用到c1,c2索引。

特殊情况,使用范围条件的时候,也会使用到该处的索引,但后面的索引都不会用到

例子: where c1 = ‘1’ and c2 > ‘1’ and c3 = ‘1’,这种情况从c2处已经断开,会使用到c1,c2索引,不会再使用到后面的c3,c4索引。

2、group by和order by 其实一样,也是遵从最左原则

可以看做继承where的条件顺序,但需要where作为基础铺垫,即没有where语句,单纯的group by | order by 也是不会使用任何索引的,并且需要和联合索引顺序一致才会使用索引。

例子:

group by c1 | order by c1,由于没有where的铺垫,不使用任何索引

where c1 = ‘1’ group | order by c2,使用c1,c2索引

where c1 = ‘1’ group | order by c3,只使用c1索引

where c1 > ‘1’ group | order by c2,前面也说了,范围搜索会断掉连接,所以也只会使用c1索引

关于顺序:

where c1 = ‘1’ group | order by c2,c3,使用c1,c2,c3索引

where c1 = ‘1’ group | order by c3,c2,只使用c1索引

主键和唯一索引区别

1.主键是一种约束,唯一索引是一种索引;

2.一张表只能有一个主键,但可以创建多个唯一索引;

3.主键创建后一定包含一个唯一索引,唯一索引并一定是主键;

4.主键不能为null,唯一索引可以为null;

5.主键可以做为外键,唯一索引不行;

6.主键产生唯一的聚集索引,唯一索引产生唯一的非聚集索引

innodb什么时候会产生回表

当索引字段为非聚簇索引,并且查询字段不是索引字段;

索引的优缺点

索引的优点:

  • 通过创建唯一性索引,可以保证数据库表中每一行数据的唯一性。
  • 可以大大加快 数据的检索速度,这也是创建索引的最主要的原因。
  • 可以加速表和表之间的连接,特别是在实现数据的参考完整性方面特别有意义。
  • 在使用分组和排序 子句进行数据检索时,同样可以显著减少查询中分组和排序的时间。
  • 通过使用索引,可以在查询的过程中,使用优化隐藏器,提高系统的性能。

索引的缺点

  • 创建索引和维护索引要耗费时间,这种时间随着数据量的增加而增加。
  • 索引需要占物理空间,除了数据表占数据空间之外,每一个索引还要占一定的物理空间,如果要建立聚簇索引,那么需要的空间就会更大。
  • 当对表中的数据进行增加、删除和修改的时候,索引也要动态的维护,这样就降低了数据的维护速度。
  • 哪些情况需要加索引?

mysql 允许null 的字段设置索引哪些需要注意

mysql 进行is null 查询会使用索引的, 进行 not null 判断则不会使用索引。

虽然MySQL可以在含有null的列上使用索引,但不代表null和其他数据在索引中是一样的。

不建议列上允许为空。最好限制not null,并设置一个默认值,比如0和’’空字符串等,如果是datetime类型,可以设置成’1970-01-01 00:00:00’这样的特殊值。

对MySQL来说,null是一个特殊的值,s。比如:不能使用=,<,>这样的运算符,对null做算术运算的结果都是null,count时不会包括null行等,null比空字符串需要更多的存储空间等。

B 树

B树特征

  1. 根结点至少有两个子女。
  2. 每个中间节点都包含k-1个元素和k个孩子,其中 m/2 <= k <= m
  3. 每一个叶子节点都包含k-1个元素,其中 m/2 <= k <= m
  4. 所有的叶子结点都位于同一层。
  5. 每个节点中的元素从小到大排列,节点当中k-1个元素正好是k个孩子包含的元素的值域分划。

B+ 树特征

  1. 有k个子树的中间节点包含有k个元素(B树中是k-1个元素),每个元素不保存数据,只用来索引,所有数据都保存在叶子节点。
  2. 所有的叶子结点中包含了全部元素的信息,及指向含这些元素记录的指针,且叶子结点本身依关键字的大小自小而大顺序链接。
  3. 所有的中间节点元素都同时存在于子节点,在子节点元素中是最大(或最小)元素。

1588818370345

B+树的优势:

  1. 单一节点存储更多的元素,使得查询的IO次数更少。
  2. 所有查询都要查找到叶子节点,查询性能稳定。
  3. 所有叶子节点形成有序链表,便于范围查询。

备注: m为阶数

Mysql 引擎

两种存储引擎区别

InnoDB:

  • InnoDB 存储引擎支持事务、支持外键、支持非锁定读、行锁设计其设计主要面向OLTP 应用。
  • InnoDB 存储引擎表采用聚集的方式存储,因此每张表的存储顺序都按主键的顺序存放,如果没有指定主键,InnoDB 存储引擎会为每一行生成一个6字节的ROWID并以此作为主键。
  • InnoDB 存储引擎通过MVCC 获的高并发性,并提供了插入缓冲、二次写、自适应哈希索引和预读等高性能高可用功能
  • InnoDB 存储引擎默认隔离级别为REPEATABLE_READ(重复读)并采用next-key locking(间隙锁)和MVCC来避免幻读

MySIAM:

  • MYISAM 存储引擎不支持事务、表锁设计、支持全文索引其设计主要面向OLAP 应用
  • MYISAM 存储引擎表由frm、MYD 和MYI 组成,frm 文件存放表格定义,MYD 用来存放数据文件,MYI 存放索引文件。MYISAM 存储引擎与众不同的地方在于它的缓冲池只缓存索引文件而不缓存数据文件,数据文件的缓存依赖于操作系统。
    操作区别:

MYISAM 保存表的具体行数,不带where 是可直接返回。InnoDB 要扫描全表。
DELETE 表时,InnoDB 是一行一行的删除,MYISAM 是先drop表,然后重建表
InnoDB 跨平台可直接拷贝使用,MYISAM 不行
InnoDB 表格很难被压缩,MYISAM 可以

引擎选择

MyISAM相对简单所以在效率上要优于InnoDB。

如果系统读多,写少。对原子性要求低,那么MyISAM最好的选择。且MyISAM恢复速度快。可直接用备份覆盖恢复。

InnoDB 更适合系统读少,写多的时候,尤其是高并发场景。

Mysql 锁

数据库锁设计的初衷是处理并发问题。作为多用户共享的资源,当出现并发访问的时候,数据库需要合理地控制资源的访问规则。而锁就是用来实现这些访问规则的重要数据结构。

根据加锁的范围,MySQL 里面的锁大致可以分成全局锁、表级锁和行锁三类。

锁的分类

全局锁

顾名思义,全局锁就是对整个数据库实例加锁。MySQL 提供了一个加全局读锁的方法,命令是 Flush tables with read lock (FTWRL)。

当你需要让整个库处于只读状态的时候,可以使用这个命令,之后其他线程的以下语句会被阻塞:数据更新语句(数据的增删改)、数据定义语句(包括建表、修改表结构等)和更新类事务的提交语句。

全局锁的典型使用场景是,做全库逻辑备份。也就是把整库每个表都 select 出来存成文本。

表级锁

MySQL 里面表级别的锁有两种:一种是表锁,一种是元数据锁(meta data lock,MDL)。

表锁的语法是 lock tables … read/write。与 FTWRL 类似,可以用 unlock tables 主动释放锁,也可以在客户端断开的时候自动释放。

需要注意,lock tables 语法除了会限制别的线程的读写外,也限定了本线程接下来的操作对象。

在还没有出现更细粒度的锁的时候,表锁是最常用的处理并发的方式。而对于 InnoDB 这种支持行锁的引擎,一般不使用 lock tables 命令来控制并发,毕竟锁住整个表的影响面还是太大。

元数据锁

MDL 不需要显式使用,在访问一个表的时候会被自动加上。MDL 的作用是,保证读写的正确性。你可以想象一下,如果一个查询正在遍历一个表中的数据,而执行期间另一个线程对这个表结构做变更,删了一列,那么查询线程拿到的结果跟表结构对不上,肯定是不行的。

因此,在 MySQL 5.5 版本中引入了 MDL,当对一个表做增删改查操作的时候,加 MDL 读锁;当要对表做结构变更操作的时候,加 MDL 写锁。

  • 读锁之间不互斥,因此你可以有多个线程同时对一张表增删改查。
  • 读写锁之间、写锁之间是互斥的,用来保证变更表结构操作的安全性。因此,如果有两个线程要同时给一个表加字段,其中一个要等另一个执行完才能开始执行。

需要注意的是:MDL 会直到事务提交才释放,在做表结构变更的时候,你一定要小心不要导致锁住线上查询和更新。

行锁

MySQL 的行锁是在引擎层由各个引擎自己实现的。但并不是所有的引擎都支持行锁,比如 MyISAM 引擎就不支持行锁。不支持行锁意味着并发控制只能使用表锁,对于这种引擎的表,同一张表上任何时刻只能有一个更新在执行,这就会影响到业务并发度。InnoDB 是支持行锁的,这也是 MyISAM 被 InnoDB 替代的重要原因之一。

在 InnoDB 事务中,行锁是在需要的时候才加上的,但并不是不需要了就立刻释放,而是要等到事务结束时才释放。这个就是两阶段锁协议。

死锁

当并发系统中不同线程出现循环资源依赖,涉及的线程都在等待别的线程释放资源时,就会导致这几个线程都进入无限等待的状态,称为死锁。

1588819238826

这时候,事务 A 在等待事务 B 释放 id=2 的行锁,而事务 B 在等待事务 A 释放 id=1 的行锁。 事务 A 和事务 B 在互相等待对方的资源释放,就是进入了死锁状态。

死锁解决策略

当出现死锁以后,有两种策略:

  • 一种策略是,直接进入等待,直到超时。这个超时时间可以通过参数 innodb_lock_wait_timeout 来设置。
  • 另一种策略是,发起死锁检测,发现死锁后,主动回滚死锁链条中的某一个事务,让其他事务得以继续执行。将参数 innodb_deadlock_detect 设置为 on,表示开启这个逻辑。

减少死锁的主要方向,就是控制访问相同资源的并发事务量。

乐观锁&悲观锁

乐观并发控制和悲观并发控制是并发控制采用的主要方法。

悲观锁

现在互联网高并发的架构中,受到fail-fast思路的影响,悲观锁已经非常少见了。

悲观锁(Pessimistic Locking),悲观锁是指在数据处理过程,使数据处于锁定状态,一般使用数据库的锁机制实现。

什么是悲观锁

举个栗子:

假设商品表中有一个字段quantity表示当前该商品的库存量。假设有一件Dulex套套,其id为100,quantity=8个;如果不使用锁,那么操作方法

如下:

1
2
3
4
5
6
//step1: 查出商品剩余量
select quantity from items where id=100;
//step2: 如果剩余量大于0,则根据商品信息生成订单
insert into orders(id,item_id) values(null,100);
//step3: 修改商品的库存
update Items set quantity=quantity-1 where id=100;

这样子的写法,在小作坊真的很正常,No Problems,但是在高并发环境下可能出现问题。

如下:

1592038079311

其实在①或者②环节,已经有人下单并且减完库存了,这个时候仍然去执行step3,就造成了超卖

但是使用悲观锁,就可以解决这个问题,在上面的场景中,商品信息从查询出来到修改,中间有一个生成订单的过程,使用悲观锁的原理就是,当我们在查询出items信息后就把当前的数据锁定,直到我们修改完毕后再解锁。那么在这个过程中,因为数据被锁定了,就不会出现有第三者来对其进行修改了。而这样做的前提是需要将要执行的SQL语句放在同一个事物中,否则达不到锁定数据行的目的。

如下:

1
2
3
4
5
6
//step1: 查出商品状态
select quantity from items where id=100 for update;
//step2: 根据商品信息生成订单
insert into orders(id,item_id) values(null,100);
//step3: 修改商品的库存
update Items set quantity=quantity-2 where id=100;

select…for update是MySQL提供的实现悲观锁的方式。此时在items表中,id为100的那条数据就被我们锁定了,其它的要执行select quantity from items where id=100 for update的事务必须等本次事务提交之后才能执行。这样我们可以保证当前的数据不会被其它事务修改。

扩展思考

需要注意的是,当我执行select quantity from items where id=100 for update后。

如果我是在第二个事务中执行select quantity from items where id=100(不带for update)仍能正常查询出数据,不会受第一个事务的影响。

另外,MySQL还有个问题是select…for update语句执行中所有扫描过的行都会被锁上,因此在MySQL中用悲观锁务必须确定走了索引,而不是全表扫描,否则将会将整个数据表锁住

悲观锁并不是适用于任何场景,它也存在一些不足,因为悲观锁大多数情况下依靠数据库的锁机制实现,以保证操作最大程度的独占性。如果加锁的时间过长,其他用户长时间无法访问,影响了程序的并发访问性,同时这样对数据库性能开销影响也很大,特别是对长事务而言,这样的开销往往无法承受,这时就需要乐观锁。

在此和大家分享一下,在Oracle中,也存在select … for update,和mysql一样,但是Oracle还存在了select … for update nowait,即发现被锁后不等待,立刻报错。

乐观锁

乐观锁相对悲观锁而言,它认为数据一般情况下不会造成冲突,所以在数据进行提交更新的时候,才会正式对数据的冲突与否进行检测,如果发现冲突了,则让返回错误信息,让用户决定如何去做。接下来我们看一下乐观锁在数据表和缓存中的实现。

什么是乐观锁

利用数据版本号(version)机制是乐观锁最常用的一种实现方式。一般通过为数据库表增加一个数字类型的 “version” 字段,当读取数据时,将version字段的值一同读出,数据每更新一次,对此version值+1。当我们提交更新的时候,判断数据库表对应记录的当前版本信息与第一次取出来的version值进行比对,如果数据库表当前版本号与第一次取出来的version值相等,则予以更新,否则认为是过期数据,返回更新失败。

放个被用烂了的图

1592038092276

image.png

举个栗子🌰:

1
2
3
4
5
6
//step1: 查询出商品信息
select (quantity,version) from items where id=100;
//step2: 根据商品信息生成订单
insert into orders(id,item_id) values(null,100);
//step3: 修改商品的库存
update items set quantity=quantity-1,version=version+1 where id=100 and version=#{version};

既然可以用version,那还可以使用时间戳字段,该方法同样是在表中增加一个时间戳字段,和上面的version类似,也是在更新提交的时候检查当前数据库中数据的时间戳和自己更新前取到的时间戳进行对比,如果一致则OK,否则就是版本冲突。

需要注意的是,如果你的数据表是读写分离的表,当master表中写入的数据没有及时同步到slave表中时会造成更新一直失败的问题。此时,需要强制读取master表中的数据(将select语句放在事物中)。

即:把select语句放在事务中,查询的就是master主库了!

间隙锁

幻读指的是一个事务 前后两次查询同一个范围的时候,后一次查询看到了前一次查询没有看到的行

为了解决幻读问题,InnoDB 只好引入新的锁,也就是 间隙锁 (Gap Lock),间隙锁,锁的就是两个值之间的空隙

跟间隙锁存在冲突关系的,是“往这个间隙中插入一个记录”这个操作

间隙锁和行锁合称 next-key lock,每个 next-key lock 是前开后闭区间

我们把间隙锁记为开区间,把 next-key lock 记为前开后闭区间

InnoDB 给每个索引加了一个不存在的最大值 suprenum,这样才符合我们前面说的“都是前开后闭区间”

间隙锁和 next-key lock 的引入,帮我们解决了幻读的问题,但同时也带来了一些“困扰”

间隙锁的引入,可能会导致同样的语句锁住更大的范围,这其实是影响了并发度的

Mysql事务

什么是事务

事务就是一组原子性的操作,这些操作要么全部发生,要么全部不发生。事务把数据库从一种一致性状态转换成另一种一致性状态。

事物的特性

事务具有ACID 四种特性,即原子性(atomicity),一致性(consistency),隔离性(isolation),持久性(durability):

  • 原子性,指的是事务是一个不可分割的操作,要么全都正确执行,要么全都不执行。
  • 一致性,指的是事务把数据库从一种一致性状态转换成另一种一致性状态,事务开始前和事务结束后,数据库的完整性约束没有被破坏。
  • 隔离性,要求每个读写事务相互之间是分开的,在事务提交前对其他事务是不可见的
  • 持久性,指的是事务一旦提交,其结果就是永久性的,即使宕机也能恢复。

事物隔离级别

事务有4 个隔离级别,分别是:

  • 读未提交(read uncommit)
  • 读已提交(read commit)
  • 可重复读(repeatable read)
  • 和序列化(serializable)。

隔离级别依次提高,分别解决了脏读、不可重读和幻读。

1、脏读:事务A读取了事务B更新的数据,然后B回滚操作,那么A读取到的数据是脏数据

2、不可重复读:事务 A 多次读取同一数据,事务 B 在事务A多次读取的过程中,对数据作了更新并提交,导致事务A多次读取同一数据时,结果不一致。

3、幻读:系统管理员A将数据库中所有学生的成绩从具体分数改为ABCDE等级,但是系统管理员B就在这个时候插入了一条具体分数的记录,当系统管理员A改结束后发现还有一条记录没有改过来,就好像发生了幻觉一样,这就叫幻读。

不可重复读的和幻读很容易混淆,不可重复读侧重于修改,幻读侧重于新增或删除。解决不可重复读的问题只需锁住满足条件的行,解决幻读需要锁表

InnoDB 默认隔离级别为repeatable read,但是通过next-key lock 解决了幻读,保证了ACID。

事务的实现

前面讲的重做日志,回滚日志以及锁技术就是实现事务的基础。

  • 事务的原子性是通过 undo log 来实现的
  • 事务的持久性性是通过 redo log 来实现的
  • 事务的隔离性是通过 (MySQL中有三种行锁算法,Record Lock(单行锁定),Gap Lock(间隙锁)和Next-Key Locking(Record+Gap)+MVCC)来实现的
  • 而事务的终极大 boss 一致性是通过原子性,持久性,隔离性来实现的!!

undo log 和 redo log

事务是基于重做日志文件(redo log)和回滚日志(undo log)实现的。

每提交一个事务必须先将该事务的所有日志写入到重做日志文件进行持久化,数据库就可以通过重做日志来保证事务的原子性和持久性。

每当有修改事务时,还会产生undo log,如果需要回滚,则根据undo log 的反向语句进行逻辑操作,比如insert 一条记录就delete 一条记录。

undo-log回滚日志总不能一直保留,什么时候删除呢?

答案是,在不需要的时候才删除。也就是说,系统会判断,当没有事务再需要用到这些回滚日志时,回滚日志会被删除

undo-log什么时候才不需要了呢?

就是当系统里没有比这个回滚日志更早的 read-view 的时候

长事务意味着系统里面会存在很老的事务视图。由于这些事务随时可能访问数据库里面的任何数据,所以这个事务提交之前,数据库里面它可能用到的回滚记录都必须保留,这就会导致大量占用存储空间

除了对回滚段的影响,长事务还占用锁资源,也可能拖垮整个库

MVCC工作原理?

快照生成过程

  1. 快照: 在可重复读隔离级别下,事务在启动的时候就“拍了快照”-read-view。这个快照是基于整库的

  2. 生成数据版本, trx_id: InnoDB 里面每个事务有一个唯一的事务 ID,叫作 transaction id。它是在事务开始的时候向 InnoDB 的事务系统申请的,是按申请顺序严格递增的, 而每行数据也都是有多个版本的。每次事务更新数据的时候,都会生成一个新的数据版本,并且把 transaction id 赋值给这个数据版本的事务 ID,记为trx_id。

  3. 保留旧版本: 同时,旧的数据版本要保留,并且在新的数据版本中,能够有信息可以直接拿到它, 也就是说,数据表中的一行记录,其实可能有多个版本 (row),每个版本有自己的 row trx_id。 每个版本不是真实存在的而是每次需要的时候根据当前版本和 undo.log 计算出来的

  4. 只承认在版本之前生成的版本: 因此,一个事务只需要在启动的时候声明说,“以我启动的时刻为准,如果一个数据版本是在我启动之前生成的,就认; 如果是我启动以后才生成的,我就不认,我必须要找到它的上一个版本; 当然,如果“上一个版本”也不可见,那就得继续往前找。还有,如果是这个事务自己更新的数据,它自己还是要认的。在实现上, InnoDB 为每个事务构造了一个数组,用来保存这个事务启动瞬间,当前正在“活跃”的所有事务 ID。“活跃”指的就是,启动了但还没提交。

  5. 一致性视图: 数组里面事务 ID 的最小值记为低水位,当前系统里面已经创建过的事务 ID 的最大值加 1 记为高水位。 这个视图数组和高水位,就组成了当前事务的一致性视图, 而数据版本的可见性规则,就是基于数据的 row trx_id 和这个一致性视图的对比结果得到的。

InnoDB 利用了“所有数据都有多个版本”的这个特性,实现了“秒级创建快照”的能力

MVCC 怎么实现隔离性

一致性读(快照读)

事务不论在什么时候查询(非for update、insert、update、delete语句),看到这行数据的结果都是一致的,所以我们称之为一致性读

一个数据版本,对于一个事务视图来说,除了自己的更新总是可见以外,有三种情况:

  1. 版本未提交,不可见;
  2. 版本已提交,但是是在视图创建后提交的,不可见;
  3. 版本已提交,而且是在视图创建前提交的,可见。

可重复读的核心就是一致性读(consistent read);

而事务更新数据的时候,只能用当前读。

当前读

当前读,读取的是最新版本数据(for update、insert、update、delete语句),并且对读取的记录加RecordLock和GapLock,阻塞其他事物对记录的同时改动,实现了当前读的一致性,并解决了幻读的问题;

总结

当查询数据的时候,通过MVCC的快照实现一致性读,实现了了快照读一致性,解决了快照读的幻读问题。

当更新数据的时候,通过RecordLock + GapLock 实现了当前读数据一致性,解决了当前读会产生幻读的问题。

使用事务应该注意的问题

  • 不要再循环中使用事务(循环提交会导致大量的redo log)
  • 不要使用自动提交
  • 不要使用自动回滚
  • 长事务切分处理
  • SQL 优化

Mysql 主从同步过程

在master机器上的操作

当master上的数据发生改变的时候,该事件(insert、update、delete)变化会按照顺序写入到binlog中。

当slave连接到master的时候,master机器会为slave开启binlog dump线程。如果读取的进度已经跟上了master,就进入睡眠状态并等待master产生新的事件。

当master 的 binlog发生变化的时候,binlog dump线程会通知slave,并将相应的binlog内容发送给slave。

在slave机器上的操作

当主从同步开启的时候,slave上会创建2个线程。

  • I/O线程。该线程连接到master机器,master机器上的binlog dump线程会将binlog的内容发送给该I/O线程。该I/O线程接收到binlog内容后,再将内容写入到本地的relay log。
  • SQL线程。该线程读取I/O线程写入的relay log。并且根据relay log的内容对slave数据库做相应的操作。

执行计划

mysql exlain 各参数解释

1、id 主要是用来标识sql的执行顺序,如果没有子查询,一般来说id只有一个,执行顺序也是从上到下

2、select_type 每个select子句的类型

  a: simple 查询中不包含任何子查询或者union

  b: primary 查询中包含了任何复杂的子部分,最外层的就会变为primary

  c: subquery 在selecth或者where中包含了子查询

  d: derived 在from中包含了子查询

  e: union 如果第二个select 出现在union之后,则被标记为union,如果union包含在from子句的子查询中,外层select会被标记成derived

  f:union result 从 union表中获取结果的select

3、type 是指mysql在表中找到所需行的方式,也就是访问行的类型。从a开始效率上升

  a (All 全表扫描)

​ b (index 会根据索引树进行遍历)

​ c (range 根据索引范围扫描,返回匹配值域的行)

​ d:(ref 非唯一性索引扫描,返回匹配某个单独值的所有行。一般是指多列的唯一索引中的某一列)

​ e (eq_ref 唯一性索引扫描。表中只有一条与之匹配)

​ f (const、system 主要针对查询中有常量的情况,如果结果中只有一行,会变成System)

​ g (null即不走表 也不走索引)

4、possible_keys 预估计了mysql能为当前查询选择的索引。这个字段是完全独立于执行计划中输出的表的顺序。意味着在实际查询中,可能用不到这些索引。如果该字段为null则意味着没有可使用的索引。这个时候你可以考虑为where后面的字段加上索引

5、key 这个字段表示mysql真实使用的索引。如果mysql优化过程中没有加索引,可以强制加索引

6、key_len 顾名思义就是索引长度字段。表示mysql使用的索引的长度

7、ref 这个字段一般是指一些常量用于选择过滤

8、rows 预估结果集的条数,可能不一定完全准确

9、extra 包含额外的信息。

​ a: using filesort: 说明mysql无法利用索引进行排序,只能利用排序算法进行排序,会消耗额外的位置

​ explain select * from emp order by sal;

​ b: using temporary: 建立临时表来保存中间结果,查询完成之后把临时表删除

​ explain select ename,count(*) from emp where deptno = 10 group by ename;

​ c: using index: 这个表示当前的查询时覆盖索引的,直接从索引中读取数据,而不用访问数据表。如果同时出现using where 表名索引被用来执行索引键值的查找,如果没有,表面索引被用来读取数据,而不是真的查找

​ explain select deptno,count(*) from emp group by deptno limit 10;

​ d: using where: 使用where进行条件过滤

​ explain select * from t_user where id = 1;

​ e: using join buffer: 使用连接缓存

​ f: impossible where:where语句的结果总是false

​ explain select * from emp where empno = 7469;

其他

mysql datetime 和timestamp 的区别

其中,datetime和timestamp这两种类型都是用于表示YYYY-MM-DD HH:MM:SS 这种年月日时分秒格式的数据,但两者还是有些许不同之处的。

  1. 存储范围不同:datetime的存储范围是 1000-01-01 00:00:00.0000009999-12-31 23:59:59.999999,而timestamp的范围是 1970-01-01 00:00:01.0000002038-01-19 03:14:07.999999
  2. datetime存储与时区无关,而timestamp存储的是与时区有关,这也是两者最大的不同。
  3. datetime适用于记录数据的创建时间,因为这个时间是不会变的。而timestamp有自动修改更新的功能,也就是说,我们对表里的其他数据进行修改,timestamp修饰的字段会自动更新为当前的时间,这个特性称为自动初始化和自动更新(Automatic Initialization and Updating)。

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

客户端请求 —> 连接器(验证用户身份,给予权限) —> 查询缓存(存在缓存则直接返回,不存在则执行后续操作) —> 分析器(对SQL进行词法分析和语法分析操作) —> 优化器(主要对执行的sql优化选择最优的执行方案方法) —> 执行器(执行时会先看用户是否有执行权限,有才去使用这个引擎提供的接口) —> 去引擎层获取数据返回(如果开启查询缓存则会缓存查询结果)

一张表,里面有ID自增主键,当insert了17条记录之后,删除了第15,16,17条记录,再把Mysql重启,再insert一条记录,这条记录的ID是18还是15 ?

如果表的类型是MyISAM,那么是18。因为MyISAM表会把自增主键的最大ID 记录到数据文件中,重启MySQL自增主键的最大ID也不会丢失;

如果表的类型是InnoDB,那么是15。因为InnoDB 表只是把自增主键的最大ID记录到内存中,所以重启数据库或对表进行OPTION操作,都会导致最大ID丢失。

刘小恺(Kyle) wechat
如有疑问可联系博主