个人MySQL实战速查手册篇

目前都在转型国产化高斯，但为了避免mysql忘记，重新给自己记录了一下MySQL实战速查手册，重点突出高频使用场景和避坑指南：

一、基础操作突击包

1. 建表模板

CREATE TABLE `user` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主键',
  `name` VARCHAR(50) NOT NULL DEFAULT '' COMMENT '用户名',
  `age` TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '年龄',
  `create_time` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_time` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '修改时间',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uniq_name` (`name`),
  KEY `idx_age` (`age`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='用户表';

要点：

• 永远用utf8mb4字符集
• 时间字段用DATETIME(别用TIMESTAMP)
• 主键用BIGINT预留扩展空间

2. 索引使用红黑榜

二、性能优化三板斧

1. EXPLAIN诊断术

EXPLAIN 
SELECT u.*, o.order_no 
FROM user u
JOIN order o ON u.id = o.user_id
WHERE u.age > 18 
  AND o.status = 1
ORDER BY u.create_time DESC
LIMIT 10;

关键指标解读：

• type：至少达到range级别（别出现ALL全表扫描）
• rows：预估扫描行数（超过1万要警惕）
• Extra：出现Using filesort说明需要优化排序

2. 分页优化技巧

反例：

SELECT * FROM order 
WHERE status=1 
ORDER BY id DESC 
LIMIT 100000, 10; -- 越往后越慢

正解：

SELECT * FROM order 
WHERE status=1 AND id < 上一页最小ID 
ORDER BY id DESC 
LIMIT 10; -- 基于游标的分页

3. 连接池配置参数

# SpringBoot配置示例
spring:
  datasource:
    hikari:
      maximum-pool-size: 20      # 建议：CPU核心数*2 + 磁盘数
      connection-timeout: 3000  # 3秒超时
      idle-timeout: 600000      # 10分钟空闲回收
      max-lifetime: 1800000     # 30分钟强制回收

三、事务与锁实战

1. 隔离级别对照表

2. 死锁急救步骤

1. 查看最近死锁日志

SHOW ENGINE INNODB STATUS; -- 找LATEST DETECTED DEADLOCK段落

2. 分析锁等待关系
3. 代码层解决方案：
   • 统一事务操作顺序（如先更新A表再B表）
   • 使用SELECT ... FOR UPDATE NOWAIT

四、Java联调指南

1. MyBatis防坑写法

反例：N+1查询问题

<select id="getUser" resultMap="userMap">
  SELECT * FROM user WHERE id = #{id}
</select>
<resultMap id="userMap">
  <collection property="orders" 
              select="getOrdersByUserId" 
              column="id"/> <!-- 每个用户都会触发一次查询 -->
</resultMap>

正解：使用JOIN一次性查询

<select id="getUserWithOrders" resultMap="userOrderMap">
  SELECT u.*, o.*
  FROM user u
  LEFT JOIN order o ON u.id = o.user_id
  WHERE u.id = #{id}
</select>

2. 批量插入优化

// 低效写法
for (User user : userList) {
    userMapper.insert(user);
}

// 高效写法（MyBatis）
<insert id="batchInsert" useGeneratedKeys="true">
    INSERT INTO user (name, age) VALUES
    <foreach collection="list" item="item" separator=",">
        (#{item.name}, #{item.age})
    </foreach>
</insert>

性能对比：1000条数据从10秒 → 0.5秒

五、运维急救箱

1. 慢查询日志分析

-- 查看慢查询配置
SHOW VARIABLES LIKE '%slow_query%'; 

-- 临时开启分析模式
SET GLOBAL slow_query_log = 1;
SET GLOBAL long_query_time = 1; -- 超过1秒的查询

-- 用mysqldumpslow工具分析
mysqldumpslow -s t /var/lib/mysql/xxx-slow.log

2. 数据清理策略

根据时间清理：

DELETE FROM log 
WHERE create_time < DATE_SUB(NOW(), INTERVAL 30 DAY)
ORDER BY create_time 
LIMIT 1000; -- 分批删除避免锁表

六、高频面试考点

1. B+树索引原理：三层B+树可存储约2000万数据（假设每页16KB）
2. redo/undo log作用：redo保证持久性，undo实现事务回滚和MVCC
3. MVCC实现机制：通过read-view和版本链实现非锁定读

附：速查命令表

# 导出表结构（不要数据）
mysqldump -h127.0.0.1 -uroot -p --no-data dbname > schema.sql

# 压力测试工具
mysqlslap --concurrency=100 --iterations=10 --query="SELECT * FROM user"

掌握这些技巧，日常开发效率至少提升50%。遇到复杂SQL时记住：能用JOIN不用子查询，能走索引不走全表！nice！