MySQL分库分表和优化

最新推荐文章于 2023-04-16 20:09:18 发布

coolwei-

最新推荐文章于 2023-04-16 20:09:18 发布

阅读量1.2k

点赞数

分类专栏：学习文章标签： mysql 数据库 java

本文链接：https://blog.csdn.net/heywei_/article/details/121735195

版权

学习专栏收录该内容

26 篇文章 6 订阅

订阅专栏

第九阶段模块三

分库分表技术之MyCat

1.海量存储问题

1.1 背景描述

随着互联网的发展，数据的量级也是成指数的增长，从GB到TB到PB。对数据的各种操作也是愈加的困难，传统的关系性数据库已经无法满足快速查询与插入数据的需求。

**解决方案1：**使用NoSQL数据库，通过降低数据的安全性，减少对事务的支持，减少对复杂查询的支持，来获取性能上的提升。

**解决方案2：**NoSQL并不是万能的，就比如有些使用场景是绝对要有事务与安全指标的，所以还是要用关系型数据库，这时候就需要搭建MySQL数据库集群，为了提高查询性能，将一个数据库的数据分散到不同的数据库中存储，通过这种数据库拆分的方法来解决数据库的性能问题。

1.2 分库分表

1.2.1 什么是分库分表

简单来说，就是指通过某种特定的条件，将我们存放在同一个数据库中的数据分散存放到多个数据库（主机）上面，以达到分散单台设备负载的效果。

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-feGmrBGK-1638708748626)(E:\MarkDown\拉勾笔记\MySQL分库分表和优化–什么是分库分表)]

分库分表解决的问题

分库分表的目的是为了解决由于数据量过大而导致数据库性能降低的问题，将原来单体服务的数据库进行拆分。将数据大表拆分成若干数据表组成，使得单一数据库、单一数据表的数据量变小，从而达到提升数据库性能的目的。
什么情况下需要分库分表
- 单机存储容量遇到瓶颈
- 连接数，处理能力达到上限

注意：

分库分表之前，要根据项目的实际情况确定我们的数据量是不是够大，并发量是不是够大，来决定是否分库分表

数据量不够就不要分表，单表数据量超过1000万或100G的时候，速度就会变慢(官方测试)

1.2.2 分库分表的方式

1.2.2.1 垂直分库

数据库中不同的表对应着不同的业务，垂直切分是指按照业务的不同将表进行分类，分布到不同的数据库上面
- 将数据库部署在不同服务器上，从而达到多个服务器共同分摊压力的效果

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-pCpco1LT-1638708748627)(E:\MarkDown\拉勾笔记\MySQL分库分表和优化–分库分表的方式–垂直分库)]

1.2.2.2 垂直分表

将一个表按照字段分成多表，每个表存储其中一部分字段
- 对职位表进行垂直拆分，将职位基本信息放在一张表，将职位描述信息存放在另一张表

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-2M3lv2AT-1638708748629)(E:\MarkDown\拉勾笔记\MySQL分库分表和优化–分库分表的方式-垂直分表)]

垂直拆分带来的一些提升
- 解决业务层面的耦合，业务清晰
- 能对不同业务的数据进行分级管理、维护、监控、扩展等
- 高并发场景下，垂直分库一定程度的提高访问性能
垂直拆分没有彻底解决单表数据量过大的问题

1.2.2.3 水平分库

将单张表的数据切分到不同的数据库中，每个数据库具有相同的库与表，只是表中数据集合不同
简单讲就是根据表中的数据的逻辑关系，将同一个表中的数据按照某种条件拆分到多台数据库（主机）上面, 例如将订单表按照id是奇数还是偶数，分别存储在不同的库中

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-QPPZahk3-1638708748631)(E:\MarkDown\拉勾笔记\MySQL分库分表和优化–分库分表的方式–水平分库)]

1.2.2.4 水平分表

针对数据量巨大的单张表（比如订单表），按照规则把一张表的数据切分到多张表里面去。但是这些表还是在同一个库中，所以库级别的数据库操作还是有IO瓶颈

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-VVPY5ovv-1638708748632)(E:\MarkDown\拉勾笔记\MySQL分库分表和优化–分库分表的方式–水平分表)]

总结
- **垂直分表：**将一个表按照字段分成多表，每个表存储其中一部分字段
- **垂直分库：**根据表的业务不同，分别存放在不同的库中，这些库分别部署在不同的服务器
- 水平分库：把一张表的数据按照一定规则，分配到不同的数据库，每一个库只有这张表的部分数据
- 水平分表：把一张表的数据按照一定规则，分配到同一个数据库多张表中，每个表只有这个表的部分数据

1.3 如何实现分库分表

可以使用一个数据库中间件mycat来解决相关的问题。接下来了解一下什么是mycat。

2. MyCat

2.1 什么是MyCat

MyCat是目前最流行的基于java语言编写的数据库中间件，是一个实现了MySQL协议的服务器，前端用户可以把它看作是一个数据库代理，用MySQL客户端工具和命令行访问，而其后端可以用MySQL原生协议与多个MySQL服务器通信，也可以用JDBC协议与大多数主流数据库服务器通信，其核心功能是分库分表和读写分离，即将一个大表水平分割为N个小表，存储在后端MySQL服务器里或者其他数据库里。

MyCat对于我们Java程序员来说,就是一个近似等于MySQL的数据库服务器，你可以用连接MySQL的方式去连接 Mycat（除了端口不同，默认的Mycat端口是8066而非MySQL的3306，因此需要在连接字符串上增加端口信息）

我们可以像使用MySQL一样使用MyCat，Mycat可以管理若干MySQL数据库，同时实现数据的存储和操作

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-TkPAwRIM-1638708748634)(E:\MarkDown\拉勾笔记\MySQL分库分表和优化–MyCat–什么是MyCat)]

2.2 MyCat支持的数据库

Oracle
MySQL
mongoDB
SQlServer

2.3 MyCat 概念说明

2.3.1 MyCat的分片策略

什么是分片
- 通过某种特定的条件，将我们存放在同一个数据库中的数据分散存放到多个数据库（主机）上面，以达到分散单台设备负载的效果
MyCat支持两种切分模式
- 一种是按照不同的表（或者Schema）来切分到不同的数据库（主机）之上，这种切可以称之为数据的垂直（纵向）切分
- 另外一种则是根据表中的数据的逻辑关系，将同一个表中的数据按照某种条件拆分到多台数据库（主机）上面，这种切分称之为数据的水平（横向）切分

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-iZViu3Uo-1638708748635)(E:\MarkDown\拉勾笔记\MySQL分库分表和优化–MyCat–MyCat的分片策略01)]

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-SPyllUIm-1638708748639)(E:\MarkDown\拉勾笔记\MySQL分库分表和优化–MyCat–MyCat的分片策略02)]

逻辑库(schema)

对数据进行分片处理之后，从原有的一个库，被切分为多个分片数据库，所有的分片数据库集群构成了整个完整的数据库存储。Mycat在操作时，使用逻辑库来代表这个完整的数据库集群，便于对整个集群操作

逻辑表(table)

既然有逻辑库，那么就会有逻辑表，分布式数据库中，对应用来说，读写数据的表就是逻辑表

逻辑表，可以是数据切分后，分布在一个或多个分片库中，也可以不做数据切分，不分片，只有一个表构成

分片表：
是指那些原有的很大数据的表，需要切分到多个数据库的表，这样，每个分片都有一部分数据，所有分
片构成了完整的数据。 总而言之就是需要进行分片的表。

非分片表：
一个数据库中并不是所有的表都很大，某些表是可以不用进行切分的，非分片是相对分片表来说的，就
是那些不需要进行数据切分的表。

分片节点(dataNode)

