MySQL 核心知识与性能优化全解析
本文涉及的产品
RDS MySQL Serverless 基础系列，0.5-2RCU 50GB
云数据库 RDS PostgreSQL，高可用系列 2核4GB
RDS MySQL Serverless 高可用系列，价值2615元额度，1个月
简介：
我整理的这份内容涵盖了 MySQL 诸多核心知识。包括查询语句的书写与执行顺序，多表查询的连接方式及内、外连接的区别。还讲了 CHAR 和 VARCHAR 的差异，索引的类型、底层结构、聚簇与非聚簇之分，以及回表查询、覆盖索引、左前缀原则和索引失效情形，还有建索引的取舍。对比了 MyISAM 和 InnoDB 存储引擎的不同，提及性能优化的多方面方法，以及超大分页处理、慢查询定位与分析等，最后提到了锁和分库分表可参考相关资料。
一、查询语句规则
- 书写顺序：严格按
SELECT...FROM...JOIN...WHERE...GROUP BY...HAVING...ORDER BY...LIMIT的流程编写。
- 执行顺序：从
FROM子句开始，历经JOIN关联、WHERE筛选、GROUP BY分组、HAVING二次筛选、SELECT投影、DISTINCT去重、ORDER BY排序，最后通过LIMIT限制结果数量。
二、多表查询机制
- 内连接：仅返回两表中满足连接条件的匹配行，有隐式（逗号分隔表 +
WHERE条件）和显式（INNER JOIN+ON条件）两种语法。
- 外连接
- 左外连接：以左表为基准，返回左表全部数据及右表匹配项，右表无匹配时补
NULL。
- 右外连接：与左外连接对称，以右表为基准。
- 全外连接：返回两表所有行，无匹配时对应表补
NULL，MySQL 需用UNION模拟。
- 交叉连接：生成两表的笛卡尔积，结果行数为两表行数乘积。
三、数据类型辨析
- CHAR：定长字符串，不足长度时用空格填充，存取速度快，适合存储长度固定的数据。
- VARCHAR：变长字符串，按实际数据长度存储，节省空间，适合长度变化大的数据。
四、索引体系详解
- 索引类型
- 单列索引：包括普通索引（无特殊限制）、唯一索引（值唯一，可含
NULL）、主键索引（特殊唯一索引，NOT NULL）和全文索引（用于文本搜索）。
- 组合索引：多字段联合创建，遵循左前缀原则，可加速多条件查询。
- 底层结构
- B + 树：MyISAM 和 InnoDB 的默认索引结构，InnoDB 的聚簇索引将数据与主键索引存储在一起。
- 哈希索引：MEMORY 引擎支持，适用于等值查询。
五、存储引擎对比
六、查询优化策略
- 索引优化
- 避免在索引列上使用函数或运算，如
WHERE YEAR(date)=2023会导致索引失效。
- 减少
!=、NOT IN、OR等操作符的使用，可用UNION替代OR。
- 利用覆盖索引，将查询字段包含在组合索引中，避免回表。
- SQL 优化
- 遵循组合索引的左前缀原则，确保查询条件从索引最左字段开始。
- 用
EXPLAIN分析 SQL 执行计划，重点关注type（访问类型）和Extra（额外信息）字段。
- 架构优化
- 读写分离：主库写、从库读，分担负载。
- 分库分表：垂直分库（按业务拆分）、水平分表（按规则拆分数据）。
- 缓存机制：使用 Redis 等缓存热点数据。
七、慢查询处理
- 开启慢查询日志：通过修改
my.cnf配置或执行SET GLOBAL命令，设置long_query_time阈值。
- 分析工具：用
pt-query-digest解析慢查询日志，定位问题 SQL。
- 分页优化：避免大偏移量分页，改用
WHERE id > max_id或延迟关联。
八、锁机制
- 表级锁：MyISAM 默认，操作时锁定整张表，并发度低。
- 行级锁：InnoDB 默认，仅锁定操作的行，并发度高，支持事务隔离级别。
- 间隙锁：InnoDB 在范围查询时使用，防止幻读。
