最新Mysql大厂面试必会的34问题

  • Z时代
  • 2024-01-10
  • 分类：综合

database

1、mysql的隔离级别

2、MYSQL性能优化

常用5种方式

3、索引详解

1、何为索引，有什么用？

2、索引的优缺点

4、什么情况下需要建索引？

5、什么情况下不建索引？

6、索引的底层数据结构

1、hash表

Hash 冲突 问题

既然哈希表这么快， 为什么MySQL 没有使用其作为索引的数据结构呢？

2、B 树& B+树

B 树& B+树两者有何异同呢？

7、Hash索引和B+树索引的区别？

8、为什么B+树比B树更适合实现数据库索引？

9、索引有什么分类？

10、什么是最左匹配原则？

11、MySQL聚簇和非聚簇索引

。。。。。。

目录

  • 1、mysql的隔离级别
  • 2、MYSQL性能优化
    • 常用5种方式
  • 3、索引详解
    • 1、何为索引，有什么用？
    • 2、索引的优缺点
  • 4、什么情况下需要建索引？
  • 5、什么情况下不建索引？
  • 6、索引的底层数据结构
    • 1、hash表
      • Hash 冲突 问题
      • 既然哈希表这么快， 为什么MySQL 没有使用其作为索引的数据结构呢？
    • 2、B 树& B+树
      • B 树& B+树两者有何异同呢？
  • 7、Hash索引和B+树索引的区别？
  • 8、为什么B+树比B树更适合实现数据库索引？
  • 9、索引有什么分类？
  • 10、什么是最左匹配原则？
  • 11、MySQL聚簇和非聚簇索引
      • 01、InnoDB中
      • 02、MYISAM中
      • 03、使用聚簇索引的优势
      • 05、为什么主键通常建议使用自增ID
  • 12、什么是覆盖索引？
  • 13、索引的设计原则？
  • 14、什么情况下无法利用索引（索引失效）
  • 15、什么是前缀索引？
  • 16、常见的存储引擎有哪些？
  • 17、 Mysql锁有哪些、如何理解
  • 18、 Mysql慢查询如何优化
  • 19、 Explain语句结果中查询的各个字段的意思
  • 20、 Innodb如何实现事务的
  • 21、 Redis和Mysql如何保证数据一致
  • 22、 索引的基本原理
  • 23、 MyISAM和innoDb的区别
  • 24、ACID靠什么保证的？
  • 25、 什么是MVCC
  • 26、关心过业务系统里面的sql耗时吗？统计过慢查询吗？对慢查询都怎么优化？
  • 25、mysql中char与varchar的区别
  • 26、 mysql中的delete , drop 和truncate 区别
  • 27、关于sql和MySQL的语句执行顺序
    • 1、sql执行顺序
    • 2、mysql的执行顺序
    • 1、SELECT语句定义
  • 28、事务的四大特性
  • 29、 大表怎么优化？
  • 30、bin log/redo log/undo log
  • 31、bin log和redo log有什么区别？
  • 32、 分库分表
  • 33、 having和where的区别？
  • 34、 乐观锁和悲观锁是什么？

1、mysql的隔离级别

四种隔离级别:

  • READ-UNCOMMITTED(读取未提交)： 最低的隔离级别，允许读取尚未提交的数据变更， 可能会导致脏读、幻读或不可重复读 。

  • READ-COMMITTED(读取已提交)： 允许读取并发事务已经提交的数据， 可以阻止脏读，但是幻读或不可重复读仍有可能发生 。

  • REPEATABLE-READ(可重复读)： 对同一字段的多次读取结果都是一致的，除非数据是被本身事务自己所修改， 可以阻止脏读和不可重复读，但幻读仍有可能发生 。

  • SERIALIZABLE(可串行化)： 最高的隔离级别，完全服从ACID的隔离级别。所有的事务依次逐个执行，这样事务之间就完全不可能产生干扰，也就是说， 该级别可以防止脏读、不可重复读以及幻读

隔离级别脏读不可重复读幻读READ UNCONMMITTED可能可能可能READ COMMITTED不可能可能可能REPRATABLE READ不可能不可能可能SERIALIZABLE不可能不可能不可能

1、 脏读：A，B两事务，A事务会读取到B事务未提交的数据，然后B因为某些原因回滚数据，所以A就读取了B没有提交的数据，也称脏数据。

2、 不可重复读：在A事务中对同一数据两次查询不一致，可能原因是在A事务提交之前B事务对该数据进行了操作

3、 幻读：当某个事务在读取某个范围内的记录时，另外一个事务又在该范围内插入了新的记录，当之前的事务再次读取该范围的记录时，会产生幻行，就像产生幻觉一样，这就是发生了幻读。

  • 不可重复读和脏读的区别 是，脏读是某一事务读取了另一个事务未提交的脏数据，而不可重复读则是读取了前一事务提交的数据。

  • 幻读和不可重复读都是读取了另一条已经提交的事务，不同的是不可重复读的重点是修改，幻读的重点在于新增或者删除。

  • 事务隔离就是为了解决上面提到的脏读、不可重复读、幻读这几个问题。

