Pregunta de la entrevista: Cómo crear 100.000 datos de prueba e insertar 100.000 datos diferentes en la base de datos
Prólogo
Pregunta de la entrevista: si crea 100.000 datos de prueba, cómo insertar 100.000 datos en la base de datos sin duplicar datos
SQL es A menudo se pregunta en entrevistas recientes. La creación de datos de prueba en la base de datos es un escenario que se usa a menudo en el trabajo diario. Generalmente, para las pruebas de estrés y las pruebas de rendimiento, los datos de prueba deben prepararse primero en la base de datos. Entonces, ¿cómo generar una gran cantidad de datos de prueba en lotes?
Como normalmente uso mucho Python, pensé en usar Python para generar SQL primero y luego ejecutar SQL para insertar datos en la base de datos.
Usar lenguaje: Python 3.6
Insertar datos
En primer lugar, la declaración SQL que quiero insertar debe ser única para cada ID. La siguiente es. para ejecutar una única instrucción de inserción
INSERT INTO apps.apiapp_card (id, card_id, card_user, add_time) VALUES ('1', '', 'test123', '2019-12-17');
10w Demasiado y el tiempo de ejecución es largo. Utilice Python para generar 10,000 elementos para medir el tiempo de ejecución.
Primero, se deben generar múltiples declaraciones inertes. Aquí uso el lenguaje Python para escribir un párrafo y generar texto SQL.
Utilice %s para reemplazar los valores de campo que deben cambiarse. Si hay varios valores que deben cambiarse, puede usar varios %s para reemplazar los valores correspondientes. Lo que diseñé aquí se puede insertar correctamente siempre que las identificaciones sean diferentes.
Utilice un bucle for para incrementar el ID en 1 cada vez, de modo que se pueda garantizar que el ID no se repita. De lo contrario, habrá duplicados al insertarlo en la base de datos y la escritura no se realizará correctamente. .
a es escritura anexada
Separe el punto y coma después de cada sql
Cada vez que escriba datos, agregue \n salto de línea al final
python3
Autor: Shanghai-Youyou QQ Group 717225969
para i en el rango(10000):
a = "INSERT INTO apps.apiapp_card (id, card_id, card_user, add_time) VALORES ('%s', '', 'test123', '2019-12-17');"%str(i+1)
con open(" a. txt", "a") as fp:
fp.write(a+"\n")
Ejecute el código Python, genere un archivo a.text localmente y ábralo. Los datos generados son parcialmente los siguientes
INSERT INTO apps.apiapp_card (id, card_id, card_user, add_time) VALUES ('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) VALORES ('3', '', 'test123', '2019-12-17');
INSERTAR EN apps.apiapp_card (id , card_id, card_user, add_time) VALORES ('4', '', 'test123', '2019-12-17');
......
INSERTAR INTO apps.apiapp_card (id, card_id, card_user, add_time) VALUES ('10000', '', 'test123', '2019-12-17');
Si el id es un número de teléfono móvil ¿Cómo generar 100.000 números de teléfonos móviles diferentes?
Se puede generar en función del segmento numérico que comienza con los primeros 3 dígitos del número de teléfono móvil. Por ejemplo, si comienza con 186, primero use los datos iniciales 1860000000 y luego agregue 1 a esto. número cada vez
Agréguelo a 18600099999, de modo que el rango de números 1860000000-18600099999 sea 100.000 números de teléfono móvil.
Después de cambiar la identificación a un número de teléfono móvil, modifique el código de la siguiente manera
python3
Autor: Shanghai-Youyou QQ Group 717225969
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+1860000000)
con open("a.txt", "a") como fp:
fp.write(a+ "\n" )
Simplemente cambie str(i+1) a str(i+1860000000) según lo anterior para generar un número de teléfono móvil
INSERT INTO apps.apiapp_card ( id, card_id, card_user, add_time) VALORES ('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) VALORES ('1860000002', '' , 'test123', '2019-12-17');
Copie el texto generado y pegue el SQL correspondiente a múltiples INSERT INTO en el cliente navicat a la vez para su ejecución
Tomó aproximadamente 5 minutos completar la ejecución, lo que significa 50 minutos para 100,000 registros. Esto es demasiado lento. Si hay más datos, tomará demasiado tiempo, ¡que no es el efecto que queremos!
Ejecución por lotes
Dado que una sola ejecución lleva demasiado tiempo, ahora es necesario optimizarla y cambiarla a una declaración inerte para insertar datos en lotes y solo escribir una inserción en él. que se puede procesar por lotes a la vez. Escribir en la base de datos será mucho más rápido.
Las sentencias SQL se pueden unir, usando insertar en la tabla () valores(),(),(),() y luego insertarlas todas a la vez.
O todas las ejecuciones por lotes son exitosas o ninguna de ellas se escribe exitosamente. Cuando hay un problema con la sintaxis SQL escrita, la escritura no será exitosa.
Nota:
Al empalmar sql, varios valores deben estar separados por comas inglesas
El valor debe corresponder uno a uno con los campos de la tabla de datos
p>
Asegúrese 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 = "INSERT INTO apps.apiapp_card VALUES "
con open("b.txt", "a") como fp:
fp.write(insert_sql+"\n")
para i en el rango(10000):
a = "('%s', '', ' test123', '2019-12-17'),"%str( i+10001)
con open("b.txt", "a") como fp:
fp.write(a+"\n")
Una vez completada la ejecución, copie el contenido del archivo b.text. Cabe señalar que debe cambiarse a; se informará un error de sintaxis
Parte del contenido de los datos se muestra a continuación
INSERT INTO apps .apiapp_card VALUES
('10001', '', 'test123 ', '2019-12-17'),
('10002', '', 'test123', '2019-12-17'),
.... ..
('20000', '', 'test123', '2019-12-17');
Copia el INSERT INTO generado al cliente navicat para su ejecución
p>
La ejecución se completó. Los resultados finales de la prueba muestran que 1w datos solo tomaron 0,217 segundos, lo que es significativamente más rápido.
Inserción de datos de 10w
Luego pruebe, ¿cuánto tiempo llevará generar datos de 10w?
Autor: Shanghai-Youyou QQ Group 717225969
python3
insert_sql = "INSERT INTO apps.apiapp_card VALUES "
con open( "b.txt", "a") como fp:
fp.write(insert_sql+"\n")
para i en el rango(100000):
a = "('%s', '', 'test123', '2019-12-17'),"%str(i+100000)
con open("b.txt" , "a") como fp:
fp.write(a+"\n")
Los datos generados después de la ejecución usando el script de Python son los siguientes
INSERTAR EN apps.apiapp_card VALORES
('100000', '', 'test123', '2019-12-17'),
('100001', '', 'prueba123', '2019-12-17'),
('199999', '', 'prueba123', '2019-12- 17');
Insertar directamente en mysql. En este momento, se informará un error: Err 1153 - Obtuve un paquete más grande que los bytes 'max_allowed_packet'
Motivo del error: Debido a la gran cantidad de datos, mysql tendrá restricciones en SQL con una gran cantidad de datos. La cadena de 100.000 datos excede el rango permitido de max_allowed_packet
.
Solución: necesita modificar el valor de max_allowed_packet en la base de datos mysql y hacerlo más grande
max_allowed_packet
Primero ingrese el comando en navicat para ver max_allowed_packet paquete máximo permitido
muestra variables globales como 'max_allowed_packet';
El valor es 4194304 y el límite máximo es 40 M. La cadena SQL que solo necesitamos es demasiado grande y excede esto rango.
En el cliente navicat, no podemos modificar el valor correspondiente directamente. Necesitamos iniciar sesión en mysql y modificarlo usando la línea de comando.
Mi MySQL aquí está construido en Docker. Debes ingresar al contenedor e iniciar sesión en MySQL.
Los pasos son los siguientes:
Docker Exec en el contenedor acoplable
mysql -uroot -p Ingrese la contraseña e inicie sesión en mysql
set global max_allowed_packet=419430400 Establezca el paquete máximo permitido en 400 M
; mostrar variables globales como 'max_allowed_packet'; ver la configuración anterior si tiene efecto
[root@VM_0_2_centos ~]# docker exec -it 934b30a6dc36 /bin/bash
root@934b30a6dc36: /# mysql -uroot -p
Ingrese la contraseña:
Bienvenido al monitor MySQL. Los comandos terminan con \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. Todos los derechos reservados.
Oracle es una marca registrada de Oracle Corporation y/o sus
filiales. Otros nombres pueden ser marcas comerciales de sus respectivos
propietarios.
Escriba '. help;' o '\h' para obtener ayuda. Escriba '\ c' para borrar la declaración de entrada actual.
mysql> muestra variables globales como 'max_allowed_packet';
+-- -------------- ----+----------+
| Nombre_variable |
+ ------------- -------+-----------+
| max_allowed_packet 4194304 |
+---------- -+-----------+
1 fila en conjunto (0,00 seg )
mysql> establece global max_allowed_packet =419430400;
Consulta correcta, 0 filas afectadas (0,00 segundos)
mysql> muestra variables globales como 'max_allowed_packet';
+----- ---------------+----------+
| Nombre_variable | Valor |
+-- ------------------+----------+
| max_allowed_packet | 419430400 |
+----------------
----+-----------+
1 fila en conjunto (0,00 segundos)
mysql>
Desde arriba Puede ver los resultados de la consulta y ha surtido efecto.
Vuelva a ejecutar los 100.000 datos anteriores y tardará unos 11 segundos en ver los resultados de la ejecución.
Filas afectadas: 100000
Tiempo: 11.678s
El método anterior solo puede surtir efecto temporalmente. Después de reiniciar MySQL, encontrará que se restaurará. de nuevo. .
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 modifique el valor correspondiente si lo hay:
max_allowed_packet=40M
El valor aquí puede estar en unidades M. Después de la modificación, debe reiniciar MySQL para que surta efecto
Use Python para ejecutar.
Si no usa el cliente navicat y usa python para ejecutarlo directamente, ¿cuánto tiempo tomará?
Primero encapsule el método de conexión a mysql y luego empalme la instrucción SQL ejecutada. Al empalmar, tenga en cuenta que el último carácter debe cambiarse a;
Obtenga el anterior. Al ejecutar la marca de tiempo del código, obtenga la marca de tiempo nuevamente una vez que se complete la ejecución del código.
El intervalo de tiempo entre dos tiempos es el tiempo de ejecución. La unidad de tiempo es s.
La referencia del código para que Python ejecute mysql es la siguiente
importar pymysql
. '''
python3
Autor: Shanghai-Youyou QQ Group 717225969
pip install PyMySQL==0.9.3
'' '
dbinfo = {
"host": "192.168.1.x",
"usuario": "raíz",
"contraseña" : "123456",
"puerto": 3306}
clase DbConnect():
def init(self, db_cof, base de datos =""):
self.db_cof = db_cof
# Abrir conexión a base de datos
self.db = pymysql.connect(database=base de datos,
cursorclass =pymysql.cursors.DictCursor,
**db_cof)
# Utilice el método cursor() para obtener la operación cursor
self .cursor = self.db.cursor ()
def select(self, sql):
# declaración de consulta SQL
# sql = "SELECT * FROM EMPLEADO # DONDE INGRESOS > %s" % (1000)
self.cursor.execute(sql)
resultados = self.cursor.fetchall()
devolver resultados
def ejecutar(self, sql):
# SQL eliminar, enviar, modificar declaraciones
# sql = "ELIMINAR DEL EMPLEADO DONDE EDAD > % s" % (20)
prueba:
#Ejecutar sentencia SQL
self.cursor.execute(sql)
#Enviar cambios
self.db.commit()
excepto:
# Revertir cuando ocurre un error
self.db.rollback( )
def close(self):
# Cerrar la conexión
self.db.close()
if nombre == 'main':
hora de importación
insert_sql = "INSERT INTO apps.apiapp_card VALUES "
insert_values = "".join(["(' %s', '', 'test123', '2019-12-17'), \n"%str(i+100000) para i en el rango(100000)])
# Splice sql
sql = insert_sql + insert_values [:-3]+";"
# print(sql)
# Ejecutar sql
time1 = time.time()
db = DbConnect(dbinfo, base de datos="aplicaciones"
)
db.execute(sql)
db.close()
tiempo2 = tiempo.tiempo()
imprimir(" Tiempo total invertido: %s" % (time2-time1))
Utilice Python para ejecutar los resultados: Tiempo total invertido: 1,0816256999969482. El resultado está más allá de mi imaginación. 100.000 datos en realidad solo toman 1 segundo !
Pregunta de la entrevista: Cómo crear 100.000 datos de prueba e insertar 100.000 datos diferentes en la base de datos
Etiqueta: declaración SQL para mejorar los nombres data==commandatiblewhere