MySQL：(0) MySQL基础

1. innodb 引擎的 4大特性

innodb 引擎的4大特性 1.插入缓冲; 2.二次写; 3.自适应哈希; 4.预读

2. Mysql 中有哪些不同的表格

2.1 InnoDB (默认)

• 地位：MySQL 5.5 版本后的默认引擎。
• 核心特性：
  • 支持事务处理 (ACID 兼容)。
  • 支持行级锁 (Row-level locking)，高并发写入性能极佳。
  • 支持外键约束 (Foreign Keys)。
  • 支持崩溃恢复 (Crash Recovery)。
• 适用场景：绝大多数需要事务安全、高并发读写的应用场景（如电商订单、用户系统）。
• 缺点：相比 MyISAM，占用更多磁盘空间，全表扫描速度略慢（但在现代硬件上差异不大）。

2.2 MyISAM (旧版默认)

• 地位：MySQL 5.5 之前的默认引擎，目前主要用于遗留系统或特定只读场景。
• 核心特性：
  • 不支持事务和外键。
  • 使用表级锁 (Table-level locking)，写入时会锁住整张表，并发性能差。
  • 读取速度快，尤其是 COUNT(*) 操作（因为内部维护了行数计数）。
  • 支持全文索引 (Full-text Index)（注：InnoDB 在 5.6+ 也支持了）。
• 适用场景：只读或读多写少、不需要事务安全的日志记录、数据仓库报表。
• 缺点：表损坏后修复困难，不支持事务，高并发写入瓶颈明显。

2.3 Memory (Heap)

• 地位：基于内存的临时表引擎。
• 核心特性：
  • 数据存储在RAM中，速度极快。
  • 重启后数据丢失（易失性）。
  • 默认使用哈希索引（Hash Index），适合等值查询，不适合范围查询。
  • 表大小受限于 max_heap_table_size。
• 适用场景：临时中间表、缓存查找表、会话存储。
• 缺点：数据不安全（断电即失），受内存大小限制。

2.4 Archive

• 地位：专为归档设计。
• 核心特性：
  • 采用高压缩比存储，节省空间。
  • 只支持插入和查询，不支持更新 (UPDATE) 和删除 (DELETE)。
  • 不支持索引（除了自增主键）。
• 适用场景：历史数据归档、日志长期存储。

2.5 CSV

• 地位：文本文件映射。
• 核心特性：
  • 数据直接以逗号分隔值 (.csv) 文件形式存储在磁盘上。
  • 可以用文本编辑器直接查看和修改。
  • 不支持索引和事务。
• 适用场景：数据交换、作为外部数据源的接口。

2.6 Blackhole (黑洞)

• 地位：特殊用途引擎。
• 核心特性：
  • 接收所有写入操作，但不存储任何数据，查询永远返回空集。
  • 会记录二进制日志 (Binlog)。
• 适用场景：主从复制中的中继节点（只转发数据不存储）、审计测试（测试 SQL 语法而不影响数据）。

2.7 Federated

• 地位：远程访问引擎。
• 核心特性：
  • 本地不存数据，指向远程 MySQL 服务器上的表。
  • 像访问本地表一样访问远程表。
• 适用场景：分布式数据库查询（性能较差，需谨慎使用）。

3. myisam 与 innodb 的区别

3.1 存储架构与空间效率

特性

MyISAM

InnoDB

3.2 事务、外键与锁

特性

MyISAM

InnoDB

3.3 操作差异

场景/操作

MyISAM 表现

InnoDB 表现

备注

例：一张表，里面有ID自增主键，当insert了17条记录之后，删除了第15,16,17条记录，
再把Mysql重启，再insert一条记录，这条记录的ID是18还是15 ？

（1）如果表的类型是MyISAM，那么是18 
因为MyISAM表会把自增主键的最大ID记录到数据文件里，重启MySQL自增主键的最大
ID 也不会丢失 。

（2）如果表的类型是InnoDB，那么是15 
InnoDB 表只是把自增主键的最大ID记录到内存中，所以重启数据库或者是对表进行
OPTIMIZE 操作，都会导致最大ID丢失 。

3.4 全文索引与崩溃恢复

特性

MyISAM

InnoDB

4. InnoDB 支持的四种事务隔离级别

• read uncommited ：读未提交
• read committed：读已提交
• repeatable read：可重读
• serializable ：串行化

5. 数据库优化相关操作

1. 用PreparedStatement， 一般来说比Statement性能高：一个sql 发给服务器去执行，涉及步骤：语法检查、语义分析， 编译，缓存。