数据切分后，一个大表被分到不同的分片数据库上面，每个表分片所在的数据库就是分片节点 (dataNode）
节点主机(dataHost)

数据切分后，每个分片节点不一定都会独占一台机器，同一机器上面可以有多个分片数据库，这样一个或多个分片节点所在的机器就是节点主机，为了规避单节点主机并发数限制，尽量将读写压力高的分片节点均衡的放在不同的节点主机dataHost
分片规则

前面讲了数据切分，一个大表被分成若干个分片表，就需要一定的规则rule，这样按照某种业务规则把数据分到某个分片的规则就是分片规则，数据切分选择合适的分片规则非常重要，将极大的避免后续数据处理的难度

2.4 MyCat的下载和安装

2.4.1 安装环境

jdk：要求jdk必须是1.7 及以上版本
MySQL：推荐mysql5.5 版本以上
MyCat：Mycat的官方网站：http://www.mycat.org.cn/

第一步：搭建3台虚拟机

server01
server02
server03

第二步：server01与server02安装MySQL数据库服务器，保证版本一致

server01 192.168.44.129
server02 192.168.44.130

第三步：创建数据库

192.168.44.129 创建 lagou1 数据库
192.168.44.130 创建 lagou2 和 lagou3 数据库

2.4.2 MyCat安装

注意：提前安装好JDK

第一步：下载MyCat

Mycat-server-1.6.7.5-release-20200410174409-linux.tar.gz

第二步：上传MyCat 到server03服务器，并解压

启动命令：./mycat start
停止命令：./mycat stop
重启命令：./mycat restart
查看状态：./mycat status

带控制台启动
```
./mycat console
```

2.5 MyCat核心配置

2.5.1 schema.xml配置

<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">

	<!-- 逻辑库 lagou -->
	<schema name="lagou" checkSQLschema="true" sqlMaxLimit="100">	
		<!-- 逻辑表 pay_order -->
		<table name="pay_order" primaryKey="id" dataNode="dn1,dn2,dn3"
			   rule="auto-sharding-long" autoIncrement="true">
		</table>
	</schema>

	<!-- 分片节点 -->
	<dataNode name="dn1" dataHost="localhost1" database="lagou1" />
	<dataNode name="dn2" dataHost="localhost2" database="lagou2" />
	<dataNode name="dn3" dataHost="localhost2" database="lagou3" />

	<!-- 节点主机 -->
	<dataHost name="localhost1" maxCon="1000" minCon="10" balance="0"
			  writeType="0" dbType="mysql" dbDriver="jdbc" switchType="1"  slaveThreshold="100">
		
		<heartbeat>select user()</heartbeat>
		
		<writeHost host="hostM1" url="192.168.44.129:3306" user="root" 
				   password="Weiwei@666">
		</writeHost>
	</dataHost>
	
	<dataHost name="localhost2" maxCon="1000" minCon="10" balance="0"
			  writeType="0" dbType="mysql" dbDriver="jdbc" switchType="1"  slaveThreshold="100">
		
		<heartbeat>select user()</heartbeat>
		
		<writeHost host="hostM2" url="192.168.44.130:3306" user="root" 
				   password="Weiwei@666">
		</writeHost>
	</dataHost>
</mycat:schema>

schema标签

Schema.xml作为MyCat中重要的配置文件之一，管理着MyCat的逻辑库、表、分片规则、DataNode以及DataSource。弄懂这些配置，是正确使用MyCat的前提。这里就一层层对该文件进行解析。

<!-- 逻辑库 -->
<schema name="lagou" checkSQLschema="true" sqlMaxLimit="100" ></schema>

属性名	值	数量限制	说明
dataNode	任意String	(0…1)	分片节点
sqlMaxLimit	Integer	（1）	查询返回的记录数限制limit
checkSQLschema	Boolean	（1）	执行SQL时,是否去掉表所属的库名

table标签

table标签定义了 Mycat 中的逻辑表，所有需要拆分的表都需要在这个标签中定义

<schema name="lagou" checkSQLschema="true" sqlMaxLimit="100" >
    <table name="pay_order" dataNode="dn1,dn2,dn3" rule="auto-sharding-long"
           primaryKey="id" autoIncrement="true" ></table>
</schema>

属性	值	数量限制	说明
name	String	(1)	逻辑表名
dataNode	String	(1…*)	分片节点
rule	String	(0…1)	分片规则
ruleRequired	Boolean	(0…1)	是否强制绑定分片规则
primaryKey	String	(1)	主键
type	String	(0…1)	逻辑表类型，全局表、普通表
autoIncrement	Boolean	(0…1)	自增长主键
subTables	String	(1)	分表
needAddLimit	Boolean	(0…1)	是否为查询SQL自动加limit限制

dataNode标签

dataNode标签定义了 MyCat 中的分片节点，也就是我们通常说所的数据分片

<dataNode name="dn1" dataHost="localhost1" database="lagou1" />
<dataNode name="dn2" dataHost="localhost2" database="lagou2" />
<dataNode name="dn3" dataHost="localhost2" database="lagou3" />

name：定义节点的名字，这个名字需要是唯一的，我们需要在table标签上应用这个名字，来建立表与分片对应的关系
dataHost：用于定义该分片属于哪个分片主机，属性值是引用dataHost标签上定义的name属性
database：用于定义该分片节点属于哪个具体的库。

dataHost标签

dataHost标签在Mycat逻辑库中也是作为最底层的标签存在，直接定义了具体的数据库实例、读写分离配置和心跳语句

<!-- 节点主机 -->
<dataHost name="localhost1" maxCon="1000" minCon="10" balance="0"
          writeType="0" dbType="mysql" dbDriver="native" switchType="1"
          slaveThreshold="100">
    <heartbeat>select user()</heartbeat>
    <writeHost host="hostM1" url="192.168.44.129:3306" user="root"
               password="Weiwei@666">
    </writeHost>
</dataHost>

<dataHost name="localhost2" maxCon="1000" minCon="10" balance="0"
          writeType="0" dbType="mysql" dbDriver="native" switchType="1"
          slaveThreshold="100">
    <heartbeat>select user()</heartbeat>
    <writeHost host="hostM2" url="192.168.44.130:3306" user="root"
               password="Weiwei@666">
    </writeHost>
</dataHost>

属性	值	数量限制	说明
name	String	(1)	节点主机名
maxCon	Integer	(1)	最大连接数
minCon	Integer	(1)	最小连接数
balance	Integer	(1)	读操作负载均衡类型
writeType	Integer	(1)	写操作负载均衡类型
dbType	String	(1)	数据库类型
dbDriver	String	(1)	数据库驱动
switchType	String	(1)	主从切换类型

heartbeat标签

heartbeat标签内指明用于和后端数据库进行心跳检查的语句。例如：MySQL可以使用select user()、 Oracle可以使用select 1 from dual等

<heartbeat>select user()</heartbeat>

writeHost和readHost标签

writeHost和readHost标签都指定后端数据库的相关配置给mycat，用于实例化后端连接池
writeHost指定写实例 , readHost指定读实例。在一个dataHost内可以定义多个writeHost和readHost

<dataHost name="localhost1" maxCon="1000" minCon="10" balance="0" writeType="0"
          dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100">
    <heartbeat>select user()</heartbeat>
    <writeHost host="hostM1" url="192.168.44.129:3306" user="root"
               password="Weiwei@666">
    </writeHost>
</dataHost>

属性	值	数量限制	说明
host	String	(1)	主机名
url	String	(1)	连接字符串
password	String	(1)	密码
user	String	(1)	用户名
weight	String	(1)	权重
usingDecrypt	String	(1)	是否对密码加密，默认0

2.5.2 server.xml配置

server.xml几乎保存了所有mycat需要的系统配置信息

2.5.2.1 user标签

这个标签主要用于定义登录mycat的用户和权限

<user name="root" defaultAccount="true">
    <property name="password">123456</property>
    <property name="schemas">lagou</property>
    <property name="defaultSchema">lagou</property>
</user>

2.5.2.2 连接MyCat

重启myCat，查看状态

./mycat start
./mycat status

连接mycat

mysql -uroot -p123456 -h127.0.0.1 -P8066

2.5.3 rule.xml配置

rule.xml里面就定义了我们对表进行拆分所涉及到的规则定义。我们可以灵活的对表使用不同的分片算法，或者对表使用相同的算法但具体的参数不同
这个文件里面主要有tableRule和function这两个标签。在具体使用过程中可以按照需求添加tableRule和function。

此配置文件可以不用修改，使用默认即可。

2.5.3.1 tableRule标签

<mycat:rule xmlns:mycat="http://io.mycat/">
    <tableRule name="sharding-by-intfile">
        <rule>
            <columns>sharding_id</columns>
            <algorithm>hash-int</algorithm>
        </rule>
    </tableRule>
</mycat:rule>

name：指定唯一的名字，用于标识不同的表规则

rule：指定对物理表中的哪一列进行拆分和使用什么路由算法

columns：指定要拆分的列名字

algorithm：使用function标签中的name属性，连接表规则和具体路由算法

2.5.2 function标签

<function name="hash-int" class="io.mycat.route.function.PartitionByFileMap">
    <property name="mapFile">partition-hash-int.txt</property>
</function>

name：指定算法的名字

class：制定路由算法具体的类名字

property：为具体算法需要用到的一些属性

2.5.4 常用的分片规则

Mycat常用分片配置示例：

自动分片

根据指定的列的范围进行分片。默认从0节点开始

<tableRule name="auto-sharding-long">
    <rule>
        <columns>id</columns>
        <algorithm>rang-long</algorithm>
    </rule>
</tableRule>

<function name="rang-long" class="io.mycat.route.function.AutoPartitionByLong">
    <property name="mapFile">autopartition-long.txt</property>
</function>

autopartition-long.txt文件：

0-200000=0
200000-400000=1

0-200000范围分配给节点0
200000-400000范围分配给节点1

枚举分片

把数据分类存储，这种方法适用于取值固定的场合，例如性别和省份

<!-- 枚举分片 -->
<tableRule name="sharding-by-intfile">
    <rule>
        <columns>sharding_id</columns>
        <algorithm>hash-int</algorithm>
    </rule>
</tableRule>

<function name="hash-int" class="io.mycat.route.function.PartitionByFileMap">
    <property name="mapFile">partition-hash-int.txt</property>
</function>

mapFile 中是自定义的分片策略文件，需要自己编写
partition-hash-int.txt文件内容如下：

beijing=0
wuhan=1
shanghai=2

取模分片

根据配置中的count值进行分片，将数据分成配置的count份，然后将数据均匀的分布在各个节点上

<tableRule name="mod-long">
    <rule>
        <columns>id</columns>
        <algorithm>mod-long</algorithm>
    </rule>
</tableRule>

<function name="mod-long" class="io.mycat.route.function.PartitionByMod">
    <!-- how many data nodes -->
    <property name="count">3</property>
</function>

2.6 MyCat分库分表

2.6.1 分片规则配置(水平分库)

**水平分库：**把一张表的数据按照一定规则，分配到不同的数据库，每一个库只有这张表的部分数据
在rule.xml配置，自动分片
- 每个datanode中保存一定数量的数据。根据id进行分片

<!-- schema 逻辑库 -->
<schema name="lagou" checkSQLschema="true" sqlMaxLimit="100" >
    <table name="pay_order" dataNode="dn1,dn2,dn3" rule="auto-sharding-long"
           primaryKey="id" autoIncrement="true" >
    </table>
</schema>

=========================================================================================

<!-- 自动分片 -->
<tableRule name="auto-sharding-long">
    <rule>
        <columns>id</columns>
        <algorithm>rang-long</algorithm>
    </rule>
</tableRule>

<function name="rang-long"
          class="io.mycat.route.function.AutoPartitionByLong">
    <property name="mapFile">autopartition-long.txt</property>
</function>

autopartition-long.txt

# range start-end ,data node index
# K=1000,M=10000.
0-1k=0
1k-2k=1
2k-3k=2

对分片规则进行测试id范围为：

Datanode1：1~1000

Datanode2：1000~2000

Datanode3：2000~3000

2.6.2 启动MyCat 进行测试

重启MyCat

停止命令：./mycat stop
重启命令：./mycat restart

在MyCat中创建逻辑表

DROP TABLE IF EXISTS pay_order;

CREATE TABLE pay_order (
    id BIGINT(20) PRIMARY KEY,
    user_id INT(11) NOT NULL ,
    product_name VARCHAR(128) ,
    amount DECIMAL(12,2)
);

MyCat中创建好表之后，我们的MySQL节点中也会对应的创建表

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-udZmCLbb-1638708748641)(E:\MarkDown\拉勾笔记\MySQL分库分表和优化–MyCat–MyCat分库分表)]

插入数据，观察数据被插入到哪张表中

INSERT INTO pay_order(id,user_id,product_name,amount) VALUES(2001,1,"面试宝典",15.8);

注意：解决MyCat乱码问题

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-cpqx6SaG-1638708748642)(E:\MarkDown\拉勾笔记\MySQL分库分表和优化–MyCat–MyCat分库分表–解决乱码问题)]

2.6.3 全局序列号

在实现分库分表的情况下，数据库自增主键已无法保证自增主键的全局唯一。为此，Mycat提供了全局 sequence，并且提供了包含本地配置和数据库配置等多种实现方式。

server.xml文件中

<system>
    <property name="sequnceHandlerType">0</property>
</system>

0 表示是表示使用本地文件方式。
1 表示的是根据数据库来生成
2 表示时间戳的方式 ID= 64 位二进制 (42(毫秒)+5(机器 ID)+5(业务编码)+12(重复累加)

2.6.3.1 本地文件

此方式Mycat将sequence配置到文件中，当使用到sequence中的配置后，Mycat会更新classpath中的 sequence_conf.properties文件中sequence当前的值

PAY_ORDER.HISIDS=
PAY_ORDER.MINID=101
PAY_ORDER.MAXID=10000000
PAY_ORDER.CURID=100

其中HISIDS表示使用过的历史分段(一般无特殊需要可不配置)，MINID表示最小ID值，MAXID表示最大ID值，CURID表示当前ID值

重启MyCat，插入一条数据，不用指定id

INSERT INTO pay_order(user_id,product_name,amount) VALUES(1,"xiao",12.8);

2.7 MyCat读写分离

2.7.1 什么是读写分离

在实际的生产环境中，数据的读写操作如果都在同一个数据库服务器中进行，当遇到大量的并发读或者写操作的时候，是没有办法满足实际需求的，数据库的吞吐量将面临巨大的瓶颈压力

主从复制

通过搭建主从架构，将数据库拆分为主库和从库，主库负责处理事务性的增删改操作，从库负责处理查询操作，能够有效的避免由数据更新导致的行锁，使得整个系统的查询性能得到极大的改善
读写分离

读写分离就是让主库处理事务性操作，从库处理select查询。数据库复制被用来把事务性查询导致的数据变更同步到从库，同时主库也可以select查询

读写分离的数据节点中的数据内容是一致

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-cPrYOQC0-1638708748645)(E:\MarkDown\拉勾笔记\MySQL分库分表和优化–MyCat–MyCat读写分离-- 什么是读写分离)]

2.7.2 MySQL主从复制(同步)

MyCat的读写分离是建立在MySQL主从复制基础之上实现的，所以必须先搭建MySQL的主从复制架构

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-DZ6HeJQJ-1638708748647)(E:\MarkDown\拉勾笔记\MySQL分库分表和优化–MyCat–MyCat读写分离-- MySQL主从复制(同步)]01)

主从复制的用途

实时灾备，用于故障切换
读写分离，提供查询服务
备份，避免影响业务

主从部署必要条件

主库开启binlog日志（设置log-bin参数）
主从server-id不同
从库服务器能连通主库

主从复制的原理

Mysql中有一种日志叫做bin日志（二进制日志）。这个日志会记录下所有修改了数据库的SQL语句（insert,update,delete,create/alter/drop table, grant 等等）
主从复制的原理其实就是把主服务器上的bin日志复制到从服务器上执行一遍，这样从服务器上的数据就和主服务器上的数据相同了

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-0Xj4LThX-1638708748648)(E:\MarkDown\拉勾笔记\MySQL分库分表和优化–MyCat–MyCat读写分离-- MySQL主从复制(同步)]–主从复制的原理)

