数据库压力测试与索引实验、备份与日志实验

一、     压力测试与索引实验（注意提前备份好机器上的关键数据，以免因实验而意外损失）

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语句

查看日志

这是一条插入语句的日志内容

以第一条日志内容为例

1. 时间戳：表示该语句执行的时间
2. 线程ID：表示执行该语句的MySql线程ID
3. 查询类型：这里的Query表示这是一条查询语句
4. 查询语句：这里包含了一条sql语句。