mysql优化

MySQL参数优化测试建议

一、参数优化前压力测试
0、优化测试前提
workstation12 ,OS centos 6.9（系统已优化），cpu2（I5 4288u 2.6GHZ）,MEM4GB ,HardDisk:Apple SSD(SM-0512F)

1、模拟数据库数据
为了测试我们创建一个oldboy的库创建一个lufei的表，然后导入20万行数据，脚本如下：
vim slap.sh
#!/bin/bash
HOSTNAME="localhost"
PORT="3306"
USERNAME="root"
PASSWORD="123"
DBNAME="oldboy"
TABLENAME="lufei"
#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 200000 ]
do
insertsql="insert into ${TABLENAME} values($i,'james$i','1','110011198809163418','1988-09-16','2017-09-13','oldboyedu_$i')"
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}"

执行脚本：
sh slap.sh
2、检查数据可用性
mysql -uroot -p123
select count() from oldboy.lufei;
mysql> select count() from oldboy.lufei;
+----------+
| count(*) |
+----------+
| 200000 |
+----------+
1 row in set (0.07 sec)