主库db的更新事件(update、insert、delete)被写到binlog
主库创建一个binlog dump thread，把binlog的内容发送到从库
从库启动并发起连接，连接到主库
从库启动之后，创建一个I/O线程，读取主库传过来的binlog内容并写入到relay log
从库启动之后，创建一个SQL线程，从relay log里面读取内容，执行读取到的更新事件，将更新内容写入到slave的db

2.7.3 主从复制架构搭建

Mysql的主从复制至少是需要两个Mysql的服务，当然Mysql的服务是可以分布在不同的服务器上，也可以在一台服务器上启动多个服务

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-i3Ek7Ab4-1638708748650)(E:\MarkDown\拉勾笔记\MySQL分库分表和优化–MyCat–MyCat读写分离-- 主从复制架构搭建)]

第一步 master中创建数据库和表

-- 创建数据库
CREATE DATABASE test CHARACTER SET utf8;

-- 创建表
CREATE TABLE users (
    id INT(11) PRIMARY KEY AUTO_INCREMENT,
    NAME VARCHAR(20) DEFAULT NULL,
    age INT(11) DEFAULT NULL
);

-- 插入数据
INSERT INTO users VALUES(NULL,'user1',20);
INSERT INTO users VALUES(NULL,'user2',21);
INSERT INTO users VALUES(NULL,'user3',22);

第二步修改主数据库的配置文件my.cnf

vim /etc/my.cnf

插入下面的内容

lower_case_table_names=1 #忽略大小写

log-bin=mysql-bin
server-id=1
binlog-do-db=test
binlog_ignore_db=mysql

server-id=1 中的1可以任定义，只要是唯一的就行
log-bin=mysql-bin 表示启用binlog功能，并制定二进制日志的存储目录
binlog-do-db=test 是表示只备份test 数据库
binlog_ignore_db=mysql 表示忽略备份mysql
不加binlog-do-db和binlog_ignore_db，那就表示备份全部数据库

第三步重启MySQL

service mysqld restart

第四步在主数据库上，创建一个允许从数据库来访问的用户账号

用户：slave

密码：123456

主从复制使用REPLICATION SLAVE赋予权限

-- 创建账号
GRANT REPLICATION SLAVE ON *.* TO 'slave'@'192.168.44.130' IDENTIFIED BY 'Qwer@1234';

第五步停止主数据库的更新操作，并且生成主数据库的备份

-- 执行以下命令锁定数据库以防止写入数据。
FLUSH TABLES WITH READ LOCK;

导出数据库，恢复写操作

使用SQLYog导出，主数据库备份完毕，恢复写操作

unlock tables;

将刚才主数据库备份的test.sql导入到从数据库

导入后，主库和从库数据会追加相平，保持同步！此过程中，若主库存在业务，并发较高，在同步的时候要先锁表，让其不要有修改！等待主从数据追平，主从同步后在打开锁！

接着修改从数据库的 my.cnf

增加server-id参数，保证唯一

server-id=2

-- 重启
service mysqld restart

在从数据库设置相关信息

执行以下SQL

STOP SLAVE;

CHANGE MASTER TO MASTER_HOST='192.168.44.129',
MASTER_USER='slave',
MASTER_PASSWORD='Qwer@1234',
MASTER_PORT=3306,
MASTER_LOG_FILE='mysql-bin.000001',
MASTER_LOG_POS=0,
MASTER_CONNECT_RETRY=10;

修改auto.cnf中的UUID，保证唯一

-- 编辑auto.cnf
vim /var/lib/mysql/auto.cnf

-- 修改UUID的值
server-uuid=aa000674-10a4-11ec-9f82-000c2923abfa

-- 重启
service mysqld restart

在从服务器上，启动slave进程

start slave;

-- 查看状态
SHOW SLAVE STATUS;

-- 命令行下查看状态 执行
SHOW SLAVE STATUS \G;

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-e2nVFK8v-1638708748652)(E:\MarkDown\拉勾笔记\MySQL分库分表和优化–MyCat–MyCat读写分离-- 主从复制架构搭建02)]

注意：这两个参数的值，必须是Yes，否则就要进行错误的排查

现在可以在我们的主服务器做一些更新的操作，然后在从服务器查看是否已经更新

-- 在主库插入一条数据,观察从库是否同步
INSERT INTO users VALUES(NULL,'user4',23);

2.7.4 实现读写分离

数据库读写分离对于大型系统或者访问量很高的互联网应用来说，是必不可少的一个重要功能。对于MySQL来说，标准的读写分离是主从模式，一个写节点Master后面跟着多个读节点，读节点的数量取决于系统的压力，通常是1-3个读节点的配置

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-hNCxBabc-1638708748653)(E:\MarkDown\拉勾笔记\MySQL分库分表和优化–MyCat–MyCat读写分离-- 实现读写分离)]

在schema.xml文件中配置Mycat读写分离。使用前需要搭建MySQL主从架构，并实现主从复制， Mycat不负责数据同步问题。

server.xml

修改用户可以访问的逻辑表为 test

<user name="root" defaultAccount="true">
    <property name="password">123456</property>
    <property name="schemas">test</property>
    <property name="defaultSchema">test</property>
</user>

schema
- 逻辑库 name=“test”
- 逻辑表 name=“users”
- 读写分离不设置分片规则 ruleRequired=false
- 分片节点 dataNode=“dn4”

<schema name="test" checkSQLschema="true" sqlMaxLimit="100">
    <table name="users" dataNode="dn4" ruleRequired="false" primaryKey="id"
           autoIncrement="true" >
    </table>
</schema>

dataNode

<!-- 读写分离 -->
<dataNode name="dn4" dataHost="localhost3" database="test" />

dataHost

<!-- 读写分离 -->
<dataHost name="localhost3" maxCon="1000" minCon="10" balance="1" writeType="0"
          dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100">
    <heartbeat>select user()</heartbeat>
    <!-- 主 -->
    <writeHost host="M1" url="192.168.44.129:3306" user="root"
               password="Weiwei@666">
        <!-- 从 -->
        <readHost host="S1" url="192.168.44.130:3306" user="root"
                  password="Weiwei@666"
                  weight="1" />
    </writeHost>
</dataHost>

balance参数：

0 ：所有读操作都发送到当前可用的writeHost
1 ：所有读操作都随机发送到readHost和stand by writeHost
2 ：所有读操作都随机发送到writeHost和readHost
3 ：所有读操作都随机发送到writeHost对应的readHost上，但是writeHost不负担读压力

writeType参数：

0 ：所有写操作都发送到可用的writeHost
1 ：所有写操作都随机发送到readHost
2 ：所有写操作都随机发送到writeHost，readHost
重启MyCat

./mycat restart

执行查询和插入操作

插入一条数据，观察否两个表都同时新增了，如果同时新增，证明插入的是主库的表

INSERT INTO users(NAME,age) VALUES('测试abc',26);

在从库插入一条数据，然后进行查询，查询的是从库中的数据，证明查询操作在从库进行

SELECT * FROM users;

分库分表技术之ShardingJDBC

1. ShardingJDBC

1.1 分库分表方式回顾

分库分表的目的就是将我们的单库的数据控制在合理范围内，从而提高数据库的性能

垂直拆分( 按照结构分 )
- 垂直分表：将一张宽表(字段很多的表)，按照字段的访问频次进行拆分，就是按照表单结构进行拆
- 垂直分库：根据不同的业务，将表进行分类，拆分到不同的数据库。这些库可以部署在不同的服务器，分摊访问压力
水平拆分( 按照数据行分 )
- 水平分库：将一张表的数据 ( 按照数据行) 分到多个不同的数据库。每个库的表结构相同。每个库都只有这张表的部分数据，当单表的数据量过大，如果继续使用水平分库，那么数据库的实例就会不断增加，不利于系统的运维。这时候就要采用水平分表
- 水平分表：将一张表的数据 ( 按照数据行) ，分配到同一个数据库的多张表中，每个表都只有一部分数据

什么时候用分库分表

在系统设计阶段，就要完成垂直分库和垂直分表。在数据量不断上升，数据库性能无法满足需求的时候，首先要考虑的是缓存、读写分离、索引技术等方案。如果数据量不断增加，并且持续增长再考虑水平分库水平分表

1.2 分库分表带来的问题

关系型数据库在单机单库的情况下，比较容易出现性能瓶颈问题，分库分表可以有效的解决这方面的问题，但是同时也会产生一些比较棘手的问题

1.2.1 事务一致性问题

当我们需要更新的内容同时分布在不同的库时，不可避免的会产生跨库的事务问题。原来在一个数据库操作，本地事务就可以进行控制，分库之后一个请求可能要访问多个数据库，如何保证事务的一致性，目前还没有简单的解决方案

1.2.2 跨节点关联的问题

在分库之后，原来在一个库中的一些表，被分散到多个库，并且这些数据库可能还不在一台服务器，无法关联查询

解决这种关联查询，需要我们在代码层面进行控制，将关联查询拆开执行，然后再将获取到的结果进行拼装

1.2.3 分页排序查询的问题

分库并行查询时，如果用到了分页每个库返回的结果集本身是无序的，只有将多个库中的数据先查出来，然后再根据排序字段在内存中进行排序，如果查询结果过大也是十分消耗资源的

1.2.4 主键避重问题

在分库分表的环境中，表中的数据存储在不同的数据库，主键自增无法保证ID不重复，需要单独设计全局主键

1.2.5 公共表的问题

不同的数据库，都需要从公共表中获取数据。可以在每一个库都创建这个公共表，所有对公共表的更新操作，都同时发送到所有分库执行。ShardingJDBC可以帮助我们解决这个问题

1.3 ShardingJDBC简介

1.3.1 什么是ShardingJDBC

ShardingSphere是一套开源的分布式数据库中间件解决方案组成的生态圈，它由Sharding-JDBC、 Sharding-Proxy和Sharding-Sidecar（计划中）这3款相互独立的产品组成，我们只关注Sharding-JDBC即可

官方地址：https://shardingsphere.apache.org/document/current/cn/overview/

Sharding-JDBC定位为轻量级Java框架，在Java的JDBC层提供的额外服务。它使用客户端直连数据库，以jar包形式提供服务，无需额外部署和依赖，可理解为增强版的JDBC驱动，完全兼容JDBC和各种ORM框架的使用

适用于任何基于Java的ORM框架，如：JPA, Hibernate, Mybatis, Spring JDBC Template或直接使用JDBC
基于任何第三方的数据库连接池，如：DBCP, C3P0, Druid等
支持任意实现JDBC规范的数据库。目前支持MySQL，Oracle，SQLServer和PostgreSQL

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-MdGEOBs4-1638708748655)(E:\MarkDown\拉勾笔记\MySQL分库分表和优化–分库分表技术之ShardingJDBC–ShardingJDBC简介–什么是ShardingJDBC)]

上图展示了Sharding-Jdbc的工作方式，使用Sharding-Jdbc前需要人工对数据库进行分库分表，在应用程序中加入Sharding-Jdbc的Jar包，应用程序通过Sharding-Jdbc操作分库分表后的数据库和数据表，由于Sharding-Jdbc是对Jdbc驱动的增强，使用Sharding-Jdbc就像使用Jdbc驱动一样，在应用程序中是无需指定具体要操作的分库和分表的

1.3.2 Sharding-JDBC主要功能

数据分片
读写分离

通过Sharding-JDBC，应用可以透明的使用jdbc访问已经分库分表、读写分离的多个数据源，而不用关心数据源的数量以及数据如何分布

1.3.3 Sharding-JDBC与MyCat的区别

mycat是一个中间件的第三方应用，sharding-jdbc是一个jar包
使用mycat时不需要修改代码，而使用sharding-jdbc时需要修改代码
Mycat 是基于 Proxy，它复写了 MySQL 协议，将 Mycat Server 伪装成一个 MySQL 数据库，而 Sharding-JDBC 是基于 JDBC 的扩展，是以 jar 包的形式提供轻量级服务的

