MySQL性能调休,这是每一个 PHPer
在面试中永远无法绕过的一道面试题。那么,PHPHa
就从个人角度的理解,来总结下相关的优化方案。MySQL性能调优,推荐从下向上的性能调优,主要包括运行环境、配置参数、SQL性能、系统架构设计的调优。
1、运行环境调优
目前更多的是基于Linux的开发,因此,这一部分也是针对Linux内核的调优。通常来说,这部分工作会由运维工程师来完成。
主要包括系统的 CPU
内存
磁盘
网络
等方面。PHPHa
经验有限,这里不做过多说明。可以参考以下文章。
2、配置参数调优
这里的配置参数调优指的是 MySQL
的配置文件 my.cnf
中的相关参数优化。通常来说,这部分工作会由专业的DBA来完成。
PHPHa
同样是经验有限,不做说明(^_^),请参考以下文章或 Google
上的优秀文章。
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
性能调优的基本方案,仅供参考。