admin管理员组

文章数量:1570208

目录

1.什么是索引？

2.为什么要使用索引？

3.常用的数据结构？

4.索引采用的数据结构是什么？

5.索引为什么要使用B+树的数据结构？

6.哪些字段适合建立索引？

7.哪些情况会引起全表扫描，索引失效？ 

8.什么是数据库事务？ 

9.事务的隔离级别有哪些？ 

10.锁表发生的原因有哪些？

11.出现锁表如何解决？

12.MySQL的引擎类型及区别？

13.当数据库查询慢时，我们从哪几个方面排查？ 

14.MyISAM和InnoDB存储引擎？ 

15.为什么建议InnoDB表必须建主键，并且推荐使用整型的自增主键？如果不建主键会发生什么？

16.什么事SQL注入？ 

17.如何防止SQL注入？ 

18.如何优化SQL? 

19.MySQL中什么是回表，什么是覆盖索引，索引下推？ 

20.关系型和非关系型数据库的区别？

 21.详细说一下一条 MySQL 语句执行的步骤？

22.MySQL使用索引的原因？

23.索引的三种常见底层数据结构以及优缺点？

 24.索引的常见类型以及它是如何发挥作用的？

25.MyISAM 和 InnoDB 实现 B 树索引方式的区别是什么？

26.InnoDB 为什么设计 B+ 树索引？

27.什么是覆盖索引和索引下推？

28.字符串加索引？

29.MySQL 的 change buffer 是什么？

31.MySQL的redo log和binlog的区别？

32.为什么需要redo log？

33.为什么 redo log 具有 crash-safe 的能力，是 binlog 无法替代的？ 

34.当数据库 crash 后，如何恢复未刷盘的数据到内存中？ 

35.redo log 写入方式？ 

36.redo log 的执行流程? 

37.binlog 的概念是什么，起到什么作用， 可以保证 crash-safe 吗? 

38.什么是两阶段提交？

39.MySQL 怎么知道 binlog 是完整的?

40.什么是 WAL 技术，有什么优点？

41.binlog 日志的三种格式？

42.redo log日志格式？

43.原本可以执行得很快的 SQL 语句，执行速度却比预期的慢很多，原因是什么？如何解决？

44. InnoDB 数据页结构?

45.MySQL 是如何保证数据不丢失的？ 

46.误删数据怎么办？ 

47. drop、truncate 和 delete 的区别？

48.在 MySQL 中有两个 kill 命令？

49.如何理解 MySQL 的边读边发？

50.MySQL 的大表查询为什么不会爆内存？ 

51.MySQL 临时表的用法和特性？

52.MySQL 存储引擎介绍（InnoDB、MyISAM、MEMORY）？

53.都说 InnoDB 好，那还要不要使用 MEMORY引擎？

54.如果数据库误操作, 如何执行数据恢复?

55.MySQL 是如何保证主备同步？

56.什么是主备延迟？

57.为什么要有多线程复制策略？

58.MySQL 的并行策略有哪些？

59.MySQL的一主一备和一主多从有什么区别？ 

60.主库出问题如何解决? 

61.MySQL 读写分离涉及到过期读问题的几种解决方案?

62.MySQL的并发链接和并发查询有什么区别？

 63.短时间提高 MySQL 性能的方法?

64.为什么 MySQL 自增主键 ID 不连续？

65.InnoDB 为什么要用自增 ID 作为主键？

67.grant 和 flush privileges语句?

68.要不要使用分区表？

69.join 用法?

70.MySQL 有哪些自增ID？各自场景是什么？

71.Xid 在 MySQL 内部是怎么生成的呢？

72.说一下 MySQL 的锁？

73.为什么 MySQL 会抖一下？

74.为什么删除了表，表文件的大小还是没变？ 

75.orderby 排序内部原理？

76.如何高效的使用 MySQL 显式随机消息？

77.Mysql数据库主从那点事？

1.什么是索引？

索引其实是一种数据结构，能够帮助我们快速的检索数据库中的数据。是帮助MySQL高效获取数据的，排好序的数据结构。

2.为什么要使用索引？

1.索引大大减少了存储引擎需要扫描的数据量，提高查询检索效率和速度，提高用户体验。

2.确保数据的唯一性。

3.常用的数据结构？

二叉树

红黑树

Hash表

B-Tree树

4.索引采用的数据结构是什么？

索引采用B+树的数据结构。

B-Tree（B树）：

（1）叶节点具有相同的深度，叶节点的指针为空；

（2）所有索引元素不重复；

（3）节点中的数据索引从左到右递增排列。

B+Tree（B-Tree变种） 

（1）非叶子节点不存储data，只存储索引（冗余），可以放更多的索引；

（2）叶子节点包含所有索引字段；

（3）叶子节点用指针连接，提高区间访问的性能。

（4）叶子结点的大小是16KB，可是使用show GLOBAL STATUS like 'Innodb page size'  命令查看。

5.索引为什么要使用B+树的数据结构？

1.B+树能够显著的减少磁盘的IO操作次数，能提高效率。

B+树的内部节点并没有指向关键字具体信息的指针，因此其内部节点相对B树更小，如果把所有同一内部节点的关键字存放在同一盘块中，那么盘块所能容纳的关键字数量也越多，一次性读入内存的需要查找的关键字也就越多，相对IO读写次数就降低了。

2.B+树的查询效率更加稳定。

由于非终结点并不是最终指向文件内容的结点，而只是叶子结点中关键字的索引。所以任何关键字的查找必须走一条从根结点到叶子结点的路。所有关键字查询的路径长度相同，导致每一个数据的查询效率相当。

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

4.B+树能提高范围查询的效率，因为叶子节点指向下一个叶子节点。

5.B树在提高了IO性能的同时并没有解决元素遍历的我效率低下的问题，正是为了解决这个问题，B+树应用而生。B+树只需要去遍历叶子节点就可以实现整棵树的遍历。而且在数据库中基于范围的查询是非常频繁的，而B树不支持这样的操作或者说效率太低。

6.哪些字段适合建立索引？

1.数据量比较大，超过300，适合建立索引。

2.关联查询的表，建立索引，提高查询速度。

3.存储小数据的字段，便于索引查询。

4.使用频率高，选择性高的字段。

5.频繁进行数据操作的表，不要建立太多的索引。

6.经常出现在Where子句中的字段。 

7.哪些情况会引起全表扫描，索引失效？ 

1.全值匹配我最爱。 

select * from user where name='July'; 

select * from user where name='July' and age='30';

2.最佳左前缀法则。

如果索引了多个列，要遵守最佳左前缀法则。指的是查询从索引的最左前列开始，并且不跳过索引中的列。

3.不要在索引上做计算。 

select * from user where left(name,4) = 'July'

4.范围条件右边的列失效。 

不能继续使用索引中范围条件（between、<、>、in等）右边的列。

select * from user where name='July' and age > 25 and pos='manager' 

