前言为什么选择 MySQLMySQL 作为开源关系型数据库的标杆凭借轻量、高效、稳定的特性广泛应用于 Web 开发、大数据存储等场景。本文从零基础入门到高阶优化整理了系统的学习笔记适合编程初学者、后端开发工程师快速掌握 MySQL 核心技能。一、MySQL 基础入门必掌握1. 环境搭建与连接安装方式本地安装Windows官网 MSI 包、Linuxyum/apt 命令容器部署docker run -p 3306:3306 -e MYSQL_ROOT_PASSWORD123456 mysql:8.0连接工具命令行mysql -u root -p -h 127.0.0.1 -P 3306可视化Navicat、DataGrip、MySQL Workbench2. 数据库与表操作DDL-- 1. 数据库操作CREATE DATABASE IF NOT EXISTS test_db CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;USE test_db; -- 切换数据库DROP DATABASE IF EXISTS test_db;-- 2. 表结构操作CREATE TABLE user (id INT PRIMARY KEY AUTO_INCREMENT, -- 自增主键username VARCHAR(50) NOT NULL UNIQUE, -- 唯一非空age TINYINT UNSIGNED DEFAULT 18, -- 无符号默认值create_time DATETIME DEFAULT CURRENT_TIMESTAMP -- 自动填充时间) ENGINEInnoDB DEFAULT CHARSETutf8mb4;-- 修改表结构ALTER TABLE user ADD COLUMN email VARCHAR(100) AFTER age;ALTER TABLE user MODIFY COLUMN age INT UNSIGNED;ALTER TABLE user DROP COLUMN email;3. 数据增删改查DML-- 插入数据INSERT INTO user (username, age) VALUES (zhangsan, 20), (lisi, 22);-- 查询数据重点SELECT username, age FROM user WHERE age 20 ORDER BY age DESC LIMIT 10;SELECT * FROM user GROUP BY age HAVING COUNT(*) 1; -- 分组筛选-- 更新与删除UPDATE user SET age 23 WHERE username zhangsan;DELETE FROM user WHERE age 18;4. 约束与索引基础约束类型PRIMARY KEY主键、UNIQUE唯一、NOT NULL非空、FOREIGN KEY外键索引创建CREATE INDEX idx_username ON user(username); -- 普通索引CREATE UNIQUE INDEX idx_email ON user(email); -- 唯一索引二、MySQL 进阶核心提升效率1. 复杂查询技巧多表连接-- 内连接取交集SELECT u.username, o.order_no FROM user u INNER JOIN order o ON u.id o.user_id;-- 左连接保留左表全部SELECT u.username, o.order_no FROM user u LEFT JOIN order o ON u.id o.user_id;子查询与关联查询-- 子查询SELECT * FROM user WHERE id IN (SELECT user_id FROM order WHERE amount 100);-- 关联子查询逐行匹配SELECT * FROM user u WHERE EXISTS (SELECT 1 FROM order o WHERE o.user_id u.id);2. 索引优化性能关键索引类型聚簇索引InnoDB 默认主键索引数据与索引存储在一起非聚簇索引普通索引存储主键地址优化原则避免索引失效WHERE age 1 20函数操作、WHERE username LIKE %san前缀模糊联合索引最左前缀原则CREATE INDEX idx_age_name ON user(age, username)优先匹配 age索引分析EXPLAIN SELECT * FROM user WHERE age 20;查看执行计划3. 事务与 ACID 特性事务操作BEGIN; -- 开启事务UPDATE user SET balance balance - 100 WHERE id 1;UPDATE order SET status 1 WHERE user_id 1;COMMIT; -- 提交事务-- ROLLBACK; -- 回滚异常时ACID 特性原子性Atomicity要么全执行要么全回滚一致性Consistency事务前后数据完整性不变隔离性Isolation多事务并发互不干扰持久性Durability事务提交后数据永久存储4. 并发控制与隔离级别隔离级别从低到高读未提交Read Uncommitted可能出现脏读读已提交Read Committed避免脏读可能出现不可重复读MySQL 默认可重复读Repeatable Read避免不可重复读可能出现幻读InnoDB 通过 MVCC 解决串行化Serializable完全隔离性能最低设置隔离级别SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;三、高阶实战技巧生产环境必备1. 数据库设计规范三大范式1NF字段原子性不可拆分2NF消除部分依赖依赖主键全部字段3NF消除传递依赖不依赖非主键字段反范式设计高频查询字段冗余如订单表存储用户名避免联表2. 性能优化实战SQL 优化避免SELECT *只查询需要字段大表分页优化SELECT * FROM user WHERE id 1000 LIMIT 10;避免全表扫描配置优化调整连接数max_connections 1000my.cnf缓存优化query_cache_size 64M查询缓存MySQL 8.0 已移除3. 备份与恢复逻辑备份mysqldumpmysqldump -u root -p test_db test_db_backup.sql -- 备份mysql -u root -p test_db _db_backup.sql -- 恢复物理备份XtraBackup适合大数据库支持增量备份4. 分库分表与读写分离分库分表水平分表按用户 ID 哈希user_1, user_2...垂直分表拆分大字段如用户表拆分出 user_profile 表读写分离主库Master写操作从库Slave读操作通过主从复制同步数据四、常见问题与避坑指南中文乱码确保数据库、表、字段字符集均为 utf8mb4支持 emoji主键自增冲突分库分表时使用雪花算法生成全局唯一 ID死锁避免长事务合理设计索引减少锁竞争慢查询开启慢查询日志slow_query_log ON分析slow.log优化 SQL总结MySQL 学习需注重 “基础扎实 实战优化”基础部分重点掌握 SQL 语法、约束与索引进阶部分聚焦查询优化、事务隔离、性能调优高阶部分则需结合生产环境场景理解分库分表、读写分离等架构设计。建议多动手实践通过EXPLAIN分析执行计划积累优化经验逐步从 “会用” 到 “精通”。
MySQL从入门到精通，帮你全方位学习数据库
前言为什么选择 MySQLMySQL 作为开源关系型数据库的标杆凭借轻量、高效、稳定的特性广泛应用于 Web 开发、大数据存储等场景。本文从零基础入门到高阶优化整理了系统的学习笔记适合编程初学者、后端开发工程师快速掌握 MySQL 核心技能。一、MySQL 基础入门必掌握1. 环境搭建与连接安装方式本地安装Windows官网 MSI 包、Linuxyum/apt 命令容器部署docker run -p 3306:3306 -e MYSQL_ROOT_PASSWORD123456 mysql:8.0连接工具命令行mysql -u root -p -h 127.0.0.1 -P 3306可视化Navicat、DataGrip、MySQL Workbench2. 数据库与表操作DDL-- 1. 数据库操作CREATE DATABASE IF NOT EXISTS test_db CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;USE test_db; -- 切换数据库DROP DATABASE IF EXISTS test_db;-- 2. 表结构操作CREATE TABLE user (id INT PRIMARY KEY AUTO_INCREMENT, -- 自增主键username VARCHAR(50) NOT NULL UNIQUE, -- 唯一非空age TINYINT UNSIGNED DEFAULT 18, -- 无符号默认值create_time DATETIME DEFAULT CURRENT_TIMESTAMP -- 自动填充时间) ENGINEInnoDB DEFAULT CHARSETutf8mb4;-- 修改表结构ALTER TABLE user ADD COLUMN email VARCHAR(100) AFTER age;ALTER TABLE user MODIFY COLUMN age INT UNSIGNED;ALTER TABLE user DROP COLUMN email;3. 数据增删改查DML-- 插入数据INSERT INTO user (username, age) VALUES (zhangsan, 20), (lisi, 22);-- 查询数据重点SELECT username, age FROM user WHERE age 20 ORDER BY age DESC LIMIT 10;SELECT * FROM user GROUP BY age HAVING COUNT(*) 1; -- 分组筛选-- 更新与删除UPDATE user SET age 23 WHERE username zhangsan;DELETE FROM user WHERE age 18;4. 约束与索引基础约束类型PRIMARY KEY主键、UNIQUE唯一、NOT NULL非空、FOREIGN KEY外键索引创建CREATE INDEX idx_username ON user(username); -- 普通索引CREATE UNIQUE INDEX idx_email ON user(email); -- 唯一索引二、MySQL 进阶核心提升效率1. 复杂查询技巧多表连接-- 内连接取交集SELECT u.username, o.order_no FROM user u INNER JOIN order o ON u.id o.user_id;-- 左连接保留左表全部SELECT u.username, o.order_no FROM user u LEFT JOIN order o ON u.id o.user_id;子查询与关联查询-- 子查询SELECT * FROM user WHERE id IN (SELECT user_id FROM order WHERE amount 100);-- 关联子查询逐行匹配SELECT * FROM user u WHERE EXISTS (SELECT 1 FROM order o WHERE o.user_id u.id);2. 索引优化性能关键索引类型聚簇索引InnoDB 默认主键索引数据与索引存储在一起非聚簇索引普通索引存储主键地址优化原则避免索引失效WHERE age 1 20函数操作、WHERE username LIKE %san前缀模糊联合索引最左前缀原则CREATE INDEX idx_age_name ON user(age, username)优先匹配 age索引分析EXPLAIN SELECT * FROM user WHERE age 20;查看执行计划3. 事务与 ACID 特性事务操作BEGIN; -- 开启事务UPDATE user SET balance balance - 100 WHERE id 1;UPDATE order SET status 1 WHERE user_id 1;COMMIT; -- 提交事务-- ROLLBACK; -- 回滚异常时ACID 特性原子性Atomicity要么全执行要么全回滚一致性Consistency事务前后数据完整性不变隔离性Isolation多事务并发互不干扰持久性Durability事务提交后数据永久存储4. 并发控制与隔离级别隔离级别从低到高读未提交Read Uncommitted可能出现脏读读已提交Read Committed避免脏读可能出现不可重复读MySQL 默认可重复读Repeatable Read避免不可重复读可能出现幻读InnoDB 通过 MVCC 解决串行化Serializable完全隔离性能最低设置隔离级别SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;三、高阶实战技巧生产环境必备1. 数据库设计规范三大范式1NF字段原子性不可拆分2NF消除部分依赖依赖主键全部字段3NF消除传递依赖不依赖非主键字段反范式设计高频查询字段冗余如订单表存储用户名避免联表2. 性能优化实战SQL 优化避免SELECT *只查询需要字段大表分页优化SELECT * FROM user WHERE id 1000 LIMIT 10;避免全表扫描配置优化调整连接数max_connections 1000my.cnf缓存优化query_cache_size 64M查询缓存MySQL 8.0 已移除3. 备份与恢复逻辑备份mysqldumpmysqldump -u root -p test_db test_db_backup.sql -- 备份mysql -u root -p test_db _db_backup.sql -- 恢复物理备份XtraBackup适合大数据库支持增量备份4. 分库分表与读写分离分库分表水平分表按用户 ID 哈希user_1, user_2...垂直分表拆分大字段如用户表拆分出 user_profile 表读写分离主库Master写操作从库Slave读操作通过主从复制同步数据四、常见问题与避坑指南中文乱码确保数据库、表、字段字符集均为 utf8mb4支持 emoji主键自增冲突分库分表时使用雪花算法生成全局唯一 ID死锁避免长事务合理设计索引减少锁竞争慢查询开启慢查询日志slow_query_log ON分析slow.log优化 SQL总结MySQL 学习需注重 “基础扎实 实战优化”基础部分重点掌握 SQL 语法、约束与索引进阶部分聚焦查询优化、事务隔离、性能调优高阶部分则需结合生产环境场景理解分库分表、读写分离等架构设计。建议多动手实践通过EXPLAIN分析执行计划积累优化经验逐步从 “会用” 到 “精通”。
