MySQL JOIN与索引一、MySQL JOIN类型 1.1 基本概念 JOIN (INNER JOIN) 只返回两个

一、MySQL JOIN类型

1.1 基本概念

JOIN (INNER JOIN)

只返回两个表中都有匹配记录的数据
如果某条记录在任一表中没有对应匹配，则不会出现在结果中

LEFT JOIN (LEFT OUTER JOIN)

返回左表的所有记录，以及右表中匹配的记录
如果右表没有匹配记录，右表字段显示为NULL

RIGHT JOIN (RIGHT OUTER JOIN)

返回右表的所有记录，以及左表中匹配的记录
如果左表没有匹配记录，左表字段显示为NULL

LEFT JOIN 和 RIGHT JOIN 在大多数情况下确实可以互换，只需要：

调换表的位置
调换JOIN的方向（LEFT ↔ RIGHT）
保持ON条件不变

但在实际开发中，建议：

优先使用LEFT JOIN，代码更易读
让主表（需要保留完整数据的表）作为LEFT JOIN的左表
保持团队编码风格的一致性

1.2 实际场景对比

假设我们有两个表：用户表(users)和订单表(orders)

-- 用户表
users: id, name, email
1, 张三, zhang@email.com
2, 李四, li@email.com  
3, 王五, wang@email.com

-- 订单表
orders: id, user_id, amount
101, 1, 500
102, 1, 300
103, 2, 800

场景1：查询有订单的用户信息

SELECT u.name, o.amount 
FROM users u 
JOIN orders o ON u.id = o.user_id;

结果影响：

只返回张三和李四的信息（因为他们有订单）
王五没有订单，不会出现在结果中
适用于只关心有业务往来的用户

场景2：查询所有用户及其订单信息

SELECT u.name, o.amount 
FROM users u 
LEFT JOIN orders o ON u.id = o.user_id;

结果影响：

返回所有用户信息
王五也会出现，但amount字段为NULL
适用于用户分析报告，需要知道哪些用户没有消费

场景3：查询所有订单及对应用户信息

SELECT u.name, o.amount 
FROM users u 
RIGHT JOIN orders o ON u.id = o.user_id;

结果影响：

返回所有订单信息
如果有订单但用户被删除，用户信息显示为NULL
适用于订单数据完整性检查

1.3 业务影响分析

数据完整性方面：

INNER JOIN可能丢失数据，适合严格匹配场景
LEFT JOIN保证主表数据完整，适合主从关系明确的查询
RIGHT JOIN较少使用，通常可以通过调换表顺序用LEFT JOIN替代

性能方面：

INNER JOIN通常性能最好，数据量最小
LEFT/RIGHT JOIN需要处理NULL值，数据量较大
建议在连接字段上建立索引优化性能

二、MySQL索引

2.1 索引为什么能加快查询速度

基本原理

索引本质上是一个有序的数据结构，就像书的目录一样。没有索引时，数据库需要全表扫描；有索引时，可以快速定位到目标数据的位置。

底层数据结构

MySQL主要使用B+Tree作为索引结构：

B+Tree特点：多路平衡查找树，叶子节点存储完整数据
查找效率：时间复杂度O(log n)，比线性查找O(n)快很多
磁盘友好：减少磁盘I/O次数，一次读取一个页面的数据

速度提升示例

-- 没有索引：需要扫描100万条记录
SELECT * FROM users WHERE phone = '13812345678';

-- 有索引：只需要3-4次磁盘I/O就能定位
-- 100万数据的B+Tree高度通常只有3-4层

2.2 索引类型详解

按数据结构分类

B-Tree索引（最常用）

CREATE INDEX idx_name ON users(name);
-- 适用于：等值查询、范围查询、排序

Hash索引

-- Memory引擎支持
-- 只适用于等值查询，不支持范围查询

全文索引

CREATE FULLTEXT INDEX idx_content ON articles(content);
-- 适用于文本搜索

按字段数量分类

单列索引

CREATE INDEX idx_email ON users(email);

复合索引（联合索引）

CREATE INDEX idx_name_age ON users(name, age);
-- 遵循最左前缀原则

按功能分类

主键索引

-- 自动创建，唯一且非空
PRIMARY KEY (id)

唯一索引

CREATE UNIQUE INDEX idx_email ON users(email);
-- 保证数据唯一性

普通索引

CREATE INDEX idx_status ON orders(status);
-- 最基本的索引类型

2.3 索引设计原则

1. 最左前缀原则

核心机制： 对于复合索引 (name, age, city)，索引在物理存储上按照 name → age → city 的顺序排列：

索引结构示例：
张三, 20, 北京
张三, 25, 上海  
张三, 30, 广州
李四, 22, 北京
李四, 28, 深圳
王五, 26, 杭州

能用到索引的查询：

-- 索引：(name, age, city)
SELECT * FROM users WHERE name = '张三';
SELECT * FROM users WHERE name = '张三' AND age = 25;
SELECT * FROM users WHERE name = '张三' AND age = 25 AND city = '北京';

不能用到索引的查询：

-- 跳过最左字段，无法使用索引
SELECT * FROM users WHERE age = 25;
SELECT * FROM users WHERE city = '北京';

