Shulou(Shulou.com)06/01 Report--

Preface

To analyze the relevant information in the MySQL query, such as a full table query or a partial query, use explain.

Advantages of indexing

Greatly reducing the amount of data that the server needs to scan can help the server avoid sorting or reduce the use of temporary table sorting indexes can be changed from random I _ O to sequential I _ O

Shortcomings of the index

Need to take up disk space, so redundant and inefficient indexes will take up a lot of disk space to reduce DML performance, for any addition or deletion of data, it is necessary to adjust the corresponding index, and even split the index will produce corresponding fragments, resulting in maintenance overhead.

1. Explain

Usage: explain + query statement.

Id: the sequence number of the query statement. There is only one select statement in the above picture, so only one sequence number will be displayed. If there is a nested query, as follows

Select_type: indicates the query type, which can be divided into the following

Simple: simple select (without using union or subqueries)

Primary: the outermost select.

Union: the second layer uses union after select.

The second select in the dependent union:union statement, which depends on the external subquery

Subquery: the first select in the subquery

Dependent subquery: the first subquery in a subquery depends on an external subquery

Derived: derived table select (subquery in from clause)

Table: query table, result set

Type: the full name is "join type", which means connection type. In popular terms, it is the way in which the mysql search engine finds data that meets the SQL conditions. Its values are:

System: system table, there is only one row of data const: read constant, at most one record will match, because it is constant, you only need to read it once. Eq_ref: there will be at most one match, usually accessed through a primary key or a unique key index. Ref: for each row combination from the previous table, all rows with matching index values will read fulltext: from this table for full-text index retrieval. Ref_or_null: the only difference from ref is a query with a null value in addition to a query referenced by an index. Index_merge: use two (or more) indexes at the same time in a query, then merge the index results, and then read table data. Unique_subquery: the combination of the returned result fields in the subquery is the primary key or the only constraint. Index_subquery: the returned result field combination in a subquery is an index (or index combination), but not a primary key or unique index. Rang: index range scan. Index: full index scan. All: full table scan.

Performance degrades from top to bottom.

Possible_keys: indexes that may be used

Key: index used

The ref:ref column shows which column or constant is used to select rows from the table with key.

Rows: displays the number of rows that MySQL thinks it must check when executing the query. Multiplying data between multiple rows can estimate the number of rows to be processed.

Extra: additional information

When Distinct:MySQL finds the first matching row, it stops searching for more rows for the current row combination. Not exists:MySQL can LEFT JOIN optimize the query, and after finding a row that matches the LEFT JOIN standard, no more rows are checked in the table for the previous combination of rows. Range checked for each record (index map: #): MySQL did not find a good index to use, but found that some indexes might be available if the column values from the previous table were known. Using filesort:MySQL needs an extra pass to figure out how to retrieve rows in sort order. Using index: retrieve the column information in the table by reading the actual rows from using only the information in the index tree without further search. Using temporary: to solve the query, MySQL needs to create a temporary table to hold the results. The Using where:WHERE clause is used to restrict which row matches the next table or is sent to the customer. Using sort_union (...), Using union (...), Using intersect (...): these functions show how to merge index scans for index_merge join types. Using index for group-by: similar to the Using index way of accessing tables, Using index for group-by indicates that MySQL has found an index that can be used to query all columns of a GROUP BY or DISTINCT query without additional searching the hard disk to access the actual table.

Second, the database does not use the index

In the example below, the GudiNo and StoreId columns all have separate indexes.

2.1.The like query has been'%.' The index continues to be used at the beginning, ending with 'xxx%'.

The% used in the first sentence in the following figure does not use an index, from rows to 224147 and using index rows to 1.

2.2 and! = are used in where statements

2.3 or is used in the where statement, but all fields in the or are not indexed.

In this case, if you need to use an index, you need to index all the fields in the or.

2.4 manipulation of field expressions in where statements

Use Not In in 2.5 where statements

After reading the articles written by others, it is said that "we should try our best to avoid judging the null value of the field in the where clause, otherwise it will cause the engine to give up using the index and carry out a full table scan."

"for a multi-column index, if it is not the first part of the use, the index will not be used." it is measured that even if the multi-index does not use the first part, the index will be hit and there is no full table scan.

Summary

The above is the whole content of this article, I hope that the content of this article has a certain reference and learning value for your study or work, if you have any questions, you can leave a message and exchange, thank you for your support.

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.