Pregunta MyISAM versus InnoDB [cerrado]


Estoy trabajando en proyectos que implican muchas escrituras de bases de datos, diría yo (70% inserciones y 30% lee) Esta relación también incluiría actualizaciones que considero que son una lectura y una escritura. Las lecturas pueden estar sucias (por ejemplo, no necesito información 100% precisa en el momento de la lectura).
La tarea en cuestión realizará más de 1 millón de transacciones de base de datos por hora.

He leído un montón de cosas en la web sobre las diferencias entre MyISAM e InnoDB, y MyISAM parece ser la elección obvia para la base de datos / tablas particulares que utilizaré para esta tarea. De lo que parece estar leyendo, InnoDB es bueno si se necesitan transacciones ya que el bloqueo de nivel de fila es compatible.

¿Alguien tiene alguna experiencia con este tipo de carga (o superior)? ¿MyISAM es el camino a seguir?


807
2017-08-21 14:50


origen


Respuestas:


Tengo brevemente discutido esta pregunta en una tabla para que pueda concluir si ir con InnoDB o MyISAM.

Aquí hay una pequeña descripción de qué motor de almacenamiento db debe usar en qué situación:

                                                 MyISAM InnoDB
-------------------------------------------------- --------------
Búsqueda obligatoria de texto completo Sí 5.6.4
-------------------------------------------------- --------------
Requerir transacciones Sí
-------------------------------------------------- --------------
Consultas de selección frecuentes Sí
-------------------------------------------------- --------------
Inserción frecuente, actualización, eliminar Sí
-------------------------------------------------- --------------
Bloqueo de filas (procesamiento múltiple en una sola tabla) Sí
-------------------------------------------------- --------------
Diseño de base relacional Sí

Para resumir:

Lectura frecuente, casi sin escritura => MyISAM
Búsqueda de texto completo en MySQL <= 5.5 => MyISAM

En todas las demás circunstancias, InnoDB usualmente es la mejor manera de hacerlo.


496
2017-07-22 22:01



No soy un experto en bases de datos, y no hablo por experiencia. Sin embargo:

Las tablas MyISAM usan bloqueo a nivel de tabla. En función de sus estimaciones de tráfico, tiene cerca de 200 escrituras por segundo. Con MyISAM, solo uno de estos podría estar en progreso en cualquier momento. Debe asegurarse de que su hardware pueda mantenerse al día con estas transacciones para evitar que se sature, es decir, una consulta única no puede tomar más de 5 ms.

Eso me sugiere que necesitaría un motor de almacenamiento que admita el bloqueo a nivel de fila, es decir, InnoDB.

Por otro lado, debería ser bastante trivial escribir unos pocos scripts simples para simular la carga con cada motor de almacenamiento, luego comparar los resultados.


263
2017-08-22 16:03



La gente a menudo habla de rendimiento, lecturas frente a escrituras, claves externas, etc. pero hay otra característica imprescindible para un motor de almacenamiento en mi opinión: actualizaciones atómicas.

Prueba esto:

  1. Emita una ACTUALIZACIÓN contra su tabla MyISAM que demora 5 segundos.
  2. Mientras la ACTUALIZACIÓN está en progreso, digamos 2.5 segundos, presione Ctrl-C para interrumpirla.
  3. Observe los efectos sobre la mesa. ¿Cuántas filas se actualizaron? ¿Cuántos no fueron actualizados? ¿La tabla es incluso legible o se corrompió al presionar Ctrl-C?
  4. Pruebe el mismo experimento con UPDATE contra una tabla InnoDB, interrumpiendo la consulta en progreso.
  5. Observe la tabla InnoDB. Cero las filas fueron actualizadas InnoDB le ha asegurado que tiene actualizaciones atómicas, y si no se puede comprometer la actualización completa, revierte todo el cambio. Además, la tabla no está corrupta. Esto funciona incluso si usa killall -9 mysqld para simular un choque.

El rendimiento es deseable, por supuesto, pero no perder datos debería triunfar eso.


172
2017-07-17 17:47



He trabajado en un sistema de alto volumen utilizando MySQL y he probado tanto MyISAM como InnoDB.

Descubrí que el bloqueo a nivel de tabla en MyISAM ocasionaba graves problemas de rendimiento para nuestra carga de trabajo, que son similares a los suyos. Desafortunadamente, también descubrí que el rendimiento en InnoDB también era peor de lo que esperaba.

Al final, resolví el problema de contención fragmentando los datos de manera que las inserciones entraran en una tabla "activa" y seleccionaran que nunca se consultó la tabla activa.

Esto también permitió borrar (los datos fueron sensibles al tiempo y solo retuvimos el valor de X días) para que ocurran en tablas "caducas" que nuevamente no fueron afectadas por las consultas de selección. InnoDB parece tener un rendimiento bajo en las eliminaciones masivas, por lo que si planea purgar los datos, es posible que desee estructurarlos de tal forma que los datos antiguos estén en una tabla obsoleta que simplemente se puede descartar en lugar de ejecutar eliminaciones en él.

