MySQL EXPLAIN命令详解学习,实则即为一般MySQL的优

作者: www.9159.com  发布:2019-11-09

在开始博客之前,还是同样的给一个大概的目录结构,实则即为一般MySQL的优化步骤

MySQL EXPLAIN命令详解学习

MySQL数据库优化详解

1、查看SQL的执行频率---------------使用show status命令

 

mysql表复制 

2、定位哪些需要优化的SQL------------通过慢查询记录+show processlist命令查看当前线程

MySQL的EXPLAIN命令用于SQL语句的查询执行计划(QEP)。这条命令的输出结果能够让我们了解MySQL 优化器是如何执行

复制表结构+复制表数据
mysql> create table t3 like t1;
mysql> insert into t3 select * from t1;
mysql索引
ALTER TABLE用来创建普通索引、UNIQUE索引或PRIMARY KEY索引
ALTER TABLE table_name ADD INDEX index_name (column_list)
ALTER TABLE table_name ADD UNIQUE (column_list)
ALTER TABLE table_name ADDPRIMARY KEY (column_list)
Create Index
CREATE INDEX index_name ON table_name (column_list)
CREATE UNIQUE INDEX index_name ON table_name (column_list)
drop index
DROP INDEX index_name ON talbe_name
alter table table drop
ALTER TABLE table_name DROP INDEX index_name
ALTER TABLE table_name DROP PRIMARY KEY
mysql视图
创建视图(www.jbxue.com 脚本学堂)
mysql> create view v_t1 as select * from t1 where id>4 and id<11;
Query OK, 0 rows affected (0.00 sec)
view视图的帮助信息
mysql> ? view
ALTER VIEW
CREATE VIEW
DROP VIEW
查看视图
mysql> show tables;
删除视图v_t1
mysql> drop view v_t1;
mysql内置函数
字符串函数
CONCAT (string2 [,… ]) //连接字串
LCASE (string2 ) //转换成小写
UCASE (string2 ) //转换成大写
LENGTH (string ) //string长度
LTRIM (string2 ) //去除前端空格
RTRIM (string2 ) //去除后端空格
REPEAT (string2 ,count ) //重复count次
REPLACE (str ,search_str ,replace_str ) //在str中用replace_str替换search_str
SUBSTRING (str , position [,length ]) //从str的position开始,取length个字符
SPACE(count) //生成count个空格

3、分析为什么SQL执行效率低------------使用explain/desc命令分析

SQL 语句的。这条命令并没有提供任何调整建议,但它能够提供重要的信息帮助你做出调优决策。

数学函数
BIN (decimal_number ) //十进制转二进制
CEILING (number2 ) //向上取整
FLOOR (number2 ) //向下取整
MAX(num1 ,num2) //取最大值
MIN(num1,num2) //取最小值
SQRT(number2) //开平方
RAND() //返回0-1内的随机值

  • 相关列简单解释:type、table、select_type...

1 语法

日期函数
CURDATE() //返回当前日期
CURTIME() //返回当前时间
NOW() //返回当前的日期时间
UNIX_TIMESTAMP(date) //返回当前date的UNIX日间戳
FROM_UNIXTIME() //返回UNIX时间戳的日期值
WEEK(date) //返回日期date为一年中的第几周
YEAR(date) //返回日期date的年份
DATEDIFF(expr,expr2) //返回起始时间expr和结束时间expr2间天数

4、对症下药采取优化措施-----------举例采取index进行优化

MySQL 的EXPLAIN 语法可以运行在SELECT 语句或者特定表上。如果作用在表上,那么此命令等同于DESC 表命令。UPDATE

mysql预处理语句
设置stmt1预处理,传递一个数据作为一个where判断条件
mysql> prepare stmt1 from 'select * from t1 where id>?';
设置一个变量
mysql> set @i=1;
执行stmt1预处理
mysql> execute stmt1 using @i;
设置@i为5
mysql> set @i=5;
再次去执行stmt1
mysql> execute stmt1 using @i;
如何删除预处理stmt1
mysql> drop prepare stmt1;
mysql事务处理
--关闭自动提交功能
mysql> set autocommit=0;
--从表t1中删除了一条记录
mysql> delete from t1 where id=11;
--此时做一个p1还原点:
mysql> savepoint p1;
--再次从表t1中删除一条记录:
mysql> delete from t1 where id=10;
--再次做一个p2还原点:
mysql> savepoint p2;
--此时恢复到p1还原点,当然后面的p2这些还原点自动会失效:
mysql> rollback to p1;
--退回到最原始的还原点:
mysql> rollback ;

  • 如何使用索引?
  • 使用索引应该注意的事项
  • 查看索引使用情况

和DELETE 命令也需要进行性能改进,当这些命令不是直接在表的主码上运行时,为了确保最优化的索引使用率,需要把它们改

mysql存储 (www.jbxue.com 脚本学堂)
创建一个存储p1()
mysql> d //
mysql> create procedure p1()
-> begin
-> set @i=0;
-> while @i<10 do
-> select @i;
-> set @i=@i+1;
-> end while;
-> end;
-> //

主要参考资料:《深入浅出MySQL》,

写成SELECT 语句(以便对它们执行EXPLAIN 命令)。请看下面的示例:

执行存储p1()
mysql> d ;
mysql> call p1();
--查看procedure p1()的status信息
mysql> show procedure statusG
--查看procedure p1()的具体信息:
mysql> show create procedure p1G
mysql触发器
修改delimiter为//
mysql> d //
创建一个名字为tg1的触发器,当向t1表中插入数据时,就向t2表中插入一条数据

 

UPDATE table1

mysql> create trigger tg1 before insert on t1 for each ro
>begin
>insert into t2(id) values(new.id);
>end//
--准备两个空表t1和t2
mysql> select * from t1;
mysql> select * from t2;
--向t1表中插入多条数据:
mysql> insert into t1 values(1),(2),(3),(4);


SET col1 = X, col2 = Y

如何制作删除表t1后t2表中的记录也会跟着删除呢
mysql>d //
mysql> create trigger tg2 beforedelete on t1 for each row
>begin delete from t2 where id=old.id;
>end//
mysql>d ;
如何制作更改表t1后t2表中的记录跟着个性呢
mysql>d //
mysql> create trigger tg3 beforeupdate on t1 for each row
>begin update t2 set id=new.id where id=old.id;
>end//
mysql>d ;
查看触发器
mysql> show triggers;

 

WHERE id1 = 9

重排auto_increment值
MYSQL数据库自动增长的ID如何恢复,清空表的时候。不能用

一、查看SQL执行频率

  使用show [session|gobal] status命令了解SQL执行频率、线程缓存内的线程的数量、当前打开的连接的数量、获得的表的锁的次数等。

比如执行show status like 'Com_%'查看每个语句执行的次数即频率,其中Com_xxx中xxx表示就是语句,比如Com_select:执行select操作的次数。

 1 mysql> use test;
 2 Database changed
 3 mysql> show status like 'Com_%';
 4 +-----------------------------+-------+
 5 | Variable_name               | Value |
 6 +-----------------------------+-------+
 7 | Com_admin_commands          | 0     |
 8 | Com_assign_to_keycache      | 0     |
 9 | Com_alter_db                | 0     |
