tips - mysqltuner windows

"SELECT COUNT(*)" es lento, incluso con where clause

Question

Estoy intentando descubrir cómo optimizar una consulta muy lenta en MySQL (no diseñé esto):

SELECT COUNT(*) FROM change_event me WHERE change_event_id > ''1212281603783391'';
+----------+
| COUNT(*) |
+----------+
|  3224022 |
+----------+
1 row in set (1 min 0.16 sec)

Comparando eso con un conteo completo:

select count(*) from change_event;
+----------+
| count(*) |
+----------+
|  6069102 |
+----------+
1 row in set (4.21 sec)

La declaración de explicación no me ayuda aquí:

 explain SELECT COUNT(*) FROM change_event me WHERE change_event_id > ''1212281603783391''/G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: me
         type: range
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 8
          ref: NULL
         rows: 4120213
        Extra: Using where; Using index
1 row in set (0.00 sec)

De acuerdo, todavía cree que se necesitan aproximadamente 4 millones de entradas para contar, ¡pero podría contar líneas en un archivo más rápido que eso! No entiendo por qué MySQL tarda tanto.

Aquí está la definición de la tabla:

CREATE TABLE `change_event` (
  `change_event_id` bigint(20) NOT NULL default ''0'',
  `timestamp` datetime NOT NULL,
  `change_type` enum(''create'',''update'',''delete'',''noop'') default NULL,
  `changed_object_type` enum(''Brand'',''Broadcast'',''Episode'',''OnDemand'') NOT NULL,
  `changed_object_id` varchar(255) default NULL,
  `changed_object_modified` datetime NOT NULL default ''1000-01-01 00:00:00'',
  `modified` datetime NOT NULL default ''1000-01-01 00:00:00'',
  `created` datetime NOT NULL default ''1000-01-01 00:00:00'',
  `pid` char(15) default NULL,
  `episode_pid` char(15) default NULL,
  `import_id` int(11) NOT NULL,
  `status` enum(''success'',''failure'') NOT NULL,
  `xml_diff` text,
  `node_digest` char(32) default NULL,
  PRIMARY KEY  (`change_event_id`),
  KEY `idx_change_events_changed_object_id` (`changed_object_id`),
  KEY `idx_change_events_episode_pid` (`episode_pid`),
  KEY `fk_import_id` (`import_id`),
  KEY `idx_change_event_timestamp_ce_id` (`timestamp`,`change_event_id`),
  KEY `idx_change_event_status` (`status`),
  CONSTRAINT `fk_change_event_import` FOREIGN KEY (`import_id`) REFERENCES `import` (`import_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

Versión:

$ mysql --version
mysql  Ver 14.12 Distrib 5.0.37, for pc-solaris2.8 (i386) using readline 5.0

¿Hay algo obvio que me estoy perdiendo? (Sí, ya he intentado con "SELECT COUNT (change_event_id)", pero no hay diferencia de rendimiento).

Answer 1

Aquí hay algunas cosas que sugiero:

• Cambie la columna de "bigint" a "int unsigned". ¿Realmente esperas tener más de 4.200 millones de registros en esta tabla? Si no, estás perdiendo espacio (y tiempo) en el campo extra ancho. Los índices de MySQL son más eficientes en tipos de datos más pequeños.

• Ejecute el comando " OPTIMIZE TABLE " y vea si su consulta es más rápida luego.

• También puede considerar dividir su tabla de acuerdo con el campo ID, especialmente si los registros anteriores (con valores de ID más bajos) se vuelven menos relevantes con el tiempo. Una tabla particionada a menudo puede ejecutar consultas agregadas más rápido que una enorme tabla sin particiones.

EDITAR:

Mirando más de cerca esta tabla, parece una tabla de estilo de registro, donde las filas se insertan pero nunca se modifican.

Si eso es cierto, es posible que no necesite toda la seguridad transaccional proporcionada por el motor de almacenamiento InnoDB, y es posible que pueda salirse con la suya cambiando a MyISAM , que es considerablemente más eficiente en las consultas agregadas.

Answer 2

Compruebe para ver qué tan fragmentados son sus índices. En mi empresa tenemos un proceso de importación nocturno que destruye nuestros índices y con el tiempo puede tener un profundo impacto en las velocidades de acceso a los datos. Por ejemplo, tuvimos un procedimiento SQL que tardó 2 horas en ejecutarse un día después de la fragmentación de los índices en 3 minutos. usamos SQL Server 2005 para buscar un script que pueda verificar esto en MySQL.

Actualización: echa un vistazo a este enlace: http://dev.mysql.com/doc/refman/5.0/en/innodb-file-defragmenting.html

Answer 3

Crearía una tabla de "contadores" y agregaría los activadores "crear fila" / "eliminar fila" a la tabla que está contando. Los activadores deberían aumentar / disminuir los valores de conteo en la tabla de "contadores" en cada inserción / eliminación, por lo que no tendrá que calcularlos cada vez que los necesite.

También puede lograr esto en el lado de la aplicación almacenando en el caché los contadores, pero esto implicará borrar el "contador de caché" en cada inserción / eliminación.

Para obtener alguna referencia, eche un vistazo a este http://pure.rednoize.com/2007/04/03/mysql-performance-use-counter-tables/

Answer 4

Ejecute " analyze table_name " en esa tabla; es posible que los índices ya no sean óptimos.

A menudo puede decir esto ejecutando " show index from table_name ". Si el valor de cardinalidad es NULL entonces debe forzar el nuevo análisis.

Answer 5

InnoDB usa claves primarias agrupadas, por lo que la clave principal se almacena junto con la fila en las páginas de datos, no en páginas de índice separadas. Para hacer un escaneo de rango, aún debe escanear todas las filas potencialmente anchas en las páginas de datos; tenga en cuenta que esta tabla contiene una columna de TEXTO.

Dos cosas que probaría:

1. ejecutar la optimize table Esto asegurará que las páginas de datos estén almacenadas físicamente en orden ordenado. Esto podría acelerar un escaneo de rango en una clave primaria agrupada.
2. crear un índice no primario adicional solo en la columna change_event_id. Esto almacenará una copia de esa columna en páginas de índice que será mucho más rápido de escanear. Después de crearlo, verifique el plan de explicación para asegurarse de que está usando el nuevo índice.

(También es probable que desee hacer que la columna change_event_id bigint no esté firmada si se incrementa desde cero)

Answer 6

Me he encontrado con un comportamiento como este antes con las bases de datos de geolocalización de IP. Pasado cierto número de registros, la capacidad de MySQL de obtener alguna ventaja de los índices para las consultas basadas en rango aparentemente se evapora. Con los DB de geolocalización, lo manejamos segmentando los datos en fragmentos que eran lo suficientemente razonables para permitir que se utilizaran los índices.

Answer 7

MySQL dice "Usar dónde" primero, ya que necesita leer todos los registros / valores de los datos del índice para contarlos realmente. Con InnoDb, también trata de "agarrar" ese rango de registro de 4 mil para contarlo.

Es posible que deba experimentar con diferentes niveles de aislamiento de transacciones: http://dev.mysql.com/doc/refman/5.1/en/set-transaction.html#isolevel_read-uncommitted

y ver cuál es mejor.

Con MyISAM sería simplemente rápido, pero con un modelo de escritura intensivo se generarán problemas de bloqueo.