大体上MySQL一般的优化思路如下：

1.首先需要使用慢查询功能，去获取所有查询时间比较长的SQL语句。
2.其次使用explain命令去查看有问题的SQL的执行计划。
3.最后使用show profile[s]查看有问题的SQL硬件性能使用情况。
4.优化改造SQL语句。

慢查询日志

慢查询日志介绍

数据库查询慢是影响项目性能的一大因素，对于数据库，我们除了要优化SQL，更重要的是得先找到哪些是需要优化的SQL语句。

MySQL数据库有一个"慢查询日志"功能用来记录查询时间超过某个设定值的SQL，这将极大程度帮助我们快速定位到查询时间较长的SQL，一遍对症下药。

至于查询时间的多少才算慢，每个项目，业务都有不同的要求。比如说传统企业的软件允许查询时间高于某个值，但是把这个目标值放在互联网项目或者访问量大的网站上，估计就是一个bug，甚至可能升级为一个功能性的缺陷。

MySQL的慢查询日志功能，默认是关闭的，需要手动开启。

开启慢查询功能

首先查看一下是否开启了慢查询日志功能，执行如下命令进行查看：

show variables like '%slow_query%';

参数说明：

slow_query_log：是否开启慢查询日志功能，ON为开启，OFF为关闭。
slow_query_log_file：MySQL数据库慢查询日志存储路径，可以不设置该参数，系统会默认给一个文件：host_name-slow.log

接着查看慢查询日志默认的时间阈值（即超过多长时间才算是慢查询SQL，该SQL执行记录就会被记录在慢查询日志文件中，即上面的/var/lib/mysql/hecs-100161-slow.log中），通过执行如下命令查看慢查询日志阈值：

show variables like 'long_query_time%';

参数说明：

long_query_time：慢查询阈值，当查询时间多于设定的阈值时，记录日志，单位为秒。

临时开启慢查询功能

在MySQL中执行如下命令，但是如果重启MySQL的话就会失效。

set global slow_query_log = ON;--开启慢查询日志功能
set global long_query_time = 1;--设置慢查询时间阈值为1秒

永久开启慢查询功能

修改/etc/my.cnf配置文件如下：，重启MySQL，这种就会永久生效。

[mysqld]
#开启慢查询日志功能
slow_query_log = ON
#设置慢查询日志文件存储路径
slow_query_log_file = /var/lib/mysql/slow.log
#设置慢查询时间阈值
long_query_time =1

演示慢查询日志功能

按照上面的步骤开启了慢查询功能，并且设置了慢查询时间阈值为1秒。接着执行一条SQL语句，保证该SQL语句的执行时间超过上面设置的慢查询时间阈值1秒，如下：

select sleep(3); --睡眠3秒

查看/var/lib/mysql/slow.log文件信息如下：

格式说明如下：

第一行：记录了SQL查询执行的具体时间，年月日时分秒
第二行：记录了执行SQL查询的连接信息，用户和连接IP
第三行：记录了一些我们比较有用的信息，如下：
- Query_time：这条SQL执行的时间，越长则约慢。
- Lock_time：在MySQL服务器阶段（不是在存储引擎阶段）等待表锁的时间。
- Rows_sent：查询返回的行数
- Rows_examined：查询检查的行数，越长就当然越费时间
第四行：设置时间戳，没有实际意义，只是和第一行对应的执行时间。
第五行：记录执行的SQL信息。

分析慢查询日志

可以使用MySQL自带的mysqldumpslow工具分析慢查询日志，举例如下：查看带有select关键字慢查询SQL语句信息

mysqldumpslow -s t -t 10 -g "select" /var/lib/mysql/slow.log

参数说明如下：

-s：表示按照何种方式排序
- c：访问计数
- l：锁定时间
- r：返回记录
- t：查询时间
- al：平均锁定时间
- ar：平均返回记录数
- at：平均查询时间
-t：是top n的意思，即为返回前面多少条的数据
-g：后边可以写一个正则匹配模式，大小写不敏感。

查看执行计划

介绍

MySQL提供了一个explain命令，它可以对select语句进行分析，并输出select执行的详细信息，以供开发人员针对性进行优化SQL
使用explain这个命令来查看SQL语句的执行计划，查看该SQL语句有没有使用上索引，有没有做全表扫描，这都可以通过explain命令来查看。
可以通过explain命令深入了解MySQL的基于开销的优化器，还可以获得很多可能被优化器考虑到的访问策略的细节，以及当运行SQL语句时哪种策略预计会被优化器采用。

explain命令用法十分简单，只需要在select语句前加上explain就可以了，例如：

参数说明

id

id：select查询的序列号，包括一组数字，表示查询中执行select子句或者操作表的顺序

id取值的三种情况：

1.id相同：执行顺序由上往下