10 | Com_alter_db_upgrade        | 0     |
11 | Com_alter_event             | 0     |
12 | Com_alter_function          | 0     |
13 | Com_alter_instance          | 0     |
14 | Com_alter_procedure         | 0     |
15 | Com_alter_server            | 0     |
16 | Com_alter_table             | 0     |
17 | Com_alter_tablespace        | 0     |
18 | Com_alter_user              | 0     |
19 | Com_analyze                 | 0     |
20 | Com_begin                   | 0     |
21 | Com_binlog                  | 0     |
22 | Com_call_procedure          | 0     |
23 | Com_change_db               | 2     |
24 | Com_change_master           | 0     |
25 | Com_change_repl_filter      | 0     |
26 | Com_check                   | 0     |
27 | Com_checksum                | 0     |
28 | Com_commit                  | 0     |
29 | Com_create_db               | 0     |
30 | Com_create_event            | 0     |
31 | Com_create_function         | 0     |
32 | Com_create_index            | 0     |
  ..............................

比如执行show status like 'slow_queries'查看慢查询次数(黑人问号??什么是慢查询呢?就是通过设置查询时间阈值long_query_time(0-10s)并打开开关show_query_log(1=OFF/0=ON),当超过这个阈值的查询都称之为慢查询,通常用来划分执行SQL效率)

mysql> show status like 'slow_queries';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Slow_queries  | 0     |
+---------------+-------+
1 row in set

比如执行show status like 'uptime'查看服务工作时间(即运行时间):

mysql> show status like 'uptime';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Uptime        | 21645 |
+---------------+-------+
1 row in set

比如执行show status like 'connections'查看MySQL连接数:

mysql> show status like 'connections';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Connections   | 6     |
+---------------+-------+
1 row in set

  通过show [session|gobal] status命令很清楚地看到哪些SQL执行效率不如人意,但是具体是怎么个不如意法,还得继续往下看,使用EXPLAIN命令分析具体的SQL语句

AND dt >= '2010-01-01';

delete from tablename;
而是要用:

 二、定位效率低的SQL

  上面也提到过慢查询这个概念主要是用来划分效率低的SQL,但是慢查询是在整个查询结束后才记录的,所以光是靠慢查询日志是跟踪不了效率低的SQL。一般有两种方式定位效率低的SQL:

  1、通过慢查询日志查看效率低的SQL语句,慢查询日志是通过show_query_log_file指定存储路径的,里面记录所有超过long_query_time的SQL语句(关于日志的查看,日后再一步研究学习),但是需要慢查询日志的产生是在查询结束后才有的。

  2、通过show processlist命令查看当前MySQL进行的线程,可以看到线程的状态信息

mysql> show processlist;
+----+------+-----------------+------+---------+------+----------+------------------+
| Id | User | Host            | db   | Command | Time | State    | Info             |
+----+------+-----------------+------+---------+------+----------+------------------+
|  2 | root | localhost:58377 | NULL | Sleep   | 2091 |          | NULL             |
|  3 | root | localhost:58382 | test | Sleep   | 2083 |          | NULL             |
|  4 | root | localhost:58386 | test | Sleep   | 2082 |          | NULL             |
|  5 | root | localhost:59092 | test | Query   |    0 | starting | show processlist |
+----+------+-----------------+------+---------+------+----------+------------------+
4 rows in set

  其中主要的是state字段,表示当前SQL语句线程的状态,如Sleeping 表示正在等待客户端发送新请求,Sending data把查询到的data结果发送给客户端等等,具体请看

这个UPDATE语句可以被重写成为下面这样的SELECT语句:

truncatetable tablename;
这样auto_increment 就恢复成1了

三、 查看分析效率低的SQL

  MYSQL 5.6.3以前只能EXPLAIN SELECT; MYSQL5.6.3以后就可以EXPLAIN SELECT,UPDATE,DELETE,现在我们先创建一个user_table的表,之后分析select* from user where name=''语句

mysql> create table user(id int, name varchar(10),password varchar(32),primary key(id))engine=InnoDB;
Query OK, 0 rows affected

之后插入三条数据:

mysql> insert into user values(1,'Zhangsan',replace(UUID(),'-','')),(2,'Lisi',replace(UUID(),'-','')),(3,'Wangwu',replace(UUID(),'-',''));
Query OK, 3 rows affected
Records: 3  Duplicates: 0  Warnings: 0
mysql> select* from user;
+----+----------+----------------------------------+
| id | name     | password                         |
+----+----------+----------------------------------+
|  1 | Zhangsan | 2d7284808e5111e8af74201a060059ce |
|  2 | Lisi     | 2d73641c8e5111e8af74201a060059ce |
|  3 | Wangwu   | 2d73670c8e5111e8af74201a060059ce |
+----+----------+----------------------------------+
3 rows in set

下面以分析select*from user where name='Lisi'语句为例:

mysql> explain select*from user where name='Lisi';
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | user  | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    3 |    33.33 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set

 

下面讲解select_type等常见列的含义的:

(1)select_type:表示SELECT的类型,主要有:

  • SIMPLE:简单表,没有表连接或者子查询
  • PRIMARY:主查询,即最外城的查询
  • UNION:UNION中的第二个或者后面的语句
  • SUBQUERY:子查询中的第一个SELECT

(2)table:结果输出的表

(3)type:表示表的连接类型,性能由好到差为:

  • system:常量表
  • const:单表中最多有一行匹配,比如primary key,unique index
  • eq_ref:多表连接中使用primary key,unique index
  • ref:使用普通索引
  • ref_or_null:与ref类似,但是包含了NULL查询
  • index_merge:索引合并优化
  • unique_subquery:in后面是一个查询主键字段的子查询
  • index_subquery:in后面是非唯一索引字段的子查询
  • range:单表中范围查看,使用like模糊查询
  • index:对于后面每一行都通过查询索引得到数据
  • all:表示全表查询

(3)possible_key:查询时可能使用的索引

(4)key:表示实际使用的索引

(5)key_len:索引字段的长度

(6)rows:查询时实际扫描的行数

(7)Extra:执行情况的说明和描述

(8)partitions:分区数目

(9)filtered:查询过滤的表占的百分比,比如这里查询的记录是name=Lisi的记录,占三条记录的33.3%

SELECT col1, col2

或者清空内容后直接用ALTER命令修改表:

四、 关于索引的优化

FROM table1

altertable tablename auto_increment =1;

1、使用索引优化的举例

  上个例子我们看到到执行explain select*from user where name='Lisi',扫描了3行(全部行数)使用了全表搜索all。如果实际业务中name是经常用到查询的字段(是指经常跟在where后的字段,不是select后的字段)并且数据量很大的情况呢?这时候就需要索引了(索引经常用到where后面的字段比select后面的字段效果更好,或者说就是要使用在where后面的字段上)

增加name前缀索引(这里只是举例,并没有选择最合适的前缀):

mysql> create index index_name on user(name(2));
Query OK, 0 rows affected
Records: 0  Duplicates: 0  Warnings: 0

执行explain分析

mysql> explain select*from user where name = 'Lisi';
+----+-------------+-------+------------+------+---------------+------------+---------+-------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key        | key_len | ref   | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | user  | NULL       | ref  | index_name    | index_name | 9       | const |    1 |      100 | Using where |
+----+-------------+-------+------------+------+---------------+------------+---------+-------+------+----------+-------------+
1 row in set

  可以看到type变为ref、rows降为1(实际上只要使用了索引都是1),filtered过滤百分比为100%,实际用到的索引为index_name。如果数据量很大的话使用索引就是很好的优化措施,对于如何选择索引,什么时候用索引,我做出了如下总结:

WHERE id1 = 9

利用GROUP BY的WITH ROLLUP
mysql> select * from demo;
+-------+-------+
| cname | pname |
+-------+-------+
| bj | hd |
| bj | xc |
| bj | hd |
| sh | dh |
| sh | rg |
| sh | dh |
+-------+-------+
9 rows in set (0.00 sec)

