MySQL (四):连接查询和索引
一、连接查询
1.1 内连接(INNER JOIN)
内连接是最常用的连接类型,它基于连接条件返回两个表中匹配的所有记录。
SELECT *
FROM table1
INNER JOIN table2
ON table1.column = table2.column;
执行流程
-
驱动表选择:MySQL 优化器会根据统计信息选择数据量较小的表(小表)作为驱动表
-
嵌套循环机制:
- 扫描驱动表的每一行记录(小表一定整表搜索)
- 对每一行记录,在被驱动表中查找匹配项
- 返回所有匹配的记录组合
-
索引优化点:
- 当 ON 子句中的连接字段存在索引时,可大幅加速匹配过程
- 无索引时,需对被驱动表进行全表扫描,复杂度为 O (n*m)
ON 与 WHERE 子句的本质区别
特性 | ON 子句 | WHERE 子句 |
---|---|---|
执行阶段 | 连接过程中过滤数据 | 连接完成后过滤结果集 |
索引利用 | 可利用索引加速连接 | 无法优化连接过程,仅过滤最终结果 |
对 NULL 处理 | 参与连接条件判断(可能影响结果集) | 过滤掉包含 NULL 的记录 |
示例对比:
-- 示例1:ON子句过滤
SELECT *
FROM orders
INNER JOIN customers
ON orders.customer_id = customers.id
AND customers.status = 'active';-- 示例2:WHERE子句过滤
SELECT *
FROM orders
INNER JOIN customers
ON orders.customer_id = customers.id
WHERE customers.status = 'active';
虽然两种方法结果一样,但是,在示例 1 中,MySQL 会在连接过程中直接过滤掉状态非 active 的客户,可能减少连接的数据量;而示例 2 会在连接完成后再进行过滤,可能产生更多的中间结果。
在内连接(INNER JOIN) 中,
ON
和WHERE
的过滤逻辑在最终结果上可能一致,但执行时机和中间过程完全不同,这直接影响 “小表” 的判断和连接效率。1. 内连接中
ON
和WHERE
的本质区别:过滤时机
ON
子句:属于 “连接条件”,作用于连接操作之前。
它会先对参与连接的两张表(如 A 和 B)分别应用ON
中的过滤条件,只保留符合条件的记录,再将过滤后的子集进行连接。
例如:A INNER JOIN B ON A.id = B.a_id AND B.status = 'active'
,会先过滤掉 B 表中status != 'active'
的记录,再与 A 表连接。
WHERE
子句:属于 “全局过滤”,作用于连接操作之后。
它会先将 A 和 B 表按连接条件(如A.id = B.a_id
)完成全量连接,生成临时的中间结果集,再对这个结果集应用WHERE
条件过滤。
例如:A INNER JOIN B ON A.id = B.a_id WHERE B.status = 'active'
,会先连接 A 和 B 的所有匹配记录,再从中间结果中删掉status != 'active'
的行。2. 为什么内连接结果可能相同,但效率差异大?
内连接的最终结果是 “两张表中匹配的记录”,因此当过滤条件只涉及其中一张表时(如只过滤 B 表),
ON
和WHERE
的最终结果可能一致,但中间过程完全不同:
- 用
ON
过滤:先过滤掉 B 表中不符合条件的记录,再与 A 表连接,中间结果集更小。- 用
WHERE
过滤:先连接 A 和 B 的所有匹配记录(可能包含大量不需要的行),再过滤,中间结果集更大,占用更多内存和 CPU。3. 对 “小表” 判断的影响
MySQL 优化器在选择连接顺序时,会优先选择 “小表” 作为驱动表(减少外层循环次数)。这里的 “小表” 指的是经过过滤后记录数更少的表,而过滤条件的位置直接影响过滤后的表大小:
- 若过滤条件写在
ON
中:过滤在连接前执行,“小表” 是过滤后的子表(如 B 表经ON
过滤后只剩 100 行,则 B 是小表)。- 若过滤条件写在
WHERE
中:过滤在连接后执行,“小表” 是原表中记录数更少的表(即使 B 表有 100 万行,但 A 表只有 100 行,仍会优先用 A 作为驱动表,导致连接时处理大量无效数据)。
1.2 左连接(LEFT JOIN)
左连接返回左表的所有记录,以及右表中匹配的记录。若右表无匹配,则对应字段填充 NULL。语法如下:
SELECT *
FROM table1
LEFT JOIN table2
ON table1.column = table2.column;
执行逻辑深入分析
-
强制扫描左表:
- 无论左表数据量多大,都会被完整扫描
- 左表记录数直接决定了连接操作的基数
-
右表匹配策略:
- 对左表的每一行,在右表中查找匹配记录
- 右表无匹配时,生成包含 NULL 的记录
左连接的性能陷阱
-
右表无索引导致全表扫描:
若右表连接字段无索引,会对右表进行 N 次全表扫描(N 为左表记录数) -
隐式类型转换:
连接字段类型不一致时,MySQL 会进行隐式转换,导致索引失效 -
大量 NULL 值处理:
右表匹配率低时,生成大量包含 NULL 的记录,增加内存和 IO 开销
1.3 右连接(RIGHT JOIN)
右连接与左连接对称,返回右表的所有记录及左表匹配记录。语法如下:
SELECT *
FROM table1
RIGHT JOIN table2
ON table1.column = table2.column;
1.4 连接查询的索引使用规则
连接操作中,索引的使用直接决定了查询性能。以下是关键规则:
驱动表索引使用规则
- 全表扫描不可避免(除非有 LIMIT 等限制)
- 索引可用于过滤驱动表数据(WHERE 子句)
- 复合索引的最左前缀原则同样适用
被驱动表索引使用规则
- 连接字段必须有索引,否则触发全表扫描
- 复合索引的顺序需与连接条件一致
- 覆盖索引可避免回表操作,提升性能
多表连接的索引优化
当涉及三个或更多表连接时:
- 选择数据量最小的表作为初始驱动表
- 确保每个被驱动表的连接字段都有索引
- 考虑创建跨表的复合索引
示例:
-- 三表连接示例
SELECT *
FROM orders
INNER JOIN customers ON orders.customer_id = customers.id
INNER JOIN products ON orders.product_id = products.id
WHERE customers.country = 'China' AND products.category = 'Electronics';-- 优化索引
CREATE INDEX idx_customer_country ON customers(country);
CREATE INDEX idx_product_category ON products(category);
CREATE INDEX idx_order_customer_product ON orders(customer_id, product_id);
1.5 连接查询的执行计划分析
使用EXPLAIN
分析连接查询是优化的关键步骤。重点关注以下指标:
-
**type 列**:
- system/const:最优,通过主键或唯一索引直接访问
- eq_ref:通过索引精确匹配
- ref:通过普通索引查找
- range:范围扫描
- index:索引扫描
- ALL:全表扫描(性能最差)
-
key 列:显示实际使用的索引
-
rows 列:估算的扫描行数,反映查询复杂度
-
**Extra 列**:
- Using index:使用覆盖索引,无需回表
- Using temporary:使用临时表,性能警告
- Using filesort:使用文件排序,性能警告
优化示例:
EXPLAIN
SELECT *
FROM orders
LEFT JOIN customers
ON orders.customer_id = customers.id
WHERE customers.status = 'active';
若执行计划显示对 customers 表进行全表扫描,应检查status
字段是否有索引,并考虑创建复合索引(status, id)
以支持覆盖索引。
二、索引
2.1 索引的物理分类
聚集索引(Clustered Index)
聚集索引是 InnoDB 存储引擎组织数据的核心方式,其特点:
-
数据与索引一体化:
- 聚集索引的叶子节点直接包含完整的数据行
- 数据按主键顺序物理存储,查询范围数据时无需额外排序
-
主键即聚集索引:
- 若表定义了主键,InnoDB 会自动将其作为聚集索引
- 若未定义主键,InnoDB 会选择第一个
NOT NULL
的唯一键作为聚集索引 - 若没有合适的唯一索引,InnoDB 会生成一个隐藏的 6 字节
ROW_ID
作为主键
-
页分裂与性能影响:
- 插入数据时,若新记录的主键值大于当前最大值,直接追加到尾部
- 若主键值插入到已有数据中间,可能导致页分裂,影响性能
聚集索引的存储结构:
+-------------------+
| 聚集索引 B+树 |
| |
| 非叶子节点: |
| 索引键 + 指针 |
| |
| 叶子节点: |
| 完整数据行 |
+-------------------+
非聚集索引(Secondary Index)
MYISAM引擎创建的索引以及辅助索引(二级索引)属于非聚集索引,其特点:
-
索引与数据分离:
- 非聚集索引的叶子节点存储的是索引键值和对应的主键值
- 查询时需通过主键值回表(Clustered Index Lookup)获取完整数据
-
多索引并行查询:
- 一张表可以有多个非聚集索引
- 不同查询可以同时使用不同的非聚集索引
-
覆盖索引优化:
- 若查询的字段全部包含在某个非聚集索引中,可直接通过索引返回结果,无需回表
非聚集索引的存储结构:
+-------------------+
| 非聚集索引 B+树 |
| |
| 非叶子节点: |
| 索引键 + 指针 |
| |
| 叶子节点: |
| 索引键 + 主键值 |
+-------------------+
2.2 索引的逻辑分类
普通索引(Normal Index)
普通索引是最基本的索引类型,创建语法:
CREATE INDEX idx_column ON table_name(column_name);
使用限制:
- 一次查询通常只能使用一个普通索引(MySQL 会选择最优索引)
- 索引数量过多会影响写操作性能(每次插入 / 更新 / 删除都需更新所有索引)
最佳实践:
- 只为经常出现在
WHERE
、JOIN
、ORDER BY
中的字段创建索引 - 避免创建低选择性的索引(如性别字段)
唯一索引(Unique Index)
唯一索引确保索引列不包含重复值,创建语法:
CREATE UNIQUE INDEX idx_unique ON table_name(column_name);
应用场景:
- 邮箱、用户名等需要唯一性验证的字段
- 自动阻止重复插入,提供数据完整性保障
- 比手动检查更高效(如先
SELECT
再INSERT
)
与普通索引的性能对比:
- 唯一索引在插入时需额外检查唯一性,写操作开销略高
- 读操作性能与普通索引无显著差异
主键索引(Primary Key Index)
主键索引是一种特殊的唯一索引,不允许NULL
值,创建语法:
CREATE TABLE users (id INT PRIMARY KEY,name VARCHAR(50)
);
InnoDB 与 MyISAM 的差异:
- InnoDB:
- 强制要求表必须有主键,否则自动生成隐藏主键
- 主键索引即聚集索引,数据按主键顺序存储
- MyISAM:
- 允许无主键表,数据与索引分开存储
- 主键索引与普通索引结构相同,只是唯一性约束更强
单列索引与多列索引
- 单列索引:仅对一个字段创建索引
- 多列索引(复合索引):对多个字段创建联合索引
CREATE INDEX idx_multi ON table_name(col1, col2, col3);
最左前缀原则:
- 查询必须从索引的最左列开始
WHERE col1 = ?
和WHERE col1 = ? AND col2 = ?
可使用索引WHERE col2 = ?
和WHERE col1 = ? OR col2 = ?
无法使用索引
复合索引的顺序设计:
- 将高选择性字段放在前面(如用户 ID、订单号)
- 将范围查询字段放在后面(如日期、金额)
最左前缀原则和复合索引的顺序设计的要求都是因为查询子句中必须包含复合索引创建时的第一个字段才会被使用,所以上述例子在查询中使用索引必须包含col1(
WHERE col1 = ? OR col2 = ?时or,可能不包含col1,所以不会使用索引
)
全文索引(Full-Text Index)
全文索引专门用于文本搜索,创建语法:
CREATE FULLTEXT INDEX idx_text ON articles(content);
查询方式:
SELECT * FROM articles WHERE MATCH(content) AGAINST('keyword');
与 LIKE 的性能对比:
操作 | 时间复杂度 | 适用场景 |
---|---|---|
LIKE 'keyword%' | O(n) | 前缀匹配,数据量较小 |
LIKE '%keyword%' | O(n) | 任意位置匹配,无索引可用 |
MATCH AGAINST | O(log n) | 全文搜索,数据量大 |
2.3 索引的创建与删除
创建索引
-- 创建普通索引
CREATE INDEX idx_age ON users(age);-- 创建唯一索引
CREATE UNIQUE INDEX idx_email ON users(email);-- 创建多列索引
CREATE INDEX idx_name_age ON users(name, age);-- 在创建表时定义索引
CREATE TABLE products (id INT PRIMARY KEY,name VARCHAR(50),price DECIMAL(10,2),INDEX idx_price(price)
);
删除索引
DROP INDEX idx_name ON users;
索引优化建议
-
只为高频访问字段创建索引:
- 分析慢查询日志,找出经常作为查询条件的字段
- 使用
SHOW STATUS LIKE 'Handler_read%';
监控索引使用情况
-
限制单表索引数量:
- 通常不超过 5 个索引(具体取决于业务读写比例)
- 过多索引会导致写操作性能下降
-
对字符串字段使用前缀索引:
-- 只索引前10个字符 CREATE INDEX idx_name ON users(name(10));
-
避免在索引列上使用函数或类型转换:
-- 错误:无法使用索引 SELECT * FROM users WHERE YEAR(register_date) = 2023;-- 正确:使用索引 SELECT * FROM users WHERE register_date >= '2023-01-01' AND register_date < '2024-01-01';
-
复合索引的顺序设计:
-- 按搜索频率设计索引顺序 CREATE INDEX idx_search ON products(category, price, status);-- 支持以下查询: -- 1. WHERE category = ? -- 2. WHERE category = ? AND price > ? -- 3. WHERE category = ? AND price > ? AND status = ?