Shulou(Shulou.com)06/01 Report--

Optimization after changing not in to not exists

Recently, there is a slow SQL in the OA database with an execution time of 3 minutes and 16 seconds. This slow SQL statement may be run several times a month, but it always causes database CPU utilization to soar. Then I tested the rewriting of the slow SQL statement, and the run time was reduced to 13s (it's still slow, but it's 18 times faster).

The specific analysis process is as follows: slow SQL captured through slow log and its running time: 1 select id,start_member_id,start_date,modify_member_id,modify_date from formmain_0141 where id not in (select content_data_id from ctp_content_all where content_template_id='6890363387462501722' and content_data_id is not null) limit 20000, 10 000\ G Empty set (3 min 2.01 sec)

It can be seen that in production, the running time of the statement is 3 minutes and 2 seconds.

Let's take a look at its implementation plan and why it is so slow:

2. My rewritten index uses not exists and internal and external interactive subqueries:

Mysql > select id,start_member_id,start_date,modify_member_id,modify_date from formmain_0141 where not exists (select 1 from ctp_content_all where content_data_id= formmain_0141.id and content_data_id is not null and content_template_id='6890363387462501722') limit 20000, 10000

Empty set (13.84 sec)

After using not exists, the execution time is reduced to 13 seconds, and the efficiency is significantly improved.

Let's take another look at the execution plan of the optimized statement:

The reason why not in is rewritten as not exists fast, I would like to use the principle of ICP, the new feature of mysql 5.6. in the rewritten sql statement, MySQL starts to judge whether id filtering can be carried out in the formmain_0141 table while taking data from the formmain_0141 all table, thus greatly reducing the record index of the upper layer to the SQL layer and improving the overall performance of the database. On the contrary, the sql statement before optimization is to take out all the eligible records in the ctp_content_all table, and then filter the id field in the formmain_0141 table, so it is slow.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.