[MySQL-Datenbank] Interpretation von Kapitel 4: Schema- und Datentypoptimierung (Teil 2)

4.2 Fallstricke im MySQL-Schema-Design

Da der MySQL-Implementierungsmechanismus einige spezifische Fehler verursacht, sollten Sie sich Zeit nehmen, wie Sie diese vermeiden können:

1. Zu viele Spalten

Wenn die MySQL-Speicher-Engine-API funktioniert, muss sie die Daten über das Zeilenpuffer--Format auf der Serverebene und der Speicher-Engine-Ebene kopieren und dann Dekodierung des Pufferinhalts in Für jede Spalte ist der Vorgang der Konvertierung der codierten Spalten in Zeilendaten aus dem Zeilenpuffer kostspielig. Myisam-Zeilen mit fester Länge entsprechen jedoch genau der Serverzeilenstruktur und erfordern keine Konvertierung Die Zeilenstruktur der InnoDB-Zeilenstruktur mit variabler Länge ist immer erforderlich. Die Konvertierungskosten hängen von der Anzahl der Spalten ab.

2. Zu viele Assoziationen

Entity-Attribute-Value EAV: Schlechtes Designmuster, MySQL beschränkt jede Assoziationsoperation auf maximal 61 Tabellen, aber Die EAV-Datenbank erfordert viele Selbstkorrelationen. Wenn Sie möchten, dass die Abfrage schnell ausgeführt wird und eine gute Parallelität aufweist, ist es am besten, Korrelationen in 12 Tabellen für eine einzelne Abfrage durchzuführen > 3. Verhindern Sie eine übermäßige Verwendung von Aufzählungen. Achten Sie darauf, eine übermäßige Verwendung von Aufzählungen zu verhindern. Verwenden Sie Fremdschlüssel, um eine Verknüpfung mit Wörterbuchtabellen oder Nachschlagetabellen herzustellen, um bestimmte Werte zu finden Aufzählungsspalten Wenn Sie der Tabelle einen Wert hinzufügen, müssen Sie einen

alter table

-Vorgang ausführen; in der aktualisierten Version 5.1 ist dies der Fall nicht am Ende der Liste hinzugefügt werden

4. Null, die hier nicht erfunden ist wird empfohlen, durch 0, Sonderwert, leere Zeichenfolge, zu ersetzen nicht null sein; aber gehen Sie nicht ins Extreme, in manchen Szenarien ist es besser, null zu verwenden:

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

MySQL speichert Nullwerte im Index, Oracle wird es tun nicht

4.3 Normalform und Anti-Normalform

4.3.1 Vor- und Nachteile

1. Normalisierte Aktualisierungsvorgänge sind schneller2 Daten sind gut normalisiert, es gibt selten doppelte Daten und es werden nur weniger Daten geändert

3 Die normalisierte Tabelle ist kleiner, kann besser im Speicher abgelegt werden und führt Operationen schneller aus

4 ist wenig redundante Daten und ruft Listendaten ab. Es sind weniger eindeutige und gruppierte Anweisungen erforderlich.

Nachteile:

Erfordert eine Zuordnung, die kostspielig ist und den Index ungültig machen kann

4.3.2 Vor- und Nachteile des Anti-Paradigmas

Vermeiden Sie Assoziationen. Daten, die größer als der Speicher sind, können viel schneller sein als Assoziationen (Vermeidung zufälliger E/A). )4.4 Cache-Tabelle und Übersichtstabelle

Cache-Tabelle:

ist sehr effektiv für die Optimierung von Such- und Abrufabfrageanweisungen,

speichert diejenigen, die leicht von anderen abgerufen werden können. Die Tabelle, die Daten erhält (jede Erfassungsgeschwindigkeit ist relativ langsam)

Zusammenfassungstabelle: Die Tabelle, die die mithilfe der Group-by-Anweisung aggregierten Daten speichert

legt fest, ob die Daten bei der Verwendung in Echtzeit verwaltet oder regelmäßig neu erstellt werden sollen.

Regelmäßig neu erstellen

: Sparen Sie Ressourcen, haben Sie weniger Fragmentierung und sequentiell organisierte Indizes (effizient)

Stellen Sie während der Rekonstruktion sicher, dass die Daten während des Betriebs weiterhin verfügbar sind, indem Sie die „Schattentabelle“ „ verwenden, um eine Schattentabelle zu erreichen: eine Tabelle, die hinter der realen Tabelle erstellt wird. Nach Abschluss des Tabellenerstellungsvorgangs können Sie dies tun Wechseln Sie zwischen der

