MySQL 是一个关系型数据库管理系统，由瑞典 MySQL AB 公司开发，目前属于 Oracle 公司。MySQL 是一种关联数据库管理系统，关联数据库将数据保存在不同的表中，而不是将所有数据放在一个大仓库内，这样就增加了速度并提高了灵活性。

简介和安装

简介

数据库管理系统（DBMS）

RDBMS: 关系型数据库管理系统

比较适合于,安全级别要求高的数据以及关系较复杂的数据

NoSQL:非关系型数据库管理系统

适合于高性能存取数据,一般是配合RDBMS进行使用的
针对大数据处理分析,分布式架构更加擅长

数据库管理系统种类

RDBMS

MySQL 、Oracle、MSSQL（SQL Server）、PG

NoSQL：Not Only SQL

键-值（key-value）：Redis, memcached
文档（document）：Mongodb

MySQL行业主流版本：

5.6
5.7
8.0(现在开始研究新特性)

企业版本选择(MySQL分支版本)

Oracle:
      MySQL官方版
红帽  :
      MariaDB
Percona:
      PerconaDB

一般选择官方主流版本:5.6,5.7

安装

1、源码安装，不用传统的configure，用cmake

2、二进制文件–我没有找，直接可执行的

3、rpm安装，下载rpm包，用rpm -ivh –nodeps –force 安装时不再分析包之间的依赖关系而直接安装,对于本机器来说，由于libgc版本过低需要更新，

4、所以还是直接有源安装比较好，用yum/rpm。

在centos7的环境下
# wget http://dev.mysql.com/get/mysql-community-release-el7-5.noarch.rpm
# rpm -ivh mysql-community-release-el7-5.noarch.rpm
# yum install mysql-community-server

2017.07.07

5、最新版本全面安装，先去mysql官网，点击downloads，然后找到最后一个开源的版本，遵守GPL协议的MySQL Community Edition (GPL)，点击进去在左侧导航栏中找到MySQL Community Server点击进去，现在redhat的版本，下在一个500多M的rpm的tar包，当然这个可以网页下载，也可以用wget等linux工具下载

下载后解压安装这个tar包

tar -xvf mysql-5.7.13-1.el7.x86_64.rpm-bundle.tar

原来系统中就携带了mysql的社区版本，需要先卸载，不然影响后面的安装

rpm -qa|grep mariadb
rpm -e mariadb-devel-5.5.44-2.el7.centos.x86_64
rpm -e mariadb-libs-5.5.44-2.el7.centos.x86_64 --nodeps

然后按顺序安装对应的rpm包，要按先后顺序，前后有依赖关系

rpm -ivh mysql-community-common-5.7.13-1.el7.x86_64.rpm
rpm -ivh mysql-community-libs-5.7.13-1.el7.x86_64.rpm
rpm -ivh mysql-community-client-5.7.13-1.el7.x86_64.rpm
rpm -ivh mysql-community-server-5.7.13-1.el7.x86_64.rpm

也可以直接去解压的目录下直接localinstall

yum install "ls mysql*"
yum localinstall *

这样就安装好了，然后需要初始化数据库

mysql_install_db --datadir=/var/lib/mysql   //必须指定datadir,执行后会生成~/.mysql_secret密码文件,这个也可以在配置文件my.cnf中修改。
mysqld --initialize                         //新版的推荐此方法，执行生会在/var/log/mysqld.log生成随机密码

这两条语句执行一个就可以完成初始化，针对的版本不一样

更改mysql数据库目录的所属用户及其所属组，然后启动mysql数据库

chown mysql:mysql /var/lib/mysql -R
systemctl start mysqld.service            //启动mysql数据库服务

然后就可以登录了。

登陆后需要重新设定密码mysql，一定要修改。

基本操作

先来了解一下mysql数据库默认的一些主要目录：

1、数据库目录　/var/lib/mysql/

2、配置文件　　/usr/share/mysql（mysql.server命令及配置文件）

3、相关命令　　/usr/bin (mysqladmin mysqldump等命令)

4、启动脚本　　/etc/rc.d/init.d/（启动脚本文件mysql的目录）

5.日志路劲：/var/log/mysqld.log

mysql服务默认端口3306。可以用netstat -an | grep 3306看看有没有启动服务

一、启动方式

1、使用 service 启动：service mysqld start

2、使用 mysqld 脚本启动：/etc/inint.d/mysqld start

3、使用 safe_mysqld 启动：safe_mysqld&

二、停止

1、使用 service 启动：service mysqld stop

2、使用 mysqld 脚本启动：/etc/inint.d/mysqld stop

3、mysqladmin shutdown

三、重启

1、使用 service 启动：service mysqld restart

2、使用 mysqld 脚本启动：/etc/inint.d/mysqld restart

初始化数据库：mysql_install_db –user=mysql –ldata=/var/lib/mysql/

体系结构与管理

体系结构

C/S(客户端/服务端)模型介绍

登陆

TCP/IP方式（远程、本地）：
mysql -uroot -poldboy123 -h 10.0.0.51 -P3306
Socket方式(仅本地)：
mysql -uroot -poldboy123 -S /tmp/mysql.sock

mysqld程序运行原理

mysqld程序结构

其实正常就是server+存储引擎层，但是server层做落连接和sql处理，这边就细化了，其实sql+连接就是server。

一条SQL语句的执行过程

连接层

提供连接协议：TCP/IP 、SOCKET
提供验证：用户、密码，IP，SOCKET
提供专用连接线程：接收用户SQL，返回结果

通过以下语句可以查看到连接线程基本情况

mysql> show processlist;

SQL层（重点）

接收上层传送的SQL语句
语法验证模块：验证语句语法,是否满足SQL_MODE
语义检查：判断SQL语句的类型
    DDL ：数据定义语言
    DCL ：数据控制语言
    DML ：数据操作语言
    DQL： 数据查询语言
...
权限检查：用户对库表有没有权限
解析器：对语句执行前,进行预处理，生成解析树(执行计划),说白了就是生成多种执行方案.
优化器：根据解析器得出的多种执行计划，进行判断，选择最优的执行计划
    代价模型：资源（CPU IO MEM）的耗损评估性能好坏
执行器：根据最优执行计划，执行SQL语句，产生执行结果
    执行结果：在磁盘的xxxx位置上
提供查询缓存（默认是没开启的），会使用redis tair替代查询缓存功能
提供日志记录（日志管理章节）：binlog，默认是没开启的。

存储引擎层（类似于Linux中的文件系统）

负责根据SQL层执行的结果，从磁盘上拿数据。
将16进制的磁盘数据，交由SQL结构化化成表，
连接层的专用线程返回给用户。

以上就是一条数据重连接到最后的数据落盘在mysqld中所走的流程。

逻辑结构

物理存储结构

库的物理存储结构

用文件系统的目录来存储

表的物理存储结构

MyISAM（一种引擎）的表：
-rw-r----- 1 mysql mysql   10816 Apr 18 11:37 user.frm
-rw-r----- 1 mysql mysql     396 Apr 18 12:20  user.MYD
-rw-r----- 1 mysql mysql    4096 Apr 18 14:48 user.MYI

InnoDB(默认的存储引擎)的表：
-rw-r----- 1 mysql mysql    8636 Apr 18 11:37 time_zone.frm
-rw-r----- 1 mysql mysql   98304 Apr 18 11:37 time_zone.ibd

time_zone.frm：存储列相关信息
time_zone.ibd：数据行+索引

表的段、区、页

扇区是硬件设备传送数据的基本单位（512B）:为了达到可接受的性能，硬盘和类似的设备快速传送几个相邻字节的数据。块设备的每次数据传输操作都是作用于一组称为扇区的相邻字节。大部分磁盘设备中，一个扇区的大小是512字节
块（block）是连续的扇区（正常是8个扇区，4K）:在Linux中，块的大小必须四2的幂，而不能超过一个页框，此外，它必须是扇区大小的整数倍，因为每个块必须包含整数个扇区。因此在80X86体系结构中，允许块的大小为512,1024,2048和4096字节。
页：最小的存储单元，默认16k（连续四个block）
区(簇)：64个连续的页，共1M
段：一个表就是一个段，包含一个或多个区

其实在页下的连续的存储机制，就是为了能够快速的插入和查找，但是这些就数据操作系统的范畴了，其实mysql就是实现了文件系统的重新定义，更加适合它使用，大小都是可以自己定义的。

基础管理

用户

作用：登录，管理数据库逻辑对象

定义：用户名@‘白名单’

白名单支持的方式？

wordpress@'10.0.0.%'
wordpress@'%'
wordpress@'10.0.0.200'
wordpress@'localhost'
wordpress@'db02'
wordpress@'10.0.0.5%'
wordpress@'10.0.0.0/255.255.254.0'

管理操作：

增：
mysql> create user oldboy@'10.0.0.%' identified by '123';
查：
mysql> desc mysql.user;    ---->  authentication_string
mysql> select user ,host ,authentication_string from mysql.user   可以看有哪些用户
改:
mysql> alter user oldboy@'10.0.0.%' identified by '456';
删：
mysql> drop user oldboy@'10.0.0.%';

修改用户密码

方法一:

mysql -u root -p

mysql>use mysql;
mysql> UPDATE user SET password=PASSWORD(“123456”) WHERE user=‘root’;
mysql> FLUSH PRIVILEGES;
mysql> quit;

方法二:

mysql -u root -p
mysql> SET PASSWORD FOR 'root'@'localhost'=PASSWORD('123456');
mysql> QUIT

方法三:用mysqladmin

mysqladmin -u root password "newpass"

如果root已经设置过密码，采用如下方法

mysqladmin -u root password oldpass "newpass"

权限

权限管理操作：

mysql> grant all on wordpress.* to wordpress@'10.0.0.%' identified  by '123';

修改权限：
grant all privileges on 数据库名.* to 用户名@主机名 identified by '密码';
grant all privileges on cachecloud.* to 'cachecloud'@'%' identified by 'cachecloud';
grant all privileges on cachecloud.* to 'cachecloud’@'localhost’ identified by 'cachecloud';

常用权限介绍:

ALL:
SELECT,INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER, CREATE TABLESPACE
ALL : 以上所有权限，一般是普通管理员拥有的
with grant option：超级管理员才具备的，给别的用户授权的功能

权限作用范围:

*.*                  ---->管理员用户
wordpress.*          ---->开发和应用用户
wordpress.t1

查看授权

mysql> show grants for app@'10.0.0.%';

回收权限

revoke  delete on app.*  from app@'10.0.0.%'；

本地管理员用户密码忘记.

[root@db01 ~]# mysqld_safe --skip-grant-tables --skip-networking &
mysql> flush privileges;
mysql>  alter user root@'localhost' identified by '123456';
[root@db01 ~]# pkill mysqld
[root@db01 ~]# systemctl start  mysqld

连接管理

mysql 常用参数：

-u                   用户
-p                   密码
-h                   IP
-P                   端口
-S                   socket文件
-e                   免交互执行命令
<                    导入SQL脚本

配置

初始化配置的方法

[root@db01 ~]# mysqld --help --verbose |grep my.cnf
/etc/my.cnf /etc/mysql/my.cnf /usr/local/mysql/etc/my.cnf ~/.my.cnf
注:
默认情况下，MySQL启动时，会依次读取以上配置文件，如果有重复选项，会以最后一个文件设置的为准。
但是，如果启动时加入了--defaults-file=xxxx时，以上的所有文件都不会读取.

配置文件的书写方式:

[标签]
配置项=xxxx

标签类型：服务端、客户端
服务器端标签：
[mysqld]
[mysqld_safe]
[server]

客户端标签：
[mysql]
[mysqldump]
[client]

配置文件的示例展示：
[root@db01 ~]# cat /etc/my.cnf
[mysqld]
user=mysql
basedir=/app/mysql
datadir=/data/mysql
socket=/tmp/mysql.sock
server_id=6
port=3306
log_error=/data/mysql/mysql.log
[mysql]
socket=/tmp/mysql.sock
prompt=Master [\\d]>
2.5 多实例的应用
2.5.1 准备多个目录
mkdir -p /data/330{7,8,9}/data
2.5.2 准备配置文件
cat > /data/3307/my.cnf <<EOF
[mysqld]
basedir=/app/mysql
datadir=/data/3307/data
socket=/data/3307/mysql.sock
log_error=/data/3307/mysql.log
port=3307
server_id=7
log_bin=/data/3307/mysql-bin
EOF

cat > /data/3308/my.cnf <<EOF
[mysqld]
basedir=/app/mysql
datadir=/data/3308/data
socket=/data/3308/mysql.sock
log_error=/data/3308/mysql.log
port=3308
server_id=8
log_bin=/data/3308/mysql-bin
EOF

cat > /data/3309/my.cnf <<EOF
[mysqld]
basedir=/app/mysql
datadir=/data/3309/data
socket=/data/3309/mysql.sock
log_error=/data/3309/mysql.log
port=3309
server_id=9
log_bin=/data/3309/mysql-bin
EOF

SQL基础应用

SQL介绍

结构化查询语言

5.7 以后符合SQL92严格模式通过sql_mode参数来控制

sql_mode常用值

ONLY_FULL_GROUP_BY
对于GROUP BY聚合操作，如果在SELECT中的列，没有在GROUP BY中出现，那么这个SQL是不合法的，因为列不在GROUP BY从句中

NO_AUTO_VALUE_ON_ZERO
该值影响自增长列的插入。默认设置下，插入0或NULL代表生成下一个自增长值。如果用户希望插入的值为0，而该列又是自增长的，那么这个选项就有用了。

STRICT_TRANS_TABLES
在该模式下，如果一个值不能插入到一个事务中，则中断当前的操作，对非事务表不做限制

NO_ZERO_IN_DATE
在严格模式下，不允许日期和月份为零

NO_ZERO_DATE
设置该值，mysql数据库不允许插入零日期，插入零日期会抛出错误而不是警告

ERROR_FOR_DIVISION_BY_ZERO
在insert或update过程中，如果数据被零除，则产生错误而非警告。如果未给出该模式，那么数据被零除时Mysql返回NULL

