数据库Mysql_学习笔记

-- 6.1.1 如何查看mysql数据库的连接数
-- 查看数据库中运行着的前100个线程
show processlist;
-- 查看数据库中运行着的全部线程
show full processlist;
-- 查看数据库支持的最大连接数
show variables like 'max_connections';
-- 查看数据库当前已经使用过的最大连接数
show global status like 'max_used_connections';

-- 6.1.2 如何查看mysql数据库当前运行的事务与锁
-- 查看数据库当前正在运行的事务
select * from information_schema.innodb_trx ;
-- 查看数据库当前产生锁的情况
select * from information_schema.innodb_locks;
-- 查看数据库当前运行的数据库事务等待锁的情况
select * from information_schema.innodb_lock_waits;
-- 数据库出现死锁时，经常需要通过查询上这三张表来找出在执行什么事务操作时导致了死锁
-- 通过执行如下sql语句可以列出数据库中所有事务的等待和锁定记录
SELECT
r.trx_isolation_level,                            /*事务隔离级别*/
r.trx_id as waiting_trx_id,                       /*正处于等待中的事务id*/
r.trx_mysql_thread_id as waiting_trx_thread,      /*正处于等待中的线程id*/
r.trx_state as waiting_trx_state,                 /*正处于等待中的事务的状态*/
lr.lock_mode as waiting_trx_lock_mode,            /*正处于等待中的事务的锁定模式*/
lr.lock_type as waiting_trx_lock_type,            /*正处于等待中的事务的锁定类型*/
lr.lock_table as waiting_trx_lock_table,          /*正处于等待中的事务的将锁定的表*/
lr.lock_index as waiting_trx_lock_index,          /*正处于等待中的事务的将锁定的索引*/
r.trx_query as waiting_trx_SQL,                   /*正处于等待中的事务将执行的SQL*/
b.trx_id as blocking_trx_id,                      /*正处于锁定中的事务id*/
b.trx_mysql_thread_id as blocking_trx_thread,     /*正处于锁定中的线程id*/
b.trx_state as blocking_trx_state,                /*正处于锁定中的事务的状态*/
lb.lock_mode as blocking_trx_lock_mode,            /*正处于锁定中的事务的锁定模式*/
lb.lock_type as blocking_trx_lock_type,            /*正处于锁定中的事务的锁定类型*/
lb.lock_table as blocking_trx_lock_table,          /*正处于锁定中的事务的将锁定的表*/
lb.lock_index as blocking_trx_lock_index,          /*正处于锁定中的事务的将锁定的索引*/
b.trx_query as blocking_trx_SQL                   /*正处于锁定中的事务将执行的SQL*/
FROM
information_schema.innodb_lock_waits wt
INNER JOIN information_schema.innodb_trx b ON b.trx_id = wt.blocking_trx_id
INNER JOIN information_schema.innodb_trx r ON r.trx_id = wt.requesting_trx_id
INNER JOIN information_schema.innodb_locks lb ON lb.lock_trx_id = wt.blocking_trx_id
INNER JOIN information_schema.innodb_locks lr ON lr.lock_trx_id = wt.requesting_trx_id
;
-- 6.1.3 mysql中数据库表的监控
-- 查看数据库中打开了哪些表
show open tables;
-- 查看数据库中当前已经被锁定的表
show open tables where in_use>0;
-- 查看数据库中表的状态，其中table_lock_waited指的时不能立即获得表级锁而需要等待的次数
-- 如果值非常大，则说明可能存在锁争抢的情况，如果频繁的出现锁争抢，则对应程序的并发性能影响很大
show status like '%tables%';
-- 查看数据库中锁的信息
show status like '%lock%';
/*
 查看数据库中表被扫描的情况,用来评估索引的使用情况
handle_read_first:从索引中读取第一项的次数，如果该值非常高，表明服务器正在执行大量的全索引扫描，
handle_read_key:基于键读取数据行的请求数。该值越高越好，表明大量的查询都使用了索引，如果越低表明缩影的利用率低
handle_read_last:读取索引中最后一个键的请求数
handle_read_next:按键顺序读取下一行的其你去数，如果查询都走了索引，那么该值将不断递增
handle_read_prev:按键顺序读取前一行的其你去数，一般用于评估order by ... desc的次数
handle_read_rnd:基于固定位置读取数据行的请求数，如果正在执行大量的需要对查询结果进行排序的查询则此值很高，
可能存在很多查询需要进行整表扫描，或者查询时一些表的关联连接没有正确使用主键或者索引
handle_read_rnd_deleted:从数据库数据文件中读取被删除记录行的请求数
handle_read_rnd_next:从数据库数据文件中读取下一行的请求数。如果SQL语句执行大量表扫描，则此值很高，如果该值很高，一般说明表没有正确添加索引或者sql语句没有通过索引来查询
*/
show global status like 'handler_read%';

