请教大神帮忙分析:生成随机数插入很慢,5个索引算多吗?
本帖最后由 jimy1 于 2017-5-4 10:56 编辑go语言写的for循环,每次循环生成5个值(对应5个索引字段)加上25个固定值拼成SQL语句,执行插入。
不加索引(30字段,也是循环生成随机值插入)用/mysqladmin -u -p extended-status -r -i1|grep -i Com_insert查看结果平均在3000每秒,插入100万数据时间为5分钟多一点:
sudo/usr/bin/mysqladmin -u -p extended-status -r -i1|grep -i Com_insert
| Com_insert | 1022904 |
| Com_insert_select | 0 |
| Com_insert | 2318 |
| Com_insert_select | 0 |
| Com_insert | 4003 |
| Com_insert_select | 0 |
| Com_insert | 2572 |
| Com_insert_select | 0 |
| Com_insert | 2050 |
| Com_insert_select | 0 |
| Com_insert | 3956 |
| Com_insert_select | 0 |
| Com_insert | 1902 |
| Com_insert_select | 0 |
| Com_insert | 4556 |
| Com_insert_select | 0 |
| Com_insert | 4584 |
| Com_insert_select | 0 |
加了5个索引后,用/mysqladminextended-status |grep Com_insert 查看每秒在300-600间,插入100万数据为30分钟。(最开始1分钟能到2000左右每秒的插入量)
sudo/usr/bin/mysqladmin -u -p extended-status -r -i1|grep -i Com_insert
| Com_insert | 29941 |
| Com_insert_select | 0 |
| Com_insert | 2566 |
| Com_insert_select | 0 |
| Com_insert | 3013 |
| Com_insert_select | 0 |
| Com_insert | 2829 |
| Com_insert_select | 0 |
| Com_insert | 2083 |
| Com_insert_select | 0 |
| Com_insert | 1537 |
| Com_insert_select | 0 |
| Com_insert | 2555 |
| Com_insert_select | 0 |
| Com_insert_select | 0 |
| Com_insert | 951 |
| Com_insert_select | 0 |
| Com_insert | 263 |
| Com_insert_select | 0 |
| Com_insert | 506 |
| Com_insert_select | 0 |
| Com_insert | 448 |
| Com_insert_select | 0 |
| Com_insert | 395 |
| Com_insert_select | 0 |
| Com_insert | 315 |
| Com_insert_select | 0 |
| Com_insert | 364 |
| Com_insert_select | 0 |
MySQL是5.7.12版本,操作系统是红帽6.1,我感觉太慢了,请各位大神帮忙分析下,谢谢了
代码(50万数据):
for i:=0;i<500;i++{
go insertUnis(db,"unis") //500个go程
}
common_package.WgDay.Wait()
//time.Sleep(2*time.Second)
return nil
}
func insert(db *sql.DB, name string){
sql := ""
user_name := string(Krand(64,0)) //生成64位的随机数
session_key:=string(Krand(56,0)) //生成56位的随机数
nat_ip := string(Krand(15,0)) //生成15位的随机数
Nas_IP := nat_ip //56
frame_ip := nat_ip //56
sql = "insert into unis values("+session_key+","+frame_ip+","+user_name+",'ee02:123::af01:9231:df18:8998','ee02:123::/64','0xBF019231DF188998','0xBF019231DF188999','a0:24:7e:68:0c:80','"+Nas_IP+"','40.2M','srunk 6/0/36:33.351 0/0/0/0/0/0',487204,587239,2458787490,'taefawega',2458787585,'20M','bj.com','21M','534111111111111111999253859','EE60-X10311900100000091463d000936','Aa:ef:dd','Eptv','Asfefji','Sefoij','"+nat_ip+"','70.11.69.45',1343,2454,2458787490);"
_, err := db.Exec(sql)
if err != nil {
Log(LL_ERROR, sql, ", err info:", err.Error(), ".")
}
}
func insertUnis(db *sql.DB, name string){
for i:=0;i<1000;i++{
insert(db,name);
}
common_package.WgDay.Done()
return
}
页:
[1]