Common MySQL optimization tips for daily work

1. Describe the optimization techniques often used by MySQL. mysql comes with slow log analysis tool Mysqldumpslow, but no description. By analyzing the script, this paper introduces its usage. Slow log is a file written by MySQL based on the run time setting of the SQL statement that is used to parse the slower-running statements.
Just configure it in the my.cnf file: log-slow-queries = [Slow_query_log_filename] to record an SQL statement that exceeds the default 10s run time. If you want to change the default settings, you can add: Long_query_time = 5 is set to 5s.
The 
 assumes that you want to log all SQL statements that can be written to: log-long-format# T=time, L=lock time, r=rows# at, AL, and AR are the corresponding averages Mysqldumpslow can accept a number of parameters: ' v+ ', # V Erbose ' d+ ', # Debug ' S=s ', # sort (t, at, L, AL, R, AR etc) ' r! ', # Inverted sort (largest last instead of first) ' T=i ', # show highest n queries ' A! ', # Don't put all the numbers with N. string with ' S ' Display ' n=i ', # Abstract numbers with at least n digits within names ' G=s ', # Grep:only consider stmts that include th is string ' h=s ', # Hostname of the DB Server for *-slow.log filename (can be wildcard) ' I=s ', # Name of server instance (if Usin G mysql.server startup script) ' l! ', # don ' t subtract lock time from total timemysql Explain usage instructions explain shows how MySQL uses the index to process SE Lect statements and join tables. 
