MySQL面经（2025.6.12）

基本操作

连接数据库
cmd管理员

net start mysql
mysql -u name -p password

数据库操作，DDL

DDL，Data Definition Language
数据定义语言，定于数据库对象（数据库，表，字段）

// 查询所有数据库
show databases;

// 建表
create table 表名(
	字段名 字段类型 [约束] [comment 字段注释],
	[conostraint] [外键名称] foreign key (外键字段名) references 主表 (主表列名)
)[comment 表注释];
// 表加字段
alter table 表名 ADD 字段名 字段类型 [comment 注释] [约束];
// 表加外键
alter table 表名 add constraint 外键名称 foreign key (外键字段名) references 主表 (主表列名);
// 表修改数据类型
alter table 表名 modify 字段名 新数据类型;
// 表修改字段名和字段类型
alter table 表名 change 旧字段名 新字段名 数据类型 [comment 注释] [约束];
// 删除字段
alter table 表名 drop 字段名;
// 删除外键
alter table 表名 drop foreign key 外键名称;
// 修改表名
alter table 表名 rename to 新表名;
// 删除表
drop table [if exists] 表名;

表操作，DML，DQL

DML，Data Manipulation Language
数据操作语言，对数据库表数据进行增删改

//添加数据
insert into 表名(字段名,...) values(值,...);
//修改数据
update 表名 set 字段名=值,... [where 条件];
//删除数据
delete from 表名 [where 条件];

DQL，Data Query Language
查询数据库中表记录

select [distinct] 字段列表 [as 别名] from 表名列表
where 条件列表
group by
having 分组后条件列表 //执行顺序where > 聚合函数 > having
order by [asc, desc]
limit 起始索引, 查询记录数
;

DCL

Data Control Language，数据控制语言，管理数据库用户、控制数据库访问权限

// 创建用户
create user '用户名'@'主机名' identified by '密码';
// 修改用户密码
alter user '用户名'@'主机名' identified with mysql_native_password by '密码';
// 删除用户
drop user '用户名'@'主机名';

函数

curdate()
curtime()
now()
year(date)
month(date)
day(date)
// 给date加上exper的时间值
date_add(date, intergval exper type)
// date1和date2之间的时间间隔
datediff(date1, date2)

多表查询

内连接

查询的时两表交集部分

隐式内连接

select 字段列表 from 表1,表2 where 条件;

select emp.name, dept.name from emp, dept where emp.dept_id = dept.id;

显式内连接

select 字段列表 from 表1 [inner] join 表2 on 连接条件;
select e.name, d.name from emp e join dept d on e.dept_id = d.id;

外连接

左外连接
完全包含左表数据

select 字段列表 from 表1 left [outer] join 表2 on 条件;
// emp表的所有数据和对应部门名称
select e.* from emp e left outer join dept d on e.dept_id = d.id;

右外连接

select 字段列表 from 表1 right [outer] join 表2 on 条件;
// 查询dept表的所有数据，和对应员工信息
select d.*, e.* from emp right outer join dept d on e.dept_id = d.id;

自连接

select 字段列表 from 表a join 表b on 条件;

事务

//查看或设置事务提交方式
select @@autocommit;
set @@autocommit = 0;
// 提交事务
commit;
// 回滚事务
rollback;

start transaction;
commit;
rollback;

ACID



事务隔离级别越高，安全性越高

// 查看事务隔离级别
select @@transaction_isolation;
// 设置事务隔离级别
set [session | global] transaction isolation level {read uncommitted | read committed | repeatable read | serializable}

MySQL体系结构

连接层：连接处理、授权认证等
服务层：SQL接口、缓存查询、SQL分析优化
引擎层：负责MySQL中数据的存储和提取，服务器通过API和存储引擎进行通信
存储层：将数据存储在文件系统

索引

MySQL的索引是在存储引擎层实现的

索引结构

B-Tree

B+Tree

MySQL的B+Tree

