1. drop、truncate和delete区别

• drop（丢弃数据）：drop table 表名，直接删除整张表，在删除表的时候使用
• truncate（清空数据）：truncate table 表名，只删除表中的数据，再插入数据的时候自增长id从1开始，在清空表中数据的时候使用
• delete（删除数据）：delete from 表名 where 列名=值，删除某一行的数据，如果不加where子句和truncate table 表名作用类似

truncate和不带where子句的delete、以及drop都会删除表内的数据，但是truncate和delete只删除数据不删除表的定义，执行drop语句，此表的定义和结构也会删除，即drop之后对应的表不存在。

2. MySQL性能优化方向

• SQL优化
  • SQL语句优化
  • 索引优化
    • 选择合适的列建立索引
    • 正确使用索引
  • 慢查询优化
• 主从复制
• 分库分表
• 读写分离
• 冷热分离

3. SQL语句优化

1. 插入优化

   1. 批量插入：多个插入合并为批量插入
   2. 手动提交事务：避免频繁的事务开启和关闭
   3. 主键顺序插入：尽量保证主键插入的顺序
   4. 大批量数据插入使用load指令，将本地磁盘文件中的数据加载到数据库中

2. 主键优化

   1. 尽量降低主键的长度
   2. 插入数据时尽量选择顺序插入
   3. 尽量避免对主键的修改
   4. 索引页大小为16K
   5. 

3. order by优化：

   Using filesort：通过表的索引或全表扫描，读取满足条件的数据行，然后再排序缓冲区sort buffer中完成排序操作，所以不是通过索引直接返回排序结果的排序都叫FileSort排序

   Using index：通过有序索引顺序扫描直接返回有序数据，不需要额外排序，操作效率高

   1. 根据排序字段建立合适的索引，多字段排序时也遵循最左前缀法则
   2. 尽量使用覆盖索引
   3. 多字段排序，一个升序一个降序，此时需要注意联合索引在创建时的规则
   4. 如果不可避免地出现filesort，大数据量排序时，可以适当增大排序缓冲区大小sort_buffer_size(默认256k)

4. group by优化

   1. 分组操作时，可以通过索引提高效率
   2. 索引的使用也满足最左匹配原则

