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

This article introduces the relevant knowledge of "the way of MySQL query optimization". In the operation of actual cases, many people will encounter such a dilemma, 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!

It is also useful to consider the EXPLAIN keyword when analyzing query performance. The EXPLAIN keyword is generally placed in front of the SELECT query statement to describe how MySQL performs the query operation and the number of rows that MySQL needs to execute to return the result set successfully. Explain can help us analyze select statements and let us know why the query is inefficient, so as to improve our query and make the query optimizer work better.

1. 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. The 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.

Each output row of EXPLAIN provides information about a table, and each row includes the following columns:

Item describes the sequence number of the query in the execution plan selected by idMySQL Query Optimizer. Indicates the order in which the select clause or action table is executed in the query. The higher the id value, the higher the priority, and the earlier it is executed. The id is the same, and the execution order is from top to bottom. Select_type query type description SIMPLE simple select query, does not use union and the second or subsequent select query in the outermost select query UNIONUNION of the PRIMARY, does not depend on the second or subsequent select query in the result set DEPENDENT UNIONUNION of the external query, depends on the first select query in the result set subquery of the external query, and does not depend on the first select query in the result set DEPENDENT subquery of the external query The result set DERIVED, which depends on the external query, is used in cases where there are subqueries in the from clause. MySQL recursively executes these subqueries and puts the results in a temporary table. Subqueries for which UNCACHEABLE SUBQUERY result sets cannot be cached must be re-evaluated for each row of the outer query. The second or subsequent select query in UNCACHEABLE UNIONUNION, which belongs to non-cacheable subquery items, describes the table type important items referenced by the table output row, shows the type used by the join, and sorts from the best to the worst type indicating that the system table has only one row (= system table). This is a special case of the const connection type. Constconst is used when comparing PRIMARY KEY with constant values. Use System when the query table has only one row. Eq_refconst is used when comparing PRIMARY KEY with constant values. Use System when the query table has only one row. Ref connections cannot select a single row based on keywords, and multiple eligible rows may be found. It is called ref because the index is compared to a reference value. This reference value is either a constant or the result value of a multi-table query in a table. Ref_or_null is like ref, but MySQL must find the null entry in the results of the initial search, and then do a second search. Index_merge indicates that index merge optimization is used. Unique_subquery uses this type in some IN queries, rather than the regular ref:value IN (SELECT primary_key FROM single_table WHERE some_expr) index_subquery in some IN queries, similar to unique_subquery, but the query is a non-unique index: value IN (SELECT key_column FROM single_table WHERE some_expr) range retrieves only a given range of rows, using an index to select rows. The key column shows which index is used. When using =, >, > =, 1 condition is a range value (so-called range), MySQL cannot use the index to retrieve the following views part, that is, the index after the range type query field is invalid.

Then we need to abandon comments and delete the old index:

The copy code is as follows:

DROP INDEX x ON article

Then create a new index:

The copy code is as follows:

ALTER TABLE `roomle` ADD INDEX y (`category_ id`, `views`)

Then run the query:

The copy code is as follows:

* * 1. Row *

Id: 1

Select_type: SIMPLE

Table: article

Type: ref

Possible_keys: y

Key: y

Key_len: 4

Ref: const

Rows: 1

Extra: Using where

1 row in set (0.00 sec)

As you can see, type becomes the Using filesort in ref,Extra and disappears, and the result is ideal.

Let's look at an example of a multi-table query.

First define three tables class and room.

The copy code is as follows:

CREATE TABLE IF NOT EXISTS `class` (

`id`int (10) unsigned NOT NULL AUTO_INCREMENT

`card` int (10) unsigned NOT NULL

PRIMARY KEY (`id`)

);

CREATE TABLE IF NOT EXISTS `book` (

`bookid` int (10) unsigned NOT NULL AUTO_INCREMENT

`card` int (10) unsigned NOT NULL

PRIMARY KEY (`bookid`)

);

CREATE TABLE IF NOT EXISTS `phone` (

`phoneid` int (10) unsigned NOT NULL AUTO_INCREMENT

`card` int (10) unsigned NOT NULL

PRIMARY KEY (`phoneid`)

) engine = innodb

Then insert a large amount of data separately. Php script that inserts the data:

The copy code is as follows:

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.