5.尽量使用覆盖索引。 

尽量使用覆盖索引（只查询索引的列），也就是索引列和查询列一致，减少 select *。

select age,name from user where name='July' and age=25 and pos='manager' 

6.索引字段上不要使用不等。 

索引字段上使用（!=  或者  <>）判断时，会导致索引失效而转向全表扫描。

select * from user where name != 'July'

select * from user where name <> 'July'

7.索引字段上不要判断null,如 is null  或者 is not null这样会引起索引失效，转向全表扫描。 

8.索引字段使用like不以通配符开头。 

索引字段使用like以通配符开头（'%字符串'）时，会导致索引失效而转向全表扫描。 

错误示范：select * from user where name like '%July'        

正确示范：select * from user where name like 'July%' 

由结果可知，like以通配符结束相当于范围查找，索引不会失效。与范围条件（between、<、>、in等）不同的是，不会导致右边的索引失效。

问题：解决like '%字符串%'时，索引失效问题的方法？ 

使用覆盖索引可以解决。 

select name,age from user where name like '%July%'

 select id,name,age from user where name like '%July%'

9.索引字段字符串要加单引号。

索引字段是字符串，但查询时不加单引号，会导致索引失效而转向全表扫描。  

正确示范：select * from user where name='20000'

错误示范：select * from user where name=2000 

10.索引字段不要使用or。

索引字段使用or时，会导致索引失效而转向全表扫描。 

select * from user where name='July' or age='23' 

8.什么是数据库事务？ 

ACID，指数据库事务正确执行的四个基本要素的缩写。

1、原子性（Atomicity）

一个事务中的所有操作，要不操作全部成功，要不全部失败，不能存在中间态。

2、一致性（Consistency）

事务必须使得数据库从一个一致性状态转变到另一个一致性状态。比如银行转账，A账户转到B账户，不管转几次，A和B账户的总额不能变。

3、隔离性（Isolation）

是指多个用户同时请求数据库，开启多个事务同时处理某个数据库，隔离性保证了各个事务之间均不受干扰，每个事务都感觉不到其他事务的存在。

4、持久性（Durability）

对数据库的修改是持久性的，一旦修改，就算数据库系统出现故障，这种修改也不会丢失，这点是数据库数据存放到硬盘中，并有redolog和binlog一起保证的。

9.事务的隔离级别有哪些？ 

1、读未提交(Read uncommitted)

读未提交其实就是事务没提交就可以读，很显然这种隔离级别会导致读到别的还没提交的数据，
一旦基于读到的数据做了进一步处理，而另一个事务最终回滚了操作，那么数据就会错乱，而且很难追踪。总的来说说，读未提交级别会导致脏读。

2、读已提交(Read committed)

顾名思义就是事务提交后才能读，假设你拿着银行卡去消费，付钱之前你看到卡里有2000元，这个时候你老婆在淘宝购物，赶在你前面完成了支付，这个时候你再支付的时候就提示余额不足，但是分明你看到卡里的钱是够的啊。

这就是两个事务在执行时，事务A一开始读取了卡里有2000元，这个时候事务B把卡里的钱花完了，事务A最终再确认余额的时候发现卡里已经没有钱了。很显然，读提交能解决脏读问题，但是解决不了不可重复读。

Sql Server，Oracle的默认隔离级别是Read committed

3、可重复读(Repeatable read)

看名字就看出来了，它的出现就是为了解决不可重复读问题，事务A一旦开始执行，无论事务B怎么改数据，事务A永远读到的就是它刚开始读的值。那么问题就来了，假设事务B把id为1的数据改成了2，事务A并不知道id发生了变化，当事务A新增数据的时候却发现为2的id已经存在了，这就是幻读。

MySQL的默认隔离级别就是Repeatable read。

4、串行化(Serializable)

这个就是最无敌的存在了，所有的事务串起来一个个执行，因为没有并发的场景出现了，什么幻读、脏读、不可重复读统统都不存在的。但是同样的，基本并发能力会非常差。最终，到底什么隔离级别完全要根据自己的业务场景选择，没有最好的，只有最适合的。

10.锁表发生的原因有哪些？

1.字段不加索引：在执行事务的时候，如果表中没有索引，会执行全表扫描，如果这个时候有其他的事务过来，就会发生锁表。

2.事务处理时间过长：事务处理时间过长，当越来越多的事务堆积的时候，会发生锁表。

3.关联操作太多：涉及到很多张表的修改等，在并发量大的时候，会造成大量表数据被锁。

11.出现锁表如何解决？

1.相关的sql语句可以查出表是否被锁和被锁定的数据。

2.为加锁进行时间限定，防止无限死锁。

3.加索引，避免全表扫描。

4.尽量顺序操作数据。

5.根据引擎选择合理的锁粒度。

6.事务中的处理时间尽量缩短。

12.MySQL的引擎类型及区别？

MySQL数据库引擎及区别_后青春的诗-CSDN博客

13.当数据库查询慢时，我们从哪几个方面排查？ 

https://blog.csdn/qq_26420601/article/details/122621714

14.MyISAM和InnoDB存储引擎？ 

1.MyISAM索引文件和数据文件是分离的（非聚集） 

例如我们创建一个表，并指定存储引擎为MyISAM 

create Table test_myisam (
id int(11) NOT NULL AUTO_INCREMENT,
name VARCHAR(255) DEFAULT NULL,
PRIMARY KEY (id)
) ENGINE=MyISAM AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;

 查看表底层存储文件：test_myisam.MYD和test_myisam.MYI两个文件，如下图

MYD含义：MyISAM Data  表示存储表的数据结构

MYI含义：MyISAM Index   表示存储表的索引结构

 2.InnoDB数据存储结构（聚集）

  • 表数据文件本身就是按B+Tree组织的一个索引结构文件
  • 聚集索引——叶子节点包含了完整的数据记录 

 例如我们创建一个表，并指定存储引擎为InnoDB

create Table test_innodb (
id int(11) NOT NULL AUTO_INCREMENT,
name VARCHAR(255) DEFAULT NULL,
PRIMARY KEY (id)
) ENGINE=INNODB DEFAULT CHARSET=utf8;

 查看表底层存储文件：test_innodb.ibd文件，如下图

idb含义：Index Database  表示表的索引和数据结构是存放在一起的

15.为什么建议InnoDB表必须建主键，并且推荐使用整型的自增主键？如果不建主键会发生什么？

MySQL底层数据结构使用了B+树的数据结构来做表的数据记录，这就决定了，如果没有建主键，那么表的数据结构没发进行组织，这个时候如果你不建立主键，那么MySQL数据库底层会帮你自动建立主键，它会从你的表记录里面找一列可以作为主键的字段来指定为该表的主键唯一索引，这样的话就可以自动组织成B+树的数据结构来进行表数据的记录。如果整张表中都找不到一列能作为主键的字段又该怎么办尼，这是MySQL会在后台自动帮你维护一列字段作为该表的主键存在，类似于RowId,这个你是看不见的，隐藏的，然后可以自动组织成B+树的数据结构来进行表数据的记录。

