Mysql database performance optimization sub-query and mysql database performance optimization

Mysql database performance optimization sub-query and mysql database performance optimization

I remember that when I was working on a project, I heard a sentence and tried not to use subqueries. So let's take a look at this article to see if this sentence is correct.

Before that, we need to introduce some conceptual things and mysql's rough statement processing.

After the connection thread of the Mysql Server receives the SQL request sent by the Client, it breaks down the Parse and analyzes it accordingly. Then, Mysql uses the query optimizer module, calculate and analyze data tables involved in the SQL statement. then we come up with a data access method that Mysql thinks is the most reasonable and optimal, that is, the "Execution Plan" we often call ", then, according to the execution plan, call the storage engine interface to obtain the corresponding data. then, the data returned by the storage engine is processed and the format required by the Client is used as the result set and returned to the Client.

Note: The statistical data here is the data statistics we obtain after we use the Analyze table command to notify Mysql to Analyze the related data of the table. these data is very important for the Mysql optimizer. The execution plan generated by the optimizer is mainly determined by these statistics.

1. Create a table

Create table User (Id int not null PRIMARY key auto_increment, NickName varchar (50) comment 'user NickName ', Sex int comment 'gender', Sign varchar (50) comment 'user sign', Birthday datetime comment 'user birthday', CreateTime datetime comment 'creation Time') default charset = utf8 comment 'user table '; create table UserGroup (Id int not null PRIMARY key auto_increment, UserId int not null comment 'user id', GroupId int not null comment 'user group id', CreateTime datetime comment 'creation time ', -- key index_groupid (GroupId) using btree, key index_userid (groupid, UserId) using btree) default charset = utf8 comment 'user group table ';

2. Prepare data

Var conStr = ConfigurationManager. connectionStrings ["ConStr"]. toString (); using (IDbConnection conn = new MySqlConnection (conStr) {Stopwatch watch = new Stopwatch (); var SQL = string. empty; var names = new string [] {"not", "think", "Red", "Emperor", "de", "watch", "Mei ", "insert", "rabbit"}; Random ran = new Random (); var insertSql = @ "insert into User (NickName, Sex, Sign, Birthday, CreateTime) values (@ NickName, @ Sex, @ Sign, @ Birthday, @ CreateTime); insert into usergroup (UserId, GroupId, CreateTime) VALUES (LAST_INSERT_ID (), @ GroupId, @ CreateTime); "; watch. start (); if (conn. state = ConnectionState. closed) {conn. open ();} var tran = conn. beginTransaction (); for (int I = 0; I <100000; I ++) {var param = new {NickName = names [ran. next (9)] + names [ran. next (9)] + I, Sign = names [ran. next (9)] + names [ran. next (9)], CreateTime = DateTime. now, Birthday = DateTime. now. addYears (ran. next (10, 30), Sex = I % 2, GroupId = ran. next (1,100)}; conn. execute (insertSql, param, tran);} tran. commit (); conn. dispose (); watch. stop (); Console. writeLine (watch. elapsedMilliseconds );}

Here I have inserted 5000 pieces of data, and the group is divided into 99 groups, which are random.

3. query SQL

 explainselect user.id, user.nickname from usergroup left join user on usergroup.UserId = user.Idwhere usergroup.groupid = 1 order by usergroup.UserId desclimit 100, 20; explainselect user.id, user.nicknamefrom (select id, userid from usergroup where groupid = 1 order by userid limit 100, 20) tleft join user on t.UserId = user.id ; explainselect user.id, user.nicknamefrom (select id, userid from usergroup where groupid = 1 order by userid ) tleft join user on t.UserId = user.id limit 100, 20;

Both the second and third sentences use subqueries. The difference is that the second sentence gets 20 pieces of data first and then associates with the user table.

4. Analysis

100000 data entries:

First sentence

Let's look at the second sentence.

Third sentence

From the above three pictures, we can see something.

First, let's look at their rows. The second sentence is the most, which adds up to more than 1000, and the other two statements add up to 996. but what I want to say is, here we don't look at the sum of rows. the correct method is to execute statements with the same id from top to bottom.

First, let's look at the statement of id = 2 in the second sentence and the statement of id = 1 in the first sentence. They all filter data from the usergroup table and get the same result set.

It seems that they all perform operations based on the same result set, and then there is a difference.

First read the first sentence, and then join the table user to the left based on the result set A, filter the final data, and return it to the client.

Then, based on A, filter the data again to obtain the required data and connect it to the left of the user table to obtain the final result.

From the above, the second execution plan is more efficient.

If you can use subqueries to greatly narrow the query scope, you can use subquery statements.

The above is a subquery of Mysql database performance optimization introduced by xiaobian. I hope it will be helpful to you. If you have any questions, please leave a message and I will reply to you in a timely manner. Thank you very much for your support for the help House website!