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

This article introduces the knowledge of "how MySQL query optimizer works". Many people will encounter this dilemma in the operation of actual cases, so let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

How does the MySQL query optimizer work

Of course, the MySQL query optimizer also makes use of indexes, but it also uses some other information. For example, if you submit a query like this, MySQL executes it very quickly, no matter how large the data table is:

SELECT*FROMtbl_nameWHERE0

In this example, MySQL looks at the WHERE clause and realizes that there are no rows of data that meet the query criteria, so it doesn't consider searching the data table at all. You can see this by providing an EXPLAIN statement that lets MySQL display some information about SELECT queries that it executed but didn't actually execute. If you want to use EXPLAIN, just put the EXPLAIN word in front of the SELECT statement:

Mysql > EXPLAINSELECT*FROMtbl_nameWHERE0\ G**1.row**id:1select_type:SIMPLEtable:NULLtype:NULLpossible_keys:NULLkey:NULLkey_len:NULLref:NULLrows:NULLExtra:ImpossibleWHERE

In general, EXPLAIN returns a little more information than above, and also includes non-empty (NULL) information such as the index used to scan the data table, the type of join used, and the estimated number of rows in each data table that need to be checked.

How the MySQL query optimizer works

The MySQL query optimizer has several goals, but the main goal is to use indexes as much as possible and to use the strictest indexes to eliminate as many rows of data as possible. Your ultimate goal is to submit SELECT statements to find data rows, not to exclude them. The reason the optimizer tries to exclude data rows is that the faster it excludes data rows, the faster it will find data rows that match the criteria. If the strictest tests can be done first, the query can be executed faster. Suppose your query examines two data columns, each with an index:

SELECTcol3FROMmytableWHEREcol1='somevalue'ANDcol2='someothervalue'

Suppose the test on col1 matches 900 data rows, the test on col2 matches 300 data rows, and the simultaneous test results in only 30 data rows. The first test Col1 will have 900 data rows, you need to check them to find 30 records that match the values in the col2, of which 870 times have failed. The first test col2 will have 300 data rows, you need to check them to find 30 of the records that match the values in col1, only 270 times have failed, so less calculation and disk I will be required. As a result, the optimizer tests col2 first because it is less expensive to do so.

You can help the optimizer make better use of the index with the following guidance:

Try to compare data columns of the same data type. When you use index data columns in a comparison operation, use columns of the same data type. The same data type performs better than different types. For example, INT is different from BIGINT. CHAR (10) is considered to be CHAR (10) or VARCHAR (10), but different from CHAR (12) or VARCHAR (12). If you are comparing data columns of different types, you can use ALTERTABLE to modify one of them so that their types match.

That's all for "how the MySQL query optimizer works". Thank you for reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!

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.