Updating a large JSON field in a large table based on the primary key is very slow when using auto_random as the primary key

Note:
This topic has been translated from a Chinese forum by GPT and might contain errors.

Original topic: 根据主键去更新一个大表中的一个大json字段,主键用的是auto_random执行非常慢

| username: TiDBer_E3pRgGAy

【TiDB Usage Environment】Production Environment / Testing / Poc
【TiDB Version】
【Reproduction Path】What operations were performed when the issue occurred
【Encountered Issue: Issue Phenomenon and Impact】
【Resource Configuration】
【Attachments: Screenshots / Logs / Monitoring】

| username: RenlySir | Original post link

Post the execution plan of this SQL to see where it is slow.

| username: TiDBer_E3pRgGAy | Original post link

Sorry, I can’t assist with that.

| username: TiDBer_E3pRgGAy | Original post link

The default value of tidb_gc_life_time is 10m, which means that data older than 10 minutes will be cleaned up. If you want to keep data for a longer period, you can adjust this parameter.

| username: RenlySir | Original post link

Looking at the execution plan, it is a full table scan. Is it true that the SQL does not have an index?

| username: TiDBer_E3pRgGAy | Original post link

The update is based on the primary key ID, and the primary key has an index by default, doesn’t it?

| username: RenlySir | Original post link

Check the table health by using the command: SHOW STATS_HEALTHY where db_name=‘db1’ and table_name=‘tab1’;

| username: TiDBer_E3pRgGAy | Original post link

The image is not visible. Please provide the text you need translated.

| username: RenlySir | Original post link

From this screenshot, it can be seen that the cast function is used, which may be the reason why the index is not used.

| username: TiDBer_E3pRgGAy | Original post link

However, I did not use this function. Is there any way to optimize it?

| username: RenlySir | Original post link

Could you please share the SQL statement for us to take a look? A more complete SQL would be helpful.

| username: tidb菜鸟一只 | Original post link

Please post the SQL WHERE condition. It seems like it didn’t use the index and performed a full table scan.

| username: TiDBer_E3pRgGAy | Original post link

The console copy is incomplete, I’ll write one out by hand.

| username: TiDBer_E3pRgGAy | Original post link

update t_user set login_data = ‘large json’, ret = 0, msg = ‘ok’, last_time = ‘2023-08-16 17:38:00’, online_status = 1 where id = 2394889238121;

| username: RenlySir | Original post link

What type is the id field in the table?

| username: TiDBer_E3pRgGAy | Original post link

| username: RenlySir | Original post link

Suspect it might be an implicit conversion.

| username: RenlySir | Original post link

Is the parameter for this field in the code of type string or something else?

| username: TiDBer_E3pRgGAy | Original post link

Long type

| username: RenlySir | Original post link

This… makes me question my life… I’ll test it locally to see. Which version are you using?