如果你不去建立数据表的主键而让MySQL数据库自动去建立表的主键的话，这样会加大数据库的性能开销，而这样做是不合理的，所以我们必须要去建立表的主键。

16.什么事SQL注入？ 

就是通过把SQL命令插入到Web表单提交或输入域名或页面请求的查询字符串，最终达到欺骗服务器执行恶意的SQL命令。 

17.如何防止SQL注入？ 

  1.  采用PreparedStatement来避免sql注入，会自动对用户填写的数据进行验证(简单有效)，sql注入只对sql语句的准备(编译)过程有破坏作用，而PreparedStatement已经准备好了,执行阶段只是把输入串作为数据处理,而不再对sql语句进行解析,准备,因此也就避免了sql注入问题。
  2. 使用正则表达式过滤传入的参数（典型的SQL 注入攻击的正则表达式 ）。
  3. 字符串过滤，敏感词过滤。

18.如何优化SQL? 

1、在表中建立索引，优先考虑where、group by使用到的字段。

2、尽量避免使用select *，返回无用的字段会降低查询效率。如下：

SELECT * FROM t 

优化方式：使用具体的字段代替*，只返回使用到的字段。

3、尽量避免使用in 和not in，会导致数据库引擎放弃索引进行全表扫描。如下：

SELECT * FROM t WHERE id IN (2,3)

SELECT * FROM t1 WHERE username IN (SELECT username FROM t2)

优化方式：如果是连续数值，可以用between代替。如下：

SELECT * FROM t WHERE id BETWEEN 2 AND 3

如果是子查询，可以用exists代替。如下：

SELECT * FROM t1 WHERE EXISTS (SELECT * FROM t2 WHERE t1.username = t2.username)

4、尽量避免使用or，会导致数据库引擎放弃索引进行全表扫描。如下：

SELECT * FROM t WHERE id = 1 OR id = 3

优化方式：可以用union代替or。如下：

SELECT * FROM t WHERE id = 1
UNION
SELECT * FROM t WHERE id = 3

（PS：如果or两边的字段是同一个，如例子中这样。貌似两种方式效率差不多，即使union扫描的是索引，or扫描的是全表）

5、尽量避免在字段开头模糊查询，会导致数据库引擎放弃索引进行全表扫描。如下：

SELECT * FROM t WHERE username LIKE '%li%'

优化方式：尽量在字段后面使用模糊查询。如下：

SELECT * FROM t WHERE username LIKE 'li%'

6、尽量避免进行null值的判断，会导致数据库引擎放弃索引进行全表扫描。如下：

SELECT * FROM t WHERE score IS NULL

优化方式：可以给字段添加默认值0，对0值进行判断。如下：

SELECT * FROM t WHERE score = 0

7、尽量避免在where条件中等号的左侧进行表达式、函数操作，会导致数据库引擎放弃索引进行全表扫描。如下：

SELECT * FROM t2 WHERE score/10 = 9

SELECT * FROM t2 WHERE SUBSTR(username,1,2) = 'li'

优化方式：可以将表达式、函数操作移动到等号右侧。如下：

SELECT * FROM t2 WHERE score = 10*9

SELECT * FROM t2 WHERE username LIKE 'li%'

8、当数据量大时，避免使用where 1=1的条件。通常为了方便拼装查询条件，我们会默认使用该条件，数据库引擎会放弃索引进行全表扫描。如下：

SELECT * FROM t WHERE 1=1

优化方式：用代码拼装sql时进行判断，没where加where，有where加and。

19.MySQL中什么是回表，什么是覆盖索引，索引下推？ 

 https://zhuanlan.zhihu/p/401198674

20.关系型和非关系型数据库的区别？

关系型数据库的优点:

  • 容易理解，因为它采用了关系模型来组织数据。
  • 可以保持数据的一致性。
  • 数据更新的开销比较小。
  • 支持复杂查询（带 where 子句的查询） 

非关系型数据库（NOSQL）的优点:

  • 无需经过 SQL 层的解析，读写效率高。
  • 基于键值对，读写性能很高，易于扩展。
  • 可以支持多种类型数据的存储，如图片，文档等等。
  • 扩展（可分为内存性数据库以及文档型数据库，比如 Redis，MongoDB，HBase 等，适合场景：数据量大高可用的日志系统/地理位置存储系统）。

 21.详细说一下一条 MySQL 语句执行的步骤？

Server 层按顺序执行 SQL 的步骤为：

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

22.MySQL使用索引的原因？

 根本原因：

  • 索引的出现，就是为了提高数据查询的效率，就像书的目录一样。
  • 对于数据库的表而言，索引其实就是它的“目录”。

扩展：

  • 创建唯一性索引，可以保证数据库表中每一行数据的唯一性。
  • 帮助引擎层避免排序和临时表。
  • 将随机 IO 变为顺序 IO，加速表和表之间的连接。

23.索引的三种常见底层数据结构以及优缺点？

三种常见的索引底层数据结构：分别是哈希表、有序数组和搜索树。

  • 哈希表这种适用于等值查询的场景，比如 memcached 以及其它一些 NoSQL 引擎，不适合范围查询。
  • 有序数组索引只适用于静态存储引擎，等值和范围查询性能好，但更新数据成本高。
  • N 叉树由于读写上的性能优点以及适配磁盘访问模式以及广泛应用在数据库引擎中。
  • 扩展（以 InnoDB 的一个整数字段索引为例，这个 N 差不多是 1200。棵树高是 4 的时候，就可以存 1200 的 3 次方个值，这已经 17 亿了。考虑到树根的数据块总是在内存中的，一个 10 亿行的表上一个整数字段的索引，查找一个值最多只需要访问 3 次磁盘。其实，树的第二层也有很大概率在内存中，那么访问磁盘的平均次数就更少了。）

 24.索引的常见类型以及它是如何发挥作用的？

根据叶子节点的内容，索引类型分为主键索引和非主键索引。

  • 主键索引的叶子节点存的整行数据，在InnoDB里也被称为聚簇索引。
  • 非主键索引叶子节点存的主键的值，在InnoDB里也被称为二级索引。

25.MyISAM 和 InnoDB 实现 B 树索引方式的区别是什么？

  • InnoDB 存储引擎：B+ 树索引的叶子节点保存数据本身，其数据文件本身就是索引文件。
  • MyISAM 存储引擎：B+ 树索引的叶子节点保存数据的物理地址，叶节点的 data 域存放的是数据记录的地址，索引文件和数据文件是分离的。

26.InnoDB 为什么设计 B+ 树索引？

两个考虑因素：

  • InnoDB 需要执行的场景和功能需要在特定查询上拥有较强的性能。
  • CPU 将磁盘上的数据加载到内存中需要花费大量时间。

