MySQL性能调休，这是每一个 PHPer 在面试中永远无法绕过的一道面试题。那么，PHPHa 就从个人角度的理解，来总结下相关的优化方案。MySQL性能调优，推荐从下向上的性能调优，主要包括运行环境、配置参数、SQL性能、系统架构设计的调优。

1、运行环境调优

目前更多的是基于Linux的开发，因此，这一部分也是针对Linux内核的调优。通常来说，这部分工作会由运维工程师来完成。

主要包括系统的 CPU 内存 磁盘 网络 等方面。PHPHa 经验有限，这里不做过多说明。可以参考以下文章。

《MySQL 优化之 Linux 系统层面调优》

2、配置参数调优

这里的配置参数调优指的是 MySQL 的配置文件 my.cnf 中的相关参数优化。通常来说，这部分工作会由专业的DBA来完成。

PHPHa 同样是经验有限，不做说明（^_^），请参考以下文章或 Google 上的优秀文章。

《MySQL 5.6 配置优化》

3、SQL性能优化

作为后端开发人员，这块是我们要重点参与的部分。因此必须熟练的掌握相关的分析技巧和优化手段。

3.1、查看 MySQL 服务器运行状态

执行 SHOW STATUS 语句，可以查看到服务器的运行状态，输出的参数项非常多，这里只提及线程相关的几个，其他的可以 Google 去详细了解。

执行语句 SHOW GLOBAL STATUS LIKE 'Threads%'，输出如下：

mysql> SHOW GLOBAL STATUS LIKE 'Threads%';
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| Threads_cached    | 91    |
| Threads_connected | 7     |
| Threads_created   | 171   |
| Threads_rejected  | 0     |
| Threads_running   | 3     |
+-------------------+-------+

参数说明：

Threads_cached已缓存的线程数
Threads_connected已连接的线程数
Threads_created已创建的线程数
Threads_rejected异常的线程数
Threads_running正在运行的线程数

3.2、查看 MySQL 运行的线程

执行 SHOW PROCESSLIST 语句，输出如下：

mysql> SHOW PROCESSLIST;
+----------+-------------+---------------------+--------------------+---------+------+-------+------------------+
| Id       | User        | Host                | db                 | Command | Time | State | Info             |
+----------+-------------+---------------------+--------------------+---------+------+-------+------------------+
| 11628697 | development | 172.17.95.12:51190  | xxxxx_pro          | Sleep   | 5971 |       | NULL             |
| 11656031 | development | 172.17.95.12:32956  | xxxxx_pro          | Sleep   |  508 |       | NULL             |
| 11660778 | rdsadm_hdm  | 100.104.224.3:56563 | information_schema | Sleep   |   18 |       | NULL             |
| 11660951 | development | 172.17.95.12:33858  | xxxxx_pro          | Query   |    0 | init  | SHOW PROCESSLIST |
+----------+-------------+---------------------+--------------------+---------+------+-------+------------------+

从返回结果中我们可以了解该线程执行了什么命令/SQL 语句以及执行的时间。其中，返回的 State 的值是我们判断性能好坏的关键，其值出现如下内容，则该行记录的 SQL 语句需要优化：

Converting HEAP to MyISAM查询结果太大时把结果放到磁盘
Create tmp table创建临时表
Copying to tmp table on disk把内存临时表复制到磁盘
locked被其他查询锁住
loggin slow query记录慢查询
Sorting result排序

3.3、查看 MySQL 慢查询日志

首先需要开启慢日志，修改 my.cnf 添加如下配置：

[mysqld]
slow_query_log = 1
slow_query_log_file=/var/lib/mysql/slow-query.log
long_query_time = 1
log_queries_not_using_indexes = 1

相关参数说明：

slow_query_log开启慢日志
slow_query_log_file慢日志文件路径（需要注意文件可写入）
long_query_time执行时间超过 1 秒则记录
log_queries_not_using_indexes记录未使用索引的语句

修改完配置后重启 MySQL 服务器，之后可以通过以下语句查询生效状态：

