mysql常用命令

• 慢查询日志

show variables like '%slow_query_log%';
临时开启 
set global slow_query_log = 1;

永久开启
show_query_log=1
show_query_log_file=/var/lib/mysql/localhost-slow.log   
阀值查看
show variables like '%long_query_time%';

临时开启 
    set global long_query_time = 1;
    
查询慢查询sql总数
    show global status like '%slow_queries%'

• mysqlddumpslow(查看慢查询日志)

通过mysqlddumpslow 查看日志
    -s, 是表示按照何种方式排序
        c：访问计数
        l：锁定时间
        r:返回记录
        al：平均锁定时间
        ar：平均访问记录数
        at：平均查询时间

    -t, 是top n的意思，即为返回前面多少条的数据；
    -g, 后边可以写一个正则匹配模式，大小写不敏感的；

-- 获取返回记录最多的3个sql
    mysqlddumpslow -s -r -t 3 log文件

-- 获取访问次数最多的3个sql 
    mysqlddumpslow -s c -t 3 log文件

--按照时间排序，前10条包含left join查询语句的sql

    mysqlddumpslow -s t -t 10 -g  "left join" log文件

• mysql5.7(虚拟列)

建表时添加虚拟列
    `SimpleDate_dayofweek` tinyint(4) GENERATED ALWAYS AS 
    (dayofweek(SimpleDate)) VIRTUAL

为表添加虚拟列
    alter table user add user_name varchar(20) generated always as 
    (data->'$.name');   

• mysql binlog

binlog格式
    –基于SQL语句的复制(statement-based replication,SBR)， 
    –基于行的复制(row-based replication,RBR)， 
    –混合模式复制(mixed-based replication,MBR)。   

1）查看binlog_format
    show variables like 'binlog_format'

2）查看是否开启binlog
    show variables like 'log_bin'

3）获取binlog文件列表
    show binary logs

4）查看当前正在写入的binlog文件
    show master status

5） 查看master上的binlog
    show master logs

6）只查看第一个binlog文件的内容
    show binlog events

7）查看指定binlog文件的内容
    show binlog events in 'mysql-bin.000002'

8） 删除binlog
    reset master;//删除master的binlog
    reset slave;    //删除slave的中继日志
    purge master logs before '2012-03-30 17:20:00';
    //删除指定日期以前的日志索引中binlog日志文件
    purge master logs to 'mysql-bin.000002'; //删除指定日志文件的日志索引中binlog日志文件
9）解析binlog 
    mysql-binlog-connector-java

• 查看锁表

查看锁表情况
    show status  like '%lock%';

查看正在被锁定的的表
    show OPEN TABLES where In_use > 0;
    
查看正在执行进程    
    show PROCESSLIST;

kill进程
    kill id

了解索引的效果
    show status like "Handler_read%"
    Handler_read_key 值高表示索引效果好，Handler_read_rnd_next值高表示索引低效。 

• 查看mysql语句运行时间

show profiles
查看是否开启      show variables like "%pro%"
设置开启            set profiling = 1
可以开始执行一些想要分析的sql语句了，执行完后，show profiles；
show profile for query 1  即可查看第1个sql语句的执行的各个操作的耗时详情

• mysql锁方面

select * from information_schema.innodb_trx;

select * from information_schema.INNODB_LOCKS;

select * from information_schema.INNODB_LOCK_WAITS;

select
    r.trx_id waiting_trx_id,
    r.trx_mysql_thread_id waiting_thread,
    r.trx_query waiting_query,
    b.trx_id blocking_trx_id,
    b.trx_mysql_thread_id blocking_thread,
    b.trx_query blocking_query
from information_schema.innodb_lock_waits w
inner join information_schema.innodb_trx b
on b.trx_id = w.blocking_trx_id
inner join information_schema.innodb_trx r
on r.trx_id = w.requesting_trx_id;