2.id不相同：如果是子查询，id的序号会递增，id值越大优先级越高，越先被执行

3.id相同不同：同时存在，id如果相同，可以认为是一组，从上往下顺序执行，id值越大，优先级越高，越先执行，table中的derived2表示表s1是由id为2的，即t3衍生而来的表。所以t3的select_type的值为DERIVED

select_type

查询的类型，主要用于区别普通查询、联合查询、子查询等复杂查询

SIMPLE：简单的select查询，查询中不包含子查询或者UNION
PRIMARY：查询中若包含任何复杂的子部分，最外层查询则被标记为PRIMARY
SUBQUERY：在SELECT或者WHERE列表中包含了子查询
DERIVED：在FROM列表中包含的子查询被标记为DERIVED（衍生）MySQL会递归执行这些子查询，把结果放在临时表里
UNION：若第二个SELECT出现在UNION之后，则被标记为UNION；若UNION包含在FROM子句的子查询中，外层SELECT将被标记为：DERIVED
UNION RESULT：从UNION表获取结果的SELECT

UNION 和 UNION RESULT举例

explain
    -> select * from tbl_emp e left join tbl_dept d on e.deptId = d.id
    -> union
    -> select * from tbl_emp e right join tbl_dept d on e.deptId = d.id;
+----+--------------+------------+------+---------------+------------+---------+-----------+------+----------------------------------------------------+
| id | select_type  | table      | type | possible_keys | key        | key_len | ref       | rows | Extra                                              |
+----+--------------+------------+------+---------------+------------+---------+-----------+------+----------------------------------------------------+
|  1 | PRIMARY      | e          | ALL  | NULL          | NULL       | NULL    | NULL      |    8 | NULL                                               |
|  1 | PRIMARY      | d          | ALL  | PRIMARY       | NULL       | NULL    | NULL      |    5 | Using where; Using join buffer (Block Nested Loop) |
|  2 | UNION        | d          | ALL  | NULL          | NULL       | NULL    | NULL      |    5 | NULL                                               |
|  2 | UNION        | e          | ref  | fk_dept_Id    | fk_dept_Id | 5       | db01.d.id |    1 | NULL                                               |
| NULL | UNION RESULT | <union1,2> | ALL  | NULL          | NULL       | NULL    | NULL      | NULL | Using temporary                                    |
+----+--------------+------------+------+---------------+------------+---------+-----------+------+----------------------------------------------------+
5 rows in set (0.00 sec)

table

显示这一行的数据是关于哪张表的

type

访问类型排列，显示查询使用了何种类型

type显示的是访问类型，是较为重要的一个指标，结果值从最好到最坏依次是： system>const>eq_ref>ref>fultext>ref_or_null>index_merge>unique_subquery>index_subquery>range>index>ALL

挑重要的来说：system>const>eq_ref>ref>range>index>ALL，一般来说，得保证查询至少达到range级别，最好能达到ref。

从最好到最差依次是：system>const>eq_ref>ref>range>index>ALL

1.system：表只有一行记录（等于系统表），这是const类型的特例，平时不会出现，这个也可以忽略不计

2.const：表示通过索引一次就找到了，const用于比较primary key或者unique索引。因为只匹配一行数据，所以很快。如将主键置于where列表中，MySQL就能将该查询转换为一个常量

3.eq_ref：唯一性索引，对于每个索引键，表中只有一条记录与之匹配，常见于主键或唯一索引扫描

4.ref：非唯一索引扫描，返回匹配某个单独值的所有行。本质上也是一种索引访问，它返回所有匹配某个单独值的行，然而，它可能会找到多个符合条件的行，所以他应该属于查找和扫描的混合体

5.range：只检索给定范围的行，使用一个索引来选择行。key列显示使用了哪个索引一般就是在你的where语句中出现了between、<、>、in等的查询，这种范围扫描索引扫描比全表扫描要好，因为他只需要开始索引的某一点，而结束于另一点，不用扫描全部索引

6.index：Full Index Scan，index与ALL区别为index类型只遍历索引树。这通常比ALL快，因为索引文件通常比数据文件小。（也就是说虽然all和index都是读全表，但index是从索引中读取的，而all是从硬盘数据库文件中读的）

7.all：FullTable Scan，将遍历全表以找到匹配的行（全表扫描）

ps：一般来说，type得保证查询只是达到range级别，最好达到ref

possible_keys

显示可能应用在这张表中的索引，一个或多个，若查询涉及的字段上存在索引，则该索引将被列出，但不一定被查询实际使用

key

实际使用的索引，如果为null，则没有使用索引，若查询中使用了覆盖索引，则该索引仅出现在key列表中

