数据库压力测试

一、 存储过程和触发器实验

1. 请在你选用的数据库平台上，针对你的应用场景，对如下操作至少各实现一个存储过程：
   1）单表或多表查询 2）数据插入 3）数据删除 4）数据修改
   1）单表查询，结果如下：

–查询u_id大于输入的num的
drop procedure if exists sl;
– 如果存在就drop
delimiter //
– 定义为碰到//才表示语句结束
create procedure sl(num int)
begin
select * from User_tbl where u_id > num;
end //
delimiter ;
– 定义为碰到;表示语句结束
call sl(5);
多表查询结果如下：
查找u_id为输入的n的用户的朋友圈内容和他的真实姓名

drop procedure if exists lo;
– 如果存在就drop
delimiter //
create procedure lo(n int)
begin
select p_content,u_truename from User_tbl,Circlemsg_tbl
where user_tbl.u_id=n
and circlemsg_tbl.p_uid=n;
end //
delimiter ;
call lo(6);
2)数据插入
drop procedure if exists sp1;
– 如果存在就drop
delimiter //
– 定义为碰到//才表示语句结束
create procedure sp1(in account varchar(255),in password varchar(255),in name varchar(255),in tname varchar(255),in sex varchar(255))
begin
insert into user_tbl(u_account,u_password,u_registtime,u_lock,u_style,u_username,u_truename,u_sex,u_location,u_face,u_intro) values(account,password,now(), 0, ‘a’, name, tname, sex, ‘null’, ‘null’, ‘null’);
select * from user_tbl;
end //
delimiter ;
call sp1(‘www’,‘32ewew’,‘wan’,‘dongyu’,‘男’);

3）数据删除，结果如下：

– 数据删除
drop procedure if exists del;
– 如果存在就drop
delimiter //
– 定义为碰到//才表示语句结束
create procedure del(id int)
begin
delete from Circlemsg_tbl where p_id = id;
select * from Circlemsg_tbl;
end //
delimiter ;
call del(3);
– 定义为碰到;表示语句结束
call del();
– 定义为碰到;表示语句结束
4）数据修改，结果如下：

drop procedure if exists up;
– 如果存在就drop
delimiter //
– 定义为碰到//才表示语句结束
create procedure up(name varchar(255),psd varchar(255))
begin
UPDATE user_tbl SET u_password = psd WHERE u_username = name;
select * from user_tbl;
end //
delimiter ;
call up(‘wan’,‘11111’);
– 定义为碰到;表示语句结束
2．通过ODBC、OLEDB、JDBC或任意其他的途径，在前端程序（C/S或B/S模式）中调用所实现的后台存储过程。
我使用的是JDBC实现在前端程序调用数据库后台存储过程，代码过长，放入压缩包中。（网页采用的是Jquery架构），网页整体页面如下：

1. 数据查询：（输入用户名，点击查询按钮即可查询到数据库中该用户的信息并在页面显示出来），结果如下图：

2. 数据插入操作 插入用户信息，点击页面的添加按钮，在动态弹出的表单中输入用户信息，点击确认，即可完成添加，添加结果自动返回显示，操作界面如下图：

3. 数据删除操作 点击每个用户栏对应的最右边的删除链接，即可实现对该用户的删除操作，删除完成后自动显示当前数据库的状态结果。如下图所示：

4. 数据修改操作，点击每个用户栏对应的最右边的修改密码链接，跳转到修改密码的页面，输入新的密码，即可实现对该用户的密码修改操作，如下图所示：

跳转：

3．在你的案例场景中，分别设计并实现一个由数据插入、数据更新、数据删除所引发的触发器（前触发或后触发都可以），测试触发器执行效果。
–创建记录微信用户的表wechat_count，默认为0
CREATE TABLE wechat_count (
wechat_count INT(11) DEFAULT 0
);
INSERT INTO wechat_count VALUES(0);
select * from wechat_count;

1） 数据插入
–创建简单触发器，在向wechat_tbl表INSERT数据时，wechat_count增加
CREATE TRIGGER trigger_insert
AFTER INSERT
ON user_tbl FOR EACH ROW
UPDATE wechat_count SET wechat_count=wechat_count+1;

--验证触发器，插入一个同学信息，wechat_count应该增加1个

call sp1(‘weqqw’,‘3121w’,‘anan’,‘pengyuyan’,‘男’);;
select * from wechat_count;
结果如下：

2） 数据更新
–数据更新
–创建简单触发器，在向wechat_tbl表UPDATA数据时，
–创建记录数据更新技术的updata_count
CREATE TABLE updata_count (
updata_count INT(11) DEFAULT 0
);
INSERT INTO updata_count VALUES(0);
select * from updata_count;

CREATE TRIGGER trigger_updata
AFTER UPDATE
ON user_tbl FOR EACH ROW
UPDATE updata_count SET updata_count=updata_count+1;

–验证触发器
delimiter ;
call up(‘2wed’,‘111121311’);
select * from user_tbl;
select * from updata_count;