mysql> SHOW VARIABLES LIKE 'slow_query%';
+---------------------+-------+
| Variable_name       | Value |
+---------------------+-------+
| slow_query_log      | ON    |
| slow_query_log_file |       |
+---------------------+-------+
2 rows in set (0.01 sec)
 
mysql> SHOW VARIABLES LIKE 'long_query_time';
+-----------------+----------+
| Variable_name   | Value    |
+-----------------+----------+
| long_query_time | 1.000000 |
+-----------------+----------+
1 row in set (0.01 sec)

可以借助 mysqldumpslow 等分析工具对慢日志文件进行分析，并进行相关的优化。

3.4、分析 SQL 语句

通过开启慢日志，那么我们经过分析，就能得出相关的慢 SQL，这就是我们需要重点分析优化的对象。

只需要在 SQL 语句前面添加 EXPLAIN 或 DESC 即可，如：

mysql> EXPLAIN SELECT * FROM ft_signing AS s LEFT JOIN ft_bill_list AS b ON b.signing_id=s.id LEFT JOIN ft_bill_fee AS f ON f.bill_id=b.id WHERE s.`status`=1;
+----+-------------+-------+------+---------------------+---------------------+---------+----------------+-------+-------+
| id | select_type | table | type | possible_keys       | key                 | key_len | ref            | rows  | Extra |
+----+-------------+-------+------+---------------------+---------------------+---------+----------------+-------+-------+
|  1 | SIMPLE      | s     | ref  | index_status        | index_status        | 1       | const          | 33919 | NULL  |
|  1 | SIMPLE      | b     | ref  | index_signid_status | index_signid_status | 4       | xxxxx_pro.s.id |     2 | NULL  |
|  1 | SIMPLE      | f     | ref  | index_bill_id       | index_bill_id       | 4       | xxxxx_pro.b.id |     1 | NULL  |
+----+-------------+-------+------+---------------------+---------------------+---------+----------------+-------+-------+
3 rows in set (0.01 sec)

相关参数说明：

id查询序列号id 相同，执行顺序由上至下
id 不同，值越大优先级越高，越先被执行
select_type查询类型[simple] 简单查询，不包含子查询或 union
[primary] 包含复杂的子查询，最外层查询标记为该值
[subquery] 在 select 或 where 包含子查询，被标记为该值
[derived] 在 from 列表中包含的子查询被标记为该值，MySQL 会递归执行这些子查询，把结果放在临时表
[union] 若第二个 select 出现在 union 之后，则被标记为该值；若 union 包含在 from 的子查询中，外层 select 被标记为 derived
[union result] 从 union 表获取结果的 select
table相关表名
type表连接类型性能从高到低排序如下：
[system] 表只有一行记录，相当于系统表
[const] 通过索引一次就找到，只匹配一行数据
[eq_ref] 唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配；常用于主键或唯一索引扫描
[ref] 非唯一性索引扫描，返回匹配某个单独值的所有行；用于 = < > 操作符带索引的列
[range] 只检索给定范围的行，使用一个索引来选择行；一般使用between > < 情况
[index] 只遍历索引树
[ALL] 全表扫描，性能最差
possible_keys可能使用的索引
key实际使用的索引
key_len索引中使用的字节数在不损失精确性的情况下，长度越短越好
显示的是索引字段的最大长度，并非实际使用长度
ref显示该表的索引字段关联了哪张表的哪个字段
rows大致估算出找到所需的记录或所需读取的行数值越小越性能越好
Extra额外信息[using filesort] 说明 MySQL 会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取；出现该值则应该优化 SQL
[using temporary] 使用了临时表保存中间结果，MySQL 在对查询结果排序时使用临时表；常见于排序 order by 和分组查询 group by；出现该值则应该优化 SQL
[using index] 表示相应的 select 操作使用了覆盖索引，避免了访问表的数据行，效率不错
[using where] where 子句用于限制哪一行
[using join buffer] 使用连接缓存
[distinct] 发现第一个匹配后，停止为当前的行组合搜索更多的行

