性能测试-数据库优化二（SQL的优化、数据库拆表分表分区，读写分离与主从同步、redis安装使用与监控、数据库监控）

乐瓜乐虫

已于 2024-08-17 00:00:27 修改

阅读量886

点赞数 17

分类专栏：性能分析数据库文章标签：数据库 sql redis

于 2024-04-12 09:59:33 首次发布

本文链接：https://blog.youkuaiyun.com/qq_35283902/article/details/137669069

版权

性能分析同时被 2 个专栏收录

15 篇文章

订阅专栏

数据库

11 篇文章

订阅专栏

数据库优化

explain select

重点： type类型，rows行数，extra

SQL的优化

在写on语句时，将数据量小的表放左边，大表写右边
where后面的条件尽可能用索引字段，复合索引时，最好按复合索引顺序写where条件
where后面有in语句，in字段的索引，最好放复合索引的后面，因为in的字段索引可能会失效
模糊查询时，尽量用常量开头，不要用%开头，用%开头查询索引将失效
- select * from table where col like ‘明确%’;
尽量不要使用 or，否则索引失效
尽量不要使用类型转换(显式、隐式),否则索引失效如果主查询数据量大，则使用in；
如果子查询数据量大，则使用exists
- select * from table_1 where id in(数据量小);
查询哪些列，就根据哪些列group\order by，不然会产生一个临时表
写select，尽可能的不用 *

实际项目中sql的优化：

获取慢sql
- 并发时候是慢sql
- 单独执行就是慢sql
执行sql
简单分析
- 简单分析
- 复杂的sql（行数在几十行）
  - 先梳理sql的业务
    - 业务的梳理，可以拆解sql
  - 复杂sql能用代码实现的，尽可能用代码来实现

数据库的优化

数据库库层面优化：

数据库的配置参数、
操作系统参数、
磁盘

数据库表层面优化

表的存储引擎
建表的时候字段数量及字段类型
建索引，一般情况下mysql表的索引，一张表不超过5个
建视图表
- 内存表，不磁盘上
sql优化

数据库拆表

数据库表中数据，产品使用一段时间会后，某些表的数据量的数据量就可能很大。同时数据库对于产品正常非常重要，万一数据库有问题了，导致产品无法正常使用，数据库还需要备份。

备份：一般会备份到其他地方

冷备份：指定一个备份规则，满足规则的时候做备份。
- 如：凌晨一两点、产品用户使用率最低的时候。
热备份：几乎实时对数据库的数据变更进行备份。
- 读写分离、主从同步

读写分离、主从同步

读写分离

主从同步：数据库，至少是两个以上。多个数据库，一般是在不同的机器

读写分离：读数据和数据变更实在不同的数据库中。

读、写，哪个用主数据库，哪个用从数据库？-------思考

docker run -itd --name mysql-1 -p 3366:3306 -e MYSQL_ROOT_PASSWORD=123456
mysql:5.7
docker run -itd --name mysql-2 -p 3377:3306 -e MYSQL_ROOT_PASSWORD=123456
mysql:5.7
docker run -itd --name mysql-3 -p 3388:3306 -e MYSQL_ROOT_PASSWORD=123456
mysql:5.7

修改数据的配置文件，在配置文件中，设置数据库的服务id和日志同步文件格式，有多个数据库，数据库的id不相同，id值越大，将来作为从数据库。

server-id=100
log-bin=mysql-bin

登录准备作为从数据库的数据库，添加主数据库的信息，添加成功之后，从数据库才知道主数据库是哪个？

CHANGE MASTER TO
MASTER_HOST='192.168.130.130',
MASTER_PORT=3366,
MASTER_USER='root',
MASTER_PASSWORD='123456';
START SLAVE;
show slave status;

看执行的结果 Slave_SQL_Running Slave_IO_Running的值为Yes。

Slave_IO_Running：意思是二进制文件同步正在运行。Yes，就会从主数据库自动同步数据库二进制文件到从数据。No，就不会同步文件

Slave_SQL_Running：二进制日志回放，正在运行，执行sql，Yes，同步过来的文件，就会执行。No，同步过来的文件，也不会执行。

数据同步，只能是主数据库同步给从数据库，不能反过来。

在主数据库做的任何操作，都可以同步给从数据库，从数据库的数据变动，是不能同步给其他从数据库和主数据库的

从数据库改了数据，主数据库中新增一条新数据，还是可以同步给从数据库的，但是如果主数据库改了从数据库相同的数据，导致了从数据库冲突，冲突之后导致所有数据都不能同步过来。只能解决冲突才能再次同步（删除对应的数据并不能解决冲突）