Schattentabelle und der Originaltabelle

< durch die atomare Umbenennungsoperation 🎜>

4.4.1 Materialisierte AnsichtVor -Berechnete und gespeicherte Tabelle auf der Festplatte, kann durch verschiedene Strategien aktualisiert und aktualisiert werden, MySQL unterstützt sie nicht nativ, kann mit dem Justin Swanhart-Tool implementiert werden. Flexviews:

Flexviews-Zusammensetzung:

Änderungsdatenerfassung, Lesen des Server-Binärprotokolls und Analysieren der relevanten Zeilen. Änderungen

Eine Reihe gespeicherter Prozeduren, die beim Erstellen helfen können und Ansichtsdefinitionen verwalten

• Einige der Änderungen, die auf materialisierte Ansichten in der Datenbank angewendet werden können. Tools

• Flexviews können

  inkrementell

  Berechnen Sie
den Inhalt einer materialisierten Ansicht neu, indem Sie Änderungen an der Quelltabelle extrahieren: Es ist nicht erforderlich, die Originaldaten abzufragen (effizient)

• 4.4.2 Zählertabelle

Zählertabelle: speichert die Anzahl der Freunde eines Benutzers, die Anzahl der Dateidownloads usw. im Cache. Es wird empfohlen, eine

unabhängige Tabelle zum Speichern von Zählern zu erstellen

. Vermeiden Sie Abfrage-Cache-Fehler. Transaktionen aktualisieren und hinzufügen kann nur seriell ausgeführt werden, der Zähler kann in mehreren Zeilen gespeichert werden und jedes Mal wird eine Zeile zur Aktualisierung ausgewählt, aggregierte Abfrage. Vielleicht ist es ein bisschen dumm. Derselbe Zähler speichert mehrere Punkte, einer davon wird jedes Mal aktualisiert und die Endsumme wird berechnet. Es scheint nicht ganz einfach zu sein, ihn noch ein paar Mal zu lesen. 4.5 Beschleunigen Sie den Vorgang zum Ändern der Tabelle

Die meisten Änderungen an der Tabellenstruktur von MySQL sind: Erstellen einer leeren Tabelle mit neuen Ergebnissen, Suchen aller Daten aus der alten Tabelle und Einfügen in diese die neue Tabelle und das Löschen der alten Tabelle

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. Vermeiden Sie übermäßiges Design, z. B. Schemadesign, das zu extrem komplexen Abfragen oder vielen Spalten führt.

5. Sie sollten die Verwendung von Nullwerten vermeiden so viel wie möglich, es sei denn, Sie haben echte Daten. Es gibt genaue Anforderungen im Modell

6. Versuchen Sie, denselben Typ zu verwenden, um ähnliche und verwandte Werte zu speichern, insbesondere die in den Assoziationsbedingungen verwendeten Spalten

7. Hinweis Zeichenfolgen mit variabler Länge, die bei Verwendung temporärer Tabellen und Sortierungen zu pessimistischen Zuweisungen maximaler Länge führen können

8. Vermeiden Sie die Verwendung veralteter Funktionen, z B. die Angabe der Genauigkeit von Gleitkommazahlen oder der Genauigkeit von Ganzzahlen. Anzeigebreite

9. Verwenden Sie Enum und Set sorgfältig. Obwohl sie sehr praktisch zu verwenden sind, sollten Sie sie manchmal nicht missbrauchen kann zu Fallen werden

10. Paradigma ist gut Ja, aber manchmal ist eine Denormalisierung erforderlich; auch Vorberechnung, Zwischenspeicherung oder Generierung von Übersichtstabellen können von großem Nutzen sein

11. Tabelle ändern In den meisten Fällen wird die Tabelle gesperrt und die gesamte Tabelle neu erstellt (Schmerzhaft) Dieses Kapitel stellt einige riskante Methoden vor, und in den meisten Szenarien müssen andere, konventionellere Methoden verwendet werden

Verwandte Artikel:

[MySQL-Datenbank] Kapitel 3 Interpretation: Server-Leistungsanalyse (Teil 1)

[MySQL-Datenbank] Kapitel 3 Interpretation: Server-Leistungsanalyse (Teil 2)

Das obige ist der detaillierte Inhalt von[MySQL-Datenbank] Interpretation von Kapitel 4: Schema- und Datentypoptimierung (Teil 2). Für weitere Informationen folgen Sie bitte anderen verwandten Artikeln auf der PHP chinesischen Website!