2. 有外键约束会影响插入和删除性能，如果程序能够保证数据的完整性，那在设计数据库时就去掉外键。

3. 表中允许适当冗余，譬如，主题帖的回复数量和最后回复时间等

4. UNION ALL 要比 UNION 快很多，所以，如果可以确认合并的两个结果集中不包含重复数据且不需要排序时的话，那么就使用UNION ALL。

特性

UNION

UNION ALL

6. mysql 的复制原理以及流程

• 主服务器 (Master)：负责处理写操作，并将所有数据变更记录到二进制日志 (Binary Log) 中。
• 从服务器 (Slave)：负责读取并应用主服务器的变更，实现数据同步（可处理读操作，分担负载）。

7. mysql 支持的复制类型

1. 基于语句的复制： 在主服务器上执行的SQL语句，在从服务器上执行同样的语句。MySQL默认采用基于语句的复制，效率比较高。 一旦发现没法精确复制时，会自动选着基于行的复制。 

2. 基于行的复制：把改变的内容复制过去，而不是把命令在从服务器上执行一遍. 从mysql5.0开始支持 

3. 混合类型的复制: 默认采用基于语句的复制，一旦发现基于语句的无法精确的复制时，就会采用基于行的复制。 

8. 索引种类与工作机制

工作机制：使用B树及其变种B+树。

• B 树 (B-Tree)：
  • 数据存储：每个节点（包括根节点、内部节点、叶子节点）都存储 键 (Key) + 数据 (Data)。
  • 查找逻辑：只要在某个节点找到匹配的 Key，就可以直接返回数据，无需走到叶子节点。
• B+ 树 (B+ Tree)：
  • 数据存储：只有叶子节点存储 键 (Key) + 数据 (Data)；非叶子节点（内部节点）只存储键 (Key) 作为索引指引。
  • 链表结构：所有叶子节点通过双向指针连接成一个有序链表。

特性

B 树

B+ 树 (MySQL 选择)

优势解析

9. MySQL中控制内存分配的全局参数，有哪些？

9.1 核心共享内存（全局唯一，直接影响总占用）

• innodb_buffer_pool_size：最重要。InnoDB 引擎的数据和索引缓存区。通常设置为物理内存的 50%-70%。
• key_buffer_size：MyISAM 引擎的索引缓存区（若不用 MyISAM 可设小）。
• query_cache_size：查询缓存（MySQL 8.0 已移除，5.7 及以下建议关闭）。
• innodb_log_buffer_size：InnoDB 重做日志（Redo Log）的缓冲区。

9.2 每线程/连接内存（全局设定上限，实际占用 = 参数值 × 并发连接数）

• sort_buffer_size：排序操作（ORDER BY, GROUP BY）使用的缓冲区。
• read_buffer_size：顺序读操作的缓冲区。
• read_rnd_buffer_size：随机读操作（如排序后读行）的缓冲区。
• join_buffer_size：表连接（JOIN）不使用索引时的缓冲区。
• tmp_table_size / max_heap_table_size：内存临时表的最大大小（超过则转磁盘）。
• thread_stack：每个线程的栈空间大小。

10. 表中有大字段如：text类型，且字段不会经常更新，以读为主，将该字段拆成子表好处是什么？

11. insert、update 的 select 语句语法

11.1 INSERT … SELECT (批量插入)

INSERT INTO 目标表 (列 1, 列 2)
SELECT 列 A, 列 B
FROM 源表
WHERE 条件;

11.2 UPDATE … JOIN (多表更新)

MySQL 不支持直接在 UPDATE 后跟 SELECT 子句（如 UPDATE t SET col = (SELECT…) 效率低且有限制），标准做法是使用 JOIN。

根据另一张表的统计值或关联值更新当前表：

UPDATE 目标表 AS t1
JOIN 源表 AS t2 ON t1.id = t2.id
SET t1.列名 = t2.列名
WHERE 条件;

替代写法 (子查询)：仅适用于单行单列返回，性能较差。

UPDATE 目标表
SET 列名 = (SELECT 值 FROM 源表 WHERE …)
WHERE 条件;

11.3 当记录不存在时 insert，当记录存在时 update

核心前提：目标表必须拥有 主键 或 唯一索引。只有当插入的值违反了唯一性约束时，才会触发更新逻辑。

INSERT INTO 表名 (id, col1, col2)
VALUES (1, '值 A', '值 B')
ON DUPLICATE KEY UPDATE
col1 = VALUES(col1),
col2 = VALUES(col2);

