数据库基础

🗄️ MySQL 核心知识

从安装配置到高级特性，系统梳理 MySQL 的核心概念与常用技巧

📌 MySQL 简介

MySQL 是全球最流行的开源关系型数据库管理系统（RDBMS），由瑞典 MySQL AB 公司开发，现属 Oracle 旗下。广泛用于 Web 应用、企业系统和数据分析场景。

核心特点

特性说明
开源免费社区版（Community）完全免费，企业版提供更多支持
跨平台支持 Linux、Windows、macOS 等主流操作系统
多存储引擎InnoDB、MyISAM、Memory 等，各有适用场景
高性能支持读写分离、分区表、连接池等优化手段
ACID 事务InnoDB 引擎完全支持原子性、一致性、隔离性、持久性

版本说明

目前主流版本为 MySQL 8.0，引入了窗口函数、JSON 增强、角色权限管理等特性，推荐新项目直接使用 8.0+。

MySQL 5.7 MySQL 8.0 MariaDB Percona Server

🔢 数据类型

选择合适的数据类型是数据库设计的基础，直接影响存储空间和查询性能。

数值类型

类型字节范围（有符号）适用场景
TINYINT1-128 ~ 127状态标志（0/1）
SMALLINT2-32768 ~ 32767小范围整数
INT4约 ±21 亿通用 ID、计数
BIGINT8约 ±922 亿亿分布式 ID、大流水号
DECIMAL(p,s)变长精确小数金额、财务数据
FLOAT / DOUBLE4 / 8浮点近似值科学计算（不宜存金额）

字符串类型

类型最大长度说明
CHAR(n)255 字符定长，不足补空格，查询速度稍快
VARCHAR(n)65535 字节变长，节省空间，最常用
TEXT65535 字节长文本，不能设默认值
LONGTEXT4GB超长文本内容
JSON—MySQL 5.7+ 原生 JSON 类型
💡 建议：固定长度用 CHAR（如手机号、MD5），可变长度用 VARCHAR，金额必须用 DECIMAL，不要用 FLOAT。

时间类型

类型格式范围特点
DATEYYYY-MM-DD1000-01-01 ~ 9999-12-31仅日期
DATETIMEYYYY-MM-DD HH:MM:SS1000 ~ 9999 年不受时区影响
TIMESTAMPYYYY-MM-DD HH:MM:SS1970 ~ 2038 年随时区转换，存 UTC
⚠️ 注意：TIMESTAMP 在 2038 年会溢出（2038 年问题），新系统建议使用 DATETIME 或存 BIGINT 时间戳。

🏗️ DDL 建表语法

DDL（Data Definition Language）用于定义和修改数据库结构。

创建数据库和表

-- 创建数据库，指定字符集
CREATE DATABASE tech_blog
  CHARACTER SET utf8mb4
  COLLATE utf8mb4_unicode_ci;

USE tech_blog;

