MySQL SQL statement optimization

General steps for an SQL statement optimization:

1 Understanding the execution frequency of various SQL statements through the show status command

Mysql> Show status; #show Status: Display Server status information

+-----------------------------------------------+-------------+

| variable_name | Value |

+-----------------------------------------------+-------------+

| aborted_clients | 0 |

| aborted_connects | 0 |

| Binlog_cache_disk_use | 0 |

| Binlog_cache_use | 8 |

| Binlog_stmt_cache_disk_use | 0 |

| Binlog_stmt_cache_use | 25 |

| bytes_received | 2919 |

| bytes_sent | 51750 |

......

Mysql> Show status like "com%"; #显示当前session中, the value of the statistic parameter

+---------------------------+-------+

| 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_procedure | 0 |

| Com_alter_server | 0 |

| com_alter_table | 2 |

| Com_alter_tablespace | 0 |

| Com_alter_user | 0 |

| Com_analyze | 0 |

| Com_begin | 0 |

......

Com_xxx: Represents the number of times each XXX statement executes, and the following statistical parameters are important:

• Com_select: Number of times the select is executed, one query cumulative plus 1

• Com_insert: Number of insert operations performed, BULK INSERT only accumulated 1

• Com_delete: The number of times the delete operation was performed,

• Com_update: The number of times the update operation was performed,

The above parameters are for all table operations of the storage engine.

The following parameters are for the InnoDB storage engine, and the algorithm is slightly different:

Innodb_rows_read:select the number of rows returned by the query

Innodb_rows_inserted: The number of rows inserted to perform an insert operation

innodb_rows_updated: Number of rows updated to perform update operation

Innodb_rows_deleted: The number of rows deleted by performing a delete operation

Through the above parameters, you can determine whether the current database is to insert the main or query operations, as well as the approximate number of different types of SQL execution scale.

In addition, the following parameters can help you understand the basic situation of the database:

Uptime: Working time of the database server

Connections: Number of attempts to connect to the server

Slow_queries: Number of slow queries

2 Locating SQL statements with low execution efficiency

Method 1: Location by slow query log

Method 2: View the currently in progress thread

Mysql> show Processlist;

+----+-------------+-----------+------+---------+-------+------------------------------------------------------ -----------------------+------------------+

| Id | User | Host | db | Command | Time | State | Info |

+----+-------------+-----------+------+---------+-------+------------------------------------------------------ -----------------------+------------------+

| 1 |           System user | | NULL | Connect | 34400 | Waiting for Master to send event | NULL |

| 2 |           System user | | NULL |  Connect | 7738 | Slave have read all relay log; Waiting for the slave I/O thread to update it | NULL |

| 4 | Root | localhost | NULL |     Query | 0 | init | Show Processlist |

3 analyzing the execution of inefficient SQL statements by explain

After querying to inefficient SQL statements through the previous steps, you can get information about how MySQL executes the SELECT statement through the explain command. Such as:

Mysql> Explain select * from EMP1;

+----+-------------+-------+------+---------------+------+---------+------+------+-------+

| ID | Select_type | Table | Type | Possible_keys | Key | Key_len | Ref | Rows | Extra |

+----+-------------+-------+------+---------------+------+---------+------+------+-------+

| 1 | Simple | EMP1 | All | NULL | NULL | NULL |    NULL | 4 | NULL |

+----+-------------+-------+------+---------------+------+---------+------+------+-------+

1 row in Set (0.00 sec)

• Select_type--select type

• table--Table of output results

• type--indicates how MySQL finds the required rows in a table, or is called an access type, often in the following ways: Performance from worst to best.

Type=all, that is, a matching row is found through a full table scan.

Type=index, index full scan, MySQL traversal index only to find matching rows.

Type=range, index range Scan,

Type=ref, using a non-unique index scan, or a prefix scan of a unique index, to return a record row that matches a single value

Type=eq_ref, similar to ref, differs in that the index used is a unique index, and only one record in the table matches for each index key value.

Type=const/system, there is a maximum of one matching row in the form, which is very fast to query. such as queries based on primary key and unique index.

Type=null, you do not need to access the table or index, you can get the results directly.

• possible_keys--represents the index that may be used when querying

• key--represents the actual index used

• key_len--the length of the index field to use

• rows--Number of scanned rows

• extra--description and description of the implementation

4 analyzing SQL by Show profile

Mysql> SELECT @ @have_profiling; #查看是否支持

+------------------+

| @ @have_profiling |

+------------------+

| YES |

+------------------+

Mysql> set profiling=1; #开启profiling, the default is to turn off

Query OK, 0 rows affected, 1 Warning (0.00 sec)

Mysql> select * from EMP1; #执行一个语句

+------+--------+-------+------------+

| Age1 | Deptno | ename | Birth |

+------+--------+-------+------------+

|      111 | 4 | CCC | 2011-11-30 |

|     666 | 11 | DDD | 2014-12-22 |

|     888 | 22 | eee | 2015-11-30 |

|      333 | 8 | FFF | 2011-04-30 |

+------+--------+-------+------------+

4 rows in Set (0.02 sec)

Mysql> Show Profiles; #查看当前SQL语句的查询ID

+----------+------------+---------------------------+

| query_id | Duration | Query |

+----------+------------+---------------------------+

| 1 | 0.01696625 | Select COUNT (*) from EMP1 |

| 2 | 0.02623125 | SELECT * FROM EMP1 |

+----------+------------+---------------------------+

Mysql> Show profile for query 2; #查看执行过程中线程的每个状态和消耗时间

+----------------------+----------+

| Status | Duration |

+----------------------+----------+

| Starting | 0.000111 |