Por supuesto, no tengo idea de cuál es tu aplicación, pero espero que esto te dé una idea de algunos de los problemas con MyISAM e InnoDB.


135
2017-09-16 21:57



Para una carga con más escrituras y lecturas, se beneficiará con InnoDB. Debido a que InnoDB proporciona bloqueo de filas en lugar de bloqueo de tablas, su SELECTs pueden ser concurrentes, no solo entre ellos, sino también con muchos INSERTs. Sin embargo, a menos que tenga la intención de usar transacciones SQL, configure InnoDB commit flush en 2 (innodb_flush_log_at_trx_commit) Esto le devuelve una gran cantidad de rendimiento en bruto que de otro modo perdería al mover tablas de MyISAM a InnoDB.

Además, considere agregar replicación. Esto le proporciona cierta escala de lectura y, dado que afirmó que sus lecturas no tienen que estar actualizadas, puede dejar que la replicación se retrase un poco. Solo asegúrate de que pueda ponerse al día con cualquier cosa que no sea el tráfico más pesado, o siempre estará detrás y nunca se pondrá al día. Si vas por este camino, sin embargo, yo fuertemente Le recomendamos que aísle la lectura de los esclavos y la administración de retraso de replicación en su manejador de base de datos. Es mucho más simple si el código de la aplicación no lo sabe.

Finalmente, tenga en cuenta las diferentes cargas de tabla. No tendrá la misma proporción de lectura / escritura en todas las tablas. Algunas tablas más pequeñas con casi el 100% de lecturas podrían permitirse permanecer MyISAM. Del mismo modo, si tiene algunas tablas que están cerca del 100% de escritura, puede beneficiarse de INSERT DELAYED, pero eso solo se admite en MyISAM (el DELAYED la cláusula se ignora para una tabla InnoDB).

Pero punto de referencia para estar seguro.


61
2018-01-05 23:39



Un poco tarde para el juego ... pero aquí hay una muy completa publicar lo escribí hace unos meses, detallando las principales diferencias entre MYISAM e InnoDB. Tome una taza de té (y tal vez una galleta) y disfrute.


La principal diferencia entre MyISAM e InnoDB está en la integridad referencial y las transacciones. También hay otras diferencias, como bloqueo, reversiones y búsquedas de texto completo.

Integridad referencial

La integridad referencial asegura que las relaciones entre tablas permanezcan consistentes. Más específicamente, esto significa que cuando una tabla (por ejemplo, Listados) tiene una clave externa (por ejemplo, ID de producto) apuntando a una tabla diferente (por ejemplo, Productos), cuando las actualizaciones o eliminaciones se producen en la tabla señalada, estos cambios se transfieren a la vinculación mesa. En nuestro ejemplo, si se cambia el nombre de un producto, las claves externas de la tabla de enlace también se actualizarán; si se elimina un producto de la tabla "Productos", también se eliminarán los listados que apunten a la entrada eliminada. Además, cualquier nueva lista debe tener esa clave externa apuntando a una entrada existente válida.

InnoDB es un DBMS relacional (RDBMS) y, por lo tanto, tiene integridad referencial, mientras que MyISAM no.

Transacciones y atomicidad

Los datos en una tabla se administran utilizando instrucciones de Lenguaje de Manipulación de Datos (DML), como SELECCIONAR, INSERTAR, ACTUALIZAR y ELIMINAR. Una transacción agrupa dos o más declaraciones DML juntas en una sola unidad de trabajo, por lo que se aplica toda la unidad, o nada de eso.

MyISAM no admite transacciones mientras que InnoDB sí.

Si se interrumpe una operación mientras se utiliza una tabla MyISAM, la operación se interrumpe de inmediato, y las filas (o incluso los datos dentro de cada fila) que se vean afectadas siguen afectadas, incluso si la operación no se completó.

Si se interrumpe una operación mientras se usa una tabla InnoDB, porque usa transacciones, que tiene atomicidad, cualquier transacción que no llegó a completarse no tendrá efecto, ya que no se realiza ninguna confirmación.

Bloqueo de tabla vs bloqueo de filas

Cuando una consulta se ejecuta contra una tabla MyISAM, se bloqueará la tabla completa en la que está consultando. Esto significa que las consultas posteriores solo se ejecutarán después de que se finalice la actual. Si está leyendo una tabla grande y / o hay operaciones frecuentes de lectura y escritura, esto puede significar un gran retraso en las consultas.

Cuando una consulta se ejecuta en una tabla InnoDB, solo las filas que están involucradas están bloqueadas, el resto de la tabla permanece disponible para las operaciones CRUD. Esto significa que las consultas se pueden ejecutar simultáneamente en la misma tabla, siempre que no usen la misma fila.

