MySQL高级

最新推荐文章于 2024-11-04 23:24:03 发布

m0_46337171

最新推荐文章于 2024-11-04 23:24:03 发布

阅读量151

点赞数

分类专栏：第九阶段文章标签： java

本文链接：https://blog.csdn.net/m0_46337171/article/details/115092455

版权

第九阶段专栏收录该内容

8 篇文章 1 订阅

订阅专栏

前言

文章内容输出来源：拉勾教育JAVA就业训练营

1.MySQL架构组成

1.1 高级MySQL介绍

1 ) 什么是DBA ?

数据库管理员，英文是Database Administrator，简称DBA。

在这里插入图片描述

百度百科介绍

2 ) 一个高级DBA的职责:

负责MySQL的容量规划，架构设计及安装、部署.
负责MySQL的日常管理，监控和维护, 并对MySQL进行持续性能优化.
负责MySQL开发支持，参与数据架构规划设计，以及相关业务的数据建模、设计评审、SQL代码审核优化.

3 )中级 Java开发工程师对数据库知识的掌握程度

熟练操作主流数据库,能够通过代码(框架) 完成日常的数据库操作.
熟练使用SQL, 熟悉SQL优化, 熟悉存储过程视图等创建及使用.
了解MySQL的整体体系结构，了解MySQL事务存储引擎的特点。
了解MySQL索引优化,了解MySQL相关锁机制

我们作为Java开发工程师, 关注的应该是跟开发相关的数据库知识. 了解这些高级的知识, 目的是让我们编写出更加高效的应用程序.
专业的数据库维护、服务器优化、性能参数调优等等数据库相关的运维工作还是要交给DBA去做的.

1.2 MySQL逻辑架构

学习 MySQL 就好比盖房子，如果想把房子盖的特别高，地基一定要稳，基础一定要牢固。学习MySQL 数据库前要先了解它的体系结构，这是学好 MySQL 数据库的前提。

1.2.1 MySQL架构体系介绍

MySQL 由连接池、SQL 接口、解析器、优化器、缓存、存储引擎等组成，可以分为四层，即连接层、服务层、引擎层和文件系统层。

如下是官方文档中 MySQL 的基础架构图：

1. 连接层
最上面是一些客户端和连接服务, 不是MySQL特有的，所有基于网络的C/S的网络应用程序都应该包括连接处理、认证、安全管理等。
2. 服务层
中间层是MySQL的核心，包括查询解析、分析、优化和缓存等。同时它还提供跨存储引擎的功能，包括存储过程、触发器和视图等。
3. 引擎层
存储引擎层，它负责存取数据。服务器通过API可以和各种存储引擎进行交互。不同的存储引擎具有不同的功能,我们可以根据实际需求选择使用对应的存储引擎
4. 存储层
数据存储层,主要是将数据存储在运行于裸设备的文件系统之上,并完成与存储引擎的交互
### 1.2.2 SQL查询流程
我们用一条 SQL SELECT 语句的执行轨迹来说明客户端与 MySQL 的交互过程，如下图所示。
![在这里插入图片描述](https://img-blog.csdnimg.cn/20210322191347902.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L20wXzQ2MzM3MTcx,size_16,color_FFFFFF,t_70)

通过客户端/服务器通信协议与 MySQL 建立连接
查询缓存，这是 MySQL 的一个可优化查询的地方，如果开启了 Query Cache 且在查询缓存过程中查
询到完全相同的 SQL 语句，则将查询结果直接返回给客户端；如果没有开启Query Cache 或者没有查询到
完全相同的 SQL 语句则会由解析器进行语法语义解析，并生成解析树。
预处理器生成新的解析树。
查询优化器生成执行计划。
查询执行引擎执行 SQL 语句，此时查询执行引擎会根据 SQL 语句中表的存储引擎类型，以及对应的
API 接口与底层存储引擎缓存或者物理文件的交互情况，得到查询结果，由MySQL Server 过滤后将查询结
果缓存并返回给客户端。若开启了 Query Cache，这时也会将SQL 语句和结果完整地保存到 Query
Cache 中，以后若有相同的 SQL 语句执行则直接返回结果。

## 1.3 MySQL物理文件
物理文件包括：日志文件，数据文件，配置文件
### 1.3.1 日志文件
- 日志文件包括
	- error log 错误日志 排错 /var/log/mysqld.log【默认开启】
	- bin log 二进制日志 备份 增量备份 DDL DML DCL
	- Relay log 中继日志 复制 接收 replication master
	- slow log 慢查询日志 调优 查询时间超过指定值
````sql
-- 查看错误日志文件路径
show variables like 'log_error';
+---------------+---------------------+
| Variable_name | Value        |
+---------------+---------------------+
| log_error   | /var/log/mysqld.log |
+---------------+---------------------+
-- 慢查询日志文件路径
show variables like 'slow_query_log_file';
+---------------------+-----------------------------------+
| Variable_name    | Value               |
+---------------------+-----------------------------------+
| slow_query_log_file | /var/lib/mysql/localhost-slow.log |
+---------------------+-----------------------------------+
-- bin log 日志文件 需要在 my.cnf 中配置
log-bin=/var/log/mysql-bin/bin.log
server-id=2
-- 查看 relay log 相关参数
show variables like '%relay%';

1.3.2 配置文件&数据文件

配置文件 my.cnf
在 my.cnf 文件中可以进行一些参数设置, 对数据库进行调优。

[client]   #客户端设置，即客户端默认的连接参数
port = 3307  #默认连接端口
socket = /data/mysqldata/3307/mysql.sock   #用于本地连接的socket套接字
default-character-set = utf8mb4  #编码
[mysqld]  #服务端基本设置
port = 3307 MySQL监听端口
socket = /data/mysqldata/3307/mysql.sock #为MySQL客户端程序和服务器之间的本地通讯指定一
个套接字文件
pid-file = /data/mysqldata/3307/mysql.pid #pid文件所在目录
basedir = /usr/local/mysql-5.7.11  #使用该目录作为根目录（安装目录）
datadir = /data/mysqldata/3307/data  #数据文件存放的目录
tmpdir = /data/mysqldata/3307/tmp  #MySQL存放临时文件的目录
character_set_server = utf8mb4   #服务端默认编码（数据库级别）

数据文件

-- 查看数据文件的位置
show variables like '%dir%';
+-----------------------------------------+----------------------------+
| Variable_name              | Value           |
+-----------------------------------------+----------------------------+
| datadir                 | /var/lib/mysql/      |
+-----------------------------------------+----------------------------+

1、.frm文件
不论是什么存储引擎，每一个表都会有一个以表名命名的.frm文件，与表相关的元数据（meta)信息都存放在
此文件中，包括表结构的定义信息等。
2、.MYD文件
myisam存储引擎专用，存放myisam表的数据（data)。每一个myisam表都会有一个.MYD文件与之呼应，同
样存放在所属数据库的目录下
3、.MYI文件
也是myisam存储引擎专用，存放myisam表的索引相关信息。每一个myisam表对应一个.MYI文件，其存放的
位置和.frm及.MYD一样
4、.ibd文件
存放innoDB的数据文件（包括索引）。
5. db.opt文件  此文件在每一个自建的库里都会有，记录这个库的默认使用的字符集和校验规。

2.MySQL的备份与恢复

2.1 为什么要进行数据备份

我们试着想一想, 在生产环境中什么最重要？如果我们服务器的硬件坏了可以维修或者换新, 软件问题可以修复或重新安装, 但是如果数据没了呢对于一些网站、系统来说，数据库就是一切，所以做好数据库的备份是至关重要的！

2.1.1 数据库备份的应用场景

数据备份在很多工作中都是经常会用到的，因为数据容易因为各种原因而丢失，造成数据丢失的原因有哪些呢?

