General steps for optimizing sql statements:

View the execution of various sql statements through the show [sesson|global] status command.

When the mysql client connection is successful, you can use the command show [session|global] status to view the status information of the server (session represents the statistical results of the current connection, global represents the statistical results of the last database launch, if you do not write the system default is sesson). The following command shows the values of all the statistical parameters in the current session:

mysql> show status like 'Com%';
+-----------------------------+-------+
| Variable_name               | Value |
+-----------------------------+-------+
| Com_admin_commands          | 0     |
| Com_assign_to_keycache      | 0     |
| Com_alter_db                | 0     |
| Com_alter_db_upgrade        | 0     |
| Com_alter_event             | 0     |
| Com_alter_function          | 0     |
| Com_alter_instance          | 0     |
| Com_alter_procedure         | 0     |
| Com_alter_server            | 0     |
| Com_alter_table             | 0     |
| Com_alter_tablespace        | 0     |
| Com_alter_user              | 0     |
| Com_analyze                 | 0     |
| Com_begin                   | 0     |
| Com_binlog                  | 0     |
| Com_call_procedure          | 0     |
| Com_change_db               | 1     |
...

Com_xxx represents the number of times each XXXX statement is executed, and we are usually concerned with the following parameters: > Com_select: The number of times a select is executed, increasing once per execution. > Com_insert: The index of insert execution, which is increased once per execution and once in batch execution. > Com_update: The number of times an update is executed, increasing once per execution. > Com_delete: The number of times deletes are executed, and the number of times a sister paper executes is increased.

ps: The above records are recorded for all storage engines, and the following parameters are for innodb engines.

> innodb_rows_read: The number of rows returned by the select query. > innodb_rows_insert: Number of rows that perform insert insert operations. > innodb_rows_update: Number of rows that perform update operations. > innodb_rows_delete: Number of rows that perform delete delete operations.

Through the above parameters, it is easy to know whether the current database is mainly based on insertion and updating or query operation, and the comparison of various types of sql roughly implemented.

For transactional applications, things can be submitted and rolled back through Com_commit and Com_rollback. For databases with very frequent rollback operations, there may be problems in application writing.

In addition, the following parameters facilitate users to understand the basic situation of the database. > Connections: Number of attempts to connect to the server. > Uptime: The time the server works. > Slow_queries: Number of slow queries.

Location execution efficiency of sql:

There are two ways to locate sql that is less efficient: the keyword show process list > Locate sql with low execution efficiency through slow query logs. > Slow query logs are recorded after the query is completed, so the problem can not be obtained in real time. You can use the show process list command to view the current execution of sql threads, including the status of threads, whether the table is locked or not. You can view the execution status of sql in real time, and optimize some lock operations.

Analyse the lower sql execution plan by explain ing:

Through the above steps, sql statements with low execution efficiency can be obtained, and information about how mysql executes select statements can be obtained by explain ing or desc commands, including how to connect and the sequence of connections during the execution of select statements.

mysql> explain select * from vc \G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: vc
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 2
     filtered: 100.00
        Extra: NULL
1 row in set, 1 warning (0.00 sec)

ERROR:
No query specified

Field description:

select_type: Represents the type of select.
Table: The table that outputs the result set
 Type: Represents how mysql finds the required rows in the table (access type)

The type type type is shown in the figure above, and the higher the level, the better the performance.

sql analysis by show profile

See if profile is supported by the command have_profile parameter.

mysql> select @@have_profiling;
+------------------+
| @@have_profiling |
+------------------+
| YES              |
+------------------+
1 row in set, 1 warning (0.00 sec)

profiling is closed by default and can be opened at session level using set statements.

mysql> select @@profiling;
+-------------+
| @@profiling |
+-------------+
|           0 |
+-------------+
1 row in set, 1 warning (0.00 sec)

mysql> set profiling = 1;
Query OK, 0 rows affected, 1 warning (0.00 sec)

Through profiling, we can get a clearer understanding of the execution process of sql statements. First, we perform query operations on an innodb table: select count(*) from ai;

mysql> select count(*) from ai;
+----------+
| count(*) |
+----------+
|        3 |
+----------+
1 row in set (0.00 sec)

Use show profiles after execution; the command to view the query_id of the current sql statement is 13:

mysql> show profiles;
+----------+------------+-----------------------------+
| Query_ID | Duration   | Query                       |
+----------+------------+-----------------------------+
|        1 | 0.00163600 | show tables                 |
|        2 | 0.00005400 | show table status 'ai'      |
|        3 | 0.00032400 | show table status like 'ai' |
|        4 | 0.00027200 | show table status like 'ai' |
|        5 | 0.00019300 | select * from ai            |
|        6 | 0.00011700 | show create table ai        |
|        7 | 0.00009600 | show create table ai        |
|        8 | 0.00004500 | insert into ai              |
|        9 | 0.00144900 | insert into ai values (1)   |
|       10 | 0.00113800 | insert into ai values (2)   |
|       11 | 0.00120700 | insert into ai values (3)   |
|       12 | 0.00016300 | select * from ai            |
|       13 | 0.00017200 | select count(*) from ai     |
|       14 | 0.00004700 | show profiling              |
+----------+------------+-----------------------------+
14 rows in set, 1 warning (0.00 sec)

Use show profile for query query_id; query:

mysql> show profile for query 13;
+----------------------+----------+
| Status               | Duration |
+----------------------+----------+
| starting             | 0.000057 |
| checking permissions | 0.000005 |
| Opening tables       | 0.000012 |
| init                 | 0.000011 |
| System lock          | 0.000005 |
| optimizing           | 0.000023 |
| executing            | 0.000012 |
| end                  | 0.000002 |
| query end            | 0.000005 |
| closing tables       | 0.000006 |
| freeing items        | 0.000011 |
| cleaning up          | 0.000023 |
+----------------------+----------+
12 rows in set, 1 warning (0.00 sec)

Generally speaking, the execution is slow as long as it concentrates on the statement of send_data. send_data is a process in which the thread begins to process the data line and returns the execution result to the client. In the process of send_data, a large number of disk reads and writes are needed, so it is often the most time-consuming state.

How to select execution plan through trace analysis optimizer

...

Indexes

Classification of indexes:

 B-Tree: The most common index, supported by most engines
 HASH: Only memory engine is used. The use scenario is simple.
 R_Tree: Spatial Index
 Full-text: Full-text index, Innodb supports Full-text index from 5.6