Distributed | DBLE SQL optimization through explain
Posted Jun 15, 2020 • 3 min read
Ai Kesheng DBA team member, responsible for database failure and platform problem resolution in the project, has a special liking for database high availability and distributed technology.
Source of this article:original submission
*Produced by Aikesheng open source community, original content can not be used at will without authorization, please reprint please contact editor and indicate the source.
During the verification of the application docking function in the customer DBLE test environment, it was found that a function did not produce results for a long time and reported an error when it timed out. Learned from the customer that the error will occur when the function cannot obtain back-end data return within a given time.
The current DBLE version is 2.19.07.3 and the database version is MySQL-5.7.25. The two tables cusvaa and cusm involved in the business SQL are split tables, and the data volume is 67 and 7600w respectively; the Join condition is the shard key and the stringhash used by the sharding rules is split into 8 shards, each Piece length is 10. After feedback from the customer engineer, the customer engineer asked me a question:"The association condition is the shard key, and the sharding rules are the same. Why is the execution efficiency so slow?"
With the customer's questions, the problem investigation started. The SQL that should be executed for this function at the time was as follows:
When I saw this screenshot, the first hunch was that the execution time was too long. Is Join's shard key condition invalid? After asking the customer for an execution plan.
The SQL execution plan at the DBLE layer is as follows:
After obtaining this information, you can basically confirm that the shard key has no effect. In the execution plan, we can clearly see that DBLE splits and delivers SQL to each shard, obtains and sorts the data from the shards in full, and performs MERGE and Join operations in the middle layer of DBLE.
Normally, SQL should be delivered to each shard, and the execution result of each shard should be returned to DBLE to directly perform MERGE operation.
Thinking of the same split rule stringhash that the customer said, I couldn't help but whisper. Has the customer configured hashSlice? Then let the customer provide the DBLE configuration file.
Seeing the rule.xml file provided by the customer, I did verify my conjecture. Although the same sharding rule stringhash is used, the specific sharding function function configuration is different. This is obviously two sharding rules.
After that, the customer and business development confirmed the value range of the shard key and adjusted the shard function. as follows:
After the adjustment, dynamic loading is performed to make the configuration take effect. View the execution plan of the SQL again:
After seeing this execution plan, confirm that the current shard key has played a role, and then let the customer perform functional verification again, and the result also responds in seconds.
The function configuration of Stringhash is not only to define partitionLengthand partitionCount, but also to configure the hashSlice binary. DBLE will judge all the contents of the sharding rules when performing SQL parsing and routing. SQL with the same sharding rules will be directly delivered to each shard. After the calculation result is returned to the DBLE layer for MERGE operation, otherwise It is necessary to go to the sharding to get all the data to the DBLE layer for MERGE and Join operations. When dealing with the problem of slow SQL execution in the dble environment, we not only need to obtain the execution status of SQL but also confirm whether the description provided by the customer to me is consistent with the actual situation.