2020-08-31 上周完成一个查询接口，从MySQL中查数据，速度贼慢

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，可是我是个小白。
下一步要实现的是

1. 安装配置es
2. 安装配置 kibana
3. 安装配置logstash
4. 利用logstash 全量或者增量导入mysql中的数据库
5. 将数据展示在kibana
6. 测试查询速度