MySQL优化思路

1、慢查询 （用日志记录出现出问题的sql）
2、Explain （显示sql使用索引，表连接情况，尅呀帮助选择更好的索引和写出更优化的查询语句）
3、Profile（查询SQL会执行多少时间, 并看出block io,CPU,Memory,swaps,context switches,source使用量, 执行过程中 Systemlock, Table lock 花多少时间等等）

慢查询

 开启

Linux:

在mysql配置文件my.cnf中增加

log-slow-queries=/var/lib/mysql/slowquery.log (日志文件位置，可以为空，系统会给一个缺省的文件host_name-slow.log)
long_query_time=2 (记录超过的时间，默认为10s)
log-queries-not-using-indexes = on (记录没有使用索引的query,可以根据情况决定是否开启)

Windows:

在my.ini的[mysqld]添加如下语句：

log-slow-queries =E:\mysql\log\mysqlslowquery.log
long_query_time = 2

 查看

使用mysqldumpslow查看
mysqldumpslow -s c -t 10 host-slow.log -- 访问次数最多的20个sql语句
mysqldumpslow -s r -t 10 host-slow.log -- 返回记录集最多的20个sql
mysqldumpslow -t 10 -s t -g "left join" host-slow.log -- 按照时间返回前10条里面含有左连接的sql语句

explain

看看官网解释：

Name: 'EXPLAIN'Description:Syntax:{EXPLAIN | DESCRIBE | DESC}    tbl_name [col_name | wild]{EXPLAIN | DESCRIBE | DESC}    [explain_type]    {explainable_stmt | FOR CONNECTION connection_id}explain_type: {    EXTENDED  | PARTITIONS  | FORMAT = format_name}format_name: {    TRADITIONAL  | JSON}explainable_stmt: {    SELECT statement  | DELETE statement  | INSERT statement  | REPLACE statement  | UPDATE statement}The DESCRIBE and EXPLAIN statements are synonyms. In practice, theDESCRIBE keyword is more often used to obtain information about tablestructure, whereas EXPLAIN is used to obtain a query execution plan(that is, an explanation of how MySQL would execute a query). Thefollowing discussion uses the DESCRIBE and EXPLAIN keywords inaccordance with those uses, but the MySQL parser treats them ascompletely synonymous.

查看执行情况直接explain 语句即可

profiling

直接上实例

mysql> select @@PROFILING;+-------------+| @@PROFILING |+-------------+|           0 |+-------------+1 row in set, 1 warning (0.00 sec)mysql> set profiling;ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1mysql> set profiling = 1;Query OK, 0 rows affected, 1 warning (0.00 sec)mysql> select 1;+---+| 1 |+---+| 1 |+---+1 row in set (0.00 sec)mysql> show profile ;+----------------------+----------+| Status               | Duration |+----------------------+----------+| starting             | 0.000056 || checking permissions | 0.000023 || Opening tables       | 0.000020 || init                 | 0.000026 || optimizing           | 0.000021 || executing            | 0.000026 || end                  | 0.000019 || query end            | 0.000020 || closing tables       | 0.000019 || freeing items        | 0.000032 || cleaning up          | 0.000030 |+----------------------+----------+11 rows in set, 1 warning (0.00 sec)mysql> show profiles ;+----------+------------+----------+| Query_ID | Duration   | Query    |+----------+------------+----------+|        1 | 0.00029200 | select 1 |+----------+------------+----------+1 row in set, 1 warning (0.00 sec)mysql> select 2;+---+| 2 |+---+| 2 |+---+1 row in set (0.00 sec)mysql> show profiles ;+----------+------------+----------+| Query_ID | Duration   | Query    |+----------+------------+----------+|        1 | 0.00029200 | select 1 ||        2 | 0.00032100 | select 2 |+----------+------------+----------+2 rows in set, 1 warning (0.00 sec)mysql> show profile for query 2;+----------------------+----------+| Status               | Duration |+----------------------+----------+| starting             | 0.000077 || checking permissions | 0.000023 || Opening tables       | 0.000022 || init                 | 0.000028 || optimizing           | 0.000022 || executing            | 0.000027 || end                  | 0.000019 || query end            | 0.000021 || closing tables       | 0.000019 || freeing items        | 0.000033 || cleaning up          | 0.000030 |+----------------------+----------+11 rows in set, 1 warning (0.00 sec)mysql> show profile block io,CPU,Memory for query 2;+----------------------+----------+----------+------------+--------------+---------------+| Status               | Duration | CPU_user | CPU_system | Block_ops_in | Block_ops_out |+----------------------+----------+----------+------------+--------------+---------------+| starting             | 0.000077 | 0.000000 |   0.000000 |            0 |             0 || checking permissions | 0.000023 | 0.000000 |   0.000000 |            0 |             0 || Opening tables       | 0.000022 | 0.000000 |   0.000000 |            0 |             0 || init                 | 0.000028 | 0.000000 |   0.000000 |            0 |             0 || optimizing           | 0.000022 | 0.000000 |   0.000000 |            0 |             0 || executing            | 0.000027 | 0.000000 |   0.000000 |            0 |             0 || end                  | 0.000019 | 0.000000 |   0.000000 |            0 |             0 || query end            | 0.000021 | 0.000000 |   0.000000 |            0 |             0 || closing tables       | 0.000019 | 0.000000 |   0.000000 |            0 |             0 || freeing items        | 0.000033 | 0.000000 |   0.000000 |            0 |             0 || cleaning up          | 0.000030 | 0.000000 |   0.000000 |            0 |             0 |+----------------------+----------+----------+------------+--------------+---------------+11 rows in set, 1 warning (0.00 sec)