2、如何高效使用索引?

  (1) 创建多列索引时,**只要查询条件中用到最左边的列,索引一般都会被用到**

  我们创建一张没有索引的表user_1:

mysql> show create table 
user_1;
+--------+--------------------------------------------------------------------------------------------------------------------------+
| Table  | Create Table                                                                                                             |
+--------+--------------------------------------------------------------------------------------------------------------------------+
| user_1 | CREATE TABLE `user_1` (
  `id` int(11) DEFAULT NULL,
  `name` varchar(10) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+--------+--------------------------------------------------------------------------------------------------------------------------+
 1 row in set

 之后同样插入数据:

mysql> select *from user_1;
+----+----------+
| id | name     |
+----+----------+
|  1 | Zhangsan |
|  2 | Lisi     |
+----+----------+
2 rows in set

 创建多列索引index_id_name

mysql> create index index_id_name on user_1(id,name);
Query OK, 0 rows affected
Records: 0  Duplicates: 0  Warnings: 0

 实验查询explain分析name与id

mysql> explain select * from user_1 where id=1;
+----+-------------+--------+------------+------+---------------+---------------+---------+-------+------+----------+-------------+
| id | select_type | table  | partitions | type | possible_keys | key           | key_len | ref   | rows | filtered | Extra       |
+----+-------------+--------+------------+------+---------------+---------------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | user_1 | NULL       | ref  | index_id_name | index_id_name | 5       | const |    1 |      100 | Using index |
+----+-------------+--------+------------+------+---------------+---------------+---------+-------+------+----------+-------------+
1 row in set

mysql> explain select * from user_1 where name='Lisi';
+----+-------------+--------+------------+-------+---------------+---------------+---------+------+------+----------+--------------------------+
| id | select_type | table  | partitions | type  | possible_keys | key           | key_len | ref  | rows | filtered | Extra                    |
+----+-------------+--------+------------+-------+---------------+---------------+---------+------+------+----------+--------------------------+
|  1 | SIMPLE      | user_1 | NULL       | index | NULL          | index_id_name | 38      | NULL |    2 |       50 | Using where; Using index |
+----+-------------+--------+------------+-------+---------------+---------------+---------+------+------+----------+--------------------------+
1 row in set

  可以看到使用最左列id的时候,rows为1,并且Extra明确使用了index,key的值为id_name_index,type的值为ref,而where不用到id,而是name的话,rows的值为2。filtered为50%,虽然key是index_id_name,但是表明是索引(个人理解,应该不太准确)

  (2) 使用like的查询,只有%不是第一个字符并且%后面是常量的情况下,索引才可能会被使用。

   执行explain select *from user where name like ‘%Li’后type为ALL且key的值为NULL,执行explain select *from user where name like ‘Li%’后key值不为空为index_name。

mysql> explain select*from user where name like '%Li';
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | user  | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    3 |    33.33 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set
mysql> explain select*from user where name like 'Li%';
+----+-------------+-------+------------+-------+---------------+------------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key        | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+------------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | user  | NULL       | range | index_name    | index_name | 9       | NULL |    1 |      100 | Using where |
+----+-------------+-------+------------+-------+---------------+------------+---------+------+------+----------+-------------+
1 row in set

  (3) 如果对打的文本进行搜索,使用全文索引而不是用like ‘%...%’(只有MyISAM支持全文索引)。

  (4) 如果列名是索引,使用column_name is null将使用索引。

mysql> explain select*from user where name is null;
+----+-------------+-------+------------+------+---------------+------------+---------+-------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key        | key_len | ref   | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | user  | NULL       | ref  | index_name    | index_name | 9       | const |    1 |      100 | Using where |
+----+-------------+-------+------------+------+---------------+------------+---------+-------+------+----------+-------------+
1 row in set

mysql> explain select*from user where password
 is null;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | user  | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    3 |    33.33 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set

AND dt >= '2010-01-01';

对demo表按照cname、pname列分组对pname列进行聚合计算如下
mysql> select cname,pname,count(pname) from demo group by
cname,pname;
+-------+-------+--------------+
| cname | pname | count(pname) |
+-------+-------+--------------+
| bj | hd | 3 |
| bj | xc | 2 |
| sh | dh | 3 |
| sh | rg | 1 |
+-------+-------+--------------+
4 rows in set (0.00 sec)

3、哪些情况下即使有索引也用不到?

  (1) MySQL使用MEMORY/HEAP引擎(使用的HASH索引),并且WHERE条件中不会使用”=”,in等进行索引列,那么不会用到索引(这是关于引擎部分特点,之后会介绍)。

  (2) 用OR分隔开的条件,如果OR前面的条件中的列有索引,而后面的列没有索引,那么涉及到的列索引不会被使用。

  执行命令show index from user可以看出password字段并没有使用任何索引,而id使用了两个索引,但是where id=1 or password='2d7284808e5111e8af74201a060059ce' 导致没有使用id列的primary索引与id_name_index索引

mysql> show index from user;
+-------+------------+---------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name      | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+---------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| user  |          0 | PRIMARY       |            1 | id          | A         |           3 | NULL     | NULL   |      | BTREE      |         |               |
| user  |          1 | index_name    |            1 | name        | A         |           3 |        2 | NULL   | YES  | BTREE      |         |               |
| user  |          1 | id_name_index |            1 | id          | A         |           3 | NULL     | NULL   |      | BTREE      |         |               |
| user  |          1 | id_name_index |            2 | name        | A         |           3 | NULL     | NULL   | YES  | BTREE      |         |               |
+-------+------------+---------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
4 rows in set

mysql> explain select*from user where id=1 or password='2d7284808e5111e8af74201a060059ce';
+----+-------------+-------+------------+------+-----------------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys         | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+------+-----------------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | user  | NULL       | ALL  | PRIMARY,id_name_index | NULL | NULL    | NULL |    3 |    55.56 | Using where |
+----+-------------+-------+------------+------+-----------------------+------+---------+------+------+----------+-------------+
1 row in set

  (3) 不是用到复合索引中的第一列即最左边的列的话,索引就不起作用(上面已经介绍)。

  (4) 如果like是以%开头的(上面已经介绍)

  (5) 如果列类型是字符串,那么where条件中字符常量值不用’’引号引起来的话,那就不会失去索引效果,这是因为MySQL会把输入的常量值进行转换再使用索引。

  select * from user_1 where name =250,其中name的索引为name_index,并且是varchar字符串类型,但是并没有将250用引号变成’250’,那么explain之后的ref仍然为NULL,rows为3

mysql> show index from user_1;
+--------+------------+---------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table  | Non_unique | Key_name      | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+--------+------------+---------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| user_1 |          1 | index_id_name |            1 | id          | A         |           2 | NULL     | NULL   | YES  | BTREE      |         |               |
| user_1 |          1 | index_id_name |            2 | name        | A         |           2 | NULL     | NULL   | YES  | BTREE      |         |               |
| user_1 |          1 | name_index    |            1 | name        | A         |           3 |        5 | NULL   | YES  | BTREE      |         |               |
+--------+------------+---------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
3 rows in set

mysql> explain select*from user_1 where name=250;
+----+-------------+--------+------------+-------+---------------+---------------+---------+------+------+----------+--------------------------+
| id | select_type | table  | partitions | type  | possible_keys | key           | key_len | ref  | rows | filtered | Extra                    |
+----+-------------+--------+------------+-------+---------------+---------------+---------+------+------+----------+--------------------------+
|  1 | SIMPLE      | user_1 | NULL       | index | name_index    | index_id_name | 38      | NULL |    3 |    33.33 | Using where; Using index |
+----+-------------+--------+------------+-------+---------------+---------------+---------+------+------+----------+--------------------------+
1 row in set

mysql> explain select*from user_1 where name='250';
+----+-------------+--------+------------+------+---------------+------------+---------+-------+------+----------+-------------+
| id | select_type | table  | partitions | type | possible_keys | key        | key_len | ref   | rows | filtered | Extra       |
+----+-------------+--------+------------+------+---------------+------------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | user_1 | NULL       | ref  | name_index    | name_index | 18      | const |    1 |      100 | Using where |
+----+-------------+--------+------------+------+---------------+------------+---------+-------+------+----------+-------------+
1 row in set

 

在5.6.10版本里面,是可以直接对dml语句进行explain分析操作的.

同样使用with rollup关键字后,统计出更多的信息,如下。注意:with rollup不可以和ordery by同时使用
ysql> select cname,pname,count(pname) from demo group by cname,pname
with rollup;
+-------+-------+--------------+
| cname | pname | count(pname) |
+-------+-------+--------------+
| bj | hd | 3 |
| bj | xc | 2 |
| bj | NULL | 5 |
| sh | dh | 3 |
| sh | rg | 1 |
| sh | NULL | 4 |
| NULL | NULL | 9 |
+-------+-------+--------------+
7 rows in set (0.00 sec)

4、查看索引的使用情况

执行show status like ‘Handler_read%’可以看到一个值Handler_read_key,它代表一行被索引值读的次数,如果值很低说明增加索引得到的性能改善不高,因为索引并不经常使用。

mysql> show status like 'Handler_read%' ;
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| Handler_read_first    | 3     |
| Handler_read_key      | 5     |
| Handler_read_last     | 0     |
| Handler_read_next     | 0     |
| Handler_read_prev     | 0     |
| Handler_read_rnd      | 0     |
| Handler_read_rnd_next | 20    |
+-----------------------+-------+
7 rows in set

(1)Handler_read_first:索引中第一条被读的次数。如果较高,它表示服务器正执行大量全索引扫描;

(2)Handler_read_key:如果索引正在工作,这个值代表一个行被索引值读的次数,如果值越低,表示索引得到的性能改善不高,因为索引不经常使用。

(3)Handler_read_next :按照键顺序读下一行的请求数。如果你用范围约束或如果执行索引扫描来查询索引列,该值增加。

(4)Handler_read_prev:按照键顺序读前一行的请求数。该读方法主要用于优化ORDER BY ... DESC。

(5)Handler_read_rnd :根据固定位置读一行的请求数。如果你正执行大量查询并需要对结果进行排序该值较高。你可能使用了大量需要MySQL扫描整个表的查询或你的连接没有正确使用键。这个值较高,意味着运行效率低,应该建立索引来补救。

(6)Handler_read_rnd_next:在数据文件中读下一行的请求数。如果你正进行大量的表扫描,该值较高。通常说明你的表索引不正确或写入的查询没有利用索引。

   注:以上6点来自于网络总结,其中比较重要的两个参数是Handler_read_key与Handler_read_rnd_next。

MySQL 优化器是基于开销来工作的,它并不提供任何的QEP的位置。这意味着QEP 是在每条SQL 语句执行的时候动态地计

使用外键需要注意的问题
创建外键的方式
mysql>create table temp( id int, name char(20), foreign key(id)
references outTable(id) on delete cascade on update cascade);
注意:Innodb类型的表支持外键,myisam类型的表,虽然创建外键可以成功,但是不起作用,主要原因是不支持外键。
优化SQL语句的一般步骤
通过show status命令了解各种SQL的执行频率
mysql> show [session|global]status;
其中:session(默认)表示当前连接,global表示自数据库启动至今
mysql>show status;
mysql>show global status;
mysql>show status like ‘Com_%';
mysql>show global status like ‘Com_%';
参数说明:
Com_XXX表示每个XXX语句执行的次数如:
Com_select 执行select操作的次数,一次查询只累计加1
Com_update 执行update操作的次数
Com_insert 执行insert操作的次数,对批量插入只算一次。
Com_delete 执行delete操作的次数
只针对于InnoDB存储引擎的:
InnoDB_rows_read 执行select操作的次数
InnoDB_rows_updated 执行update操作的次数
InnoDB_rows_inserted 执行insert操作的次数
InnoDB_rows_deleted 执行delete操作的次数
其他:
connections 连接mysql的数量
Uptime 服务器已经工作的秒数
Slow_queries:慢查询的次数
定位执行效率较低的SQL语句
explain select * from table where id=1000;
desc select * from table where id=1000;
通过EXPLAIN分析较低效SQL的执行计划
mysql> explain select count(*) from stu where name like "a%"G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: stu
type: range
possible_keys: name,ind_stu_name
key: name
key_len: 50
ref: NULL
rows: 8
Extra: Using where; Using index
1 row in set (0.00 sec)

算出来的。在MySQL 存储过程中的SQL 语句也是在每次执行时计算QEP 的。存储过程缓存仅仅解析查询树。

每一列的简单解释

2 各列详解

id: 1
select_type: SIMPLE 表示select的类型,常见的取值有SIMPLE()简单表,即不使用表连接或者子查询)、PRIMARY(主查询,即外层的查询)、UNION(UNION中的第二个或者后面的查询语句)、SUBQUERY(子查询中的第一个SESECT)等
table: stu 输出结果集的表
type: range 表示表的连接类型,性能有好到差:system(表仅一行)、const(只一行匹配)、eq_ref(对于前面的每一行使用主键和唯一)、ref(同eq_ref,但没有使用主键和唯一)、ref_or_null(同前面对null查询)、index_merge(索引合并优化)、unique_subquery(主键子查询)、index_subquery(非主键子查询)、range(表单中的范围查询)、index(都通过查询索引来得到数据)、all(通过全表扫描得到的数据)
possible_keys: name,ind_stu_name 表查询时可能使用的索引。
key: name 表示实际使用的索引。
key_len: 50 索引字段的长度
ref: NULL
rows: 8 扫描行的数量
Extra: Using where; Using index 执行情况的说明和描述
索引问题
MyISAM存储引擎的表的数据和索引是自动分开存储的,各自是独一的一个文件;InnoDB存储引擎的表的数据和索引是存储在同一个表空间里面,但可以有多个文件组成。MySQL目前不支持函数索引,但是能对列的前面某一部分进行索引,例如name字段,可以只取name的前4个字符进行索引,这个特性可以大大缩小索引文件的大小,用户在设计表结构的时候也可以对文本列根据此特性进行灵活设计。
mysql>create index ind_company2_name on company2(name(4));
--其中company表名ind_company2_name索引名
MySQL如何使用索引
1、使用索引
(1)对于创建的多列索引,只要查询的条件中用到最左边的列,索引一般就会被使用。如下创建一个复合索引。
mysql>create index ind_sales2_com_mon on sales2(company_id,moneys);
然后按company_id进行查询,发现使用到了复合索引
mysql>explain select * from sales2 where company_id=2006G
使用下面的查询就没有使用到复合索引。
mysql>explain select * from sales2 where moneys=1G
(2) 使用like的查询,后面如果是常量并且只有%号不在第一个字符,索引才可能会被使用,如下:
mysql> explain select * from company2 where name like "%3"G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: company2
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命令能够为SQL语句中的每个表生成以下信息:

