ToB企服应用市场:ToB评测及商务社交产业平台

标题: MySQL 单表访问方法详解 [打印本页]

---

作者: 南飓风    时间: 5 天前
标题: MySQL 单表访问方法详解
MySQL 单表访问方法详解：高效查询之道

焦点思想： MySQL 执行单表查询的目标是尽可能高效地从表中获取所需数据。访问方法（Access Method）决定了 MySQL 如何扫描表中的记录，是全表扫描还是利用索引，直接影响查询性能。理解并选择符合的访问方法是查询优化的底子。
一、 查询执行底子

• 查询优化器****与执行计划： MySQL Server 中的查询优化器解析查询语句后生成执行计划。执行计划决定了利用哪些索引、表的连接顺序等。存储引擎根据执行计划执行查询并返回结果。理解查询执行原理是优化慢查询的关键。
  
• 单表查询： 本文聚焦于单表查询（FROM 子句后只有一个表），这是最底子的查询范例。
  

二、 访问方法 (Access Method) 概念

访问方法是指 MySQL 访问表数据的方式，决定了查询优化器选择哪种策略检索数据。

• 全表扫描 (ALL)： 逐行扫描全部记录。实用于无索引或查询条件无法利用索引的情况，效率低，尤其对于大表。
  
• 索引访问： 利用索引快速定位数据。包括针对主键/唯一索引的等值查询、平常二级索引的等值查询、索引列的范围查询以及索引全扫描等。
  

三、 详细访问方法 (从最优到最差)

以下访问方法按效率从高到低排序，类似于查询优化器选择路径的优先级：

• const (或 system)：常数级别访问
  

• 原理： 利用主键 (PRIMARY KEY) 或唯一二级索引 (UNIQUE INDEX) 与常数进行等值匹配，且最多返回一条记录。system 是 const 的特例，表只有一行记录时利用。
  
• 机制： B+ 树索引快速查找，直接定位。由于索引的有序性，查找接近 O(1)。查询优化器预先计算索引查找路径。
  
• 应用场景： 根据用户 ID (主键) 查用户信息；根据唯一订单号 (唯一索引) 查订单。
  
• 性能： 极速，少量 I/O。
  
• 示例：
  

2. SELECT * FROM users WHERE id = 1; -- id 是主键
4. SELECT * FROM orders WHERE order_no = 'unique_123'; -- order_no 是唯一索引

复制代码

限定 ：仅实用于主键列或唯一二级索引列与常数的等值比较，且当唯一二级索引列值为 NULL 时不可用此方法。

• eq_ref：唯一索引等值引用 (连接查询)
  

• 原理： 连接查询中，被驱动表通过主键或唯一二级索引等值匹配访问，且保证对于驱动表的每条记录，被驱动表最多只返回一条匹配记录。
  
• 机制： 索引关联，驱动表结果驱动被驱动表查询。被驱动表通过唯一索引快速查找。
  
• 应用场景： 订单表和用户表关联，通过用户 ID (用户表主键) 关联订单表；一对一关联。
  
• 性能： 高效，少量 I/O。
  
• 示例：
  

2. SELECT o.*, u.* FROM orders o JOIN users u ON o.user_id = u.id WHERE o.order_id = 100; -- users.id 是主键

复制代码

• ref：非唯一索引等值引用
  

• 原理： 利用非唯一二级索引进行等值匹配，可能返回多条记录。
  
• 机制： 索引范围扫描，找到多条索引记录。ref 是等值匹配 ( =, IN)，range 是范围匹配 (>, <, BETWEEN)。
  
• 应用场景： 根据商品分类 ID (非唯一索引) 查商品；根据用户角色 (非唯一索引) 查用户。
  
• 性能： 比全表扫描高效，但不如 const 和 eq_ref。
  
• 示例：
  

2. SELECT * FROM products WHERE category_id = 5; -- category_id 是非唯一索引

复制代码

注意事项 ：若二级索引列值为 NULL，只能利用 ref 访问方法而非 const；对于团结索引，只要最左边的一连索引列与常数等值比较就可能采用 ref 方法，否则不能称为 ref。

• fulltext：全文索引
  

