Mysql从入门到精通的实战指南

MySQL是一个开放源代码的数据库管理系统（DBMS），是由MySQL AB公司开发、发布并支持的。MySQL是一个跨平台的开源关系型数据库管理系统，广泛地应用在Internet上的中小型网站开发中。

概念

SQL语句分类

数据定义语言DDL（Data Ddefinition Language）：CREATE，DROP，ALTER

主要为以上操作即对逻辑结构等有操作的，其中包括表结构，视图和索引。

数据查询语言DQL（Data Query Language）：SELECT

这个较为好理解即查询操作，以select关键字。各种简单查询，连接查询等都属于DQL。

数据操纵语言DML（Data Manipulation Language）：INSERT，UPDATE，DELETE

主要为以上操作即对数据进行操作的，对应上面所说的查询操作 DQL与DML共同构建了多数初级程序员常用的增删改查操作。而查询是较为特殊的一种被划分到DQL中。

数据控制功能DCL（Data Control Language）：GRANT，REVOKE，COMMIT，ROLLBACK

主要为以上操作即对数据库安全性完整性等有操作的，可以简单的理解为权限控制等。

数据库三大范式是什么

第一范式：每个列都不可以再拆分。

第二范式：在第一范式的基础上，非主键列完全依赖于主键，而不能是依赖于主键的一部分。

第三范式：在第二范式的基础上，非主键列只依赖于主键，不依赖于其他非主键。

在设计数据库结构的时候，要尽量遵守三范式，如果不遵守，必须有足够的理由。比如性能。事实上我们经常会为了性能而妥协数据库的设计。

一存储引擎

1.1 存储引擎

MySQL 8.0支持的存储引擎有InnoDB、MyISAM、Memory、Merge、Archive、Federated、CSV、BLACKHOLE等。

MySQL常用存储引擎包括：

InnoDB（默认）：支持事务、外键、行级锁，适合高并发场景。
MyISAM：不支持事务和外键，表级锁，读写性能较高（适合读多写少）。
Memory：数据存储在内存中，速度极快，适合临时表。
Archive：只支持INSERT和SELECT，压缩存储，适合历史数据归档。

核心区别：

特性	InnoDB	MyISAM
事务支持	✅	❌
外键	✅	❌
锁机制	行级锁	表级锁
索引与数据	聚簇索引（索引和数据存储在一起）	非聚簇索引
崩溃恢复	支持	不支持

InnoDB与MyISAM对比：

InnoDB主键索引直接存储数据，MyISAM索引只存储数据地址。
InnoDB的二级索引叶子节点存储主键值，MyISAM存储数据地址。
MyISAM：不支持事务和外键，支持表级锁，查询速度快，适合读多写少场景（如日志表），崩溃后恢复困难。
InnoDB：支持事务（ACID）、外键和行级锁，有崩溃恢复能力（依赖redo日志），适合写操作频繁的场景（如订单表），性能略低于MyISAM但安全性更高。

1.2 日志文件

常用的日志文件包括错误日志、二进制日志、查询日志、慢查询日志和InnoDB引擎在线Redo日志等。

错误日志

Error Log错误日志文件记录了MySQL Server运行过程遇到的所有严重的错误信息，以及MySQL每次启动和关闭的详细信息。默认情况下，错误日志功能是关闭的，启动时要重新配置–log-error[=file_name]选项，修改错误日志存放的目录和文件名称。

（2）二进制日志

Binary Log二进制日志文件就是常说的binlog。二进制日志记录了MySQL所有修改数据库的操作，然后以二进制的形式记录在日志文件中，其中还包括每条语句所执行的时间和所消耗的资源，以及相关的事务信息。默认情况下，二进制日志功能是开启的，启动时可以重新配置–log-bin[=file_name]选项，修改二进制日志存放的目录和文件名称。

（3）查询日志

Query Log默认的查询日志文件是hostname.log。查询日志记录所有的查询操作，包括所有的select操作信息，体积比较大，开启后对性能有较大的影响，可以通过“–log[=file_name]”选项开启。如果需要跟踪某些特殊的SQL性能问题，可以短暂地打开该功能。

（4）慢查询日志

Slow Query Log慢查询日志是指所有SQL执行的时间超过long_query_time变量的语句和达到min_examined_row_limit条件的语句。用户可以针对这部分语句进行性能调优。慢查询日志设置–log-slow_queries[=file_name]选项开启后，将记录日志所在的路径和名称。MySQL系统默认的慢查询日志的文件名是hostname-slow.log，默认目录也是data目录。查看慢查询日志可以采用mysqldumpslow命令对慢查询日志进行分析。

（5）InnoDB引擎在线Redo日志：InnoDB redo Log

InnoDB引擎在线Redo日志记录了InnoDB所做的所有物理变更和事务信息。通过Redo日志和Undo信息，InnoDB大大加强了事务的安全性。InnoDB在线Redo日志默认存放在data目录下面，可以通过设置innodb_log_group_home_dir选项来更改日志的存放位置、通过innodb_log_files_in_group选项来设置日志的数量。

undo log日志用于存放数据修改被修改前的值，来实现回滚操作，保证事务的一致性

redo log ，用于记录数据修改后的记录，顺序记录。

binlog日志作用

binlog（二进制日志）记录所有数据变更操作（增删改、建表等），用于主从复制和数据恢复。

格式：

①STATEMENT：记录 SQL 语句（如update table set a=1 where id=1），体积小，但某些函数（如now()）可能导致主从数据不一致；
②ROW：记录行的变更（如 “id=1 的行 a 从 0 变为 1”），主从一致性高，体积大；
③MIXED：默认用 STATEMENT，特殊情况自动切换为 ROW，平衡体积和一致性。

binlog三种格式

有三种格式，statement，row和mixed。

statement模式下，每一条会修改数据的sql都会记录在binlog中。不需要记录每一行的变化，减少了binlog日志量，节约了IO，提高性能。由于sql的执行是有上下文的，因此在保存的时候需要保存相关的信息，同时还有一些使用了函数之类的语句无法被记录复制。

row级别下，不记录sql语句上下文相关信息，仅保存哪条记录被修改。记录单元为每一行的改动，基本是可以全部记下来但是由于很多操作，会导致大量行的改动(比如alter table)，因此这种模式的文件保存的信息太多，日志量太大。

mixed，一种折中的方案，普通操作使用statement记录，当无法使用statement的时候使用row。

此外，新版的MySQL中对row级别也做了一些优化，当表结构发生变化的时候，会记录语句而不是逐行记录。

主从复制的原理

主从复制：将主数据库中的DDL和DML操作通过二进制日志（BINLOG）传输到从数据库上，然后将这些日志重新执行（重做）；从而使得从数据库的数据与主数据库保持一致。

解决的问题

数据分布：随意开始或停止复制，并在不同地理位置分布数据备份

负载均衡：降低单个服务器的压力

高可用和故障切换：帮助应用程序避免单点失败

升级测试：可以用更高版本的MySQL作为从库

基本原理流程

