Mysql 运维优化必须知道的几个命令

针对SQL语句维护，运行的命令以及命令详解

#显示当前session中所有的统计参数（对于所有存储引擎的表操作都会有累计），常关心的几个参数

show status like 'Com_%';

上面输出参数比较常用的了解一下：

Com_select:        执行select次数，一次查询只累加1
Com_insert:        执行insert次数，对于批量插入，只累计1
Com_update:      执行update次数，执行update次数

Com_delete:        执行delete次数，执行delete次数

#只针对InnoDB存储引擎

show status like 'Innodb_rows_%';

上面输出参数比较常用的了解一下：

Innodb_row_read：            SELECT查询返回行数
Innodb_row_inserted：      执行INSERT操作插入行数
Innodb_row_updated：      执行UPDATE操作更新行数

Innodb_row_deleted：       执行DELETE操作删除行数

#试图连接Mysql服务器次数

show status like 'Connections';

#服务器工作时间

show status like 'Uptime';

#慢查询次数
show status like 'Slow_queries';


#通过explain和desc来查看执行计划

explain/desc   query_statement(SQL语句)

对执行计划的每个列进行说明
一、 select_type:表示SELECT的类型，常见取值有
(1)、SIMPLE(简单表，即不使用表连接或者子查询)，
(2)、PARMARY(主查询，即外层查询),
(3)、UNION(UNION中的第二个或者后面的查询语句),
(4)、SUBQUERY(子查询第一个SELECT)。

(5)、DERIVED（派生表 From 里面的子查询）

二、table:输出表结果

三、 type：表示在MYSQL在表中找到所需行的方式，或者叫访问类型，以下类型是从左到右，性能由最差变最好
all->index->range->ref->eq_ref->const,system->null
(1)type=all :全表扫描，MYSQL遍历全表来找来匹配行
(2)type=index :搜索全扫描，MYSQL遍历整个索引来查询匹配行
(3)type=range :索引扫描范围，常见于<、<=、>、>=等操作
(4)type=ref :使用非唯一索引扫描或唯一索引的前缀扫描，返回匹配某个单独值的记录行（ref经常出现在join中）
(5)type=eq_ref :使用唯一索引，对于每个检索值，表中只有一条记录匹配。（多表关联中使用primary key 或者unique index 为关联条件）
(6)type=const/system ：单表中最多有一个匹配行，查询起来非常速度，所以这个匹配行中的其他列的值可以被优化器在当前查询中当做常量
来处理，例如，根据主键primary key 或 唯一索引 unique index 进行查询。
(7)type=null :MYSQL不用访问表或者索引，直接就能够得到结果，例如，select 1 from dual where 1

四、 possible_keys:表示查询时可能用到的索引
五、 key:表示实际使用的索引
六、 key_len：使用到索引字段长度
七、 rows：扫描行的数量
八、 Extra：执行情况的说明和描述，包含不适合在其他列中显示但对执行计划非常重要的额外信息。

#使用 explain extended  命令加上  show warnings，我们能够看到SQL真正执行之前优化器做了哪些处理。可以查看到经过优化后的sql语句

案例：

EXPLAIN EXTENDED
select DISTINCT c.Tag
from be_Blogs a 
join be_Posts b on a.BlogId = b.BlogID
join be_PostTag c on c.BlogID = b.BlogID;
SHOW WARNINGS

# explain partitions 查看sql所访问的分区
EXPLAIN PARTITIONS
select DISTINCT c.Tag
from be_Blogs a 
join be_Posts b on a.BlogId = b.BlogID
join be_PostTag c on c.BlogID = b.BlogID;


#查看是否支持show profiles
select @@have_profiling;

#设置profiles开启
set profiling = 1;

#可以通过 show profiles 查看当前SQL的 QueryID 值为多少
select DISTINCT c.Tag
from be_Blogs a 
join be_Posts b on a.BlogId = b.BlogID
join be_PostTag c on c.BlogID = b.BlogID;
show profiles

#查看执行过程中线程每个状态和消耗时间

show profile for query QueryID（通过show profile 查询出来的QueryID）;

#选择查看CPU耗费时间

show profile cpu for query QueryID;

#查看SQL解析执行过程中每个步骤对应的源码的文件、函数名以及具体的源文件行数。
show profile source for query 140; 



Mysql 锁信息查询语句

#输出一个字符串文本，可以额外研究，内含信息量的信息，对数据库整体运行情况，对高性能调优很有帮助
show engine innodb status


#现在获取的锁，但是不含没有获取的锁，而且只是针对INNODB的。
SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS; 


#包含了所有正在执行的的事物相关信息（INNODB），而且包含了事物是否被阻塞或者请求锁。
select * from information_schema.innodb_trx;


#系统锁等待相关信息，包含了阻塞的一行或者多行的记录，而且还有锁请求和被阻塞改请求的锁信息等。
select * from information_schema.INNODB_LOCK_WAITS;


#查看正在被锁定的的表

show OPEN TABLES where In_use > 0;

有兴趣的朋友也可以了解一下mysql抓取慢查询日志信息，点此传送