当前位置: 首页 > news >正文

MySQL (四):连接查询和索引

一、连接查询

1.1 内连接(INNER JOIN)

内连接是最常用的连接类型,它基于连接条件返回两个表中匹配的所有记录

SELECT * 
FROM table1 
INNER JOIN table2 
ON table1.column = table2.column;
执行流程
  1. 驱动表选择:MySQL 优化器会根据统计信息选择数据量较小的表(小表)作为驱动表

  2. 嵌套循环机制

    • 扫描驱动表的每一行记录(小表一定整表搜索)
    • 对每一行记录,在被驱动表中查找匹配项
    • 返回所有匹配的记录组合
  3. 索引优化点

    • 当 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) 中,ONWHERE的过滤逻辑在最终结果上可能一致,但执行时机和中间过程完全不同,这直接影响 “小表” 的判断和连接效率。

1. 内连接中ONWHERE的本质区别:过滤时机

  • 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 表),ONWHERE的最终结果可能一致,但中间过程完全不同:

 
  • 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;
执行逻辑深入分析
  1. 强制扫描左表

    • 无论左表数据量多大,都会被完整扫描
    • 左表记录数直接决定了连接操作的基数
  2. 右表匹配策略

    • 对左表的每一行,在右表中查找匹配记录
    • 右表无匹配时,生成包含 NULL 的记录
左连接的性能陷阱
  1. 右表无索引导致全表扫描

    右表连接字段无索引,会对右表进行 N 次全表扫描(N 为左表记录数)
  2. 隐式类型转换

    连接字段类型不一致时,MySQL 会进行隐式转换,导致索引失效
  3. 大量 NULL 值处理

    右表匹配率低时,生成大量包含 NULL 的记录,增加内存和 IO 开销

1.3 右连接(RIGHT JOIN)

右连接与左连接对称,返回右表的所有记录及左表匹配记录。语法如下:

SELECT * 
FROM table1 
RIGHT JOIN table2 
ON table1.column = table2.column;

1.4 连接查询的索引使用规则

连接操作中,索引的使用直接决定了查询性能。以下是关键规则:

驱动表索引使用规则
  1. 全表扫描不可避免(除非有 LIMIT 等限制)
  2. 索引可用于过滤驱动表数据(WHERE 子句)
  3. 复合索引的最左前缀原则同样适用
被驱动表索引使用规则
  1. 连接字段必须有索引,否则触发全表扫描
  2. 复合索引的顺序需与连接条件一致
  3. 覆盖索引可避免回表操作,提升性能
多表连接的索引优化

当涉及三个或更多表连接时:

  1. 选择数据量最小的表作为初始驱动表
  2. 确保每个被驱动表的连接字段都有索引
  3. 考虑创建跨表的复合索引

示例

-- 三表连接示例
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分析连接查询是优化的关键步骤。重点关注以下指标:

  1. **type 列**

    • system/const:最优,通过主键或唯一索引直接访问
    • eq_ref:通过索引精确匹配
    • ref:通过普通索引查找
    • range:范围扫描
    • index:索引扫描
    • ALL:全表扫描(性能最差)
  2. key 列:显示实际使用的索引

  3. rows 列:估算的扫描行数,反映查询复杂度

  4. **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 存储引擎组织数据的核心方式,其特点:

  1. 数据与索引一体化

    • 聚集索引的叶子节点直接包含完整的数据行
    • 数据按主键顺序物理存储,查询范围数据时无需额外排序
  2. 主键即聚集索引

    • 若表定义了主键,InnoDB 会自动将其作为聚集索引
    • 若未定义主键,InnoDB 会选择第一个NOT NULL唯一键作为聚集索引
    • 若没有合适的唯一索引,InnoDB 会生成一个隐藏的 6 字节ROW_ID作为主键
  3. 页分裂与性能影响

    • 插入数据时,若新记录的主键值大于当前最大值,直接追加到尾部
    • 若主键值插入到已有数据中间,可能导致页分裂,影响性能

聚集索引的存储结构

+-------------------+
| 聚集索引 B+树      |
|                   |
| 非叶子节点:       |
|  索引键 + 指针    |
|                   |
| 叶子节点:         |
|  完整数据行       |
+-------------------+
非聚集索引(Secondary Index)

MYISAM引擎创建的索引以及辅助索引(二级索引)属于非聚集索引,其特点:

  1. 索引与数据分离

    • 非聚集索引的叶子节点存储的是索引键值和对应的主键值
    • 查询时需通过主键值回表(Clustered Index Lookup)获取完整数据
  2. 多索引并行查询

    • 一张表可以有多个非聚集索引
    • 不同查询可以同时使用不同的非聚集索引
  3. 覆盖索引优化

    • 若查询的字段全部包含在某个非聚集索引中,可直接通过索引返回结果,无需回表

非聚集索引的存储结构

+-------------------+
| 非聚集索引 B+树    |
|                   |
| 非叶子节点:       |
|  索引键 + 指针    |
|                   |
| 叶子节点:         |
|  索引键 + 主键值  |
+-------------------+

