MySQL面试总结

归纳整理人： 魏姚

邮箱: qluogxbd@gmail.com

个人博客: https://www.zhihu.com/column/c_1876380493982867456

更新时间：2026.1.18

更新日志： 添加目录模式，方便查看

题库总数量： 454

目录

基础面试题

1.1.1 MySQL 安装方式有哪些，你们公司用的哪个方式，为什么？

1.1.2 关系型数据库与非关系型数据库的区别?

• 关系型数据库: 具有复杂功能性，追求数据一致性，但降低了性能和可拓展性

• 非关系数据库: 具有超高的性能(内存特性)，可拓展性，使用灵活，同时降低了数据一致性与关系型数据库互补

1.1.3 MySQL 5.6,5.7/8.0 版本安装过程有什么区别？

1. 初始化命令路径，命令区别

   • 5.6：/usr/local/mysql/scripts/mysql_install_db

   • 5.7及其以上：/usr/local/mysql/bin/mysqld

2. 不同的初始化参数

   • 5.6 无初始化参数

   • 5.7 及其以后:

     • --initialize 生成随机密码放在MySQL日志文件中 默认安装的话在 /var/log/mysql/目录中

     • --initalize-insecure 密码为空

1.1.4 MySQL 5.7,8.0 在用户管理功能上有什么区别，请举例说明

• 5.7:

  • 创建用户和授权可以用一条命令解决 (给用户授权的时候可以直接创建用户)

    grant all on *.* to oldboy@'10.0.0.%' identified by 'oldboy123';
    

  • 密码加密插件: mysql_native_password

• 8.0:

  • 创建用户时必须先创建用户再授权

  • 引入了角色机制

  • 密码加密方式 caching_sha2_password

  • 增加了可以锁定账户的功能

1.1.5 请描述 MySQL 的授权表有哪些，都有什么作用？

1. mysql.user 存储全局权限 （用户全局权限，创建用户，管理服务）

2. mysql.db 记录数据库级别权限，控制用户对特定数据库的操作权限（如 SELECT INSERT）

3. mysql.tables_priv 存储表级权限（如对某张表的增删改查权限）

4. mysql.colums_priv 记录列级权限（如仅允许用户修改某表的特定列）

5. mysql.procs_priv 管理存储过程和函数的权限（如执行或修改存储过程的权限）

6. mysql.proxies_priv 控制代理用户权限（允许用户以其他用户的身份执行操作）

7. mysql.global_grants 存储动态全局权限（如角色管理、审计相关权限）

1.1.6 简述你在工作中使用 MySQL 连接的方式？

1. 本地连接

   • 利用套接字文件连接 （客户端加载socket文件 =（路径/名称）= 服务端创建socket文件）

2. 远程连接 （利用TCP/IP协议）

   1. 利用客户端命令进行连接

      • mysql 登录访问服务端命令

      • mysqladmin登录管理服务端命令 （密码 运行状态）

      • mysqldump 登录保存服务端数据

   2. 利用客户端工具进行连接

   • MySQL workbench

   • Navicat

1.1.7 MySQL 的配置文件标签有哪些？（my.cnf）

1. 客户端标签：影响客户端连接命令，可以省略命令参数信息 （加载客户端标签信息 不用重新启动服务）

   • 全局标签：[client] 局部标签：[mysql] [mysqladmin] [mysqldump]

2. 服务端标签：影响数据库服务功能（使配置生效需要重启服务）

   • 全局标签：[server] 局部标签：[mysqld] [mysqlserver] [mysqld_safe]

1.1.8 MySQL 忘记root 用户密码怎么办？

1. 关闭数据库服务 （需提前联系通知用户和相关业务部分）

   • /etc/init.d/mysqld stop

2. 采用安全模式启动数据库 （跳过授权表,停止网络连接）

   • mysqld --skip-grant-tables --skip-networking &

