segundo - optimizar consultas mysql explain

Optimización del rendimiento MySQL: ordenar por el campo de fecha y hora

Question

Tengo una tabla con aproximadamente 100.000 publicaciones en el blog, vinculada a una tabla con 50 feeds a través de una relación 1: n. Cuando consulto ambas tablas con una instrucción select, ordenada por un campo de fecha y hora de la tabla de publicaciones, MySQL siempre usa filesort, lo que resulta en tiempos de consulta muy lentos (> 1 segundo). Aquí está el esquema de la tabla de postings (simplificado):

+---------------------+--------------+------+-----+---------+----------------+
| Field               | Type         | Null | Key | Default | Extra          |
+---------------------+--------------+------+-----+---------+----------------+
| id                  | int(11)      | NO   | PRI | NULL    | auto_increment |
| feed_id             | int(11)      | NO   | MUL | NULL    |                |
| crawl_date          | datetime     | NO   |     | NULL    |                |
| is_active           | tinyint(1)   | NO   | MUL | 0       |                |
| link                | varchar(255) | NO   | MUL | NULL    |                |
| author              | varchar(255) | NO   |     | NULL    |                |
| title               | varchar(255) | NO   |     | NULL    |                |
| excerpt             | text         | NO   |     | NULL    |                |
| long_excerpt        | text         | NO   |     | NULL    |                |
| user_offtopic_count | int(11)      | NO   | MUL | 0       |                |
+---------------------+--------------+------+-----+---------+----------------+

Y aquí está la tabla de feed :

+-------------+--------------+------+-----+---------+----------------+
| Field       | Type         | Null | Key | Default | Extra          |
+-------------+--------------+------+-----+---------+----------------+
| id          | int(11)      | NO   | PRI | NULL    | auto_increment |
| type        | int(11)      | NO   | MUL | 0       |                |
| title       | varchar(255) | NO   |     | NULL    |                |
| website     | varchar(255) | NO   |     | NULL    |                |
| url         | varchar(255) | NO   |     | NULL    |                |
+-------------+--------------+------+-----+---------+----------------+

Y aquí está la consulta que tarda> 1 segundo en ejecutarse. Tenga en cuenta que el campo post_date tiene un índice, pero MySQL no lo está usando para ordenar la tabla de publicaciones:

SELECT 
    `postings`.`id`, 
    UNIX_TIMESTAMP(postings.post_date) as post_date, 
    `postings`.`link`, 
    `postings`.`title`, 
    `postings`.`author`, 
    `postings`.`excerpt`, 
    `postings`.`long_excerpt`, 
    `feeds`.`title` AS feed_title, 
    `feeds`.`website` AS feed_website
FROM 
    (`postings`)
JOIN 
    `feeds` 
ON 
    `feeds`.`id` = `postings`.`feed_id`
WHERE 
    `feeds`.`type` = 1 AND 
    `postings`.`user_offtopic_count` < 10 AND 
    `postings`.`is_active` = 1
ORDER BY 
    `postings`.`post_date` desc
LIMIT 
    15  

El resultado del comando explain extended en esta consulta muestra que MySQL está utilizando filesort:

+----+-------------+----------+--------+---------------------------------------+-----------+---------+--------------------------+-------+-----------------------------+
| id | select_type | table    | type   | possible_keys                         | key       | key_len | ref                      | rows  | Extra                       |
+----+-------------+----------+--------+---------------------------------------+-----------+---------+--------------------------+-------+-----------------------------+
|  1 | SIMPLE      | postings | ref    | feed_id,is_active,user_offtopic_count | is_active | 1       | const                    | 30996 | Using where; Using filesort |
|  1 | SIMPLE      | feeds    | eq_ref | PRIMARY,type                          | PRIMARY   | 4       | feedian.postings.feed_id |     1 | Using where                 |
+----+-------------+----------+--------+---------------------------------------+-----------+---------+--------------------------+-------+-----------------------------+

Cuando elimino el order by partes, MySQL deja de usar filesort. Indíqueme si tiene alguna idea sobre cómo optimizar esta consulta para que MySQL ordene y seleccione los datos mediante el uso de índices. Ya he intentado algunas cosas, como crear un índice combinado en todos los campos donde / ordenar por, como lo sugieren algunas publicaciones en el blog, pero tampoco funcionó.

