GreatSQL茶话会——分享一下你的五一假期

GreatSQL社区

搜索

[已解决] MYSQL 5.7 RDS OPTIMER TRACE 优化问题

482 5 2023-6-8 16:48
本帖最后由 innerDBA 于 2023-6-8 16:48 编辑

我有个两表关联的SQL, 一个业务表,一个字典表, 然后 ORDER BY REQUES_DATE_TIME  DESC  LIMIT 0,10;  执行了5秒, 如果使用PAY_TIME 索引执行245毫秒
使用OPTIMER 去分析为什么? 使用REQEST_DATE_TIME 索引



上面显示执行计划选择走PAY_TIME索引
行评估:

也是PAY_TIME索引优先.


附件条件影响下使用REQUEST_DATETIME_IDX



最终执行计划


我猜测 优化器认为 通过PAY_TIME返回符合条件的行,要进行排序成本,高于通过REQUEST_DATE_TIME 索引消除FILE SORT操作!
不过排序成本 两个索引OPTIMER 并没有去分析跟踪出来!
下面是JSON格式的执行计划:
  1. {
  2.   "query_block": {
  3.     "select_id": 1,
  4.     "cost_info": {
  5.       "query_cost": "88932.23"
  6.     },
  7.     "ordering_operation": {
  8.       "using_filesort": false,
  9.       "nested_loop": [
  10.         {
  11.           "table": {
  12.             "table_name": "a",
  13.             "partitions": [
  14.               "request_date_time_20221026",
  15.               "request_date_time_20230426",
  16.               "request_date_time_20230503",
  17.               "request_date_time_20230510",
  18.               "request_date_time_20230517",
  19.               "request_date_time_20230524"
  20.             ],
  21.             "access_type": "range",
  22.             "possible_keys": [
  23.               "request_date_time_idx",
  24.               "merchant_trans_no_idx",
  25.               "channel_code_product_code_sktrans_idx",
  26.               "pay_time_sktrans_idx"
  27.             ],
  28.             "key": "request_date_time_idx",
  29.             "used_key_parts": [
  30.               "request_date_time"
  31.             ],
  32.             "key_length": "5",
  33.             "rows_examined_per_scan": 638300,
  34.             "rows_produced_per_join": 17,
  35.             "filtered": "0.00",
  36.             "index_condition": "((`wayangpay`.`a`.`request_date_time` >= '2023-04-19 00:00:00') and (`wayangpay`.`a`.`request_date_time` <= '2023-05-19 23:59:59'))",
  37.             "cost_info": {
  38.               "read_cost": "88936.69",
  39.               "eval_cost": "3.52",
  40.               "prefix_cost": "88940.21",
  41.               "data_read_per_join": "298K"
  42.             },
  43.             "used_columns": [
  44.               "id",
  45.               "channel_code",
  46.               "channel_product_code",
  47.               "amount",
  48.               "request_date_time",
  49.               "customer_name",
  50.               "email",
  51.               "mobile_phone",
  52.               "collect_code",
  53.               "settle_no",
  54.               "status",
  55.               "remit_no",
  56.               "trans_fee_rate",
  57.               "total_trans_fee",
  58.               "trans_fee",
  59.               "platform_trans_fee_rate",
  60.               "platform_trans_fee",
  61.               "merchant_id",
  62.               "merchant_no",
  63.               "trans_no",
  64.               "cp_refund_no",
  65.               "merchant_trans_no",
  66.               "merchant_refund_no",
  67.               "channel_trans_no",
  68.               "merchant_notify_url",
  69.               "notifty_status",
  70.               "extra_content",
  71.               "version",
  72.               "pay_time",
  73.               "create_time",
  74.               "last_update_time",
  75.               "invalidate_time",
  76.               "va_code",
  77.               "va_code_expired_time",
  78.               "goods_info",
  79.               "source_from",
  80.               "pay_back_name",
  81.               "id_card",
  82.               "account_name",
  83.               "bank_name",
  84.               "branch_bank_name",
  85.               "cit_name",
  86.               "location_code",
  87.               "initial_amount"
  88.             ],
  89.             "attached_condition": "((`wayangpay`.`a`.`channel_code` = '153') and (`wayangpay`.`a`.`location_code` = 'ID') and (`wayangpay`.`a`.`status` = '02') and (`wayangpay`.`a`.`channel_product_code` = 'DANAWALLET') and (`wayangpay`.`a`.`pay_time` >= '2023-05-19 00:00:00') and (`wayangpay`.`a`.`pay_time` <= '2023-05-19 23:59:59'))"
  90.           }
  91.         },
  92.         {
  93.           "table": {
  94.             "table_name": "c",
  95.             "access_type": "ref",
  96.             "possible_keys": [
  97.               "INX_M_SMI"
  98.             ],
  99.             "key": "INX_M_SMI",
  100.             "used_key_parts": [
  101.               "merchant_no"
  102.             ],
  103.             "key_length": "123",
  104.             "ref": [
  105.               "wayangpay.a.merchant_no"
  106.             ],
  107.             "rows_examined_per_scan": 1,
  108.             "rows_produced_per_join": 7,
  109.             "filtered": "40.00",
  110.             "cost_info": {
  111.               "read_cost": "17.59",
  112.               "eval_cost": "1.41",
  113.               "prefix_cost": "88961.31",
  114.               "data_read_per_join": "168K"
  115.             },
  116.             "used_columns": [
  117.               "id",
  118.               "merchant_no",
  119.               "register_name",
  120.               "short_name",
  121.               "salesman_no",
  122.               "merchant_type",
  123.               "subject_name"
  124.             ],
  125.             "attached_condition": "(`wayangpay`.`c`.`subject_name` in ('AESYGO','KOALA FIRST','TYGATEWAY','PAYOK'))"
  126.           }
  127.         }
  128.       ]
  129.     }
  130.   }
  131. }