在原B+Tree的基础上，增加一个指向相邻叶子节点的链表指针，就形成了带有顺序指针的B+Tree

Hash

A. Hash索引只能用于对等比较(=，in)，不支持范围查询（between，>，< ，…）
B. 无法利用索引完成排序操作
C. 查询效率高，通常(不存在hash冲突的情况)只需要一次检索就可以了，效率通常要高于B+tree索
引
在MySQL中，支持hash索引的是Memory存储引擎。 而InnoDB中具有自适应hash功能，hash索引是
InnoDB存储引擎根据B+Tree索引在指定条件下自动构建的。

索引分类

MySQL中分为主键索引(primary)、唯一索引(unique)、常规索引、全文索引(fulltext)
InnoDB存储引擎中，根据存储形式分为聚集索引（Clustered Index）和二级索引（Secondary Index）

索引语法

// 查看索引
show index from 表名;
// 删除索引
drop index 索引名 on 表名;
// 创建索引
create [unique|fulltext] index 索引名 on 表名(字段名,...);

// 查看sql执行频次
show [global|session] status like 'Com_______';

索引使用原则

如果索引了多列（联合索引），要遵守最左前缀法则。最左前缀法则指的是查询从索引的最左列开始，并且不跳过索引中的列。如果跳跃某一列，索引将会部分失效(后面的字段索引失效)。

联合索引中，出现范围查询(>,<)，范围查询右侧的列索引失效。

索引失效

//进行函数运算操作

// 字符串不加引号
explain select * from tb_user where phone = 17799990015;
// 头部模糊查询
explain select * from tb_user where profession like '%工程';
// or连接了没有索引的列
// mysql评估全表扫描更快

SQL提示

索引使用

覆盖索引

覆盖索引：查询时使用索引，并且返回的列在索引中都能找到
使用select * 易出现回表查询

前缀索引

单列索引和联合索引

SQL优化

insert优化

主键顺序插入，性能高于乱序插入

InnoDB逻辑结构图：

Page是InnoDB磁盘管理的最小单元

手动控制事务

start transaction;
insert into tb_test values (1, 'Tom'), (2, 'Cat');
...
commit;

大批量插入

mysql --local-infile -u root -p

select @@local_infile;
set global local_infile = 1;

load data local infile '位置' into table 表名 fields terminated by ',' lines terminated by '\n';

主键优化

order by 优化

Using index : 直接通过索引返回数据
Using filesort：通过表的索引或全表扫描，读取满足条件的数据行，然后在排序缓冲区sort buffer中完成排序操作

group by 优化

limit 优化

覆盖索引+子查询

explain select * from tb_sku t , (select id from tb_sku order by id limit 2000000,10) a where t.id = a.id;

count 优化

MyISAM 执行count(*)时直接返回记录在磁盘上的总行数

count()记录的是不为NULL的数量

update 优化

InnoDB的行锁是针对索引加的锁，不是针对记录加的锁 ,并且该索引不能失效，否则会从行锁升级为表锁 。

视图

语法

//创建视图
CREATE [OR REPLACE] VIEW 视图名称[(列名列表)] AS SELECT语句 [ WITH [CASCADED | LOCAL ] CHECK OPTION ]

//查看创建视图语句
SHOW CREATE VIEW 视图名称;
//查看视图数据
SELECT * FROM 视图名称 ...... ;

//修改视图
CREATE [OR REPLACE] VIEW 视图名称[(列名列表)] AS SELECT语句 [ WITH [ CASCADED | LOCAL ] CHECK OPTION ]
ALTER VIEW 视图名称[(列名列表)] AS SELECT语句 [ WITH [ CASCADED | LOCAL ] CHECK OPTION ]

//删除视图
DROP VIEW [IF EXISTS] 视图名称 [,视图名称] ...

检查选项with check option

cascaded

local

视图的更新

存储过程

语法

// 创建
create procedure 存储过程名称([参数列表])
begin
end;

//调用
call 名称([参数]);

