Shulou(Shulou.com)05/31 Report--

Editor to share with you how to optimize the mysql related query, I believe that most people do not know much about it, so share this article for your reference, I hope you can learn a lot after reading this article, let's go to understand it!

Any associated query in mysql is a nest loop (nested loop) operation. Nest loop takes a piece of data from the driven table, compares it row by row from the driven table, puts those in accordance with the rules into the result set, and then cycles through the next row. Each row returned by the driven table is scanned once.

The nest loop association mechanism needs to be optimized from the following aspects:

1. Reduce the number of nest loop cycles, use small result sets as driving tables, and drive large result sets.

2. The driven table must be scanned every cycle, so it is required that there must be an index on the associated key, and the selectivity is good.

3. If the second item cannot be satisfied, you can set the size of the join buffer by adjusting join_buffer_size, but it is recommended to add an index instead of simply increasing the join_buffer_size

Next, learn about mysql's nest loop through the following experiments

Experimental environment: Percona server5.6.27 large table bill, small table user, all have indexes on the table

Mysql > select count (*) from bill

+-+

| | count (*) |

+-+

| | 1966789 |

+-+

Mysql > select count (*) from user_tmp

+-+

| | count (*) |

+-+

| | 36317 |

+-+

I. implementation of the plan:

Mysql > explain select a. Userkeeper from bill b left JOIN user_tmp an on a.user_id=b.user_id b.loankeeper infographic id

+-- +

| | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |

+-- +

| | 1 | SIMPLE | b | ALL | NULL | NULL | NULL | NULL | 1912096 | NULL |

| | 1 | SIMPLE | a | eq_ref | PRIMARY | PRIMARY | 194 | CDM.b.user_id | 1 | Using index |

+-- +

No matter how big the left table is, the left table is always driven, and the right table is always driven.

Mysql > explain select a. Userkeeper from bill b INNER JOIN user_tmp an on a.user_id=b.user_id b.loankeeper infographic id

+-+- -- +

| | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |

+-+- -- +

| | 1 | SIMPLE | a | index | PRIMARY | PRIMARY | 194 | NULL | 35970 | Using index |

| | 1 | SIMPLE | b | ref | in_bill_user_id | in_bill_user_id | 194 | CDM.a.user_id | 3 | NULL |

+-+- -- +

2 rows in set (0.00 sec)

Inner join, the optimizer of mysql will automatically select the small table user_tmp as the driver table based on the statistical information. You can see that the rows column value is inconsistent with the number of rows we just counted, because there is a difference between the statistical information and the actual, so sometimes the inaccurate statistical information will cause the execution plan to be not optimal. Inner joins can be performed sequentially with STRAIGHT_JOIN, that is, the left table is designated as the driver table

Mysql > explain select STRAIGHT_JOIN a. Userkeeper from bill b inner JOIN user_tmp an on a.user_id=b.user_id b.loankeeper infographic id

+-+-- +-+

| | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |

+-+-- +-+

| | 1 | SIMPLE | b | ALL | in_bill_user_id | NULL | NULL | NULL | 1912096 | NULL |

| | 1 | SIMPLE | a | eq_ref | PRIMARY | PRIMARY | 194 | CDM.b.user_id | 1 | Using index |

+-+-- +-+

At this time, mysql will not use the small table on the right as the driver table according to the statistics.

Delete driven table bill index

Mysql > explain select a. Userkeeper from bill b INNER JOIN user_tmp an on a.user_id=b.user_id b.loankeeper infographic id

+-- +

| | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |

+-- +

| | 1 | SIMPLE | b | ALL | NULL | NULL | NULL | NULL | 1905575 | NULL |

| | 1 | SIMPLE | a | eq_ref | PRIMARY | PRIMARY | 194 | CDM.b.user_id | 1 | Using index |

+-- +

The mysql optimizer is based on cost. If bill has no index, the entire bill table will be scanned 35970 times, and the cost is higher than that of 1905575 user_tmp index scans. Therefore, the execution plan has been changed to use the large table as the driving table, thus reducing the query efficiency.

Second, execution efficiency (all associated keys have indexes):

When the small watch is driving the watch

Mysql > select a. Userkeeper from bill b INNER JOIN user_tmp an on a.user_id=b.user_id b.loankeeper infographic id

There are tens of thousands of result sets here, omitted

Time: 0.202s

When using STRAIGHT_JOIN to force large tables to drive tables

Mysql > select STRAIGHT_JOIN a. Userkeeper from bill b INNER JOIN user_tmp an on a.user_id=b.user_id b.loankeeper infographic id

Time: 5.260s

Since the difference between the two tables is tens of times, the efficiency of the two kinds of execution plans is also obvious.

Note: if the index selectivity of the associated key of the large table is poor (such as too much duplicate data, etc.), and there are too many sweeps in each cycle, it is better to let the large table be the driving table. The above experiment is the result of the good index selectivity of the large table.

The above is all the contents of the article "how to optimize mysql related queries". Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to follow the industry information channel!

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.