• 原理: 查询条件涉及MATCH AGAINST语法，且利用全文索引。
  
• 机制： 基于倒排索引，将文本拆分成词语并记录。支持干系性排序（如 TF-IDF 或 BM25）。
  
• 应用场景: 商品搜索（根据名称、描述）；博客文章搜索（根据标题、内容）。
  
• 性能: 针对文本搜索优化，比LIKE '%keyword%'效率高。
  
• 示例:
  

2. SELECT * FROM articles WHERE MATCH(title,content) AGAINST ('MySQL 优化');

复制代码

• ref_or_null：索引等值引用或 NULL
  

• 原理： 类似于 ref，但多了对 NULL 值的处理。查询条件是索引列等值匹配 + IS NULL。
  
• 机制： 先索引等值查找 (同 ref)，再扫描索引的 NULL 值记录。索引列需允许 NULL。
  
• 应用场景： 查询非必填字段为特定值或为空的记录。
  
• 性能： 略低于 ref。
  
• 示例：
  

2. SELECT * FROM users WHERE email = 'test@example.com' OR email IS NULL; -- email 有索引且允许 NULL

复制代码

• index_merge：索引合并
  

• 原理： 一个表可以利用多个索引完成查询，MySQL 将多个索引扫描结果合并。
  
• 机制： 优化器判断可利用多个索引分别过滤，合并结果 (Intersection, Union, Sort-Union)。
  
• index_merge_intersection: 索引求交集 (AND)。
  
• index_merge_union: 索引求并集 (OR)。
  
• index_merge_sort_union: 先排序，再求并集。
  
• 应用场景： 复杂查询条件，可利用多个索引独立过滤。
  
• 性能： 通常优于全表扫描，但取决于索引选择性和合并策略。不妥利用可能比单个索引低效。
  
• 示例：
  

Intersection 合并 示例：查询 single_table 表中 key1 = ‘a’ AND key3 = ‘b’ 的记录，可利用 idx_key1 和 idx_key3 索引进行 Intersection 合并。别的，主键列可进行范围匹配的情况也可利用此合并方法。
Union 合并 示例：查询 single_table 表中 key1 = ‘a’ OR key3 = ‘b’ 的记录，可利用 idx_key1 和 idx_key3 索引进行 Union 合并。
Sort-Union 合并 示例：查询 single_table 表中 key1 < ‘a’ OR key3 > ‘z’ 的记录，可利用 idx_key1 和 idx_key3 索引进行 Sort-Union 合并。

2. SELECT * FROM products WHERE price < 100 OR category_id = 5; -- price 和 category_id 都有索引

复制代码

团结索引替代索引合并 ：在可能的情况下，利用团结索引可替代索引合并，提高查询效率。例如查询 single_table 表中 key1 = ‘a’ AND key3 = ‘b’ 的记录，可通过创建团结索引 idx_key1_key3(key1, key3) 来直接利用团结索引查询，克制索引合并操纵。

• range：索引范围扫描
  

• 原理： 利用索引进行范围查询 (BETWEEN, >, <, IN, OR 等)。
  
• 机制： 索引有序性，定位范围起止，扫描范围内的索引记录。可处理复杂范围条件。
  
• 应用场景： 查询价格范围内的商品；查询时间段内的订单；IN 列表查询。
  
• 性能： 比全表扫描高效，但范围越大，效率越低。
  
• 示例：
  

2. SELECT * FROM products WHERE price BETWEEN 50 AND 100;
4. SELECT * FROM orders WHERE order_date >= '2023-01-01' AND order_date < '2023-02-01';

复制代码

范围区间确定 ：对于复杂搜索条件，需分析哪些条件可利用同一索引，并确定该索引对应的范围区间。如多个条件通过 AND 连接，取各条件范围区间的交集；通过 OR 连接则取并集。若部门条件无法利用索引，则在确定范围区间时将其替换为 TRUE。

• index：索引全扫描
  

• 原理： 扫描整个索引树，但只需要索引中的列数据。
  
• 机制： 遍历索引树。索引通常比数据表小且有序，index 比全表扫描快。常发生在 “覆盖索引” 情况。
  
