How does MySQL performance optimization find problematic SQL? monitoring efficient SQL with MySQL slow log the following is the SQL statement associated with the slow query: show variables like ' show_query_log '//Use this to see if it turns on the slow log show variables like ' %log% ';//For all parameters of SQL set global show_query_log_file = '/home/mysql/sql_log/mysql-slow.log '//refers to the storage location set of the slow query file. Global log_queries_not_using_indexes = on; Whether we record SQL records that do not use indexes to the slow query log, typically the optimization of the database is primarily about optimizing the table's indexes and how queries are used, so that the SQL is recorded for later optimization set global Long_query_time =1;// More than a few seconds after the record in the slow query log, more than 1 seconds to check 0.01 seconds general
The analysis tool for the slow log mysqldumpslowpt-query-digest uses explain to query the SQL execution plan type: from best to worst const: Represents a constant lookup, typically a primary key or a unique index to find Eq_reg: Indicates that a range lookup is typically a primary key or a unique index to find ref: In a query that is commonly used for joins, a table that is based on a table's lookup range: The scope of the index looks up index: Scan all: Table Scan Note: Key_len: The index is as small as possible, Because MySQL reads are all in the page, and if the index of the page in the larger, then his query efficiency is higher than a few examples above this statement can be seen as a full table scan, so that the amount of data is particularly large, and the query frequency is particularly high, the Sqlio performance is particularly high, Will slow down the overall server IO efficiency, increase the server instability factor, usually in this case you can add the index on the Payment_date field, and then look at the execution plan select tables optimized away meaning is not the actual query, Through the index can know the results of SQL execution, because the index is ordered, by the statistics of the last value of the data look like, that is, do not need the table operation can be directly known, so that the efficiency of SQL execution and reduce the IO operation, in fact, this index can be called the overlay index, It means that you can query the result of the statement by indexing the information. I was thinking of 2006 and 2007 respectively of the film cooked, it is now together, so it is wrong, the correct way to say count, usually we use COUNT (*) or count (field name) of the two ways, Which of these two ways is good, for example, COUNT (*) is a null-containing number of rows count (ID) does not include the use of the Count attribute if the release year is 2006 to give it a count, if not 2006 to return a null value, And this null value is not counted in count,
MySQL performance optimization part of the summary