Mycat(proxy中间件层)

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-nsL1vcnz-1638708748657)(E:\MarkDown\拉勾笔记\MySQL分库分表和优化–分库分表技术之ShardingJDBC–ShardingJDBC简介–Sharding-JDBC主要功能–Mycat(proxy中间件层)])

Sharding-jdbc(应用层)：

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-X3aBcDRy-1638708748658)(E:\MarkDown\拉勾笔记\MySQL分库分表和优化–分库分表技术之ShardingJDBC–ShardingJDBC简介–Sharding-JDBC主要功能–Sharding-jdbc(应用层)])

1.4 Sharding-JDBC入门使用

1.4.1 搭建基础环境

需求说明

创建数据库lg_order，模拟将订单表进行水平拆分，创建两张表pay_order_1 与 pay_order_2，这两张表是订单表拆分后的表，我们通过Sharding-Jdbc向订单表插入数据，按照一定的分片规则，主键为偶数的落入pay_order_1表，为奇数的落入pay_order_2表，再通过Sharding-Jdbc进行查询
创建数据库

CREATE DATABASE lg_order CHARACTER SET 'utf8';

DROP TABLE IF EXISTS pay_order_1;
CREATE TABLE pay_order_1 (
    order_id BIGINT(20) PRIMARY KEY AUTO_INCREMENT ,
    user_id INT(11) ,
    product_name VARCHAR(128),
    COUNT INT(11)
);

DROP TABLE IF EXISTS pay_order_2;
CREATE TABLE pay_order_2 (
    order_id BIGINT(20) PRIMARY KEY AUTO_INCREMENT ,
    user_id INT(11) ,
    product_name VARCHAR(128),
    COUNT INT(11)
);

创建SpringBoot项目引入maven依赖

sharding-jdbc以jar包形式提供服务，所以要先引入maven依赖

<dependency>
    <groupId>org.apache.shardingsphere</groupId>
    <artifactId>sharding-jdbc-spring-boot-starter</artifactId>
    <version>4.0.0-RC1</version>
</dependency>

1.4.2 分片规则配置(水平分表)

使用sharding-jdbc对数据库中水平拆分的表进行操作，通过sharding-jdbc对分库分表的规则进行配置，配置内容包括：数据源、主键生成策略、分片策略等。

application.properties

基础配置

spring.application.name = sharding-jdbc-simple
server.servlet.context-path = /sharding-jdbc
spring.http.encoding.enabled = true
spring.http.encoding.charset = UTF-8
spring.http.encoding.force = true

spring.main.allow-bean-definition-overriding = true
mybatis.configuration.map-underscore-to-camel-case = true

数据源

# 定义数据源
spring.shardingsphere.datasource.names = db1

spring.shardingsphere.datasource.db1.type = com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.db1.driver-class-name = com.mysql.jdbc.Driver
spring.shardingsphere.datasource.db1.url = jdbc:mysql://localhost:3306/lg_order?characterEncoding=UTF-8&useSSL=false
spring.shardingsphere.datasource.db1.username = root
spring.shardingsphere.datasource.db1.password = 123456

配置数据节点
```
#配置数据节点,指定节点的信息
spring.shardingsphere.sharding.tables.pay_order.actual-data-nodes = db1.pay_order_$->{1..2}
```
表达式db1.pay_order_$->{1…2}

$ 会被大括号中的 {1…2} 所替换

会有两种选择：db1.pay_order_1和db1.pay_order_2

配置主键生成策略

#指定pay_order表 (逻辑表)的主键生成策略为 SNOWFLAKE
spring.shardingsphere.sharding.tables.pay_order.key-generator.column=order_id
spring.shardingsphere.sharding.tables.pay_order.key-generator.type=SNOWFLAKE

使用shardingJDBC提供的主键生成策略，全局主键

为避免主键重复，生成主键采用SNOWFLAKE分布式ID生成算法

配置分片算法

#指定pay_order表的分片策略，分片策略包括分片键和分片算法
spring.shardingsphere.sharding.tables.pay_order.table-strategy.inline.sharding-column = order_id
spring.shardingsphere.sharding.tables.pay_order.table-strategy.inline.algorithm-expression = pay_order_$->{order_id % 2 + 1}

分表策略表达式: pay_order_$-> {order_id % 2 + 1}

{order_id % 2 + 1}结果是偶数操作pay_order_1表

{order_id % 2 + 1}结果是奇数操作pay_order_2表

打开SQL日志

# 打开sql输出日志
spring.shardingsphere.props.sql.show = true

步骤总结
1. 定义数据源
2. 指定pay_order 表的数据分布情况，分布在 pay_order_1 和 pay_order_2
3. 指定pay_order 表的主键生成策略为SNOWFLAKE，是一种分布式自增算法，保证id全局唯一
4. 定义pay_order分片策略，order_id为偶数的数据下沉到pay_order_1，为奇数下沉到在 pay_order_2

1.4.3 编写程序

新增订单

@Mapper
@Component
public interface PayOrderDao {

    /**
     * 新增订单
     * @param user_id
     * @param product_name
     * @param count
     * @return
     */
    @Insert("INSERT INTO pay_order(user_id,product_name,COUNT) VALUES(#{user_id},#{product_name},#{count})")
    int insertPayOrder(@Param("user_id") int user_id,@Param("product_name") String product_name,@Param("count") int count);
}

测试

@RunWith(SpringRunner.class)
@SpringBootTest(classes = RunBoot.class)
public class PayOrderDaoTest {

    @Autowired
    PayOrderDao orderDao;

    @Test
    public void testInsertPayOrder(){

        for (int i = 0; i < 10; i++) {
            orderDao.insertPayOrder(101+i,"华为手机",10);
        }
    }
}

根据Id查询订单

@Mapper
@Component
public interface PayOrderDao {

    /**
     * 根据ID 查询订单
     * @param orderIds
     * @return
     */
    @Select({"<script>"+
            "select * from pay_order p where p.order_id in " +
                    "<foreach collection='orderIds' item='id' open='(' separator=',' close=')'>#{id}</foreach>"
            + "</script>"
    })
    List<Map> findOrderByIds(@Param("orderIds") List<Long> orderIds);
}

测试

@RunWith(SpringRunner.class)
@SpringBootTest(classes = RunBoot.class)
public class PayOrderDaoTest {

    @Autowired
    PayOrderDao orderDao;

    @Test
    public void testFindOrderByIds(){

        List<Long> ids = new ArrayList<>();
        // order_1
        ids.add(673489657048072192L);
        ids.add(673489657765298176L);

        // order_2
        ids.add(673489656033050625L);
        ids.add(673489657308119041L);

        List<Map> list = orderDao.findOrderByIds(ids);
        System.out.println(list);
    }
}

1.4.4 ShardingJDBC执行流程

当ShardingJDBC接收到发送的SQL之后，会执行下面的步骤，最终返回执行结果

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-akzX2vDB-1638708748660)(E:\MarkDown\拉勾笔记\MySQL分库分表和优化–分库分表技术之ShardingJDBC–Sharding-JDBC入门使用–ShardingJDBC执行流程)]

SQL解析：编写SQL查询的是逻辑表，执行时ShardingJDBC要解析SQL，解析目的是为了找到需要改写的位置
SQL路由：SQL的路由是指将对逻辑表的操作，映射到对应的数据节点的过程。ShardingJDBC会获取分片键判断是否正确，正确就执行分片策略(算法) 来找到真实的表
SQL改写：程序员面向的是逻辑表编写SQL，并不能直接在真实的数据库中执行，SQL改写用于将逻辑SQL改为在真实的数据库中可以正确执行的SQL
SQL执行：通过配置规则pay_order_$->{order_id % 2 + 1}，可以知道当order_id为偶数时，应该向 pay_order_1表中插入数据，为奇数时向pay_order_2表插入数据
将所有真正执行sql的结果进行汇总合并，然后返回。

1.5 Sharding-JDBC分库分表

1.5.1 水平分表

把一张表的数据按照一定规则，分配到同一个数据库的多张表中，每个表只有这个表的部分数据。在Sharding-JDBC入门使用中，我们已经完成了水平分表的操作

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-WZvZyXP3-1638708748661)(E:\MarkDown\拉勾笔记\MySQL分库分表和优化–分库分表技术之ShardingJDBC–Sharding-JDBC分库分表)]

1.5.2 水平分库

水平分库是把同一个表的数据按一定规则拆到不同的数据库中，每个库可以放在不同的服务器上。接下来看一下如何使用Sharding-JDBC实现水平分库

将原来的lg_order数据库，拆分为lg_order_1和lg_order_2

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-DkopUHBv-1638708748663)(E:\MarkDown\拉勾笔记\MySQL分库分表和优化–分库分表技术之ShardingJDBC–Sharding-JDBC分库分表–水平分库)]

分片规则配置

现在是两个数据库，所以要配置两份数据源信息

# 定义多个数据源
spring.shardingsphere.datasource.names = db1,db2

spring.shardingsphere.datasource.db1.type = com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.db1.driver-class-name = com.mysql.jdbc.Driver
spring.shardingsphere.datasource.db1.url = jdbc:mysql://localhost:3306/lg_order_1?characterEncoding=UTF-8&useSSL=false
spring.shardingsphere.datasource.db1.username = root
spring.shardingsphere.datasource.db1.password = 123456

spring.shardingsphere.datasource.db2.type = com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.db2.driver-class-name =com.mysql.jdbc.Driver
spring.shardingsphere.datasource.db2.url = jdbc:mysql://localhost:3306/lg_order_2?characterEncoding=UTF-8&useSSL=false
spring.shardingsphere.datasource.db2.username = root
spring.shardingsphere.datasource.db2.password = 123456

通过配置对数据库的分片策略，来指定数据库进行操作

# 分库策略，以user_id为分片键，分片策略为user_id % 2 + 1，user_id为偶数操作db1数据源，否则操作db2。
spring.shardingsphere.sharding.tables.pay_order.database-strategy.inline.sharding-column = user_id

spring.shardingsphere.sharding.tables.pay_order.database-strategy.inline.algorithm-expression = db$->{user_id % 2 + 1}

分库分表的策略

分库策略，目的是将一个逻辑表，映射到多个数据源

# 分库找的是数据库 db$->{user_id % 2 + 1}
spring.shardingsphere.sharding.tables.逻辑表名称.database-strategy.分片策略.分片策略属性名 = 分片策略表达式

分表策略，如何将一个逻辑表，映射为多个实际表

#分表 找的是具体的表 pay_order_$->{order_id % 2 + 1}
spring.shardingsphere.sharding.tables.逻辑表名称.table-strategy.分片策略.algorithm-expression = 分片策略表达式

Sharding-JDBC支持以下几种分片策略：

具体信息请查阅官方文档：https://shardingsphere.apache.org
- standard：标准分片策略
- complex：符合分片策略
- inline：行表达式分片策略，使用Groovy的表达式
- hint：Hint分片策略，对应HintShardingStrategy
- none：不分片策略，对应NoneShardingStrategy。不分片的策略
插入测试
```
@Test
public void testInsertPayOrder(){

    for (int i = 0; i < 10; i++) {
        orderDao.insertPayOrder(100+i,"华为手机",10);
    }
}
```
首先会根据分库策略找到对应的数据库db$->{user_id % 2 + 1}

然后再根据分表策略找到要插入数据的表pay_order_$->{order_id % 2 + 1}

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-qdaePLbD-1638708748664)(E:\MarkDown\拉勾笔记\MySQL分库分表和优化–分库分表技术之ShardingJDBC–Sharding-JDBC分库分表–水平分库02)]
查询测试
```
@Test
public void testFindOrderByIds(){

    List<Long> ids = new ArrayList<>();

    // la_order_1 数据库的 pay_order_2
    ids.add(673609750264414209L);
    ids.add(673609752130879489L);

    ids.add(673609568617496576L);// la_order_2 --> pay_order_1
    ids.add(673609565949919233L);// la_order_2 --> pay_order_2

    List<Map> list = orderDao.findOrderByIds(ids);
    System.out.println(list);
}
```
通过日志发现，sharding-jdbc将sql路由到了db1

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-XvHBboxE-1638708748665)(E:\MarkDown\拉勾笔记\MySQL分库分表和优化–分库分表技术之ShardingJDBC–Sharding-JDBC分库分表–水平分库03)]