为什么选择 B+ 树：

  • 哈希索引虽然能提供O（1）复杂度查询，但对范围查询和排序却无法很好的支持，最终会导致全表扫描。
  • B 树能够在非叶子节点存储数据，但会导致在查询连续数据可能带来更多的随机 IO。
  • 而 B+ 树的所有叶节点可以通过指针来相互连接，减少顺序遍历带来的随机 IO。
  • 普通索引还是唯一索引？

        由于唯一索引用不上 change buffer 的优化机制，因此如果业务可以接受，从性能角度出发建议你优先考虑非唯一索引。

27.什么是覆盖索引和索引下推？

覆盖索引：

  • 在某个查询里面，索引 k 已经“覆盖了”我们的查询需求，称为覆盖索引。
  • 覆盖索引可以减少树的搜索次数，显著提升查询性能，所以使用覆盖索引是一个常用的性能优化手段。

索引下推：

  • MySQL 5.6 引入的索引下推优化（index condition pushdown)， 可以在索引遍历过程中，对索引中包含的字段先做判断，直接过滤掉不满足条件的记录，减少回表次数。

28.字符串加索引？

  • 直接创建完整索引，这样可能会比较占用空间。
  • 创建前缀索引，节省空间，但会增加查询扫描次数，并且不能使用覆盖索引。
  • 倒序存储，再创建前缀索引，用于绕过字符串本身前缀的区分度不够的问题。
  • 创建 hash 字段索引，查询性能稳定，有额外的存储和计算消耗，跟第三种方式一样，都不支持范围扫描。

29.MySQL 的 change buffer 是什么？

  • 当需要更新一个数据页时，如果数据页在内存中就直接更新；而如果这个数据页还没有在内存中的话，在不影响数据一致性的前提下，InnoDB 会将这些更新操作缓存在 change buffer 中。
  • 这样就不需要从磁盘中读入这个数据页了，在下次查询需要访问这个数据页的时候，将数据页读入内存，然后执行 change buffer 中与这个页有关的操作。通过这种方式就能保证这个数据逻辑的正确性。
  • 注意唯一索引的更新就不能使用 change buffer，实际上也只有普通索引可以使用。
  • 适用场景：

        - 对于写多读少的业务来说，页面在写完以后马上被访问到的概率比较小，此时 change buffer 的使用效果最好。这种业务模型常见的就是账单类、日志类的系统。

        - 反过来，假设一个业务的更新模式是写入之后马上会做查询，那么即使满足了条件，将更新先记录在 change buffer，但之后由于马上要访问这个数据页，会立即触发 merge 过程。这样随机访问 IO 的次数不会减少，反而增加了 change buffer 的维护代价。

30.MySQL 是如何判断一行扫描数的？ 

  • MySQL 在真正开始执行语句之前，并不能精确地知道满足这个条件的记录有多少条。
  • 而只能根据统计信息来估算记录数。这个统计信息就是索引的“区分度。

31.MySQL的redo log和binlog的区别？

redo logbinlog
作用用于崩溃回复主从复制和数据恢复
实现方式Innodb存储引擎实现Server层实现，所有的存储引擎都可以使用binlong日志
记录方式循环写的方式记录，写到结尾时，会回到开头循环写日志通过追加的方式记录，当文件尺寸大于给配置值后，后续的日志会记录到新的文件上
文件大小redo log的大小是固定的通过配置参数max_binlog_size设置每个binlong文件大小
crash-safe能力具有没有
日志类型逻辑日志物理日志

32.为什么需要redo log？

  • redo log 主要用于 MySQL 异常重启后的一种数据恢复手段，确保了数据的一致性。
  • 其实是为了配合 MySQL 的 WAL 机制。因为 MySQL 进行更新操作，为了能够快速响应，所以采用了异步写回磁盘的技术，写入内存后就返回。但是这样，会存在 crash后 内存数据丢失的隐患，而 redo log 具备 crash safe 的能力。

33.为什么 redo log 具有 crash-safe 的能力，是 binlog 无法替代的？ 

第一点：redo log 可确保 innoDB 判断哪些数据已经刷盘，哪些数据还没有

  • redo log 和 binlog 有一个很大的区别就是，一个是循环写，一个是追加写。也就是说 redo log 只会记录未刷盘的日志，已经刷入磁盘的数据都会从 redo log 这个有限大小的日志文件里删除。binlog 是追加日志，保存的是全量的日志。
  • 当数据库 crash 后，想要恢复未刷盘但已经写入 redo log 和 binlog 的数据到内存时，binlog 是无法恢复的。虽然 binlog 拥有全量的日志，但没有一个标志让 innoDB 判断哪些数据已经刷盘，哪些数据还没有。
  • 但 redo log 不一样，只要刷入磁盘的数据，都会从 redo log 中抹掉，因为是循环写！数据库重启后，直接把 redo log 中的数据都恢复至内存就可以了。

第二点：如果 redo log 写入失败，说明此次操作失败，事务也不可能提交

  • redo log 每次更新操作完成后，就一定会写入日志，如果写入失败，说明此次操作失败，事务也不可能提交。
  • redo log 内部结构是基于页的，记录了这个页的字段值变化，只要crash后读取redo log进行重放，就可以恢复数据。
  • 这就是为什么 redo log 具有 crash-safe 的能力，而 binlog 不具备。

34.当数据库 crash 后，如何恢复未刷盘的数据到内存中？ 

  • 根据 redo log 和 binlog 的两阶段提交，未持久化的数据分为几种情况：
  • change buffer 写入，redo log 虽然做了 fsync 但未 commit，binlog 未 fsync 到磁盘，这部分数据丢失。
  • change buffer 写入，redo log fsync 未 commit，binlog 已经 fsync 到磁盘，先从 binlog 恢复 redo log，再从 redo log 恢复 change buffer。
  • change buffer 写入，redo log 和 binlog 都已经 fsync，直接从 redo log 里恢复。

35.redo log 写入方式？ 

redo log包括两部分内容，分别是内存中的日志缓冲(redo log buffer)和磁盘上的日志文件(redo log file)。

MySQL 每执行一条 DML 语句，会先把记录写入 redo log buffer（用户空间） ，再保存到内核空间的缓冲区 OS-buffer 中，后续某个时间点再一次性将多个操作记录写到 redo log file（刷盘） 。这种先写日志，再写磁盘的技术，就是WAL。

可以发现，redo log buffer写入到redo log file，是经过OS buffer中转的。其实可以通过参数innodb_flush_log_at_trx_commit进行配置，参数值含义如下：

  • 0：称为延迟写，事务提交时不会将redo log buffer中日志写入到OS buffer，而是每秒写入OS buffer并调用写入到redo log file中。
  • 1：称为实时写，实时刷”，事务每次提交都会将redo log buffer中的日志写入OS buffer并保存到redo log file中。
  • 2：称为实时写，延迟刷。每次事务提交写入到OS buffer，然后是每秒将日志写入到redo log file。