NO_AUTO_CREATE_USER
禁止GRANT创建密码为空的用户

NO_ENGINE_SUBSTITUTION
如果需要的存储引擎被禁用或未编译，那么抛出错误。不设置此值时，用默认的存储引擎替代，并抛出一个异常

PIPES_AS_CONCAT
将"||"视为字符串的连接操作符而非或运算符，这和Oracle数据库是一样是，也和字符串的拼接函数Concat想类似

ANSI_QUOTES
启用ANSI_QUOTES后，不能用双引号来引用字符串，因为它被解释为识别符

数据类型、表属性、字符集

数据类型保证数据的准确性和标准性。

1、数值类型

tinyint  ： -128~127
int       ：-2^31~2^31-1
说明：手机号是无法存储到int的。一般是使用char类型来存储收集号

2、字符类型

char(11) ：
定长 的字符串类型,在存储字符串时，最大字符长度11个，立即分配11个字符长度的存储空间，如果存不满，空格填充。
varchar(11):
变长的字符串类型看，最大字符长度11个。在存储字符串时，自动判断字符长度，按需分配存储空间。

3、枚举类型，比较适合于将来此列的值是固定范围内的特点，可以使用enum,可以很大程度的优化我们的索引结构。

enum('bj','tj','sh')：

4、时间类型

DATETIME
范围为从 1000-01-01 00:00:00.000000 至 9999-12-31 23:59:59.999999。
TIMESTAMP
1970-01-01 00:00:00.000000 至 2038-01-19 03:14:07.999999。
timestamp会受到时区的影响

5、二进制类型

表属性

列属性

约束(一般建表时添加):

primary key ：主键约束设置为主键的列，此列的值必须非空且唯一，主键在一个表中只能有一个，但是可以有多个列一起构成。

not null ：非空约束列值不能为空，也是表设计的规范，尽可能将所有的列设置为非空。可以设置默认值为0

unique key ：唯一键列值不能重复

unsigned ：无符号针对数字列，非负数。
其他属性:

key :索引可以在某列上建立索引，来优化查询,一般是根据需要后添加

default :默认值列中，没有录入值时，会自动使用default的值填充

auto_increment:自增长针对数字列，顺序的自动填充数据（默认是从1开始，将来可以设定起始点和偏移量）

comment : 注释

存储引擎:InnoDB（默认的）

字符集和排序规则: utf8 utf8mb4

字符集和校对规则

字符集

utf8
utf8mb4(扩展了存储字节的大小，原来utf8只能存储三个字节，但是后来出来表情需要四个字节的，所以对其进行了扩张)

校对规则（排序规则）

大小写是否敏感

常用SQL分类

DDL：数据定义语言
DCL：数据控制语言
DML：数据操作语言
DQL：数据查询语言

DDL应用

数据定义语言

库

1.创建数据库

create database school;
create schema sch;
show charset;
show collation;
CREATE DATABASE test CHARSET utf8;
create database xyz charset utf8mb4 collate utf8mb4_bin;

建库规范：

1.库名不能有大写字母
2.建库要加字符集
3.库名不能有数字开头
4.库名要和业务相关

建库标准语句

mysql> create database db charset utf8mb4;
mysql> show create database xuexiao;

2.删除(生产中禁止使用)

mysql> drop database oldboy;

3.修改

SHOW CREATE DATABASE school;
ALTER DATABASE school  CHARSET utf8;

注意：修改字符集，修改后的字符集一定是原字符集的严格超集

4.查询库相关信息（DQL）

show databases；
show create database oldboy；

5.进入数据库

use databasename；进入这个数据库

表

1.创建

create table stu(
列1  属性（数据类型、约束、其他属性） ，
列2  属性，
列3  属性
)

实例

CREATE TABLE stu(
id      INT NOT NULL PRIMARY KEY AUTO_INCREMENT COMMENT '学号',
sname   VARCHAR(255) NOT NULL COMMENT '姓名',
sage    TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '年龄',
sgender ENUM('m','f','n') NOT NULL DEFAULT 'n' COMMENT '性别' ,
sfz     CHAR(18) NOT NULL UNIQUE  COMMENT '身份证',
intime  TIMESTAMP NOT NULL DEFAULT NOW() COMMENT '入学时间'
) ENGINE=INNODB CHARSET=utf8 COMMENT '学生表';

建表规范：

1. 表名小写
2. 不能是数字开头
3. 注意字符集和存储引擎
4. 表名和业务有关
5. 选择合适的数据类型
6. 每个列都要有注释
7. 每个列设置为非空，无法保证非空，用0来填充。

2.删除(生产中禁用命令)

drop table t1;

3.修改

在stu表中添加qq列

DESC stu;
ALTER TABLE stu ADD qq VARCHAR(20) NOT NULL UNIQUE COMMENT 'qq号';

在sname后加微信列

ALTER TABLE stu ADD wechat VARCHAR(64) NOT NULL UNIQUE  COMMENT '微信号' AFTER sname ;

在id列前加一个新列num

ALTER TABLE stu ADD num INT NOT NULL COMMENT '数字' FIRST;
DESC stu;

把刚才添加的列都删掉(危险)

ALTER TABLE stu DROP num;
ALTER TABLE stu DROP qq;
ALTER TABLE stu DROP wechat;

修改sname数据类型的属性

ALTER TABLE stu MODIFY sname VARCHAR(128)  NOT NULL ;

将sgender 改为 sg 数据类型改为 CHAR 类型

ALTER TABLE stu CHANGE sgender sg CHAR(1) NOT NULL DEFAULT 'n' ;
DESC stu;

4.表属性查询（DQL）

use school
show tables；
desc stu;
show create table stu；
CREATE TABLE ceshi LIKE stu;

DCL数据控制语言

grant,revoke赋权和收回权限，上面已经讲过，可以查看

DML数据操作语言

对表中的数据行进行增、删、改

1.insert

最标准的insert语句

INSERT INTO stu(id,sname,sage,sg,sfz,intime)
VALUES
(1,'zs',18,'m','123456',NOW());
SELECT * FROM stu;

省事的写法

INSERT INTO stu
VALUES
(2,'ls',18,'m','1234567',NOW());

针对性的录入数据

INSERT INTO stu(sname,sfz)
VALUES ('w5','34445788');

同时录入多行数据

INSERT INTO stu(sname,sfz)
VALUES
('w55','3444578d8'),
('m6','1212313'),
('aa','123213123123');
SELECT * FROM stu;

2.update

DESC stu;
SELECT * FROM stu;
UPDATE stu SET sname='zhao4' WHERE id=2;

注意：update语句必须要加where。

3.delete（危险！！）

DELETE FROM stu  WHERE id=3;

全表删除:

DELETE FROM stu
truncate table stu;
drop table school;

区别:

delete: DML操作, 是逻辑性质删除,逐行进行删除,速度慢.内存不释放，水位线不下降
drop:   删除表的逻辑和物理结构
truncate: DDL操作,对与表段中的数据页进行清空,速度快.内存释放，水位线下降

伪删除：用update来替代delete，最终保证业务中查不到（select）即可

DQL数据查询语言(select )

select语句的执行顺序

1.select
2.from
3.where
4.group by
5.having
6.order by
7.limit

1.单独使用

select @@xxx 查看系统参数

SELECT @@port;
SELECT @@basedir;
SELECT @@datadir;
SELECT @@socket;
SELECT @@server_id;

select 函数();

SELECT NOW();
SELECT DATABASE();
SELECT USER();
SELECT CONCAT("hello world");
SELECT CONCAT(USER,"@",HOST) FROM mysql.user;
SELECT GROUP_CONCAT(USER,"@",HOST) FROM mysql.user;

2.单表子句-from

SELECT 列1,列2 FROM 表
SELECT  *  FROM 表

3.单表子句-where

SELECT col1,col2 FROM TABLE WHERE colN 条件;

where配合等值查询

SELECT * FROM city WHERE countrycode='CHN';

where配合比较操作符(> < >= <= <>)

SELECT * FROM city WHERE population<100;

where配合逻辑运算符(and or )

SELECT * FROM city WHERE countrycode='CHN' AND population>5000000;

where配合模糊查询

SELECT * FROM city WHERE district LIKE 'guang%';

注意:%不能放在前面,因为不走索引.

where配合in语句

SELECT * FROM city WHERE countrycode IN ('CHN' ,'USA');

where配合between and

SELECT * FROM city  WHERE population >1000000 AND population <2000000;
SELECT * FROM city  WHERE population BETWEEN 1000000 AND 2000000;

4.group by + 常用聚合函数

根据 by后面的条件进行分组，方便统计，by后面跟一个列或多个列

常用聚合函数

max() ：最大值

min() ：最小值

avg() ：平均值

sum() ：总和

count() ：个数

group_concat() : 列转行

例子：统计世界上每个国家的总人口数.

USE world
SELECT countrycode ,SUM(population)    FROM  city  GROUP BY countrycode;

例子2：统计中国各个省的总人口数量(练习)

SELECT district,SUM(Population) FROM city  WHERE countrycode='chn' GROUP BY district;

5.having

例子4：统计中国每个省的总人口数，只打印总人口数小于100

SELECT district,SUM(Population)
FROM city
WHERE countrycode='chn'
GROUP BY district
HAVING SUM(Population) < 1000000 ;

6.order by

实现先排序，by后添加条件列

查看中国所有的城市，并按人口数进行排序(从大到小)

SELECT * FROM city WHERE countrycode='CHN' ORDER BY population DESC;

统计中国各个省的总人口数量，按照总人口从大到小排序

SELECT district AS 省 ,SUM(Population) AS 总人口
FROM city
WHERE countrycode='chn'
GROUP BY district
ORDER BY 总人口 DESC ;

统计中国,每个省的总人口,找出总人口大于500w的,并按总人口从大到小排序,只显示前三名

SELECT  district, SUM(population)  FROM  city
WHERE countrycode='CHN'
GROUP BY district
HAVING SUM(population)>5000000
ORDER BY SUM(population) DESC
LIMIT 3 ;

7.limit

LIMIT N ,M —>跳过N,显示一共M行

LIMIT 5,5

SELECT  district, SUM(population)  FROM  city
WHERE countrycode='CHN'
GROUP BY district
HAVING SUM(population)>5000000
ORDER BY SUM(population) DESC
LIMIT 5,5;

8.distinct：去重复

SELECT countrycode FROM city ;
SELECT DISTINCT(countrycode) FROM city  ;

9.联合查询- union all

– 中国或美国城市信息

SELECT * FROM city
WHERE countrycode IN ('CHN' ,'USA');

SELECT * FROM city WHERE countrycode='CHN'
UNION ALL
SELECT * FROM city WHERE countrycode='USA'

说明:一般情况下,我们会将 IN 或者 OR 语句改写成 UNION ALL,来提高性能

UNION 去重复 UNION ALL 不去重复

10.join 多表连接查询

INNER JOIN（内连接,或等值连接）：获取两个表中字段匹配关系的记录。
LEFT JOIN（左连接）：获取左表所有记录，即使右表没有对应匹配的记录。
RIGHT JOIN（右连接）： 与 LEFT JOIN 相反，用于获取右表所有记录，即使左表没有对应匹配的记录。

查询张三的家庭住址

SELECT A.name,B.address FROM
A JOIN  B
ON A.id=B.id
WHERE A.name='zhangsan'

查询一下世界上人口数量小于100人的城市名和国家名

SELECT b.name ,a.name ,a.population
FROM  city  AS a
JOIN  country AS b
ON    b.code=a.countrycode
WHERE  a.Population<100

查询城市shenyang，城市人口，所在国家名（name）及国土面积（SurfaceArea）

SELECT a.name,a.population,b.name ,b.SurfaceArea
FROM city  AS a JOIN country AS b
ON a.countrycode=b.code
WHERE a.name='shenyang';

information_schema.tables视图

DESC information_schema.TABLES

TABLE_SCHEMA    ---->库名
TABLE_NAME      ---->表名
ENGINE          ---->引擎
TABLE_ROWS      ---->表的行数
AVG_ROW_LENGTH  ---->表中行的平均行（字节）
INDEX_LENGTH    ---->索引的占用空间大小（字节）

查看表大小

select concat(round(sum(DATA_LENGTH/1024/1024),2),'MB') as data from information_schema.TABLES 
where table_schema='数据库名' and table_name='表名';

sum(expression)，expression可为字段或者公式

round(x,d)，用于对数据进行四舍五入，x表示要操作的数，d表示要保留的位数

concat(s1,s2,s3,...)，将多个字符串拼接成一个字符串

DATA_LENGTH是字节数，所以通过/1024去转换成MB、GB等

查询整个数据库中所有库和所对应的表信息

SELECT table_schema,GROUP_CONCAT(table_name)
FROM  information_schema.tables
GROUP BY table_schema;

统计所有库下的表个数

SELECT table_schema,COUNT(table_name)
FROM information_schema.TABLES
GROUP BY table_schema

查询所有innodb引擎的表及所在的库

SELECT table_schema,table_name,ENGINE FROM information_schema.`TABLES`
WHERE ENGINE='innodb';

统计world数据库下每张表的磁盘空间占用

SELECT table_name,CONCAT((TABLE_ROWS*AVG_ROW_LENGTH+INDEX_LENGTH)/1024," KB")  AS size_KB
FROM information_schema.tables WHERE TABLE_SCHEMA='world';

统计所有数据库的总的磁盘空间占用

SELECT
TABLE_SCHEMA,
CONCAT(SUM(TABLE_ROWS*AVG_ROW_LENGTH+INDEX_LENGTH)/1024," KB") AS Total_KB
FROM information_schema.tables
GROUP BY table_schema;

mysql -uroot -p123 -e "SELECT TABLE_SCHEMA,CONCAT(SUM(TABLE_ROWS*AVG_ROW_LENGTH+INDEX_LENGTH)/1024,' KB') AS Total_KB FROM information_schema.tables GROUP BY table_schema;"

生成整个数据库下的所有表的单独备份语句

模板语句：