覆盖索引：比如对于user表在name，age列建立了一个组合索引index_name_age，查询语句：select name, age where name = xxx and age = xxx就用到了覆盖索引，即select中查询的字段是索引的所有字段，where条件中使用的条件也都是索引的列

key_len

表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度。在不损失精确性的情况下，长度越短越好
key_len显示的值为索引最大可能长度，并非实际使用长度，即key_len是根据表定义计算而得，不是通过表内检索出的

ref

显示索引哪一列被使用了，哪些列或常量被用于查找索引列上的值，如果是使用的常数等值查询，这里会显示const，如果是连接查询，被驱动表的执行计划这里会显示驱动表的关联索引字段。
如果可能的话，ref最好是一个常数const。

上述演示案例中，由key_len可知t1表的索引idx_col1_col2被充分使用，t1表的col1匹配t2表的col1，t1表的col2匹配了一个常量，即’ac’

rows

根据表统计信息及索引选用情况，大致估算出找到所需的记录所需要读取的行数

Extra

包含不适合在其他列中显示但十分重要的额外信息

1.Using filesort（文件排序）：排序时无法使用索引时，就会出现这个，常见于order by和group by语句中，- 说明mysql会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取，出现Using filesort不好（九死一生），需要尽快优化SQL。

上述示例中第一个查询只使用了 col1 和 col3，原有索引派不上用场，所以进行了外部文件排序

上述示例中第二个查询使用了 col1、col2 和 col3，原有索引派上用场，无需进行文件排序

2.Using temporary（创建临时表）：使用了临时表保存中间结果，MySQL在对查询结果排序时使用临时表。常见于排序 order by 和分组查询 group by，出现 Using temporary 超级不好（十死无生），需要立即优化 SQL。

上述示例中第一个查询只使用了 col1，原有索引派不上用场，所以创建了临时表进行分组

上述示例中第二个查询使用了 col1、col2，原有索引派上用场，无需创建临时表

3.Using index（覆盖索引）：表示相应的select操作中使用了覆盖索引（Coveing Index），避免访问了表的数据行，即不需要回表，效率不错！如果同时出现using where，表明索引被用来执行索引键值的查找，如果没有同时出现using where，表明索引用来读取数据而非执行查找动作。

回表：我们直到覆盖索引存储的只是与主键的映射关系，聚集索引中才又主键跟记录的映射关系，一个查询如果使用到了覆盖索引，只需要查询覆盖索引即可，因为覆盖索引中已经包含了查询列的值（即覆盖索引的列），不需要再找聚集索引

4.Using where：表明存储引擎返回的记录并不是所有的都满足查询条件，需要在server层进行过滤。

explain案例

第一行（执行顺序4）：id列为1，表示是union里的第一个select，select_type列的primary表示该查询为外层查询，table列被标记为derived3，表示查询结果来自一个衍生表，其中derived3中3代表该查询衍生自第三个select查询，即id为3的select。【select d1.name ...】
第二行（执行顺序2）：id为3，是整个查询中第三个select的一部分。因查询包含在from中，所以为derived。【select id, name from t1 where other_column= ' '】
第三行（执行顺序3）：select列表中的子查询select_type为subquery，为整个查询中的第二个select。【select id from t3】
第四行（执行顺序1）：select_type为union，说明第四个select是union里的第二个select，最先执行【select name, id from t2】
第五行（执行顺序5）：代表从union的临时表中读取行的阶段，table列的union1, 4表示用第一个和第四个select的结果进行union操作。【两个结果进行uinion操作】

profile分析语句

介绍

Query Profiler是MySQL自带的一种query诊断分析工具，通过它可以分析出一条SQL语句的硬件性能瓶颈在什么地方。

通常我们是使用explain，以及slow query log都无法做到精确分析，但是Query Profiler却可以定位出一条SQL语句执行的各种资源消耗情况，比如CPU，IO等，以及该SQL执行所耗费的时间等，不过该工具在MySQL5.0.37以上版本才有实现。

默认情况下，MySQL的该功能没有打开，需要自己手动启动。

profile使用

show profile和show profiles语句可以展示当前会话中执行语句的资源使用情况。
show profiles：以列表的形式显示最近发送到服务器上面的执行语句的资源使用情况，显示的记录数由变量profiling_history_size控制，默认15条

show profile：展示最近一条语句执行的详细资源占用信息，默认展示status和Duration两列。

开启profile功能

查看是当前的SQL版本是否支持Show Profile

show variables like ‘profiling%’; --查看 Show Profile 是否开启

接着开启profile功能

set profiling = 1; --1是开启，0是关闭

案例演示 1.执行sql语句

2.执行show profiles查看分析列表

3.查看第二条语句执行情况

show profile for query 2;

也可以指定资源类型查询：

show profile CPU,SWAPS for query 2; --查询语句2的CPU和SWAPS使用情况

MySQL性能分析

慢查询日志