Answer 1

Además, es importante recordar que MySQL no usará un índice si la columna que está ordenando tiene una función aplicada.

También debería probar aliasing postings.post_date como algo más. Esto le indicará a MySQL que ordene por la columna inalterada, y usted todavía seleccionará la marca de tiempo de unix.

Answer 2

Cree un índice compuesto en las postings (is_active, post_date) (en ese orden).

Se usará tanto para filtrar is_active como para ordenar por post_date .

MySQL debería mostrar el método de acceso REF sobre este índice en EXPLAIN EXTENDED .

Tenga en cuenta que tiene una condición de filtrado RANGE sobre user_offtopic_count , es por eso que no puede usar un índice sobre este campo tanto en el filtrado como en la clasificación por otro campo.

Dependiendo de cuán selectivo sea su user_offtopic_count (es decir, cuántas filas satisfacen a user_offtopic_count < 10 ), puede ser más útil crear un índice en user_offtopic_count y dejar que se clasifiquen las fechas_posteriores.

Para ello, cree un índice compuesto en las postings (is_active, user_offtopic_count) y asegúrese de utilizar el método de acceso RANGE sobre este índice.

Qué índice será más rápido depende de su distribución de datos. Cree ambos índices, FORCE y vea cuál es más rápido:

CREATE INDEX ix_active_offtopic ON postings (is_active, user_offtopic_count);
CREATE INDEX ix_active_date ON postings (is_active, post_date);

SELECT 
    `postings`.`id`, 
    UNIX_TIMESTAMP(postings.post_date) as post_date, 
    `postings`.`link`, 
    `postings`.`title`, 
    `postings`.`author`, 
    `postings`.`excerpt`, 
    `postings`.`long_excerpt`, 
    `feeds`.`title` AS feed_title, 
    `feeds`.`website` AS feed_website
FROM 
    `postings` FORCE INDEX (ix_active_offtopic)
JOIN 
    `feeds` 
ON 
    `feeds`.`id` = `postings`.`feed_id`
WHERE 
    `feeds`.`type` = 1 AND 
    `postings`.`user_offtopic_count` < 10 AND 
    `postings`.`is_active` = 1
ORDER BY 
    `postings`.`post_date` desc
LIMIT 
    15

/* This should show RANGE access with few rows and keep the FILESORT */

SELECT 
    `postings`.`id`, 
    UNIX_TIMESTAMP(postings.post_date) as post_date, 
    `postings`.`link`, 
    `postings`.`title`, 
    `postings`.`author`, 
    `postings`.`excerpt`, 
    `postings`.`long_excerpt`, 
    `feeds`.`title` AS feed_title, 
    `feeds`.`website` AS feed_website
FROM 
    `postings` FORCE INDEX (ix_active_date)
JOIN 
    `feeds` 
ON 
    `feeds`.`id` = `postings`.`feed_id`
WHERE 
    `feeds`.`type` = 1 AND 
    `postings`.`user_offtopic_count` < 10 AND 
    `postings`.`is_active` = 1
ORDER BY 
    `postings`.`post_date` desc
LIMIT 
    15

/* This should show REF access with lots of rows and no FILESORT */

Answer 3

MySQL tiene dos algoritmos de archivos: un filesort más antiguo que ordena los registros en el disco, y una nueva versión que funciona en la memoria.

Si no puede usar un índice en la primera tabla de la unión para ordenar la consulta, tendrá que hacer una clasificación de archivos. Si el conjunto de resultados antes de la clasificación convertida a formato de ancho fijo es mayor que el búfer de clasificación O si contiene cualquier campo de texto, tendrá que usar el algoritmo de almacenamiento de archivos en disco más lento (la segunda condición se cumple ya que su consulta tiene un campo de texto) .

MySQL está eligiendo usar la columna is_active, aparentemente porque cree que la columna es más selectiva al eliminar filas antes de continuar con las otras uniones y donde las condiciones. Lo primero que sugeriría sería intentar crear índices compuestos con post_date, feed_id y las columnas en la condición where, por ejemplo (is_active, user_offtopic_count, post_date, feed_id).