Performance optimization of Mysql

Performance optimization of Mysql

1. Reference book: MYSQL 5.5 learning from scratch
MySQL performance optimization can make MySQL run faster and save resources by reasonably arranging resources and adjusting system parameters. MySQL performance optimization includes query speed optimization, update speed optimization, MySQL server optimization and so on. Here, the following optimizations are introduced. Including, introduction to performance optimization, query optimization, database structure optimization, MySQL server optimization.
MySQL optimization, on the one hand, is to find out the bottleneck of the system and improve the overall performance of MySQL database. On the other hand, it needs reasonable structural design and parameter adjustment to improve the speed of user operation response. At the same time, save system resources as much as possible so that the system can provide greater load services. Mysql database optimization is multifaceted. The principle is to reduce the bottleneck of the system, reduce the occupation of resources and increase the speed of system response.

2. In Mysql, you can use the SHOW STATUS statement to query the performance parameters of some Mysql databases. The syntax of the SHOW STATUS statement is as follows:
Where, value is the parameter value to be queried. Some common performance parameters are as follows:
     a. Connections, the number of connections to the mysql server.
     b. Uptime, the online time of mysql server.
     c,Slow_queries, the number of slow queries.
     d,Com_select, the number of query operations.
     e,Com_insert, the number of insert operations.
     f,Com_update, the number of update operations.
     g,Com_delete, the number of delete operations.

1 -- query mysql Number of connections to the server
2 SHOW STATUS LIKE 'Connections';
4 -- query mysql Number of slow queries on the server. The slow query times parameter can be combined with the slow query log to find out the slow query statements, and then optimize the table structure or query statements for the slow query statements.
5 SHOW STATUS LIKE 'Slow_queries';

  3. Analyze query statements.

 1 Through the analysis of query statements, we can understand the execution of query statements, find out the bottleneck of query statement execution, and optimize query statements. mysql Provided in EXPLAIN Statement and DESCRIBE Statement to analyze query statements.
 2 EXPLAIN The basic syntax of the statement is as follows:
 3 EXPLAIN [EXTENDED] SELECT select_options;
 4 use EXTENED keyword,EXPLAIN Statement will produce additional information. select_options yes select Statement, including from where Clause, etc.
 5 Execute this statement to analyze EXPLAIN hinder select Statement, and can analyze some characteristics of the queried table.
 6 For example: EXPLAIN SELECT * FROM user;
 8 Explain the query results:
 9 a,id: select ID, this is select Query serial number of.
