当程序出现性能问题,确定不存在物理系统资源瓶颈之后,需要关注到数据库性能;确认数据库性能瓶颈后,将需要用到SQL的调优。
1. 识别性能问题
1.1 寻找运行缓慢的SQL语句
mysql > SHOW FULL PROCESSLIST\G
输出的Info对应查询语句,Time值可以看出该条SQL的运行时间。
1.2 确认低效查询
发现潜在低效查询之后,需要确认该查询是否每次重复执行都缓慢,需要验证某次的低效表现是否受到系统瓶颈等其他因素影响。
a)重复运行SQL语句并记录执行时间
mysql> SELECT * FROM inventory WHERE item_id=16102176;
Empty set (3.19 sec)
重复运行的方法只适用于SELECT 语句。如果低效语句是UPDATE或者DELETE这种会修改现有数据的语句,应该将其简单重写成SELECT再进行验证。
b)生成查询执行计划(Query Execution Plan, QEP)
QEP决定了MySQL从底层存储引擎中获取信息的方式。
mysql> EXPLAIN SELECT * FROM inventory WHERE item_id = 16102176\G
*************************************** 1.row ***************************************
id: 1
select_type: SIMPLE
table: inventory
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 787338
Extra: Using where
key列显示查询语句使用的索引,任何没有索引的查询语句都可以认为是没有被足够调优的SQL查询。
rows列显示受影响的行数,可以用来估计查询需要读取的数据量,这和查询所需要的执行时间直接相关。
type显示ALL也是潜在性能问题的一个标志。(未详解)
* Explain多数情况并不执行查询。而当优化器需要执行这条SQL的一部分来决定执行计划时,也会例外。此时select_type会显示 DERIVED。
* 根据底层不同的存储引擎,rows这个指标可能是估计值(InnoDB)也可能是精确值,
2. 优化查询
2.1 不应该做的事情
在没进一步验证的情况下,千万不要直接基于WHERE语句添上一个索引。如:
mysql> ALTER TABLE inventory ADD INDEX (item_id);
Query OK, 734787 rows affected (54.22 sec)
Records: 734787 Duplicates: 0 Warnings: 0
决定添加索引需要考虑很多因素。
如果决定部署到生产环境,上例中的语句执行了55秒,在此期间,由于ALTER语句是阻塞操作,引起所有为表添加和修改数据的其他请求都被阻塞了。根据其他DML(数据操作语言)的执行顺序,SELECT语句也会被阻塞。
如果数据量更大一些,ALTER语句可能需要几小时甚至几天才能完成。
另一个需要考虑的因素是一个表有多个索引的情况下,DML语句有额外的性能开销。
2.2 确认优化
上例优化之后,重复执行SQL查询可以看到性能得到明显改善。
mysql> SELECT * FROM inventory WHERE item_id = 16102176;
Empty set (0.00 sec)
也可以通过查看修正了的QEP来确认新索引的效率:
mysql> EXPLAIN SELECT * FROM inventory WHERE item_id = 16102176\G
************************************ 1.row ************************************
id: 1
select_type: SIMPLE
table: inventory
type: ref
possible_keys: item_id
key: item_id
key_len: 4
ref: const
rows: 1
Extra:
MySQL 优化器现在选择了一个key列值指定的索引。rows变成了1。
2.3 正确的方式
为table添加索引有有点也有缺点,因此能否添加一个索引是需要综合考虑的。
在决定添加索引之前,通常应该至少做两项检查: 首先验证表现有的结构, 然后确认表的大小。
可以通过如下语句获取上述信息:
mysql> SHOW CREATE TABLE inventory\G
*************************** 1. row ***************************
Create Table: CREATE TABLE inventory (
id INT(10) unsigned NOT NULL AUTO_INCREMENT,
supp_id int(10) unsigned NOT NULL DEFAULT '0',
item_id int(10) unsigned NOT NULL DEFAULT '0',
qyt int(11) NOT NULL,
created timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (id)
UNIQUE KEY supp_id (supp_id, item_id),
KEY created (created),
) ENGINE=InnoDB DEFAULT CHARSET=latin1
mysql> SHOW TABLE STATUS LIKE 'inventory'\G
******************************** 1.row ********************************
Name: inventory
Engine: InnoDB
Version: 10
Row_format: Compact
Rows: 679890
Avg_row_length: 371
Data_length: 252395520
Max_data_length: 0
Index_length: 40861696
Data_free: 0
Auto_increment: 1612406
Create_time: 2010-08-17 20:16:13
Update_time: NULL
Check_time: NULL
Collation: latin1_swedish_ci
Checksum: NULL
Create_options:
Comment: InnoDB free: 644096 Kb
从返回结果中可以看到,当前表结构包含一系列索引,也包括了一个使用了item_id的索引。然后这个索引并没有被用到,因此之前的查询不能满足索引中最左边的列(?)
也可以通过SHOW TABLE STATUS命令的DATA_length 和Rows 信息来获得表大小的近似值。2.4 备选的解决方案
优化SQL的正确方法包括 理解和验证此SQL 语句以及与表相关的SQL语句的目的。
上例中添加索引并不是解决查询速度慢的理想方法,而是创建了一个不必要的索引,导致了额外的开销。
supp_id已经有索引,可以将它放在WHERE字句后面作为一个条件,这样就能使用到现有的索引而不需要做任何改变。