所以数据变更是在主数据库，获取数据是在从数据库。从数据库是用来读数据，主数据库是用来写数据的。

主从同步

企业中项目，70%的性能问题都会与数据库有关。

项目中从数据库中获取数据的使用率，远远高于进行数据变更的。

实现数据库读写分离的话，那么进行数据变更，获取数据就可以从不同的数据库中获取。这样获取数据的性能会要更高一些。

主从同步，至少要有两个以上的数据库。主数据库，主要是做数据变更，从数据库，数据会自动从主数据库同步过来。这样就可以保障主从数据库的数据一致。读的时候，使用从数据库数据，数据不会错，变更数据，使用主数据库，这样一旦数据发生变化，自动同步给从数据库，获取数据是从从数据库中获取，所以从数据库的数据，也是变更之后的数据。

从数据库可以是多个。多个从数据库，可以配置为集群。项目中，jdbc的url地址，就配置数据库的集群地址，这样项目就可以实现从多个从数据库中获取数据。这样获取数据的性能就提升了。

使用主从同步，读写分离，在企业项目中是比较常见的。使用之后数据库的性能也是可以得到明显的提升。

1、用docker方式创建了2个数据库

2、区分主从数据库

主数据库的id小，从数据库的id大

修改数据库的配置文件 server-id=xx

3、主从数据库之间数据同步？

同步方式：二进制日志文件

log-bin=mysql-bin

连接信息：两个库之间连接信息（从数据库中，配置主数据库的信息）

主从数据库的搭建（实战）

在一台机器中，用docker创建多个数据库详细搭建教程看这里

在docker中创建一个数据库

docker run -itd --restart always --name  mysql-master  -p 3307:3306 -e MYSQL_ROOT_PASSWORD=123456  daocloud.io/mysql:5.7

2个数据库如下：

进入主数据库

docker exec -it centos7_mysql57 /bin/bash

修改文件mysqld.cnf（安装方式不同路径不一样），将mysqld.cnf拷贝到宿主机上进行修改

docker cp mysql-master:/etc/mysql/mysql.conf.d/mysqld.cnf $PWD

修改mysqld.cnf

# 在宿主机上修改mysqld.cnf，修改如下

# 在宿主机上修改mysqld.cnf，修改如下
[mysqld]
pid-file        = /var/run/mysqld/mysqld.pid
socket          = /var/run/mysqld/mysqld.sock
datadir         = /var/lib/mysql
server-id       = 100
log-bin         = mysql-bin

# server-id 是唯一的服务器id，非0整数即可，但不能重复（从数据库的id要比主数据库大）
# log-bin 使用binary logging， mysql-bin是log的文件名称前缀

从宿主机拷贝mysqld.cnf文件到容器

# 从宿主机本地路径中，拷贝修改后的mysql.cnf文件到mysql-master容器

主数据库如下

从数据库如下

重启两台数据库容器，让配置生效

docker restart mysql-master centos7_mysql57

使用数据库连接工具连接主从数据库

在从数据库中新建查询，执行sql语句连接到主数据库

CHANGE MASTER TO
MASTER_HOST='192.168.100.101',    -- 宿主机ip
MASTER_PORT=3337,   -- mysql-master映射到宿主机的端口
MASTER_USER='root',
MASTER_PASSWORD='123456';START SLAVE;

执行后结果如下

启动从服务数据库

start slave;

再执行show SLAVE STATUS查看是否同步成功

show SLAVE STATUS

查看状态中： SLAVE_IO_Running SLAVE_SQL_Running 要都为Yes

SLAVE_IO_Running 与主数据库之间文件同步状态， yes就是正常，no就不能进行文件同步。

SLAVE_SQL_Running 从数据库，收到同步文件之后，回放脚本是否正常，yes就是正常的，no就是不能回放脚本。

数据库的任何操作，都是日志回放

从库不要进行数据变更，进行数据变更，就可能到两库之间数据冲突；就需要解决冲突问题，然后，重启从库的slave。

我们先在主数据库中新建一个数据库andy01,在从数据库刷新下就可以看见主数据库新创建数据库

我们在主数据库中再创建一个新表，并添加修改和删除对应的数据，刷新从数据库也添加、修改和删除对应的数据

如果我们改变从数据库的添加数据会发生什么呢？

从数据库的表内容添加数据如下：

主数据库也添加数据如下：

刷新从库的数据，从库结果没有发生改变，说明从数据库新增和无法同步到主数据库

也可以通过show SLAVE STATUS查看同步情况