5. limit分页查询优化

   1. 覆盖索引 + 子查询的方式

   2. # 优化前
      select * from tb_sku order by id limit 9000000, 10;
      
      # 优化后
      select * from tb_sku where id in (select id from tb_sku order by id limit 9000000, 10);
      1
      2
      3
      4
      5
      6
      7
      8
      9
      10
      11
      12
      13
      14
      15
      16
      17
      18
      19
      20
      21
      22
      23
      24
      25
      26
      27
      28
      29
      30
      31
      32
      33
      34
      35
      36
      37
      
      6. count优化
      
         1. count(*)：MyISAM会把一张表的总行数存在磁盘上，因此执行count(\*)会直接返回这个数，效率很高。InnoDB需要数据一行一行读出来再累计计数，效率比较低。**优化思路是可以在redis中自己维护一个数据库表和行数的kv存储，缺点是比较麻烦**
         2. 效率：count(*) 约等于count(1) > count(主键id) > count(其他字段)，数据库对count(\*)有专门优化
      
      # 4. 选择合适的列建立索引
      
      - 列的值不应该经常变动
      - 列的长度尽可能短
      - 使用频繁，where从句中出现的
      
      # 5. 正确使用索引的建议
      
      - 遵循最左前缀法则
      - 使用覆盖索引减少回表查询
      - 避免索引失效
        - 隐式类型转换
        - 不满足最左匹配原则
      - 单表建立的索引一般不要超过3个
      - 尽量建议联合索引实现复用
      
      # 6. 慢查询优化
      
      1. **定位慢查询语句，性能分析**
      
         1. **查看数据库SQL的执行频次**，使用`show global status like 'Com_______;'`（7个下划线），如果该数据库的查询次数较多，可以尝试优化查询语句
      
            <img src="/Users/effy/Library/Application Support/typora-user-images/image-20250328142926710.png" alt="image-20250328142926710" style="zoom:50%;" />
      
         2. 慢查询日志: 记录所有执行时间超过指定参数(`long_query_time`,  默认10秒)的所有SQL语句的日志。MYSQL的慢查询日志默认没有开启(可以通过`show variables like 'slow_query_log'`查看是否开启)，可在配置文件(/etc/my.cnf)中配置
      
            ```shell
            # 开启MYSQL慢查询日志开关
            slow_query_log=1
            # SQL语句执行时间超过2秒，就会视为慢查询，记录慢查询日志
            long_query_time=2
      
      

   3. show profiles: 查看所有SQL语句的耗时时间，然后使用show profile [cpu] for query query_id，可以查看该SQL语句更详细的执行耗时时间/CPU占用

6. explain执行计划

7. explain执行计划分析


select_type:

type：性能由好到差的连接类型为NULL、system、const、eq_ref、ref、range、index、all

Extra：

https://blog.csdn.net/wuseyukui/article/details/71512793

8. 什么情况行锁退化成表锁

InnoDB的行锁是针对索引加的锁，不是针对记录加的锁

• 存储引擎不支持行锁：InnoDB支持行锁，MyISAM不支持行锁
• SQL语句没有使用索引
• 索引失效：比如不符合最左匹配原则、隐式类型转换
• 范围查询需要加的行锁过多，MySQL可能自动升级成表锁
• 执行DDL等修改表结构时，加表锁

9. 长事务

长事务指运行时间比较长、长时间未提交的事务，也叫大事务

9.1 出现的场景

• 在事务中进行大量数据查询
• 事务中有其他非DB的耗时操作，比如RPC、消息队列
• 事务中有大量的锁竞争

9.2 长事务的危害

• 数据库连接池被占满，应用无法获取连接资源
• 容易引发数据库死锁
• 数据库回滚时间长
• 执行时间长，容易造成主从延迟
• undo log膨胀

9.3 如何找到长事务

information_schema.INNODB_TRX表中包含了当前innodb内部正在运行的事务信息，这个表中给出了事务的开始时间。

9.4 长事务如何优化

宗旨是对事务方法进行拆分，尽量让事务变小，变快，减小事务的颗粒度。

1. 使用编程式事务手动控制事务范围，避免使用声明式事务
2. 将不需要事务管理的逻辑与事务操作分开（容易造成事务不生效的经典场景）
3. 避免一次处理太多操作、避免不必要的查询

https://www.cnblogs.com/SparkMore/p/16106378.html

https://juejin.cn/post/6844903945920315405

10. 深分页查询优化

覆盖索引 + 子查询

数据游标

11. 为什么InnoDB在RR隔离级别下不能完全解决幻读？

• 对于事务1的第一次和第二次查询，由于没有加锁，是快照读，因此事务2的插入可以正常插入。由于InnodDB的MVCC机制，使得事务一第二次查询时查出来的数据中不存在事务2插入的数据（ReadView中的不可见算法）
• 当事务1进行了update操作后，三条记录最新版本数据的隐藏字段的事务id都被更新成事务1，因此根据可见性算法事务2插入的原Bob数据对事务1可见，出现了幻读。
• 这里需要讨论的是“幻读”的定义，如果专指新插入的行，那么通过next-key lock解决了幻读问题；但如果认为幻读包含所有写操作，那可以说幻读问题没有被解决。

1. SQL语句分类

数据定义语言（DDL）

数据定义语言（Data Definition Language，DDL）是SQL语言集中负责数据结构定义与数据库对象定义的语言，包括数据库操作和表操作两类

1
2
3
4
5
6
7
8
9
10
11
12
13

-- 查看所有库
SHOW DATABASES; 
-- 查询当前数据库
SELECT DATABASE();

-- 创建数据库
CREATE DATABASE [IF NOT EXISTS] 数据库名;

-- 删除数据库
DROP DATABASE [IF EXISTS] 数据库名;

-- 使用数据库
USE DATABASE 数据库名;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

-- 查看当前库下的所有表
SHOW TABLES;
-- 创建表
CREATE TABLE 表名(字段 字段类型, 字段 字段类型, ...);

-- 查看当前表有哪些字段
DESC 表名;
-- 查看建表语句
SHOW CREATE TABLE 表名;

-- 修改表结构
ALTER TABLE 表名 ADD|MODIFY|CHANGE|DROP|RENAME TO ...;

-- 删除整张表
DROP TABLE 表名;

数据操纵语言（DML）

1
2
3
4
5
6
7
8
9
10
11
12
13

-- 插入数据
-- 插入特定列
INSERT INTO 表名 (字段名1, 字段名2, ...) VALUES (值1, 值2,...);
-- 插入所有列
INSERT INTO 表名 VALUES(值1, 值2, ...);
-- 批量插入
INSERT INTO 表名 VALUES(值1, 值2, ...), (值1, 值2, ...);

-- 更新数据 没有WHERE条件则更新整张表
UPDATE 表名 SET 字段名1=值1, 字段名2=值2, ... [WHERE 条件];

-- 删除数据
DELETE FROM 表名 [WHERE 条件]; 

数据查询语言（DQL）

1
2
3
4
5
6
7
8
9
10
11
12
13
14

SELECT
	字段列表
FROM 
	表名列表
WHERE
	条件列表
GROUP BY
	分组列表
HAVING
	分组后条件列表
ORDER BY
  排序字段列表 [ASC|DESC]
LIMIT
	起始索引, 查询记录数;

• 聚合函数COUNT、AVG、MAX、MIN、SUM等都不会统计NULL值
• 执行顺序：
  1. FROM：首先对from子句中的前两个表执行一个笛卡尔乘积，此时生成虚拟表 vt1（选择相对小的表做基础表）。
  2. ON：接下来应用on筛选器，on 中的逻辑表达式将应用到 vt1 中的各个行，筛选出满足on逻辑表达式的行，生成虚拟表 vt2
  3. JOIN：如果是outer join 那么这一步就将添加外部行，left outer jion 就把左表在第二步中过滤的添加进来，如果是right outer join 那么就将右表在第二步中过滤掉的行添加进来，这样生成虚拟表 vt3 。
  4. 如果 from 子句中的表数目多余两个表，那么就将vt3和第三个表连接从而计算笛卡尔乘积，生成虚拟表，该过程就是一个重复1-3的步骤，最终得到一个新的虚拟表 vt3。
  5. WHERE：应用where筛选器，对上一步生产的虚拟表引用where筛选器，生成虚拟表vt4。注意where与on的区别：先执行on，后执行where；on是建立关联关系在生成临时表时候执行，where是在临时表生成后对数据进行筛选的。
  6. GROUP BY：group by 子句将vt4中指定列唯一的值组合成为一组，得到虚拟表vt5，后面的所有步骤都只能得到vt5的列或者是聚合函数（count、sum、avg等）。原因在于最终的结果集中只为每个组包含一行。
  7. 聚合函数：应用avg或者sum选项，为vt5生成超组，生成vt6.
  8. HAVING：应用having筛选器，生成vt7。having筛选器是第一个也是为唯一一个应用到已分组数据的筛选器。
  9. SELECT：处理select子句。将vt7中的在select中出现的列筛选出来。生成vt8.
  10. DISTINCT：应用distinct子句，对vt8进行去重，生成vt9。
  11. ORDER BY：应用order by子句。按照order_by_condition排序vt9，此时返回的是一个游标，而不是虚拟表。
  12. LIMIT：应用limit选项。生成vt10返回结果给请求者即用户。

数据控制语言（DCL）

• 用户管理

  1. 查询用户

     1 2	USE mysql; SELECT * FROM user;

  2. 创建用户

     1	CREATE USER '用户名'@'主机名' IDENTIFIED BY '密码';

  3. 修改用户密码

     1	ALTER USER '用户名'@'主机名' WITH mysql_native_password BY '新密码';

  4. 删除用户

     1	DROP USER '用户名'@'主机名';

• 权限管理

  

事务控制语言（TCL）

1
2
3

BEGIN
COMMIT
ROLLBACK

2. MySQL内置函数

• 字符串函数

  函数	功能	示例	结果
  CONCAT(s1, s2, …sn)	将所有字符串拼接	SELECT CONCAT(“He”, “llo”);	Hello
  LOWER(str)	将字符串所有字符小写	SELECT LOWER(“Hello”);	hello
  UPPER(str)	将字符串所有字符大写	SELECT UPPER(“Hello”);	HELLO
  LPAD(str, len, pad)	左填充，用字符串pad对str的左边填充到len长度	SELECT LPAD(“01”, 10, ‘-‘);	—01
  RPAD(str, len, pad)	右填充，用字符串pad对str的右边填充到len长度	SELECT RPAD(“01”, 10, ‘-‘);	01—
  TRIM(str)	去掉字符串头部和尾部的空格	SELECT TRIM(“ Hello MySQL “);	Hello MySQL
  SUBSTRING(str, start, len)	返回字符串从start起始位置长度为len的字符串。索引值从1开始	SELECT SUBSTRING(“Hello MySQL”, 1, 5);	Hello

• 数值函数

  函数	功能	示例	结果
  CEIL(x)	向上取整	SELECT CEIL(1.3)	2
  FLOOR(x)	向下取整	SELECT FLOOR(1.9)	1
  MOD(x, y)	返回x/y的模	SELECT MOD(7, 4)	3
  RAND()	返回0~1的随机数	SELECT RAND()	/
  ROUND(x, y)	求参数x的四舍五入值，保留y位小数	SELECT ROUND(3.345, 2)	3.35

• 日期函数

  函数	功能	示例	结果
  CURDATE()	返回当前日期	SELECT CURDATE();	2025-04-06
  CURTIME()	返回当前时间	SELECT CURTIME();	16:21:12
  NOW()	返回当前日期和时间	SELECT NOW();	2025-04-06 16:22:24
  YEAR(date)	返回年份	SELECT YEAR(NOW());	2025
  MONTH(date)	返回月份	SELECT MONTH(NOW());	4
  DAY(date)	返回天份	SELECT DAY(NOW());	6
  DATE_ADD(date, INTERVAL expr type)	返回date指定时间间隔后的时间	SELECT DATE_ADD(NOW(), INTERVAL 70 DAY);	2025-06-15 16:22:58
  DATEDIFF(date1, date2)	返回两个日期之间的天数，第一个日期减去第二个日期	SELECT DATEDIFF(‘2021-10-01’, ‘2021-12-01’);	-61

• 流程控制函数

  函数	功能	示例	结果
  IF(value, t, f)	如果value为true，则返回t，否则返回f	select if(true, ‘ok’, ‘error’);	ok
  IFNULL(value1, value2)	如果value1不为空，返回value1，否则返回value2	Select ifnull(null, ‘default’)	default
  CASE WHEN [val1] THEN [res1] … ELSE [default] END	如果val1位true，返回res1，…，否则返回默认default值
  CASE [expr] WHEN [val1] THEN [res1] … ELSE [default] END	如果expr的值等于val1，返回res1，…，否则返回default默认值

3. 存储引擎

3.1 InnoDB存储引擎


3.2 了解哪些存储引擎，有什么区别


4.3 怎么选择存储引擎？

• InnoDB：如果应用对事务的完整性有比较高的要求，在并发条件下要求数据的一致性，数据操作除了插入和查询之外，还包含很多的更新、删除操作，InnoDB比较合适
• MyISAM：如果应用是以读操作和插入操作为主，只有很少的更新和删除操作，并且对事务的完整性、并发性要求不是很高，可以选择MyISAM。非核心的数据如日志、用户评论等
• Memory：将所有数据保存在内存中，访问速度快，通常用于临时表及缓存。缺陷时对表的大小有限制，太大的表无法换存在内存中，而且无法保障数据的安全性

MyISAM一般被MongoDB替代、Memory被Redis替代

4. 锁

锁的分类：

• 全局锁：对整个数据库实例加锁
• 表级锁：锁整张表
• 行级锁：锁某一具体行

4.1 全局锁

典型的使用场景时做全库的逻辑备份，对所有的表进行锁定，不能执行DML和DDL语句，从而获取一致性视图，保证数据的完整性。

1

flush tables with readlocal;


4.2 表级锁

表级锁分类：

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

• 表锁

  • 表共享读锁

  • 表独占写锁

  • lock tables xxx write/read;
    unlock tables;
    

• 元数据锁：加锁过程系统自动控制，在访问一张表时自动加上。主要作用是维护表元数据的数据一致性。

• 意向锁：当线程想要给数据表加表锁时，判断是否与当前意向锁互斥，如果互斥会被阻塞，否则加锁成功，不用再一行一行判断是否有行锁

4.3 行级锁

• 行锁（Record Lock）
  • 共享锁
  • 排他锁
  • 普通select不加锁
  • select … lock in share mode：共享锁
  • select .. for update：排他锁
• 间隙锁
• 临键锁：锁当前行以及行之前的间隙，左开右闭

4.4 select * from tb_test where id = 1 for update会加哪些锁？

元数据锁

意向排他锁

行锁

5. 日志

5.1 错误日志

记录MySQL启动和停止时，以及服务器运行过程中发生任何严重错误时的相关信息。当数据库出现任何故障导致无法正常使用时，建议首先查看此日志。


如果是homebrew安装，则存放在/opt/homebrew/var/mysql目录下


5.2 二进制日志binlog

• 记录内容：只记录DDL（数据定义语言）和DML（数据操纵语言），不包括数据查询语言（SELECT、SHOW）

• 作用：崩溃恢复、主从同步


• 文件格式binlog_format
  • statement：记录SQL语句
  • row：记录每一行变更前后的数据
  • mixed

5.3 查询日志

记录所有客户端的操作语句，包括查询语句，默认为关闭


5.4 慢查询日志

• 记录内容：记录所有执行时间超过参数long_query_time设置值并且扫描记录数不少于min_examined_row_limit的所有SQL语句的日志，默认未开启。

• 参数

  • long_query_time=2：默认为10秒，最小为0，精度可以到微秒
  • log_slow_admin_statements=1：记录执行较慢的管理语句
  • log__queries_not_using_indexes=1：记录执行较慢的未使用索引的语句

6. 高性能MySQL

6.1 主从复制

• 原理
  1. Master主库在事务提交时，会把数据变更记录在binlog文件中
  2. 从库Slave读取主库binlog，写入到自己的中继日志Relay.log中
  3. Slave重做中继日志中的事件，改变自己的数据


6.2 分库分表

• 类型

  • 垂直分库：以表为依据，根据业务将不同表拆分到不同库中

    

  • 垂直分表：以字段为依据，根据字段属性将不同字段拆分到不同表中

    

  • 水平分库：将一张表的数据拆分到不同库中

  • 水平分表：将一张表的数据拆分到不同表中

• 分库分表中间件

  • shardingJDBC：基于AOP，只用于java
  • MyCat：阿里开源

• 分片规则

  • 范围：根据指定的字段值的范围进行分片
  • 取模：根据指定的字段值和节点数量进行求模运算
  • 一致性Hash：相同的哈希因子计算值总是被划分到相同的分区表中，不会因为分区节点的增加而改变原来数据的分区位置
  • 枚举

6.3 读写分离

• 一主一从
• 双主双从

有没有压测过MySQL？

参考资料：

https://www.bilibili.com/video/BV1Kr4y1i7ru?spm_id_from=333.788.player.switch&vd_source=9fef6d5ed1ed306fd39b4f3ff38854ff&p=96