35 pictures show you MySQL tuning

This is the fourth article in the MySQL Basics series. For the previous three articles, see the following links

138 pictures take you to get started with MySQL

47 pictures take you MySQL advanced! ! !

burst! MySQL 82 pictures take you to fly

Generally, traditional Internet companies rarely come into contact with SQL optimization problems. The reason is that the amount of data is small. Most of the manufacturers’ database performance can meet daily business needs, so SQL optimization is not required. However, as the application continues to grow, the data The performance of the database itself cannot keep up with the rapid increase in the amount of data. At this time, it is necessary to optimize from the perspective of SQL itself, which is also discussed in this article.

SQL optimization steps

When faced with a SQL that needs to be optimized, what kinds of troubleshooting ideas do we have?

Know the number of SQL executions through the show status command

First, we can use the show status command to view the server status information. The show status command will display the variable_name and value of each server variable. The status variable is read-only. If you use SQL commands, you can use like or where conditions to limit the results. like can do standard pattern matching on variable names.

I haven't cut the picture completely, there are many variables below, readers can try it out for themselves. You can also use the mysqladmin extended-status command on the operating system to get these messages.

But after I execute mysqladmin extended-status, this error appears.

It should be the reason why I did not enter the password. After using mysqladmin -P3306 -uroot -p -h127.0.0.1 -r -i 1 extended-status , the problem was solved.

Here you need to pay attention to the level of statistical results that can be added to the show status command. There are two levels for this level

• Session level: the statistical results of the current link by default
• Global level: the statistical results since the database was last started to the present

If the statistical result level is not specified, the session level is used by default.

For the statistical results of the show status query, there are two types of parameters that need to be paid attention to. One is Com_ , and the other is the parameter starting Innodb_

The following are the parameters starting with Com_. There are many parameters, and I haven't cut them all.

Com_xxx represents the number of executions of each xxx statement. What we usually care about is the number of executions of select, insert, update, and delete statements, namely

• Com_select: The number of times the select operation is executed, a query will make the result +1.
• Com_insert: The number of times to execute INSERT operations. For batch insert INSERT operations, it is only accumulated once.
• Com_update: The number of UPDATE operations performed.
• Com_delete: The number of DELETE operations performed.

The parameters starting with Innodb_ mainly include

• Innodb_rows_read: The number of rows returned by the 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 statistics of the execution results of the above parameters, we can roughly understand whether the current database is mainly updated (including inserts, deletes) or queries.

In addition, there are some other parameters for understanding the basic situation of the database.

• Connections: The number of connections to query the MySQL database. This number is counted regardless of whether the connection is successful or not.
• Uptime: The working time of the server.
• Slow_queries: The number of full queries.
• Threads_connected: View the number of currently open connections.

The following blog summarizes almost all show status parameters, which can be used as a reference manual.

https://blog.csdn.net/ayay_870621/article/details/88633092

Locate SQL with low execution efficiency

There are generally two ways to locate SQL statements with slower execution efficiency

• You can use the slow query log to locate which SQL statements are executed inefficiently.

MySQL provides a slow query logging function, which can write statements whose query SQL statement time is longer than how many seconds into the slow query log. In daily maintenance, you can quickly and accurately determine the problem through the record information of the slow query log. When started with the --log-slow-queries option, mysqld will write a log file that contains all SQL statements whose execution time exceeds long_query_time seconds, and locate the inefficient SQL by viewing this log file.

For example, we can add the following code to my.cnf, and then exit and restart MySQL.

log-slow-queries = /tmp/mysql-slow.log
long_query_time = 2

Usually we set the longest query time to be 2 seconds, which means that the query time is more than 2 seconds to record. Normally, 2 seconds is enough. However, for many WEB applications, 2 seconds is still relatively long.

It can also be turned on by command:

Let's first query whether the MySQL slow query log is enabled

show variables like "%slow%";

Enable slow query log

set global slow_query_log='ON';

Then check again whether the slow query is turned on

As shown in the figure, we have turned on the slow query log.

The slow query log will be recorded after the end of the query, so the slow query log cannot locate the problem when there is a problem with the application response execution efficiency. At this time, you should use the show processlist command to view the current MySQL threads in progress. Including the status of the thread, whether the table is locked, etc., you can view the SQL execution in real time. Similarly, use the mysqladmin processlist statement to get this information.