-- 创建文章表
CREATE TABLE articles (
  id          BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  title       VARCHAR(200)    NOT NULL             COMMENT '文章标题',
  content     LONGTEXT                             COMMENT '正文内容',
  author_id   INT UNSIGNED    NOT NULL             COMMENT '作者ID',
  status      TINYINT         NOT NULL DEFAULT 1   COMMENT '1=草稿 2=发布',
  view_count  INT UNSIGNED    NOT NULL DEFAULT 0   COMMENT '浏览数',
  created_at  DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at  DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP
                               ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_author (author_id),
  KEY idx_status_created (status, created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='文章表';

常用 ALTER 语句

-- 新增列
ALTER TABLE articles ADD COLUMN tags VARCHAR(200) DEFAULT '' AFTER title;

-- 修改列类型
ALTER TABLE articles MODIFY COLUMN view_count BIGINT UNSIGNED NOT NULL DEFAULT 0;

-- 添加索引
ALTER TABLE articles ADD INDEX idx_title (title(50));

-- 删除列
ALTER TABLE articles DROP COLUMN tags;

-- 重命名表
RENAME TABLE articles TO blog_articles;
💡 生产环境建议：大表执行 ALTER 时推荐使用 pt-online-schema-change 或 MySQL 8.0 的 Instant DDL，避免锁表。

✍️ DML 增删改查

DML（Data Manipulation Language）是日常开发中使用最频繁的 SQL 操作。

INSERT

-- 单行插入
INSERT INTO articles (title, author_id, status)
VALUES ('MySQL 入门教程', 1, 2);

-- 批量插入（性能更好）
INSERT INTO articles (title, author_id, status) VALUES
  ('MySQL 索引详解', 1, 2),
  ('MySQL 事务原理', 1, 1),
  ('MySQL 性能优化', 2, 2);

-- INSERT ... ON DUPLICATE KEY UPDATE（存在则更新）
INSERT INTO article_stats (article_id, view_count)
VALUES (1, 1)
ON DUPLICATE KEY UPDATE view_count = view_count + 1;

SELECT 查询

-- 基本查询
SELECT id, title, created_at
FROM articles
WHERE status = 2
ORDER BY created_at DESC
LIMIT 10 OFFSET 0;

-- 聚合查询
SELECT
  author_id,
  COUNT(*) AS article_count,
  SUM(view_count) AS total_views,
  MAX(created_at) AS last_publish
FROM articles
WHERE status = 2
GROUP BY author_id
HAVING article_count > 5
ORDER BY total_views DESC;

UPDATE / DELETE

-- 更新
UPDATE articles
SET status = 2, updated_at = NOW()
WHERE id = 1;

-- 删除（务必带 WHERE 条件！）
DELETE FROM articles WHERE id = 100;

-- 清空表（不可回滚，比 DELETE 快）
TRUNCATE TABLE draft_cache;
⚠️ 生产安全：执行 UPDATE/DELETE 前，建议先用 SELECT 确认影响行数。可开启 sql_safe_updates=1 禁止无 WHERE 的全表操作。

⚡ 索引原理

索引是 MySQL 性能优化的核心，本质上是一种加速查找的数据结构（B+Tree 或 Hash）。

B+Tree 索引结构

InnoDB 使用 B+Tree 实现索引，所有数据存在叶节点，叶节点之间用双向链表相连，非常适合范围查询。

索引类型

类型说明特点
主键索引（聚簇索引）PRIMARY KEY叶节点存放完整行数据，每表只有一个
普通索引（二级索引）INDEX / KEY叶节点存主键值，需回表查询
唯一索引UNIQUE KEY值唯一，可有 NULL
联合索引INDEX(a, b, c)遵循最左前缀原则
全文索引FULLTEXT用于文本搜索，类似搜索引擎

联合索引最左前缀原则

-- 假设有联合索引 INDEX(a, b, c)

SELECT * FROM t WHERE a=1;               -- ✅ 命中
SELECT * FROM t WHERE a=1 AND b=2;      -- ✅ 命中
SELECT * FROM t WHERE a=1 AND b=2 AND c=3; -- ✅ 命中
SELECT * FROM t WHERE b=2;               -- ❌ 未命中（跳过了 a）
SELECT * FROM t WHERE a=1 AND c=3;     -- ⚠️ 只命中 a 部分

EXPLAIN 分析执行计划

EXPLAIN SELECT * FROM articles WHERE author_id = 1;

重点关注字段：type（越接近 const/ref 越好）、key（实际使用的索引）、rows（扫描行数，越小越好）、Extra（有无 "Using filesort" / "Using temporary"）。

💡 索引设计原则：区分度高的列放左边；避免在索引列上做函数运算；单表索引数量建议不超过 6 个。

🔗 JOIN 连接查询

JOIN 类型对比

类型说明结果
INNER JOIN内连接只返回两表都匹配的行
LEFT JOIN左外连接返回左表全部 + 右表匹配，右表无则 NULL
RIGHT JOIN右外连接返回右表全部 + 左表匹配
 
-- 查询文章及其作者信息（内连接）
SELECT
  a.id, a.title, a.view_count,
  u.username, u.email
FROM articles a
INNER JOIN users u ON a.author_id = u.id
WHERE a.status = 2
ORDER BY a.view_count DESC
LIMIT 20;

-- 查询所有作者的文章数（含没发过文章的，左连接）
SELECT
  u.id, u.username,
  COUNT(a.id) AS article_count
FROM users u
LEFT JOIN articles a ON u.id = a.author_id AND a.status = 2
GROUP BY u.id, u.username;

🔒 事务与锁

InnoDB 支持完整的 ACID 事务，是 MySQL 在生产环境中被广泛信任的重要原因。

事务基本用法

START TRANSACTION;

UPDATE accounts SET balance = balance - 500 WHERE id = 1;
UPDATE accounts SET balance = balance + 500 WHERE id = 2;

-- 检查无误后提交
COMMIT;

-- 如果出错则回滚
-- ROLLBACK;

事务隔离级别

隔离级别脏读不可重复读幻读说明
READ UNCOMMITTED❌❌❌最低，几乎不用
READ COMMITTED✅❌❌Oracle 默认
REPEATABLE READ✅✅⚠️MySQL 默认（InnoDB 用 MVCC 解决幻读）
SERIALIZABLE✅✅✅最严格，性能最低
💡 MySQL InnoDB 在 REPEATABLE READ 级别下，通过 MVCC + Gap Lock（间隙锁） 基本解决了幻读问题，是生产环境的最佳选择。

🔧 存储引擎对比

特性InnoDBMyISAMMemory
事务支持✅ 完整 ACID❌❌
行级锁✅❌（表锁）❌（表锁）
外键✅❌❌
崩溃恢复✅ redo log❌（需修复）❌（重启清空）
全文索引✅（5.6+）✅❌
适用场景通用，推荐默认读多写少（现已少用）临时表、缓存
💡 结论：新项目统一使用 InnoDB，MyISAM 已基本退出历史舞台。

🛠️ 常用技巧

查看当前连接和进程

SHOW PROCESSLIST;
SHOW FULL PROCESSLIST; -- 显示完整 SQL

-- 杀掉慢查询进程
KILL 1234;

查看表结构和索引

DESCRIBE articles;
SHOW CREATE TABLE articles;
SHOW INDEX FROM articles;

慢查询日志

-- 开启慢查询（临时）
SET GLOBAL slow_query_log = ON;
SET GLOBAL long_query_time = 1; -- 超过 1 秒记录

-- 查看慢查询统计
SHOW GLOBAL STATUS LIKE 'Slow_queries';

常用系统变量

SHOW VARIABLES LIKE 'innodb_buffer_pool_size'; -- 缓冲池大小
SHOW VARIABLES LIKE 'max_connections';          -- 最大连接数
SHOW VARIABLES LIKE 'character_set%';           -- 字符集配置
💡 推荐工具：日常管理可用 MySQL Workbench、DBeaver 或 Navicat，慢查询分析推荐 pt-query-digest。