当前位置:首页 > 公众号精选 > 架构师社区
[导读]来自:Java面试那些事儿 最近,在脉脉上看到一个楼主提出的问题:MySQL数据量大时,delete操作无法命中索引;并且还附上了相关案例截图。 最终,楼主通过开启MySQL分析优化器追踪,定位到是优化器搞的鬼,它觉得花费时间太长。因为我这个是测试数据,究其原因


我艹,MySQL数据量大时,delete操作无法命中索引。

来自:Java面试那些事儿


最近,在脉脉上看到一个楼主提出的问题:MySQL数据量大时,delete操作无法命中索引;并且还附上了相关案例截图。

我艹,MySQL数据量大时,delete操作无法命中索引。

我艹,MySQL数据量大时,delete操作无法命中索引。

我艹,MySQL数据量大时,delete操作无法命中索引。

我艹,MySQL数据量大时,delete操作无法命中索引。

最终,楼主通过开启MySQL分析优化器追踪,定位到是优化器搞的鬼,它觉得花费时间太长。因为我这个是测试数据,究其原因是因为数据倾斜,导致计算出的数据占比较大、花费时间长


大家要记住一点,一条SQL语句走哪条索引是通过其中的优化器代价分析两个部分来决定的。所以,随着数据的不断变化,最优解也要跟着变化。因此,就需要DBA来不断的优化SQL。


对于查询情况,其实MySQL提供给我们一个功能来引导优化器更好的优化,那便是MySQL的查询优化提示(Query Optimizer Hints)。比如,想让SQL强制走索引的话,可以使用 FORCE INDEX 或者USE INDEX;它们基本相同,不同点:在于就算索引的实际用处不大,FORCE INDEX也得要使用索引。

EXPLAIN SELECT * FROM yp_user FORCE INDEX(idx_gender) where gender=1 ;

同样,你也可以通过IGNORE INDEX来忽略索引。

EXPLAIN SELECT * FROM yp_user IGNORE INDEX(idx_gender) where gender=1 ;

在我看来,虽然有MySQL Hints这种好用的工具,但我建议还是不要再生产环境使用,因为当数据量增长时,你压根儿都不知道这种索引的方式是否还适应于当前的环境,还是得配合DBA从索引的结构上去优化。


接下来,我来教大家如何用MySQL的trace分析优化器是如何选择执行计划的?很重要的手段,建议多实战一下。


1、什么是Trace?


关于这个问题,我觉得去最好的描述是官方文档。

我艹,MySQL数据量大时,delete操作无法命中索引。

在MySQL 5.6中,MySQL优化器增加了一个新的跟踪功能。该接口由一组optimizer_trace_xxx系统变量和INFORMATION_SCHEMA.OPTIMIZER_TRACE表提供,但可能会发生变化。

通俗点,就是通过trace文件能够进一步了解为什么优化器选择A执行计划而不选择B执行计划,帮助我们更好的理解优化器的行为。


2、如何使用?


还是得看官方文档。

# 查看优化器跟踪是否状态SHOW VARIABLES LIKE '%optimizer_trace%';# 开启tracing (默认是关闭的):SET optimizer_trace="enabled=on";# 你的查询语句SELECT ...; # 查询trace json文件SELECT * FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;# 当完成后,关闭traceSET optimizer_trace="enabled=off";


3、分析trace文件


根据我本地的一个例子为例,具体文件内容如下。

SELECT * FROM yp_user where gender=1 | { "steps": [ { "join_preparation": { "select#": 1, "steps": [ { "expanded_query": "/* select#1 */ select `yp_user`.`open_id` AS `open_id`,`yp_user`.`avatar_url` AS `avatar_url`,`yp_user`.`city` AS `city`,`yp_user`.`country` AS `country`,`yp_user`.`create_time` AS `create_time`,`yp_user`.`gender` AS `gender`,`yp_user`.`language` AS `language`,`yp_user`.`nick_name` AS `nick_name`,`yp_user`.`province` AS `province`,`yp_user`.`skey` AS `skey`,`yp_user`.`update_time` AS `update_time`,`yp_user`.`privilege` AS `privilege` from `yp_user` where (`yp_user`.`gender` = 1)" } ] } }, { "join_optimization": { "select#": 1, "steps": [ { "condition_processing": { "condition": "WHERE", "original_condition": "(`yp_user`.`gender` = 1)", "steps": [ { "transformation": "equality_propagation", "resulting_condition": "multiple equal(1, `yp_user`.`gender`)" }, { "transformation": "constant_propagation", "resulting_condition": "multiple equal(1, `yp_user`.`gender`)" }, { "transformation": "trivial_condition_removal", "resulting_condition": "multiple equal(1, `yp_user`.`gender`)" } ] } }, { "substitute_generated_columns": { } }, { "table_dependencies": [ { "table": "`yp_user`", "row_may_be_null": false, "map_bit": 0, "depends_on_map_bits": [ ] } ] }, { "ref_optimizer_key_uses": [ { "table": "`yp_user`", "field": "gender", "equals": "1", "null_rejecting": false } ] }, { "rows_estimation": [ { "table": "`yp_user`", "range_analysis": { "table_scan": { "rows": 3100, "cost": 719.1 }, "potential_range_indexes": [ { "index": "PRIMARY", "usable": false, "cause": "not_applicable" }, { "index": "idx_skey", "usable": false, "cause": "not_applicable" }, { "index": "idx_gender", "usable": true, "key_parts": [ "gender", "open_id" ] } ], "setup_range_conditions": [ ], "group_index_range": { "chosen": false, "cause": "not_group_by_or_distinct" }, "analyzing_range_alternatives": { "range_scan_alternatives": [ { "index": "idx_gender", "ranges": [ "1 <= gender <= 1" ], "index_dives_for_eq_ranges": true, "rowid_ordered": true, "using_mrr": false, "index_only": false, "rows": 2731, "cost": 3278.2, "chosen": false, "cause": "cost" } ], "analyzing_roworder_intersect": { "usable": false, "cause": "too_few_roworder_scans" } } } } ] }, { "considered_execution_plans": [ { "plan_prefix": [ ], "table": "`yp_user`", "best_access_path": { "considered_access_paths": [ { "access_type": "ref", "index": "idx_gender", "rows": 2731, "cost": 837.2, "chosen": true }, { "rows_to_scan": 3100, "access_type": "scan", "resulting_rows": 3100, "cost": 717, "chosen": true } ] }, "condition_filtering_pct": 100, "rows_for_plan": 3100, "cost_for_plan": 717, "chosen": true } ] }, { "attaching_conditions_to_tables": { "original_condition": "(`yp_user`.`gender` = 1)", "attached_conditions_computation": [ ], "attached_conditions_summary": [ { "table": "`yp_user`", "attached": "(`yp_user`.`gender` = 1)" } ] } }, { "refine_plan": [ { "table": "`yp_user`" } ] } ] } }, { "join_execution": { "select#": 1, "steps": [ ] } } ]}

