当面对一个sql性能问题,我们应该从何处入手使得尽快定位问题sql,我们从基础的命令开始
mysql> show status like 'Com_%' \g
+---------------------------+-------+
| Variable_name | Value |
+---------------------------+-------+
| Com_admin_commands | 0 |
| Com_assign_to_keycache | 0 |
| Com_alter_db | 0 |
| Com_alter_db_upgrade | 0 |
| Com_alter_event | 0 |
| Com_alter_function | 0 |
| Com_alter_procedure | 0 |
| Com_alter_server | 0 |
| Com_alter_table | 0 |
| Com_alter_tablespace | 0 |
| Com_alter_user | 0 |
| Com_analyze | 0 |
| Com_begin | 0 |
| Com_binlog | 0 |
| Com_call_procedure | 0 |
| Com_change_db | 0 |
| Com_change_master | 0 |
| Com_check | 0 |
| Com_checksum | 0 |
| Com_commit | 0 |
Com_xxx表示每个xxx语句执行的次数,创建的的参数如下
Com_select :执行select操作的次数,一次查询累加一次。
Com_insert: 执行insert操作的次数,批量插入累加一次
Com_delete:执行delete操作的次数
Com_update:执行update操作的次数
下面的参数只针对InnoDB存储引擎,累加的算法也略有不通。
Innodb_rows_read:select查询返回的行数。
Innodb_rows_inserted:insert插入的的行数。
innodb_rows_deleted:delete删除的行数。
innodb_rows_updated:update更新的行数,不管是提交还是回滚都会进行累加。
上面命令可以查出你的数据库到底是以查询为主,还是更新操作为主。
对于事物型的应用,通过Com_commit和Com_rollback可以了解提交和回滚的情况,对于回滚此处过多,就有可能是编程出现了问题。
下面参数是了解数据库的基本情况
Connections:试图连接Mysql服务器的次数。
Uptime:服务器工作时间。
Slow_queries:慢查询的次数。
通过慢查询日志定位那些sql执行效率低,通过--log_slow-queries[=file-names]选项启动,mysqld会记录一个超过long_query_time秒的日志文件。
慢查询日志是在应用执行完之后记录的,索引应用在出现sql效率的问题上并能定位问题,我们可以使用 show processlist命令查看当前mysql在进行的线程,包括线程的状态,是够锁表,可以实时的查看sql的执行情况,同时对一些锁表操作进行优化。
通过上面步骤查看到低效率的sql之后,我们就可以使用explain或desc命令获取mysql如何执行select语句的信息,包括如何连接和链接的顺序。
我们先看几个参数的说明
select_type:表示select类型。常见的取值有SIMPLE(简单表,既不是用表连接或子查询)PRIMARY(朱查询,即外层查询),UNION( UNION中的第二哥或者后面的查询语句)SUBQUERY(子查询的第一个select)
table:输出结果的表。
type:访问类型,在表中找到所需行的方式。
从左到右,性能依次最好。
mysql> explain select * from film where rating>9\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: film
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 1000
Extra: Using where
1 row in set (0.00 sec)
mysql> explain select title from film\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: film
type: index
possible_keys: NULL
key: idx_title
key_len: 767
ref: NULL
rows: 1000
Extra: Using index
1 row in set (0.00 sec)
mysql> explain select * from payment where customer_id>=300 and customer_id<350\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: payment
type: range
possible_keys: idx_fk_customer_id
key: idx_fk_customer_id
key_len: 2
ref: NULL
rows: 1325
Extra: Using index condition
1 row in set (0.00 sec)
mysql> explain select * from payment where customer_id=350\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: payment
type: ref
possible_keys: idx_fk_customer_id
key: idx_fk_customer_id
key_len: 2
ref: const
rows: 23
Extra: NULL
1 row in set (0.00 sec)
mysql> explain select * from film a, film_text b where a.film_id =b.film_id\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: b
type: ALL
possible_keys: PRIMARY
key: NULL
key_len: NULL
ref: NULL
rows: 1000
Extra: NULL
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: a
type: eq_ref
possible_keys: PRIMARY
key: PRIMARY
key_len: 2
ref: sakila.b.film_id
rows: 1
Extra: Using where
2 rows in set (0.00 sec)
mysql> explain select * from (select * from customer where email='AARON.SELBY@SAKILACUSTOMER.ORG')a\G
*************************** 1. row ***************************
id: 1
select_type: PRIMARY
table: <derived2>
type: system
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 1
Extra: NULL
*************************** 2. row ***************************
id: 2
select_type: DERIVED
table: customer
type: const
possible_keys: uk_email
key: uk_email
key_len: 153
ref: const
rows: 1
Extra: NULL
2 rows in set (0.00 sec)
mysql> explain select 1 from dual where 1\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: NULL
type: NULL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: NULL
Extra: No tables used
1 row in set (0.00 sec)
类型type还有其他值,如ref_or_null(与ref类似,区别是对查询条件包含null值查询),index_merge(索引合并优化),unique_subquery(in的后面是一个查询主键字段的子查询),index_subquery(与unique_subquery类似,区别是在于in的后面是查询非唯一索引字段的子查询)等,
mysql4.1引入了explain extended 命令,通过explain extended和show warnings,w我们能够看到sql真正执行之前优化器做了哪些优化,在mysql 5.1开始支持分区的功能,同时可以使用explain partitions 查看数据结果多需要访问的分区名称。
首先查看是否支持show profile,如果支持,开启profiling,如下是否支持使用
select @@have_profiling.
mysql> select @@have_profiling; //是否支持show profiling
+------------------+
| @@have_profiling |
+------------------+
| YES |
+------------------+
1 row in set, 1 warning (0.00 sec)
mysql> select @@profiling; //默认关闭
+-------------+
| @@profiling |
+-------------+
| 0 |
+-------------+
1 row in set, 1 warning (0.00 sec)
mysql> set profiling=1; //开启profiling
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> select @@profiling;
+-------------+
| @@profiling |
+-------------+
| 1 |
+-------------+
1 row in set, 1 warning (0.01 sec)
通过profile,我们可以更清除的了解sql执行的过程,比如,我使用count(*).我们知道myisam表使用count(*)是不需要小号太多资源的,相对于Innodb就没有这种元数据缓存,count(*)执行的较慢,如下
mysql> select count(*) from payment;
+----------+
| count(*) |
+----------+
| 16049 |
+----------+
1 row in set (0.01 sec)
使用show profiles查看query_id
mysql> show profiles;
+----------+------------+------------------------------+
| Query_ID | Duration | Query |
+----------+------------+------------------------------+
| 1 | 0.00350500 | select @@profiling |
| 2 | 0.00863050 | select count(*) from payment |
+----------+------------+------------------------------+
2 rows in set, 1 warning (0.00 sec)
在使用show profiling for query 查看执行过程的每个线程状态和消耗的时间
mysql> show profile for query 2;
+----------------------+----------+
| Status | Duration |
+----------------------+----------+
| starting | 0.000506 |
| checking permissions | 0.000272 |
| Opening tables | 0.000467 |
| init | 0.000039 |
| System lock | 0.000229 |
| optimizing | 0.000231 |
| statistics | 0.000040 |
| preparing | 0.000222 |
| executing | 0.000083 |
| Sending data | 0.006353 |
| end | 0.000024 |
| query end | 0.000013 |
| closing tables | 0.000022 |
| freeing items | 0.000090 |
| cleaning up | 0.000041 |
+----------------------+----------+
15 rows in set, 1 warning (0.00 sec)
我们发现消耗的时间在 sending data(表示mysql线程开始访问数据,到返回到客户端,而不是仅仅返回结果给客户端,sending data 状态往往需要做大量的磁盘读取操作,所以往往是访问消耗最长的状态), 我们也可以查看mysql在上什么资源(all,cpu,block io,context switch,page faults)上消耗过高的时间,例如,查看CPU
mysql> show profile cpu for query 2;
+----------------------+----------+----------+------------+
| Status | Duration | CPU_user | CPU_system |
+----------------------+----------+----------+------------+
| starting | 0.000506 | 0.000000 | 0.000000 |
| checking permissions | 0.000272 | 0.000000 | 0.000000 |
| Opening tables | 0.000467 | 0.000000 | 0.000000 |
| init | 0.000039 | 0.000000 | 0.000000 |
| System lock | 0.000229 | 0.000000 | 0.000000 |
| optimizing | 0.000231 | 0.000000 | 0.000000 |
| statistics | 0.000040 | 0.000000 | 0.000000 |
| preparing | 0.000222 | 0.000000 | 0.000000 |
| executing | 0.000083 | 0.000000 | 0.000000 |
| Sending data | 0.006353 | 0.010000 | 0.000000 |
| end | 0.000024 | 0.000000 | 0.000000 |
| query end | 0.000013 | 0.000000 | 0.000000 |
| closing tables | 0.000022 | 0.000000 | 0.000000 |
| freeing items | 0.000090 | 0.000000 | 0.000000 |
| cleaning up | 0.000041 | 0.000000 | 0.000000 |
+----------------------+----------+----------+------------+
15 rows in set, 1 warning (0.01 sec)
使用myisam存储引擎,效率就比较好,完全不需要访问数据
mysql> create table payment_myisam like payment;
Query OK, 0 rows affected (0.05 sec)
mysql> alter table payment_myisam engine=myisam;
Query OK, 0 rows affected (0.05 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> insert into payment_myisam select * from payment;
Query OK, 16049 rows affected (0.09 sec)
Records: 16049 Duplicates: 0 Warnings: 0
mysql> select count(*) from payment_myisam;
+----------+
| count(*) |
+----------+
| 16049 |
+----------+
1 row in set (0.00 sec)
使用show profiles 查看queryid ,查看queryid消耗在哪个状态以及时间
mysql> show profiles
-> ;
+----------+------------+----------------------------------------------------+
| Query_ID | Duration | Query |
+----------+------------+----------------------------------------------------+
| 1 | 0.00350500 | select @@profiling |
| 2 | 0.00863050 | select count(*) from payment |
| 3 | 0.00044600 | set @query_id :=2 |
| 4 | 0.00038475 | select state ,sum(duration) as total_r, |
| 5 | 0.05704400 | create table payment_myisam like payment |
| 6 | 0.05384750 | alter table payment_myisam engine=myisam |
| 7 | 0.08750150 | insert into payment_myisam select * from payment |
| 8 | 0.00061250 | select count(*) from payment_myisam |
+----------+------------+----------------------------------------------------+
8 rows in set, 1 warning (0.00 sec)
mysql> show profile for query 8;
+----------------------+----------+
| Status | Duration |
+----------------------+----------+
| starting | 0.000115 |
| checking permissions | 0.000035 |
| Opening tables | 0.000064 |
| init | 0.000054 |
| System lock | 0.000053 |
| optimizing | 0.000023 |
| executing | 0.000022 |
| end | 0.000023 |
| query end | 0.000022 |
| closing tables | 0.000037 |
| freeing items | 0.000048 |
| cleaning up | 0.000117 |
+----------------------+----------+
12 rows in set, 1 warning (0.00 sec)
发现在myisam存储引擎中,在executing之后就直接结束查询了,完全不需要访问数据。
经过以上步骤基本就可确定问题出现在哪里,此时可以采用响应的措施,进行提高执行的效率。最常见的措施就是添加索引,索引下一篇文章在见,请持续关注。