数据库压力测试
一、 存儲(chǔ)過程和觸發(fā)器實(shí)驗(yàn)
1）單表或多表查詢 2）數(shù)據(jù)插入 3）數(shù)據(jù)刪除 4）數(shù)據(jù)修改
1）單表查詢，結(jié)果如下：
–查詢u_id大于輸入的num的
drop procedure if exists sl;
– 如果存在就drop
delimiter //
– 定義為碰到//才表示語句結(jié)束
create procedure sl(num int)
begin
select * from User_tbl where u_id > num;
end //
delimiter ;
– 定義為碰到;表示語句結(jié)束
call sl(5);
多表查詢結(jié)果如下：
查找u_id為輸入的n的用戶的朋友圈內(nèi)容和他的真實(shí)姓名
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)數(shù)據(jù)插入
drop procedure if exists sp1;
– 如果存在就drop
delimiter //
– 定義為碰到//才表示語句結(jié)束
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(chǎn)’, name, tname, sex, ‘null’, ‘null’, ‘null’);
select * from user_tbl;
end //
delimiter ;
call sp1(‘www’,‘32ewew’,‘wan’,‘dongyu’,‘男’);
3）數(shù)據(jù)刪除，結(jié)果如下：
– 數(shù)據(jù)刪除
drop procedure if exists del;
– 如果存在就drop
delimiter //
– 定義為碰到//才表示語句結(jié)束
create procedure del(id int)
begin
delete from Circlemsg_tbl where p_id = id;
select * from Circlemsg_tbl;
end //
delimiter ;
call del(3);
– 定義為碰到;表示語句結(jié)束
call del();
– 定義為碰到;表示語句結(jié)束
4）數(shù)據(jù)修改，結(jié)果如下：
drop procedure if exists up;
– 如果存在就drop
delimiter //
– 定義為碰到//才表示語句結(jié)束
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’);
– 定義為碰到;表示語句結(jié)束
2．通過ODBC、OLEDB、JDBC或任意其他的途徑，在前端程序（C/S或B/S模式）中調(diào)用所實(shí)現(xiàn)的后臺(tái)存儲(chǔ)過程。
我使用的是JDBC實(shí)現(xiàn)在前端程序調(diào)用數(shù)據(jù)庫(kù)后臺(tái)存儲(chǔ)過程，代碼過長(zhǎng)，放入壓縮包中。（網(wǎng)頁(yè)采用的是Jquery架構(gòu)），網(wǎng)頁(yè)整體頁(yè)面如下：
數(shù)據(jù)查詢：（輸入用戶名，點(diǎn)擊查詢按鈕即可查詢到數(shù)據(jù)庫(kù)中該用戶的信息并在頁(yè)面顯示出來），結(jié)果如下圖：
數(shù)據(jù)插入操作 插入用戶信息，點(diǎn)擊頁(yè)面的添加按鈕，在動(dòng)態(tài)彈出的表單中輸入用戶信息，點(diǎn)擊確認(rèn)，即可完成添加，添加結(jié)果自動(dòng)返回顯示，操作界面如下圖：
數(shù)據(jù)刪除操作 點(diǎn)擊每個(gè)用戶欄對(duì)應(yīng)的最右邊的刪除鏈接，即可實(shí)現(xiàn)對(duì)該用戶的刪除操作，刪除完成后自動(dòng)顯示當(dāng)前數(shù)據(jù)庫(kù)的狀態(tài)結(jié)果。如下圖所示：
數(shù)據(jù)修改操作，點(diǎn)擊每個(gè)用戶欄對(duì)應(yīng)的最右邊的修改密碼鏈接，跳轉(zhuǎn)到修改密碼的頁(yè)面，輸入新的密碼，即可實(shí)現(xiàn)對(duì)該用戶的密碼修改操作，如下圖所示：
跳轉(zhuǎn)：
3．在你的案例場(chǎng)景中，分別設(shè)計(jì)并實(shí)現(xiàn)一個(gè)由數(shù)據(jù)插入、數(shù)據(jù)更新、數(shù)據(jù)刪除所引發(fā)的觸發(fā)器（前觸發(fā)或后觸發(fā)都可以），測(cè)試觸發(fā)器執(zhí)行效果。
–創(chuàng)建記錄微信用戶的表wechat_count，默認(rèn)為0
CREATE TABLE wechat_count (
wechat_count INT(11) DEFAULT 0
);
INSERT INTO wechat_count VALUES(0);
select * from wechat_count;
1） 數(shù)據(jù)插入
–創(chuàng)建簡(jiǎn)單觸發(fā)器，在向wechat_tbl表INSERT數(shù)據(jù)時(shí)，wechat_count增加
CREATE TRIGGER trigger_insert
AFTER INSERT
ON user_tbl FOR EACH ROW
UPDATE wechat_count SET wechat_count=wechat_count+1;
call sp1(‘weqqw’,‘3121w’,‘a(chǎn)nan’,‘pengyuyan’,‘男’);;
select * from wechat_count;
結(jié)果如下：
2） 數(shù)據(jù)更新
–數(shù)據(jù)更新
–創(chuàng)建簡(jiǎn)單觸發(fā)器，在向wechat_tbl表UPDATA數(shù)據(jù)時(shí)，
–創(chuàng)建記錄數(shù)據(jù)更新技術(shù)的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;
–驗(yàn)證觸發(fā)器
delimiter ;
call up(‘2wed’,‘111121311’);
select * from user_tbl;
select * from updata_count;
3） 數(shù)據(jù)刪除
–數(shù)據(jù)刪除
–創(chuàng)建簡(jiǎn)單觸發(fā)器，在向wechat_tbl表DELETE數(shù)據(jù)時(shí)，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;
二、 壓力測(cè)試與索引實(shí)驗(yàn)（注意提前備份好機(jī)器上的關(guān)鍵數(shù)據(jù)，以免因?qū)嶒?yàn)而意外損失）
實(shí)驗(yàn)過程如下：
1）
2）無索引壓力測(cè)試實(shí)驗(yàn)：
在代碼中通過Thread.sleep(1000)控制插入頻率和查詢頻率（題目要求為插入頻率100條/秒，查詢頻率為1/秒）
查詢所插入和查詢的表中存在的索引，確定不存在針對(duì)wechat_password建立的非聚集索引，查詢結(jié)果如下圖所示：
將寫好的java運(yùn)行一段時(shí)間使數(shù)據(jù)達(dá)到百萬條級(jí)以上（在java內(nèi)通過循環(huán)實(shí)現(xiàn)）,數(shù)據(jù)上限我設(shè)的是100萬條，運(yùn)行寫好的程序，將每秒每插入100條數(shù)據(jù)的時(shí)間以及查詢一次的時(shí)間記錄到文本中（文本文件已放入壓縮包），對(duì)這10000條數(shù)據(jù)使用Excel 對(duì)每100條數(shù)據(jù)求平均值，進(jìn)行擬合得出隨數(shù)據(jù)增加插入時(shí)間變化圖表以及查詢時(shí)間變化圖表如下：
3） 有索引壓力測(cè)試：
可見數(shù)據(jù)量較大時(shí)，創(chuàng)建索引的時(shí)間也比較長(zhǎng)；
上圖為刪掉增加的100萬條數(shù)據(jù)；
重新開始運(yùn)行程序
2. 數(shù)據(jù)上限依然設(shè)的是100萬條，運(yùn)行寫好的程序，將每秒每插入100條數(shù)據(jù)的時(shí)間以及查詢一次的時(shí)間記錄到文本中（文本文件已放入壓縮包），對(duì)這10000條數(shù)據(jù)使用Excel 對(duì)每100條數(shù)據(jù)求平均值，進(jìn)行擬合得出隨數(shù)據(jù)增加插入時(shí)間變化圖表以及查詢時(shí)間變化圖表如下：
4） 分析實(shí)驗(yàn)數(shù)據(jù)：
有索引和無索引的情況下，插入時(shí)間與查詢時(shí)間隨數(shù)據(jù)量增加的變化情況對(duì)比圖在上方展示，由此分析：
無索引的情況下：
首先，插入時(shí)間每插入100條數(shù)據(jù)，所需要的時(shí)間都大致在170ms左右波動(dòng)，并且隨數(shù)據(jù)量的增加，由圖所示趨勢(shì)線可知在不斷增長(zhǎng)。
其次，數(shù)據(jù)庫(kù)在多于100萬條數(shù)據(jù)的情況下，查詢時(shí)間由開始的1ms，到100萬條數(shù)據(jù)時(shí)的400ms，查詢所需時(shí)間隨插入數(shù)據(jù)量的增加，查詢所需時(shí)間呈增長(zhǎng)趨勢(shì)，大致平均每10萬條數(shù)據(jù)查詢所需要的時(shí)間就要增加40ms。
有索引的情況下：
首先，相比無索引的情況，插入數(shù)據(jù)時(shí)間大致在270Ms左右，隨數(shù)據(jù)量的增加，由圖所示趨勢(shì)線可知也在不斷增長(zhǎng)。每次查詢的時(shí)間大大減少，100萬數(shù)據(jù)時(shí)查詢時(shí)間由之前的無索引狀態(tài)下的400ms變成現(xiàn)在的1ms左右，其查詢速度顯著提高，并且隨著插入數(shù)據(jù)的增加，查詢時(shí)間并沒有明顯的增加，一直保持在1Ms左右，可見加入索引之后大大提高了查詢的速度。
相同數(shù)據(jù)量情況下
經(jīng)統(tǒng)計(jì)插入一百萬條數(shù)據(jù)無索引一共需要1765578ms，也就是1765.578s，而有索引情況下插入一百萬條數(shù)據(jù)需要3260311ms，也就是3260.311s，比較發(fā)現(xiàn)二者時(shí)間差接近兩倍。
有索引在相同數(shù)據(jù)量情況下的查詢時(shí)間在數(shù)據(jù)量少時(shí)與無索引查詢時(shí)間相差不大，但是在數(shù)據(jù)量大時(shí)，產(chǎn)生了較大的差異，無索引查詢時(shí)間呈線性增長(zhǎng)趨勢(shì)，而有索引并無增長(zhǎng)趨勢(shì)，而控制在一個(gè)常數(shù)范圍。
另外，有索引的插入速度在相同數(shù)據(jù)量情況下，每插入100條數(shù)據(jù)比無索引慢了大概100ms左右，且隨數(shù)據(jù)量的增加，差異值一直控制在這個(gè)范圍內(nèi)，沒有明顯增長(zhǎng)。
實(shí)驗(yàn)結(jié)果分析：
有上面結(jié)果可知，添加索引后使查詢速度增加了，卻使插入速度降低了，索引能讓數(shù)據(jù)庫(kù)查詢數(shù)據(jù)的速度上升， 而使寫入數(shù)據(jù)的速度下降，原因很簡(jiǎn)單的， 因?yàn)槠胶鈽溥@個(gè)結(jié)構(gòu)必須一直維持在一個(gè)正確的狀態(tài)， 增刪改數(shù)據(jù)都會(huì)改變平衡樹各節(jié)點(diǎn)中的索引數(shù)據(jù)內(nèi)容，破壞樹結(jié)構(gòu)， 因此，在每次數(shù)據(jù)改變時(shí)， DBMS必須去重新梳理樹（索引）的結(jié)構(gòu)以確保它的正確，這會(huì)帶來不小的性能開銷，也就是為什么索引會(huì)給查詢以外的操作帶來副作用的原因。
對(duì)于用到的非聚簇索引，除了聚集索引以外的索引都是非聚集索引，細(xì)分一下非聚集索引，分成普通索引，唯一索引，全文索引。非聚集索引和聚集索引一樣， 同樣是采用平衡樹作為索引的數(shù)據(jù)結(jié)構(gòu)。索引樹結(jié)構(gòu)中各節(jié)點(diǎn)的值來自于表中的索引字段， 假如給wechat_name表的wechat_password字段加上索引 ，那么索引就是由wechat_password字段中的值構(gòu)成，在數(shù)據(jù)改變時(shí)， DBMS需要一直維護(hù)索引結(jié)構(gòu)的正確性。如果給表中多個(gè)字段加上索引 ， 那么就會(huì)出現(xiàn)多個(gè)獨(dú)立的索引結(jié)構(gòu)，每個(gè)索引（非聚集索引）互相之間不存在關(guān)聯(lián)。非聚集索引和聚集索引的區(qū)別在于， 通過聚集索引可以查到需要查找的數(shù)據(jù)， 而通過非聚集索引可以查到記錄對(duì)應(yīng)的主鍵值 ， 再使用主鍵的值通過聚集索引查找到需要的數(shù)據(jù)。
每次給字段建一個(gè)新索引， 字段中的數(shù)據(jù)就會(huì)被復(fù)制一份出來， 用于生成索引。 因此，給表添加索引，會(huì)增加表的體積，占用磁盤存儲(chǔ)空間。這也就解釋了實(shí)驗(yàn)中添加索引后插入速度降低的原因。
經(jīng)過本次實(shí)驗(yàn)，在以后創(chuàng)建表格時(shí)，盡量保證將主要的數(shù)據(jù)都插入之后，再添加索引，避免在添加索引之后進(jìn)行數(shù)據(jù)插入，以保證效率最高。
附錄：（壓力測(cè)試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) {
總結(jié)
- 上一篇： Python批量裁剪图形外围空白区域
- 下一篇： 网络管理的四大模型