性能测试之数据库测试
- 一、 数据库分类
- 二、 mysql安装及密码的修改
- 1、安装：数据库的版本 mysql5.7版
- 方法1：直接安装
- 方法2：使用rpm包安装
- 方法3：docker方式安装
- 2、修改数据库的密码
- 3、创建库
- 4、创建表
- 三、存储引擎
- 1、InnoDB
- 特点
- 2、MyISAM
- 特点
- 不同的引擎及其介绍
- 四、索引
- 1、简介
- 2、优势和弊端
- 3、分类
- 1、主键索引： id（有且只有一个）
- 2、唯一索引： 不可重复，只有NULL可以重复且可以有多个
- 3、复合索引： **多列**的名称一起建立一个索引，列的字段是有顺序
- 五、 数据库在磁盘的展示
- 六、B树索引
- B+树（balanceTree平衡树）的优点：
- 七、视图
- 1、概念
- 2、作用
- 3、用法
- 八、数据查询解析过程
- 1、数据库解析过程
- 九、影响数据库性能的因素总结
- 十、数据库的性能优化
- 1、操作系统优化
- 2、库的优化
- 数据库常用配置参数及其含义含义
- 十、慢查询分析
- 2、数据库表层面的优化
- 十一、性能分析实战
- explain sql语句 -----分析你的sql
- 1、id: 编号
- 2、select_type: 查询类型，种类有多种
- 3、**type：** 查询的类型，也是有多种值（重点掌握）
- 4、possible_keys：可能用到的索引
- 5、key: 脚本真正用到索引
- 6、key_len：实际使用索引的长度
- 7、ref：表之间的匹配条件
- 8、 rows: 总共查询了多少行（通过索引查询到的数据行数）
- 9、extra：
- 十二、sql优化tips
- 十三、数据库监控：grafana+Prometheus+mysql_exporter
- 十四、主从同步、读写分离
- 一、概括
- 二、主从同步
- 1、配置方式
- 2、总结
- 三、分表分区
- 分表
- 水平分表
- 分区
一、 数据库分类
- 1、关系型数据库：如mysql
- 2、非关系型数据库：如redis
二、 mysql安装及密码的修改
rpm -Uvh http://dev.mysql.com/get/mysql57-community-release-el7-10.noarch.rpm# 安装mysql-server
yum install mysql-community-server -y# 启动数据库
systemctl restart mysqld# 配置开机自启动
systemctl enable mysqld
方法2：使用rpm包安装
速度很块，但是，包有先后顺序的依赖关系
方法3：docker方式安装
速度很快，但是，需要有docker的技能
# 安装dockeryum install -y yum-utils device-mapper-persistent-data lvm2curl -fsSL https://get.docker.com | bash -s docker --mirror Aliyuncurl -sSL # 重启docker systemctl restart docker# 配置开机自启动systemctl enable docker# 安装mysql并配置相关信息docker run -itd --name mysql -p 3337:3306 -e MYSQL_ROOT_PASSOWORD=123456 mysql:5.7
-
2、修改数据库的密码
-
通过
grep "password" /var/log/mysqld.log可以找到密码，但是这个密码是高复杂度的密码，这个密码不好记忆 -
我们想要修改为低复杂度好记忆的密码
-
修改 mysql数据库的配置文件
/etc/my.cnf- 这个路径，是直接安装mysql数据库方式的 数据库的配置文件路径
- 在[mysqld]节点下
[mysqld]validate_password_policy=0 # 设置为 弱密码 validate_password=off # 关闭密码策略character_set_server=utf8 init_connect='SET NAMES utf8'重启动mysql:
systemctl restart mysqld进入mysql客户端
mysql -uroot -p回车# 修改密码alter user 'root'@'localhost' identified by '123456';# 赋权GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY '123456' WITH GRANT OPTION;# 刷新权限FLUSH PRIVILEGES;# 退出exit;mysql的默认端口 3306
-
3、创建库
-
4、创建表
-
三、存储引擎
-
| 存储引擎 | 描述 |
|---|---|
| ARCHIVE | 用于数据存储的引擎，数据被插入后就不能再修改了，且不支持索引，行锁，占用磁盘少；常用语日志系统，大量的设备数据采集 |
| CDV | 在存储数据时，会以逗号作为数据项之间的分隔符；常用语数据库的快速导出导入，表格转换为CSV |
| BLACKHOLE | 会丢弃写操作，该操作会返回空内容 |
| FEDERATED | 将数据存储在远程数据库中，用来访问远程表的存储引擎 |
| InnoDB | 默认，5.5版本后新增，具备外键支持功能的事务处理引擎，事务优先，行锁，支持B树索引 |
| MEMORY | 置于内存的表，速度快，但安全没保障，置于16M，不支持大数据存储类型，表锁；常用语等值查找热度较高的数据 |
| MERGE/MRG_MYISAM | 用来管理由多个MyISAM表构成的表集合 |
| MyISAM | 5.5版本之前默认引擎，主要是非事务处理存储引擎，性能优先，表锁，支持B树，哈希索引 |
| NDB | Mysql集群专用存储引擎 |
-
建立好了表，表就会有多个字段，往里面写入数据。
- 一般不建议在关系型数据库表中，填加过多字段（一般推荐2，30个字段以内）
- 表列建立索引，主键，默认就是索引。
-
四、索引
-
1、简介
- 是一种数据结构，用于帮助我们在大量数据中快速定位我们要查找的问题
- 典型：汉语字典，书籍目录
- 建索引：使用时间换空间，索引有一定大小，占磁盘/内存空间，以此来换取时间更少
- 建立索引的目的，是加快数据查找速度。但在写入数据时，这一操作会使整个文件增大，导致修改速度变慢。
- 但是，在数据库的使用过程中，查找使用频率时远远高于修改频率，所以我们建表一般会建立索引。
-
2、优势和弊端
- 优势
- 1、降低IO,CPU使用率
- 2、索引列，可以保证行的唯一性
- 3、可以有效缩短数据检索时间
- 4、加快表与表之间的连接
- 弊端
- 1、索引本身很大，通常存在磁盘（也可以存在内存）
- 2、不是所有的情况都可以用索引：数据量少\列值频繁变更\列很少使用
- 3、索引会降低增删改效率，但是一般会提升查效率
- 优势
-
3、分类
-
索引分为主键索引（primary key）、唯一索引（unique index）、复合索引
-
1、主键索引： id（有且只有一个）
-
SQL：
create index index_name on table_name(col_name); alter table table_name add index index_name(col_name); -
2、唯一索引： 不可重复，只有NULL可以重复且可以有多个
-
SQL：
create unique index index_name on table_name(col_name)
-
3、复合索引： 多列的名称一起建立一个索引，列的字段是有顺序
-
SQL：
create index index_name on table_name(col_1,col_2......)
- 复合索引，字段顺序非常关键，你在使用时，就一定要按照它的顺序来，如果不是按照它的顺序，那么你就没有使用这个复合索引
- 举例：
- st_student （col1,col2,col3,col4,col5,col6）
- fhindex (col1, col3, col4)
- 顺序：
- col1 --> col3 —col4
- col1 —> col3
- col1
- 顺序：
- sql的where条件，就一定要按照fhindex的顺序
- where条件
- 1: col1=?,col2=? ×
- 2: col1=?,col3=? √
- 3: col1=?, col3=?, col4=? √
- 4: col1=? col4=?, col3=? ×
- 5: col3=?, col4=? ×
- 6: col1=?, col4=? ×
- 7: col1=? √
- 8: col3=? ×
- 9: col4=?, col3=? ×
- where条件
-
-
-
五、 数据库在磁盘的展示
建立了数据库和表之后，在
/var/lib/mysql路径下，就有数据库名称文件夹
- 文件夹中，就有表名称的文件
- Innodb
- ibd文件，这个表是InnoDB存储引擎时， 数据文件
- frm文件，就是表结构文件
- MyISAM
- frm文件，表结构文件
- MYD文件，数据文件
- MYI文件，索引文件
六、B树索引
mysql数据库使用InnoDB存储引擎，默认使用B+树索引
- InnoDB存储引擎支持：B+树索引、全文索引、哈希索引，默认是B+树索引
- 可以理解为二叉树，进行旋转而建立一个立体的二叉树集
-
B+树（balanceTree平衡树）的优点：
- 树的层次更低，IO次数更少
- 每次查询的结果都是在叶子节点，查询性能稳定
- 叶子节点形成链表，范围查询方便
- 注意：与存储过程的区别
- 存储过程：是一组为了完成特定功能的sql语句集合，是一个可编辑的函数
- 存储引擎：数据库文件的存储机制，表的类型
-
七、视图
-
1、概念
- 虚拟表，从一个表或多个表中查询出来的表，作用和真实表一样，包含一系列带有行和列的数据。视图中，用户可以使用SELECT语句查询数据，也可以使用INSERT，UPDATE，DELETE修改记录，视图可以使用户操作方便，并保障数据库系统安全
-
2、作用
- 缩减脚本复杂度，提升性能
-
3、用法
- 比如这张表长这样
- 新建报表：
CREATE VIEW t_access_dataset_view AS SELECT *FROM t_access_dataset WHERE STATUS = "access";（也可以指定具体字段，后面的查询的那个跟普通的sql语句写法一样的） - 关闭该数据库然后重新进入以后在报表那边可以看到
- 打开表
- 后续如果需要在这个的基础上进行过滤，直接查询这个视图就好啦
- 而且如果原表数据更新了，这边的视图也会更新哦，比如原表新增了这个
- 我们刷新下视图
- 比如这张表长这样
八、数据查询解析过程
-
- 查询语句的解析过程：
- from 表
- where 条件 过滤数据 --> 条件，影响性能
- group by 查询结果条件来分组 -->列没有变化
- having <expression> 过滤分组 -->在上一步基础上进行过滤
- select 字段 -->这一步的时候，列的数量才会变化
- order by 字段 --> 排序。如果order by的字段不在目前现有的字段里面，会产生一个临时表，是非常消耗性能的，不推荐（回表查询）
- limit 数量限制
-
九、影响数据库性能的因素总结
- 1、表的存储引擎
- 2、表的字段（字段越多性能越差）
- 3、表的索引
- 4、sql语句，where条件
- 5、sql语句，order by
-
十、数据库的性能优化
- 数据库的优化，两个方向： 操作系统+库的优化， 表+sql的优化
- 数据库是用来存储数据的，必然有服务器磁盘操作，磁盘的读写速度也会影响性能。所以数据库的服务器，一般选择IO性能比较高，空间比较大的磁盘进行数据存储
- 操作系统： 操作系统受打开文件数量（一般企业有个16000够用了）和缓存的限制很大
-
下面我们对常用参数进行说明：
- 1、table_open_cache 数据表缓存
- 数据库缓存有两种：
- 数据库本身的缓存： 库本身 cache、buff
- 缓存数据库： redis
- 数据库本身的缓存大小是可以被配置，配置的越大，数据库要用的内存也就越大
- 数据库缓存有两种：
- 2、max_heap_table_size 是说 memory存储引擎表的大小为多少 默认16M
- 3、slow_query_log 慢查询的开关
- 4、slow_query_log_file 慢查询开关开启启，慢查询脚本会自动写入文件
- 5、long_query_time 慢查询时间的阈值， 当sql的执行时间超过这个设定时间，就是慢查询。单位： 秒
- 如何获取到慢查询脚本？
- 生产环境中，默认是不会写慢查询日志的。
- slow_query_log 这个开关默认是关闭的，就要开启这个开关
- 开关开启之后，日志就根据long_query_time的时间去判断，超过这个时间，那么就会写入slow_query_log_file文件中去。
- 如何获取到慢查询脚本？
-
可以试着查看下（这里不建议前面加
%，这样性能不太好）
- 配置文件：
/etc/my.cnf - 修改配置：
- 1、
set global var_name=var_value; - 2、修改配置文件
- 1、
- 配置文件：
- 1、table_open_cache 数据表缓存
十、慢查询分析
- 慢查询日志
- 慢查询日志，用来记录mysql中执行时间超过指定时间的查询语句，通过这个日志，可以看出哪些语句执行效率低下，需要优化。但是，开启该日志，会影响数据库性能，所以一般关闭
- 慢查询日志，只是用于定义定位慢查询sql，并不是优化sql
- 查看状态
- show variables like ‘slow_query%’;
- show_query_log 慢查询开启状态
- show_query_log_file 慢查询日志存放位置
- show variables like ‘long_query_time’;
- long_query_file 慢查询的阈值，单位秒 阈值 阈值
- show variables like ‘slow_query%’;
- 开始实战
- 首先我们进入
/etc/my.cnf，在mysqld下面然后修改，加上开启慢查询的语句，然后保存，重启数据库(systemctl restart mysqld;)。或者我们可以修改数据库的配置文件set global variable slow_query_log=ON/OFF; set global variable long_query_time=0.2;进行设置
- 然后我们就运行脚本产生慢sql。只有响应时间超过 long_query_time这个设置的时间，才可能出现慢sql
- 在出现慢查询的日志后，我们可以到对应文件去查看（show VARIABLES like ‘%slow_query%’;可以看到对应文件夹）
- 如果出现 Too many connections 适当的调整max_connections
- 常见错误
- mysql：ERROR 1040: Too many connections
- 简单定位：可以拿一个navicat试一下，连不上就是连接数不够用
- 定位：
- show variables like ‘max_connections%’; --查看系统配置的最大连接数
- show global status like ‘Max_used_connections’; --查看当前用户已经建立的连接数
- 性能优化提升
- 1、数据库配置连接数不够，修改max_connections值 set global max_connections=512;或者在配置文件中
/etc/my.cnf修改。但是这边也不会调的过大，比如现在设置是151不够用，我们一般会设置为200，最多调到1000，要不然会导致内存消耗过多，效果也不大。调的时候可以参考innodb_open_file_limit和open_file_limit的数量。或者找运维和开发确认下 - 2、程序性能上不去，修改程序配置文件
- 服务器性能上不去，数据库连接足够用，此时，修改程序中数据库连接配置
- 1、数据库配置连接数不够，修改max_connections值 set global max_connections=512;或者在配置文件中
- 首先我们进入
| 参数 | 用法 |
|---|---|
| initialSize | 初始化连接数 |
| maxActive | 连接池的最大连接数，0表示限制 |
| maxIdle | 最大空闲连接，暂时不断开，随时待命 |
| minLdle | 最小空闲连接 |
| maxWait | 超时等待时间，毫秒；-1表示无限等待，直到超时 |
-
但是这块数据通常是开发写在代码里，我们拿不到，我们只能通过应用服务器和数据服务器之间的网络连接数有多少来进行判断，具体脚本如下：
-
配置文件
-
netstat -ano|grep 数据库端口 |grep ESTABLISHED |wc -l
这个命令，在数据库机器中执行 看数据库当前总共有多少的连接数 -
netstat -ano|grep 数据库端口 |grep 应用程序ip |grep ESTABLISHED |wc -l
这个命令，在数据库机器中执行后，就可以看到应用程序xxx与数据库建立连接数有多少
-
-
-
然后再找开发要上图的配置文件进行对比，看看配置文件的参数配置是否太小，需要适当进行调整
- 分析慢sql，只是数据库表层面的优化的一种。
-
2、数据库表层面的优化
- 数据表
- 建表的时候： 存储引擎选择
- 建表的时候：表字段数量
- 查表方式： 垂直拆表（拆表的字段）、水平拆表（拆表的数据）
- 表字段： 索引数据量比较大时，有索引和没有索引，在查询数据上时是有差异的。
- 视图：一种基于真实表的虚拟表，数据来源是建立在真实表数据之上，可以认为是水平拆表，也可以认为是垂直拆表
create view table_view as select xxx- 是虚拟表
- 基于真实数据
- 能简化sql，提高读速度
- 会占用更多内存
- 数据表
十一、性能分析实战
-
1、我们从之前的慢查询日志里获取一个sql，比如这个：
-
2、sql前面加上explain，这样可以得到下图，我们就可以进行分析了
-
我们来对这些参数进行逐个分析
-
1、id: 编号
- id值不同，大的先被执行；
- id相同的，执行顺序从上往下
- id值不同，大的先被执行；
-
2、select_type: 查询类型，种类有多种
- 前三个重点掌握
- PRIMARY：查询中包含复杂的子查询，最外层的select被标记为PRIMARY
- SUBQUERY：子查询的第一个select
- SIMPLE：简单的SELECT，不使用union或子查询
- UNION：union中第二个或后面的select语句
- DEPENDENT UNION： union中的第二个或后面的select语句，取决于外面的查询
- UNION RESULT: union的结果
- DEPENDENT SUBQUERY：子查询中的第一个select，取决于外面的查询
- DERIVED：衍生查询，使用了临时表(select、from子句的子查询)
- UNCACHEABLE SUBQUERY： 一个子查询的结果不能被缓存，必须重新评估外链接的第一行
-
3、type： 查询的类型，也是有多种值（重点掌握）
- system、const：mysql对查询某部分进行优化，并转换为一个常量时，使用这些类型访问。如将主键置于where列表中，mysql就能将该查询转化为一个常量，system是const类型的特例，当查询的表只有一行的情况下，使用system（比如select type from xxx where id in（select id from table_2 where name=“hhh”); 当子查询语句里只有一条数据的时候，就用的system）
- eq_ref：类似ref，只使用唯一索引作为索引，性能比ref好
- ref：使用了索引列上值进行查询（写的sql语句有一个where条件，where条件使用了某个字段，该字段正好建了多个索引（主键索引，唯一索引，复合索引），查找时候使用到了它，性能比range好）
- range：使用一个索引来检索给定范围的行（就像是使用新华字典比如找“吃”这个字，搜c来进行查找，比较快就找到，性能比index好）
- index：索引，遍历索引数据Full index scan（使用了索引，但是是整个索引（主键索引，唯一索引，复合索引）都找了一遍才找到数据。相当于字典找个字虽然使用了偏旁部首找到了，但是是翻遍了整个偏旁部首才找到）
- ALL : 全表扫描，你要找数据，要全表查找。类似你找个字整页新华字典去翻，耗时长
- NULL: MySQL在优化过程中分解语句，执行时甚至不用访问表或索引
- 性能效率：system > const > eq_ref > ref > range > index > all 左边效率高于右边
-
4、possible_keys：可能用到的索引
-
5、key: 脚本真正用到索引
-
6、key_len：实际使用索引的长度
-
7、ref：表之间的匹配条件
-
8、 rows: 总共查询了多少行（通过索引查询到的数据行数）
-
9、extra：
- using where：显示的字段，不在索引中（select的字段，有的不在索引中，要从源table表中查询）
- Using index： 使用了索引，不用回表查询，能够起到性能提升
- Using temporary： 使用了临时表，性能消耗比较大，常见于group by语句
- Using filesort：使用文件排序，无法利用索引完成排序操作，性能消耗非常大
- Using join buffer：mysql引擎使用了连接缓存
- Impossible where: where子句永远为false
- Select tables optimized away：仅通过使用索引，优化器可能仅从聚合函数结果中返回一行
-
-
根据上面我们可以试着分析下，这里key为空，表示没有索引，我们可以加个索引，这边是根据mobile进行搜索，我们可以把mobile设为索引
-
十二、sql优化tips
- 写on语句时，将数据量小的表放左边，数据量大的放右边
- where后面的字段尽可能使用索引字段，复合索引时，最好按照复合索引顺序写where条件
- where后面有in语句，in字段的索引，建议放在复合索引字段后面，因为in的字段索引可能会失效
- where条件使用like模糊查询， 不要用模糊匹配开头
- where条件多个尽可能不要使用or
- 模糊查询时，尽量使用常量开头，不要用%开头，用%开头查询索引将失效
- 尽量不要使用类型转换（显式，隐式），否则索引失效
- 如果主查询数据量大，则使用in；如果子查询数据量大，则使用exists
- 查询哪些列，就根据哪些列group by，不然会产生一个临时表
-
十三、数据库监控：grafana+Prometheus+mysql_exporter
- 步骤如下：
-
1、下载mysqld_exporter，上传到mysql服务器，然后解压缩
- 下载：
wget https://github.com/prometheus/mysqld_exporter/releases/download/v0.12.1/mysqld_exporter-0.12.1.linux-amd64.tar.gz - 解压缩：
tar -zxvf mysqld_exporter-0.12.1.linux-amd64.tar.gz
- 下载：
-
2、配置my.cnf文件：
/etc/my.cnf保存了以后记得重启数据库：systemctl restart mysqld
-
3、启动mysqld_exporter： 进入该文件夹，然后输入
./mysqld_exporter --config.my-cnf=/etc/my.cnf
接着我们可以浏览器访问：ip:9104 -
4、启动grafana:
systemctl restart grafana -
5、配置Prometheus.yml，添加上线这段
-
mysqld_exporter端口：9104
-
./prometheus启动
-
- 步骤如下：
- job_name: 'mysqld_exporter'
static_configs:
- targets: ['ip:9104']
- 6、浏览器访问，配置模版7362
- Prometheus和grafana配置参考这篇 使用Jmeter进行性能测试及性能监控平台搭建
- Prometheus和grafana配置参考这篇 使用Jmeter进行性能测试及性能监控平台搭建
十四、主从同步、读写分离
-
一、概括
-
主从同步、读写分离====实现 热备份(同时)， 异步
-
分表分区
-
主从同步、读写分离
- 至少两个数据，分别进行读写操作，进行数据变更时候，会自动同步到两个数据中
- 这种方式，可以提升，io利用，从而提供数据写、读速度。
- 关键点： 数据同步
- 数据库的数据操作，其实都是日志操作。
- 企业中，会要求有两个以上的数据库服务器
-
-
二、主从同步
yum install -y yum-utils device-mapper-persistent-data lvm2curl -fsSL https://get.docker.com | bash -s docker --mirror Aliyun# 开机自启动、重启docker systemctl enable docker systemctl restart docker- 安装mysql数据库
Hub · DaoCloud
Docker Hub
# 下载镜像 docker pull daocloud.io/library/mysql:5.7.7docker run -itd --name mysq577_master -p 3336:3306 -e MYSQL_ROOT_PASSWORD=123456 daocloud.io/library/mysql:5.7.7docker run -itd --name mysq577_slave -p 3346:3306 -e MYSQL_ROOT_PASSWORD=123456 daocloud.io/library/mysql:5.7.7-
定义主数据库、从数据库
-
找到数据库的配置文件，修改配置文件
-
配置文件路径： /etc/mysql/my.cnf
-
修改
# 从容器中，拷贝文件到宿主机 docker cp mysq577_master:/etc/mysql/my.cnf $PWD# 修改配置文件 [mysqld] server-id = 100 log-bin = mysql-bin# server-id值得大小来区分主、从数据库，越小的为主数据库，越大的为从数据# 把修改后的，拷贝到容器中 docker cp my.cnf mysq577_master:/etc/mysql/my.cnf# 重启动容器 -
两个数据库之间要建立关联关系
# 从数据库中执行 CHANGE MASTER TO MASTER_HOST='192.168.2.42', MASTER_PORT=3336, MASTER_USER='root', MASTER_PASSWORD='123456';start slave;show slave status; -- 查看slave的状态- 重点关注：
- Slave_IO_Runing 必须为 Yes
- Slave_SQL_Runing 必须为 Yes
- 重点关注：
-
安装完成以后，我们就可以看到主数据库这里有个mysql-bin文件，这是一个二进制文件
从数据库也有
-
三、分表分区
![[论文笔记]Prefix Tuning](https://img-blog.csdnimg.cn/img_convert/0d307fd4adbc77ebb663456f611696e1.png)