3） 数据删除
–数据删除
–创建简单触发器，在向wechat_tbl表DELETE数据时，wechat_count减少
CREATE TRIGGER trigger_delete
AFTER DELETE
ON user_tbl FOR EACH ROW
UPDATE wechat_count SET wechat_count=wechat_count-1;
DELETE FROM user_tbl WHERE u_id=11;
select * from wechat_count;

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

1. 结合作业#3，针对你的数据库中的一个表，编写简单的数据查询（查询语句应包括单个涉及非主属性等值比较的查询条件，设该非主属性为A，具体属性结合业务背景）和数据插入语句，程序应能在终端或服务器以文件形式记录每次数据读写操作的耗时。
2. 无索引压力测试实验：设定该表的数据插入频率（如100条/秒），至少有1台以上终端以并发的形式插入数据，运行一段时间使数据达到百万条级以上，数据规模上限自定。同时使用1台终端每秒并发执行一次查询操作，要求查询条件不包含主码，且不存在针对属性A建立的非聚集索引。
3. 有索引压力测试：清空原有数据，实验内容同上，但除主键索引外，需要针对属性A建立非聚集索引，重新执行一轮压力测试。
4. 选做：加大数据插入与查询频率，观察你的服务器和不同设计方案的压力承受能力。注意控制规模以免导致硬盘工作空间溢出。
5. 分析实验数据，制作图表，比较有索引和无索引的情况下，插入时间与查询时间随数据量增加的变化情况，以及在相同资源条件下比较有索引和无索引的情况下同一指标随数据量增加的变化情况，分析导致实验结果的原因。
   实验过程如下：
   1）

1. 首先，选取之前建立的数据库，选择wechat_tbl表（也就是用户表）
2. 编写简单的查询语句为：select*from wechat_tbl where wechat_password=‘oqXKaJ’（查询密码为oqXKaJ的用户），其中wechat_password为非主属性满足题目要求。
3. 数据插入语句我选择由java程序实现，设计程序语言读取记录每次查询和插入所需要的时间，并且能够将结果存入txt文件中。程序运行结果如下图所示：（代码已放入压缩包中）

2）无索引压力测试实验：

1. 在代码中通过Thread.sleep(1000)控制插入频率和查询频率（题目要求为插入频率100条/秒，查询频率为1/秒）

2. 查询所插入和查询的表中存在的索引，确定不存在针对wechat_password建立的非聚集索引，查询结果如下图所示：

3. 将写好的java运行一段时间使数据达到百万条级以上（在java内通过循环实现）,数据上限我设的是100万条，运行写好的程序，将每秒每插入100条数据的时间以及查询一次的时间记录到文本中（文本文件已放入压缩包），对这10000条数据使用Excel 对每100条数据求平均值，进行拟合得出随数据增加插入时间变化图表以及查询时间变化图表如下：

3） 有索引压力测试：

1. 清空原有数据，实验内容同上，wechat_password建立非聚集索引，重新执行一轮压力测试，过程如下图所示：

可见数据量较大时，创建索引的时间也比较长；

上图为删掉增加的100万条数据；

重新开始运行程序
2. 数据上限依然设的是100万条，运行写好的程序，将每秒每插入100条数据的时间以及查询一次的时间记录到文本中（文本文件已放入压缩包），对这10000条数据使用Excel 对每100条数据求平均值，进行拟合得出随数据增加插入时间变化图表以及查询时间变化图表如下：

4） 分析实验数据：

1. 有索引和无索引的情况下，插入时间与查询时间随数据量增加的变化情况对比图在上方展示，由此分析：
   无索引的情况下：
   首先，插入时间每插入100条数据，所需要的时间都大致在170ms左右波动，并且随数据量的增加，由图所示趋势线可知在不断增长。
   其次，数据库在多于100万条数据的情况下，查询时间由开始的1ms，到100万条数据时的400ms，查询所需时间随插入数据量的增加，查询所需时间呈增长趋势，大致平均每10万条数据查询所需要的时间就要增加40ms。
   有索引的情况下：
   首先，相比无索引的情况，插入数据时间大致在270Ms左右，随数据量的增加，由图所示趋势线可知也在不断增长。每次查询的时间大大减少，100万数据时查询时间由之前的无索引状态下的400ms变成现在的1ms左右，其查询速度显著提高，并且随着插入数据的增加，查询时间并没有明显的增加，一直保持在1Ms左右，可见加入索引之后大大提高了查询的速度。

2. 相同数据量情况下
   经统计插入一百万条数据无索引一共需要1765578ms，也就是1765.578s，而有索引情况下插入一百万条数据需要3260311ms，也就是3260.311s，比较发现二者时间差接近两倍。
   有索引在相同数据量情况下的查询时间在数据量少时与无索引查询时间相差不大，但是在数据量大时，产生了较大的差异，无索引查询时间呈线性增长趋势，而有索引并无增长趋势，而控制在一个常数范围。
   另外，有索引的插入速度在相同数据量情况下，每插入100条数据比无索引慢了大概100ms左右，且随数据量的增加，差异值一直控制在这个范围内，没有明显增长。

