SELECT TRX_ID FROM INFORMATION_SCHEMA.INNODB_TRX WHERE TRX_MYSQL_THREAD_ID = CONNECTION_ID()
查询表的状态
showtablestatuslike't_user'
2. 进程相关
查看进程列表:
SHOW PROCESSLIST;
查看InnoDB信息:
SHOWENGINEINNODBSTATUS;
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. 存储过程
存储:
DROPPROCEDUREIFEXISTS test_insert;DELIMITER;;CREATEPROCEDURE test_insert()BEGINDECLARE Y BIGINTDEFAULT1;WHILE Y <9DOINSERTINTO t_vocabulary (content, create_person, pinyin, modify_time, classification_ids)VALUES(Y,'管理员','pinyin',CURRENT_TIMESTAMP, ‘2’);INSERTINTO t_voca_cla (voca_id, classification_id)VALUES((SELECT id FROM t_vocabulary WHERE content = CAST(Y ASCHAR)),2);SET Y = Y +1;ENDWHILE;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'%表名%'GROUPBY index_name;
5. 强大的 performance_schema
如何找出阻塞事务源头SQL
select pse.SQL_TEXT from performance_schema.data_lock_waits psd leftjoin
performance_schema.events_statements_history pse
on psd.BLOCKING_THREAD_ID = pse.THREAD_ID and psd.BLOCKING_EVENT_ID = pse.EVENT_ID +1;