从界面中我们可以看到数据可以同步成功，但无法回放成功，原因是主键id冲突了，所以从库不要做数据变更。

删除主数据库和重数据的冲突主键数据，并停止从数据库服务

stop  SLAVE;   -- 停止服务

再次启动从数据库服务

START SLAVE;

再次看看同步状态，那就恢复正常啦

分表分区

-详细看这里

分表

分表：拆表垂直拆表，水平拆表

拆表拆出来的子表，都是真实的物理表，表中数据，是落在磁盘上的。每个表在磁盘上的文件变小，表的性能就提升了。

垂直拆表：根据一定的策略，把表的列拆分。
- 策略：如，根据sql语句中，使用率高低
水平拆表：根据一定的策略，把表的行数量拆分
- 策略：如，根据id的尾号

宽表：一个表的列很多，大几十或上百列

视图：不是一个真正的物理表。是一个虚拟表。不落磁盘

由表根据某些字段，满足条件，构成一个虚拟表

如 create view 名称 as select 指定某些列 from ？ where teacher =‘allen’

好处：

项目中写sql语句时候，sql语句可以简化；加快数据的查询速度。

缺点：

视图，占用内存空间。

视图表的数据量不能太大---占用的内存也太多，就会降低数据库的性能

拆完表，使用过程中，又会遇到，从所有数据中，查找要的数据。

合并问题。

合表：

表的存储引擎MRG_MyISAM，不是物理表，不落磁盘

子表的存储引擎，只能是 MyISAM

合表：主表(是一张虚拟表，不再磁盘上)的存储引擎是MRG_MYISAM，子表存储引擎必须为MYISAM。 mysql数据库中InnoDB存储引擎，不支持合表

分区

数据库表的数据落入磁盘时，写入不同的磁盘分区上去。

可以是一张表，根据一定的策略把满足策略的数据写入到不同硬盘的分区上。

也可以是数据库中的多张表，不同的表写入到不同的硬盘分区上。

写到不同的硬盘，每个硬盘都有自己的IO，写到不同的硬盘，就可以使用不同硬盘的IO性能

分区可以把表数据，落到磁盘上的文件变小，同时也可以使用不同硬盘IO性能，所以数据库表分区对性能提升是很大的。

但是这种方法在企业中相对主从同步用的少一些。

主要原因：

成本，做数据表的分区，一般要使用RAID磁盘矩阵
技术，RAID技术、数据库分区的技术、数据恢复技术

总结关系型数据库性能优化

1、数据库是一个软件，是安装在操作系统中

优化：操作系统优化(硬件、系统参数)、数据库自身参数优化
优化：主从同步

2、数据库的建库、建表

优化：建表的存储引擎、字段类型、字段数量、表索引，关联表、建视图
优化：分表分区

3、数据库的使用

优化：sql优化
优化：读写分离

4、补充：现在数据库越来越多，出现了分布式数据库。TiDB

分布式存储

分布式计算--sqlite

非关系型数据库

是相对于关系型数据库而言的，关系型数据库，表与表之间是有关联关系的。非关系型数据库，就是说表与表之间，没有明确的关联关系。

关系型数据库，表是二维表，把数据进行了栅格化。但是非关系型数据库，没有对数据进行统一的标准化的格式化。

关系型数据库，统一标准的sql语句，非关系型数据库，没有统一标准的sql，各个不同的非关系型数据库，都有自己的独立的sql语句。

总体的情况是非关系型数据库，数据结构虽然没有关系型数据库那么清晰，但是性能和数据扩展性要远远高于关系型数据库。

现在企业项目数据库，一般会选择：关系型数据库为主数据库，非关系型数据库为辅助数据库，这种多数据库组合的方式。

在企业项目中，redis作为缓存数据库是最常见的。

因为：redis数据库，是一个内存数据库，它的数据是保存在内存中，但是它又有一个自动同步磁盘的机制，可以根据设定的配置项目，自动间隔一段时间把内存中的数据写入磁盘。redis数据库可以实现缓存集群。

性能测试学习redis：

是内存数据库，性能是比较好的，至少它的性能比磁盘数据库的性能要高。
也会存在性能问题。------→性能测试经历中，有见过接口响应时间规律性忽高忽低，这种情况很大概率就是redis性能问题

redis的安装与使用

redis的安装一

redis是6.x版本，这个版本要求GCC要大于5.3版本。

GCC是linux系统中，编译安装系统软件的必须的库，这个库linux中可以直接安装。centos7系统，默认直接安装的版本是4.8.5版本。来安装redis6.x版本会报错。

# centos7系统
# 安装gcc

