Mysql performance problem locating

Mysql performance problem locating
Using mysql as the basic database application may encounter some database performance problems. We can locate the problem through some methods. The following describes four methods to locate performance problems. 1. Enable slow query log: record the SQL statement with query time greater than long_query_time. The default value of long_query_time is 2 s. show variables like '% slow %' to obtain the information shown in the figure, check whether the slow query log is enabled and the directory where the slow query log files are stored. How to enable slow log query: 1. vi/etc/my. cnf (this is the default directory for reading configuration files from mysql. put the cnf file below) add slow_query_log = ONlong_query_time = 1 under [mysqld] (if the SQL statement execution time exceeds this parameter value, it will be printed in the slow query log ), SQL statements that are executed for more than 2 seconds by default are printed in slow query logs. You have modified the configuration items in my. cnf and need to restart the database. 2. If you do not restart the database, run set global slow_query_log = ON to enable slow query logs. After the slow query log is enabled, track the slow query SQL statements in the slow query log file, analyze the SQL statements in detail, or add the correct index to display unexpected performance results. Ii. Analyze slow query SQL: 1. Explain: Add explain before the selcet statement executed by the execution plan to show you how mysql executes the statement. Note the content displayed in the type, key, rows, and extra columns. Among them, Type = all indicates that full table scan is used. In case of a large amount of data, full table scan is very performance-consuming. This requires special attention; Type = index, indicates that index scanning is used and only the index tree is traversed. Type = range indicates that index range scanning is used, which is common in queries such as between,>, and <. Type = ref, non-unique index scan, returns a unique index scan that matches a single value for all rows Type = eq_ref. For each index key, there is only one record in the table that matches the Type = const/system Read Constant. Only one record can match at most, because it is a constant, in fact, you only need to read Type = null once. You do not need to scan the table to change the access Type from top to bottom to the best. Key indicates the index used in the select statement. If it is null, the index is not used. In terms of query efficiency, using an index is faster than not using an index. However, not all indexes need to be added, and there are also deficiencies in indexes. I will not explain them here. Rows indicates the number of rows to be scanned when the SQL statement is executed. There is no absolute value for reference. The smaller the value, the better. If the number of rows in a database with 1 million data volume is 0.7 million, through this, we can judge that the query performance of SQL is very poor. If the number of rows in a database with 1 million data records is 10 thousand, it is acceptable from my personal perspective. Extra has some important additional information, focusing on the Keyword: Using filesort: When the Query contains the order by operation and the index cannot be used to complete the sorting operation, mySQL Query Optimizer has to select the corresponding Sorting Algorithm for implementation. Using temporary: when temporary tables must be used in some operations, the Using temporary will appear in the Extra information, it is usually used in group by and order by operations. When the execution plan Extra has Using filesort and Using temporary, you can consider whether to optimize and adjust the SQL statements, and then adjust my. parameters related to sorting or temporary tables in cnf, such as sort_buffer_size, tmp_table_size.2, and show full processlist, the Unit is second (s). The State indicates the state of the current thread. Info indicates the operation being performed. If the time value is found to be large, the State remains in the same state, then we can get the time-consuming operation from Info and analyze it in detail. Observe the status of the lock keyword in the State. This command intuitively shows the SQL statement being executed and its current status, and the operation is convenient. 3. show profile locates the resource usage of SQL in the database (Note: show profiles and show profile). Show profiles is displayed in the current session, profiling_history_size SQL Execution time and query_id. The default value is 15. The maximum value is 100, and cannot be set to 0. Show variables like '% profiling_history_size %' show variables like '% profiling' or select @ profiling check whether profiling is enabled set profiling = 1 enable profiling to execute an SQL statement and then execute show profiles, the latest SQL statement is displayed. Find the SQL statement you just executed. The query_id is 120 and the execution time is 0.00079725s. If you want to check the time consumed by the SQL statement in each stage, the time consumed by each phase of show profile for query 120 is clear. Show profile is written as Show profile TYPE for query n, n is query_id, TYPE can be written, but the value of TYPE can be ALL, block io, context switches, CPU, IPC, MEMORY, page faults, SOURCE, and SWAPS. In the above example, show profile cpu, if memory for query 120 does not contain the for query n statement, a statement is executed before the show profile statement is executed. 4. Run Mysqladmin to query the status of the entire database in the bin directory of mysql. /mysqladmin-u username-p password proc stat add proc here just like show full processlist function stat shows the current database status threads indicates the current number of threads, Opens currently Opens the number of tables, queries per second. The better the database performance, the higher the value.