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
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
Chapitre 3 : Sélectionnez le type de données approprié
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
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
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.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)
Chapitre 4 : Jeu de caractères
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;
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 :
prepareStatement = Bind-variable, ne pas utiliser l'épissage sql
Utiliser la fonction de conversion fournie par l'application
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
Récupérer les lignes contenant des valeurs maximales/minimales : MAX([DISTINCE] expr), MIN([DISTINCE] expr)
Utilisez rand()/rand(n) habilement pour extraire des lignes aléatoires
Utilisez les clauses group by et with rollup pour faire des statistiques
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
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
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.
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)
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
pour vérifier que toutes les insertions utilisent bien les index nécessaires.
É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.
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.
La séparation de la lecture et de l'écriture améliore les performances
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
Afficher les valeurs par défaut des paramètres du serveur : mysqld --verbose --help
Afficher les valeurs réelles des paramètres du serveur : shell> mysqladmin variables or mysql> SHOW VARIABLES
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
key_buffer_size : keycache
table_cache : ouvert dans la base de données Nombre de caches
innode_buffer_pool_size : La taille du tampon mémoire pour la mise en cache des données et des index InnoDB
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.
Distribuer les E/S à l'aide de baies de disques ou de volumes de fichiers virtuels
Distribuer les E/S à l'aide de liens symboliques
Chapitre 16 : Optimisation des applications
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.
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
pour augmenter la couche de cache
É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!
mysql modifier le nom de la table de données
MySQL crée une procédure stockée
La différence entre MongoDB et MySQL
Comment vérifier si le mot de passe MySQL est oublié
mysql créer une base de données
niveau d'isolement des transactions par défaut de MySQL
La différence entre sqlserver et mysql
mysqlmot de passe oublié
![[Web front-end] Démarrage rapide de Node.js](https://img.php.cn/upload/course/000/000/067/662b5d34ba7c0227.png)
