我艹,MySQL数据量大时,delete操作无法命中索引。
扫描二维码
随时随地手机看文章
来自:Java面试那些事儿
最近,在脉脉上看到一个楼主提出的问题: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 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;
# 当完成后,关闭trace
SET 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;因此,优化器倾向于选择全表扫描。
今晚上就熬夜写到这里吧。
-----------------------------
特别推荐一个分享架构+算法的优质内容,还没关注的小伙伴,可以长按关注一下:
长按订阅更多精彩▼
如有收获,点个在看,诚挚感谢
免责声明:本文内容由21ic获得授权后发布,版权归原作者所有,本平台仅提供信息存储服务。文章仅代表作者个人观点,不代表本平台立场,如有问题,请联系我们,谢谢!