当前位置：首页 > news >正文

2025秋招八股文--mysql篇

news 2024/10/23 18:41:32

前言

1.本系列面试八股文的题目及答案均来自于网络平台的内容整理，对其进行了归类整理，在格式和内容上或许会存在一定错误，大家自行理解。内容涵盖部分若有侵权部分，请后台联系，及时删除。

2.本系列发布内容分为12篇分别是：

c/c++语言
数据结构与算法
GDB
设计模式
操作系统
系统编程
网络原理
网络编程
mysql
redis
服务器
RPG
本文为第九篇，后续会陆续更新。
共计200+道八股文。

3.本系列的200+道为整理的八股文系列的一小部分。完整整理完的八股文面试题共计1000+道，100W字左右，体量太大，故此处放至百度云盘链接： https://pan.baidu.com/s/1IOxQs0ifbSPGgxK7Yz7BtQ?pwd=zl1i

提取码：zl1i 需要的同学自取即可。

4.八股文对于面试的同学来说仅作为参考使用，不能作为面试上岸的唯一准备，还是要结合自身的技术能力和项目，同步发育。

最后祝各位同学都能拿到自己满意的offer，成功上岸！
————————————————

九、mysql

01.有哪些引擎

游戏引擎：如Unity、Unreal Engine、Cocos2d-x等，用于游戏开发。
物理引擎：如Box2D、Bullet Physics等，用于模拟物理系统。
三维建模和动画引擎：如Blender、Maya等，用于三维场景的建模和动画制作。
图形渲染引擎：如OpenGL、DirectX等，用于实现计算机图形学相关功能。
数据库引擎：如MySQL、Oracle等，用于管理和操作数据库。
网络通信引擎：如Netty、Boost.Asio等，用于实现高性能网络通信。
人工智能引擎：如TensorFlow、PyTorch等，用于实现机器学习和深度学习相关功能。
测试自动化框架/测试工具: 如Selenium, Appium, TestComplete, JMeter, Robot Framework.
操作系统内核: 如Linux Kernel, Windows NT Kernel, FreeBSD Kernel
大数据处理框架: 如Hadoop, Spark。

02.数据库的架构

数据库架构通常包括三层：

外层模式（外部架构）：也称为用户视图，是用户对数据库的逻辑观察方式，定义了用户能够看到和使用的数据。
概念层（概念架构）：也称为全局视图或逻辑架构，描述整个数据库系统中数据的总体结构和关系。它表示对应用程序员或系统分析师来说最重要的内容。
内层模式（内部架构）：也称为存储视图或物理架构，它描述了数据在计算机上实际存储的方式。涉及到磁盘存储、文件组织、索引方法等底层操作。

这种三层结构的设计方式被称为 ANSI/SPARC 三级体系结构模型。在实际应用中，可以根据具体需求增加或减少这些层次。

除此之外，还有一些其他类型的数据库架构：

主从复制架构：将主数据库中的所有更改同步到一个或多个备份数据库中。
分布式数据库架构：将数据分散到多个不同地理位置或处理器之间以提高性能和可靠性。
数据库集群技术：通过共享硬件资源和负载均衡技术将多个独立的计算机组成一个集群，提高数据库系统的可扩展性和容错能力。
数据库镜像架构：将主数据库的所有更新复制到一个或多个备份数据库上，以提高数据的可靠性和安全性。

03.不同引擎对索引的支持

#我们可以在创建上述索引的时候，为其指定索引类型，分两类
hash类型的索引：查询单条快，范围查询慢
btree类型的索引：b+树，层数越多，数据量指数级增长（我们就用它，因为innodb默认支持它）

#不同的存储引擎支持的索引类型也不一样
InnoDB 支持事务，支持行级别锁定，支持 B-tree、Full-text 等索引，不支持 Hash 索引；
MyISAM 不支持事务，支持表级别锁定，支持 B-tree、Full-text 等索引，不支持 Hash 索引；
Memory 不支持事务，支持表级别锁定，支持 B-tree、Hash 等索引，不支持 Full-text 索引；
NDB 支持事务，支持行级别锁定，支持 Hash 索引，不支持 B-tree、Full-text 等索引；
Archive 不支持事务，支持表级别锁定，不支持 B-tree、Hash、Full-text 等索引；

04.InnoDB和MyISAM的区别

区别

1.myisam是默认表类型不是事务安全的；innodb支持事务。

2.myisam不支持外键；Innodb支持外键。

3.myisam支持表级锁（不支持高并发，以读为主）；innodb支持行锁（共享锁，排它锁，意向锁），粒度更小，但是在执行不能确定扫描范围的sql语句时，innodb同样会锁全表。

4.执行大量select，myisam是最好的选择；执行大量的update和insert最好用innodb。

5.myisam在磁盘上存储上有三个文件.frm(存储表定义) .myd（存储表数据） .myi（存储表索引）；innodb磁盘上存储的是表空间数据文件和日志文件，innodb表大小只受限于操作系统大小。

6.myisam使用非聚集索引，索引和数据分开，只缓存索引；innodb使用聚集索引，索引和数据存在一个文件。

7.myisam保存表具体行数；innodb不保存。

8.delete from table时，innodb不会重新简历表，而会一行一行的删除。

05.隔离级别

SQL标准定义了4类隔离级别，包括了一些具体规则，用来限定事务内外的哪些改变是可见的，哪些是不可见的。低级别的隔离级一般支持更高的并发处理，并拥有更低的系统开销。
Read Uncommitted（读取未提交内容）

在该隔离级别，所有事务都可以看到其他未提交事务的执行结果。本隔离级别很少用于实际应用，因为它的性能也不比其他级别好多少。读取未提交的数据，也被称之为脏读（Dirty Read）。
Read Committed（读取提交内容）

这是大多数数据库系统的默认隔离级别（但不是MySQL默认的）。它满足了隔离的简单定义：一个事务只能看见已经提交事务所做的改变。这种隔离级别也支持所谓的不可重复读（Nonrepeatable Read），因为同一事务的其他实例在该实例处理其间可能会有新的commit，所以同一select可能返回不同结果。
Repeatable Read（可重读）

这是MySQL的默认事务隔离级别，它确保同一事务的多个实例在并发读取数据时，会看到同样的数据行。不过理论上，这会导致另一个棘手的问题：幻读（Phantom Read）。简单的说，幻读指当用户读取某一范围的数据行时，另一个事务又在该范围内插入了新行，当用户再读取该范围的数据行时，会发现有新的“幻影” 行。InnoDB和Falcon存储引擎通过多版本并发控制（MVCC，Multiversion Concurrency Control）机制解决了该问题。

Serializable（可串行化）
这是最高的隔离级别，它通过强制事务排序，使之不可能相互冲突，从而解决幻读问题。简言之，它是在每个读的数据行上加上共享锁。在这个级别，可能导致大量的超时现象和锁竞争。

这四种隔离级别采取不同的锁类型来实现，若读取的是同一个数据的话，就容易发生问题。例如：

脏读(Drity Read)：某个事务已更新一份数据，另一个事务在此时读取了同一份数据，由于某些原因，前一个RollBack了操作，则后一个事务所读取的数据就会是不正确的。

不可重复读(Non-repeatable read):在一个事务的两次查询之中数据不一致，这可能是两次查询过程中间插入了一个事务更新的原有的数据。

幻读(Phantom Read):在一个事务的两次查询中数据笔数不一致，例如有一个事务查询了几列(Row)数据，而另一个事务却在此时插入了新的几列数据，先前的事务在接下来的查询中，就会发现有几列数据是它先前所没有的。

06.最左前缀原则

最左前缀原则：顾名思义是最左优先，以最左边的为起点任何连续的索引都能匹配上。

（1）如果第一个字段是范围查询需要单独建一个索引；

（2）在创建多列索引时，要根据业务需求，where子句中使用最频繁的一列放在最左边；

07.MySQL的集群是用什么样的方式去增加并发量

MySQL是广泛应用于开发领域的关系型数据库管理系统，但在高并发情况下，其性能会受到严重影响。本文将介绍实现高并发MySQL数据库的技巧，助您提升MySQL的最大并发量。

1.使用连接池技术

在高并发情况下，频繁地创建和关闭数据库连接会占用大量的系统资源。使用连接池技术可以将连接缓存起来，减少连接创建和关闭的次数，提高系统的响应速度。

2.合理使用索引

索引是MySQL查询语句中的重要组成部分，正确地使用索引可以大大提高查询效率。但是，过多的索引会降低写入性能，因此需要根据实际情况进行合理的索引设计。

3.优化SQL语句

SQL语句的优化对于提高MySQL的性能至关重要。在编写SQL语句时，应避免使用不必要的子查询和联合查询，同时合理使用缓存和分页技术。

4.使用缓存技术cached等缓存系统，将常用的数据缓存起来，减少数据库的访问次数，提高系统的响应速度。

5.分库分表

当数据量较大时，可以考虑使用分库分表技术，将数据分散到多个数据库或表中，提高系统的并发能力和处理能力。

6.使用存储过程

存储过程是一种预先编译的SQL语句块，可以提高MySQL的性能和安全性。存储过程可以减少网络传输的开销，同时可以减少SQL注入的风险。

7.升级硬件

在提高MySQL性能时，硬件升级也是一个不可忽视的因素。可以考虑升级CPU、内存、硬盘等硬件设备，提高系统的处理能力和响应速度。

实现高并发MySQL数据库需要综合考虑多个方面的因素，包括连接池技术、索引设计、SQL语句优化、缓存技术、分库分表、存储过程和硬件升级等。通过对这些技巧的合理应用，可以提高MySQL的最大并发量，实现高性能的数据库系统。

08.除了读写分离还有吗？

Mysql读写分离的四种方案

一、读写分离介绍

1、做读写分离的原因

数据库写入效率要低于读取效率，一般系统中数据读取频率高于写入频率，单个数据库实例在写入的时候会影响读取性能，这是做读写分离的原因。

2、MySQL读写分离的基础

实现方式主要基于mysql的主从复制，通过路由的方式使应用对数据库的写请求只在master上进行，读请求在slave上进行。

二、实现读写分离的原理与方案

1、基于MySQL proxy代理的方式

在应用和数据库之间增加代理层，代理层接收应用对数据库的请求，根据不同请求类型转发到不同的实例，在实现读写分离的同时可以实现负载均衡。

（1）实现原理

在这里插入图片描述

（2）开源方案

MySQL的代理最常见的是mysql-proxy、cobar、mycat、Atlas等。这种方式对于应用来说，MySQL Proxy是完全透明的，应用则只需要连接到MySQL Proxy的监听端口即可。当然，这样proxy机器可能成为单点失效，但完全可以使用多个proxy机器做为冗余，在应用服务器的连接池配置中配置到多个proxy的连接参数即可。

mysql-proxy是一个轻量的中间代理，是官方提供的mysql中间件产品可以实现负载平衡，读写分离，failover等，依靠内部一个lua脚本实现读写语句的判断。项目地址： https://github.com/mysql/mysql-proxy ，该项目已经六七年没有维护了，官方也不建议应用于生成环境。
cobar是阿里提供的一个中间件，已经停止更新。项目地址：https://github.com/alibaba/cobar
mycat的前身就是cobar，活跃度比较高，完全使用java语言开发。项目地址：https://github.com/MyCATApache/Mycat-Server ，该项目当前已经有8.3k的点赞量。
moeba（变形虫）是阿里工程师陈思儒基于java开发的一款数据库读写分离的项目(读写分离只是它的一个小功能)，与MySQL官方的MySQL Proxy相比，作者强调的是amoeba配置的方便（基于XML的配置文件，用SQLJEP语法书写规则，比基于lua脚本的MySQL Proxy简单）。更多详细介绍请参考：https://www.biaodianfu.com/amoeba.html , 下载地址：https://sourceforge.net/projects/amoeba/ 。
Atlas奇虎360的一个开源中间代理，是在mysql官方mysql-proxy 0.8.2的基础上进行了优化，增加一些新的功能特性。项目地址: https://github.com/Qihoo360/Atlas ，该项目当前已经有4.4k的点赞量。

2、基于应用内路由的方式

基于应用内路由的方式即为在应用程序中实现，针对不同的请求类型去不同的实例执行sql。

（1）实现原理

在这里插入图片描述

（2）实现方案

基于spring的aop实现: 用aop来拦截spring项目的dao层方法，根据方法名称就可以判断要执行的sql类型(即是read还是write类型)，进而动态切换主从数据源。类似项目有：

多数据源切换：https://gitee.com/baomidou/dynamic-datasource-spring-boot-starter

3 、基于mysql-connector-java的jdbc驱动方式

（1）实现原理

使用mysql驱动Connector/J的可以实现读写分离。即在jdbc的url中配置为如下的形示：

jdbc:mysql:replication://master,slave1,slave2,slave3/test

（2）实现方案

java程序通过在连接MySQL的jdbc中配置主库与从库等地址，jdbc会自动将读请求发送给从库，将写请求发送给主库，此外，mysql的jdbc驱动还能够实现多个从库的负载均衡。

关于mysql的jdbc说明官方文档地址：https://dev.mysql.com/doc/connector-j/8.0/en/connector-j-reference-jdbc-url-format.html
关于mysql的读写分离文档地址：https://dev.mysql.com/doc/connector-j/8.0/en/connector-j-source-replica-replication-connection.html

4、基于sharding-jdbc的方式

sharding-sphere是强大的读写分离、分表分库中间件，sharding-jdbc是sharding-sphere的核心模块。

（1）实现原理

在这里插入图片描述

（2）实现方案

sharding-jdbc可以与springboot集成。官方网址：https://shardingsphere.apache.org/

<dependency><groupId>org.apache.shardingsphere</groupId><artifactId>sharding-jdbc-spring-boot-starter</artifactId><version>4.0.1</version>
</dependency>

三、最后总结

以上四种方案各有优缺点，基于MySQL proxy代理的方式对于应用来说相对简单，但是在项目稳定性、事务支持性等方面还存在问题；而基于应用内路由的方式固然灵活度比较高，但是也增加了应用逻辑的复杂度；基于mysql-connector-java的jdbc驱动和sharding-jdbc的方式在使用上相对简单，但限制了需要使用java开发。

09.mysql的隔离级别和锁

数据库锁，分为悲观锁和乐观锁，“悲观锁”

悲观锁一般利用 SELECT … FOR UPDATE 类似的语句
乐观锁利用 CAS 机制，并不会对数据加锁，而是通过对比数据的时间戳或者版本号，实现版本判断。

脏读：读到了未提交事务的数据。

不可重复读：事务 A 先读取一条数据，然后执行逻辑的过程中，事务 B 更新了这条数据，事务 A 再读取时，发现数据不匹配，这个现象就是“不可重复读”。

