MySQL 数据库操作入门指南（2）-优快云博客

本文链接：https://blog.youkuaiyun.com/Ming_chao/article/details/148952113

📚 索引深度解析与优化策略

索引核心语法与类型

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

-- 创建唯一索引
CREATE UNIQUE INDEX idx_email ON users(email);

-- 创建组合索引（最左前缀原则）
CREATE INDEX idx_name_age ON users(name, age);

-- 查看表索引
SHOW INDEX FROM users;

-- 删除索引
DROP INDEX idx_name ON users;

🔥 索引深度分析

索引数据结构
- B + 树索引：MySQL 默认索引结构，适合范围查询，叶子节点存储完整数据指针
- 哈希索引：仅支持等值查询，不支持范围查询（如 MEMORY 引擎默认使用）
- 全文索引：MySQL 5.6 + 支持 InnoDB 全文索引，用于文本搜索
组合索引最佳实践
- 遵循最左前缀原则：如组合索引(name, age)可用于WHERE name='A'或WHERE name='A' AND age>20，但无法用于单独查询age
- 避免冗余索引：若已有(a,b)索引，无需再建(a)索引
索引失效场景
- 条件中使用LIKE '%前缀'（如LIKE '%abc'）
- 字段类型隐式转换（如WHERE id='123'，id 为 INT 类型）
- 使用OR连接条件且部分条件无索引
- 对索引字段使用函数（如WHERE YEAR(create_time)=2023）

覆盖索引优化
当查询字段均在索引中时，无需回表查询，例如：

-- 假设idx_name_age包含name和age字段
SELECT name, age FROM users WHERE name='张三';

🔒 事务与锁机制深度解析

事务核心语法

-- 开启事务
START TRANSACTION;

-- 执行操作
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;

-- 提交事务
COMMIT;

-- 回滚事务
ROLLBACK;

⚙️ 事务特性与隔离级别

ACID 特性
- 原子性（Atomicity）：事务中的操作要么全成功，要么全失败
- 一致性（Consistency）：事务执行前后数据完整性不变
- 隔离性（Isolation）：多事务并发执行时互不干扰
- 持久性（Durability）：提交后数据永久保存
隔离级别对比

隔离级别脏读不可重复读幻读 MySQL 默认
READ UNCOMMITTED 是是是否
READ COMMITTED 否是是否
REPEATABLE READ 否否是是
SERIALIZABLE 否否否否

隔离级别	脏读	不可重复读	幻读	MySQL 默认
READ UNCOMMITTED	是	是	是	否
READ COMMITTED	否	是	是	否
REPEATABLE READ	否	否	是	是
SERIALIZABLE	否	否	否	否

幻读解决方案
InnoDB 通过间隙锁（Gap Lock） 解决幻读：

sql

-- 锁定id在(10,20)之间的范围，防止插入新数据
SELECT * FROM users WHERE id BETWEEN 10 AND 20 FOR UPDATE;

🧰 存储引擎深度对比

引擎特性对比表

特性	InnoDB（默认）	MyISAM	Memory
事务支持	支持	不支持	不支持
外键支持	支持	不支持	不支持
行级锁	支持	仅表级锁	仅表级锁
存储位置	数据 + 索引合并存储	数据 (.MYD)+ 索引 (.MYI)	内存中
适用场景	OLTP（事务型业务）	只读报表、统计查询	临时缓存、高频查询

🔧 引擎选择最佳实践

电商订单系统：选 InnoDB，保证事务一致性
日志统计系统：选 MyISAM，提升查询性能（无事务需求）
实时缓存数据：选 Memory，但需配合持久化机制

🔍 查询优化实战

执行计划分析

sql

-- 查看查询执行计划
EXPLAIN SELECT * FROM users WHERE name='张三' AND age>25;

📊 执行计划关键参数解析

type 字段（查询类型，性能从差到好）
- ALL：全表扫描（最差）
- index：索引全扫描
- range：索引范围扫描
- ref：非唯一索引匹配
- eq_ref：唯一索引匹配（最佳）
key 字段：实际使用的索引名，若为NULL则未使用索引
rows 字段：预估扫描行数，数值越小性能越好

🌰 优化案例

慢查询场景：

-- 慢查询：全表扫描
SELECT * FROM orders WHERE create_time BETWEEN '2023-01-01' AND '2023-12-31';

优化方案：

-- 创建时间索引
CREATE INDEX idx_create_time ON orders(create_time);

-- 优化后查询（使用range扫描）
SELECT * FROM orders WHERE create_time BETWEEN '2023-01-01' AND '2023-12-31';

🛠️ 高级管理操作

备份与恢复

-- 全库备份
mysqldump -u root -p --all-databases > all_db_backup.sql

-- 单库备份
mysqldump -u root -p db_name > db_backup.sql

-- 表级备份
mysqldump -u root -p db_name table1 table2 > tables_backup.sql

-- 恢复数据
mysql -u root -p db_name < db_backup.sql

📈 性能监控

-- 查看数据库状态
SHOW STATUS;

-- 查看慢查询日志（需先开启）
SET GLOBAL slow_query_log = ON;
SET GLOBAL long_query_time = 1; -- 超过1秒的查询记录为慢查询

-- 查看当前连接
SHOW PROCESSLIST;

🚨 生产环境注意事项

字符集陷阱
- 全局字符集设为utf8mb4，避免 emoji 存储异常
```
-- 查看当前字符集
SHOW VARIABLES LIKE 'character_set_%';
```
大表 DDL 风险
- 避免直接执行ALTER TABLE，使用pt-online-schema-change等工具
- 大表删除前先备份：CREATE TABLE old_table LIKE big_table; INSERT old_table SELECT * FROM big_table;
权限最小化原则
- 禁止使用GRANT ALL，按业务需求拆分权限
- 定期清理无用用户：SELECT User, Host FROM mysql.user WHERE User NOT IN ('root', 'admin');