Pregunta de la entrevista: Si crea 100.000 datos de prueba, ¿cómo insertar 100.000 datos en la base de datos sin duplicados?
En las entrevistas recientes se suelen hacer preguntas relacionadas con SQL. La creación de datos de prueba en la base de datos es un escenario común en el trabajo diario. Las pruebas de estrés generales y las pruebas de rendimiento también requieren que los datos de la prueba se preparen primero en la base de datos. Entonces, ¿cómo generar una gran cantidad de datos de prueba en lotes?
Debido a que uso Python con frecuencia, pensé en usarlo para generar SQL y luego ejecutar SQL para insertar datos en la base de datos.
Idioma: Python 3.6
Insertar datos
En primer lugar, necesito insertar sentencias SQL y cada ID no debe repetirse. El siguiente es el proceso de ejecución de una declaración de inserción.
Insertar en apps.apiapp_card (id, card_id, card_user, add_time) valor (' 1 ', '', ' test123 ', ' 2019-12-17 '
); El tiempo de ejecución de 10w es demasiado largo. Utilice Mr. Python para medir el tiempo de ejecución usando 1w.
Primero, necesito generar múltiples declaraciones diferidas. Aquí uso el lenguaje Python para escribir párrafos y generar texto SQL.
Reemplace el valor del campo que debe cambiarse con s. Si necesita cambiar varios valores, puede reemplazar el valor correspondiente con varios s. La tabla que diseñé aquí se puede insertar correctamente siempre que Los identificadores son diferentes.
Utilice un bucle for y agregue 1 al ID de cada bucle. Esto garantiza que el ID no se repita. De lo contrario, el ID repetido no se podrá escribir correctamente cuando se inserte en la base de datos.
a es texto adicional.
Separa cada sql con punto y coma.
Cada vez que escribas datos, añade un carácter de nueva línea al final.
python3
Autor: Shanghai Youyou QQ Group 717225969
Para I(10000) en el rango:
a = " INSERT INTO apps.API app_card(id,card_id,card_user,add_time) value('s','','test123','2019-12-17'); "str(i 1)
Usar open ("a.txt", "a") como fp:
fp.write(a "\n ")
Ejecute código Python y genere un archivo.text localmente para abrir los datos generados. La sección es la siguiente.
Insertar en apps.apiapp_card (id, card_id, card_user, add_time) valor (' 1 ', '', ' test123 ', ' 2019-12-17 '
); Insertar en apps.apiapp_card (id, card_id, card_user, add_time) valores ('2', '', 'test123', '2019-12-17'
Insertar en apps.apiapp_card'); (id , card_id, card_user, add_time) value (' 3 ', '', ' test123 ', ' 2019-12-17 '
Insertar en apps.apiapp_card (id, card_id, card_user, add_time) value(' 4 ', '', ' test123 ', ' 2019-12-17 ');
......
Insertar en apps.apiapp_card ( id, card_id, card_user, add_time) value (' 10000 ', '', ' test123 ', ' 2019-12-17 ');
Si el id es un número de teléfono móvil, cómo generar 10w ¿Diferentes números de teléfono móvil?
Se puede generar en función del campo numérico que comienza con los primeros tres dígitos del número de teléfono móvil, como los números que comienzan con 186. Primero, los datos iniciales son 1860000000 y luego el número aumenta en 1 cada vez.
Agregado a 1860009999, entonces el campo numérico 186000000-1860009999 es 10w.
Después de cambiar la identificación al número de teléfono móvil, modifique el código de la siguiente manera
python3
Autor: Shanghai Youyou QQ Group 717225969
Para I(10000) en el rango:
a = " INSERTAR EN aplicaciones . API app_card(id, card_id, card_user, add_time) VALUES (' s ', '', ' test123 ', ' 2019 - 12-17 '); "str(i 1860000000)
Usar open("a.txt ", " a ") como fp:
fp.write(a "\ n ")
Siempre que str (i 1) se cambie a STR (I 186000000) según lo anterior, se puede generar el número de teléfono móvil.
Insertar en apps.apiapp_card (id, card_id, card_user, add_time) valor (' 1860000000 ', '', ' test123 ', ' 2019-12-17 '); Insertar en apps.apiapp_card (id, card_id, card_user, add_time) valores (' 1860000001 ', '', ' test123 ', ' 2019-12-17 '
Insertar en apps.apiapp_card ); (id , card_id, card_user, add_time) value (' 1860000002 ', '', ' test123 ', ' 2019-12-17 '
Copie el texto generado e INSERTE EN múltiples sql correspondientes a uno); time Pégalo en el cliente navicat y ejecútalo.
Se tardaron unos 5 minutos en completar la ejecución, lo que significa que 10w tarda 50 minutos, lo cual es demasiado lento.
Si hay más datos, el tiempo será demasiado largo, ¡que no es lo que queremos!
Ejecución por lotes
Debido a que el tiempo de ejecución única es demasiado largo, es necesario optimizarlo ahora. Cámbielo a una declaración diferida, inserte datos en lotes y solo escriba una inserción. a la vez para escribir en la base de datos en lotes mucho más rápido.
Puedes concatenar sentencias SQL usando insertar en la tabla () valores(), (), (), (), () y luego insertarlas todas a la vez.
O todas las ejecuciones por lotes tienen éxito o ninguna de las escrituras tiene éxito. Cuando hay un problema con la sintaxis del SQL escrito, la escritura fallará.
Nota:
Al empalmar SQL, utilice comas para separar varios valores.
Este valor debe corresponder al campo de la tabla de datos.
Asegúrate de tener en cuenta que el último dato no va seguido de una coma, sino de un punto y coma.
python3
Autor: Shanghai Youyou QQ Group 717225969
insert_sql = "Insertar en apps.apiapp_card valor"
Usar open(" b.txt "," a ") como fp:
fp.write(insert_sql "\n ")
Para I(10000) en el rango:
a = "('s ', '', ' test123 ', ' 2019-12-17 ')," str(i 10001)
Usar open("b.txt ", " a ") como fp:
fp.write(a "\n ")
Una vez completada la ejecución, copie el contenido del archivo b.text. Tenga en cuenta que esto debe cambiarse a ; al final; de lo contrario, la sintaxis será incorrecta.
A continuación se muestran algunos contenidos de datos.
Insertar en apps.apiapp_card valor
(' 10001 ', '', ' test123 ', ' 2019-12-17 '),
( ' 10002 ', '', ' prueba123 ', ' 2019-12-17 '),
......
(' 20000 ', '', ' prueba123 ' , '2019-12-17');
Copie el contenido de inserción generado al cliente navicat para su ejecución.
Los resultados finales de la prueba después de la ejecución mostraron que 1w piezas de datos solo tardaron 0,217 segundos, lo que supuso una mejora significativa en la velocidad.
Inserción de datos de 10w
Entonces, ¿cuánto tiempo se tarda en generar datos de 10w?
Autor: Shanghai Youyou QQ Group 717225969
python3
insert_sql = "Insertar en apps.apiapp_card valor"
Usar open(" b.txt "," a ") como fp:
fp.write(insert_sql "\n ")
Para I(100000) en el rango:
a = "('s ', '', ' test123 ', ' 2019-12-17 ')," str(i 100000)
Usar open("b.txt ", " a ") como fp:
fp.write(a "\n ")
Los datos generados después de la ejecución utilizando el script de Python son los siguientes
Insertar en apps.apiapp_card Valores
(' 100000 ', '', ' test123 ', ' 2019-12-17 '),
(' 100001 ', '', ' test123 ' , ' 2019-12-17 '),
......
(' 199999 ', '', ' test123 ', ' 2019-12-17 ') ;
Si inserta directamente en mysql, obtendrá un error: error 1153 - Obtuve un paquete mayor que los bytes 'max_allowed_packet'.
Motivo del error: Mysql limitará SQL con una gran cantidad de datos en una sola tabla debido a que la gran cantidad de datos excede max_allowed_packet.
El rango permitido.
Solución: el valor de max_allowed_packet en la base de datos mysql debe modificarse para que sea mayor.
Paquetes de datos máximos permitidos
Primero, ingrese el comando en navicat para ver el máximo de paquetes de datos permitidos max_allowed_packet.
Muestra variables globales, como "max_allowed_packet";
El valor encontrado es 4194304, el límite máximo es 40 m, la cadena SQL que solo necesitamos es demasiado grande y excedió este rango. .
En el cliente navicat, no podemos modificar directamente el valor correspondiente. Necesitamos iniciar sesión en mysql y usar la línea de comando para modificarlo.
Mi MySQL está construido en Docker, por lo que necesito un contenedor avanzado para iniciar sesión en MySQL.
Los pasos son los siguientes:
Docker exec ingresa al contenedor Docker.
Mysql-urot-p Introduce la contraseña para iniciar sesión en Mysql.
establecer global max _ permitido _ paquete = 419430400; establecer el paquete de datos máximo permitido 400M m
Muestra variables globales, como "max _ permitido _ paquete"; La configuración es efectiva.
[root @ VM _ 0 _ 2 _ centos ~]# docker exec-it 934 b 30 a6 DC 36/bin/bash
root @ 934 b 30 a6 DC 36: /# MySQL-u root-p
Ingrese contraseña:
Bienvenido al monitor MySQL. El comando termina con; o \g.
Su ID de conexión MySQL es 303822
Versión del servidor: 5.7.27 MySQL Community Server (GPL)
Copyright (c) 2000, 2019, Oracle y/ o sus afiliados. Copyright
Oracle es una subsidiaria de Oracle Corporation y/o sus
filiales. Otros nombres pueden ser marcas comerciales de sus respectivos propietarios.
Escriba "ayuda" o "\h" para obtener ayuda. Escriba '\c' para borrar la declaración de entrada actual.
mysql gt muestra variables globales, como "paquete _ máximo _ permitido"
- -
|Nombre de variable|Valor|
<; p > - -| max _ permitido _ paquete |
- -
0 filas en 65438 colección (0,00 segundos)
mysql gtset global max _ permitido _ paquete = 419430400;
La consulta es normal, 0 filas se ven afectadas (0,00 segundos)
mysql gt muestra variables globales, como "max _ paquete _ permitido";
- -
|Nombre de variable|valor|
- -
| paquete _ máximo _ permitido | 419430400 |
- -
0 filas en la colección 65438 (0,00 segundos)
mysql gt
Como se puede ver en Los resultados de la consulta anterior han entrado en vigor.
Vuelva a ejecutar los datos de 10w anteriores y tardará unos 11 segundos en comprobar los resultados de la ejecución.
Líneas afectadas: 100000
Tiempo: 11,678 segundos
El método anterior solo puede tener efecto temporalmente. Cuando mysql se reinicie, encontrará que se ha restaurado.
También existe un método permanente, que requiere modificar el archivo de configuración my.cnf.
Agregue una oración en la sección [mysqld] y, de ser así, modifique el valor correspondiente:
Paquetes de datos máximos permitidos = 40 M
El valor aquí puede ser m como unidad. Después de la modificación, debe reiniciar MySQL para que surta efecto.
Ejecutar en Python
¿Cuánto tiempo llevará si se usa Python en lugar del cliente Navicat?
Primero encapsule el método de conexión a MySQL y luego empalme la declaración SQL ejecutada. Al empalmar, tenga en cuenta que el último carácter debe cambiarse a;
Obtenga la marca de tiempo actual antes de ejecutar el código y obtenga la marca de tiempo nuevamente después de ejecutar el código. El intervalo de tiempo entre los dos tiempos es el tiempo de ejecución y la unidad de tiempo es s.
Python hace referencia al siguiente contenido para ejecutar código mysql
Importar pymysql
'''
python3
Autor: grupo Shanghai Youyou QQ 717225969
pip install PyMySQL==0.9.3
'''
dbinfo = {
"Host" : "192.168.1.x",
"Usuario": "root",
"Contraseña": "123456",
[ Puerto]: 3306}
Clase DbConnect():
def init(self, db_cof, base de datos=" "):
self.db_cof = db_cof
#Abrir conexión de base de datos
self .db = pymysql . connect(database = base de datos,
clase de cursor = pymysql . cursors . dict cursor,
**db_cof)
#Utilice el método cursor() para obtener el cursor de operación.
self.cursor = self.db.cursor()
Definir selección (self, sql):
# declaración de consulta SQL
# SQL = " SELECCIONAR * DEL EMPLEADO # DONDE INGRESOS gt;s" (1000)
self.cursor.execute(sql)
resultados = self.cursor.fetchall()
Devolver resultados
Ejecución de definición (self, sql):
# SQL eliminar, enviar y modificar declaraciones
# sql = " Elimine los gts de edad de los empleados" (20)
Pruebe:
#Execute sentencia SQL
self.cursor.execute(sql)
#Commit cambios
self.db.commit()
Excepto:
#Rollback en caso de error
self. ()
Definir cerrar (self):
#Cerrar la conexión
self.db.close()
si nombre = = 'main ':
Hora de importación
insert_sql = "Insertar valores en apps.apiapp_card"
insert_values = " ".
join(["('s ', '', ' test123 ', ' 2019-12-17 '),\n " str(I 100000)para I en el rango(10000)])
# Empalmar sql
sql = insertar sql insertar valor [:-3] ";"
#Imprimir (sql)
#Ejecutar sql
time1 = time.time()
db = DbConnect(dbinfo, base de datos="apps ")
Ejecución de base de datos (sql)
db.close ()
time2 = time.time()
Print("Tiempo total de ejecución: s" (time2-time1))
Utilice Python para ejecutar el Resultado: El tiempo total es demasiado largo: 1.016256999969482, el resultado está más allá de mi imaginación. ¡Solo se necesita 1 segundo para procesar 100.000 datos!
Pregunta de la entrevista: ¿Cómo crear 100.000 datos de prueba e insertar 100.000 datos diferentes en la base de datos?
Etiqueta: mejora de la declaración SQL nombre datos ===commandatiblewhere