yum install gcc* -y

# 可以使用 gcc -v 来查看gcc的版本

yum -y install centos-release-scl
 
# 可以使用 yum search gcc 或 yum list available | grep gcc 来查看，当前系统可以安装的gcc的版本

#安装gcc9版本，其他版本需要修改
sudo yum install -y devtoolset-9-gcc devtoolset-9-gcc-c++
或
yum -y install devtoolset-9-gcc devtoolset-9-gcc-c++ devtoolset-9-binutils

#设置默认的gcc,需要和上面的版本相同

scl enable devtoolset-9 bash

#再次验证gcc版本，gcc -v  gcc的版本就变更为9.3.1了

#添加环境变量

echo "source /opt/rh/devtoolset-9/enable" >>/etc/profile

安装gcc后可以看出我的gcc版本是4.8.5

升级后版本如下9.31

下载安装redis

wget http://download.redis.io/releases/redis-6.0.8.tar.gz
tar -xzvf redis-6.0.8.tar.gz
cd redis-6.0.8
make

# 如果想安装到指定路径： make PREFIX=/usr/local/redis install   指定安装到/usr/local/redis路径，make不指定路径安装在当前路径

# 启动

src/redis-server

安装成功如下：

redis.conf文件就是数据的配置文件

在redis.conf 中protected-mode yes ：指数据库是否开启保护模式，默认yes，不允许外部连接，需要外部连接则需要改为 no

port 6379 数据库默认端口

daemonize no 在redis默认情况下守护进程为no，即不是守护进程

databases 16 ：redis默认有16个库，可以多个项目共用一个redis数据库，但使用的库在这16中不能是同一个库

启动redis

在redis解压目录下redis-server就是启动文件

./redis-server
或
src/redis-server

关闭redis服务

src/redis-cli shutdown

重启redis

sudo systemctl restart redis

redis的配置

修改配置文件redis.conf

key	value	用意
port	6379	端口
protected-mode	no	是否启用保护模式
tcp-backlog	511	tcp监听最大容纳数量
timeout	0	客户端闲置多少秒关闭连接，0不限制
daemonize	no	是否为守护进程，no不是
requirepass foobared	123456	设置访问 Redis 的密码。

绑定地址:

bind 127.0.0.1

通过设置此选项，您可以指定 Redis 监听的 IP 地址。默认为 127.0.0.1，表示仅允许本地连接。如果需要外部访问，可以设置为 0.0.0.0（注意安全风险）。

启用保护模式

protected-mode no 为no时启动保护模式，即远程无法连接redis

端口设置

port 6379 rides的端口参数设置

监听听最大容纳数量

tcp-backlog 511

远程连接时间断开设置

timeout 0 户端闲置多少秒关闭连接，0不限制

启用守护进程

daemonize yes 启动redis后，直接进入后台模式

设置密码

requirepass yourpassword设置访问 Redis 的密码。

设置密码要注意空格，保存好配置文件后需要重启redis服务并指定对应的配置文档

src/redis-server /opt/andy/redis-6.0.8/redis.conf

验证密码设置

#连接redis
src/redis-cli

#直接查找配置文件密码
CONFIG GET requirepass
# 输入上面的查询预计会提示需要身份信息
# 提示：(error) NOAUTH Authentication required.
#输入密码
AUTH 123456
#提示ok,就可以执行接下来的操作了

设置rides日志级别

loglevel notice 设置日志记录的级别（如 debug, verbose, notice, warning）。

持久化选项

RDB 持久化:

save 900 1
save 300 10
save 60 10000

这些行配置数据在多久内有变化时进行持久化。第一行表示每 900 秒（15 分钟）至少有 1 次修改时保存。

1、使用客户端连接redis： src/redis-cli

2、执行： config get * --获取所有的配置信息

3、config get keyname -- 获取某个具体key的值

获取配置信息requirepass的值

CONFIG GET requirepass

配置信息中，持久化到磁盘的方式，会影响性能

配置信息中，持久化到磁盘的事件间隔，会影响性能

做性能测试，要懂得redis的安装、redis的配置，redis的使用要了解。

redis的使用

redis的常用数据类型与操作命令

string字符串
1. set strkey strvalue get strkey
hash哈希
1. hmset hashkey field1 "value1" field2 "value2" hget haskey filed1
list列表
1. lpush lrange
set集合
1. sadd smembers sorted
set 有序集合
1. zadd zrangebyscore

key：value设置的时候，后[EX seconds|PX milliseconds|KEEPTTL] 可选参数，设置这个key在内存中，存活的时长的。没有时长，就是永久优秀，永久的存在内存中。

