文章目录
1. MySQL查询配置
1.1 真实生产慢SQL排查
1.1.1 慢查询 - 需要手动设置开启
long_query_time：每条SQL最大查询时间，一旦超过该查询时间则将SQL记录到 慢查询文件中。以待排查解决
show variables like '%query%'
# 修改慢查询时间
set @@long_query_time = 2;
# 全局修改慢查询的时间
set global long_query_time = 2;
# 当前连接启动慢查询 - ( 全局启动需要在配置文件中添加该启动属性 )
# 启动当前连接慢查询
set global slow_query_log = 1;
# 模拟慢查询记录
select sleep(5);
全局启动慢查询 - my.cnf
# 全局启动慢查询
slow_query_log = 1
# 绝对路径
slow_query_log_file = /var/lib/mysql/Centos7-slow.log
/var/lib/mysql/Centos7-slow.log - 慢查询文件构成
1.1.2 分析慢查询文件 - mysqldumpslow
mysql提供了慢日志分析工具 - 故我们可以不用人力总结排查慢SQL
1. mysqldumpslow帮助文档
mysqldumpslow --help
2. 常用的mysqldumpslow语句
# 记录数最多的前N个SQL
mysqldumpslow -s r -t N 慢查询文件相对/绝对地址
# 记录数最少的前N个SQL
mysqldumpslow -s r -r -t N 慢查询文件相对/绝对地址
# 执行次数最多的前N个SQL
mysqldumpslow -s c -t N 慢查询文件相对/绝对地址
# 含有join即连接的SQL语句，执行时间最长的前N个SQL
mysqldumpslow -g 'join' -s t -r -t N 慢查询文件相对/绝对地址
1.1.3 查看SQL性能 - show profile
分析某个SQL语句的资源消耗情况
# 开启性能记录
# 1. have_profiling - 是否开启性能分析
# 2. profiling - SQL性能分析工具是否开启
# 3. profiling_history_size - 显示最新发送给数据库的SQL语句条数 - 最大只能设置100条
show variables like '%profiling%';
# 开启SQL性能记录 - Linux上默认关闭
SET profiling = 1;
# 执行SQL查询语句
# 显示最新的n条语句的查询ID号以及SQL语句
show profiles;
# 查看指定SQL的性能参数 - 利用上面的语句进行查找
show profile 【输出参数】 for query 【SQLID号】【limit 行数】;
1.2 全局SQL日志记录开启 - 默认关闭
注意：永远不要在生产（上线环境）启动这个功能 - 只要是个SQL语句都会被写入文件中
# 查看变量
show VARIABLES like '%general%log%'
# 开启全局查询
set global general_log = 1;
# 将日志文件保存到数据库表中而不是以文件保存 - 默认日志是以文件保存
# set global log_output = 'table'
# 查看日志文件
cat 【general_log_file变量】
# 查看全局日志表 - 前提是你日志是以表的形式保存
# select * from mysql.general_log;
全局日志文件
全局日志表
2. 锁
2.1 表锁 - 读、写锁
- MyISAM引擎 - 粒度大（锁住整个表）、开销小（一个表只要一把锁即可）、加锁快、无死锁、并发度低
- 读锁（表级）：一旦锁住整个表，任何用户只能读取不能执行DML操作 - 任何session都可以添加读锁 - 表一旦有读锁不可添加写锁
- 写锁（表级）：一旦锁住整个表，任何用户都不能读取上锁表数据（DQL语句进入阻塞状态），上锁的session只能执行DML语句 - - 表只能由一个写锁，而且只能由上锁的session解除
语法
# 添加读、写锁
lock table 表名 【read/write】
# 解除当前session所有表锁
unlock tables;
# 显示表的锁数量
show open tables like '表名'
2.1.1 读锁（表级别）
session1窗口
# ①
lock table test read;
# ②执行失败 - 如下图
insert into test(name) values ('test3');
# ⑥ - test表读锁全部被解除才可以进行DML操作
unlock tables;
# ⑦执行成功
insert into test(name) values ('test3');
session2窗口
# ③
lock table test read;
# ④ - 下图
show open tables like 'test';
# ⑤
unlock tables;
2.1.2 写锁（表级别）
session1窗口
# ①
lock table test write;
# ②执行成功
insert into test(name) values ('test3');
# 执行DQL查询语句也会进入无限等待阶段 - 除非释放写锁
# ④
unlock tables;
session2窗口
# ③ - SQL进入阻塞等待执行阶段 - 待session1释放锁才可以上锁 - 如下图 - 执行第④步骤才可以运行
lock table test write/read;
2.2 行锁 - 读、写锁
# 查看数据行的相关信息
show status like 'innodb_row%';
9. 数据库事务隔离级别
答：有四种隔离级别分别是：读未提交、读已提交、可重复读、以及序列化， MySQL可以设置这四种隔离级别，默认是可重复读这个隔离级别，而Oracle只有三种隔离级别，即没有第一种，默认是读已提交这个隔离级别。
- 读未提交的意思是有两个SQL窗口即两个用户在操纵同一个数据库。当前窗口能读取到另一窗口未提交的数据条。即这就是脏读。
- 读已提交，意思是当前窗口只能读取到已经提交的数据，但如果它在一个事务中，连续读取同一条数据，则可能会读到数据不一致的情况，这是另一个用户修改了这条数据，并且提交了，这就是重复读。
- 可重复读意思是两个用户的窗口都开启了事务，一旦用户查询了某表数据，就会把当前事务的数据库所有表进行锁定，即使另一个用户进行DML数据，并且结束事务，当前事务的所有表数据并没有改变，需要等待结束事务之后才会改变，当前用户才能看到另一个用户修改的DML数据。
- 序列化：两个窗口都进行事务开启，锁住select那范围部分数据，其他session对这范围部分的数据可读不可写，当前session可读可写这范围数据，如果有一个窗口进行DML语句，则会进入阻塞状态，并且不能在执行任何操作，需要等待其他所有用户的事务结束才能进行DML数据操作成功 - 被上锁的那部分数据其他session可读
2.2.0 优化建议
2.2.1 Serializable序列化 - 被上锁的那部分数据其他session可读
2.2.1.1 类似锁表
1. 进入MySQL数据库： mysql -uroot -p
2. 进入mysql之后，查看有什么数据库： show databases;
3. 选择数据库： use 数据库名;
4. 修改事务的隔离级别：
set global/session transaction isolation level (Read uncommitted) | (Read committed) | (Repeatable) | (Serializable);
5. 查看当前窗口或者整个数据库的隔离级别的事务隔离级别： select @@tx_isolation
或者 select @@global.tx_isolation
6. 查看事务是否启动自动提交即commit（默认开启）： show session variables like ‘autocommit’;
7. 关闭session自动提交功能： set session autocommit=0;
8. 开启事务： start transaction;
两个窗口同时开启事务 - 开启事务，并且两边都需同时查询同表数据才能模拟出下面的情况
select * from 表 – 没有任何where，已经相当于当前session对这个表上了"表写锁"，不同的是这个写锁，对其他session来说依然可以读取数据，但不能进行DML操作而已
另一个窗口commit进行结束事务：
2.2.1.2 类似锁部分范围数据
session1
# 开启事务
start transaction; # ①
# 当前session锁住id为1到10范围的数据
select * from where id between 1 and 10; # ③
# 事务未结束，依然可以查看最新其他事务添加数据
select * from where id = 200; # ⑥
# commit执行、第⑧步骤执行成功
commit # ⑨
session2
# 开启事务
start transaction; # ②
insert into member(id,year,name) values (200, 100, '测试'); # ④
commit # ⑤
# 开启事务
start transaction; # ⑦
# session1事务未结束，不能修改被sessoin1上锁的范围数据 - 阻塞阶段、等待事务结束
update mmeber set year = 300 where id = 5; # ⑧
commit # ⑩
说明Serializable - 多个窗口同时开启Serializable隔离级别事务，并且有1个以上的用户查询同一个表，则当前session会把当前选中的select部分锁起来，只有由当前session执行这上锁部分的DML，其他session不能进行任何DML操作( 等待阻塞状态 )，锁部分数据期间，一旦有其他session对上锁的这范围表数据进行DML操作，则DML语句进入阻塞状态，只有仅剩你一个用户还在事务的时候，即其他人都commit了，那么你的DML语句就会得到执行。
3. MySQL引擎
InnoDB有行锁实现，并发能力比MyISAM高很多。因为MyISAM一锁就锁住整个表数据，而InnoDB的行锁只对某行数据进行锁定而已。MyISAM锁开销比InnoDB开销小很多，前者只需一个表维护一个锁即可，而后者一个表需要维护多个表
4. 主从复制
MySQL复制是异步的且串行化的
主机数据库 - 记得关闭防火墙，允许3306端口被tcp访问
步骤1 - mysql配置文件 - 必须重启MySQL
[client]
port=3306
default-character-set=utf8
[mysqld]
basedir=E:\MySQL\mysql-5.7.28-winx64\mysql-5.7.28-winx64
tmpdir=E:\MySQL\mysql-5.7.28-winx64\mysql-5.7.28-winx64
datadir=E:\MySQL\mysql-5.7.28-winx64\mysql-5.7.28-winx64\data
character_set_server=utf8
sql_mode=NO_ENGINE_SUBSTITUTION,NO_AUTO_CREATE_USER
explicit_defaults_for_timestamp=true
#skip-grant-tables
# 主从复制关键配置
#每个mysql必须唯一ID
server-id=1
skip_name_resolve=ON
innodb-file-per-table=ON
read-only=0
log-bin=E:\MySQL\mysql-5.7.28-winx64\mysql-5.7.28-winx64\myLog\myLogBin.bin
#log-err=E:\MySQL\mysql-5.7.28-winx64\mysql-5.7.28-winx64\myLog\myLogError.bin
binlog-do-db=masterslave,masterslave2
binlog-ignore-db=mysql
[mysql]
default-character-set=utf8
步骤2 - 权限操作
# 创建一个主机用户
create user '用户名'@'从机ip' identified by '密码';
#赋予该用户在某ip下有拷贝权限
grant replication slave on *.* to '用户名'@'从机ip地址' identified by '密码'
#刷新权限
flush privileges;
#查看某用户在某ip地址登录下有什么权限
show grants for '用户名'@'ip地址';
#主机日志文件、以及日志文件最新行位置 - 需要填写到下面从机的SQL上的
show master status;
从机数据库
步骤1 - mysql配置文件添加下面内容 - 必须重启MySQL
#slave
server-id=2
relay-log=relay-log
relay-log-index=relay-log.index
skip_name_resolve=ON
innodb-file-per-table=ON
步骤2 - 权限操作
# 从机读取主机数据的配置
change master to
master_host='主机IP',
master_user='用户名',
master_password='密码',
master_log_file='主机日志文件',
master_log_pos=从日志文件什么地方开始读;
# 开始读取主机信息 - 启动线程
start/stop slave;
# 查看从机复制状态信息
show slave status\G
#停止从机拷贝主机数据
# 每次停止，准备启动都要更新change语句position或者master_log_file信息
stop slave;