12. 其他问题

12.1 数据库三范式

列唯一、主键唯一、其他列必须直接依赖主键，非主键之间不能有依赖。

12.2 若一张表中只有一个字段 VARCHAR(N) 类型，utf8编码，则N最大值？精确到数量级即可 

由于 utf8 的每个字符最多占用3个字节。而MySQL定义行的长度不能超过 65535，因此N的最大值计算方法为：(65535-1-2)/3。减去1的原因是实际存储从第二个字节开始，减去2的原因是因为要在列表长度存储实际的字符长度，除以3是因为utf8限制：每个字符最多占用3个字节。

12.3 Heap 表

HEAP 表存在于内存中，用于临时高速存储。

• BLOB 或TEXT字段是不允许的；
• 只能使用比较运算符=，，=>，= <；
• HEAP 表不支持AUTO_INCREMENT，索引不可为NULL。

12.4 如何控制HEAP表的最大尺寸

通过称为max_heap_table_size的Mysql配置变量来控制。

12.5 默认端口 3306

12.6 [SELECT *] 和[SELECT 全部字段]的2种写法有何优缺点

特性

SELECT *

SELECT 字段 1, 字段 2…

12.7 HAVNG 子句 和 WHERE的异同点

特性

WHERE

HAVING

FROM → WHERE (过滤行) → GROUP BY (分组) → HAVING (过滤组) → SELECT → ORDER BY

12.8 FLOAT和DOUBLE区别

FLOAT，8位精度，有四个字节。 
DOUBLE，18位精度，有八个字节。

12.9 CHAR_LENGTH和LENGTH区别

CHAR_LENGTH 是字符数，而LENGTH是字节数。Latin字符的这两个数据是相同的，但是对于Unicode 和其他编码，它们是不同的。

12.10 CHAR 和VARCHAR的区别

• CHAR 是定长字符串，长度固定（如 CHAR(10)始终占用 10 个字符的空间，不足部分用空格填充），适合存储长度固定的数据（如手机号、国家代码）。
• VARCHAR 是变长字符串，仅占用实际字符长度 + 1/2 字节（记录长度），适合存储长度变化较大的数据（如用户名、地址），更节省空间。

12.11 LIKE 和 REGEXP 操作区别

特性

LIKE

REGEXP (正则表达式)

12.12 BLOB 和TEXT 区别

特性

BLOB (Binary Large Object)

TEXT

12.13 主键和候选键区别

主键是从候选键中选择的、用于唯一标识表中每个记录的一个特定键（不允许 NULL 值）。候选键是表中所有能唯一标识记录的一个或多个字段集合（都满足唯一性且最小），主键是其中被“选定”的那个键，每个表只能有一个主键。

12.14 TIMESTAMP 在UPDATE CURRENT_TIMESTAMP 数据类型上做什么

TIMESTAMP列如果设置了 ON UPDATE CURRENT_TIMESTAMP，会在该行数据发生任何更新时，自动将该列的值更新为当前时间戳

12.15 myisamchk 是用来做什么的

myisamchk是 MySQL 中一个用于检查、修复、优化和描述 MyISAM 表的命令行工具。它直接在物理表文件（.MYD、.MYI）上操作，因此在使用前需确保相关表未被服务器使用（或服务器已停止）。主要功能包括检查表错误、修复损坏的表、压缩索引、获取表信息等。

12.16 MyISAM Static 和 MyISAM Dynamic 区别

MyISAM Static（静态表）​ 和 MyISAM Dynamic（动态表）​ 是 MyISAM 存储引擎的两种表格式，主要区别在于列存储方式：

• Static（静态）：表中所有行都使用固定的存储长度。如果声明的列是 CHAR 或固定长度的类型，它总是静态的。即使 VARCHAR 列，如果表中没有 BLOB/TEXT，且所有 VARCHAR 列都声明了足够大的长度，MySQL 也可能将其转为静态格式以提升性能。

• Dynamic（动态）：表中包含可变长度列（如 VARCHAR、BLOB、TEXT），每行仅存储实际数据长度，节省空间但可能产生碎片。

简单说：静态表固定长度，查询稍快但可能浪费空间；动态表可变长度，节省空间但可能有碎片，频繁更新后需优化来整理碎片。

12.17 NOW（）和CURRENT_DATE（）区别

• NOW（）命令用于显示当前年份，月份，日期，小时，分钟和秒。
• CURRENT_DATE（）仅显示当前年份，月份和日期。