Esta característica en InnoDB se conoce como concurrencia. Por muy bueno que sea la concurrencia, existe una desventaja importante que se aplica a un rango selecto de tablas, en el sentido de que hay una sobrecarga al cambiar entre hilos del kernel, y debe establecer un límite en los hilos del kernel para evitar que el servidor se detenga. .

Transacciones y retrocesos

Cuando ejecuta una operación en MyISAM, los cambios se establecen; en InnoDB, esos cambios pueden revertirse. Los comandos más comunes utilizados para controlar las transacciones son COMMIT, ROLLBACK y SAVEPOINT. 1. COMPROMISO: puede escribir múltiples operaciones DML, pero los cambios solo se guardarán cuando se realice un COMPROMISO 2. ROLLBACK: puede descartar cualquier operación que aún no se haya cometido aún 3. SAVEPOINT - establece un punto en la lista de operaciones a las cuales una operación ROLLBACK puede retroceder a

Confiabilidad

MyISAM no ofrece integridad de datos: las fallas de hardware, las paradas sucias y las operaciones canceladas pueden hacer que los datos se dañen. Esto requeriría una reparación completa o reconstrucciones de los índices y tablas.

InnoDB, por otro lado, utiliza un registro transaccional, un búfer de doble escritura y sumas de verificación y validación automáticas para evitar la corrupción. Antes de que InnoDB realice cambios, registra los datos antes de las transacciones en un archivo de espacio de tablas del sistema llamado ibdata1. Si se produce un bloqueo, InnoDB se autorecupe a través de la reproducción de esos registros.

Indexación FULLTEXT

InnoDB no admite indexación FULLTEXT hasta MySQL versión 5.6.4. A partir de la redacción de esta publicación, la versión de MySQL de muchos proveedores de alojamiento compartido sigue estando por debajo de 5.6.4, lo que significa que la indexación de FULLTEXT no es compatible con las tablas de InnoDB.

Sin embargo, esta no es una razón válida para usar MyISAM. Lo mejor es cambiar a un proveedor de hosting que admita versiones actualizadas de MySQL. No es que una tabla MyISAM que usa indexación FULLTEXT no se pueda convertir a una tabla InnoDB.

Conclusión

En conclusión, InnoDB debería ser su motor de almacenamiento predeterminado. Elija MyISAM u otros tipos de datos cuando atiendan una necesidad específica.


61
2018-01-21 15:32



Para agregar a la amplia selección de respuestas aquí que cubre las diferencias mecánicas entre los dos motores, presento un estudio empírico de comparación de velocidad.

En términos de velocidad pura, MyISAM no siempre es más rápido que InnoDB pero, en mi experiencia, tiende a ser más rápido para los entornos de trabajo PURE LEED en un factor de aproximadamente 2.0-2.5 veces. Claramente, esto no es apropiado para todos los entornos, como otros han escrito, MyISAM carece de cosas como transacciones y claves externas.

He hecho un poco de benchmarking a continuación: he usado python para bucles y la biblioteca timeit para comparaciones de tiempo. Para mayor interés, también incluí el motor de memoria, esto ofrece el mejor rendimiento en todos los ámbitos, aunque solo es adecuado para mesas más pequeñas (continuamente te encuentras The table 'tbl' is full cuando excedas el límite de memoria MySQL). Los cuatro tipos de seleccionar que miro son:

  1. SELECTs vainilla
  2. conteos
  3. SELECTs condicional
  4. sub-selecciones indexadas y no indexadas

En primer lugar, creé tres tablas con el siguiente SQL

CREATE TABLE
    data_interrogation.test_table_myisam
    (
        index_col BIGINT NOT NULL AUTO_INCREMENT,
        value1 DOUBLE,
        value2 DOUBLE,
        value3 DOUBLE,
        value4 DOUBLE,
        PRIMARY KEY (index_col)
    )
    ENGINE=MyISAM DEFAULT CHARSET=utf8

con 'MyISAM' sustituido por 'InnoDB' y 'memoria' en la segunda y tercera tablas.

1) Selecciones de vainilla

Consulta: SELECT * FROM tbl WHERE index_col = xx

Resultado: dibujar

Comparison of vanilla selects by different database engines

La velocidad de estos es ampliamente similar y, como se espera, es lineal en el número de columnas que se seleccionarán. InnoDB parece ligeramente más rápido que MyISAM, pero esto es realmente marginal.

Código:

import timeit
import MySQLdb
import MySQLdb.cursors
import random
from random import randint

db = MySQLdb.connect(host="...", user="...", passwd="...", db="...", cursorclass=MySQLdb.cursors.DictCursor)
cur = db.cursor()

lengthOfTable = 100000

