2020-08-31 上周完成一个查询接口,从MySQL中查数据,速度贼慢,
原因是数据5000万+,所以真正接触优化的机会来了,所以决定好好研究一下,做一个记录。
事情是这样的,mysql表中只有两个字段,企业名称,企业统一信用代码。
目标:实现百度搜索的功能,输入关键词,就出来相关词的下拉列表。
首先第一步就是要在mysql建表,并把数据写入,写入的方法使用的是kettle中的文本文件输入,表输出,实现效果是48小时2000万条。太慢了。导完数据也不知能不能实现快速查询。
下一步,
一是尝试其他写入数据的方法。
二是,安装es,尝试es查询。
三是,了解mysql的写入优化。
四是,实现百度搜索的功能。
#该文件主要实现将txt文件传入mysql数据库
import pymysql
import re
import time,datetime
#变量初始化
# connect MySQL
con = pymysql.connect(
host= "xxxxxx",
user="root" ,
passwd="xxxxxxx" ,
db= "leeno",
port=3306 ,
charset='utf8')
def insert(con,uniscid,entname):
#数据库游标!
cue = con.cursor()
try:
cue.execute(
"insert into lihua (uniscid,entname) values(%s,%s)",
[uniscid,entname])
#执行sql语句
# print("insert success") # 测试语句
except Exception as e:
print('Insert error:', e)
con.rollback()
#报错反馈
else:
con.commit()
#真正的执行语句
def read():
count=0
filename="C:\\Users\\admin\\Desktop\\456\\456.txt"
#按行读取txt文本文档
with open(filename, 'r',encoding='utf-8') as f:
datas = f.readlines()
while True:
if datas is not None:
for data in datas:
txt=re.split(r',',data)
uniscid=txt[0]
entname=txt[1]
insert(con, uniscid, entname)
count=count+1
print(count)
if data is None:break
if datas is None:break
//正式执行过程
start = datetime.datetime.now()
print("开始时间:" + time.strftime("%Y-%m-%d %H:%M:%S", time.localtime(time.time())))
read()
#执行read函数
con.close()
#关闭连接
end = datetime.datetime.now()
print("结束时间:" + time.strftime("%Y-%m-%d %H:%M:%S", time.localtime(time.time())))
print("总用时:" + str(end - start))
注意上面的代码,是循环执行insert语句,一条条插入的。刚开始插入速度200条/秒。
怎么优化呢?
经过下面的过程
1.换服务器了,32G+4核
2.Load data local infile 卡住不显示导入数据速度
3.Kettle 写入新服务器的sql,每秒速度1500条,还是太慢
4.考虑 将 数据 转成 insert into 的sql文件,5000万条数据转成50个sql文件,利用souce sql文件 的方式,看下速度如何
其他写入数据的方法:
import pymysql
from impala.dbapi import connect
import time,datetime
import pandas as pd
start = datetime.datetime.now()
print("开始时间:" + time.strftime("%Y-%m-%d %H:%M:%S", time.localtime(time.time())))
data = []
filename1 = "C:\\Users\\admin\\Desktop\\123\\uniscidename_20200828_091852.txt"
i=0
with open(filename1, 'r',encoding='utf-8') as file_to_read:
while True:
lines = file_to_read.readline()
if not lines:
break
pass
uniscid=lines.split(",")[0].strip("\n")
entname = lines.split(",")[1].strip("\n")
str1 = "insert into TAX_ENTNAME_UNISCID (uniscid,entname) values"+"('" + uniscid + "','"+entname+"');"
# print(str1)
data.append(str1)
i=i+1
if (i%1000000==0 and lines is not None ):
df = pd.DataFrame(data)
df.to_csv("C:\\Users\\admin\\Desktop\\123\\"+time.strftime("%Y%m%d%H%M%S", time.localtime(time.time()))+".sql",sep='\n',header=False,index=False)
i = 0
data=[]
if(i<1000000 and lines is None):
df = pd.DataFrame(data)
df.to_csv("C:\\Users\\admin\\Desktop\\123\\" + time.strftime("%Y%m%d%H%M%S",time.localtime(time.time())) +"LAST"+ ".sql",sep='\n',header=False,index=False)
break;
end = datetime.datetime.now()
print("结束时间:" + time.strftime("%Y-%m-%d %H:%M:%S", time.localtime(time.time())))
print("总用时:" + str(end - start))
5000万条数据,每个企业名称查询时间 约 1分钟。
所以,打算换成elaticsearch,可是我是个小白。
下一步要实现的是
- 安装配置es
- 安装配置 kibana
- 安装配置logstash
- 利用logstash 全量或者增量导入mysql中的数据库
- 将数据展示在kibana
- 测试查询速度