12.18 列设置为AUTO INCREMENT时，如果在表中达到最大值，会发生什么情况

它会停止递增，任何进一步的插入都将产生错误，因为密钥已被使用。

12.19 怎样才能找出最后一次插入时分配了哪个自动增量

LAST_INSERT_ID 将返回由 Auto_increment 分配的最后一个值，并且不需要指定表名称。

12.20 获取当前的Mysql版本

SELECT VERSION();用于获取当前 Mysql 的版本

12.21 怎么看到为表格定义的所有索引

SHOW INDEX FROM

12.22 如何在Unix和Mysql时间戳之间进行转换

• UNIX_TIMESTAMP 是从 Mysql 时间戳转换为Unix时间戳的命令；
• FROM_UNIXTIME 是从Unix 时间戳转换为Mysql时间戳的命令。

12.23 如何得到受查询影响的行数

SELECT COUNT (user_id) FROM users;

12.24 Mysql 查询是否区分大小写

不区分

12.25 ENUM的用法

ENUM是一个字符串对象，用于指定一组预定义的值，并可在创建表时使用。 Create table size(name ENUM('Smail,'Medium','Large');

12.26 如何在mysql中运行批处理模式

mysql -u 用户名 -p 数据库名 < 脚本文件.sql
mysql> source /path/to/脚本文件.sql;
— 或者简写为
mysql> \\. /path/to/脚本文件.sql;

12.27 ISAM 是什么

ISAM (Indexed Sequential Access Method，索引顺序访问方法) 是 MySQL 早期（2006年以前）使用的一种存储引擎。

12.28 Mysql 如何优化DISTINCT

DISTINCT 是 SQL 中的关键字，用于过滤查询结果中的重复行，确保返回的每一行数据都是唯一的。

策略

核心动作

原理与效果

12.29 如何输入字符为十六进制数字

场景

语法/函数

示例

结果/说明

12.30 如何显示前50行

使用 LIMIT 50 子句，例如：SELECT * FROM 表名 LIMIT 50;

12.31 可以使用多少列创建索引

任何标准表最多可以创建16个索引列。

12.32 Mysql 表中允许有多少个TRIGGERS？

每个表最多支持 6 个触发器。

组合逻辑为：2 种时机 (BEFORE, AFTER) × 3 种操作 (INSERT, UPDATE, DELETE) = 6 个。

12.33 什么是非标准字符串类型

通常指 SET 和 ENUM

12.34 解释访问控制列表

访问控制列表 (ACL) 在 MySQL 中是权限管理系统的核心数据结构。

• 定义：它是服务器内存中缓存的一组表格，记录了所有用户账号及其对应的权限信息（如能访问哪些库、表、列，能执行什么操作）。
• 工作原理：当用户发起连接或执行 SQL 时，MySQL 会将请求与 ACL 进行比对。只有匹配到允许规则，操作才会被执行；否则拒绝访问。
• 来源：ACL 的内容来源于系统数据库 mysql 中的权限表（如 user, db, tables_priv 等）。
• 刷新机制：执行 GRANT/REVOKE 命令或 FLUSH PRIVILEGES 时，ACL 会重新加载生效。

12.35 mysql 里记录货币用什么字段类型好

NUMERIC 和DECIMAL 类型（被Mysql实现为同样的类型）

12.36 MYSQL 数据表在什么情况下容易损坏

1. 硬件与系统故障 (最常见)

• 突然断电：服务器在写入数据时突然断电，导致磁盘文件头或数据块写入不完整。
• 磁盘坏道：硬盘物理损伤导致存储的数据位翻转或丢失。
• 内存错误：RAM 故障导致写入磁盘的数据本身就是错误的。
• 操作系统崩溃：OS 死机或强制重启，文件系统未正常同步。

2. 不当的操作与管理

• 强制杀死进程：在 MySQL 进行写操作时，使用 kill -9 强制杀掉 mysqld 进程，跳过正常的清理和关闭流程。
• 直接操作文件：在 MySQL 服务运行时，直接在操作系统层面复制、移动、删除或修改 .frm, .MYD, .MYI (MyISAM) 或 .ibd (InnoDB) 文件。
• 非正常关机：未执行 service mysql stop 而直接拔电源或重置虚拟机。

12.37 mysql 有关权限的表都有哪几个

权限表存放在mysql数据库里，由 mysql_install_db 脚本初始化。

表名

作用范围 (粒度)

说明