10 b,select_type: identification select Type of statement.
11     It can take the following values:
12         b1,SIMPLE(simple)Represents a simple query, excluding join queries and subqueries.
13         b2,PRIMARY(primary)Represents the main query or the outermost query statement.
14         b3,UNION(union)Represents the second or subsequent query statement of the join query.
15         b4,DEPENDENT UNION(dependent union)Join the second or later in the query select sentence. Depends on external queries.
16         b5,UNION RESULT(union result)Connect the results of the query.
17         b6,SUBQUERY(subquery)1st subquery select sentence.
18         b7,DEPENDENT SUBQUERY(dependent subquery)1st subquery select,Depends on external queries.
19         b8,DERIVED(derived)Of exported tables SELECT(FROM Subquery of clause). 
20 c,table: Represents the table of the query.
21 d,type: Represents the connection type of the table.
22     Various connection types are given below in order from the best type to the worst type.
23     d1,system,This table is a system table with only one row. This is const A special case of connection type.
24     d2,const,The data table has at most one matching row, which will be read at the beginning of the query and treated as a constant for the rest of the query optimization. const Table queries are fast because they are read only once. const Used for comparison using constant values primary key perhaps unique All parts of the index.
25         For example: EXPLAIN SELECT * FROM user WHERE id=1;
26     d3,eq_ref,For each row combination from the previous table, one row is read from that table. When all parts of an index are used in a query and the index is UNIQUE perhaps PRIMARY KEY This type can be used when. eq_ref Can be used to use“="Operator compares indexed columns. The comparison value can be a constant or an expression of the column of the table read before the table.
27         For example: EXPLAIN SELECT * FROM user,db_company WHERE user.company_id =;
28     d4,ref For any combination of rows from the previous table, all matching rows will be read from the table. This type is used for, so it is neither UNION Neither primaey key Or the left subset of the index column is used in the query, that is, the combination of the left parts in the index. ref Can be used to use=perhaps<=>The indexed column of the operator.
29     d5,ref_or_null,This connection type if ref,But if you add mysql You can specifically search for a file that contains null The optimization of this connection type is often used in solving subqueries.
30     d6,index_merge,This connection type indicates that the index merge optimization method is used. In this case, key Column contains a list of indexes used, key_len Contains the longest key element of the index used.
31     d7,unique_subquery,This type replaces the following form in Subquery ref. Is an index query function, which can completely replace sub queries and is more efficient.
32     d8,index_subquery,The connection type is similar to unique_subquery,Can replace in Subquery, but only suitable for non unique indexes in the following forms of subquery.
33     d9,range,Retrieve only rows in a given range, using an index to select rows. key The column shows which index is used. key_len Contains the longest key element of the index used=,<>,>,>=,<,<=,is null,<=>,between perhaps in Operator, when comparing keyword columns with constants, the type is range. 
34     d10,index,The connection type is the same as all Same, except that only the index tree is scanned all Fast, citation index pricing is usually smaller than data files.
35     d11,all,For any row combination of the previous table, perform a complete table scan. If the table is the first, it is not marked const This is not good, and in other cases it is bad. You can usually add more indexes to avoid using all connect.
36 e,possible_keys:possible_keys Column indicates mysql Can use that index to find rows in the table. If the column is null,There is no relevant index. In this case, you can pass the check where Clause to see if it causes some columns or columns suitable for index to improve query performance. If so, you can create a suitable index to improve query performance.
37 f,key: Indicates the index actually used by the query. If no index is selected, the value of this column is null,To force mysql Use or neglect possible_key Index in column, used in query force index,use index perhaps ignore index. 
38 g,key_len: express mysql Select the length of the index field in bytes. If yes null,Then the length is null. Attention through key_len The value can be determined mysql Several fields in a multi column index will actually be used.
39 h,ref:Indicates which column or constant or index is used to query records together.
40 i,rows:display mysql The number of rows that must be checked for queries in the table.
41 j,Extra:This column mysql Details when processing queries.

4. The use method of the DESCRIBE statement is the same as that of the EXPLAIN statement, and the analysis results are the same. The syntax form of the DESCRIBE statement is as follows: DESCRIBE SELECT select_options. DESCRIBE can be abbreviated to DESC.

5. The impact of index on query speed.

1 mysql The most effective way to improve performance in is to design a reasonable index for the data table. Indexing provides an efficient way to access data and speeds up queries. Therefore, index has a crucial impact on the speed of query. Therefore, you can quickly locate a record in the table, so as to improve the speed of database query and improve the performance of the database. If the index is not used when querying, the query statement will scan all records in the table. In the case of large amount of data, the query speed will be very slow. If the index is used for query, the query statement can quickly locate the records to be queried according to the index, so as to reduce the number of records to be queried and improve the query speed.

For example, the difference between using an index and not using an index:

The value of rows column without index is 26, indicating that this query statement has scanned 26 records.

The value of the rows column using the index is 1, indicating that this query statement has scanned 1 record. The query speed naturally becomes faster. This method can also determine whether the added has worked.

  6. Using index query, there are several special cases.

Index can improve the speed of query, but it does not work when using field query with index. Here are some special cases. In these cases, when it is possible to use a field query with an index, the index does not work.

6.1. Query statements using the like keyword. In a query statement that uses the like keyword to query, if the first character of the matching string is'% ', the index will not work. The index will only work if '%' is not in the first position.

Using fuzzy query, "%" in the previous results shows that the index has no effect.

Using fuzzy query, "%" in the following results, it shows that the cable has played a role.

6.2. Query statements using multi column indexes. mysql can create indexes for multiple fields. An index can include 16 fields. For multi column indexes, the index will be used only when the first field of these fields is used in the query criteria.

When the first field of multiple columns is used as the query condition, it is found that the search is effective:

6.3. Query statement using or keyword. In the query condition using the statement, only the or keyword is used, and the columns in the two conditions before and after or have indexes. Otherwise, the query will not be applicable to the index.

Use the name index and the sex index to query the qualified.



Tags: MySQL

Posted on Sat, 27 Nov 2021 21:01:24 -0500 by electrix