MySQL常用命令

Mysql 常用命令

1. 事务相关

• 关闭事务自动提交：

SET @@autocommit=0;

• 查看事务状态

SHOW  VARIABLES LIKE '%autocommit%';

• 查看事务隔离级别

SELECT @@transaction_isolation;

• 设置事务隔离级别：

读未提交：set session transaction isolation level read uncommitted;
读已提交：set session transaction isolation level read committed;
可重复读：set session transaction isolation level repeatable read;
序列化：set session transaction isolation level serializable;

• 查看当前事务id：

SELECT * FROM INFORMATION_SCHEMA.INNODB_TRX

• 获取当前活跃事务id：

SELECT TRX_ID FROM INFORMATION_SCHEMA.INNODB_TRX  WHERE TRX_MYSQL_THREAD_ID = CONNECTION_ID()

• 查询表的状态

show table status like 't_user'

2. 进程相关

• 查看进程列表：

SHOW PROCESSLIST;

• 查看InnoDB信息：

SHOW ENGINE INNODB STATUS;

3. buffer pool相关

• 查看 buffer pool 大小：

SELECT @@innodb_buffer_pool_size/1024/1024;

• 查询排序长度限制：

SHOW VARIABLES LIKE '%max_length_for_sort_data%'; 

• 设置排序长度限制：

SET max_length_for_sort_data = 1024;

2. 存储过程

• 存储：

DROP PROCEDURE IF EXISTS test_insert;
DELIMITER ;;
CREATE PROCEDURE test_insert()
BEGIN
DECLARE Y BIGINT DEFAULT 1;
WHILE Y < 9 DO
INSERT INTO t_vocabulary (content, create_person, pinyin, modify_time, classification_ids) VALUES (Y, '管理员', 'pinyin', CURRENT_TIMESTAMP, ‘2’);
INSERT INTO t_voca_cla (voca_id, classification_id) VALUES ((SELECT id FROM t_vocabulary WHERE content = CAST(Y AS CHAR)), 2);
SET Y = Y + 1;
END WHILE;
COMMIT;
END;;
CALL test_insert();

3. SQL 分析

• 关闭索引下推

set optimizer_switch='index_condition_pushdown=off';

4. 数据页相关

• 查看 buffer pool 中的数据页情况：

SELECT index_name,COUNT(*) FROM information_schema.INNODB_BUFFER_PAGE 
   WHERE INDEX_NAME IN('索引名', '索引名') AND TABLE_NAME LIKE '%表名%' GROUP BY index_name;

5. 强大的 performance_schema

• 如何找出阻塞事务源头SQL

select pse.SQL_TEXT from performance_schema.data_lock_waits psd left join 
performance_schema.events_statements_history pse 
on psd.BLOCKING_THREAD_ID = pse.THREAD_ID and psd.BLOCKING_EVENT_ID = pse.EVENT_ID + 1;