2、MYSQL性能优化

常用5种方式

  • 1、最大连接数优化

**修改my.ini文件(永久修改)**

  • 2、启用查询缓存

特别注意：查询缓存从MySQL 5.7.20开始已被弃用，并在MySQL 8.0中被删除。、

  • 一种说法是不建议使用查询缓存，因为查询缓存往往弊大于利。查询缓存的失效非常频繁，只要有对一个表的更新，这个表上的所有的查询缓存都会被清空。因此很可能你费劲地把结果存起来，还没使用呢，就被一个更新全清空了。对于更新压力大的数据库来说，查询缓存的命中率会非常低。除非你的业务有一张静态表，很长时间更新一次，比如系统配置表，那么这张表的查询才适合做查询缓存。

  • 3、引擎优化

MyISAM存储引擎：

场景 ：如果表主要是用于插入新记录和读出记录，那么选择MyISAM能实现处理高效率。

优点：MyISAM引擎能提供较高的查询效率，适用于对数据进行频繁查询操作的数据表（InnoDB提供提交、回滚、崩溃恢复能力及并发控制能力，适用于对数据更新操作频率高的数据表）

  • 4、索引优化

这里是便于查询可以设置索引，让查询效率变高.

聚合索引按照从左到右的匹配原则。也就是必须先匹配ID才能匹配name查询。

全文检索的查询方式：

SELECT * FROM article WHERE MATCH(title, content) AGAINST("查询字符串")

全文索引只适合MyISAM引擎的数据表。并且只能对英文进行检索

  • 5、SQL语句优化

SQL优化的重心是查询优化，查询优化的重心是建立索引。所以查询优化主要是避免出现导致索引失效的查询。

①避免在索引列上出现null。

②不要在索引列上进行算术运算。：select age+1 from user

③避免实现!=或者<>、is null或者is not null、in等可能导致全表遍历的操作。

④模糊查询只能使用右边%。

⑤where语句后尽可能少用小括号、或者不要出现小括号嵌套小括号。

3、索引详解

1、何为索引，有什么用？

索引是存储引擎用于提高数据库表的访问速度的一种 数据结构 。。常见的索引结构有：Hash、B数，B+树。

索引的作用就是相当于目录的作用。打个比方： 我们在查字典的时候，如果没有目录，那我们就只能一页一页的去找我们需要查的 那个字，速度很慢。如果有目录了，我们只需要先去目录里查找字的位置，然后直接翻到那一页就行了。

数据是存储在磁盘上的，查询数据时，如果没有索引，会加载所有的数据到内存，依次进行检索，读取磁盘次数较多。有了索引，就不需要加载所有数据，因为B+树的高度一般在2-4层，最多只需要读取2-4次磁盘，查询速度大大提升。

2、索引的优缺点

  • 优点：

    • 加快数据查找的速度

    • 为用来排序或者是分组的字段添加索引，可以 加快分组和排序的速度

    • 加快表与表之间连接的速度

  • 缺点：

    • 建立索引需要 占用物理空间

    • 会降低表的增删改的效率，因为每次对表记录进行增删改，需要进行 动态维护索引 ，导致增删改时间变长

注意： 使用索引一定能提高查询性能吗?

大多数情况下，索引查询都是比全表扫描要快的。但是如果数据库的数据量不大，那么使用索引也不一定能够带来很大提升。

4、什么情况下需要建索引？

  1. 经常用于查询的字段

  2. 经常用于连接的字段建立索引，可以加快连接的速度

  3. 经常需要排序的字段建立索引，因为索引已经排好序，可以加快排序查询速度

5、什么情况下不建索引？

  1. where条件中用不到的字段不适合建立索引

  2. 表记录较少

  3. 需要经常增删改

  4. 参与列计算 的列不适合建索引

  5. 区分度不高 的字段不适合建立索引，如性别等

6、索引的底层数据结构

1、hash表

哈希索引是基于哈希表实现的，对于每一行数据，存储引擎会对索引列进行哈希计算得到哈希码，并且哈希算法要尽量保证不同的列值计算出的哈希码值是不同的，将哈希码的值作为哈希表的key值，将指向数据行的指针作为哈希表的value值。这样查找一个数据的时间复杂度就是O(1)，一般多用于精确查找。

是键值对的集合，通过键(key)即可快速取出对应的值(value)，因此哈希表可以快速检索数据（接近 O（1）

  • Hash 冲突 问题

​ 也就是说多个不同的 key 最后得到的 index 相同。通常情况下，我们常用的解决办法是 链地址法 。链地址法就是将哈希冲突数据存放在链表中。就比如 JDK1.8 之前 HashMap就是通过链地址法来解决哈希冲突的。不过，JDK1.8 以后HashMap为了减少链表过长的时候搜索时间过长引入了红黑树。

  • 既然哈希表这么快， 为什么MySQL 没有使用其作为索引的数据结构呢？

