[Base de données MySQL] Interprétation du Chapitre 4 : Optimisation des schémas et des types de données (Partie 2)

4.2 Pièges dans la conception de schémas MySQL

Étant donné que le mécanisme d'implémentation de MySQL provoque des erreurs spécifiques, comment les éviter, prenons votre temps :

1. Trop de colonnes

Lorsque l'API du moteur de stockage MySQL fonctionne, elle doit copier les données via le format tampon de ligne au niveau de la couche serveur et de la couche moteur de stockage, puis décoder le contenu du tampon en Pour chaque colonne, l'opération de conversion des colonnes codées en données de ligne à partir du tampon de ligne est coûteuse Les lignes de longueur fixe Myisam correspondent exactement à la structure des lignes du serveur et ne nécessitent cependant pas de conversion ; , la structure de lignes de la structure de lignes de longueur variable InnoDB Conversion est toujours requise, Le coût de conversion dépend du nombre de colonnes.

2. Trop d'associations

EAV Entité-Attribut-Valeur : Mauvais modèle de conception, MySQL limite chaque opération d'association à un maximum de 61 tables, mais la base de données EAV nécessite de nombreuses auto-corrélations ; en règle générale, si vous souhaitez que la requête s'exécute rapidement et ait une bonne simultanéité, il est préférable de faire des corrélations dans 12 tables pour une seule requête ; >

3. Empêchez la surutilisation des énumérations

Veillez à éviter la surutilisation des énumérations ; utilisez des clés étrangères pour les associer à des tables de dictionnaire ou à des tables de recherche pour trouver des valeurs spécifiques. colonnes d'énumération

Lors de l'ajout d'une valeur à la table, vous devez effectuer une opération alter table ; MySQL5.0 précédemment modifier la tableblocage Dans la version mise à jour 5.1, ce sera le cas. ne pas être ajouté à la fin de la liste. Il faut également modifier le tableau 4. Il est recommandé de remplacer nul qui n'est pas inventé ici

par 0, valeur spéciale, chaîne vide. ne pas être nul; mais n'allez pas aux extrêmes, dans certains scénarios, il serait préférable d'utiliser null :

create table ……（
//全0 （不可能的日期）会导致很多问题
    dt datetime not null default '0000-00-00 00:00:00'
    ……
）

MySQL stockera les valeurs nulles dans l'index

, Oracle ne le fera pas 4.3 Forme normale et paradigme d'inversion

4.3.1 Avantages et inconvénients

1. Les opérations de mise à jour normalisées sont plus rapides

2. les données sont bien normalisées, il y a rarement des données en double. Seules moins de données doivent être modifiées

3. La table normalisée est plus petite et peut être mieux placée dans la mémoire, et l'opération est effectuée plus rapidement

4. Il y a peu de données et de récupération redondantes Lors de la liste des données, il y a moins besoin d'instructions distinctes et groupées par

Inconvénients :

Nécessite association, ce qui est coûteux et peut invalider l'index

4.3.2 Avantages et inconvénients de l'anti-paradigme

Éviter l'association des données plus volumineuses que la mémoire peut l'être. beaucoup plus rapide que l'association (évitant les E/S aléatoires)

4.4 Tables de cache et tables récapitulatives

Tables de cache :

est très efficace pour optimiser la recherche et les instructions de requête de récupération,

stocke celles qui peuvent être plus simples. Tables qui obtiennent des données d'autres tables (la vitesse d'acquisition est plus lente à chaque fois)

Table récapitulative :

Enregistrez le table qui agrège les données à l'aide de l'instruction group by La décision est en temps réel lorsqu'elle est utilisée Maintenir les données ou reconstruire régulièrement,

Reconstruire régulièrement

 : Économisez des ressources, ayez moins de fragmentation et des index organisés de manière séquentielle ( efficace) Lors de la reconstruction, assurez-vous que les données sont toujours disponibles pendant les opérations, grâce à la

"Table fantôme"

est implémentée Table fantôme : une table créée derrière la table réelle. l'opération est terminée, la table fantôme et la table d'origine peuvent être commutées via l'opération de renommage atomique

4.4.1 Vue matérialisée

Table pré-calculée et stockée sur disque

, qui peut être actualisée et mise à jour via diverses stratégies, mysql ne prend pas en charge natif, peut être implémentée à l'aide de l'outil flexviews de Justin Swanhart :

Composition flexviews :

Modifier la capture de données, lire le journal binaire du serveur et analyser les modifications dans les lignes associées
Une série de procédures stockées qui peut aider à créer et à gérer des définitions de vues
Certaines matérialisations pouvant appliquer des modifications à la base de données. Les outils de visualisation
les vues flexibles peuvent

de manière incrémentielle

recalculer le contenu d'une vue matérialisée en extrayant les modifications de la table source : aucune requête requise Données originales (efficaces)4.4.2 Table compteurTable compteur : met en cache le nombre d'amis de un utilisateur, le nombre de téléchargements de fichiers, etc. Il est recommandé de créer une

table indépendante pour stocker les compteurs

, pour éviter l'échec du cache des requêtes

Les transactions de mise à jour et d'ajout ne peuvent être exécutées qu'en série ; . Pour une concurrence plus élevée, le compteur peut être enregistré sur plusieurs lignes, et une ligne est sélectionnée au hasard pour être mise à jour à chaque fois. Lorsque les résultats doivent être comptés, requête d'agrégation (j'ai lu ceci deux ou trois fois. Cela peut être stupide. . Le même compteur enregistre plusieurs points. Chaque fois que l'un d'entre eux est sélectionné pour être mis à jour, et la somme finale est calculée. Veuillez le lire encore quelques fois) 4.5. Accélérer l'opération de modification de table

