MySQL Query optimization summary

Transfer from http://www.cnblogs.com/112ba/p/6220650.html

Data type

Simple principle: Smaller, better, simpler, avoid null
1) integer type such as int (10) The value in parentheses is independent of the storage size
2) Real numbers take up more storage space than float and double and the CPU cannot directly calculate decimal
3) String column maximum length <=255varchar use one byte to save the length, otherwise 2 bytes are used. char (10) is the number of characters in parentheses, not the number of bytes (the number of bytes is related to the column character set).
4) Use MySQL's built-in type to save date and time, such as datetime, timestamp

Index type

In addition to data, the database system maintains a data structure that satisfies a particular lookup algorithm that references (points to) data in some way, so that an advanced find algorithm can be implemented on those data structures. This data structure is the index.

1) B-tree Index
MyISAM, InnoDB use B+tree as the index structure
MyISAM and InnoDB differ in implementation: The MyISAM primary key is consistent with the structure used by the normal index, and the leaf node holds the address that points to the data record.

How the InnoDB primary key takes a clustered index (leaf node
The full row data is saved, and the normal index is associated with the MyISAM
Similar, but the leaf node holds the value of the primary key.

NDB Cluster internal actual use T-tree Structure implementation index

Query types that can use the B-tree index in MySQL:
CREATE TABLE people (
last_name varchar () NOT NULL,
first_name varchar () NOT NULL,
DOB date NOT NULL,
Gender enum (' m ', ' F ') is not NULL,
Key (Last_name,first_name,don)
) Engine=myisam;
1. Match Full Name
Where last_nam= ' a ' and first_name= ' B ' and dob= ' 1990-01-01′
2. Match the leftmost prefix
Where Last_name= ' a ' or where last_name= ' a ' and first_name= ' B '
3. Matching column prefixes
where last_name like ' a% '
4. Match range values
where Last_Name >= ' a ' and last_name<= ' d '
5. Exact match part and match another part of a range
Where Last_name= ' a ' and first_name like ' k% '
6. Queries that only access the index
B-tree supports access to indexed queries only, and does not access data rows (overwriting
Index
Select first_name from people where last_name= ' a '

Some limitations of the B-tree index in MySQL:

1. The query is not the leftmost prefix start cannot use the index
Where First_name= ' a ' or where last_name like '%a '
2. You cannot skip columns in an index
Where Last_name= ' a ' and dob= ' 1990-01-01′ will only use the first column of the index
3. The column to the right of the first range condition cannot use the index
Where Last_name= ' a ' and first_name like ' a% ' and dob= ' 1990-01-01′ because like is a range query, so only the first two columns of the index are used

2) Hash Index

Hash index is based on the hash table, only the use of the cable
The exact query for each column in the citation is useful (memory and NDB support,
INNOBD Support Adaptive Hash Index)

Hash queries are quick but some limitations:
1. Only hash code and line pointers are saved in the index, cannot overwrite index
2. Cannot use hash index to sort
3. Partial index column matching is not supported
4. Only support ' = ', ' in ', ' <=> ' equality comparison query, range query cannot use hash index
5. If the collision rate of the hash algorithm is high, it will affect the index performance
6. High collision Rate Hash Index Delete Row is expensive

Query optimization

1. Quarantine columns
Isolating a column means that the column in the condition is not an expression or is not in a function
2. Prefix index and index selectivity
Index selectivity = Do not repeat index values/all rows in the table, the larger the better
Prefix index: For char, varchar, blob, text type can be
The index starts with several characters, and the index selectively determines several
Character
KEY ' BC ' (' B ', ' C ' (5))
KEY ' CD ' (' C ' (5), ' d ' (5))

3. Using indexes for sorting

The ORDER BY clause is exactly the same as the column order in the index, and all column sort directions are used to sort the index. If you join more than one table, only the first table is referenced by all columns in the ORDER BY clause (after the query optimizer rewrites the query).

The ORDER BY clause also follows the index leftmost prefix principle (except for the exact match of the leading column in where).

Cases:

Key T1 (day,in_id,con_id) The following query can use the index (explain extra does not see the using Filesort)

4. Group BY use Index

Group by uses an index rule that is consistent with order by.

Group by defaults to sort operations, and if you do not care about the return order, you can skip sorting by adding order by NULL.

Distinct FD If the FD column has an index and the index is used in the WHERE clause, you can also use the index optimization distinct

5. Optimize min, max

Select min (fd2) from tt1 where m= ' 2012-03-29′;

Select FD2 from TT1 Force index (FD2) where

M= ' 2012-03-29′order by fd2 limit 1;

Select Max (FD2) from tt1 where m= ' 2012-03-29′;

Select FD2 from TT1 Force index (FD2) where

M= ' 2012-03-29′order by fd2 desc LIMIT 1;

6. Using explain to analyze queries

Explain analysis can only be performed on a select query. can add

extened keyword, then use show warnings to check

After the optimizer overrides the SQL, if the partition table can be added

partitions keyword. Such as:

EXPLAIN extened SELECT ...

MySQL Query optimization summary