mysqldump -uroot -p123 world city >/tmp/world_city.sql
SELECT CONCAT("mysqldump -uroot -p123 ",table_schema," ",table_name," >/tmp/",table_schema,"_",table_name,".sql" )
FROM information_schema.tables
WHERE table_schema NOT IN('information_schema','performance_schema','sys')
INTO OUTFILE '/tmp/bak.sh' ;

CONCAT("mysqldump -uroot -p123 ",table_schema," ",table_name," >/tmp/",table_schema,"_",table_name,".sql" )

107张表，都需要执行以下2条语句

ALTER TABLE world.city DISCARD TABLESPACE;
ALTER TABLE world.city IMPORT TABLESPACE;
SELECT CONCAT("alter table ",table_schema,".",table_name," discard tablespace")
FROM information_schema.tables
WHERE table_schema='world'
INTO OUTFILE '/tmp/dis.sql';

show 命令

show  databases;                          #查看所有数据库
show tables;                                          #查看当前库的所有表
SHOW TABLES FROM                        #查看某个指定库下的表
show create database world                #查看建库语句
show create table world.city                #查看建表语句
show  grants for  root@'localhost'       #查看用户的权限信息
show  charset；                                   #查看字符集
show collation                                      #查看校对规则
show processlist;                                  #查看数据库连接情况
show index from                                 #表的索引情况
show status                                         #数据库状态查看
SHOW STATUS LIKE '%lock%';         #模糊查询数据库某些状态
SHOW VARIABLES                             #查看所有配置信息
SHOW variables LIKE '%lock%';          #查看部分配置信息
show engines                                       #查看支持的所有的存储引擎
show engine innodb status\G               #查看InnoDB引擎相关的状态信息
show binary logs                                    #列举所有的二进制日志
show master status                                 #查看数据库的日志位置信息
show binlog evnets in                             #查看二进制日志事件
show slave status \G                             #查看从库状态
SHOW RELAYLOG EVENTS               #查看从库relaylog事件信息
desc  (show colums from city)               #查看表的列定义信息

索引及执行计划

索引

索引作用

提供了类似于书中目录的作用,目的是为了优化查询

索引是存储在内存中的，占用空间小，还可以减少与原始数据查询的io的次数。

索引的种类(算法)

B树索引
Hash索引
R树
Full text
GIS

B树

基于不同的查找算法分类介绍

B(B-)tree 其实就是为了降低树的高度，从而减少查询的次数。
B+Tree 在范围查询方面提供了更好的性能(> < >= <= like)，就是在B树的基础上在叶子节点新增的双向链表，减少io的次数
B*Tree 其实也是B+树，只不过是在B+树上做了优化，在所有的相同层次的节点间新增了双向链表，减少io的次数

在功能上的分类

1.聚集(聚簇（区）)索引©（innodb独有的）

聚簇索引是对磁盘上实际数据重新组织以按指定的一个或多个列的值排序的算法。

前提

表中设置了主键,主键列就会自动被作为聚集索引.比如ID not null primary key
如果没有主键,会选择唯一键（unique列）作为聚集索引.
以上都没有，生成隐藏的聚集索引
聚集索引必须在建表时才有意义,一般是表的无关列(ID)

作用

有了聚簇索引，将来插入数据，在同一个区内，将按着ID的顺序有序的在磁盘存储数据。

聚集(聚簇（区）)索引©怎么构建B树结构的?

在建表时,设置了主键列(ID)
在将来录入数据时,就会按照ID列的顺序存储到磁盘上.(我们又称之为聚集索引组织表)
将排好序的整行数据,生成叶子节点（把原始数据按页大小划分生成叶子节点，也就是一个页（page）16K）.可以理解为,磁盘的数据页（连续的数据）就是叶子节点
根据叶子节点生产枝节点，指向每个叶子节点的开头，可以理解为枝节点就是存储的数据范围。
根据枝节点生成根节点，和枝节点一样，存储枝节点的范围

2.辅助索引(S)

就是我们自己新建的索引，因为我们常见的查询还有很多不是根据主健的值进行查询的，这个时候就需要我们新建索引了，也就是辅助索引。辅助索引的主要功能就是构建b树很快的查找到主键的id，然后根据id再去聚簇索引进行查找，这个也是我们常说的回表。

辅助索引(S)怎么构建B树结构的?

索引是基于表中,列(索引键)的值生成的B树结构
首先提取此列所有的值和主键列的所有值，按着新建索引列的值进行自动排序
将排好序的值,均匀的分布到索引树的叶子节点中(16K)，也就是聚簇索引生成B树的一个过程，也就是上面的过程
然后生成此索引键值所对应得后端数据页的指针，生成枝节点和根节点,根据数据量级和索引键长度,生成合适的索引树高度

如何实现辅助的？

在辅助索引中很快的找到你要查找的辅助索引的关键字，根据这个关键字可以找到一起做索引的主健的key，然后去带着聚簇索引key去聚簇索引中查找所有的原始数据。

回表

回表的坏处

io量级变大
iops，io的次数增加
随机io

如何减少回表

查询尽量使用主键
设计合理的联合索引，尽量完全覆盖
使用更加精确的查询条件
优化器算法：MRR

3.聚集索引和辅助索引构成区别

聚集索引只能有一个,非空唯一,一般时主键
辅助索引,可以有多个,时配合聚集索引使用的

详细

    1).聚集索引一个表只能有一个，而非聚集索引一个表可以存在多个
    2).聚集索引存储记录是物理上连续存在，而非聚集索引是逻辑上的连续，物理存储并不连续
    3).聚集索引:物理存储按照索引排序；聚集索引是一种索引组织形式，索引的键值逻辑顺序决定了表数据行的物理存储顺序。非聚集索引:物理存储不按照索引排序；非聚集索引则就是普通索引了，仅仅只是对数据列创建相应的索引，不影响整个表的物理存储顺序.
    4).索引是通过二叉树的数据结构来描述的，我们可以这么理解聚簇索引：索引的叶节点就是数据节点。而非聚簇索引的叶节点仍然是索引节点，只不过有一个指针指向对应的数据块。

4.辅助索引细分

单列

普通的单列辅助索引
唯一索引

索引列的值都是唯一的.

多列

联合索引

多个列作为索引条件,生成索引树,理论上设计的好的,可以减少大量的回表

可单可双

前缀索引

由于我们所选择的索引列的长度过长，会导致树的高度非常高，所以使用前缀一部分来降低索引的覆盖范围，一般mysql索引树的高度建议最多3～4层

关于索引树的高度受什么影响

数据量级, 解决方法:分表,分库,分布式，正常表存储到500到1000W数据，再大就需要分
索引列值过长 , 解决方法:前缀索引

数据类型:选择合适的数据类型

变长长度字符串,使用了char,解决方案:变长字符串使用varchar
enum类型的使用enum ('山东','河北','黑龙江','吉林','辽宁','陕西'......)

索引的基本管理

什么时候建索引？

并不是索引越多越好，需要结构业务建立合适的索引，索引过多，会导致索引过大，数据发生变化的时候，需要频繁的变更索引，还会导致优化器选择偏差。
索引最好建立在经常where，group by ，order by，jion on上

建立索引的流程

查看当前表的索引
分析业务，创建合适的索引
如果觉得不合适，还可以删除索引

实例

索引建立前

desc city;
+-------------+----------+------+-----+---------+----------------+
| Field      | Type    | Null | Key | Default | Extra          |
+-------------+----------+------+-----+---------+----------------+
| ID          | int(11)  | NO  | PRI | NULL    | auto_increment |
| Name        | char(35) | NO  |    |        |                |
| CountryCode | char(3)  | NO  | MUL |        |                |
| District    | char(20) | NO  |    |        |                |
| Population  | int(11)  | NO  |    | 0      |                |
+-------------+----------+------+-----+---------+----------------+
5 rows in set (0.00 sec)

Field :列名字
key :有没有索引,索引类型
PRI: 主键索引
UNI: 唯一索引
MUL: 辅助索引(单列,联和,前缀)

1、单列普通辅助索引

创建

alter table city add index idx_name(name);
create index idx_name1 on city(name);
show index from city;

注意: 以上操作不代表生产操作,我们不建议在一个列上建多个索引同一个表中，索引名不能同名。

删除索引:

alter table city drop index idx_name1;

2、覆盖索引(联合索引)

多列构建一个辅助索引，先以最左列进行排序，然后根据最左列排完顺序的情况下对于最左列相同的情况对第二列进行排序，依次类推的排序形成联合索引。

注意最左原则，比如新建索引inx（a,b,c）—–》相关于创建了a，ab，abc三个索引，看到这三个索引能不能走索引就很能简单的理解了

查询必须包含最左列也就是a列，否则不走索引，只要包含最左列，那么全部覆盖和部分覆盖都是可以走索引的
建立联合索引必须选择重复最少的作为最左列

创建

alter table city add index idx_co_po(countrycode,population);


1. SELECT * FROM t1  WHERE a=    b=
我们建立联合索引时:
ALTER TABLE t1 ADD INDEX idx_a_b(a,b);
ALTER TABLE t1 ADD INDEX idx_b_a(b,a);
以上的查询不考虑索引的顺序,优化器会自动调整where的条件顺序
注意: 索引,我们在这种情况下建索引时,需要考虑哪个列的唯一值更多,哪个放在索引左边.

2.  如果出现where 条件中出现不等值查询条件
DESC  SELECT * FROM t_100w WHERE num <1000 AND k2='DEEF';
我们建索引时:
ALTER TABLE t_100w ADD INDEX idx_2_n(k2,num);
语句书写时
DESC  SELECT * FROM t_100w WHERE  k2='DEEF'  AND  num <1000 ;
3. 如果查询中出现多子句
我们要按照子句的执行顺序进行建立索引.

3、前缀索引

由于我们所选择的索引列的长度过长，会导致树的高度非常高，所以使用前缀一部分来降低索引的覆盖范围，一般mysql索引树的高度建议最多3～4层

创建

alter table city add index idx_di(district(5));

注意：数字列不能用作前缀索引。

4、唯一索引

创建

alter table city add unique index idx_uni1(name);
ERROR 1062 (23000): Duplicate entry 'San Jose' for key 'idx_uni1'

索引压测

1、导入数据

source sql

2、压测命令

mysqlslap --defaults-file=/etc/my.cnf \
--concurrency=100 --iterations=1 --create-schema='oldboy' \
--query="select * from oldboy.t1000w where k2='FGCD'" engine=innodb \
--number-of-queries=200 -uroot -p123 -verbose

3、优化，建立索引

4、再次压测

性能提升了70s-->0.263s,也就是300倍的速度。

5、测试过程

优化前:
[root@db01 ~]# mysqlslap --defaults-file=/etc/my.cnf \
> --concurrency=100 --iterations=1 --create-schema='oldboy' \
> --query="select * from oldboy.t_100w where k2='780P'" engine=innodb \
> --number-of-queries=2000 -uroot -p123 -verbose
mysqlslap: [Warning] Using a password on the command line interface can be insecure.
Benchmark
    Running for engine rbose
    Average number of seconds to run all queries: 701.743 seconds
    Minimum number of seconds to run all queries: 701.743 seconds
    Maximum number of seconds to run all queries: 701.743 seconds
    Number of clients running queries: 100
    Average number of queries per client: 20

优化后:
[root@db01 ~]# mysqlslap --defaults-file=/etc/my.cnf --concurrency=100 --iterations=1 --create-schema='oldboy' --query="select * from oldboy.t_100w where k2='780P'" engine=innodb --number-of-queries=2000 -uroot -p123 -verbose
mysqlslap: [Warning] Using a password on the command line interface can be insecure.
Benchmark
    Running for engine rbose
    Average number of seconds to run all queries: 0.190 seconds
    Minimum number of seconds to run all queries: 0.190 seconds
    Maximum number of seconds to run all queries: 0.190 seconds
    Number of clients running queries: 100
    Average number of queries per client: 20

应用

1、更新数据，索引是实时变化的吗？

聚簇索引是实时更新的。辅助索引不是实时更新的，在5。7之后，在innodb内存存储结构中加入了insert buffer（change），用来实现实时更新辅助索引

2、双11这种情况下如何使用mysql

1、双11的时候，并发度很高，提前一两周将热点商品数据放入到tair（redis，memcached）集群中（查询）
2、设置一个队列，将数据先缓存到队列中，然后缓慢消费进入mysql（存储）
3、所以和索引实时性没有太大关系
4、查看slog，对其进行分析，然后去建索引，加快查询

执行计划获取及分析

介绍

我们执行的sql的方式获取到的是优化器选择完成的,他认为代价最小的执行计划.这边并不是执行时间最短的，而是使用资源（cpu，mem，io）最少的，但是在很多情况下，我们资源充足，而是需要减少执行时间，加快并发，所以需要看执行计划来调整。

作用

语句执行前,先看执行计划信息,可以有效的防止性能较差的语句带来的性能问题，如果业务中出现了慢语句，我们也需要借助此命令进行语句的评估，分析优化方案。

select 获取数据的方法

全表扫描(应当尽量避免,因为性能低)
索引扫描
获取不到数据

执行计划获取

获取优化器选择后的执行计划

执行计划分析

1.重点关注的信息

*代表重要程度

table: city                              ---->查询操作的表 ，在多表的情况可以看出哪些表有问题   **
possible_keys: CountryCode,idx_co_po      ---->可能会走的索引  **
key: CountryCode   ---->真正走的索引    ***
type: ref   ---->索引类型        *****
Extra: Using index condition              ---->额外信息        *****

type详解

总体性能是有差到好的。

1、ALL :全表扫描,不走索引

例子:
1. 查询条件列,没有索引
SELECT * FROM t_100w WHERE k2='780P';
2. 查询条件出现以下语句(辅助索引列)
USE world
DESC city;
DESC SELECT * FROM city WHERE countrycode <> 'CHN';
DESC SELECT * FROM city WHERE countrycode NOT IN ('CHN','USA');
DESC SELECT * FROM city WHERE countrycode LIKE '%CH%';
注意:对于聚集索引列，也就是查询条件是主键,使用以上语句,依然会走索引，如下，当然not in 和like也是走的
DESC SELECT * FROM city WHERE id <> 10;

2、INDEX :全索引扫描