La plupart des modifications apportées à la structure de table de MySQL sont : créer une table vide avec de nouveaux résultats, rechercher toutes les données de l'ancienne table et les insérer dans la nouvelle table, et supprimer le vieille table

mysql5.1及更新包含一些类型的“在线”操作的支持，整个过程不需要全锁表，最新版的InnoDB(MySQL5.5和更新版本中唯一的InnoDB)支持通过排序来建索引，建索引更快且紧凑的布局；

一般而言，大部分alter table导致mysql服务中断，对常见场景，使用的技巧：

1、先在一台不提供服务的机器上执行alter table操作，然后和提取服务的主库进行切换

2、影子拷贝，用要求的表结构创建张和源表无关的新表，通过重命名、删表交换两张表（上有）

不是all的alter table都引起表重建，理论上可跳过创建表的步骤：列默认值实际上存在表的.frm文件中，so可直接修改这个文件不需要改动表本身，但mysql还没有采用这种优化方法，all的modify column将导致表重建；

alter column：通frm文件改变列默认值：alter table容许使用alter column、modify column change column修改列，三种操作不一样;

alter table sakila.film alter column rental_duration set default 5;

4.5.1只修改frm文件

mysql有时在没有必要的时候也重建表，如果愿冒一些风险，可做些其他类型的修改而不用重建表：下面操作可能不能正常工作，先备份数据

下面操作不需要重建表：

     1、移除一个列的auto_increment

     2、增加、移除、更改enum和set常量，如果移除的是被用到的常量、查询返回空字符串

基本技术为想要的表结果创建新的frm文件，然后用它替换掉已经存在的那张表的frm文件：

     1、创建一张有相同结构的空表，进行所需的修改

     2、执行flush tables with read lock：关闭all正在使用的表且禁止任何表被打开

     3、交换frm文件

     4、执行unlock tables释放第2步的读锁

示例略 

4.5.2快速创建myISAM索引

1、为高效地载入数据到MyISAM表，常用技巧：先禁用索引、载入数据、重启索引：因为构建索引的工作延迟到数据载入后，此时可通过排序构建索引，快且使得索引树的碎片更少、更紧凑

但是对唯一索引无效（disable  keys），myisam会在内存中构造唯一索引且为载入的每一行检查唯一性，一旦索引大小超过有效内存、载入操作会越来越慢；

2、在现代版InnoDB中，有个类似技巧：先删除all非唯一索引，然后增加新的列，最后重建删除掉的索引（依赖于innodb快速在线索引创建功能）Percona server可自动完成这些操作；

3、像前alter table 的骇客方法来加速这个操作，但需多做些工作且承担风险，这对从备份中载入数据很有用，如already know all data is effective ，and no need to do the unique check

•     用需要的表结构创建一张表，不包括索引（如用load data file 且载入的表是空的，myisam可排序建索引）

• 载入数据到表中以构建MYD文件

• 按需要的结构创建另外一张空表，这次要包含索引，会创建.frm .MYI文件

• 获读锁并刷新表

• 重命名第二张表的frm文件 MYI，让mysql认为这是第一张表的文件

• 释放读锁

• 使用repair table来重建表的索引，该操作会通过排序来构建all索引、包括唯一索引 

4.6总结

良好的schema设计原则是普通使用的，但mysql有自己的实现细节要注意，概括来说：尽可能保持任何东西小而简单总是好的；mysql喜欢简单（好恰、我也是）

1. 最好避免使用bit

2. 使用小而简单的合适类型；

3. 尽量使用整型定义标识列

4. Évitez la conception excessive, telle que la conception de schémas qui conduirait à des requêtes extrêmement complexes ou à de nombreuses colonnes

5. Vous devez éviter d'utiliser des valeurs nulles ; autant que possible, sauf si vous disposez de données réelles. Il y a des besoins exacts dans le modèle

6. Essayez d'utiliser le même type pour stocker des valeurs similaires et liées, en particulier les colonnes utilisées dans les conditions d'association

7. Remarque Les chaînes de longueur variable, qui peuvent conduire à des allocations pessimistes de longueur maximale lors de l'utilisation de tables temporaires et du tri

8. Évitez d'utiliser des fonctionnalités obsolètes, comme spécifier la précision des nombres à virgule flottante, ou la précision des entiers Largeur d'affichage

9. Utilisez enum et définissez-le avec précaution, bien qu'ils soient très pratiques à utiliser, n'en abusez pas, parfois ils peuvent devenir des pièges

10. Le paradigme c'est bien Oui, mais la dénormalisation est parfois nécessaire ; le précalcul, la mise en cache ou la génération de tableaux récapitulatifs peuvent aussi être d'un grand bénéfice

11. alter table Dans la plupart des cas, la table sera verrouillée et la table entière sera reconstruite (Douloureux) Ce chapitre fournit quelques méthodes risquées, et la plupart des scénarios doivent utiliser d'autres méthodes plus conventionnelles

Articles connexes :

[Base de données MySQL 】Chapitre 3 Interprétation : Analyse des performances du serveur (Partie 1)

[Base de données MySQL] Chapitre 3 Interprétation : Performances du serveur Analyse (Partie 2)

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!