Let's explain the concepts corresponding to each field below

• Id: Id is an indicator, which is useful when we use the kill command to kill a process, such as the kill process number.
• User: Display the current user. If it is not root, this command will only display the SQL statements within your authority.
• Host: Display IP, used to track problems
• Db: Shows which database this process is currently connected to. If it is null, it means that the database has not been selected yet.
• Command: Displays the commands executed by the current connection lock. Generally, there are three types: query query, sleep, and connect.
• Time: The duration of this state, in seconds
• State: Displays the state of the current SQL statement, which is very important and will be explained in detail below.
• Info: Display this SQL statement.

The State column is very important. There are more content about this column. Readers can refer to this article.

https://blog.csdn.net/weixin_34357436/article/details/91768402

This involves options such as the status of the thread, whether to lock the table, etc., you can view the execution of SQL in real time, and optimize some lock tables at the same time.

Analyze SQL execution plan through EXPLAIN command

After querying the inefficient SQL statement through the above steps, you can use the EXPLAIN or DESC command to obtain information on how MySQL executes the SELECT statement, including how the tables are connected and the order of the connections during the execution of the SELECT statement.

For example, we use the following SQL statement to analyze the execution plan

explain select * from test1;

The content in the above table is as follows

• select_type: Represents the common SELECT types. SIMPLE is a common one. SIMPLE represents a simple SQL statement and does not include UNION or subquery operations. For example, the following paragraph is the SIMPLE type.

PRIMARY, the outermost SELECT in the query (for example, two tables do UNION or the outer table operation with subquery is PRIMARY, and the inner operation is UNION), such as the following subquery.

UNION, in the UNION operation, the inner SELECT in the query (when the inner SELECT statement has no dependency on the outer SELECT statement).

SUBQUERY: The first SELECT in the subquery (if there are multiple subqueries), such as our query statement above, the first subquery is the sr (sys_role) table, so its select_type is SUBQUERY.

• table, this option indicates the table of the output result set.

• type, this option indicates the connection type of the table. This option is of great value for in-depth research, because many SQL tuning is based on type, but in this article we mainly focus on the optimization method. We use the type field As an understanding for the time being, this article is not going to go too deep.

  The type field will be involved in the performance of the connection, and its different types of performance from good to poor are

  system: When there is only one piece of data in the table, the query of this table is like querying a constant table.

  const: When there is only one record in the table that matches, for example, the primary key or unique index of the table is used for query.

  eq-ref: Indicates that the primary key of the table or the unique index of the table is used when connecting multiple tables, such as

  select A.text, B.text where A.ID = B.ID

For this query statement, for each ID row in the A table, there can only be a unique B.Id in the B table for matching.

ref: This type is not as fast as the eq-ref above, because it means that for each row scanned in table A, there are several possible rows in table C, and C.ID is not unique.

ref_or_null: Similar to ref, except that this option includes a query for NULL.

index_merge: The query statement uses more than two indexes. For example, in scenarios where the and and or keywords often appear, but because reads too many indexes its performance may not be as good as range (described later).

unique_subquery: This option is often used after the in keyword. In subqueries with the where keyword, the subquery is represented by sql.

value IN (SELECT primary_key FROM single_table WHERE some_expr)

range: index range query such as =, <>, >, >=, <, <=, IS NULL, <=>, BETWEEN, IN() or like.

index: scan the entire table of the index, sweep the index from start to finish.

all: We have the most contact with this, that is, the full table query, select * from xxx, the performance is the worst.

The above is a general explanation of the content of type. Regarding type, we often use explain to analyze its type in the SQL tuning process, and then improve the query method. The closer to system, the higher the query efficiency, and the closer to all, the lower the query efficiency.

• possible_keys: Indicates the indexes that may be used when querying.
• key: Indicates the index actually used.
• key_len: The length of the index field.
• rows: The number of scanned rows.
• filtered: The proportion of the total number of rows occupied by the SQL number queried by the query conditions.
• extra: description of the implementation.

Through the above analysis, we can roughly determine the reason for the low efficiency of SQL. A very effective way to improve the efficiency of SQL queries is to use indexes. Next, I will explain how to use indexes to improve query efficiency.

index

Indexing is the most commonly used and most important method in database optimization. By using different indexes, most SQL performance problems can be solved. It is also an optimization method that is often asked in interviews. Around the index, the interviewer allows you to create a rocket, so The summary is that the index is very, very heavy! want! Not just use it, you have to understand the reason! Rationale!