1. 查询需要获取整个索引树种的值时:
DESC  SELECT countrycode  FROM city;

2. 联合索引中,任何一个非最左列作为查询条件时:
idx_a_b_c(a,b,c)  ---> a  ab  abc

SELECT * FROM t1 WHERE b
SELECT * FROM t1 WHERE c

3、RANGE :索引范围扫描

辅助索引> < >= <= LIKE IN OR
主键 <>  NOT IN

例子:
1.
DESC SELECT * FROM city WHERE id<5;
2.
DESC SELECT * FROM city WHERE countrycode LIKE 'CH%';
3.
DESC SELECT * FROM city WHERE countrycode IN ('CHN','USA');

注意:
1和2例子中,可以享受到B+树的优势,但是3例子中是不能享受的.
所以,我们可以将3号列子改写:

DESC SELECT * FROM city WHERE countrycode='CHN'
UNION ALL
SELECT * FROM city WHERE countrycode='USA';

4、ref:非唯一性索引,等值查询

DESC SELECT * FROM city WHERE countrycode='CHN';

5、eq_ref:在多表连接时,连接条件使用了唯一索引(uk pK)

DESC SELECT b.name,a.name FROM city AS a
JOIN country AS b
ON a.countrycode=b.code
WHERE a.population <100;
DESC country

6、system,const :唯一索引的等值查询

DESC SELECT * FROM city WHERE id=10;

extra详解

1、filesort ,文件排序.

SHOW INDEX FROM city;
ALTER TABLE city ADD INDEX CountryCode(CountryCode);
ALTER TABLE city DROP INDEX idx_c_p;

DESC SELECT * FROM city WHERE countrycode='CHN'  ORDER BY population

ALTER TABLE city ADD INDEX idx_(population);
DESC SELECT * FROM city WHERE countrycode='CHN'  ORDER BY population
ALTER TABLE city ADD INDEX idx_c_p(countrycode,population);
ALTER TABLE city DROP INDEX idx_;
ALTER TABLE city DROP INDEX CountryCode;
DESC SELECT * FROM city WHERE countrycode='CHN'  ORDER BY population

结论:
1.当我们看到执行计划extra位置出现filesort,说明由文件排序出现
2.观察需要排序(ORDER BY,GROUP BY ,DISTINCT )的条件,有没有索引
3. 根据子句的执行顺序,去创建联合索引

2.explain(desc)使用场景（面试题）

题目意思: 我们公司业务慢,请你从数据库的角度分析原因

mysql出现性能问题,我总结有两种情况:

1、应急性的慢：突然夯住

应急情况:数据库hang(卡了,资源耗尽)
处理过程:
1.show processlist;  获取到导致数据库hang的语句
2. explain 分析SQL的执行计划,有没有走索引,索引的类型情况
3. 建索引,改语句

2、一段时间慢(持续性的):

(1)记录慢日志slowlog,分析slowlog
(2)explain 分析SQL的执行计划,有没有走索引,索引的类型情况
(3)建索引,改语句

索引优化

为了使索引的使用效率更高，在创建索引时，必须考虑在哪些字段上创建索引和创建什么类型的索引。那么索引设计原则又是怎样的?

索引优化

1、建表时一定要有主键,一般是个无关列

2、选择唯一性索引

唯一性索引的值是唯一的，可以更快速的通过该索引来确定某条记录。

例如，学生表中学号是具有唯一性的字段。为该字段建立唯一性索引可以很快的确定某个学生的信息。如果使用姓名的话，可能存在同名现象，从而降低查询速度。

优化方案:

(1) 如果非得使用重复值较多的列作为查询条件(例如:男女),可以将表逻辑拆分（男生表和女生表）

(2) 可以将此列和其他的查询类,做联和索引

select count(*) from world.city;
select count(distinct countrycode) from world.city;
select count(distinct countrycode,population ) from world.city;

3、(必须的) 为经常需要where 、ORDER BY、GROUP BY,join on等操作的字段，

排序操作会浪费很多时间。

where  A B C      ----》 A  B  C
in
where A   group by B  order by C
A,B，C

如果为其建立索引，优化查询注：如果经常作为条件的列，重复值特别多，可以建立联合索引。

4、尽量使用前缀来索引

如果索引字段的值很长，最好使用值的前缀来索引。

5、限制索引的数目

索引的数目不是越多越好。

可能会产生的问题:

(1) 每个索引都需要占用磁盘空间，索引越多，需要的磁盘空间就越大。
(2) 修改表时，对索引的重构和更新很麻烦。越多的索引，会使更新表变得很浪费时间。
(3) 优化器的负担会很重,有可能会影响到优化器的选择.

percona-toolkit中有个工具,专门分析索引是否有用

6、删除不再使用或者很少使用的索引(percona toolkit)

pt-duplicate-key-checker

表中的数据被大量更新，或者数据的使用方式被改变后，原有的一些索引可能不再需要。数据库管理员应当定期找出这些索引，将它们删除，从而减少索引对更新操作的影响。

7、大表加索引,要在业务不繁忙期间操作

8、尽量少在经常更新值的列上建索引

9、建索引原则

(1) 必须要有主键,如果没有可以做为主键条件的列,创建无关列
(2) 经常做为where条件列  order by  group by  join on, distinct 的条件(业务:产品功能+用户行为)
(3) 最好使用唯一值多的列作为索引,如果索引列重复值较多,可以考虑使用联合索引
(4) 列值长度较长的索引列,我们建议使用前缀索引.
(5) 降低索引条目,一方面不要创建没用索引,不常使用的索引清理,percona toolkit(xxxxx)
(6) 索引维护要避开业务繁忙期

不走索引的情况（开发规范）

1、没有查询条件，或者查询条件没有建立索引

select * from tab;       全表扫描。
select  * from tab where 1=1;

在业务数据库中，特别是数据量比较大的表。是没有全表扫描这种需求。

对用户查看是非常痛苦的。
对服务器来讲毁灭性的。

如果必须全表：可以使用分页来获取部分，或者这种热点数据直接放到redis等缓存数据库中。

2、查询结果集是原表中的大部分数据，应该是25％以上。

查询的结果集，超过了总数行数25%，优化器觉得就没有必要走索引了，因为数据可以有预读的功能，会将连续的空间数据读取出来，会比走索引减少iops。

假如：tab表 id，name    id:1-100w  ，id列有(辅助)索引
select * from tab  where id>500000;
如果业务允许，可以使用limit控制。
结合业务判断，有没有更好的方式。如果没有更好的改写方案
尽量不要在mysql存放这个数据了。放到redis里面。

3、索引本身失效，统计数据不真实

索引有自我维护的能力。对于表内容变化比较频繁的情况下，有可能会出现索引失效。一般是删除重建

现象: 有一条select语句平常查询时很快,突然有一天很慢,会是什么原因

select?  --->索引失效,，统计数据不真实
DML ?   --->锁冲突

4、查询条件使用函数在索引列上，或者对索引列进行运算，运算包括(+，-，*，/，! 等)

例子：
错误的例子：select * from test where id-1=9;
正确的例子：select * from test where id=10;

5、隐式转换（单引号）导致索引失效.这一点应当引起重视.也是开发中经常会犯的错误.

这样会导致索引失效. 错误的例子：
mysql> alter table tab add index inx_tel(telnum);
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0
mysql>
mysql> desc tab;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| id    | int(11)    | YES  |    | NULL    |      |
| name  | varchar(20) | YES  |    | NULL    |      |
| telnum | varchar(20) | YES  | MUL | NULL    |      |
+--------+-------------+------+-----+---------+-------+
3 rows in set (0.01 sec)
mysql> select * from tab where telnum='1333333';
+------+------+---------+
| id  | name | telnum  |
+------+------+---------+
|    1 | a    | 1333333 |
+------+------+---------+
1 row in set (0.00 sec)
mysql> select * from tab where telnum=1333333;
+------+------+---------+
| id  | name | telnum  |
+------+------+---------+
|    1 | a    | 1333333 |
+------+------+---------+
1 row in set (0.00 sec)
mysql> explain  select * from tab where telnum='1333333';
+----+-------------+-------+------+---------------+---------+---------+-------+------+-----------------------+
| id | select_type | table | type | possible_keys | key    | key_len | ref  | rows | Extra                |
+----+-------------+-------+------+---------------+---------+---------+-------+------+-----------------------+

|  1 | SIMPLE      | tab  | ref  | inx_tel      | inx_tel | 63      | const |    1 | Using index condition |
+----+-------------+-------+------+---------------+---------+---------+-------+------+-----------------------+
1 row in set (0.00 sec)
mysql> explain  select * from tab where telnum=1333333;
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra      |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | tab  | ALL  | inx_tel      | NULL | NULL    | NULL |    2 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)
mysql> explain  select * from tab where telnum=1555555;
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra      |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | tab  | ALL  | inx_tel      | NULL | NULL    | NULL |    2 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)
mysql> explain  select * from tab where telnum='1555555';
+----+-------------+-------+------+---------------+---------+---------+-------+------+-----------------------+
| id | select_type | table | type | possible_keys | key    | key_len | ref  | rows | Extra                |
+----+-------------+-------+------+---------------+---------+---------+-------+------+-----------------------+
|  1 | SIMPLE      | tab  | ref  | inx_tel      | inx_tel | 63      | const |    1 | Using index condition |
+----+-------------+-------+------+---------------+---------+---------+-------+------+-----------------------+
1 row in set (0.00 sec)
mysql>

6、<> ，not in 不走索引（辅助索引）

EXPLAIN  SELECT * FROM teltab WHERE telnum  <> '110';
EXPLAIN  SELECT * FROM teltab WHERE telnum  NOT IN ('110','119');

mysql> select * from tab where telnum <> '1555555';
+------+------+---------+
| id  | name | telnum  |
+------+------+---------+
|    1 | a    | 1333333 |
+------+------+---------+
1 row in set (0.00 sec)
mysql> explain select * from tab where telnum <> '1555555';

单独的>,<,in 有可能走，也有可能不走，和结果集有关，尽量结合业务添加limit

or或in 尽量改成union

EXPLAIN  SELECT * FROM teltab WHERE telnum  IN ('110','119');
改写成：
EXPLAIN SELECT * FROM teltab WHERE telnum='110'
UNION ALL
SELECT * FROM teltab WHERE telnum='119'

7、like “%_” 百分号在最前面不走

EXPLAIN SELECT * FROM teltab WHERE telnum LIKE '31%'  走range索引扫描
EXPLAIN SELECT * FROM teltab WHERE telnum LIKE '%110'  不走索引

%linux%类的搜索需求，可以使用elasticsearch+mongodb 专门做搜索服务的数据库产品

存储引擎

相当于Linux文件系统，只不过比文件系统强大

功能

数据读写
数据安全和一致性
提高性能
热备份
自动故障恢复
高可用方面支持

存储引擎种类

Oracle MySQL

InnoDB
MyISAM
MEMORY
ARCHIVE
FEDERATED
EXAMPLE
BLACKHOLE
MERGE
NDBCLUSTER
CSV

引擎种类查看

show engines;

存储引擎是作用在表上的，也就意味着，不同的表可以有不同的存储引擎类型。

PerconaDB:默认是XtraDB
MariaDB:默认是InnoDB
其他的存储引擎支持:
TokuDB
RocksDB
MyRocks和RocksDB差不多
以上三种存储引擎的共同点:压缩比较高,数据插入性能极高
现在很多的NewSQL,使用比较多的功能特性.

inboDB和MyISAM的特点

(1) InnoDB

a,支持ACID，简单地说就是支持事务完整性、一致性；
b,支持行锁，以及类似ORACLE的一致性读，多用户并发；
c,独有的聚集索引主键设计方式，可大幅提升并发读写性能；
d,支持外键；
e,支持崩溃数据自修复；

InnoDB设计目标是处理大容量数据库系统，它的CPU利用率是其它基于磁盘的关系数据库引擎所不能比的。它是一个可靠地事务处理引擎，不支持全文本搜索

(2) MyISAM

a,不支持 每次查询具有原子性
b,只支持表锁
c,强调的是性能，其执行速度比InnoDB类型更快，但是不提供事务支持
d,如果执行大量的SELECT，MyISAM是更好的选择

实例

1、zabbix监控系统架构整改（使用TokuDB）

环境: zabbix 3.2    mariaDB 5.5  centos 7.3
现象 : zabbix卡的要死 ,  每隔3-4个月,都要重新搭建一遍zabbix,存储空间经常爆满.

问题 :

zabbix 版本
数据库版本
zabbix数据库500G,存在一个文件里

优化建议:

数据库版本升级到5.7版本,zabbix升级更高版本
存储引擎改为tokudb
监控数据按月份进行切割(二次开发:zabbix 数据保留机制功能重写,数据库分表)
关闭binlog和双1
参数调整….

优化结果:

监控状态良好

为什么?

原生态支持TokuDB,另外经过测试环境,5.7要比5.5 版本性能高 2-3倍
TokuDB:insert数据比Innodb快的多，数据压缩比要Innodb高
监控数据按月份进行切割,为了能够truncate每个分区表,立即释放空间
关闭binlog —–>减少无关日志的记录.
参数调整…—–>安全性参数关闭,提高性能.

2、InnoDB和MyISAM存储引擎的替换

环境: centos 5.8 ,MySQL 5.0版本,MyISAM存储引擎,网站业务(LNMP),数据量50G左右

现象问题: 业务压力大的时候,非常卡;经历过宕机,会有部分数据丢失.

问题分析:

MyISAM存储引擎表级锁,在高并发时,会有很高锁等待
MyISAM存储引擎不支持事务,在断电时,会有可能丢失数据

职责

监控锁的情况:有很多的表锁等待
存储引擎查看:所有表默认是MyISAM

解决方案:

升级MySQL 5.6.10版本
迁移所有表到新环境
开启双1安全参数

InnoDB存储引擎介绍

在MySQL5.5版本之后，默认的存储引擎，提供高可靠性和高性能。

特点