复制代码



全部回复(5)
只看楼主
innerDBA 2023-6-8 17:05:28
我另外把SQL 丢进历史库,并且更改了年份, 它走第三个索引 CHANNEL_CODE. 我查了下该时间段没有值. 业务表是分区表,7天一个分区,使用REQUEST_DATE_TIME字段为分区.

历史库的OPTIMER 附加条件下 没有任何影响. LIMIT 这个没有出现在OPTIMER JSON里. 然后选择FILE SORT 方式.

这感情历史库 更加聪明吧! 难道是小版本的差异导致 生产库和历史库优化器的不同
InnerCodeDBA
回复 采纳

举报

innerDBA 2023-6-8 17:08:30
生产库 小版本: rds_20211231  历史库小版本:rds_20211231  看来是一样的!
我查了下 CHANNEL_CODE =153 生产库有4.6万
InnerCodeDBA
回复 采纳

举报

innerDBA 2023-6-8 17:11:38
本帖最后由 innerDBA 于 2023-6-8 17:13 编辑

我在历史库 选择个8万的CHNNAEL_CODE ,结果也选择REQUEST_DATE_TIME索引
  1.     from
  2.       trans_online a
  3.       left join merchant_info c on a.merchant_no = c.merchant_no
  4.     where
  5.       1 = 1
  6.       and a.location_code = 'BR'
  7.       and a.status = '01'
  8.       and a.channel_code = '0X8'
  9.       and a.channel_product_code = 'ZXIS'
  10.       and a.request_date_time >= '2022-04-19 00:00:00'
  11.       and a.request_date_time <= '2022-05-19 23:59:59'
  12.       and a.pay_time >= '2022-05-19 00:00:00'
  13.       and a.pay_time <= '2022-05-19 23:59:59'
  14.       and c.subject_name in ('SHARK', 'CHIANNEL', 'DORILS', 'LV')
  15.     order by      request_date_time desc
  16.     limit      0, 10;
复制代码
InnerCodeDBA
回复 采纳

举报

yejr 2023-6-8 22:38:49
表DDL、统计信息、执行计划（可以EXPLAIN + EXPLAIN ANALYZE两种都尝试下）也贴出来
回复 采纳

举报

张旭峰 2023-6-9 09:14:52
yejr 发表于 2023-6-8 22:38
表DDL、统计信息、执行计划（可以EXPLAIN + EXPLAIN ANALYZE两种都尝试下）也贴出来 ...

是的 叶老师说的得对 ,有很多情况下 explain 普通的执行计划 ,就可以判断不出 ,要是看不出来在 看json 和 树形格式的执行计划 别一上来就整这么复杂的
回复 采纳

举报

innerDBA

12

主题

0

博客

61

贡献

注册会员

Rank: 2

积分
92

助人为乐（铜）勤学好问（铜）

发消息
互动与交流
社区活动
论坛问答
加入社区
贡献者指南
学习与应用
文章博客
文档资料
视频资料
常见问题
发现社区
社区准则
建议反馈
社区之星

合作电话：010-64087828

社区邮箱：greatsql@greatdb.com

社区公众号
社区小助手
QQ群
友情链接
万里数据库开源中国墨天轮开放原子开源基金会OpenEuler
©2022 GreatSQL All Rights Reserved. ( 京ICP备06057874号 )
GMT+8, 2024-5-17 17:34 , Processed in 0.028417 second(s), 14 queries , Redis On.
快速回复 返回顶部 返回列表