主库将变更写入binlog（二进制日志）。
从库通过IO线程读取主库的binlog，并写入relay log（中继日志）。
从库的SQL线程执行relay log中的SQL语句，实现数据同步。

复制过程

Binary log：主数据库的二进制日志

Relay log：从服务器的中继日志

第一步：master在每个事务更新数据完成之前，将该操作记录串行地写入到binlog文件中。

第二步：salve开启一个I/O Thread，该线程在master打开一个普通连接，主要工作是binlog dump process。如果读取的进度已经跟上了master，就进入睡眠状态并等待master产生新的事件。I/O线程最终的目的是将这些事件写入到中继日志中。

第三步：SQL Thread会读取中继日志，并顺序执行该日志中的SQL事件，从而与主数据库中的数据保持一致。

1.3 锁定机制

MySQL各存储引擎使用了3种级别的锁定机制：行级锁定，页级锁定和表级锁定

表级锁：锁定整张表，开销小、速度快，冲突概率高（如MyISAM的读锁/写锁）。
行级锁：锁定单行数据，开销大、速度慢，冲突概率低（InnoDB特有，依赖索引，无索引时会退化为表锁）。

行级锁的含义

共享锁（S，读锁，多个事务可同时持有）：允许一个事务读一行数据时阻止其他的事务读取相同数据的排他锁。
排他锁（X，写锁，仅一个事务持有）：允许获得排他锁的事务更新数据，阻止其他事务取得相同数据的共享锁和排他锁。
意向共享锁（IS）：事务打算给数据行加行共享锁。事务在给一个数据行加共享锁前必须先取得该表的IS锁。
意向排他锁（IX）：事务打算给数据行加行排他锁。事务在给一个数据行加排他锁前必须先取得该表的IX锁。

共享锁和排他锁必须要满足以下几个条件：

设置autocommit的值是OFF或者0。
表的数据引擎是支持事务的，比如InnoDB数据引擎。
如果不管autocommit，手动在事务里执行操作，这时要使用begin或者start transaction开始事务。
不要在锁定事务规定的时间外使用共享锁和排他锁。

意向锁是InnoDB存储引擎自动加的。对于普通SELECT语句，InnoDB不会加任何锁，对于INSERT、UPDATE、DELETE语句，InnoDB会自动给涉及的数据加排他锁，InnoDB可以通过以下语句添加共享锁和排他锁。

（1）添加共享锁（S）：

　　SELECT * FROM table_name WHERE … LOCK IN SHARE MODE。

（2）添加排他锁（X）：

　　SELECT * FROM table_name WHERE … FOR UPDATE。

InnoDB 的行级锁基于索引实现，通过锁定索引记录来锁定对应行数据。

原理：当执行update table set name='a' where id=10时，若id是索引列，InnoDB 会锁定id=10的索引记录，仅影响该行；
锁升级：若查询条件中的列无索引（如where name='a'且name无索引），InnoDB 无法定位具体行，会锁定整张表的所有索引记录，即升级为表锁。

Row Lock与Table Lock