-- 6.1.4 mysql中其他常用监控
-- 查看每秒事务的提交数
show global status like 'com_commit';
-- 查看每秒事务的回滚数
show global status like 'com_rollback';
-- 查看每秒事务的提交数
show global status like 'threads_%';
-- 查看每秒事务的提交数
show global status like 'com_commit';
-- 查看线程运行情况
show global status like 'com_commit';
-- 查看数据库建立过的连接总数（包含连接中和已经断开的）
show global status like 'Connections%';
-- 查看innodb引擎缓存命中情况
show global status like '%innodb_buffer_pool_read%';
-- 查看join操作时全表扫描（没有使用索引）的次数，如果该值非常大，那可能时sql语句中的join操作存在性能问题
show global status like 'select_full_join';
-- 查看sql中排序使用情况
show global status like 'sort%';
-- 查看sql中查询缓存的命中情况
show global status like 'qcache%';
-- 查看数据库查询缓存的设置
show variables like 'query_cache%';
-- 数据库大部分的状态信息都可以通过如下语句查看,其中前者查询当前的运行状态，后者是查询全局的运行状态
show status;
show global status;

-- 6.2.1 慢SQL
-- 查看慢SQL功能设置
show variables like 'slow_query%';
-- 临时开启慢SQL
set global slow_query_log = ON    
/* 永久开启慢SQL：需要修改etc/my.cnf配置文件，在mysqld处加入如下配置，再重启数据库
slow_query_log = ON
slow_query_log_file = /var/lib/mysql/localhost-slow.log
*/
-- 慢查询默认的记录时间是10秒，可以通过如下语句修改慢查询的记录时间
set long_query_time=20
-- 查看慢查询发生的次数
show status like 'slow_queries';

-- 6.2.2 执行计划
/*
 执行计划：查看数据库是如何执行sql查询语句的
id：查询的顺序编号，表示查询中执行的顺序，id值越大，执行的优先级越高，如果id相同，则从上往下执行
select_type：查询类型
        SIMPLE： 表示简单查询方式，SQL语句中一般不会使用UNION和子查询
        PRIMARY： 表示包含子查询的SQL语句的最外层查询语句的查询类型，即当查询中包含子查询时，最外层的查询语句就会显示为PRIMARY
        UNION： 在查询语句中，如果在UNION关键字之后出现来第二个SELECT，则标记为UNION
        UNION RESULT： 表示查询中有多个查询结果集执行UNION操作
        DEPENDENT SUBQUERY： 子查询中UNION中第一个SELECT查询为DEPENDENT SUBQUERY
        SUBQUERY： 子查询内曾查询的第一个SELECT
        DERIVED： 在查询语句中，如果from子句的子查询中出现了union关键字，则外层的select查询将被标记为DERIVED
        MATERIALIZED： 表示子查询被物化，物化通过将子查询结果作为一个临时表来加快查询执行速度，从而能够使得子查询只执行一次
        UNCACHEABLE SUBQUERY： 表示查询结果集无法缓存的子查询，需要逐次查询
        UNCACHEABLE UNION： 表示子查询不可被物化，需要逐次运行(即需要执行多次)
table：查询涉及的表名或者表的别名
type：表示表连接的类型，包括的类型如下（性能从高到低的顺序）
       null->system->const->eq_ref->ref->fulltext->ref_or_null->index_merge->unique_subquery->index_subquery->range->index->ALL
       null： 表示不访问任何的表
       system： 表示表中只有一条记录，相当于系统表。一般可以认为是const类型的特例
       const： 表示主键或者唯一索引的常量查询，表中最多只有1行记录符合查询要求。通常const使用到主键或者唯一索引进行定值查询、常量查询，查询速度快
       eq_ref： 表示join查询过程中，关联条件字段使用主键或者唯一索引，出来的行数不止一行。eq_ref是一种查询性能很高的join操作
       ref： 表示非聚集索引的常量查询
       fulltext： 表示查询过程中，使用来fulltext类型的索引
       ref_or_null： 跟ref查询类似，在ref的查询基础上多加一个null值的条件查询
       index_merge： 表示索引联合查询
       unique_subquery： 表示查询使用主键的子查询
       index_subquery： 表示查询使用非聚集索引的子查询
       range： 表示查询通过使用索引范围的查询。一般包括=、<>、>、>=、<、<=、IS NULL、BETWEEN、IN、<=>等范围
       index： 表示通过索引进行扫描查询
       ALL： 表示全表扫描，性能最差
possible_keys：查询时预计可能会使用的索引
key：实际查询时真实使用的索引
key_len：使用的索引长度
ref：关联信息
rows：查询时扫描的数据记录行数
extra：查询特性的使用情况
        using index：表示使用了索引；
        using index condition：表示使用了索引进行过滤；
        using MRR：表示使用了索引进行内部排序；
        using where：表示使用了where条件；
        using temporary：表示使用了临时表；
        using filesort：表示使用了文件排序，一般指无法利用索引来完成的排序；
*/
explain select * from zt_bug