极客时间mysql进阶训练营
拼课》》》❤ jzit.top/2862/
MySQL进阶技巧详解
1. 索引与优化
索引作用：
索引是数据库的“目录”，通过构建数据结构（如B-Tree、Hash等）加速查询。合理使用索引可显著提升性能，但需注意索引的维护成本。
常见索引类型：
-
B-Tree（默认）：
-
适用于范围查询（如
WHERE age > 18）、排序和分组。 -
示例：
CREATE INDEX idx_age ON users(age);
-
-
FULLTEXT：
-
用于全文搜索（如博客文章、商品描述）。
-
示例：
CREATE FULLTEXT INDEX idx_content ON articles(content);
-
-
HASH：
-
适用于等值查询（如
WHERE id = 123），但无法用于范围查询。 -
示例：
CREATE INDEX idx_hash ON users(id) USING HASH;
-
优化建议：
-
覆盖索引：让查询仅访问索引，避免回表。
-
示例：
SELECT id, name FROM users WHERE age = 25;（索引包含age, id, name）
-
-
避免冗余索引：定期清理无用索引，减少写入开销。
-
分析查询计划：使用
EXPLAIN查看索引使用情况。
2. 视图、存储过程和函数
视图（View）：
-
定义：虚拟表，封装复杂查询逻辑。
-
用途：简化查询、权限控制（如只暴露部分字段）。
-
示例：
sql
CREATE VIEW active_users AS
SELECT id, name FROM users WHERE status = 'active';
-
SELECT * FROM active_users;
存储过程（Stored Procedure）：
-
定义：预编译的SQL代码块，可包含逻辑判断和事务。
-
用途：提高代码复用性、减少网络传输。
-
示例：
sql
DELIMITER //
CREATE PROCEDURE GetUserById(IN userId INT)
BEGIN
SELECT * FROM users WHERE id = userId;
END //
DELIMITER ;
-
CALL GetUserById(1);
函数（Function）：
-
定义：返回单一值的存储过程，常用于计算。
-
与存储过程的区别：函数可嵌套在SQL中，存储过程不可。
-
示例：
sql
CREATE FUNCTION CalculateAge(birthdate DATE) RETURNS INT
BEGIN
RETURN TIMESTAMPDIFF(YEAR, birthdate, CURDATE());
END;
-
SELECT CalculateAge('1990-01-01');
3. 事务与锁
事务（Transaction）：
-
ACID属性：
-
原子性：事务要么全成功，要么全失败。
-
一致性：事务前后数据保持一致。
-
隔离性：事务间互不干扰（通过锁实现）。
-
持久性：事务提交后数据永久保存。
-
-
使用示例：
sql
START TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
-
COMMIT; -- 或 ROLLBACK;
锁机制：
-
表级锁：
-
锁定整个表，并发性低，但开销小。
-
示例：
LOCK TABLES users WRITE;
-
-
行级锁：
-
仅锁定目标行，并发性高，但开销大。
-
示例：
SELECT * FROM users WHERE id = 1 FOR UPDATE;
-
-
隔离级别（影响锁的行为）：
-
READ UNCOMMITTED：最低隔离，可能读到脏数据。
-
READ COMMITTED：避免脏读，但可能出现不可重复读。
-
REPEATABLE READ（MySQL默认）：避免不可重复读，但可能产生幻读。
-
SERIALIZABLE：最高隔离，性能最低。
-
总结与应用建议
-
索引：优先为高频查询字段（如WHERE、JOIN条件）创建索引，但需平衡读写性能。
-
视图/存储过程：
-
视图适合简化复杂查询，存储过程适合封装业务逻辑。
-
避免过度使用存储过程，以免增加调试难度。
-
事务：
-
确保事务粒度适中，避免长时间持有锁。
-
根据业务需求选择合适的隔离级别（如电商场景常用
REPEATABLE READ）。
通过合理运用这些技巧，可显著提升MySQL的性能和可维护性。
