MySQL高级命令

MySQL 安装(Linux)
MySQL 启动（Mac）
- mac 下启动 mysql
MySQL 连接信息
MySQL 配置文件
mysql 位置信息
mac

MySQL 安装(Linux)

参考 Linux 环境搭建中的{{1.13 安装 mysql}}

MySQL 启动（Mac）

mac 下启动 mysql

##### 启动MySQL服务
> sudo /usr/local/MySQL/support-files/mysql.server start
##### 停止MySQL服务
> sudo /usr/local/mysql/support-files/mysql.server stop
##### 重启MySQL服务
> sudo /usr/local/mysql/support-files/mysql.server restart
##### 终端连接MySQL
> mysql -uroot -proot
##### 强制关闭mysql
> sudo pkill -9 mysql

MySQL 连接信息

#查看最大连接数，默认是151
show variables like '%max_connection%'; 
#重新设置最大连接数
set global max_connections=1000;  
#在/etc/my.cnf里面设置数据库的最大连接数
# max_connections = 1000    
#查看连接数
show status like  'Threads%';
#查看当前用户的连接100之内
show processlist;
#查看当前用户所有的连接
show full processlist;

MySQL 配置文件

必须在 /etc 新建 my.cnf 文件

sudo vim /etc/my.cnf

添加 {{my.cnf 文件内容}}

# 修改文件读写权限
sudo chmod 664 my.cnf
# mysql 认为666权限不安全会忽略掉
参考文章：https://blog.csdn.net/jyongchong/article/details/77862819

mysql 位置信息

mac
/usr/local/mysql/data/

/var/lib/mysql/

基本命令

database

#创建
create database lane;
#使用
use lane;

table

#创建
create table dept (
dept_id int primary key,
deptName varchar(200)
) engine = innodb;

查看表数据结构信息

desc user;

查看表创建信息

show create table user;

二进制日志（binary log）

show variables like '%log_bin%'; //是否开启 
show variables like '%binlog%';//参数查看 show binary logs;//查看日志文件

慢查询日志（Slow query log）

# 记录所有执行时间超时的查询SQL，默认是10秒。
show variables like '%slow_query%'; //是否开启 
show variables like '%long_query_time%'; //时长

查询缓存

show variables like '%query_cache%'; //查看查询缓存是否启用，空间大小，限制等
show status like 'Qcache%'; //查看更详细的缓存参数，可用缓存空间，缓存块，缓存多少等

存储引擎 innoDB 命令

存储引擎

show engines //命令，就可以查看当前数据库支持的引擎信息。

Buﬀer Pool 配置参数

show variables like '%innodb_page_size%'; //查看page页大小 
show variables like '%innodb_old%'; //查看lru list中old列表参数 
show variables like '%innodb_buﬀer%'; //查看buﬀer pool参数 
# 建议：将innodb_buﬀer_pool_size设置为总内存大小的60%-80%， 
# innodb_buﬀer_pool_instances可以设置为多个，这样可以避免缓存争夺。

Change Buﬀer

# ChangeBuﬀer占用BuﬀerPool空间，默认占25%，最大允许占50%，可以根据读写业务量来 进行调整。
# 参数innodb_change_buﬀer_max_size;
show variables like '%innodb_change_buffer_max_size%';

Log Buﬀer

show variables like '%innodb_log_buffer_size%';
show variables like '%innodb_log%';
show variables like '%innodb_flush_log_at_trx_commit%'
set global innodb_flush_log_at_trx_commit =2
innodb_ﬂush_log_at_trx_commit 参数控制日志刷新行为，默认为 1
0： 每隔 1 秒写日志文件和刷盘操作（写日志文件 LogBuﬀer-->OS cache，刷盘 OS cache--> 磁盘文件），最多丢失 1 秒数据
1： 事务提交，立刻写日志文件和刷盘，数据不丢失，但是会频繁 IO 操作 
2： 事务提交，立刻写日志文件，每隔 1 秒钟进行刷盘操作

通用表空间

CREATE TABLESPACE ts1 ADD DATAFILE ts1.ibd Engine=InnoDB; //创建表空间ts1
CREATE TABLE t1 (c1 INT PRIMARY KEY) TABLESPACE ts1; //将表添加到ts1表空间

查看线程

show variables like '%innodb_purge_threads%';
show variables like '%innodb_page_cleaners%';

查看文件存储格式

# \G 表示以列的形式展示
show table status \G;
# 通过 information_schema 查看指定表的文件格式
select * from information_schema.innodb_sys_tables \G;

修改文件格式

ALTER TABLE 表名 ROW_FORMAT=格式类型;
# 只有在重新创建表及其索引才生效

Undo Log 信息

show variables like '%innodb_undo%';

Redo Log 信息

show variables like '%innodb_log%';

系统日志命令

Binlog 状态查看

show variables like 'log_bin';

开启 Binlog 功能