# Fill up the tables with random data
for x in xrange(lengthOfTable):
    rand1 = random.random()
    rand2 = random.random()
    rand3 = random.random()
    rand4 = random.random()

    insertString = "INSERT INTO test_table_innodb (value1,value2,value3,value4) VALUES (" + str(rand1) + "," + str(rand2) + "," + str(rand3) + "," + str(rand4) + ")"
    insertString2 = "INSERT INTO test_table_myisam (value1,value2,value3,value4) VALUES (" + str(rand1) + "," + str(rand2) + "," + str(rand3) + "," + str(rand4) + ")"
    insertString3 = "INSERT INTO test_table_memory (value1,value2,value3,value4) VALUES (" + str(rand1) + "," + str(rand2) + "," + str(rand3) + "," + str(rand4) + ")"

    cur.execute(insertString)
    cur.execute(insertString2)
    cur.execute(insertString3)

db.commit()

# Define a function to pull a certain number of records from these tables
def selectRandomRecords(testTable,numberOfRecords):

    for x in xrange(numberOfRecords):
        rand1 = randint(0,lengthOfTable)

        selectString = "SELECT * FROM " + testTable + " WHERE index_col = " + str(rand1)
        cur.execute(selectString)

setupString = "from __main__ import selectRandomRecords"

# Test time taken using timeit
myisam_times = []
innodb_times = []
memory_times = []

for theLength in [3,10,30,100,300,1000,3000,10000]:

    innodb_times.append( timeit.timeit('selectRandomRecords("test_table_innodb",' + str(theLength) + ')', number=100, setup=setupString) )
    myisam_times.append( timeit.timeit('selectRandomRecords("test_table_myisam",' + str(theLength) + ')', number=100, setup=setupString) )
    memory_times.append( timeit.timeit('selectRandomRecords("test_table_memory",' + str(theLength) + ')', number=100, setup=setupString) )

2) Cuenta

Consulta: SELECT count(*) FROM tbl

Resultado: MyISAM gana

Comparison of counts by different database engines

Éste demuestra una gran diferencia entre MyISAM e InnoDB: MyISAM (y la memoria) realiza un seguimiento del número de registros en la tabla, por lo que esta transacción es rápida y O (1). La cantidad de tiempo requerida para contar InnoDB aumenta de forma super-lineal con el tamaño de la tabla en el rango que investigué. Sospecho que muchas de las aceleraciones de consultas MyISAM que se observan en la práctica se deben a efectos similares.

Código:

myisam_times = []
innodb_times = []
memory_times = []

# Define a function to count the records
def countRecords(testTable):

    selectString = "SELECT count(*) FROM " + testTable
    cur.execute(selectString)

setupString = "from __main__ import countRecords"

# Truncate the tables and re-fill with a set amount of data
for theLength in [3,10,30,100,300,1000,3000,10000,30000,100000]:

    truncateString = "TRUNCATE test_table_innodb"
    truncateString2 = "TRUNCATE test_table_myisam"
    truncateString3 = "TRUNCATE test_table_memory"

    cur.execute(truncateString)
    cur.execute(truncateString2)
    cur.execute(truncateString3)

    for x in xrange(theLength):
        rand1 = random.random()
        rand2 = random.random()
        rand3 = random.random()
        rand4 = random.random()

        insertString = "INSERT INTO test_table_innodb (value1,value2,value3,value4) VALUES (" + str(rand1) + "," + str(rand2) + "," + str(rand3) + "," + str(rand4) + ")"
        insertString2 = "INSERT INTO test_table_myisam (value1,value2,value3,value4) VALUES (" + str(rand1) + "," + str(rand2) + "," + str(rand3) + "," + str(rand4) + ")"
        insertString3 = "INSERT INTO test_table_memory (value1,value2,value3,value4) VALUES (" + str(rand1) + "," + str(rand2) + "," + str(rand3) + "," + str(rand4) + ")"

        cur.execute(insertString)
        cur.execute(insertString2)
        cur.execute(insertString3)

    db.commit()

    # Count and time the query
    innodb_times.append( timeit.timeit('countRecords("test_table_innodb")', number=100, setup=setupString) )
    myisam_times.append( timeit.timeit('countRecords("test_table_myisam")', number=100, setup=setupString) )
    memory_times.append( timeit.timeit('countRecords("test_table_memory")', number=100, setup=setupString) )

3) Selecciones condicionales

Consulta: SELECT * FROM tbl WHERE value1<0.5 AND value2<0.5 AND value3<0.5 AND value4<0.5

Resultado: MyISAM gana

Comparison of conditional selects by different database engines

Aquí, MyISAM y la memoria realizan aproximadamente lo mismo, y superan a InnoDB en aproximadamente un 50% para tablas más grandes. Este es el tipo de consulta para la cual los beneficios de MyISAM parecen estar maximizados.

Código:

myisam_times = []
innodb_times = []
memory_times = []

# Define a function to perform conditional selects
def conditionalSelect(testTable):
    selectString = "SELECT * FROM " + testTable + " WHERE value1 < 0.5 AND value2 < 0.5 AND value3 < 0.5 AND value4 < 0.5"
    cur.execute(selectString)

