一、 压力测试与索引实验(注意提前备份好机器上的关键数据,以免因实验而意外损失)
1) 针对你的数据库中的一个表,编写简单的数据查询(查询语句应包括单个涉及非主属性等值比较的查询条件,设该非主属性为A,具体属性结合业务背景)和数据插入语句,程序应能在终端或服务器以文件形式记录每次数据读写操作的耗时。
数据插入:
import pymysql
import time
db=pymysql.connect(host='172.24.xx.xx',user='root'
,passwd='xxxxxxxx'
,port= 3306
,db= '12306')
cur = db.cursor()
insertnum=0
a=1
pertime=0
while insertnum<1000000: #共插入1000000条数据
begin=time.perf_counter()
sql="call insertusr({0},10000)".format(a) #每10000条数据共同打包插入,耗时1-2秒左右
cur.execute(sql)
db.commit()
end=time.perf_counter()
totaltime=end-begin
with open('insert.txt','a') as f: #将每次插入所耗时间写入文件中
f.write(f"{totaltime}s\n")
insertnum=insertnum+10000
a=a+10000
数据查询:
import pymysql
import time
db=pymysql.connect(host='172.24.xx.xx',user='root'
,passwd='xxxxxxxx'
,port= 3306
,db= '12306')
cur = db.cursor()
times=0
while times<=101: #查询101次,1秒一次
begin=time.perf_counter()
sql="select * from 用户 where 登录密码='123456100'"
cur.execute(sql)
data = cur.fetchall()
print(data)
db.commit()
end=time.perf_counter()
totaltime=end-begin
with open('select.txt','a') as f: #将每次查询所耗时间写入文件
f.write(f"{totaltime}s\n")
times=times+1
time.sleep(1)
存储过程:
CREATE DEFINER=`root`@`%` PROCEDURE `insertusr`(IN init int, IN times int)
BEGIN
DECLARE i INT;
DECLARE tID INT;
SET i = 0;
SET tID = init;
WHILE i < times DO
INSERT INTO `用户`
VALUES (CONCAT('USER',tID),CONCAT('123456',tID));
SET tID = tID + 1;
SET i = i + 1;
END WHILE;
SET AUTOCOMMIT=0;
END
2) 无索引压力测试实验:设定该表的数据插入频率(如100条/秒),至少有1台以上终端以并发的形式插入数据,运行一段时间使数据达到百万条级以上,数据规模上限自定。同时使用1台终端每秒并发执行一次查询操作,要求查询条件不包含主码,且不存在针对属性A建立的非聚集索引。
设定插入频率为每次打包插入10000条数据,即关闭AUTOCOMMIT,每次耗时1-2秒左右,直至插入1000000条数据。同时对数据进行并发查询,查询对象为非主属性“用户密码”,且不存在针对其建立的非聚集索引。
图 1 插入耗时-before
图 2 查询耗时-before
3) 有索引压力测试:清空原有数据,实验内容同上,但除主键索引外,需要针对属性A建立非聚集索引,重新执行一轮压力测试。
基本条件同上,对“用户密码属性”建立非聚集索引,重新进行一轮测试
图 3 建立非聚集索引
图 4 插入耗时-after
图 5 查询耗时-after
4) 分析实验数据,制作图表,观察同一方案插入时间与查询时间变化,分析实验结果原因。
使用matplotlib制作折线图
图 6 未使用索引
可见,在未使用索引时,随着数据量的增长,插入以及查询都有一定幅度的增长。这是因为在未使用非聚集索引的情况下,向表中插入数据需要遍历整个表从而找到一个顺序合适的位置插入;向表中查询也需要遍历整个表从而找到符合目标的行数据。随着数据量的增大,遍历整张表所需的时间也会随之增加。
图 7 使用索引
在使用了非聚集索引的情况下,插入数据所耗时间依旧会随着数据量的增大而增加,而数据查询耗时则没有明显增加。这是因为在有索引的情况下就不需要再遍历整张表了。
5) 分析实验数据,制作图表,比较相同资源条件下不同方案同一指标随时间的变化,分析实验结果原因。
图 8 插入操作对比
由图可见,使用了非聚集索引后的插入操作耗时有所上升,这是因为插入数据会改变平衡树各节点中的索引数据内容,破坏树结构。 因此,在每次数据改变时, DBMS都必须去重新梳理树的结构,这是耗时的主要原因。
图 9 查询操作对比
可见。在使用了非聚集索引之后查询耗时有了明显的下降。这是因为使用索引之后的查询操作就不需要再遍历整张表了,这使得查询耗时得到明显的优化。
matplotlib:
import matplotlib.pyplot as plt
with open("select0.txt", "r") as file1:
data1 = file1.readlines()
with open("select.txt", "r") as file2:
data2 = file2.readlines()
x1 = []
y1 = []
for line in data1:
time_str = line.strip().replace('s', '')
time = float(time_str)
x1.append((len(x1) + 1)*10000)
y1.append(time)
x2 = []
y2 = []
for line in data2:
time_str = line.strip().replace('s', '')
time = float(time_str)
x2.append((len(x2) + 1)*10000)
y2.append(time)
plt.plot(x1, y1, label='before')
plt.plot(x2, y2, label='after')
plt.xlabel('nums')
plt.ylabel('time:second')
plt.legend()
plt.show()
二、 备份与日志初步实验
1) 了解你所使用的数据库平台的单表数据备份和整库备份方法,进行相应备份操作,并尝试利用备份数据在另一个机器上恢复数据,并在实验报告中描述上述过程。
单表数据备份:
将sql文件通过scp传递到另一台机器上
使用source语句进行备份恢复
可见单表已经成功进行了恢复
整库备份:
这里为了方便就备份了另一个数据库hw1,其中包含phonenumber和test两个表
可见整库备份只能恢复到当前数据库中,不能自动创建一个新的数据库,需要自行先创建一个数据库,进入该数据库中使用source语句进行恢复。
2) 学习掌握数据库日志的概念,并说明数据备份、日志与故障恢复之间的关系。
数据库日志是一种记录数据库操作的机制,它可以记录数据库中的所有修改操作,包括插入、更新和删除等操作,并将这些操作记录在一个专门的日志文件中。数据库日志可以用于数据备份、日志和故障恢复。
数据备份:
数据库日志可以帮助用户创建数据备份,以便在数据库故障时恢复数据。在每次修改数据库时,MySQL都会将这些修改操作记录在日志文件中。如果需要备份数据,可以使用备份工具或手动复制日志文件来创建备份。用户可以使用备份来恢复数据,以避免因为数据库故障而丢失数据。
日志:
数据库日志还可以用于跟踪数据库操作的历史记录。通过查看日志文件,用户可以了解所有修改操作的详细信息,包括操作时间、操作人员、操作类型和操作数据等。这些信息可以用于审计和安全管理等方面。
故障恢复:
数据库日志可以帮助用户在数据库故障时恢复数据。如果数据库出现故障,用户可以使用数据库日志中记录的修改操作,将数据库恢复到故障发生之前的状态。具体来说,用户可以使用数据库日志来重放修改操作,以将数据库恢复到故障发生之前的状态。这种故障恢复的过程称为“回滚”。
3) 查阅资料,在你所使用的平台的数据库中找到能记录数据修改操作的日志文件,针对某个表执行插入或修改操作,请在相应日志文件中找对应的插入或修改操作日志记录,至少解释其中的一条日志数据样例。
在/etc/mysql/mysql.conf.d/mysqld.cnf中添加general日志路径
重启mysql
执行一条sql语句
查看日志
这是一条插入语句的日志内容
以第一条日志内容为例
- 时间戳:表示该语句执行的时间
- 线程ID:表示执行该语句的MySql线程ID
- 查询类型:这里的Query表示这是一条查询语句
- 查询语句:这里包含了一条sql语句。