原因在配置上有问题，数据库只指定了db1

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-3xzWwbeI-1638708748666)(E:\MarkDown\拉勾笔记\MySQL分库分表和优化–分库分表技术之ShardingJDBC–Sharding-JDBC分库分表–水平分库04)]

修改数据节点配置

#数据节点: db1.pay_order_1 , db1.pay_order_2, db2.pay_order_1,db2.pay_order_2
spring.shardingsphere.sharding.tables.pay_order.actual-data-nodes = db$->{1..2}.pay_order_$->{1..2}

1.5.3 垂直分库

垂直分库是指按照业务将表进行分类，分布到不同的数据库上面，每个库可以放在不同的服务器上，它的核心理念是专库专用

在使用微服务架构时，业务切割得足够独立，数据也会按照业务切分，保证业务数据隔离，大大提升了数据库的吞吐能力

创建数据库

CREATE DATABASE lg_user CHARACTER SET 'utf8';

在lg_user 数据库中 users 创建表

DROP TABLE IF EXISTS users;
CREATE TABLE users (
    id BIGINT(20) PRIMARY KEY,
    username VARCHAR(20) ,
    phone VARCHAR(11),
    STATUS VARCHAR(11)
);

规则配置

配置数据源信息

spring.shardingsphere.datasource.names = db1,db2,db3

spring.shardingsphere.datasource.db3.type = com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.db3.driver-class-name = com.mysql.jdbc.Driver
spring.shardingsphere.datasource.db3.url = jdbc:mysql://localhost:3306/lg_user?characterEncoding=UTF-8&useSSL=false
spring.shardingsphere.datasource.db3.username = root
spring.shardingsphere.datasource.db3.password = 123456

配置数据节点

spring.shardingsphere.sharding.tables.users.actual-data-nodes = db$->{3}.users

spring.shardingsphere.sharding.tables.users.table-strategy.inline.sharding-column = id
spring.shardingsphere.sharding.tables.users.table-strategy.inline.algorithm-expression = users

测试插入与查询

UserDao

@Mapper
@Component
public interface UsersDao {

    /**
     * 新增用户
     * @param id
     * @param username
     * @param phone
     * @param status
     * @return
     */
    @Insert("insert into users(id,username,phone,status) values(#{id},#{username},#{phone},#{status})")
    int insertUser(@Param("id") Long id, @Param("username") String username, @Param("phone") String phone,
                   @Param("status") String status);

    /**
     * 查询用户
     * @param userIds
     * @return
     */
    @Select({"<script>"+
            "select * from users u where u.id in " +
            "<foreach collection='userIds' item='id' open='(' separator=',' close=')'>#{id}</foreach>"
            + "</script>"
    })
    List<Map> findOrderByIds(@Param("userIds") List<Long> userIds);
}

UserDaoTest

@RunWith(SpringRunner.class)
@SpringBootTest(classes = RunBoot.class)
public class UsersDaoTest {

    @Autowired
    private UsersDao usersDao;

    @Test
    public void testInsert(){

        for (int i = 0; i < 10; i++) {
            Long id = i+100L;
            usersDao.insertUser(id,"药水"+i,"13555556666","1");
        }

    }

    @Test
    public void testFindUser(){

        List<Long> ids = new ArrayList<>();
        ids.add(101L);
        ids.add(102L);

        List<Map> list = usersDao.findUserByIds(ids);
        System.out.println(list);
    }
}

1.6 Sharding-JDBC操作公共表

1.6.1 什么是公共表

公共表属于系统中数据量较小，变动少，而且属于高频联合查询的依赖表。参数表、数据字典表等属于此类型。

可以将这类表在每个数据库都保存一份，所有更新操作都同时发送到所有分库执行。接下来看一下如何使用Sharding-JDBC实现公共表的数据维护。

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-Ndn1Msr0-1638708748668)(E:\MarkDown\拉勾笔记\MySQL分库分表和优化–分库分表技术之ShardingJDBC–Sharding-JDBC 操作公共表–什么是公共表)]

1.6.2 公共表配置与测试

创建数据库

分别在lg_order_1，lg_order_2，lg_user都创建district表

-- 区域表
CREATE TABLE district (
    id BIGINT(20) PRIMARY KEY COMMENT '区域ID',
    district_name VARCHAR(100) COMMENT '区域名称',
    LEVEL INT COMMENT '等级'
);

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-etNYaRN5-1638708748669)(E:\MarkDown\拉勾笔记\MySQL分库分表和优化–分库分表技术之ShardingJDBC–Sharding-JDBC 操作公共表–公共表配置与测试)]

在Sharding-JDBC的配置文件中指定公共表

# 指定district为公共表
spring.shardingsphere.sharding.broadcast-tables=district
# 主键生成策略
spring.shardingsphere.sharding.tables.district.key-generator.column=id
spring.shardingsphere.sharding.tables.district.key-generator.type=SNOWFLAKE

编写代码，操作公共表

DistrictDao

@Mapper
@Component
public interface DistrictDao {

    /**
     * 插入操作
     * @param district_name
     * @param level
     * @return
     */
    @Insert("insert into district(district_name,level) values(#{district_name},#{level})")
    int insertDist(@Param("district_name") String district_name, @Param("level") int level);

    /**
     * 删除数据
     * @param id
     * @return
     */
    @Delete("delete from district where id = #{id}")
    int deleteDict(@Param("id") Long id);
}

DistrictDaoTest

@RunWith(SpringRunner.class)
@SpringBootTest(classes = RunBoot.class)
public class DistrictDaoTest {

    @Autowired
    private DistrictDao districtDao;

    @Test
    public void testInsert(){
        districtDao.insertDist("昌平区",2);
        districtDao.insertDist("朝阳区",2);
    }

    @Test
    public void testDelete(){
        districtDao.deleteDict(673618707355795457L);
        districtDao.deleteDict(673618708437925888L);
    }
}

1.7 Sharding-JDBC读写分离

Sharding-JDBC读写分离则是根据SQL语义的分析，将读操作和写操作分别路由至主库与从库。它提供透明化读写分离，让使用方尽量像使用一个数据库一样使用主从数据库集群。

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-iaNX4TqK-1638708748670)(E:\MarkDown\拉勾笔记\MySQL分库分表和优化–分库分表技术之ShardingJDBC–Sharding-JDBC读写分离)]

1.7.1 MySQL主从同步

为了实现Sharding-JDBC的读写分离，首先，要进行mysql的主从同步配置。

我们直接使用MyCat讲解中，在虚拟机上搭建的主从数据库

在主服务器中的test数据库创建商品表

CREATE TABLE products (
    pid BIGINT(32) PRIMARY KEY ,
    pname VARCHAR(50) DEFAULT NULL,
    price INT(11) DEFAULT NULL,
    flag VARCHAR(2) DEFAULT NULL
);

主库新建表之后，从库会根据binlog日志，同步创建

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-K5IMUR5I-1638708748672)(E:\MarkDown\拉勾笔记\MySQL分库分表和优化–分库分表技术之ShardingJDBC–Sharding-JDBC读写分离–MySQL主从同步)]

1.7.2 sharding-jdbc实现读写分离

1.配置数据源

# 定义多个数据源
spring.shardingsphere.datasource.names = db1,db2,db3,m1,s1

spring.shardingsphere.datasource.m1.type = com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.m1.driver-class-name = com.mysql.jdbc.Driver
spring.shardingsphere.datasource.m1.url = jdbc:mysql://192.168.44.129:3306/test?
characterEncoding=UTF-8&useSSL=false
spring.shardingsphere.datasource.m1.username = root
spring.shardingsphere.datasource.m1.password = Weiwei@666

spring.shardingsphere.datasource.s1.type = com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.s1.driver-class-name = com.mysql.jdbc.Driver
spring.shardingsphere.datasource.s1.url = jdbc:mysql://192.168.44.130:3306/test?
characterEncoding=UTF-8&useSSL=false
spring.shardingsphere.datasource.s1.username = root
spring.shardingsphere.datasource.s1.password = Weiwei@666

2.配置主库与从库的相关信息

ms1 包含了 m1 和 s1

spring.shardingsphere.sharding.master-slave-rules.ms1.master-data-source-name=m1
spring.shardingsphere.sharding.master-slave-rules.ms1.slave-data-source-names=s1

3.配置数据节点

#配置数据节点
spring.shardingsphere.sharding.tables.products.actual-data-nodes = ms1.products

4.编写测试代码

ProductsDao

@Mapper
@Component
public interface ProductsDao {

    /**
     * 读写分离 插入
     * @param pid
     * @param pname
     * @param price
     * @param flag
     * @return
     */
    @Insert("insert into products(pid,pname,price,flag) values(#{pid},#{pname},#{price},#{flag})")
    int insertProduct(@Param("pid") Long pid, @Param("pname") String pname, @Param("price") int price,
                      @Param("flag") String flag);

    /**
     *  读写分离 查询
     * @return
     */
    @Select("select * from products")
    List<Map> findAll();
}

测试

@RunWith(SpringRunner.class)
@SpringBootTest(classes = RunBoot.class)
public class ProductsDaoTest {

    @Autowired
    private ProductsDao productsDao;

    /**
     * 测试插入
     */
    @Test
    public void testInsert(){
        for (int i = 0; i < 5; i++) {
            productsDao.insertProduct(100L+i,"小米手机"+i,1888,"1");
        }
    }

    /**
     * 测试查询
     */
    @Test
    public void testSelect(){
        List<Map> all = productsDao.findAll();
        System.out.println(all);
    }
}

MySQL高级

1.MySQL架构组成

1.1 高级MySQL介绍

课程主要内容

1.MySQL架构组成
2.MySQL的备份与恢复
3.MySQL查询和慢查询日志分析
4.MySQL存储引擎
5.MySQL索引优化
6.MySQL锁机制
7.MySQL高可用设计
8.MySQL集群

什么是DBA ?

数据库管理员，英文是Database Administrator，简称DBA
百度百科介绍

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-uasmtiiM-1638708748673)(E:\MarkDown\拉勾笔记\MySQL分库分表和优化–MySQL高级–MySQL架构组成-- 高级MySQL介绍)]

一个高级DBA的职责：

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

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

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

我们作为Java开发工程师，关注的应该是跟开发相关的数据库知识。了解这些高级的知识，目的是让我们编写出更加高效的应用程序

专业的数据库维护、服务器优化、性能参数调优等等数据库相关的运维工作还是要交给DBA去做的

1.2 MySQL逻辑架构

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

1.2.1 MySQL架构体系介绍

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

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

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-gCocEHLz-1638708748674)(E:\MarkDown\拉勾笔记\MySQL分库分表和优化–MySQL高级–MySQL架构组成–MySQL逻辑架构–MySQL架构体系介绍)]

1.连接层

最上面是一些客户端和连接服务, 不是MySQL特有的，所有基于网络的C/S的网络应用程序都应该包括
连接处理、认证、安全管理等。

2.服务层

中间层是MySQL的核心，包括查询解析、分析、优化和缓存等。同时它还提供跨存储引擎的功能，包括
存储过程、触发器和视图等。

3.引擎层

存储引擎层，它负责存取数据。服务器通过API可以和各种存储引擎进行交互。不同的存储引擎具有不同
的功能,我们可以根据实际需求选择使用对应的存储引擎

4.存储层

数据存储层,主要是将数据存储在运行于裸设备的文件系统之上,并完成与存储引擎的交互

1.2.2 SQL查询流程

我们用一条SQL SELECT语句的执行轨迹来说明客户端与MySQL的交互过程，如下图所示

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-kR12ZNLM-1638708748675)(E:\MarkDown\拉勾笔记\MySQL分库分表和优化–MySQL高级–MySQL架构组成–MySQL逻辑架构–SQL查询流程)]