Can help select better indexes and write more optimized query statements. Usage, add explain before the SELECT statement: For example: Explain select Surname,first_name form A, a where a.id=b.id analysis results form such as the following: reference mysql> explain SELECT * from ' whisper ' WHERE to_id = 6696 and del = 0 and whisper=0 ORDER by ' send_time ' DESC LIMIT 4;+--+ ————-+ ——— +--+-- ——— + ——-+ ——— + ——-+--+ ————————— –+| ID | Select_type | Table | Type | Possible_keys | Key | Key_len | Ref | Rows | Extra |+--+ ————-+ ——— +--+ ————— + ——-+ ——— + ——-+--+ ————————— –+| 1 | Simple | Whisper | Ref | to_id | to_id | 4 | Const | 1 | Using where; Explanation of the Using filesort |+--+ ————-+ ——— +--+ ————— + ——-+ ——— + ——-+--+ ————————— –+1 row in Set (0.00 sec) explain column: Table Displays the data for this row about which table is of type which is an important column. Shows what type of connection is used. The best to worst connection types are const, EQ_REG, ref, range, Indexhe, and Allpossible_keys to display the indexes that may be applied in this table.
Assuming null, there is no possible index.
The ability to select an appropriate statement from the where statement for the related domain key is actually used by the index.
Assuming null, the index is not used. In very few cases, MySQL chooses an index that is poorly optimized. This case. You can use using index (INDEXNAME) in a SELECT statement to force an index or use ignore index (indexname) to force MySQL to ignore the length of the index Key_len used by the index.
 Without loss of accuracy. The shorter the length, the better. Ref shows which column of the index is used, assuming possible. is a constant rows MySQL feels obliged to check the number of rows that are used to return the requested data extra for additional information on how MySQL parses the query. will be discussed in Table 4.3. But the bad example that can be seen here is the using temporary and using filesort, meaning that MySQL simply cannot use the index, and the result is that the retrieval will be very slow extra column returns the meaning of the descriptive narrative distinct once MySQL finds a row that matches a row , no longer searches for the not exists MySQL optimizes the left join, and once it finds a row that matches the left join standard, it no longer searches for the range checked for Eachrecord (index map:#) does not find the ideal index. So for each combination of rows from the previous table, MySQL checks which index to use. and use it to return rows from the table. This is a using filesort in the slowest connection that uses the index. When you see this, the query needs to be optimized. MySQL needs to take additional steps to find out how to sort the returned rows. It sorts all rows based on the connection type and the row pointers of all rows that store the sort key values and matching criteria, and the index column data is returned from a table that uses only the information in the indexes and does not read the actual action. This occurs when all the request columns on the table are part of the same index and the query needs to be optimized when the using temporary sees this. Here, MySQL needs to create a temporary table to store the results, which usually occurs on order by on different sets of columns, rather than on group by where used uses the WHERE clause to restrict which rows will match the next table or return to the user. Suppose you don't want to return all the rows in the table, and the connection type all or index, this happens, or the query has a problem the interpretation of the different connection types (sorted in order of efficiency) the system table has only one row: the system table. This is a special case of the const connection type. The maximum value of a record in a const table can match this query (the index can be a primary key or a unique index). 
Because only one row, this value is actually a constant, because MySQL read this value first and then treat it as a constant to the eq_ref in the connection, MySQL at query time. From the previous table. The union of each record reads a record from the table, and it uses ref when the query uses all indexes as primary or unique keys. This connection type only occurs when the query uses a key that is not a unique or primary key or is part of those types (for example, the leftmost prefix is used).
Union for each row of the previous table. All records are read from the table.
This type is heavily dependent on how many records are matched by the index-the less the better range this type of connection uses the index to return rows in a range, such as when using > or < to find things. Index This connection type makes a full scan of each record in the preceding table (better than all , because the index is generally less than the table data) All this connection type is fully scanned for each previous record union. This is generally worse, and should try to avoid MySQL often used by hint (original) MySQL often used by hint for frequent use of Oracle's friends may know. Oracle has a wide variety of hint features. There are a number of methods available for optimizing SQL statements.
Same, in MySQL, there are similar hint functions. Some of the most frequently used ones are described below. [b] Forced index Force Index[/b]select * from TABLE1 Forces index (FIELD1) ... The above SQL statements use only the indexes built on FIELD1, not the indexes on the other fields. [b] Ignore index IGNORE index[/b]select * from TABLE1 IGNORE index (FIELD1, FIELD2) ... In the above SQL statement, the indexes on FIELD1 and FIELD2 in the TABLE1 table are not used. [b] Close the query buffer sql_no_cache[/b]select sql_no_cache field1, field2 from TABLE1; there are some SQL statements that need to query the data in real time, or do not use it very often (maybe one or two times a day). This will need to turn the buffer off, regardless of whether the SQL statement has been run, the server will not look in the buffer, each time it will run.
[b] Force query buffer sql_cache[/b]select Sql_calhe * from TABLE1; Suppose Query_cache_type in My.ini is set to 2. This only uses the query buffer after Sql_cache is used.
[b] Priority operation High_priority[/b]high_priority can be used in select and insert operations. Let MySQL know. This operation takes precedence. SELECT high_priority * from TABLE1; [b] hysteresis operation low_priority[/b]low_priority can be used in insert and update operations. Let MySQL know that this operation is lagging behind.
Update low_priority table1 set field1= where field1= ... [b] Delay insertion insert Delayed[/b]insert DELAYED into table1 set field1= ... INSERT DELAYED into, is the client submits the data to Mysql,mysql return OK state to the client.
Instead of inserting data into a table, it is stored in memory waiting to be queued. When MySQL is free. Insert again. Another important advantage is that insertions from many clients are lumped together. and is programmed into a block.
This is much faster than running a lot of separate inserts. The downside is that you can't return the IDs you've actively incremented, and when the system crashes, MySQL hasn't had time to insert the data, and that data will be lost. [B] Force connection order Straight_join[/b]select TABLE1. FIELD1, TABLE2. FIELD2 from TABLE1 straight_join TABLE2 WHERE ... This is known by the SQL statement above. By Straight_join forcing MySQL to connect tables in TABLE1, TABLE2 order.
If you feel that you are more efficient at connecting in your own order than the MySQL recommended sequence, you can determine the connection order by Straight_join. [B] Force the use of a temporary table sql_buffer_result[/b]select Sql_buffer_result * from TABLE1 WHERE ... When we have more data in the result set of our query, we can force the result set to be placed in the temporary table by sql_buffer_result. option. This allows the MySQL table lock to be released very quickly (so that other SQL statements can query the records) and can provide a large recordset for the client for a long time. [b] grouping uses temporary tables Sql_big_result and Sql_small_result[/b]select sql_buffer_result FIELD1, COUNT (*) from TABLE1 GROUP by FIELD1; Typically used for grouping or distinctkeyword, this option notifies MySQL, assuming it is necessary, to place the query results in a temporary table, even in a temporary table. Sql_small_result is almost identical to sql_big_result and very rarely used. Queries are the most frequently used operations in database technology.
The process of query operation is relatively simple, first from the client issued a query SQL statement, the database server receives the client from the SQL statement. Run the SQL statement, and then return the results of the query to the client. Although the process is very easy, different query methods and database settings will have a very good effect on the performance of the query. Therefore, this article discusses the query optimization techniques that are often used in MySQL. The contents of the discussion are as follows: Query buffering is used to improve query speed, MySQL actively optimizes queries, index-based sorting, detection of unreachable queries, and use of various query choices to improve performance. First, query buffering to improve the query speed generally we use SQL statements when querying. The database server runs the SQL statement each time it receives a SQL from the client. But when within a certain interval (for example, within 1 minutes), the exact same SQL statement is received. Run it as well. This ensures the real-time nature of the data, though. But most of the time, the data does not require total real-time. This means that there is a certain delay. Assuming this is the case, running the same SQL in a short period of time is not worth the candle.
Fortunately, MySQL provides us with the ability to query buffers (only the query buffer can be used in MySQL 4.0.1 and above version numbers). We are able to improve query performance to some extent through query buffering. We are able to set the query buffer through the My.ini file in the MySQL installation folder. Settings are also very easy. Just have to set Query_cache_type to 1.
When this property is set, MySQL queries its buffer to see if the same SELECT statement is run and false before running whatever the SELECT statement. And the running result is not expired, then the query results are returned to the client directly.
However, when writing SQL statements, note that the query buffer for MySQL is distinguished by uppercase and lowercase.
For example, the following two SELECT statements: 1. SELECT * from TABLE12.3. The two SQL statements above the SELECT * from TABLE1 are a completely different select for query buffering.
And the query buffer does not actively handle the space itself. So. When writing SQL statements, you should minimize the use of whitespace. Especially at the top and the end of the SQL space (due to. Query buffering does not take the initiative to intercept the leading and trailing spaces.
Although query buffering is not set. This can sometimes be a performance loss, but there are some SQL statements that need to query the data in real time, or are not used frequently (perhaps one or two times a day).
This will require the buffer to be closed.
Of course, this can turn off query buffering by setting the value of Query_cache_type, but this will permanently shut down the query buffer. Provides a way to temporarily turn off query buffering in MySQL 5.0:1. SELECT Sql_no_cache field1, field2 from TABLE1 above SQL statements because of the use of Sql_no_cache. Therefore, whether or not this SQL statement has been run. The server does not look in the buffer and runs it every time.
We can also set the Query_cache_type in My.ini to 2, which only uses the query buffer after using Sql_cache.
1. SELECT Sql_calhe * from TABLE1 second, MySQL self-optimizing indexes on queries is very important to the database. Performance can be improved by indexing at query time. However, sometimes using an index can actually reduce performance.
We can look at the following sales table for example: 1. CREATE TABLE SALES2.3. (4.5. ID INT (Ten) UNSIGNED not NULL auto_increment,6.7. NAME VARCHAR (+) not null,8.9. Price FLOAT is not null,10.11. Sale_count INT not null,12.13. Sale_date DATE not null,14.15. PRIMARY KEY (ID), 16.17. INDEX (NAME), 18.19. INDEX (sale_date) 20.21. ), assuming that the table has millions of data saved. We would like to inquire about the average price of goods with product number 1000 in 2004 and 2005. We are able to write SQL statements such as the following: SELECT AVG (price) from saleswhere ID = sale_date between ' 2004-01-01′and ' 2005-12-31′; Often, almost the same accounts for 50% or more of the records of the sales table. It is somewhat slow to use the index on the Sale_date field to calculate the average. Because the index is assumed to be used, the index must be sorted. When the condition is met for a very long time (such as 50% or many other proportions of the records of the entire table). The speed slows down. This is not as good as scanning the entire table.
therefore MySQL will voluntarily decide whether to use an index to query by itself, depending on the proportion of data that satisfies the condition. For MySQL. The index is not used when the result of the above query is about 30% of the total table record. This ratio is based on the experience of MySQL developers.
However, the actual scale value will vary depending on the database engine being used. Iii. Index-based sequencing one of the weaknesses of MySQL is its sort. Although MySQL can query about 15,000 records in 1 seconds, MySQL can only use one index at a time when querying.
Therefore, assuming that the where condition already occupies the index, the index is not used in the sort, which greatly reduces the speed of the query. We can look at SQL statements such as the following: 1. SELECT * from SALES where NAME = ' name ' ORDER by Sale_date DESC; the index on the NAME field is already used in the WHERE clause of SQL above, so. The index is no longer used when sorting sale_date.
In order to solve the problem. We were able to build a composite index on the sales table: 1. ALTER TABLE SALES DROP index NAME, ADD index (NAME, sale_date) so the speed will be increased when queried using the SELECT statement above. Note, however, that when you use this method, make sure that there are no sort fields in the WHERE clause. In the example above, it is not possible to query with sale_date, otherwise, even though the sorting is fast. However, there is no separate index on the Sale_date field. So the query slows down again. Iv. Detection of unreachable queries when running SQL statements, you will inevitably encounter some of the conditions that must be false.
The condition of the so-called false is that no matter how the data in the table changes, this condition is false.
such as where value < and value > 200. We can never find a number that is less than 100 and more than 200. Assuming this query condition is met, it is superfluous to run the SQL statement. Fortunately, MySQL was able to detect this situation on its own initiative. If we can look at SQL statements such as the following: 1. SELECT * from SALES WHERE name = "Name1" and name = "Name2" above the query statement to find records with name equal to name1 and equal to name2.
Obviously, this is an unreachable query, where the condition must be false. MySQL before running the SQL statement. The Where condition is not an unreachable query is first analyzed. The assumption is that the SQL statement will no longer run. In order to verify this. Let's start by using explain for example sql: 1. EXPLAIN SELECT * from SALES WHERE NAME = "name1" The query above is a normal query. We can see that the table item in the run information data returned using explain is sales.
This indicates that MySQL has operated on sales. Then look at the following statement: 1. EXPLAIN SELECT * from SALES WHERE NAME = "name1" and name = "Name2" We can see that the table entry is empty. This means that MySQL does not operate on the sales table. V. Using a variety of query choices to improve performance SELECT statements in addition to normal use, MySQL also provides us with a lot of options to enhance query performance. As described above, the Sql_no_cache and Sql_cache for controlling query buffering are among the two options. In this part. I'll cover a few of the frequently used query options.
1. Straight_join: Force Connection Order when we connect two or more tables together for querying, we don't care which table the MySQL first joins, and then which table.
And all this is done by MySQL internally through a series of calculations, evaluations. Finally, a connection sequence is determined. For example, in the following SQL statement, TABLE1 and TABLE2 are not necessarily who connected who: 1. SELECT TABLE1. FIELD1, TABLE2. FIELD2 from TABLE1, TABLE2 WHERE ... Assuming that developers need to manually intervene in the order of connections, they have to use straight_joinkeyword, such as the following SQL statement: 1. SELECT TABLE1. FIELD1, TABLE2. FIELD2 from TABLE1 straight_join TABLE2 WHERE ... The SQL statement above shows that by Straight_join forcing MySQL to join the table in TABLE1, TABLE2 order. If you feel that you are more efficient at connecting in your own order than the MySQL recommended sequence, you can determine the connection order by Straight_join.
2. Intervention index use. Improved performance the use of indexes has been mentioned above. Normally, when querying, MySQL will decide for itself whether to use an index or not, which index to use. But in some special cases, we want MySQL to use only one or several indexes, or not to use an index. This requires some query options for MySQL's control index. Limiting the scope of using indexes sometimes we build very many indexes in the data table. When MySQL chooses the index, these indexes are within the scope of consideration.
But sometimes we want MySQL to consider just a few indexes. Instead of all indexes. This requires using the use index to set the query statement. 1. SELECT * from TABLE1 use INDEX (FIELD1, FIELD2) ... From the above SQL statements can be seen. Regardless of how many indexes have been established in TABLE1, MySQL selects the index. Consider only the indexes established on FIELD1 and FIELD2.
Restricting the scope of not using an index assumes that we have a very wide range of indexes to consider, and that indexes that are not being used are very few. Ability to use ignore index for reverse selection. In the example above, the index is selected for consideration, while using ignore index is an index that is not considered. 1. SELECT * from TABLE1 IGNORE INDEX (FIELD1, FIELD2) ... In the SQL statement above. Only indexes on FIELD1 and FIELD2 in the TABLE1 table are not used. Forcing the use of one of the two examples on an index gives MySQL a choice. This means that MySQL does not necessarily use these indexes. And sometimes we want MySQL to have to use an index (because MySQL can only use an index when querying, so it can only force MySQL to use an index).
This will require the use of force index to complete this function. 1. SELECT * from TABLE1 Force INDEX (FIELD1) ... The above SQL statements use only the indexes built on FIELD1, not the indexes on the other fields. 3. Using temporal tables to provide query performance when the data in the result set of our query is relatively long, we can force the result set to be placed in the temporary table through the Sql_buffer_result option, so that the MySQL table lock can be released very quickly ( This allows other SQL statements to be queried for these records, and can provide a large recordset for the client for extended periods of time. 1. SELECT Sql_buffer_result * from TABLE1 WHERE ... and Sql_buffer_result. Similar to Sql_big_result, this option is typically used for grouping or Distinctkeyword, which notifies MySQL, assuming it is necessary, to place the query results in a temporary table, even in a temporary table.
1. SELECT Sql_buffer_result FIELD1, COUNT (*) from TABLE1 GROUP by FIELD1

Common MySQL optimization tips for daily work