幻读：在一个事务内，同一条查询语句在不同时间段执行，得到不同的结果集。

解决幻读的办法是锁住记录之间的“间隙”，为此 MySQL InnoDB 引入了新的锁，叫间隙锁（Gap Lock），所以在面试中，你也要掌握间隙锁，以及间隙锁与行锁结合的 next-key lock 锁。

一般都用设置RC ，不用RR，间隙锁性能低，可能锁表

死锁只有同时满足互斥、持有并等待、不可剥夺、循环等待时才会发生

持有并等待：我们可以一次性申请所有的资源，这样就不存在等待了。
不可剥夺：占用部分资源的线程进一步申请其他资源时，如果申请不到，可以主动释放它占有的资源，这样不可剥夺这个条件就破坏掉了。
循环等待：可以靠按序申请资源来预防，也就是所谓的资源有序分配原则，让资源的申请和使用有线性顺序，申请的时候可以先申请资源序号小的，再申请资源序号大的，这样的线性化操作就自然就不存在循环了。

10.数据库delete和trancate区别(这个trancate没用过，没说出来)

1.truncate

删除表中的内容，不删除表结构，释放空间；

2.delete

删除内容，不删除表结构，但不释放空间

3.区别

3.1内存空间

truncate删除数据后重新写数据会从1开始，而delete删除数据后只会从删除前的最后一行续写；内存空间上，truncate省空间

3.2处理速度

因为，truncate是直接从1开始，即全部清空开始，而delete需要先得到当前行数，从而进行续写；所以truncate删除速度比delete快；

3.3语句类型

delete属于DML语句，而truncate和drop都属于DDL语句，这造成了它们在事务中的不同现象：

delete在事务中，因为属于DML语句，所以可以进行回滚和提交操作（由操作者）
truncate和drop则属于DDL语句，在事务中，执行后会自动commit，所以不可以回滚；

3.4语法

delete from 表名 (where…可写可不写，写的话进行选择性删除，不选清空表中数据)

truncate 表名（删除表中的数据，无法回滚的）

delete可以在后续加上where进行针对行的删除
truncate和drop后面只能加上表名，直接删除表，无法where

注意：并且drop和truncate不能够激活触发器，因为该操作不记录各行删除；

drop table 表名

删除表，内容连带结构一起删除；

11.mysql索引（B+树）

B+树索引是B+树在数据库中的一种实现，是最常见也是数据库中使用最为频繁的一种索引。B+树中的B代表平衡（balance），而不是二叉（binary），因为B+树是从最早的平衡二叉树演化而来的。在讲B+树之前必须先了解二叉查找树、平衡二叉树（AVLTree）和平衡多路查找树（B-Tree），B+树即由这些树逐步优化而来。

一、二叉查找树

二叉树具有以下性质：左子树的键值小于根的键值，右子树的键值大于根的键值。
如下图所示就是一棵二叉查找树。

对该二叉树的节点进行查找发现深度为1的节点的查找次数为1，深度为2的查找次数为2，深度为n的节点的查找次数为n，因此其平均查找次数为 (1+2+2+3+3+3) / 6 = 2.3次

二叉查找树可以任意地构造，同样是2,3,5,6,7,8这六个数字，也可以按照下图的方式来构造：

但是这棵二叉树的查询效率就低了。因此若想二叉树的查询效率尽可能高，需要这棵二叉树是平衡的，从而引出新的定义——平衡二叉树，或称AVL树。

如果在AVL树中进行插入或删除节点，可能导致AVL树失去平衡，这种失去平衡的二叉树可以概括为四种姿态：LL（左左）、RR（右右）、LR（左右）、RL（右左）。它们的示意图如下：

这四种失去平衡的姿态都有各自的定义：

LL：LeftLeft，也称“左左”。插入或删除一个节点后，根节点的左孩子（Left Child）的左孩子（Left Child）还有非空节点，导致根节点的左子树高度比右子树高度高2，AVL树失去平衡。

RR：RightRight，也称“右右”。插入或删除一个节点后，根节点的右孩子（Right Child）的右孩子（Right Child）还有非空节点，导致根节点的右子树高度比左子树高度高2，AVL树失去平衡。

LR：LeftRight，也称“左右”。插入或删除一个节点后，根节点的左孩子（Left Child）的右孩子（Right Child）还有非空节点，导致根节点的左子树高度比右子树高度高2，AVL树失去平衡。

RL：RightLeft，也称“右左”。插入或删除一个节点后，根节点的右孩子（Right Child）的左孩子（Left Child）还有非空节点，导致根节点的右子树高度比左子树高度高2，AVL树失去平衡。

AVL树失去平衡之后，可以通过旋转使其恢复平衡。下面分别介绍四种失去平衡的情况下对应的旋转方法。

LL的旋转。LL失去平衡的情况下，可以通过一次旋转让AVL树恢复平衡。步骤如下：

将根节点的左孩子作为新根节点。
将新根节点的右孩子作为原根节点的左孩子。
将原根节点作为新根节点的右孩子。

LL旋转示意图如下：

RR的旋转：RR失去平衡的情况下，旋转方法与LL旋转对称，步骤如下：

将根节点的右孩子作为新根节点。
将新根节点的左孩子作为原根节点的右孩子。
将原根节点作为新根节点的左孩子。

RR旋转示意图如下：

LR的旋转：LR失去平衡的情况下，需要进行两次旋转，步骤如下：

围绕根节点的左孩子进行RR旋转。
围绕根节点进行LL旋转。

LR的旋转示意图如下：

RL的旋转：RL失去平衡的情况下也需要进行两次旋转，旋转方法与LR旋转对称，步骤如下：

围绕根节点的右孩子进行LL旋转。
围绕根节点进行RR旋转。

RL的旋转示意图如下：

二、平衡多路查找树（B-Tree）

B-Tree是为磁盘等外存储设备设计的一种平衡查找树。因此在讲B-Tree之前先了解下磁盘的相关知识。

系统从磁盘读取数据到内存时是以磁盘块（block）为基本单位的，位于同一个磁盘块中的数据会被一次性读取出来，而不是需要什么取什么。

InnoDB存储引擎中有页（Page）的概念，页是其磁盘管理的最小单位。InnoDB存储引擎中默认每个页的大小为16KB，可通过参数innodb_page_size将页的大小设置为4K、8K、16K，在MySQL中可通过如下命令查看页的大小：

mysql> show variables like 'innodb_page_size';

而系统一个磁盘块的存储空间往往没有这么大，因此InnoDB每次申请磁盘空间时都会是若干地址连续磁盘块来达到页的大小16KB。InnoDB在把磁盘数据读入到磁盘时会以页为基本单位，在查询数据时如果一个页中的每条数据都能有助于定位数据记录的位置，这将会减少磁盘I/O次数，提高查询效率。

B-Tree结构的数据可以让系统高效的找到数据所在的磁盘块。为了描述B-Tree，首先定义一条记录为一个二元组[key, data] ，key为记录的键值，对应表中的主键值，data为一行记录中除主键外的数据。对于不同的记录，key值互不相同。

一棵m阶的B-Tree有如下特性：

\1. 每个节点最多有m个孩子。
\2. 除了根节点和叶子节点外，其它每个节点至少有Ceil(m/2)个孩子。
\3. 若根节点不是叶子节点，则至少有2个孩子
\4. 所有叶子节点都在同一层，且不包含其它关键字信息
\5. 每个非终端节点包含n个关键字信息（P0,P1,…Pn, k1,…kn）
\6. 关键字的个数n满足：ceil(m/2)-1 <= n <= m-1
\7. ki(i=1,…n)为关键字，且关键字升序排序。
\8. Pi(i=1,…n)为指向子树根节点的指针。P(i-1)指向的子树的所有节点关键字均小于ki，但都大于k(i-1)

B-Tree中的每个节点根据实际情况可以包含大量的关键字信息和分支，如下图所示为一个3阶的B-Tree：

每个节点占用一个盘块的磁盘空间，一个节点上有两个升序排序的关键字和三个指向子树根节点的指针，指针存储的是子节点所在磁盘块的地址。两个关键词划分成的三个范围域对应三个指针指向的子树的数据的范围域。以根节点为例，关键字为17和35，P1指针指向的子树的数据范围为小于17，P2指针指向的子树的数据范围为17~35，P3指针指向的子树的数据范围为大于35。

模拟查找关键字29的过程：

根据根节点找到磁盘块1，读入内存。【磁盘I/O操作第1次】
比较关键字29在区间（17,35），找到磁盘块1的指针P2。
根据P2指针找到磁盘块3，读入内存。【磁盘I/O操作第2次】
比较关键字29在区间（26,30），找到磁盘块3的指针P2。
根据P2指针找到磁盘块8，读入内存。【磁盘I/O操作第3次】
在磁盘块8中的关键字列表中找到关键字29。
分析上面过程，发现需要3次磁盘I/O操作，和3次内存查找操作。由于内存中的关键字是一个有序表结构，可以利用二分法查找提高效率。而3次磁盘I/O操作是影响整个B-Tree查找效率的决定因素。B-Tree相对于AVLTree缩减了节点个数，使每次磁盘I/O取到内存的数据都发挥了作用，从而提高了查询效率。

三、B+Tree

B+Tree是在B-Tree基础上的一种优化，使其更适合实现外存储索引结构，InnoDB存储引擎就是用B+Tree实现其索引结构。

从上一节中的B-Tree结构图中可以看到每个节点中不仅包含数据的key值，还有data值。而每一个页的存储空间是有限的，如果data数据较大时将会导致每个节点（即一个页）能存储的key的数量很小，当存储的数据量很大时同样会导致B-Tree的深度较大，增大查询时的磁盘I/O次数，进而影响查询效率。在B+Tree中，所有数据记录节点都是按照键值大小顺序存放在同一层的叶子节点上，而非叶子节点上只存储key值信息，这样可以大大加大每个节点存储的key值数量，降低B+Tree的高度。

B+Tree相对于B-Tree有几点不同：

非叶子节点只存储键值信息。
所有叶子节点之间都有一个链指针。
数据记录都存放在叶子节点中。
将上一节中的B-Tree优化，由于B+Tree的非叶子节点只存储键值信息，假设每个磁盘块能存储4个键值及指针信息，则变成B+Tree后其结构如下图所示：

通常在B+Tree上有两个头指针，一个指向根节点，另一个指向关键字最小的叶子节点，而且所有叶子节点（即数据节点）之间是一种链式环结构。因此可以对B+Tree进行两种查找运算：一种是对于主键的范围查找和分页查找，另一种是从根节点开始，进行随机查找。

可能上面例子中只有22条数据记录，看不出B+Tree的优点，下面做一个推算：

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的高度一般都在2_{4层。MySQL的InnoDB存储引擎在设计时是将根节点常驻内存的，也就是说查找某一键值的行记录时最多只需要1}3次磁盘I/O操作。

数据库中的B+Tree索引可以分为聚集索引（clustered index）和辅助索引（secondary index）。上面的B+Tree示例图在数据库中的实现即为聚集索引，聚集索引的B+Tree中的叶子节点存放的是整张表的行记录数据。辅助索引与聚集索引的区别在于辅助索引的叶子节点并不包含行记录的全部数据，而是存储相应行数据的聚集索引键，即主键。当通过辅助索引来查询数据时，InnoDB存储引擎会遍历辅助索引找到主键，然后再通过主键在聚集索引中找到完整的行记录数据。

12.B树和B+树的区别

1.B树只适合随机检索，B+树支持随机检索和顺序检索
2.B+树空间利用率高，可以减少IO次数，磁盘读写代价更低。
一般来说索引本身也很大，往往以索引文件的形式存储在磁盘上，这样索引查找过程就要产生磁盘IO消耗。B+树的内部节点只作为索引使用，其内部节点（非叶子节点）比B树更小，判断能容纳的节点中关键字更多，一次读取到的键更多。
3.B+树查询效率更稳定，因为数据存放在叶子节点。
4.B树在一定程度上也提高了磁盘IO性能，但没有解决遍历效率低下的问题。B+树的叶子节点都使用指针顺序连接在一起，只要遍历叶子节点就可以实现所有值。
5.增删文件时，B树需要重新调整树结构。B+树不需要调整树结构，因此B+树效率更高。

13.B+树树高怎么算？树高为4能支持多少数据量

前一段被问到一个平时没有关注到有关于MYSQL索引相关的问题点，被问到一个表有3000万记录，假如有一列占8位字节的字段，根据这一列建索引的话索引树的高度是多少？

这一问当时就被问蒙了，平时这也只关注MySQL索引一般都是都是用B+Tree来存储维护索引的，还有一些复合索引的最左匹配原则等等，还真没有实际关注过始即然用到索引能提升

查询的效率，那么这个索引树高是多少，给定表和索引字段后怎么计算出索引树的高度？下面将用举例的形式来说明如何计算索引树的高度。

在举例之前，先给出一个千万级记录表的索引的高度大概在3-5的样，当时我看到这个数字时也是很惊讶的！

举例前先做一下举例时用到的公式的一些维度的说明

假设：

表的记录数是N

每一个BTREE节点平均有B个索引KEY

那么B+TREE索引树的高度就是logNB(等价于logN/logB)

由于索引树每个节点的大小固定，所以索引KEY越小，B值就越大，那么每个BTREE节点上可以保存更多的索引KEY，也就是B值越大，索引树的高度就越小，那么基于索引的查询的性能就越高。所以相同表记录数的情况下，索引KEY越小，索引树的高度就越小。

现在我们假设表3000W条记录（因为2^{25=33554432），如果每个节点保存64个索引KEY,那么索引的高度就是（log2}25)/log64≈ 25/6 ≈ 4.17

通过上面的计算可知，要计一张表索引树的高度，只需要知道一个节点有多，从而就能知道每个节点能存储多少个索引KEY。现代数据库经过不断的探索和优化，并结合磁盘的预读特点，每个索引节点一般都是操作系统页的整数倍，操作系统页可通过命令得到该值得大小，且一般是4094，即4k。而InnoDB的pageSize可以通过命令得到，默认值是16k。

以BIGINT为例，存储大小为8个字节。INT存储大小为4个字节（32位）。索引树上每个节点除了存储KEY，还需要存储指针。所以每个节点保存的KEY的数量为pagesize/(keysize+pointsize)（如果是B-TREE索引结构，则是pagesize/(keysize+datasize+pointsize)）。

假设平均指针大小是4个字节，那么索引树的每个节点可以存储16k/((8+4)*8)≈171。那么：一个拥有3000w数据，且主键是BIGINT类型的表的主键索引树的高度就是(log2^25)/log171 ≈ 25/7.4 ≈ 3.38。

