Mysql 相关命令
索引相关
# 查看表的索引
show index form user;
# 创建索引（alter可创建primark key）
# INDEX、UNIQUE、PROMARY KEY、fulltext index
create index table_name on table_name(column_list);
alter table table_name add index index_name(column_list);
alter table table add primark key(column);
# 删除索引
drop index index_name on table_name;
事务相关
# 查看自动提交状态
show variables like 'autocommit';
# 设置当前会话 自动提交状态
set session autocommit = 0;
# 设置全局 自动提交状态
set global autocommit = 1;
# 查看事务的隔离级别
SELECT @@tx_isolation;
SHOW variables like 'transaction_isolation';
# 设置事务的隔离级别
# read uncommitted
# read committed
# repeatable read
# serializable
set session transaction isolation level serializable;
锁相关
# 添加表级读锁
LOCK TABLES tbl_name WRITE
# 添加表级写锁
LOCK TABLES tbl_name READ；
## innodb事务默认是排他锁
## innodb行级锁是给索引项的索引列加锁，实现的行级锁
## 普通列加锁是默认加的表级锁
# 添加行级锁 共享锁
select * from table where id =1 lock in share mode;
# 添加行级锁 排他锁
select * from table where id =1 for update;
# 查看当前数据库锁表的情况
SELECT * FROM information_schema.INNODB_TRX;
# 杀掉查询结果中锁表的trx_mysql_thread_id
kill trx_mysql_thread_id;
# 查询是否锁表
show OPEN TABLES where In_use > 0;
# 查看正在锁的事务
SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS;
# 查看等待锁的事务
SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS;
# 查询表级锁争用情况
# 如果Table_locks_waited的值比较高，则说明存在着较严重的表级锁争用情况。
show status like 'table%';
# 获取InnoDB行锁争用情况
# 如InnoDB_row_lock_waits和InnoDB_row_lock_time_avg的值比较高，则需要解锁
show status like 'innodb_row_lock%';
# 查询进程 然后 kill id
show processlist;
慢sql相关
# 查看Mysql慢Sql设置
show variables like '%slow_query_log%';
# 开启慢Sql日志记录
set global slow_query_log=1;
# 查看慢Sql阀值
show variables like '%long_query_time%';
# 设置慢Sql阀值
set global long_query_time=3;
# 查看慢Sql的记录数
show global status like '%slow_queries%';
Profiling相关
# 进行sql性能分析，启用后sql查询包括错误的语句都会记录在内。
# 查看Profiling信息
show variables like '%profiling%';
# 开启profiling
set profiling=1;
# 设置最大历史数 （5.7.28 默认15）
set profiling_history_size=20;
# 参数备注
ALL 显示所有的开销信息
BLOCK IO 显示块io(块的输入输出)的次数
CONTEXT SWITCHES 上下文切换相关开销
CPU 显示用户和系统的CPU使用情况
IPC 显示发送和接受的消息数量
MEMORY 显示内存相关开销
PAGE FAULTS 显示主要和次要的页面故障
SOURCE 显示源代码的函数名
SWAPS 显示交换次数相关开销
# 需要注意的结论
converting heap to myisam 查询结果太大，内存不够用往磁盘上搬了
creating tmp table 创建临时表（拷贝数据到临时表，用完删除）
copying to tmp table on disk 把内存中临时复制到磁盘
# 显示profile记录的sql
show profiles;
# 分析某一条sql
show profile CPU,block io for query 259;
PERFORMANCE_SCHEMA
# MySQL 5.5开始新增一个数据库：PERFORMANCE_SCHEMA，主要用于收集数据库服务器性能参数。
# 查看状态
show variables like 'performance_schema';
...
全局日志
# 查看状态
show variables like '%general%';
# 开启全局日志
set global general_log=1;
set global general_log_file = '/var/lib/mysql/localhost.log';
# 可记录到表中或文件中
log_output='TABLE,FILE'
select * from mysql.general_log
# 查询记录
select * from mysql.general_log
复制表相关
# 复制表结构及数据到新表，新表中没有了旧表的primary key、Extra（auto_increment）等属性
create table new_table select * from old_table;
# 只复制表结构到新表
create table new_table select * from old_table;
create table new_table like old_table;
# 复制旧表的数据到新表(假设两个表结构一样)
insert into empty_table select * from old_table;
# 复制旧表的数据到新表(假设两个表结构一样)
insert into empty_table(column_list) select column_list from old_table;
其他相关
# 查看表结构
desc old_table;
# 导出表的创建语句
show create table old_table;
# 修改结束符，默认；
delimiter $$;
# 重置自增长
ALTER TABLE table_name AUTO_INCREMENT = 1;
mysql配置文件
# 配置慢Sql相关
slow_query_log=1;
slow_query_log_file=/var/local/mysql/data/chodbl_slow.log;
long_query_time=3;
log_output=FILE;
mysqldumpslow相关
-s 是表示按照何种方式排序
c 访问计数
l 锁定时间
r 返回记录
t 查询时间
al 平均锁定时间
ar 平均返回记录数
at 平均查询时间
-t 是top n的意思，即为返回前面多少条的数据
-g 后边可以写一个正则匹配模式，大小写不敏感的
# 得到返回记录集最多的10个SQL
mysqldumpslow -s r -t 10 /database/mysql/mysql06_slow.log
# 得到访问次数最多的10个SQL
mysqldumpslow -s c -t 10 /database/mysql/mysql06_slow.log
# 得到按照时间排序的前10条里面含有左连接的查询语句。
mysqldumpslow -s t -t 10 -g “left join” /database/mysql/mysql06_slow.log
# 另外建议在使用这些命令时结合 | 和more 使用 ，否则有可能出现刷屏的情况。
mysqldumpslow -s r -t 20 /mysqldata/mysql/mysql06-slow.log | more
浙公网安备 33010602011771号