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, the
DESCRIBE keyword is more often used to obtain information about table
structure, whereas EXPLAIN is used to obtain a query execution plan
(that is, an explanation of how MySQL would execute a query). The
following discussion uses the DESCRIBE and EXPLAIN keywords in
accordance with those uses, but the MySQL parser treats them as
completely 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 1
mysql> 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)