假设平均指针大小是8个字节，那么索引树的每个节点可以存储16k/((8+8)*8)≈128。那么：一个拥有3000w数据，且主键是BIGINT类型的表的主键索引树的高度就是(log2^25)/log128 ≈ 25/7 ≈ 3.57

由上面的计算可知：一个千万量级，且存储引擎是MyISAM或者InnoDB的表，其索引树的高度在3~5之间。

14.数据库ACID怎么实现

1.前言

我们在学MySQL的时候事务是必须要知道的部分，也就是原子性(Atomic)、一致性(Consistency)、隔离性(isolation)和持久性(Persistence)。知道他的概念其实是远远不够的，现在越来越卷，那么就必须知道的他的原理什么？怎么是实现的。

2.原理

2.1 原子性：

概念
是指事物是一个不可分割的工作单位，事物中的操作要么都发生，要么都不发生。最经典的就是转账案例，我们把转入和转出当做一个事物的话，就需要在SQL中显式指定开启事务。

2.2 实现原理：undo log

undo log 是回滚日志也叫逻辑日志是实现原子实现事务原子性和隔离性实现的基础。首先我们要清楚的是在实现原子性的是是由一个回滚的操作，回滚就是说我们在执行操作的时候那么我们会执行一个相反的操作来是数据库的状态变成之前为操作的状态。

具体的实现原理：在数据库执行操作的时候，InnoDB会生成一个undo log日志，里面包含的是数据库的SQL语句，如果说数据库的操作失败的时候，会调用rollback，导致事务回滚，那么InnoDB就可以利用undo log 进行一个回滚的操作，具体是如果说你之前执行的是insert操作那么就会执行一个相反的delete操作，之前执行的是delete操作那么就会执行一个insert操作，或者之前执行的是update操作那么就会执行一个反向的update操作。总得来说，就是InnoDB会根据回滚之前生成的undo log操作日志执行相反的操作。

3.一致性

3.1 概念

是说数据库事务不能破坏关系数据的完整性以及业务逻辑上的一致性。也就是说其他三个事务特性最终的目的的就是为了达到这个一致性效果。

4.隔离性

4.1 概念

指的是多个事务并发访问时，事务之间是隔离的，一个事务不应该影响其它事务运行效果。多个事务并发访问时，事务之间是隔离的，一个事务不应该影响其它事务运行效果。需要使用mvcc实现。如果不知道什么是MVCC的或者不知道其底层实现的可以去看我的前面几篇博客，写的很详细。https://blog.csdn.net/Ppphill_C/article/details/123833430?spm=1001.2014.3001.5501

5.持久性

5.1 概念

意味着即使出现了任何事故比如断电等，事务一旦提交，则持久化保存在数据库中，不会被回滚。

5.2 实现原理：redo log

redo log 是事务日志，用于保证事务的持久性。在这之前我们先要知道一下为什么需要用到他然后我们在去掌握我们要怎么用它，以及他的工作原理是什么？

我们都知道在数据库的操作过程中，一开始的话都是直接访问数据库的。所以效率并不是特别的好，并不能满足要求。因此就提出了一个buffer pool 这个概念，其中 buffer pool 是对数据库磁盘数据的一个映射，我们在操作的时候直接去读取buffer pool 就不必去读取数据库，那么buffer pool 会隔一段时间对数据库进行数据的更新操作，那么这一个过程又称 “刷脏”。

有了这个buffer pool 的加入可以大大的加快的数据库的访问速度，整体的提升了工作的效率。但是有一个不好的地方就是，如果说MySQL宕机了，buffer pool 开始刷脏，那么这个时候就会是数据的丢失。这就不符合我们的持久性问题了。因此，就提出了redo log这个日志来保证持久性。

首先我们要知道的是redo log 是采用的一种叫(Write-ahead logging)预先写的方法，换句话就是说所有的修改先写入日志，然后在更新到buffer pool。如果MySQL宕机，重启时可以读取redo log中的数据，对数据库进行恢复。这样就保证了数据不会因为MySQL宕机而丢失，从而满足持久性的要求。

我们可以根据下面这张图来加深一下理解：

在这里插入图片描述

这边大家应该会有这么一个疑问就是：既然redo log 也可以写入磁盘，那为什么他会比Buffer pool写入的时候更快呢？

刷脏是随机IO，因为每次修改的数据位置随机，但写redo log是追加操作，属于顺序IO。
刷脏是以数据页（Page）为单位的，MySQL默认页大小是16KB，一个Page上一个小修改都要整页写入；而redo log中只包含真正需要写入的部分，无效IO大大减少。这就有点redis中的AOF持久化的工作原理的味道了。

6.结语

好了，MySQL的事务就讲完了，希望能够给你带来帮助！收工~

15.binlog记录的是什么

（一） binlog介绍

binlog,即二进制日志,它记录了数据库上的所有改变，并以二进制的形式保存在磁盘中；

它可以用来查看数据库的变更历史、数据库增量备份和恢复、Mysql的复制（主从数据库的复制）。

（二） binlog格式

binlog有三种格式：Statement、Row以及Mixed。

–基于SQL语句的复制(statement-based replication,SBR)， –基于行的复制(row-based replication,RBR)， –混合模式复制(mixed-based replication,MBR)。

2.1 Statement 每一条会修改数据的sql都会记录在binlog中。

优点：不需要记录每一行的变化，减少了binlog日志量，节约了IO，提高性能。

缺点：由于记录的只是执行语句，为了这些语句能在slave上正确运行，因此还必须记录每条语句在执行的时候的一些相关信息，以保证所有语句能在slave得到和在master端执行时候相同的结果。另外mysql 的复制,像一些特定函数功能，slave可与master上要保持一致会有很多相关问题。

ps：相比row能节约多少性能与日志量，这个取决于应用的SQL情况，正常同一条记录修改或者插入row格式所产生的日志量还小于Statement产生的日志量，但是考虑到如果带条件的update操作，以及整表删除，alter表等操作，ROW格式会产生大量日志，因此在考虑是否使用ROW格式日志时应该跟据应用的实际情况，其所产生的日志量会增加多少，以及带来的IO性能问题。

2.2 Row

5.1.5版本的MySQL才开始支持row level的复制,它不记录sql语句上下文相关信息，仅保存哪条记录被修改。

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

缺点:所有的执行的语句当记录到日志中的时候，都将以每行记录的修改来记录，这样可能会产生大量的日志内容。

ps:新版本的MySQL中对row level模式也被做了优化，并不是所有的修改都会以row level来记录，像遇到表结构变更的时候就会以statement模式来记录，如果sql语句确实就是update或者delete等修改数据的语句，那么还是会记录所有行的变更。

2.3 Mixed

从5.1.8版本开始，MySQL提供了Mixed格式，实际上就是Statement与Row的结合。

在Mixed模式下，一般的语句修改使用statment格式保存binlog，如一些函数，statement无法完成主从复制的操作，则采用row格式保存binlog，MySQL会根据执行的每一条具体的sql语句来区分对待记录的日志形式，也就是在Statement和Row之间选择一种。

只查看第一个binlog文件的内容 mysql> show binlog events;

查看指定binlog文件的内容 mysql> show binlog events in ‘mysql-bin.000001’;

获取binlog文件列表

mysql> show binary logs;

[python] view plain copy

mysql> show binary logs;
+——————+———–+
| Log_name | File_size |
+——————+———–+
| mysql-bin.000001 | 3548 |
| mysql-bin.000002 | 106 |
+——————+———–+
2 rows in set (0.00 sec)

1 当停止或重启服务器时，服务器会把日志文件记入下一个日志文件，Mysql会在重启时生成一个新的日志文件，文件序号递增；

2 如果日志文件超过max_binlog_size（默认值1G）系统变量配置的上限时，也会生成新的日志文件（在这里需要注意的是，如果你正使用大的事务，二进制日志还会超过max_binlog_size，不会生成新的日志文件，事务全写入一个二进制日志中,这种情况主要是为了保证事务的完整性）

3 日志被刷新时，新生成一个日志文件。

如：

binglog的查看通过mysqlbinlog命令可以查看binlog的内容 [root@localhost ~]# mysqlbinlog /home/mysql/binlog/binlog.000003 | more

/!40019 SET @@session.max_insert_delayed_threads=0/; /!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0/; DELIMITER /!/; # at 4 #120330 16:51:46 server id 1 end_log_pos 98 Start: binlog v 4, server v 5.0.45-log created 120330 1 6:51:46 # Warning: this binlog was not closed properly. Most probably mysqld crashed writing it. # at 196 #120330 17:54:15 server id 1 end_log_pos 294 Query thread_id=3 exec_time=2 error_code=0 SET TIMESTAMP=1333101255/!/; insert into tt7 select * from tt7/!/; # at 294 #120330 17:54:46 server id 1 end_log_pos 388 Query thread_id=3 exec_time=28 error_code=0 SET TIMESTAMP=1333101286/!/; alter table tt7 engine=innodb/!/;

解析binlog格式

位置位于文件中的位置，“at 294”说明“事件”的起点，是以第294字节开始；“end_log_pos 388 ”说明以第388 字节结束

时间戳事件发生的时间戳：“120330 17:54:46”

事件执行时间事件执行花费的时间:”exec_time=28″

错误码错误码为：“error_code=0”

服务器的标识服务器的标识id：“server id 1”

16.mysql的ACLS（事务）

MySQL 事务主要用于处理操作量大，复杂度高的数据。比如说，在人员管理系统中，你删除一个人员，你既需要删除人员的基本资料，也要删除和该人员相关的信息，如信箱，文章等等，这样，这些数据库操作语句就构成一个事务！

在 MySQL 中只有使用了 Innodb 数据库引擎的数据库或表才支持事务。
事务处理可以用来维护数据库的完整性，保证成批的 SQL 语句要么全部执行，要么全部不执行。
事务用来管理 insert,update,delete 语句

一般来说，事务是必须满足4个条件（ACID）：：原子性（Atomicity，或称不可分割性）、一致性（Consistency）、隔离性（Isolation，又称独立性）、持久性（Durability）。

**原子性：**一个事务（transaction）中的所有操作，要么全部完成，要么全部不完成，不会结束在中间某个环节。事务在执行过程中发生错误，会被回滚（Rollback）到事务开始前的状态，就像这个事务从来没有执行过一样。
**一致性：**在事务开始之前和事务结束以后，数据库的完整性没有被破坏。这表示写入的资料必须完全符合所有的预设规则，这包含资料的精确度、串联性以及后续数据库可以自发性地完成预定的工作。
**隔离性：**数据库允许多个并发事务同时对其数据进行读写和修改的能力，隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。事务隔离分为不同级别，包括读未提交（Read uncommitted）、读提交（read committed）、可重复读（repeatable read）和串行化（Serializable）。
**持久性：**事务处理结束后，对数据的修改就是永久的，即便系统故障也不会丢失。

在 MySQL 命令行的默认设置下，事务都是自动提交的，即执行 SQL 语句后就会马上执行 COMMIT 操作。因此要显式地开启一个事务务须使用命令 BEGIN 或 START TRANSACTION，或者执行命令 SET AUTOCOMMIT=0，用来禁止使用当前会话的自动提交。

事务控制语句：

BEGIN 或 START TRANSACTION 显式地开启一个事务；
COMMIT 也可以使用 COMMIT WORK，不过二者是等价的。COMMIT 会提交事务，并使已对数据库进行的所有修改成为永久性的；
ROLLBACK 也可以使用 ROLLBACK WORK，不过二者是等价的。回滚会结束用户的事务，并撤销正在进行的所有未提交的修改；
SAVEPOINT identifier，SAVEPOINT 允许在事务中创建一个保存点，一个事务中可以有多个 SAVEPOINT；
RELEASE SAVEPOINT identifier 删除一个事务的保存点，当没有指定的保存点时，执行该语句会抛出一个异常；
ROLLBACK TO identifier 把事务回滚到标记点；
SET TRANSACTION 用来设置事务的隔离级别。InnoDB 存储引擎提供事务的隔离级别有READ UNCOMMITTED、READ COMMITTED、REPEATABLE READ 和 SERIALIZABLE。

MYSQL 事务处理主要有两种方法：

1、用 BEGIN, ROLLBACK, COMMIT来实现

BEGIN 开始一个事务
ROLLBACK 事务回滚
COMMIT 事务确认

2、直接用 SET 来改变 MySQL 的自动提交模式:

SET AUTOCOMMIT=0 禁止自动提交
SET AUTOCOMMIT=1 开启自动提交

## 事务测试mysql> use RUNOOB; Database changed mysql> CREATE TABLE runoob_transaction_test( id int(5)) engine=innodb;  # 创建数据表 Query OK, 0 rows affected (0.04 sec)  mysql> select * from runoob_transaction_test; Empty set (0.01 sec)  mysql> begin;  # 开始事务 Query OK, 0 rows affected (0.00 sec)  mysql> insert into runoob_transaction_test value(5); Query OK, 1 rows affected (0.01 sec)  mysql> insert into runoob_transaction_test value(6); Query OK, 1 rows affected (0.00 sec)  mysql> commit; # 提交事务 Query OK, 0 rows affected (0.01 sec)  mysql>  select * from runoob_transaction_test; +------+ | id   | +------+ | 5    | | 6    | +------+ 2 rows in set (0.01 sec)  mysql> begin;    # 开始事务 Query OK, 0 rows affected (0.00 sec)  mysql>  insert into runoob_transaction_test values(7); Query OK, 1 rows affected (0.00 sec)  mysql> rollback;   # 回滚 Query OK, 0 rows affected (0.00 sec)  mysql>   select * from runoob_transaction_test;   # 因为回滚所以数据没有插入 +------+ | id   | +------+ | 5    | | 6    | +------+ 2 rows in set (0.01 sec)  mysql>

PHP中使用事务实例

## MySQL ORDER BY 测试：<?php $dbhost = 'localhost';  // mysql服务器主机地址 $dbuser = 'root';            // mysql用户名 $dbpass = '123456';          // mysql用户名密码 $conn = mysqli_connect($dbhost, $dbuser, $dbpass); if(! $conn ) {    die('连接失败: ' . mysqli_error($conn)); } // 设置编码，防止中文乱码 mysqli_query($conn, "set names utf8"); mysqli_select_db( $conn, 'RUNOOB' ); mysqli_query($conn, "SET AUTOCOMMIT=0"); // 设置为不自动提交，因为MYSQL默认立即执行 mysqli_begin_transaction($conn);            // 开始事务定义  if(!mysqli_query($conn, "insert into runoob_transaction_test (id) values(8)")) {    mysqli_query($conn, "ROLLBACK");     // 判断当执行失败时回滚 }  if(!mysqli_query($conn, "insert into runoob_transaction_test (id) values(9)")) {    mysqli_query($conn, "ROLLBACK");      // 判断执行失败时回滚 } mysqli_commit($conn);            //执行事务 mysqli_close($conn); ?>