36.redo log 的执行流程? 

我们来看下Redo log的执行流程，假设执行的 SQL 如下：

update T set a =1 where id =666

  1. MySQL 客户端将请求语句 update T set a =1 where id =666，发往 MySQL Server 层。

  2. MySQL Server 层接收到 SQL 请求后，对其进行分析、优化、执行等处理工作，将生成的 SQL 执行计划发到 InnoDB 存储引擎层执行。

  3. InnoDB 存储引擎层将a修改为1的这个操作记录到内存中。

  4. 记录到内存以后会修改 redo log 的记录，会在添加一行记录，其内容是需要在哪个数据页上做什么修改。

  5. 此后，将事务的状态设置为 prepare ，说明已经准备好提交事务了。

  6. 等到 MySQL Server 层处理完事务以后，会将事务的状态设置为 commit，也就是提交该事务。

  7. 在收到事务提交的请求以后，redo log 会把刚才写入内存中的操作记录写入到磁盘中，从而完成整个日志的记录过程。

37.binlog 的概念是什么，起到什么作用， 可以保证 crash-safe 吗? 

  • binlog 是归档日志，属于 MySQL Server 层的日志。可以实现主从复制和数据恢复两个作用。
  • 当需要恢复数据时，可以取出某个时间范围内的 binlog 进行重放恢复。
  • 但是 binlog 不可以做 crash safe，因为 crash 之前，binlog 可能没有写入完全 MySQL 就挂了。所以需要配合 redo log 才可以进行 crash safe。

38.什么是两阶段提交？

 MySQL 将 redo log 的写入拆成了两个步骤：prepare 和 commit，中间再穿插写入binlog，这就是"两阶段提交"。

而两阶段提交就是让这两个状态保持逻辑上的一致。redolog 用于恢复主机故障时的未更新的物理数据，binlog 用于备份操作。两者本身就是两个独立的个体，要想保持一致，就必须使用分布式事务的解决方案来处理。

为什么需要两阶段提交呢?

  • 如果不用两阶段提交的话，可能会出现这样情况
  • 先写 redo log，crash 后 bin log 备份恢复时少了一次更新，与当前数据不一致。
  • 先写 bin log，crash 后，由于 redo log 没写入，事务无效，所以后续 bin log 备份恢复时，数据不一致。
  • 两阶段提交就是为了保证 redo log 和 binlog 数据的安全一致性。只有在这两个日志文件逻辑上高度一致了才能放心的使用。

在恢复数据时，redolog 状态为 commit 则说明 binlog 也成功，直接恢复数据；如果 redolog 是 prepare，则需要查询对应的 binlog事务是否成功，决定是回滚还是执行。 

39.MySQL 怎么知道 binlog 是完整的?

一个事务的 binlog 是有完整格式的： 

  • statement 格式的 binlog，最后会有 COMMIT；
  • row 格式的 binlog，最后会有一个 XID event。

40.什么是 WAL 技术，有什么优点？

WAL，中文全称是 Write-Ahead Logging，它的关键点就是日志先写内存，再写磁盘。MySQL 执行更新操作后，在真正把数据写入到磁盘前，先记录日志。 

好处是不用每一次操作都实时把数据写盘，就算 crash 后也可以通过redo log 恢复，所以能够实现快速响应 SQL 语句。 

41.binlog 日志的三种格式？

binlog 日志有三种格式：

  • Statement：基于SQL语句的复制((statement-based replication,SBR))
  • Row：基于行的复制。(row-based replication,RBR)
  • Mixed：混合模式复制。(mixed-based replication,MBR)

 Statement格式

每一条会修改数据的 SQL 都会记录在 binlog 中

  • 优点：不需要记录每一行的变化，减少了binlog日志量，节约了IO，提高性能。
  • 缺点：由于记录的只是执行语句，为了这些语句能在备库上正确运行，还必须记录每条语句在执行的时候的一些相关信息，以保证所有语句能在备库得到和在主库端执行时候相同的结果。

 Row格式

不记录 SQL 语句上下文相关信息，仅保存哪条记录被修改。

  • 优点：binlog 中可以不记录执行的 SQL 语句的上下文相关的信息，仅需要记录那一条记录被修改成什么了。所以rowlevel的日志内容会非常清楚的记录下每一行数据修改的细节。不会出现某些特定情况下的存储过程、或 function、或trigger的调用和触发无法被正确复制的问题。
  • 缺点:可能会产生大量的日志内容。

Mixed格式 

实际上就是 Statement 与 Row 的结合。一般的语句修改使用 statment 格式保存 binlog，如一些函数，statement 无法完成主从复制的操作，则采用 row 格式保存 binlog，MySQL 会根据执行的每一条具体的 SQL 语句来区分对待记录的日志形式。 

42.redo log日志格式？

 redo log buffer (内存中)是由首尾相连的四个文件组成的，它们分别是：ib_logfile_1、ib_logfile_2、ib_logfile_3、ib_logfile_4。

  • write pos 是当前记录的位置，一边写一边后移，写到第 3 号文件末尾后就回到 0 号文件开头。 
  • checkpoint 是当前要擦除的位置，也是往后推移并且循环的，擦除记录前要把记录更新到数据文件。
  • write pos 和 checkpoint 之间的是“粉板”上还空着的部分，可以用来记录新的操作。
  • 如果 write pos 追上 checkpoint，表示“粉板”满了，这时候不能再执行新的更新，得停下来先擦掉一些记录，把 checkpoint 推进一下。
  • 有了 redo log，当数据库发生宕机重启后，可通过 redo log将未落盘的数据（check point之后的数据）恢复，保证已经提交的事务记录不会丢失，这种能力称为crash-safe。

43.原本可以执行得很快的 SQL 语句，执行速度却比预期的慢很多，原因是什么？如何解决？

原因：从大到小可分为四种情况

  • MySQL 数据库本身被堵住了，比如：系统或网络资源不够。
  • SQL 语句被堵住了，比如：表锁，行锁等，导致存储引擎不执行对应的 SQL 语句。
  • 确实是索引使用不当，没有走索引。
  • 表中数据的特点导致的，走了索引，但回表次数庞大。

解决：

  • 考虑采用 force index 强行选择一个索引
  • 考虑修改语句，引导 MySQL 使用我们期望的索引。比如把“order by b limit 1” 改成 “order by b,a limit 1” ，语义的逻辑是相同的。
  • 第三种方法是，在有些场景下，可以新建一个更合适的索引，来提供给优化器做选择，或删掉误用的索引。
  • 如果确定是索引根本没必要，可以考虑删除索引。

44. InnoDB 数据页结构?

