MySQL optimization measures from SQL optimization

http://geeksblog.cc/2016/06/11/mysql-optimize/

General steps for Optimizing SQL

1. Learn the frequency of various SQL executions with show status
2. Locating SQL statements with low execution efficiency
3. Analyze inefficient SQL with explain
4. Analyze SQL with show profile?
5. How the optimizer chooses an execution plan through the trace analysis
6. Identify problems and take measures to optimize

Index optimization measures

1. Typical scenarios for using indexes in MySQL

   1. Match all values, condition all columns are in the index and are equivalent
   2. Match the range lookup for the value, the field must be in the index
   3. Match the leftmost prefix, and the composite index will only be found based on the leftmost column
   4. Query only the index, that is, all the fields of the query are on the index
   5. Match the column prefix, such as like ' abc% ', if it's like '%aaa ' you can't
   6. If the column name is an index, using column is null uses the index

2. Typical scenario where an index exists but does not use an index

   1. A like query that starts with% cannot use the B-Tree index
   2. An implicit conversion of a data type is not possible with an index
   3. Composite index, query conditions do not conform to the leftmost rule
   4. Conditions that are split with or, if the previous condition has an index, and the subsequent condition is not indexed

3. To view the usage of the index

   1	' handler_read% ';

If the value of Handler_read_rnd_next is higher, the index is incorrect or the query is not used to index

There are indexes:


Mysql> Select *  fromDD;+----+|A|+----+|  1 ||  2 ||  3 ||  4 ||  5 ||  6 ||  7 ||  8 ||  9 || Ten ||  One ||  A ||  - |+----+ -Rowsinch Set(0.00sec) MySQL>ShowCreate TableDD;+-------+----------------------------------------| Table | Create Table+-------+----------------------------------------|Dd| CREATE TABLE' DD ' (' a 'int( One) not NULL,  PRIMARY KEY (' a ')) ENGINE=InnoDBDEFAULTCHARSET=Utf8|+-------+----------------------------------------1Rowinch Set(0.02Sec

Mysql> Select *  fromDdwhereA=Ten;+----+|A|+----+| Ten |+----+1Rowinch Set(0.00sec) MySQL>Show status like 'handler_read%';+-----------------------+-------+|Variable_name|Value|+-----------------------+-------+|Handler_read_first| 0     || Handler_read_key |    1 | This value is added.|Handler_read_last| 0     ||Handler_read_next| 0     ||Handler_read_prev| 0     ||Handler_read_rnd| 0     ||Handler_read_rnd_next| 2     |+-----------------------+-------+7Rowsinch Set(0.00Sec

No index:




Mysql>ShowCreate TableQ;+-------+------------------------------------------------------------------------------------+| Table | Create Table                                                                       |+-------+------------------------------------------------------------------------------------+|Q| CREATE TABLE' Q ' (' A 'int( One)DEFAULT NULL) ENGINE=InnoDBDEFAULTCHARSET=Utf8|+-------+------------------------------------------------------------------------------------+1Rowinch Set(0.00sec) MySQL> Select *  fromQwhereA=Ten;+------+|A|+------+|   Ten |+------+1Rowinch Set(0.00sec) MySQL>Show status like 'handler_read%';+-----------------------+-------+|Variable_name|Value|+-----------------------+-------+|Handler_read_first| 1     ||Handler_read_key| 1     ||Handler_read_last| 0     ||Handler_read_next| 0     ||Handler_read_prev| 0     ||Handler_read_rnd| 0     ||Handler_read_rnd_next|  About    |+-----------------------+-------+7Rowsinch Set(0.00sec) MySQL> Select *  fromQwhereA= One;+------+|A|+------+|    One |+------+1Rowinch Set(0.00sec) MySQL>Show status like 'handler_read%';+-----------------------+-------+|Variable_name|Value|+-----------------------+-------+|Handler_read_first| 2     ||Handler_read_key| 2     ||Handler_read_last| 0     ||Handler_read_next| 0     ||Handler_read_prev| 0     ||Handler_read_rnd| 0     || Handler_read_rnd_next |      - |+-----------------------+-------+7Rowsinch Set(0.01Sec

Simple and practical optimization method

1. Periodic checklist and Analysis tables
   Parsing table Syntax:

   1	Table name;

Check table syntax:

1	Table name;

1. Regularly optimize tables
   • For fields with an irregular byte size, data updates and deletions can cause disk space not to be freed, and the row optimizes the table, which can defragment the disk and improve performance
     The syntax is as follows:

     1	User(table name);

MySQL optimization measures from SQL optimization