Apprenez Mysql en termes simples (Collection)

Avant-propos

La base de données a toujours été un point faible pour l'auteur. D'après ma propre expérience (python+sqlalchemy), etc. , je vais faire un enregistrement. Je préfère utiliser ORM, mais j'ai toujours pensé qu'épeler SQL était une tâche relativement pénible (principalement parce que je ne suis pas bon en SQL, j'ai également rencontré des pièges dans les bases de données dans des projets de maintenance précédents), tels que des problèmes d'encodage, une perte de précision en virgule flottante, etc., pour éviter de répéter le piège à l'avenir.

Chapitre 1 : Utiliser l'aide

Utiliser la commande d'aide intégrée de MySQL

1. msyql> ? data types : 查看数据类型
   mysql> ? int
   mysql> ? create table

Chapitre 2 : Sélection du type de table (moteur de stockage)

Les deux plus couramment utilisés moteurs :

1. Myisam est le moteur de stockage par défaut de Mysql Lorsque create crée une nouvelle table et que le moteur de stockage de la nouvelle table n'est pas spécifié, Myisam est utilisé par défaut. Chaque MyISAM est stocké sous forme de trois fichiers sur le disque. Les noms de fichiers sont les mêmes que les noms de tables et les extensions sont .frm (définition de la table de stockage), .MYD (MYData, données de stockage), .MYI (MYIndex, stockage index ). Les fichiers de données et les fichiers d'index peuvent être placés dans différents répertoires pour répartir uniformément les E/S et obtenir des vitesses plus rapides.

2. Le moteur de stockage InnoDB fournit une sécurité des transactions avec des capacités de validation, de restauration et de récupération après incident. Cependant, par rapport au moteur de stockage de Myisam, l'efficacité du traitement d'écriture d'InnoDB est moins efficace et prend plus d'espace disque pour conserver les données et les index.

Environnements courants :

1. MyISAM : le moteur de stockage de plug-in MySQL par défaut, qui est le plus couramment utilisé dans le Web, l'entreposage de données et d'autres environnements d'application Un des moteurs de stockage utilisés

2. InnoDB : utilisé pour les applications de traitement de transactions, avec de nombreuses fonctionnalités, dont le support des transactions ACID.

Chapitre 3 : Sélectionnez le type de données approprié

Sélectionnez d'abord le moteur de stockage approprié, en fonction du stockage spécifié moteur Déterminez le type de données approprié.

• MyISAM : Il est préférable d'utiliser des colonnes de données de longueur fixe plutôt que des colonnes de données de longueur variable.

• InnoDB : Il est recommandé d'utiliser varchar

  
  

Quelques types de données à noter :

1. Char et varchar : les méthodes de sauvegarde et de récupération sont différentes, et la longueur maximale et la conservation des espaces de fin sont également différentes. char a une longueur fixe Si la longueur n'est pas assez longue, utilisez des espaces pour la remplir. Si PAD_CHAR_TO_FULL_LENGTH n'est pas défini lors de la récupération, les espaces de fin seront supprimés par défaut.

varchar longueur variable
chaîne, les espaces de fin seront conservés lors de la récupération. Notez que la requête n'est pas sensible à la casse. Si vous utilisez sqlalchemy pour être sensible à la casse, n'utilisez pas la fonction . func.binary

2. Lorsque le texte,

et le blob effectuent un grand nombre de mises à jour ou de suppressions, de gros "trous" seront laissés. Il est recommandé d'utiliser régulièrement la fonction OPTIMIZE TABLE pour défragmenter ces tables. Évitez de récupérer des valeurs de blob ou de texte volumineuses. Séparez les colonnes de texte et de blob dans des tableaux distincts. blob: text

3. Flottant et décimal à virgule fixe :

Notez quelques points :

1. Bien que les nombres à virgule flottante puissent représenter une plus grande plage de données , mais il y a un problème d'erreur.

2. Pour les problèmes sensibles à la précision tels que la monnaie, un stockage à virgule fixe doit être utilisé. Il y a eu des pièges dans les projets précédents, et j'ai dû utiliser des méthodes de grossissement et de réduction pour résoudre le problème, ce qui était assez moche.

3.

ProgrammationSi vous rencontrez des nombres à virgule flottante, faites attention aux problèmes d'erreur et essayez d'éviter les comparaisons de nombres à virgule flottante (la comparaison de nombres à virgule flottante nécessite une différence inférieure à une valeur spécifique précision), en python3.5 Cela peut être comparé comme ceci : float_eq = partial(math.isclose, rel_tol=1e-09, abs_tol=0.0)

4. Faites attention au traitement de certaines valeurs spéciales​​dans les nombres à virgule flottante.

Chapitre 4 : Jeu de caractères

Pour choisir le jeu de caractères approprié au début , Sinon, un remplacement ultérieur coûtera très cher. Le jeu de caractères dans python2 est un problème de longue date qui déroute de nombreux novices. Le projet précédemment maintenu utilisait le jeu de caractères latin1 par défaut de msyql, ce qui obligeait la chaîne à être codée manuellement en utf8 à chaque fois qu'elle était écrite. Récemment, j'ai utilisé utf8 pour des projets utilisant python3.5+flask, et je n'ai plus jamais rencontré de problèmes d'encodage :

• Créer une base de données Utilisez utf8, CRÉER UNE BASE DE DONNÉES SI NON EXISTS my_db default charset utf8 COLLATE utf8_general_ci;

• l'url de connexion sqlalchemy utilise mysql://root:root@127.0.0.1:3306/my_db?charset=utf8. Plus besoin de vous soucier des

  caractères tronqués
  

Chapitre 5 : Conception et utilisation de l'index

Tous les types de colonnes MySQL peuvent être indexés. L'utilisation d'index sur les colonnes associées est le meilleur moyen d'améliorer les performances des opérations de sélection. Principes de conception d'index :

1. La colonne d'index recherchée n'est pas nécessairement la colonne à sélectionner. Les colonnes les plus appropriées pour l'index sont les colonnes qui apparaissent dans la clause Where , ou les colonnes spécifiées dans la clause join, plutôt que les colonnes qui apparaissent dans la liste de sélection après le mot-clé select.

2. Utilisez un index unique. Pour les colonnes avec des valeurs uniques, l'effet d'indexation est meilleur, tandis que pour les colonnes avec plusieurs valeurs en double, l'effet d'indexation est médiocre.

3. Utilisez des index courts. Si vous indexez une colonne de chaîne, vous devez spécifier une longueur de préfixe, et vous devez le faire autant que possible.

4. Utilisez le préfixe le plus à gauche. Lors de la création d'un index à n colonnes, n index disponibles pour MySQL sont réellement créés. Un index multicolonne peut fonctionner comme plusieurs index car les lignes peuvent être mises en correspondance en utilisant l'ensemble de colonnes le plus à gauche de l'index, qui devient le préfixe le plus à gauche.

5. Ne surindexez pas. Les index gaspillent de l’espace disque et réduisent les performances d’écriture.

6. Considérez les types de comparaisons à effectuer sur les colonnes.

Chapitre 6 : Mécanisme de verrouillage et contrôle des transactions

Le moteur InnoDB fournit des verrous au niveau des lignes et prend en charge les verrous partagés et Deux modes de verrouillage : verrouillage exclusif et quatre niveaux d'isolation différents. mysql prend en charge les transactions locales via des instructions telles que AUTOCOMIT, START TRANSACTIONS, COMMIT et ROLLBACK.

Chapitre 7 : Problèmes de sécurité dans SQL

Injection SQL : exploiter l'interface externe de certaines bases de données Insérez les données utilisateur dans l'opération de base de donnéesvoix (sql) réelle pour atteindre l'objectif d'envahir la base de données ou même le système d'exploitation. La raison principale est que les données saisies par l'utilisateur du tas du programme ne sont pas strictement filtrées, ce qui entraîne l'exécution d'instructions de requête de base de données illégales :

1. prepareStatement = Bind-variable, ne pas utiliser l'épissage sql

2. Utiliser la fonction de conversion fournie par l'application

3. Vérification de fonction personnalisée (vérification de formulaire, etc.)
   

Chapitre 8 : Mode SQL et problèmes associés

Modifier le mode d'exécution MySQL par défaut, tel que le mode strict suivant Lorsque l'insertion ou la mise à jour est incorrecte, mysql donnera une erreur et abandonnera l'opération. set session sql_mode='STRICT_TRANS_TABLES'. La définition de sql_mode nécessite que le personnel chargé de l'application pèse divers gains et pertes et fasse un choix approprié.

Chapitre 9 : Conseils SQL courants

1. Récupérer les lignes contenant des valeurs maximales/minimales : MAX([DISTINCE] expr), MIN([DISTINCE] expr)

2. Utilisez rand()/rand(n) habilement pour extraire des lignes aléatoires

3. Utilisez les clauses group by et with rollup pour faire des statistiques

4. Utilisez bit group functions pour créer des statistiques
   

Chapitre 10 : Autres problèmes nécessitant une attention

Problèmes de casse des noms de bases de données et de noms de tables : différentes plates-formes et systèmes ont une sensibilité à la casse différente. Le conseil est de toujours utiliser des noms en minuscules.
Choses à noter lors de l'utilisation de clés étrangères : InnoDB dans MySQL prend en charge la vérification des contraintes de mots clés étrangers.

Chapitre 11 : Optimisation SQL

Étapes générales pour optimiser SQL :

1. Utiliser afficher l'état et les caractéristiques de l'application pour comprendre la fréquence d'exécution de divers SQL et le taux d'exécution approximatif de divers SQL. Par exemple, le nombre de lignes renvoyées par la requête Innode_rows_read du paramètre InnoDB, le nombre de lignes insérées par Innodb_rows_inserted lors de l'exécution de l'insertion et le nombre de lignes mises à jour par Innodb_rows_updated. Il y a aussi quelques paramètres : les tentatives de connexion au serveur mysql, le temps de travail du serveur Uptime et le nombre de requêtes lentes Slow_queries.

2. Localisez les instructions SQL avec une faible efficacité d'exécution. Il existe deux manières : la première consiste à localiser les instructions avec une faible efficacité d'exécution via des journaux de requêtes lents. Lors du démarrage avec l'option --log-slow-queries[=file_name], mysqld écrit un fichier journal contenant toutes les instructions SQL dont le temps d'exécution dépasse long_query_time. secondes. L'autre consiste à afficher la liste des processus pour afficher les threads MySQL actuels, y compris le statut du thread, s'il faut verrouiller la table, etc. Vous pouvez afficher l'état d'exécution SQL en temps réel et optimiser certaines opérations de verrouillage de la table.

3. Analysez le plan d'exécution de SQL inefficace via EXPLAIN : expliquer peut savoir quand la table doit être indexée pour obtenir un SELECT plus rapide qui utilise l'index pour rechercher des enregistrements. Ce qui suit est après l'exécution d'EXPLAIN Description. des résultats obtenus :

• select_type : type de sélection

• table : la table qui génère l'ensemble de résultats

• type : représente le connexion du type table. Lorsqu'il n'y a qu'une seule ligne dans la table avec la valeur de type étant system, il s'agit du meilleur type de connexion ; lorsqu'un index est utilisé pour la connexion à la table dans l'opération de sélection, la valeur du type est ref lorsque la connexion à la table de sélection ne le fait pas ; utilisez un index, la valeur de type est souvent vue. Si c'est ALL, cela signifie qu'une analyse complète de la table a été effectuée sur la table. À ce stade, vous devez envisager de créer un index pour améliorer l'efficacité de la connexion à la table.

• possible_keys : Indique les colonnes d'index qui peuvent être utilisées lors des requêtes.

• key : Indique l'index utilisé

• key_len : Longueur de l'index

• lignes : Plage de balayage

• Extra : Explication et description de l'exécution

• 
  

4. Confirmer les problèmes et prendre les mesures d'optimisation correspondantes.

Problème d'index

1. Classification de stockage de l'index : le fichier de données et le fichier d'index de la table myisam sont automatiquement séparés, et les données et l'index de innodb sont placés au même endroit dans un tablespace. Les types de stockage d'index de myisam et innodb sont tous deux btree

2. Comment Mysql utilise les index : les index sont utilisés pour trouver rapidement des lignes avec des valeurs spécifiques dans une colonne. La condition la plus importante pour utiliser un index dans une requête est d'utiliser la clé d'index dans la condition de requête. S'il s'agit d'un index multi-colonnes, l'index ne peut être utilisé que lorsque le préfixe le plus à gauche de la clé multi-colonnes est utilisé dans. la condition de requête, sinon cela ne sera pas possible.

3. Afficher l'utilisation de l'index : La valeur de Handler_read_key représente le nombre de fois qu'une ligne a été indexée. Une valeur faible signifie que l'index n'est pas fréquemment utilisé. Une valeur Handler_read_rnd_next élevée signifie que la requête s'exécute de manière inefficace et qu'un index doit être créé pour y remédier. show status like 'Handler_read%';

Deux méthodes d'optimisation simples et pratiques

• Table d'analyse régulière : ANALYZE TABLE, CHECK TABLE, CHECKSUM TABLE

• Utiliser la table OPTIMIZE ;
  

Optimiser du point de vue du client (côté code)

1. Utilisez une connexion persistante à la base de données pour éviter les frais de connexion. Dans le code, on utilise généralement le pool de connexions

2. pour vérifier que toutes les insertions utilisent bien les index nécessaires.

3. Évitez d'exécuter des requêtes de sélection complexes sur des tables fréquemment mises à jour pour éviter les problèmes liés aux verrouillages de tables dus à des conflits de lecture et d'écriture.

4. Profitez pleinement des valeurs par défaut et insérez des valeurs explicitement uniquement si elles diffèrent de la valeur par défaut. Réduisez l'analyse syntaxique que MySQL doit effectuer pour augmenter la vitesse d'insertion.

5. La séparation de la lecture et de l'écriture améliore les performances

6. Essayez de ne pas utiliser de champs de tableau auto-croissants dans les variables pour éviter ce champ ne se produit pas dans des conditions de concurrence élevée. L'auto-incrémentation affecte l'efficacité. Il est recommandé d'implémenter l'auto-incrémentation des champs via les applications.

Chapitre 12 : Optimisation de la base de données Objets

Optimisation des types de données de table : PROCEDURE ANALYZE()Fournir des suggestions d'optimisation pour juger le type de table actuel. L'optimisation réelle peut être combinée avec des informations statistiques.

Améliorez l'efficacité de l'accès aux tables grâce au fractionnement : le fractionnement ici concerne principalement les tables de type Myisam.

• Répartition verticale : en fonction de la fréquence d'accès à l'application, les champs fréquemment consultés et les champs rarement consultés dans la table sont divisés en deux tables fréquemment consultées. long.

• Répartition horizontale : selon la situation de l'application, les données sont délibérément divisées horizontalement en plusieurs tables ou divisées en plusieurs partitions via des partitions, ce qui peut efficacement éviter les problèmes de verrouillage des tables Myisam causés par. récupération et mise à jour.
  

Dénormalisation : la conception normalisée met l'accent sur l'indépendance. Les données doivent être aussi peu redondantes que possible. Plus de redondance signifie occuper plus d'espace physique. Les tests sexuels posent des problèmes. Une redondance appropriée peut réduire l'accès à plusieurs tables et améliorer considérablement l'efficacité des requêtes. Dans ce cas, une redondance appropriée peut être envisagée pour améliorer l'efficacité.

Utilisez des tableaux statistiques redondants : utilisez create temporary table pour l'analyse statistique

Choisissez un type de tableau plus approprié : 1. Si l'application présente de graves conflits de verrouillage, veuillez envisager de modifier délibérément le moteur de stockage d'InnoDB, le mécanisme de verrouillage de ligne peut réduire efficacement l'apparition de conflits de verrouillage. 2. Si l'application comporte de nombreuses opérations de requête et n'a pas d'exigences strictes en matière d'intégrité des transactions, vous pouvez envisager d'utiliser Myisam.

Chapitre 13 : Problèmes de verrouillage

En attente d'acquisition de verrous : variables d'état table_locks_waited et table_locks_immediate pour analyser la table système conflit de verrouillage. Vérifiez Innode_row_lock pour analyser les conflits de verrouillage de ligne.

Chapitre 14 : Optimisation du serveur Mysql

Afficher les paramètres actuels du serveur Mysql

1. Afficher les valeurs par défaut des paramètres du serveur : mysqld --verbose --help

2. Afficher les valeurs réelles des paramètres du serveur : shell> mysqladmin variables or mysql> SHOW VARIABLES

3. Afficher la valeur de l'état de fonctionnement du serveur : mysqladmin extended-status or mysql>SHOW STATUS
   

Paramètres importants qui affectent les performances de MySQL

1. key_buffer_size : keycache

2. table_cache : ouvert dans la base de données Nombre de caches

3. innode_buffer_pool_size : La taille du tampon mémoire pour la mise en cache des données et des index InnoDB

4. innodb_flush_log_at_trx_commit : Il est recommandé de définir à 1, dans chaque transaction Lors de la validation, le tampon de journal est écrit dans le fichier journal et le fichier journal est actualisé par les opérations de disque.

Chapitre 15 : Problèmes d'E/S

La recherche de disque est un énorme goulot d'étranglement en termes de performances.

1. Distribuer les E/S à l'aide de baies de disques ou de volumes de fichiers virtuels

2. Distribuer les E/S à l'aide de liens symboliques
   

Chapitre 16 : Optimisation des applications

1. Utiliser le pool de connexion : le coût d'établissement d'une connexion est relativement élevé, via Établir un pool de connexions pour améliorer les performances d'accès.

2. Réduisez l'accès à Mysql : 1. Évitez la récupération répétée des données de consentement. 2 Utilisez le cache de requêtes MySQL

3. pour augmenter la couche de cache

4. Équilibrage de charge : 1. Utilisez MySQL pour copier et décharger les opérations de requête. 2 Base de données distribuée Architecture

Résumé

Ce qui précède est le contenu détaillé de. pour plus d'informations, suivez d'autres articles connexes sur le site Web de PHP en chinois!