事务（Transaction）
MVCC（Multi-Version Concurrency Control多版本并发控制）
行级锁(Row-level Lock)
ACSR（Auto Crash Safey Recovery）自动的故障安全恢复，主要使用了redo日志，通过redo日志进行数据恢复。
支持热备份(Hot Backup)
Replication: Group Commit , GTID (Global Transaction ID) ,多线程(Multi-Threads-SQL )

存储引擎基本操作

1、使用 SELECT 确认会话存储引擎

SELECT @@default_storage_engine;

2、设置存储引擎(不代表生产操作)

会话级别:
set default_storage_engine=myisam;

全局级别(仅影响新会话):
set global default_storage_engine=myisam;
重启之后,所有参数均失效.

如果要永久生效:
写入配置文件
vim /etc/my.cnf
[mysqld]
default_storage_engine=myisam

存储引擎是表级别的,每个表创建时可以指定不同的存储引擎,但是我们建议统一为innodb.

3、SHOW 确认每个表的存储引擎：

SHOW CREATE TABLE City\G;
SHOW TABLE STATUS LIKE 'CountryLanguage'\G

4、INFORMATION_SCHEMA 确认每个表的存储引擎

[world]>select table_schema,table_name ,engine from information_schema.tables where table_schema not in ('sys','mysql','information_schema','performance_schema');
Master [world]>show table status;
Master [world]>show create table city;

5、修改一个表的存储引擎

db01 [oldboy]>alter table t1 engine innodb;
注意：此命令我们经常使用他，进行innodb表的碎片整理

平常处理过的MySQL问题–碎片处理

环境:centos7.4,MySQL 5.7.20,InnoDB存储引擎
业务特点:数据量级较大,经常需要按月删除历史数据.
问题:磁盘空间占用很大,不释放

处理方法:
以前:
将数据逻辑导出,手工drop表,然后导入进去

