众所周知，大访问量的情况下，可添加节点或改变架构可有效的缓解数据库压力，不过一切的原点，都是从单台mysql开始的。下面总结一些使用过或者研究过的经验，从配置以及调节索引的方面入手，对mysql进行一些优化。 第一步应该做的就是排查问题，找出瓶颈，所以，先从日志入手 开启慢查询日志 mysql>show variables like “%slow%”; 查看慢查询配置，没有则在my.cnf中添加，如下
log-slow-queries = /data/mysqldata/slowquery.log #日志目录 long_query_time = 1 #记录下查询时间查过1秒 log-queries-not-using-indexes #表示记录下没有使用索引的查询分析日志 – mysqldumpslow 分析日志，可用mysql提供的mysqldumpslow，使用很简单，参数可–help查看
# -s：排序方式。c , t , l , r 表示记录次数、时间、查询时间的多少、返回的记录数排序； # ac , at , al , ar 表示相应的倒叙； # -t：返回前面多少条的数据； # -g：包含什么，大小写不敏感的； mysqldumpslow - s r - t 10 / slowquery. log #slow记录最多的10个语句 mysqldumpslow - s t - t 10 - g "left join" / slowquery. log #按照时间排序前10中含有"left join"的推荐用分析日志工具 – mysqlsla
wget http:// hackmysql.com/ scripts/ mysqlsla-2.03.tar.gz tar zvxf mysqlsla-2.03.tar.gz cd mysqlsla-2.03 perl Makefile.PL make make install mysqlsla / data/ mysqldata/ slow.log # mysqlsla会自动判断日志类型，为了方便可以建立一个配置文件“~/.mysqlsla” # 在文件里写上：top=100，这样会打印出前100条结果。【说明】 queries total: 总查询次数 unique:去重后的sql数量 sorted by : 输出报表的内容排序 最重大的慢sql统计信息, 包括 平均执行时间, 等待锁时间, 结果行的总数, 扫描的行总数. Count, sql的执行次数及占总的slow log数量的百分比. Time, 执行时间, 包括总时间, 平均时间, 最小, 最大时间, 时间占到总慢sql时间的百分比. 95% of Time, 去除最快和最慢的sql, 覆盖率占95%的sql的执行时间. Lock Time, 等待锁的时间. 95% of Lock , 95%的慢sql等待锁时间. Rows sent, 结果行统计数量, 包括平均, 最小, 最大数量. Rows examined, 扫描的行数量. Database, 属于哪个数据库 Users, 哪个用户,IP, 占到所有用户执行的sql百分比 Query abstract, 抽象后的sql语句 Query sample, sql语句
Profiling 分析具体的sql语句消耗 此外，MySQL5.0.37版本以上支持了Profiling – 官方手册 。 此工具可用来查询 SQL 会执行多少时间， 并看出 CPU/Memory 使用量，执行过程中 System lock和Table lock 花多少时间等等。从启动profile之后的所有查询包括错误的语句都会记录。关闭会话或者set profiling=0 就关闭。这对帮助排查和分析sql语句，还是比较有用的。
mysql> set profiling= 1 ; #开启 mysql> select * from li limit 2 ; +--------+----------+ | id | name | +--------+----------+ | 1458 | hello | | 1457 | world | +--------+----------+ mysql> show profiles; +--------------+----------------+------------------------------+ | Query_ID | Duration | Query | +--------------+----------------+------------------------------+ | 1 | 0.00013200 | SELECT DATABASE ( ) | | 2 | 0.00044100 | select * from li limit 2 | +--------------+----------------+------------------------------+ mysql> show profile for query 2 ; +---------------------------+--------------+ | Status | Duration | +---------------------------+--------------+ | starting | 0.000058 | | Opening tables | 0.000011 | | System lock | 0.000007 | | Table lock | 0.000014 | | init | 0.000027 | | optimizing | 0.000003 | | statistics | 0.000010 | | preparing | 0.000010 | | executing | 0.000007 | | Sending data | 0.000068 | | end | 0.000003 | | query end | 0.000002 | | freeing items | 0.000208 | | logging slow query | 0.000002 | | logging slow query | 0.000009 | | cleaning up | 0.000002 | +---------------------------+--------------+ #此外还有2个语句可以查看更多信息。 show profile cpu for query 2 ; show profile IPC for query 2 ;总结 慢查询分析是个长期工作，即便系统长时间没有引入新的SQL查询，也可能由于不断更新数据使得数据分布特征发生变化，从而导致索引失效，进而产生慢查询。 另外可以在mysql环境下用命令：show status , show processlist查看mysql运行的状态和正在执行的操作。当然为了智能化，也可以写一些脚本来监测。比如，下面的kill掉sleep的查询， 避免堵死队列
<?php define ( 'MAX_SLEEP_TIME' , 120 ) ; $connect = mysql_connect ( 'localhost' , 'root' , 'root' ) ; $result = mysql_query ( "SHOW PROCESSLIST" , $connect ) ; while ( $proc = mysql_fetch_assoc ( $result ) ) { if ( $proc [ "Command" ] == "Sleep" && $proc [ "Time" ] > MAX_SLEEP_TIME) { @ mysql_query ( "KILL " . $proc [ "Id" ] , $connect ) ; } } mysql_close ( $connect ) ; ?>