如下这个使用到了索引,而下面例子能够使用索引,区别就在于“%”的位置不同,上面的例子是吧“%”放在了第一位,而下面的例子则没有
mysql> explain select * from company2 where name like "3%"G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: company2
type: range
possible_keys: ind_company2_name
key: ind_company2_name
key_len: 11
ref: NULL
rows: 103
Extra: Using where
1 row in set (0.00 sec)

mysql> EXPLAIN SELECT * FROM inventory WHERE item_id = 16102176G;

(3)如果对大的文本进行搜索,使用全文索引而不使用like“%...%”.
(4)如果列名是索引,使用column_name is null将使用索引。如下
mysql> explain select * from company2 where name is nullG
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: company2
type: ref
possible_keys: ind_company2_name
key: ind_company2_name
key_len: 11
ref: const
rows: 1
Extra: Using where
1 row in set (0.00 sec)

  ********************* 1. row ***********************

存在索引但不使用索引
(1)如果MySQL估计使用索引比全表扫描更慢,则不使用索引。例如如果列key_part1均匀分布在1到100之间,查询时使用索引就不是很好
mysql>select * from table_name where key_part1>1 and key_part<90;
(2)如果使用MEMORY/HEAP表并且where条件中不使用“=”进行索引列,那么不会用到索引。Heap表只有在“=”的条件下会使用索引。
(3)用or分割开的条件,如果or前的条件中的列有索引,而后面的列中没有索引,www.jbxue.com 那么涉及的索引都不会被用到。
mysql>show index from salesG
*************************** 1. row ***************************
……
key_name: ind_sales_year
seq_in_index:1
Column_name: year
……

  id: 1

