[MySQL データベース] 第 4 章の解釈: スキーマとデータ型の最適化 (パート 2)

4.2 MySQL スキーマ設計の罠

mysql の実装メカニズムによっていくつかの特定のエラーが発生するため、それらを回避する方法については、時間をかけて説明します:

1. 列が多すぎます

MySQL ストレージ エンジン API は次のようにする必要があります。作業時に使用されるサーバー層とストレージ エンジン層は、行バッファー形式でデータをコピーし、サーバー層でバッファーの内容を各列にデコードし、エンコードされた列を行バッファーから行データに変換します。高価な、myisam 固定長行 これはサーバーの行構造と完全に一致しており、変換する必要はありません。ただし、InnoDB の可変長行構造は常に変換する必要があり、変換コストは列の数によって異なります。

2. 関連付けが多すぎます

エンティティ属性値 EAV: 設計パターンが不十分で、mysql では各関連付け操作が最大 61 テーブルに制限されていますが、EAV データベースには多くの自己相関が必要です。 、クエリを迅速に実行し、優れた同時実行性を実現したい場合は、

12 個のテーブルに単一のクエリを関連付けることが最適です

3. 列挙型の過剰使用を防止する

外部キーを使用するように注意してください。 MySQL では、

列挙列

テーブルに値を追加する必要がある場合、MySQL5.0 より前のテーブルの変更

を実行する必要があります。操作、5.1 更新されたバージョンでは、リストの最後に値を追加する場合もテーブル 4 を変更する必要があります。これはここでは作成されていません 代わりに 0、特別な値、または空の文字列を使用して格納することをお勧めします。 null 値は使用しないようにしてください。ただし、極端なことはしないでください。シナリオによっては、null を使用した方がよい場合もあります。

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

MySQL はインデックスに null 値を格納しますが、Oracle は 1 を格納しません。正規化された更新操作が高速化されます

2. データが適切に正規化されている場合、重複するデータが少なく、変更する必要があるデータが少なくなります

3. 正規化されたテーブルは小さくなり、より適切に処理できるようになります

4. 冗長なデータがほとんどなく、リストデータを取得するときに必要な個別ステートメントとグループ化ステートメントが少なくなります

欠点:

コストがかかり、インデックスが無効になる可能性がある関連付けが必要です

4.3.2 利点アンチノーマルの欠点

アソシエーションを回避し、メモリより大きなデータはアソシエーションよりもはるかに高速になる可能性があります (ランダム I/O を回避) 4.4 キャッシュ テーブルとサマリー テーブル

キャッシュ テーブル :

最適化に非常に効果的検索および取得クエリ ステートメント、 他のテーブルから簡単にデータを取得できるストレージ テーブル (それぞれの取得に時間がかかります)

集計テーブル:

group by ステートメントを使用して集計を保存 データ テーブル

は、データを実際に保持するかどうかを決定します使用する場合は、時間を節約するか、定期的に再構築します。 定期的に再構築します : リソースを節約し、断片化を減らし、インデックスを順番に編成します (効率的)

再構築するときは、

「シャドウ テーブル」

を通じて、操作中にデータがまだ利用可能であることを確認してください。実現するには、シャドウ テーブル: 実テーブルの背後に作成されたテーブル。テーブル作成操作の完了後、アトミックな名前変更操作を通じてシャドウ テーブルと元のテーブルを切り替えることができます

4.4.1 マテリアライズド ビュー

ディスク上に事前に計算されて保存されたテーブル

。これは、さまざまな戦略を通じてリフレッシュおよび更新できます。MySQL はネイティブでサポートしていませんが、Justin Swanhart ツールの flexviews:flexviews 構成:

Change を使用して実装できます。データを取得し、サーバーのバイナリ ログを読み取り、関連する行の変更を解析します

ビューの定義の作成と管理に役立つ一連のストアド プロシージャ

マテリアライズド ビューに変更を適用できるいくつかのツールデータベース内

フレックスビューは、ソーステーブルへの変更を抽出することで、マテリアライズドビューの内容を
• 増分的に

  再

  再計算
することができます: 元のデータをクエリする必要はありません(効率的)

• 4.4.2 カウンタテーブル

Counterテーブル: ユーザーの友達の数、ファイルのダウンロード時間などをキャッシュするため、クエリ キャッシュの失敗を避けるために、
• カウンターを保存する独立したテーブルを作成することをお勧めします。
  トランザクションの追加は連続的にのみ実行できます。同時実行性が高い場合は、カウンタを複数の行に保存して、毎回更新する行をランダムに選択し、結果をカウントしたい場合は集計クエリを使用します (これを 2 ～ 3 回読みましたが、これは愚かなことかもしれません。 、同じカウンターが複数のポイントを保存し、その中から 1 つを選択して毎回更新し、最後に合計すると、そうではないようです。理解しやすいので、何回か読んでください)

4.5 は、テーブルの変更操作を高速化します

mysql のテーブル構造に対するほとんどの変更は、新しい結果を含む空のテーブルを作成し、古いテーブルからすべてのデータを検索して新しいテーブルに挿入することです。 古いテーブルを削除します。

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. 非常に複雑なクエリや多数の列を引き起こすスキーマ設計などの過剰な設計は避けてください。

5. 実際に必要な場合を除き、可能な限り null 値の使用を避ける必要があります。データ モデル

6. 同じ型を使用するようにします 類似した関連する値、特に関連付け条件で使用される列を格納します

7. 一時テーブルを操作する場合、悲観的な最大長のメモリ割り当てにつながる可能性がある可変長文字列に注意してくださいおよびソート

8. 浮動小数点数の精度や整数の表示幅の指定など、放棄された機能の使用を避けてください

9. enum を使用し、慎重に設定してください。これらは非常に便利ですが、乱用しないでください。 、時々それらはトラップになる可能性があります

10. パラダイムは良いですが、逆正規化が必要な場合もあります; 事前計算、キャッシュ、または要約テーブルの生成も大きな利点になります

11. テーブルを変更するほとんどの場合、テーブルはロックされ、テーブル全体が再構築されます (痛みを伴います) この章ではいくつかの危険な方法を説明しますが、ほとんどのシナリオでは他の従来の方法を使用する必要があります

関連記事:

[MySQL Database] 第 3 章の解釈: サーバー パフォーマンス分析 (パート 1)

[MySQL データベース] 第 3 章の解釈: サーバーのパフォーマンス分析 (パート 2)

以上が[MySQL データベース] 第 4 章の解釈: スキーマとデータ型の最適化 (パート 2)の詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。