一、查看mysql锁相关命令
1、通过INFORMATION_SCHEMA.INNODB_TRX、INNODB_LOCKS、INNODB_LOCK_WAITS 获取事务与锁的信息
1）查看哪些事务正在执行
命令：SELECT * FROM INFORMATION_SCHEMA.INNODB_TRX;
trx_state表示事务的状态
mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_TRX \G *************************** 1. row *************************** trx_id: 4391 trx_state: LOCK WAIT //事务状态，LOCK WAIT表示等待锁 trx_started: 2019-09-27 11:39:09 trx_requested_lock_id: 4391:57:962:104 trx_wait_started: 2019-09-27 11:39:09 trx_weight: 2 trx_mysql_thread_id: 9 //线程ID trx_query: select * from employees where birth_date = '1953-09-02' for update trx_operation_state: starting index read trx_tables_in_use: 1 trx_tables_locked: 1 trx_lock_structs: 2 trx_lock_memory_bytes: 1136 trx_rows_locked: 1 trx_rows_modified: 0 trx_concurrency_tickets: 0 trx_isolation_level: REPEATABLE READ trx_unique_checks: 1 trx_foreign_key_checks: 1 trx_last_foreign_key_error: NULL trx_adaptive_hash_latched: 0 trx_adaptive_hash_timeout: 0 trx_is_read_only: 0 trx_autocommit_non_locking: 0 *************************** 2. row *************************** trx_id: 4390 trx_state: RUNNING //事务状态，RUNNING表示正在运行的事务，如果一直运行中，有可能是block了 trx_started: 2019-09-27 11:38:41 trx_requested_lock_id: NULL trx_wait_started: NULL trx_weight: 65 trx_mysql_thread_id: 20 //线程ID trx_query: NULL trx_operation_state: NULL trx_tables_in_use: 0 trx_tables_locked: 1 trx_lock_structs: 65 trx_lock_memory_bytes: 8400 trx_rows_locked: 127 trx_rows_modified: 0 trx_concurrency_tickets: 0 trx_isolation_level: REPEATABLE READ trx_unique_checks: 1 trx_foreign_key_checks: 1 trx_last_foreign_key_error: NULL trx_adaptive_hash_latched: 0 trx_adaptive_hash_timeout: 0 trx_is_read_only: 0 trx_autocommit_non_locking: 0 2 rows in set (0.00 sec)
2）查看当前锁定的事务
命令：SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS;
mysql> select * from information_schema.INNODB_LOCKS\G *************************** 1. row *************************** lock_id: 17778:82:3:6 --当前锁ID lock_trx_id: 17778 --该锁对应的事务ID lock_mode: X -- 锁类型，排它锁X lock_type: RECORD --锁范围，记录锁：record lock，其他锁范围：间隙锁：gap lock，或者next-key lock（记录锁+间隙锁） lock_table: `test`.`trx_fee` lock_index: PRIMARY --加载在哪个索引上的锁 lock_space: 82 lock_page: 3 lock_rec: 6 lock_data: 4 *************************** 2. row *************************** lock_id: 17773:82:3:6 lock_trx_id: 17773 lock_mode: X lock_type: RECORD lock_table: `test`.`trx_fee` lock_index: PRIMARY lock_space: 82 lock_page: 3 lock_rec: 6 lock_data: 4
3）查看当前正在等待锁的事务
命令：SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS;
mysql> select * from information_schema.INNODB_LOCK_WAITS\G *************************** 1. row *************************** requesting_trx_id: 17778 --请求锁的事务ID（等待方） requested_lock_id: 17778:82:3:6 -- 请求锁ID blocking_trx_id: 17773 -- 阻塞该锁的事务ID（当前持有方，待释放） blocking_lock_id: 17773:82:3:6 -- 持有的锁ID
2、查询锁争用情况
1）查询表级锁争用情况
可以通过检查table_locks_waited和table_locks_immediate状态变量来分析表锁争夺情况。
mysql> show status like 'table%';
+----------------------------+-------+
| Variable_name | Value |
+----------------------------+-------+
| Table_locks_immediate | 6165 |
| Table_locks_waited | 0 |
| Table_open_cache_hits | 0 |
| Table_open_cache_misses | 0 |
| Table_open_cache_overflows | 0 |
+----------------------------+-------+
变量解释：
table_lock_waited 表示不能马上获得锁的数量
table_lock_immediate表示马上获得锁的数量
2）查看记录锁使用情况
mysql> show status like 'innodb_row_lock_%';
+-------------------------------+-------+
| Variable_name | Value |
+-------------------------------+-------+
| Innodb_row_lock_current_waits | 0 |
| Innodb_row_lock_time | 1896 |
| Innodb_row_lock_time_avg | 6 |
| Innodb_row_lock_time_max | 193 |
| Innodb_row_lock_waits | 274 |
+-------------------------------+-------+
变量解释：
Innodb_row_lock_current_waits：当前等待锁的数量
Innodb_row_lock_time：系统启动到现在，锁定的总时间长度
Innodb_row_lock_time_avg：每次平均锁定的时间
Innodb_row_lock_time_max：最长一次锁定时间
Innodb_row_lock_waits：系统启动到现在总共锁定的次数
二、查看MySQL的运行状态
我们需要了解MySQL的服务器状态信息，比如查看当前MySQL启动后的运行时间，MySQL的客户端会话连接数，MySQL服务器执行的慢查询数，MySQL执行了多少SELECT/UPDATE/DELETE/INSERT语句等统计信息。查看MySQL运行状态，优化MySQL运行效率，就需要使用show status命令。
1、线程缓存命中率
1）查看当前连接数
命令：SHOW STATUS LIKE 'Thread_%';
Thread_cached:被缓存的线程的个数
Thread_running：处于激活状态的线程的个数
Thread_connected：当前连接的线程的个数
Thread_created：总共被创建的线程的个数
Thread Cache Hits
Thread_connected = SHOW GLOBAL STATUS LIKE Thread_created;
Connections = SHOW GLOBAL STATUS LIKE 'Connections';
TCH = (1 - ( Threads_created / Connections ) ) * 100
如果TCH数小于90%，说明命中率不高，创建连接耗费了不少时间，可以考虑加大 thread_cache_size 变量值来增大Thread_cached的数量。
2）线程缓存大小 thread_cache_size
当客户端断开连接之后，MySQL会把线程缓存起来以响应下一个请求而不是直接销毁(前提是缓存数未达上限)
查看thread_cache_size的值：
show global variables like 'thread_cache_size';
设置thread_cache_size大小：
如果是短连接，可以适当设大一点。因为短连接往往需要不停地创建和销毁。如果设大一点，重新创建和销毁的数量会小一些，对性能提升有较大的作用。
如果是长连接，如果是长连接，可以适当设置小一点。
物理内存设置规则：
可以大致根据物理内存来设置，1G-->8、2G-->16、3G-->32、4G-->64
两种设置方法：
mysql> set global thread_cache_size = 16
编辑/etc/my.cnf 更改/添加 thread_concurrency = 16
2、QPS、TPS 和 RW Ratio
几个重要参数：
Qcache_hits：查询缓存命中次数
SHOW GLOBAL STATUS LIKE 'Qcache_hits';
Com_select：总的查询语句量
SHOW GLOBAL STATUS LIKE 'Com_select';
Com_insert：执行插入语句量
SHOW GLOBAL STATUS LIKE 'Com_insert';
Com_update：执行更新语句量
SHOW GLOBAL STATUS LIKE 'Com_update';
Com_delete：执行删除语句量
SHOW GLOBAL STATUS LIKE 'Com_delete';
Com_replace：执行字符串替换语句量
SHOW GLOBAL STATUS LIKE 'Com_replace';
Uptime：本次启动服务到现在执行的时长
SHOW GLOBAL STATUS LIKE 'Uptime';
1）QPS，每秒查询量
基于Questions计算QPS。Questions表示发送给服务器的查询数量
Questions：
SHOW GLOBAL STATUS LIKE 'Questions';
QPS = Questions / Uptime
基于com_*计算QPS。Com_select表示查询语句执行数量
QPS = Com_select / Uptime
使用Questions进行计算QPS时，人为拉高了计算结果。相对来说，使用Com_select这种计算方式更符合实际一些。
2）TPS，每秒事务量
服务器每秒处理的事务数，如果是InnoDB会显示，没有InnoDB就不会显示。
基于Com_commit和Com_rollback计算。Com_commit表示执行的事务提交次数，Com_rollback表示执行的事务回滚次数
Com_commit：
SHOW GLOBAL STATUS LIKE 'Com_commit';
Com_rollback：
SHOW GLOBAL STATUS LIKE 'Com_rollback';
TPS = (Com_commit + Com_rollback) / Uptime
QPS 和 TPS值一定要实时监控，如果接近架构搭建时的测试峰值，表示系统压力很大
3）R/W Ratio，读写比
Read/Writes Ratio，数据库读写比
R/W = (Com_select + Qcache_hits) / (Com_insert + Com_update + Com_delete + Com_replace) * 100
读写比：优化数据库的重要依据，读的多优化读，写的多优化写
3、慢查询、无索引 join查询
1）慢查询
/*查看慢查询时间 */
show variables like "long_query_time";默认10s
/*查看慢查询配置情况 */
show status like "%slow_queries%";
/*查看慢查询日志路径 */
show variables like "%slow%";
=======================================
/*开启慢日志 */
set global slow_query_log = ON;
/*设置慢查询时间 2s */
set global long_query_time = 2;
/*设置慢查询日志路径 */
set global slow_query_log_file='/usr/local/mysql/data/slow.log';
/*设置无索引的查询,设置后没有使用索引的查询也会被认为是慢查询 */
set global log_queries_not_using_indexes = ON;
======================================
Slow queries per minute
Slow_queries：
SHOW GLOBAL STATUS LIKE 'Slow_queries';
SQPM = Slow_queries/(Uptime/60)
S/Q = Slow_queries/Questions
新版本上线时要重点关注慢查询，出现慢查询sql，要尽快优化。
2）无索引join查询
Full_join per minute
Select_full_join：
SHOW GLOBAL STATUS LIKE 'Select_full_join';
FJPM = Select_full_join / (Uptime / 60)
没有使用索引而造成的全表扫描的join，需要优化索引。
4、buffer 和cache设置
Innodb buffer read hits，buffer命中率
Innodb_buffer_pool_reads：
SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_reads';
Innodb_buffer_pool_read_requests：
SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_read_requests';
IFRH = (1 - Innodb_buffer_pool_reads/Innodb_buffer_pool_read_requests) * 100
InnoDB Buffer命中率，目标95%-99%;
Table Cache，表缓存
Open_tables：
SHOW GLOBAL STATUS LIKE 'Open_tables';
Opened_tables：
SHOW GLOBAL STATUS LIKE 'Opened_tables';
table_cache：
SHOW GLOBAL STATUS LIKE 'table_cache';
table_cache应该大于Open_tables小于Opened_tables