17.mysql的mvcc

本文给大家详细的类介绍下MVCC的内容，MVCC对大家的工作和面试都是非常重要的内容。

一、前置内容

1.ACID

在看MVCC之前我们先补充些基础内容，首先来看下事务的ACID。

2.MySQL的核心日志

在MySQL数据库中有三个非常重要的日志binlog,undolog,redolog.

举例演示为：

3.隔离级别

MySQL的事务隔离级别有四个，分别为：

事务隔离级别指的是一个事务对数据的修改与另一个并行的事务的隔离程度，当多个事务同时访问相同数据时，如果没有采取必要的隔离机制，就可能发生以下问题：

问题描述脏读一个事务读到另一个事务未提交的更新数据，所谓脏读，就是指事务A读到了事务B还没有提交的数据，比如银行取钱，事务A开启事务，此时切换到事务B，事务B开启事务–>取走100元，此时切换回事务A，事务A读取的肯定是数据库里面的原始数据，因为事务B取走了100块钱，并没有提交，数据库里面的账务余额肯定还是原始余额，这就是脏读幻读是指当事务不是独立执行时发生的一种现象，例如第一个事务对一个表中的数据进行了修改，这种修改涉及到表中的全部数据行。同时，第二个事务也修改这个表中的数据，这种修改是向表中插入一行新数据。那么，以后就会发生操作第一个事务的用户发现表中还有没有修改的数据行，就好象发生了幻觉一样。不可重复读在一个事务里面的操作中发现了未被操作的数据比方说在同一个事务中先后执行两条一模一样的select语句，期间在此次事务中没有执行过任何DDL语句，但先后得到的结果不一致，这就是不可重复读隔离级别描述READ_UNCOMITTED读未提交（脏读）最低的隔离级别，一切皆有可能。READ_COMMITED读已提交，ORACLE默认隔离级别，有幻读以及不可重复读风险。REPEATABLE_READ可重复读，解决不可重复读的隔离级别，但还是有幻读风险。SERLALIZABLE串行化，最高的事务隔离级别，不管多少事务，挨个运行完一个事务的所有子事务之后才可以执行另外一个事务里面的所有子事务，这样就解决了脏读、不可重复读和幻读的问题了在这里插入图片描述数据库的并发场景：

读读：不会存在任何问题，也不需要并发控制
读写：有线程安全问题，可能会造成事务隔离性问题，可能遇到脏读，幻读，不可重复读，需要MVCC控制
写写：有线程安全问题，可能会存在更新丢失问题，比如第一类更新丢失，第二类更新丢失

二、MVCC

1.什么是MVCC

MVCC（Multi-Version Concurrency Control）：多版本并发控制，是一种并发控制的方法，一般在数据库管理系统中，实现对数据库的并发访问，在编程语言中实现事务内存。
MVCC 在 MySQL InnoDB 中的实现主要是为了提高数据库并发性能，用更好的方式去处理读-写冲突，做到即使有读写冲突时，也能做到不加锁，非阻塞并发读。

2.什么是当前读和快照读

在学习 MVCC 多版本并发控制之前，我们必须先了解一下，什么是 MySQL InnoDB 下的当前读和快照读。

在这里插入图片描述

其实MVCC 就是为了实现读-写冲突不加锁，而这个读指的就是快照读, 而非当前读，当前读实际上是一种加锁的操作，是悲观锁的实现.

3.三者关系

当前读，快照度和MVCC的关系是如何的呢？

MVCC 多版本并发控制是(维持一个数据的多个版本，使得读写操作没有冲突) 的概念，只是一个抽象概念，并非实现
因为 MVCC 只是一个抽象概念，要实现这么一个概念，MySQL 就需要提供具体的功能去实现它，(快照读就是 MySQL 实现 MVCC 理想模型的其中一个非阻塞读功能)。而相对而言，当前读就是悲观锁的具体功能实现
要说的再细致一些，快照读本身也是一个抽象概念，再深入研究。MVCC 模型在 MySQL 中的具体实现则是由 3 个隐式字段，undo 日志，Read View 等去完成的，具体可以看下面的 MVCC 实现原理

4.MVCC的好处

首先我们要清楚数据库中的并发场景有三种，分别是：

在这里插入图片描述
然后来看MVCC的好处：
多版本并发控制（MVCC）是一种用来解决读-写冲突的无锁并发控制，也就是为事务分配单向增长的时间戳，为每个修改保存一个版本，版本与事务时间戳关联，读操作只读该事务开始前的数据库的快照。所以 MVCC 可以为数据库解决以下问题:

在并发读写数据库时，可以做到在读操作时不用阻塞写操作，写操作也不用阻塞读操作，提高了数据库并发读写的性能
同时还可以解决脏读，幻读，不可重复读等事务隔离问题，但不能解决更新丢失问题

5.MVCC工作原理

MVCC 的目的就是多版本并发控制，在数据库中的实现，就是为了解决读写冲突，它的实现原理主要是依赖记录中的 3个隐式字段，undo日志，Read View 来实现的。

5.1 三个隐藏字段

在这里插入图片描述DB_ROW_ID 是数据库默认为该行记录生成的唯一隐式主键，DB_TRX_ID 是当前操作该记录的事务 ID ,而 DB_ROLL_PTR 是一个回滚指针，用于配合 undo日志，指向上一个旧版本
举例如：
在这里插入图片描述
在这里插入图片描述
在这里插入图片描述

从上面，我们就可以看出，不同事务或者相同事务的对同一记录的修改，会导致该记录的undo log成为一条记录版本线性表，既链表，undo log 的链首就是最新的旧记录，链尾就是最早的旧记录.
undolog：回滚日志，保存了事务发生之前的数据的一个版本，作用：

可以用于回滚
同时可以提供多版本并发控制下的读（MVCC），也即非锁定读。
事务开始之前，将当前事务版本生成 undo log，undo log 也会产生 redo log 来保证 undo log 的可靠性。
当事务提交之后，undo log 并不能立马被删除，而是放入待清理的链表。
由 purge 线程判断是否有其它事务在使用 undo 段中表的上一个事务之前的版本信息，从而决定是否可以清理 undo log 的日志空间。

5.2 ReadView

什么是 Read View，说白了 Read View 就是事务进行快照读操作的时候生产的读视图 (Read View)，在该事务执行的快照读的那一刻，会生成数据库系统当前的一个快照，记录并维护系统当前活跃事务的 ID (当每个事务开启时，都会被分配一个 ID , 这个 ID 是递增的，所以最新的事务，ID 值越大)

所以我们知道 Read View 主要是用来做可见性判断的, 即当我们某个事务执行快照读的时候，对该记录创建一个 Read View 读视图，把它比作条件用来判断当前事务能够看到哪个版本的数据，既可能是当前最新的数据，也有可能是该行记录的undo log里面的某个版本的数据。

Read View遵循一个可见性算法，主要是将要被修改的数据的最新记录中的 DB_TRX_ID（即当前事务 ID ）取出来，与系统当前其他活跃事务的 ID 去对比（由 Read View 维护），如果 DB_TRX_ID 跟 Read View 的属性做了某些比较，不符合可见性，那就通过 DB_ROLL_PTR 回滚指针去取出 Undo Log 中的 DB_TRX_ID 再比较，即遍历链表的 DB_TRX_ID（从链首到链尾，即从最近的一次修改查起），直到找到满足特定条件的 DB_TRX_ID , 那么这个 DB_TRX_ID 所在的旧记录就是当前事务能看见的最新老版本.

在这里插入图片描述
举例说明：
在这里插入图片描述
分析：
在这里插入图片描述

第二种情况：

在这里插入图片描述
分析：
在这里插入图片描述

最后完成的绘图：

在这里插入图片描述

扩展阅读:https://www.ixiera.com

总结：能否看到事务修改的数据，取决于可见性算法，可见性算法比较的时候又取决于ReadView中的结果值！因为在不同隔离级别的时候，生成ReadView的时机是不同的RC：每次执行时快照读都会重新生成新的ReadViewRR：只有当第一次事务进行快照读的时候才会生成ReadView，之后的快照读操作都会复用当前的ReadView 。

18.mysql锁，每个锁的应用场景

本文将深入探讨MySQL数据库锁的各种类型及其应用场景。MySQL数据库锁是数据库管理系统中的一种重要机制，通过锁定数据库中的数据来保证数据的完整性和一致性。MySQL数据库锁的种类很多，不同的锁有不同的应用场景，因此需要根据具体情况选择合适的锁类型。

什么是MySQL数据库锁？

MySQL数据库锁是一种机制，用于保护多个用户同时访问数据库时的数据完整性和一致性。在MySQL中，锁可以分为共享锁和排它锁两种类型。共享锁用于读取数据，而排它锁用于修改数据。当一个事务获得了排它锁后，其他事务就不能再对该数据进行修改，直到该事务释放锁为止。

MySQL数据库锁的种类有哪些？

MySQL数据库锁的种类有很多，常见的锁类型有以下几种：

（1）表锁：锁定整张表，其他事务不能对该表进行读写操作。

（2）行锁：锁定表中的某一行数据，其他事务不能对该行数据进行修改。

（3）读锁：用于读取数据时加锁，多个读锁可以同时存在。

（4）写锁：用于修改数据时加锁，同一时刻只能存在一个写锁。

（5）意向锁：用于辅助表锁和行锁，表示事务将要对一个表或行进行读写操作。

MySQL数据库锁的应用场景有哪些？

MySQL数据库锁的应用场景很广泛，常见的应用场景有以下几种：

（1）高并发场景：在高并发场景下，为了保证数据的完整性和一致性，需要使用锁机制来避免多个事务同时对同一数据进行修改。

（2）事务处理：在事务处理中，需要使用锁机制来保证事务的原子性、一致性、隔离性和持久性。

（3）备份与恢复：在备份和恢复数据时，需要使用锁机制来保证数据的一致性。在备份时需要加锁，防止数据被修改，而在恢复时需要解锁，使其他事务可以访问数据。

如何选择合适的MySQL数据库锁？

选择合适的MySQL数据库锁需要根据具体情况进行选择。在进行锁选择时，需要考虑以下几个方面：

（1）锁的类型：根据业务需求选择合适的锁类型，如读锁、写锁、行锁或表锁。

（2）锁的粒度：锁的粒度越小，锁的并发性越高，但也会增加锁的开销。因此，需要根据具体情况选择适当的锁粒度。

（3）锁的优先级：不同的锁有不同的优先级，需要根据业务需求和性能要求选择合适的锁优先级。

总之，在选择MySQL数据库锁时，需要根据具体情况进行选择，以达到最优的锁效果。

以上就是本文关于MySQL数据库锁的深入解析及其应用场景的详细介绍。MySQL数据库锁是数据库管理系统中非常重要的机制，了解MySQL数据库锁的种类和应用场景，能够更好地保证数据的完整性和一致性，提高系统的性能和稳定性。

19.什么情况下会照成死锁，举个例子

1.什么是死锁

死锁主要是锁彼此间进行锁等待，导致每个锁都不能正常执行的情况

例子1：多个锁相互等待造成死锁

假设有两个锁对象为lock1，lock2

线程t1对lock1进行加锁操作，在lock1加锁操作中多lock2再进行加锁操作

线程t2先对lock2进行加锁操作，在lock2加锁操作中又对lock1进行加锁

此时就会造成死锁

伪代码：

public class Demo3 {public static void main(String[] args) {Object lock1 = new Object();Object lock2 = new Object();Thread t1 = new Thread(() -> {while(true){synchronized (lock1){synchronized (lock2){System.out.println("hello t1");}}}});Thread t2 = new Thread(() -> {while(true){synchronized (lock2){synchronized (lock1){System.out.println("hello t2");}}}});t1.start();t2.start();}}

上述代码则必然会造成死锁

t1 和 t2 线程同时执行

当t1线程对lock1加锁成功了之后，此时lock1已经被占用了，然后t2线程对lock2加锁成功了，此时lock2已经被占用了；

在这种情况下，t1线程在去尝试对lock2进行加锁则会进行锁等待，因为lock2进行被线程t2占用了，那么此时t2线程去尝试对lock1进行加锁也是同理，此时t1占用个lock1不放，t2占用着lock2不放，导致对方都拿不到锁进行等待，那么此时t1和t2线程就不能向下执行死锁了

例子2：同一个线程对同一个锁进行加锁并且该锁是不可重入锁，自己给自己进行锁等待，必然死锁

假设有一个锁lock

代码：

public class Demo4 {public static void main(String[] args) {Object lock = new Object();int a = 0;int b = 0;synchronized (lock){a++;synchronized (lock){b++;}}}
}

如果是不可重入锁的话上面代码必然死锁

原因，第一次对lock加锁，是此时lock已经被自己占用了，第二次再对lock加锁则发现lock已经被占用此时进行等待lock释放，但是线程已经锁等待了，此时肯定是等不到lock释放的，则造成死锁

注意：由于synchronized是可重入锁，所以上述代码并不会死锁，上面代码只是举个例子说明这种情况

2.形成死锁的原因（四个必要条件）

本质原因就是，锁之间进行相互等待，等待不到也不会释放手中的资源，相互进行死等导致死锁

而造成这样的底层原因为如下几点：

互斥使用：锁的特性是每次只能有一个线程对锁进行使用，当锁没解锁时，锁资源不会被其他线程正常获取到
不可抢占：资源请求者不能从资源占用者那里抢占到资源，资源只能被占用者主动释放后，请求者才能去获取资源
请求和保持：锁资源请求者在请求其他锁资源时，会保持手中锁资源的占用

如上面例1，t1占用了lock1，再去请求lock2时会仍然占用这lock1，导致其他请求者获取不到

循环等待：线程之间循环占用着彼此的资源，导致彼此间都获取不到资源去正常运行导致死锁

如上面例1：t1占用这个lock1，且正在亲请求lock2，t2占用这lock2，且正在请求lock1；

此时t1占用着t2请求的资源，t2占用着t1请求的资源，这样形成了一个请求闭环，导致相互拿不到资源进而死锁

注意：只要形成了死锁，则必然满足上面四个条件

3.如果有效避免死锁

既然需要避免死锁，那么就需要根据造成死锁的原因入手。根据上面四条必要条件

1，2点是锁为了保证线程安全锁持有的的特性改变不了

但是3，4则是编码者自己写出来的可以避免

只要破坏了3，4点其中一条自然也就不会死锁

死锁与前面的四个原因是必要关系

方法：破坏循环等待的环路

我们只要保证加锁的顺序一致，那么环路就会得到有效的破坏

前面的例1进行改写：

把两个线程的加锁顺序改为一致

