In the process of application development, due to the small amount of data in the initial stage, developers pay more attention to the realization of functions when writing SQL statements. However, after the application system is officially launched, with the rapid growth of production data, many SQL statements begin to show performance problems gradually and have a greater and greater impact on production, At this time, these problematic SQL statements become the bottleneck of the whole system performance, so we must optimize them.
When facing a database with SQL performance problems, where should we start to make a systematic analysis, so that we can locate and solve the problem as soon as possible.
View SQL execution frequency
After the MySQL client is successfully connected, the server status information can be provided through the show [session|global] status command. show
[session|global] status can add the parameter "session" or "global" as needed to display the session level (current connection) and global level (since the last startup of the database). If you do not write, the default parameter is "session".
The following command displays the values of all statistical parameters in the current session:
show status like 'Com_______';
show status like 'Innodb_rows_%';
Com_xxx indicates the number of times each xxx statement is executed. We usually care about the following statistical parameters.
| parameter | meaning |
|---|---|
| Com_select | The number of times the select operation is executed. A query only accumulates 1. |
| Com_insert | The number of times the INSERT operation is executed. For the INSERT operation of batch insertion, it is only accumulated once. |
| Com_update | The number of times the UPDATE operation was performed. |
| Com_delete | The number of times the DELETE operation was performed. |
| Innodb_rows_read | select the number of rows returned by the 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. |
| Connections | The number of attempts to connect to the MySQL server. |
| Uptime | Server working hours. |
| Slow_queries | Number of slow queries. |
Com_*** : These parameters are accumulated for table operations of all storage engines.
Innodb_*** : These parameters are only for the InnoDB storage engine, and the accumulation algorithm is slightly different.
Locate inefficient SQL execution
You can locate SQL statements with low execution efficiency in the following two ways.
- Slow query log: locate SQL with low execution efficiency through slow query log
Statement. When started with the – log slow queries [= file_name] option, mysqld writes a statement containing all the execution time
long_ query_ Log file of SQL statement with time seconds. - show processlist :
The slow query log is recorded only after the query is completed. Therefore, querying the slow query log can not locate the problem when the application reflects that there is a problem in the execution efficiency. You can use the show processlist command to view the current MySQL thread, including the thread status and whether to lock the table. You can view the execution of SQL in real time and optimize some table locking operations at the same time.
show processlist
1) When you log in to the system, you can use the "connection id" column of the mysql function_ id () view
2) The user column displays the current user. If it is not root, this command will only display the sql statements within the user's permission range
3) The host column shows the ip address and port from which the statement is sent. It can be used to track the user of the statement in question
4) db column, showing which database this process is currently connected to
5) The command column displays the commands executed by the current connection. The general values are sleep, query, connect, etc
6) The time column displays the duration of this state, in seconds
7) The state column displays the status of the sql statement using the current connection. It is a very important column. State describes a state in the execution of a statement. An sql statement, taking query as an example, may need to go through the status of copying to tmp table, sorting result, sending data, etc
8) info column, which displays the sql statement, is an important basis for judging the problem statement
explain analysis execution plan
After querying the inefficient SQL statement through the above steps, you can obtain the information of how MySQL executes the SELECT statement through the EXPLAIN or DESC command, including how to connect the tables and the order of connection during the execution of the SELECT statement.
explain select * from user where id = 1;
| field | meaning |
|---|---|
| id | The sequence number of select query is a group of numbers, which indicates the order in which the select clause or operation table is executed in the query. |
| select_type | Indicates the type of SELECT. Common values include SIMPLE (SIMPLE table, i.e. no table connection or sub query), PRIMARY (PRIMARY query, i.e. outer query), UNION (the second or subsequent query statement in UNION), SUBQUERY (the first SELECT in sub query), etc |
| table | Table of output result set |
| type | Indicates the connection type of the table. The connection type with good performance to poor performance is (system - > const ------ > eq_ref ------ -- > ref ------ > ref_or_null ------ > index_merge - > index_subquery ------ > range ------ > index ------ -- > all) |
| possible_keys | Represents the index that may be used when querying |
| key | Represents the index actually used |
| key_len | Length of index field |
| rows | Number of scan lines |
| extra | Description and description of implementation |
Environmental preparation
CREATE TABLE `t_role` ( `id` varchar(32) NOT NULL, `role_name` varchar(255) DEFAULT NULL, `role_code` varchar(255) DEFAULT NULL, `description` varchar(255) DEFAULT NULL, PRIMARY KEY (`id`), UNIQUE KEY `unique_role_name` (`role_name`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE `t_user` ( `id` varchar(32) NOT NULL, `username` varchar(45) NOT NULL, `password` varchar(96) NOT NULL, `name` varchar(45) NOT NULL, PRIMARY KEY (`id`), UNIQUE KEY `unique_user_username` (`username`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE `user_role` ( `id` int(11) NOT NULL auto_increment , `user_id` varchar(32) DEFAULT NULL, `role_id` varchar(32) DEFAULT NULL, PRIMARY KEY (`id`), KEY `fk_ur_user_id` (`user_id`), KEY `fk_ur_role_id` (`role_id`), CONSTRAINT `fk_ur_role_id` FOREIGN KEY (`role_id`) REFERENCES `t_role` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION, CONSTRAINT `fk_ur_user_id` FOREIGN KEY (`user_id`) REFERENCES `t_user` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
insert into `t_user` (`id`, `username`, `password`, `name`) values('1','super','$2a$10$TJ4TmCdK.X4wv/tCqHW14.w70U3CC33CeVncD3SLmyMXMknstqKRe','Super administrator');
insert into `t_user` (`id`, `username`, `password`, `name`) values('2','admin','$2a$10$TJ4TmCdK.X4wv/tCqHW14.w70U3CC33CeVncD3SLmyMXMknstqKRe','system administrator');
insert into `t_user` (`id`, `username`, `password`, `name`) values('3','itcast','$2a$10$8qmaHgUFUAmPR5pOuWhYWOr291WJYjHelUlYn07k5ELF8ZCrW0Cui','test02');
insert into `t_user` (`id`, `username`, `password`, `name`) values('4','stu1','$2a$10$pLtt2KDAFpwTWLjNsmTEi.oU1yOZyIn9XkziK/y/spH5rftCpUMZa','Student 1');
insert into `t_user` (`id`, `username`, `password`, `name`) values('5','stu2','$2a$10$nxPKkYSez7uz2YQYUnwhR.z57km3yqKn3Hr/p1FR6ZKgc18u.Tvqm','Student 2');
insert into `t_user` (`id`, `username`, `password`, `name`) values('6','t1','$2a$10$TJ4TmCdK.X4wv/tCqHW14.w70U3CC33CeVncD3SLmyMXMknstqKRe','Teacher 1');
INSERT INTO `t_role` (`id`, `role_name`, `role_code`, `description`) VALUES('5','student','student','student');
INSERT INTO `t_role` (`id`, `role_name`, `role_code`, `description`) VALUES('7','teacher','teacher','teacher');
INSERT INTO `t_role` (`id`, `role_name`, `role_code`, `description`) VALUES('8','Teaching administrator','teachmanager','Teaching administrator');
INSERT INTO `t_role` (`id`, `role_name`, `role_code`, `description`) VALUES('9','administrators','admin','administrators');
INSERT INTO `t_role` (`id`, `role_name`, `role_code`, `description`) VALUES('10','Super administrator','super','Super administrator');
INSERT INTO user_role(id,user_id,role_id) VALUES(NULL, '1', '5'),(NULL, '1', '7'),(NULL, '2', '8'),(NULL, '3', '9'),(NULL, '4', '8'),(NULL, '5', '10') ;
explain id
The id field is the serial number of the select query, which is a group of numbers, indicating the order in which the select clause or the operation table is executed in the query. There are three situations
1) The same id indicates that the order of loading the table is from top to bottom.
explain select * from t_role r, t_user u, user_role ur where r.id = ur.role_id and u.id = ur.user_id ;
2) Different IDs. The larger the id value, the higher the priority and the earlier it is executed.
EXPLAIN SELECT * FROM t_role WHERE id = (SELECT role_id FROM user_role WHERE user_id = (SELECT id FROM t_user WHERE username = 'stu1'))
3) IDs are the same and different, and exist at the same time. Those with the same id can be considered as a group and executed from top to bottom; In all groups, the higher the id value, the higher the priority and the earlier the execution.
EXPLAIN SELECT * FROM t_role r , (SELECT * FROM user_role ur WHERE ur.`user_id` = '2') a WHERE r.id = a.role_id ;
explain select_type
Indicates the type of SELECT and common values, as shown in the following table:
| select_type | meaning |
|---|---|
| SIMPLE | A simple select query that does not contain subqueries or unions |
| PRIMARY | If any complex sub query is included in the query, the outermost query is marked with this ID |
| SUBQUERY | Subqueries are included in the SELECT or WHERE list |
| DERIVED | The sub queries contained in the FROM list are marked as DERIVED. MYSQL will recursively execute these sub queries and put the results in the temporary table |
| UNION | If the second SELECT appears after UNION, it is marked as UNION; If UNION is included in subquery of the FROM clause, outer SELECT will be marked as: DERIVED |
| UNION RESULT | SELECT to get the result from the UNION table |
explain table
Show which table the data in this row is about
explain type
Type displays the access type, which is an important indicator. The values can be:
| type | meaning |
|---|---|
| NULL | MySQL does not access any tables or indexes and returns results directly |
| system | The table has only one row of records (equal to the system table), which is a special case of const type and generally does not appear |
| const | Indicates that the index can be found at one time. const is used to compare the primary key or unique index. Because only one row of data is matched, it is very fast. If the primary key is placed in the where list, MySQL can convert the query into a constant light. const is used to compare all parts of a "primary key" or "unique" index with a constant value |
| eq_ref | Similar to ref, the difference is that the unique index is used. For the association query using the primary key, there is only one record found in the association query. Common in primary key or unique index scanning |
| ref | Non unique index scan that returns all rows that match a single value. In essence, it is also an index access, which returns all rows (multiple) that match a single value |
| range | Retrieve only the rows returned for a given, using an index to select the row. After where, operations such as between, <, >, in appear. |
| index | The difference between index and ALL is that index type only traverses the index tree, which is usually faster than ALL. ALL traverses the data file. |
| all | The entire table will be traversed to find matching rows |
The result value is from best to worst, which is:
NULL > system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL system > const > eq_ref > ref > range > index > ALL
Generally speaking, we need to ensure that the query reaches at least range level, preferably ref.
explain key
possible_keys: displays one or more indexes that may be applied to this table.
key: the index actually used. If it is NULL, the index is not used.
key_len: indicates the number of bytes used in the index. This value is the maximum possible length of the index field, not the actual length. The shorter the length, the better without losing accuracy.
explain rows
Number of scan lines.
explain extra
Other additional execution plan information is displayed in this column.
| extra | meaning |
|---|---|
| using filesort | It shows that mysql will use an external index to sort the data instead of reading according to the index order in the table. It is called "file sorting", which is inefficient. |
| using temporary | Temporary tables are used to save intermediate results. MySQL uses temporary tables when sorting query results. Common in order by and group by; Low efficiency |
| using index | It indicates that the corresponding select operation uses the overlay index to avoid accessing the data rows of the table, which is efficient. |