第18章 SQL优化
18.1 优化SQL语句的一般步骤
18.1.1 通过show status命令了解各种SQL的执行效率
查询当前session中所有统计参数的值:show status like 'Com_%';
- Com_select:一次查询只累加一次;
- Com_insert:对于批量插入的INSERT操作,只累加一次;
- Com_update:
- Com_delete:
针对InnoDB引擎:
InnoDB_rows_read、InnoDB_rows_inserted、InnoDB_rows_updated、InnoDB_rows_deleted。
通过以上参数,可以很容易地了解当前数据库的应用是以插入为主还是查询为主。
18.1.2 定位执行效率较低的SQL语句
- 通过慢查询日志定位那些执行效率较低的SQL语句,用--log-slow-queries[=file_name]选项启动时,mysqld写一个包含所有执行时间超过long_query_time秒的SQL语句的日志文件。
- 慢查询日志在查询结束后才记录,所以应用并不能立即定位问题,可以使用show processlist命令查看当前MySQL在进行的线程,包含线程的状态、是否锁表等,可以实时地查看SQL的执行情况,同时对一些锁表操作进行优化。
18.1.3 通过EXPLAIN分析低效SQL的执行计划
举例
explain
select sum(amount) from customer a, payment b
where 1=1
and a.customer_id = b. customer_id
and email = 'JANE.BETWEEN@sakilacustomer.org'\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: a
partitions: NULL
type: ALL
possible_keys: PRIMARY
key: NULL
key_len: NULL
ref: NULL
rows: 1
filtered: 100.00
Extra: Using where
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: b
partitions: NULL
type: ref
possible_keys: idx_fk_customer_id
key: idx_fk_customer_id
key_len: 2
ref: sakila.a.customer_id
rows: 1
filtered: 100.00
Extra: NULL
2 rows in set, 1 warning (0.00 sec)
- select_type:SELECT的类型
- SIMPLE简单表不使用表连接或者子查询
- PRIMARY主查询即外层的查询
- UNION中的第二列或者后面的查询语句
- SUBQUERY子查询中的第一个SLECT等
- table:输出结果集的表
- type:找到行所需的方式或者叫查询类型:ALL、index、range、ref、eq_ref、const,system、NULL,性能由差到好
--type=ALL,全表扫描,遍历全表找到匹配的行
explain select * from film where rating >9\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: film
partitions: NULL
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 1000
filtered: 33.33
Extra: Using where
1 row in set, 1 warning (0.00 sec)
--type=index,索引全扫描,遍历整个索引来查询匹配的行:
explain select title from film\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: film
partitions: NULL
type: index
possible_keys: NULL
key: idx_title
key_len: 767
ref: NULL
rows: 1000
filtered: 100.00
Extra: Using index
1 row in set, 1 warning (0.00 sec)
--type=range,索引范围扫描,常见于大于小于大于等于between
explain select * from payment where customer_id >= 300 and customer_id <= 350\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: payment
partitions: NULL
type: range
possible_keys: idx_fk_customer_id
key: idx_fk_customer_id
key_len: 2
ref: NULL
rows: 1350
filtered: 100.00
Extra: Using index condition
1 row in set, 1 warning (0.00 sec)
--type=ref, 使用非唯一索引或唯一索引的前缀扫描,返回匹配某个单独值的记录行
explain select * from payment where customer_id = 350\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: payment
partitions: NULL
type: ref
possible_keys: idx_fk_customer_id
key: idx_fk_customer_id
key_len: 2
ref: const
rows: 23
filtered: 100.00
Extra: NULL
--type=eq_ref,类似于ref,区别就是使用的索引是唯一索引,对于每个索引键值,表中只有一条记录匹配,
--简单的说,就是多表连接中使用primary key或者unique index作为关联条件
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
partitions: NULL
type: ALL
possible_keys: PRIMARY
key: NULL
key_len: NULL
ref: NULL
rows: 1000
filtered: 100.00
Extra: NULL
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: a
partitions: NULL
type: eq_ref
possible_keys: PRIMARY
key: PRIMARY
key_len: 2
ref: sakila.b.film_id
rows: 1
filtered: 100.00
Extra: Using where
2 rows in set, 1 warning (0.00 sec)
--type=const/system,表单中最多有一个匹配行,查询起来非常迅速,所以这个匹配行中的其他列的值可以
--被优化器在当前查询中当作常量来处理,例如,根据主键或者唯一索引进行的查询
explain select * from (
-> select * from customer where email = 'AARON.SELBY@sakilacustomer.org')a\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: customer
partitions: NULL
type: const
possible_keys: uk_email
key: uk_email
key_len: 153
ref: const
rows: 1
filtered: 100.00
Extra: NULL
1 row in set, 1 warning (0.00 sec)
--通过唯一索引访问的时候,类型type=const
--而从我们构造的仅有一条记录的表中检索时,类型为system
--type=NULL,不用访问表或者索引,直接就能够得到结果
explain select 1 from dual where 1\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: NULL
partitions: NULL
type: NULL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: NULL
filtered: NULL
Extra: No tables used
1 row in set, 1 warning (0.00 sec)
有的时候,仅仅通过explain分析执行计划并不能很快地定位sql问题,这个时候我们还可以选择profile联合分析。
18.1.4 通过show profile分析SQL
MyISAM表有表元数据地缓存,即COUNT(*)值,那么对一个MyISAM表的COUNT(*)是不需要太多资源的,而InnoDB比较慢;
select count(*) from payment
+----------+
| count(*) |
+----------+
| 16049 |
+----------+
1 row in set (0.01 sec)
show profiles;
+----------+------------+------------------------------+
| Query_ID | Duration | Query |
+----------+------------+------------------------------+
| 1 | 0.00435625 | select count(*) from payment |
+----------+------------+------------------------------+
show profile for query 1;
+----------------------+----------+
| Status | Duration |
+----------------------+----------+
| starting | 0.000095 |
| checking permissions | 0.000007 |
| Opening tables | 0.000202 |
| init | 0.000019 |
| System lock | 0.000011 |
| optimizing | 0.000007 |
| statistics | 0.000015 |
| preparing | 0.000014 |
| executing | 0.000004 |
| Sending data | 0.003895 |
| end | 0.000022 |
| query end | 0.000015 |
| closing tables | 0.000012 |
| freeing items | 0.000021 |
| cleaning up | 0.000019 |
+----------------------+----------+
create table payment_myisam like payment;
alter table payment_myisam engine = myisam;
insert into payment_myisam select * from payment;
select count(*) from payment_myisam;
show profiles;
show profile for query 7;
+----------+------------+--------------------------------------------------+
| Query_ID | Duration | Query |
+----------+------------+--------------------------------------------------+
| 1 | 0.00435625 | select count(*) from payment |
| 2 | 0.00009400 | show profiles for query 1 |
| 3 | 0.05729850 | create table payment_myisam like payment |
| 4 | 0.00006000 | alert table payment_myisam engine = myisam |
| 5 | 0.08633100 | alter table payment_myisam engine = myisam |
| 6 | 0.08052975 | insert into payment_myisam select * from payment |
| 7 | 0.00017700 | select count(*) from payment_myisam |
+----------+------------+--------------------------------------------------+
mysql> show profile for query 7;
+----------------------+----------+
| Status | Duration |
+----------------------+----------+
| starting | 0.000075 |
| checking permissions | 0.000008 |
| Opening tables | 0.000017 |
| init | 0.000013 |
| System lock | 0.000011 |
| optimizing | 0.000006 |
| executing | 0.000008 |
| end | 0.000004 |
| query end | 0.000005 |
| closing tables | 0.000009 |
| freeing items | 0.000010 |
| cleaning up | 0.000011 |
+----------------------+----------+
从结果可以看到,MyISAM完全不需要访问数据