3. 实验结果分析：
   有上面结果可知，添加索引后使查询速度增加了，却使插入速度降低了，索引能让数据库查询数据的速度上升， 而使写入数据的速度下降，原因很简单的， 因为平衡树这个结构必须一直维持在一个正确的状态， 增删改数据都会改变平衡树各节点中的索引数据内容，破坏树结构， 因此，在每次数据改变时， DBMS必须去重新梳理树（索引）的结构以确保它的正确，这会带来不小的性能开销，也就是为什么索引会给查询以外的操作带来副作用的原因。
   对于用到的非聚簇索引，除了聚集索引以外的索引都是非聚集索引，细分一下非聚集索引，分成普通索引，唯一索引，全文索引。非聚集索引和聚集索引一样， 同样是采用平衡树作为索引的数据结构。索引树结构中各节点的值来自于表中的索引字段， 假如给wechat_name表的wechat_password字段加上索引 ，那么索引就是由wechat_password字段中的值构成，在数据改变时， DBMS需要一直维护索引结构的正确性。如果给表中多个字段加上索引 ， 那么就会出现多个独立的索引结构，每个索引（非聚集索引）互相之间不存在关联。非聚集索引和聚集索引的区别在于， 通过聚集索引可以查到需要查找的数据， 而通过非聚集索引可以查到记录对应的主键值 ， 再使用主键的值通过聚集索引查找到需要的数据。
   每次给字段建一个新索引， 字段中的数据就会被复制一份出来， 用于生成索引。 因此，给表添加索引，会增加表的体积，占用磁盘存储空间。这也就解释了实验中添加索引后插入速度降低的原因。
   经过本次实验，在以后创建表格时，尽量保证将主要的数据都插入之后，再添加索引，避免在添加索引之后进行数据插入，以保证效率最高。
   附录：（压力测试java代码）

package testmysql;
import java.io.File;
import java.io.FileWriter;
import java.io.IOException;
import java.io.Writer;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.Date;
import java.util.Random;

public class TestMysql {
static int k;
public static void main(String[] arg){
Connection conn;
PreparedStatement stmt;
String driver = “com.mysql.jdbc.Driver”;
String url = “jdbc:mysql://localhost:3306/wechat?useSSL=false&serverTimezone=Asia/Shanghai”;
String user = “root”;
String password = null;
String sql = “insert into wechat_tbl values (?,?,?,?,?,?,?)”;
String sql2 = “select*from wechat_tbl where wechat_password=‘oqXKaJ’”;
k=101;
Boolean result = false;
File file =new File(“D:\soft\2.txt”);
try{
Writer out =new FileWriter(file);
int count = 0;
while(!result) {

	try{
		Class.forName(driver);
		
		conn = DriverManager.getConnection(url, user, password);
		stmt = (PreparedStatement) conn.prepareStatement(sql);
		Thread.sleep(1000); //设置暂停的时间  1秒
		
		count ++ ;
		System.out.println(new Date() + "--循环执行第" + count + "次");
		long saa=0;
		for(int i=0;i<100;i++){
		stmt.setInt(1, i+k);
		stmt.setString(2, TestMysql.getRandomString(7));
		stmt.setString(3, TestMysql.getRandomString(5));
		if(i%2==0){
		stmt.setString(4, "boy");
		}
		else
		{
			stmt.setString(4, "girl");
		}
		stmt.setString(5, TestMysql.getRandomString(6));
		stmt.setString(6, "china");
		stmt.setString(7, TestMysql.getRandomString(9));
		long spp =  System.currentTimeMillis();
		stmt.executeUpdate();
		long sqq =  System.currentTimeMillis();
		saa +=  sqq-spp;
		}
		k=k+101;
	long midTime =  System.currentTimeMillis();
	stmt = (PreparedStatement) conn.prepareStatement(sql2);
	stmt.executeQuery();
	long endTime =System.currentTimeMillis();
	long usedTime2 = (endTime-midTime);	
	System.out.println(saa+"ms  "+usedTime2+"ms");
	out.write(saa+"ms  "+usedTime2+"ms\n");
	if (count == 10000) {
		result = true;
		out.close();
		break ;
	}
	}
	catch (InterruptedException e) {
		e.printStackTrace();
	 
	} catch (ClassNotFoundException e) {
		// TODO 自动生成的 catch 块
		e.printStackTrace();
	} catch (SQLException e) {
		// TODO 自动生成的 catch 块
		e.printStackTrace();
	}
	
	}
	}
	catch(IOException e){
	      e.printStackTrace();
	     }
	}
public static String getRandomString(int length) {
	Random random = new Random();
	StringBuffer sb = new StringBuffer();
	for (int i = 0; i < length; i++) {
		int number = random.nextInt(3);
		long result = 0;
		switch (number) {
		case 0:
			result = Math.round(Math.random() * 25 + 65);
			sb.append(String.valueOf((char) result));
			break;
		case 1:
			result = Math.round(Math.random() * 25 + 97);
			sb.append(String.valueOf((char) result));
			break;
		case 2:
			sb.append(String.valueOf(new Random().nextInt(10)));
			break;
		}
	}
	return sb.toString();
}