Mysql优化(深入浅出MySql) explain

Mysql优化

1. 优化的一般步骤:

1. 通过show status命令了解各种SQL执行的频率

在成功连接了mysql客户端之后,我们可以通过show [session|global] status来查看服务器状态信息,也可以在系统上使用mysqladmin extended-status命令获得这些信息,命令参数session表示当前连接统计结果,global表示自数据库上次启动至今的统计结果;如果不加参数默认是session级的

show status like 'Com%':Com_xxx表示每个xxx语句执行的次数,一般我们关心的是一下几个统计参数

Com_select:执行SELECT语句操作的次数,一次查询之累加1

Com_insert:执行INSERT操作的次数,对于批量插入的INSERT操作,只累加一次

Com_update:执行UPDATE操作的次数

Com_delete:执行DELETE操作的次数

上面这些参数对于所有存储引擎的表操作都会进行累计.下面这几个参数只是针对InnoDB存储引擎的,累加的算法也略有不同

Innodb_rows_read:SELECT查询返回的行数

Innodb_rows_inserted:执行INSERT操作插入的行数

Innodb_rows_updated:执行UPDATE操作更新的行数

Innodb_rows_delete:执行DELETE操作删除的行数

通过以上的参数去了解当前数据库应用是以插入为主还是以查询为主,以及各种类型的SQL大致的执行比例是多少.对于更新操作的统计,是对执行次数的计数,不论提交还是会滚都会进行累加

对于事务型的应用,通过Com_commit和Com_rollback可以了解事物提交和回滚的情况,对于回滚操作非常频繁的数据库,可能意味着应用编写存在问题

Connections:试图连接MySql服务器的次数

Uptime:服务器工作时间

Slow_queries:慢查询次数

2. 定位执行效率较低的SQL语句

1. 通过日志:将slow-query-log参数设置为1之后,MySql会将所有执行时间超过long_query_time参数所设定的阈值的SQL,写入slow_query_log_file参数所指定的文件中
2. 使用show processlist命令:查看当前MySql在进行的线程,包括线程的状态,是否锁表等,可以实时的查看SQL的执行情况.同时对一些锁表操作进行优化

3. 通过EXPLAIN分析低效SQL的执行计划

创建mysql官网的sakila数据库并且倒入相应的结构和数据sql脚本

执行EXPLAIN select sum(amount) from customer a,payment b where 1=1 and a.customer_id = b.customer_id and email = 'JANET.PHILLIPS@sakilacustomer.org'sql进行查询

select_type:表示select的类型,常见的取值有SIMPLE(简单表,即不使用表连接或者子查询),PRIMARY(主查询,即外层的查询),UNION(UNION中的第二个或者后面的查询语句),SUBQUERY(子查询中的第一个SELECT)等

table:输出结果集的表

type:表示MYSQL在表中找到所需行的方式,或者叫访问类型,常见的类型有All index range ref eq_ref const,system NULL,从左至右,性能由差到最好

(1)type=All,全表扫描.MySql遍历全表来找到匹配行:EXPLAIN select * from film where rating>9;

(2)type=index,索引全扫描,MySql遍历整个索引来查询匹配的行:explain select title from film;

(3)type=range,索引全范围扫描,常见于<,<=,>,>=,between等操作符:EXPLAIN select * from payment where customer_id >= 300 and customer_id <= 350

(4)type=ref,使用非唯一索引扫描或唯一索引的前缀扫描,返回匹配某个单独值的记录行,例如:EXPLAIN select * from payment where customer_id = 350,索引idx_fk_customer_id是非唯一索引,查询条件为等值查询条件customer_id = 350,所以扫描索引的类型为ref.ref还经常出现在join操作中:EXPLAIN SELECT a.*,b.* from payment a,customer b where a.customer_id = b.customer_id;

(5)type=eq_ref:类似ref,区别就在使用的索引是唯一索引,对于每个索引键值,表中只有一条记录匹配;简单来讲,就是多表连接中使用primary key或者unique index作为关联条件:EXPLAIN select * from film a,film_text b where a.film_id = b.film_id;

(6)type=const/system:单表中最多有一个匹配行,查询起来非常迅速,所以这个匹配行中的其他列的值可以被优化器在当前查询中当作常量来处理,例如,根据主键primary key或者唯一索引unique index进行的查询:alter table customer drop index idx_email;alter table customer add unique index uk_email(emial);explain select *from (select * from customer where email = 'AARON.SELBY@sakilacustomer.org')a.通过唯一索引uk_email访问的时候,类型type为const;而从我们构造的仅有一条记录的a表中检索时,,类型type就为system

(7)type=NULL,mysql不用访问表或者索引,直接就能够得到结果,例如explain select 1 from dual where 1

类型type还有其他值,如ref_or_null(与ref类似,区别在于条件中包含对NULL的查询),index_merge(索引合并优化),unique_subquery(in的后面是一个查询主键字段的子查询),index_subquery(与unique_subquery类似,区别在于in的后面是查询非唯一索引字段的子查询)等

possible_keys:表示查询时可能使用的索引

key:表示实际使用的索引

key_len:使用到索引字段的长度

rows:扫描行的数量

Extra:执行情况的说明和描述,包含不适合在其他列中显示但是对执行计划非常重要的额外信息