从上面可以发现只有year列上面有索引。来看如下的执行计划。
mysql> explain select * from sales where year=2001 or country=‘China'G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: sales
type: ALL
possible_keys: ind_sales_year
key: NULL
key_len: NULL
ref: NULL
rows: 12
Extra: Using where
1 row in set (0.00 sec)

  select_type: SIMPLE

(4)如果不是索引列的第一部分,如下例子:可见虽然在money上面建有复合索引,但是由于money不是索引的第一列,那么在查询中这个索引也不会被MySQL采用。
mysql> explain select * from sales2 where moneys=1 G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: sales2
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 1000
Extra: Using where
1 row in set (0.00 sec)

  table: inventory

(5)如果like是以%开始,可见虽然在name上面建有索引,但是由于where 条件中like的值的“%”在第一位了,那么MySQL也会采用这个索引。
(6)如果列类型是字符串,但在查询时把一个数值型常量赋值给了一个字符型的列名name,那么虽然在name列上有索引,但是也没有用到。
mysql> explain select * from company2 where name name=294G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: company2
type: ALL
possible_keys: ind_company2_name
key: NULL
key_len: NULL
ref: NULL
rows: 1000
Extra: Using where
1 row in set (0.00 sec)

  type: ALL

而下面的sql语句就可以正确使用索引。
mysql> explain select * from company2 where name name=‘294'G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: company2
type: ref
possible_keys: ind_company2_name
key: ind_company2_name
key_len: 23
ref: const
rows: 1
Extra: Using where
1 row in set (0.00 sec)

  possible_keys: NULL

查看索引使用情况
如果索引正在工作,Handler_read_key的值将很高,这个值代表了一个行被索引值读的次数。
Handler_read_rnd_next的值高则意味着查询运行低效,并且应该建立索引补救。
mysql> show status like 'Handler_read%';
+-----------------------+-------+
| Variable_name | Value |
+-----------------------+-------+
| Handler_read_first | 0 |
| Handler_read_key | 5 |
| Handler_read_next | 0 |
| Handler_read_prev | 0 |
| Handler_read_rnd | 0 |
| Handler_read_rnd_next | 2055 |
+-----------------------+-------+
6 rows in set (0.00 sec)

  key: NULL

两个简单实用的优化方法
分析表的语法如下:(检查一个或多个表是否有错误)
mysql> CHECK TABLE tbl_name[,tbl_name] …[option] …option =
{ QUICK | FAST | MEDIUM| EXTENDED | CHANGED}
mysql> check table sales;
+--------------+-------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+--------------+-------+----------+----------+
| sakila.sales | check | status | OK |
+--------------+-------+----------+----------+
1 row in set (0.01 sec)

  key_len: NULL

优化表的语法格式:
OPTIMIZE [LOCAL | NO_WRITE_TO_BINLOG] TABLE tbl_name [,tbl_name]
如果已经删除了表的一大部分,或者如果已经对含有可变长度行的表进行了很多的改动,则需要做定期优化。这个命令可以将表中的空间碎片进行合并,但是此命令只对MyISAM、BDB和InnoDB表起作用。
mysql> optimize table sales;
+--------------+----------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+--------------+----------+----------+----------+
| sakila.sales | optimize | status | OK |
+--------------+----------+----------+----------+
1 row in set (0.05 sec)

  ref: NULL

常用SQL的优化
大批量插入数据
当用load命令导入数据的时候,适当设置可以提高导入的速度。
对于MyISAM存储引擎的表,可以通过以下方式快速的导入大量的数据。
ALTER TABLE tbl_name DISABLE KEYS
loading the data
ALTER TABLE tbl_name ENABLE KEYS
DISABLE KEYS 和ENABLE KEYS 用来打开或关闭MyISAM表非唯一索引的更新,可以提高速度,注意:对InnoDB表无效。

  rows: 787338

--没有使用打开或关闭MyISAM表非唯一索引:
mysql> load data infile ‘/home/mysql/film_test.txt'into table film_test2;
Query OK,529056 rows affected (1 min 55.12 sec)
Records:529056 Deleted:0 Skipped:0 Warnings:0
--使用打开或关闭MyISAM表非唯一索引:
mysql> alter table film_test2 disable keys;
Query OK,0 rows affected (0.0 sec)
mysql> load data infile ‘/home/mysql/film_test.txt'into table film_test2;
Query OK,529056 rows affected (6.34 sec)
Records:529056 Deleted:0 Skipped:0 Warnings:0
mysql> alter table film_test2 enable keys;
Query OK,0 rows affected (12.25 sec)
--以上对MyISAM表的数据导入,但对于InnoDB表并不能提高导入数据的效率
(1)针对于InnoDB类型表数据导入的优化
因为InnoDB表的按照主键顺序保存的,所以将导入的数据主键的顺序排列,可以有效地提高导入数据的效率。

  Extra: Using where

--使用test3.txt文本是按表film_test4主键存储顺序保存的
mysql> load data infile ‘/home/mysql/film_test3.txt'into table film_test4;
Query OK, 1587168 rows affected (22.92 sec)
Records:1587168 Deleted:0 Skipped:0 Warnings:0
--使用test3.txt没有任何顺序的文本(效率慢了1.12倍)
mysql> load data infile ‘/home/mysql/film_test4.txt'into table film_test4;
Query OK, 1587168 rows affected (31.16 sec)
Records:1587168 Deleted:0 Skipped:0 Warnings:0
(2)关闭唯一性效验可以提高导入效率
在导入数据前先执行set unique_checks=0,关闭唯一性效验,在导入结束后执行set unique_checks=1,恢复唯一性效验,可以提高导入效率。

  

--当unique_checks=1时
mysql> load data infile ‘/home/mysql/film_test3.txt'into table film_test4;
Query OK,1587168 rows affected (22.92 sec)
Records:1587168 Deleted:0 Skipped:0 Warnings:0
--当unique_checks=0时
mysql> load data infile ‘/home/mysql/film_test3.txt'into table film_test4;
Query OK,1587168 rows affected (19.92 sec)
Records:1587168 Deleted:0 Skipped:0 Warnings:0
(3)关闭自动提交可以提高导入效率
在导入数据前先执行set autocommit=0,关闭自动提交事务,在导入结束后执行set autocommit=1,恢复自动提交,可以提高导入效率。