mysql> set global log_bin=mysqllogbin;
ERROR 1238 (HY000): Variable 'log_bin' is a read only variable
#需要修改my.cnf或my.ini配置文件，在[mysqld]下面增加log_bin=mysql-bin，重启 MySQL服务。
#可以开启之后通过命令行设置记录的类型
set global binlog_format='ROW';
#开启binlog日志大概会有1%的性能损耗。

  # Replication Master Server (default)  
  # binary logging is required for replication  
  log-bin=mysql-bin
    # binary logging format - mixed recommended  
  binlog_format=mixed
server-id   = 1
# expire_logs_days = 7                 //binlog过期清理时间
# max_binlog_size 100m              //binlog每个日志文件大小

常用 Binlog 命令

# 查看所有binlog日志列表
show binary logs; //等价于show master logs; 
#查看master状态，即最后(最新)一个binlog日志的编号名称，及其最后一个操作事件pos结束点(Position)值
show master status; 
show binlog events; 
show binlog events in 'mysql-bin.000001';
#刷新log日志，自此刻开始产生一个新编号的binlog日志文件
flush logs;
#重置(清空)所有binlog日志
reset master;
purge binary logs to 'mysql-bin.000001'; //删除指定文件 
purge binary logs before '2021-07-08 00:00:00'; //删除指定时间之前的文件 
#可以通过设置expire_logs_days参数来启动自动清理功能。默认值为0表示没启用。
#设置为1表示超出1天binlog文件会自动删除掉。

恢复 binlog 数据

cd /usr/local/mysql/data
#执行命令
mysqlbinlog --start-position=219 --stop-position=1496  mysql-bin.000001 | mysql -uroot -proot
#出现错误
mysqlbinlog: [ERROR] unknown variable 'default-character-set=utf8'
mysqlbinlog --no-defaults --start-position=219 --stop-position=1496  mysql-bin.000001 | mysql -uroot -proot 
# mysqlbinlog --no-defaults --start-position=219 --stop-position=1496 --database=lanebin  mysql-bin.000001 | mysql -uroot -proot -v lanebin

常用参数选项解释：
—start-datetime：从二进制日志中读取指定等于时间戳或者晚于本地服务器的时间
—stop-datetime：从二进制日志中读取指定小于时间戳或者等于本地服务器的时间取值和上述一样
—start-position：从二进制日志中读取指定 position 事件位置作为开始。
—stop-position：从二进制日志中读取指定 position 事件位置作为事件截至
—database=lanebin 指定只恢复 zyyshop 数据库(一台主机上往往有多个数据库，只限本地 log 日志) 不常用选项：
-u —user=name 连接到远程主机的用户名
-p —password[=name] 连接到远程主机的密码
-h —host=name 从远程主机上获取 binlog 日志
—read-from-remote-server 从某个 MySQL 服务器上读取 binlog 日志

索引相关命令

查看索引

show index from table_name \G;
show index from dept \G;

删除索引


drop index indexName ON table_name;
drop index deptcode_1 on dept;
drop index deptcode_2 on dept;
drop index deptcode_3 on dept;

创建普通索引

创建表的时候添加


CREATE TABLE tablename ( [...], INDEX 索引的名字 (字段名));
create table dept (
id int primary key ,
deptname varchar(200), 
deptcode varchar(200), 
index deptname_1(deptname), 
index deptcode_1(deptcode)
) engine =innodb charset =utf8;

修改表结构添加

ALTER TABLE tablename ADD INDEX [索引的名字] (字段名);
alter table dept add index deptcode_2(deptcode);

直接创建索引

CREATE INDEX indexName ON tableName (字段名);
create index deptcode_3 on dept(deptcode);

创建唯一索引

