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. 测试查询速度
  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
好的,让我们开始吧。以下是一个使用Spring Boot开发的基本查询接口的示例: 首先,你需要在pom.xml文件中添加MySQL和Spring Data JPA的依赖项。 ``` <dependencies> <!-- MySQL数据库驱动 --> <dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> <version>8.0.26</version> </dependency> <!-- Spring Data JPA依赖 --> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-data-jpa</artifactId> </dependency> </dependencies> ``` 然后,你需要在application.properties文件中配置MySQL数据库的连接信息。 ``` spring.datasource.url=jdbc:mysql://localhost:3306/dbname spring.datasource.username=username spring.datasource.password=password spring.jpa.show-sql=true spring.jpa.hibernate.ddl-auto=update ``` 上述代码中的一些要点是: 1. 数据库连接的URL格式是jdbc:mysql://host:port/database_name。 2. 显示SQL语句是spring.jpa.show-sql=true。 3. 自动生成数据表是spring.jpa.hibernate.ddl-auto=update。 接下来,你需要创建一个实体类,用于映射MySQL中的数据表。 ``` @Entity @Table(name = "tablename") public class EntityName { @Id @GeneratedValue(strategy = GenerationType.IDENTITY) private Long id; @Column(name = "columnname") private String columnName; // get和set方法 } ``` 在上面的代码中,@Entity注释指示这是一个JPA实体类,@Table注释指示此实体映射到哪个数据表中。 接着,你需要创建一个Spring Data JPA Repository接口,它将帮助你执行数据库操作,并定义查询方法。 ``` @Repository public interface EntityNameRepository extends JpaRepository<EntityName, Long> { @Query(value = "SELECT * FROM tablename WHERE columnname = ?1", nativeQuery = true) List<EntityName> findByColumnName(String columnName); } ``` 在上面的代码中,@Repository注释使得Spring Boot可以自动发现此接口。其中findByColumnName方法会根据给定的条件查询数据表,并返回结果列表。 最后,在你的Controller中注入EntityNameRepository,然后编写一个查询API接口。 ``` @RestController public class EntityNameController { @Autowired private EntityNameRepository entityNameRepository; @GetMapping("/entityname") public List<EntityName> getEntityName(@RequestParam("columnname") String columnName) { return entityNameRepository.findByColumnName(columnName); } } ``` 在这个示例代码中,GetMapping注释用于声明GET请求的路由,@RequestParam注释用于获取查询参数,并将其传递给findByColumnName方法。 现在,你已经成功地开发了一个简单的查询接口,并根据条件返回MySQL数据库中的数据

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值