一个数据页大致划分七个部分

  • File Header：表示页的一些通用信息，占固定的38字节。
  • page Header：表示数据页专有信息，占固定的56字节。
  • inimum+Supermum：两个虚拟的伪记录，分别表示页中的最小记录和最大记录，占固定的26字节。
  • User Records：真正存储我们插入的数据，大小不固定。
  • Free Space：页中尚未使用的部分，大小不固定。
  • Page Directory：页中某些记录的相对位置，也就是各个槽对应的记录在页面中的地址偏移量。
  • File Trailer：用于检验页是否完整，占固定大小 8 字节。

45.MySQL 是如何保证数据不丢失的？ 

  • 只要redolog 和 binlog 保证持久化磁盘就能确保MySQL异常重启后回复数据。
  • 在恢复数据时，redolog 状态为 commit 则说明 binlog 也成功，直接恢复数据；如果 redolog 是 prepare，则需要查询对应的 binlog事务是否成功，决定是回滚还是执行。

46.误删数据怎么办？ 

DBA 的最核心的工作就是保证数据的完整性，先要做好预防，预防的话大概是通过这几个点：

  • 权限控制与分配(数据库和服务器权限)
  • 制作操作规范
  • 定期给开发进行培训
  • 搭建延迟备库
  • 做好 SQL 审计，只要是对线上数据有更改操作的语句(DML和DDL)都需要进行审核
  • 做好备份。备份的话又分为两个点 (1)如果数据量比较大，用物理备份 xtrabackup。定期对数据库进行全量备份，也可以做增量备份。(2)如果数据量较少，用 mysqldump 或者 mysqldumper。再利用 binlog 来恢复或者搭建主从的方式来恢复数据。定期备份binlog 文件也是很有必要的
  • 如果发生了数据删除的操作，又可以从以下几个点来恢复:
  • DML 误操作语句造成数据不完整或者丢失。可以通过 flashback，美团的 myflash，也是一个不错的工具，本质都差不多
  • 都是先解析 binlog event，然后在进行反转。把 delete 反转为insert，insert 反转为 delete，update前后 image 对调。
  • 所以必须设置binlog_format=row 和 binlog_row_image=full，切记恢复数据的时候，应该先恢复到临时的实例，然后在恢复回主库上。
  • DDL语句误操作(truncate和drop)，由于DDL语句不管 binlog_format 是 row 还是 statement ，在 binlog 里都只记录语句，不记录 image 所以恢复起来相对要麻烦得多。
  • 只能通过全量备份+应用 binlog 的方式来恢复数据。一旦数据量比较大，那么恢复时间就特别长
  • rm 删除：使用备份跨机房，或者最好是跨城市保存。

47. drop、truncate 和 delete 的区别？

  • DELETE 语句执行删除的过程是每次从表中删除一行，并且同时将该行的删除操作作为事务记录在日志中保存以便进行进行回滚操作。
  • TRUNCATE TABLE  则一次性地从表中删除所有的数据并不把单独的删除操作记录记入日志保存，删除行是不能恢复的。并且在删除的过程中不会激活与表有关的删除触发器。执行速度快。
  • drop语句将表所占用的空间全释放掉。
  • 在速度上，一般来说，drop> truncate > delete。
  • 如果想删除部分数据用 delete，注意带上 where 子句，回滚段要足够大；
  • 如果想删除表，当然用 drop；如果想保留表而将所有数据删除，如果和事务无关，用 truncate 即可；
  • 如果和事务有关，或者想触发 trigger，还是用 delete；如果是整理表内部的碎片，可以用 truncate 跟上 reuse stroage，再重新导入/插入数据。

48.在 MySQL 中有两个 kill 命令？

  • 一个是 kill query + 线程 id，表示终止这个线程中正在执行的语句。
  • 一个是 kill connection + 线程 id，这里 connection 可缺省，表示断开这个线程的连接。

kill 不掉的原因

  • kill命令被堵了，还没到位。
  • kill命令到位了，但是没被立刻触发。
  • kill命令被触发了，但执行完也需要时间。

49.如何理解 MySQL 的边读边发？

  • 如果客户端接受慢，会导致 MySQL 服务端由于结果发不出去，这个事务的执行时间会很长。
  • 服务端并不需要保存一个完整的结果集，取数据和发数据的流程都是通过一个 next_buffer 来操作的。
  • 内存的数据页都是在 Buffer_Pool中操作的。
  • InnoDB 管理 Buffer_Pool 使用的是改进的 LRU 算法，使用链表实现，实现上，按照 5:3 的比例把整个 LRU 链表分成了 young 区域和 old 区域。

50.MySQL 的大表查询为什么不会爆内存？ 

  • 由于 MySQL 是边读变发，因此对于数据量很大的查询结果来说，不会再 server 端保存完整的结果集，所以，如果客户端读结果不及时，会堵住 MySQL 的查询过程，但是不会把内存打爆。
  • InnoDB 引擎内部，由于有淘汰策略，InnoDB 管理 Buffer_Pool 使用的是改进的 LRU 算法，使用链表实现，实现上，按照 5:3 的比例把整个 LRU 链表分成了 young 区域和 old 区域。对冷数据的全扫描，影响也能做到可控制。

51.MySQL 临时表的用法和特性？

  • 只对当前session可见。
  • 可以与普通表重名。
  • 增删改查用的是临时表。
  • show tables 不显示普通表。
  • 在实际应用中，临时表一般用于处理比较复杂的计算逻辑。
  • 由于临时表是每个线程自己可见的，所以不需要考虑多个线程执行同一个处理时临时表的重名问题，在线程退出的时候，临时表会自动删除。

52.MySQL 存储引擎介绍（InnoDB、MyISAM、MEMORY）？

  • InnoDB 是事务型数据库的首选引擎，支持事务安全表 (ACID)，支持行锁定和外键。MySQL5.5.5 之后，InnoDB 作为默认存储引擎。
  • MyISAM 基于 ISAM 的存储引擎，并对其进行扩展。它是在 Web、数据存储和其他应用环境下最常用的存储引擎之一。MyISAM 拥有较高的插入、查询速度，但不支持事务。在 MySQL5.5.5 之前的版本中，MyISAM 是默认存储引擎。
  • MEMORY 存储引擎将表中的数据存储到内存中，为查询和引用其他表数据提供快速访问。

53.都说 InnoDB 好，那还要不要使用 MEMORY引擎？

  • 内存表就是使用 memory 引擎创建的表
  • 为什么我不建议你在生产环境上使用内存表。这里的原因主要包括两个方面：锁粒度问题；数据持久化问题。
  • 由于重启会丢数据，如果一个备库重启，会导致主备同步线程停止；如果主库跟这个备库是双 M 架构，还可能导致主库的内存表数据被删掉。

54.如果数据库误操作, 如何执行数据恢复?

数据库在某个时候误操作，就可以找到距离误操作最近的时间节点的bin log，重放到临时数据库里，然后选择误删的数据节点，恢复到线上数据库。 

55.MySQL 是如何保证主备同步？

