MySQL indexing and explain and common optimizations

principles of MySQL index design 

1. The index column of the search is not necessarily the column you want to select. In other words, the column that best fits the index is the column that appears in the WHERE clause, or the column specified in the JOIN clause, rather than the column that appears in the select list after the SELECT keyword.

2. Use a unique index. Consider the distribution of values in a column. For columns with unique values, the index works best, and columns with multiple duplicate values have the worst indexes. For example, a column that holds age has different values, and it's easy to differentiate between rows. The columns used to record the gender, which contain only "M" and "F", are not much useful for indexing this column (whichever value you search, you will get about half of the rows)

3. Use a short index. If you index a string, you should specify a prefix length, which you should do whenever possible. For example, if you have a char (200) column, and if the majority value is unique within the first 10 or 20 characters, do not index the entire column. Indexing the first 10 or 20 characters can save a lot of index space

may also make the query faster. Smaller indexes involve fewer disk I/O, and shorter values compare faster. More importantly, for shorter key values, the blocks in the index cache can hold more key values, so MySQL can also accommodate more values in memory. This increases the likelihood of finding rows without having to read more chunks in the index.

(Of course, some common sense should be used.) It is not possible to have the advantage of indexing only the first character of a column value, because there are not many different values in the index. ）

4. Use the leftmost prefix. When you create an index of n columns, you are actually creating n indexes that MySQL can use. Multiple-column indexes can serve several indexes because the leftmost Lie in the index can be used to match rows. Such a column set is called the leftmost prefix. (This is different from indexing a column, and the prefix for a column is to use the first n words

As the index value. ) 5. Do not over-index. Do not assume that the index "the more the better", everything is indexed is wrong. Each additional index consumes additional disk space and reduces the performance of write operations, as we have described earlier. When you modify the contents of a table, the index must be updated, and sometimes refactoring may be required, so the more indexes you have, the longer it takes. If an index is seldom exploited or never used, it will unnecessarily slow down the table's modification speed. In addition, MySQL takes time to consider each index when generating an execution plan. Creating redundant indexes has brought more work to query optimization. Too many indexes may also make MySQL choose the best index to use. Maintaining only the desired index is advantageous for query optimization. If you want to add an index to an indexed table, you should consider whether the index you want to increase is the leftmost index of an existing multi-column index. If so, don't bother to increase the index because it's already there.

6. Consider the type of comparison that is performed on the column. Indexes are available for <, < =, =, > =, >, and between operations. When a pattern has a direct volume prefix, the index is also used for the like operation. If you use only one column for other types of operations, such as strcmp (), it is not worthwhile to index it.

Optimize SELECT

SELECT * from PC_SPU where cate_id=2;

SELECT * from PC_SPU where cate_id=2 and brand_id=2;

Optimize ORDER BY

SELECT * from PC_SPU WHERE cate_id=2 ORDER by brand_id asc,cate_id DESC;

EXPLAIN SELECT * from PC_SPU Force INDEX (idx_example) WHERE cate_id=2 ORDER by brand_id asc,cate_id DESC;

This query must be used for this index

CREATE INDEX IDX_EXAMPLE  
on table1  (Col1 ASC,  COL2 DESC, COL3 ASC)    In this case, the following query can be optimized:    
Select col1,  col2, col3 from table1  order by col1 asc, col2 desc,  col3 ASC   and    
select col1, col2, col3 from  example  
order by col1 desc, col2 asc, col3 desc   The
index is not used to optimize queries in any other pattern that has  ASC  and  DESC  in the  ORDER BY  clause. For example:    
Select col1, col2, col3 from table1  order by  col1 ASC, col2 ASC, col3 ASC   will not get optimized

Optimize paged Queries

SELECT * from Bbs_title ORDER by gmt_modified LIMIT 1000, 10;

Select A.* from Bbs_title a INNER JOIN (select ID from Bbs_title b ORDER by b.gmt_modified LIMIT 1000,10) b on a.id=b.id;

And then add an index to the gmt_modified.

Count Query

SELECT COUNT (*) from Bbs_title;

SELECT COUNT (ID) from Bbs_title;

SELECT COUNT (1) from Bbs_title;

Similar in performance

Do not use COUNT (*), the number of locked columns when querying more

Explanation of the Explain column:

Table: Shows which table the data for this row is about

Type: This is an important column that shows what type of connection is used. The best to worst connection types are const, EQ_REG, ref, range, Indexhe, and all

Possible_keys: Displays the indexes that may be applied to this table. If it is empty, there is no possible index. You can select an appropriate statement from the where statement for the related domain

Key: The actual index used. If NULL, the index is not used. In rare cases, MySQL chooses an index that is poorly optimized. In this case, use Index (indexname) can be used in the SELECT statement to force an index or use ignore index (indexname) to force MySQL to ignore the index

Key_len: The length of the index used. The shorter the length the better, without loss of accuracy

Ref: Shows which column of the index is being used and, if possible, a constant

Rows:mysql the number of rows that must be checked to return the requested data

Extra: Additional information on how MySQL resolves queries. The bad example is the using temporary and using filesort, meaning that MySQL simply cannot use the index, and the result is that the retrieval will be slow

The meaning of the description returned by the extra column

Distinct: Once MySQL finds a row that matches a row, it no longer searches for

Not Exists:mysql optimizes the left join, and once it finds a row that matches the left join standard, it no longer searches for

Range checked for each Record (index map:#): No ideal index was found, so for every combination of rows from the preceding table, MySQL examines which index to use and uses it to return rows from the table. This is one of the slowest connections to use the index

Using Filesort: When you see this, the query needs to be optimized. MySQL requires additional steps to find out how to sort the rows that are returned. It sorts all rows based on the connection type and the row pointers for all rows that store the sort key values and matching criteria.

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

Using temporary When you see this, the query needs to be optimized. Here, MySQL needs to create a temporary table to store the results, which usually occurs on an order by on a different set of columns, rather than on the group by

Where used uses a WHERE clause to restrict which rows will match the next table or return to the user. If you do not want to return all rows in the table, and the connection type all or index, this occurs, or the query has a problem different connection types of interpretation (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 maximum value of a record in a table can match this query (the index can be a primary key or a unique index). Because there is only one row, this value is actually a constant, because MySQL reads the value first and treats it as a constant.

Eq_ref: In a connection, when MySQL queries, from the previous table, the union of each record reads a record from the table, which is used when the query uses the index as the primary key or the unique key.

Ref: This connection type occurs only if the query uses a key that is not a unique or primary key or is part of these types (for example, using the leftmost prefix). For each row union of the previous table, all records are read from the table. This type is heavily dependent on how many records are matched against the index-the less the better

Range: This connection type uses an index to return rows in a range, such as what happens when you use > or < to find something

Index: This connection type is fully scanned for each record in the previous table (better than all because the index is generally less than the table data)

All: This connection type is fully scanned for each previous record, which is generally bad and should be avoided as much as possible.

MySQL indexing and explain and common optimizations