文章目录每日一句正能量一、前言索引是性能的基石二、MySQL与KaiwuDB索引差异2.1 索引类型对比2.2 索引存储差异2.3 索引选择器差异三、迁移后索引问题排查3.1 慢查询分析3.2 索引使用情况3.3 常见问题四、索引重建策略4.1 重建前分析4.2 重建策略4.3 重建步骤五、性能调优实战5.1 查询优化5.2 报表查询优化5.3 分页查询优化六、索引维护6.1 定期分析6.2 索引监控6.3 索引清理七、总结每日一句正能量要大笑要做梦要与众不同人生是一场伟大的冒险。将人生视为“伟大的冒险”意味着接纳其不确定性、挑战与惊喜用主动探索代替被动接受让过程本身充满英雄般的色彩。一、前言索引是性能的基石前面八篇文章我分享了MySQL迁移KaiwuDB的完整流程、工具使用、类型映射、SQL改造、性能测试、灰度策略。有读者问“迁移完成后查询变慢了特别是那些复杂的报表查询从原来的几秒变成了几十秒怎么办”这是个典型问题。迁移后虽然数据完整性和功能都正常但索引可能需要重新优化。因为MySQL和KaiwuDB的索引实现机制不同原有的索引策略可能不再适用。本文就把迁移后的索引优化实战经验分享出来包括索引差异分析、重建策略、性能调优。二、MySQL与KaiwuDB索引差异2.1 索引类型对比索引类型MySQLKaiwuDB说明B树索引支持支持默认索引类型哈希索引支持不支持KaiwuDB不支持哈希索引全文索引支持不支持KaiwuDB不支持全文索引空间索引支持不支持KaiwuDB不支持空间索引前缀索引支持支持前缀长度限制不同函数索引支持支持实现方式不同覆盖索引支持支持概念相同联合索引支持支持最左前缀原则相同2.2 索引存储差异MySQL InnoDB聚簇索引数据按主键顺序存储二级索引叶子节点存储主键值索引组织表数据存储在索引中KaiwuDB分布式索引数据分布在多个节点全局索引跨节点的全局索引本地索引节点内的本地索引2.3 索引选择器差异MySQL基于成本的优化器统计信息更新频率较低支持索引提示USE INDEX, FORCE INDEXKaiwuDB基于成本的优化器自动收集统计信息支持索引提示三、迁移后索引问题排查3.1 慢查询分析-- 查看慢查询日志SHOWLOGS;-- 查看执行计划EXPLAINANALYZESELECT*FROMordersWHEREuser_id10086;3.2 索引使用情况-- 查看表的索引SHOWINDEXFROMorders;-- 查看索引大小SELECTindex_name,pg_size_pretty(pg_relation_size(indexrelid))asindex_sizeFROMpg_stat_user_indexesWHERErelnameorders;3.3 常见问题问题1索引失效-- MySQL中有效的索引SELECT*FROMordersWHEREYEAR(created_at)2023;-- KaiwuDB中可能失效-- 因为函数导致索引失效解决方案-- 改写SQL避免函数SELECT*FROMordersWHEREcreated_at2023-01-01ANDcreated_at2024-01-01;问题2前缀索引长度限制-- MySQL中CREATEINDEXidx_order_noONorders(order_no(10));-- KaiwuDB中CREATEINDEXidx_order_noONorders(order_no);-- 不支持前缀长度解决方案-- 使用完整字段创建索引CREATEINDEXidx_order_noONorders(order_no);-- 或者使用表达式索引CREATEINDEXidx_order_no_prefixONorders(SUBSTRING(order_no,1,10));四、索引重建策略4.1 重建前分析-- 分析表结构SELECTcolumn_name,data_type,is_nullableFROMinformation_schema.columnsWHEREtable_nameorders;-- 分析现有索引SELECTindex_name,column_name,cardinalityFROMinformation_schema.statisticsWHEREtable_nameorders;4.2 重建策略策略1保留原有索引-- 创建与MySQL相同的索引CREATEINDEXidx_user_idONorders(user_id);CREATEINDEXidx_created_atONorders(created_at);CREATEINDEXidx_statusONorders(status);策略2优化联合索引-- MySQL中的联合索引CREATEINDEXidx_user_createdONorders(user_id,created_at);-- KaiwuDB中优化为覆盖索引CREATEINDEXidx_user_created_coveringONorders(user_id,created_at)INCLUDE(order_no,total_amount);策略3创建分区索引-- 按时间分区CREATETABLEorders(idNUMERIC(20)PRIMARYKEY,user_id INT8,order_noVARCHAR(64),total_amountDECIMAL(18,2),statusINT2,created_atTIMESTAMP)PARTITIONBYRANGE(created_at);-- 创建分区索引CREATEINDEXidx_user_idONorders(user_id);4.3 重建步骤#!/bin/bash# rebuild_index.sh# 1. 分析表结构kwbase sql --certs-dir/etc/kaiwudb/certs--host192.168.1.100:26257-e SELECT column_name, data_type FROM information_schema.columns WHERE table_name orders; # 2. 删除旧索引kwbase sql --certs-dir/etc/kaiwudb/certs--host192.168.1.100:26257-e DROP INDEX IF EXISTS idx_user_id; DROP INDEX IF EXISTS idx_created_at; # 3. 创建新索引kwbase sql --certs-dir/etc/kaiwudb/certs--host192.168.1.100:26257-e CREATE INDEX idx_user_id ON orders(user_id); CREATE INDEX idx_created_at ON orders(created_at); CREATE INDEX idx_user_created ON orders(user_id, created_at); # 4. 验证索引kwbase sql --certs-dir/etc/kaiwudb/certs--host192.168.1.100:26257-e SHOW INDEX FROM orders; # 5. 分析表kwbase sql --certs-dir/etc/kaiwudb/certs--host192.168.1.100:26257-e ANALYZE orders; 五、性能调优实战5.1 查询优化优化前-- 慢查询查询用户近一年的订单SELECT*FROMordersWHEREuser_id10086ANDcreated_at2023-01-01;执行计划Seq Scan on orders (cost0.00..12345.67 rows1000) Filter: ((user_id 10086) AND (created_at 2023-01-01))优化后-- 创建联合索引CREATEINDEXidx_user_createdONorders(user_id,created_at);-- 优化查询SELECT*FROMordersWHEREuser_id10086ANDcreated_at2023-01-01;执行计划Index Scan using idx_user_created on orders (cost0.00..123.45 rows100) Index Cond: ((user_id 10086) AND (created_at 2023-01-01))性能提升指标优化前优化后提升执行时间12.34s0.12s99%扫描行数5,234,5671,23499.98%索引使用否是-5.2 报表查询优化优化前-- 慢查询统计每月订单金额SELECTDATE_FORMAT(created_at,%Y-%m)asmonth,SUM(total_amount)astotalFROMordersGROUPBYmonth;优化后-- 创建物化视图CREATEMATERIALIZEDVIEWmonthly_ordersASSELECTDATE_TRUNC(month,created_at)asmonth,SUM(total_amount)astotalFROMordersGROUPBYmonth;-- 创建索引CREATEINDEXidx_monthONmonthly_orders(month);-- 查询物化视图SELECT*FROMmonthly_ordersWHEREmonth2023-01-01;性能提升指标优化前优化后提升执行时间45.67s0.05s99.9%扫描行数5,234,5671299.99%5.3 分页查询优化优化前-- 慢查询分页查询SELECT*FROMordersORDERBYcreated_atDESCLIMIT10OFFSET100000;优化后-- 使用游标分页SELECT*FROMordersWHEREcreated_at2023-01-01 00:00:00ORDERBYcreated_atDESCLIMIT10;-- 或者使用键集分页SELECT*FROMordersWHERE(created_at,id)(2023-01-01 00:00:00,100000)ORDERBYcreated_atDESC,idDESCLIMIT10;性能提升指标优化前优化后提升执行时间23.45s0.08s99.7%扫描行数100,0101099.99%六、索引维护6.1 定期分析-- 分析表ANALYZEorders;-- 查看统计信息SELECTtablename,attname,n_distinct,most_common_valsFROMpg_statsWHEREtablenameorders;6.2 索引监控-- 查看索引使用情况SELECTindexrelname,idx_scan,idx_tup_read,idx_tup_fetchFROMpg_stat_user_indexesWHERErelnameorders;-- 查看索引大小SELECTindexrelname,pg_size_pretty(pg_relation_size(indexrelid))asindex_sizeFROMpg_stat_user_indexesWHERErelnameorders;6.3 索引清理-- 删除未使用的索引SELECTindexrelname,idx_scanFROMpg_stat_user_indexesWHERErelnameordersANDidx_scan0;-- 删除索引DROPINDEXIFEXISTSidx_unused;七、总结索引优化是数据库迁移后的重要工作。通过本文的介绍你应该能够理解索引差异MySQL和KaiwuDB的索引实现机制不同排查索引问题慢查询分析、索引使用情况重建索引保留原有索引、优化联合索引、创建分区索引性能调优查询优化、报表查询优化、分页查询优化维护索引定期分析、监控、清理关键要点迁移后需要重新评估索引策略联合索引的顺序很重要覆盖索引可以显著提升性能物化视图适合报表查询定期分析和监控索引如果你正在考虑数据库国产化迁移建议在迁移后做好索引优化确保查询性能满足业务需求。转载自https://blog.csdn.net/u014727709/article/details/163895221欢迎 点赞✍评论⭐收藏欢迎指正 MySQL迁移KaiwuDB后索引优化实战
文章目录每日一句正能量一、前言索引是性能的基石二、MySQL与KaiwuDB索引差异2.1 索引类型对比2.2 索引存储差异2.3 索引选择器差异三、迁移后索引问题排查3.1 慢查询分析3.2 索引使用情况3.3 常见问题四、索引重建策略4.1 重建前分析4.2 重建策略4.3 重建步骤五、性能调优实战5.1 查询优化5.2 报表查询优化5.3 分页查询优化六、索引维护6.1 定期分析6.2 索引监控6.3 索引清理七、总结每日一句正能量要大笑要做梦要与众不同人生是一场伟大的冒险。将人生视为“伟大的冒险”意味着接纳其不确定性、挑战与惊喜用主动探索代替被动接受让过程本身充满英雄般的色彩。一、前言索引是性能的基石前面八篇文章我分享了MySQL迁移KaiwuDB的完整流程、工具使用、类型映射、SQL改造、性能测试、灰度策略。有读者问“迁移完成后查询变慢了特别是那些复杂的报表查询从原来的几秒变成了几十秒怎么办”这是个典型问题。迁移后虽然数据完整性和功能都正常但索引可能需要重新优化。因为MySQL和KaiwuDB的索引实现机制不同原有的索引策略可能不再适用。本文就把迁移后的索引优化实战经验分享出来包括索引差异分析、重建策略、性能调优。二、MySQL与KaiwuDB索引差异2.1 索引类型对比索引类型MySQLKaiwuDB说明B树索引支持支持默认索引类型哈希索引支持不支持KaiwuDB不支持哈希索引全文索引支持不支持KaiwuDB不支持全文索引空间索引支持不支持KaiwuDB不支持空间索引前缀索引支持支持前缀长度限制不同函数索引支持支持实现方式不同覆盖索引支持支持概念相同联合索引支持支持最左前缀原则相同2.2 索引存储差异MySQL InnoDB聚簇索引数据按主键顺序存储二级索引叶子节点存储主键值索引组织表数据存储在索引中KaiwuDB分布式索引数据分布在多个节点全局索引跨节点的全局索引本地索引节点内的本地索引2.3 索引选择器差异MySQL基于成本的优化器统计信息更新频率较低支持索引提示USE INDEX, FORCE INDEXKaiwuDB基于成本的优化器自动收集统计信息支持索引提示三、迁移后索引问题排查3.1 慢查询分析-- 查看慢查询日志SHOWLOGS;-- 查看执行计划EXPLAINANALYZESELECT*FROMordersWHEREuser_id10086;3.2 索引使用情况-- 查看表的索引SHOWINDEXFROMorders;-- 查看索引大小SELECTindex_name,pg_size_pretty(pg_relation_size(indexrelid))asindex_sizeFROMpg_stat_user_indexesWHERErelnameorders;3.3 常见问题问题1索引失效-- MySQL中有效的索引SELECT*FROMordersWHEREYEAR(created_at)2023;-- KaiwuDB中可能失效-- 因为函数导致索引失效解决方案-- 改写SQL避免函数SELECT*FROMordersWHEREcreated_at2023-01-01ANDcreated_at2024-01-01;问题2前缀索引长度限制-- MySQL中CREATEINDEXidx_order_noONorders(order_no(10));-- KaiwuDB中CREATEINDEXidx_order_noONorders(order_no);-- 不支持前缀长度解决方案-- 使用完整字段创建索引CREATEINDEXidx_order_noONorders(order_no);-- 或者使用表达式索引CREATEINDEXidx_order_no_prefixONorders(SUBSTRING(order_no,1,10));四、索引重建策略4.1 重建前分析-- 分析表结构SELECTcolumn_name,data_type,is_nullableFROMinformation_schema.columnsWHEREtable_nameorders;-- 分析现有索引SELECTindex_name,column_name,cardinalityFROMinformation_schema.statisticsWHEREtable_nameorders;4.2 重建策略策略1保留原有索引-- 创建与MySQL相同的索引CREATEINDEXidx_user_idONorders(user_id);CREATEINDEXidx_created_atONorders(created_at);CREATEINDEXidx_statusONorders(status);策略2优化联合索引-- MySQL中的联合索引CREATEINDEXidx_user_createdONorders(user_id,created_at);-- KaiwuDB中优化为覆盖索引CREATEINDEXidx_user_created_coveringONorders(user_id,created_at)INCLUDE(order_no,total_amount);策略3创建分区索引-- 按时间分区CREATETABLEorders(idNUMERIC(20)PRIMARYKEY,user_id INT8,order_noVARCHAR(64),total_amountDECIMAL(18,2),statusINT2,created_atTIMESTAMP)PARTITIONBYRANGE(created_at);-- 创建分区索引CREATEINDEXidx_user_idONorders(user_id);4.3 重建步骤#!/bin/bash# rebuild_index.sh# 1. 分析表结构kwbase sql --certs-dir/etc/kaiwudb/certs--host192.168.1.100:26257-e SELECT column_name, data_type FROM information_schema.columns WHERE table_name orders; # 2. 删除旧索引kwbase sql --certs-dir/etc/kaiwudb/certs--host192.168.1.100:26257-e DROP INDEX IF EXISTS idx_user_id; DROP INDEX IF EXISTS idx_created_at; # 3. 创建新索引kwbase sql --certs-dir/etc/kaiwudb/certs--host192.168.1.100:26257-e CREATE INDEX idx_user_id ON orders(user_id); CREATE INDEX idx_created_at ON orders(created_at); CREATE INDEX idx_user_created ON orders(user_id, created_at); # 4. 验证索引kwbase sql --certs-dir/etc/kaiwudb/certs--host192.168.1.100:26257-e SHOW INDEX FROM orders; # 5. 分析表kwbase sql --certs-dir/etc/kaiwudb/certs--host192.168.1.100:26257-e ANALYZE orders; 五、性能调优实战5.1 查询优化优化前-- 慢查询查询用户近一年的订单SELECT*FROMordersWHEREuser_id10086ANDcreated_at2023-01-01;执行计划Seq Scan on orders (cost0.00..12345.67 rows1000) Filter: ((user_id 10086) AND (created_at 2023-01-01))优化后-- 创建联合索引CREATEINDEXidx_user_createdONorders(user_id,created_at);-- 优化查询SELECT*FROMordersWHEREuser_id10086ANDcreated_at2023-01-01;执行计划Index Scan using idx_user_created on orders (cost0.00..123.45 rows100) Index Cond: ((user_id 10086) AND (created_at 2023-01-01))性能提升指标优化前优化后提升执行时间12.34s0.12s99%扫描行数5,234,5671,23499.98%索引使用否是-5.2 报表查询优化优化前-- 慢查询统计每月订单金额SELECTDATE_FORMAT(created_at,%Y-%m)asmonth,SUM(total_amount)astotalFROMordersGROUPBYmonth;优化后-- 创建物化视图CREATEMATERIALIZEDVIEWmonthly_ordersASSELECTDATE_TRUNC(month,created_at)asmonth,SUM(total_amount)astotalFROMordersGROUPBYmonth;-- 创建索引CREATEINDEXidx_monthONmonthly_orders(month);-- 查询物化视图SELECT*FROMmonthly_ordersWHEREmonth2023-01-01;性能提升指标优化前优化后提升执行时间45.67s0.05s99.9%扫描行数5,234,5671299.99%5.3 分页查询优化优化前-- 慢查询分页查询SELECT*FROMordersORDERBYcreated_atDESCLIMIT10OFFSET100000;优化后-- 使用游标分页SELECT*FROMordersWHEREcreated_at2023-01-01 00:00:00ORDERBYcreated_atDESCLIMIT10;-- 或者使用键集分页SELECT*FROMordersWHERE(created_at,id)(2023-01-01 00:00:00,100000)ORDERBYcreated_atDESC,idDESCLIMIT10;性能提升指标优化前优化后提升执行时间23.45s0.08s99.7%扫描行数100,0101099.99%六、索引维护6.1 定期分析-- 分析表ANALYZEorders;-- 查看统计信息SELECTtablename,attname,n_distinct,most_common_valsFROMpg_statsWHEREtablenameorders;6.2 索引监控-- 查看索引使用情况SELECTindexrelname,idx_scan,idx_tup_read,idx_tup_fetchFROMpg_stat_user_indexesWHERErelnameorders;-- 查看索引大小SELECTindexrelname,pg_size_pretty(pg_relation_size(indexrelid))asindex_sizeFROMpg_stat_user_indexesWHERErelnameorders;6.3 索引清理-- 删除未使用的索引SELECTindexrelname,idx_scanFROMpg_stat_user_indexesWHERErelnameordersANDidx_scan0;-- 删除索引DROPINDEXIFEXISTSidx_unused;七、总结索引优化是数据库迁移后的重要工作。通过本文的介绍你应该能够理解索引差异MySQL和KaiwuDB的索引实现机制不同排查索引问题慢查询分析、索引使用情况重建索引保留原有索引、优化联合索引、创建分区索引性能调优查询优化、报表查询优化、分页查询优化维护索引定期分析、监控、清理关键要点迁移后需要重新评估索引策略联合索引的顺序很重要覆盖索引可以显著提升性能物化视图适合报表查询定期分析和监控索引如果你正在考虑数据库国产化迁移建议在迁移后做好索引优化确保查询性能满足业务需求。转载自https://blog.csdn.net/u014727709/article/details/163895221欢迎 点赞✍评论⭐收藏欢迎指正