这个QEP 显示没有使用任何索引(也就是全表扫描)并且处理了大量的行来满足查询。对同样一条SELECT 语句,一个优化过的QEP 如下所示:

--当autocommit=1时
mysql> load data infile ‘/home/mysql/film_test3.txt'into table film_test4;
Query OK,1587168 rows affected (22.92 sec)
Records:1587168 Deleted:0 Skipped:0 Warnings:0
--当autocommit=0时
mysql> load data infile ‘/home/mysql/film_test3.txt'into table film_test4;
Query OK,1587168 rows affected (20.87 sec)
Records:1587168 Deleted:0 Skipped:0 Warnings:0
优化insert语句
尽量使用多个值表的insert语句,这样可以大大缩短客户与数据库的连接、关闭等损耗。
可以使用insert delayed(马上执行)语句得到更高的效率。
将索引文件和数据文件分别存放不同的磁盘上。www.jbxue.com
可以增加bulk_insert_buffer_size 变量值的方法来提高速度,但是只对MyISAM表使用当从一个文件中装载一个表时,使用LOAD DATA INFILE。这个通常比使用很多insert语句要快20倍。
优化group by语句
如果查询包含group by但用户想要避免排序结果的损耗,则可以使用使用order by null来禁止排序:
如下没有使用order by null来禁止排序
mysql> explain select id,sum(moneys) from sales2 group by idG
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: sales2
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 1000
Extra: Using temporary;Using filesort
1 row in set (0.00 sec)

  ********************* 1. row ***********************

如下使用order by null的效果:
mysql> explain select id,sum(moneys) from sales2 group by id order by nullG
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: sales2
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 1000
Extra: Using temporary
1 row in set (0.00 sec)

  id: 1

优化嵌套查询
下面是采用嵌套查询的效果(可以使用更有效的链接查询(Join)替代)。
mysql> explain select * from sales2 where company_id not in(select id
from company2)G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: sales2
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 1000
Extra: Using where
1 row in set (0.00 sec)
*************************** 2. row ***************************
id: 2
select_type: SIMPLE
table: company2
type: index_subquery
possible_keys: ind_company2_id
key: ind_company2_id
key_len: 5
ref: func
rows: 2
Extra: Using index
1 row in set (0.00 sec)

  select_type: SIMPLE

下面是使用更有效的链接查询(Join)
mysql> explain select * from sales2 left join company2 on
sales2.company_id = company2.id where sales2.company_id is nullG
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: sales2
type: ALL
possible_keys: ind_sales2_companyid_moneys
key: ind_sales2_companyid_moneys
key_len: 5
ref: count
rows: 1
Extra: Using where
1 row in set (0.00 sec)
*************************** 2. row ***************************
id: 2
select_type: SIMPLE
table: company2
type: index_subquery
possible_keys: ind_company2_id
key: ind_company2_id
key_len: 5
ref: func
rows: 1
Extra:
1 row in set (0.00 sec)

  table: inventory

从执行计划中可以明显看出查询扫描的记录范围和使用索引的情况都有了很大的改善。连接(JOIN)子所以更有效率一些,是因为MySQL不需要再内存中创建临时表来完成这个逻辑上的需要两个步骤的查询工作。
数据库优化
优化表的类型
在MySQL中,可以使用函数PROCEDUREANALYSE()对当前应用的表进行分析,改函数可以对数据表中列的数据类型提出优化建议,用户可以根据应用的实际情况酌情考虑是否实施优化。
mysql> select * from duck_cust procedure analyse()G
*************************** 1. row ***************************
Field_name: sakila.duch_cust.cust_num
Min_value: 1
Max_value: 6
Min_length: 1
Max_length: 1
Empties_or_zeros: 0
Nulls: 0
Avg_value_or_avg_length: 3.5000
Std: 1.7078
Optimal_fieldtype: ENUM(‘1',‘2',‘3',‘4') NOT NULL
*************************** 2. row ***************************
……

  type: ref

大存储量解决
1.分库分表
2.分区
主要目的:
1.减少表的记录数
2.减小对操作系统的负担压力
中间表
中间表的产生:
1.view 视图
2.重新生成一个新表
Mysql服务器优化
myisam读锁定

  possible_keys: item_id

  1. lock table t1 read
    2.开启另一个mysql连接终端,接着去尝试:
    select * from t1
    3.再insert、update和delete t1这张表,你会发现所有的数据都停留在终端上没有真正的去操作
    4.读锁定对我们在做备份大量数据时非常有用.
    mysqldump -uroot -p123 test >test.sql
    myisam写锁定
  2. lock table t1 write
    2.打开另一个mysql终端,尝试去select、insert、update和delete这张表t1,你会发现都不能操作,都会停留在终端上,只有等第一个终端操作完毕,第二个终端才能真正执行.
    3.可见表的写锁定比读锁定更严格
    4.一般情况下我们很少去显式的去对表进行read 和write锁定的,myisam会自动进行锁定的.
    Mysql服务器优化
    二进制日志
    1.log-bin=mysql-bin
    查看bin-log日志:
    mysql> show binary logs;
    查看最后一个bin-log日志:
    mysql> show master status;
    慢查询日志
    开户和设置慢查询时间:
    vi /etc/my.cnf
    log_slow_queries=slow.log
    long_query_time=5
    慢查询次数:
    mysql> show global status like "%quer%"
    socket问题
    mysql socket无法登录
    1. 有时登录mysql时提示不能用socket登录,此时可以换成tcp方式去登录,但是可以测试时可以这样用,但是必须要在php去用之前把这个事情解决了.
    [root@localhost mysql]# mysql -uroot -pwei --protocol tcp -hlocalhost
    Welcome to the MySQL monitor. Commands end with ; or g.
    Your MySQL connection id is 34
    Server version: 5.0.77-log Source distribution
    Type 'help;' or 'h' for help. Type 'c' to clear the buffer.
    mysql>
    这样就可以登录,这样就不用mysql.sock来登录,而mysql.sock是启动mysqld服务时产生的
    root密码丢失
    root密码丢失破解
    1.service mysqld stop
  3. mysqld_safe --skip-grant-tables --user=mysql &
    //跳过授权表mysql.user和mysql.db这些表
  4. mysql -uroot
  5. set password=password("wei");
    //用这一条语句结果报错,就是因为加了--skip-grant-tables
  6. mysql>update user set password=password("wei") where user='root'
    and host='localhost';
  7. mysql> set password for root@localhost=password("wei");
  8. mysql> set password=password("wei");
    //和第五步一样,都可能成功修改密码

  key: item_id

  key_len: 4

  ref: const

  rows: 1

  Extra:

在这个QEP 中,我们看到使用了一个索引,且估计只有一行数据将被获取。

QEP 中每个行的所有列表如下所示:

 id

 select_type

 table

 partitions(这一列只有在EXPLAIN PARTITIONS 语法中才会出现)

 possible_keys

 key

 key_len

 ref

 rows

 filtered(这一列只有在EXPLAINED EXTENDED 语法中才会出现)

 Extra

这些列展示了SELECT 语句对每一个表的QEP。一个表可能和一个物理模式表或者在SQL 执行时生成的内部临时表(例如从子查询或者合并操作会产生内部临时表)相关联。

 

2.1 key

 key 列指出优化器选择使用的索引。一般来说SQL 查询中的每个表都仅使用一个索引。也存在索引合并的少数例外情况,如给定表上用到了两个或者更多索引。

 下面是QEP 中key 列的示例:

 key: item_id

 key: NULL

 key: first, last

 SHOW CREATE TABLE <table>命令是最简单的查看表和索引列细节的方式。和key 列相关的列还包括possible_keys、rows 以及key_len。

