Mysql index optimization method index

Create an index
Indexes are especially important for applications where queries account for the majority. In many cases, the performance problem is very simple because we forget to add an index, or we have not added a more effective index. If you do not add
If the index is used, a full table scan will be performed to search for any specific data. If a table has a large amount of data and few results meet the conditions, if no index is added, the performance will be fatal.
Drop. However, it is not always necessary to create an index. For example, there may be only two values for gender. Creating an index not only has no advantages, but also affects the update speed. This is called excessive index.
2. Composite Index
For example, there is a statement like this: select * from users where area = 'beijing' and age = 22;
If we create a single index on the area and age respectively, because mysql queries can only use one index at a time, the full table scan improves the efficiency even if the index is not used.
Rate, but creating a composite index on the "area" and "age" columns will lead to higher efficiency. If we create an (area, age,
Salary) composite index, which is equivalent to creating three indexes (area, age, salary), (area, age), and (area). This is called the best left prefix.
Features. Therefore, when creating a composite index, we should place the columns that are most commonly used as restrictions on the leftmost and decrease in turn.
3. The index does not contain columns with NULL values.
As long as a column contains a NULL value, it will not be included in the index. If a column in the composite index contains a NULL value, this column is invalid for this composite index. Therefore, do not set the default value of a field to NULL during database design.
4. Use short Indexes
Index a string or column. If possible, specify a prefix length. For example, if a CHAR (255) Column exists and multiple values are unique within the first 10 or 20 characters, do not index the entire column. Short indexes not only increase query speed, but also save disk space and I/O operations.
5. Sorting index problems
Mysql queries only use one index. Therefore, if an index is already used in the where clause, columns in order by will not use the index. Therefore, do not use the sorting operation when the database's default sorting can meet the requirements. Try not to include the sorting of multiple columns. It is best to create a composite index for these columns if necessary.
6. like statement operation
Generally, like operations are not encouraged. If they are not usable, how to use them is also a problem. Like "% aaa %" does not use indexes, but like "aaa %" can use indexes.
7. Do not perform operations on columns
Select * from users where
YEAR (adddate)
8. Do NOT use not in and operations
Neither the not in nor the operation will use the index to scan the entire table. Not in can be replaced by not exists, while id3 can use id> 3 or id

Ref_or_null

The join type is like ref, but MySQL can search for rows containing NULL values. The optimization of this join type is often used in solving subqueries. In the following example, MySQL can use the ref_or_null join for processing. Ref_tables:

SELECT * FROM ref_tableWHERE key_column=expr OR key_column IS NULL;

O index_merge this join type indicates that the index merge optimization method is used. In this case, the key column contains the list of indexes used, and key_len contains the longest key element of the index used. O unique_subquery this type replaces the ref of 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 replace subqueries completely, improving efficiency. O index_subquery the join type is similar to unique_subquery. An IN subquery can be replaced by an IN subquery, but it is only applicable to non-unique indexes IN the following forms:

value IN (SELECT key_column FROM single_table WHERE some_expr)

O range only retrieves rows in a given range and uses an index to select rows. The key column shows the index used. Key_len contains the longest key element of the index used. In this type, the ref column is NULL. When using the =, <>,>,> =, <, <=, is null, <=>, BETWEEN, or IN operator, you can use the range:

SELECT * FROM tbl_nameWHERE key_column = 10; SELECT * FROM tbl_nameWHERE key_column BETWEEN 10 and 20; SELECT * FROM tbl_nameWHERE key_column IN (10,20,30); SELECT * FROM tbl_nameWHERE key_part1= 10 AND key_part2 IN (10,20,30);