使用select…for update会把数据给锁住，不过我们需要注意一些锁的级别，MySQL InnoDB默认Row-Level Lock，所以只有「明确」地指定主键或者索引，MySQL 才会执行Row lock (只锁住被选取的数据) ，否则MySQL 将会执行Table Lock (将整个数据表单给锁住。举例如下：

1、select * from t_items where id=1 for update;

这条语句明确指定主键（id=1），并且有此数据（id=1的数据存在），则采用row lock。只锁定当前这条数据。

2、select * from t_items where id=3 for update;

这条语句明确指定主键，但是却查无此数据，此时不会产生lock（没有元数据，又去lock谁呢？）。

3、select * from t_items where name=‘手机’ for update;

这条语句没有指定数据的主键，那么此时产生table lock，即在当前事务提交前整张数据表的所有字段将无法被查询。

4、select * from t_items where id>0 for update; 或者select * from t_items where id<>1 for update;（注：<>在SQL中表示不等于）

上述两条语句的主键都不明确，也会产生table lock。

5、select * from t_items where status=1 for update;（假设为status字段添加了索引）

这条语句明确指定了索引，并且有此数据，则产生row lock。

6、select * from t_items where status=3 for update;（假设为status字段添加了索引）

这条语句明确指定索引，但是根据索引查无此数据，也就不会产生lock。

间隙锁(Net-Key锁)

在更新InnoDB存储引擎表中的某个区间数据时，将会锁定这个区间的所有记录。例如，update xxx where id between 1 and 100，就会锁住id从1到100之间所有的记录。值得注意的是，在这个区间中假设某条记录并不存在，该条记录也会被锁住，这时，如果另外一个Session往这个表中添加一条记录，就必须要等到上一个事务释放锁资源。

InnoDB使用间隙锁的目的有两方面：一方面是为了防止幻读，如果没有添加间隙锁，其他事务要添加id在1到100之间的某条记录就会发生幻读；另一方面是为了满足其恢复和赋值的需求。

死锁

死锁：两个或多个事务互相等待对方释放锁资源，导致永久阻塞。

处理机制：InnoDB 会检测死锁，通过 “等待图” 发现循环等待后，选择 “事务权重小”（如修改行数少）的事务回滚，释放锁，让其他事务继续执行。

避免方法：

事务按相同顺序访问资源。（如都先锁行 1 再锁行 2）
降低事务隔离级别（如从SERIALIZABLE改为REPEATABLE READ）。
使用索引减少锁范围，避免全表扫描。
设置锁超时参数（innodb_lock_wait_timeout）。
通过SHOW ENGINE INNODB STATUS分析死锁日志，优化查询。

1.4 事务四大特性（ACID）

事务是一组不可分割的操作，四大特性为：

原子性（Atomicity）：事务要么全执行，要么全不执行（如转账时一方扣款失败则另一方不收款）。
一致性（Consistency）：事务执行前后数据状态一致（如转账总金额不变）。
隔离性（Isolation）：多个事务并发执行时，彼此不干扰（通过隔离级别控制）。
持久性（Durability）：事务提交后，数据修改永久保存（即使断电也不丢失）。

MySQL实现：

使用BEGIN/START TRANSACTION开启事务，COMMIT提交，ROLLBACK回滚。
InnoDB通过undo日志实现原子性，redo日志实现持久性，锁机制和MVCC（多版本并发控制）实现隔离性。

1.5 事务隔离级别

InnoDB存储引擎并发事务处理能力大大增加了数据库资源的利用率，提高了数据库系统的事务吞吐量，但并发事务同时也存在一些问题，主要包括更新丢失（LostUpdate）、脏读（Dirty Reads）、不可重复读（Non- Repeatable Reads）、幻读（Phantom Reads）。

**更新丢失(Update lose)：两个事务同时操作相同数据，后提交的事务会覆盖先提交的事务处理结果。**两个事务更新同一行数据，但是第二个事务却中途失败退出了，导致对两个修改都失效了，这时系统没有执行任何锁操作，因此并发事务并没有被隔离。通过乐观锁就可以解决。
**脏读(Dirty read)：在一个事务中读取到另一个事务已经修改但没有提交的数据。**一个事务读了某行数据，但是另一个事务已经更新了这行数据，这是非常危险的，很可能所有的操作被回滚。
**不可重复读(NonRepeatable Read)：在一个事务的两次查询之中数据不一致，这可能是两次查询过程中间插入了一个事务更新的原有的数据。**一个事务对一行数据重复读取两次，可是得到了不同的结果。在两次读取数据的中途，有可能存在另外一个事务对数据进行了修改。
幻读(Phantom Read)：事务A读取到了事务B已经提交的新增数据。在一个事务中，两次查询的结果不一致(针对的insert操作) 。事务在操作过程中进行两次查询，第二次查询结果包含了第一次没有出现的数据。出现幻读的主要原因是，两次查询过程中另一个事务插入新的数据。

共4种隔离级别，从低到高依次为：

读未提交（Read Uncommitted）：可能读取到其他事务未提交的数据（脏读）。
读已提交（Read Committed）：只能读取其他事务已提交的数据，解决脏读，但可能出现不可重复读（同一事务内两次查询结果不同）。
可重复读（Repeatable Read）：同一事务内多次查询结果一致，解决不可重复读，可能出现幻读（新增数据被读取），是MySQL默认级别。
串行化（Serializable）：事务串行执行，避免所有并发问题，但性能极差。事务被处理为顺序执行。除了防止脏读，不可重复读外，还避免了幻像读。

Mysql 默认采用的 REPEATABLE_READ隔离级别 Oracle 默认采用的 READ_COMMITTED隔离级别

幻读：在一个事务中，多次查询同一范围的数据时，由于其他事务插入或删除记录，导致结果集不一致。

MVCC（多版本并发控制）：InnoDB通过版本号和undo日志实现，在REPEATABLE READ级别下避免幻读。

数据库隔离级别：https://zhuanlan.zhihu.com/p/117476959

1.6 Java @Transactional传播行为

PROPAGATION_REQUIRED：如果当前没有事务，就创建一个新事务，如果当前存在事务，就加入该事务，该设置是最常用的设置。
PROPAGATION_SUPPORTS：支持当前事务，如果当前存在事务，就加入该事务，如果当前不存在事务，就以非事务执行。
PROPAGATION_MANDATORY：支持当前事务，如果当前存在事务，就加入该事务，如果当前不存在事务，就抛出异常。
PROPAGATION_REQUIRES_NEW：创建新事务，无论当前存不存在事务，都创建新事务。
PROPAGATION_NOT_SUPPORTED：以非事务方式执行操作，如果当前存在事务，就把当前事务挂起。
PROPAGATION_NEVER：以非事务方式执行，如果当前存在事务，则抛出异常。
PROPAGATION_NESTED：如果当前存在事务，则在嵌套事务内执行。如果当前没有事务，则执行与PROPAGATION_REQUIRED类似的操作。

1.7 MVCC（多版本并发控制）

MySQL 的 MVCC（多版本并发控制）是 InnoDB 存储引擎实现高并发读写的核心机制，通过保存数据的多个版本，实现读写不冲突，提升数据库并发性能。

MVCC 通过 版本链（undo log + 隐藏列） 和 Read View 实现了“读写不阻塞”，核心是让读操作访问历史版本数据，而非当前正在修改的数据。其优势在于：

并发性能高：避免了读锁和写锁的冲突。
支持事务隔离：通过 Read View 控制不同事务对版本数据的可见性。

缺点是会占用额外存储空间（undo log），且版本链过长可能影响查询性能（需定期清理 undo log）。

实现方式：通过每行数据的隐藏列（DB_TRX_ID事务ID、DB_ROLL_PTR回滚指针）和undo日志（保存历史版本）。查询时，InnoDB会根据事务可见性规则，读取符合条件的历史版本，避免脏读和不可重复读。

1. MVCC 的核心目标

解决读写冲突：让读操作（SELECT）不阻塞写操作（INSERT/UPDATE/DELETE），写操作也不阻塞读操作。
实现事务隔离：基于多版本数据，支持 READ COMMITTED（读已提交） 和 REPEATABLE READ（可重复读） 隔离级别（InnoDB 默认）。

2. 关键技术组件

MVCC 的实现依赖以下核心结构：

（1）隐藏列

InnoDB 为每个表的每行数据添加了 3 个隐藏列：

DB_TRX_ID：记录最后一次修改该行数据的事务 ID（64 位，自增）。
DB_ROLL_PTR：回滚指针，指向该行数据的历史版本（存储在 undo log 中）。
DB_ROW_ID：若表无主键，InnoDB 会自动生成该列作为行唯一标识。

（2）Undo Log（回滚日志）

用于保存数据的历史版本：当数据被修改时，旧版本数据会被写入 undo log，通过 DB_ROLL_PTR 串联成一个版本链。
作用：支持事务回滚（ROLLBACK），同时为 MVCC 提供历史数据版本。

（3）Read View（读视图）

事务在读取数据时生成的“快照”，用于判断哪些版本的数据对当前事务可见。
包含 4 个核心参数：
- m_ids：当前活跃（未提交）的事务 ID 列表。
- min_trx_id：m_ids 中最小的事务 ID。
- max_trx_id：下一个将要分配的事务 ID（大于当前所有活跃事务 ID）。
- creator_trx_id：当前事务自身的 ID。

3. 工作原理：版本可见性判断

当事务读取数据时，InnoDB 会通过 Read View 检查数据版本链中的 DB_TRX_ID，判断该版本是否可见，规则如下：

若数据版本的 DB_TRX_ID = 当前事务 ID（creator_trx_id）：可见（自己修改的数据）。
若 DB_TRX_ID < min_trx_id：可见（该版本由已提交事务生成）。
若 DB_TRX_ID > max_trx_id：不可见（该版本由读取后才开启的事务生成）。
若 min_trx_id ≤ DB_TRX_ID ≤ max_trx_id：
- 若 DB_TRX_ID 在 m_ids 中（事务未提交）：不可见。
- 若不在 m_ids 中（事务已提交）：可见。

若当前版本不可见，则通过 DB_ROLL_PTR 找到上一个历史版本，重复上述判断，直到找到可见版本或版本链结束（返回空）。

4. 不同隔离级别的 MVCC 差异

REPEATABLE READ（可重复读，InnoDB 默认）：
- 事务开始时生成一次 Read View，后续所有读取都使用该快照，因此能保证“可重复读”（多次读取数据一致）。
READ COMMITTED（读已提交）：
- 每次执行 SELECT 时都会重新生成 Read View，因此只能看到“已提交”的最新数据（可能出现不可重复读）。

1.8 事务隔离级别示例

我们使用 test 数据库，新建 tx 表：－－－ＭｙＳＱＬ数据库

第1级别：Read Uncommitted(读取未提交内容) (1)所有事务都可以看到其他未提交事务的执行结果 (2)本隔离级别很少用于实际应用，因为它的性能也不比其他级别好多少 (3)该级别引发的问题是——脏读(Dirty Read)：读取到了未提交的数据

#首先，修改隔离级别
set tx_isolation='READ-UNCOMMITTED';
select @@tx_isolation;
+------------------+
| @@tx_isolation   |
+------------------+
| READ-UNCOMMITTED |
+------------------+

#事务A：启动一个事务
start transaction;
select * from tx;
+------+------+
| id   | num  |
+------+------+
|    1 |    1 |
|    2 |    2 |
|    3 |    3 |
+------+------+

#事务B：也启动一个事务(那么两个事务交叉了)
       在事务B中执行更新语句，且不提交
start transaction;
update tx set num=10 where id=1;
select * from tx;
+------+------+
| id   | num  |
+------+------+
|    1 |   10 |
|    2 |    2 |
|    3 |    3 |
+------+------+

#事务A：那么这时候事务A能看到这个更新了的数据吗?
select * from tx;
+------+------+
| id   | num  |
+------+------+
|    1 |   10 |   --->可以看到！说明我们读到了事务B还没有提交的数据
|    2 |    2 |
|    3 |    3 |
+------+------+

#事务B：事务B回滚,仍然未提交
rollback;
select * from tx;
+------+------+
| id   | num  |
+------+------+
|    1 |    1 |
|    2 |    2 |
|    3 |    3 |
+------+------+

#事务A：在事务A里面看到的也是B没有提交的数据
select * from tx;
+------+------+
| id   | num  |
+------+------+
|    1 |    1 |      --->脏读意味着我在这个事务中(A中)，事务B虽然没有提交，但它任何一条数据变化，我都可以看到！
|    2 |    2 |
|    3 |    3 |
+------+------+

第2级别：Read Committed(读取提交内容)

(1)这是大多数数据库系统的默认隔离级别（但不是MySQL默认的） (2)它满足了隔离的简单定义：一个事务只能看见已经提交事务所做的改变 (3)这种隔离级别出现的问题是——不可重复读(Nonrepeatable Read)：不可重复读意味着我们在同一个事务中执行完全相同的select语句时可能看到不一样的结果。 |——>导致这种情况的原因可能有：(1)有一个交叉的事务有新的commit，导致了数据的改变;(2)一个数据库被多个实例操作时,同一事务的其他实例在该实例处理其间可能会有新的commit

#首先修改隔离级别
set tx_isolation='read-committed';
select @@tx_isolation;
+----------------+
| @@tx_isolation |
+----------------+
| READ-COMMITTED |
+----------------+

#事务A：启动一个事务
start transaction;
select * from tx;
+------+------+
| id   | num  |
+------+------+
|    1 |    1 |
|    2 |    2 |
|    3 |    3 |
+------+------+

#事务B：也启动一个事务(那么两个事务交叉了)
       在这事务中更新数据，且未提交
start transaction;
update tx set num=10 where id=1;
select * from tx;
+------+------+
| id   | num  |
+------+------+
|    1 |   10 |
|    2 |    2 |
|    3 |    3 |
+------+------+

#事务A：这个时候我们在事务A中能看到数据的变化吗?
select * from tx; --------------->
+------+------+                |
| id   | num  |                |
+------+------+                |
|    1 |    1 |--->并不能看到！  |
|    2 |    2 |                |
|    3 |    3 |                |
+------+------+                |——>相同的select语句，结果却不一样
                                  |
#事务B：如果提交了事务B呢?            |
commit;                           |
                                  |
#事务A:                            |
select * from tx; --------------->
+------+------+
| id   | num  |
+------+------+
|    1 |   10 |--->因为事务B已经提交了，所以在A中我们看到了数据变化
|    2 |    2 |
|    3 |    3 |
+------+------+

第3级别：Repeatable Read(可重读) (1)这是MySQL的默认事务隔离级别 (2)它确保同一事务的多个实例在并发读取数据时，会看到同样的数据行 (3)此级别可能出现的问题——幻读(Phantom Read)：当用户读取某一范围的数据行时，另一个事务又在该范围内插入了新行，当用户再读取该范围的数据行时，会发现有新的“幻影” 行 (4)InnoDB和Falcon存储引擎通过多版本并发控制(MVCC，Multiversion Concurrency Control)机制解决了该问题

#首先，更改隔离级别
set tx_isolation='repeatable-read';
select @@tx_isolation;
+-----------------+
| @@tx_isolation  |
+-----------------+
| REPEATABLE-READ |
+-----------------+

#事务A：启动一个事务
start transaction;
select * from tx;
+------+------+
| id   | num  |
+------+------+
|    1 |    1 |
|    2 |    2 |
|    3 |    3 |
+------+------+

#事务B：开启一个新事务(那么这两个事务交叉了)
       在事务B中更新数据，并提交
start transaction;
update tx set num=10 where id=1;
select * from tx;
+------+------+
| id   | num  |
+------+------+
|    1 |   10 |
|    2 |    2 |
|    3 |    3 |
+------+------+
commit;

#事务A：这时候即使事务B已经提交了,但A能不能看到数据变化？
select * from tx;
+------+------+
| id   | num  |
+------+------+
|    1 |    1 | --->还是看不到的！(这个级别2不一样，也说明级别3解决了不可重复读问题)
|    2 |    2 |
|    3 |    3 |
+------+------+

#事务A：只有当事务A也提交了，它才能够看到数据变化
commit;
select * from tx;
+------+------+
| id   | num  |
+------+------+
|    1 |   10 |
|    2 |    2 |
|    3 |    3 |
+------+------+

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

#首先修改隔离界别
set tx_isolation='serializable';
select @@tx_isolation;
+----------------+
| @@tx_isolation |
+----------------+
| SERIALIZABLE   |
+----------------+

#事务A：开启一个新事务
start transaction;

#事务B：在A没有commit之前，这个交叉事务是不能更改数据的
start transaction;
insert tx values('4','4');
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
update tx set num=10 where id=1;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

二索引

索引是帮助MySQL高效查询数据的数据结构（类似书籍目录），可加快查询速度，但会降低插入/更新效率（需维护索引）。如果没有索引，一般来说执行查询时遍历整张表。

索引的原理很简单，就是把无序的数据变成有序的查询

把创建了索引的列的内容进行排序
对排序结果生成倒排表
在倒排表内容上拼上数据地址链
在查询的时候，先拿到倒排表内容，再取出数据地址链，从而拿到具体数据

2.1 创建索引的原则（重中之重）

索引虽好，但也不是无限制的使用，最好符合一下几个原则

1）最左前缀匹配原则，组合索引非常重要的原则，mysql会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配，比如a = 1 and b = 2 and c > 3 and d = 4 如果建立(a,b,c,d)顺序的索引，d是用不到索引的，如果建立(a,b,d,c)的索引则都可以用到，a,b,d的顺序可以任意调整。

2）较频繁作为查询条件的字段才去创建索引

3）更新频繁字段不适合创建索引

4）若是不能有效区分数据的列不适合做索引列(如性别，男女未知，最多也就三种，区分度实在太低)

5）尽量的扩展索引，不要新建索引。比如表中已经有a的索引，现在要加(a,b)的索引，那么只需要修改原来的索引即可。

6）定义有外键的数据列一定要建立索引。

7）对于那些查询中很少涉及的列，重复值比较多的列不要建立索引。

8）对于定义为text、image和bit的数据类型的列不要建立索引。

索引的缺点

时间方面：创建索引和维护索引要耗费时间，具体地，当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，会降低增/改/删的执行效率；

空间方面：索引需要占物理空间。

2.2 常见索引

主键索引：针对主键创建，唯一且非空，一个表只能有一个。
- 可以通过 ALTER TABLE table_name ADD UNIQUE (column); 创建唯一索引
- 可以通过 ALTER TABLE table_name ADD UNIQUE (column1,column2); 创建唯一组合索引
唯一索引：索引列值唯一（允许空值），如用户表的手机号。
普通索引：最基本的索引，无唯一性限制。
- 可以通过ALTER TABLE table_name ADD INDEX index_name (column);创建普通索引
- 可以通过ALTER TABLE table_name ADD INDEX index_name(column1, column2, column3);创建组合索引
联合索引：多列组合的索引，遵循“最左前缀原则”（查询需包含最左列才可能命中）。
全文索引：用于长文本搜索（如文章内容），仅MyISAM和InnoDB（5.6+）支持。
- 可以通过ALTER TABLE table_name ADD FULLTEXT (column);创建全文索引

创建索引的三种方式，删除索引

第一种方式：在执行CREATE TABLE时创建索引

CREATE TABLE user_index2 (
	id INT auto_increment PRIMARY KEY,
	first_name VARCHAR (16),
	last_name VARCHAR (16),
	id_card VARCHAR (18),
	information text,
	KEY name (first_name, last_name),
	FULLTEXT KEY (information),
	UNIQUE KEY (id_card)
);

第二种方式：使用ALTER TABLE命令去增加索引

ALTER TABLE table_name ADD INDEX index_name (column_list);

ALTER TABLE用来创建普通索引、UNIQUE索引或PRIMARY KEY索引。

其中table_name是要增加索引的表名，column_list指出对哪些列进行索引，多列时各列之间用逗号分隔。

索引名index_name可自己命名，缺省时，MySQL将根据第一个索引列赋一个名称。另外，ALTER TABLE允许在单个语句中更改多个表，因此可以在同时创建多个索引。

第三种方式：使用CREATE INDEX命令创建

CREATE INDEX index_name ON table_name (column_list);

CREATE INDEX可对表增加普通索引或UNIQUE索引。（但是，不能创建PRIMARY KEY索引）

删除索引

根据索引名删除普通索引、唯一索引、全文索引：alter table 表名 drop KEY 索引名

alter table user_index drop KEY name;
alter table user_index drop KEY id_card;
alter table user_index drop KEY information;

但通常不会删除主键，因为设计主键一定与业务逻辑无关。

2.3 索引类型

索引的数据结构和具体存储引擎的实现有关，在MySQL中使用较多的索引有Hash索引，B+树索引等，而我们经常使用的InnoDB存储引擎的默认索引实现为：B+树索引。对于哈希索引来说，底层的数据结构就是哈希表，因此在绝大多数需求为单条记录查询的时候，可以选择哈希索引，查询性能最快；其余大部分场景，建议选择BTree索引。

B-Tree索引（默认）：适合等值查询、范围查询，InnoDB和MyISAM均支持。
哈希索引：Memory引擎默认使用，仅支持等值查询，不支持范围查询。
全文索引（FULLTEXT）：用于文本搜索，支持MATCH AGAINST语法。
空间索引（SPATIAL）：用于地理空间数据（如GIS）。
复合索引：多个字段组合的索引，遵循最左前缀原则。

哈希索引

简要说下，类似于数据结构中简单实现的HASH表（散列表）一样，当我们在mysql中用哈希索引时，主要就是通过Hash算法（常见的Hash算法有直接定址法、平方取中法、折叠法、除数取余法、随机数法），将数据库字段数据转换成定长的Hash值，与这条数据的行指针一并存入Hash表的对应位置；如果发生Hash碰撞（两个不同关键字的Hash值相同），则在对应Hash键下以链表形式存储。当然这只是简略模拟图。

Hash Hash索引只能用于对等比较，例如=,<=>（相当于=）操作符。由于是一次定位数据，不像BTree索引需要从根节点到枝节点，最后才能访问到页节点这样多次IO访问，所以检索效率远高于BTree索引。

B树和B+树的区别

在B树中，你可以将键和值存放在内部节点和叶子节点；但在B+树中，内部节点都是键，没有值，叶子节点同时存放键和值。

B+树的叶子节点有一条链相连，而B树的叶子节点各自独立。

使用B树的好处

B树可以在内部节点同时存储键和值，因此，把频繁访问的数据放在靠近根节点的地方将会大大提高热点数据的查询效率。这种特性使得B树在特定数据重复多次查询的场景中更加高效。

使用B+树的好处

由于B+树的内部节点只存放键，不存放值，因此，一次读取，可以在内存页中获取更多的键，有利于更快地缩小查找范围。 B+树的叶节点由一条链相连，因此，当需要进行一次全数据遍历的时候，B+树只需要使用O(logN)时间找到最小的一个节点，然后通过链进行O(N)的顺序遍历即可。而B树则需要对树的每一层进行遍历，这会需要更多的内存置换次数，因此也就需要花费更多的时间

数据库为什么使用B+树而不是B树

B树只适合随机检索，而B+树同时支持随机检索和顺序检索；

B+树空间利用率更高，可减少I/O次数，磁盘读写代价更低。一般来说，索引本身也很大，不可能全部存储在内存中，因此索引往往以索引文件的形式存储的磁盘上。这样的话，索引查找过程中就要产生磁盘I/O消耗。B+树的内部结点并没有指向关键字具体信息的指针，只是作为索引使用，其内部结点比B树小，盘块能容纳的结点中关键字数量更多，一次性读入内存中可以查找的关键字也就越多，相对的，IO读写次数也就降低了。而IO读写次数是影响索引检索效率的最大因素；

B+树的查询效率更加稳定。B树搜索有可能会在非叶子结点结束，越靠近根节点的记录查找时间越短，只要找到关键字即可确定记录的存在，其性能等价于在关键字全集内做一次二分查找。而在B+树中，顺序检索比较明显，随机检索时，任何关键字的查找都必须走一条从根节点到叶节点的路，所有关键字的查找路径长度相同，导致每一个关键字的查询效率相当。

B-树在提高了磁盘IO性能的同时并没有解决元素遍历的效率低下的问题。B+树的叶子节点使用指针顺序连接在一起，只要遍历叶子节点就可以实现整棵树的遍历。而且在数据库中基于范围的查询是非常频繁的，而B树不支持这样的操作。

增删文件（节点）时，效率更高。因为B+树的叶子节点包含所有关键字，并以有序的链表结构存储，这样可很好提高增删效率。

2.4 聚簇索引和非聚簇索引

mysql聚簇索引和非聚簇索引的区别是：对于聚簇索引，表数据按顺序存储，即索引顺序和表记录物理存储顺序一致；对于非聚簇索引，表数据存储顺序与索引顺序无关。

聚簇索引：索引和数据存储在一起，InnoDB的主键索引是聚簇索引。
- 优点：查询效率高，可直接获取数据。
- 缺点：插入操作可能导致页分裂，影响性能。
非聚簇索引：索引和数据分离存储，MyISAM的索引和InnoDB的二级索引（非主键索引）都是非聚簇索引。
- 二级索引需要通过索引树找到主键，再通过主键回表查询数据（两次查询）。

聚簇索引：

将数据存储与索引放到了一块，找到索引也就找到了数据

表数据按顺序存储，即索引顺序和表记录物理存储顺序一致。

聚簇索引叶子节点存储数据行和B-Tree索引。

在一个表中只能有一个聚簇索引，因为真实物理存储顺序只能有一种。

聚簇索引是稀疏索引，数据页上一级索引存储是页指针，非行指针。

非聚簇索引：

将数据存储于索引分开结构，索引结构的叶子节点指向了数据的对应行，myisam通过key_buffer把索引先缓存到内存中，当需要访问数据时（通过索引访问数据），在内存中直接搜索索引，然后通过索引找到磁盘相应数据，这也就是为什么索引不在key buffer命中时，速度慢的原因

表数据存储顺序与索引顺序无关。

对于非聚簇索引，叶节点包含行主键值和主键值作为指向行的“指针”，通过二级索引查找，两次B-Tree查找 InnoDB自适应哈希索引能够减少这样重复工作。

非聚簇索引是密集索引，在数据页的上一级索引页它为每一个数据行存储一条记录。

非聚簇索引一定会回表查询吗？

不一定，这涉及到查询语句所要求的字段是否全部命中了索引，如果全部命中了索引，那么就不必再进行回表查询。

举个简单的例子，假设我们在员工表的年龄上建立了索引，那么当进行select age from employee where age < 20的查询时，在索引的叶子节点上，已经包含了age信息，不会再次进行回表查询。

2.5 索引失效

索引失效指查询时未使用预期索引，导致全表扫描（性能下降）。

常见情况：

索引列使用函数或运算（如where age+1=10）。
用!=、<>、not in、is not null等操作符（视数据分布可能失效）。
字符串不加引号（如where name=123，导致类型转换）。
联合索引不满足最左前缀原则（如索引(a,b,c)，查询条件不含a）。
like以%开头（如where name like '%张三'）。

2.7 创建索引时需要注意什么？

非空字段：应该指定列为NOT NULL，除非你想存储NULL。在mysql中，含有空值的列很难进行查询优化，因为它们使得索引、索引的统计信息以及比较运算更加复杂。你应该用0、一个特殊的值或者一个空串代替空值；
取值离散大的字段：（变量各个取值之间的差异程度）的列放到联合索引的前面，可以通过count()函数查看字段的差异值，返回值越大说明字段的唯一值越多字段的离散程度高；
索引字段越小越好：数据库的数据存储以页为单位一页存储的数据越多一次IO操作获取的数据越大效率越高。

三数据的备份与恢复

物理备份：
- 使用mysqlpump或xtrabackup直接复制数据文件（如/var/lib/mysql）。适合大数据库，速度快，需停库或锁表，适合全量备份。
逻辑备份：
- 使用mysqldump导出SQL语句（适合小数据库）。
- 示例：mysqldump -u root -p dbname > backup.sql
恢复方法：
- 物理恢复：停止MySQL，替换数据文件，重启服务。
- 逻辑恢复：执行备份的SQL文件。
- 示例：mysql -u root -p dbname < backup.sql
增量备份：结合 binlog 日志，备份两次全量备份之间的 binlog，恢复时先恢复全量，再执行 binlog 中的增量操作。

四数据库设计优化

分页查询优化

分页查询用limit实现，语法：select * from table limit 偏移量, 每页条数（如limit 1000, 10表示从第 1001 行取 10 条）。

问题：当偏移量过大（如limit 100000, 10），MySQL 需扫描前 100010 行再丢弃前 100000 行，效率低。
优化：①用主键定位（如where id > 100000 limit 10，需主键有序）；②用索引覆盖查询（只查索引列，减少数据访问）。

高并发场景下的MySQL表结构设计

分库分表：
- 垂直拆分：按字段拆分表（如将大字段单独存储）。
- 水平分片：按哈希或范围将数据分散到多个表/库（如按用户ID取模）。
缓存层：使用Redis缓存热点数据，减轻数据库压力。
读写分离：主库写，从库读，通过主从复制同步数据。
避免大事务：减少事务持有锁的时间，降低锁冲突。
索引优化：合理创建复合索引，避免全表扫描。
增加冗余字段：设计数据表时应尽量遵循范式理论的规约，尽可能的减少冗余字段，让数据库设计看起来精致、优雅。但是，合理的加入冗余字段可以提高查询速度。

优化慢查询

开启慢查询日志：配置slow_query_log和long_query_time参数。
索引优化：
- 为经常用于WHERE、JOIN、ORDER BY的字段添加索引。
- 避免索引失效（如函数操作、模糊查询以%开头）。
查询优化：
- 避免SELECT *，只查询需要的字段。
- 使用EXPLAIN分析查询执行计划，检查是否全表扫描或索引未命中。
- EXPLAIN Type：system > const > eq_ref > ref > range > index > all ：
数据库设计：
- 合理范式化（减少数据冗余）与反范式化（提高查询效率）。
- 分库分表（垂直拆分或水平分片）。
配置优化：
- 调整innodb_buffer_pool_size（缓冲池大小）。
- 优化查询缓存（MySQL 8.0已移除）。
硬件升级：使用SSD、增加内存、优化磁盘IO。

防止SQL注入

答案：SQL注入是攻击者通过输入恶意SQL片段（如' or '1'='1），篡改原SQL逻辑，非法获取或修改数据。

防止方法：

使用预处理语句（prepareStatement）或参数化查询，避免拼接SQL。
对输入进行过滤/转义（如过滤单引号、特殊字符）。
限制数据库用户权限（最小权限原则）。

分表原则

阿里巴巴《Java 开发手册》提出单表行数超过 500 万行或者单表容量超过 2GB，才推荐进行分库分表。对此，有阿里的黄金铁律支撑，所以，很多人设计大数据存储时，多会以此为标准，进行分表操作。

Mysql查询库表大小：

SELECT CONCAT(table_schema,'.',table_name) AS 'Table Name', table_rows AS 'Number of Rows', CONCAT(ROUND(data_length/(1024*1024),4),'M') AS 'Data Size', CONCAT(ROUND(index_length/(1024*1024),4),'M') AS 'Index Size', CONCAT(ROUND((data_length+index_length)/(1024*1
024),4),'M') AS'Total'FROM information_schema.TABLES WHERE table_schema LIKE 'imc_report';

InnoDB存储引擎也有自己的最小储存单元——页（Page），一个页的大小是16K。

数据表中的数据都是存储在页中的，所以一个页中能存储多少行数据呢？假设一行数据的大小是1k，那么一个页可以存放16行这样的数据。

MySQL数据库cpu飙升到500%的话他怎么处理？

当 cpu 飙升到 500%时，先用操作系统命令 top 命令观察是不是 mysqld 占用导致的，如果不是，找出占用高的进程，并进行相关处理。

如果是 mysqld 造成的， show processlist，看看里面跑的 session 情况，是不是有消耗资源的 sql 在运行。找出消耗高的 sql，看看执行计划是否准确， index 是否缺失，或者实在是数据量太大造成。

一般来说，肯定要 kill 掉这些线程(同时观察 cpu 使用率是否下降)，等进行相应的调整(比如说加索引、改 sql、改内存参数)之后，再重新跑这些 SQL。

也有可能是每个 sql 消耗资源并不多，但是突然之间，有大量的 session 连进来导致 cpu 飙升，这种情况就需要跟应用一起来分析为何连接数会激增，再做出相应的调整，比如说限制连接数等

百万级别或以上的数据如何删除

关于索引：由于索引需要额外的维护成本，因为索引文件是单独存在的文件,所以当我们对数据的增加,修改,删除,都会产生额外的对索引文件的操作,这些操作需要消耗额外的IO,会降低增/改/删的执行效率。所以，在我们删除数据库百万级别数据的时候，查询MySQL官方手册得知删除数据的速度和创建的索引数量是成正比的。

所以我们想要删除百万数据的时候可以先删除索引（此时大概耗时三分多钟）
然后删除其中无用数据（此过程需要不到两分钟）
删除完成后重新创建索引(此时数据较少了)创建索引也非常快，约十分钟左右。
与之前的直接删除绝对是要快速很多，更别说万一删除中断,一切删除会回滚。那更是坑了。

mysql中 in 和 exists 区别

mysql中的in语句是把外表和内表作hash 连接，而exists语句是对外表作loop循环，每次loop循环再对内表进行查询。一直大家都认为exists比in语句的效率要高，这种说法其实是不准确的。这个是要区分环境的。

如果查询的两个表大小相当，那么用in和exists差别不大。如果两个表中一个较小，一个是大表，则子查询表大的用exists，子查询表小的用in。 not in 和not exists：如果查询语句使用了not in，那么内外表都进行全表扫描，没有用到索引；而not extsts的子查询依然能用到表上的索引。所以无论那个表大，用not exists都比not in要快。

mysql中int(10)和char(10)以及varchar(10)的区别

int(10)的10表示显示的数据的长度，不是存储数据的大小；chart(10)和varchar(10)的10表示存储数据的大小，即表示存储多少个字符。

int(10) 10位的数据长度 9999999999，占32个字节，int型4位

char(10) 10位固定字符串，不足10个就用空格补齐最多10个字符

varchar(10)表示存储10个变长的字符，存储多少个就是多少个，空格也按一个字符存储，这一点是和char(10)的空格不同的，char(10)的空格表示占位不算一个字符

drop、delete与truncate的区别

drop最快，truncate次之，delete最慢；

因此，在不再需要一张表的时候，用drop；在想删除部分数据行时候，用delete；在保留表而删除所有数据的时候用truncate。

主键使用自增ID还是UUID？

推荐使用自增ID，不要使用UUID。

因为在InnoDB存储引擎中，主键索引是作为聚簇索引存在的，也就是说，主键索引的B+树叶子节点上存储了主键索引以及全部的数据(按照顺序)，如果主键索引是自增ID，那么只需要不断向后排列即可，如果是UUID，由于到来的ID与原来的大小不确定，会造成非常多的数据插入，数据移动，然后导致产生很多的内存碎片，进而造成插入性能的下降。

总之，在数据量大一些的情况下，用自增主键性能会好一些。

关于主键是聚簇索引，如果没有主键，InnoDB会选择一个唯一键来作为聚簇索引，如果没有唯一键，会生成一个隐式的主键。

字段为什么要求定义为not null？

null值会占用更多的字节，且会在程序中造成很多与预期不符的情况。

explain执行计划

对于低性能的SQL语句的定位，最重要也是最有效的方法就是使用执行计划，MySQL提供了explain命令来查看语句的执行计划。

执行计划包含的信息 id 有一组数字组成。表示一个查询中各个子查询的执行顺序;

id

id相同执行顺序由上至下。
id不同，id值越大优先级越高，越先被执行。
id为null时表示一个结果集，不需要使用它查询，常出现在包含union等查询语句中。
select_type 每个子查询的查询类型，一些常见的查询类型。

select_type

id select_type description 1 SIMPLE 不包含任何子查询或union等查询 2 PRIMARY 包含子查询最外层查询就显示为 PRIMARY 3 SUBQUERY 在select或 where字句中包含的查询 4 DERIVED from字句中包含的查询 5 UNION 出现在union后的查询语句中 6 UNION RESULT 从UNION中获取结果集，例如上文的第三个例子

table

table 查询的数据表，当从衍生表中查数据时会显示 x 表示对应的执行计划id partitions 表分区、表创建的时候可以指定通过那个列进行表分区。举个例子：

type

type(非常重要，可以看到有没有走索引) 访问类型

ALL 扫描全表数据 index 遍历索引 range 索引范围查找 index_subquery 在子查询中使用 ref unique_subquery 在子查询中使用 eq_ref ref_or_null 对Null进行索引的优化的 ref fulltext 使用全文索引 ref 使用非唯一索引查找数据 eq_ref 在join查询中使用PRIMARY KEYorUNIQUE NOT NULL索引关联。

system > const > eq_ref > ref > range > index > all ：

possible_keys

possible_keys 可能使用的索引，注意不一定会使用。查询涉及到的字段上若存在索引，则该索引将被列出来。当该列为 NULL时就要考虑当前的SQL是否需要优化了。

key

key 显示MySQL在查询中实际使用的索引，若没有使用索引，显示为NULL。

TIPS:查询中若使用了覆盖索引(覆盖索引：索引的数据覆盖了需要查询的所有数据)，则该索引仅出现在key列表中

key_length

key_length 索引长度

ref 表示上述表的连接匹配条件，即哪些列或常量被用于查找索引列上的值

rows

rows 返回估算的结果集数目，并不是一个准确的值。

extra

extra 的信息非常丰富，常见的有：

Using index 使用覆盖索引 Using where 使用了用where子句来过滤结果集 Using filesort 使用文件排序，使用非索引列进行排序时出现，非常消耗性能，尽量优化。 Using temporary 使用了临时表 sql优化的目标可以参考阿里开发手册

重要

【推荐】SQL性能优化的目标：至少要达到 range 级别，要求是ref级别，如果可以是consts最好。 
说明： 
1） consts 单表中最多只有一个匹配行（主键或者唯一索引），在优化阶段即可读取到数据。 
2） ref 指的是使用普通的索引（normal index）。 
3） range 对索引进行范围检索。 
反例：explain表的结果，type=index，索引物理文件全扫描，速度非常慢，这个index级别比较range还低，与全表扫描是小巫见大巫。

五 Mysql常见面试题

MySQL中MyISAM和InnoDB存储引擎的主要区别是什么？

答案：两者核心区别体现在事务和锁机制上：
- MyISAM：不支持事务和外键，支持表级锁，查询速度快，适合读多写少场景（如日志表），崩溃后恢复困难。
- InnoDB：支持事务（ACID）、外键和行级锁，有崩溃恢复能力（依赖redo日志），适合写操作频繁的场景（如订单表），性能略低于MyISAM但安全性更高。
什么是索引？MySQL中常见的索引类型有哪些？

答案：索引是帮助MySQL高效查询数据的数据结构（类似书籍目录），可加快查询速度，但会降低插入/更新效率（需维护索引）。
常见类型：
- 主键索引：针对主键创建，唯一且非空，一个表只能有一个。
- 唯一索引：索引列值唯一（允许空值），如用户表的手机号。
- 普通索引：最基本的索引，无唯一性限制。
- 联合索引：多列组合的索引，遵循“最左前缀原则”（查询需包含最左列才可能命中）。
- 全文索引：用于长文本搜索（如文章内容），仅MyISAM和InnoDB（5.6+）支持。
MySQL事务的四大特性（ACID）是什么？

答案：事务是一组不可分割的操作，四大特性为：
- 原子性（Atomicity）：事务要么全执行，要么全不执行（如转账时一方扣款失败则另一方不收款）。
- 一致性（Consistency）：事务执行前后数据状态一致（如转账总金额不变）。
- 隔离性（Isolation）：多个事务并发执行时，彼此不干扰（通过隔离级别控制）。
- 持久性（Durability）：事务提交后，数据修改永久保存（即使断电也不丢失）。
MySQL的事务隔离级别有哪些？默认级别是什么？

答案：共4种隔离级别，从低到高依次为：
- 读未提交（Read Uncommitted）：可能读取到其他事务未提交的数据（脏读）。
- 读已提交（Read Committed）：只能读取其他事务已提交的数据，解决脏读，但可能出现不可重复读（同一事务内两次查询结果不同）。
- 可重复读（Repeatable Read）：同一事务内多次查询结果一致，解决不可重复读，可能出现幻读（新增数据被读取），是MySQL默认级别。
- 串行化（Serializable）：事务串行执行，避免所有并发问题，但性能极差。
什么是索引失效？哪些情况会导致索引失效？

答案：索引失效指查询时未使用预期索引，导致全表扫描（性能下降）。常见情况：
- 索引列使用函数或运算（如where age+1=10）。
- 用!=、<>、not in、is not null等操作符（视数据分布可能失效）。
- 字符串不加引号（如where name=123，导致类型转换）。
- 联合索引不满足最左前缀原则（如索引(a,b,c)，查询条件不含a）。
- like以%开头（如where name like '%张三'）。
MySQL中的锁有哪些类型？行级锁和表级锁的区别？

答案：按粒度分：
- 表级锁：锁定整张表，开销小、速度快，冲突概率高（如MyISAM的读锁/写锁）。
- 行级锁：锁定单行数据，开销大、速度慢，冲突概率低（InnoDB特有，依赖索引，无索引时会退化为表锁）。
  
  按功能分：共享锁（S锁，读锁，多个事务可同时持有）、排他锁（X锁，写锁，仅一个事务持有）。
什么是MVCC？它在InnoDB中如何实现？

答案：MVCC（多版本并发控制）是InnoDB实现隔离级别的基础，允许读写不冲突（读不加锁）。

实现方式：通过每行数据的隐藏列（DB_TRX_ID事务ID、DB_ROLL_PTR回滚指针）和undo日志（保存历史版本）。查询时，InnoDB会根据事务可见性规则，读取符合条件的历史版本，避免脏读和不可重复读。
MySQL如何优化慢查询？

答案：常见优化步骤：
- 开启慢查询日志（slow_query_log=1），设置阈值（long_query_time=1秒），定位慢SQL。
- 用explain分析SQL执行计划，查看是否走索引、扫描行数等。
- 优化索引（添加缺失索引，删除冗余索引）。
- 避免全表扫描（如优化where条件、不用select *）。
- 大表分库分表（水平分表按数据量，垂直分表按字段冷热）。
- 调整MySQL参数（如innodb_buffer_pool_size缓存大小）。
MySQL的主从复制原理是什么？

答案：主从复制用于实现数据备份和读写分离，流程为：
1. 主库（Master）将数据变更记录到binlog日志。
2. 从库（Slave）启动IO线程，连接主库读取binlog，写入本地relay日志。
3. 从库SQL线程读取relay日志，重演变更操作，使主从数据一致。
核心：异步复制（主库不等待从库确认），可能存在短暂数据延迟。
什么是SQL注入？如何防止？

答案：SQL注入是攻击者通过输入恶意SQL片段（如' or '1'='1），篡改原SQL逻辑，非法获取或修改数据。

防止方法：
- 使用预处理语句（prepareStatement）或参数化查询，避免拼接SQL。
- 对输入进行过滤/转义（如过滤单引号、特殊字符）。
- 限制数据库用户权限（最小权限原则）。

六相关资料

MySQL-面试必备 - 知乎

(1条消息) MySQL数据库面试题（2020最新版）_ThinkWon的博客-CSDN博客_mysql数据库面试题

概念#

SQL语句分类#

数据库三大范式是什么#

一 存储引擎#

1.1 存储引擎#

1.2 日志文件#

binlog日志作用#

binlog三种格式#

主从复制的原理#

复制过程#

1.3 锁定机制#

行级锁的含义#

Row Lock与Table Lock#

间隙锁(Net-Key锁)#

死锁#

1.4 事务四大特性（ACID）#

1.5 事务隔离级别#

1.6 Java @Transactional传播行为#

1.7 MVCC（多版本并发控制）#

1. MVCC 的核心目标#

2. 关键技术组件#

（1）隐藏列#

（2）Undo Log（回滚日志）#

（3）Read View（读视图）#

3. 工作原理：版本可见性判断#

4. 不同隔离级别的 MVCC 差异#

1.8 事务隔离级别示例#

二 索引#

2.1 创建索引的原则（重中之重）#

2.2 常见索引#

创建索引的三种方式，删除索引#

删除索引#

2.3 索引类型#

哈希索引#

B树和B+树的区别#

使用B树的好处#

使用B+树的好处#

数据库为什么使用B+树而不是B树#

2.4 聚簇索引和非聚簇索引#

2.5 索引失效#

2.7 创建索引时需要注意什么？#

三 数据的备份与恢复#

四 数据库设计优化#

分页查询优化#

高并发场景下的MySQL表结构设计#

优化慢查询#

防止SQL注入#

分表原则#

MySQL数据库cpu飙升到500%的话他怎么处理？#

百万级别或以上的数据如何删除#

mysql中 in 和 exists 区别#

mysql中int(10)和char(10)以及varchar(10)的区别#

drop、delete与truncate的区别#

主键使用自增ID还是UUID？#

字段为什么要求定义为not null？#

explain执行计划#

id#

select_type#

table#

type#

possible_keys#

key#

key_length#

rows#

extra#

重要#

五 Mysql常见面试题#

六 相关资料#

概念