2.2 索引的逻辑分类

普通索引(Normal Index)

普通索引是最基本的索引类型,创建语法:

CREATE INDEX idx_column ON table_name(column_name);

使用限制

  • 一次查询通常只能使用一个普通索引(MySQL 会选择最优索引)
  • 索引数量过多会影响写操作性能(每次插入 / 更新 / 删除都需更新所有索引

最佳实践

  • 只为经常出现在WHEREJOINORDER BY中的字段创建索引
  • 避免创建低选择性的索引(如性别字段)
唯一索引(Unique Index)

唯一索引确保索引列不包含重复值,创建语法:

CREATE UNIQUE INDEX idx_unique ON table_name(column_name);

应用场景

  • 邮箱、用户名等需要唯一性验证的字段
  • 自动阻止重复插入,提供数据完整性保障
  • 比手动检查更高效(如先SELECTINSERT

与普通索引的性能对比

  • 唯一索引在插入时需额外检查唯一性,写操作开销略高
  • 读操作性能与普通索引无显著差异
主键索引(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 = ?无法使用索引

复合索引的顺序设计

  1. 将高选择性字段放在前面(如用户 ID、订单号)
  2. 将范围查询字段放在后面(如日期、金额)

最左前缀原则和复合索引的顺序设计的要求都是因为查询子句中必须包含复合索引创建时的第一个字段才会被使用,所以上述例子在查询中使用索引必须包含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 AGAINSTO(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;
索引优化建议
  1. 只为高频访问字段创建索引

    • 分析慢查询日志,找出经常作为查询条件的字段
    • 使用SHOW STATUS LIKE 'Handler_read%';监控索引使用情况
  2. 限制单表索引数量

    • 通常不超过 5 个索引(具体取决于业务读写比例)
    • 过多索引会导致写操作性能下降
  3. 对字符串字段使用前缀索引

    -- 只索引前10个字符
    CREATE INDEX idx_name ON users(name(10));
    
  4. 避免在索引列上使用函数或类型转换

    -- 错误:无法使用索引
    SELECT * FROM users WHERE YEAR(register_date) = 2023;-- 正确:使用索引
    SELECT * FROM users WHERE register_date >= '2023-01-01' AND register_date < '2024-01-01';
    
  5. 复合索引的顺序设计

    -- 按搜索频率设计索引顺序
    CREATE INDEX idx_search ON products(category, price, status);-- 支持以下查询:
    -- 1. WHERE category = ?
    -- 2. WHERE category = ? AND price > ?
    -- 3. WHERE category = ? AND price > ? AND status = ?
    
http://www.lqws.cn/news/581833.html

相关文章:

  • macos 安装 xcode
  • 【软考中级·软件评测师】下午题·面向对象测试之架构考点全析:分层、分布式、微内核与事件驱动
  • 基于C#的OPCServer应用开发,引用WtOPCSvr.dll
  • python | numpy小记(五):理解 NumPy 中的 `np.arccos`:反余弦函数
  • 卡片跳转到应用页面(router事件)
  • 一文详解Modbus协议原理、技术细节及软件辅助调试
  • 华为云Flexus+DeepSeek征文|​​华为云ModelArts Studio大模型 + WPS:AI智能PPT生成解决方案​
  • 基于时间策略+应用过滤的游戏防沉迷方案:技术实现与工具推荐
  • 本地服务器部署后外网怎么访问不了?内网地址映射互联网上无法连接问题的排查
  • 【Pandas】pandas DataFrame Flags
  • AR 学习:开启未来学习新视界​
  • Stable Diffusion 项目实战落地:从0到1 掌握ControlNet 第四篇 风格化字体大揭秘:从线稿到涂鸦,ControlNet让文字焕发新生
  • SQuirreL SQL:一个免费的通用数据库开发工具
  • OpenWrt | 使用 Docker 运行 iperf3
  • 2 大语言模型基础-2.2 生成式预训练语言模型GPT-2.2.2 有监督下游任务微调-Instruct-GPT强化学习奖励模型的结构改造与维度转换解析
  • AI行业深度观察:从资本竞逐到技术突破的全面解析
  • 38.docker启动python解释器,pycharm通过SSH服务直连
  • 物联网基础
  • 【Mars3d】支持的basemaps数组与layers数组的坐标系列举
  • 电脑开机加速工具,优化启动项管理
  • 感受新复古主义独特魅力,新大洲本田NS150LA上市
  • Docker从部署到实战
  • ADB 安装 APK 及处理安装弹窗的方法
  • 人工智能基石:SVM支持向量机全解析(附Python实战)
  • SpringBoot+ShardingSphere-分库分表教程(二)
  • 如何防止恶意软件感染服务器:防病毒与EDR方案
  • creo 2.0学习笔记
  • 云上攻防—Docker安全容器逃逸特权模式危险挂载
  • DORIS数据备份(HDFS)
  • 对称非对称加密,https和http,https通讯原理,Charles抓包原理