O index: the join type is the same as that of 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 querying only columns that are part of a single index. O ALL performs a full table scan for each row combination from the previous table. If the table is the first table without the const Mark, this is usually not good, and usually in this case VeryPoor. Generally, you can add more indexes instead of ALL, so that the rows can be retrieved Based on the constant values or column values in the preceding table .? The possible_keyspossible_keys column indicates MySQLWhich index can be used to find rows in the table. Note that this column is completely independent of the Order of the tables shown in the EXPLAIN output. This means that some keys in possible_keys cannot be used in the order of the generated table. If this column is NULL, there is no relevant index. In this case, you can check the WHERE clause to see if it references certain columns or columns suitable for indexing to improve your query performance. If so, create an appropriate index and use EXPLAIN again to check the query. To see what INDEX a table has, use SHOW INDEX FROM Tbl_name.? Keykey Column Display MySQLThe key (INDEX) actually used ). If no index is selected, the key is NULL. To FORCE MySQL to USE or IGNORE the indexes in the possible_keys column, use force index, use index, or ignore index in the query. For MyISAM and BDB tables, running analyze table helps the optimizer to select better indexes. For MyISAM tables, you can use Myisamchk -- analyze.? Key_lenkey_len Column Display MySQLDetermines the length of the key used. If the key is NULL, the length is NULL. Note that using the key_len value, we can determine the parts of MySQL that will actually use multiple keywords .? The refref column shows which column or constant is used together with the key to select rows from the table .? Rowsrows Column Display MySQLThe number of rows that must be checked during query execution .? Extra This column contains detailed information about MySQL queries. The following explains the different text strings that can be displayed in this column: o DistinctMySQL finds 1st matching rows and stops searching for more rows for the current row combination. O Not existsMySQL can perform left join Optimization on the query. After finding a row that matches the left join standard, it no longer checks more rows for the preceding row combination in the table. The following IS an example of a query type that can be optimized as follows: SELECT * from t1 left join t2 ON t1.id = t2.id WHERE t2.id is null; assume that t2.id IS defined as not null. In this case, MySQL uses the value of t1.id to scan t1 and find the rows in T2. If MySQL finds a matched row in t2, it knows that t2.id will never be NULL and does not scan rows with the same id value in t2. In other words, for each line of t1, MySQL only needs to find it once in t2, regardless of the actual number of matched rows in t2. O range checked for each record (index map: #) MySQL does not find any indexes that can be used. However, if the column values from the preceding table are known, some indexes may be used. For each row combination in the preceding table, MySQL checks whether the range or index_merge access method can be used to obtain rows. This is not very fast, but it is much faster than executing a join without an index. O Using filesortMySQL requires an additional pass to find out how to retrieve rows in order. You can browse all rows based on the join type and save the sorting keywords and row pointers for all rows matching the WHERE clause. Then the keywords are sorted and the rows are retrieved in the order of sorting. O Using index uses only the information in the index tree instead of further searching and reading the actual row to retrieve the column information in the table. This policy can be used when queries only use columns that are part of a single index. O Using temporary in order to solve the query, MySQL needs to create a temporary table to accommodate the results. In typical cases, if a query contains the group by and order by clauses that can list columns according to different situations. The o Using whereWHERE clause is used to limit which row matches the next table or sends it to the customer. Unless you request or check ALL rows from a table, if the Extra value is not Using where and the table join type is ALL or index, the query may have some errors. If you want to make the query as fast as possible, you should find the Extra values of Using filesort and Using temporary. O Using sort_union (...), Using union (...), Using intersect (...) These functions describe how to merge index scanning for the index_merge join type. O Using index for group-by is similar to the Using index method used to access the table. Using index for group-by indicates that MySQL has found an index and can be used to query all columns queried by group by or DISTINCT, instead of searching for the actual table by hard disk. In addition, indexes are used in the most effective way to read only a small number of index entries for each group. By multiplying all the values in the rows column output by EXPLAIN, you can get a prompt about how a join works. This should roughly tell you MySQLYou must check the number of rows for query. When you use the max_join_size variable to limit queries, you can also use this product to determine which multi-Table SELECT statement to execute.