3. 重置密码信息

   1. flush privileges; -- 重新将磁盘授权表加载到内存中

   2. `alter user root@'localhost' identified by '012012';

4. 重新正常启动

   1. pkill mysql

   2. /etc/init.d/mysqld start mysql -uroot -p012012

1.1.9 在公司中一般给开发，运维，管理员授予什么权限

• 针对管理员设置权限：DBA

  grant all on *.* to xiaoA@'localhost';

  如果需要给其他用户授权需要再授予一个grant option

  grant all on *.* to xiaoA@'localhost with grant option';

• 针对运维人员设置权限：SA

  grant Alter,Create,Delete,Drop,Insert,Select,Update on www.* to xiaoB@'localhost';

• 针对开发人员设置权限：DEV

  grant Delete,Insert,Select,Update on www.t1 to xiaoC@'localhost';

1.1.10 请列举 MySQL 配置文件的读取顺序？

xxxxxxxxxx
/etc/my.cnf ➔ /etc/mysql/my.cnf ➔ /usr/local/mysql/etc/my.cnf ➔ ~/.my.cnf

1.1.11 请列举 MySQL 启动和关闭方式？

• 启动

  1. 使用 systemctl

  2. 使用 /etc/init.d/mysqld

  3. 使用support-files/mysql.server

• 关闭

  1. 使用 systemctl

  2. 使用 /etc/init.d/mysqld

  3. 使用 support-files/mysql.server

  4. 使用 kill

  5. 使用 mysqladmin -u*** -p*** -S *** shutdown （推荐）

1.1.12 你们公司使用多实例环境吗？在什么地方用的？

• 业务量、并发量小的业务，且项目多，要求相对物理隔开

• 测试环境大量使用

1.1.13 如何查看数据库当前连接情况

xxxxxxxxxx
SHOW PROCESSLIST;

1.1.14 简述数据库启动不了，如何排查?

1. 错误日志排查：log_error=对应路径/主机名

2. 手工启动检查：mysqld --defaults-file=/etc/my.cnf

3. 初始化数据库（不推荐轻易操作）

1.1.15 数据库连接不上如何排查？

1. 确认网络连通性

   • 数据库端口检查 （telnet,nc,nmap）

2. 确认网络硬件设备

   • 路由器，交换机，防火墙

3. 确认服务端配置

   1. 检查是否创建了用户信息和密码

   2. 检查用户登录白名单

4. 确认客户端配置

   1. 检查输入用户信息

   2. 检查输入密码喜喜

   3. 检查密码加密方式（旧版本客户端加密使用mysql_native_password）

1.1.16 MySQL常用数据类型有哪些

• 字符类型

  1. char

  2. varchar

• 整型

  1. tinyint 微小整数 1个字节 范围(-128~127) 范围(0~255) 2的8=256

  2. smallint 小型整数 2个字节 范围(-32768~32767) 范围(0~65535) 2的16=65536

  3. mediumint 中型整数 3个字节 范围(-8388608~8388607) 范围(0~16777215) 2的24=16777216

  4. int 标准整数 4个字节 范围(-2147483648~2147483647

  5. bigint 大型整数 8个字节 范围(+-9.22*10的18次方)

• 浮点型

  1. float单精度浮点类型 可以保留的小数位最多 6

  2. double 双精度浮点类型 可以保留的小数位最多 17

  3. decimal定点数类型 可以自定义

• 日期类型

  1. date 日期类型 存储年月日

  2. time 时间类型 存储小时分钟秒

  3. datetime 日期时间类型 存储年月日 小时分钟秒 1000~9999

  4. timestamp 日期时间类型 存储年月日 小时分钟秒 1970~2037

• ENUM 枚举类型

• SET集合类型

• 。。。。。。。

1.1.17 MySQL 约束有哪些？

• 主键约束

• 唯一约束

• 非空约束

• 外键约束

1.1.18 MySQL 列的属性设置有哪些？

• default设定默认数据信息，可以实现自动填充

• auto_increment 设定数值信息自增，可以实现数值编号自增填充（一般配合主键使用）

• comment设定数据注释信息

• unsigned 设定数值信息非负，可以实现数值信息列不能出现负数信息

1.1.19 MySQL 如何设置自增列 自增列是否可以自定义起始值

• alter table test02 auto_increment=100;

• auto-increment-increment=100 此配置项影响自增的间隔数值，默认为1 2025110001

1.1.20 MySQL 自增列的范围？

• 自增列数值自增范围: 根据表字段数据类型定义 tinyint（0~255）

1.1.21 MySQL服务模式有哪些常用参数，什么时候会用到？

参数：

• only_full_group_by 禁止一行信息对应多行信息显示输出（在表进行分组处理时）

• strict_trans_tables录入的数据信息，超过数据类型的限制后，会自动录入失败

• no_zero_in_date,no_zero_date 录入的日期信息，不可能出现0000-00-00

• errror_for_division_by_zero 当出现数值运算。，不能出现除数为0

应用场景：

• 低版本数据库中 sql_mod 配置信息更简单，更不完善，当低版本数据库迁移到高版本的时候，（逻辑迁移）会出现失败迁移时，将高版本数据库服务的sql_mode功能关闭，再进行数据迁移

• 绕开sql_mod可以使用物理迁移(set global sql_mode='')

1.1.22 MySQL 查询表中数据中文字体乱码，原因可能是什么？如何修改？

原因：

• 字符集不一致导致：

  1. 操作系统级别

  2. 操作系统客户端级别

  3. MySQL 实例级别

  4. 库级别

  5. 表级别

  6. 列级别

  7. MySQL 客户端

  8. 开发端（如 Java/Python 等程序连接）

修改方法：

1. 查看字符集 SHOW VARIABLES LIKE 'character_set%';

2. 修改字符集 在 /etc/my.cnf 中修改服务端和客户端配置

1.1.23 什么是实例

• 实例 = mysqld + 线程（master thread，IO, SQL，purge）+ 内存结构 + 磁盘结构

1.1.24 简述 MySQL 程序结构？

• 客户端连接器 → MySQL 服务层 → 存储引擎层 → 文件系统层

1.1.25 简述一条 select 语句的执行过程？

• 先通过连接器校验用户身份，权限 处理客户端的请求；

• 利用分析器进行SQL语句的词法分析和语法，语义分析，构建解析树；

• 使用优化器生成多个执行计划，评估执行计划的成本，选出最优的执行计划交给执行器

• 利用执行器，通过API接口调用引擎层查询数据，返回结果集给客户端

1.1.26 请简述 MySQL 逻辑结构和宏观物理结构？

逻辑结构：

1. 实例

2. 库：库名/库属性（字符集，校对规则）

3. 表：列（列名 + 列属性）+ 行（元数据 + 数据）+ 表属性 + 表名

宏观物理结构：系统上库对应数据库库名及目录

1.1.27 请简述段、区、页的构成

• 一个段由多个区构成。

• 一个区由连续 64 个页构成，每页 16k

• 一个段约1M大小

1.1.28 你们公司用的 MySQL 版本，为什么选择？

• 版本选择：

  • 5.7.22 或 5.7.30 或 5.7.36

  • 8.0.22 使用较多，将成为主流

1.1.29 数据库文件损坏最大原因？

1. kill -9 强制关闭数据库引起数据丢失损坏

2. 突然断电

3. 磁盘坏道

1.1.30 MySQL 用户安全规范

1. 主机域范围尽量小，最好细化到单一 IP，本机用 localhost

2. 禁止使用 % 模糊匹配授权

3. 用户名应有实际意义，一目了然

4. 删除无用用户

5. 密码复杂性

6. 为每个项目设置一个对应用户，禁止使用 root 用户作为项目用户

1.1.31 授权权限 3 条红线

1. 授权一定不用 all，而是 select, insert, update, delete 权限

2. 库表不用 *.*，而用 oldboy.* 格式具体到库

3. 主机域不要用 %，而应用内网的 IP 网段，如 172.16.1.%

1.1.32 inplace 升级至 5.7、8.0，升级方式上有什么区别？8.0要注意什么 ？

5.6升级5.7

1. 本地升级 (Inplace) 必须进行业务中断 需要提前告知用户中断时间

2. 创建MySQL 5.6 版本实例 （安装部署）

3. 创建测试数据

4. 下载部署5.7数据库程序

5. 提前部署新版本5.7 数据库多实例

6. 进行原有5.6 数据库数据备份（mysqldump xbp 克隆本地）mysqldump 和 xbp 恢复速度太慢了 建议选择 克隆备份

7. 重新编写配置文件，实现数据库升级（挂库升级 让）basedir5.6 和basedir5.7 数据目录上进行关联

8. 进入mysql.5.7 的配置文件 修改 datadir 修改启动端口为56实例的端口

9. 以安全模式启动数据库服务（5.7）mysqld --default-files=/data/3357/my.cnf --skip-grant-tables --skip-networking 避免无法正常启动

10. 启动成功时会出现报错（表结构错误）但是不用管，可以正常进入数据库

11. usr/local/mysq57/bin/mysql_upgrade -S /tmp/mysql3357.sock /tmp/mysql3357.sock --force

12. 重新启动mysql5.7

13. 进行升级后数据库备份 （mysqldump）

异地升级（Merging）不停止业务进行数据库升级

1. 需要在新的数据库节点安装mysql5.6 程序 （实现主从同步）（ 5.6 直接和5.7 简历主从会出现数据无法正常加载）

2. 在从节点安装部署新版本5.7 数据库服务

3. 在从节点上实现 5.6 到5.7 的挂库升级

4. 重新启动5.7 数据库程序，和主库进行数据同步（此时再同步就是业务库信息）

5. 利用MHA高可用服务，实现手工切换主节点

6. 可以再将其他节点依次进行升级

5.7升级8.0

• mysql 8.0 以安全模式启动的时候会自动的去调整表结构 其余步骤与上面一样

注意事项

5.7.30 -- 8.0.36

• 数据库服务版本升级时，只支持在GA（General Availability）版本之间进行升级

• 数据库服务版本升级时，支持从数据库5.6到5.7再到8.0，跨版本升级，但是需要先将5.6升级到最新小版本，在进行跨版本升级

• 数据库服务版本升级时，需要提前考虑好版本回退的方案，最好升级前做好数据备份（特别是向8.0版本升级）

• 数据库服务版本升级时，制定的升级方案和升级步骤，需要尽可能降低数据库服务停机的时间 数据库服务官方参考资料：https://dev.mysql.com/doc/refman/8.0/en/upgrade-paths.html

1.1.33 MySQL 中 TEXT 类型最大可以存储多长的文本

• TINYTEXT: 最大存储 255 字节（约 255 个字符）

• TEXT: 最大存储 65,535 字节（约 64 KB）

• MEDIUMTEXT: 最大存储 16,777,215 字节（约 16 MB）

• LONGTEXT: 最大存储 4,294,967,295 字节（约 4 GB）

1.1.34 MySQL 中 AUTO_INCREMENT 列达到最大值时会发生什么？

• 当 MySQL 中 AUTO_INCREMENT 列达到其数据类型的最大值时，插入操作将失败，并返回一个错误

1.1.35 MySQL 中 EXISTS 和 IN 的区别是什么？

在执行机制和性能上有区别

• EXISTS是检查子查询是否有结果返回，返回布尔值。而IN则是判断某个值是否在子查询的结果集中。IN的执行过程是先执行子查询，得到一个结果集，然后主查询再和这个结果集做匹配。而EXISTS则是主查询的每一行都去执行子查询，直到找到匹配项就停止，也就是所谓的“短路”机制

• 性能方面,当子查询结果集大时，用EXISTS更好，因为EXISTS一旦找到匹配就会停止，而IN需要处理整个结果集。相反，当子查询结果集小，主表大时，IN可能更高效，因为可以利用索引。比如，如果子查询结果集小，IN可以快速匹配，而EXISTS需要对主表每一行都执行子查询，可能更慢

• IN在内外表都可能用到索引，而EXISTS主要在子查询（内表）使用索引。比如，当子查询表有索引时，EXISTS的效率会更高，因为它可以快速定位到匹配行

• NOT EXISTS通常比NOT IN效率高，因为NOT IN会对内外表都做全表扫描，而NOT EXISTS可以利用索引，特别是在子查询结果集大的时候

1.1.36 MySQL数据库的优缺点?

优点：

• 体积小，开源，且支持多种操作系统，大型数据库，具有稳定性，同时具有高度多样性（命令行客户端操作，网页浏览器，及各种程序语言，C+，Java）可用于Windows,Linux

缺点：

• 不支持热备，数据读写比经过sql解析，大量数据，高并发下读写能力不足

• 数据读写时或修改数据结果时需要加锁，影响并发操作

1.1.37 什么是隐式转换?

• 隐式转换也叫自动类型转换，指不需要书写代码，由系统自动完成的类型转换

1.1.38 MySQL 怎么查看系统资源（内存，CPU）?

• 在操作系统层面，用 free 命令查看系统内存资源使用情况，用 top-c 命令查看进程使用内存占用情况

1.1.39 数据库启动时间与哪些因素有关？

• 硬件因素: CPU,内存，磁盘IO，网络

• 操作系统: Linux

• 数据库引擎: innodb 文件系统采用XFS

• 数据库配置参数:

1.1.40 有一个 8核心 16g 服务器，活动会话数达到多少个会不正常？

• 正常：3，4，5 个。满配 8 个。不正常：1 倍或 1.5 倍

1.1.41 MySQL 如何管理用户连接的？

查看用户连接

• show processlist # 查看系统连接用户信息

• max_connections # 最大连接数，如果达到最大连接时，会给管理员一个额外名额去连接

  这里的管理是指root 或授权时赋予 all 的用户

1.1.42 MySQL 5.7 和 MySQL 8.0 有哪些区别？

• 字符集

  • 8.0 默认使用 uft-8

  • 5.7 默认使用 lantin

• 用户管理

  • 8.0加入了角色机制

  • 8.0 创建用户时必须先创建用户再授权 5.7 可以授权的时候直接创建用户

  • 增加了可以锁定账户的功能

  • 移除PASSWORD()函数。这就意味着无法通过“SET PASSWORD..=PASSWORD(auth_string)“命令修改用秦户密码

• 密码插件

  • 5.7 采用了 mysql_native_password 加密

  • 8.0 采用了 caching_sha2_password 加密方式

• 索引机制

  • 隐藏索引：8.0 开始支持隐藏索引，不可见索引不会被优化器使用，但仍需维护

  • 降序索引：8.0 开始真正支持降序索引

  • 隐式索引：不再对 GROUP BY 操作进行隐式排序

  • 函数索引：支持在索引中使用函数计算后的值

• 缓存

  • MySQL 8.0 不再支持查询缓存，因为之前的查询缓存命中率太低了（必须SQL语句一模一样）

• 主从信息表

  • mysql 8.0 中取消了master.info 文件 改用 slave_master_info表存储主库信息

  • 通过参数可以修改库信息存放方式 master_info_repository table/file

• 表空间文件

  • mysql 8.0 使用了 idb 存放表空间和表结构

  • mysql 5.7 使用了idb 存放数据 .frm 存放表结构

• 双写文件缓冲区

  • mysql 8.0 将 double write buffer 放到了独立的空间

  • mysql 5.0 将 double write buffer 放到了系统表空间

• 在MySQL 5.7版本中，只能通过重启以实现主节点的自动切换，不能手动切换

• MySQL 8.0 自增主键的持久化

• MySQL 8.0 对redo log 进行大改 可以并行写入redo log

• MySQL 8.0 从库并行回放加入了 Writeset

• MySQL 8.0 undo log 可以在线调整 undo log 数量

• MySQL 8.0 默认的内存临时表由MEMORY引擎更改为TempTable引擎，相比于前者，后者支持以变长方式存储VARCHAR，VARBINARY等变长字段

• MySQL 8.0 新增 LOCKINSTANCE FOR BACKUP

• MySQL 8.0 新增 performance_schema.data_locks 和 data_lock_waits 表

1.1.43 MySQL 5.6 和MySQL 5.7 有什么区别？

• MySQL 从 5.6 版本开始支持多线程复制 BinLog

  在 MySQL 5.6 中引入了 BLGC (Binary Log Group Commit)，这有助于提高并发提交性能

  到 MySQL 5.7，进一步增强了多线程复制的功能，使得可以在 Slave 上实现真正的多线程并发复制，不再局限于同一 Schema 下的不同表之间的并行处理

1.1.44 MySQL 从5.6 升级到5.7 是不是大版本升级？

• 是大版本升级 从5.7 以后 MySQL大版本升级版本号为 8 9 10 依次命名

1.1.45 MySQL 数据库升级，你常用的是哪个数据库版本的升级？

• mysql 5.7 升级 mysql8.0.22 现在的话（2025）推荐 升级到8.0.35 因为bug少更加稳定

1.1.46 idb 和 frm 文件是用来干什么？

• idb 表空间文件，用于存放数据

• frm 文件是用于存储表数据和元数据的关键文件

1.1.47 ibdata 和 ib_log_file 放的什么?

ibdata

• InnoDB 表的元数据：这是指关于表结构的信息。

• Undo Logs：用于回滚未提交的事务以保持数据库的一致性。

• Change Buffer：优化非唯一二级索引更新操作的一种机制。

• DoubleWrite Buffer：一种提高崩溃恢复性能的安全措施，在写入主数据之前先将其复制到一个临时区域

ib_log_file 是 redo 日志文件的一部分

1.1.48 你们一台物理机是部署一个MySQL还是多套?因为是多实例的，有遇到过实例之间的影响案例吗?

1.1.49 哪些不是活跃连接?用什么语句看?

• show processlist

• 状态是sleep 的就是 不活跃的

1.1.50 MySQL 碎片信息怎么看？

• 通过 information_schema.TABLES 表查看 data free 字段

1.1.51 mysql结构体系和工作原理？

1.1.52 mysql 8.0 新特性

SQL

1.2.1 请简述select语句的各个子句的执行顺序？

• From→on→join→where→group by→having+聚合函数→select→order by→limit

1.2.2 请列举 SQL 语句的种类和代表命令？

• DDL:数据定义语言，对元数据修改，create创建，alter修改，drop删除

• DCL:数据控制语言，权限，数据提交grant用户授权，revoke权限回收，commit提交，rollbake回滚

• DML:数据操作语言，表内容处理update,delete,insert

• DQL:数据查询语言，表内容查询select

1.2.3 请简述SQL_MODE的作用？ONLY_FULL_GROUP_BY是干什么用的？

• Sql_mode是对SQL语法控制和校验的一套规则 用于保证数据录入的合理性

• Sql_mode对group by的控制：only_full_group_by是控制select语句中带group by子句的检测，要求select后的选择列，要么是group by的条件，要么是通过聚合函数处理，否则语句就会报错

1.2.4 SQL_MODE 的常用参数，什么使用应用这些参数？

参数

1. only_full_group_by 禁止一行信息对于多行信息显示输出（在表进行分组处理时）

2. strict_trans_tables 录入的数据信息，超过数据类型的限制后，会自动录入失败

3. no_zero_indate,no_zero_date 录入的日期，不可能出现 0000-00-00

4. error_for_division_by_zero 当出现数值运算的，不能出现除数为0

应用

• 正常不做更变，常用于数据库版本升级（低版本向高版本升级，会临时清空 sql_mod 变量）

1.2.5 请简述MySQL utf8和utf8mb4区别？

• utf8mb4字符范围大于utf8,utf8实际是utf8mb3,是utf8mb4的子集

• utf8一个汉字占3个字节，utf8mb4一个汉字占4个字节

• 8.0默认字符集是utf8mb4，8.0之前默认latin1,但常修改为utf8字符集

1.2.6 请简述tinyint、int、bigint 如何计算的存储位数？

1.2.7 请简述 CHAR(10) 和 VARCHAR(10) 区别，生产如何选择？并阐述为什么？

• 不同点：

  • CHAR(10)：定长，浪费存储空间，但作为条件列查询会更快。

  • VARCHAR(10)：变长，节省空间。

• 相同点：

  • 都是字符串类型

• 生产选择：

  • 定长列选择 CHAR。

  • 变长列选择 VARCHAR。

1.2.8 请简述 DATETIME 和 TIMESTAMP 区别？

• 存储空间：

  • TIMESTAMP：占用 4 个字节。

  • DATETIME：占用 8 个字节。

• 存储范围：

  • DATETIME：范围更大。

  • TIMESTAMP：有时间限制。

• 时区：

  • TIMESTAMP：受时区变化影响。

  • DATETIME：不受时区变化影响。

1.2.9 什么是数据库的视图？

• 数据库的视图是一种虚拟表，其内容由查询定义。视图并不是实际存储在数据库中的数据集合，而是基于一个或多个现有表（称为基表）构建出来的动态结果集。每次使用视图时，都会执行定义视图的那个查询语句来获取最新的数据。

1.2.10 请简述你们数据库开发过程，选择数据类型的规范是什么？

• 数字类型：

  • 分类：整数、浮点数（小数放大 n 倍存储）。

• 字符串类型：

  • CHAR：固定长度。

  • VARCHAR：变长。

• 时间类型：

  • DATETIME

  • TIMESTAMP

• 选择规范：

  • 变长列选择 VARCHAR

  • 定长列选择 CHAR

  • 不在数据库中存储图片等二进制数据

  • 将字符转化为数字存储（例如：IP 存储使用 INT 无符号存储）

  • 1.2.11 请列举你了解的数据库的约束有哪些？他们的特点是什么？

1. PRIMARY KEY (PK)：

   • 设置在主键列，非空且唯一，用于必填且不能重复。

2. NOT NULL：

   • 表示列的内容是否非空，空列不利于数据库优化。

3. UNIQUE KEY (UK)：

   • 表示列的内容唯一（例如：手机号）。

4. FOREIGN KEY (FK)：

   • 表示表的外键，用于多个表之间的关联。

5. CHARSET_NAME：

   • 指定表的字符集，默认是 utf8mb4。

1.2.12 请简述你们公司在 Schema 设计过程中有哪些开发规范？

库的 DDL 规范：

1. 禁止线上业务系统出现 DROP 操作。

2. 显示设置字符集。

3. 库名不能大写字母，不能是关键字，不能以数字开头，一般与业务有关。

表的 DDL 操作规范：

1. 列名要与业务有关。

2. 列的数据类型讲究：完整、简洁、合适、精度不高浮点数，n 放大 n 倍。

3. 每列要有注释。

4. 更改数据库需要在数据库低谷时间点进行。如果紧急，使用 pt-osc 或 gh-ost

1.2.13 请简述 DROP TABLE、TRUNCATE TABLE、DELETE FROM TABLE 的区别？

• 从性能来看，Drop>Truncate>Delete

• Delete

本质上这个删除其实就是给数据行打个标记，并不实时删除，因此delete之后，空间的大小不会变化。

而且delete操作会生成binlog、redolog 和 undolog，所以如果删除全表使用delete的话，性能会比较差! 但是它可以回滚.

• Drop

在InnoDB中，每张表数据内容和索引都存储在一个以,ibd 后缀的文件中，drop 就是直接把这个文件给删除了；

还有一个.frm后缀的文件也会被删除，这个文件包含表的元数据和结构定义。

文件都删了，所以这个操作无法回滚，表空间会被回收，但是如果表存在系统共享表空间，则不会回收空间。

默认创建的表会有独立表空间，把 innodb_file_per_table的值改为 OFF 后，就会被放到共享表空间中，即统一的ibdata1文件中。

• Truncate

Truncate会对整张表的数据进行删除，且不会记录回滚等日志，所以它无法被回滚。

并且主键

1.2.14 请简述如何利用 UPDATE 替换 DELETE 语句实现伪删除？

1. 添加 state 状态字段，默认为 1。

2. 查询数据时使用：

   xxxxxxxxxx
   SELECT * FROM stu WHERE state = 1;
   

3. 伪删除，将要删除的行 state 改为 0：

   xxxxxxxxxx
   UPDATE stu SET state = 0 WHERE id = 4;
   

4. 查询时实际上并没有删除：

   xxxxxxxxxx
   SELECT * FROM stu;
   

1.2.15 如果要你规划一个 10 亿的大表，你有什么好的方案？

• 考虑索引应用和存储问题：

  1. 分区表

  2. 归档表

  3. 分布式架构

1.2.16 如果这张10亿单表已经存在了，想要删除1000W数据如何处理？

1.2.17 生产中使用过分区表吗？你们使用的是什么分表策略？分区表有什么优势和劣势?

• 使用过MyCAT做分区分表

• 可以解决单库单表，在数据量大和高并发请求时带来的性能问题和扩展问题

•  

1.2.18 请简述 GROUP BY 语句的执行原理?

• 取需要聚合的列及分组列，然后按分组列值进行排序去重，最后用聚合函数对要聚合的列进行聚合

1.2.19 WHERE 和 HAVING 语句的区别?

• WHERE：

  • 分组前的条件过滤。

  • 不能使用聚合函数。

• HAVING：

  • GROUP BY 分组后的条件过滤。

  • 可以使用聚合函数。

1.2.20 生产中进行数据库资产统计，都统计什么？如何统计？

1. 统计数据库的库表等元数据信息：

   • 使用 information_schema.tables 和 information_schema.columns

2. 业务上：

   • 定时分析 binlog

1.2.21 请介绍你常用的聚合函数及其作用

• 聚合函数：

  • COUNT()：返回指定组中数据的量，括号内可加列名

  • SUM()：返回指定组中数据之和，只能用于数字列，空值被忽略

  • AVG()：返回指定组中的平均值，空值被忽略

  • MAX()：返回指定数据的最大值

  • MIN()：返回指定数据的最小值

  • GROUP_CONCAT()：返回指定的数据，按逗号分隔为一行

1.2.22 简述多表连接的方式

• 内连接：

  • INNER JOIN

• 左外连接：

  • LEFT JOIN

• 右外连接：

  • RIGHT JOIN

• 全外连接：

  • FULL JOIN（MySQL 不支持，可用 UNION ALL 连接左连接和右连接来实现）

1.2.25 什么是笛卡尔乘积?

• 每张表的一行与另一张表的每一行做关联，逐行对比

1.2.26 你们公司 Online DDL 如何处理的？

1. 评估需求:

   • 在执行任何Online DDL之前，首先评估DDL操作的必要性，确保它对业务的正面影响超过可能带来的风险。

2. 选择合适的工具:

   • 利用MySQL内置的Online DDL功能，如INPLACE和COPY算法，或者第三方工具如pt-osc、gh-ost或NineData等，这些工具提供了更高级的在线变更能力，尤其是对于不支持原生Online DDL的操作。

3. 测试与验证:

   • 在生产环境部署前，先在测试环境中模拟相同的DDL操作，确保不会对现有数据造成意外影响，并验证性能影响。

4. 最小化影响:

   • 选择在业务低峰期执行DDL，减少对用户的影响。

5. 监控与备份:

   • 在执行前确保有完整的数据库备份，同时开启详细的监控，以便在操作过程中或之后快速响应任何异常。

6. 使用自适应工具:

   • 如NineData SQL开发专业版和企业版，它们提供了自适应Online DDL能力，自动选择最适合当前操作的执行方法，减少人工判断的复杂度。

7. 实施Rollback计划:

   • 准备好回滚策略，一旦出现错误，能够迅速恢复到操作前的状态。

8. 并发控制:

   • 确保在执行DDL时，通过锁定机制或工具的内置机制来管理并发，避免数据不一致。

9. 文档与沟通:

   • 记录整个过程，包括决策依据、执行步骤和结果，同时与团队成员保持沟通，确保每个人都了解变更的细节和时间表。

10. 持续学习与优化:

    • 根据每次操作的经验，不断调整策略，优化未来Online DDL的执行流程

1.2.27 5.6、5.7、8.0 在 Online DDL 的改变？

MySQL 5.6

• 初步支持: 引入了在线DDL的概念，允许某些类型的ALTER TABLE操作在不完全阻塞DML操作的情况下执行。这主要通过INPLACE算法实现，但其能力有限，对于一些操作，如添加索引，仍然可能导致较长时间的锁表。

• 并发性提升: 开始支持在DDL过程中进行一定程度的并发DML，但具体支持的操作类型有限。

MySQL 5.7

• 更多操作支持: 在5.6的基础上，增加了对更多类型DDL操作的支持，比如重命名索引、调整数值类型大小和VARCHAR长度的在线增大等。

• 性能优化: 对Online DDL的性能进行了优化，减少了数据拷贝的需求，提高了操作效率，特别是在处理大型表时。

• 稳定性增强: 提高了在线DDL的稳定性和可靠性，减少了操作失败的风险。

MySQL 8.0

• INSTANT算法: 引入了革命性的INSTANT算法，允许在表的最后添加新列时，仅修改元数据而不涉及数据移动，这几乎不影响DML操作，且执行速度极快。

• 原子DDL: 实现了原子性DDL操作，确保DDL要么完全成功，要么完全回滚，提高了数据的一致性。

• 更灵活的并发控制: 提供了更细粒度的LOCK选项，如LOCK=NONE允许在DDL执行期间进行查询和DML操作，进一步减少了对业务的影响。

• 改进的算法选择: 默认算法更加智能，能够根据操作自动选择最合适的执行方式（COPY, INPLACE, 或 INSTANT）。

• 增强的元数据管理: 改进了数据字典的处理，使得某些DDL操作更加高效，减少了系统锁定时间。

1.2.28 简述 pt-osc 或者 gh-ost 第三方工具在处理 DDL 时的原理？

pt-online-schema-change (pt-osc)

原理步骤

1. 创建影子表:

   • 创建一个与原始表结构相同的影子表（例如_original_table_new）

2. 应用DDL操作:

   • 在影子表上执行指定的DDL操作（如添加、修改或删除字段）

3. 创建触发器:

   • 在原始表上创建三个触发器（分别用于INSERT、UPDATE和DELETE操作），将这些操作的应用也同步到影子表中。这样可以保证在数据迁移的过程中，对原始表的所有DML操作都被正确反映到影子表中。

xxxxxxxxxx
CREATE TRIGGER `pt_osc_schema_original_table_del` AFTER DELETE ON `schema`.`original_table` FOR EACH ROW ...
CREATE TRIGGER `pt_osc_schema_original_table_upd` AFTER UPDATE ON `schema`.`original_table` FOR EACH ROW ...
CREATE TRIGGER `pt_osc_schema_original_table_ins` AFTER INSERT ON `schema`.`original_table` FOR EACH ROW ...

1. 全量数据复制:

   • 执行批量COPY操作，将原始表中的数据逐步复制到影子表中。这个过程可能涉及分批次读取和写入，以减轻对数据库的压力。

xxxxxxxxxx
INSERT LOW_PRIORITY IGNORE INTO `_original_table_new` (...) SELECT ... FROM `original_table`;

1. Cut-over 切换:

   • 最终阶段，当所有数据都已成功复制并且触发器捕获了所有增量变化后，通过原子性操作交换两张表的角色。具体做法是在极短的时间窗口内禁用触发器并对原始表加锁，然后重命名原始表为备份名称（例如 _original_table_old），并将影子表重命名为原始表的名称。

xxxxxxxxxx
 ALTER TABLE original_table RENAME TO _original_table_old;
 ALTER TABLE _original_table_new RENAME TO original_table;

1. 清理工作:

   • 移除不再需要的临时对象，包括触发器、备份表和其他辅助表。

xxxxxxxxxx
DROP TRIGGER IF EXISTS pt_osc_schema_original_table_del;
DROP TRIGGER IF EXISTS pt_osc_schema_original_table_upd;
DROP TRIGGER IF EXISTS pt_osc_schema_original_table_ins;
DROP TABLE IF EXISTS _original_table_old;

特点

• 最小化停机时间: 由于大部分操作都在后台异步进行，因此不会严重影响正在运行的服务。

• 兼容性强: 支持大多数MySQL版本，并能适应各种类型的DDL操作。

• 易于使用: 提供详细的文档和支持丰富的命令行参数，便于配置和调试。

gh-ost

原理步骤

1. 创建影子表:

   • 创建一个与原始表结构相同的影子表（例如_original_table_ghost）。

2. 应用DDL操作:

   • 在影子表上执行指定的DDL操作。

3. BinLog Streaming:

   • 设置一个二进制日志流监听器（类似于从库的行为），实时读取并解析原始表上的binlog事件，将这些变更应用到影子表中。这种方式消除了对触发器的需求，减少了潜在的性能瓶颈。

xxxxxxxxxx
 # 内部实现类似以下伪代码
     binlog_streamer := NewStreamer(original_table)
     for event in binlog_streamer.ReadEvents() {
         ApplyEventToGhost(event, shadow_table)
     }

1. 全量数据复制:

   • 使用高效的算法将原始表中的全部数据复制到影子表中。这个过程尽量减少对数据库的影响，确保尽可能小的停机时间。

xxxxxxxxxx
INSERT /* low_priority */ INTO `_original_table_ghost` (...) SELECT ... FROM `original_table`;

1. Cut-over 切换:

   • 在完成数据复制并通过一系列检查确认一切正常后，执行最终的切换单元操作。这部分操作非常迅速且几乎不影响正在进行的其他活动。

​x
      -- 锁定原始表防止新数据进入
     FLUSH TABLES WITH READ LOCK;

     -- 记录最后一个binlog位置
     last_binlog_pos := GetLastAppliedPosition();

     -- 解锁表允许继续写入
     UNLOCK TABLES;

     -- 继续应用剩余的日志事件至shadow_table
     while current_position < last_binlog_pos {
         ApplyNextEvent();
     }
     -- 原子性重命名表
     RENAME TABLE original_table TO _original_table_old,
                  _original_table_ghost TO original_table;

1. 清理工作:

   • 清理不再需要的对象，包括备份表和其他中间产物。

xxxxxxxxxx
DROP TABLE IF EXISTS _original_table_old;
STOP SLAVE; -- 如果设置了slave线程

特点

• 零停机时间: 整个过程几乎没有停机时间，适用于要求极高可用性的生产环境

• 高性能: 通过利用binlog而不是触发器来跟踪数据的变化，提高了系统的整体性能

• 灵活扩展: 易于与其他自动化工具和服务集成，支持分布式架构下的部署

• 社区活跃: 来自GitHub的强大社区提供了持续的支持和技术改进

1.2.29 为什么在MySQL 中不推荐使用多表 Join ?

MySQL 不推荐使用多表 JOIN 的核心原因可归结为 性能、扩展性、维护性 三大问题，具体分析如下：

一、性能瓶颈

1. 查询复杂度激增

• 指数级计算量：多表 JOIN 的查询复杂度随表数量增加呈指数级上升（如 3 表 JOIN 可能涉及百万级行匹配）

• 磁盘 I/O 压力：若关联字段无索引或数据量大，需频繁全表扫描，导致磁盘 I/O 激增（参考案例：无索引时 1 亿数据查询超时）

• 内存消耗高：临时表存储中间结果，可能触发内存交换（如 Using temporary; Using filesort），降低响应速度

2. 锁竞争与并发限制

• 行锁/表锁冲突：高并发场景下，JOIN 可能因锁等待导致吞吐量下降（如事务隔离级别为 REPEATABLE READ 时的间隙锁竞争）。

• 资源争抢：数据库 CPU、内存资源被复杂查询独占，影响其他业务操作。

3. 优化器局限性

• 执行计划不稳定：MySQL 优化器可能因统计信息不准确或索引变化选择低效执行路径（如误选 Nested-Loop Join 而非 Hash Join）。

• 索引设计难度：需为多表关联字段分别建索引，维护成本高且易遗漏。

二、扩展性缺陷

1. 分库分表困境

• 跨库 JOIN 失效：分布式场景下，数据分散在不同节点，无法直接关联（如分库分表后 user 表和 order 表不在同一物理库）。

• 中间件支持不足：主流分库中间件（如 ShardingSphere）对跨库 JOIN 支持有限，通常需业务层手动分片聚合。

2. 水平扩容困难

• 数据分布不均：JOIN 依赖数据局部性，扩容后关联查询可能跨多节点，网络延迟成为瓶颈（如电商订单与用户信息分散存储）。

三、维护成本高

1. 耦合性强

• 业务逻辑侵入：JOIN 条件与表结构深度绑定，表结构变更（如字段类型修改）需同步调整所有关联查询，易引发错误。

• 可复用性差：复杂 JOIN SQL 难以复用，不同业务需重复编写相似逻辑（如订单关联用户、商品、物流等多表查询）。

2. 缓存利用率低

• 查询缓存失效：任意关联表数据变更均导致缓存失效（如 user 表更新，user JOIN order 的缓存结果需清除）。

• 部分缓存不可行：无法单独缓存单表数据，需全量缓存关联结果，内存占用高。

1.2.30 MySQL中count(*) count(1) count (字段名) 的区别是什么？

1.2.31 MySQL 中 int(11) 的 11 表示什么？

• MySQL 中 int(11) 的 11 表示 显示宽度（Display Width），其作用仅限于 控制数值的显示格式，与存储范围、存储字节数无关

1.2.32 MySQL 中 varchar 和 char 有什么区别？

存储方式

• CHAR(M) 固定长度，存储时填充空格至长度 M，检索时自动删除尾部空格

• VARCHAR(M)可变长度，存储实际字符+1~2字节长度标记，保留尾部空格

最大长度

• CHAR(M) 255 字符（与字符集无关）

• VARCHAR(M) 65535 字节（字符数受字符集影响，如 UTF8 最多 21844 字符）

存储空间

• CHAR(M) 占用空间大小固定有空间浪费

• VARCHAR(M) 实际存储的大小，空间浪费小

性能对比

• CHAR(M) 读/写更快（无需计算长度，适合索引查询）

• VARCHAR(M) 读/写略慢（需解析长度标记，碎片化存储可能增加 I/O）

本质区别

• varchar 在行格式中 使用一个可变长字段记录实际长度

使用场景

• CHAR(M) 固定长度字段,如手机号、身份证号, 数据量小

• VARCHAR(M) 长度波动较大的字段（如地址、描述、评论）数据量大

排序性能影响

• 当使用varchar 进行排序的时候，带来一个问题，mysql 加载排序的字段时，sort_buffer 是按照 varchar(n) 里面的 n 进行分配空间的，这样就会导致有可能因为varchar 字段设置长度过长，而选择了了双路排序，这样的话就需要回表，带来性能损耗，如果是单路排序的话就你减少IO次数，提升性能

1.2.33 MySQL 中如何进行 SQL 调优？

平时进行SQL调优，主要是通过观察SQL，然后利用explain分析查询语句的执行计划，识别性能瓶颈，优化查询语句：

• 合理设计索引，利用联合索引进行覆盖索引的优化，避免回表的发生，减少查询过程的随机I/O消耗；

• 避免SELECT * ，只查询必要的字段

• 避免在SQL中进行函数计算等操作，使得无法命中索引

• 避免使用like %，导致全表扫描

• 注意联合索引需要满足最左匹配原则

• 不要对无索引字段进行排序操作

• 连表查询需要注意不同字段的字符集是否一致，否则也会导致全表扫描

• 还可以利用缓存来优化，一些变化少或者访问频繁的数据设置到缓存中，减轻数据库的压力，提升查询的效率；

• 还可以通过业务优化，尽量少展示一些不必要的字段，减少多表查询的情况，将列表查询替换成分页分批查询；

1.2.34 select *和 select 所有字段区别?

• SELECT *，需要数据库先 Query Table Metadata For Columns，一定程度上为数据库增加了负担 但是实际上，两者效率差别不大

1.2.35 select *一定会全表遍历吗?

本质是索引失效问题* 可以回答索引失效的点

• SELECT * 不一定总是导致全表遍历 具体行为依赖于表上的索引和查询条件

• 合理的索引设计 可以显著提高查询效率，避免不必要的全表扫描

• 使用 EXPLAIN 工具 可以帮助您诊断和优化查询性能

1.2.36 连表查询怎么看哪个是驱动表，哪个是被驱动表?

• 驱动表（Driving Table）：这是查询过程中首先被访问的表。它的数据会被用来与其他表进行比较和连接。

• 被驱动表（Driven Table）：这是根据驱动表的数据来进行查找和匹配的表。只有当驱动表中的某一行满足连接条件时，才会去被驱动表中查找相应的行

• 使用 EXPLAIN 语句：可以通过 table 和 ID字段的顺序知道 哪个是驱动表，哪个是被驱动表

• 默认情况会选择小表作为驱动表，大表作为被驱动表

• 可以利用外连接设置，影响驱动表和被驱动

• 当连接查询有 where 条件时，带 where 条件的表是驱动表，否则是被驱动表

观察 SQL 语句

• LEFT JOIN / RIGHT JOIN: 默认情况下，左边的表是驱动表，右边的表是被驱动表

• 如果有 WHERE 条件限制了某些表的行为，则按照 WHERE 条件来决定驱动表

• INNER JOIN: 如果没有 WHERE 条件，MySQL 会选择较小的表作为驱动表。如果有 WHERE 条件，通常会优先选择有 WHERE 条件的表作为驱动表

• 使用 STRAIGHT_JOIN 强制指定驱动表 有时自动选择的驱动表并不是最优的，这时可以使用 STRAIGHT_JOIN来强制指定驱动表

1.2.37 嵌套连结和hash join原理 ？

1.2.38 update一条语句的流程？

update t1 set name=xiaoB where 条件;

1. 调取数据过程 ( select name from t1 where 条件 )

   • 经过数据库server层进行处理，获取数据存储位置点（连接器 分析器 优化器 执行器）；

   • 经过数据库engine层进行处理，会加载索引页信息（辅助索引-回表过程-聚簇索引）

   • 经过异常数据库服务层和引擎层处理，会将磁盘中的数据加载到内存区域（buffer pool）

2. 修改数据过程（存储过程）

   • 创建新信息事务信息，会将内存中的数据页进行修改；

   • 会将数据页修改前的信息，保存到 undo日志中，会将数据页修改后的信息，保存到redo日志中；（只是完成事务第一阶段操作 prepar ）

   • 会将修改数据SQL语句信息保存到 binlog文件中，确认事务完成提交（完成事务第二个阶段操作 binlog redo-commit）

   • 最后会将 buffer pool脏页数据信息进行落盘操作

     • 会先将数据页16kb信息保存到双写缓冲区，利用双写缓冲区将信息保存到双写文件中；

     • 会再将数据页16KB信息保存到磁盘文件中（t1.ibd）

索引

1.3.1 请列举 MySQL 索引的类型

依据功能分类:

1. 隐藏索引（8.0 开始支持）隐藏索引不会被优化器使用。

2. 降序索引（8.0 开始支持）

3. 普通索引

4. 唯一索引

5. 主键索引

6. 全文索引

7. 空间索引

8. 联合索引

从InnoDB B+Tree索引树角度看：

• 聚簇索引（`clustered index）

