MHA基础概念
MHA
MHA（MasterHigh Availability）是一套优秀的MySQL高可用环境下故障切换和主从复制的软件。
MHA 的出现就是解决MySQL 单点的问题。
MySQL故障切换过程中，MHA能做到0-30秒内自动完成故障切换操作。
MHA能在故障切换的过程中最大程度上保证数据的一致性，以达到真正意义上的高可用。
MHA的组成
- MHA Node（数据节点）
MHA Node 运行在每台 MySQL 服务器上。
- MHA Manager（管理节点）
MHA Manager 可以单独部署在一台独立的机器上，管理多个 master-slave 集群；也可以部署在一台 slave 节点上。
MHA Manager 会定时探测集群中的 master 节点。当 master 出现故障时，它可以自动将最新数据的 slave 提升为新的 master， 然后将所有其他的 slave 重新指向新的 master。整个故障转移过程对应用程序完全透明。
MHA的特点
-
自动故障切换过程中，MHA试图从宕机的主服务器上保存二进制日志，最大程度的保证数据不丢失
-
使用半同步复制，可以大大降低数据丢失的风险，如果只有一个slave已经收到了最新的二进制日志，MHA可以将最新的二进制日志应用于其他所有的slave服务器上，因此可以保证所有节点的数据一致性
-
目前MHA支持一主多从架构，最少三台服务，即一主两从
搭建MySQL + MHA
MySQL安装（3台）
下载
wget https://cdn.mysql.com/archives/mysql-5.7/mysql-5.7.28-.el7.x86_64.rpm-bundle.tar
解压
tar xvf mysql-5.7.28-1.el7.x86_64.rpm-bundle.tar
安装
要移除CentOS自带的mariadb-libs，不然会提示冲突
rpm -qa|grep mariadb
rpm -e mariadb-libs-5.5.41-2.el7_0.x86_64 --nodeps
由于MySQL的server服务依赖了common、libs、client，所以需要按照以下顺序依次安装。
RPM是Red Hat公司随Redhat Linux推出的一个软件包管理器，通过它能够更加方便地实现软件的安装。rpm常用的命令有以下几个:
-i, --install 安装软件包
-v, --verbose 可视化，提供更多的详细信息的输出
-h, --hash 显示安装进度
-U, --upgrade=<packagefile>+ 升级软件包
-e, --erase=<package>+ 卸载软件包
--nodeps 不验证软件包的依赖
组合可得到几个常用命令：
安装软件：rpm -ivh rpm包名
升级软件：rpm -Uvh rpm包名
卸载软件：rpm -e rpm包名
查看某个包是否被安装 rpm -qa | grep 软件名称
下面就利用安装命令来安装MySQL：
rpm -ivh mysql-community-common-5.7.28-1.el7.x86_64.rpm
rpm -ivh mysql-community-libs-5.7.28-1.el7.x86_64.rpm
rpm -ivh mysql-community-libs-compat-5.7.28-1.el7.x86_64.rpm
rpm -ivh mysql-community-client-5.7.28-1.el7.x86_64.rpm
rpm -ivh mysql-community-server-5.7.28-1.el7.x86_64.rpm
rpm -ivh mysql-community-devel-5.7.28-1.el7.x86_64.rpm
启动
初始化用户
mysqld --initialize --user=mysql
查看初始密码
cat /var/log/mysqld.log | grep password
修改默认配置文件，支持远程连接
vim /etc/my.cnf
bind-address=0.0.0.0
启动MySQL服务
systemctl start mysqld
配置为开机启动
systemctl enable mysqld
接下来登录MySQL，修改默认密码并支持远程连接
mysql -uroot -p
xxxxxx输入初始密码
SET PASSWORD = PASSWORD('123456');
# 所有用户都能连接服务器
mysql> grant all privileges on *.* to 'root'@'%' identified by '123456' mysql> with grant option;
flush privileges;
关闭防火墙
不同的MySQL直接要互相访问，需要关闭Linux的防火墙，否则就要在配置/etc/sysconfig/iptables中增加规则。
systemctl stop firewalld
MySQL主从配置
Master节点
使用vi /etc/my.cnf命令修改Master配置文件
# bin_log配置
log_bin=mysql-bin # 开启二进制binlog日志
server-id=1
sync-binlog=1 # 表示每次提交事务都要发生 fsync 磁盘同步指令
# 表示不记录自带的数据库的二进制日志
binlog-ignore-db=information_schema
binlog-ignore-db=mysql
binlog-ignore-db=performance_schema
binlog-ignore-db=sys
# relay_log配置
relay_log=mysql-relay-bin
log_slave_updates=1 # 控制master是否把所有的操作写入到binary log，
relay_log_purge=0 #旧relay logs会在SQL线程执行完毕后不会自动删除
重启服务
systemctl restart mysqld
主库给从库授权
登录MySQL，在MySQL命令行执行如下命令：
# 授权从服务器有复制功能
mysql> grant replication slave on *.* to root@'%' identified by '密码';
mysql> grant all privileges on *.* to root@'%' identified by '密码';
mysql> flush privileges;
//查看主库状态信息，例如master_log_file='mysql-in.000007',master_log_pos=154
mysql> show master status;
Slave节点
修改Slave的MySQL配置文件my.cnf，两台Slave的server-id分别设置为2和3
# bin_log配置
log_bin=mysql-bin
# 服务器ID,从库1是2,从库2是3
server-id=2
sync-binlog=1
binlog-ignore-db=information_schema
binlog-ignore-db=mysql
binlog-ignore-db=performance_schema
binlog-ignore-db=sys
#relay_log配置
relay_log=mysql-relay-bin
log_slave_updates=1
relay_log_purge=0
read_only=1 # 设置为 只读模式
重启服务
systemctl restart mysqld
开启同步
登录MySQL，在Slave节点的MySQL命令行执行同步操作，例如下面命令（注意参数与上面show master status操作显示的参数一致）：
mysql> change master to
master_host='192.168.31.199',master_port=3306,master_user='root',master_password='123456',master_log_file='mysql-bin.000007',master_log_pos=154;
mysql> start slave; // 开启同步
mysql -uroot -p123123 -e "show slave status\G" | awk '/Running:/{print}'
Slave_IO_Running: Yes # IO线程是否启动并连接服务器
Slave_SQL_Running: Yes # sql线程是否启动
配置半同步复制
Master节点
登录MySQL，在MySQL命令行执行下面命令安装插件
mysql> install plugin rpl_semi_sync_master soname 'semisync_master.so';
mysql> show variables like '%semi%';
使用vi /etc/my.cnf，修改MySQL配置文件
# 自动开启半同步复制
rpl_semi_sync_master_enabled=ON #表示主上是否开启半同步复制功能，
rpl_semi_sync_master_timeout=1000 # 控制主库等待备库响应的消息时间
#如果超过该阀值1000ms，则认为备库存在问题，将半同步复制修改为异步复制，避免主库的执行事务长时间等待。
重启MySQL服务
systemctl restart mysqld
Slave节点
两台Slave节点都执行以下步骤。
登录MySQL，在MySQL命令行执行下面命令安装插件
install plugin rpl_semi_sync_slave soname 'semisync_slave.so';
使用vi /etc/my.cnf，修改MySQL配置文件
# 自动开启半同步复制
rpl_semi_sync_slave_enabled=ON
重启服务
systemctl restart mysqld
测试半同步状态
首先通过MySQL命令行检查参数的方式，查看半同步是否开启。
mysql> show variables like '%semi%';
+---------------------------------+-------+
| Variable_name | Value |
+---------------------------------+-------+
| rpl_semi_sync_slave_enabled | ON |
| rpl_semi_sync_slave_trace_level | 32 |
+---------------------------------+-------+
2 rows in set (0.00 sec)
然后通过MySQL日志再次确认。
cat /var/log/mysqld.log
可以看到日志中已经启动半同步信息，例如：
Start semi-sync binlog_dump to slave (server_id: 2), pos(mysql-bin.000005, 154)
MySQL并行复制
Master节点
使用vi /etc/my.cnf，修改MySQL配置文件
binlog_group_commit_sync_delay=1000 # 等待1000ms时间后才进行组提交
binlog_group_commit_sync_no_delay_count=100
# 控制MySQL写binlog时使用组提交的机制.MySQL会累计100个事务后再将它们一起写入binlog文件，这可以减少磁盘IO操作，提升写入性能。
重启服务
systemctl restart mysqld
Slave节点
使用vi /etc/my.cnf，修改MySQL配置文件
# 值：DATABASE;基于数据库的并发，每一个数据库都有一个线程去同步，如果只有一个数据库，那其它线程不工作
# 值：LOGICAL_CLOCK时，则可以一个数据库一个多线程同步
slave_parallel_type='LOGICAL_CLOCK'
slave_parallel_workers=8 # 启动8个线程用于同步
relay_log_info_repository='TABLE'
relay_log_recovery=1
#当slave从库宕机后，假如relay-log损坏了，导致一部分中继日志没有处理，则自动放弃所有未执行的relay-log，并且重新从master上获取日志，这样就保证了relay-log的完整性。
重启服务
systemctl restart mysqld
查看中继日志设置，看是否生效
show variables like '%relay_log%';
MHA高可用搭建
四台机器ssh互通
在四台服务器上分别执行下面命令，生成公钥和私钥（注意：连续按换行回车采用默认值）
ssh-keygen -t rsa
在三台MySQL服务器分别执行下面命令，密码输入系统密码，将公钥拷到MHA Manager服务器上
ssh-copy-id 192.168.31.126
之后可以在MHA Manager服务器上检查下，看看.ssh/authorized_keys文件是否包含3个公钥
cat /root/.ssh/authorized_keys
执行下面命令，将MHA Manager的公钥添加到authorized_keys文件中（此时应该包含4个公钥）
cat /root/.ssh/id_rsa.pub >> /root/.ssh/authorized_keys
从MHA Manager服务器执行下面命令，向其他三台MySQL服务器分发公钥信息
scp /root/.ssh/authorized_keys [email protected]:/root/.ssh/authorized_keys
scp /root/.ssh/authorized_keys [email protected]:/root/.ssh/authorized_keys
scp /root/.ssh/authorized_keys [email protected]:/root/.ssh/authorized_keys
可以MHA Manager执行下面命令，检测下与三台MySQL是否实现ssh互通
ssh 192.168.31.199
exit
ssh 192.168.31.165
exit
ssh 192.168.31.142
exit
MHA下载安装
MHA下载
MySQL5.7对应的MHA版本是0.5.8，所以在GitHub上找到对应的rpm包进行下载，MHA manager和node的安装包需要分别下载：
https://github.com/yoshinorim/mha4mysql-manager/releases/tag/v0.58
https://github.com/yoshinorim/mha4mysql-node/releases/tag/v0.58
下载后，将Manager和Node的安装包分别上传到对应的服务器。（可使用WinSCP等工具）。也可以使用wget命令在linux系统直接下载获取，例如
wget https://github.com/yoshinorim/mha4mysql-
manager/releases/download/v0.58/mha4mysql-manager-0.58-
0.el7.centos.noarch.rpm
- 三台MySQL服务器需要安装node
- MHA Manager服务器需要安装manager和node
MHA node安装
MHA的Node依赖于perl-DBD-MySQL，所以要先安装perl-DBD-MySQL
yum install perl-DBD-MySQL -y
wget https://github.com/yoshinorim/mha4mysql-
node/releases/download/v0.58/mha4mysql-node-0.58-0.el7.centos.noarch.rpm
rpm -ivh mha4mysql-node-0.58-0.el7.centos.noarch.rpm
MHA manager安装
MHA的manager又依赖了perl-Config-Tiny、perl-Log-Dispatch、perl-Parallel-ForkManager，分别进行安装
wget http://dl.fedoraproject.org/pub/epel/epel-release-latest-.noarch.rpm
rpm -ivh epel-release-latest-7.noarch.rpm
yum install perl-DBD-MySQL perl-Config-Tiny perl-Log-Dispatch perl-Parallel-ForkManager -y
wget https://github.com/yoshinorim/mha4mysql-node/releases/download/v0.58/mha4mysql-node-0.58-0.el7.centos.noarch.rpm
rpm -ivh mha4mysql-node-0.58-0.el7.centos.noarch.rpm
wget https://github.com/yoshinorim/mha4mysql-manager/releases/download/v0.58/mha4mysql-manager-0.58-.el7.centos.noarch.rpm
rpm -ivh mha4mysql-manager-0.58-0.el7.centos.noarch.rpm
MHA 配置文件
MHA Manager服务器需要为每个监控的 Master/Slave 集群提供一个专用的配置文件，而所有的Master/Slave 集群也可共享全局配置
初始化配置目录
# 目录说明
# /var/log (CentOS目录)
# /mha (MHA监控根目录)
# /app1 (MHA监控实例根目录)
# /manager.log (MHA监控实例日志文件)
mkdir -p /var/log/mha/app1
touch /var/log/mha/app1/manager.log
配置监控全局配置文件
vim /etc/masterha_default.cnf
[server default]
# 主库用户名，在master mysql的主库执行下列命令建一个新用户
# create user 'mha'@'%' identified by '123123';
# grant all on *.* to mha@'%' identified by '123123';
# flush privileges;
user=mha
password=123123
port=3306
#ssh登录账号
ssh_user=root
# 从库复制账号和密码
repl_user=root
repl_password=123456
port=3306
# ping次数
ping_interval=1
# 二次检查的主机
secondary_check_script=masterha_secondary_check -s 192.168.31.199 -s
192.168.31.165 -s 192.168.31.142
配置监控实例配置文件
先使用 mkdir -p /etc/mha 命令创建目录，然后使用 vim /etc/mha/app1.cnf 命令编辑文件
[server default]
# MHA监控实例根目录
manager_workdir=/var/log/mha/app1
# MHA监控实例日志文件
manager_log=/var/log/mha/app1/manager.log
#[serverx] 服务器编号
# hostname 主机名
# candidate_master 可以做主库
# master_binlog_dir binlog日志文件目录
[server1]
hostname=192.168.31.199
candidate_master=1
master_binlog_dir="/var/lib/mysql"
[server2]
hostname=192.168.31.165
candidate_master=1
master_binlog_dir="/var/lib/mysql"
[server3]
hostname=192.168.31.142
candidate_master=1
master_binlog_dir="/var/lib/mysql"
MHA 配置检测
执行ssh通信检测
在MHA Manager服务器上执行：
masterha_check_ssh --conf=/etc/mha/app1.cnf
检测MySQL主从复制
在MHA Manager服务器上执行：
masterha_check_repl --conf=/etc/mha/app1.cnf
出现“MySQL Replication Health is OK.”证明MySQL复制集群没有问题。
MHA Manager启动
在MHA Manager服务器上执行：
nohup masterha_manager --conf=/etc/mha/app1.cnf --remove_dead_master_conf --ignore_last_failover < /dev/null > /var/log/mha/app1/manager.log 2>&1 &
查看监控状态命令如下：
masterha_check_status --conf=/etc/mha/app1.cnf
查看监控日志命令如下：
tail -f /var/log/mha/app1/manager.log
测试MHA故障转移
模拟主节点崩溃
在MHA Manager服务器执行打开日志命令：
tail -200f /var/log/mha/app1/manager.log
关闭Master MySQL服务器服务，模拟主节点崩溃
systemctl stop mysqld
查看MHA日志，可以看到哪台slave切换成了master
show master status;
故障修复
重启宕机服务
systemctl restart mysqld
修复主从
现在主库服务器MySQL(192.168.200.27)查看二进制文件和同步点
# mysql -uroot -p123123 -e "show master status;"
+------------------+----------+--------------+-------------------------------------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+-------------------------------------------------+-------------------+
| mysql-bin.000003 | 829 | | information_schema,mysql,performance_schema,sys | |
+------------------+----------+--------------+-------------------------------------------------+-------------------+
1 row in set (0.00 sec)
在原master节点执行同步，二进制日志名和偏移量使用刚刚从节点，现变master获取到的
mysql> change master to
-> master_host='192.168.200.27',
-> master_user='root',
-> master_password='123123',
-> master_port=3306,
-> master_log_file='mysql-bin.000003',
-> master_log_pos=829;
# 开启同步
mysql> start slave;
在 manager 节点上修改配置文件app1.cnf（再把这个记录添加进去，因为它检测掉失效时候会自动消失）
vim /etc/mha/app1.cnf
[server1]
candidate_master=1
hostname=192.168.200.26
master_binlog_dir="/var/lib/mysql"
在manager节点上启动MHA
masterha_stop --conf=/etc/mha/app1.cnf #停止
# 启动
nohup masterha_manager --conf=/etc/mha/app1.cnf --remove_dead_master_conf --ignore_last_failover < /dev/null > /var/log/mha/app1/manager.log 2>&1 &
# 查看MHA状态，当前master是slave01 --> master
masterha_check_status --conf=/etc/mha/app1.cnf
app1 (pid:3550) is running(0:PING_OK), master:192.168.200.27
一键安装MySQL
下载mysql-5.7.31-linux-glibc2.12-x86_64.tar.gz并将其放入/opt目录下。执行脚本
#!/bin/bash
echo '==开始安装mysql=='
# 得到当前脚本所在绝对路径
DIR=/opt
useradd mysql -s /sbin/nologin -M
tar -zxvf $DIR/mysql-5.7.31-linux-glibc2.12-x86_64.tar.gz -C $DIR/
mv $DIR/mysql-5.7.31-linux-glibc2.12-x86_64 /usr/local/mysql-5.7.30
ln -s /usr/local/mysql-5.7.30 /usr/local/mysql
rpm -e --nodeps mariadb-libs
cat >/etc/my.cnf<<EOF
[mysqld]
character-set-server=utf8mb4
basedir = /usr/local/mysql/
datadir = /usr/local/mysql/data
socket = /tmp/mysql.sock
server_id = 1
port = 13306
log_error = /usr/local/mysql/data/dcs_mysql.err
lower_case_table_names = 1
max_allowed_packet=64M
log_bin_trust_function_creators = ON
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
wait_timeout= 180
interactive_timeout= 180
# 开启慢查询1为启用，0为禁用
slow_query_log = 1
# 开启慢查询时间，此处为1秒，达到此值才记录数据
long_query_time = 3
# 检索行数达到此数值，才记录慢查询日志中
min_examined_row_limit = 100
# 设定每分钟记录到日志的未使用索引的语句数目，超过这个数目后只记录语句数量和花费的总时间
log_throttle_queries_not_using_indexes = 10
# 慢查询日志文件地址
slow_query_log_file = /usr/local/mysql/logs/mysql-slow.log
# 开启记录没有使用索引查询语句
log-queries-not-using-indexes = 1
#5.7版本新增时间戳所属时区参数，默认记录UTC时区的时间戳到慢查询日志，应修改为记录系统时区
log_timestamps=system
[client]
default-character-set=utf8mb4
socket = /tmp/mysql.sock
[mysql]
socket = /tmp/mysql.sock
prompt = mysql> # 定义mysql控制台名
EOF
yum install libaio-devel net-tools -y
# rpm -ivh /opt/libaio-devel-0.3.12-1.i386.rpm
mkdir -p /usr/local/mysql/data
chown -R mysql.mysql /usr/local/mysql/
/usr/local/mysql/bin/mysqld --initialize-insecure --user=mysql --basedir=/usr/local/mysql/ --datadir=/usr/local/mysql/data
cat >/etc/systemd/system/mysqld.service<<EOF
[Unit]
Description=MySQL Server by dcs
Documentation=man:mysqld(8)
Documentation=http://dev.mysql.com/doc/refman/en/using-systemd.html
After=network.target
After=syslog.target
[Install]
WantedBy=multi-user.target
[Service]
User=mysql
Group=mysql
ExecStart=/usr/local/mysql/bin/mysqld --defaults-file=/etc/my.cnf
LimitNOFILE = 5000
EOF
systemctl start mysqld
systemctl enable mysqld
netstat -lntup|grep mysql
ps -ef|grep mysql|grep -v grep
echo 'export PATH=/usr/local/mysql/bin:$PATH' >>/etc/profile
source /etc/profile
mysqladmin -u root password '123123'
echo '==结束安装mysql=='
搭建主从架构：
systemctl stop firewalld && systemctl disable firewalld
setenforce 0
==========master主服务器====================
vim /etc/my.cnf
[mysqld]
server-id = 20 # 三台mysql服务器不一样即可
log_bin = master-bin #开启二进制日志
log-slave-updates = true #允许从服务器进行复制更新二进制文件
===========slave从节点=======================
vim /etc/my.cnf
server-id = 30
#server-id = 40 mysql3则为40，三台服务器 server-id 不能一样
log_bin = master-bin
relay-log = relay-log-bin
relay-log-index = slave-relay-bin.index
===============================================
# 修改完配置文件后，重启服务并将mysql命令和mysql日志文件软链接到/usr/sbin，便于系统识别
systemctl restart mysqld
ln -s /usr/local/mysql/bin/mysql /usr/sbin/
ln -s /usr/local/mysql/bin/mysqlbinlog /usr/sbin/
主库给从库授权：
登录MySQL，在MySQL命令行执行如下命令：
mysql> grant replication slave on *.* to root@'%' identified by '密码';
# 授权从服务器有复制功能
mysql> grant all privileges on *.* to root@'%' identified by '密码';
# 所有用户都能连接服务器
mysql> flush privileges;
//查看主库状态信息，例如master_log_file='mysql-bin.000007',master_log_pos=154
mysql> show master status;
从库开启同步：
登录MySQL，在Slave节点的MySQL命令行执行同步操作，例如下面命令（注意参数与上面show master status操作显示的参数一致）：
change master to
master_host='192.168.200.26',master_port=3306,master_user='root',master_password
='123456',master_log_file='mysql-bin.000007',master_log_pos=154;
start slave; // 开启同步
mysql -uroot -p123123 -e "show slave status\G" | awk '/Running:/{print}'
Slave_IO_Running: Yes # IO线程是否启动并连接服务器
Slave_SQL_Running: Yes # sql线程是否启动
mysql -p123123 -e "set global read_only=1;" # 从节点设置为 只读模式
MySQL优化
show profiles
是MySQL提供可以用来分析当前会话中SQL语句执行的资源消耗情况。可以用于SQL的调优测量。默认情况下，参数处于关闭状态，并保存最近 15 次的运行结果
开启 profiles
- 查看是否开启
show variables like "%profiling%";
+------------------------+-------+
| Variable_name | Value |
+------------------------+-------+
| have_profiling | YES |
| profiling | OFF |
| profiling_history_size | 15 |
+------------------------+-------+
3 rows in set (0.00 sec)
- 开启
set profiling = 1;
开始分析
- 先执行要分析的
SQL语句 - 执行
show profiles;会出现如下结果
mysql> show profiles;
+----------+------------+-----------------------------------------------------------------------------------------------------------------------+
| Query_ID | Duration | Query |
+----------+------------+-----------------------------------------------------------------------------------------------------------------------+
| 5 | 1.03400375 | create database test1 |
| 6 | 0.00030675 | show databases |
| 7 | 0.00035725 | SELECT DATABASE() |
| 8 | 0.00011200 | SELECT DATABASE() |
| 9 | 0.00025950 | show databases |
| 10 | 0.00012000 | show tables |
| 11 | 0.14616200 | create table student(id int not null auto_increment primary key,
name varchar(10) not null,
age int,
tel varchar(11)) |
| 12 | 0.00022225 | show tables |
| 13 | 0.00059125 | desc students |
| 14 | 0.01117450 | desc student |
| 15 | 0.15021075 | insert into student (id,name,age,tel) values (2015,'jagger',23,'12345678901') |
| 16 | 0.00030200 | select * from student |
| 17 | 0.00003825 | update table student add addr varchar(10) |
| 18 | 0.16433725 | alter table student add addr varchar(10) |
| 19 | 0.00034325 | desc student |
+----------+------------+-----------------------------------------------------------------------------------------------------------------------+
15 rows in set, 1 warning (0.01 sec)
- 比如我们分析截图中的第
5条SQL语句
mysql> show profile cpu,block io for query 11;
+--------------------------------+----------+----------+------------+--------------+---------------+
| Status | Duration | CPU_user | CPU_system | Block_ops_in | Block_ops_out |
+--------------------------------+----------+----------+------------+--------------+---------------+
| starting | 0.000113 | 0.000036 | 0.000075 | 0 | 0 |
| checking permissions | 0.000008 | 0.000002 | 0.000004 | 0 | 0 |
| Opening tables | 0.000189 | 0.000062 | 0.000129 | 0 | 0 |
| creating table | 0.005309 | 0.003243 | 0.000000 | 0 | 288 |
| After create | 0.000032 | 0.000029 | 0.000000 | 0 | 0 |
| query end | 0.140427 | 0.001127 | 0.000000 | 0 | 16 |
| Waiting for semi-sync ACK from | 0.000017 | 0.000009 | 0.000000 | 0 | 0 |
| query end | 0.000013 | 0.000014 | 0.000000 | 0 | 0 |
| closing tables | 0.000015 | 0.000015 | 0.000000 | 0 | 0 |
| freeing items | 0.000023 | 0.000023 | 0.000000 | 0 | 0 |
| cleaning up | 0.000018 | 0.000018 | 0.000000 | 0 | 0 |
+--------------------------------+----------+----------+------------+--------------+---------------+
11 rows in set, 1 warning (0.00 sec)
- show profile返回结果字段含义
Status ： sql 语句执行的状态
Duration: sql 执行过程中每一个步骤的耗时
CPU_user: 当前用户占有的 cpu
CPU_system: 系统占有的 cpu
Block_ops_in : I/O 输入
Block_ops_out : I/O 输出 - show profile type 选项
all：显示所有的性能开销信息
block io：显示块 IO 相关的开销信息
context switches: 上下文切换相关开销
cpu：显示 CPU 相关的信息
ipc：显示发送和接收相关的开销信息
memory：显示内存相关的开销信息
page faults：显示页面错误相关开销信息
source：显示和 Source_function、Source_file、Source_line 相关的开销信息
swaps：显示交换次数的相关信息
status出现以下情况的建议
System lock
#确认是由于哪个锁引起的，通常是因为MySQL或InnoDB内核级的锁引起的。建议：如果耗时较大再关注即可，一般情况下都还好
Sending data
#解释：从server端发送数据到客户端，也有可能是接收存储引擎层返回的数据，再发送给客户端，数据量很大时尤其经常能看见。
#备注：Sending Data不是网络发送，是从硬盘读取，发送到网络是Writing to net。建议：通过索引或加上LIMIT，减少需要扫描并且发送给客户端的数据量
Sorting result
正在对结果进行排序，类似Creating sort index，不过是正常表，而不是在内存表中进行排序。
建议：创建适当的索引
Table lock
#表级锁，没什么好说的，要么是因为MyISAM引擎表级锁，要么是其他情况显式锁表
create sort index
#当前的SELECT中需要用到临时表在进行ORDER BY排序
#建议：创建适当的索引
Creating tmp table
#创建临时表。先拷贝数据到临时表，用完后再删除临时表。消耗内存，数据来回拷贝删除，消耗时间，建议：优化索引
converting HEAP to MyISAM
#查询结果太大，内存不够，数据往磁盘上搬了。建议：优化索引，可以调整max_heap_table_size
Copying to tmp table on disk
#把内存中临时表复制到磁盘上，危险！！！建议：优化索引，可以调整tmp_table_size参数，增大内存临时表大小
EXPLAIN用法和结果分析
使用EXPLAIN关键字可以模拟优化器执行SQL查询语句，从而知道MySQL是如何处理你的SQL语句的。分析你的查询语句或是表结构的性能瓶颈。
➤ 通过EXPLAIN，我们可以分析出以下结果：
- 表的读取顺序
- 数据读取操作的操作类型
- 哪些索引可以使用
- 哪些索引被实际使用
- 表之间的引用
- 每张表有多少行被优化器查询
➤ 使用方式如下：
EXPLAIN +SQL语句
mysql> explain select * from student;
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------+
| 1 | SIMPLE | student | NULL | ALL | NULL | NULL | NULL | NULL | 1 | 100.00 | NULL |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)