CREATE UNIQUE INDEX 索引的名字 ON tablename (字段名); 
ALTER TABLE tablename ADD UNIQUE INDEX 索引的名字 (字段名); 
CREATE TABLE tablename ( [...], UNIQUE 索引的名字 (字段名) ;

创建主键索引

CREATE TABLE tablename ( [...], PRIMARY KEY (字段名) ); 
ALTER TABLE tablename ADD PRIMARY KEY (字段名);
#可以看出一般创建表的时候指定了primary key则默认创建主键索引

创建复合索引

CREATE INDEX 索引的名字 ON tablename (字段名1，字段名2...); 
ALTER  TABLE tablename ADD INDEX 索引的名字 (字段名1，字段名2...);
CREATE TABLE tablename ( [...], INDEX 索引的名字 (字段名1，字段名2...) );

创建全文索引

CREATE FULLTEXT INDEX 索引的名字 ON tablename (字段名); 
ALTER TABLE tablename ADD FULLTEXT 索引的名字 (字段名); 
CREATE TABLE tablename ( [...], FULLTEXT KEY 索引的名字 (字段名) ;

全文索引语法

#和常用的like模糊查询不同，全文索引有自己的语法格式，使用 match 和 against 关键字进行等值匹配
#注意在innodb引擎模糊匹配至少要有3个字符最多84个字符，否则失效
select * from user where match(name) against('aaa');

布尔模式

#采用布尔模式可以模糊匹配，否则是等值匹配
select * from user where match(name) against('a*' in boolean mode);

自适应哈希

show engine innodb status \G; 
show variables like '%innodb_adaptive%';

EXPLAIN 命令

EXPLAIN SELECT * from user WHERE id < 3;

慢查询

是否开启慢查询

SHOW VARIABLES LIKE 'slow_query_log%';

开启慢查询

SET global slow_query_log = ON; 
SET global slow_query_log_file = 'slow_query_log.log'; 
#记录不使用索引的查询
SET global log_queries_not_using_indexes = ON; 
#记录超过查询时间阈值
SET long_query_time = 0.0001;

查询慢查询日志

show variables like '%slow_query%';

分页查询

查看 profiling 是否开启

#默认关闭
show variables like 'profiling'

开启 profiling 功能

set profiling = 1

查询 sql 执行时间

show profiles

分页查询

SELECT * FROM 表名 LIMIT [offset,] rows
#偏移量从0开始
select * from user limit 1000,10; //查询前1000条数据之后的10条，既是1001~1010

事务相关

查看事务隔离级别

show variables like 'tx_isolation';
#或者
select @@tx_isolation;

设置事务隔离级别

#会话设置
set tx_isolation='READ-UNCOMMITTED';
set tx_isolation='READ-COMMITTED'; 
set tx_isolation='REPEATABLE-READ'; 
set tx_isolation='SERIALIZABLE';
#全局设置加上global
set global tx_isolation='READ-UNCOMMITTED';

开启事务

#开启事务
begin;
#提交事务
commit;
#回滚事务
rollback；

添加锁的语句

在 RR 隔离级别，InnoDB 对于记录加锁行为都是先采用 Next-Key Lock，但是当 SQL 操作含有唯一索引 时，Innodb 会对 Next-Key Lock 进行优化，降级为 RecordLock，仅锁住索引本身而非范围。
1）select ... from 语句：InnoDB 引擎采用 MVCC 机制实现非阻塞读，所以对于普通的 select 语句， InnoDB 不加锁
2）select ... from lock in share mode 语句：追加了共享锁，InnoDB 会使用 Next-Key Lock 锁进行处 理，如果扫描发现唯一索引，可以降级为 RecordLock 锁。
3）select ... from for update 语句：追加了排他锁，InnoDB 会使用 Next-Key Lock 锁进行处理，如果扫 描发现唯一索引，可以降级为 RecordLock 锁。
4）update ... where 语句：InnoDB 会使用 Next-Key Lock 锁进行处理，如果扫描发现唯一索引，可以 降级为 RecordLock 锁。
5）delete ... where 语句：InnoDB 会使用 Next-Key Lock 锁进行处理，如果扫描发现唯一索引，可以降 级为 RecordLock 锁。
6）insert 语句：InnoDB 会在将要插入的那一行设置一个排他的 RecordLock 锁。

表锁相关

#添加表锁的语句
lock table 表名称 read|write,表名称2 read|write;
lock table user read ;
#删除表锁的语句
unlock tables;
#查看表锁的语句
show open tables;

共享锁（行级锁-读锁）

select ...  lock in share mode
select * from user where id =1 lock in share mode

排他锁（行级锁-写锁）

#排他锁的方法是在 SQL 末尾加上 for update，innodb 引擎默认会在 update，delete 语句加上 for update
 select * from user where id =1 for update

死锁相关

#查看死锁日志
show engine innodb status \G;
#查看锁状态变量
show status like 'innodb_row_lock%'
#Innodb_row_lock_current_waits：当前正在等待锁的数量 
#Innodb_row_lock_time：从系统启动到现在锁定总时间长度 
#Innodb_row_lock_time_avg： 每次等待锁的平均时间 
#Innodb_row_lock_time_max：从系统启动到现在等待最长的一次锁的时间 
#Innodb_row_lock_waits：系统启动后到现在总共等待的次数

MySQL 安装(Linux)

MySQL 启动（Mac）

mac 下启动 mysql

MySQL 连接信息

MySQL 配置文件

mysql 位置信息

mac

基本命令

database

table

查看表数据结构信息

查看表创建信息

二进制日志（binary log）

慢查询日志（Slow query log）

查询缓存

存储引擎 innoDB 命令

存储引擎

Buﬀer Pool 配置参数

Change Buﬀer

Log Buﬀer

通用表空间

查看线程

查看文件存储格式

修改文件格式

Undo Log 信息

Redo Log 信息

系统日志命令

Binlog 状态查看

开启 Binlog 功能

常用 Binlog 命令

恢复 binlog 数据

索引相关命令

查看索引

删除索引

创建普通索引

创建唯一 索引

创建主键索引

创建复合索引

创建全文索引

自适应哈希

EXPLAIN 命令

慢查询

是否开启慢查询

开启慢查询

查询慢查询日志

分页查询

查看 profiling 是否开启

开启 profiling 功能

查询 sql 执行时间

分页查询

事务相关

查看事务隔离级别

设置事务隔离级别

开启事务

添加锁的语句

表锁相关

共享锁（行级锁-读锁）

排他锁（行级锁-写锁）

死锁相关

创建唯一索引