2.2 ROWS

 rows 列提供了试图分析所有存在于累计结果集中的行数目的MySQL 优化器估计值。QEP 很容易描述这个很困难的统计量。

 查询中总的读操作数量是基于合并之前行的每一行的rows 值的连续积累而得出的。这是一种嵌套行算法。

 

 以连接两个表的QEP 为例。通过id=1 这个条件找到的第一行的rows 值为1,这等于对第一个表做了一次读操作。第二行是

 通过id=2 找到的,rows 的值为5。这等于有5 次读操作符合当前1 的积累量。参考两个表,读操作的总数目是6。在另一个QEP

 中,第一rows 的值是5,第二rows 的值是1。这等于第一个表有5 次读操作,对5个积累量中每个都有一个读操作。因此两个表

 总的读操作的次数是10(5+5)次。

 

 最好的估计值是1,一般来说这种情况发生在当寻找的行在表中可以通过主键或者唯一键找到的时候。

 在下面的QEP 中,外面的嵌套循环可以通过id=1 来找到,其估计的物理行数是1。第二个循环处理了10行。

 

 

 ********************* 1. row ***********************

 id: 1

 select_type: SIMPLE

 table: p

 type: const

 possible_keys: PRIMARY

 key: PRIMARY

 key_len: 4

 ref: const

 rows: 1

 Extra:

 ********************* 2. row ***********************

 id: 1

 select_type: SIMPLE

 table: c

 type: ref

 possible_keys: parent_id

 key: parent_id

 key_len: 4

 ref: const

 rows: 10

 Extra:

 

 可以使用SHOW STATUS 命令来查看实际的行操作。这个命令可以提供最佳的确认物理行操作的方式。请看下面的示例:

 mysql> SHOW SESSION STATUS LIKE 'Handler_read%';

  +-----------------------+-------+

  | Variable_name         | Value |

  +-----------------------+-------+

  | Handler_read_first    | 0     |

  | Handler_read_key      | 0     | 

  | Handler_read_last     | 0     |

  | Handler_read_next     | 0     |

  | Handler_read_prev     | 0     |

  | Handler_read_rnd      | 0     |

  | Handler_read_rnd_next | 11    |

  +-----------------------+-------+

  7 rows in set (0.00 sec)

  

 在下一个QEP 中,通过id=1 找到的外层嵌套循环估计有160行。第二个循环估计有1 行。

 ********************* 1. row ***********************

  id: 1

  select_type: SIMPLE

  table: p

  type: ALL

  possible_keys: NULL

  key: NULL

  key_len: NULL

  ref: NULL

  rows: 160

  Extra:

 ********************* 2. row ***********************

  id: 1

  select type: SIMPLE

  table: c

  type: ref

  possible_keys: PRIMARY,parent_id

  key: parent_id

  key_len: 4

  ref: test.p.parent_id

  rows: 1

  Extra: Using where

 

 通过SHOW STATUS 命令可以查看实际的行操作,该命令表明物理读操作数量大幅增加。请看下面的示例:

 mysql> SHOW SESSION STATUS LIKE 'Handler_read%';

 +--------------------------------------+---------+

 | Variable_name | Value |

 +--------------------------------------+---------+

 | Handler_read_first | 1 |

 | Handler_read_key | 164 |

 | Handler_read_last | 0 |

 | Handler_read_next | 107 |

 | Handler_read_prev | 0 |

 | Handler_read_rnd | 0 |

 | Handler_read_rnd_next | 161 |

 +--------------------------------------+---------+

 相关的QEP 列还包括key列。

 

 2.3 possible_keys

 possible_keys 列指出优化器为查询选定的索引。

 一个会列出大量可能的索引(例如多于3 个)的QEP 意味着备选索引数量太多了,同时也可能提示存在一个无效的单列索引。

 可以用第2 章详细介绍过的SHOW INDEXES 命令来检查索引是否有效且是否具有合适的基数。

 为查询确定QEP 的速度也会影响到查询的性能。如果发现有大量的可能的索引,则意味着这些索引没有被使用到。

 相关的QEP 列还包括key 列。

 

 2.4 key_len

 key_len 列定义了用于SQL 语句的连接条件的键的长度。此列值对于确认索引的有效性以及多列索引中用到的列的数目很重要。

 此列的一些示例值如下所示:

 

 此列的一些示例值如下所示:

 key_len: 4 // INT NOT NULL

 key_len: 5 // INT NULL

 key_len: 30 // CHAR(30) NOT NULL

 key_len: 32 // VARCHAR(30) NOT NULL

 key_len: 92 // VARCHAR(30) NULL CHARSET=utf8

 

 从这些示例中可以看出,是否可以为空、可变长度的列以及key_len 列的值只和用在连接和WHERE 条件中的索引的列

 有关。索引中的其他列会在ORDER BY 或者GROUP BY 语句中被用到。下面这个来自于著名的开源博客软件WordPress 的表展示了

 如何以最佳方式使用带有定义好的表索引的SQL 语句:

 CREATE TABLE `wp_posts` (

  `ID` bigint(20) unsigned NOT NULL AUTO_INCREMENT,

  `post_date` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',

  `post_status` varchar(20) NOT NULL DEFAULT 'publish' ,

  `post_type` varchar(20) NOT NULL DEFAULT 'post',

  PRIMARY KEY (`ID`),

  KEY `type_status_date`(`post_type`,`post_status`,`post_date`,`ID`)

 ) DEFAULT CHARSET=utf8

 

  CREATE TABLE `wp_posts` (

  `ID` bigint(20) unsigned NOT NULL AUTO_INCREMENT,

  `post_date` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',

  `post_status` varchar(20) NOT NULL DEFAULT 'publish' ,

  `post_type` varchar(20) NOT NULL DEFAULT 'post',

  PRIMARY KEY (`ID`),

  KEY `type_status_date`(`post_type`,`post_status`,`post_date`,`ID`)

 ) DEFAULT CHARSET=utf8

 

 这个表的索引包括post_type、post_status、post_date 以及ID列。下面是一个演示索引列用法的SQL 查询:

 EXPLAIN SELECT ID, post_title FROM wp_posts WHERE post_type='post' AND post_date > '2010-06-01';

 

 这个查询的QEP 返回的key_len 是62。这说明只有post_type列上的索引用到了(因为(20×3)+2=62)。尽管查询在WHERE 语句

 中使用了post_type 和post_date 列,但只有post_type 部分被用到了。其他索引没有被使用的原因是MySQL 只能使用定义索引的

 最左边部分。为了更好地利用这个索引,可以修改这个查询来调整索引的列。请看下面的示例:

 mysql> EXPLAIN SELECT ID, post_title

 -> FROM wp_posts

 -> WHERE post_type='post'

 -> AND post_status='publish'

 -> AND post_date > '2010-06-01';

 

 在SELECT查询的添加一个post_status 列的限制条件后,QEP显示key_len 的值为132,这意味着post_type、post_status、post_date

 三列(62+62+8,(20×3)+2,(20×3)+2,8)都被用到了。此外,这个索引的主码列ID 的定义是使用MyISAM 存储索

 引的遗留痕迹。当使用InnoDB 存储引擎时,在非主码索引中包含主码列是多余的,这可以从key_len 的用法看出来。

 相关的QEP 列还包括带有Using index 值的Extra 列。

 

 2.5 table

 table 列是EXPLAIN 命令输出结果中的一个单独行的唯一标识符。这个值可能是表名、表的别名或者一个为查询产生临时表

 的标识符,如派生表、子查询或集合。下面是QEP 中table 列的一些示例:

 table: item

 table: <derivedN>

 table: <unionN,M>

 表中N 和M 的值参考了另一个符合id 列值的table 行。相关的QEP 列还有select_type

 2.6 select_type

 select_type 列提供了各种表示table 列引用的使用方式的类型。最常见的值包括SIMPLE、PRIMARY、DERIVED 和UNION。其他可能

 的值还有UNION RESULT、DEPENDENT SUBQUERY、DEPENDENT UNION、UNCACHEABLE UNION 以及UNCACHEABLE QUERY。

 1. SIMPLE

 对于不包含子查询和其他复杂语法的简单查询,这是一个常 见的类型。

 2. PRIMARY

 这是为更复杂的查询而创建的首要表(也就是最外层的表)。这个类型通常可以在DERIVED 和UNION 类型混合使用时见到。

 

 3. DERIVED

 当一个表不是一个物理表时,那么就被叫做DERIVED。下面的SQL 语句给出了一个QEP 中DERIVED select-type 类型的

 示例:

 mysql> EXPLAIN SELECT MAX(id)

 -> FROM (SELECT id FROM users WHERE first = 'west') c;

 4. DEPENDENT SUBQUERY

 这个select-type 值是为使用子查询而定义的。下面的SQL语句提供了这个值:

 mysql> EXPLAIN SELECT p.*

 -> FROM parent p

 -> WHERE p.id NOT IN (SELECT c.parent_id FROM child c);

 

 5. UNION

 这是UNION 语句其中的一个SQL 元素。

 6. UNION RESULT

 这是一系列定义在UNION 语句中的表的返回结果。当select_type 为这个值时,经常可以看到table 的值是<unionN,M>,

 这说明匹配的id 行是这个集合的一部分。下面的SQL产生了一个UNION和UNION RESULT select-type:

 mysql> EXPLAIN SELECT p.* FROM parent p WHERE p.val

 LIKE 'a%'

 -> UNION

 -> SELECT p.* FROM parent p WHERE p.id > 5;

 2.7  partitions

  partitions 列代表给定表所使用的分区。这一列只会在EXPLAIN

  PARTITIONS 语句中出现。

 2.8 Extra

 Extra 列提供了有关不同种类的MySQL 优化器路径的一系列

 额外信息。Extra 列可以包含多个值,可以有很多不同的取值,并

 且这些值还在随着MySQL 新版本的发布而进一步增加。下面给

 出常用值的列表。你可以从下面的地址找到更全面的值的列表:

 

 1. Using where

 这个值表示查询使用了where 语句来处理结果——例如执行

 全表扫描。如果也用到了索引,那么行的限制条件是通过获取必

 要的数据之后处理读缓冲区来实现的。

 2. Using temporary

 这个值表示使用了内部临时(基于内存的)表。一个查询可能

 用到多个临时表。有很多原因都会导致MySQL 在执行查询期间

 创建临时表。两个常见的原因是在来自不同表的列上使用了

 DISTINCT,或者使用了不同的ORDER BY 和GROUP BY 列。

 

 可以强制指定一个临时表使用基于磁盘的MyISAM 存储引

 擎。这样做的原因主要有两个:

  内部临时表占用的空间超过min(tmp_table_size,max_

 heap_table_size)系统变量的限制

  使用了TEXT/BLOB 列

 3. Using filesort

 这是ORDER BY 语句的结果。这可能是一个CPU 密集型的过程。

 可以通过选择合适的索引来改进性能,用索引来为查询结果排序。详细过程请参考第4 章。

 4. Using index

 这个值重点强调了只需要使用索引就可以满足查询表的要求,不需要直接访问表数据。请参考第5 章的详细示例来理解这

 个值。

 5. Using join buffer

 这个值强调了在获取连接条件时没有使用索引,并且需要连接缓冲区来存储中间结果。

 如果出现了这个值,那应该注意,根据查询的具体情况可能需要添加索引来改进性能。

 6. Impossible where

 这个值强调了where 语句会导致没有符合条件的行。请看下面的示例:

 mysql> EXPLAIN SELECT * FROM user WHERE 1=2;

 7. Select tables optimized away

 这个值意味着仅通过使用索引,优化器可能仅从聚合函数结果中返回一行。请看下面的示例:

 8. Distinct

 这个值意味着MySQL 在找到第一个匹配的行之后就会停止搜索其他行。

 9. Index merges

 当MySQL 决定要在一个给定的表上使用超过一个索引的时候,就会出现以下格式中的一个,详细说明使用的索引以及合并的类型。

  Using sort_union(...)

  Using union(...)

  Using intersect(...)

 2.9 id

 id 列是在QEP 中展示的表的连续引用。

 2.10 ref

 ref 列可以被用来标识那些用来进行索引比较的列或者常量。

 2.11 filtered

 filtered 列给出了一个百分比的值,这个百分比值和rows 列的值一起使用,可以估计出那些将要和QEP 中的前一个表进行连

 接的行的数目。前一个表就是指id 列的值比当前表的id 小的表。这一列只有在EXPLAIN EXTENDED 语句中才会出现。

 2.12 type

 type 列代表QEP 中指定的表使用的连接方式。下面是最常用的几种连接方式:

  const 当这个表最多只有一行匹配的行时出现system 这是const 的特例,当表只有一个row 时会出现

  eq_ref 这个值表示有一行是为了每个之前确定的表而读取的

  ref 这个值表示所有具有匹配的索引值的行都被用到

  range 这个值表示所有符合一个给定范围值的索引行都被用到

  ALL 这个值表示需要一次全表扫描其他类型的值还有fulltext 、ref_or_null 、index_merge 、unique_subquery、index_subquery 以及index。

 

 3 解释EXPLAIN 输出结果

 理解你的应用程序(包括技术和实现可能性)和优化SQL 语句同等重要。下面给出一个从父子关系中获取孤立的父辈记录的商

 业需求的例子。这个查询可以用三种不同的方式构造。尽管会产生相同的结果,但QEP 会显示三种不同的路径。

 mysql> EXPLAIN SELECT p.*

 -> FROM parent p

 -> WHERE p.id NOT IN (SELECT c.parent_id FROM child

 c)G

 ********************* 1. row ***********************

 id: 1

 select type: PRIMARY

 table: p

 type: ALL

 possible_keys: NULL

 key: NULL

 key_len: NULL

 ref: NULL

 rows: 160

 Extra: Using where

 ********************* 2. row ***********************

 id: 2

 select_type: DEPENDENT SUBQUERY

 table: c

 type: index_subquery

 possible_keys: parent_id

 key: parent_id

 key_len: 4

 ref: func

 rows: 1

 Extra: Using index

 2 rows in set (0.00 sec)

 

 EXPLAIN SELECT p.* FROM parent p LEFT JOIN child c ON p.id = c.parent_id WHERE c.child_id IS NULLG

 ********************* 1. row ***********************

 id: 1

 select_type: SIMPLE

 table: p

 type: ALL

 possible_keys: NULL

 key: NULL

 key_len: NULL

 ref: NULL

 rows: 160

 Extra:

 ********************* 2. row ***********************

 id: 1

 select_type: SIMPLE

 table: c

 type: ref

 possible_keys: parent_id

 key: parent_id

 key_len: 4

 ref: test.p.id

 rows: 1

 Extra: Using where; Using index; Not exists

 2 rows in set (0.00 sec)

 

EXPLAIN命令详解学习 MySQL的EXPLAIN命令用于SQL语句的查询执行计划(QEP)。这条命令的输出结果能够让我们了解MySQL 优化器是如何执行 SQL 语...

本文由9159.com发布于www.9159.com,转载请注明出处:MySQL EXPLAIN命令详解学习,实则即为一般MySQL的优

关键词: