You are viewing a plain text version of this content. The canonical link for it is here.
Posted to notifications@shardingsphere.apache.org by GitBox <gi...@apache.org> on 2020/12/08 07:12:16 UTC
[GitHub] [shardingsphere] qwaszx92717 opened a new issue #8528: “in” query slow
qwaszx92717 opened a new issue #8528:
URL: https://github.com/apache/shardingsphere/issues/8528
When using “in” query, and the parameters have thousands of “ID”, the query speed will be very slow. Use "_+partitioned " query speed is normal. Here are some configuration data and troubleshooting data.
config

query sharding log

testing environment
1.using sharding、50w data、sql:select * from table where column in (...)、condition have index, not shardingid. query time 15s
2.not used sharding,ditto. query time less 0.2s
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] tristaZero commented on issue #8528: “in” query slow
Posted by GitBox <gi...@apache.org>.
tristaZero commented on issue #8528:
URL: https://github.com/apache/shardingsphere/issues/8528#issuecomment-744193803
I guess you can get the execution time, like
```
[INFO ] 14:05:43.837 [ShardingSphere-Command-8] ShardingSphere-SQL - Logic SQL: select * from t_order where order_id in (1,2)
[INFO ] 14:05:43.837 [ShardingSphere-Command-8] ShardingSphere-SQL - SQLStatement: MySQLSelectStatement(limit=Optional.empty, lock=Optional.empty)
[INFO ] 14:05:43.837 [ShardingSphere-Command-8] ShardingSphere-SQL - Actual SQL: ds_0 ::: select * from t_order_0 where order_id in (1,2)
[INFO ] 14:05:43.837 [ShardingSphere-Command-8] ShardingSphere-SQL - Actual SQL: ds_0 ::: select * from t_order_1 where order_id in (1,2)
[INFO ] 14:05:43.837 [ShardingSphere-Command-8] ShardingSphere-SQL - Actual SQL: ds_1 ::: select * from t_order_0 where order_id in (1,2)
[INFO ] 14:05:43.837 [ShardingSphere-Command-8] ShardingSphere-SQL - Actual SQL: ds_1 ::: select * from t_order_1 where order_id in (1,2)
---- New one
[INFO ] 14:06:07.247 [ShardingSphere-Command-9] ShardingSphere-SQL - Logic SQL: select * from t_order_item
[INFO ] 14:06:07.247 [ShardingSphere-Command-9] ShardingSphere-SQL - SQLStatement: MySQLSelectStatement(limit=Optional.empty, lock=Optional.empty)
[INFO ] 14:06:07.247 [ShardingSphere-Command-9] ShardingSphere-SQL - Actual SQL: ds_0 ::: select * from t_order_item_0
[INFO ] 14:06:07.247 [ShardingSphere-Command-9] ShardingSphere-SQL - Actual SQL: ds_0 ::: select * from t_order_item_1
[INFO ] 14:06:07.247 [ShardingSphere-Command-9] ShardingSphere-SQL - Actual SQL: ds_1 ::: select * from t_order_item_0
[INFO ] 14:06:07.247 [ShardingSphere-Command-9] ShardingSphere-SQL - Actual SQL: ds_1 ::: select * from t_order_item_1
```
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] qwaszx92717 edited a comment on issue #8528: “in” query slow
Posted by GitBox <gi...@apache.org>.
qwaszx92717 edited a comment on issue #8528:
URL: https://github.com/apache/shardingsphere/issues/8528#issuecomment-742914942
> Hi @qwaszx92717 ,
>
> Thanks for your info.
> ShardingSphere helps user handle SQLs queried in a database cluster though, the consuming time your provided is longer than expected.
> The actual SQL is executed in MySQL using the index, isn't it?
> Have you ever counted how many seconds one actual SQL takes? I am curious where that `15s` is used.
I'm sorry I didn't make it clear. I'll give you another description
The problem I have encountered is that when there are too many query conditions, the query speed will be very slow. When 'in' params are 5000 or more , the query time will be about 10 seconds.
(select * from table where FILEINDEXID in('1021725517717865',......)
This colunm have index.
KEY `FILEID` (`FILEID`) USING BTREE
excute record

When I execute the SQL directly in one of the sharding tables of a sharding database, the query speed is faster (about 0.2S)
(select * from table_1 where FILEINDEXID in('1021725517717865',......)
after troubleshooting
Sharding log has no information when executing SQL.When the execution is completed, the following log will be printed.

When printing stack information, the stack information for executing SQL is as follows

----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] qwaszx92717 commented on issue #8528: “in” query slow
Posted by GitBox <gi...@apache.org>.
qwaszx92717 commented on issue #8528:
URL: https://github.com/apache/shardingsphere/issues/8528#issuecomment-742914942
> Hi @qwaszx92717 ,
>
> Thanks for your info.
> ShardingSphere helps user handle SQLs queried in a database cluster though, the consuming time your provided is longer than expected.
> The actual SQL is executed in MySQL using the index, isn't it?
> Have you ever counted how many seconds one actual SQL takes? I am curious where that `15s` is used.
This column have index.
At that time,I connect some shading database and try to execute SQL in some sharding table.
example:select * from **table_1** where column in (...); this query speed is very fast.
after troubleshooting ,I print the stack information ,found that most of the time it was pointing to sharding.
sharding log as above,but no useful information was found.
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] qwaszx92717 edited a comment on issue #8528: “in” query slow
Posted by GitBox <gi...@apache.org>.
qwaszx92717 edited a comment on issue #8528:
URL: https://github.com/apache/shardingsphere/issues/8528#issuecomment-742914942
> Hi @qwaszx92717 ,
>
> Thanks for your info.
> ShardingSphere helps user handle SQLs queried in a database cluster though, the consuming time your provided is longer than expected.
> The actual SQL is executed in MySQL using the index, isn't it?
> Have you ever counted how many seconds one actual SQL takes? I am curious where that `15s` is used.
This column have index.
At that time,I connect some shading database and try to execute SQL in some sharding table.
example:select * from **table_1** where column in (...); this query speed is very fast(0.2s,the parameters are about 6000).
after troubleshooting
Sharding log has no information when executing SQL.The above log will be printed until the execution is complete.
excute record

stack record

----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] qwaszx92717 edited a comment on issue #8528: “in” query slow
Posted by GitBox <gi...@apache.org>.
qwaszx92717 edited a comment on issue #8528:
URL: https://github.com/apache/shardingsphere/issues/8528#issuecomment-742914942
> Hi @qwaszx92717 ,
>
> Thanks for your info.
> ShardingSphere helps user handle SQLs queried in a database cluster though, the consuming time your provided is longer than expected.
> The actual SQL is executed in MySQL using the index, isn't it?
> Have you ever counted how many seconds one actual SQL takes? I am curious where that `15s` is used.
I'm sorry I didn't describe it clear. I'll give you another description
The problem I have encountered is that when there are too many query conditions, the query speed will be very slow. When 'in' params are 5000 or more , the query time will be about 10 seconds.
(select * from table where FILEINDEXID in('1021725517717865',......)
This colunm have index.
KEY `FILEID` (`FILEID`) USING BTREE
excute record

When I execute the SQL directly in one of the sharding tables of a sharding database, the query speed is faster (about 0.2S)
(select * from table_1 where FILEINDEXID in('1021725517717865',......)
after troubleshooting
Sharding log has no information when executing SQL.When the execution is completed, the following log will be printed.

When printing stack information, the stack information for executing SQL is as follows

----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] terrymanu closed issue #8528: “in” query slow
Posted by GitBox <gi...@apache.org>.
terrymanu closed issue #8528:
URL: https://github.com/apache/shardingsphere/issues/8528
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] terrymanu commented on issue #8528: “in” query slow
Posted by GitBox <gi...@apache.org>.
terrymanu commented on issue #8528:
URL: https://github.com/apache/shardingsphere/issues/8528#issuecomment-749896729
Closed because of no response
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] qwaszx92717 commented on issue #8528: “in” query slow
Posted by GitBox <gi...@apache.org>.
qwaszx92717 commented on issue #8528:
URL: https://github.com/apache/shardingsphere/issues/8528#issuecomment-743140843
sharding version :4.0.1
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] tristaZero commented on issue #8528: “in” query slow
Posted by GitBox <gi...@apache.org>.
tristaZero commented on issue #8528:
URL: https://github.com/apache/shardingsphere/issues/8528#issuecomment-741513676
Hi @qwaszx92717 ,
Thanks for your info.
ShardingSphere helps user handle SQLs queried in a database cluster though, the consuming time your provided is longer than expected.
The actual SQL is executed in MySQL using the index, isn't it?
Have you ever counted how many seconds one actual SQL takes? I am curious where that `15s` is used.
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] rouxiaoxiao commented on issue #8528: “in” query slow
Posted by GitBox <gi...@apache.org>.
rouxiaoxiao commented on issue #8528:
URL: https://github.com/apache/shardingsphere/issues/8528#issuecomment-857328584
I have the same problem, is there a solution? sharding version :4.0.1
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org