Create an index
Indexing is especially important for queries as a major application. Most of the time the performance problem is simply because we forgot to add the index, or we didn't add a more efficient index. If you do not add
Index, then look for any even just a specific data will be a full table scan, if a table of large amounts of data and meet the conditions of the result is very small, then no index will cause fatal performance
Drop. But it is not always necessary to build an index, such as the gender may only have two values, the index not only has no advantage, but also affect the update speed, which is called an excessive index.
2, composite Index
For example, there is a statement like this: SELECT * from users where area= ' Beijing ' and age=22;
If we were to create a single index on area and age, because the MySQL query could only use one index at a time, the full table scan was a lot more effective when it was relatively non indexed
Rate, but creating a composite index on an area, an age two column will result in greater efficiency. If we create an area, an age,
Salary), it is actually equivalent to creating (Area,age,salary), (Area,age), (area) Three indexes, which is called the best left prefix
Characteristics. So when we create a composite index, we should place the columns that are most commonly used as constraints to the left, decreasing in descending order.
3, the index does not contain columns with null values
This column is not valid for this composite index as long as the column contains null values that will not be included in the index, as long as one column in the composite index contains null values. So we don't want the default value of the field to be null when designing the database.
4, using short index
Index A string column, if possible, to specify a prefix length. For example, if you have a column with char (255), if most values are unique within the first 10 or 20 characters, do not index the entire column. Short indexing can not only improve query speed but also save disk space and I/O operations.
5, sort of indexing problem
The MySQL query uses only one index, so the columns in the order by are not indexed if the index is already used in the WHERE clause. Therefore, do not use sort operations when the database default sort meets the requirements, and try not to include sorting of multiple columns, preferably if you need to create a composite index for these columns.
6,like Statement Actions
It is generally discouraged to use like operations, and how to use them is also a problem if not used. Like "%aaa%" does not use indexes and like "aaa%" can use indexes.
7, do not perform operations on the column
SELECT * from Users where
Year (adddate)
8, no in and operation not used
None in and operations do not use the index to perform a full table scan. Not in can be substituted by not exists, ID3 can use id>3 or ID
Ref_or_null
The join type is like ref, but it is added that MySQL can specifically search for rows that contain null values. Optimization of this join type is often used in resolving subqueries.
In the following example, MySQL can use the Ref_or_null join to handle the
ref_tables:
SELECT * from Ref_tablewhere key_column=expr OR Key_column is NULL;
The join type represents the use of an index consolidation optimization method. In this case, the key column contains a list of the indexes used, Key_len contains the longest key element of the index used.
This type replaces ref for the in subquery in the following form:
Value in (SELECT primary_key from single_table WHERE some_expr)
Unique_subquery is an index lookup function that can completely replace subqueries and is more efficient.
The join type is similar to Unique_subquery. You can replace in subqueries, but only for non unique indexes in subqueries of the following forms:
Value in (SELECT key_column from single_table WHERE some_expr)
Retrieves only the rows of a given range, using an index to select rows. The key column shows which index is used. Key_len contains the longest critical element of the index used. Ref column is NULL in this type.
When using the =, <>, >, >=, <, <=, is NULL, <=>, between, or in operators, you can use range when you use constants to compare key columns:
SELECT * from Tbl_namewhere key_column = ten;
SELECT * from Tbl_namewhere key_column BETWEEN;
SELECT * from Tbl_namewhere key_column in (10,20,30);
SELECT * from Tbl_namewhere key_part1=, Key_part2 in (10,20,30);
The join type is the same as all except that only the index tree is scanned. This is usually faster than all because index files are usually smaller than data files.
MySQL can use this join type when a query uses only columns that are part of a single index.
Complete table scans for each row combination from the previous table. This is usually bad if the table is the first table that does not have a const, and is usually
poor in its case. You can usually add more indexes instead of all so that rows can be retrieved based on constant values or column values in the previous table.
The Possible_keys column indicates which index
MySQL can use to find rows in the table. Note that the column is completely independent of the order of the tables shown in the explain output. This means that some keys in possible_keys cannot actually be used in the generated table order.
If the column is null, there is no index associated with it. In this case, you can improve your query performance by checking the WHERE clause to see if it references certain columns or columns that fit the index. If so, create an appropriate index and check the query again with explain.
To see what index a table has, use the show index from
tbl_name.
The key column shows the keys (indexes) that
MySQL actually decides to use. If no index is selected, the key is null. To force MySQL to use or ignore the index in the Possible_keys column, use force index, using index, or ignore index in the query.
For MyISAM and BDB tables, running analyze table can help the optimizer choose a better index. For MyISAM tables, you can use
myisamchk--analyze.
The Key_len column shows the length of the key that
MySQL decided to use. If the key is null, the length is null. Note that by Key_len value we can determine that MySQL will actually use several parts of a multiple-part keyword.
The ref column shows which column or constant is used with the key to select rows from the table.
The Rows column shows the number of rows that
MySQL must check when it executes the query.
This column contains detailed information about the MySQL resolution query. The following explains the different text strings that the column can display:
After MySQL finds the 1th matching row, stop searching for more rows for the current row combination.
MySQL is able to perform a left join optimization for a query, and after discovering 1 rows that match the left join criteria, no more rows are checked for the preceding row group in the table.
Here is an example of a query type that can be optimized like this:
SELECT * from T1 left JOIN T2 to T1.id=t2.id WHERE t2.id is NULL;
Suppose t2.id is defined as not NULL. In this case, MySQL uses the T1.id value to scan the T1 and find the rows in T2. If MySQL finds a matching row in T2, it knows that t2.id will never be null and no longer scans rows with the same ID values in T2. In other words, for each row of T1, MySQL only needs to find it once in the T2, regardless of how many matching rows are actually in the T2.
o 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. For each row in the previous table, MySQL checks whether a range or Index_merge access method can be used to request a row.
This is not fast, but it is much faster than performing a join without an index.
MySQL requires an extra pass to find out how to retrieve rows in sorted order. Sorting is done by browsing all rows based on the join type and holding pointers to the sort keys and rows for all rows that match the WHERE clause. The keywords are then sorted and the rows are retrieved in the sorted order.
Retrieves the column information in a table from using only the information in the index tree without further searching to read the actual rows. This policy can be used when a query uses only columns that are part of a single index.
To resolve the query, MySQL needs to create a temporary table to accommodate the results. Typically, a query contains the group BY and ORDER BY clauses that can list columns in different situations.
The WHERE clause is used to restrict which row matches the next table or to the customer. Unless you specifically request or check all rows from a table, queries may have errors if the extra value is not a using where and the table join type is all or index.
If you want to make the query as fast as possible, you should find the extra value of the using Filesort and the using temporary.
o using Sort_union (...), using Union (...), using intersect (...)
These functions describe how to merge index scans for Index_merge join types.
o Using Index for group-by
Similar to the using index method for accessing a table, the using index for group-by indicates that MySQL found an index that can be used to query all columns of a group by or distinct query, instead of searching hard disks to access the actual table. Also, use the index in the most efficient way so that only a small number of index entries are read for each group.
by multiplying all the values of the rows column of the explain output, you can get a hint about how a join IS. This should roughly tell you how many rows
MySQL must check to execute the query. When you use the max_join_size variable to limit the query, you also use this product to determine which multiple table SELECT statement to execute.