setupString = "from __main__ import conditionalSelect"

# Truncate the tables and re-fill with a set amount of data
for theLength in [3,10,30,100,300,1000,3000,10000,30000,100000]:

    truncateString = "TRUNCATE test_table_innodb"
    truncateString2 = "TRUNCATE test_table_myisam"
    truncateString3 = "TRUNCATE test_table_memory"

    cur.execute(truncateString)
    cur.execute(truncateString2)
    cur.execute(truncateString3)

    for x in xrange(theLength):
        rand1 = random.random()
        rand2 = random.random()
        rand3 = random.random()
        rand4 = random.random()

        insertString = "INSERT INTO test_table_innodb (value1,value2,value3,value4) VALUES (" + str(rand1) + "," + str(rand2) + "," + str(rand3) + "," + str(rand4) + ")"
        insertString2 = "INSERT INTO test_table_myisam (value1,value2,value3,value4) VALUES (" + str(rand1) + "," + str(rand2) + "," + str(rand3) + "," + str(rand4) + ")"
        insertString3 = "INSERT INTO test_table_memory (value1,value2,value3,value4) VALUES (" + str(rand1) + "," + str(rand2) + "," + str(rand3) + "," + str(rand4) + ")"

        cur.execute(insertString)
        cur.execute(insertString2)
        cur.execute(insertString3)

    db.commit()

    # Count and time the query
    innodb_times.append( timeit.timeit('conditionalSelect("test_table_innodb")', number=100, setup=setupString) )
    myisam_times.append( timeit.timeit('conditionalSelect("test_table_myisam")', number=100, setup=setupString) )
    memory_times.append( timeit.timeit('conditionalSelect("test_table_memory")', number=100, setup=setupString) )

4) Sub-selects

Resultado: InnoDB gana

Para esta consulta, creé un conjunto adicional de tablas para la selección secundaria. Cada uno es simplemente dos columnas de BIGINT, una con un índice de clave principal y otra sin ningún índice. Debido al gran tamaño de la mesa, no probé el motor de memoria. El comando de creación de tabla SQL era

CREATE TABLE
    subselect_myisam
    (
        index_col bigint NOT NULL,
        non_index_col bigint,
        PRIMARY KEY (index_col)
    )
    ENGINE=MyISAM DEFAULT CHARSET=utf8;

donde una vez más, 'MyISAM' se sustituye por 'InnoDB' en la segunda tabla.

En esta consulta, dejo el tamaño de la tabla de selección en 1000000 y, en su lugar, varío el tamaño de las columnas seleccionadas.

Comparison of sub-selects by different database engines

Aquí el InnoDB gana fácilmente. Después de llegar a una tabla de tamaño razonable, ambos motores se escalan linealmente con el tamaño de la selección secundaria. El índice acelera el comando MyISAM pero curiosamente tiene poco efecto en la velocidad de InnoDB. subSelect.png

Código:

myisam_times = []
innodb_times = []
myisam_times_2 = []
innodb_times_2 = []

def subSelectRecordsIndexed(testTable,testSubSelect):
    selectString = "SELECT * FROM " + testTable + " WHERE index_col in ( SELECT index_col FROM " + testSubSelect + " )"
    cur.execute(selectString)

setupString = "from __main__ import subSelectRecordsIndexed"

def subSelectRecordsNotIndexed(testTable,testSubSelect):
    selectString = "SELECT * FROM " + testTable + " WHERE index_col in ( SELECT non_index_col FROM " + testSubSelect + " )"
    cur.execute(selectString)

setupString2 = "from __main__ import subSelectRecordsNotIndexed"

# Truncate the old tables, and re-fill with 1000000 records
truncateString = "TRUNCATE test_table_innodb"
truncateString2 = "TRUNCATE test_table_myisam"

cur.execute(truncateString)
cur.execute(truncateString2)

lengthOfTable = 1000000

# Fill up the tables with random data
for x in xrange(lengthOfTable):
    rand1 = random.random()
    rand2 = random.random()
    rand3 = random.random()
    rand4 = random.random()

    insertString = "INSERT INTO test_table_innodb (value1,value2,value3,value4) VALUES (" + str(rand1) + "," + str(rand2) + "," + str(rand3) + "," + str(rand4) + ")"
    insertString2 = "INSERT INTO test_table_myisam (value1,value2,value3,value4) VALUES (" + str(rand1) + "," + str(rand2) + "," + str(rand3) + "," + str(rand4) + ")"

    cur.execute(insertString)
    cur.execute(insertString2)