public class Demo3 {public static void main(String[] args) {Object lock1 = new Object();Object lock2 = new Object();Thread t1 = new Thread(() -> {while(true){synchronized (lock1){synchronized (lock2){System.out.println("hello t1");}}}});Thread t2 = new Thread(() -> {while(true){synchronized (lock1){synchronized (lock2){System.out.println("hello t2");}}}});t1.start();t2.start();}}

此时 t1 和 t2 线程都是先对lock1进行加锁，再对lock2进行加锁，此时就不会产生资源彼此占用的环路了也就不会进行死锁等待

解析：

t1先占用了lock1锁，此时t2就获取不到lock1锁了，那么此时t2就在当前代码位置进行锁等待了，更加执行不到去尝试占用lock2锁的代码了，那么t1线程就能正常拿到lock2的锁资源了，所以此时t1和t2只会执行一个线程，不会形成请求资源的闭环。

20.事务安全（隔离级别）

1.引言

数据库事务的隔离级别有4种，由低到高分别为Read uncommitted（读未提交）、Read committed （读已提交）、Repeatable read （重复读）、Serializable （序列化）。读现象是在多个事务并发执行时，在读取数据方面可能碰到的问题。包括脏读、不可重复读、幻读。

脏读：读到了脏数据，即无效数据。

不可重复读：是指在数据库访问中，一个事务内的多次相同查询却返回了不同数据。

幻读：指同一个事务内多次查询返回的结果集不一样，比如增加了行记录。

备注：不可重复读对应的是修改，即update操作。幻读对应的是插入操作。幻读是不可重复读的一种特殊场景。

要想解决脏读、不可重复读、幻读等读现象，那么就需要提高事务的隔离级别。但是随之带来的，隔离级别越高，并发能力越低。所以，需要根据业务去进行衡量，具体场景应该使用哪种隔离级别。

下面通过事例一一阐述它们的概念与联系。

2.事务隔离级别

2.1 Read uncommitted（读未提交）

提供了事务建最小限度的隔离。顾名思义，就是一个事务可以读取另一个未提交事务的数据。

示例：小明去商店买衣服，付款的时候，小明正常付款，钱已经打到商店老板账户，但是小明发起的事务还没有提交。就在这时，商店老板查看自己账户，发现钱已到账，于是小明正常离开。小明在走出商店后，马上回滚差点提交的事务，撤销了本次交易曹邹。

结果：小明未付钱买到了衣服，商店老板实际未收到小明的付款。

分析：商店老板查看自己的资金账户，这个时候看到的是小明还没有提交事务的付款。这就是脏读。

注意：处于该隔离级别的事务A与B，如果事务A使用事务B不提交的变化作为计算的基础，然后哪些未提交的变化被事务A撤销，这就导致了大量的数据错误变化。

2.2 Read committed （读已提交）

处于Read committed （读已提交）级别的事务可以看到其他事务对数据的修改。也就是说，在事务处理期间，如果其他事务修改了相应的表，那么同一个事务的同一sql在其他事务执行前后返回的是不同的结果。一个事务要等另一个事务提交后才能读取数据。

示例：小明卡里有1000元，准备与几个朋友聚餐消费，消费1000元，当他买单时（事务开启），收费系统检测到他卡里有1000元。就在检测完毕的时候，小明女朋友发现小明有私房钱，全部转走并提交。当收费系统准备扣款时，再检查小明卡里的金额，发现已经没钱了，付款不成功。小明此时就会很纳闷，明明有钱的呀，钱呢？

分析：该示例中同一个事务范围内两个相同的查询却返回了不同数据，这就是不可重复读。该隔离级别可以解决脏读问题。

2.3 Repeatable read （重复读）

在开始读取数据（事务开启）时，不再允许修改操作

示例：还是小明有1000元，准备跟朋友聚餐消费这个场景，当他买单（事务开启）时，收费系统检测到他卡里有1000元，这个时候，他的女朋友不能转出金额。接下来，收费系统就可以扣款成功了，小明醉醺醺的回家，准备跪脱衣板。

分析：重复读可以解决不可重复读的问题，这句话有些别扭，大家可以仔细品一下。

写到这里，大家可能会产生疑问，什么情况下产生幻读呢？

示例来了：

小明在公司上班，女朋友告诉他，拿着他的卡去逛街消费。花了一千元，然后小明去查看他银行卡的消费记录（事务开启），看到确实是花了一千元。就在这个时候，小明女朋友又花三千元买了一些化妆品和衣服，即新增了一些消费记录。当小明打印自己银行卡消费记录单的时候（女朋友事务提交），发现花了四千元，似乎出现了幻觉，小明很心疼。这就是幻读

扩展：当我们开启一个事务以后，有如下的程序操作

第一步：更新A表id=1的记录

第二步：查询A表id=1的记录

第三步：使用第二步的查询结果作为依据继续业务逻辑

第四步：提交事务

问题来了：同一个事务中，事务未提交前，第二步的查询结果是第一步执行前的结果还是第一步执行后的结果呢？

答案：事务隔离级别是针对不通事务的，同一事务中的未提交的更新，在后续是可以查询到的。

2.4 Serializable （序列化）

数据库事务的最高隔离级别。在此级别下，事务串行执行。可以避免脏读、不可重复读、幻读等读现象。但是效率低下，耗费数据库性能，不推荐使用。

3.mysql事务隔离级别查询

3.1mysql查看数据库实例默认的全局隔离级别sql

Mysql8以前：SELECT @@GLOBAL.tx_isolation, @@tx_isolation;

Mysql8开始：SELECT @@GLOBAL.transaction_isolation, @@transaction_isolation;

3.2修改隔离级别命令：

SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

建议开发者在修改时，仅修改当前session隔离级别即可。

3.3mysql默认隔离级别

REPEATABLE-READ，可以避免脏读，不可重复读，不可避免幻读

21.你的项目死锁怎么检测的

如果程序出现死锁问题，可通过以下 3种方案中的任意一种进行分析和排查。
方案一：jstack
在使用 jstack 之前，我们需要先通过 jps -l得到运行程序的进程 ID，使用方法如下：

在这里插入图片描述

有了进程 ID(PID)之后，我们就可以使用“jstack -l PID”来发现死锁问题了，如下图所示：

在这里插入图片描述

方案二：jconsole
jconsole工具位于jdk的bin目录下，如图：

在这里插入图片描述

双击进入，选择要调试的程序，如下图所示：

在这里插入图片描述

会出现如下提示，选择不安全的链接

在这里插入图片描述

点击线程栏目，然后点下面死锁，就会检测出来了

在这里插入图片描述

方案三：jvisualvm
jvisualvm 也在 JDK 的 bin 目录中，同样是双击打开：

在这里插入图片描述

点击线程栏目，如果发生死锁，会自动提示如下：

在这里插入图片描述

点击线程dump:

在这里插入图片描述

以上是jdk8以上版本排查线程死锁的常用三种方案，还有一种是根据jmc（Oracle Java Mission Control ）排查，jmc是一个对 Java 程序进行管理、监控、概要分析和故障排查的工具套件，它也是在 JDK 的 bin 目录中。但是jdk1.8之后，jmc就已经不随着jdk一起发出去了，如果想要使用jmc对jvm进行监控，这时候需要手工下载jmc，然后在本地运行。oracle官网比较坑，现在只有jmc8.1版本下载，它需要jdk11,但作者暂时还没亲自试过，所以先不写了。

22.数据库三大范式（忘了）

1、第一范式(1NF)

所谓第一范式（1NF）是指在关系模型中，对于添加的一个规范要求，所有的域都应该是原子性的，即数据库表的每一列都是不可分割的原子数据项，而不能是集合，数组，记录等非原子数据项。即实体中的某个属性有多个值时，必须拆分为不同的属性。在符合第一范式（1NF）表中的每个域值只能是实体的一个属性或一个属性的一部分。简而言之，第一范式就是无重复的域。

在任何一个关系数据库中，第一范式（1NF）是对关系模式的设计基本要求，一般设计中都必须满足第一范式（1NF）。不过有些关系模型中突破了1NF的限制，这种称为非1NF的关系模型。换句话说，是否必须满足1NF的最低要求，主要依赖于所使用的关系模型。

特点：属性不可分割，即每个属性都是不可分割的原子项。(实体的属性即表中的列)

2、第二范式(2NF)

在1NF的基础上，非码属性必须完全依赖于候选码（在1NF基础上消除非主属性对主码的部分函数依赖）。

第二范式（2NF）是在第一范式（1NF）的基础上建立起来的，即满足第二范式（2NF）必须先满足第一范式（1NF）。第二范式（2NF）要求数据库表中的每个实例或记录必须可以被唯一地区分。选取一个能区分每个实体的属性或属性组，作为实体的唯一标识。例如在员工表中的身份证号码即可实现每个一员工的区分，该身份证号码即为候选键，任何一个候选键都可以被选作主键。在找不到候选键时，可额外增加属性以实现区分，如果在员工关系中，没有对其身份证号进行存储，而姓名可能会在数据库运行的某个时间重复，无法区分出实体时，设计辟如ID等不重复的编号以实现区分，被添加的编号或ID选作主键。（该主键的添加是在ER设计时添加，不是建库时随意添加）。

第二范式（2NF）要求实体的属性完全依赖于主关键字。所谓完全依赖是指不能存在仅依赖主关键字一部分的属性，如果存在，那么这个属性和主关键字的这一部分应该分离出来形成一个新的实体，新实体与原实体之间是一对多的关系。为实现区分通常需要为表加上一个列，以存储各个实例的唯一标识。简而言之，第二范式就是在第一范式的基础上属性完全依赖于主键。

特点：

满足第一范式；
第二范式需要确保数据表中的每一列都和主键相关，而不能只与主键的某一部分相关（主要针对联合主键而言），每张表只描述一件事情；
消除部分依赖，要求一张表中的每一列都完全依赖于主键（针对于组合主键），也就是不会出现某一列只和部分主键相关。

3、第三范式(3NF)

在2NF基础上，任何非主属性不依赖于其它非主属性（在2NF基础上消除传递依赖）。

第三范式（3NF）是第二范式（2NF）的一个子集，即满足第三范式（3NF）必须满足第二范式（2NF）。简而言之，第三范式（3NF）要求一个关系中不包含已在其它关系已包含的非主关键字信息。例如，存在一个部门信息表，其中每个部门有部门编号（dept_id）、部门名称、部门简介等信息。那么在员工信息表中列出部门编号后就不能再将部门名称、部门简介等与部门有关的信息再加入员工信息表中。如果不存在部门信息表，则根据第三范式（3NF）也应该构建它，否则就会有大量的数据冗余。简而言之，第三范式就是属性不依赖于其它非主属性，也就是在满足2NF的基础上，任何非主属性不得传递依赖于主属性。

特点：

前提：满足第一范式和第二范式；
第三范式需要确保数据表中的每一列数据表和主键直接相关，而不能间接相关；
消除传递依赖，要求一张表中的每一列都和主键是直接依赖的，不是间接依赖。

23.如何加快数据检索的效率

数据检索速度慢的原因：
1、没有索引或者没有用到索引
2、I/O吞吐量小，形成了瓶颈效应。
3、没有创建计算列导致查询不优化。
4、内存不足
5、网络速度慢
6、查询出的数据量过大
7、锁或者死锁
8、sp_lock,sp_who,活动的用户查看,原因是读写竞争资源。
9、返回了不必要的行和列
10、查询语句不好，没有优化

优化方法：
1．合理使用索引
索引是数据库中重要的数据结构，它的根本目的就是为了提高查询效率。现在大多数的数据库产品都采用IBM最先提出的ISAM索引结构。索引的使用要恰到好处，其使用原则如下：
●在经常进行连接，但是没有指定为外键的列上建立索引，而不经常连接的字段则由优化器自动生成索引。
●在频繁进行排序或分组（即进行group by或order by操作）的列上建立索引。
●在条件表达式中经常用到的不同值较多的列上建立检索，在不同值少的列上不要建立索引。比如在雇员表的“性别”列上只有“男”与“女”两个不同值，因此就无必要建立索引。如果建立索引不但不会提高查询效率，反而会严重降低更新速度。
●如果待排序的列有多个，可以在这些列上建立复合索引（compound index）。
●使用系统工具。如Informix数据库有一个tbcheck工具，可以在可疑的索引上进行检查。在一些数据库服务器上，索引可能失效或者因为频繁操作而使得读取效率降低，如果一个使用索引的查询不明不白地慢下来，可以试着用tbcheck工具检查索引的完整性，必要时进行修复。另外，当数据库表更新大量数据后，删除并重建索引可以提高查询速度。

使用临时表加速查询
把表的一个子集进行排序并创建临时表，有时能加速查询。它有助于避免多重排序操作，而且在其他方面还能简化优化器的工作。

3．用排序来取代非顺序存取
非顺序磁盘存取是最慢的操作，表现在磁盘存取臂的来回移动。SQL语句隐藏了这一情况，使得我们在写应用程序时很容易写出要求存取大量非顺序页的查询。

把数据、日志、索引放到不同的I/O设备上，增加读取速度
以前可以将Tempdb应放在RAID0上，SQL2000不在支持。数据量（尺寸）越大，提高I/O越重要.
纵向、横向分割表，减少表的尺寸

6.扩大服务器的内存
Windows 2000和SQL server 2000能支持4-8G的内存。配置虚拟内存：虚拟内存大小应基于计算机上并发运行的服务进行配置。运行 Microsoft SQL Server? 2000 时，可考虑将虚拟内存大小设置为计算机中安装的物理内存的 1.5 倍。如果另外安装了全文检索功能，并打算运行 Microsoft 搜索服务以便执行全文索引和查询，可考虑：将虚拟内存大小配置为至少是计算机中安装的物理内存的 3 倍。将 SQL Server max server memory 服务器配置选项配置为物理内存的 1.5 倍（虚拟内存大小设置的一半）。

增加服务器CPU个数
但是必须明白并行处理串行处理更需要资源例如内存。使用并行还是串行程是MsSQL自动评估选择的。单个任务分解成多个任务，就可以在处理器上运行。例如耽搁查询的排序、连接、扫描和GROUP BY字句同时执行，SQL SERVER根据系统的负载情况决定最优的并行等级，复杂的需要消耗大量的CPU的查询最适合并行处理。但是更新操作UPDATE,INSERT， DELETE还不能并行处理。
优化查找语句
在查询Select语句中用Where字句限制返回的行数，避免表扫描,如果返回不必要的数据，浪费了服务器的I/O资源，加重了网络的负担降低性能。如果表很大，在表扫描的期间将表锁住，禁止其他的联接访问表,后果严重。

9.尽可能不使用游标
它占用大量的资源。

