5.5 Optimisation du serveur MySQL
5 Optimisation de MySQL
Manuel de Référence MySQL 4.1 : Version Française
. Réglage du système, au moment de la compilation, et paramètres du démarrage . Réglage des paramètres du serveur . Influences de la compilation et des liaisons sur la vitesse de MySQL . Comment MySQL gère la mémoire . Comment MySQL utilise le DNS ->Syntaxe de SET
|
5.5.6 Syntaxe de SET
SET [GLOBAL | SESSION] variable_sql=expression, [[GLOBAL | SESSION] variable_sql=expression...]
|
SET
permet de configurer plusieurs options qui affectent le comportement
de votre serveur ou de votre client.
Les exemples suivants montrent les différentes syntaxes qu'on peut utiliser pour
configurer des variables :
Dans les anciennes versions de MySQL nous avons permis l'utilisation de la syntaxe
SET OPTION
, mais celle-ci est à présent désapprouvée.
Dans la version 4.0.3 de MySQL nous avons ajouté les options
GLOBAL
et
SESSION
et l'accès aux variables de configuration les plus importantes.
LOCAL
peut être utilisé en tant que synonyme de
SESSION
.
Si vous configurez plusieurs variables sur une seule ligne de commande, le dernier
mode
GLOBAL | SESSION
utilisé est pris en compte.
SET sort_buffer_size=10000; SET @@local.sort_buffer_size=10000; SET GLOBAL sort_buffer_size=1000000, SESSION sort_buffer_size=1000000; SET @@sort_buffer_size=1000000; SET @@global.sort_buffer_size=1000000, @@local.sort_buffer_size=1000000;
|
La syntaxe
@@nom_variable
est supportée pour rendre MySQL compatible
avec d'autres bases de données.
Le différentes variables système qu'on peut configurer sont décrites dans la section
variables système de ce manuel. Variables système .
Si vous utilisez
SESSION
(par défaut) l'option que vous configurez garde
son effet jusqu'à ce que la session courante se termine, ou que vous modifiez à
nouveau cette option. Si vous utilisez
GLOBAL
, qui requière le privilège
SUPER
, l'option est gardée en mémoire et utilisée pour les nouvelles
connexion jusqu'au redémarrage du serveur. Si vous voulez qu'un changement reste
permanent, vous devez l'effectuer dans l'un des fichiers d'options de MySQL.
Fichier d'options
my.cnf
.
Pour éviter un mauvais usage, MySQL donnera une erreur si vous utilisez
SET
GLOBAL
avec une variable qui ne peut être inutilisée que par
SET SESSION
ou
si vous n'utilisez pas
SET GLOBAL
avec une variable globale.
Si vous voulez configurer une variable
SESSION
à une valeur
GLOBAL
ou
une valeur
GLOBAL
à la valeur par défaut de MySQL, vous pouvez la configurer
à
DEFAULT
.
SET max_join_size=DEFAULT;
|
Ceci est identique à :
SET @@session.max_join_size=@@global.max_join_size;
|
Si vous voulez poser une limite sur la valeur maximale qu'une valeur
de variable peut prendre avec la commande
SET
, vous pouvez le spécifier
en utilisant l'option de ligne de commande
--maximum-nom-variable
.
Options en ligne de commande de
mysqld
.Vous pouvez obtenir une liste de la plupart des variables avec
SHOW VARIABLES
.
SHOW VARIABLES
. Vous pouvez obtenir la valeur d'une variable spécifique avec
la syntaxe
@@[global.|local.]nom_variable
:
SHOW VARIABLES like "max_join_size"; SHOW GLOBAL VARIABLES like "max_join_size"; SELECT @@max_join_size, @@global.max_join_size;
|
Vous trouverez ici une description des variables qui utilisent une syntaxe non-standard
de
SET
. Les définitions des autres variables peuvent être trouvées
dans la section des variables système, avec les options de démarrage ou dans la
description de
SHOW VARIABLES
. Variables système . Options en ligne de commande de
mysqld
.
SHOW VARIABLES
.
-
CHARACTER SET nom_jeu_de_caractères DEFAULT
-
Cela change le jeu de caractère dans toutes les chaînes du et vers le client avec le jeu donné.
Jusqu'à maintenant, la seule option pour
nom_jeu_de_caractères
est
cp1251_koi8
, mais vous pouvez facilement ajouter d'autres possibilités en éditant le
fichier
sql/convert.cc
dans la distribution des sources MySQL. Le jeu de caractères
par défaut peut être restauré en utilisant la valeur
DEFAULT
de
nom_jeu_de_caractères
DEFAULT
.
Notez que la syntaxe pour configurer l'option
CHARACTER SET
diffère
de la syntaxe pour configurer les autres options.
-
PASSWORD = PASSWORD('un mot de passe')
-
Change le mot de passe pour l'utilisateur courant. Chaque utilisateur non
anonyme peut changer son propre mot de passe !
-
PASSWORD FOR utilisateur = PASSWORD('un mot de passe')
-
Configure un mot de passe pour un utilisateur spécifique sur le serveur courant.
Seul un utilisateur avec l'accès à la base de données
mysql
peut faire cela.
L'utilisateur doit être donné au format
utilisateur@hote
, où
utilsateur
et
hote
sont exactement comme ils sont listés dans les colonnes
User
et
Host
des entrées de la table
mysql.user
. Par exemple, si vous avez une entrée
avec dans les champs
User
et
Host
les entrées
'bob'
et
'%.loc.gov'
,
vous écrirez :
mysql> SET PASSWORD FOR bob@"%.loc.gov" = PASSWORD("nouveaupass");
|
Ce qui est équivalent à :
mysql> UPDATE mysql.user SET password=PASSWORD("nouveaupass") -> WHERE user="bob' AND host="%.loc.gov";
|
-
SQL_AUTO_IS_NULL = 0 | 1
-
Si définie à
1
(par défaut) alors on peut trouver la
dernière ligne insérée dans une table avec une colonne
AUTO_INCREMENT
avec la construction suivante :
WHERE auto_increment_column IS NULL
. Ceci est utilisé par des
programmes ODBC tel que
Access.
-
AUTOCOMMIT= 0 | 1
-
Si définie à
1
tous les changements dans une table se feront
en une seule fois. Pour démarrer une transaction multi-commandes, vous devez
utiliser la commande
BEGIN
. Syntaxe des
BEGIN/COMMIT/ROLLBACK
. Si définie à
0
vous devez utiliser
COMMIT
/
ROLLBACK
pour accepter/annuler cette transaction.
Syntaxe des
BEGIN/COMMIT/ROLLBACK
. Notez que quand vous passez du mode non
AUTOCOMMIT
vers le mode
AUTOCOMMIT
, MySQL fera un
COMMIT
automatique sur toutes les transactions
en cours.
-
BIG_TABLES = 0 | 1
-
Si définie à
1
, toutes les tables temporaires sont stockées
sur le disque plutôt qu'en mémoire. Cela sera un peu plus lent, mais vous
n'obtiendrez jamais l'erreur
The table nom_de_table is full
pour les grands
SELECT
qui requièrent une table temporaire.
La valeur par défaut pour une nouvelle connexion est
0
(qui est d'utiliser
la mémoire pour les tables temporaires).
Cette option se nommait avant
SQL_BIG_TABLES
.
-
SQL_BIG_SELECTS = 0 | 1
-
Si configuré à
0
, MySQL interrompra les requêtes
SELECT
qui prendront probablement trop de temps. C'est utile lorsqu'une clause
WHERE
déconseillée a été utilisée. Une grosse requête
est définie comme étant un
SELECT
qui devra probablement étudier
plus de
max_join_size
lignes. La valeur par défaut d'une nouvelle connexion
est
1
(qui permet toutes les requêtes
SELECT
).
-
SQL_BUFFER_RESULT = 0 | 1
-
SQL_BUFFER_RESULT
forcera les résultats des requêtes
SELECT
à être placés dans une table temporaire. Cela aidera MySQL à
libérer les verrous sur table plus tôt et améliorera les cas où
le jeu de résultats de la requête prend trop de temps à être
envoyée au client.
-
LOW_PRIORITY_UPDATES = 0 | 1
-
Si vous configurez à
1
, toutes les commandes
INSERT
,
UPDATE
,
DELETE
, et
LOCK TABLE WRITE
attendent qu'il n'y ai plus de
SELECT
ou
LOCK TABLE READ
sur la table affectée.
Cette option s'appelait avant
SQL_LOW_PRIORITY_UPDATES
.
-
MAX_JOIN_SIZE = valeur | DEFAULT
-
Ne pas permettre les requêtes
SELECT
qui auront besoin d'examiner plus de
valeur
combinaisons de lignes. En configurant cette valeur, vous pouvez capturer
les requêtes
SELECT
où les clefs sont mal utilisées et qui
prendront probablement beaucoup de temps à s'exécuter. Prendre une autre
valeur que
DEFAULT
mettre à zéro l'option
SQL_BIG_SELECTS
.
Si vous reconfigurez l'option
SQL_BIG_SELECTS
, la variable
SQL_MAX_JOIN_SIZE
sera ignorée.
Vous pouvez configurer une valeur par défaut pour cette variable en démarrant
mysqld
avec
-O max_join_size=#
.
Cette option s'appelait avant
SQL_MAX_JOIN_SIZE
.
Notez que si le résultat de la requête est déjà dans le cache
de requêtes, le test précédent ne sera pas effectué. A la place,
MySQL renverra le résultat au client. Puisque le résultat de la requête
est déjà traité et qu'il sera aisé au serveur d'envoyer la
réponse au client.
-
QUERY_CACHE_TYPE = OFF | ON | DEMAND
-
-
QUERY_CACHE_TYPE = 0 | 1 | 2
-
Configure le cache de requêtes pour ce processus.
Option
|
Description
|
0 ou OFF |
Ne pas cacher ou récupérer les résultats.
|
1 ou ON |
Met tous les résultats en cache à part les requêtes
SELECT SQL_NO_CACHE ...
.
|
2 ou DEMAND |
Ne met en cache que les requêtes
SELECT SQL_CACHE ...
.
|
-
SQL_SAFE_UPDATES = 0 | 1
-
Si définit à
1
, MySQL annulera si un
UPDATE
ou un
DELETE
est exécuté alors qu'il n'utilise pas de clef ou de
LIMIT
dans la clause
WHERE
. Cela permet de bloquer les requêtes erronées crées à
la main.
-
SQL_SELECT_LIMIT = valeur | DEFAULT
-
Le nombre maximal des enregistrements que doivent retourner les requêtes
SELECT
.
Si un
SELECT
possède une clause
LIMIT
, celle-ci est utilisée. La
valeur par défaut pour une nouvelle connexion est ``illimitée.'' Si vous avez
changé la limite, la valeur par défaut peut être retrouvée en
utilisant la valeur
DEFAULT
avec
SQL_SELECT_LIMIT
.
-
SQL_LOG_OFF = 0 | 1
-
Si définie à
1
, aucune entrée ne sera écrite dans le log
standard pour ce client, si le client à le privilège
SUPER
. Cela n'affecte
pas le log des mises à jours !
-
SQL_LOG_UPDATE = 0 | 1
-
Si définie à
0
, aucune trace des requêtes ne sera gardée
dans le log des mises à jour pour le client, si le client à le privilège
SUPER
. Cela n'affecte pas le log standard !
-
SQL_QUOTE_SHOW_CREATE = 0 | 1
-
Si vous le configurez à
1
,
SHOW CREATE TABLE
protégera les
noms de tables et de colonnes. Ceci est
activé
par défaut,
pour que la réplication des tables avec des noms à risques fonctionne.
SHOW CREATE TABLE
.
-
TIMESTAMP = valeur_timestamp | DEFAULT
-
Configure le temps pour ce client. C'est utilisé pour obtenir le timestamp d'origine
si vous utilisez le log de mises à jour pour restaurer des lignes.
valeur_timestamp
doit être un timestamp Unix, et non un timestamp MySQL.
-
LAST_INSERT_ID = #
-
Configure la valeur qui doit être retournée par
LAST_INSERT_ID()
. C'est
enregistré dans le log de mises à jour quand vous utilisez
LAST_INSERT_ID()
dans une commande qui met à jour une table.
-
INSERT_ID = #
-
Configure la valeur à utiliser par l'appel suivant à la commande
INSERT
ou
ALTER TABLE
lors de l'insertion d'une valeur
AUTO_INCREMENT
.
Cela est souvent utilisé par le log des mises à jour.
|