• 辅助索引（`non-clustered index）

从数据结构角度看：

• B+TREE索引

• 哈希索引

• 倒排序索引（Full-Text）

• R-Tree索引；(多维空间树)

功能应用

• B+树索引: 通过树形结构存储数据，适用于范围査询(如 BETEEN)和精确査询(如=)，支持有序数据的快速査找、排序和聚合操作 是MySQL 默认的索引类型，常用于InnoDB 和 MyISAM 引擎

• 哈希索引: 基于哈希表的结构，适用于等值査询(如 =)，查询速度非常快，但不支持范围査询(如>、<)哈希索引不存储数据的顺序常用于 Memory 引擎

• 倒排索引(Full-Text):
  用于全文搜索，将全文分词，通过存储词与文档的映射，支持模糊匹配和关键字搜索 特别适合用于大文本字段，如TEXT类型的列，用于查找包含特定词语的记录

• R-树索引: 专为多维空间数据(如地理坐标)设计，适用于空间査询(例如，计算地理位置的最近距离、区域查询等)。 常用于存储和査询地理信息系统(GIS)中的空间数据

   

• 聚簇索引: InnoDB 中主键索引就是聚簇索引。它基于主键排序存储。之所以叫聚簇索引是因为索引的叶子节点存储完整数据行数据

• 非聚簇索引: 指的是 InnoDB 中非主键索引的索引，之所以称之为非聚簇是因为这个索引的叶子节点仅保存索引字段和主键的值 如果要查询完整的数据行中的数据，需要再从聚簇索引即主键索引中通过主键查询，一个表可以有多个非聚簇索引

   

• 普通索引: 一般指非主键索引且非唯一索引

• 主键索引: 表中的每一行数据都有唯一的主键。每个表只能有一个主键索引，且主键值不能为 NULL。InnoD8 中主键索引是聚簇索引结构实现的

• 联合索引: 由多个列组成的索引，适用于多列的查询条件，能够提高包含多个条件的查询的性能。联合索引中的列是按照指定顺序排列的

• 唯一索引: 保证索引列中的值是唯一的，可以有效防止重复数据的插入。唯一索引允许 NULL值，但一个列中可以有多个 NULL

   

1.3.2 MySQL 索引算法演变：二叉树，二叉平衡树，红黑树，B -Tree, B+Tree？

1. 二叉树（Binary Tree）

   • 解决的问题：相比顺序遍历，利用二分法快速定位数据

   • 局限性：退化为链表：数据有序插入时（如递增ID），树退化为线性结构，查询复杂度由O(logN)退化为O(N)。

   • 磁盘IO问题：树高不可控，数据量较大时树层级过深，导致磁盘IO次数过多（每次查询需多次磁盘访问）

2. 平衡二叉树（AVL树）

   • 解决的问题：避免二叉树退化为链表，强制左右子树高度差≤1，保证查询复杂度

   • 解决的问题：在平衡二叉树基础上放宽平衡要求（最长路径≤2倍最短路径），减少旋转次数，提升插入/删除效率

   • 优势：

     • 近似平衡：插入/删除最多3次旋转，复杂度稳定在O(logN)

     • 内存友好：适合内存数据结构（如Java的TreeMap）

   • 局限性：磁盘场景不适用：树高仍随数据量增长，无法解决海量数据下磁盘IO问题

3. B-Tree（多路平衡查找树）

   • 解决的问题：针对磁盘IO优化，通过多节点存储降低树高

   • 关键优化：

     • 多路分支：每个节点存储多个键值（阶数m），子节点数=键值数+1，显著减少树高（如阶数500，百万数据仅3层）

     • 节点预读：磁盘按页（如16KB）读取，单节点存多键值，减少IO次数

   • 局限性：

     • 范围查询效率低：非叶子节点存储数据，范围查询需跨层多次遍历

     • 空间冗余：键值与数据混合存储，节点容量受限

4. B+Tree（B-Tree优化版）

   • 解决的问题：优化B-Tree的查询效率与存储结构，适配数据库场景

   • 核心改进：

     • 非叶子节点仅存索引：不存数据，单节点容纳更多键值，进一步降低树高

     • 叶子节点链表连接：所有数据存于叶子节点，并按顺序形成双向链表，支持高效范围查询与顺序扫描

     • 聚簇索引优化：InnoDB主键索引叶子节点直接存数据行，减少回表

       优势：

   • 优势：

     • 磁盘IO更少：树高更低，百万数据仅需3次IO

     • 范围查询高效：通过叶子节点链表快速遍历区间数据

     • 全表扫描更快：叶子节点包含全部数据，避免非必要分支访问

5. 最终选择B+Tree的原因：

   • 磁盘友好：树高可控，减少IO次数

   • 范围查询高效：叶子链表天然支持范围扫描

   • 存储利用率高：非叶子节点仅存索引，容纳更多键值

   • 与数据库设计匹配：聚簇索引、覆盖索引等特性直接依赖B+Tree结构

1.3.3 索引树高度影响因素有哪些？

1. 表的数据行数过多办法：表分区；定期归档（工具pt-archiver）

2. 索引列长度过长办法：使用前缀索引

3. 数据类型不当（char/varchar）

1.3.4 详细说一说B+树在磁盘IO方面的优势

• MySQL中一次IO操作最小的单位是page，每个page设计为16kb，以业务常用的8字节的bigint为例，8字节加上6字节指向下一元素的指针共14字节，每个page可以存下约1000左右的数据，那么三层高度的树就可以存下共1000^3个元素，这样就算每次只往内存加载一层，最多三次IO也就能定位到要找的元素在那个位置了，page大小可以指定，但是最好就使用16kb的设置，因为它是符合操作系统底层IO设计的--一次查一页4kb，但每次一并返回周围的三块页共页也就是16kb

1.3.5 MySQL 8.0索引的新特性

1. 隐藏索引：8.0 开始支持隐藏索引，不可见索引不会被优化器使用，但仍需维护

2. 降序索引：8.0 开始真正支持降序索引

3. 隐式索引：不再对 GROUP BY 操作进行隐式排序

4. 函数索引：支持在索引中使用函数计算后的值

1.3.6 创建索引时应当注意什么（什么时候适合创建索引）？

需要创建索引：

• 频繁作为where查询的字段 （select,update,delete 的where 条件）

• DISTINCT 字段需要创建索引

• 经常使用GROUP BY 和 ORDER BY 的列

注意事项：

• 字段的数值具有唯一性限制 （业务上具有唯一特性的字段，即使是组合字段，也必须组成唯一索引）

• 对列的类型小的创建索引 （数据类型越小查询速度越快）

• 列值长度较长的索引列，建议使用前缀索引 （截取字符串前面的一部分建立索引（前缀索引），全文索引会占用空间和查询时间）

• 使用散列度高的列作为索引 （但不一定 例如性别，在男性多的中查找女性，如果对女性做索引就能增加查询速度）

• 使用最频繁的列放到联合索引的左侧

• 多表连接时创建索引

• 连接表的时尽量不要超过三张

• 对于连接的字段创建索引 （JOIN　ON ）

• 最好使用唯一值多的列作为索引，如果索引列重复值较多，可以考虑使用联合

• 索引维护要避开业务繁忙期

1.3.7 什么时候不适合创建索引

• 在where中使用不到的字段，不要设置索引

• 数据量小的表不要使用索引 （全表扫描更快）

• 有大量重复数据的列上不要建立索引

• 避免对经常更新的表创建过多的索引

• 不建议用无序的值作为索引 （尽量单调递增）

• 删除不再使用或者很少使用的索引

• 不要定义夯余或者重复的索引

1.3.8 什么情况会造成索引失效？

• 主键插入顺序出现忽大忽小，或从中间插入值

• 函数导致索引失效

• 类型转换导致索引失效

• 不等于 <> !=

• is null ,is not null (有例外)

• like 通配符以 %开头

• 范围条件右边的索引失效 需要将范围查找的值放到最后

• OR 前后存在非索引的列，索引失效

• where选取范围过大 会从range 转化为 all

• 查询条件复杂

• 统计数据不准确，导致成本计算误判

• 表中两个不同字段进行比较

• 使用了 order by 但是后面不是主键，或者不是覆盖索引导致不走索引

• 子查询可能会导致失效

• 连表查询需要注意不同字段的字符集是否一致，否则也会导致全表扫描

1.3.9 如何获取执行计划？如何理解分析执行计划的输出？

为什么分析执行计划？

• 分析比较慢的执行语句

• 上线新的业务，可能包含很多 select update delete 提前发现

如何产生的执行计划:

• 优化器得出的，代价最低的SQL语句执行方案

如何获取执行计划：

• 使用 EXPLAIN SQL 语句

• 使用 DESC SQL 语句

执行计划字段解释

• id：选择标识符

• select_type：查询类型（简单查询或复杂查询）

• table：当前操作的表

• type：操作类型（ALL、INDEX、RANGE、REF 等）

• possible_keys：可能使用的索引

• key：实际使用的索引

• key_len：索引覆盖长度（字节）

• rows：预估需要扫描的行数

• extra：额外信息（如 Using index、Using where 等）

1.3.10 什么是回表查询？如何减少回表

回表查询：InnoDB中，数据以B+树的形式进行组织，聚簇索引叶子节点存的是主键所对应的行所有字段的数据，而非聚簇索引叶子结点仅存该索引所对应的主键ID。如果某条查询走了非聚簇索引但又需要返回整行数据的话，则需要进行回表操作，会损耗一定的性能（覆盖索引的情况下则无需回表）

回表的问题：增加磁盘 IO 次数（IOPS），增加吞吐量

减少回表的方法

1. 建立合适的联合索引，尽可能将查询条件的数据包含在联合索引中

2. 精细查询条件，尽量使用等值查询，符合联合索引规则，覆盖的列越多越好

3. 索引优化器：

   • ICP（Index Condition Pushdown）：索引下推优化

   • MRR（Multi-Range Read）：在辅助索引阶段获取主键值后排序回表

1.3.11 聚簇索引和非聚簇索引有什么区别？

• 聚簇索引中，索引的叶子节点存储的的是数据行，可以直接访问到完整的数据

• 每个表中只能有一个聚簇索引，通常是主键索引，适合范围查询和排序

• 非聚簇索引中，索引的叶子节点存储的数据行的主键和对于索引列，需要通过主键才能访问完整的数据行

• 一个表中可以有多个非聚簇索引，适用于查找特定列的数据

• 如果使用非聚簇索引查询字段信息，且查询的字段不在where 的查询字段中，需要回表查询

1.3.12 描述 MySQL 的 B+ 树中查询数据的全过程

1. 数据从根节点找器，根据键值的大小确定左子树还是右子树，从上到下定位到叶子节点；

2. 叶子节点中存储事务的数据行记录，但一页只有16KB大小，存储数据不止一条

3. 叶子节点中数据行以组的形式划分，利用页的目录结构中slot，通过二分法可以定位到对应的组

4. 定位组之后，利用链表遍历就可以找到对应的数据行

1.3.13 唯一索引与普通索引的区别？

区别在于更新时，需要检查更新后的值是否具备唯一性，这个在启用了 changeBuffer时影响较大，当使用唯一索引时，更新后的值无法直接存入changeBuffer，而是要做一次磁盘查询，来确定更新后的值的唯一性，因此性能相对较差，因此应当尽量使用业务来保证数据的唯一性。

1.3.14 详细说说最左前缀匹配

MySQL索引的最左前缀匹配原则指的是使用联合索引时

1. 查询条件必须从索引的最左侧开始匹配数据 a* b c selech from where a=xx / a>=xx / a<=xx

2. 创建联合索引需要将索引选择度高的列放在最左边 name-索引选择度高 gender 减少辅助索引查询数据IO消耗

最左原则应用情况：

1. 最左列最好是等值查询（=，>=,<=），不能范围查询 (> <)

2. 一定条件信息包含最左列，但其他列信息可以不包含，或可以不用等值查询，因为可以借助索引下推功能，减少IO消耗查询数据

3. 当最左列索引选择度低时，可以在查询信息时，不定义最左列信息，利用skip scan优化功能，会自动添加左列信息