Blog

Trucs et astuces MySQL

Écrit le 27 05 2013 par Kévin MET _

Un billet fourre-tout dans lequel je vais recenser les différents trucs et astuces que j'ai sur MySQL. Je continuerai de mettre à jour ce billet à chaque fois que j'aurai chopé un nouveau truc sympa.

Création d'index utiles

Un serveur MySQL peut être lent pour beaucoup de raisons. Le truc le plus simple pour essayer de le booster un peu est de créer des indexs appropriés à vos requêtes. C'est très simple à mettre en place et ça permet de beaucoup gagner en perf. Il faut commencer par activer les slow_query_log.


mysql> SET GLOBAL long_query_time=2;
Query OK, 0 rows affected (0.00 sec)

On va pouvoir observer les requêtes qui mettent plus de deux secondes à s'executer. On peut aussi mettre log_queries_not_using_indexes sur ON qui permet de logguer toutes les requêtes SELECT n'utilisant pas d'index.

Voici un exemple de ce qu'on peut trouver dans les logs : (en vérité j'avais pas de serveur sous le coude avec des requêtes qui durent plus de deux secondes mais on va faire comme si)


# User@Host: mnttech[mnttech] @ localhost []  Id: 13017
# Query_time: 2.000211  Lock_time: 0.000056 Rows_sent: 5  Rows_examined: 10
SET timestamp=1369674583;
SELECT   wp_posts.* FROM wp_posts  WHERE 1=1  AND wp_posts.post_parent = 1803  AND wp_posts.post_type = 'revision' AND (wp_posts.post_status = 'inherit')  ORDER BY wp_posts.post_date DESC;

On va simplement rejouer la requête via le CLI en ajoutant EXPLAIN devant.


mysql> EXPLAIN SELECT   wp_posts.* FROM wp_posts  WHERE 1=1  AND wp_posts.post_parent = 1503  AND wp_posts.post_type = 'revision' AND (wp_posts.post_status = 'inherit')  ORDER BY wp_posts.post_date DESC;
+----+-------------+----------+------+------------------------------+-------------+---------+-------+------+----------------------------------------------------+
| id | select_type | table    | type | possible_keys                | key         | key_len | ref   | rows | Extra                                              |
+----+-------------+----------+------+------------------------------+-------------+---------+-------+------+----------------------------------------------------+
|  1 | SIMPLE      | wp_posts | ref  | type_status_date,post_parent | post_parent | 8       | const |    5 | Using index condition; Using where; Using filesort |
+----+-------------+----------+------+------------------------------+-------------+---------+-------+------+----------------------------------------------------+
1 row in set (0.03 sec)

Là on voit que tout se passe bien et d'ailleurs vous voyez que j'ai triché plus en haut en changeant la valeur du Query_time. Pourquoi je dit que tout se passe bien ? Car on voit dans la colonne key que cette requête utilise bien un index. Si jamais elle n'utilisait pas d'index vous auriez eu NULL à la place. Le nombre de colonnes parcouru est également important. On le retrouve dans la colonne rows. Plus le chiffre est bas et plus la requête s’exécutera rapidement.

Dans le cas ou l'index n'aurait pas été créé il aurait fallu l'ajouter. L'utilisation des index dans MySQL est soumis à quelques règles simples qui sont décrites ici : index mysql

En utilisant ces règles on voit que l'on va devoir créer un index qui regroupe post_parent, post_type et post_status. L'ordre de la requête doit être respectée. Évidemment il faut créer des index sur les requêtes qui reviennent le plus souvent dans les slow_query_logs. Dans notre exemple on fera ceci :


CREATE INDEX parent_type_status ON wp_posts (post_parent, post_type et post_status);

Après création de l'index, MySQL utilisera celui-ci pour exécuter la requête.

Requêtes magiques

Et voici deux requêtes magiques (ou pas) qui peuvent très très approximativement vous donner une valeur de innodb_buffer_pool_size et de key_buffer_size. En gros, les deux paramètres les plus important dans le réglage de votre serveur MySQL si vous avez un mix Innodb/MyIsam. Il vaut mieux les utiliser quand on est très pressé et faire des benchs si on a le temps :


