MySQL基于索引的压力测试的实现

一、模拟数据库数据

1-1 创建数据库及表脚本 – vim slap.sh

#!/bin/bash  

HOSTNAME=”localhost”

PORT=”3306″

USERNAME=”root”

PASSWORD=”123″

DBNAME=”testdb”

TABLENAME=”t1″

#create database

mysql -h ${HOSTNAME} -P${PORT} -u${USERNAME} -p${PASSWORD} -e “drop database if exists ${DBNAME}”

create_db_sql=”create database if not exists ${DBNAME}”

mysql -h ${HOSTNAME} -P${PORT} -u${USERNAME} -p${PASSWORD} -e “${create_db_sql}”

#create table

create_table_sql=”create table if not exists ${TABLENAME}(stuid int not null primary key,stuname varchar(20) not null,stusеx char(1)   

not null,cardid varchar(20) not null,birthday datetime,entertime datetime,address varchar(100)default null)”

mysql -h ${HOSTNAME} -P${PORT} -u${USERNAME} -p${PASSWORD} ${DBNAME} -e “${create_table_sql}”

#insert data to table

i=”1″

while [ $i -le 500000 ]  

do  

insert_sql=”insert into ${TABLENAME}  values($i,’alexsb_$i’,’1′,’110011198809163418′,’1990-05-16′,’2022-09-13′,’testdb’)”

mysql -h ${HOSTNAME} -P${PORT} -u${USERNAME} -p${PASSWORD} ${DBNAME} -e “${insert_sql}”

let i++  

done  

#select data  

select_sql=”select count(*) from ${TABLENAME}”

mysql -h ${HOSTNAME} -P${PORT} -u${USERNAME} -p${PASSWORD} ${DBNAME} -e “${select_sql}”1-2 执行脚本

sh slap.sh1-3 检查数据的可用性

mysql -uroot -p123

select count(*) from testdb.t1;1-4 在索引优化前后使用mysqlslap进行压力测试

mysqlslap –defaults-file=/etc/my.cnf \

–concurrency=100 –iterations=1 –create-schema=’testdb’ \

–query=”select * from testdb.t1 where stuname=’test_100′” engine=innodb \

–number-of-queries=2000 -uroot -p123 -verbose

到此这篇关于MySQL基于索引的压力测试的实现的文章就介绍到这了,更多相关MySQL 索引压力测试内容请搜索共生网络以前的文章或继续浏览下面的相关文章希望大家以后多多支持共生网络！