SQL is essential in project development, and so is the case. Do you know how many of these SQL operations are hitting the index? Which index is hit? What is an invalid index in the index? Do these invalid indexes affect the performance of the system? Take these questions and we will study together. In MySQL, the execution plan for inefficient SQL is analyzed through the Explain command. The use of commands is simple. Example explain select * from Adminlog execution Result:
| Id |
Select_type |
Table |
Partitjons |
Type |
Possible_keys |
Key |
Key_len |
Ref |
Row |
Filtered |
Extra |
| 1 |
Simple |
Adminlog |
|
All |
|
|
|
|
2 |
100 |
|
Description of each column of the execution result:
1, Select_type: query type, common values [simple: Easy table, do not use table joins or subqueries. PRIMARY: Main query, outer query. UNION the second or subsequent query statement. Subquery: The first select in a subquery]
2. Table : Table of output results
3. Type : Indicates how MySQL finds the desired row in the table, or is called an access type. Common types:
| All |
Index |
Range |
Ref |
Eq_ref |
Const,system |
Null |
From left to right, performance is from the worst to the best.3.1 Type=allFull table Scan,3.2 Type=indexIndex full scan, traverse entire index to query matching rows3.3 Type=rangeIndex range scanning, commonly used in operators such as <,<=,>,>=,between,in. Example explain select * from Adminlog where id>0, explain select * from Adminlog where id>0 and id<=100 ex Plain SELECT * FROM Adminlog where ID in ($)3.4 type=refReturns a row of records that match a single value by using a prefix scan of a non-unique index or a unique index. Ref also often appears in join operations3.5 Type=eq_refSimilar to ref, the difference is that the index used is a unique index, for each index key value, there is a record match in the table; In short, the use of a master or a unique health as an association condition in a multi-table connection3.6 Type=const/systemThere is a maximum of one matching row in a single table. Mainly used to compare primary key [primary key index] or unique[unique] index, because the data are unique, so the performance is optimal. Condition use =.3.7 Type=nullWithout accessing the table or index, you can get the result example explain select 1 from dual, type types have other values such as Ref_or_null: similar to ref, except that the condition contains a null query. Index_merge: Index merge optimization, Unique_subquery:in is followed by a subquery for a primary key field. Index_subquery: Similar to Unique_subquery, except that in is followed by subqueries that query for non-unique indexed fields
4, Possible_keys : The list of indexes that may be used.
5. Key: Implementing an index list with execution
6, Key_len : The length of the index
7. Ref: Shows which column or constant is used together with key to select rows from the table.
8. Row : The number of rows to execute the query, simple and important, the larger the number is not good, the description is not used well index 9, filtered:
10, Extra: This column contains the details of the MySQL resolution query. 10.1 Not exists the 10.2 range checked for each record does not find a suitable index 10.5 using index only uses the information in the index tree without requiring further search to read the actual rows to retrieve the information in the table. It is recommended to take the index column. This allows you to avoid finding data in the actual table through the index. Returns the data for the indexed column directly. One query. Otherwise, the index table is checked once, the actual table is checked once. 10.6 Using temporary in order to resolve the query, MySQL needs to create a temporary table to accommodate the results. A typical case is when a query contains a group by and an ORDER BY clause that can be listed in different cases.
Invalid index: Columns with small data changes. such as the XX type, is valid, the project ID and so on column index is invalid. These invalid indexes also affect the performance of INSERT, Update, Delete statements. Because the execution of these packets is to update the index table. And because the values of these tables vary little, it is difficult for the database to properly allocate indexes for them. Therefore, the performance of the statement is affected.
whether the In,or will walk the index: A SQL will not go index one look at the conditions used by the operator, and the other to see if there is no index. So SQL will not go index and in.or,group by no relationship. What operator does not go index, <>,!=explain select * from cbdfinance.adminlog where id = 1 or id=2;explain SELECT * from Cbdfinance.admin Log where id = 1 or searchtext1= ' updatemodelerrormsg '; explain SELECT * from Cbdfinance.adminlog where ID in (Sat) explain S Elect * from Cbdfinance.adminlog GROUP by SearchText1
MySQL optimizer-is your SQL hit index up?
