关系型数据库sqlserver和pg创建覆盖索引的include写法示例及原理详解
这是一个覆盖索引（Covering Index）的创建语句，让我详细解释它的结构、作用和工作原理：
一、语法结构分解
CREATEINDEXidx_orders_coveringONorders(customer_id,created_date)-- 键列（Key Columns）INCLUDE(amount,status,product_id);-- 包含列（Included Columns）1.键列（Key Columns）
customer_id, created_date：索引的排序和查找键- 这些列参与B+树结构
- 用于WHERE条件、JOIN条件、ORDER BY、GROUP BY
2.包含列（Included Columns）
amount, status, product_id：非键列，存储在索引叶子节点- 不参与B+树排序结构
- 仅用于"覆盖查询"
二、与传统索引对比
传统复合索引
CREATEINDEXidx_traditionalONorders(customer_id,created_date);-- 索引只包含：customer_id, created_date, order_id（主键）-- 查询其他字段需要"回表"覆盖索引
CREATEINDEXidx_coveringONorders(customer_id,created_date)INCLUDE(amount,status,product_id);-- 索引包含：customer_id, created_date, amount, status, product_id, order_id-- 无需回表三、工作原理示例
查询场景
-- 查询1：完全覆盖SELECTcustomer_id,created_date,amount,statusFROMordersWHEREcustomer_id=123ANDcreated_date>='2024-01-01';-- 查询2：部分覆盖SELECTcustomer_id,created_date,amountFROMordersWHEREcustomer_id=123ORDERBYcreated_dateDESCLIMIT10;执行流程对比
传统索引流程： 1. 使用索引找到符合条件的行位置（索引扫描） 2. 根据主键回表获取完整行数据（回表操作） 3. 从行数据中提取amount, status字段 4. 返回结果 覆盖索引流程： 1. 使用索引找到符合条件的行（索引扫描） 2. 直接从索引叶子节点读取所有需要的字段 3. 返回结果（无需回表！）四、性能优势
1.消除回表（Bookmark Lookup）
-- 假设orders表有100万行-- 查询返回1000行传统索引：-索引扫描：1000次查找-回表操作：1000次随机IO（每次10ms）-总耗时：约10秒 覆盖索引：-索引扫描：1000次查找-无需回表：所有数据在索引中-总耗时：约0.1秒（快100倍！）2.减少IO操作
磁盘访问模式： 随机IO（回表）：每次10ms 顺序IO（索引扫描）：每次0.1ms 覆盖索引将随机IO转为顺序IO五、INCLUDE子句的优势
与传统方法的对比
-- 方法1：将列加入键列（不推荐）CREATEINDEXidx_badONorders(customer_id,created_date,amount,status,product_id);-- 问题：索引树变得庞大，维护成本高-- 方法2：使用INCLUDE（推荐）CREATEINDEXidx_goodONorders(customer_id,created_date)INCLUDE(amount,status,product_id);-- 优势：包含列不增加索引层级，只增加叶子节点大小INCLUDE列的特点：
- 不参与排序：不影响索引查找效率
- 不用于过滤：不能用于WHERE条件
- 仅存储值：类似"附表"附加在叶子节点
- 更新代价低：修改包含列只更新叶子节点
六、适用场景
1.高频查询优化
-- 报表查询：经常查询固定字段SELECTcustomer_id,created_date,amount,statusFROMordersWHEREcustomer_idIN(1,2,3)ANDcreated_dateBETWEEN'2024-01-01'AND'2024-01-31';-- 创建针对性的覆盖索引CREATEINDEXidx_reportONorders(customer_id,created_date)INCLUDE(amount,status);2.分页查询优化
-- 分页查询避免大量回表SELECTorder_id,customer_id,created_date,amountFROMordersWHEREcustomer_id=123ORDERBYcreated_dateDESCLIMIT100OFFSET1000;-- 覆盖索引可以完全满足CREATEINDEXidx_pagingONorders(customer_id,created_dateDESC)INCLUDE(amount);3.聚合查询加速
-- 分组统计SELECTcustomer_id,DATE(created_date),SUM(amount),COUNT(*)FROMordersWHEREcreated_date>='2024-01-01'GROUPBYcustomer_id,DATE(created_date);-- 覆盖索引提供所有需要的数据CREATEINDEXidx_aggONorders(created_date,customer_id)INCLUDE(amount);七、注意事项和限制
1.索引大小权衡
-- 覆盖索引会更大-- 原始数据：customer_id(8B) + created_date(8B) = 16B-- 覆盖索引：16B + amount(8B) + status(4B) + product_id(8B) = 36B-- 索引大小增加125%，但查询性能提升显著2.更新代价
-- 更新包含列时UPDATEordersSETamount=200WHEREorder_id=1;-- 需要更新：主表 + 所有包含该列的覆盖索引-- 写操作变慢，读操作变快3.数据库支持
-- 不同数据库语法不同-- SQL Server/PostgreSQL: 支持INCLUDE语法-- MySQL: 不支持INCLUDE，所有列都是键列-- Oracle: 通过"索引组织表"或"函数索引"实现类似功能-- MySQL的替代方案（没有INCLUDE）：CREATEINDEXidx_mysqlONorders(customer_id,created_date,amount,status,product_id);-- 但这不是真正的覆盖索引，所有列都参与排序八、最佳实践建议
1.选择性包含
-- 只包含高频查询的列CREATEINDEXidx_selectiveONorders(customer_id,created_date)INCLUDE(amount,-- 经常用于SUM/AVGstatus,-- 经常用于筛选-- 不包含：description（大文本字段）-- 不包含：updated_at（很少查询）);2.监控使用情况
-- 检查索引使用SELECT*FROMsys.dm_db_index_usage_statsWHEREobject_id=OBJECT_ID('orders');-- 检查索引大小EXECsp_spaceused'orders';3.组合策略
-- 针对不同查询创建多个覆盖索引-- 索引1：用于客户查询CREATEINDEXidx_customer_queryONorders(customer_id,created_date)INCLUDE(amount,status);-- 索引2：用于产品分析CREATEINDEXidx_product_analysisONorders(product_id,created_date)INCLUDE(amount,customer_id);-- 索引3：用于状态监控CREATEINDEXidx_status_monitorONorders(status,created_date)INCLUDE(amount,customer_id);