| Checking Permissions | 0.000019 |

| Opening Tables | 0.000046 |

| init | 0.000043 |

| System Lock | 0.000031 |

| Optimizing | 0.000016 |

| Statistics | 0.000039 |

| Preparing | 0.000023 |

| Executing | 0.000008 |

| Sending Data | 0.025442 |

| End | 0.000020 |

| Query End | 0.000014 |

| Closing Tables | 0.000016 |

| Freeing items | 0.000326 |

| Cleaning Up | 0.000079 |

+----------------------+----------+

Sending data means that the MySQL thread starts accessing the rows and returns the results to the client. is typically the longest-consuming state in the entire query

Mysql> Show profile CPU for query 2; #查看耗费CPU的时间, sending data is mainly consumed on the CPU

+----------------------+----------+----------+------------+

| Status | Duration | Cpu_user | Cpu_system |

+----------------------+----------+----------+------------+

| Starting | 0.000111 |   0.000000 | 0.000000 |

| Checking Permissions | 0.000019 |   0.000000 | 0.000000 |

| Opening Tables | 0.000046 |   0.000000 | 0.000000 |

| init | 0.000043 |   0.000000 | 0.000000 |

| System Lock | 0.000031 |   0.000000 | 0.000000 |

| Optimizing | 0.000016 |   0.000000 | 0.000000 |

| Statistics | 0.000039 |   0.000000 | 0.000000 |

| Preparing | 0.000023 |   0.000000 | 0.000000 |

| Executing | 0.000008 |   0.000000 | 0.000000 |

| Sending Data | 0.025442 |   0.000000 | 0.001999 |

| End | 0.000020 |   0.000000 | 0.000000 |

| Query End | 0.000014 |   0.000000 | 0.000000 |

| Closing Tables | 0.000016 |   0.000000 | 0.000000 |

| Freeing items | 0.000326 |   0.000000 | 0.000000 |

| Cleaning Up | 0.000079 |   0.000000 | 0.000000 |

+----------------------+----------+----------+------------+

5 How to select an execution plan through the trace analysis optimizer

6 After identifying the problem, take appropriate measures to optimize

An index of a field in a table is indexed by the previous steps to confirm a full table scan of the table, which results in an unsatisfactory query. Specific as follows:

Mysql> CREATE index Index_ename on EMP1 (ename);

Query OK, 0 rows affected (0.25 sec)

records:0 duplicates:0 warnings:0

After indexing, look at the execution state of this statement:

Mysql> explain select ename from EMP1;

After indexing, you can see that the number of rows scanned for a table is greatly reduced, increasing the speed of access to the table.

Two-index problem

Indexing is one of the most important and commonly used methods of database optimization, and indexing helps users to solve most SQL performance problems.

1 Storage classifications for indexes: indexes are implemented in the storage engine layer

• B-tree index: Most common indexes, most engines support B-Tree indexes.

• Hash index: Only memory engine support, simple to use the scene
  

• Full-text (full-text index): A special type of index

Create INDEX Mode 1:

mysql> Create index index_age1 on EMP1 (AGE1);

Query OK, 0 rows affected (0.15 sec)

records:0 duplicates:0 warnings:0

Create INDEX Mode 2:

mysql> ALTER TABLE ZWJ.EMP1 add index Index_ename (ename);

Query OK, 0 rows affected (0.05 sec)

records:0 duplicates:0 warnings:0

View index:

Mysql> Show index from ZWJ.EMP1;

+-------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+ ------+------------+---------+---------------+

| Table | Non_unique | Key_name | Seq_in_index | column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | index_comment |

+-------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+ ------+------------+---------+---------------+

|          EMP1 | 1 |            Index_ename | 1 | ename |           A |     4 | NULL | NULL | YES |         BTREE |               | |

|          EMP1 | 1 |            Index_age1 | 1 | Age1 |           A |     4 | NULL | NULL | YES |         BTREE |               | |

+-------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+ ------+------------+---------+---------------+

To delete an index:

Mysql> DROP index index_age1 on ZWJ.EMP1;

Query OK, 0 rows affected (0.06 sec)

records:0 duplicates:0 warnings:0

Or

mysql> ALTER TABLE ZWJ.EMP1 DROP INDEX index_ename;

Query OK, 0 rows affected (0.04 sec)

records:0 duplicates:0 warnings:0

Another composite index: need to consult a developer

Create a composite index (the column that is most commonly used as a constraint is left-most, decreasing in turn):

Mysql> CREATE index name_passwd on abc.student (NAME,PASSWD); (need to consult research and development Department)

2 Viewing the usage of the index:

Mysql> Show status like ' handler_read% ';

+-----------------------+-------+

| variable_name | Value |

+-----------------------+-------+

| Handler_read_first | 4 |

| Handler_read_key | 5 |

| Handler_read_last | 0 |

| Handler_read_next | 0 |

| Handler_read_prev | 0 |

| Handler_read_rnd | 0 |

| Handler_read_rnd_next | 56 |

+-----------------------+-------+

7 Rows in Set (0.00 sec)

Handler_read_key: If the index is working, this value should be high, which represents the number of times a row has been read by the indexed value. If the value is too low, the performance improvement of the index is not high because the index is not often used.

Handler_read_rnd_next: High values mean that queries run inefficiently, and Index remediation should be established. The meaning of this value is the number of requests to read the next line in the data file. If a large number of scans are performed, the value is high, indicating that the index is incorrect or the query is not taking advantage of the index.

This article from "10,000 years too long, seize" blog, please be sure to keep this source http://zengwj1949.blog.51cto.com/10747365/1920641

MySQL SQL statement optimization