3.5、表结构设计优化

3.5.1、字段优化

1) 使用可以存下数据最小的数据类型
2) 使用简单的数据类型，int 要比 varchar 类型处理简单
3) 尽量使用 tinyint、smallint、mediumint 作为整数类型而非 int
4) 尽可能使用 not null 定义字段，因为 null 占用4字节空间
5) 尽量少用 text 类型，非用不可时最好考虑分表
6) 尽量使用 timestamp 而非 datetime
7) 单表不要有太多字段，建议在 20 以内

3.5.2、读写分离

可以使用集群方案，1主N从，因为大部分场景下，都是读远大于写。

3.5.3、分区分库分表

此处涉及的知识点，另写一篇文章单独总结。《简述 MySQL 分区分库分表》

3.6、MySQL 查询优化

1) 一次尽量少查（字段数和记录数），避免 SELECT *，加上 LIMIT N
2) 小表驱动大表，即小的数据集驱动大的数据集。如：以 A，B 两表为例，两表通过 id 字段进行关联
当 B 表的数据集小于 A 表时，用 in 优化 exist；使用 in ，两表执行顺序是先查 B 表，再查 A 表：
select * from A where id in (select id from B)
当 A 表的数据集小于 B 表时，用 exist 优化 in；使用 exists，两表执行顺序是先查 A 表，再查 B 表：
select * from A where exists (select 1 from B where B.id = A.id)
3) 一些情况下，可以使用连接代替子查询，因为使用 join，MySQL 不会在内存中创建临时表
4) 适当添加冗余字段，减少表关联
5) 避免 ORDER BY RAND()
6) 合适场景选用 ENUM 而不是 VARCHAR
7) 字段尽可能 NOT NULL
8) 把IP地址存成 UNSIGNED INT，PHP 中可以借助函数 ip2long() / long2ip()
9) 拆分大的 DELETE 或 INSERT 语句，可以分多次处理，DELETE 可加上 LIMIT N

3.7、MySQL 索引优化

3.7.1、适合使用索引的场景

1) 主键自动创建唯一索引
2) 频繁作为查询条件的字段
3) 查询中与其他表关联的字段
4) 查询中排序的字段
5) 查询中统计或分组字段

3.7.2、不适合使用索引的场景

1) 频繁更新的字段
2) where 条件中用不到的字段
3) 表记录太少
4) 经常增删改的表
5) 字段的值的差异性不大或重复性高

3.7.3、索引创建和使用原则

1) 单表查询：哪个列作查询条件，就在该列创建索引
2) 多表查询：left join 时，索引添加到右表关联字段；right join 时，索引添加到左表关联字段
3) 不要对索引列进行任何操作（计算、函数、类型转换）
4) 索引列中不要使用 !=，<> 非等于
5) 索引列不要为空，且不要使用 is null 或 is not null 判断
6) 索引字段是字符串类型，查询条件的值要加''单引号，避免底层类型自动转换

3.7.4、索引失效情况

1) 模糊查询时，以 % 开头
2) 使用 or 时，如：字段1（非索引）or 字段2（索引）会导致索引失效
3) 不满足最左前缀原则

最左前缀原则说明，举例：index(a,b,c) 作为复合索引

SQL语句索引有效性
where a = 1字段 a 索引生效
where a = 1 and b = 2字段 a 和 b 索引生效
where a = 1 and b = 2 and c = 3全部生效
where b = 2 或 where c = 3失效
where a = 1 and c = 3字段 a 生效，字段 c 失效
where a = 1 and b > 2 and c = 3字段 a，b 生效，字段 c 失效
where a = 1 and b like 'xxx%' and c = 3字段 a，b 生效，字段 c 失效

4、总结

MySQL发展至今，各种配置参数及优化手段之多，相信一般的 DBA 也未必能全部掌握。

以上就是从后端开发人员的角度总结的 MySQL 性能调优的基本方案，仅供参考。