Mots écrits auparavant : Ce n'est qu'après qu'un sinistre se produit que l'on se souvient de l'importance de la reprise après sinistre. Ce n'est qu'après avoir subi une perte que l'on se souvient du rappel de quelqu'un

Cet article concerne principalement la base de données MySQL

Principes de conception

1. Ne faites pas de calculs dans la base de données : les calculs CPU doivent être déplacés vers la couche métier
2 Contrôlez la quantité de données dans une seule table : les enregistrements dans une seule table sont contrôlés à 1000w3. Contrôlez le nombre de colonnes : le nombre de champs est contrôlé dans les 20
4. Équilibrez le paradigme et la redondance : sacrifiez la conception du paradigme et les données redondantes pour améliorer l'efficacité
5. Rejetez le code SQL volumineux et les transactions volumineuses. , gros lots
6. Utilisez UTF8 pour le jeu de caractères du tableau
7. Utilisez le moteur de stockage INNODB

tinyint(1Byte)
smallint(2Byte)
mediumint(3Byte)
int(4Byte)
bigint(8Byte)
bad case：int(1)/int(11)

3. Utilisez enum ou définissez d'abord,
enum ('F', ' M')sex4, évitez d'utiliser des champs NULL

NULL字段很难查询优化
NULL字段的索引需要额外空间
NULL字段的复合索引无效
bad case：`name` char(32) default null`age` int not null
good case：`age` int not null default 0

6. Ne stockez pas d'images dans la base de données
7. . Pour la table MyISAM, s'il n'y a pas de colonnes de longueur variable (colonnes VARCHAR, TEXT ou BLOB), utilisez des formats d'enregistrement de taille fixe. C'est plus rapide mais cela peut malheureusement gaspiller de l'espace. Même si vous avez utilisé l'option CREATE pour rendre la colonne VARCHAR ROW_FORMAT=fixed, vous pouvez toujours être invité à utiliser des lignes de longueur fixe
8. Utilisez un exemple de jeu de caractères, tel que latin1. Essayez d'utiliser le moins possible utf-8, car utf-8 prend 3 fois plus d'espace que latin1. Vous pouvez utiliser latin1 sur les champs qui n'ont pas besoin d'utiliser utf-8, comme mail, url, etc.
9. Conversion entre précision et espace. Les types à virgule flottante utilisent généralement moins d'espace que les types DECIMAL lors du stockage de données dans la même plage numérique. Le champ FLOAT utilise 4 octets pour stocker les données. Le type DOUBLE nécessite 8 octets et a une précision plus élevée et une plage numérique plus grande. Les données de type DECIMAL seront converties en type DOUBLE
10. Le nom de la bibliothèque, le nom de la table et le nom du champ doivent avoir une longueur de dénomination fixe, 12. Les noms de bibliothèques, les noms de tables et les noms de champs ne doivent pas dépasser 32 caractères. La signification du nom doit être comprise ; les mots réservés MySQL sont interdits dans les noms de bibliothèques, les noms de tables et les noms de champs ; les noms de bibliothèques et de tables temporaires doivent être préfixés par tmp et suffixés par la date ; suffixé par la date
11. La longueur physique de l'enregistrement de ligne de la table InnoDB ne dépasse pas 8 Ko. La page de données par défaut d'InnoDB est de 16 Ko. Selon les caractéristiques de B+Tree, une page de données doit stocker au moins 2 enregistrements. . Par conséquent, lorsque la longueur de stockage réelle d'une grande colonne dépasse 8 Ko (en particulier les colonnes TEXT/BLOB), cela provoquera un « stockage par débordement de page », similaire à la « migration de lignes » dans ORACLE. Par conséquent, si de grandes colonnes doivent être utilisées (en particulier. type TEXT/BLOB) et si elles sont lues et écrites fréquemment, il est préférable de diviser ces colonnes en sous-tables et de ne pas les stocker avec la table principale. Si elles ne sont pas trop fréquentes, vous pouvez envisager de les conserver dans la table principale. table principale. Si vous modifiez l'option innodb
pagesize sur 8 Ko, il est recommandé que la longueur physique de l'enregistrement de ligne ne dépasse pas 4 Ko.

改善查询、减慢更新
索引一定不是越多越好（能不加就不加，要加的一定得加）
覆盖记录条数过多不适合建索引，例如“性别”

2. Un index de préfixe doit être construit pour les champs de caractères
3 N'effectuez pas d'opérations de colonne dans l'index, mauvais cas : sélectionnez l'identifiant où age +1 = 10;
4. colonnes à incrémentation automatique pour les clés primaires innodb

主键建立聚簇索引
主键不应该被修改
字符串不应该做主键
如果不指定主键，innodb会使用唯一且非空值索引代替