• 应用场景： 统计记录数 (索引包罗全部行)；查询只需要索引列数据。
  
• 性能： 比全表扫描快，但仍需扫描整个索引，低于 range。
  
• 示例：
  

界说 ：当查询列表中的列全部包罗在某个二级索引中，且查询条件仅涉及该索引列时，可直接遍历二级索引记录获取结果，无需回表操纵。因二级索引记录较小，且克制了回表开销，查询效率较高。

2. SELECT COUNT(*) FROM orders; -- 若优化器选择索引扫描
4. SELECT order_id FROM orders; -- order_id 是主键或有索引

复制代码

• ALL：全表扫描
  

• 原理： 无法利用索引，扫描全部记录。
  
• 机制： 逐行读取，检查是否满足条件。效率最低。
  
• 应用场景： 表很小；查询条件无法利用索引 (无索引，或索引失效)。
  
• 性能： 极低，尤其对于大表，应克制。
  
• 示例：
  

2. SELECT * FROM products WHERE description LIKE '%keyword%'; -- description 无索引或前导模糊匹配

复制代码

四、 注意事项

• 二级索引+回表： 一般，查询只能用单个二级索引。优化器选扫描行数少的二级索引查询，再回表获取完备记录，根据剩余条件过滤。例如：SELECT * FROM single_table WHERE key1 = 'abc' AND key2 > 1000;，可能先用idx_key1定位key1 = 'abc'，再回表根据key2 > 1000过滤。
  

五、 总结与优化建议

• 索引是关键： 高效访问方法多依赖索引。公道创建和利用索引是焦点。
  
• 克制全表扫描： ALL 通常是性能瓶颈。尽量优化查询，利用索引。
  
• 利用 EXPLAIN： 分析 SQL 执行计划，看 MySQL 选择的访问方法，判断是否高效，及如何优化。
  
• 关注索引范例和选择性： 不同索引 (B-tree, Hash, Fulltext) 实用不同场景。索引选择性 (区分度) 越高越好。
  
• 优化 SQL： 编写高效 SQL，克制索引失效 (如索引列上用函数)。
  

六、 电商网站数据存储应用示例

• const： 用户登录，根据用户名 (唯一索引) 和暗码 (通常不直接索引，但用户名唯一索引可快速定位)。
  
• eq_ref： 订单详情，订单表关联订单项表，通过订单 ID (订单项表外键，订单表主键)。
  
• ref： 商品分类，根据分类 ID (非唯一索引) 查询商品。
  
• range： 商品价格筛选，查询价格范围内的商品。
  
• fulltext: 商品搜索功能。
  
• index / ALL (需优化克制)： 报表统计，若计划不妥可能全表扫描或索引全扫描，需索引优化和查询改写。
  

七、 数据备份与恢复模子 (补充)

数据备份与恢复虽非直接访问方法，但保证数据安全和可用性，间接提升访问效率。

• 备份策略：
  
• 逻辑备份 (Logical Backup)： 导出 SQL (如 mysqldump)，灵活但慢。
  
• 物理备份 (Physical Backup)： 复制数据文件 (如 MySQL Enterprise Backup, Xtrabackup)，快但灵活性低。
  
• 全量备份 (Full Backup)： 备份全部数据。
  
• 增量备份 (Incremental Backup)： 备份前次全量/增量备份后厘革的数据。
  
• 差别备份 (Differential Backup)： 备份前次全量备份后厘革的数据。
  
• 恢复策略：
  
• 完全恢复： 恢复到备份时间点。
  
• 时间点恢复 (with Binary Logs)： 团结备份和二进制日志，恢复到恣意时间点。
  
• 备份模子选择： 根据数据紧张性、RTO、RPO、存储空间等选择。焦点业务数据库通常采用物理全量 + 增量 + 二进制日志。
  

总结： 数据备份与恢复保证数据安全和可用性，是数据库稳固运行的底子。定期备份和恢复演练是 DBA 的紧张职责。

免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。

---

欢迎光临 ToB企服应用市场:ToB评测及商务社交产业平台 (https://dis.qidao123.com/)

Powered by Discuz! X3.4