//查询指定数据库的存储过程及状态信息
select * from information_schema.routines where routine_schema = 'xxx';
//查询某个存储过程的定义
show create procedure 存储过程名称;

//删除存储过程
drop procedure [if exists] 存储过程名称;

变量

系统变量、用户定义变量、局部变量

系统变量

全局变量、会话变量（默认）

//查看所有系统变量
show [session | global] variables;
show [session | global] variables like '';
//查看指定变量值
select @@[session | global] 系统变量名;

//设置系统变量
set [session | global] 系统变量名 = 值;
set @@[session | global] 系统变量名 = 值;

用户定义变量

作用域为当前会话
用户定义的变量无需对其进行声明或初始化，只不过获取到的值为NULL。

//赋值
set @var_name = expr [, @var_name = expr];
set @var_name := expr [, @var_name := expr];
select @var_name := expr [, @var_name := expr]; //必须是:=
select 字段名 into @var_name from 表名;

//使用
select @var_name;

局部变量

// 声明 int, bigint, char, varchar, date, time...
declare 变量名 变量类型 [default ...];

// 赋值
set 变量名 = 值;
set 变量名 := 值;
select 字段名 into 变量名 from 表名;

其他语法

// if
IF 条件1 THEN
.....
ELSEIF 条件2 THEN -- 可选
.....
ELSE -- 可选
.....
END IF;

//参数
CREATE PROCEDURE 存储过程名称 ([ IN/OUT/INOUT 参数名 参数类型 ])
BEGIN
-- SQL语句
END ;

// case
-- 含义： 当case_value的值为 when_value1时，执行statement_list1，当值为 when_value2时，
执行statement_list2， 否则就执行 statement_list
CASE case_value
WHEN when_value1 THEN statement_list1
[ WHEN when_value2 THEN statement_list2] ...
[ ELSE statement_list ]
END CASE;
-- 含义： 当条件search_condition1成立时，执行statement_list1，当条件search_condition2成
立时，执行statement_list2， 否则就执行 statement_list
CASE
WHEN search_condition1 THEN statement_list1
[WHEN search_condition2 THEN statement_list2] ...
[ELSE statement_list]
END CASE;

//while
-- 先判定条件，如果条件为true，则执行逻辑，否则，不执行逻辑
WHILE 条件 DO
SQL逻辑...
END WHILE;

//repeat
-- 先执行一次逻辑，然后判定UNTIL条件是否满足，如果满足，则退出。如果不满足，则继续下一次循环
REPEAT
SQL逻辑...
UNTIL 条件
END REPEAT;

//loop
[begin_label:] LOOP
SQL逻辑...
END LOOP [end_label];
LEAVE label; -- 退出指定标记的循环体
ITERATE label; -- 直接进入下一次循环

//游标
//声明游标
declare 游标名称 cursor for 查询语句;
//打开游标
open 游标名称;
//获取游标记录
fetch 游标名称 into 变量[, 变量];
//关闭游标
close 游标名称;

//条件处理程序
DECLARE handler_action HANDLER FOR condition_value [, condition_value]
... statement ;

handler_action 的取值：
	CONTINUE: 继续执行当前程序
	EXIT: 终止执行当前程序
condition_value 的取值：
	SQLSTATE sqlstate_value: 状态码，如 02000
	SQLWARNING: 所有以01开头的SQLSTATE代码的简写
	NOT FOUND: 所有以02开头的SQLSTATE代码的简写
	SQLEXCEPTION: 所有没有被SQLWARNING 或 NOT FOUND捕获的SQLSTATE代码的简写

存储函数

CREATE FUNCTION 存储函数名称 ([ 参数列表 ])
RETURNS type [characteristic ...]
BEGIN
-- SQL语句
RETURN ...;
END;

characteristic说明：
	DETERMINISTIC：相同的输入参数总是产生相同的结果
	NO SQL ：不包含 SQL 语句。
	READS SQL DATA：包含读取数据的语句，但不包含写入数据的语句。

