MySQL optimisation de la performance: commande par champ datetime

J'ai une table avec environ 100.000 billets de blog, liée à une table avec 50 alimente via un 1:n relation. Quand j'ai une requête à la fois des tables avec une instruction select, commandé par un champ date /heure de l'affichage de la table, MySQL utilise toujours filesort, entraînant une très lent temps de requête (>1 seconde). Voici le schéma de la postings table (simplifié):

+---------------------+--------------+------+-----+---------+----------------+
| 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       |                |
+---------------------+--------------+------+-----+---------+----------------+

Et voici le feed table:

+-------------+--------------+------+-----+---------+----------------+
| 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    |                |
+-------------+--------------+------+-----+---------+----------------+

Et voici la requête qui prend >1 seconde à exécuter. Veuillez noter que le post_date champ a un indice, mais MySQL n'est pas à l'utiliser pour trier les écritures de la table:

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

Le résultat de la explain extended commande sur cette requête indique que MySQL à l'aide de 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                 |
+----+-------------+----------+--------+---------------------------------------+-----------+---------+--------------------------+-------+-----------------------------+

Quand j'enlève le order by partie, MySQL cesse à l'aide de filesort. S'il vous plaît laissez-moi savoir si vous avez des idées sur la manière d'optimiser cette requête pour obtenir MySQL afin de trier et de sélectionner les données à l'aide des index. J'ai déjà essayé quelques petites choses comme la création d'un indice combiné sur tous où/de la commande par les champs, comme l'ont suggéré quelques billets de blog, mais cela ne fonctionne pas non plus.

J'aime beaucoup la présentation claire et détaillée que vous avez demandé ce.

InformationsquelleAutor Dennis G. | 2009-04-03

Créer un indice composite, soit sur postings (is_active, post_date) (dans cet ordre).

Il sera utilisé à la fois pour le filtrage sur is_active et de commande par post_date.

MySQL devrait montrer REF méthode d'accès sur cet indice dans la EXPLAIN EXTENDED.

Remarque que vous avez un RANGE conditions de filtrage sur user_offtopic_count, c'est pourquoi vous ne pouvez pas utiliser un index sur ce champ à la fois dans le filtrage et le tri par d'autres champ.

En fonction de la sélectivité est votre user_offtopic_count (j'. e. combien de lignes de satisfaire user_offtopic_count < 10), il peut être plus utile de créer un index sur user_offtopic_count et de laisser le post_dates être triés.

Pour ce faire, créez un index composite sur postings (is_active, user_offtopic_count) et assurez-vous que le RANGE méthode d'accès plus cet indice est utilisé.

De l'index sera plus rapide dépend de vos données distribuion. Créer deux index, FORCE eux et de voir laquelle est la plus rapide:

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 */

Cela a fonctionné pour moi, merci beaucoup! J'ai dû utiliser l'indice de force pour obtenir le meilleur indice utilisé. Nous sommes maintenant à l'aide de plusieurs indices combinés pour les différentes requêtes.
Fonctionne comme un charme, l'indice de force est vraiment là où il est, lors de l'ajout de sous-requêtes, l'optimiseur semble jeter des choses dans le drain.

InformationsquelleAutor Quassnoi

3

MySQL a deux filesort algorithmes: une ancienne filesort qui trie les enregistrements sur le disque, et une nouvelle version qui fonctionne dans la mémoire.

Si elle ne peut pas utiliser un index sur la première table de la jointure pour trier la requête, il devra faire un filesort. Si le jeu de résultats avant de les trier converti à largeur fixe le format est plus grand que le type de tampon OU si elle contient des champs de texte, il faut utiliser le plus lent sur disque filesort algorithme (la deuxième condition est satisfaite puisque votre requête a un champ de texte).

MySQL est de choisir d'utiliser le is_active colonne, soi-disant parce qu'il pense que la colonne est plus sélective, en éliminant les lignes avant de poursuivre avec l'autre le rejoint et où les conditions de travail. La première chose que je dirais serait d'essayer de créer des index composites avec post_date, feed_id, et les colonnes dans la condition where, par exemple (is_active, user_offtopic_count, post_date, feed_id).
- Merci pour l'explication!
InformationsquelleAutor ʞɔıu
3

Aussi, il est important de se rappeler que MySQL de ne pas utiliser un index si la colonne que vous souhaitez commander en a une fonction qui lui est appliqué.

Vous devriez aussi essayer d'aliasing offres.post_date que quelque chose d'autre. Cela indique à MySQL à l'ordre par la inchangée colonne, et vous pourrez toujours sélectionner le timestamp unix de l'.

InformationsquelleAutor Chris Henry

Vous devez vous connecter pour publier un commentaire.