Index introduction

The purpose of the index is to quickly find the data of a certain column, and the use of the index on the related data column can greatly improve the performance of the query operation. Without indexing, MySQL must read the entire table from the first record until it finds the relevant rows. The larger the table, the more time it takes to query the data. If the query column in the table has an index, MySQL can quickly reach a location to search the data file without having to view all the data, which will save a lot of time.

Index classification

Let's first understand what classifications are available in the index.

• Global Index (FULLTEXT): Global index, currently only MyISAM engine supports global index, it appears to solve the problem of low efficiency of fuzzy query for text, and is limited to CHAR, VARCHAR and TEXT columns.
• Hash Index (HASH): A hash index is a data structure of the only key-value pair used in MySQL, which is very suitable as an index. HASH index has the advantage of one-time positioning. It does not need to look up node by node like a tree, but this kind of search is suitable for searching for a single key. For range search, the performance of hash index will be very low. By default, the MEMORY storage engine uses HASH index, but it also supports BTREE index.
• B-Tree Index: B means Balance. BTree is a balanced tree. It has many variants. The most common is B+ Tree, which is widely used by MySQL.
• R-Tree index: R-Tree is rarely used in MySQL and only supports geometry data type. The only storage engines that support this type are MyISAM, BDb, InnoDb, NDb, Archive. Compared with B-Tree, R-Tree The advantage of Tree lies in the range search.

To logically classify MySQL, it is mainly divided into the following categories

• Ordinary index: Ordinary index is the most basic index type, it has no restrictions. The creation method is as follows

  create index normal_index on cxuan003(id);

Delete method

drop index normal_index on cxuan003;

• Unique index: The value of the unique index column must be unique, and null values are allowed. If it is a composite index, the combination of column values must be unique. The creation method is as follows

  create unique index normal_index on cxuan003(id);

• Primary key index: It is a special index. A table can only have one primary key and no null values are allowed. Generally, the primary key index is created at the same time as the table is built.

  CREATE TABLE `table` (
           `id` int(11) NOT NULL AUTO_INCREMENT ,
           `title` char(255) NOT NULL ,
           PRIMARY KEY (`id`)
  )

• Composite index: refers to an index created on multiple fields. The index will only be used when the first field when creating the index is used in the query conditions. When using a composite index, follow the principle of the leftmost prefix, and we will create a composite index below.

• Full-text index: It is mainly used to find keywords in the text, rather than directly compare with the value in the index. At present, only char, varchar, and text columns can be created with full-text index. It is suitable to add full-text index when creating a table.

  CREATE TABLE `table` (
      `id` int(11) NOT NULL AUTO_INCREMENT ,
      `title` char(255) CHARACTER NOT NULL ,
      `content` text CHARACTER NULL ,
      `time` int(10) NULL DEFAULT NULL ,
      PRIMARY KEY (`id`),
      FULLTEXT (content)
  );

Of course, you can also create a global index directly

CREATE FULLTEXT INDEX index_content ON article(content)

Index usage

The index can be created when the table is created, or it can be created separately. Below we use a separate creation method. We create a prefix index on cxuan004

We use explain for analysis, and we can see how cxuan004 uses the index

If you don’t want to use the index, you can delete the index. The delete syntax of the index is

Index Usage Rules

We create a composite index based on id and hash on cxuan005, as shown below

create index id_hash_index on cxuan005(id,hash);

Then analyze the execution plan according to id

explain select * from cxuan005 where id = '333';

It can be found that even if the composite index (Id, hash) is not used in the where condition, the index can still be used. This is the prefix feature of the index. But if you only query according to the hash, the index will not be used.

explain select * from cxuan005 where hash='8fd1f12575f6b39ee7c6d704eb54b353';

If the where condition uses the like query, and % not the first character, the index may be used.

For compound indexes, you can only use id for like query, because the hash column will not be indexed no matter how you query it.

explain select * from cxuan005 where id like '%1';

As you can see, if the first character is %, no index is used.

explain select * from cxuan005 where id like '1%';

If the% sign is used, the index will be triggered.

If the column name is an index, then a NULL query on the column name will trigger the index.

explain select * from cxuan005 where id is null;

There are also cases where indexes exist but MySQL will not use them.