用Profiler来跟踪查询
得到查询所需的时间找出SQL的问题所在;用索引优化器优化索引
尽量少用视图
它的效率低。对视图操作比直接对表操作慢,可以用stored procedure来代替她。特别的是不要用视图嵌套,嵌套视图增加了寻找原始资料的难度。我们看视图的本质：它是存放在服务器上的被优化好了的已经产生了查询规划的SQL。对单个表检索数据时，不要使用指向多个表的视图，直接从表检索或者仅仅包含这个表的视图上读，否则增加了不必要的开销,查询受到干扰.为了加快视图的查询，MsSQL增加了视图索引的功能。

24…注册登陆的用户名和密码存在哪里？（数据库）

Mysql用户名密码存放在Mysql的系统数据库中的user表中。

25.面试官灵魂4连问：乐观锁与悲观锁的概念、实现方式、场景、优缺点？

01 乐观锁与悲观锁的基本概念

何为乐观锁与悲观锁？

答：乐观锁对应于生活中乐观的人总是想着事情往好的方向发展，悲观锁对应于生活中悲观的人总是想着事情往坏的方向发展。这两种人各有优缺点，不能不以场景而定说一种人好于另外一种人。乐观锁和悲观锁是两种思想，用于解决并发场景下的数据竞争问题。

乐观锁：乐观锁在操作数据时非常乐观，认为别人不会同时修改数据。

因此乐观锁不会上锁，只是在执行更新的时候判断一下在此期间别人是否修改了数据：如果别人修改了数据则放弃操作，否则执行操作。

悲观锁：悲观锁在操作数据时比较悲观，认为别人会同时修改数据。

因此操作数据时直接把数据锁住，直到操作完成后才会释放锁；上锁期间其他人不能修改数据。

02 乐观锁与悲观锁的实现方式(含实例)

在说明实现方式之前，需要明确：乐观锁和悲观锁是两种思想，它们的使用是非常广泛的，不局限于某种编程语言或数据库。

悲观锁的实现方式是加锁，加锁既可以是对代码块加锁（如Java的synchronized关键字），也可以是对数据加锁（如MySQL中的排它锁）。

2.1 乐观锁的实现方式主要有两种：CAS机制和版本号机制

（1）CAS（Compare And Swap）

CAS操作包括了3个操作数：

 1) 需要读写的内存位置(V)2) 进行比较的预期值(A)3) 拟写入的新值(B)

CAS操作逻辑如下：

如果内存位置V的值等于预期的A值，则将该位置更新为新值B，否则不进行任何操作。

许多CAS的操作是自旋的：如果操作不成功，会一直重试，直到操作成功为止。

这里引出一个新的问题，既然CAS包含了Compare和Swap两个操作，它又如何保证原子性呢？
答案是：CAS是由CPU支持的原子操作，其原子性是在硬件层面进行保证的。

下面以Java中的自增操作(i++)为例，看一下悲观锁和CAS分别是如何保证线程安全的。

我们知道，在Java中自增操作不是原子操作，它实际上包含三个独立的操作：

1) 读取i值；
2) 加1；
3) 将新值写回i

因此，如果并发执行自增操作，可能导致计算结果的不准确。

在下面的代码示例中：value1没有进行任何线程安全方面的保护，value2使用了乐观锁(CAS)，value3使用了悲观锁(synchronized)。

运行程序，使用1000个线程同时对value1、value2和value3进行自增操作，可以发现：value2和value3的值总是等于1000，而value1的值常常小于1000。

public class Test {//value1：线程不安全  private static int value1 = 0;//value2：使用乐观锁  private static AtomicInteger value2 = new AtomicInteger(0);//value3：使用悲观锁  private static int value3 = 0;private static synchronized void increaseValue3() {value3++;}public static void main(String[] args) throws Exception {//开启1000个线程，并执行自增操作  for (int i = 0; i < 1000; ++i) {new Thread(new Runnable() {@Overridepublic void run() {try {Thread.sleep(100);} catch (InterruptedException e) {e.printStackTrace();}value1++;value2.getAndIncrement();increaseValue3();}}).start();}//查看活跃线程 ,因守护线程的原因[基于工具问题windows:idea run 启动用 >2,debug 用>1] while (Thread.activeCount() > 2) {//Thread.currentThread().getThreadGroup().list();Thread.yield();//让出cpu}//打印结果  Thread.sleep(1000);System.out.println("线程不安全：" + value1);System.out.println("乐观锁(AtomicInteger)：" + value2);System.out.println("悲观锁(synchronized)：" + value3);}
}

上面的代码中:

1)首先来介绍AtomicInteger。

AtomicInteger是java.util.concurrent.atomic包提供的原子类，利用CPU提供的CAS操作来保证原子性；

除了AtomicInteger外，还有AtomicBoolean、AtomicLong、AtomicReference等众多原子类。

下面看一下AtomicInteger的源码，了解下它的自增操作getAndIncrement()是如何实现的（源码以Java7为例，Java8有所不同，但思想类似）。

public class AtomicInteger extends Number implements java.io.Serializable { 
//存储整数值，volatile保证可视性 
private volatile int value;//Unsafe用于实现对底层资源的访问private static final Unsafe unsafe = Unsafe.getUnsafe();//valueOffset是value在内存中的偏移量  
private static final long valueOffset;  
//通过Unsafe获得valueOffset  
static {  try {  valueOffset = unsafe.objectFieldOffset(AtomicInteger.class.getDeclaredField("value"));  } catch (Exception ex) { throw new Error(ex); }  
}  public final boolean compareAndSet(int expect, int update) {  return unsafe.compareAndSwapInt(this, valueOffset, expect, update);  
}  public final int getAndIncrement() {  for (;;) {  int current = get();  int next = current + 1;  if (compareAndSet(current, next))  return current;  }  
}  
}

源码分析说明如下：

getAndIncrement()实现的自增操作是自旋CAS操作：在循环中进行compareAndSet，如果执行成功则退出，否则一直执行。
其中compareAndSet是CAS操作的核心，它是利用Unsafe对象实现的。
Unsafe又是何许人也呢？

Unsafe是用来帮助Java访问操作系统底层资源的类（如可以分配内存、释放内存,在netty中大量用到它,属于C++层面的native方法,我们一般使用反射获取,哈哈~），通过Unsafe，Java具有了底层操作能力，可以提升运行效率；

强大的底层资源操作能力也带来了安全隐患(类的名字Unsafe也在提醒我们这一点)，因此正常情况下用户无法使用。

AtomicInteger在这里使用了Unsafe提供的CAS功能。

valueOffset可以理解为value在内存中的偏移量，对应了CAS三个操作数(V/A/B)中的V；偏移量的获得也是通过Unsafe实现的。
value域的volatile修饰符：Java并发编程要保证线程安全，需要保证原子性、可视性和有序性；

CAS操作可以保证原子性，而volatile可以保证可视性和一定程度的有序性；

在AtomicInteger中，volatile和CAS一起保证了线程安全性。

关于volatile作用原理的说明涉及到Java内存模型(JMM)，这里不详细展开。

2) 说完了AtomicInteger，再说synchronized。

synchronized通过对代码块加锁来保证线程安全：在同一时刻，只能有一个线程可以执行代码块中的代码。

synchronized是一个重量级的操作，不仅是因为加锁需要消耗额外的资源，还因为线程状态的切换会涉及操作系统核心态和用户态的转换；

不过随着JVM对锁进行的一系列优化(如自旋锁、轻量级锁、锁粗化等)，synchronized的性能表现已经越来越好。

（2）版本号机制除了CAS，版本号机制也可以用来实现乐观锁

版本号机制的基本思路是在数据中增加一个字段version，表示该数据的版本号，每当数据被修改，版本号加1。

当某个线程查询数据时，将该数据的版本号一起查出来；
当该线程更新数据时，判断当前版本号与之前读取的版本号是否一致，如果一致才进行操作。

需要注意的是，这里使用了版本号作为判断数据变化的标记，实际上可以根据实际情况选用其他能够标记数据版本的字段，如时间戳等。

下面以“更新玩家金币数”为例（数据库为MySQL，其他数据库同理），看看悲观锁和版本号机制是如何应对并发问题的。

考虑这样一种场景：游戏系统需要更新玩家的金币数，更新后的金币数依赖于当前状态(如金币数、等级等)，因此更新前需要先查询玩家当前状态。

下面的实现方式，没有进行任何线程安全方面的保护。如果有其他线程在query和update之间更新了玩家的信息，会导致玩家金币数的不准确。

@Transactional
public void updateCoins(Integer playerId){//根据player_id查询玩家信息Player player = query("select coins, level from player where player_id = {0}", playerId);//根据玩家当前信息及其他信息，计算新的金币数Long newCoins = ……;//更新金币数update("update player set coins = {0} where player_id = {1}", newCoins, playerId);
}

为了避免这个问题，悲观锁通过加锁解决这个问题，代码如下所示。在查询玩家信息时，使用select …… for update进行查询；

该查询语句会为该玩家数据加上排它锁，直到事务提交或回滚时才会释放排它锁；

在此期间，如果其他线程试图更新该玩家信息或者执行select for update，会被阻塞(所以…如果你for update了,但是没有commit,那就一直锁着…锁住了青春~~~)。

@Transactional
public void updateCoins(Integer playerId){//根据player_id查询玩家信息（加排它锁）Player player = queryForUpdate("select coins, level from player where player_id = {0} for update", playerId);//根据玩家当前信息及其他信息，计算新的金币数Long newCoins = ……;//更新金币数update("update player set coins = {0} where player_id = {1}", newCoins, playerId);
}

版本号机制则是另一种思路，它为玩家信息增加一个字段：version。在初次查询玩家信息时，同时查询出version信息；

在执行update操作时，校验version是否发生了变化，如果version变化，则不进行更新。

@Transactional
public void updateCoins(Integer playerId){//根据player_id查询玩家信息，包含version信息Player player = query("select coins, level, version from player where player_id = {0}", playerId);//根据玩家当前信息及其他信息，计算新的金币数Long newCoins = ……;//更新金币数，条件中增加对version的校验update("update player set coins = {0} where player_id = {1} and version = {2}", newCoins, playerId, player.version);
}

03 乐观锁和悲观锁优缺点和适用场景

乐观锁和悲观锁并没有优劣之分，它们有各自适合的场景；下面从两个方面进行说明。

功能限制与悲观锁相比，乐观锁适用的场景受到了更多的限制，无论是CAS还是版本号机制。

例如，CAS只能保证单个变量操作的原子性，当涉及到多个变量时，CAS是无能为力的，而synchronized则可以通过对整个代码块加锁来处理。

再比如版本号机制，如果query的时候是针对表1，而update的时候是针对表2，也很难通过简单的版本号来实现乐观锁。

竞争激烈程度如果悲观锁和乐观锁都可以使用，那么选择就要考虑竞争的激烈程度：

当竞争不激烈 (出现并发冲突的概率小)时，乐观锁更有优势，因为悲观锁会锁住代码块或数据，其他线程无法同时访问，影响并发，而且加锁和释放锁都需要消耗额外的资源。

当竞争激烈(出现并发冲突的概率大)时，悲观锁更有优势，因为乐观锁在执行更新时频繁失败，需要不断重试，浪费CPU资源。

04 乐观锁加锁吗？

下面是我对这个问题的理解：

乐观锁本身是不加锁的，只是在更新时判断一下数据是否被其他线程更新了；AtomicInteger便是一个例子。
有时乐观锁可能与加锁操作合作，例如，在前述updateCoins()的例子中，MySQL在执行update时会加排它锁。

但这只是乐观锁与加锁操作合作的例子，不能改变“乐观锁本身不加锁”这一事实。

05 CAS有哪些缺点？

面试到这里，面试官可能已经中意你了。

不过面试官准备对你发起最后的进攻：你知道CAS这种实现方式有什么缺点吗？

下面是CAS一些不那么完美的地方：

1.ABA问题假设有两个线程——线程1和线程2，两个线程按照顺序进行以下操作：

(1)线程1读取内存中数据为A；
(2)线程2将该数据修改为B；
(3)线程2将该数据修改为A；
(4)线程1对数据进行CAS操作

在第(4)步中，由于内存中数据仍然为A，因此CAS操作成功，但实际上该数据已经被线程2修改过了。这就是ABA问题。

在AtomicInteger的例子中，ABA似乎没有什么危害。

但是在某些场景下，ABA却会带来隐患，例如栈顶问题：一个栈的栈顶经过两次(或多次)变化又恢复了原值，但是栈可能已发生了变化。

对于ABA问题，比较有效的方案是引入版本号，内存中的值每发生一次变化，版本号都+1；

在进行CAS操作时，不仅比较内存中的值，也会比较版本号，只有当二者都没有变化时，CAS才能执行成功。

Java中的AtomicStampedReference类便是使用版本号来解决ABA问题的。

2.高竞争下的开销问题在并发冲突概率大的高竞争环境下，如果CAS一直失败，会一直重试，CPU开销较大。

针对这个问题的一个思路是引入退出机制，如重试次数超过一定阈值后失败退出。

当然，更重要的是避免在高竞争环境下使用乐观锁。

3.功能限制 CAS的功能是比较受限的，例如CAS只能保证单个变量（或者说单个内存值）操作的原子性，这意味着：

(1)原子性不一定能保证线程安全，例如在Java中需要与volatile配合来保证线程安全；

(2)当涉及到多个变量(内存值)时，CAS也无能为力。

除此之外，CAS的实现需要硬件层面处理器的支持，在Java中普通用户无法直接使用，只能借助atomic包下的原子类使用，灵活性受到限制。

26.哪几种常见的 signal? SIGSEGV… -> 正常终止程序的信号？-> kill 进程，几号信号？

常见的 signal 包括：

SIGSEGV（段错误）：访问未分配内存或访问越界的内存会触发该信号；
SIGABRT（异常终止）：程序调用abort()函数会触发该信号；
SIGFPE（浮点异常）：执行除零操作或溢出操作会触发该信号；
SIGILL（非法指令）：执行非法指令会触发该信号；
SIGINT（中断）：用户在控制台按下Ctrl+C组合键时会向进程发送该信号。

正常终止程序的信号是SIGTERM，当进程接收到SIGTERM信号时，就会进行优雅退出。这意味着进程可以完成当前正在执行的任务，并释放资源。如果没有处理SIGTERM信号，则默认情况下进程将立即终止。

kill 命令默认使用9号信号(SIGKILL)来杀死进程，它是一种无条件终止进程的方式，不能被忽略、捕获或处理。如果想要使用其他信号来杀死进程，可以使用kill命令后面加上-s参数和对应的信号编号。例如：

复制代码kill -s TERM <PID>

这条命令将使用SIGTERM信号来杀死指定PID的进程。

27.一千五百万行数据如何快速找到某一行数据，给出方案，设计数据库表结构