通过这个例子,我们可以得到全表扫描的代价如下。

"table_scan": { "rows": 3100, "cost": 719.1}

分析结果:全表扫描访问的rows记录为3100,代价cost计算为719.1。


索引扫描的代价如下。

"range_scan_alternatives": [ { "index": "idx_gender", "ranges": [ "1 <= gender <= 1" ], "index_dives_for_eq_ranges": true, "rowid_ordered": true, "using_mrr": false, "index_only": false, "rows": 2731, "cost": 3278.2, "chosen": false, "cause": "cost" }]

分析结果:这里看到了通过idx_gender索引过滤时,优化器预估需要返回2731记录,访问代价cost为3278.2,大于全表扫描代价719.1;因此,优化器倾向于选择全表扫描。


今晚上就熬夜写到这里吧。


-----------------------------

特别推荐一个分享架构+算法的优质内容,还没关注的小伙伴,可以长按关注一下:

我艹,MySQL数据量大时,delete操作无法命中索引。

长按订阅更多精彩▼

我艹,MySQL数据量大时,delete操作无法命中索引。

如有收获,点个在看,诚挚感谢

免责声明:本文内容由21ic获得授权后发布,版权归原作者所有,本平台仅提供信息存储服务。文章仅代表作者个人观点,不代表本平台立场,如有问题,请联系我们,谢谢!

本站声明: 本文章由作者或相关机构授权发布,目的在于传递更多信息,并不代表本站赞同其观点,本站亦不保证或承诺内容真实性等。需要转载请联系该专栏作者,如若文章内容侵犯您的权益,请及时联系本站删除。
换一批
延伸阅读

9月2日消息,不造车的华为或将催生出更大的独角兽公司,随着阿维塔和赛力斯的入局,华为引望愈发显得引人瞩目。

关键字: 阿维塔 塞力斯 华为

加利福尼亚州圣克拉拉县2024年8月30日 /美通社/ -- 数字化转型技术解决方案公司Trianz今天宣布,该公司与Amazon Web Services (AWS)签订了...

关键字: AWS AN BSP 数字化

伦敦2024年8月29日 /美通社/ -- 英国汽车技术公司SODA.Auto推出其旗舰产品SODA V,这是全球首款涵盖汽车工程师从创意到认证的所有需求的工具,可用于创建软件定义汽车。 SODA V工具的开发耗时1.5...

关键字: 汽车 人工智能 智能驱动 BSP

北京2024年8月28日 /美通社/ -- 越来越多用户希望企业业务能7×24不间断运行,同时企业却面临越来越多业务中断的风险,如企业系统复杂性的增加,频繁的功能更新和发布等。如何确保业务连续性,提升韧性,成...

关键字: 亚马逊 解密 控制平面 BSP

8月30日消息,据媒体报道,腾讯和网易近期正在缩减他们对日本游戏市场的投资。

关键字: 腾讯 编码器 CPU

8月28日消息,今天上午,2024中国国际大数据产业博览会开幕式在贵阳举行,华为董事、质量流程IT总裁陶景文发表了演讲。

关键字: 华为 12nm EDA 半导体

8月28日消息,在2024中国国际大数据产业博览会上,华为常务董事、华为云CEO张平安发表演讲称,数字世界的话语权最终是由生态的繁荣决定的。

关键字: 华为 12nm 手机 卫星通信

要点: 有效应对环境变化,经营业绩稳中有升 落实提质增效举措,毛利润率延续升势 战略布局成效显著,战新业务引领增长 以科技创新为引领,提升企业核心竞争力 坚持高质量发展策略,塑强核心竞争优势...

关键字: 通信 BSP 电信运营商 数字经济

北京2024年8月27日 /美通社/ -- 8月21日,由中央广播电视总台与中国电影电视技术学会联合牵头组建的NVI技术创新联盟在BIRTV2024超高清全产业链发展研讨会上宣布正式成立。 活动现场 NVI技术创新联...

关键字: VI 传输协议 音频 BSP

北京2024年8月27日 /美通社/ -- 在8月23日举办的2024年长三角生态绿色一体化发展示范区联合招商会上,软通动力信息技术(集团)股份有限公司(以下简称"软通动力")与长三角投资(上海)有限...

关键字: BSP 信息技术
关闭
关闭