为什么必须从最左开始？ 因为索引首先按 name 排序，age 在不同 name 下是无序的。查询 WHERE age = 25 时，数据库无法利用B+Tree快速定位，必须全表扫描。所以如果有索引(a,b)，通常不需要单独的索引(a)

2. 选择性原则

选择重复值少的字段建索引：

-- 好的选择：手机号、邮箱（选择性高）
CREATE INDEX idx_phone ON users(phone);

-- 差的选择：性别、状态（选择性低）
-- 如果status只有0,1两个值，索引效果很差

3. 索引长度优化

-- 对于长字符串，使用前缀索引
CREATE INDEX idx_title ON articles(title(20));

-- 检查前缀区分度
SELECT COUNT(DISTINCT LEFT(title, 10)) / COUNT(*) as selectivity 
FROM articles;

2.4 实际应用场景

电商系统索引设计

-- 用户表
CREATE INDEX idx_phone ON users(phone);           -- 登录查询
CREATE INDEX idx_email ON users(email);           -- 邮箱登录
CREATE INDEX idx_created_at ON users(created_at); -- 时间范围查询

-- 订单表
CREATE INDEX idx_user_id ON orders(user_id);                    -- 查询用户订单
CREATE INDEX idx_status_created ON orders(status, created_at);  -- 状态+时间查询
CREATE INDEX idx_order_no ON orders(order_no);                  -- 订单号查询

复合索引实际案例

-- 查询条件：WHERE city = '北京' AND age > 25 AND salary > 10000
-- 索引设计：
CREATE INDEX idx_city_age_salary ON employees(city, age, salary);

-- 原因：city等值查询放最前，age和salary范围查询按选择性排序

2.5 索引使用注意事项

避免索引失效的情况

-- 1. 使用函数
SELECT * FROM users WHERE UPPER(name) = 'ZHANG';  -- 索引失效
SELECT * FROM users WHERE name = 'zhang';         -- 索引有效

-- 2. 类型转换
SELECT * FROM users WHERE phone = 13812345678;    -- phone是varchar，索引失效
SELECT * FROM users WHERE phone = '13812345678';  -- 索引有效

-- 3. 前导模糊查询
SELECT * FROM users WHERE name LIKE '%张%';       -- 索引失效
SELECT * FROM users WHERE name LIKE '张%';        -- 索引有效

-- 4. 负向查询
SELECT * FROM users WHERE status != 1;            -- 索引效果差
SELECT * FROM users WHERE status = 0;             -- 索引有效

索引使用规则总结

-- 索引：(name, age, city)

-- ✅ 完全匹配最左前缀
WHERE name = '张三'
WHERE name = '张三' AND age = 25
WHERE name = '张三' AND age = 25 AND city = '北京'

-- ✅ 最左前缀 + 范围查询
WHERE name = '张三' AND age > 20
WHERE name = '张三' AND age > 20 AND city = '北京'  -- city无法使用索引

-- ❌ 跳过最左字段
WHERE age = 25
WHERE city = '北京'  
WHERE age = 25 AND city = '北京'

-- ❌ 不连续使用
WHERE name = '张三' AND city = '北京'  -- 只有name能用到索引，city用不到

2.6 创建唯一索引时遇到重复数据怎么处理

表中已有重复数据时创建唯一索引

-- 假设users表已有重复邮箱数据
INSERT INTO users (name, email) VALUES 
('张三', 'test@email.com'),
('李四', 'test@email.com');  -- 重复邮箱

-- 尝试创建唯一索引会失败
CREATE UNIQUE INDEX idx_email ON users(email);

结果

ERROR 1062 (23000): Duplicate entry 'test@email.com' for key 'idx_email'

解决方案

-- 方案1：先清理重复数据，再创建索引
-- 查找重复数据
SELECT email, COUNT(*) as count 
FROM users 
GROUP BY email 
HAVING COUNT(*) > 1;

-- 删除重复数据（保留ID最小的）
DELETE u1 FROM users u1
INNER JOIN users u2 
WHERE u1.id > u2.id AND u1.email = u2.email;

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

-- 方案2：使用IGNORE关键字（MySQL会自动选择保留哪条）
ALTER IGNORE TABLE users ADD UNIQUE INDEX idx_email (email);
-- 注意：这个语法在新版MySQL中被废弃

插入数据时违反唯一约束

直接插入重复数据

-- 假设已有唯一索引
CREATE UNIQUE INDEX idx_email ON users(email);

-- 插入重复数据会报错
INSERT INTO users (name, email) VALUES ('王五', 'existing@email.com');

结果

ERROR 1062 (23000): Duplicate entry 'existing@email.com' for key 'idx_email'

解决方案

-- 假设已有唯一索引
CREATE UNIQUE INDEX idx_email ON users(email);

-- 插入重复数据会报错
INSERT INTO users (name, email) VALUES ('王五', 'existing@email.com');

-- 如果重复就更新现有记录
INSERT INTO users (name, email) 
VALUES ('王五', 'existing@email.com')
ON DUPLICATE KEY UPDATE name = VALUES(name);

-- 相当于：如果邮箱存在，就更新name字段

-- 如果重复就删除旧记录，插入新记录
REPLACE INTO users (name, email) 
VALUES ('王五', 'existing@email.com');

-- 注意：这会删除整条旧记录，可能丢失其他字段数据