for theLength in [3,10,30,100,300,1000,3000,10000,30000,100000]:

    truncateString = "TRUNCATE subselect_innodb"
    truncateString2 = "TRUNCATE subselect_myisam"

    cur.execute(truncateString)
    cur.execute(truncateString2)

    # For each length, empty the table and re-fill it with random data
    rand_sample = sorted(random.sample(xrange(lengthOfTable), theLength))
    rand_sample_2 = random.sample(xrange(lengthOfTable), theLength)

    for (the_value_1,the_value_2) in zip(rand_sample,rand_sample_2):
        insertString = "INSERT INTO subselect_innodb (index_col,non_index_col) VALUES (" + str(the_value_1) + "," + str(the_value_2) + ")"
        insertString2 = "INSERT INTO subselect_myisam (index_col,non_index_col) VALUES (" + str(the_value_1) + "," + str(the_value_2) + ")"

        cur.execute(insertString)
        cur.execute(insertString2)

    db.commit()

    # Finally, time the queries
    innodb_times.append( timeit.timeit('subSelectRecordsIndexed("test_table_innodb","subselect_innodb")', number=100, setup=setupString) )
    myisam_times.append( timeit.timeit('subSelectRecordsIndexed("test_table_myisam","subselect_myisam")', number=100, setup=setupString) )

    innodb_times_2.append( timeit.timeit('subSelectRecordsNotIndexed("test_table_innodb","subselect_innodb")', number=100, setup=setupString2) )
    myisam_times_2.append( timeit.timeit('subSelectRecordsNotIndexed("test_table_myisam","subselect_myisam")', number=100, setup=setupString2) )

Creo que el mensaje final de todo esto es que si eres De Verdad Preocupado por la velocidad, necesita comparar las consultas que está haciendo en lugar de hacer suposiciones sobre qué motor será más adecuado.


51
2018-06-11 09:15



Un poco fuera de tema, pero para fines de documentación e integridad, me gustaría agregar lo siguiente.

En general, usar InnoDB dará como resultado una aplicación mucho MENOS compleja, probablemente también más libre de errores. Debido a que puede poner toda la integridad referencial (restricciones de clave externa) en el modelo de datos, no necesita ni cerca del código de aplicación que necesite con MyISAM.

Cada vez que inserte, elimine o reemplace un registro, TENDRÁ que verificar y mantener las relaciones. P.ej. si elimina un padre, todos los hijos también deberían eliminarse. Por ejemplo, incluso en un sistema de blog simple, si elimina un registro de publicación de blog, tendrá que eliminar los registros de comentarios, los "me gusta", etc. En InnoDB esto lo hace automáticamente el motor de la base de datos (si especificó las restricciones en el modelo) ) y no requiere código de aplicación. En MyISAM esto tendrá que estar codificado en la aplicación, lo cual es muy difícil en los servidores web. Los servidores web son por naturaleza muy concurrentes / paralelos y debido a que estas acciones deben ser atómicas y MyISAM no admite transacciones reales, el uso de MyISAM para servidores web es arriesgado / propenso a errores.

También en la mayoría de los casos, InnoDB funcionará mucho mejor, por múltiples motivos, uno de ellos es capaz de utilizar el bloqueo de nivel de registro en lugar de bloqueo a nivel de tabla. No solo en una situación donde las escrituras son más frecuentes que las lecturas, también en situaciones con uniones complejas en grandes conjuntos de datos. Notamos un aumento de rendimiento de 3 veces solo usando tablas InnoDB sobre tablas MyISAM para uniones muy grandes (tomando varios minutos).

Diría que, en general, InnoDB (utilizando un modelo de datos 3NF completo con integridad referencial) debería ser la opción predeterminada al usar MySQL. MyISAM solo debe usarse en casos muy específicos. Lo más probable es que rinda menos, como resultado una aplicación más grande y con errores.

Habiendo dicho ésto. Datamodelling es un arte que rara vez se encuentra entre los diseñadores / programadores web. Sin ofender, pero explica que MyISAM sea usado tanto.


32
2017-08-26 12:18



InnoDB ofrece:

ACID transactions
row-level locking
foreign key constraints
automatic crash recovery
table compression (read/write)
spatial data types (no spatial indexes)

En InnoDB, todos los datos en una fila a excepción de TEXT y BLOB pueden ocupar 8,000 bytes como máximo. No hay indexación de texto completo disponible para InnoDB. En InnoDB, el COUNT (*) s (cuando WHERE, GROUP BY o JOIN no se usa) se ejecuta más despacio que en MyISAM porque el recuento de filas no se almacena internamente. InnoDB almacena datos e índices en un archivo. InnoDB usa un grupo de búferes para almacenar tanto los datos como los índices.

MyISAM ofrece:

fast COUNT(*)s (when WHERE, GROUP BY, or JOIN is not used)
full text indexing
smaller disk footprint
very high table compression (read only)
spatial data types and indexes (R-tree)