在项目中，有的数据，是需要有生命时长的。过了时长，这个数据，就会自动消失。

redis有5种常用数据类型，以及这些类型是基本使用。
string字符串类型 ----→ 值为string
- set 设置key和value，get获取key的值
hash类型
- hmset key filed1 value1 flied2 value2 filed3 value3……
- hget key filedname
list类型
- lpush key value1 value2 value3….. 往列表的左边插入数据
- lrange key start end 从列表的左边开始取值
set集合数据不会重复
- sadd key value1 value2
- smembers
sorted set有序集合
- zadd 添加数据
- zrangebyscore 获取数据

redis的性能问题

主要的原因，redis数据需要设置一个有效时长，如果不设置这个key对应value就会一直存在redis数据库中，redis是一个内存数据库，随着使用时间越来越长，如果没有给key设置一个失效时长，内存总会出现不够用的情况。所以要给key设置一个有效时长。但是如果这个有效时长设置的不合理，就可能导致问题

redis的穿透

redis是一个缓存数据库，是用于缓存从磁盘数据库中获取到数据，减少从磁盘获取数据的次数，提高获取数据的速度。
redis的穿透，是获取数据的时候，我们key是非法的，永远不存在的。获取key的值时，从 redis是永远拿不到值，就要去后面的磁盘数据库中获取，对磁盘数据库造成了很大的压力，设置可能导致磁盘数据库宕机。
- 解决：在代码中，对非法的key进行处理就可以了。

redis的击穿

持续一段时间内，有大量的请求，集中在少量的key上面，当key失效的时候，瞬间请求到了后端磁盘数据库，导致后端磁盘数据库瞬间压力非常大。
- 解决：限量

redis的雪崩

redis中key是有一个失效时间，在同一个时间点里，有大面积的key失效，请求这些key的时候，都请求到了后端磁盘数据库。
- 解决：redis设置key的失效时间随机

redis的性能监控测试

redis是内存数据库，性能要比磁盘数据库性能要好很多。所以一般情况下不会有性能问题。

redis本身数据类型的操作的信息是非常快的。redis自带了一个性能测试工具，这个工具模拟对redis 进行数据操作

查看redis-benchmark帮助信息

 src/redis-benchmark --help

选项	描述	默认值	选择	描述	默认值
-h	指定服务器主机名	127.0.0.1	-r	set\get\incr随机key
-p	指定端口	6379	-P	通过管道传输请求	1
-s	指定服务器socket		-q	强制退出redis
-c	指定并发连接数	50	--csv	以csv格式输出
-n	指定请求数	10000	-l	生成循环，永久执行
-d	以字节指定set\get大小	2	-t	仅运行以逗号分隔的测试命令列表
-k	1 keep-alive 0 reconnect	1	-I	Idle 模式

进行性能测试

 src/redis-benchmark -n 1000000 -q

通过上图我们可以知道 redis的数据库数据设置与读取的速度，远远快于关系型数据库。

数据库监控

监控还是使用grafana+Prometheus+exporter监控

MySQL监控

两种：

docker方式来监控数据库

[root@vircent7 ~]# docker run -itd --name mysqld_exporter -p 9114:9104 -e
DATA_SOCRCE_NAME="root:123456@(192.168.130.130:3337)" prom/mysqld-exporter

mysqld_exporter包监控
- 数据库是直接安装在centos7系统中 /etc/my.cnf配置文件
- 在数据库的机器上，放置mysqld_exporter文件 https://github.com/prometheus/mysqld_exporter/releases/download/v0.15.0- rc.0/mysqld_exporter-0.15.0-rc.0.linux-amd64.tar.gz
- 解压
- 修改数据库的配置文件/etc/my.cnf

[client]
host=ip地址
port=3306
user=
password=

启动mysqld_expoter: ./mysqld_exporter --config.my-cnf="/etc/my.cnf"

修改Prometheus.yml

- job_name: 'mysql'
static_configs:
- targets: ['mysql的ip:9104']

启动Prometheus： ./prometheus

启动grafana： systemctl restart grafana-server

登录grafana，引入的模板是 7362

redis监控

下载redis_expoter https://github.com/prometheus/mysqld_exporter/releases/download/v0.15.0- rc.0/mysqld_exporter-0.15.0-rc.0.linux-amd64.tar.gz

解压，启动redis： ./redis_expoter -redis.addr redis的bind地址

修改Prometheus.yml

- job_name: 'redis'
static_configs:
- targets: ['redis的ip:9121']

重启Prometheus 登录grafana，

引入的模板 763