• The simplest, if the efficiency of using the index is worse than not using the index, then MySQL will not use the index.

• If the OR condition is used in SQL, the condition column before the OR has an index, and the following column has no index, then the index involved will not be used. For example, in the cxuan005 table, only the id and hash fields have indexes, and the info field There is no index, then we use or to query.

  explain select * from cxuan005 where id = 111 and info = 'cxuan';

We can see from the execution result of explain that although the id_hash_index index is still on the possible_keys option, it can be known from the key and key_len that this SQL statement does not use the index.

• Query data that is not the first column on a column with a composite index, and the index will not be used.

  explain select * from cxuan005 where hash = '8fd1f12575f6b39ee7c6d704eb54b353';

• If the column of the where condition participates in the calculation, then the index will not be used

  explain select * from cxuan005 where id + '111' = '666';

• The index column uses a function, but also does not use an index

  explain select * from cxuan005 where concat(id,'111') = '666';

• If the index column uses like and % is the first character, the index will not be used.
• In the order by operation, the sorted column is also in the where statement, and the index will not be used.

• When the data type is implicitly converted, for example, varchar may be converted to int type without single quotes, it will invalidate the index and trigger a full table scan. For example, the following two examples can clearly illustrate this point

  

• Use IS NOT NULL operations on indexed columns

  

• Use <>, != on the index field. The inequality operator will never use the index, so its processing will only produce a full table scan.

  

There are many scenarios where the index is set but the index does not take effect. This requires continuous summary and improvement in the work of small partners. However, the scenarios of index failure I summarized above can cover most index failure scenarios.

View index usage

During the use of the MySQL index, there is a Handler_read_key , which represents the number of a row of 160c020b10b58d is read by the index value . If the value of Handler_read_key is relatively low, it indicates that the performance improvement obtained by increasing the index is not very satisfactory, and the frequency of index usage may not be high.

There is also a value of Handler_read_rnd_next . A high value means that the query operation efficiency is not high, and an index should be built for rescue. The meaning of this value is the number of requests to read the next line in the data file. If a large number of table scans are in progress, and the value of Handler_read_rnd_next is relatively high, it means that the table index is incorrect or the written query does not use the index.

MySQL analysis table, check table and optimization table

For most developers, they are more inclined to solve the optimization of simple SQL, and the optimization of complex SQL is left to the company's DBA.

Here are a few simple optimization methods from the perspective of ordinary programmers.

MySQL Analysis Table

The analysis table is used to analyze and store the key distribution of the table. The result of the analysis can enable the system to obtain accurate statistical information and make SQL generate the correct execution plan. If it is used to feel that the actual execution plan does not match the expectation, you can execute the analysis table to solve the problem. The syntax of the analysis table is as follows

analyze table cxuan005;

The field attributes involved in the analysis results are as follows

Table: indicates the name of the table;

Op: indicates the operation performed, analyze indicates the analysis operation, check indicates the inspection and search, and optimize indicates the optimization operation;

Msg_type: indicates the type of information, the displayed value is usually one of status, warning, error and information;

Msg_text: Display information.

Regular analysis of the table can improve performance and should be part of daily work. Because the table is analyzed by updating the index information of the table, the database performance can be improved.

MySQL checklist

Databases often encounter errors, such as errors when data is written to disk, or indexes are not updated synchronously, or MySQL stops without closing the database. Encountered these situations, the data may have errors: Incorrect key file for table: ''. Try to repair it . At this point, we can use the Check Table statement to check the table and its corresponding index.

check table cxuan005;

The main purpose of the checklist is to check whether one or more tables have errors. Check Table is useful for MyISAM and InnoDB tables. Check Table can also check for errors in the view.

MySQL optimized table

MySQL optimized tables are suitable for deleting a large amount of table data, or making a lot of modifications to VARCHAR, BLOB or TEXT commands. MySQL optimized tables can merge a large number of space fragments, eliminating space waste caused by deletion or update. Its command is as follows

optimize table cxuan005;

My storage engine is the InnoDB engine, but as you can see from the figure, InnoDB does not support optimize optimization. It is recommended to use recreate + analyze for optimization. The optimize command only works on MyISAM and BDB tables.

I have six PDFs on my own, and they have spread over 10w+ throughout the Internet. After searching for "programmer cxuan" on WeChat and following the

six PDF links