要快速找到某一行数据，可以通过在数据库表中建立索引来实现。索引是一种数据结构，用于快速定位目标数据的位置。在查询时，数据库可以使用索引直接定位到目标数据，而不必扫描整个表。

以下是一个可能的数据库表结构设计：

sql复制代码CREATE TABLE data (id INT PRIMARY KEY AUTO_INCREMENT,value VARCHAR(255)
);

在上述设计中，我们创建了一个名为data的表，包含两列：id和value。id是主键，并自动递增生成唯一值。value列存储每一行的数据。

要加快查询速度，我们可以在value列上创建索引：

sql复制代码CREATE INDEX value_index ON data(value);

以上命令将为value列创建一个名为value_index的索引。此后，在查询时如果需要根据value查找某行数据，则可以通过以下SQL语句实现：

sql复制代码SELECT * FROM data WHERE value = 'target_value';

当执行以上语句时，MySQL会使用已经创建好的索引来加快查找过程，并返回符合条件的结果集。

请注意，在进行大规模数据存储和查询时，请考虑分库、分表等优化措施以提高性能和稳定性。

28.sql优化

前言

sql优化是一个大家都比较关注的热门话题，无论你在面试，还是工作中，都很有可能会遇到。

如果某天你负责的某个线上接口，出现了性能问题，需要做优化。那么你首先想到的很有可能是优化sql语句，因为它的改造成本相对于代码来说也要小得多。

那么，如何优化sql语句呢？

这篇文章从15个方面，分享了sql优化的一些小技巧，希望对你有所帮助。

1.避免使用select

很多时候，我们写sql语句时，为了方便，喜欢直接使用select *，一次性查出表中所有列的数据。

反例：

select * from user where id=1;

在实际业务场景中，可能我们真正需要使用的只有其中一两列。查了很多数据，但是不用，白白浪费了数据库资源，比如：内存或者cpu。

此外，多查出来的数据，通过网络IO传输的过程中，也会增加数据传输的时间。

还有一个最重要的问题是： select不会走覆盖索引，会出现大量的回表操作，而从导致查询sql的性能很低。

那么，如何优化呢？

正例

select name,age from user where id=1;

sql语句查询时，只查需要用到的列，多余的列根本无需查出来。

2.用union all代替union

我们都知道sql语句使用union关键字后，可以获取排重后的数据。而如果使用union all关键字，可以获取所有数据，包含重复的数据。

反例：

(select * from user where id=1) 
union 
(select * from user where id=2);

排重的过程需要遍历、排序和比较，它更耗时，更消耗cpu资源。所以如果能用union all的时候，尽量不用union。

正例：

(select * from user where id=1) 
union all
(select * from user where id=2);

除非是有些特殊的场景，比如union all之后，结果集中出现了重复数据，而业务场景中是不允许产生重复数据的，这时可以使用union。

3.小表驱动大表

小表驱动大表，也就是说用小表的数据集驱动大表的数据集。

假如有order和user两张表，其中order表有10000条数据，而user表有100条数据。时如果想查一下，所有有效的用户下过的订单列表。可以使用in关键字实现：

select * from order
where user_id in (select id from user where status=1)

也可以使用exists关键字实现：

select * from order
where exists (select 1 from user where order.user_id = user.id and status=1)

前面提到的这种业务场景，使用in关键字去实现业务需求，更加合适。

为什么呢？

因为如果sql语句中包含了in关键字，则它会优先执行in里面的子查询语句，然后再执行in外面的语句。如果in里面的数据量很少，作为条件查询速度更快。

而如果sql语句中包含了exists关键字，它优先执行exists左边的语句（即主查询语句）。然后把它作为条件，去跟右边的语句匹配。如果匹配上，则可以查询出数据。如果匹配不上，数据就被过滤掉了。

这个需求中，order表有10000条数据，而user表有100条数据。order表是大表，user表是小表。如果order表在左边，则用in关键字性能更好。

总结一下：

in 适用于左边大表，右边小表。
exists 适用于左边小表，右边大表。
不管是用in，还是exists关键字，其核心思想都是用小表驱动大表。

4.批量操作

如果你有一批数据经过业务处理之后，需要插入数据，该怎么办？

反例：

for(Order order: list){orderMapper.insert(order):
}

在循环中逐条插入数据。

insert into order(id,code,user_id) 
values(123,'001',100);

该操作需要多次请求数据库，才能完成这批数据的插入。

但众所周知，我们在代码中，每次远程请求数据库，是会消耗一定性能的。而如果我们的代码需要请求多次数据库，才能完成本次业务功能，势必会消耗更多的性能。

那么如何优化呢？

正例：

orderMapper.insertBatch(list):

提供一个批量插入数据的方法。

insert into order(id,code,user_id) 
values(123,'001',100),(124,'002',100),(125,'003',101);

这样只需要远程请求一次数据库，sql性能会得到提升，数据量越多，提升越大。

但需要注意的是，不建议一次批量操作太多的数据，如果数据太多数据库响应也会很慢。批量操作需要把握一个度，建议每批数据尽量控制在500以内。如果数据多于500，则分多批次处理。

5.多用limit

有时候，我们需要查询某些数据中的第一条，比如：查询某个用户下的第一个订单，想看看他第一次的首单时间。

反例：

select id, create_date 
from order 
where user_id=123 
order by create_date asc;

根据用户id查询订单，按下单时间排序，先查出该用户所有的订单数据，得到一个订单集合。然后在代码中，获取第一个元素的数据，即首单的数据，就能获取首单时间。

List<Order> list = orderMapper.getOrderList();
Order order = list.get(0);

虽说这种做法在功能上没有问题，但它的效率非常不高，需要先查询出所有的数据，有点浪费资源。

那么，如何优化呢？

正例：

select id, create_date 
from order 
where user_id=123 
order by create_date asc 
limit 1;

使用limit 1，只返回该用户下单时间最小的那一条数据即可。

此外，在删除或者修改数据时，为了防止误操作，导致删除或修改了不相干的数据，也可以在sql语句最后加上limit。

例如：

update order set status=0,edit_time=now(3) 
where id>=100 and id<200 limit 100;

这样即使误操作，比如把id搞错了，也不会对太多的数据造成影响。

6.in中值太多

对于批量查询接口，我们通常会使用in关键字过滤出数据。比如：想通过指定的一些id，批量查询出用户信息。

sql语句如下：

select id,name from category
where id in (1,2,3...100000000);

如果我们不做任何限制，该查询语句一次性可能会查询出非常多的数据，很容易导致接口超时。

这时该怎么办呢？

select id,name from category
where id in (1,2,3...100)
limit 500;

可以在sql中对数据用limit做限制。

不过我们更多的是要在业务代码中加限制，伪代码如下：

public List<Category> getCategory(List<Long> ids) {if(CollectionUtils.isEmpty(ids)) {return null;}if(ids.size() > 500) {throw new BusinessException("一次最多允许查询500条记录")}return mapper.getCategoryList(ids);
}

7.增量查询

有时候，我们需要通过远程接口查询数据，然后同步到另外一个数据库。

反例：

select * from user;

如果直接获取所有的数据，然后同步过去。这样虽说非常方便，但是带来了一个非常大的问题，就是如果数据很多的话，查询性能会非常差。

这时该怎么办呢？

正例：

select * 
from user 
where id>#{lastId} and create_time >= #{lastCreateTime} 
limit 100;

按id和时间升序，每次只同步一批数据，这一批数据只有100条记录。每次同步完成之后，保存这100条数据中最大的id和时间，给同步下一批数据的时候用。

通过这种增量查询的方式，能够提升单次查询的效率。

8.高效的分页

有时候，列表页在查询数据时，为了避免一次性返回过多的数据影响接口性能，我们一般会对查询接口做分页处理。

在mysql中分页一般用的limit关键字：

select id,name,age 
from user limit 10,20;

如果表中数据量少，用limit关键字做分页，没啥问题。但如果表中数据量很多，用它就会出现性能问题。

比如现在分页参数变成了：

select id,name,age 
from user limit 1000000,20;

mysql会查到1000020条数据，然后丢弃前面的1000000条，只查后面的20条数据，这个是非常浪费资源的。

那么，这种海量数据该怎么分页呢？

优化sql：

select id,name,age 
from user where id > 1000000 limit 20;

先找到上次分页最大的id，然后利用id上的索引查询。不过该方案，要求id是连续的，并且有序的。

还能使用between优化分页。

select id,name,age 
from user where id between 1000000 and 1000020;

需要注意的是between要在唯一索引上分页，不然会出现每页大小不一致的问题。

9.用连接查询代替子查询

mysql中如果需要从两张以上的表中查询出数据的话，一般有两种实现方式：子查询和连接查询。

子查询的例子如下：

select * from order
where user_id in (select id from user where status=1)

子查询语句可以通过in关键字实现，一个查询语句的条件落在另一个select语句的查询结果中。程序先运行在嵌套在最内层的语句，再运行外层的语句。

子查询语句的优点是简单，结构化，如果涉及的表数量不多的话。

但缺点是mysql执行子查询时，需要创建临时表，查询完毕后，需要再删除这些临时表，有一些额外的性能消耗。

这时可以改成连接查询。具体例子如下：

select o.* from order o
inner join user u on o.user_id = u.id
where u.status=1

10.join的表不宜过多

根据阿里巴巴开发者手册的规定，join表的数量不应该超过3个。

反例：

select a.name,b.name.c.name,d.name
from a 
inner join b on a.id = b.a_id
inner join c on c.b_id = b.id
inner join d on d.c_id = c.id
inner join e on e.d_id = d.id
inner join f on f.e_id = e.id
inner join g on g.f_id = f.id

如果join太多，mysql在选择索引的时候会非常复杂，很容易选错索引。

并且如果没有命中中，nested loop join 就是分别从两个表读一行数据进行两两对比，复杂度是 n^2。

所以我们应该尽量控制join表的数量。

正例：

select a.name,b.name.c.name,a.d_name 
from a 
inner join b on a.id = b.a_id
inner join c on c.b_id = b.id

如果实现业务场景中需要查询出另外几张表中的数据，可以在a、b、c表中冗余专门的字段，比如：在表a中冗余d_name字段，保存需要查询出的数据。

不过我之前也见过有些ERP系统，并发量不大，但业务比较复杂，需要join十几张表才能查询出数据。

所以join表的数量要根据系统的实际情况决定，不能一概而论，尽量越少越好。

11.join时要注意

我们在涉及到多张表联合查询的时候，一般会使用join关键字。

而join使用最多的是left join和inner join。

left join：求两个表的交集外加左表剩下的数据。
inner join：求两个表交集的数据。
使用inner join的示例如下：

select o.id,o.code,u.name 
from order o 
inner join user u on o.user_id = u.id
where u.status=1;

如果两张表使用inner join关联，mysql会自动选择两张表中的小表，去驱动大表，所以性能上不会有太大的问题。

使用left join的示例如下：

select o.id,o.code,u.name 
from order o 
left join user u on o.user_id = u.id
where u.status=1;

如果两张表使用left join关联，mysql会默认用left join关键字左边的表，去驱动它右边的表。如果左边的表数据很多时，就会出现性能问题。

要特别注意的是在用left join关联查询时，左边要用小表，右边可以用大表。如果能用inner join的地方，尽量少用left join。

12.控制索引的数量

众所周知，索引能够显著的提升查询sql的性能，但索引数量并非越多越好。

因为表中新增数据时，需要同时为它创建索引，而索引是需要额外的存储空间的，而且还会有一定的性能消耗。

阿里巴巴的开发者手册中规定，单表的索引数量应该尽量控制在5个以内，并且单个索引中的字段数不超过5个。

mysql使用的B+树的结构来保存索引的，在insert、update和delete操作时，需要更新B+树索引。如果索引过多，会消耗很多额外的性能。

那么，问题来了，如果表中的索引太多，超过了5个该怎么办？

这个问题要辩证的看，如果你的系统并发量不高，表中的数据量也不多，其实超过5个也可以，只要不要超过太多就行。

但对于一些高并发的系统，请务必遵守单表索引数量不要超过5的限制。

那么，高并发系统如何优化索引数量？

能够建联合索引，就别建单个索引，可以删除无用的单个索引。

将部分查询功能迁移到其他类型的数据库中，比如：Elastic Seach、HBase等，在业务表中只需要建几个关键索引即可。

13.选择合理的字段类型

char表示固定字符串类型，该类型的字段存储空间的固定的，会浪费存储空间。

alter table order 
add column code char(20) NOT NULL;

varchar表示变长字符串类型，该类型的字段存储空间会根据实际数据的长度调整，不会浪费存储空间。

alter table order 
add column code varchar(20) NOT NULL;

如果是长度固定的字段，比如用户手机号，一般都是11位的，可以定义成char类型，长度是11字节。

但如果是企业名称字段，假如定义成char类型，就有问题了。

如果长度定义得太长，比如定义成了200字节，而实际企业长度只有50字节，则会浪费150字节的存储空间。

如果长度定义得太短，比如定义成了50字节，但实际企业名称有100字节，就会存储不下，而抛出异常。

所以建议将企业名称改成varchar类型，变长字段存储空间小，可以节省存储空间，而且对于查询来说，在一个相对较小的字段内搜索效率显然要高些。

我们在选择字段类型时，应该遵循这样的原则：

能用数字类型，就不用字符串，因为字符的处理往往比数字要慢。
尽可能使用小的类型，比如：用bit存布尔值，用tinyint存枚举值等。
长度固定的字符串字段，用char类型。
长度可变的字符串字段，用varchar类型。
金额字段用decimal，避免精度丢失问题。

还有很多原则，这里就不一一列举了。

14.提升group by的效率

我们有很多业务场景需要使用group by关键字，它主要的功能是去重和分组。

通常它会跟having一起配合使用，表示分组后再根据一定的条件过滤数据。

反例：

select user_id,user_name from order
group by user_id
having user_id <= 200;

这种写法性能不好，它先把所有的订单根据用户id分组之后，再去过滤用户id大于等于200的用户。

分组是一个相对耗时的操作，为什么我们不先缩小数据的范围之后，再分组呢？

正例：

select user_id,user_name from order
where user_id <= 200
group by user_id

使用where条件在分组前，就把多余的数据过滤掉了，这样分组时效率就会更高一些。

其实这是一种思路，不仅限于group by的优化。我们的sql语句在做一些耗时的操作之前，应尽可能缩小数据范围，这样能提升sql整体的性能。

15.索引优化

sql优化当中，有一个非常重要的内容就是：索引优化。

很多时候sql语句，走了索引，和没有走索引，执行效率差别很大。所以索引优化被作为sql优化的首选。

索引优化的第一步是：检查sql语句有没有走索引。

那么，如何查看sql走了索引没？

