运行 sql 时 warings 日志如何定位？

[Tongzhenguo]

mysql -u root -h 10.16.16.131 -P 4000 --comments
CREATE TABLE feature.user_hot_click_list_weekly (
    gid BIGINT NOT NULL,
    feat_val TEXT NOT NULL,
    create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY (gid)
)
;
CREATE UNIQUE INDEX user_unique_gid ON feature.user_hot_click_list_weekly (gid);

set @gid=15972817;
select /*+ read_from_storage(tiflash[a]) */ gid,feat_val,create_time
from feature.user_hot_click_list_weekly a
where gid = @gid \G
*************************** 1. row ***************************
        gid: 15972817
   feat_val: XXXXXX
create_time: 2020-06-13 08:55:24
1 row in set, 1 warning (4.04 sec)

MySQL [feature]> select @@version;
+--------------------+
| @@version          |
+--------------------+
| 5.7.25-TiDB-v4.0.0 |

q1:我的查询变慢了？如何定位？
q2:如上waring日志在哪？tidb dashboard不知道怎么搜索关键字

[yilong (yi888long)]

1. 查看 https://docs.pingcap.com/zh/tidb/v4.0/identify-slow-queries#慢查询日志
2. 查看 执行计划是否有改变 https://docs.pingcap.com/zh/tidb/v4.0/query-execution-plan#理解-tidb-执行计划
3. 查看监控，系统负载是否太高

[Tongzhenguo]

关于慢查询：

set @gid=20892449;
MySQL [(none)]> select gid,feat_val,create_time
    -> from feature.user_hot_click_list_weekly a
    -> where gid = @gid \G
*************************** 1. row ***************************
        gid: 20892449
   feat_val: xxxxxx
create_time: 2020-06-14 18:31:10
1 row in set (3.97 sec)

MySQL [(none)]> select gid,feat_val,create_time
    -> from feature.user_hot_click_list_weekly a
    -> where gid = 20892449 \G
*************************** 1. row ***************************
        gid: 20892449
   feat_val: xxxxxxx
create_time: 2020-06-14 18:31:10
1 row in set (0.00 sec)

这个大佬们帮解释下加一个@gid变量，性能影响这么大？

2.关于waring，去掉了hint/*+ read_from_storage(tiflash[a]) */之后没有再waring，怀疑和cbo有关，这个可以怎么继续定位下？

*************************** 3. row ***************************
id: └─TableFullScan_8
estRows: 6840275.00
actRows: 6840146
task: cop[tiflash]
access object: table:a
execution info: proc max:661.005266ms, min:15.000119ms, p80:518.004128ms, p95:587.004676ms, iters:255, tasks:77
operator info: keep order:false
memory: N/A
disk: N/A

[来了老弟]

@Tongzhenguo

1 row in set, 1 warning (4.04 sec)

可以在语句执行完毕并执行，show warnings 来查看

@Tongzhenguo

这个大佬们帮解释下加一个@gid变量，性能影响这么大？

自定义变量无法使用 point get 所以会比较慢，此类简单 sql 直接指定 value 进行查询，