1) Scene catch the slow query statement show full processlist;
2) configuration parameters:
Slow_query_log_file = On slow query open switch
long_query_time = 2 record SQL statement greater than 2 seconds
Log_queries_not_using_indexes = on does not use an indexed SQL statement
Slow_query_log_file =/application/mysql-5.6.34/data/db01-slow.log Slow log file
Min_examined_row_limit = 800 records An SQL statement with a result set greater than 800 rows
3) poll by day: Slow_log.log
4) Slow query log analysis tool--mysqlsla or pt-query-digest (recommended)
5) every night 0 o'clock timing analysis slow query sent to core development DBA analysis and Senior operations CTO's mailbox
DBA analysis gives recommendations for optimization-core development validation changes--DBA on-line Operations processing
6) regularly use Pt-duplicate-key-checker to check and delete + +
Use the Pt-index-usage tool regularly to check and remove
7) using explain and set profile to optimize SQL statements
How does the SQL statement learned by MySQL database be optimized? Database development