5. N'utilisez pas de clés étrangères, veuillez vous assurer des contraintes par le programme
6. Évitez de créer des index sur des préfixes qui ont déjà des index. Par exemple : si l'index (a, b) existe, supprimez l'index (a)
7. Contrôlez la longueur d'un seul index. Utilisez la clé (nom (8)) pour créer un index sur les premiers caractères des données
8. Utilisez les index de manière sélective. Il n'est pas très bon d'utiliser des index sur des colonnes qui changent rarement, comme les colonnes de sexe
9. Optimiser la table peut compresser et trier l'index, veillez à ne pas l'exécuter fréquemment
10. Analyser la table peut mettre à jour les données 11. La sélectivité de l'index est le rapport entre les valeurs d'index uniques, également appelées cardinalité, et le nombre de lignes de données dans la table. Sélectivité de l'index = cardinalité/lignes de données, count(distinct(username))/count(*) est la sélectivité de l'index. , index élevé L'avantage de la sélectivité est que MySQL peut filtrer plus de lignes lors de la recherche de correspondances. L'index unique a la meilleure sélectivité, avec une valeur de 1
12. N'utilisez pas d'index en double ou redondants. Moteur INNODB, à chaque fois Pour modifier les données, vous devez modifier l'index de clé primaire et la valeur d'index correspondante dans l'index auxiliaire. Cela peut entraîner une grande quantité de migration, de pagination et de fragmentation des données
13. Pour les colonnes de chaîne avec. une longueur supérieure à 20, il est préférable de créer un index de colonne non entier (par exemple : ALTER TABLE t1 ADD INDEX(user(20))) peut améliorer efficacement l'utilisation de l'index, mais son inconvénient est que le préfixe est utilisé. l'index n'est pas utilisé lors du tri de cette colonne. La longueur de l'index de préfixe peut être déterminée en fonction des statistiques du champ. Généralement, elle est légèrement supérieure à la longueur moyenne
14. Utilisez régulièrement l'outil pt-duplicate-key-checker pour vérifier et supprimer les index en double. Par exemple, si l'index idx1(a, b) couvre déjà l'index idx2(a), vous pouvez supprimer l'index idx2

2. Transactions simples, le temps de transaction doit être aussi court que possible, mauvais cas : télécharger une transaction d'image
3. Évitez d'utiliser trig/func, les déclencheurs et les fonctions sont. non utilisé, remplacez-les par des programmes clients
4 Pas besoin de sélectionner *, Consomme du processeur, des IO, de la mémoire et de la bande passante
5 OU est réécrit en IN()

or的效率是n级别
in的消息时log(n)级别
in的个数建议控制在200以内
select id from t where phone=’159′ or phone=’136′ =>select id from t where phone in (’159′, ’136′);

La fusion des index MySQL est très retardée

select id from t where phone = '159' or name = 'john';
=>
select id from t where phone='159' union  select id from t where name='jonh';

7. Évitez les % négatifs tels que not in/like

9. Limitez la pagination efficace
.
Plus la limite est grande, plus l'efficacité est faible

select id from t limit 10000, 10;
=>
select id from t where id > 10000 limit 10;

10. Utilisez union all au lieu de union. Union a une surcharge de déduplication

12. Utilisez le groupe par, le regroupement et le tri automatique
13. >14. Utilisez le chargement des données pour importer des données. Le chargement des données est environ 20 fois plus rapide que l'insertion ;
15. Les données de mise à jour doivent être divisées et mises à jour par lots.
16. Utiliser des outils d'analyse des performances

Sql explain  /  showprofile   /    mysqlsla

show profile;
mysqlsla;
mysqldumpslow;
explain;
show slow log;
show processlist;
show query_response_time(percona)

optimiser Lorsque les données sont insérées, mises à jour et supprimées, certaines migrations et paginations de données sont inévitables, puis certains fragments apparaîtront au fil du temps, des fragments s'accumulent et affectent les performances. Cela nécessite que l'administrateur de base de données optimise régulièrement la base de données pour réduire les fragments. , ce qui se fait via la commande d'optimisation. Par exemple, en opérant sur la table MyISAM : optimiser le nom de la table

18. Il est interdit d'exécuter des requêtes volumineuses dans la base de données
19. Utilisez des instructions précompilées et ne transmettez que des paramètres, ce qui est plus efficace que de transmettre des instructions SQL une fois et de les utiliser plusieurs fois ;
20. Il est interdit d'utiliser order by rand()
21 Il est interdit de mettre à jour plusieurs tables en même temps avec une seule instruction SQL
22. n'est pas bon en opérations mathématiques et en jugement logique)
23, les instructions SQL nécessitent toute la R&D, les mots-clés SQL sont tous en majuscules et chaque mot ne peut avoir qu'un seul espace
24. Si vous pouvez utiliser NOT IN, vous peut utiliser NOTIN. Il y a trop de pièges. . Détectera vide et NULL

Attention

1. Même s'il s'agit d'un filtrage conditionnel basé sur des index, si l'optimiseur se rend compte que la quantité totale de données à analyser dépasse 30 % (elle semble être de 20 % dans ORACLE, et actuellement 30 % dans MySQL, il pourra être ajusté à l'avenir), il changera directement le plan d'exécution en une analyse complète de la table et n'utilisera plus l'index
2. Lors de la jointure de plusieurs tables, le filtrage doit être le plus grand (pas nécessairement le plus petit montant de données, mais celui avec le filtrage le plus important après avoir ajouté la condition WHERE) ) est sélectionné comme table de pilotes. De plus, s'il y a un tri après JOIN, le champ de tri doit appartenir à la table des pilotes, afin que l'index de la table des pilotes puisse être utilisé pour terminer le tri
3. Dans la plupart des cas, le coût du tri est généralement plus haut, donc si vous voyez There is Using filesort dans le plan d'exécution, créez d'abord l'index de tri
4. Utilisez pt-query-digest pour analyser régulièrement le journal des requêtes lentes et combinez-le avec Box Anemometer pour créer une requête lente système d'analyse et d'optimisation des logs

J'ai vu quelque chose d'intéressant aujourd'hui :
Quels algorithmes/structures de données dois-je « reconnaître » et connaître par leur nom ?