MyISAM tiene bloqueo a nivel de tabla, pero no bloqueo a nivel de fila. Sin transacciones. Sin recuperación automática de fallos, pero sí ofrece la funcionalidad de la tabla de reparación. Sin restricciones de clave externa. Las tablas MyISAM generalmente son de tamaño más compacto en el disco en comparación con las tablas InnoDB. Las tablas MyISAM pueden reducirse aún más en tamaño al comprimir con myisampack si es necesario, pero se vuelven de solo lectura. MyISAM almacena índices en un archivo y datos en otro. MyISAM utiliza búferes de claves para almacenar índices en el caché y deja la administración del almacenamiento en caché de datos en el sistema operativo.

En general, recomendaría InnoDB para la mayoría de los propósitos y MyISAM solo para usos especializados. InnoDB ahora es el motor predeterminado en las nuevas versiones de MySQL.


29
2018-05-28 07:03



Si usas MyISAM, no estarás haciendo alguna transacciones por hora, a menos que considere que cada declaración DML sea una transacción (que en cualquier caso, no será duradera o atómica en el caso de una falla).

Por lo tanto, creo que debes usar InnoDB.

300 transacciones por segundo suena bastante. Si realmente necesita que estas transacciones sean duraderas en caso de fallo de alimentación, asegúrese de que su subsistema de E / S pueda gestionar fácilmente tantas escrituras por segundo. Necesitará al menos un controlador RAID con caché respaldado por batería.

Si puede tomar un pequeño golpe de durabilidad, puede usar InnoDB con innodb_flush_log_at_trx_commit establecido en 0 o 2 (ver documentos para más detalles), puede mejorar el rendimiento.

Hay una serie de parches que pueden aumentar la concurrencia de Google y otros, estos pueden ser de interés si aún no puede obtener el rendimiento suficiente sin ellos.


24
2017-09-16 21:34



tenga en cuenta que mi educación formal y experiencia es con Oracle, mientras que mi trabajo con MySQL ha sido completamente personal y en mi propio tiempo, así que si digo cosas que son verdad para Oracle pero que no son ciertas para MySQL, me disculpo. Si bien los dos sistemas comparten mucho, la teoría / álgebra relacional es la misma, y ​​las bases de datos relacionales siguen siendo bases de datos relacionales, ¡todavía hay muchas diferencias!

Me gusta particularmente (así como el bloqueo a nivel de fila) que InnoDB se base en transacciones, lo que significa que puede estar actualizando / insertando / creando / alterando / soltando / etc varias veces para una "operación" de su aplicación web. El problema que surge es que si solo algunos de esos cambios / operaciones terminan comprometiéndose, pero otros no, la mayoría de las veces (dependiendo del diseño específico de la base de datos) terminan con una base de datos con datos / estructuras en conflicto.

Nota: Con Oracle, las sentencias create / alter / drop se denominan sentencias "DDL" (definición de datos) y desencadenan implícitamente una confirmación. Insertar / actualizar / eliminar declaraciones, llamado "DML" (Manipulación de datos), son no confirmado automáticamente, pero solo cuando se ejecuta un DDL, una confirmación o una salida / abandono (o si configura su sesión como "confirmación automática", o si su cliente se confirma automáticamente). Es imperativo tenerlo en cuenta al trabajar con Oracle, pero no estoy seguro de cómo maneja MySQL los dos tipos de declaraciones. Debido a esto, quiero dejar en claro que no estoy seguro de esto cuando se trata de MySQL; solo con Oracle

Un ejemplo de cuando los motores basados ​​en transacciones se destacan:

Digamos que usted o usted están en una página web para inscribirse para asistir a un evento gratuito, y uno de los principales propósitos del sistema es permitir que solo 100 personas se inscriban, ya que ese es el límite de los asientos. Para el evento. Una vez que se alcanzan 100 registros, el sistema inhabilitará más registros, al menos hasta que otros cancelen.

En este caso, puede haber una mesa para los invitados (nombre, teléfono, correo electrónico, etc.) y una segunda tabla que rastrea el número de invitados que se han registrado. Por lo tanto, tenemos dos operaciones para una "transacción". Ahora supongamos que después de agregar la información del invitado a la tabla INVITADOS, hay una pérdida de conexión o un error con el mismo impacto. La tabla INVITADOS se actualizó (insertó en), pero la conexión se perdió antes de que los "asientos disponibles" pudieran actualizarse.

Ahora tenemos un invitado agregado a la mesa de invitados, pero la cantidad de asientos disponibles ahora es incorrecta (por ejemplo, el valor es 85 cuando en realidad es 84).

Por supuesto hay muchas maneras de manejar esto, como rastrear asientos disponibles con "100 menos número de filas en la mesa de invitados" o algún código que verifique que la información sea consistente, etc. Pero con un motor de base de datos basado en transacciones como InnoDB, TODAS de las operaciones están comprometidas, o NINGUNA de ellos son Esto puede ser útil en muchos casos, pero como he dicho, no es la ÚNICA manera de estar seguro, no (de una manera agradable, sin embargo, manejada por la base de datos, no por el programador / guionista).