数据丢失应用场景
- 系统硬件或软件故障
- 自然灾害,比如水灾火灾地震等
- 黑客攻击,非法访问者故意破坏
- 误操作 , 人为的误操作占比最大
非数据丢失应用场景：
- 开发测试环境数据库搭建
- 数据库或者数据迁移

2.2 数据备份的类型

2.2.1 按照业务方式分

完全备份
将数据库的全部信息进行备份，包括数据库的数据文件、日志文件，还需要备份文件的存储位置以及数据库中的全部对象和相关信息。
差异备份
备份从最近的完全备份后对数据所做的修改，备份完全备份后变化了的数据文件、日志文件以及数据库中其他被修改的内容。
增量备份
增量备份是指在一次全备份或上一次增量备份后，以后每次的备份只需备份与前一次相比增加或者被修改的文件

在这里插入图片描述

-	完全备份	差异备份	增量备份
备份方法	备份所有文件一次全备份后,备份与全备份差异的部分	一次全备份后,备份与上次备份的差异部分
备份速度	最慢	较快	最快
恢复速度	最快	较快	最慢
空间要求	最多	较多	最少
优势	最快的恢复速度, 只需要上一次完全备份就能恢复	相比增量,更快也更简单并且只需要最近一次的完全备份和最后一次的差异备份就能恢复	备份速度快,较少的空间需求,没有重复的备份文件
劣势	最多的空间需求大量重复的备份	较慢的备份速度,仍然会存在许多的备份文件	最慢的恢复速度恢复需要最近一次完全备份和全部增量备份

2.2.2 备份的组合方式

b完全备份与差异备份
以每周数据备份为例，可以在星期一进行完全备份，在星期二至星期六进行差异备份。如果在星期六数据被破坏了，则只需要还原星期一完全的备份和星期五的差异备份。
这种策略备份数据需要较多的时间，但还原数据使用较少的时间。

在这里插入图片描述

完全备份与增量备份
以每周数据备份为例，在星期一进行完全备份，在星期二至星期六进行增量备份。如果在星期六数
据被破坏了，则需要还原星期一正常的备份和从星期二至星期五的所有增量备份。
这种策略备份数据需要较少的时间，但还原数据使用较长的时间。

2.3 MySQL冷备份和热备份

冷备份和热备份指的是, 按照数据库的运行状态分类

2.3.1 冷备份

冷备份指的是当数据库进行备份时, 数据库不能进行读写操作, 即数据库要下线
在这里插入图片描述
冷备份的优点：

是操作比较方便的备份方法（只需拷贝文件）
低度维护，高度安全。

冷备份的缺点:

在实施备份的全过程中，数据库必须要作备份而不能作其它工作。
若磁盘空间有限，只能拷贝到磁带等其它外部存储设备上，速度比较慢慢。
不能按表或按用户恢复。

2.3.2 热备份

热备份

热备份是在数据库运行的情况下，备份数据库操作的sql语句，当数据库发生问题时，可以重新执行一遍备份的sql语句。

热备份的优点:
可在表空间或数据文件级备份，备份时间短。
备份时数据库仍可使用。
可达到秒级恢复（恢复到某一时间点上）。

热备份的缺点:

不能出错，否则后果严重。
因难维护，所以要特别仔细小心，不允许“以失败而告终”。

2.4 实战演练

2.4.1 冷备份实战

关闭SELinux
修改 selinux 配置文件,将SELINUX=enforcing改为SELINUX=disabled，保存后退出

vim /etc/selinux/config
SELINUX=disabled

修改后需要重启

reboot  # 重启命令

找到MySQL数据文件位置,停止MySQL服务

SHOW VARIABLES LIKE '%dir%';
-- 结果显示, 数据目录就是datadir的所在位置，即 /var/lib/mysql/   
service mysqld stop -- 停止mysql

进入到 /mysql 目录, 执行打包命令将数据文件打包备份

cd /var/lib/            # 进入其上级目录
tar jcvf /root/backup.tar.bz2 mysql/   # 打包压缩到 root目录下

-- 删除原目录
rm -rf /var/lib/mysql/

4)恢复数据 (使用tar命令)

-- 解压
tar jxvf backup.tar.bz2 mysql/
-- 把备份的文件移动到/var/lib/里面去替代原来的mysql
mv /root/mysql/ /var/lib/

启动MySQL, 然后登陆MySQL,查看数据是否丢失, 如果数据正常代表冷备成功

service mysqld start

2.4.2 热备份实战

mysqldump 备份工具
mysqldump是MySQL数据库用来备份和数据转移的一个工具，一般在数据量很小的时候（几个G）可以用于备份。热备可以对多个库进行备份，可以对单张表或者某几张表进行备份。
备份单个数据库

创建文件夹 , 备份数据

[root@localhost ~]# mkdir databackup
[root@localhost ~]# cd databackup
[root@localhost databackup]# mysqldump -uroot -p lagou_edu > lagou_edu.sql

模拟数据丢失，删除数据库,然后重新创建一个新的库.

DROP DATABASE lagou_edu;
CREATE DATABASE lagou_edu CHARACTER SET 'utf8';

恢复数据

[root@localhost databackup]cd databackup
[root@localhost databackup]# mysql -uroot -p lagou_edu < lagou_edu.sql

备份数据库的某些表

备份表数据

[root@localhost databackup]# mysqldump -uroot -p lagou_edu course course_lesson
> backupTable.sql

模拟数据丢失，删除数据表

DROP TABLE course;
DROP TABLE course_lesson;

恢复数据

mysql -uroot -p lagou_edu < backupTable.sql

直接将MySQL数据库压缩备份

备份数据

mysqldump -uroot -p lagou_edu | gzip > lagou_edu.sql.gz

模拟数据丢失，删除数据库

DROP DATABASE lagou_edu;
CREATE DATABASE lagou_edu CHARACTER SET 'utf8';

恢复数据

gunzip < lagou_edu.sql.gz | mysql -uroot -p lagou_edu

3.MySQL查询和慢查询日志分析

3.1 SQL性能下降的原因

在日常的运维过程中，经常会遇到DBA将一些执行效率较低的SQL发过来找开发人员分析，当我们拿到这个SQL语句之后，在对这些SQL进行分析之前，需要明确可能导致SQL执行性能下降的原因进行分析，执行性能下降可以体现在以下两个方面：

等待时间长

1.锁表导致查询一直处于等待状态，后续我们从MySQL锁的机制去分析SQL执行的原理

执行时间长

1.查询语句写的烂
2.索引失效
3.关联查询太多join
4.服务器调优及各个参数的设置

3.2 需要遵守的优化原则

查询优化是一个复杂的工程，涉及从硬件到参数配置、不同数据库的解析器、优化器实现、SQL 语句的执行顺序、索引以及统计信息的采集等等方面.
下面给大家介绍几个编写SQL的关键原则，可以帮助我们编写出更加高效的 SQL 查询.

第一条: 只返回需要的结果
- 一定要为查询语句指定 WHERE 条件，过滤掉不需要的数据行
- 避免使用 select * from , 因为它表示查询表中的所有字段
第二条: 确保查询使用了正确的索引
- 经常出现在 WHERE 条件中的字段建立索引,可以避免全表扫描；
- 将 ORDER BY 排序的字段加入到索引中，可以避免额外的排序操作；
- 多表连接查询的关联字段建立索引，可以提高连接查询的性能；
- 将 GROUP BY 分组操作字段加入到索引中，可以利用索引完成分组。
第三条: 避免让索引失效
- 在 WHERE 子句中对索引字段进行表达式运算或者
- 使用函数都会导致索引失效使用 LIKE 匹配时，如果通配符出现在左侧无法使用索引
- 如果 WHERE 条件中的字段上创建了索引，尽量设置为 NOT NULL

