Mysql slow query and slow query log analysis

As we all know, in the case of large traffic, can add node or change the schema can effectively alleviate the database pressure, but all the origin, all from a single MySQL start. The following summarizes some of the experiences that have been used or studied, and some optimizations are made to MySQL, starting with the configuration and adjusting the index.
The first step should be to troubleshoot the problem and find the bottleneck, so start with the log
turn on slow query log
Mysql>show variables like "%slow%"; To view the slow query configuration, no is added in My.cnf, as follows

Log-slow-queries =/data/mysqldata/slowquery. Log    #日志目录 1                          #记录下查询时间超过1秒log-queries-not-using-indexes     #表示记录下没有使用索引的查询

Analysis Log –mysqldumpslow
Analysis log, available with MySQL Mysqldumpslow, easy to use, parameters can be help view

#-S: Sort the way. C, T, L, R indicates the number of records, the time, the query time, the number of records returned to sort;# AC, at, AL, AR indicates the corresponding flashback;#-T: Returns the data for the previous number of bars;#-G: Contains nothing, case insensitive;Mysqldumpslow-sR-TTen  /Slowquery.Log     #slow记录最多的10个语句Mysqldumpslow-sT-TTen -G"left join"  /Slowquery.Log     #按照时间排序前10中含有 "left join".

Recommended Analysis Log Tool –mysqlsla

wgethttp//Hackmysql.com/Scripts/Mysqlsla-2.03.tar.gzTarZVXF mysqlsla-2.03.tar.gzCDmysqlsla-2.03Perlmakefile.pl Make Make InstallMysqlsla/Data/Mysqldata/Slow.log# Mysqlsla will automatically determine the log type, in order to facilitate the establishment of a configuration file "~/.mysqlsla"# Write in the file: top=100, this will print out the first 100 results. 

"description" Queries totals: Total queries unique: Number of SQL sorted by: Output report The most significant slow SQL statistics, including average execution time, waiting lock time, total number of result rows, total number of rows scanned. Count, the number of executions of SQL and the percentage of total slow log count. Time, execution times, including total time, average time, minimum, maximum time, and percentage of total slow SQL time. 95% of time, removing the fastest and slowest SQL, coverage accounted for 95% of SQL execution time. Lock time, waiting for the lock. 95% of lock, 95% slow SQL waits for lock time. Rows sent, resulting row statistics, including average, minimum, maximum number. Rows examined, the number of lines scanned. database, which databases users, which user, IP, accounts for all users execute SQL percent query abstract, abstract SQL statement Query sample, SQL statement

Mysql slow query and slow query log analysis