Eso es todo "basado en transacciones" esencialmente significa en este contexto, a menos que me falta algo, que o bien toda la transacción tiene éxito como debería, o nada se cambia, ya que hacer solo cambios parciales podría convertir un problema menor en SEVERE de la base de datos, incluso corrompiéndolo ...

Pero lo diré una vez más, no es la única forma de evitar hacer un desastre. Pero es uno de los métodos que maneja el motor, lo que le permite codificar / secuencia de comandos con solo preocuparse por "si la transacción fue exitosa o no, y qué hago si no lo hago (como volver a intentar)", en lugar de hacerlo manualmente escribir código para verificarlo "manualmente" desde fuera de la base de datos, y hacer mucho más trabajo para tales eventos.

Por último, una nota sobre el bloqueo de tablas frente al bloqueo de filas:

RENUNCIA:  Puedo estar equivocado en todo lo que sigue con respecto a MySQL, y las situaciones hipotéticas / de ejemplo son cosas que hay que investigar, pero puedo estar equivocado en lo que exactamente es posible causar daños con MySQL. Sin embargo, los ejemplos son muy reales en la programación general, incluso si MySQL tiene más mecanismos para evitar tales cosas ...

De todos modos, estoy bastante seguro de estar de acuerdo con aquellos que han argumentado que cuántas conexiones están permitidas a la vez hace no trabajar alrededor de una mesa bloqueada. De hecho, múltiples conexiones son el punto de bloquear una mesa!  Para que otros procesos / usuarios / aplicaciones no puedan dañar la base de datos realizando cambios al mismo tiempo.

¿Cómo funcionarían dos o más conexiones en la misma fila para REALMENTE MAL DÍA para usted? Supongamos que hay dos procesos que desean / necesitan actualizar el mismo valor en la misma fila, digamos porque la fila es un registro de un recorrido en autobús, y cada uno de los dos procesos simultáneamente quiere actualizar los "jinetes" o "asientos disponibles". campo como "el valor actual más 1."

Hagamos esto hipotéticamente, paso a paso:

  1. El proceso uno lee el valor actual, digamos que está vacío, por lo tanto '0' hasta el momento.
  2. El proceso dos lee también el valor actual, que sigue siendo 0.
  3. El proceso uno escribe (actual + 1) que es 1.
  4. Proceso dos debería estar escribiendo 2, pero dado que lee el valor actual antes de proceso uno escribe el nuevo valor, también escribe 1 en la tabla.

estoy no seguro que dos conexiones podrían entremezclarse así, ambas leyendo antes de que la primera escriba ... Pero si no, entonces todavía vería un problema con:

  1. El proceso uno lee el valor actual, que es 0.
  2. El proceso uno escribe (actual + 1), que es 1.
  3. El proceso dos lee el valor actual ahora. Pero mientras que el proceso DID escribe (actualización), no ha comprometido los datos, por lo tanto, solo ese mismo proceso puede leer el nuevo valor que actualizó, mientras que todos los demás ven el valor anterior, hasta que haya un compromiso.

Además, al menos con las bases de datos de Oracle, existen niveles de aislamiento, por lo que no perderé nuestro tiempo tratando de parafrasear. Aquí hay un buen artículo sobre ese tema, y ​​cada nivel de aislamiento teniendo sus pros y sus contras, lo que estaría de acuerdo con la importancia de los motores basados ​​en transacciones en una base de datos ...

Por último, es probable que existan salvaguardas diferentes en MyISAM, en lugar de claves externas e interacción basada en transacciones. Bueno, para empezar, está el hecho de que toda una tabla está bloqueada, lo que hace menos probable que las transacciones / FK sean necesario.

Y, por desgracia, si conoce estos problemas de simultaneidad, sí puede jugar con menos seguridad y solo escribir sus aplicaciones, configurar sus sistemas para que tales errores no sean posibles (su código es responsable, en lugar de la base de datos en sí). Sin embargo, en mi opinión, diría que siempre es mejor usar tantas salvaguardas como sea posible, programar a la defensiva, y siempre ser conscientes de que el error humano es imposible de evitar por completo. Le sucede a todos, y cualquiera que diga que es inmune debe mentir, o no ha hecho más que escribir una aplicación / script "Hello World". ;-)

Espero que ALGO sea útil para alguien, y aún más, así que espero no haber sido culpable de suposiciones y ser humano por error. Mis disculpas si es así, pero los ejemplos son buenos para pensar, investigar el riesgo de, etc., incluso si no son potenciales en este contexto específico.

No dudes en corregirme, editar esta "respuesta", incluso votar por ella. Solo intente mejorar, en lugar de corregir una mala suposición con otra. ;-)

Esta es mi primera respuesta, así que, por favor, perdonen la duración debido a todos los descargos de responsabilidad, etc ... ¡Simplemente no quiero sonar arrogante cuando no estoy absolutamente seguro!


12
2018-04-21 01:54