1. 通过客户端/服务器通信协议与MySQL建立连接

2. 查询缓存，这是MySQL的一个可优化查询的地方，如果开启了Query Cache且在查询缓存过程中查
询到完全相同的SQL语句，则将查询结果直接返回给客户端；如果没有开启Query Cache或者没有查询到
完全相同的SQL语句则会由解析器进行语法语义解析，并生成解析树。

3. 预处理器生成新的解析树。

4. 查询优化器生成执行计划。

5. 查询执行引擎执行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慢查询日志调优查询时间超过指定值

-- 查看错误日志文件路径
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 数据库备份的应用场景

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

数据丢失应用场景
- 系统硬件或软件故障
- 自然灾害，比如水灾、火灾、地震等
- 黑客攻击，非法访问者故意破坏
- 误操作，人为的误操作占比最大

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-M6HzZgzB-1638708748677)(E:\MarkDown\拉勾笔记\MySQL分库分表和优化–MySQL高级–MySQL的备份与恢复–数据库备份的应用场景)]

非数据丢失应用场景：
- 开发测试环境数据库搭建
- 数据库或者数据迁移

2.2 数据备份的类型

2.2.1 按照业务方式分

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

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-fwOEvQEJ-1638708748678)(E:\MarkDown\拉勾笔记\MySQL分库分表和优化–MySQL高级–MySQL的备份与恢复–数据备份的类型–按照业务方式分)]

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

2.2.2 备份的组合方式

完全备份与差异备份

以每周数据备份为例，可以在星期一进行完全备份，在星期二至星期六进行差异备份。如果在星期六数据被破坏了，则只需要还原星期一完全的备份和星期五的差异备份

这种策略备份数据需要较多的时间，但还原数据使用较少的时间

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-SH8OztdR-1638708748680)(E:\MarkDown\拉勾笔记\MySQL分库分表和优化–MySQL高级–MySQL的备份与恢复–数据备份的类型–备份的组合方式–完全备份与差异备份)]
完全备份与增量备份

以每周数据备份为例，在星期一进行完全备份，在星期二至星期六进行增量备份。如果在星期六数据被破坏了，则需要还原星期一正常的备份和从星期二至星期五的所有增量备份

这种策略备份数据需要较少的时间，但还原数据使用较长的时间

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-vLmvvAdq-1638708748682)(E:\MarkDown\拉勾笔记\MySQL分库分表和优化–MySQL高级–MySQL的备份与恢复–数据备份的类型–备份的组合方式–完全备份与增量备份)]

2.3 MySQL冷备份和热备份

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

2.3.1 冷备份

冷备份指的是当数据库进行备份时，数据库不能进行读写操作，即数据库要下线

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-pXwzsjYM-1638708748683)(E:\MarkDown\拉勾笔记\MySQL分库分表和优化–MySQL高级–MySQL的备份与恢复–MySQL冷备份和热备份-- 冷备份)]

冷备份的优点：

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

冷备份的缺点：

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

2.3.2 热备份

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

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-xEcnHlT0-1638708748684)(E:\MarkDown\拉勾笔记\MySQL分库分表和优化–MySQL高级–MySQL的备份与恢复–MySQL冷备份和热备份–热备份)]

热备份的优点：

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

热备份的缺点：

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

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/

恢复数据 (使用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

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-GYng5Leu-1638708748685)(E:\MarkDown\拉勾笔记\MySQL分库分表和优化–MySQL高级–MySQL查询和慢查询日志分析–SQL的执行顺序–程序员编写的SQL)]

MySQL执行的SQL

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-W18qpyBA-1638708748686)(E:\MarkDown\拉勾笔记\MySQL分库分表和优化–MySQL高级–MySQL查询和慢查询日志分析–SQL的执行顺序–MySQL执行的SQL)]

1. FORM子句 : 左右两个表的笛卡尔积

2. ON: 筛选满足条件的数据

3. JOIN： 如果是 inner join 那就正常,如果是 outer join 则会添加回来上面一步过滤掉的一些行

4. WHERE： 对不满足条件的行进行移除, 并且不能恢复

5. GROUP BY: 分组后只能得到每组的第一行数据,或者聚合函数的数值

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，可以分为四类：内连接 、左连接 、右连接、 全连接

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-UdLUqwa0-1638708748687)(E:\MarkDown\拉勾笔记\MySQL分库分表和优化–MySQL高级–MySQL查询和慢查询日志分析–JOIN查询的七种方式)]

3.5 JOIN查询SQL编写

1.创建表插入数据

---部门表
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 内连接

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-UlxlZcsi-1638708748688)(E:\MarkDown\拉勾笔记\MySQL分库分表和优化–MySQL高级–MySQL查询和慢查询日志分析–JOIN查询的七种方式-- 内连接)]

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

3.5.2 左连接

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-ESCLI8fd-1638708748689)(E:\MarkDown\拉勾笔记\MySQL分库分表和优化–MySQL高级–MySQL查询和慢查询日志分析–JOIN查询的七种方式–左连接)]

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

3.5.3 左连接去重叠部分

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-RRduby6r-1638708748690)(E:\MarkDown\拉勾笔记\MySQL分库分表和优化–MySQL高级–MySQL查询和慢查询日志分析–JOIN查询的七种方式–左连接去重叠部分)]

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

3.5.4 右连接

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-irJFofxT-1638708748691)(E:\MarkDown\拉勾笔记\MySQL分库分表和优化–MySQL高级–MySQL查询和慢查询日志分析–JOIN查询的七种方式–右连接)]

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

3.5.5 右连接去重叠部分

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-M1LsK9dG-1638708748692)(E:\MarkDown\拉勾笔记\MySQL分库分表和优化–MySQL高级–MySQL查询和慢查询日志分析–JOIN查询的七种方式–右连接去重叠部分)]

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

3.5.6 全连接

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-DRguHQd9-1638708748693)(E:\MarkDown\拉勾笔记\MySQL分库分表和优化–MySQL高级–MySQL查询和慢查询日志分析–JOIN查询的七种方式–全连接)]

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 各自独有

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-kOh6SAdV-1638708748694)(E:\MarkDown\拉勾笔记\MySQL分库分表和优化–MySQL高级–MySQL查询和慢查询日志分析–JOIN查询的七种方式–各自独有)]

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 慢查询配置方式

1.默认情况下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 |
+---------------------+-----------------------------------+

2.可以通过设置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 |
+---------------------+-----------------------------------+

3.使用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 |
+---------------------+-------------------------------+

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

mysql> show variables like 'long_query_time';
+-----------------+-----------+
| 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 |
+-----------------+-----------+

5.我修改了变量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 |
+-----------------+----------+

6.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’。日志记录到系统的专用日志表中，要比记录到文件耗费更多的系统资源，因此对于需要启用慢查询日志，又需要能够获得更高的系统性能，那么建议优先记录到文件

7.系统变量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)

3.6.4 慢查询测试

1.执行test_index.sql脚本, 导入测试表

2.执行下面的SQL,执行超时 (超过1秒) 我们去查看慢查询日志

SELECT * FROM test_index WHERE
hobby = '20009951' OR hobby = '10009931' OR hobby = '30009931';

3.日志内容

我们得到慢查询日志后，最重要的一步就是去分析这个日志。我们先来看下慢日志里到底记录了哪些内容。

如下是慢日志里其中一条SQL的记录内容，可以看到有时间戳，用户，查询时长及具体的SQL等

==> lagou-slow.log <==
# User@Host: root[root] @ [192.168.52.1] Id: 4
# Query_time: 1.681371 Lock_time: 0.000089 Rows_sent: 3 Rows_examined: 5000000
SET timestamp=1604307746;
select * from test_index where
hobby = '20009951' or hobby = '10009931' or hobby = '30009931' LIMIT 0, 1000;
# Time: 2020-11-02T09:02:26.052231Z

4.MySQL存储引擎

4.1 存储引擎介绍

什么是存储引擎
- 百度百科
  
  [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-HODAKmxF-1638708748696)(E:\MarkDown\拉勾笔记\MySQL分库分表和优化–MySQL高级–MySQL存储引擎–存储引擎介绍–什么是存储引擎)]
存储引擎就是如何存储数据、如何为存储的数据建立索引和如何更新、查询数据等技术的实现方法。就像汽车的发动机一样，存储引擎好坏决定的数据库提供的功能和性能
存储引擎的作用
- 并发性
- 事务支持
- 引用完整性
- 索引支持

4.2 常见的3种存储引擎

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

SHOW ENGINES;

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-c8vsqd8G-1638708748697)(E:\MarkDown\拉勾笔记\MySQL分库分表和优化–MySQL高级–MySQL存储引擎–存储引擎介绍–常见的3种存储引擎01)]

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

SHOW VARIABLES LIKE '%default_storage_engine%';

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-0L6ufpPP-1638708748698)(E:\MarkDown\拉勾笔记\MySQL分库分表和优化–MySQL高级–MySQL存储引擎–存储引擎介绍–常见的3种存储引擎02)]

在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 什么是索引

索引就是排好序的，帮助我们进行快速查找的数据结构

简单来讲，索引就是一种将数据库中的记录按照特殊形式存储的数据结构。通过索引，能够显著地提高数据查询的效率，从而提升服务器的性能

专业一点来说呢，索引是一个排好序的列表，在这个列表中存储着索引的值和包含这个值的数据所在行的物理地址。在数据库十分庞大的时候，索引可以大大加快查询的速度，这是因为使用索引后可以不用扫描全表来定位某行的数据，而是先通过索引表找到该行数据对应的物理地址然后访问相应的数据。

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-jR6YTumU-1638708748699)(E:\MarkDown\拉勾笔记\MySQL分库分表和优化–MySQL高级–MySQL索引优化–索引简介–什么是索引)]

没有用索引时执行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 (字段名) );
ALTER TABLE tablename ADD 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存储引擎均支持
```
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 关键字，比如
```
SELECT * FROM users3 WHERE MATCH(NAME) AGAINST('aabb');
-- * 表示通配符,只能在词的后面
SELECT * FROM users3 WHERE MATCH(NAME) AGAINST('aa*' IN BOOLEAN MODE);
```
- 全文索引使用注意事项：
  - 全文索引必须在字符串、文本字段上建立
  - 全文索引字段值必须在最小字符和最大字符之间的才会有效。（innodb：3-84； myisam：4-84）

5.1.3 索引的优势与劣势

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

5.2 索引原理

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

5.2.1 HASH结构

Hash底层实现是由Hash表来实现的，是根据键值存储数据的结构。非常适合根据key查找value值，也就是单个key查询，或者说等值查询
- 对于每一行数据，存储引擎都会对所有的索引列计算一个哈希码，哈希码是一个较小的值，并且不同键值的行计算出来的哈希码也不一样
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-rlTNie7j-1638708748700)(E:\MarkDown\拉勾笔记\MySQL分库分表和优化–MySQL高级–MySQL索引优化–索引原理–HASH结构)]
Hash索引的缺点
- 哈希索引只包含哈希值和行指针，而不存储字段值，所以不能使用索引中的值来避免读取行
- 哈希索引数据并不是按照索引值顺序存储的，所以也就无法用于排序
- 哈希索引只支持等值比较查询。不支持任何范围查询和部分索引列匹配查找
Hsah索引的优点
- 只需要做等值比较查询，而不包含排序或范围查询的需求，都适合使用哈希索引
- 访问哈希索引的数据非常快，除非有很多哈希冲突。

5.2.2 B+Tree结构

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

B+Tree结构
- 非叶子节点不存储data数据，只存储索引值，这样便于存储更多的索引值
- 叶子节点包含了所有的索引值和data数据
- 叶子节点用指针连接，提高区间的访问性能
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-49aPzv0l-1638708748701)(E:\MarkDown\拉勾笔记\MySQL分库分表和优化–MySQL高级–MySQL索引优化–索引原理–B+Tree结构)]
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;
```
  [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-7BKIrzab-1638708748702)(E:\MarkDown\拉勾笔记\MySQL分库分表和优化–MySQL高级–MySQL索引优化–EXPLAIN性能分析–EXPLAIN的入门)]

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) );

