索引优化MAX()

最新推荐文章于 2025-06-06 15:25:30 发布
最新推荐文章于 2025-06-06 15:25:30 发布
阅读量599 收藏
点赞数
分类专栏： Mysql

普通的 MAX() SQL执行计划：

这里写图片描述

从执行计划中可以看到：本次SQL查询检索了payment表（全表扫描，FTS），一共检索了16086行数据。意味着从磁盘中读取了16086行数据至MySQL服务端内存中进行最大值判断（有数据IO）。

为payment_date建立索引以提高查询效率：

这里写图片描述

再次执行相同的MAX()操作并查看执行计划：

这里写图片描述

table：NULL，rows：NULL，即本次查询没有检索数据库中的表。并且Extra提示Select tables optimized away，说明该查询已经是最优化的方法，即可以直接在索引中进行查找并返回，根本不涉及数据表的IO。

值得注意的是：

这里写图片描述

虽然为payment_date建立了索引，但是查找payment_date与查找MAX(payment_date)是不一样的。查找payment_date要检索表（使用索引检索），共16086行（磁盘IO）。

ClickHouse数据库的索引优化与查询加速技巧
AI天才研究院
07-18 1104
随着企业数字化转型加速，秒级响应的实时报表、用户行为分析等需求激增。ClickHouse作为专为OLAP设计的数据库，理论上能处理亿级数据，但实际性能表现与索引设计强相关。本文聚焦索引优化这一核心环节，覆盖从索引原理到实战调优的全链路知识，帮助开发者解决“查询慢”的痛点。本文从“生活化场景”切入索引原理→拆解ClickHouse特有索引类型→通过代码实战演示优化过程→总结10条可落地的加速技巧→展望未来索引技术趋势，形成“原理-实践-经验”的完整知识闭环。稀疏索引。
【MySQL】之索引与索引优化
王廷云的博客
06-17 944
索引（Index）是帮助MySQL高效获取数据的数据结构。索引是数据结构。索引的目的在于提高查询效率，就好像是字典一样，如果要查“mysql”这个单词，我们肯定需要定位到m字母，然后从下往下找到y字母，再找到剩下的sql。如果没有索引，那么你可能需要 a - z，如果我想找到Java开头的单词呢？或者Oracle开头的单词呢？是不是觉得如果没有索引，这个事情根本无法完成？我们可以简单理解为排好序的快速查找结构。
Oracle性能优化-利用索引特性进行max/min优化（IS NOT NULL的优化）
dream_like的专栏
04-19 1516
1. 问题提出 客户系统有这样一条SQL，脱敏后如下： SELECT NVL(MAX(T1.CREATED),SYSDATE) FROM DUAL LEFT JOIN TEST11 T1 ON T1.OWNER=’OUTLN’ AND OBJECT_TYPE IS NOT NULL; SQL是TEST11表和DUAL表相关联，WHERE条件中OWNER字段有索引，SQL走了该字段索引范围扫描的执行计划，单次执行逻辑读2117。SQL执行频率非常高，一分钟数万次。执行计划如下： 2. 初步优化...
mysql使用函数怎么走索引_慎用mysql max()函数，可通过走索引替换
weixin_34765290的博客
01-18 690
mysql max() 函数的需扫描where条件过滤后的所有行：在测试环境中重现：测试版本：Server version: 5.1.58-log MySQL Community Server (GPL)testtable表中的索引mysql> show index from testtable;+-----------+------------+------------+-...
mysql max走索引,MIN和MAX的MySQL索引
weixin_30838923的博客
01-18 497
Could anyone clarify this point from the official MySQL documentationIndexes are used ... To find the MIN() or MAX() value for a specific indexed column key_col. This is optimized by a preprocessor th...
max优化脚本
05-18
本插件运用与max插件，优化模型（整理模型ID号，清空没有用的贴图）
SQL极致优化案例：利用索引特性进行max/min优化
Enmotech的博客
04-01 727
不要错过更多干货文章，点击上方蓝字关注我们IS NOT NULL的优化1. 问题提出客户系统有这样一条SQL，脱敏后如下：SELECT NVL(MAX(T1.CREATED),SYSDAT...
sql学习 索引特性之有序与存列值优化max.sql
02-11
sql学习 索引特性之有序与存列值优化max.sql
MySQL索引优化：全文索引
weixin_43833540的博客
06-06 956
在MySQL中，是一种专门为文本数据设计的索引类型，用于高效处理自然语言搜索（如文章内容、评论、关键词匹配等）。与传统的B-Tree索引相比，全文索引在处理大量文本数据时性能更优，且支持更复杂的搜索语法。CHARVARCHARTEXTMySQL*"数据库设计"MySQL内置分词器对中文支持不佳（默认按空格分词），需使用第三方插件（如ngram全文索引的维护成本高于普通索引，写入操作可能变慢。MATCHUPPER()WHERE全文索引通常比原数据大，需预留足够磁盘空间。
19.读书笔记收获不止Oracle之 索引MAX和MIN优化
badman250的专栏
11-05 959
19.读书笔记收获不止Oracle之 索引MAX和MIN优化 1.  示例一 SQL> drop table t purge; SQL> create table t as select * fromdba_objects; SQL> create index idx1_object_id on t(object_id); 使用MAX看看，能不能用到索引。 set autotrace
索引易错点：索引与max()，min()
jlds123的专栏
11-17 7057
前面说完了索引与count(*)的关系，现在来讨论另外一种聚合查询max()，min()与索引的关系，大家觉得这个聚合查询能用的到索引吗？ 通过上一小节的学习后，可能有人会回答：“可以用得上，但是索引列必须要建主键或者要写where column is not null就可以用到了。”对于这样的回答应该值得肯定，非常正确！看来前面没白讲了。不过用上了什么样的索引扫描方式呢？上一小节的方式是IND
慎用mysql max()函数，可通过走索引替换
UFO
11-11 2287
mysql max() 函数的需扫描where条件过滤后的所有行： 在测试环境中重现： 测试版本：Server version:         5.1.58-log MySQL Community Server (GPL) testtable表中的索引 mysql> show index from testtable; +-----------+
oracle学习之索引误区
钦
08-22 598
索引误区 索引
MySQL之查询：max()和min()函数与索引利用
u012393450的博客
04-18 2万+
       通常大部分索引的数据结构均为B-Tree，该结构按顺序直接存储该索引字段的值，因此直接可以再树的两端获取到最大值与最小值，例如下面这条SQL，我想统计出一个公司员工ID的最大值与最小值： SELECT MAX(id),MIN(id) FROM staff_info EXPLAIN分析结果如下：        综述：可以看到在索引字段使用MAX，MIN聚集函数时，可以直接取...
oracle 分区表的相关索引
Uncle.Cui的技术博客
06-04 428
index
Oracle之索引，视图，序列
A0911X的博客
11-21 1932
cache|no cache:指定序列缓存，默认缓存20个,oracle会预先在内存中放置一组指定大小的序列，当使用完这些序列后再生成下一组，这样会存取得快些，但当数据库关闭等情况时，下一次再生成序列时可能会使序列间断，不是一串连续的号，当不是特别需要连续的序列时最好指定；给name字段加上索引，oracle就会对全表进行一次搜索，将每条记录的name值进行升序排列，然后构建索引条目，存储在索引段中，搜索name=’scott’ ，直接找索引排序后的结果。--这样它就会使用索引。--不能直接取当前值。
各种Oracle索引类型介绍
热门推荐
极客神殿
03-03 3万+
逻辑上：Single column 单行索引 Concatenated 多行索引 Unique 唯一索引 NonUnique 非唯一索引 Function-based函数索引 Domain 域索引物理上：Partitioned 分区索引 NonPartitioned 非分区索引 B-tree： Normal 正常型B树 Rever Key 反转型B树 Bitmap 位图索引索引结构：B-tree：
SqlServer索引及优化详解
oArvin1234的专栏
12-17 592
（一）深入浅出理解索引结构         实际上，您可以把索引理解为一种特殊的目录。微软的SQL SERVER提供了两种索引：聚集索引（clustered index，也称聚类索引、簇集索引）和非聚集索引（nonclustered index，也称非聚类索引、非簇集索引）。下面，我们举例来说明一下聚集索引和非聚集索引的区别：   其实，我们的汉语字典的正文本身就是一个聚集索引。比如，
h2 索引优化
最新发布
08-08
<think>我们正在讨论H2数据库的索引优化策略。根据引用内容，H2数据库在索引使用上存在一些特殊情况，比如即使使用了索引，查询也可能缓慢（引用[4]），因为优化器可能无法正确识别索引的使用场景（如排序和限制结果集）。此外，索引的有序性对性能有重要影响（引用[3]）。同时，我们也要考虑一般数据库索引的最佳实践（引用[1]、[2]）。 因此，我们需要结合H2数据库的特性，给出索引优化的最佳实践。 ### 一、H2数据库索引特性 1. **索引结构**：H2的索引采用B树结构（引用[5]），索引文件内部偏移量（offsets）以有序数组形式存储（引用[3]），这有利于磁盘顺序读取。 2. **优化器限制**：H2优化器在决定是否使用索引进行排序时，要求排序字段必须是索引的第一列（引用[4]）。如果查询条件中包含索引第一列的等值条件，但排序字段不是索引的后续列，优化器可能无法利用索引进行排序，导致全索引扫描。 ### 二、H2索引优化策略 1. **索引设计原则** - **最左前缀匹配**：与MySQL类似，H2的复合索引遵循最左前缀原则。例如，复合索引`(A, B, C)`只能用于： - `WHERE A = ?` - `WHERE A = ? AND B = ?` - `WHERE A = ? AND B = ? AND C = ?` 但无法用于`WHERE B = ?`或`WHERE C = ?`。 - **覆盖索引**：尽可能让索引包含查询所需的所有列，避免回表查询。例如： ```sql -- 创建覆盖索引 CREATE INDEX idx_covering ON orders (customer_id, order_date) INCLUDE (total_amount); ``` - **前缀索引**：对于长字符串列，使用前缀索引（H2支持）减少索引大小： ```sql CREATE INDEX idx_name ON table (left(column, 20)); ``` 2. **避免索引失效场景** - **隐式类型转换**：确保查询条件与索引列类型一致。例如，字符串索引列避免用数字查询： ```sql -- 错误：索引失效 SELECT * FROM users WHERE name = 123; -- 正确：类型匹配 SELECT * FROM users WHERE name = '123'; ``` - **避免索引列计算**：不要在索引列上使用函数或表达式： ```sql -- 错误：索引失效 SELECT * FROM logs WHERE YEAR(create_time) = 2023; -- 正确：重写为范围查询 SELECT * FROM logs WHERE create_time >= '2023-01-01' AND create_time < '2024-01-01'; ``` - **LIKE查询优化**：避免前导通配符（如`%text`）： ```sql -- 索引失效 SELECT * FROM products WHERE name LIKE '%Apple%'; -- 可考虑全文索引（如H2的`CREATE ALIAS IF NOT EXISTS FT_INIT FOR "org.h2.fulltext.FullText.init"`） ``` 3. **排序与分页优化** - **利用索引排序**：确保`ORDER BY`字段与索引顺序一致，且第一列在查询条件中等值使用： ```sql -- 索引 (status, create_time) -- 有效：第一列status等值，第二列排序 SELECT * FROM orders WHERE status = 'SHIPPED' ORDER BY create_time DESC; -- 无效：跳过status直接排序 SELECT * FROM orders ORDER BY create_time DESC; -- 全表扫描 ``` - **分页优化**：结合`LIMIT`和索引避免大偏移量扫描： ```sql -- 低效：偏移量越大越慢 SELECT * FROM orders ORDER BY id LIMIT 100000, 10; -- 高效：记录上一页最后ID SELECT * FROM orders WHERE id > 100000 ORDER BY id LIMIT 10; ``` 4. **统计信息更新** H2不会自动更新统计信息，需手动执行： ```sql ANALYZE TABLE table_name; -- 更新单表统计信息 ANALYZE SAMPLE_SIZE 1000; -- 设置采样行数 ``` ### 三、性能问题诊断 1. **执行计划分析** 使用`EXPLAIN`检查索引使用情况： ```sql EXPLAIN SELECT * FROM orders WHERE customer_id = 123; ``` 输出示例： ``` SELECT CUSTOMER_ID = 123 >> 使用索引 `IDX_CUSTOMER`：扫描范围 [123, 123] ``` 关注： - `>> 使用索引 ...`：表示命中索引 - `>> 扫描行数 ...`：评估索引过滤性 2. **强制索引（谨慎使用）** 当优化器误判时，可强制索引（H2语法）： ```sql SELECT * FROM table_name /*+ INDEX(index_name) */ WHERE ...; ``` **适用场景**： - 统计信息滞后导致优化器选择全表扫描 - 复合索引未被正确选择（如排序场景） ### 四、索引维护 1. **重建索引** 定期重建碎片化索引： ```sql ALTER INDEX index_name RECREATE; ``` 2. **监控索引大小** 查询索引占用空间： ```sql SELECT * FROM INFORMATION_SCHEMA.INDEXES; ``` ### 五、决策流程图 ```mermaid graph TD A[查询慢] --> B{EXPLAIN分析} B -->|未用索引| C[检查索引设计] C -->|无合适索引| D[创建新索引] C -->|有索引但未用| E{条件是否导致失效？} E -->|是| F[重写查询] E -->|否| G[更新统计信息] G --> H[重新EXPLAIN] H -->|仍不用索引| I[考虑强制索引] I --> J[测试验证] B -->|使用索引但慢| K[检查排序/分页] K -->|排序字段未在索引中| L[修改索引包含排序字段] K -->|分页偏移大| M[优化分页策略] ``` ### 六、最佳实践总结 1. **设计阶段**： - 对高频查询字段创建复合索引，并确保最左前缀匹配查询条件 - 排序字段加入索引末尾 2. **开发阶段**： - 避免索引列计算和类型转换 - 分页查询使用连续ID替代`OFFSET` 3. **运维阶段**： - 定期更新统计信息（`ANALYZE`） - 重建碎片化索引 > ⚠️ **注意事项**： > - H2的全文检索（Full-Text Search）适用于文本模糊查询，比`LIKE`更高效 > - 内存模式（`jdbc:h2:mem:`）索引性能极高，但文件模式（`jdbc:h2:file:`）需关注磁盘I/O §§ 相关问题 §§ 1. H2数据库如何实现全文检索？ 2. 如何监控H2数据库的索引碎片情况？ 3. H2数据库内存模式与文件模式的性能差异有哪些？ 4. H2数据库的`ANALYZE`命令具体收集哪些统计信息？</think>### H2数据库索引优化最佳实践与方法 #### 一、索引设计原则 1. **最左前缀匹配原则** - 复合索引`(A,B,C)`只能用于`A=?`、`A=? AND B=?`等场景，无法用于`B=?`单独查询 - **解决方案**：调整查询条件顺序或拆分索引 ```sql -- 有效使用索引 SELECT * FROM orders WHERE account_id = 100 AND status = 'SHIPPED'; ``` 2. **高区分度列优先** - 选择区分度高的列作为索引首列，公式：$区分度 = \frac{count(distinct\ col)}{count(*)}$ - 状态/性别等低区分度列（接近0）不适合作为索引首列[^2] 3. **避免过长的索引键** - 对长字符串使用前缀索引（H2支持） ```sql CREATE INDEX idx_name ON users(name(10)); -- 前10字符索引 ``` - ⚠️ 限制：前缀索引无法用于`ORDER BY`/`GROUP BY`[^2] 4. **索引列保持"干净"** - 禁止在索引列上使用函数或计算 ```sql -- 错误：索引失效 SELECT * FROM logs WHERE YEAR(create_time) = 2023; -- 正确：重写为范围查询 SELECT * FROM logs WHERE create_time BETWEEN '2023-01-01' AND '2023-12-31'; ``` #### 二、查询优化策略 1. **排序与分页优化** - 当`ORDER BY`字段不是索引首列时，H2可能全索引扫描[^4] - **解决方案**： ```sql -- 添加首列等值条件（account是索引首列） SELECT * FROM transactions WHERE account = 1001 -- 引导条件 ORDER BY create_time DESC LIMIT 10; ``` 2. **覆盖索引优化** - 让索引包含所有查询字段，避免回表 ```sql -- 创建覆盖索引 CREATE INDEX idx_cover ON orders (account_id, status) INCLUDE (amount); -- 直接使用索引返回数据 SELECT account_id, amount FROM orders WHERE status = 'PAID'; ``` 3. **LIKE查询优化** - 前导通配符导致索引失效 ```sql -- 索引失效 SELECT * FROM products WHERE name LIKE '%Apple%'; -- 使用全文索引替代 CREATE ALIAS IF NOT EXISTS FT_INIT FOR "org.h2.fulltext.FullText.init"; CALL FT_INIT(); CALL FT_CREATE_INDEX('PUBLIC', 'PRODUCTS', 'NAME'); ``` #### 三、索引维护与监控 1. **统计信息更新** - 定期更新统计信息，避免优化器误判 ```sql ANALYZE TABLE orders; -- 更新单表统计信息 ANALYZE SAMPLE_SIZE 1000; -- 设置采样行数 ``` 2. **索引碎片整理** - 重建索引提升性能 ```sql ALTER INDEX idx_orders RECREATE; -- 重建索引 ``` 3. **索引使用监控** - 通过系统表分析索引效率 ```sql SELECT * FROM INFORMATION_SCHEMA.INDEXES WHERE TABLE_NAME = 'ORDERS'; -- 查看索引使用统计 SELECT * FROM INFORMATION_SCHEMA.INDEX_STATS; ``` #### 四、H2特定优化技巧 1. **强制索引使用** - 当优化器选择低效计划时强制使用索引 ```sql SELECT /*+ INDEX(orders idx_account) */ * FROM orders WHERE account_id = 1001; ``` 2. **内存优化配置** - 调整缓存提升索引性能 ```sql SET CACHE_SIZE 1024; -- 增加缓存页数量(默认16MB) SET MAX_MEMORY_ROWS 50000; -- 提高内存排序行数限制 ``` 3. **批量操作优化** - 批量DML时临时禁用索引 ```sql SET IGNORECASE TRUE; CREATE INDEX idx_temp ON large_table(col); -- 批量插入数据... DROP INDEX idx_temp; -- 操作完成后重建正式索引 ``` #### 五、决策流程图 ```mermaid graph TD A[查询性能差] --> B{EXPLAIN分析计划} B -->|未用索引| C[检查索引设计] C -->|缺少索引| D[创建新索引] C -->|有索引未用| E{检查失效原因} E -->|类型不匹配| F[修正查询条件] E -->|函数计算| G[重写查询] B -->|使用索引仍慢| H{检查排序/分页} H -->|排序字段问题| I[优化索引结构] H -->|大数据量| J[添加引导条件] I --> K[测试验证] K -->|性能提升| L[应用方案] K -->|仍不足| M[考虑分库分表] ``` > 💡 **关键实践**： > 1. 复合索引字段顺序 = 查询频率从高到低 + 区分度从高到低 > 2. 更新操作频繁的表，索引数量不超过5个[^1] > 3. 文本搜索使用`FULLTEXT`替代`LIKE` > 4. 定期执行`ANALYZE`和索引重建（尤其频繁DML后） #### 六、性能对比示例 | 场景 | 未优化响应 | 优化后响应 | 优化方式 | |------|------------|------------|---------| | 百万数据分页 | 1200ms | 35ms | 改用连续ID分页: `WHERE id > ? LIMIT ?` | | 状态字段查询 | 全表扫描800ms | 200ms | 添加低区分度索引+覆盖索引 | | 模糊搜索 | 全表扫描2s | 150ms | 改用全文索引 |
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值