现在:
对表进行按月进行分区表(partition,中间件），业务替换为truncate方式，可以删除磁盘空间

扩展:如何批量修改

需求:将zabbix库中的所有表,innodb替换为tokudb

select concat("alter table zabbix.",table_name," engine tokudb;") from
information_schema.tables where table_schema='zabbix' into outfile '/tmp/tokudb.sql';

InnoDB存储引擎物理存储结构

1、最直观的存储方式(/data/mysql/data)

ibdata1：系统数据字典信息(统计信息)，UNDO表空间等数据
ib_logfile0 ~ ib_logfile1: REDO日志文件，事务日志文件。
ibtmp1：临时表空间磁盘位置，存储临时表
frm：存储表的列信息
ibd：表的数据行和索引

2、表空间(Tablespace)

共享表空间

需要将所有数据存储到同一个表空间中，管理比较混乱

5.5版本出现的管理模式，也是默认的管理模式。
5.6版本以，共享表空间保留，只用来存储:数据字典信息,undo,临时表。
5.7 版本,临时表被独立出来了
8.0版本,undo也被独立出去了

具体变化参考官方文档:

https://dev.mysql.com/doc/refman/5.6/en/innodb-architecture.html https://dev.mysql.com/doc/refman/5.7/en/innodb-architecture.html https://dev.mysql.com/doc/refman/5.8/en/innodb-architecture.html

共享表空间设置

共享表空间设置(在搭建MySQL时，初始化数据之前设置到参数文件中)

[(none)]>select @@innodb_data_file_path;
[(none)]>show variables like '%extend%';
innodb_data_file_path=ibdata1:512M:ibdata2:512M:autoextend
innodb_autoextend_increment=64

独立表空间

从5.6，默认表空间不再使用共享表空间，替换为独立表空间。主要存储的是用户数据

存储特点为：一个表一个ibd文件，存储数据行和索引信息

基本表结构元数据存储：

xxx.frm
最终结论：
      元数据            数据行+索引
mysql表数据    =（ibdataX+frm）+ibd(段、区、页)
        DDL             DML+DQL

MySQL的存储引擎日志：

Redo Log: ib_logfile0  ib_logfile1，重做日志，主要记录操作的日志（基于WAL机制）和版本号，用于系统恢复的时候进行数据恢复，也就是前滚。
Undo Log: ibdata1 ibdata2(存储在共享表空间中)，回滚日志，一般存储的是执行语句的相反语句，用于回滚。
临时表:ibtmp1，在做join union操作产生临时数据，用完就自动

独立表空间设置问题

db01 [(none)]>select @@innodb_file_per_table;
+-------------------------+
| @@innodb_file_per_table |
+-------------------------+
|                      1 |
+-------------------------+
alter table city dicard tablespace;
alter table city import tablespace;

6.1.5 真实案例

案例背景:

硬件及软件环境:
联想服务器（IBM）
磁盘500G 没有raid
centos 6.8
mysql 5.6.33  innodb引擎  独立表空间
备份没有，日志也没开

开发用户专用库:
jira(bug追踪) 、 confluence(内部知识库)    ------>LNMT

故障描述:

断电了，启动完成后“/” 只读
fsck  重启,系统成功启动,mysql启动不了。
结果：confulence库在  ， jira库不见了

求助内容:

求助：
这种情况怎么恢复？
我问：
有备份没
求助：
连二进制日志都没有，没有备份，没有主从
我说：
没招了，jira需要硬盘恢复了。
求助：
1、jira问题拉倒中关村了
2、能不能暂时把confulence库先打开用着
将生产库confulence，拷贝到1:1虚拟机上/var/lib/mysql,直接访问时访问不了的

问：有没有工具能直接读取ibd
我说：我查查，最后发现没有

我想出一个办法来：

表空间迁移:
create table xxx
alter table  confulence.t1 discard tablespace;
alter table confulence.t1 import tablespace;
虚拟机测试可行。
处理问题思路:

confulence库中一共有107张表。
1、创建107和和原来一模一样的表。
他有2016年的历史库，我让他去他同时电脑上 mysqldump备份confulence库
mysqldump -uroot -ppassw0rd -B  confulence --no-data >test.sql
拿到你的测试库，进行恢复
到这步为止，表结构有了。
2、表空间删除。
select concat('alter table ',table_schema,'.'table_name,' discard tablespace;') from information_schema.tables where table_schema='confluence' into outfile '/tmp/discad.sql';
source /tmp/discard.sql
执行过程中发现，有20-30个表无法成功。主外键关系
很绝望，一个表一个表分析表结构，很痛苦。
set foreign_key_checks=0 跳过外键检查。
把有问题的表表空间也删掉了。
3、拷贝生产中confulence库下的所有表的ibd文件拷贝到准备好的环境中
select concat('alter table ',table_schema,'.'table_name,' import tablespace;') from information_schema.tables where table_schema='confluence' into outfile '/tmp/discad.sql';
4、验证数据
表都可以访问了，数据挽回到了出现问题时刻的状态（2-8）

事务

1、单体事务

事务是一组原子性sql查询语句，被当作一个工作单元。若mysql对改事务单元内的所有sql语句都正常的执行完，则事务操作视为成功，所有的sql语句才对数据生效，若sql中任意不能执行或出错则事务操作失败，所有对数据的操作则无效（通过回滚恢复数据）。事务有四个属性：

事务的ACID特性

Atomic（原子性）
所有语句作为一个单元全部成功执行或全部取消。不能出现中间状态。

Consistent（一致性）
如果数据库在事务开始时处于一致状态，则在执行该事务期间将保留一致状态。

Isolated（隔离性）
事务之间不相互影响。未提交的事务不应该影响其他事务

Durable（持久性）
事务成功完成后，所做的所有更改都会准确地记录在数据库中。所做的更改不会丢失。

事务的生命周期（事务控制语句）

事务的开始
begin
说明:在5.5 以上的版本，不需要手工begin，只要你执行的是一个DML，会自动在前面加一个begin命令。

事务的结束
commit：提交事务
完成一个事务，一旦事务提交成功 ，就说明具备ACID特性了。

rollback ：回滚事务
将内存中，已执行过的操作，回滚回去


自动提交策略（autocommit）
db01 [(none)]>select @@autocommit;
db01 [(none)]>set autocommit=0;
db01 [(none)]>set global autocommit=0;
注：
自动提交是否打开，一般在有事务需求的MySQL中，将其关闭
不管有没有事务需求，我们一般也都建议设置为0，可以很大程度上提高数据库性能
(1)
set autocommit=0;
set global autocommit=0;
(2)
vim /etc/my.cnf
autocommit=0

隐式提交语句
用于隐式提交的 SQL 语句：
begin
a
b
begin

SET AUTOCOMMIT = 1

导致提交的非事务语句：
DDL语句： （ALTER、CREATE 和 DROP）
DCL语句： （GRANT、REVOKE 和 SET PASSWORD）
锁定语句：（LOCK TABLES 和 UNLOCK TABLES）
导致隐式提交的语句示例：
TRUNCATE TABLE
LOAD DATA INFILE
SELECT FOR UPDATE

开始事务流程：

1、检查autocommit是否为关闭状态

select @@autocommit;
或者：
show variables like 'autocommit';

2、开启事务,并结束事务

begin
delete from student where name='alexsb';
update student set name='alexsb' where name='alex';
rollback;

begin
delete from student where name='alexsb';
update student set name='alexsb' where name='alex';
commit;

3、InnoDB 事务的ACID如何保证?

概念

redo log ---> 重做日志 ib_logfile0~1   50M   , 轮询使用
redo log buffer ---> redo内存区域
ibd     ----> 存储 数据行和索引
buffer pool --->缓冲区池,数据和索引的缓冲
LSN : 日志序列号
磁盘数据页,redo文件,buffer pool,redo buffer
MySQL 每次数据库启动,都会比较磁盘数据页和redolog的LSN,必须要求两者LSN一致数据库才能正常启动
WAL : write ahead log 日志优先写的方式实现持久化
脏页: 内存脏页,内存中发生了修改,没写入到磁盘之前,我们把内存页称之为脏页.
CKPT:Checkpoint,检查点,就是将脏页刷写到磁盘的动作
TXID: 事务号,InnoDB会为每一个事务生成一个事务号,伴随着整个事务.

redo 前滚

redo,顾名思义“重做日志”，是事务日志的一种。
在事务ACID过程中，实现的是“D”持久化的作用。对于AC也有相应的作用
redo的日志文件：iblogfile0 iblogfile1
redo的buffer:数据页的变化信息+数据页当时的LSN号
LSN：日志序列号磁盘数据页、内存数据页、redo buffer、redolog

redo的刷新策略

commit;
刷新当前事务的redo buffer到磁盘
还会顺便将一部分redo buffer中没有提交的事务日志也刷新到磁盘

MySQL CSR——前滚

MySQL : 在启动时,必须保证redo日志文件和数据文件LSN必须一致, 如果不一致就会触发CSR,最终保证一致

情况一:

我们做了一个事务,begin;update;commit.

1.在begin ,会立即分配一个TXID=tx_01.
2.update时,会将需要修改的数据页(dp_01,LSN=101),加载到data buffer中
3.DBWR线程,会进行dp_01数据页修改更新,并更新LSN=102
4.LOGBWR日志写线程,会将dp_01数据页的变化+LSN+TXID存储到redobuffer
5.执行commit时,LGWR日志写线程会将redobuffer信息写入redolog日志文件中,基于WAL原则,

在日志完全写入磁盘后,commit命令才执行成功,(会将此日志打上commit标记)
6.假如此时宕机,内存脏页没有来得及写入磁盘,内存数据全部丢失
7.MySQL再次重启时,必须要redolog和磁盘数据页的LSN是一致的.但是,此时dp_01,TXID=tx_01磁盘是LSN=101,dp_01,TXID=tx_01,redolog中LSN=102

MySQL此时无法正常启动,MySQL触发CSR.在内存追平LSN号,触发ckpt,将内存数据页更新到磁盘,从而保证磁盘数据页和redolog LSN一值.这时MySQL正长启动以上的工作过程,我们把它称之为基于REDO的”前滚操作”

undo 回滚日志

undo,顾名思义“回滚日志”

在事务ACID过程中，实现的是“A” 原子性的作用，另外CI也依赖于Undo

在rolback时,将数据恢复到修改之前的状态
在CSR实现的是,将redo当中记录的未提交的时候进行回滚.
undo提供快照技术,保存事务修改之前的数据状态.保证了MVCC,隔离性,mysqldump的热备

隔离级别

影响到数据的读取,默认的级别是RR模式.可通过transaction_isolation 隔离级别(参数)来调整。

RU （read uncommit）  : 读未提交,会出现脏读,不可重复读，幻读，一般生产不会设置出现
    脏读：其他事务还没有提交的，被查询的时候查询出来
    不可重复读：在操作本事务的时候，不同的时候对同一个数据有不同的值，也就是在操作的时候其他commit被查询出来了
    幻读：就是在范围查询修改的时候，其他事务在在这一范围内进行操作，出现修改不如意的情况。
RC （read commit） : 读已提交,可能出现幻读，不可重复读,可以防止脏读，主要是因为它只读取commit后的数据，所以可以防止脏读
RR （read review） : 可重复读，默认级别。解决了脏读（和rc一样），不可重复读（主要是使用快照功能，每次读取的都是这个快照的数据）快照可以防止"幻读"现象 ,但是不可能完全解决，完全解决需要利用的是undo的快照技术+GAP(间隙锁)+NextLock(下键锁)，这个隔离是借助mvcc实现的
SR （read）  : 可串行化,就是串行，可以解决上面所有问题，可以防止死锁,但是并发事务性能较差

实战解答

问题一：Mysql怎么保证一致性的？
OK，这个问题分为两个层面来说。
从数据库层面，数据库通过原子性、隔离性、持久性来保证一致性。也就是说ACID四大特性之中，C(一致性)是目的，A(原子性)、I(隔离性)、D(持久性)是手段，是为了保证一致性，数据库提供的手段。数据库必须要实现AID三大特性，才有可能实现一致性。例如，原子性无法保证，显然一致性也无法保证。
但是，如果你在事务里故意写出违反约束的代码，一致性还是无法保证的。例如，你在转账的例子中，你的代码里故意不给B账户加钱，那一致性还是无法保证。因此，还必须从应用层角度考虑。
从应用层面，通过代码判断数据库数据是否有效，然后决定回滚还是提交数据！

问题二: Mysql怎么保证原子性的？
OK，是利用Innodb的undo log。
undo log名为回滚日志，是实现原子性的关键，当事务回滚时能够撤销所有已经成功执行的sql语句，他需要记录你要回滚的相应日志信息。
例如
(1)当你delete一条数据的时候，就需要记录这条数据的信息，回滚的时候，insert这条旧数据
(2)当你update一条数据的时候，就需要记录之前的旧值，回滚的时候，根据旧值执行update操作
(3)当年insert一条数据的时候，就需要这条记录的主键，回滚的时候，根据主键执行delete操作
undo log记录了这些回滚需要的信息，当事务执行失败或调用了rollback，导致事务需要回滚，便可以利用undo log中的信息将数据回滚到修改之前的样子。
ps:具体的undo log日志长啥样，这个可以写一篇文章了。而且写出来，看的人也不多，姑且先这么简单的理解吧。

问题三: Mysql怎么保证持久性的？
OK，是利用Innodb的redo log。
正如之前说的，Mysql是先把磁盘上的数据加载到内存中，在内存中对数据进行修改，再刷回磁盘上。如果此时突然宕机，内存中的数据就会丢失。
怎么解决这个问题？
简单啊，事务提交前直接把数据写入磁盘就行啊。
这么做有什么问题？
只修改一个页面里的一个字节，就要将整个页面刷入磁盘，太浪费资源了。毕竟一个页面16kb大小，你只改其中一点点东西，就要将16kb的内容刷入磁盘，听着也不合理。
毕竟一个事务里的SQL可能牵涉到多个数据页的修改，而这些数据页可能不是相邻的，也就是属于随机IO。显然操作随机IO，速度会比较慢。
于是，决定采用redo log解决上面的问题。当做数据修改的时候，不仅在内存中操作，还会在redo log中记录这次操作。当事务提交的时候，会将redo log日志进行刷盘(redo log一部分在内存中，一部分在磁盘上)。当数据库宕机重启的时候，会将redo log中的内容恢复到数据库中，再根据undo log和binlog内容决定回滚数据还是提交数据。
采用redo log的好处？
其实好处就是将redo log进行刷盘比对数据页刷盘效率高，具体表现如下
redo log体积小，毕竟只记录了哪一页修改了啥，因此体积小，刷盘快。
redo log是一直往末尾进行追加，属于顺序IO。效率显然比随机IO来的快。
ps:不想具体去谈redo log具体长什么样，因为内容太多了。

问题四: Mysql怎么保证隔离性的？
OK,利用的是锁和MVCC机制。还是拿转账例子来说明，有一个账户表如下
表名t_balance
在这里插入图片描述其中id是主键，user_id为账户名，balance为余额。还是以转账两次为例，如下图所示
至于MVCC,即多版本并发控制(Multi Version Concurrency Control),一个行记录数据有多个版本对快照数据，这些快照数据在undo log中。
如果一个事务读取的行正在做DELELE或者UPDATE操作，读取操作不会等行上的锁释放，而是读取该行的快照版本。
由于MVCC机制在可重复读(Repeateable Read)和读已提交(Read Commited)的MVCC表现形式不同，就不赘述了。
但是有一点说明一下，在事务隔离级别为读已提交(Read Commited)时，一个事务能够读到另一个事务已经提交的数据，是不满足隔离性的。但是当事务隔离级别为可重复读(Repeateable Read)中，是满足隔离性的。

2、分布式事务

mysql好像是从5.0开始支持分布式事务

分布式事务原理：分段式提交

分布式事务通常采用2PC协议，全称Two Phase Commitment Protocol。该协议主要为了解决在分布式数据库场景下，所有节点间数据一致性的问题。分布式事务通过2PC协议将提交分成两个阶段：

prepare；
commit/rollback

阶段一为准备（prepare）阶段。即所有的参与者准备执行事务并锁住需要的资源。参与者ready时，向transaction manager报告已准备就绪。

阶段二为提交阶段（commit）。当transaction manager确认所有参与者都ready后，向所有参与者发送commit命令。

传统意义的2pc在分布式环境下具有非常严重的局限性，体现在：

使用全局事务，数据被锁住的时间横跨整个事务，直到事务结束才释放，在高并发和涉及业务模块较多的情况下 对数据库的性能影响较大。
在技术栈比较复杂的分布式应用中，存储组件可能会不支持 XA 协议。

但是两阶段提交的思想十分常见，InnoDB 存储引擎中的 Redo log 与 Binlog 的本地事务提交过程也使用了二阶段提交的思想，让这两个日志的状态保持逻辑上的一致。

可以使用分布式事务解决方案来来解决

锁

mysql一般不需要我们自己加锁，innodb引擎会自己给我们的语句进行加锁，只要了解锁的机制就好，主要在优化的时候知道走什么锁。

分类

资源分配

内存锁

mutex，lanth

对象锁

1、MDL                   ：元数据锁，也是锁表，就是对information_sechme表进行加锁，一般DDL操作就会进行加锁，所以在生产上需要减少DDL操作，因为元数据表被锁，会影响所有的操作。
2、table lock            ：表锁，锁起整张表，开销比较大，是myisam支持的锁级别
3、record（row）lock      ：行锁，也叫记录锁，其实锁住的是索引，也叫索引锁，主要是对数据行（的索引）进行加锁，是innodb支持的锁级别
4、GAP                   ：间隙锁，锁住两个数据行之间的锁，也是锁住这些索引
5、next lock             ：下一键锁，也是row+GAP

粒度

1、MDL                   ：元数据锁，也是锁表，就是对information_sechme表进行加锁，一般DDL操作就会进行加锁，所以在生产上需要减少DDL操作，因为元数据表被锁，会影响所有的操作。
2、table lock            ：表锁，锁起整张表，开销比较大
3、record（row）lock      ：行锁，也叫记录锁，其实锁住的是索引，也叫索引锁，主要是对数据行（的索引）进行加锁
4、GAP                   ：间隙锁，锁住两个数据行之间的锁，也是锁住这些索引
5、next lock             ：下一键锁，也是row+GAP

功能

两种行级锁（row-level locking）

共享锁（S）：事务执行read时，可以持有这个锁，读锁
排它锁（X）：事务执行update或delete时，可以持有这个锁，写锁

当事务1持有某一行数据的S锁，其他事务就只能申请这一行数据的S锁，不能申请X锁。

当事务1持有某一行数据的X锁，其他事务不能申请这一行的S和X锁。

两种表级锁（table-level locking）

意向共享锁IS（intention shared lock）：表明事务之后要申请表中特定行的S锁    读锁
意向排他锁IX（intention exclusive lock）：表明事务之后要申请表中特定行的X锁 写锁

先走意向锁，在去具体的行锁。

其实其他的上面的对象锁，都是通过读写锁进行实现的。

读写锁(悲观锁)

对于锁分配的优先级，是： LOW_PRIORITY WRITE < READ < WRITE

读锁：也叫共享锁、S锁，若事务T对数据对象A加上S锁，则事务T可以读A但不能修改A，其他事务只能再对A加S锁，而不能加X锁，直到T释放A上的S 锁。这保证了其他事务可以读A，但在T释放A上的S锁之前不能对A做任何修改。

读锁：

用法： LOCK TABLE table_name [ AS alias_name ] READ

指定数据表，LOCK类型为READ即可，AS别名是可选参数，如果指定别名，使用时也要指定别名才可


申请读锁前提：当前没有线程对该数据表使用写锁，否则申请会阻塞。

操作限制：其他线程可以对锁定表使用读锁；其他线程不可以对锁定表使用写锁

写锁：又称排他锁、X锁。若事务T对数据对象A加上X锁，事务T可以读A也可以修改A，其他事务不能再对A加任何锁，直到T释放A上的锁。这保证了其他事务在T释放A上的锁之前不能再读取和修改A。

写锁：

用法： LOCK TABLE table_name [AS alias_name] [ LOW_PRIORITY ] WRITE

别名用法与读锁一样，写锁增加了指定优先级的功能，加入LOW_PRIORITY可以指定写锁为低优先级。

申请写锁前提： 当没有线程对该数据表使用写锁与读锁，否则申请回阻塞。

操作限制：其他MySQL线程不可以对锁表使用写锁、读锁

mvcc（乐观锁）

MVCC：多版本并发控制（MVCC,Multiversion Currency Control）。一般情况下，事务性储存引擎不是只使用表锁，行加锁的处理数据，而是结合了MVCC机制，以处理更多的并发问题。Mvcc处理高并发能力最强，但系统开销比最大（较表锁、行级锁），这是最求高并发付出的代价

InnoDB存储引擎MVCC的实现策略

在每一行数据中额外保存两个隐藏的列：当前行创建时的版本号和删除时的版本号（可能为空，其实还有一列称为回滚指针，用于事务回滚，不在本文范畴）。这里的版本号并不是实际的时间值，而是系统版本号。每开始新的事务，系统版本号都会自动递增。事务开始时刻的系统版本号会作为事务的版本号，用来和查询每行记录的版本号进行比较。

每个事务又有自己的版本号，这样事务内执行CRUD操作时，就通过版本号的比较来达到数据版本控制的目的。

mvcc主要实现了undo的日志回滚策略，mvcc通过悲观锁和乐观锁实现的

1、先用排他锁锁定行数据
2、然后将数据copy到undo日志中去
3、记录修改的事务id形成版本链

    trx_id
    这个id用来存储的每次对某条聚簇索引记录进行修改的时候的事务id。

    roll_pointer
    每次对哪条聚簇索引记录有修改的时候，都会把老版本写入undo日志中。这个roll_pointer就是存了一个指针，它指向这条聚簇索引记录的上一个版本的位置，通过它来获得上一个版本的记录信息。(注意插入操作的undo日志没有这个属性，因为它没有老版本)

    max_id
    已经创建的最大的事务id，包括已经提交的和未提交的

    readview
    版本快照是有未提交的事务id组成的数组和已经创建的所有的id的最大值组成

mvcc比较规则

    1、trx_id如果小于未提交的事务id的最小值，那么说明这个事务是在这些事务之前创建提交的，就是这条数据
    2、trx_id如果大于max_id，说明是最新的事务id，肯定不是查询的时候创建的，绝对不是这条数据
    3、trx_id如果在未提交的事务id的最小值和max_id之间，就有两种情况
        1、如果trx_id在未提交的事务id组成的数组中，说明没有提交，所以不是这个数据
        2、如果trx_id不在未提交的事务id组成的数组中，说明已经提交，所以是这个数据

这样就做到了在一个session中的可重复读。

如果是删除数据，就加一个删除的标识。

数据库多版本读场景

session 1
select a from test; return a = 10    
start transaction;   
update test set a = 20;  

session 2
start transaction;
select a from test; return ?
commit;  

select a from test; return ?
我们看下上面这个数据库日常操作的例子。

session 1修改了一条记录，没有提交；与此同时，session 2 来查询这条记录，这时候返回记录应该是多少呢？
session 1 提交之后 session 2 查询出来的又应该是多少呢？
由于MySQL支持多种隔离级别，这个问题是需要看session2的事务隔离级别的，情况如下:

    隔离级别为 READ-UNCOMMITTED 情况下: 
    session 1 commit前后 session 2 去查看都会看到的是修改后的结果 a = 20
    隔离级别为 READ-COMMITTED 情况下: 
    session 1 commit 前查看到的还是 a =10 , commit之后看到的是 a = 20
    隔离级别为 REPEATABLE-READ， SERIALIZABLE 情况下: 
    session 1 commit前后 session 2 去查看都会看到的是修改后的结果 a = 10

其实不管隔离级别，我们也抛开数据库中的ACID，我们思考一个问题：众所周知，InnoDB的数据都是存储在B-tree里面的，修改后的数据到底要不要存储在实际的B-tree叶子节点，session2是怎么做到查询出来的结果还是10，而不是20列？

MVCC使得数据库读不会对数据加锁，select不会加锁，提高了数据库的并发处理能力借助MVCC，数据库可以实现RC，RR等隔离级别，用户可以查看当前数据的前一个或者前几个历史版本。保证了ACID中的I-隔离性。

总结

在事务ACID过程中，“锁”和“隔离级别”一起来实现“I”隔离性和”C” 一致性 (redo也有参与).

悲观锁:行级锁定(行锁):谁先操作某个数据行,就会持有<这行>的(X)锁.

乐观锁: 没有锁，mvcc机制

架构改造项目

项目背景:

2台  IBM X3650   32G  ,原来主从关系,2年多没有主从了,"小问题"不断(锁,宕机后的安全)
MySQL 5.1.77   默认存储引擎 MyISAM
数据量: 60G左右 ,每周全备,没有开二进制日志

架构方案:

1. 升级数据库版本到5.7.20
2. 更新所有业务表的存储引擎为InnoDB
3. 重新设计备份策略为热备份,每天全备,并备份日志
4. 重新构建主从

结果:

1.性能
2.安全方面
3.快速故障处理

其他核心特性

1、缓冲区池

select @@innodb_buffer_pool_size;
show engine innodb status\G
innodb_buffer_pool_size
一般建议最多是物理内存的 75-80%

2、flush

innodb_flush_log_at_trx_commit (双一标准之一)
主要控制了innodb将log buffer中的数据写入日志文件并flush磁盘的时间点，取值分别为0、1、2三个。


参数说明:
1，每次事物的提交都会引起日志文件写入、flush磁盘的操作，确保了事务的ACID；flush  到操作系统的文件系统缓存  fsync到物理磁盘.
0，表示当事务提交时，不做日志写入操作，而是每秒钟将log buffer中的数据写入文件系统缓存并且秒fsync磁盘一次；
2，每次事务提交引起写入文件系统缓存,但每秒钟完成一次fsync磁盘操作。

3、flush method

Innodb_flush_method=(O_DIRECT, fdatasync)

https://dev.mysql.com/doc/refman/5.7/en/innodb-parameters.html#sysvar_innodb_flush_method

控制的是,log buffer 和data buffer,刷写磁盘的时候是否经过文件系统缓存

show variables like '%innodb_flush%';

参数值说明

O_DIRECT  :数据缓冲区写磁盘,不走OS buffer
fsync :日志和数据缓冲区写磁盘,都走OS buffer
O_DSYNC  :日志缓冲区写磁盘,不走 OS buffer

使用建议

最高安全模式
innodb_flush_log_at_trx_commit=1
Innodb_flush_method=O_DIRECT
最高性能:
innodb_flush_log_at_trx_commit=0
Innodb_flush_method=fsync

4、redo日志有关的参数

innodb_log_buffer_size=16777216
innodb_log_file_size=50331648
innodb_log_files_in_group = 3

日志管理

错误日志(log_error)

记录启动\关闭\日常运行过程中,状态信息,警告,错误

错误日志配置

默认就是开启的:  /数据路径下/hostname.err
手工设定:
Master [(none)]>select @@log_error;
vim /etc/my.cnf
log_error=/var/log/mysql.log
log_timestamps=system
重启生效
show variables like 'log_error';

日志内容查看

主要关注[ERROR],看上下文

binlog(binary logs):二进制日志 *****

作用

(1)备份恢复必须依赖二进制日志,主要是根据event来重开始的时间编号执行到结束的时间编号，有点类似于redo的功能，只不过是二进制的方式。
(2)主从环境必须依赖二进制日志

binlog配置 (5.7必须加server_id)

注意：MySQL默认是没有开启二进制日志的。

1、基础参数查看:

开关:
[(none)]>select @@log_bin;
日志路径及名字
[(none)]>select @@log_bin_basename;
服务ID号:
[(none)]>select @@server_id;
二进制日志格式:
[(none)]>select @@binlog_format;
双一标准之二:
[(none)]>select @@sync_binlog;

2、创建日志目录

mkdir /data/binlog
chown -R mysql.mysql /data/binlog

3、修改配置文件

vim /etc/my.cnf
server_id=6                                    ----->5.6中，单机可以不需要此参数
log_bin=/data/binlog/mysql-bin
binlog_format=row

4、重启数据库生效

[root@db01 mysql]# /etc/init.d/mysqld restart

5、参数说明

server_id=3306
主要是在主从复制过程中必须要加的,但是在5.7版本中,要用以下参数(log_bin),开启binlog日志,即使是单机也是必加的
log_bin=/data/binlog/mysql-bin
(1)开启二进制日志功能
(2)设置二进制日志目录及名称前缀
binlog_format=row
binlog的记录格式??

6、binlog记录了什么?

binlog是SQL层的功能。记录的是变更SQL语句，不记录查询语句。

记录SQL语句种类

DDL ：原封不动的记录当前DDL(statement语句方式)。
DCL ：原封不动的记录当前DCL(statement语句方式)。
DML ：只记录已经提交的事务DML

DML三种记录方式

binlog_format（binlog的记录格式）参数影响
（1）statement（5.6默认）SBR(statement based replication) ：语句模式原封不动的记录当前DML。
（2）ROW(5.7 默认值) RBR(ROW based replication) ：记录数据行的变化(用户看不懂，需要工具分析)
（3）mixed（混合）MBR(mixed based replication)模式  ：以上两种模式的混合

实例

SBR与RBR模式的对比

STATEMENT：可读性较高，日志量少，但是不够严谨
ROW      ：可读性很低，日志量大，足够严谨

7、二进制日志的最小记录单元

对于DDL,DCL,一个语句就是一个event 对于DML语句来讲:只记录已提交的事务。

例如以下列子,就被分为了4个event

begin;      120  - 340
DML1        340  - 460
DML2        460  - 550
commit;     550  - 760

某个事件在binlog中的相对位置号，为了方便我们截取事件

8、日志文件查看

查看日志的开启情况

log_bin参数设置的路径,可以找到二进制日志

Master [(none)]>show variables like '%log_bin%';
+---------------------------------+------------------------------+
| Variable_name                   | Value                        |
+---------------------------------+------------------------------+
| log_bin                         | ON                           |
| log_bin_basename                | /data/binlog/mysql-bin       |
| log_bin_index                   | /data/binlog/mysql-bin.index |
| log_bin_trust_function_creators | OFF                          |
| log_bin_use_v1_row_events       | OFF                          |
| sql_log_bin                     | ON                           |
+---------------------------------+------------------------------+
6 rows in set (0.01 sec)

查看一共多少个binlog

查看

Master [(none)]>show binary logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 |       154 |
+------------------+-----------+
1 row in set (0.01 sec)

Master [(none)]>flush logs;
Query OK, 0 rows affected (0.03 sec)

Master [(none)]>flush logs;
Query OK, 0 rows affected (0.01 sec)

Master [(none)]>show binary logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 |       201 |
| mysql-bin.000002 |       201 |
| mysql-bin.000003 |       154 |
+------------------+-----------+
3 rows in set (0.00 sec)

查看mysql正在使用的日志文件

查看

Master [(none)]>show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000003 |      154 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+

file：当前MySQL正在使用的文件名

Position：最后一个事件的结束位置号

9、日志内容查看

event查看

Master [binlog]>show binlog events in 'mysql-bin.000003';
+------------------+-----+----------------+-----------+-------------+----------------------------------------+
| Log_name         | Pos | Event_type     | Server_id | End_log_pos | Info                                   |
+------------------+-----+----------------+-----------+-------------+----------------------------------------+
| mysql-bin.000003 |   4 | Format_desc    |         6 |         123 | Server ver: 5.7.20-log, Binlog ver: 4  |
| mysql-bin.000003 | 123 | Previous_gtids |         6 |         154 |                                        |
| mysql-bin.000003 | 154 | Anonymous_Gtid |         6 |         219 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'   |
| mysql-bin.000003 | 219 | Query          |         6 |         319 | create database binlog                 |
| mysql-bin.000003 | 319 | Anonymous_Gtid |         6 |         384 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'   |
| mysql-bin.000003 | 384 | Query          |         6 |         486 | use `binlog`; create table t1 (id int) |
+------------------+-----+----------------+-----------+-------------+----------------------------------------+

Log_name：binlog文件名
Pos：开始的position    *****
Event_type：事件类型
Format_desc：格式描述，每一个日志文件的第一个事件，多用户没有意义，MySQL识别binlog必要信息
Server_id：mysql服务号标识
End_log_pos：事件的结束位置号 *****
Info：事件内容*****

补充:

SHOW BINLOG EVENTS
   [IN 'log_name']
   [FROM pos]
   [LIMIT [offset,] row_count]
[root@db01 binlog]# mysql -e "show binlog events in 'mysql-bin.000004'" |grep drop

10、binlog文件内容详细查看

mysqlbinlog /data/mysql/mysql-bin.000006
mysqlbinlog --base64-output=decode-rows -vvv /data/binlog/mysql-bin.000003
mysqlbinlog  -d binlog /data/binlog/mysql-bin.000003
[root@db01 binlog]# mysqlbinlog --start-datetime='2019-05-06 17:00:00' --stop-datetime='2019-05-06 17:01:00'  /data/binlog/mysql-bin.000004

11、基于Position号进行日志截取

核心就是找截取的起点和终点

--start-position=321
--stop-position=513
 mysqlbinlog --start-position=219 --stop-position=1347 /data/binlog/mysql-bin.000003 >/tmp/bin.sql

案例

使用binlog日志进行数据恢复

模拟:

1.
[(none)]>create database binlog charset utf8;
2.
[(none)]>use binlog;
[binlog]>create table t1(id int);
3.
[binlog]>insert into t1 values(1);
[binlog]>commit;
[binlog]>insert into t1 values(2);
[binlog]>commit;
[binlog]>insert into t1 values(3);
[binlog]>commit;
4.
[binlog]>drop database binlog;

恢复:

[(none)]>show master status ;
[(none)]>show binlog events in 'mysql-bin.000004';
[root@db01 binlog]# mysqlbinlog --start-position=1227 --stop-position=2342 /data/binlog/mysql-bin.000004 >/tmp/bin.sql
[(none)]>set sql_Log_bin=0;
[(none)]>source /tmp/bin.sql

实例:

备份策略每天全备,有全量的二进制日志
业务中一共10个库,其中一个被误drop了
需要在其他9个库正常工作过程中进行数据恢复

binlog日志的GTID新特性

5.6 版本新加的特性,5.7中做了加强,5.6 中不开启,没有这个功能.,5.7 中的GTID,即使不开也会有自动生成

SET @@SESSION.GTID_NEXT= 'ANONYMOUS'

是对于一个已提交事务的编号，并且是一个全局唯一的编号。

GTID = source_id ：transaction_id
7E11FA47-31CA-19E1-9E56-C43AA21293967:29

重要参数介绍：

vim /etc/my.cnf
gtid-mode=on
enforce-gtid-consistency=true
systemctl restart mysqld

实例

Master [(none)]>create database gtid charset utf8;
Query OK, 1 row affected (0.01 sec)

Master [(none)]>show master status ;
+------------------+----------+--------------+------------------+----------------------------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                      |
+------------------+----------+--------------+------------------+----------------------------------------+
| mysql-bin.000004 |      326 |              |                  | dff98809-55c3-11e9-a58b-000c2928f5dd:1 |
+------------------+----------+--------------+------------------+----------------------------------------+
1 row in set (0.00 sec)

Master [(none)]>use gtid
Database changed
Master [gtid]>create table t1 (id int);
Query OK, 0 rows affected (0.01 sec)

Master [gtid]>show master status ;
+------------------+----------+--------------+------------------+------------------------------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                        |
+------------------+----------+--------------+------------------+------------------------------------------+
| mysql-bin.000004 |      489 |              |                  | dff98809-55c3-11e9-a58b-000c2928f5dd:1-2 |
+------------------+----------+--------------+------------------+------------------------------------------+
1 row in set (0.00 sec)

Master [gtid]>create table t2 (id int);
Query OK, 0 rows affected (0.01 sec)

Master [gtid]>create table t3 (id int);
Query OK, 0 rows affected (0.02 sec)

Master [gtid]>show master status ;
+------------------+----------+--------------+------------------+------------------------------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                        |
+------------------+----------+--------------+------------------+------------------------------------------+
| mysql-bin.000004 |      815 |              |                  | dff98809-55c3-11e9-a58b-000c2928f5dd:1-4 |
+------------------+----------+--------------+------------------+------------------------------------------+
1 row in set (0.00 sec)

Master [gtid]>begin;
Query OK, 0 rows affected (0.00 sec)

Master [gtid]>insert into t1 values(1);
Query OK, 1 row affected (0.00 sec)

Master [gtid]>commit;
Query OK, 0 rows affected (0.00 sec)

Master [gtid]>show master status ;
+------------------+----------+--------------+------------------+------------------------------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                        |
+------------------+----------+--------------+------------------+------------------------------------------+
| mysql-bin.000004 |     1068 |              |                  | dff98809-55c3-11e9-a58b-000c2928f5dd:1-5 |
+------------------+----------+--------------+------------------+------------------------------------------+
1 row in set (0.00 sec)

Master [gtid]>begin;
Query OK, 0 rows affected (0.00 sec)

Master [gtid]>insert into t2 values(1);
Query OK, 1 row affected (0.00 sec)

Master [gtid]>commit;
Query OK, 0 rows affected (0.01 sec)

Master [gtid]>show master status ;
+------------------+----------+--------------+------------------+------------------------------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                        |
+------------------+----------+--------------+------------------+------------------------------------------+
| mysql-bin.000004 |     1321 |              |                  | dff98809-55c3-11e9-a58b-000c2928f5dd:1-6 |
+------------------+----------+--------------+------------------+------------------------------------------+
1 row in set (0.00 sec)

基于GTID进行查看binlog

具备GTID后,可以截取查看某些事务日志:

--include-gtids
--exclude-gtids
mysqlbinlog --include-gtids='dff98809-55c3-11e9-a58b-000c2928f5dd:1-6' --exclude-gtids='dff98809-55c3-11e9-a58b-000c2928f5dd:4'  /data/binlog/mysql-bin.000004

GTID的幂等性

开启GTID后,MySQL恢复Binlog时,重复GTID的事务不会再执行了就想恢复?怎么办?

--skip-gtids
mysqlbinlog --include-gtids='3ca79ab5-3e4d-11e9-a709-000c293b577e:4' /data/binlog/mysql-bin.000004 /data/binlog/mysql-bin.000004
set sql_log_bin=0;
source /tmp/binlog.sql
set sql_log_bin=1;

实例

使用二进制日志恢复数据案例

创建了一个库 db, 导入了表t1 ,t1表中录入了很多数据一个开发人员,drop database db; 没有备份,日志都在.怎么恢复?

思路:找到建库语句到删库之前所有的日志,进行恢复.(开启了GTID模式)

故障案例模拟:

(0) drop database if exists db ;
(1) create database db charset utf8;
(2) use db;
(3) create table t1 (id int);
(4) insert into t1 values(1),(2),(3);
(5) insert into t1 values(4),(5),(6);
(6) commit
(7) update t1 set id=30 where id=3;
(8) commit;
(9) delete from t1 where id=4;
(10)commit;
(11)insert into t1 values(7),(8),(9);
(12)commit;
(13)drop database db;

运行以上语句，模拟故障场景需求：将数据库恢复到以下状态（提示第9步和第13步是误操作，其他都是正常操作）

恢复过程(无GTID时的恢复)

查看当前使用的 binlog文件

oldguo [db]>show master status ;
+------------------+----------+--------------+------------------+-------------------+

| File            | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |

+------------------+----------+--------------+------------------+-------------------+

| mysql-bin.000006 |    1873 |              |                  |                  |

+------------------+----------+--------------+------------------+-------------------+

2.查看事件：

第一段：
| mysql-bin.000006 |  813 | Query      |        1 |        907 | use `db`; create table t1 (id int)                  |

| mysql-bin.000006 |  907 | Query      |        1 |        977 | BEGIN                                              |

| mysql-bin.000006 |  977 | Table_map  |        1 |        1020 | table_id: 77 (db.t1)                                |

| mysql-bin.000006 | 1020 | Write_rows  |        1 |        1070 | table_id: 77 flags: STMT_END_F                      |

| mysql-bin.000006 | 1070 | Table_map  |        1 |        1113 | table_id: 77 (db.t1)                                |

| mysql-bin.000006 | 1113 | Write_rows  |        1 |        1163 | table_id: 77 flags: STMT_END_F                      |

| mysql-bin.000006 | 1163 | Xid        |        1 |        1194 | COMMIT /* xid=74 */                                |

| mysql-bin.000006 | 1194 | Query      |        1 |        1264 | BEGIN                                              |

| mysql-bin.000006 | 1264 | Table_map  |        1 |        1307 | table_id: 77 (db.t1)                                |

| mysql-bin.000006 | 1307 | Update_rows |        1 |        1353 | table_id: 77 flags: STMT_END_F                      |

| mysql-bin.000006 | 1353 | Xid        |        1 |        1384 | COMMIT /* xid=77 */

mysqlbinlog --start-position=813 --stop-position=1384 /data/mysql/mysql-bin.000006 >/tmp/bin1.sql
第二段：


| mysql-bin.000006 | 1568 | Query      |        1 |        1638 | BEGIN                                              |

| mysql-bin.000006 | 1638 | Table_map  |        1 |        1681 | table_id: 77 (db.t1)                                |

| mysql-bin.000006 | 1681 | Write_rows  |        1 |        1731 | table_id: 77 flags: STMT_END_F                      |

| mysql-bin.000006 | 1731 | Xid        |        1 |        1762 | COMMIT /* xid=81 */

mysqlbinlog --start-position=1568 --stop-position=1762 /data/mysql/mysql-bin.000006 >/tmp/bin2.sql
3.恢复

set sql_log_bin=0;
source /tmp/bin1.sql
source /tmp/bin2.sql
set sql_log_bin=1;
oldguo [db]>select * from t1;

+------+

| id  |

+------+

|    1 |

|    2 |

|  30 |

|    4 |

|    5 |

|    6 |

|    7 |

|    8 |

|    9 |

有GTID的恢复:

(1)截取

mysqlbinlog --skip-gtids --include-gtids='3ca79ab5-3e4d-11e9-a709-000c293b577e:7-12' mysql-bin.000004> /tmp/bin.sql
(2)恢复

set sql_log_bin=0;
source /tmp/bin.sql

二进制日志其他操作

1、自动清理日志

show variables like '%expire%';
expire_logs_days  0

自动清理时间,是要按照全备周期+1

set global expire_logs_days=8;

永久生效:

my.cnf
expire_logs_days=15;
企业建议,至少保留两个全备周期+1的binlog

2、手工清理

PURGE BINARY LOGS BEFORE now() - INTERVAL 3 day;
PURGE BINARY LOGS TO 'mysql-bin.000010';

注意:不要手工 rm binlog文件

1.my.cnf binlog关闭掉,启动数据库
2.把数据库关闭,开启binlog,启动数据库
删除所有binlog,并从000001开始重新记录日志
*reset master; 主从关系中，主库执行此操作，主从环境必崩

3、日志是怎么滚动

flush logs;
重启mysql也会自动滚动一个新的
日志文件达到1G大小(max_binlog_size)
| max_binlog_size                          | 1073741824
备份时,加入参数也可以自动滚动

slow_log 慢日志

记录慢SQL语句的日志,定位低效SQL语句的工具日志

1、开启慢日志(默认没开启)

开关:
slow_query_log=1
文件位置及名字
slow_query_log_file=/data/mysql/slow.log
设定慢查询时间:
long_query_time=0.1
没走索引的语句也记录:
log_queries_not_using_indexes
vim /etc/my.cnf
slow_query_log=1
slow_query_log_file=/data/mysql/slow.log
long_query_time=0.1
log_queries_not_using_indexes
systemctl restart mysqld

2、mysqldumpslow 分析慢日志

mysqldumpslow -s c -t 10 /data/mysql/slow.log

还有一些第三方工具(自己扩展)

数据备份和迁移

备份

逻辑备份工具

  基于SQL语句进行备份
  mysqldump       *****
  mysqlbinlog     *****

物理备份工具

  基于磁盘数据文件备份
  xtrabackup(XBK) ：percona 第三方   *****
  MySQL Enterprise Backup（MEB）

备份类型

2.1 热备
在数据库正常业务时,备份数据,并且能够一致性恢复（只能是innodb）
对业务影响非常小
2.2 温备
锁表备份,只能查询不能修改（myisam）
影响到写入操作
2.3 冷备
关闭数据库业务,数据库没有任何变更的情况下,进行备份数据.
业务停止

实例

使用的是mysqldump命令进行备份，还可以mysql主重备份


使用crontab的定时任务运行脚本

    #crontab -e

    59 23 * * * /data/mysqlbak/mysqlbak.sh


脚本

    #!/bin/bash
    #数据库IP
    dbserver='127.0.0.1'
    #数据库port
    dbport='3306'
    #数据库用户名
    dbuser='root'
    #数据密码
    dbpasswd='********'
    #数据库,如有多个库用空格分开
    dbname='back01'
    #备份时间
    backtime=`date "%Y-%m-%d %H:%M:%S"`
    #备份输出日志路径
    logpath='/data/mysqlbak'
    #文件夹不存在就创建
    if [ ! -d "$logpath" ]; then
            mkdir $logpath
    fi

    echo "################## ${backtime} #############################"
    echo "开始备份"
    #日志记录头部
    echo "" >> ${logpath%*/}/mysqlback.log
    echo "-------------------------------------------------" >> ${logpath%*/}/mysqlback.log
    echo "备份时间为${backtime},备份数据库表 ${dbname} 开始" >> ${logpath%*/}/mysqlback.log
    #正式备份数据库
    for table in $dbname; do
    source=`mysqldump -h ${dbserver} -P ${dbport} -u ${dbuser} -p${dbpasswd} ${table} > ${logpath%*/}/${backtime}.sql` 2>> ${logpath%*/}/mysqlback.log;
    #备份成功以下操作
    if [ "$?" == 0 ];then
    #为节约硬盘空间，将数据库压缩
    tar -zcvPf ${logpath%*/}/${table}${backtime}.tar.gz ${logpath%*/}/${backtime}.sql > /dev/null
    #删除原始文件，只留压缩后文件
    rm -f ${logpath%*/}/${backtime}.sql
    #删除七天前备份，也就是只保存7天内的备份
    find $logpath -name "*.tar.gz" -type f -mtime +7 -exec rm -rf {} \; > /dev/null 2>&1
    echo "数据库表 ${dbname} 备份成功!!" >> ${logpath%*/}/mysqlback.log
    else
    #备份失败则进行以下操作
    echo "数据库表 ${dbname} 备份失败!!" >> ${logpath%*/}/mysqlback.log
    fi
    done
    echo "完成备份"
    echo "################## ${backtime} #############################"



MySQL的备份有两种方式:热备份和冷备份，热备份目前主要是采用master/slave方式(master/slave方式的同步目前主要用于数据库读写分离，也可以用于热备份数据)，关于如何配置这方面的资料，大家可以找到很多。冷备份的话就是数据有一定的延迟，但是可以保证该时间段之前的数据完整。

例如有些时候可能我们的误操作引起了数据的丢失，那么master/slave模式是无法找回丢失数据的，但是通过冷备份可以部分恢复数据。

上面的脚本的形式就是冷备份，然后可以通过rsync同步到一台非本地机房。

mysql恢复

有备份就有恢复，上面说的有冷备份和热备份，热备份很简单，就是我们常用的master/slave的主被切换，冷备份就需要命令了

mysql -u username -p databse < backup.sql

实例

如果不小心误删了全库，可以这么恢复：

将最近一次全量备份的全库找到，拷贝回来（文件一般比较大），解压，应用
将最近一次全量备份后，每一天的增量binlog找到，拷贝回来（文件较多），依次重放
将最近一次增量备份后，到执行“删全库”之前的binlog找到，重放，恢复完毕。

这个恢复时间比较长，有没有更加优化的方案呢？就是使用冗余备份的方案：一小时延从库

增加一个从库，这个从库不是实时与主库保持同步的，而是每隔1个小时同步一次主库，同步完之后立马断开1小时，这个从库会与主库保持1个小时的数据差距。在删除全库的时候，也不会同步将从库数据删除，这是只要恢复一个小时的binlog数据就可以。当然可能会出现正在同步删库的情况，这个时候可以使用双一小时延从库，岔开时间，必然保证数据在，只不过成本比较高，这个时候就要看情况考虑，大部分是能接收这个资源的，因为数据的安全性完整性才是第一位。

当然我们也可以对于一些“允许延时”的业务，可以使用1小时延时从来提高资源利用率。

mysql-proxy

mysql-proxy是mysql官方提供的mysql中间件服务，上游可接入若干个mysql-client，后端可连接若干个mysql-server。

mysql-proxy最基本的用法，就是作为一个请求拦截，请求中转的中间层，进一步的，mysql-proxy可以分析与修改请求。拦截查询和修改结果，需要通过编写Lua脚本来完成。

mysql-proxy允许用户指定Lua脚本对请求进行拦截，对请求进行分析与修改，它还允许用户指定Lua脚本对服务器的返回结果进行修改，加入一些结果集或者去除一些结果集均可。

所以说，根本上，mysql-proxy是一个官方提供的框架，具备良好的扩展性，可以用来完成：

sql拦截与修改
性能分析与监控
读写分离
请求路由

基本原理

mysql并行复制

mysql主从复制，读写分离是互联网用的非常多的mysql架构，老版本的mysql主从复制最令人诟病的地方就是，在数据量较大并发量较大的场景下，主从延时会比较严重。

我们简单看一下mysql的主从复制的原理，就可以看出为什么常规使用情况下，大量数据mysql复制延迟如此验证，就是因为从库使用单线程解析relaylog。

所以我们最想想到的就是并发处理relaylog，并发并不是常规的并发就可以，因为relaylog存在这个串行的概念，比如

update account set money=100 where uid=58;
update account set money=150 where uid=58;
update account set money=200 where uid=58;

这三条命令你必须保证他是串行执行的，最后的就过必须是200，在这种情况下，我们只能按库来并发，相同的库的相同id的操作有同一个线程来执行，不同的库可以使用并发来提高效率。

将一个库中同时并行执行的事务，分为一组，编一个号，这些事务在从库上的回放可以并行执行（事务在主库上的执行都进入到prepare阶段，说明事务之间没有冲突，否则就不可能提交），没错，mysql正是这么做的。

新版的mysql，将组提交的信息存放在GTID中，使用mysqlbinlog工具，可以看到组提交内部的信息：

20160607 23:22 server_id 58 XXX GTID last_committed=0 sequence_numer=1
20160607 23:22 server_id 58 XXX GTID last_committed=0 sequence_numer=2
20160607 23:22 server_id 58 XXX GTID last_committed=0 sequence_numer=3
20160607 23:22 server_id 58 XXX GTID last_committed=0 sequence_numer=4

和原来的日志相比，多了last_committed和sequence_number。

last_committed表示事务提交时，上次事务提交的编号，如果具备相同的last_committed，说明它们在一个组内，可以并发回放执行。

mysql在并行复制上的逐步优化演进：

mysql5.5 -> 不支持并行复制，对大伙的启示：升级mysql吧
mysql5.6 -> 按照库并行复制，对大伙的启示：使用“多库”架构吧
mysql5.7 -> 按照GTID并行复制

其实这个演进的过程给我一个很好的架构思想：

多线程并发分派任务时必须保证幂等性：mysql的演进思路，提供了“按照库幂等”，“按照commit_id幂等”两种方式，思路可以借鉴。

使用规范

1、必须使用InnoDB存储引擎：支持事务、行级锁、并发性能更好、CPU及内存缓存页优化使得资源利用率更高

2、必须使用UTF8字符集：万国码，无需转码，无乱码风险，节省空间

3、数据表、数据字段必须加入中文注释

4、禁止使用存储过程、视图、触发器、Event：高并发大数据的互联网业务，架构设计思路是“解放数据库CPU，将计算转移到服务层”，并发量大的情况下，这些功能很可能将数据库拖死，业务逻辑放到服务层具备更好的扩展性，能够轻易实现“增机器就加性能”。数据库擅长存储与索引，CPU计算还是上移吧

5、禁止存储大文件或者大照片：为何要让数据库做它不擅长的事情？大文件和照片存储在文件系统，数据库里存URI多好

6、只允许使用内网域名，而不是ip连接数据库

7、线上环境、开发环境、测试环境数据库内网域名遵循命名规范

8、库名、表名、字段名：小写，下划线风格，不超过32个字符，必须见名知意，禁止拼音英文混用

9、表名t_xxx，非唯一索引名idx_xxx，唯一索引名uniq_xxx

10、单实例表数目必须小于500

11、单表列数目必须小于30

12、表必须有主键

13、禁止使用外键，如果有外键完整性约束，需要应用程序控制：外键会导致表与表之间耦合，update与delete操作都会涉及相关联的表，十分影响sql 的性能，甚至会造成死锁。高并发情况下容易造成数据库性能，大数据高并发业务场景数据库使用以性能优先

14、必须把字段定义为NOT NULL并且提供默认值

15、禁止使用TEXT、BLOB类型：会浪费更多的磁盘和内存空间，非必要的大量的大字段查询会淘汰掉热数据，导致内存命中率急剧降低，影响数据库性能

16、禁止使用小数存储货币

17、必须使用varchar(20)存储手机号

18、禁止使用ENUM，可使用TINYINT代替

19、单表索引建议控制在5个以内

20、单索引字段数不允许超过5个

21、禁止在更新十分频繁、区分度不高的属性上建立索引

22、建立组合索引，必须把区分度高的字段放在前面

23、禁止使用SELECT *，只获取必要的字段，需要显示说明列属性

24、禁止使用INSERT INTO t_xxx VALUES(xxx)，必须显示指定插入的列属性

25、禁止使用属性隐式转换

26、禁止在WHERE条件的属性上使用函数或者表达式

27、禁止负向查询，以及%开头的模糊查询

28、禁止大表使用JOIN查询，禁止大表使用子查询

29、禁止使用OR条件，必须改为IN查询

30、应用程序必须捕获SQL异常，并有相应处理