-- 每张表插入1条数据
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;
```
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-14RuEq4K-1638708748703)(E:\MarkDown\拉勾笔记\MySQL分库分表和优化–MySQL高级–MySQL索引优化–EXPLAIN性能分析–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'));
```
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-JKgptwsx-1638708748704)(E:\MarkDown\拉勾笔记\MySQL分库分表和优化–MySQL高级–MySQL索引优化–EXPLAIN性能分析–ID介绍02)]

3.select_type和table介绍

查询类型，主要用于区别普通查询，联合查询，子查询等的复杂查询

simple：简单的select查询，查询中不包含子查询或者UNION
```
EXPLAIN SELECT * FROM L1;
```
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-lIwXUSfh-1638708748705)(E:\MarkDown\拉勾笔记\MySQL分库分表和优化–MySQL高级–MySQL索引优化–EXPLAIN性能分析–select_type和table介绍01)]
primary：查询中若包含任何复杂的子部分，最外层查询被标记
```
EXPLAIN SELECT * FROM L2 WHERE id = (
SELECT id FROM L1 WHERE id = (SELECT L3.id FROM L3 WHERE L3.title = 'lagou03'));
```
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-gMD97DvP-1638708748706)(E:\MarkDown\拉勾笔记\MySQL分库分表和优化–MySQL高级–MySQL索引优化–EXPLAIN性能分析–select_type和table介绍02)]
subquery：在select或where列表中包含了子查询
```
EXPLAIN SELECT * FROM L2 WHERE L2.id = (SELECT id FROM L3 WHERE L3.title = 'lagou03' );
```
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-3DtgCA35-1638708748707)(E:\MarkDown\拉勾笔记\MySQL分库分表和优化–MySQL高级–MySQL索引优化–EXPLAIN性能分析–select_type和table介绍03)]
derived：在from列表中包含的子查询被标记为derived（衍生），MySQL会递归执行这些子查询，把结果放到临时表中
union：如果第二个select出现在UNION之后，则被标记为UNION，如果union包含在from子句的子查询中，外层select被标记为derived
union result：UNION 的结果
```
EXPLAIN SELECT * FROM L2
UNION
SELECT * FROM L3
```
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-4pWqX4DK-1638708748708)(E:\MarkDown\拉勾笔记\MySQL分库分表和优化–MySQL高级–MySQL索引优化–EXPLAIN性能分析–select_type和table介绍04)]

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;
```
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-n3BK68vw-1638708748709)(E:\MarkDown\拉勾笔记\MySQL分库分表和优化–MySQL高级–MySQL索引优化–EXPLAIN性能分析–type介绍01)]
eq_ref：唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配。常见与主键或唯一索引扫描
```
EXPLAIN SELECT * FROM L1 ,L2 WHERE L1.id = L2.id;
```
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-yFOS5G94-1638708748709)(E:\MarkDown\拉勾笔记\MySQL分库分表和优化–MySQL高级–MySQL索引优化–EXPLAIN性能分析–type介绍02)]
ref：非唯一性索引扫描，返回匹配某个单独值的所有行，本质上也是一种索引访问，它返回所有匹配某个单独值的行，这是比较常见连接类型

未加索引之前
```
EXPLAIN SELECT * FROM L1 ,L2 WHERE L1.title = L2.title;
```
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-bn2os3dX-1638708748710)(E:\MarkDown\拉勾笔记\MySQL分库分表和优化–MySQL高级–MySQL索引优化–EXPLAIN性能分析–type介绍03)]

加索引之后
```
CREATE INDEX idx_title ON L2(title);

EXPLAIN SELECT * FROM L1 ,L2 WHERE L1.title = L2.title;
```
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-zdsfzIvi-1638708748711)(E:\MarkDown\拉勾笔记\MySQL分库分表和优化–MySQL高级–MySQL索引优化–EXPLAIN性能分析–type介绍04)]
range：只检索给定范围的行，使用一个索引来选择行
```
EXPLAIN SELECT * FROM L1 WHERE L1.id > 10;
EXPLAIN SELECT * FROM L1 WHERE L1.id IN (1,2);
```
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-aLGH0fEB-1638708748713)(E:\MarkDown\拉勾笔记\MySQL分库分表和优化–MySQL高级–MySQL索引优化–EXPLAIN性能分析–type介绍05)]

key显示使用了哪个索引。where子句后面使用between 、< 、> 、in等查询，这种范围查询要比全表扫描好
index：出现index是SQL使用了索引，但是没有通过索引进行过滤，一般是使用了索引进行排序分组
```
EXPLAIN SELECT * FROM L1 ORDER BY id;
```
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-oMTwzVzR-1638708748714)(E:\MarkDown\拉勾笔记\MySQL分库分表和优化–MySQL高级–MySQL索引优化–EXPLAIN性能分析–type介绍06)]
ALL：对于每个来自于先前的表的行组合，进行完整的表扫描
```
EXPLAIN SELECT * FROM L1;
```
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-E7vshM1j-1638708748715)(E:\MarkDown\拉勾笔记\MySQL分库分表和优化–MySQL高级–MySQL索引优化–EXPLAIN性能分析–type介绍07)]

一般来说，需要保证查询至少达到range级别，最好能到ref

5.possible_keys 与 key介绍

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

1.理论上没有使用索引，但实际上使用了

EXPLAIN SELECT L1.id FROM L1;

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-6hCJpNdz-1638708748716)(E:\MarkDown\拉勾笔记\MySQL分库分表和优化–MySQL高级–MySQL索引优化–EXPLAIN性能分析–possible_keys与key介绍01)]

2.理论和实际上都没有使用索引

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

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-iMRNum7h-1638708748717)(E:\MarkDown\拉勾笔记\MySQL分库分表和优化–MySQL高级–MySQL索引优化–EXPLAIN性能分析–possible_keys与key介绍02)]

3.理论和实际上都使用了索引

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

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-Rtn8gEwb-1638708748718)(E:\MarkDown\拉勾笔记\MySQL分库分表和优化–MySQL高级–MySQL索引优化–EXPLAIN性能分析–possible_keys与key介绍03)]

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

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-EvUroiSr-1638708748719)(E:\MarkDown\拉勾笔记\MySQL分库分表和优化–MySQL高级–MySQL索引优化–EXPLAIN性能分析–key_len介绍01)]
为b字段添加索引
```
ALTER TABLE T1 ADD INDEX idx_b(b);
```
再次测试
```
EXPLAIN SELECT * FROM T1 WHERE a > 1 AND b = 1;
```
两列都使用了索引，所以，这里ken_len是8

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-C0tS2Qa6-1638708748720)(E:\MarkDown\拉勾笔记\MySQL分库分表和优化–MySQL高级–MySQL索引优化–EXPLAIN性能分析–key_len介绍02)]
为d字段添加索引
```
ALTER TABLE T1 ADD INDEX idx_d(d);
```
执行测试
```
EXPLAIN SELECT * FROM T1 WHERE d = '';
```
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-KisQwngO-1638708748720)(E:\MarkDown\拉勾笔记\MySQL分库分表和优化–MySQL高级–MySQL索引优化–EXPLAIN性能分析–key_len介绍03)]

字符集是utf8 一个字符3个字节，d字段是 char(10)代表的是10个字符相当30个字节

7.ref 介绍

显示索引的哪一列被使用了，如果可能的话，是一个常数。哪些列或常量被用于查找索引列上的值
- L1.id=‘1’：1是常量，ref = const
```
EXPLAIN SELECT * FROM L1 WHERE L1.id='1';
```
  [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-PgZjGxr1-1638708748721)(E:\MarkDown\拉勾笔记\MySQL分库分表和优化–MySQL高级–MySQL索引优化–EXPLAIN性能分析–ref介绍01)]
- L2表被关联查询的时候，使用了主键索引，而值使用的是驱动表(执行计划中靠前的表是驱动表)L1表的ID，所以 ref = test_explain.L1.id
```
EXPLAIN SELECT * FROM L1 LEFT JOIN L2 ON L1.id = L2.id WHERE L1.title = 'lagou01';
```
  [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-vp5aDeil-1638708748723)(E:\MarkDown\拉勾笔记\MySQL分库分表和优化–MySQL高级–MySQL索引优化–EXPLAIN性能分析–ref介绍02)]

8.rows 介绍

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

1.使用like 查询，会产生全表扫描，L2中有3条记录，就需要读取3条记录进行查找
```
EXPLAIN SELECT * FROM L1,L2 WHERE L1.id = L2.id AND L2.title LIKE '%la%';
```
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-c9IsUP9m-1638708748724)(E:\MarkDown\拉勾笔记\MySQL分库分表和优化–MySQL高级–MySQL索引优化–EXPLAIN性能分析–rows介绍01)]

2.如果使用等值查询，则可以直接找到要查询的记录，返回即可，所以只需要读取一条
```
EXPLAIN SELECT * FROM L1,L2 WHERE L1.id = L2.id AND L2.title = 'lagou03';
```
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-UDZkjrU3-1638708748725)(E:\MarkDown\拉勾笔记\MySQL分库分表和优化–MySQL高级–MySQL索引优化–EXPLAIN性能分析–rows介绍02)]

总结：当我们需要优化一个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;
```
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-OUd6RM3I-1638708748727)(E:\MarkDown\拉勾笔记\MySQL分库分表和优化–MySQL高级–MySQL索引优化–EXPLAIN性能分析–extra介绍–Using filesort)]

执行结果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;
```
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-E95qpIyU-1638708748728)(E:\MarkDown\拉勾笔记\MySQL分库分表和优化–MySQL高级–MySQL索引优化–EXPLAIN性能分析–extra介绍–Using temporary)]

执行结果Extra为Using temporary，这说明需要建立临时表 (temporary table) 来暂存中间结果。

常见与 group by 和 order by，这类SQL语句性能较低，往往也需要进行优化。
Using where
```
EXPLAIN SELECT * FROM users WHERE age=10;
```
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-7AeKRbNj-1638708748730)(E:\MarkDown\拉勾笔记\MySQL分库分表和优化–MySQL高级–MySQL索引优化–EXPLAIN性能分析–extra介绍–Using where)]

此语句的执行结果Extra为Using where，表示使用了where条件过滤数据

需要注意的是：
1. 返回所有记录的SQL，不使用where条件过滤数据，大概率不符合预期，对于这类SQL往往需要进行优化
2. 使用where条件的SQL，并不代表不需要优化，往往需要配合explain结果中的type（连接类型）来综合判断。例如本例查询的age未设置索引，所以返回的type为ALL，仍有优化空间，可以建立索引优化查询。
Using index
```
EXPLAIN SELECT uid,uname FROM users WHERE uname='lisa';
```
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-SYKWmHI1-1638708748731)(E:\MarkDown\拉勾笔记\MySQL分库分表和优化–MySQL高级–MySQL索引优化–EXPLAIN性能分析–extra介绍–Using index)]

此句执行结果为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;
```
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-LZJHquXN-1638708748732)(E:\MarkDown\拉勾笔记\MySQL分库分表和优化–MySQL高级–MySQL索引优化–EXPLAIN性能分析–extra介绍–Using join buffer)]

执行结果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;

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-62LBG8Kq-1638708748734)(E:\MarkDown\拉勾笔记\MySQL分库分表和优化–MySQL高级–MySQL锁机制–表级锁(偏读)]–加锁语法–查看表中加过的锁)

手动增加表锁

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

SHOW OPEN TABLES WHERE In_use > 0;

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-WdHzXRAH-1638708748735)(E:\MarkDown\拉勾笔记\MySQL分库分表和优化–MySQL高级–MySQL锁机制–表级锁(偏读)]–加锁语法–手动增加表锁)

释放锁，解除锁定

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