3.3 SQL的执行顺序

程序员编写的SQL
MySQL执行的SQL

1. FORM子句 : 左右两个表的笛卡尔积
2. ON: 筛选满足条件的数据
3. JOIN： 如果是 inner join 那就正常,如果是 outer join 则会添加回来上面一步过滤掉的一些行
4. WHERE： 对不满足条件的行进行移除, 并且不能恢复
5. GROUP BY: 分组后只能得到每组的第一行数据,或者聚合函数的数值
6. 6. HAVING： 对分组后的数据进行筛选
7. SELECT： 执行select操作，获取需要的列。
8. DISTINCT： 去重
9. ORDER BY: 排序
10. LIMIT：取出指定行的记录, 并将结果返回。

查看下面的SQL 分析执行顺序

select
id,
sex,
count(*) AS num
from
employee
where name is not null
group by sex
order by id

上面的SQL执行执行顺序如下

1. 首先执行 FROM 子句, 从 employee 表组装数据源的数据
2. 执行 WHERE 子句, 筛选 employee 表中所有name不为 NULL 的数据
3. 执行 GROUP BY 子句, 按 "性别" 列进行分组
4. 执行select操作，获取需要的列。
5. 最后执行order by，对最终的结果进行排序。

3.4 JOIN查询的七种方式

7中JOIN ,可以分为四类: 内连接、左连接、右连接、全连接

3.5 JOIN查询SQL编写

创建表插入数据

