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

In this issue, the editor will bring you about what is the best index strategy in MySQL. The article is rich in content and analyzes and describes it from a professional point of view. I hope you can get something after reading this article.

The so-called index is to sort some specific algorithms for specific mysql fields, such as binary tree algorithm and hash algorithm, hash algorithm is through the establishment of eigenvalues, and then quickly find according to eigenvalues. And the most frequently used, and the default of mysql is the binary tree algorithm BTREE. Fields indexed by BTREE algorithm, such as scanning 20 rows before using BTREE, can get the results of scanning 2 ^ 20 rows before using BTREE. The specific implementation method will be analyzed and discussed in a specific algorithm topic later in this blog.

Explain optimized query Detection

EXPLAIN can help developers analyze SQL problems. Explain shows how mysql uses indexes to handle select statements and join tables, which can help select better indexes and write more optimized query statements.

To use the method, add Explain before the select statement:

Explain select * from blog where false

Before executing a query, mysql analyzes each SQL issued to decide whether to use an index or a full table scan. If a select * from blog where falseMysql is sent, the query operation will not be performed, because after the analysis of the SQL parser, MySQL has made it clear that there will not be any statements that match the operation.

Example

Mysql > EXPLAIN SELECT `birday` FROM `user`WHERE `dayday`

Const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL,const means * at once, and ALL means to scan the whole table before determining the result. In general, it is necessary to ensure that the query reaches at least range level, and * * can reach ref. Possible_keys: birthday-- indicates which index MySQL can use to find rows in this table. If it is empty, there is no relevant index. To improve performance at this point, check the WHERE clause to see if some fields are referenced, or check that the fields are not suitable for the index. Key: birthday-- the index actually used. If NULL, the index is not used. If primary, the primary key is used. Key_len: 4-the longest index width. If the key is NULL, the length is NULL. Without losing accuracy, the shorter the length, the better ref: const-- shows which field or constant is used with key. Rows: 1-- this number indicates how much data mysql has to traverse to find it, which is not accurate on innodb. Extra: Using where; Using index-execution status description. The bad examples you can see here are Using temporary and Using.

Select_type

Simple simple select (without union or subquery)

The outermost select of primary

The second or subsequent select statement in union union

The second or subsequent select statement in dependent union union, depending on the external query

Results of union result union.

* select in subquery subquery

* select in the dependent subquery subquery, depending on the external query

Derived exports the select of the table (a subquery of the from clause)

Details of Extra and type

Distinct: once MYSQL finds a row that matches the row union, it no longer searches

Not exists: MYSQL optimizes LEFT JOIN so that once it finds rows that match the LEFT JOIN standard, it no longer searches

Range checked for each Record (index map:#): no ideal index was found, so for each combination of rows from the previous table, MYSQL checks which index to use and uses it to return rows from the table. This is one of the slowest connections to use an index

Using filesort: when you see this, the query needs to be optimized. MYSQL needs to take additional steps to discover how to sort the returned rows. It sorts all rows according to the connection type and the row pointer to all rows that store the sort key values and matching criteria.

Using index: column data is returned from a table that only uses the information in the index and does not read the actual action, which occurs when all request columns on the table are part of the same index

When Using temporary sees this, the query needs to be optimized. Here, MYSQL needs to create a temporary table to store the results, which usually occurs on the ORDER BY of different sets of columns, not on the GROUP BY

Where used uses the WHERE clause to restrict which rows will match the next table or be returned to the user. This can happen if you do not want to return all the rows in the table and the join type is ALL or index, or the query has a problem with the interpretation of different join types (sorted in order of efficiency

The system table has only one row: the system table. This is a special case of the const connection type

Const: the * * value of a record in the table can match this query (the index can be a primary key or a unique index). Because there is only one line, this value is actually a constant, because MYSQL reads the value first and then treats it as a constant.

Eq_ref: in a join, when MYSQL queries, the union of each record from the previous table reads a record from the table, which is used when the query uses the index as the primary key or all of the unique key.

Ref: this join type occurs only when the query uses keys that are not unique or primary keys or parts of these types (for example, using the leftmost prefix). For each row join of the previous table, all records are read out from the table. This type depends heavily on how many records are matched by the index-the less, the better.

Range: this connection type uses an index to return rows in a range, such as using > or

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.