Shulou(Shulou.com)06/01 Report--

General steps for optimizing SQL statements:

1 know 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%"; # displays the values of statistical parameters in the current session

+-+ +

| | 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 is executed. The following statistical parameters are very important:

Com_select: the number of times select is executed. Add 1 to each query.

Com_insert: the number of times insert operations are performed. Batch inserts only add up to 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 table operations of all storage engines.

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

Number of rows returned by Innodb_rows_read:select query

Innodb_rows_inserted: the number of rows inserted by the insert operation

Innodb_rows_updated: the number of rows updated by the update operation

Innodb_rows_deleted: the number of rows deleted by the delete operation

Through the understanding of the above parameters, we can judge whether the current database is mainly insert update or query operation, as well as the approximate execution proportion of various types of SQL.

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

Uptime: working hours of the database server

Connections: the number of attempts to connect to the server

Slow_queries: the number of slow queries

2 locate SQL statements with low execution efficiency

Method 1: locate through slow query log

Method 2: view currently in-progress threads

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 has read all relay log; waiting for the slave thread to update it O thread to update it | NULL |

| | 4 | root | localhost | NULL | Query | 0 | init | show processlist | |

Or

[root@localhost] # mysqladmin-uroot-h 127.0.0.1 processlist-proot

Warning: Using a password on the command line interface can be insecure.

+-+-

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

+-+-

| | 1 | root | localhost | | Sleep | 265 |

| | 12 | root | localhost:42210 | | Query | 0 | init | show processlist |

+-+-

Note: show processlist; only lists the first 100 items. If you want to list them all, please use show full processlist.

3 analyze the execution of inefficient SQL statements through explain

After you have queried the inefficient SQL statement through the previous steps, you can use the explain command to get information about how MySQL executes the select statement. 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 Typ

Table of table-- output results

Type-- means how MySQL finds the required rows in the table, or the type of access, which is common in the following ways: performance ranges from the worst to the best.

Type=all, that is, the matching rows are found through a full table scan.

Type=index, index full scan, mysql traverses the index to find matching rows.

Type=range, index range scan

Type=ref, which uses a scan of a non-unique index, or a prefix scan of a unique index, to return rows of records that match a single value

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

Type=const/system, which has at most one matching row in the form, can be queried very quickly. Such as queries based on primary keys and unique indexes.

Type=null, you don't need to access a table or index to get the results directly.

Possible_keys-- represents the index that may be used when querying

Key-- represents the index actually used

Key_len-- uses the length of the index field

Number of rows-- scan lines

Description and description of Extra-- implementation

4 understand and analyze the process of SQL execution through show profile

Mysql > select @ @ have_profiling; # to see if it is supported

+-+

| | @ @ have_profiling |

+-+

| | YES |

+-+

Mysql > set profiling=1; # enable profiling. Default is off.

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

Mysql > select * from emp1; # execute a statement

+-+

| | age1 | deptno | ename | birth | |

+-+

| | 2011 | 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; # View the query ID of the current SQL statement

+-+

| | Query_ID | Duration | Query | |

+-+

| | 1 | 0.01696625 | select count (*) from emp1 |

| | 2 | 0.02623125 | select * from emp1 |

+-+

Mysql > show profile for query 2; # View the status and elapsed time of each thread during execution

+-+ +

| | 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 indicates that the MySQL thread starts accessing the data row and returns the result to the client. Usually the most time-consuming state in the entire query

Mysql > show profile cpu for query 2; # check the time spent on CPU, and Sending data is mainly spent on 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 | |

+-+

Mysql > show profile all for query 1\ G # check all the details to see what resources MySQL is spending too much time on

5 analyze how the optimizer chooses the execution plan through trace

6 after determining the problem, take corresponding measures to optimize

The previous step confirms that a full table scan of the table results in unsatisfactory query results, then an index is established on a field of the table. The details are 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, take a look at the execution status of this statement:

Mysql > explain select ename from emp1

After the index is established, it can be found that the number of rows scanned on the table is greatly reduced, and the access speed to the table is improved.

Second index problem

Index is one of the most important and commonly used methods in database optimization, which can help users solve most SQL performance problems.

1 Storage classification of indexes: indexes are implemented in the storage engine layer

B-Tree index: the most common index, most engines support B-tree index.

HASH index: only supported by Memory engine. It is easy to use.

Full-text (full text Index): a special type of index

Create index method 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 method 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 the 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 |

+-- -+

Delete the 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: you need to consult the developer

Create a composite index (place the column most commonly used as a constraint on the far left, decreasing in turn):

Mysql > create index name_passwd on abc.student (name,passwd); (need to consult R & D department)

2 check 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, and this value represents the number of times a row has been read by the index value. If the value is too low, the performance improvement from increasing the index is not high, because the index is not often used.

Handler_read_rnd_next: a high value means that the query runs inefficiently and index remediation should be established. This value means the number of requests to read the next line in the data file. If a large number of scans are performed, its value will be high, indicating that the index is incorrect or that the query does not take advantage of the index.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.