1.Hash 冲突问题 ：我们上面也提到过Hash 冲突了，不过对于数据库来说这还不算最大的缺点。

2.Hash 索引不支持顺序和范围查询(Hash 索引不支持顺序和范围查询是它最大的缺点： 假如我们要对表中的数据进行排序或者进行范围查询，那 Hash 索引可就不行了。

2、B 树& B+树

B 树,全称为 多路平衡查找树 ，B+ 树是 B 树的一种变体。B 树和 B+树中的 B 是

Balanced （平衡）的意思。

目前大部分数据库系统及文件系统都采用 B-Tree 或其变种 B+Tree 作为索引结构。

进行查找操作时，首先在根节点进行二分查找，找到

key所在的指针，然后递归地在指针所指向的节点进行查找。直到查找到叶子节点，然后在叶子节点上进行二分查找，找出

key所对应的数据项。

  • B 树& B+树两者有何异同呢？

  1. B 树的所有节点既存放键(key) 也存放 数据(data)，而 B+树只有叶子节点存放 key 和 data，其他内节点只存放 key。

  2. B 树的叶子节点都是独立的;B+树的叶子节点有一条引用链指向与它相邻的叶子节点。

  3. B 树的检索的过程相当于对范围内的每个节点的关键字做二分查找，可能还没有到达叶子节点，检索就结束了。而 B+树的检索效率就很稳定了，任何查找都是从根节点到叶子节点的过程，叶子节点的顺序检索很明显。

  4. innoDB存储引擎中的页大小为16kb，一般主键类型int（占4字节）或者bigint（占8字节），指针类型也一般占4或8字节，也就是说一个页（B+Tree中的一个节点）中大概可以存储16KB/（8B+8B）=1K个键值。也就是说一个深度为3的B+Tree索引跨行业维护10^3 10^3 10^3=10亿条记录（8亿）

  5. 实际情况中，每个节点并不能【填充满，因此在数据库中，B+Tree的高度一般都在2-4层，mysql的innoDB索引引擎在设计的时候是将根节点常驻放在内存中的，也就是说查找某一键值记录最多只需要1-3次I/O操作。注意顶层页常驻内存

7、Hash索引和B+树索引的区别？

  • 哈希索引 不支持排序 ，因为哈希表是无序的。

  • 哈希索引 不支持范围查找 。

  • 哈希索引 不支持模糊查询 及多列索引的最左前缀匹配。

  • 因为哈希表中会 存在哈希冲突 ，所以哈希索引的性能是不稳定的，而B+树索引的性能是相对稳定的，每次查询都是从根节点到叶子节点。

8、为什么B+树比B树更适合实现数据库索引？

  • 由于B+树的数据都存储在叶子结点中，叶子结点均为索引，方便扫库，只需要扫一遍叶子结点即可，但是B树因为其分支结点同样存储着数据，我们要找到具体的数据，需要进行一次中序遍历按序来扫，所以B+树更加适合在区间查询的情况，而在数据库中基于范围的查询是非常频繁的，所以通常B+树用于数据库索引。

  • B+树的节点只存储索引key值，具体信息的地址存在于叶子节点的地址中。这就使以页为单位的索引中可以存放更多的节点。减少更多的I/O支出。

  • B+树的查询效率更加稳定，任何关键字的查找必须走一条从根结点到叶子结点的路。所有关键字查询的路径长度相同，导致每一个数据的查询效率相当。

9、索引有什么分类？

1、 主键索引 ：名为primary的唯一非空索引，不允许有空值。

2、 唯一索引 ：索引列中的值必须是唯一的，但是允许为空值。唯一索引和主键索引的区别是：唯一约束的列可以为null且可以存在多个null值。唯一索引的用途：唯一标识数据库表中的每条记录，主要是用来防止数据重复插入。创建唯一索引的SQL语句如下：

ALTER TABLE table_name
ADD CONSTRAINT constraint_name UNIQUE KEY(column_1,column_2,...);

3、 组合索引 ：在表中的多个字段组合上创建的索引，只有在查询条件中使用了这些字段的左边字段时，索引才会被使用，使用组合索引时需遵循最左前缀原则。

4、 普通索引（ 单值索引）

5、 全文索引 ：只有在MyISAM引擎上才能使用，只能在CHAR、VARCHAR和TEXT类型字段上使用全文索引。

10、什么是最左匹配原则？

如果 SQL 语句中用到了组合索引中的最左边的索引，那么这条 SQL 语句就可以利用这个组合索引去进行匹配。当遇到范围查询(>、<、between、like)就会停止匹配，后面的字段不会用到索引。

(a,b,c)建立索引，查询条件使用 a/ab/abc 会走索引，使用 bc 不会走索引。如果查询条件为

a = 1 and b > 2 and c = 3，那么a、b个字两段能用到索引，而c无法使用索引，因为b字段是范围查询，导致后面的字段无法使用索引。如下图，对(a, b) 建立索引，a 在索引树中是全局有序的，而 b 是全局无序，局部有序（当a相等时，会根据b进行排序）。

11、MySQL聚簇和非聚簇索引

都是B+树的数据结构

  • 1 、聚簇索引： 叶子节点将数据存储和索引存放在一起，并且是按照一定顺序组织的，找到索引也就是找到数据，数据的物理存放顺序和索引顺序是一致的。即：只要索引相邻，那么对应的数据一定也是相邻的存放在磁盘上的

  • 2 、非聚簇索引： 将数据存储和索引分开存储的，索引结构的叶子节点指向数据对应的位置

叶子节点不存储数据、存储的是数据的行地址（索引），也就是说根据索引查询到的数据行的位置，再取磁盘查找数据，这个就类似一本树的目录。

在innoDB中，在聚簇索引之上创建的索引是非聚簇索引，非聚簇索引是辅佐索引，像复合索引、前缀索引、唯一索引。辅佐索引的叶子节点存储的不再是行的物理位置，而是主键值，辅佐索引访问数据总是需要二次查找

01、InnoDB中

  • InnoDB使用的是聚簇索引，将主键组织到一个棵树中，而行数据就存储在叶子节点上，若使用“where id = 4”这样的条件查找主键，则按照B+树的检索算法，即可查找到对应的叶子节点，之后获得行数据

  • 若对name列进行条件搜索，则需要两步：

    • 第一步：在辅佐索引B+树中检索name，到达其叶子节点获取对应的主键

  • 第二部：使用主键在主键索引B+树再执行一次B+树检索操作，最终达到叶子节点可获取整行数据（重点：在于通过其他键需要建立辅佐索引）

  • 聚簇索引默认是主键，如果表中没有定义主键InnoDB会选择一个唯一且非空的索引代替。如果没有这样的索引，innoDB会隐式的定义一个主键（类似Oracle的Rowid）来作为聚簇索引。如果已经射设置了主键为聚簇索引，想希望单独设置聚簇索引，必须先删除主键，然后添加我们想要的聚簇索引，最终在恢复主键设置即可

02、MYISAM中

MYISAM使用的是非聚簇，非聚簇索引的两棵B+树看上去没有太大的区别，节点的结构完全一致，至少2存储的内瑞内容不一样。主键索引B+树的节点的存储了主键，辅佐索引B+树存储了辅佐键，表的数据在独立的地方，这两课B+树的叶子节点都使用地址指向真正的表数据，对表数据来说，这两个键没有任何差别，由于索引树是独立的，通过辅佐键检索无需访问主键的索引树

03、使用聚簇索引的优势

  • 问题：每次使用辅佐索引检索都需要经过两次的B+树查找，看上去聚簇索引的效率明显低于非聚簇索引，这不是多次一举吗，聚簇索引的优势在哪呢？

1、 由于行数据和聚簇索引的叶子节点存储在一起，在同一页会有多条行数据，访问同一数据页不同行记录时，已经把页的加载到Buffer（缓冲器），再次访问的时，会再内存中完成访问，不必再访问磁盘，这样主键和行数据是一起载入内存的，找到叶子节点就立刻将行数据返回了，如果按照ID来组织数据，获取数据更快

2、 辅佐索引的叶子节点，存储主键值，而不是数据的存放地址。好处是当行数据发生变化时，索引树的节点也需要分裂变化；或者是我们需要查找的数据，在上一次IO读写的缓存中没有，需要发送一次新的IO操作时，可以避免对辅佐索引的维护工作，只需要维护聚簇索引树就好。另外好处就是，因为辅佐索引存放的时主键值，减少辅佐索引占用的存储空间大小

05、为什么主键通常建议使用自增ID

12、什么是覆盖索引？

select的数据列只用从索引中就能够取得，不需要 回表 进行二次查询，也就是说查询列要被所使用的索引覆盖。对于

innodb表的二级索引，如果索引能覆盖到查询的列，那么就可以避免对主键索引的二次查询。

不是所有类型的索引都可以成为覆盖索引。覆盖索引要存储索引列的值，而哈希索引、全文索引不存储索引列的值，所以MySQL使用b+树索引做覆盖索引。

13、索引的设计原则？

  • 索引列的 区分度越高 ，索引的效果越好。比如使用性别这种区分度很低的列作为索引，效果就会很差。

  • 尽量使用 短索引 ，对于较长的字符串进行索引时应该指定一个较短的前缀长度，因为较小的索引涉及到的磁盘I/O较少，查询速度更快。

  • 索引不是越多越好，每个索引都需要额外的物理空间，维护也需要花费时间。

  • 利用 最左前缀原则 。

14、什么情况下无法利用索引（索引失效）

导致索引失效的情况：

  • 1、对于组合索引，不是使用组合索引最左边的字段，则不会使用索引

  • 2、查询语句中使用like关键字

以%开头的like查询如%abc，无法使用索引；非%开头的like查询如abc%，相当于范围查询，会使用索引

  • 3、查询语句中使用OR关键字

查询条件使用or连接

  • 如果前后条件的列都是索引那么可以利用索引

  • 如果前后索引中华有一列不是索引，则无法利用索引

  • 4、查询条件中列类型是字符串，没有使用引号，可能会因为类型不同发生隐式转换，使索引失效

  • 5、对索引列进行运算

15、什么是前缀索引？

有时需要在很长的字符列上创建索引，这会造成索引特别大且慢。使用前缀索引可以避免这个问题。

前缀索引是指对文本或者字符串的前几个字符建立索引，这样索引的长度更短，查询速度更快。

创建前缀索引的关键在于选择足够长的前缀以 保证较高的索引选择性 。索引选择性越高查询效率就越高，因为选择性高的索引可以让MySQL在查找时过滤掉更多的数据行。

16、常见的存储引擎有哪些？

MySQL中常用的四种存储引擎分别是： MyISAM 、 InnoDB 、 MEMORY 、 ARCHIVE 。MySQL 5.5版本后默认的存储引擎为InnoDB。

17、 Mysql锁有哪些、如何理解

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-uVWXa7Hj-1650358046863)(https://pizximfzuc.feishu.cn/space/api/box/stream/download/asynccode/?code=MjZhZDY3OTBhMzE1ODE4MGUxZTUyZTEyMjFhZjU0OTJfQVpRSDlaaERqU05HWVNrWlRwWjNzUDNobThvVzN1cUtfVG9rZW46Ym94Y25sVEtmcDFkdXYzZjVjbkJPbGkwUDRlXzE2NTAzNTc2NTY6MTY1MDM2MTI1Nl9WNA)]

18、 Mysql慢查询如何优化

19、 Explain语句结果中查询的各个字段的意思

20、 Innodb如何实现事务的

Innodb通过Buffer Pool，Redo Log，Undo Log来实现事务，以一个update语句为例：

  • innodb在收到一个update语句后，会根据条件找到数据所在叶。并将该页缓存在buffer Pool中

  • 执行update语句，修改Buffer Pool中的数据，也就是内存中的数据

  • 针对update语句生成一个RedoLog对象，兵存入LogBuffer中

  • 针对update语句生成的undoLog日志，用于事务回滚

  • 如果事务提交，则把RedoLog日对象进行持久化，后续还有其他机制将Buffer Pool中修改的数据页持久化到磁盘中

  • 如果事务回滚，则利用undoLog日志进行回滚

21、 Redis和Mysql如何保证数据一致

1、先更新Mysql，再更新Redis，如果redis失败，依旧可能数据不一致

2、先删除redis中数据，再更新mysql，再次查询的时候再更新数据添加到缓存中。（这个方案解决了1的问题）。但是再高并发的情况下效率比较低下，并且还是可能有数据不一致的可能。比如线程1删除后，再更新msql时，但是此时有一个线程2进来查询时，就会将mysql中老数据又查询到redis中。

3、``延迟双删。步骤：先删除redis中的数据，再更新mysql，再隔几百毫秒再删除redis中缓存数据。这样就算在更新mysql时，有其他线程读取mysql时，把老数据读到redis中也会很快被删掉

22、 索引的基本原理

索引是用来快速查询有特定值的记录。如果没有索引，一般来说执行查询时候需要查询全表

索引基本原理： 把无序的数据变成有序的查询

  • 1、把创建索引的列的内容进行排序

  • 2、对排序结果进行倒排列

  • 3、在倒排表后面内容上拼接上数据地址链

  • 4、在查询的时候，先拿到倒排表内容，再取出数据地址链，从而就取出具体数据

23、 MyISAM和innoDb的区别

MyISAM:

  • 不支持事务，但是每一次的查询都是原子的

  • 支持表级锁，即每次操作都会对表进行加锁

  • 存储表的总行数

  • 一个MyISAM表有三个文件：索引文件、表结构文件、数据文件

  • 采用非聚簇索引，索引文件的数据有存储指向数据文件的指针。辅助索引与主索引基本一致，但是辅助索引不用保证唯一性

InnoDb：

  • 支持ACID的事务。支持事务的四种隔离级别

  • 支持行级锁与外键约束：因此可以支持写并发

  • 不存储总行数

  • 一个InnoDb引擎存储在一个文件空间（共享表空间。表大小不受操作系统的控制，一个表可能分布在多个文化里），也可能为多个（设置为独立表空，表大小受操作系统文件大小的 控制，一般为2G）。受操作系统文件大小的限制

  • 主键索引采用聚簇索引（索引的数据域存储数据文件本身），辅助索引的数据域存储主键的值；因此从辅助索引查找数据，需要先通过辅佐索引找到主键值，再访问主索引；最好使用自增主键。防止插入数据时，为维护B+树结构，文件的大整

24、ACID靠什么保证的？

  • A原子性由undo log日志保证，他记录了需要回滚的日志信息，事务回滚时撤销已近执行成功的sql

  • C 一致性由其他三大特性保证、数据上的一致需要程序代码要保证业务上的一致性

  • I 隔离性 由MVCC来保证的

  • D 持久性 由内存和redo log来保证，mysql修改数据同时在内存和redo log记录这次操作。嘎机的时候可以从redo log恢复

redo log的刷盘会在系统空闲时候进行

25、 什么是MVCC

多版本并发控制：读取数据时通过一种类似快照的方式将数据保存下来，这样读锁和写锁就不冲突了，不同的事务session会看到自己的特定版本的数据，版本链

MVCC只在READ COOMMITED 和REPETABLE READ 两个隔离级别下工作。其他两个隔离级别和MVCC冲突不兼容，因为READ UNCOMMITED 总是读取最新的数据行，而不是 符合当前事务版本的数据行。而SESRIALIZABLE则会对所有的读取行加锁

聚簇索引记录中有两个必要的隐藏列

trx_id：用来存储每次对某条聚簇索引记录进行修改的时候事务id

roll_pointer：每次对哪条索引记录进行修改的时候，都会把老版本写入undo日志中，这个roll_pointer就是保存了一个指针，它指向这条聚簇索引记录的上一个版本的位置，通过它来获取上一个版本的记录信息。（注意插入操作的undo日志没有这个属性，因为它没有老版本）

使用事务更新行记录的时候，就会生成版本链，执行过程如下：

  1. 用排他锁锁住该行；

  2. 将该行原本的值拷贝到

undo log，作为旧版本用于回滚；

  1. 修改当前行的值，生成一个新版本，更新事务id，使回滚指针指向旧版本的记录，这样就形成一条版本链。

已提交读和可重复读的区别在于他们生成的ReadView的策瑜不同

无法复制加载中的内容

接下来了解下read view的概念。

read view可以理解成将数据在每个时刻的状态拍成“照片”记录下来。在获取某时刻t的数据时，到t时间点拍的“照片”上取数据。

在read view内部维护一个活跃事务链表，表示生成read view的时候还在活跃的事务。这个链表包含在创建read view之前还未提交的事务，不包含创建read view之后提交的事务。

不同隔离级别创建read view的时机不同。

  • read committed：每次执行select都会创建新的read_view，保证能读取到其他事务已经提交的修改。

  • repeatable read：在一个事务范围内，第一次select时更新这个read_view，以后不会再更新，后续所有的select都是复用之前的read_view。这样可以保证事务范围内每次读取的内容都一样，即可重复读。

总结 ：InnoDB 的MVCC是通过 read view 和版本链实现的，版本链保存有历史版本记录，通过read view 判断当前版本的数据是否可见，如果不可见，再从版本链中找到上一个版本，继续进行判断，直到找到一个可见的版本。

26、关心过业务系统里面的sql耗时吗？统计过慢查询吗？对慢查询都怎么优化？

在业务系统中，除了使用主键进行查询，还有其他的在测试库上测试其耗时，慢查询的统计一般主要由运维在做，会定期的将业务中的慢查询反馈给我们、慢查询的优化首先需要慢的原因是什么？是查询条件没有命中索引？是load了需要的数据列，还是数据量过大？

所以优化也是针对这三个方向来的。

  • 首先分析语句，看看是不是load了额外不需要的数据，可能是查询了多余的行并且抛弃掉了，可能是加载了许多结果中 炳不需要的列，对语句进行分析以及重写

  • 分析语句的执行计划，获得其使用索引的情况，之后修改语句或者索引，使得语句可以尽可能的命中索引

  • 如果语句的优化已经无法进行，可以考虑表中的数据量是否过大，如果是的话可以进行考虑分表

25、mysql中char与varchar的区别

都是用来存储字符串的，只是他们的保存方式不一样罢了

  • char有固定的长度，而varchar属于可变长的字符类型。char 长度是固定的，不管你存储的数据是多少他都会都固定的长度。而varchar则处可变长度但他要在总长度上加1字符，这个用来存储位置

26、 mysql中的delete , drop 和truncate 区别

  • 1、delete和truncate仅仅删除表数据，trop连表数据和结构一起删除，打个比方delete 是单杀，truncate 是团灭，drop 是把电脑摔了。

  • 2、delete 是 DML 语句，操作完以后如果没有不想提交事务还可以回滚，truncate 和 drop 是 DDL 语句，操作完马上生效，不能回滚，打个比方，delete 是发微信说分手，后悔还可以撤回，truncate 和 drop 是直接扇耳光说滚，不能反悔。

  • 3、执行的速度上， drop>truncate>delete ，打个比方，drop 是神舟火箭，truncate 是和谐号动车，delete 是自行车。

delete 是删除一条数据，truncate是将这个表的所有数据都删除，这两种不删除表的结构，可以用

27、关于sql和MySQL的语句执行顺序

1、sql执行顺序

  1. from

  2. join

  3. on

  4. where

  5. group by(开始使用select中的别名，后面的语句中都可以使用)

  6. avg,sum....

  7. having

  8. select

  9. distinct

  10. order by

  11. limit

从这个顺序中我们不难发现，所有的 查询语句都是从from开始执行的，在执行过程中，每个步骤都会为下一个步骤生成一个虚拟表，这个虚拟表将作为下一个执行步骤的输入。

第一步：首先对from子句中的前两个表执行一个笛卡尔乘积，此时生成虚拟表 vt1（选择相对小的表做基础表）。

第二步：接下来便是应用on筛选器，on 中的逻辑表达式将应用到 vt1 中的各个行，筛选出满足on逻辑表达式的行，生成虚拟表 vt2 。

第三步：如果是outer join 那么这一步就将添加外部行，left outer jion 就把左表在第二步中过滤的添加进来，如果是right outer join 那么就将右表在第二步中过滤掉的行添加进来，这样生成虚拟表 vt3 。

第四步：如果 from 子句中的表数目多余两个表，那么就将vt3和第三个表连接从而计算笛卡尔乘积，生成虚拟表，该过程就是一个重复1-3的步骤，最终得到一个新的虚拟表 vt3。

第五步：应用where筛选器，对上一步生产的虚拟表引用where筛选器，生成虚拟表vt4，在这有个比较重要的细节不得不说一下，对于包含outer join子句的查询，就有一个让人感到困惑的问题，到底在on筛选器还是用where筛选器指定逻辑表达式呢？on和where的最大区别在于，如果在on应用逻辑表达式那么在第三步outer join中还可以把移除的行再次添加回来，而where的移除的最终的。举个简单的例子，有一个学生表（班级,姓名）和一个成绩表(姓名,成绩)，我现在需要返回一个x班级的全体同学的成绩，但是这个班级有几个学生缺考，也就是说在成绩表中没有记录。为了得到我们预期的结果我们就需要在on子句指定学生和成绩表的关系（学生.姓名=成绩.姓名）那么我们是否发现在执行第二步的时候，对于没有参加考试的学生记录就不会出现在vt2中，因为他们被on的逻辑表达式过滤掉了,但是我们用left outer join就可以把左表（学生）中没有参加考试的学生找回来，因为我们想返回的是x班级的所有学生，如果在on中应用学生.班级="x"的话，left outer join会把x班级的所有学生记录找回（感谢网友康钦谋__康钦苗的指正），所以只能在where筛选器中应用学生.班级="x" 因为它的过滤是最终的。

第六步：group by 子句将中的唯一的值组合成为一组，得到虚拟表vt5。如果应用了group by，那么后面的所有步骤都只能得到的vt5的列或者是聚合函数（count、sum、avg等）。原因在于最终的结果集中只为每个组包含一行。这一点请牢记。

第七步：应用cube或者rollup选项，为vt5生成超组，生成vt6.

第八步：应用having筛选器，生成vt7。having筛选器是第一个也是为唯一一个应用到已分组数据的筛选器。

第九步：处理select子句。将vt7中的在select中出现的列筛选出来。生成vt8.

第十步：应用distinct子句，vt8中移除相同的行，生成vt9。事实上如果应用了group by子句那么distinct是多余的，原因同样在于，分组的时候是将列中唯一的值分成一组，同时只为每一组返回一行记录，那么所以的记录都将是不相同的。

第十一步：应用order by子句。按照order_by_condition排序vt9，此时返回的一个游标，而不是虚拟表。sql是基于集合的理论的，集合不会预先对他的行排序，它只是成员的逻辑集合，成员的顺序是无关紧要的。对表进行排序的查询可以返回一个对象，这个对象包含特定的物理顺序的逻辑组织。这个对象就叫游标。正因为返回值是游标，那么使用order by 子句查询不能应用于表表达式。排序是很需要成本的，除非你必须要排序，否则最好不要指定order by，最后，在这一步中是第一个也是唯一一个可以使用select列表中别名的步骤。

第十二步：应用top选项。此时才返回结果给请求者即用户。

2、mysql的执行顺序

1、SELECT语句定义

一个完成的SELECT语句包含可选的几个子句。SELECT语句的定义如下：

SQL代码

<SELECT clause> [<FROM clause>] [<WHERE clause>] [<GROUP BY clause>] [<HAVING clause>] [<ORDER BY clause>] [<LIMIT clause>]

2、SELECT语句执行顺序

SELECT语句中子句的执行顺序与SELECT语句中子句的输入顺序是不一样的，所以并不是从SELECT子句开始执行的，而是按照下面的顺序执行：

开始->FROM子句->WHERE子句->GROUP BY子句->HAVING子句->ORDER BY子句->SELECT子句->LIMIT子句->最终结果

28、事务的四大特性

事务特性ACID ： 原子性 （Atomicity）、 一致性 （Consistency）、 隔离性 （Isolation）、 持久性 （Durability）。

  • 原子性 是指事务包含的所有操作要么全部成功，要么全部失败回滚。

  • 一致性 是指一个事务执行之前和执行之后都必须处于一致性状态。比如a与b账户共有1000块，两人之间转账之后无论成功还是失败，它们的账户总和还是1000。

  • 隔离性 。跟隔离级别相关，如read committed，一个事务只能读到已经提交的修改。

  • 持久性 是指一个事务一旦被提交了，那么对数据库中的数据的改变就是永久性的，即便是在数据库系统遇到故障的情况下也不会丢失提交事务的操作。

29、 大表怎么优化？

某个表有近千万数据，查询比较慢，如何优化？

当MySQL单表记录数过大时，数据库的性能会明显下降，一些常见的优化措施如下：

  • 限定数据的范围。比如：用户在查询历史信息的时候，可以控制在一个月的时间范围内；

  • 读写分离：经典的数据库拆分方案，主库负责写，从库负责读；

  • 通过分库分表的方式进行优化，主要有垂直拆分和水平拆分。

30、bin log/redo log/undo log

mysql日志主要有查询日志、慢查询日志、事务日志、错误日志、二进制日志。其中比较中亚的是bin log（二进制日志）、redo log（重做日志）、undo log（回滚日志）

  • bin log

bin log是MySQL数据库级别的文件 ，记录对MySQL数据库执行修改的所有操作，不会记录select等语句，主要用于恢复数据库和同步数据库

  • redo log

redo log 是innodb引擎级别，用来记录innodb存储引擎的事务日志，不管事务是否提交都会记录下来，用于数据恢复。当数据库发生故障，innodb存储引擎会使用redo log恢复到发生故障前时刻，以此来保证数据的完整性。将参数innodb_flush_log_at_tx_commit设置为1，那么在执行commit时会将redo log同步写到磁盘。

  • undo log

除了记录redo log外。当进行数据修改时还会记录undo log, undo log用于数据的撤回操作，他保留了记录修改前的内容。通过undo log可实现是事务的回滚，并且可以根据undo log回溯到某个特定的版本数据，实现 MVCC

31、bin log和redo log有什么区别？

  1. bin log会记录所有日志记录，包括InnoDB、MyISAM等存储引擎的日志；redo log只记录innoDB自身的事务日志。

  2. bin log只在事务提交前写入到磁盘，一个事务只写一次；而在事务进行过程，会有redo log不断写入磁盘。

  3. bin log是逻辑日志，记录的是SQL语句的原始逻辑；redo log是物理日志，记录的是在某个数据页上做了什么修改。

32、 分库分表

当单表的数据量达到1000W或100G以后，优化索引、添加从库等可能对数据库性能提升效果不明显，此时就要考虑对其进行切分了。切分的目的就在于减少数据库的负担，缩短查询的时间。

数据切分可以分为两种方式：垂直划分和水平划分。

  • 垂直划分

垂直划分数据库是根据业务进行划分，例如购物场景，可以将库中涉及商品、订单、用户的表分别划分出成一个库，通过降低单库的大小来提高性能。同样的，分表的情况就是将一个大表根据业务功能拆分成一个个子表，例如商品基本信息和商品描述，商品基本信息一般会展示在商品列表，商品描述在商品详情页，可以将商品基本信息和商品描述拆分成两张表。

优点 ：行记录变小，数据页可以存放更多记录，在查询时减少I/O次数。

缺点 ：

优点 ：行记录变小，数据页可以存放更多记录，在查询时减少I/O次数。

缺点 ：

  • 主键出现冗余，需要管理冗余列；

  • 会引起表连接JOIN操作，可以通过在业务服务器上进行join来减少数据库压力；

  • 依然存在单表数据量过大的问题。

  • 水平划分

水平划分是根据一定规则，例如时间或id序列值等进行数据的拆分。比如根据年份来拆分不同的数据库。每个数据库结构一致，但是数据得以拆分，从而提升性能。

优点 ：单库（表）的数据量得以减少，提高性能；切分出的表结构相同，程序改动较少。

缺点 ：

  • 分片事务一致性难以解决

  • 跨节点join性能差，逻辑复杂

  • 数据分片在扩容时需要迁移

33、 having和where的区别？

  • 二者作用的对象不同，where子句作用于表和视图，having作用于组。

  • where在数据分组前进行过滤，having在数据分组后进行过滤。

34、 乐观锁和悲观锁是什么？

数据库中的并发控制是确保在多个事务同时存取数据库中同一数据时不破坏事务的隔离性和统一性以及数据库的统一性。乐观锁和悲观锁是并发控制主要采用的技术手段。

  • 悲观锁：假定会发生并发冲突，在查询完数据的时候就把事务锁起来，直到提交事务。实现方式：使用数据库中的锁机制。

  • 乐观锁：假设不会发生并发冲突，只在提交操作时检查是否数据是否被修改过。给表增加version字段，在修改提交之前检查version与原来取到的version值是否相等，若相等，表示数据没有被修改，可以更新，否则，数据为脏数据，不能更新。实现方式：乐观锁一般使用版本号机制或CAS算法实现。

以上是 最新Mysql大厂面试必会的34问题 的全部内容， 来源链接： utcz.com/z/536432.html

回到顶部