MySQL面试题大全

一个摩拉

于 2025-05-13 14:45:17 发布

阅读量344

点赞数 5

CC 4.0 BY-SA版权

分类专栏：面试文章标签： mysql

本文链接：https://blog.youkuaiyun.com/QWERTYuuid/article/details/147923652

面试专栏收录该内容

9 篇文章

订阅专栏

MySQL面试题大全

一、基础概念与数据类型

1.1 MySQL架构组成

MySQL采用典型的C/S架构，主要包含：

连接池组件
SQL接口组件
查询分析器
优化器
缓存组件
插件式存储引擎
物理文件

1.2 常用数据类型

-- 数值类型
INT(11)            -- 整型
DECIMAL(10,2)      -- 精确小数
FLOAT(8,3)         -- 单精度浮点

-- 字符串类型
VARCHAR(255)       -- 可变长度字符串
CHAR(10)           -- 定长字符串
TEXT               -- 长文本数据

-- 时间类型
DATETIME           -- 日期时间（精度到秒）
TIMESTAMP          -- 时间戳（自动更新）
DATE               -- 日期

二、索引与性能优化

2.1 B+树索引原理

$$ \begin{aligned} &\text{B+树特点：}\ &1. \text{非叶子节点仅存储键值}\ &2. \text{叶子节点形成有序链表}\ &3. \text{所有数据存储在叶子节点} \end{aligned} $$

2.2 索引创建示例

-- 创建普通索引
CREATE INDEX idx_name ON users(last_name);

-- 创建联合索引
ALTER TABLE orders ADD INDEX idx_cust_date (customer_id, order_date);

-- 查看索引使用情况
EXPLAIN SELECT * FROM products WHERE price > 100;

2.3 索引优化策略

最左前缀原则应用
避免在索引列上使用函数
区分度高的列适合建索引
使用覆盖索引减少IO

三、事务与锁机制

3.1 ACID特性实现

START TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE user_id = 1;
UPDATE accounts SET balance = balance + 100 WHERE user_id = 2;
COMMIT;

3.2 隔离级别对比

隔离级别	脏读	不可重复读	幻读	锁机制
READ UNCOMMITTED	✓	✓	✓	无锁
READ COMMITTED	×	✓	✓	行级锁
REPEATABLE READ	×	×	✓	Gap锁
SERIALIZABLE	×	×	×	表级锁

四、存储引擎对比

4.1 InnoDB vs MyISAM

特性	InnoDB	MyISAM
事务支持	✓	×
行级锁	✓	×（表锁）
外键约束	✓	×
崩溃恢复	支持	不支持
存储文件	.ibd	.MYD/.MYI

五、SQL优化实战

5.1 分页查询优化

-- 低效写法
SELECT * FROM logs ORDER BY create_time DESC LIMIT 1000000, 10;

-- 优化写法（使用覆盖索引）
SELECT * FROM logs 
WHERE id > (SELECT id FROM logs ORDER BY create_time DESC LIMIT 1000000, 1)
ORDER BY create_time DESC 
LIMIT 10;

5.2 慢查询日志分析

# 配置my.cnf
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 2
log_queries_not_using_indexes = 1

# 使用mysqldumpslow分析
mysqldumpslow -t 10 -s at /var/log/mysql/slow.log

六、主从复制配置

6.1 配置步骤

主库配置：

[mysqld]
server-id=1
log-bin=mysql-bin
binlog_format=row

创建复制用户：

CREATE USER 'repl'@'%' IDENTIFIED BY 'password';
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';

从库配置：

CHANGE MASTER TO
MASTER_HOST='master_host',
MASTER_USER='repl',
MASTER_PASSWORD='password',
MASTER_LOG_FILE='mysql-bin.000001',
MASTER_LOG_POS=107;

七、分库分表方案

7.1 分片策略对比

策略类型	优点	缺点
范围分片	易于扩展	数据分布不均
哈希分片	数据分布均匀	扩容困难
一致性哈希	扩容影响小	实现复杂

7.2 MyCat分表示例

<!-- schema.xml配置 -->
<table name="orders" dataNode="dn1,dn2" rule="mod-long"/>
<dataNode name="dn1" dataHost="host1" database="db1" />
<dataNode name="dn2" dataHost="host2" database="db2" />

八、高可用方案

8.1 MHA架构

应用程序 → VIP
        ↓
Master → Slave1（候选主）
        ↓
Slave2 → Slave3

8.2 故障转移流程

检测主库故障
选择最新从库
提升为新的主库
其他从库切换复制源
应用连接新主库

九、安全与权限管理

9.1 权限授予示例

-- 创建只读用户
CREATE USER 'reader'@'%' IDENTIFIED BY 'SecurePass123!';
GRANT SELECT ON sales.* TO 'reader'@'%';

-- 查看权限
SHOW GRANTS FOR 'reader'@'%';

9.2 SQL注入防护

使用预处理语句：

cursor.execute("SELECT * FROM users WHERE id = %s", (user_id,))

输入验证过滤
最小权限原则
定期漏洞扫描

十、新特性解析

10.1 窗口函数应用

SELECT 
    department_id,
    employee_name,
    salary,
    RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS rank
FROM employees;

10.2 JSON类型操作

-- 创建JSON字段
CREATE TABLE product (
    id INT PRIMARY KEY,
    specs JSON
);

-- 查询JSON字段
SELECT specs->'$.dimensions.width' 
FROM product 
WHERE specs->'$.color' = 'red';

（后续章节包含：查询缓存优化、死锁处理方案、备份恢复策略、性能监控工具使用等深度内容）