主备关系的建立：

  • 一开始创建主备关系的时候，是由备库指定的，比如基于位点的主备关系，备库说“我要从binlog文件A的位置P”开始同步，主库就从这个指定的位置开始往后发。
  • 而主备关系搭建之后，是主库决定要发给数据给备库的，所以主库有新的日志也会发给备库。

MySQL 主备切换流程：

  • 客户端读写都是直接访问A，而节点B是备库，只要将A的更新都同步过来，到本地执行就可以保证数据是相同的。
  • 当需要切换的时候就把节点换一下，A的节点B的备库。

一个事务完整的同步过程：

  • 备库B和主库A建立来了长链接，主库A内部专门线程用于维护了这个长链接。
  • 在备库B上通过changemaster命令设置主库A的IP端口用户名密码以及从哪个位置开始请求binlog包括文件名和日志偏移量。
  • 在备库B上执行start-slave命令备库会启动两个线程：io_thread和sql_thread分别负责建立连接和读取中转日志进行解析执行。
  • 备库读取主库传过来的binlog文件备库收到文件写到本地成为中转日志。
  • 后来由于多线程复制方案的引入，sql_thread演化成了多个线程。

56.什么是主备延迟？

主库和备库在执行同一个事务的时候出现时间差的问题，主要原因有：

  • 有些部署条件下，备库所在机器的性能要比主库性能差。
  • 备库的压力较大。
  • 大事务，一个主库上语句执行10分钟，那么这个事务可能会导致从库延迟10分钟。

57.为什么要有多线程复制策略？

  • 因为单线程复制的能力全面低于多线程复制，对于更新压力较大的主库，备库可能是一直追不上主库的，带来的现象就是备库上seconds_behind_master值越来越大。
  • 在实际应用中，建议使用可靠性优先策略，减少主备延迟，提升系统可用性，尽量减少大事务操作，把大事务拆分小事务。

58.MySQL 的并行策略有哪些？

  • 按表分发策略：如果两个事务更新不同的表，它们就可以并行。因为数据是存储在表里的，所以按表分发，可以保证两个 worker 不会更新同一行。缺点：如果碰到热点表，比如所有的更新事务都会涉及到某一个表的时候，所有事务都会被分配到同一个 worker 中，就变成单线程复制了。
  • 按行分发策略：如果两个事务没有更新相同的行，它们在备库上可以并行。如果两个事务没有更新相同的行，它们在备库上可以并行执行。显然，这个模式要求 binlog 格式必须是 row。缺点：相比于按表并行分发策略，按行并行策略在决定线程分发的时候，需要消耗更多的计算资源。

59.MySQL的一主一备和一主多从有什么区别？ 

在一主一备的双 M 架构里，主备切换只需要把客户端流量切到备库；而在一主多从架构里，主备切换除了要把客户端流量切到备库外，还需要把从库接到新主库上。 

60.主库出问题如何解决? 

  • 基于位点的主备切换：存在找同步位点这个问题
  • MySQL 5.6 版本引入了 GTID，彻底解决了这个困难。那么，GTID 到底是什么意思，又是如何解决找同步位点这个问题呢？
  • GTID：全局事务 ID，是一个事务在提交的时候生成的，是这个事务的唯一标识；它由两部分组成，格式是：GTID=server_uuid:gno
  • 每个 MySQL 实例都维护了一个 GTID 集合，用来对应“这个实例执行过的所有事务”。
  • 在基于 GTID 的主备关系里，系统认为只要建立主备关系，就必须保证主库发给备库的日志是完整的。因此，如果实例 B 需要的日志已经不存在，A’就拒绝把日志发给 B。

61.MySQL 读写分离涉及到过期读问题的几种解决方案?

  • 强制走主库方案
  • sleep 方案
  • 判断主备无延迟方案
  • 配合 semi-sync 方案
  • 等主库位点方案
  • GTID 方案。
  • 实际生产中，先客户端对请求做分类，区分哪些请求可以接受过期读，而哪些请求完全不能接受过期读；然后，对于不能接受过期读的语句，再使用等 GTID 或等位点的方案。

62.MySQL的并发链接和并发查询有什么区别？

  • 在执行show processlist的结果里，看到了几千个连接，指的是并发连接。而"当前正在执行"的语句，才是并发查询。
  • 并发连接数多影响的是内存，并发查询太高对CPU不利。一个机器的CPU核数有限，线程全冲进来，上下文切换的成本就会太高。
  • 所以需要设置参数：innodb_thread_concurrency 用来限制线程数，当线程数达到该参数，InnoDB就会认为线程数用完了，会阻止其他语句进入引擎执行。

 63.短时间提高 MySQL 性能的方法?

  • 第一种方法：先处理掉那些占着连接但是不工作的线程。或者再考虑断开事务内空闲太久的连接。kill connection + id
  • 第二种方法：减少连接过程的消耗：慢查询性能问题在 MySQL 中，会引发性能问题的慢查询，大体有以下三种可能：索引没有设计好；SQL 语句没写好；MySQL 选错了索引（force index）。

64.为什么 MySQL 自增主键 ID 不连续？

  • 唯一键冲突
  • 事务回滚
  • 自增主键的批量申请
  • 深层次原因是：MySQL 不判断自增主键是否存在，从而减少加锁的时间范围和粒度，这样能保持更高的性能，确保自增主键不能回退，所以才有自增主键不连续。
  • 自增主键怎么做到唯一性？自增值加1来通过自增锁控制并发。

65.InnoDB 为什么要用自增 ID 作为主键？

  • 自增主键的插入模式，符合递增插入，每次都是追加操作，不涉及挪动记录，也不会触发叶子节点的分裂。
  • 每次插入新的记录就会顺序添加到当前索引节点的后续位置，当一页写满，就会自动开辟一个新的页。
  • 而有业务逻辑的字段做主键，不容易保证有序插入，由于每次插入主键的值近似于随机
  • 因此每次新纪录都要被插到现有索引页得中间某个位置， 频繁的移动、分页操作造成了大量的碎片，得到了不够紧凑的索引结构，写数据成本较高。

66.如何最快的复制一张表？

  • 为了避免对源表加读锁，更稳妥的方案是先将数据写到外部文本文件，然后再写回目标表
  • 一种方法是，使用 mysqldump 命令将数据导出成一组 INSERT 语句
  • 另一种方法是直接将结果导出成.csv 文件。MySQL 提供语法，用来将查询结果导出到服务端本地目录：select * from db1.t where a>900 into outfile '/server_tmp/t.csv';得到.csv 导出文件后，你就可以用下面的 load data 命令将数据导入到目标表 db2.t 中：load data infile '/server_tmp/t.csv' into table db2.t;
  • 物理拷贝：在 MySQL 5.6 版本引入了可传输表空间(transportable tablespace) 的方法，可以通过导出 + 导入表空间的方式，实现物理拷贝表的功能。

