insert into优化

结果：

• 通过navicat客户端操作
  • 插入一万条数据，批量执行和单执行分别耗时0.217秒和5分钟。
  • 插入十万条数据，批量执行，耗时11秒。
• 通过python操作
  • 插入十万条数据，批量执行，耗时1秒。

案例：

单个执行，执行完成花了5分钟左右

for i in range(10000):
    a = "INSERT INTO `apps`.`apiapp_card` (`id`, `card_id`, `card_user`, `add_time`) VALUES ('%s', '', 'test123', '2019-12-17');"%str(i+1)
    with open("a.txt", "a") as fp:
        fp.write(a+"\n")

把生成的文本复制出来 ，多个INSERT INTO 对应的 sql 一次性贴到 navicat 客户端执行，如下图

批量执行，执行完成花了0.217秒，速度明显提高不少

insert_sql = "INSERT INTO `apps`.`apiapp_card`  VALUES "
with open("b.txt", "a") as fp:
        fp.write(insert_sql+"\n")
for i in range(10000):
    a = "('%s', '', 'test123', '2019-12-17'),"%str(i+10001)
    with open("b.txt", "a") as fp:
        fp.write(a+"\n")

注意最后一条数据后面不是逗号，改成分号，否则语法报错

• 部分数据内容展示如下
  INSERT INTO apps.apiapp_card VALUES
  ('10001', '', 'test123', '2019-12-17'),
  ('10002', '', 'test123', '2019-12-17'),
  ......
  ('20000', '', 'test123', '2019-12-17');

复制生成的 INSERT INTO 到 navicat 客户端执行

假设10W条数据直接插入mysql 这时候报错：Err 1153 - Got a packet bigger than ‘max_allowed_packet’ bytes，
报错原因：由于数据量较大，mysql 会对单表数据量较大的 SQL 做限制，10w条数据的字符串超出了max_allowed_packet的允许范围。

先在 navicat 输入命令查看 max_allowed_packet 最大允许包

show global variables like ‘max_allowed_packet’;

image.png

解决：

修改my.cnf配置文件
在[mysqld]部分添加一句，如果有就修改对应的值：
max_allowed_packet=40M
这里的值，可以用 M单位，修改后，需要重启下mysql就可以生效了

再次重新执行上面10w条数据，查看运行结果总共花11秒左右时间。

以上操作均用navicat客户端，若直接用python去执行，会花多少时间呢？

dbinfo = {
    "host": "192.168.1.x",
    "user": "root",
    "password": "123456",
    "port": 3306}
class DbConnect():
    def __init__(self, db_cof, database=""):
        self.db_cof = db_cof
        # 打开数据库连接
        self.db = pymysql.connect(database=database,
                                  cursorclass=pymysql.cursors.DictCursor,
                                  **db_cof)
        # 使用cursor()方法获取操作游标
        self.cursor = self.db.cursor()
    def select(self, sql):
        # SQL 查询语句
        # sql = "SELECT * FROM EMPLOYEE \
        #        WHERE INCOME > %s" % (1000)
        self.cursor.execute(sql)
        results = self.cursor.fetchall()
        return results
    def execute(self, sql):
        # SQL 删除、提交、修改语句
        # sql = "DELETE FROM EMPLOYEE WHERE AGE > %s" % (20)
        try:
          # 执行SQL语句
          self.cursor.execute(sql)
          # 提交修改
          self.db.commit()
        except:
          # 发生错误时回滚
          self.db.rollback()
    def close(self):
        # 关闭连接
        self.db.close()
if __name__ == '__main__':
    import time
    insert_sql = "INSERT INTO `apps`.`apiapp_card`  VALUES "
    insert_values = "".join(["('%s', '', 'test123', '2019-12-17'), \n"%str(i+100000) for i in range(100000)])
    # 拼接sql
    sql = insert_sql + insert_values[:-3]+";"
    # print(sql)
    # 执行sql
    time1 = time.time()
    db = DbConnect(dbinfo, database="apps")
    db.execute(sql)
    db.close()
    time2 = time.time()
    print("总过耗时：%s" % (time2-time1))

python执行结果：
总过耗时：1.0816256999969482
10w条数据只要1秒钟