可以使用explain命令，查看mysql的执行计划。

例如：

explain select * from `order` where code='002';

结果：

通过这几列可以判断索引使用情况，执行计划包含列的含义如下图所示：

说实话，sql语句没有走索引，排除没有建索引之外，最大的可能性是索引失效了。

下面说说索引失效的常见原因：

如果不是上面的这些原因，则需要再进一步排查一下其他原因。

此外，你有没有遇到过这样一种情况：明明是同一条sql，只有入参不同而已。有的时候走的索引a，有的时候却走的索引b？

没错，有时候mysql会选错索引。

必要时可以使用force index来强制查询sql走某个索引。

29.事务

事务是指一组要么全部执行，要么全部不执行的操作集合。一个事务通常包含多个数据库操作语句，这些操作需要作为一个整体进行提交或回滚。

在关系型数据库中，事务的ACID特性是非常重要的：

原子性（Atomicity）：事务中的所有操作要么全部完成，要么全部失败回滚。
一致性（Consistency）：事务执行前后，数据应该保持一致状态。如果有任何一个操作失败，则整个事务都应该回滚到之前的状态。
隔离性（Isolation）：并发访问时，每个事务应该与其他事务隔离开来，不会互相干扰。
持久性（Durability）：一旦事务提交成功，则所做的修改必须永久保存在数据库中。

常见的使用场景包括转账、订单处理、库存管理等业务流程。通过使用事务可以确保这些操作以原子方式执行，并且能够恢复到正确状态，避免了数据不一致和异常情况下数据丢失问题。

30.什么情况下使用读已提交

MySQL是一个可靠的开源数据库管理系统，广泛用于各种应用场景。其中，读已提交是一种常见的隔离级别，它可以提高并发性和数据准确性。下面我们介绍一些MySQL读已提交应用场景。

1. 订单系统
假设有一个在线商店，它需要处理大量订单。多个用户可能同时提交订单，如果不使用读已提交隔离级别，有可能会出现数据混乱或错误的情况。例如，一个用户提交订单时，第二个用户同时查看订单，但却看到了已提交但未完成的订单，导致订单重复或缺失。使用读已提交隔离级别可以避免这种情况，确保每个用户都只看到已经提交并生效的订单。
2. 金融交易系统
金融交易系统需要确保交易的准确性和可靠性。如果多个用户同时尝试交易，使用读已提交隔离级别可以保证交易数据的准确性和可靠性。例如，一个用户在查询账户余额时，另一个用户提交了交易请求。如果不使用读已提交隔离级别，第一个用户可能会看到错误的账户余额，导致交易失败或损失。
3. 社交网络
社交网络需要快速、准确地查询和更新用户信息。使用读已提交隔离级别可以提高并发性和数据准确性。例如，多个用户同时修改自己的个人资料或查询好友列表。如果不使用读已提交隔离级别，可能会导致数据冲突或遗漏，加重系统负担。

综上所述，MySQL读已提交隔离级别适用于多种应用场景，可以提高并发性和数据准确性，确保系统稳定可靠。

31.对于脏读的理解

脏读：脏读就是指当一个事务正在访问数据，并且对数据进行了修改，而这种修改还没有提交到数据库中，这时，另外一个事务也访问这个数据，然后使用了这个数据。它强调的是第二个事务读到的数据不够新。

32.慢查询怎么看，怎么优化

引言：为什么查询速度会慢？

通常来说，查询的生命周期大致可以按照顺序来看：从客户端>>服务器>>在服务器上进行解析>>生成执行计划>>执行>>返回结果给客户端。其中执行可以认为是整个生命周期中最重要的阶段，这其中包括了大量为了检索数据到存储引擎的调用以及调用后的数据处理，包括排序、分组等。了解查询的生命周期、清楚查询的时间消耗情况对于优化查询有很大的意义。

1、优化数据访问

原因：查询性能低下的最基本的原因是访问的数据太多，大部分性能低下的查询都可以通过减少访问的数据量的方式进行优化。

具体情况：检索大量超过需要的数据，访问了太多行或列。有如下一些情况：

（1）查询不需要的记录

（2）多表关联时返回全部列

（3）总是取出全部列

（4）重复查询相同的数据

2、MYSQL是否在扫描额外的记录

对于MYSQL，最简单的衡量查询开销的三个指标包含：响应时间，扫描的行数，返回的行数。

（1）响应时间：响应时间分为服务时间和排队时间，其中服务时间是指数据库处理查询真正花费的时间，排队时间指等待资源的时间。

（2）扫描的行数与返回的行数：扫描的行数与返回的行数应尽量接近1:1，一般在1:1到1:10。

（3）扫描的行数与访问类型：mysql有好几种访问方式可以查询并返回一行结果，有些访问方式可能需要扫描很多行数据才能返回一行数据，而有些方式则无需扫描就能返回结果。在explain查询语句的type反应了访问类型，从全表扫描到索引扫描、范围扫描、唯一索引查询，常数引用等，这里列的这些，速度从慢到快，扫描的行数从小到大。如果查询没有办法找到合适的访问类型，那么最好的方法通常就是增加一个合适的索引，让mysql以最高效、扫描行数最少的方式找到需要的记录。

3、重构查询方式

将一个复杂查询划分为多个简单查询，mysql从设计上让连接和断开连接都很轻量化，在返回一个小的查询结果方面很高效。

（1）切分查询：对于一个大查询需要分而治之，将大查询切分为小查询，每个小查询只完成一小部分，每次返回一小部分查询结果。例如以下删除10000行数据：

方式1：delete from tablename where create<date_sub(non(),interval 3 month);

方式2：rows_affected=0

Do{

Rows_affected=do_query(

“delete from tablename where created<date_sub(now(),interval 3 month)limit10000”

)

}while rows_affected>0

方式2中一次删除一行数据一般是一个比较高效而且对服务器影响最小的做法。同时需要注意的是，如果每次删除数据后，都暂停一会儿进行下一次删除，这样可以将服务器上原本一次性的压力分散到一个很长的时间段，可以大大减少删除时锁的持有时间。

（2）分解关联查询：很多高性能应用都会对关联查询进行分解。简单的，可以对每一个表进行一次单表查询，然后将结果在应用程序中关联。

4、优化特定类型的查询

（1）对COUNT()的优化

COUNT()的作用：

COUNT()是一个特殊的函数，它可以统计某个列值的数量，也可以统计行数。

在统计列值时要求列值是非空的(不统计NULL)。如果在COUNT()的括号中指定了列或者列的表达式，则统计的就是这个表达式有值的结果数。

COUNT()的另一个作用是统计结果集的行数。当MySQL确认括号内的表达式值不可能为空时，实际上就是在统计行数。最简单的就是当我们使用COUNT()的时候，这种情况下通配符并不会像我们猜想的那样扩展成所有的列，实际上，它会忽略所有的列而直接统计所有的行数。

优化案例如下：

（2）对关联查询的优化

关联查询的优化需要特别注意的是:

①确保ON或者USING子句中的列上有索引。在创建索引的时候就要考虑到关联的顺序。当表A和表B用列c关联的时候，如果优化器的关联顺序是B、A，那么就不需要在B表(驱动表)的对应列上建上索引，没有用到的索引只会带来额外的负担。一般来说，只需要在关联顺序中的第二个表(被驱动表)的相应列上创建索引。

②确保任何的GROUPBY和ORDERBY中的表达式只涉及到一个表中的列，这样MySQL

才有可能使用索引来优化这个过程。

③当升级MySQL的时候需要注意关联语法、运算符优先级等可能会变化的地方。因为以前是普通关联的地方可能会变成笛卡儿积，不同类型的关联可能会生成不同的结果等。

（3）对UNION查询的优化

MySQL总是通过创建并填充临时表的方式来执行UNION查询。因此很多优化策略在 UNION查询中都没法很好地使用。经常需要手工地将WHERE、LIMIT、ORDERBY等子句“下推”到UNION的各个子查询中，以便优化器可以充分利用这些条件进行优化。除非确实需要服务器消除重复的行，否则就一定要使用UNIONALL。如果没有ALL关键字，MySQL会给临时表加上DISTINCT选项，这会导致对整个临时表的数据做唯一性检查。这样做的代价非常高。

5、总结

1.对查询进行优化，应尽量避免全表扫描，首先应考虑在 where 及 order by 涉及的列上建立索引。

2.应尽量避免在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进行全表扫描，如：select id from t where num is null可以在num上设置默认值0，确保表中num列没有null值，然后这样查询：select id from t where num=0

3.应尽量避免在 where 子句中使用!=或<>操作符，否则引擎将放弃使用索引而进行全表扫描。

4.应尽量避免在 where 子句中使用or 来连接条件，否则将导致引擎放弃使用索引而进行全表扫描，如：select id from t where num=10 or num=20可以这样查询：select id from t where num=10 union all select id from t where num=20

5.in 和 not in 也要慎用，否则会导致全表扫描，如：select id from t where num in(1,2,3) 对于连续的数值，能用 between 就不要用 in 了：select id from t where num between 1 and 3

6.下面的查询也将导致全表扫描：select id from t where name like '李%'若要提高效率，可以考虑全文检索。

7.如果在 where 子句中使用参数，也会导致全表扫描。因为SQL只有在运行时才会解析局部变量，但优化程序不能将访问计划的选择推迟到运行时；它必须在编译时进行选择。然而，如果在编译时建立访问计划，变量的值还是未知的，因而无法作为索引选择的输入项。如下面语句将进行全表扫描：select id from t where num=@num可以改为强制查询使用索引：select id from t with(index(索引名)) where num=@num

8.应尽量避免在 where 子句中对字段进行表达式操作，这将导致引擎放弃使用索引而进行全表扫描。如：select id from t where num/2=100应改为:select id from t where num=100*2

9.应尽量避免在where子句中对字段进行函数操作，这将导致引擎放弃使用索引而进行全表扫描。如：select id from t where substring(name,1,3)=‘abc’ ，name以abc开头的id应改为:select id from t where name like ‘abc%’

10.不要在 where 子句中的“=”左边进行函数、算术运算或其他表达式运算，否则系统将可能无法正确使用索引。

11.在使用索引字段作为条件时，如果该索引是复合索引，那么必须使用到该索引中的第一个字段作为条件时才能保证系统使用该索引，否则该索引将不会被使用，并且应尽可能的让字段顺序与索引顺序相一致。

12.不要写一些没有意义的查询，如需要生成一个空表结构：select col1,col2 into #t from t where 1=0这类代码不会返回任何结果集，但是会消耗系统资源的，应改成这样：create table #t(…)

13.很多时候用 exists 代替 in 是一个好的选择：select num from a where num in(select num from b)用下面的语句替换：select num from a where exists(select 1 from b where num=a.num)

14.并不是所有索引对查询都有效，SQL是根据表中数据来进行查询优化的，当索引列有大量数据重复时，SQL查询可能不会去利用索引，如一表中有字段sex，male、female几乎各一半，那么即使在sex上建了索引也对查询效率起不了作用。

15.索引并不是越多越好，索引固然可以提高相应的 select 的效率，但同时也降低了 insert 及 update 的效率，因为 insert 或 update 时有可能会重建索引，所以怎样建索引需要慎重考虑，视具体情况而定。一个表的索引数最好不要超过6个，若太多则应考虑一些不常使用到的列上建的索引是否有必要。

16.应尽可能的避免更新 clustered 索引数据列，因为 clustered 索引数据列的顺序就是表记录的物理存储顺序，一旦该列值改变将导致整个表记录的顺序的调整，会耗费相当大的资源。若应用系统需要频繁更新 clustered 索引数据列，那么需要考虑是否应将该索引建为 clustered 索引。

17.尽量使用数字型字段，若只含数值信息的字段尽量不要设计为字符型，这会降低查询和连接的性能，并会增加存储开销。这是因为引擎在处理查询和连接时会逐个比较字符串中每一个字符，而对于数字型而言只需要比较一次就够了。

18.尽可能的使用 varchar/nvarchar 代替 char/nchar ，因为首先变长字段存储空间小，可以节省存储空间，其次对于查询来说，在一个相对较小的字段内搜索效率显然要高些。

19.任何地方都不要使用 select * from t ，用具体的字段列表代替“*”，不要返回用不到的任何字段。

20.尽量使用表变量来代替临时表。如果表变量包含大量数据，请注意索引非常有限（只有主键索引）。

21.避免频繁创建和删除临时表，以减少系统表资源的消耗。

22.临时表并不是不可使用，适当地使用它们可以使某些例程更有效，例如，当需要重复引用大型表或常用表中的某个数据集时。但是，对于一次性事件，最好使用导出表。

23.在新建临时表时，如果一次性插入数据量很大，那么可以使用 select into 代替 create table，避免造成大量 log ，以提高速度；如果数据量不大，为了缓和系统表的资源，应先create table，然后insert。

24.如果使用到了临时表，在存储过程的最后务必将所有的临时表显式删除，先 truncate table ，然后 drop table ，这样可以避免系统表的较长时间锁定。

25.尽量避免使用游标，因为游标的效率较差，如果游标操作的数据超过1万行，那么就应该考虑改写。

26.使用基于游标的方法或临时表方法之前，应先寻找基于集的解决方案来解决问题，基于集的方法通常更有效。

27.与临时表一样，游标并不是不可使用。对小型数据集使用 FAST_FORWARD 游标通常要优于其他逐行处理方法，尤其是在必须引用几个表才能获得所需的数据时。在结果集中包括“合计”的例程通常要比使用游标执行的速度快。如果开发时间允许，基于游标的方法和基于集的方法都可以尝试一下，看哪一种方法的效果更好。

28.在所有的存储过程和触发器的开始处设置 SET NOCOUNT ON ，在结束时设置 SET NOCOUNT OFF 。无需在执行存储过程和触发器的每个语句后向客户端发送DONE_IN_PROC 消息。

29.尽量避免大事务操作，提高系统并发能力。

30.尽量避免向客户端返回大数据量，若数据量过大，应该考虑相应需求是否合理。

33.联合索引(a,b,c)，where a, b, c和where b, a, c区别

今日面试官问我，假设存在(a,b,c)三个字段的联合索引，问我
　1、如果where a=x and b=x，是否会用到索引？
　2、如果 where a=x and b=x order by c desc 会不会用到索引？
　3、如果 where a=x and b=x group by c 会不会用到索引？

我当时的回答是

1 否，因为没有用到c
2是，因为order by会用到索引

3 否，因为group by是在where之后，不走索引

那么我现在来检查一下我的答案。查阅了一下资料，其实建立(a,b,c)三个字段的联合索引，就是相当于建立了(a,b,c)、(a,b)，a的三个索引（最佳左前缀特性），而order by是能使用最左前缀索引的，所以其实