select 存储函数名称;

触发器

语法

//创建
create trigger 名称
before/after insert/update/delete
on 表名 for each row
begin
end;

//查看
show triggers;

//删除
drop trigger [schema_name.]trigger_name; //如果没有指定 schema_name，默认为当前数据库

锁

全局锁

//加全局锁
flush tables with read lock;

//数据备份（在本地cmd中）
mysqldump -h主机号 -uroot -p密码 HeiMa1 > D:/HeiMa1.sql

//释放锁
unlock tables;

表级锁

表锁、元数据锁（Meta Data Lock，MDL）、意向锁

表锁

表共享读锁（read lock）、表独占写锁（write lock）

//加锁
lock tables 表名... read/write

//释放锁
unlock tables / 客户端断开连接

读锁不会阻塞其他客户端的读，但是会阻塞写。写锁既会阻塞其他客户端的读，又会阻塞其他客户端的写。

元数据锁（Meta Data Lock，MDL）

避免DML和DDL冲突，保证读写的正确性

select object_type, object_schema, object_name, lock_type, lock_duration from performance_schema.metadata_locks;

意向锁

表锁不用检查每行是否有加锁，减少表锁的检查

select object_schema,object_name,index_name,lock_type,lock_mode,lock_data from performance_schema.data_locks;

行级锁

对索引上的索引项加锁来实现

行锁

共享锁（S）：允许一个事务去读一行，阻止其他事务获得相同数据集的排它锁。
排他锁（X）：允许获取排他锁的事务更新数据，阻止其他事务获得相同数据集的共享锁和排他
锁。

间隙锁、临键锁

InnoDB存储引擎

//存储目录
cd /var/lib/mysql

事务原理

• 原子性（Atomicity）：事务是不可分割的最小操作单元，要么全部成功，要么全部失败。
• 一致性（Consistency）：事务完成时，必须使所有的数据都保持一致状态。
• 隔离性（Isolation）：数据库系统提供的隔离机制，保证事务在不受外部并发操作影响的独立环
境下运行。
• 持久性（Durability）：事务一旦提交或回滚，它对数据库中的数据的改变就是永久的

redolog（持久性）

重做日志，记录的是事务提交时数据页的物理修改，是用来实现事务的持久性。
该日志文件由两部分组成：重做日志缓冲（redo log buffer）以及重做日志文件（redo log file）,前者是在内存中，后者在磁盘中。当事务提交之后会把所有修改信息都存到该日志文件中, 用于在刷新脏页到磁盘,发生错误时, 进行数据恢复使用。

undolog（原子性）

回滚日志，用于记录数据被修改前的信息 , 作用包含两个 : 提供回滚(保证事务的原子性) 和 MVCC(多版本并发控制) 。

undo log和redo log记录物理日志不一样，它是逻辑日志。可以认为当delete一条记录时，undo log中会记录一条对应的insert记录，反之亦然，当update一条记录时，它记录一条对应相反的update记录。当执行rollback时，就可以从undo log中的逻辑记录读取到相应的内容并进行回滚。

Undo log销毁：undo log在事务执行时产生，事务提交时，并不会立即删除undo log，因为这些日志可能还用于MVCC。

Undo log存储：undo log采用段的方式进行管理和记录，存放在前面介绍的 rollback segment 回滚段中，内部包含1024个undo log segment。

MVCC

Multi-Version Concurrency Control 多版本并发控制
维护与一个数据的多个版本，使读写操作没有冲突
MVCC实现依赖于数据库记录中的三个隐式字段、undo log日志、readView

当前读

读取的是记录的最新版本

select ... lock in share mode(共享锁)
select ... for update、update、insert、delete

快照读

读取的是记录数据的可见版本，可能是历史数据。不加锁，是非阻塞读

• Read Committed：每次select生成一个快照读
• Repeatable Read：开启事务后的第一个select是快照读
• Serializable：快照读会退化为当前读

MVCC实现

记录中的隐藏字段

undo log

readView