SHOW PROCESSLIST;
kill id

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-XzCyXsAf-1638708748736)(E:\MarkDown\拉勾笔记\MySQL分库分表和优化–MySQL高级–MySQL锁机制–表级锁(偏读)]–加锁语法–释放锁, 解除锁定)

6.3.3 加读锁测试

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

1.对mylock01表加读锁

lock table mylock01 read;

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

select * from mylock01;

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-0udMeeXk-1638708748737)(E:\MarkDown\拉勾笔记\MySQL分库分表和优化–MySQL高级–MySQL锁机制–表级锁(偏读)]–加读锁测试01)

3.在1窗口进行写操作 (update)，失败

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

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-tPZg5bSG-1638708748738)(E:\MarkDown\拉勾笔记\MySQL分库分表和优化–MySQL高级–MySQL锁机制–表级锁(偏读)]–加读锁测试02)

4.在1窗口中读取其他的表，比如读取mylock02表。读取失败

select * from mylock02;

错误提示：表“mylock02” 未用锁表锁定

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-XaI9YWdp-1638708748739)(E:\MarkDown\拉勾笔记\MySQL分库分表和优化–MySQL高级–MySQL锁机制–表级锁(偏读)]–加读锁测试03)

5.在2窗口中对mylock01表进行写操作

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

执行后一直阻塞

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-9E8MDSlZ-1638708748740)(E:\MarkDown\拉勾笔记\MySQL分库分表和优化–MySQL高级–MySQL锁机制–表级锁(偏读)]–加读锁测试04)

6.解除mylock01的锁定，窗口2的修改执行

unlock tables;

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-FHtHKvke-1638708748742)(E:\MarkDown\拉勾笔记\MySQL分库分表和优化–MySQL高级–MySQL锁机制–表级锁(偏读)]–加读锁测试05)

总结：

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

6.3.4 加写锁测试

1.在窗口1中对mylock01表加写

lock table mylock01 write;

2.在窗口1中，对mylock01进行读写操作，都是可以进行的

select * from mylock01 where id = 1;
update mylock01 set title = 'a123' where id = 1;

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-lvv7Yz46-1638708748743)(E:\MarkDown\拉勾笔记\MySQL分库分表和优化–MySQL高级–MySQL锁机制–表级锁(偏读)]–加写锁测试01)

3.在窗口1中读其他表，还是不允许

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-ShroXftj-1638708748744)(E:\MarkDown\拉勾笔记\MySQL分库分表和优化–MySQL高级–MySQL锁机制–表级锁(偏读)]–加写锁测试02)

4.在窗口2中读mylock01表，读操作被阻塞

select * from mylock01;

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-kJRFQeK7-1638708748746)(E:\MarkDown\拉勾笔记\MySQL分库分表和优化–MySQL高级–MySQL锁机制–表级锁(偏读)]–加写锁测试03)

5.在窗口2中对mylock01表进行写操作，仍然被阻塞

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

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-IxQzsMbu-1638708748747)(E:\MarkDown\拉勾笔记\MySQL分库分表和优化–MySQL高级–MySQL锁机制–表级锁(偏读)]–加写锁测试04)

6.释放锁，窗口2操作执行执行

unlock tables;

总结：对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;
锁兼容
- 共享锁只能兼容共享锁，不兼容排它锁
- 排它锁互斥共享锁和其它排它锁
  
  [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-YptkTddE-1638708748748)(E:\MarkDown\拉勾笔记\MySQL分库分表和优化–MySQL高级–MySQL锁机制–行级锁(偏写)]–行级锁介绍–锁兼容)
行级锁都是基于索引的，如果一条SQL语句用不到索引是不会使用行级锁的，而会使用表级锁把整张表锁住，这点需要咱们格外的注意

6.4.2 行锁测试

1.更新时的行锁测试

1.数据准备

#创建表
CREATE TABLE innodb_lock(
    id INT PRIMARY KEY AUTO_INCREMENT,
    NAME VARCHAR(20),
    age INT,
    INDEX idx_name(NAME)
);

# 插入数据
INSERT INTO innodb_lock VALUES(NULL,'a', 13);
INSERT INTO innodb_lock VALUES(NULL,'b', 23);
INSERT INTO innodb_lock VALUES(NULL,'c', 33);
INSERT INTO innodb_lock VALUES(NULL,'d', 43);

2.打开两个窗口，都开启手动提交事务 (提交事务或回滚事务就会释放锁)

#开启MySQL数据库手动提交
SET autocommit=0;

3.执行不同会话修改操作，窗口1读，窗口2写

窗口1进行，对id为1的数据进行更新操作，但是不进行commit
执行之后，在当前窗口查看表数据，发现被修改了

update innodb_lock set name = 'aaa' where id=1;
select * from innodb_lock;

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-110VhUon-1638708748749)(E:\MarkDown\拉勾笔记\MySQL分库分表和优化–MySQL高级–MySQL锁机制–行级锁(偏写)]–行锁测试01)

4.在窗口2查看表信息，无法看到更新的内容

select * from innodb_lock;

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-rTfUOpBf-1638708748750)(E:\MarkDown\拉勾笔记\MySQL分库分表和优化–MySQL高级–MySQL锁机制–行级锁(偏写)]–行锁测试02)

总结：行级锁中的写锁主要是为了解决在修改数据时，不允许其他事务对当前数据进行修改和读取操作，从而可以有效避免”脏读”问题的产生。

5.窗口1对innodb_lock表的id=1的这一行，进行写操作，但是不要commit

begin;
update innodb_lock set name = 'abc' where id=1;

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-8PWLtRKV-1638708748751)(E:\MarkDown\拉勾笔记\MySQL分库分表和优化–MySQL高级–MySQL锁机制–行级锁(偏写)]–行锁测试03)

6.接下来窗口2也对innodb_lock表的id=1的这一行，进行写操作，发现发生了阻塞

begin;
update innodb_lock set name = 'a123' where id=1;

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-aLtKTEvF-1638708748752)(E:\MarkDown\拉勾笔记\MySQL分库分表和优化–MySQL高级–MySQL锁机制–行级锁(偏写)]–行锁测试04)

7.等窗口1执行commit语句之后，窗口2的SQL就会执行了

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-lfLYO2F5-1638708748753)(E:\MarkDown\拉勾笔记\MySQL分库分表和优化–MySQL高级–MySQL锁机制–行级锁(偏写)]–行锁测试05)

总结：在有写锁的情况下，其他事务不能再对当前数据添加写锁，从而保证数据的一致性，从而避免了不可重复读的问题

2.查询时的排他锁测试

1.select语句加排他锁方式：select * from table_name where … for update;

for update 的作用
- for update 是在数据库中上锁用的，可以为数据库中的行上一个排他锁
for update 的应用场景
- 存在高并发并且对于数据的准确性很有要求的场景，是需要了解和使用for update的
for update 的注意点
- for update 仅适用于InnoDB，并且必须开启事务，在begin与commit之间才生效

2.在窗口1中，首先开启事务，然后对id为1的数据进行排他查询

begin;
select * from innodb_lock where id = 1 for update;

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-y1oVltvR-1638708748755)(E:\MarkDown\拉勾笔记\MySQL分库分表和优化–MySQL高级–MySQL锁机制–行级锁(偏写)]–行锁测试06)

3.在窗口2中，对同一数据分别使用排他查和共享锁两种方式查询

-- 排他锁查询
select * from innodb_lock where id = 1 for update;

-- 共享锁查询
select * from innodb_lock where id = 1 lock in share mode;

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-LJXtjm0S-1638708748756)(E:\MarkDown\拉勾笔记\MySQL分库分表和优化–MySQL高级–MySQL锁机制–行级锁(偏写)]–行锁测试07)

我们看到开了排他锁查询和共享锁查询都会处于阻塞状态，因为id=1的数据已经被加上了排他锁，此处阻塞是等待排他锁释放。

4.如果只是使用普通查询，我们发现是可以的

select * from innodb_lock where id = 1;

3. 查询时的共享锁测试

添加共享锁：select * from table_name where … lock in share mode;
事务获取了共享锁，在其他查询中也只能加共享锁，但是不能加排它锁

1.窗口1 开启事务，使用共享锁查询id = 2的数据，但是不要提交事务

begin;
select * from innodb_lock where id = 2 lock in share mode;

2.窗口2开启事务，使用普通查询和共享锁查询id = 2的数据，是可以的

select * from innodb_lock where id = 2 lock in share mode;
select * from innodb_lock where id = 2;

3.加排他锁就查不到，因为排他锁与共享锁不能存在同一数据上

select * from innodb_lock where id = 2 for update;

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-pjaGU1KG-1638708748757)(E:\MarkDown\拉勾笔记\MySQL分库分表和优化–MySQL高级–MySQL锁机制–行级锁(偏写)]–行锁测试08)

6.4.3 行锁分析

1.执行下面的命令，可以获取行锁锁信息

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 |
+-------------------------------+-------+

2.参数说明

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 高可用介绍

1. 什么是高可用性

维基百科的解释是：

高可用性（英语：High availability，缩写为 HA），IT术语，指系统无中断地执行其功能的能力，代表系统的可用性程度。是进行系统设计时的准则之一。高可用性系统与构成该系统的各个组件相比可以更长时间运行。

计算公式: A表示可用性；MTBF表示平均故障间隔；MTTR表示平均恢复时间

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-SYsmlej0-1638708748758)(E:\MarkDown\拉勾笔记\MySQL分库分表和优化–MySQL高级–MySQL集群架构–MySQL高可用设计–. 什么是高可用性)]

假设系统一直能够提供服务，我们说系统的可用性是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端。

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-NwyKpIBm-1638708748759)(E:\MarkDown\拉勾笔记\MySQL分库分表和优化–MySQL高级–MySQL集群架构–MySQL高可用集群方案–主从复制+读写分离)]

主从复制的优点：

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

读写分离的优点：

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

7.2.2 双主从复制

很多企业刚开始都是使用MySQL主从模式，一主多从、读写分离等。但是单主如果发生单点故障，从库切换成主库还需要作改动。因此，如果是双主或者多主，就会增加MySQL入口，提升了主库的可用性

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-JPOQYYmS-1638708748761)(E:\MarkDown\拉勾笔记\MySQL分库分表和优化–MySQL高级–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实现的双主高可用架构。

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-v4kuIvYp-1638708748762)(E:\MarkDown\拉勾笔记\MySQL分库分表和优化–MySQL高级–MySQL集群架构–MySQL高可用集群方案–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管理多组主从复制

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-0PzVP7CT-1638708748763)(E:\MarkDown\拉勾笔记\MySQL分库分表和优化–MySQL高级–MySQL集群架构–MySQL高可用集群方案–MHA架构)]该软件由两部分组成：MHA Manager（管理节点）和MHA Node（数据节点）。

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

MHA故障处理机制：

把宕机master的binlog保存下来
根据binlog位置点找到最新的slave
用最新slave的relay log修复其它slave
将保存下来的binlog在最新的slave上恢复
将最新的slave提升为master
将其它slave重新指向新提升的master，并开启主从复制

MHA优点：

自动故障转移快
主库崩溃不存在数据一致性问题
性能优秀，支持半同步复制和异步复制
一个Manager监控节点可以监控多个集群

coolwei-

关注

0
点赞
踩
4

收藏

觉得还不错? 一键收藏
0
评论
MySQL分库分表和优化

第九阶段模块三分库分表技术之MyCat1.海量存储问题1.1 背景描述随着互联网的发展，数据的量级也是成指数的增长，从GB到TB到PB。对数据的各种操作也是愈加的困难，传统的关系性数据库已经无法满足快速查询与插入数据的需求。**解决方案1：**使用NoSQL数据库，通过降低数据的安全性，减少对事务的支持，减少对复杂查询的支持，来获取性能上的提升。**解决方案2：**NoSQL并不是万能的，就比如有些使用场景是绝对要有事务与安全指标的，所以还是要用关系型数据库，这时候就需要搭建MySQL数据库集群
复制链接

扫一扫