---部门表
DROP TABLE IF EXISTS `t_dept`;
CREATE TABLE `t_dept` (
`id` varchar(40) NOT NULL,
`name` varchar(40) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
---员工表
DROP TABLE IF EXISTS `t_emp`;
CREATE TABLE `t_emp` (
`id` varchar(40) NOT NULL,
`name` varchar(40) DEFAULT NULL,
`age` int(3) DEFAULT NULL,
`deptid` varchar(40) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `deptid` (`deptid`),
CONSTRAINT `deptid` FOREIGN KEY (`deptid`) REFERENCES `t_dept` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
--插入部门数据
INSERT INTO `t_dept` VALUES ('1', '研发部');
INSERT INTO `t_dept` VALUES ('2', '人事部');
INSERT INTO `t_dept` VALUES ('3', '财务部');
--插入员工数据
INSERT INTO `t_emp` VALUES ('1', '赵四', 23, '1');
INSERT INTO `t_emp` VALUES ('2', '刘能', 25, '2');
INSERT INTO `t_emp` VALUES ('3', '广坤', 27, '1');
INSERT INTO `t_emp` VALUES ('4', '玉田', 43, NULL);

3.5.1 内连接

在这里插入图片描述

SELECT * FROM t_emp e INNER JOIN t_dept d ON e.deptid = d.id

3.5.2 左连接

在这里插入图片描述

SELECT * FROM t_emp e LEFT JOIN t_dept d ON e.deptid = d.id

3.5.3 左连接去重叠部分

SELECT * FROM t_emp e LEFT JOIN t_dept d ON e.deptid = d.id
WHERE e.deptid IS NULL;

3.5.4 右连接

在这里插入图片描述

SELECT * FROM t_emp e RIGHT JOIN t_dept d ON e.deptid = d.id

3.5.5 右连接去重叠部分

在这里插入图片描述

SELECT * FROM t_emp e RIGHT JOIN t_dept d ON e.deptid = d.id
WHERE e.id IS NULL;

3.5.6 全连接

在这里插入图片描述

SELECT * FROM t_emp e LEFT JOIN t_dept d ON e.deptid = d.id
UNION
SELECT * FROM t_emp e RIGHT JOIN t_dept d ON e.deptid = d.id

MySQL UNION 操作符用于连接两个以上的 SELECT 语句的结果组合到一个结果集合中。多个SELECT 语句会删除重复的数据。

3.5.7 各自独有

在这里插入图片描述

SELECT * FROM t_emp e LEFT JOIN t_dept d ON e.deptid = d.id
WHERE e.deptid IS NULL
UNION
SELECT * FROM t_emp e RIGHT JOIN t_dept d ON e.deptid = d.id
WHERE e.id IS NULL

3.6 慢查询日志分析

3.6.1 慢查询介绍

MySQL的慢查询，全名是慢查询日志，是MySQL提供的一种日志记录，用来记录在MySQL中响应时间超过阈值的语句。
默认情况下，MySQL数据库并不启动慢查询日志，需要手动来设置这个参数。
如果不是调优需要的话，一般不建议启动该参数，因为开启慢查询日志会或多或少带来一定的性能影响。
-慢查询日志支持将日志记录写入文件和数据库表。

3.6.2 慢查询参数

执行下面的语句

SHOW VARIABLES LIKE "%query%" ;

MySQL 慢查询的相关参数解释：
slow_query_log：是否开启慢查询日志， 1 表示开启， 0 表示关闭。
slow-query-log-file：新版（5.6及以上版本）MySQL数据库慢查询日志存储路径。
long_query_time：慢查询阈值，当查询时间多于设定的阈值时，记录日志。

3.6.3 慢查询配置方式

默认情况下slow_query_log的值为OFF，表示慢查询日志是禁用的

mysql> SHOW VARIABLES LIKE '%slow_query_log%';
+---------------------+-----------------------------------+
| Variable_name    | Value               |
+---------------------+-----------------------------------+
| slow_query_log   | OFF                |
| slow_query_log_file | /var/lib/mysql/localhost-slow.log |
+---------------------+-----------------------------------+

可以通过设置slow_query_log的值来开启

mysql> set global slow_query_log=1;
mysql> SHOW VARIABLES LIKE '%slow_query_log%';
+---------------------+-----------------------------------+
| Variable_name    | Value               |
+---------------------+-----------------------------------+
| slow_query_log   | ON                |
| slow_query_log_file | /var/lib/mysql/localhost-slow.log |
+---------------------+-----------------------------------+

使用 set global slow_query_log=1 开启了慢查询日志只对当前数据库生效，MySQL重启后则会失效。如果要永久生效，就必须修改配置文件my.cnf（其它系统变量也是如此）

-- 编辑配置
vim /etc/my.cnf
-- 添加如下内容
slow_query_log =1
slow_query_log_file=/var/lib/mysql/lagou-slow.log
-- 重启MySQL
service mysqld restart
mysql> SHOW VARIABLES LIKE '%slow_query_log%';
+---------------------+-------------------------------+
| Variable_name    | Value             |
+---------------------+-------------------------------+
| slow_query_log   | ON              |
| slow_query_log_file | /var/lib/mysql/lagou-slow.log |
+---------------------+-------------------------------+

那么开启了慢查询日志后，什么样的SQL才会记录到慢查询日志里面呢？这个是由参数
long_query_time 控制，默认情况下long_query_time的值为10秒

| Variable_name  | Value   |
+-----------------+-----------+
| long_query_time | 10.000000 |
+-----------------+-----------+
mysql> set global long_query_time=1;
Query OK, 0 rows affected (0.00 sec)
mysql> show variables like 'long_query_time';
+-----------------+-----------+
| Variable_name  | Value   |
+-----------------+-----------+
| long_query_time | 10.000000 |

我修改了变量long_query_time，但是查询变量long_query_time的值还是10，难道没有修改到呢？
注意：使用命令 set global long_query_time=1 修改后，需要重新连接或新开一个会话才能看到修改值。

mysql> show variables like 'long_query_time';
+-----------------+----------+
| Variable_name  | Value  |
+-----------------+----------+
| long_query_time | 1.000000 |
+-----------------+----------+

log_output 参数是指定日志的存储方式。 log_output=‘FILE’ 表示将日志存入文件，默认值是’FILE’。 log_output=‘TABLE’ 表示将日志存入数据库，这样日志信息就会被写入到mysql.slow_log 表中。

mysql> SHOW VARIABLES LIKE '%log_output%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_output  | FILE |
+---------------+-------+

MySQL数据库支持同时两种日志存储方式，配置的时候以逗号隔开即可，如：log_output=‘FILE,TABLE’。日志记录到系统的专用日志表中，要比记录到文件耗费更多的系统资源，因此对于需要启用慢查询日志，又需要能够获得更高的系统性能，那么建议优先记录到文件.

系统变量 log-queries-not-using-indexes ：未使用索引的查询也被记录到慢查询日志中（可选项）。如果调优的话，建议开启这个选项。

mysql> show variables like 'log_queries_not_using_indexes';
+-------------------------------+-------+
| Variable_name         | Value |
+-------------------------------+-------+
| log_queries_not_using_indexes | OFF  |
+-------------------------------+-------+
mysql> set global log_queries_not_using_indexes=1;
Query OK, 0 rows affected (0.00 sec)
mysql> show variables like 'log_queries_not_using_indexes';
+-------------------------------+-------+
| Variable_name         | Value |
+-------------------------------+-------+
| log_queries_not_using_indexes | ON  |
+-------------------------------+-------+
1 row in set (0.00 sec)

4.MySQL存储引擎

4.1 存储引擎介绍

什么是存储引擎

存储引擎就是如何存储数据、如何为存储的数据建立索引和如何更新、查询数据等技术的实现方法。就像汽车的发动机一样, 存储引擎好坏决定的数据库提供的功能和性能

存储引擎的作用

并发性
事务支持
引用完整性
索引支持

4.2 常见的3种存储引擎

MySQL给用户提供了很多种类的存储引擎, 主要分两大类:
- 事务安全表: InnoDB
- 非事务安全表: MyISAM、MEMORY、MERGE、EXAMPLE、NDB Cluster、ARCHIVE、CSV、BLACKHOLE、FEDERATED等。
查看MySQL数据的存储引擎有哪些

SHOW ENGINES;

在这里插入图片描述

查看当前的默认存储引擎 (MySQL5.7 默认使用 InnoDB)

SHOW VARIABLES LIKE '%default_storage_engine%';

在这里插入图片描述

在MySQL中,不需要整个服务器都是用同一种引擎,针对具体的需求,可以对每一个表使用不同的存储引擎 .并且想要进一步优化, 还可以自己编写一个存储引擎.

-- 创建新表时指定存储引擎
create table(...) engine=MyISAM;

4.2.1 InnoDB(推荐)

InnoDB是一个健壮的事务型存储引擎，这种存储引擎已经被很多互联网公司使用，为用户操作非常大的数据存储提供了一个强大的解决方案。InnoDB还引入了行级锁定和外键约束，在以下场合下，使用InnoDB是最理想的选择
优点

Innodb引擎提供了对数据库ACID事务的支持，并且实现了SQL标准的四种隔离级别
支持多版本并发控制的行级锁，由于锁粒度小，写操作和更新操作并发高、速度快。
支持自增长列。
支持外键。
适合于大容量数据库系统，支持自动灾难恢复。

缺点

它没有保存表的行数，当SELECT COUNT(*) FROM TABLE时需要扫描全表

应用场景

当需要使用数据库事务时，该引擎当然是首选。由于锁的粒度更小，写操作不会锁定全表，所以在并发较高时，使用Innodb引擎会提升效率
更新密集的表, InnoDB存储引擎特别适合处理多重并发的更新请求。

4.2.2 MyISAM

MyISAM引擎, 不支持事务、也不支持外键，优势是访问速度快，对事务完整性没有要求或者以select，insert为主的应用基本上可以用这个引擎来创建表
优点

MyISAM表是独立于操作系统的，这说明可以轻松地将其从Windows服务器移植到Linux服务器。
MyISAM存储引擎在查询大量数据时非常迅速，这是它最突出的优点
另外进行大批量插入操作时执行速度也比较快。

缺点

MyISAM表没有提供对数据库事务的支持。
不支持行级锁和外键。
不适合用于经常UPDATE（更新）的表，效率低。

应用场景

以读为主的业务，例如：图片信息数据库，博客数据库，商品库等业务。
对数据一致性要求不是非常高的业务（不支持事务）
硬件资源比较差的机器可以用 MyiSAM （占用资源少）

4.2.3 MEMORY

MEMORY的特点是将表中的数据放在内存中，适用于存储临时数据的临时表和数据仓库中的纬度表
优点

memory类型的表访问非常的快，因为它的数据是放在内存中的

缺点

一旦服务关闭，表中的数据就会丢失掉。
只支持表锁，并发性能差，不支持TEXT和BLOB列类型，存储varchar时是按照char的方式

应用场景

目标数据较小，而且被非常频繁地访问。
如果数据是临时的，而且要求必须立即可用，那么就可以存放在内存表中。
存储在Memory表中的数据如果突然丢失，不会对应用服务产生实质的负面影响。

4.3 如何选择存储引擎

不同的存储引擎都有各自的特点，以适应不同的需求，如表所示。为了做出选择，首先要考虑每一个存储引擎提供了哪些不同的功能。

特性	InnoDB	MyISAM	MEMORY
存储限制(Storage limits)	64TB	No	YES
支持事物(Transactions)	Yes	No	No
锁机制(Locking granularity)	行锁	表锁	表锁
B树索引(B-tree indexes)	Yes	Yes	Yes
哈希索引(Hash indexes)	Yes	No	Yes
外键支持(Foreign key support)	Yes	No	No
存储空间消耗(Storage Cost)	高	低	低
内存消耗(Memory Cost)	高	低	高
批量数据写入效率(Bulk insert speed)	慢	快	快

提供几个选择标准，然后按照标准，选择对应的存储引擎
- 是否需要支持事务；
- 崩溃恢复，能否接受崩溃；
- 是否需要外键支持；
- 存储的限制；
- 对索引和缓存的支持；

5.MySQL索引优化

5.1 索引简介

5.1.1 什么是索引

索引就是排好序的,帮助我们进行快速查找的数据结构.
简单来讲，索引就是一种将数据库中的记录按照特殊形式存储的数据结构。通过索引，能够显著地提高数据查询的效率，从而提升服务器的性能.
专业一点来说呢，索引是一个排好序的列表，在这个列表中存储着索引的值和包含这个值的数据所在行的物理地址。在数据库十分庞大的时候，索引可以大大加快查询的速度，这是因为使用索引后可以不用扫描全表来定位某行的数据，而是先通过索引表找到该行数据对应的物理地址然后访问相应的数据。

没有用索引时执行 select * from where t.Col2 = , 数据从磁盘一条一条拿去最终找到结果，效率低下。
为了加快查找,可以维护一个二叉树,左侧节点小于父节点, 右侧节点大于父节点,每个节点分别保存字段数据和一个指向对应数据记录物理地址的指针.
查找时就可以使用二叉树查找获取相应的数据,从而快速检索出符合条件的记录

一般来说索引本身也比较大,不可能全部保存在内存中,因此索引通常是以索引文件的形式存储在磁盘上

5.1.2 索引的种类

普通索引
- 这是最基本的索引类型，基于普通字段建立的索引，没有任何限制。

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

唯一索引
- 与"普通索引"类似，不同的就是：索引字段的值必须唯一，但允许有空值。

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

主键索引
- 它是一种特殊的唯一索引，不允许有空值。在创建或修改表时追加主键约束即可，每个表只能有一个主键。

CREATE TABLE tablename ( [...], PRIMARY KEY (字段名

复合索引
- 用户可以在多个列上建立索引，这种索引叫做组复合索引（组合索引）。复合索引可以代替多个单一索引，相比多个单一索引复合索引所需的开销更小。

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

- 复合索引使用注意事项：
1. 何时使用复合索引，要根据where条件建索引，注意不要过多使用索引，过多使用会对更新操作效率有很大影响。
2. 如果表已经建立了(col1，col2)，就没有必要再单独建立（col1）；如果现在有(col1)索引，如果查询需要col1和col2条件，可以建立(col1,col2)复合索引，对于查询有一定提高。
全文索引
- 查询操作在数据量比较少时，可以使用like模糊查询，但是对于大量的文本数据检索，效率很低。如果使用全文索引，查询速度会比like快很多倍。在MySQL 5.6 以前的版本，只有
  MyISAM存储引擎支持全文索引，从MySQL 5.6开始MyISAM和InnoDB存储引擎均支持。复合索引使用注意事项：

何时使用复合索引，要根据where条件建索引，注意不要过多使用索引，过多使用会对
更新操作效率有很大影响。
如果表已经建立了(col1，col2)，就没有必要再单独建立（col1）；如果现在有(col1)索
引，如果查询需要col1和col2条件，可以建立(col1,col2)复合索引，对于查询有一定提
高。

全文索引
- 查询操作在数据量比较少时，可以使用like模糊查询，但是对于大量的文本数据检索，效率很
  低。如果使用全文索引，查询速度会比like快很多倍。在MySQL 5.6 以前的版本，只有MyISAM存储引擎支持全文索引，从MySQL 5.6开始MyISAM和InnoDB存储引擎均支持。

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

- 全文索引方式有自然语言检索 IN NATURAL LANGUAGE MODE 和布尔检索 IN BOOLEAN MODE两种
和常用的like模糊查询不同，全文索引有自己的语法格式，使用 match 和 against 关键字，比如
- 全文索引使用注意事项：
  - 全文索引必须在字符串、文本字段上建立。
  - 全文索引字段值必须在最小字符和最大字符之间的才会有效。（innodb：3-84；myisam：4-84）

SELECT * FROM users3 WHERE MATCH(NAME) AGAINST('aabb');
-- * 表示通配符,只能在词的后面
SELECT * FROM users3 WHERE MATCH(NAME) AGAINST('aa*'  IN BOOLEAN MODE);

# 5.1.3 索引的优势与劣势

优点
- 提高数据检索的效率,降低数据库的IO成本
- 通过索引列对数据进行排序,降低数据排序的成本,降低了CPU的消耗
缺点
- 创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增加
- 索引需要占物理空间，除了数据表占用数据空间之外，每一个索引还要占用一定的物理空间
- 当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，降低了数据的维护速度
创建索引的原则
- 在经常需要搜索的列上创建索引，可以加快搜索的速度；
- 在作为主键的列上创建索引，强制该列的唯一性和组织表中数据的排列结构；
- 在经常用在连接的列上，这些列主要是一些外键，可以加快连接的速度；
- 在经常需要根据范围进行搜索的列上创建索引，因为索引已经排序，其指定的范围是连续的；
- 在经常需要排序的列上创建索引，因为索引已经排序，这样查询可以利用索引的排序，加快排序查询时间；
- 在经常使用在WHERE子句中的列上面创建索引，加快条件的判断速度。

5.2 索引原理

MySQL中索引的常用数据结构有两种，一种是Hash，另一种是BTree。

5.2.1 HASH结构

Hash底层实现是由Hash表来实现的，是根据键值 <key,value> 存储数据的结构。非常适合根据key查找value值，也就是单个key查询，或者说等值查询。
- 对于每一行数据，存储引擎都会对所有的索引列计算一个哈希码，哈希码是一个较小的值，并且不同键值的行计算出来的哈希码也不一样。
Hash索引的缺点
- 哈希索引只包含哈希值和行指针，而不存储字段值，所以不能使用索引中的值来避免读取行。
- 哈希索引数据并不是按照索引值顺序存储的，所以也就无法用于排序。
- 哈希索引只支持等值比较查询。不支持任何范围查询和部分索引列匹配查找。
Hsah索引的优点
- 只需要做等值比较查询，而不包含排序或范围查询的需求，都适合使用哈希索引
- 访问哈希索引的数据非常快，除非有很多哈希冲突。

5.2.2 B+Tree结构

MySQL数据库索引采用的是B+Tree结构，在B-Tree结构上做了优化改造。

B+Tree结构
- 非叶子节点不存储data数据，只存储索引值，这样便于存储更多的索引值
- 叶子节点包含了所有的索引值和data数据
- 叶子节点用指针连接，提高区间的访问性能
B树索引的应用
- 全键值查询 where x=123
- 键值范围查询 where 45 < x < 123

5.3 EXPLAIN性能分析

5.3.1 EXPLAIN简介

概述
- 使用 EXPLAIN 关键字可以模拟优化器执行SQL查询语句，从而知道MySQL是如何处理你的SQL语句的。分析你的查询语句或是表结构的性能瓶颈。通过explain我们可以获得以下信息：
EXPLAIN的作用
- 表的读取顺序。（对应id）
- 数据读取操作的操作类型。（对应select_type）
- 哪些索引可以使用。（对应possible_keys）
- 哪些索引被实际使用。（对应key）
- 表直接的引用。（对应ref）
- 每张表有多少行被优化器查询。（对应rows）
EXPLAIN的入门
- explain使用：explain+sql语句，通过执行explain可以获得sql语句执行的相关信息

explain select * from course;

在这里插入图片描述

5.3.2 EXPLAIN字段介绍

1.数据准备

-- 创建数据库
CREATE DATABASE test_explain CHARACTER SET 'utf8';
-- 创建表
CREATE TABLE L1(id INT PRIMARY KEY AUTO_INCREMENT,title VARCHAR(100) );
CREATE TABLE L2(id INT PRIMARY KEY AUTO_INCREMENT,title VARCHAR(100) );
CREATE TABLE L3(id INT PRIMARY KEY AUTO_INCREMENT,title VARCHAR(100) );
CREATE TABLE L4(id INT PRIMARY KEY AUTO_INCREMENT,title VARCHAR(100) );
-- 每张表插入3条数据
INSERT INTO L1(title) VALUES('lagou01');
INSERT INTO L2(title) VALUES('lagou02');
INSERT INTO L3(title) VALUES('lagou03');
INSERT INTO L4(title) VALUES('lagou04');

2.ID介绍
select查询的序列号，包含一组数字，表示查询中执行select子句或操作表的顺序

id相同，执行顺序由上至下

EXPLAIN SELECT * FROM L1,L2,L3 WHERE L1.id=L2.id AND L2.id = L3.id;

在这里插入图片描述

id不同，如果是子查询，id的序号会递增，id值越大优先级越高，越先被执行

EXPLAIN SELECT * FROM L2 WHERE id = (
SELECT id FROM L1 WHERE id = (SELECT L3.id FROM L3 WHERE L3.title ='lagou03'));

在这里插入图片描述
3.select_type和table介绍
查询类型，主要用于区别普通查询，联合查询，子查询等的复杂查询

simple : 简单的select查询，查询中不包含子查询或者UNION

EXPLAIN SELECT * FROM L1;

在这里插入图片描述

primary : 查询中若包含任何复杂的子部分，最外层查询被标记

EXPLAIN SELECT * FROM L2 WHERE id = (
SELECT id FROM L1 WHERE id = (SELECT L3.id FROM L3 WHERE L3.title ='lagou03'));

在这里插入图片描述

subquery : 在select或where列表中包含了子查询

EXPLAIN SELECT * FROM L2 WHERE L2.id = (SELECT id FROM L3 WHERE L3.title =
'lagou03' )

在这里插入图片描述

erived : 在from列表中包含的子查询被标记为derived（衍生），MySQL会递归执行这些子查询，把结果放到临时表中
union : 如果第二个select出现在UNION之后，则被标记为UNION，如果union包含在from子句的子查询中，外层select被标记为derived
union result : UNION 的结果

EXPLAIN SELECT * FROM L2
UNION
SELECT * FROM L3

在这里插入图片描述
4.type介绍
type显示的是连接类型，是较为重要的一个指标。下面给出各种连接类型,按照从最佳类型到最坏类型进行排序:

system > const > eq_ref > ref > fulltext > ref_or_null > index_merge >
unique_subquery > index_subquery > range > index > ALL
-- 简化
system > const > eq_ref > ref > range > index > ALL

system : 表仅有一行 (等于系统表)。这是const连接类型的一个特例,很少出现。
const : 表示通过索引一次就找到了, const用于比较 primary -key 或者 unique 索引. 因为只匹配一行数据,所以如果将主键放在 where条件中, MySQL就能将该查询转换为一个常量

EXPLAIN SELECT * FROM L1 WHERE L1.id = 1

在这里插入图片描述

eq_ref : 唯一性索引扫描,对于每个索引键,表中只有一条记录与之匹配. 常见与主键或唯一索引扫描

EXPLAIN SELECT * FROM L1 ,L2 WHERE L1.id = L2.id ;

在这里插入图片描述

ef : 非唯一性索引扫描, 返回匹配某个单独值的所有行, 本质上也是一种索引访问, 它返回所有匹配某个单独值的行, 这是比较常见连接类型.
未加索引之前

EXPLAIN SELECT * FROM L1 ,L2 WHERE L1.title = L2.title ;

在这里插入图片描述

加索引之后

CREATE INDEX idx_title ON L2(title);
EXPLAIN SELECT * FROM L1 ,L2 WHERE L1.title = L2.title ;

在这里插入图片描述

range : 只检索给定范围的行,使用一个索引来选择行。

EXPLAIN SELECT * FROM L1 WHERE L1.id > 10;
EXPLAIN SELECT * FROM L1 WHERE L1.id IN (1,2);

在这里插入图片描述
key显示使用了哪个索引. where 子句后面使用 between 、< 、> 、in 等查询, 这种范围查询要比全表扫描好

index : 出现index 是 SQL 使用了索引, 但是没有通过索引进行过滤,一般是使用了索引进行排序分组

EXPLAIN SELECT * FROM L1 ORDER BY id;

在这里插入图片描述

ALL : 对于每个来自于先前的表的行组合,进行完整的表扫描。

EXPLAIN SELECT * FROM L1;

在这里插入图片描述
一般来说,需要保证查询至少达到 range级别,最好能到ref

5.possible_keys 与 key介绍

possible_keys
- 显示可能应用到这张表上的索引, 一个或者多个. 查询涉及到的字段上若存在索引, 则该索引将被列出, 但不一定被查询实际使用.
key
- 实际使用的索引，若为null，则没有使用到索引。（两种可能，1.没建立索引, 2.建立索引，但索引失效）。查询中若使用了覆盖索引，则该索引仅出现在key列表中。
- 覆盖索引：一个索引包含(或覆盖)所有需要查询的字段的值,通过查询索引就可以获取到字段值

理论上没有使用索引,但实际上使用了

EXPLAIN SELECT L1.id FROM L1;

在这里插入图片描述
2. 理论和实际上都没有使用索引

EXPLAIN SELECT * FROM L1 WHERE title = 'lagou01';

在这里插入图片描述
3. 理论和实际上都使用了索引

EXPLAIN SELECT * FROM L2 WHERE title = 'lagou02';

在这里插入图片描述
6.key_len介绍
表示索引中使用的字节数, 可以通过该列计算查询中使用索引的长度.
key_len 字段能够帮你检查是否充分利用了索引 ken_len 越长, 说明索引使用的越充分

创建表

- CREATE TABLE T1(
a INT PRIMARY KEY,
b INT NOT NULL,
c INT DEFAULT NULL,
 d CHAR(10) NOT NULL
);

使用explain 进行测试

EXPLAIN SELECT * FROM T1 WHERE a > 1 AND b = 1;

索引中只包含了1列,所以,key_len是4。
在这里插入图片描述

为b字段添加索引

ALTER TABLE T1 ADD INDEX idx_b(b);

再次测试

EXPLAIN SELECT * FROM T1 WHERE a > 1 AND b = 1;

两列都使用了索引,所以,这里ken_len是8。

为d字段添加索引

 ALTER TABLE T1 ADD INDEX idx_d(d);
 执行测试
EXPLAIN SELECT * FROM T1 WHERE d = '';

在这里插入图片描述
字符集是utf8 一个字符3个字节，d字段是 char(10)代表的是10个字符相当30个字节

7.ref 介绍

显示索引的哪一列被使用了，如果可能的话，是一个常数。哪些列或常量被用于查找索引列上的值
- L1.id=‘1’; 1是常量 , ref = const

EXPLAIN SELECT * FROM L1 WHERE L1.id='1';

在这里插入图片描述

L2表被关联查询的时候,使用了主键索引, 而值使用的是驱动表(执行计划中靠前的表是驱动
表)L1表的ID, 所以 ref = test_explain.L1.id

EXPLAIN SELECT * FROM L1 LEFT JOIN L2 ON L1.id = L2.id WHERE L1.title ='lagou01';

在这里插入图片描述
8.rows 介绍

表示MySQL根据表统计信息及索引选用情况，估算的找到所需的记录所需要读取的行数；越少越
好

使用like 查询,会产生全表扫描, L2中有3条记录,就需要读取3条记录进行查找

EXPLAIN SELECT * FROM L1,L2 WHERE L1.id = L2.id AND L2.title LIKE '%la%';

如果使用等值查询, 则可以直接找到要查询的记录,返回即可,所以只需要读取一条

EXPLAIN SELECT * FROM L1,L2 WHERE L1.id = L2.id AND L2.title = 'lagou03';

在这里插入图片描述

总结: 当我们需要优化一个SQL语句的时候，我们需要知道该SQL的执行计划，比如是全表扫描，还是索引扫描；使用 explain 关键字可以模拟优化器执行 sql 语句，从而知道 mysql 是如何处理 sql 语句的,方便我们开发人员有针对性的对SQL进行优化.

表的读取顺序。（对应id）
数据读取操作的操作类型。（对应select_type）
哪些索引可以使用。（对应possible_keys）
哪些索引被实际使用。（对应key）
-每张表有多少行被优化器查询。（对应rows）
评估sql的质量与效率 (对应type)

9.extra 介绍
Extra 是 EXPLAIN 输出中另外一个很重要的列，该列显示MySQL在查询过程中的一些详细信息

准备数据

CREATE TABLE users (
uid INT PRIMARY KEY AUTO_INCREMENT,
uname VARCHAR(20),
age INT(11)
);
INSERT INTO users VALUES(NULL, 'lisa',10);
INSERT INTO users VALUES(NULL, 'lisa',10);
INSERT INTO users VALUES(NULL, 'rose',11);
INSERT INTO users VALUES(NULL, 'jack', 12);
INSERT INTO users VALUES(NULL, 'sam', 13);

Using filesort

EXPLAIN SELECT * FROM users ORDER BY age;

在这里插入图片描述
执行结果Extra为 Using filesort ，这说明，得到所需结果集，需要对所有记录进行文件排序。这类SQL语句性能极差，需要进行优化。

典型的，在一个没有建立索引的列上进行了order by，就会触发filesort，常见的优化方案是，在order by的列上添加索引，避免每次查询都全量排序。

filtered 它指返回结果的行占需要读到的行(rows列的值)的百分比

Using temporary

EXPLAIN SELECT COUNT(*),sex FROM users WHERE uid > 2  GROUP BY sex;

在这里插入图片描述
执行结果Extra为 Using temporary ，这说明需要建立临时表 (temporary table) 来暂存中间结果。
常见与 group by 和 order by，这类SQL语句性能较低，往往也需要进行优化。

Using where

EXPLAIN SELECT * FROM users WHERE age=10;

在这里插入图片描述
此语句的执行结果Extra为Using where，表示使用了where条件过滤数据
需要注意的是：

返回所有记录的SQL，不使用where条件过滤数据，大概率不符合预期，对于这类SQL往往需要进行优化；
使用了where条件的SQL，并不代表不需要优化，往往需要配合explain结果中的type（连接类型）来综合判断。例如本例查询的 age 未设置索引，所以返回的type为ALL，仍有优化空
间，可以建立索引优化查询。

Using index

EXPLAIN SELECT uid,uname FROM users WHERE uname='lisa';

在这里插入图片描述
此句执行结果为Extra为Using index，说明sql所需要返回的所有列数据均在一棵索引树上，而无需访问实际的行记录。

Using join buffer

EXPLAIN SELECT * FROM users u1 LEFT JOIN (SELECT * FROM users WHERE sex =
'0') u2 ON u1.uname = u2.uname;

在这里插入图片描述

执行结果Extra为 Using join buffer (Block Nested Loop) 说明，需要进行嵌套循环计算, 这里每个表都有五条记录，内外表查询的type都为ALL。
问题在于两个关联表join 使用 uname，关联字段均未建立索引，就会出现这种情况。
常见的优化方案是，在关联字段上添加索引，避免每次嵌套循环计算。

6.MySQL锁机制

6.1 MySQL锁概述

1.锁的概念
数据库锁定机制简单来说，就是数据库为了保证数据的一致性，而使各种共享资源在被并发访问变得有序所设计的一种规则.
假设当前商品只有一件, 两个用户同时购买,我们需要保证只有一个用户能下单成功.

在这里插入图片描述
因为购买行为是一组操作,这里需要使用事务控制,从获取商品数量,插入订单到付款后插入付款信息,更新
商品数量.在这个过程中,使用锁可以对有限的资源进行保护.

6.2 MySQL的锁分类

MySQL数据库由于其自身架构的特点,存在多种数据存储引擎, MySQL中不同的存储引擎支持不同的锁机制。

MyISAM和MEMORY存储引擎采用的表级锁，
InnoDB存储引擎既支持行级锁，也支持表级锁，默认情况下采用行级锁。
BDB采用的是页面锁，也支持表级锁

按照数据操作的类型分

读锁（共享锁）：针对同一份数据，多个读操作可以同时进行而不会互相影响。
写锁（排他锁）：当前写操作没有完成前，它会阻断其他写锁和读锁。

按照数据操作的粒度分

表级锁：开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高，并发度最低。
行级锁：开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度也最高。
页面锁：开销和加锁时间界于表锁和行锁之间；会出现死锁；锁定粒度界于表锁和行锁之间，并发度一般

按照操作性能可分为乐观锁和悲观锁

乐观锁：一般的实现方式是对记录数据版本进行比对，在数据更新提交的时候才会进行冲突检测，如果发现冲突了，则提示错误信息。
悲观锁：在对一条数据修改的时候，为了避免同时被其他人修改，在修改数据之前先锁定，再修改的控制方式。共享锁和排他锁是悲观锁的不同实现，但都属于悲观锁范畴。

6.3 表级锁

表级锁是MySQL中锁定粒度最大的一种锁，表示对当前操作的整张表加锁，它实现简单，资源消耗较少，被大部分MySQL引擎支持。最常使用的MYISAM与INNODB都支持表级锁定。
表级锁定分为: 表共享读锁（共享锁）与表独占写锁（排他锁）。
特点: 开销小，加锁快；不会出现死锁；锁定粒度大，发出锁冲突的概率最高，并发度最低。

6.3.1 数据准备

-- 创建数据库
CREATE DATABASE test_lock CHARACTER SET 'utf8';
-- 创建表,选择 MYISAM存储引擎
CREATE TABLE mylock01(
id INT PRIMARY KEY AUTO_INCREMENT,
title VARCHAR(20)
)ENGINE MYISAM;
-- 创建表
CREATE TABLE mylock02(
id INT PRIMARY KEY AUTO_INCREMENT,
title VARCHAR(20)
)ENGINE MYISAM;
-- mylock01表中向插入数据
INSERT INTO mylock01(title) VALUES('a1');
INSERT INTO mylock01(title) VALUES('b1');
INSERT INTO mylock01(title) VALUES('c1');
INSERT INTO mylock01(title) VALUES('d1');
INSERT INTO mylock01(title) VALUES('e1');
-- mylock02表中向插入数据
INSERT INTO mylock02(title) VALUES('a');
INSERT INTO mylock02(title) VALUES('b');
INSERT INTO mylock02(title) VALUES('c');
INSERT INTO mylock02(title) VALUES('d');
INSERT INTO mylock02(title) VALUES('e');
SELECT * FROM mylock01;

6.3.2 加锁语法

查看表中加过的锁

-- 0表示没有加锁,当前的所有数据库表都没有加锁
SHOW OPEN TABLES;
-- 查询加锁的表,条件In_use 大于0
SHOW OPEN TABLES WHERE In_use > 0;

在这里插入图片描述
手动增加表锁

-- 语法格式: LOCK TABLE 表名 READ(WRITE), 表名2 READ(WRITE), 其他;
-- 为mylock01加读锁(共享锁) , 给mylock02加写锁(排他锁)
lock table mylock01 read,mylock02 write;
SHOW OPEN TABLES WHERE In_use > 0;

在这里插入图片描述
释放锁, 解除锁定

-- 方式1
unlock tables;
-- 方式2 找到锁进程,得到id
SHOW PROCESSLIST;
kill id

在这里插入图片描述

6.3.3 加读锁测试

MySQL 的表级锁有两种模式:
- 表共享读锁(Table Read Lock)
- 表独占写锁(Table Write Lock)。

对mylock01表加读锁

lock table mylock01 read;

开启两个窗口,对mylock01进行读操作, 两个窗口都可以读

select * from mylock01;

在这里插入图片描述

在1窗口进行写操作 (update), 失败

update mylock01 set title='a123' where id = 1;

在这里插入图片描述

在1窗口中读取其他的表,比如读取 mylock 02表. 读取失败.

select * from mylock02;

错误提示: 表“mylock02” 未用锁表锁定
在这里插入图片描述
5. 在2窗口中对 mylock01表进行写操作

update mylock01 set title='a123' where id = 1;

执行后一直阻塞
在这里插入图片描述
6. 解除 mylock01 的锁定,窗口2 的修改执行.

unlock tables;

在这里插入图片描述
总结:

对MyISAM表的读操作 (加读锁) ,不会阻塞其他进程对同一表的读请求,但是会阻塞对同一表的写请
求. 只有当读锁释放后,才会执行其他进程的写操作.

6.3.4 加写锁测试

总结:
- 对MyISAM表加写锁, 会阻塞其他进程对同一表的读和写操作,只有当写锁释放后,才会执行其他进
程的操作

6.4 行级锁(偏写)

6.4.1 行级锁介绍

行锁的是mysql锁中粒度最小的一种锁，因为锁的粒度很小，所以发生资源争抢的概率也最小，并发性能最大，但是也会造成死锁，每次加锁和释放锁的开销也会变大。

使用MySQL行级锁的两个前提
- 使用 innoDB 引擎
- 开启事务 (隔离级别为 Repeatable Read )
InnoDB行锁的类型
- 共享锁（S）：当事务对数据加上共享锁后, 其他用户可以并发读取数据，但任何事务都不能对数据进行修改（获取数据上的排他锁），直到已释放所有共享锁。
- 排他锁（X）：如果事务T对数据A加上排他锁后，则其他事务不能再对数据A加任任何类型的封锁。获准排他锁的事务既能读数据，又能修改数据。
加锁的方式
- InnoDB引擎默认更新语句，update,delete,insert 都会自动给涉及到的数据加上排他锁，select语句默认不会加任何锁类型，如果要加可以使用下面的方式:
- 加共享锁（S）：select * from table_name where … lock in share mode;
- 加排他锁（x）：select * from table_name where … for update;
锁兼容
- 共享锁只能兼容共享锁, 不兼容排它锁
- 排它锁互斥共享锁和其它排它锁
行级锁都是基于索引的，如果一条 SQL 语句用不到索引是不会使用行级锁的，而会使用表级锁把整张表锁住，这点需要咱们格外的注意

6.4.2 行锁测试

总结:

行级锁中的写锁主要是为了解决在修改数据时，不允许其他事务对当前数据进行修改和读取操作，从而可以有效避免”脏读”问题的产生。
在有写锁的情况下，其他事务不能再对当前数据添加写锁，从而保证数据的一致性,从而避免了不可重复读的问题.

6.4.2 行锁分析

执行下面的命令,可以获取行锁锁信息

mysql> SHOW STATUS LIKE 'innodb_row_lock%';
+-------------------------------+-------+
| Variable_name         | Value |
+-------------------------------+-------+
| Innodb_row_lock_current_waits | 0   |
| Innodb_row_lock_time     | 51265 |
| Innodb_row_lock_time_avg   | 51265 |
| Innodb_row_lock_time_max   | 51265 |
| Innodb_row_lock_waits     | 1   |
+-------------------------------+-------+

参数说明

Innodb_row_lock_current_waits：当前正在等待锁定的数量。
Innodb_row_lock_time：从系统启动到现在锁定总时间长度（重要）。
Innodb_row_lock_time_avg：每次等待所花的平均时间（重要）。
Innodb_row_lock_time_max：从系统启动到现在等待最长的一次所花的时间。
Innodb_row_lock_waits：系统启动后到现在总共等待的次数（重要）。

当等待=次数很高，而且每次等待时长也不小的时候，我们就需要分析系统中为什么会有如此多的等待，然后根据分析结果着手制定优化策略。

7.MySQL集群架构

7.1 MySQL高可用设计

7.1.1 高可用介绍

什么是高可用性
维基百科的解释是：
高可用性（英语：High availability，缩写为 HA），IT术语，指系统无中断地执行其功能的能力，代表
系统的可用性程度。是进行系统设计时的准则之一。高可用性系统与构成该系统的各个组件相比可以更
长时间运行。
计算公式: A表示可用性; MTBF表示平均故障间隔; MTTR表示平均恢复时间

在这里插入图片描述
高可用有一个标准,9越多代表越容错, 可用性越高.

假设系统一直能够提供服务，我们说系统的可用性是100%。如果系统每运行100个时间单位，会有1个时间单位无法提供服务，我们说系统的可用性是99%。很多公司的高可用目标是4个9，也就是99.99%

7.1.2 MySQL高可用介绍

我们在考虑MySQL数据库的高可用的架构时，主要要考虑如下几方面：

如果数据库发生了宕机或者意外中断等故障，能尽快恢复数据库的可用性，尽可能的减少停机时间，保证业务不会因为数据库的故障而中断。
用作备份、只读副本等功能的非主节点的数据应该和主节点的数据实时或者最终保持一致。当业务发生数据库切换时，切换前后的数据库内容应当一致，不会因为数据缺失或者数据不一致而影响业务。
客户端通过Master对数据库进行写操作，slave端进行读操作，并可进行备份。Master出现问题后，可以手动将应用切换到slave端。

7.2 MySQL高可用集群方案

7.2.1 主从复制+读写分离

此种架构，一般初创企业比较常用，也便于后面步步的扩展, 客户端通过Master对数据库进行写操作，slave端进行读操作，并可进行备份。Master出现问题后，可以手动将应用切换到slave端。
在这里插入图片描述
主从复制的优点：

实时灾备，用于故障切换（高可用）
读写分离，提供查询服务（读扩展）
数据备份，避免影响业务（高可用）

读写分离的优点:

主从只负责各自的写和读，极大程度的缓解锁争用
从库可配置myisam引擎，提升查询性能以及节约系统开销
从库同步主库，通过主库发送来的binlog恢复数据
读写分离适用与读远大于写的场景，如果只有一台服务器，当select很多时，update和delete会被这些select访问中的数据堵塞，等待select结束，并发性能不高。对于写和读比例相近的应用，应该部署双主相互复制

7.2.2 双主从复制

很多企业刚开始都是使用MySQL主从模式，一主多从、读写分离等。但是单主如果发生单点故障，从库切换成主库还需要作改动。因此，如果是双主或者多主，就会增加MySQL入口，提升了主库的可用性
在这里插入图片描述
双主模式是指两台服务器互为主从，任何一台服务器数据变更，都会通过复制应用到另外一方的数据库中。

问题: 使用双主双写还是双主单写？
建议大家使用双主单写，因为双主双写存在以下缺点：

ID冲突
在A主库写入，当A数据未同步到B主库时，对B主库写入，如果采用自动递增容易发生ID主键的冲突。
可以采用MySQL自身的自动增长步长来解决，例如A的主键为1,3,5,7…，B的主键为2,4,6,8… ，但是对数据库运维、扩展都不友好。
更新丢失
同一条记录在两个主库中进行更新，会发生前面覆盖后面的更新丢失。

7.2.3 MMM架构

MMM（Master-Master Replication Manager for MySQL）是一套用来管理和监控双主复制，支持双主故障切换的第三方软件。MMM 使用Perl语言开发，虽然是双主架构，但是业务上同一时间只允许一个节点进行写入操作。下图是基于MMM实现的双主高可用架构。
在这里插入图片描述

MMM故障处理机制
MMM 包含writer和reader两类角色，分别对应写节点和读节点。
- 当 writer节点出现故障，程序会自动移除该节点上的VIP
- 写操作切换到 Master2，并将Master2设置为writer
- 将所有Slave节点会指向Master2

除了管理双主节点，MMM 也会管理 Slave 节点，在出现宕机、复制延迟或复制错误，MMM 会移除该节点的 VIP，直到节点恢复正常。

7.2.4 MHA架构

MHA（Master High Availability）目前在MySQL高可用方面是一个相对成熟的解决方案，它由日本人开发，是一套优秀的作为MySQL高可用性环境下故障切换和主从提升的高可用软件。
在MySQL故障切换过程中，MHA能做到在0~30秒之内自动完成数据库的故障切换操作，并且在进行故障切换的过程中，MHA能在最大程度上保证数据的一致性，以达到真正意义上的高可用。
目前MHA主要支持一主多从的架构，要搭建MHA,要求一个复制集群中必须最少有三台数据库服务器，一主二从，即一台充当master，一台充当备用master，另外一台充当从库

MHA Manager管理多组主从复制
在这里插入图片描述
该软件由两部分组成：MHA Manager（管理节点）和MHA Node（数据节点）。

MHA Manager可以单独部署在一台独立的机器上管理多个master-slave集群，也可以部署在一台slave节点上。
MHA Node运行在每台MySQL服务器上，MHA Manager会定时探测集群中的master节点，当master出现故障时，它可以自动将最新数据的slave提升为新的master，然后将所有其他的slave重新指向新的master。整个故障转移过程对应用程序完全透明。

MHA故障处理机制：