J'ai travaillé avec Microsoft SQL Server depuis de nombreuses années mais je n'ai commencé que récemment à utiliser MySQL avec mes applications web, et je suis avide de connaissances.
Pour poursuivre la longue lignée des Questions sur les "fonctions cachées". J'aimerais connaître toutes les fonctions cachées ou pratiques de MySQL, ce qui me permettrait d'améliorer mes connaissances de cette base de données open source.
Puisque vous avez mis une prime, je vais partager mes secrets durement gagnés...
En général, tous les SQL que j'ai mis au point aujourd'hui ont nécessité l'utilisation de sous-requêtes. Venant du monde des bases de données Oracle, les choses que je considérais comme acquises ne fonctionnaient pas de la même manière avec MySQL. Et mes lectures sur le tuning de MySQL me font conclure que MySQL est derrière Oracle en termes d'optimisation des requêtes.
Alors que les requêtes simples requises pour la plupart des applications B2C peuvent fonctionner correctement avec MySQL, la plupart des requêtes de type rapport agrégé nécessaires pour le rapportage d'intelligence semblent nécessiter une bonne dose de planification et de réorganisation des requêtes SQL pour guider MySQL afin qu'il les exécute plus rapidement.
max_connections est le nombre de connexions simultanées. La valeur par défaut est de 100 connexions (151 depuis la version 5.0) - très faible.
Note :
Les connexions prennent de la mémoire et votre système d'exploitation peut ne pas être en mesure de gérer un grand nombre de connexions.
Les binaires MySQL pour Linux/x86 vous permettent d'avoir jusqu'à 4096 connexions simultanées, mais les binaires auto-compilés sont souvent moins limités.
Définissez table_cache pour correspondre au nombre de vos tables ouvertes et de vos connexions simultanées. Surveillez la valeur de open_tables et si elle augmente rapidement, vous devrez augmenter sa taille.
Note :
Les 2 paramètres précédents peuvent nécessiter beaucoup de fichiers ouverts. 20+max_connections+table_cache*2 est une bonne estimation de ce dont vous avez besoin. MySQL sous Linux a une option open_file_limit, définissez cette limite.
Si vous avez des requêtes complexes, sort_buffer_size et tmp_table_size sont susceptibles d'être très importants. Les valeurs dépendront de la complexité de la requête et des ressources disponibles, mais 4Mb et 32Mb, respectivement, sont des points de départ recommandés.
Remarque : Il s'agit de valeurs "par connexion", parmi read_buffer_size, read_rnd_buffer_size et quelques autres, ce qui signifie que cette valeur peut être nécessaire pour chaque connexion. Par conséquent, tenez compte de votre charge et des ressources disponibles lorsque vous définissez ces paramètres. Par exemple, sort_buffer_size n'est alloué que si MySQL doit effectuer un tri. Note : faites attention à ne pas manquer de mémoire.
Si vous avez de nombreuses connexions établies (c'est-à-dire un site Web sans connexions persistantes), vous pouvez améliorer les performances en définissant thread_cache_size à une valeur non nulle. 16 est une bonne valeur pour commencer. Augmentez la valeur jusqu'à ce que vos threads_created ne croissent pas très rapidement.
Il ne peut y avoir qu'une seule colonne AUTO_INCREMENT par table, elle doit être indexée et ne peut pas avoir de valeur par défaut.
KEY est normalement un synonyme d'INDEX. L'attribut de clé PRIMARY KEY peut également être spécifié en tant que KEY simple lorsqu'il est indiqué dans une définition de colonne. Ceci a été mis en place pour des raisons de compatibilité avec d'autres systèmes de bases de données.
Une PRIMARY KEY est un index unique où toutes les colonnes clés doivent être définies comme NOT NULL.
Si un index PRIMARY KEY ou UNIQUE est composé d'une seule colonne de type entier, vous pouvez également faire référence à la colonne en tant que "_rowid" dans les instructions SELECT.
Dans MySQL, le nom d'une PRIMARY KEY est PRIMARY
Actuellement, seules les tables InnoDB (v5.1 ?) prennent en charge les clés étrangères.
En général, vous créez tous les index dont vous avez besoin lorsque vous créez des tables. Toute colonne déclarée comme PRIMARY KEY, KEY, UNIQUE ou INDEX sera indexée.
NULL signifie "n'ayant pas de valeur". Pour tester la présence de NULL, il faut ne peut pas utiliser les opérateurs de comparaison arithmétique tels que =, <, ou <>. Utilisez plutôt les opérateurs IS NULL et IS NOT NULL :
NO_AUTO_VALUE_ON_ZERO supprime l'incrémentation automatique pour 0 afin que seul NULL génère le prochain numéro de séquence. Ce mode peut être utile si 0 a été stocké dans la colonne AUTO_INCREMENT d'une table. (Le stockage de 0 n'est pas une pratique recommandée, soit dit en passant).
Pour modifier la valeur du compteur AUTO_INCREMENT à utiliser pour les nouvelles lignes :
ALTER TABLE mytable AUTO_INCREMENT = value; ou SET INSERT_ID = valeur ;
Sauf indication contraire, la valeur commencera par : 1000000 ou la spécifier ainsi :
...) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1
Les valeurs des colonnes TIMESTAMP sont converties du fuseau horaire actuel en UTC pour le stockage, et de UTC vers le fuseau horaire actuel pour la récupération.
http://dev.mysql.com/doc/refman/5.1/en/timestamp.html Pour une colonne TIMESTAMP dans une table, vous pouvez attribuer l'horodatage actuel comme valeur par défaut et comme valeur de mise à jour automatique.
Si vous utilisez l'un de ces types dans une clause WHERE, il est préférable de procéder comme suit WHERE datecolumn = FROM_UNIXTIME(1057941242) et non WHERE UNIX_TIMESTAMP(datecolumn) = 1057941242. En faisant cette dernière, vous ne profiterez pas d'un index sur cette colonne.
http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html
UNIX_TIMESTAMP()
FROM_UNIXTIME()
UTC_DATE()
UTC_TIME()
UTC_TIMESTAMP()si vous convertissez une date en timestamp unix dans MySQL :
Et puis ajoutez-y 24 heures :
Et si on le reconvertit en date, il perd magiquement une heure !
Voici ce qui se passe. Lors de la conversion de l'horodatage unix en date, le fuseau horaire est pris en compte et il se trouve qu'entre le 28 et le 29 octobre 2006, nous sommes passés à l'heure d'été et avons perdu une heure.
À partir de la version 4.1.3 de MySQL, les fonctions CURRENT_TIMESTAMP(), CURRENT_TIME(), CURRENT_DATE() et FROM_UNIXTIME() renvoient des valeurs dans le format de connexion fuseau horaire actuel qui est disponible comme valeur de la variable système time_zone. De plus, UNIX_TIMESTAMP() suppose que son argument est une valeur de date dans le fuseau horaire actuel.
Le paramètre du fuseau horaire actuel n'affecte pas les valeurs affichées par des fonctions telles que UTC_TIMESTAMP() ou les valeurs des colonnes DATE, TIME ou DATETIME.
NOTE : SUR LA MISE À JOUR UNIQUEMENT met à jour l'heure si un champ est modifié Si une mise à jour ne modifie aucun champ, l'heure n'est PAS mise à jour !
De plus, le premier TIMESTAMP est toujours AUTOUPDATE par défaut, même s'il n'est pas spécifié.
Lorsque je travaille avec des dates, je me réfère presque toujours à la date julienne parce que les mathématiques des données sont alors une simple question d'addition ou de soustraction d'entiers, et aux secondes depuis minuit pour la même raison. Il est rare que j'aie besoin d'une résolution temporelle d'une granularité plus fine que les secondes.
Les deux peuvent être stockés sous la forme d'un entier de 4 octets, et si l'espace est vraiment restreint, ils peuvent être combinés en temps UNIX (secondes depuis l'époque 1/1/1970) sous la forme d'un entier non signé qui sera valable jusqu'à environ 2106 :
' sec dans 24Hrs = 86400
Signed Integer max val = 2,147,483,647 - peut contenir 68 années de secondes.
Valeur maximale du nombre entier non signé = 4 294 967 295 - peut contenir 136 années de secondes.
MySQL 4.1 a introduit un protocole binaire qui permet d'envoyer et de renvoyer des valeurs de données autres que des chaînes de caractères. et renvoyées au format natif sans conversion vers et depuis le format chaîne. (Très utile)
De plus, mysql_real_query() est plus rapide que mysql_query() car elle n'appelle pas strlen() pour opérer sur la chaîne de l'énoncé.
http://dev.mysql.com/tech-resources/articles/4.1/prepared-statements.html Le protocole binaire prend en charge les instructions préparées côté serveur et permet la transmission de valeurs de données au format natif. Le protocole binaire a fait l'objet de nombreuses révisions au cours des premières versions de MySQL 4.1.
Vous pouvez utiliser la macro IS_NUM() pour vérifier si un champ est de type numérique. Passez la valeur du type à la macro IS_NUM() et elle obtient la valeur TRUE si le champ est numérique :
Une chose à noter est que les données binaires CAN être envoyé à l'intérieur d'une requête régulière si vous l'échappez et n'oubliez pas que MySQL requiert uniquement que la barre oblique inverse et le caractère quote soient échappés. Il s'agit donc d'un moyen très simple d'INSERER des chaînes binaires plus courtes, comme des mots de passe cryptés/salés, par exemple.
http://www.experts-exchange.com/Database/MySQL/Q_22967482.html
http://www.databasejournal.com/features/mysql/article.php/10897_3355201_2
ACCORDER LA RÉPLICATION ESCLAVE SUR . à utilisateur_esclave IDENTIFIÉ PAR 'mot_de_passe_esclave'.
#Master Binary Logging Config STATEMENT causes replication
to be statement-based - default
log-bin=Mike
binlog-format=STATEMENT
server-id=1
max_binlog_size = 10M
expire_logs_days = 120
#Slave Config
master-host=master-hostname
master-user=slave-user
master-password=slave-password
server-id=2Le fichier journal binaire doit être lu :
http://dev.mysql.com/doc/refman/5.0/en/binary-log.html
http://www.mydigitallife.info/2007/10/06/how-to-read-mysql-binary-log-files-binlog-with-mysqlbinlog/
http://dev.mysql.com/doc/refman/5.1/en/mysqlbinlog.html
http://dev.mysql.com/doc/refman/5.0/en/binary-log.html
http://dev.mysql.com/doc/refman/5.1/en/binary-log-setting.html
Vous pouvez supprimer tous les fichiers journaux binaires avec l'instruction RESET MASTER, ou un sous-ensemble d'entre eux avec PURGE MASTER.
--result-file=binlog.txt TrustedFriend-bin.000030
http://dev.mysql.com/tech-resources/articles/intro-to-normalization.html
http://www.koders.com/cpp/fid10666379322B54AD41AEB0E4100D87C8CDDF1D8C.aspx
http://souptonuts.sourceforge.net/readme_mysql.htm
http://dev.mysql.com/doc/refman/5.1/en/storage-requirements.html
http://www.informit.com/articles/article.aspx?p=1238838&seqNum=2
http://bitfilm.net/2008/03/24/saving-bytes-efficient-data-storage-mysql-part-1/
Une chose à noter est que sur une table mixte contenant à la fois des CHAR et des VARCHAR, mySQL transformera les CHAR en VARCHAR.
RecNum integer_type UNSIGNED NOT NULL AUTO_INCREMENT, PRIMARY KEY (RecNum)
MySQL représente toujours les dates avec l'année en premier, conformément à la norme SQL et aux spécifications ISO 8601.
La désactivation de certaines fonctionnalités de MySQl aura pour résultat des fichiers de données plus petits et un accès plus rapide. et un accès plus rapide. Par exemple :
--datadir spécifiera le répertoire de données et
--skip-innodb désactivera l'option inno et vous fera économiser 10-20M
Plus d'informations ici http://dev.mysql.com/tech-resources/articles/mysql-c-api.html
Télécharger le chapitre 7 - Gratuit
InnoDB est transactionnel mais il y a une surcharge de performance qui l'accompagne. J'ai trouvé que les tables MyISAM étaient suffisantes pour 90% de mes projets. Les tables non transactionnelles (MyISAM) présentent plusieurs avantages qui leur sont propres :
il n'y a pas de frais de transaction :
Beaucoup plus rapide
Faible besoin en espace disque
Moins de mémoire nécessaire pour effectuer les mises à jour
Chaque table MyISAM est stockée sur le disque dans trois fichiers. Les fichiers ont des noms qui commencent par le nom de la table et ont une extension pour indiquer le type de fichier. Un fichier .frm stocke le format de la table. Le fichier de données a une extension .MYD (MYData). Le fichier d'index porte l'extension .MYI (MYIndex).
Ces fichiers puede être copié vers un emplacement de stockage intact sans utiliser la fonction de sauvegarde des administrateurs MySQL qui prend du temps (tout comme la restauration)
L'astuce consiste à faire une copie de ces fichiers puis à supprimer la table. Lorsque vous remettez les fichiers MySQl les reconnaîtra et mettra à jour le suivi de la table.
Si vous devez sauvegarder/restaurer,
La restauration d'une sauvegarde, ou l'importation à partir d'un fichier de vidage existant, peut prendre beaucoup de temps en fonction du nombre d'index et de clés primaires que vous avez sur chaque table. Vous pouvez accélérer ce processus de façon spectaculaire en modifiant votre fichier de vidage original en l'entourant de ce qui suit :
SET AUTOCOMMIT = 0;
SET FOREIGN_KEY_CHECKS=0;
.. your dump file ..
SET FOREIGN_KEY_CHECKS = 1;
COMMIT;
SET AUTOCOMMIT = 1;Pour augmenter considérablement la vitesse du rechargement, ajoutez la commande SQL SET AUTOCOMMIT = 0 ; au début du fichier de vidage, et ajoutez la commande COMMIT ; à la fin.
Par défaut, l'autocommit est activé, ce qui signifie que chaque commande d'insertion dans le fichier de vidage sera traitée comme une transaction distincte et écrite sur le disque avant que la commande ne soit exécutée. le fichier dump sera traitée comme une transaction distincte et écrite sur le disque avant que la suivante ne soit lancée. Si vous n'ajoutez pas ces commandes, le rechargement d'une grande base de données dans InnoDB peut prendre plusieurs heures...
La taille maximale d'une ligne dans une table MySQL est de 65 535 octets.
La longueur maximale effective d'un VARCHAR dans MySQL 5.0.3 et suivants = taille maximale d'une ligne (65 535 octets)
Les valeurs VARCHAR ne sont pas complétées lorsqu'elles sont stockées. Les espaces de fin de ligne sont conservés lorsque sont stockées et récupérées, conformément à la norme SQL.
Les valeurs CHAR et VARCHAR de MySQL sont comparées sans tenir compte des espaces de fin.
L'utilisation de CHAR n'accélérera votre accès que si l'ensemble de l'enregistrement est de taille fixe. En d'autres termes, si vous utilisez un objet de taille variable, autant que tous les objets soient de taille variable. Vous ne gagnez aucune vitesse en utilisant un CHAR dans une table qui contient également un VARCHAR.
La limite VARCHAR de 255 caractères a été portée à 65535 caractères à partir de MySQL 5.0.3.
Les recherches en texte intégral ne sont prises en charge que pour les tables MyISAM.
http://dev.mysql.com/doc/refman/5.0/en/fulltext-search.html
Les colonnes BLOB ne possèdent pas de jeu de caractères, et le tri et la comparaison sont basés sur les valeurs numériques des octets de la colonne.
Si le mode SQL strict n'est pas activé et que vous attribuez à une colonne BLOB ou TEXT une valeur qui dépasse la longueur maximale de la colonne, la valeur est tronquée pour s'adapter et un avertissement est affiché. dépasse la longueur maximale de la colonne, la valeur est tronquée pour s'adapter et un avertissement est généré.
vérifier le mode strict : SELECT @@global.sql_mode ;
Désactiver le mode strict :
SET @@global.sql_mode= '' ;
SET @@global.sql_mode='MYSQL40' (en anglais)
ou supprimer : sql-mode="STRICT_TRANS_TABLES,...
MONTRER LES COLONNES DE mytable
SELECT max(namecount) AS virtualcolumn FROM mytable ORDER BY virtualcolumn
http://dev.mysql.com/doc/refman/5.0/en/group-by-hidden-fields.html
http://dev.mysql.com/doc/refman/5.1/en/information-functions.html#function_last-insert-id last_insert_id()
vous donne le PK de la dernière ligne insérée dans le fil actuel max(pkcolname) vous donne le dernier PK global.
Remarque : si la table est vide, max(pkcolname) renvoie 1 mysql_insert_id() convertit le type de retour de la fonction native de l'API C de MySQL mysql_insert_id() en un type de long (appelé int en PHP).
Si votre colonne AUTO_INCREMENT a un type de colonne BIGINT, la valeur retournée par mysql_insert_id() sera incorrecte. Utilisez plutôt la fonction SQL interne de MySQL LAST_INSERT_ID() dans une requête SQL.
http://dev.mysql.com/doc/refman/5.0/en/information-functions.html#function_last-insert-id
Il faut savoir que lorsque vous essayez d'insérer des données dans un tableau et que vous obtenez l'erreur :
Unknown column ‘the first bit of data what you want to put into the table‘ in ‘field list’en utilisant quelque chose comme
INSERT INTO table (this, that) VALUES ($this, $that)c'est parce que vous n'avez pas d'apostrophes autour des valeurs que vous essayez d'insérer dans le tableau. Vous devriez donc changer votre code en :
INSERT INTO table (this, that) VALUES ('$this', '$that') rappelons que les `` sont utilisés pour définir des champs MySQL, des bases de données ou des tables, et non des valeurs ;)
Perte de connexion au serveur pendant la requête :
http://dev.mysql.com/doc/refman/5.1/en/gone-away.html
http://dev.mysql.com/doc/refman/5.1/en/packet-too-large.html
http://dev.mysql.com/doc/refman/5.0/en/server-parameters.html
http://dev.mysql.com/doc/refman/5.1/en/show-variables.html
http://dev.mysql.com/doc/refman/5.1/en/option-files.html
http://dev.mysql.com/doc/refman/5.1/en/error-log.html
http://www.artfulsoftware.com/infotree/queries.php?&bw=1313
Cela devrait suffire pour obtenir le bonus, je pense... Le fruit de nombreuses heures et de nombreux projets avec une grande équipe d'ingénieurs. gratuit base de données. Je développe des serveurs de données d'application sur des plateformes Windows, principalement avec MySQL. Le pire problème que j'ai eu à résoudre est le suivant
Le cauchemar ultime des bases de données MySQL
Cela a nécessité une série d'applications pour transformer les tableaux en quelque chose d'utile en utilisant plusieurs des astuces mentionnées ici.
Si vous avez trouvé ce document étonnamment utile, exprimez vos remerciements en votant pour lui.
Consultez également mes autres articles et livres blancs à l'adresse suivante : www.coastrd.com.
Une commande pour savoir quelles tables sont actuellement dans le cache :
mysql> SHOW open TABLES FROM test;
+----------+-------+--------+-------------+
| DATABASE | TABLE | In_use | Name_locked |
+----------+-------+--------+-------------+
| test | a | 3 | 0 |
+----------+-------+--------+-------------+
1 row IN SET (0.00 sec)
Une commande pour savoir qui fait quoi :
mysql> show processlist;
show processlist;
+----+-------------+-----------------+------+---------+------+----------------------------------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+-------------+-----------------+------+---------+------+----------------------------------+------------------+
| 1 | root | localhost:32893 | NULL | Sleep | 0 | | NULL |
| 5 | system user | | NULL | Connect | 98 | Waiting for master to send event | NULL |
| 6 | system user | | NULL | Connect | 5018 | Reading event from the relay log | NULL |
+-----+------+-----------+---------+---------+-------+-------+------------------+
3 rows in set (0.00 sec)Et vous pouvez tuer un processus avec :
mysql>kill 5
Prograide est une communauté de développeurs qui cherche à élargir la connaissance de la programmation au-delà de l'anglais.
Pour cela nous avons les plus grands doutes résolus en français et vous pouvez aussi poser vos propres questions ou résoudre celles des autres.
