潮州网站制作成都公司展厅

潮州网站制作,成都公司展厅,织梦网站手机端,注册建筑工程公司起名大全由于MySQL 5.7 不支持 INCLUDE 语法#xff01; 本文我详细解释MySQL 5.7覆盖索引的实现方式、替代方案和限制#xff1a; 一、MySQL的覆盖索引实现方式 MySQL 5.7的实际语法 -- MySQL 5.7 不支持INCLUDE语法 -- 以下语句会报错#xff1a; CREATE INDEX idx_orders_cove…由于MySQL 5.7 不支持INCLUDE语法本文我详细解释MySQL 5.7覆盖索引的实现方式、替代方案和限制一、MySQL的覆盖索引实现方式MySQL 5.7的实际语法-- MySQL 5.7 不支持INCLUDE语法-- 以下语句会报错CREATEINDEXidx_orders_coveringONorders(customer_id,created_date)INCLUDE(amount,status,product_id);-- ❌ 语法错误-- MySQL的正确写法CREATEINDEXidx_orders_coveringONorders(customer_id,created_date,amount,status,product_id);-- ✅工作原理差异-- SQL Server/PostgreSQL键列和包含列分离CREATEINDEXidx_separateONtable(key1,key2)INCLUDE(col3,col4);-- 索引结构key1, key2 | col3, col4 (附加存储)-- MySQL所有列都是键列CREATEINDEXidx_all_keysONtable(key1,key2,col3,col4);-- 索引结构key1, key2, col3, col4 (全部参与排序)二、MySQL 5.7的替代方案方案1创建复合索引最常用-- 将所有需要的列都放在索引定义中CREATEINDEXidx_covering_mysqlONorders(customer_id,created_date,amount,status,product_id);-- 查询验证EXPLAINSELECTcustomer_id,created_date,amount,statusFROMordersWHEREcustomer_id123ANDcreated_date2024-01-01;-- 如果Extra显示Using index说明使用了覆盖索引方案2使用索引扩展MySQL 5.6-- MySQL会自动将主键附加到二级索引末尾-- 假设主键是order_idCREATEINDEXidx_partialONorders(customer_id,created_date);-- 实际索引包含customer_id, created_date, order_id-- 可以利用这一点SELECTcustomer_id,created_date,order_idFROMordersWHEREcustomer_id123;-- 这个查询可以使用覆盖索引方案3使用生成列MySQL 5.7-- 通过生成列创建函数索引ALTERTABLEordersADDCOLUMNstatus_codeTINYINTAS(CASEstatusWHENpendingTHEN1WHENshippedTHEN2WHENdeliveredTHEN3ELSE0END)STORED;-- 创建包含生成列的索引CREATEINDEXidx_with_storedONorders(customer_id,created_date,status_code);三、MySQL覆盖索引的局限性1.索引大小问题-- MySQL中所有索引列都参与B树排序-- 如果包含大字段索引会非常庞大CREATEINDEXidx_bigONorders(customer_id,created_date,product_nameVARCHAR(200),-- 大字段会使索引很大descriptionTEXT(500)-- 更糟糕);-- ❌ 不推荐可能比表数据还大2.前缀索引限制-- 对于文本字段可以使用前缀索引CREATEINDEXidx_text_prefixONorders(customer_id,created_date,product_name(50)-- 只索引前50个字符);-- 但可能无法完全覆盖查询SELECTcustomer_id,created_date,product_nameFROMordersWHEREcustomer_id123;-- 如果product_name长度超过50需要回表3.最左前缀原则限制-- 索引customer_id, created_date, amount, status-- 有效查询SELECT*FROMordersWHEREcustomer_id123;-- ✅ 使用索引SELECT*FROMordersWHEREcustomer_id123ANDcreated_date2024-01-01;-- ✅-- 无效查询SELECT*FROMordersWHEREcreated_date2024-01-01;-- ❌ 不使用索引SELECT*FROMordersWHEREamount100;-- ❌ 不使用索引四、MySQL 5.7的优化技巧技巧1选择合适的列顺序-- 按选择性和查询频率排序CREATEINDEXidx_optimizedONorders(customer_id,-- 高选择性经常用于WHEREcreated_date,-- 范围查询放在第二status,-- 低选择性很少单独查询amount-- 仅用于SELECT列表);技巧2使用索引合并-- 如果无法创建大型复合索引CREATEINDEXidx_customer_dateONorders(customer_id,created_date);CREATEINDEXidx_statusONorders(status);-- 查询时MySQL可能使用索引合并EXPLAINSELECTcustomer_id,created_date,amountFROMordersWHEREcustomer_id123ANDstatusshipped;-- 可能使用idx_customer_date AND idx_status技巧3分析索引使用情况-- 查看索引统计SELECTTABLE_NAME,INDEX_NAME,SEQ_IN_INDEX,COLUMN_NAME,CARDINALITYFROMINFORMATION_SCHEMA.STATISTICSWHERETABLE_SCHEMAyour_databaseANDTABLE_NAMEordersORDERBYINDEX_NAME,SEQ_IN_INDEX;-- 查看索引大小SELECTTABLE_NAME,INDEX_NAME,ROUND(INDEX_LENGTH/1024/1024,2)ASSize(MB)FROMINFORMATION_SCHEMA.TABLESWHERETABLE_SCHEMAyour_databaseANDTABLE_NAMEorders;五、MySQL 8.0的改进降序索引MySQL 8.0-- MySQL 5.7不支持降序索引8.0支持CREATEINDEXidx_descONorders(customer_id,created_dateDESC);-- 对于ORDER BY ... DESC查询更高效函数索引MySQL 8.0-- 直接在索引中使用函数CREATEINDEXidx_funcONorders((UPPER(customer_name)));六、实际应用示例场景订单查询优化-- 查询模式1按客户和时间查询SELECTorder_id,customer_id,created_date,amount,statusFROMordersWHEREcustomer_id123ANDcreated_dateBETWEEN2024-01-01AND2024-01-31;-- 查询模式2按状态和时间查询SELECTorder_id,customer_id,created_date,amountFROMordersWHEREstatusshippedANDcreated_date2024-01-01;-- MySQL 5.7解决方案创建两个索引CREATEINDEXidx_customer_date_coveringONorders(customer_id,created_date,amount,status);-- 注意order_id会自动包含主键CREATEINDEXidx_status_date_coveringONorders(status,created_date,customer_id,amount);七、最佳实践建议1.避免过度索引-- 不要为每个查询创建覆盖索引-- 评估查询频率和性能收益-- 一般原则一个表的索引数量不超过5-7个2.监控和维护-- 定期分析索引使用SELECT*FROMsys.schema_unused_indexes;-- MySQL 8.0-- 使用Performance Schema监控SELECT*FROMperformance_schema.table_io_waits_summary_by_index_usage;3.测试验证-- 创建索引前测试EXPLAINSELECT...-- 查看执行计划-- 创建索引后验证ANALYZETABLEorders;-- 更新统计信息EXPLAINSELECT...-- 确认索引使用