SELECT CONCAT(ROUND(KBS/POWER(1024,IF(pw<0,0,IF(pw>3,0,pw)))+0.49999),
SUBSTR(' KMG',IF(pw<0,0,IF(pw>3,0,pw))+1,1)) recommended_innodb_buffer_pool_size
FROM (SELECT SUM(index_length) KBS FROM information_schema.tables WHERE
engine='InnoDB') A,(SELECT 3 pw) B;

SELECT CONCAT(ROUND(KBS/POWER(1024,IF(pw<0,0,IF(pw>3,0,pw)))+0.49999),
SUBSTR(' KMG',IF(pw<0,0,IF(pw>3,0,pw))+1,1)) recommended_key_buffer_size
FROM (SELECT SUM(index_length) KBS FROM information_schema.tables
WHERE engine='MyISAM' AND table_schema NOT IN ('information_schema','mysql')) A,
(SELECT 3 pw) B;

Export et import à la volée

Voici une commande bien pratique qui permet de faire un mysqldump et d'importer les données sur un autre serveur en passant pas ssh. La commande est à lancé sur le serveur sur lequel on veut importer les données. Dans l'exemple, la base se nomme plop, le serveur distant plop.serveur.com, le user local est root et son mot de passe et rootpasswword :


ssh -C root@plop.serveur.com 'mysqldump --compact --max_allowed_packet=1073741824 --lock-tables=false -u root  -p plop' | mysql -u root -prootpassword plop

Mise à jour d'une partie d'un champ dans MySQL

Lorsque l'on souhaite remplacer le mot plop par le mot blob dans le champ field_name de la table table_name


UPDATE table_name SET filed_name = REPLACE(field_name, 'plop', 'blob') WHERE field_name LIKE '%plop%';

Trouver les clés étrangères sur toutes les tables

Ce truc là est surtout utile lorsque l'on a renommé une table qui avait des clés étrangères. Sinon en faisant un simple :


SHOW CREATE TABLE `nom_de_la_table`;

On obtient les clés étrangères. Mais lorsque la table a été renommée, on ne peut pas voir les références aux anciennes clés étrangères de cette façon et on ne peut pas utiliser les mêmes noms pour ajouter de nouvelles clés étrangères. Il existe donc une solution qui consiste à effectuer ce genre de requête :


SELECT * FROM information_schema.TABLE_CONSTRAINTS  WHERE information_schema.TABLE_CONSTRAINTS.CONSTRAINT_TYPE = 'FOREIGN KEY';*

On peut ensuite affiner en utilisant un ou plusieurs WHERE comme cela :


SELECT * FROM information_schema.TABLE_CONSTRAINTS  
WHERE information_schema.TABLE_CONSTRAINTS.CONSTRAINT_TYPE = 'FOREIGN KEY'  
AND information_schema.TABLE_CONSTRAINTS.TABLE_SCHEMA = 'schema' 
AND information_schema.TABLE_CONSTRAINTS.TABLE_NAME = 'table';

Tables sans clé primaire ou index avec clé unique

Voici une requête pour trouver toutes les tables d'un serveur MySQL qui n'ont pas de clé primaires ou d'index avec clé unique. Cela peut être utile lorsque vous voulez migrez un serveur vers Galera car il est indispensable d'avoir des clés primaires sur toutes vos tables.


select tables.table_schema, tables.table_name, tables.engine
from information_schema.tables
left join (
select table_schema, table_name
from information_schema.statistics
group by table_schema, table_name, index_name
having
sum(
case
when non_unique = 0
and nullable != 'YES' then 1
else 0
end
) = count(*)
) puks
on tables.table_schema = puks.table_schema
and tables.table_name = puks.table_name
where puks.table_name is null
and tables.table_type = 'BASE TABLE';
♥ Partage sur tes réseaux sociaux ♥
Kévin MET
Kévin MET

Auteur de ce blog et gérant de la société MNT-TECH, je publie sur ce blog lorsque le temps me le permet et lorsqu'un sujet qui me parait intéressant n'a pas encore été abordé en français. Toutes les informations techniques présentes sur cette page peuvent être réutilisées moyennant le fait de citer la source.