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

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

2021-12-02 16:45LSYHhhhh Mysql

本文主要介绍了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,stusex 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','2017-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.sh

1-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