微网站简介中专网站建设与管理就业前景

微网站简介,中专网站建设与管理就业前景,和国外做贸易用什么网站,小程序自己免费制作前言#xff1a;一个令人困惑的选择你是否曾遇到这样的情况#xff1a;明明表上有合适的索引#xff0c;但explain的结果却显示 MySQL 选择了全表扫描#xff1f;这背后其实是一个看不见的指挥家——MySQL 优化器——基于一系列「成本常数」做出的决策。今天#xff0c;我…前言一个令人困惑的选择你是否曾遇到这样的情况明明表上有合适的索引但explain的结果却显示 MySQL 选择了全表扫描这背后其实是一个看不见的指挥家——MySQL 优化器——基于一系列「成本常数」做出的决策。今天我们将深入探索 MySQL 成本常数的奥秘揭开查询优化背后的神秘面纱。1 一个费解的SQL现象1.1 表结构CREATE TABLE mapping_filter_record ( idbigint (20) NOTnull AUTO_INCREMENT, source_typeint (11) NOTnullCOMMENT来源类型, source_idvarchar(64) NOTnullCOMMENT来源方id, -- ... 其他字段省略 PRIMARY KEY (id), KEYidx_source_type (source_type, update_time) USING BTREE, KEYidx_source_id (source_id, source_type, state) USING BTREE ) ENGINE InnoDB AUTO_INCREMENT 290240042300201321DEFAULTCHARSET utf8mb4 COMMENT 商品发布拦截记录表;1.2 耗时较久的SQL(10秒以上)select * from dbzz_ypofflinemart.mapping_filter_record WHERE (source_type 9401003 and source_id 1814613774586351713) order by id asc LIMIT 1;1.3 分析下执行计划需要表数据符合一定情况才会发生以下情况。explain select * from dbzz_ypofflinemart.mapping_filter_record WHERE (source_type 9401003 and source_id 1814613774586351713) order by id asc LIMIT 1;执行计划结果令人困惑的是使用了主键索引PRIMARY而非期望的idx_source_id索引。1.4 explain的进阶用法explain可以输出四种格式传统格式、json格式、tree格式以及可视化输出。传统的explain工具只告诉我们结果没有告诉我们为什么。而json格式是四种格式里面输出信息最详尽的格式里面包含了执行的成本信息。我们加上formatjson分析下结果。执行命令1(未指定索引)explain format json select * from dbzz_ypofflinemart.mapping_filter_record WHERE (source_type 9401003 and source_id 1814613774586351713) order by id asc LIMIT 1;得到执行计划1{ query_block: { select_id: 1, cost_info: { query_cost: 3865.20 }, ordering_operation: { using_filesort: false, table: { table_name: mapping_filter_record, access_type: index, possible_keys: [ idx_source_type, idx_source_id ], key: PRIMARY, used_key_parts: [ id ], key_length: 8, rows_examined_per_scan: 501, rows_produced_per_join: 3221, filtered: 4.26, cost_info: { read_cost: 3221.00, eval_cost: 644.20, prefix_cost: 3865.20, data_read_per_join: 92M } } } } }强制指定使用idx_source_id索引再分析执行计划。 执行命令2(指定索引)explain format json select * from dbzz_ypofflinemart.mapping_filter_record FORCE INDEX(idx_source_id) WHERE (source_type 9401003 and source_id 1814613774586351713) order by id asc LIMIT 1;得到执行计划2{ query_block: { select_id: 1, cost_info: { query_cost: 3865.20 }, ordering_operation: { using_filesort: true, table: { table_name: mapping_filter_record, access_type: ref, possible_keys: [ idx_source_id ], key: idx_source_id, used_key_parts: [ source_id, source_type ], key_length: 262, ref: [ const, const ], rows_examined_per_scan: 3221, rows_produced_per_join: 3221, filtered: 100.00, cost_info: { read_cost: 3221.00, eval_cost: 644.20, prefix_cost: 3865.20, data_read_per_join: 92M } } } } }1.5 分析执行计划对比两个 SQL 的执行成本和排序命令query_costusing_filesort命令13865.20false命令23865.20true优化器认为使用PRIMARY聚簇索引和idx_source_id二级索引的查询数据成本相同但是使用PRIMARY聚簇索引可以按索引顺序读取无需再次进行排序操作因此优化器选择了使用PRIMARY聚簇索引来执行该 SQL。2 查询 SQL 语句执行流程2.1 查询优化器优化器的工作流程可以简化为四个步骤解析 SQL理解查询意图生成多种可能的执行方案基于成本常数计算每种方案的代价选择成本最低的方案执行。2.2 执行成本下面需要先介绍一些比较枯燥的概念。SQL执行总成本 CPU成本 I/O成本CPU成本读取以及检测记录是否满足对应的搜索条件、对结果集进行排序等这些操作损耗的时间称之为CPU成本。I/O成本我们的表经常使用的MyISAM、InnoDB存储引擎都是将数据和索引都存储到磁盘上的当我们想查询表中的记录时需要先把数据或者索引加载到内存中然后再操作。将数据从磁盘加载到内存的过程所损耗的时间称为I/O成本。2.3 MySQL 5.7 版本的默认成本常数在 MySQL 中成本常数Cost Constants是查询优化器用来评估不同执行计划的资源消耗的固定数值。这些常数帮助优化器估算执行计划的I/O和CPU成本从而选择最优的执行计划。Server层一些操作对应的成本常数存储引擎层一些操作对应的成本常数3 执行成本分析3.1 表统计信息查询表的一些预估信息用于成本计算。show table status like mapping_filter_record;RowsAvg_row_lengthData_lengthMax_data_lengthIndex_lengthData_free1615460939615180234752055223910441943043.2 命令2 (指定索引) 的执行成本分析先根据非聚簇索引idx_source_id查询出对应数据的主键然后通过主键回表查询、筛选需要的数据。对命令2的执行成本计算如下非聚簇索引CPU成本 读取的记录数 × 读取一条记录的成本 1等值查询定位到单个索引位置 × 0.2row_evaluate_cost非聚簇索引I/O成本1等值查询定位到单个索引位置 × 1io_block_read_cost回表CPU成本 3221rows_examined_per_scan × 0.2row_evaluate_cost回表IO成本3221rows_examined_per_scan× 1io_block_read_cost总成本 3865.2非聚簇索引的访问成本相对于回表成本可以忽略不计计算的成本3865.2和执行计划中的成本3865.20是一致的。3.3 命令1 (未指定索引) 的执行成本分析命令1使用主键索引全表扫描的成本是要比正确使用非聚簇索引的成本要高很多的。实际得到的成本确实相同的。依据1 我们注意到rows_examined_per_scan(扫描行数)为501这是个很突兀的值。增加需要的结果数量得到以下的数据执行语句使用的索引扫描行数实际执行时间select * from xxx WHERE xxx order by id asc LIMIT 1;PRIMARY50119.4秒select * from xxx WHERE xxx order by id asc LIMIT 2;PRIMARY100320.2秒select * from xxx WHERE xxx order by id asc LIMIT 6;PRIMARY300920.24秒select * from xxx WHERE xxx order by id asc LIMIT 7;idx_source_id32210.026秒依据2 表中总数据为 1,615,460 条符合WHERE条件的数据共 3,2211,615,460 除以 3,221 约等于 501。推断MySQL 优化器假设数据是均匀分布的据此估算出每扫描 501 条数据便可找到一条符合条件的记录。这样查询的效率比通过非聚簇索引再回表的效率高。当使用limit时MySQL 的优化器会尝试通过全表扫描的方式来查询数据。当扫描行数小于非聚簇索引的扫描行数时优化器以扫描行数 3221 作为依据计算成本。以上是基于我遇到的情况基于 MySQL 5.7版本进行的分析并未找到明确官方说明有不当之处欢迎大家讨论、指正。4 优化虽然 MySQL 按照数据均匀分布的假设使用了主键索引但实际的情况查询的数据大多在表中靠后的位置就导致了需要扫描百万行才能找到第一条符合条件的数据。多个此类 SQL 同时执行会造成数据库负载过高进而对相关业务服务产生重大影响。针对这种情况有很多优化思路。本例中我采用的优化方法是改为子查询引导优化器优先使用高效的索引避免其因成本误判而选择全表扫描。SELECT * FROM mapping_filter_record WHEREid ( SELECTid FROM mapping_filter_record WHERE source_type 9401003AND source_id 1814613774586351713 ORDERBYidASC LIMIT1 );5 总结这个案例深刻揭示了MySQL 优化器基于成本计算而非直觉进行决策成本常数是优化器评估执行计划的核心依据统计信息的准确性直接影响优化器的选择理解成本计算模型是 SQL 性能优化的关键。通过深入理解 MySQL 优化器的工作原理我们能够更好地设计索引和优化查询提升数据库整体性能。思考题在你的项目中是否遇到过类似索引失效的情况欢迎在评论区分享你的经验和解决方案