67.grant 和 flush privileges语句?

  • grant语句会同时修改数据表和内存，判断权限的时候使用的内存数据，因此，规范使用是不需要加上 flush privileges 语句。
  • flush privileges 语句本身会用数据表的数据重建一份内存权限数据，所以在权限数据可能存在不一致的情况下再使用。

68.要不要使用分区表？

  • 分区并不是越细越好。实际上，单表或者单分区的数据一千万行，只要没有特别大的索引，对于现在的硬件能力来说都已经是小表了。
  • 分区也不要提前预留太多，在使用之前预先创建即可。比如，如果是按月分区，每年年底时再把下一年度的 12 个新分区创建上即可。对于没有数据的历史分区，要及时的 drop 掉。

69.join 用法?

  • 使用 left join 左边的表不一定是驱动表
  • 如果需要 left join 的语义，就不能把被驱动表的字段放在 where 条件里面做等值判断或不等值判断，必须都写在 on 里面
  • 标准的 group by 语句，是需要在 select 部分加一个聚合函数，比如select a,count(*) from t group by a order by null;

70.MySQL 有哪些自增ID？各自场景是什么？

  • 表的自增 ID 达到上限之后，在申请值不会变化，进而导致联系插入数据的时候报主键冲突错误。
  • row_id 达到上限之后，归 0 在重新递增，如果出现相同的 row_id 后写的数据会覆盖之前的数据。
  • Xid 只需要不在同一个 binlog 文件出现重复值即可，理论上会出现重复值，但概率极小可忽略不计。
  • InnoDB 的 max_trx_id 递增值每次 MySQL 重启会保存起来。
  • Xid 是由 server 层维护的。InnoDB 内部使用 Xid，就是为了能够在 InnoDB 事务和 server 之间做关联。但是，InnoDB 自己的 trx_id，是另外维护的。
  • thread_id 是我们使用中最常见的，而且也是处理得最好的一个自增 id 逻辑了。使用了insert_unique算法。

71.Xid 在 MySQL 内部是怎么生成的呢？

MySQL 内部维护了一个全局变量 global_query_id，每次执行语句（包括select语句）的时候将它赋值给 Query_id，然后给这个变量加 1。如果当前语句是这个事务执行的第一条语句，那么 MySQL 还会同时把 Query_id 赋值给这个事务的 Xid。

而 global_query_id 是一个纯内存变量，重启之后就清零了。所以你就知道了，在同一个数据库实例中，不同事务的 Xid 也是有可能相同的。但是 MySQL 重启之后会重新生成新的 binlog 文件，这就保证了，同一个 binlog 文件里，Xid 一定是惟一的。

72.说一下 MySQL 的锁？

  • MySQL 在 server 层 和 存储引擎层 都运用了大量的锁

  • MySQL server 层需要讲两种锁，第一种是MDL(metadata lock) 元数据锁，第二种则 Table Lock 表锁。

  • MDL 又名元数据锁，那么什么是元数据呢，任何描述数据库的内容就是元数据，比如我们的表结构、库结构等都是元数据。那为什么需要 MDL 呢？

  • 主要解决两个问题：事务隔离问题；数据复制问题

  • InnoDB 有五种表级锁：IS（意向读锁）；IX（意向写锁）；S（读）；X（写）；AUTO-INC

  • 在对表进行select/insert/delete/update语句时候不会加表级锁

  • IS和IX的作用是为了判断表中是否有已经被加锁的记录

  • 自增主键的保障就是有 AUTO-INC 锁，是语句级别的：为表的某个列添加 AUTO_INCREMENT 属性，之后在插⼊记录时，可以不指定该列的值，系统会⾃动为它赋上单调递增的值。

  • InnoDB 4 种行级锁

  • RecordLock：记录锁

  • GapLock：间隙锁解决幻读；前一次查询不存在的东西在下一次查询出现了，其实就是事务A中的两次查询之间事务B执行插入操作被事务A感知了

  • Next-KeyLock：锁住某条记录又想阻止其它事务在改记录前面的间隙插入新纪录

  • InsertIntentionLock：插入意向锁;如果插入到同一行间隙中的多个事务未插入到间隙内的同一位置则无须等待

  • 行锁和表锁的抉择

    • 全表扫描用行级锁

73.为什么 MySQL 会抖一下？

        脏页会被后台线程自动 flush，也会由于数据页淘汰而触发 flush，而刷脏页的过程由于会占用资源，可能会让你的更新和查询语句的响应时间长一些。

74.为什么删除了表，表文件的大小还是没变？ 

  • 数据项删除之后 InnoDB 某个页 page A 会被标记为可复用。
  • delete 命令把整个表的数据删除，结果就是，所有的数据页都会被标记为可复用。但是磁盘上，文件不会变小。
  • 经过大量增删改的表，都是可能是存在空洞的。这些空洞也占空间所以，如果能够把这些空洞去掉，就能达到收缩表空间的目的。
  • 重建表，就可以达到这样的目的。可以使用 alter table A engine=InnoDB 命令来重建表。

75.orderby 排序内部原理？

  • MySQL 会为每个线程分配一个内存（sort-buffer）用于排序该内存大小为 sort_buffer_size；

  • 如果排序的数据量小于 sort_buffer_size，排序就会在内存中完成；

    内部排序分为两种

  • 全字段排序：到索引树上找到满足条件的主键ID根据主键ID去取出数据放到sort_buffer然后进行快速排序

  • rowid排序：通过控制排序的行数据的长度来让sort_buffer中尽可能多的存放数据

  • 如果数据量很大，内存中无法存下这么多，就会使用磁盘临时文件来辅助排序，称为外部排序；

  • 外部排序，MySQL会分为好几份单独的临时文件来存放排序后的数据，一般是磁盘文件中进行归并，然后将这些文件合并成一个大文件；

76.如何高效的使用 MySQL 显式随机消息？

  • 随机取出 Y1,Y2,Y3之后，算出Ymax,Ymin

  • 得到id集后算出Y1、Y2、Y3对应的三个id 最后 select * from t where id in (id1, id2, id3) 这样扫描的行数应该是C+Ymax+3

mysql> select count(*) into @C from t;
set @Y1 = floor(@C * rand());
set @Y2 = floor(@C * rand());
set @Y3 = floor(@C * rand());
Ymax = max(Y1,Y2,Y3)
Ymin = min(Y1,Y2,Y3)
select id from t limit Ymin，(Ymax - Ymin)

77.Mysql数据库主从那点事？

https://blog.csdn/qq_26420601/article/details/123686071

本文标签： 数据mysql

版权声明：本文标题：MySQL数据相关问题总结（精选） 内容由热心网友自发贡献，该文观点仅代表作者本人， 转载请联系作者并注明出处：https://www.elefans.com/dongtai/1727660749a1124227.html， 本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌抄袭侵权/违法违规的内容，一经查实，本站将立刻删除。