MySQL管理と最適化(15):データベース・オブジェクトの最適化

データベース・オブジェクトの最適化
テーブルのデータ型の最適化:

PROCEDURE_を通過可能ANALYZE()現在のアプリケーションのテーブルを分析し、構文:

SELECT * FROM tb_name PROCEDURE ANALYSE()

例:

--   
mysql> CREATE TABLE DUCK_CUST(
    -> cust_num MEDIUMINT AUTO_INCREMENT,
    -> cust_title TINYINT,
    -> cust_last CHAR(20) NOT NULL,
    -> cust_first CHAR(15) NOT NULL,
    -> cust_suffix ENUM('Jr.', 'II', 'III', 'IV', 'V', 'M.D.', 'PhD'),
    -> cust_add1 CHAR(30) NOT NULL,
    -> cust_add2 CHAR(10),
    -> cust_city CHAR(18) NOT NULL,
    -> cust_state CHAR(2) NOT NULL,
    -> cust_zip1 CHAR(5) NOT NULL,
    -> cust_zip2 CHAR(4),
    -> cust_duckname CHAR(25) NOT NULL,
    -> cust_duckday DATE,
    -> PRIMARY KEY (cust_num)
)ENGINE=MyISAM;

#    
...

#     
mysql> SELECT * FROM DUCK_CUST PROCEDURE ANALYSE()\G
*************************** 1. row ***************************
             Field_name: test.DUCK_CUST.cust_num
              Min_value: 1
              Max_value: 1
             Min_length: 1
             Max_length: 1
       Empties_or_zeros: 0
                  Nulls: 0
Avg_value_or_avg_length: 1.0000
                    Std: 0.0000
      Optimal_fieldtype: ENUM('1') NOT NULL
*************************** 2. row ***************************
             Field_name: test.DUCK_CUST.cust_title
              Min_value: 1
              Max_value: 1
             Min_length: 1
             Max_length: 1
...

    cust_num  Max_length, Min_length, Avg_value_or_avg_length,     1，     MEDIUMINT(2)

分割によるテーブルへのアクセス効率の向上:

ここでの分割はデータベーステーブルの分割を指し、MyISAMテーブルには主に2つの分割方法がある:

      1. 1つ目の方法は、プライマリ・キーとカラムを1つのテーブルに配置し、プライマリ・キーと他のカラムを別のテーブルに配置することです.これにより、常用列と非常用列を分けてI/O回数をある程度減らすことができますが、冗長列を管理する必要があり、すべてのデータを照会するにはJOIN操作が必要です.
      2. 2つ目の方法は、1つのカラムまたは複数のカラムの値に基づいて、2つの独立したテーブルにデータ行を配置する水平分割です.表は大きく、分割するとクエリー時に読むデータとインデックスのページ数を減らすことができます.表のデータにはもともと独立性があります.例えば、よく使われるデータもあれば、あまり使わないデータもあります.複数のメディアにデータを格納する必要があります.
逆正規化:

大学の先生はすべて3つの様式にこだわるのが好きで、道理は悪くなくて、データ量が大きくて、検索の頻繁なインターネットの応用の中で置いて、完全に通じなくて、私达はいつでも逆規範化する必要があります.

でよく用いられる逆規範化技術は、

である.
       1. ≪冗長列の追加｜Add Redundancy Columns｜oem_src≫:複数のテーブルに同じ列があることを示し、接続クエリーを回避します.
       2. ≪派生列の追加|Add派生列|emdw≫:追加された列が他の表のデータから生成され、他の表のデータが計算されて生成され、接続クエリーとセット関数の使用が減少します.
       3. ≪表の再グループ化|Regroup Table|emdw≫:多くのユーザーが2つの表のデータをクエリーする必要がある場合、接続クエリーを減らすために表に結合します.
       4. 分割表:常用列と非常用列などを分けて保存し、クエリーのI/Oを減らす.

逆規範も一定の問題をもたらします.例えば、データの完全性など、各テーブルの各種冗長フィールドの同期を保証する必要があります.個人的には、冗長ではない列や頻繁に変動しない列を提案します.他の方法でデータの完全性を保証することができます.

       1. 定期バッチ更新.
       2. 論理インプリメンテーションで同期を実現します.
       3. トリガが同期して実行されます.
中間テーブルを使用して統計クエリーの速度を上げるには、次の手順に従います.

データ量の大きいテーブルに対して、中間テーブルを使用してクエリーを行い、クエリーのパフォーマンスを向上させることができます.

例:

#   
mysql> CREATE TABLE session(
    -> cust_id VARCHAR(10),
    -> cust_amount DECIMAL(16,2),
    -> cust_date DATE,
    -> cust_ip VARCHAR(20));
Query OK, 0 rows affected (0.36 sec)

#        
...

#             
mysql> SELECT sum(cust_amount) FROM session WHERE cust_id='123';
+------------------+
| sum(cust_amount) |
+------------------+
|      25585254.40 |
+------------------+
1 row in set (5.81 sec)

#            
mysql> CREATE TABLE tmp_session(
    -> cust_id VARCHAR(10),
    -> cust_amount DECIMAL(16, 2),
    -> cust_date DATE,
    -> cust_ip VARCHAR(20));
Query OK, 0 rows affected (0.72 sec)

#         
mysql> INSERT INTO tmp_session SELECT * FROM session WHERE cust_id='123';
Query OK, 2097152 rows affected (21.19 sec)
Records: 2097152  Duplicates: 0  Warnings: 0

mysql> SELECT sum(cust_amount) FROM tmp_session;
+------------------+
| sum(cust_amount) |
+------------------+
|      25585254.40 |
+------------------+
1 row in set (1.76 sec)

中間テーブルは統計クエリーでよく使用されます.主な利点は

です.
      1. 中間テーブルは、ソーステーブルの一部のデータをコピーし、ソーステーブルから分離します.中間テーブルで統計クエリーを行うと、オンラインアプリケーションに悪影響を及ぼすことはありません.
      2. 中間テーブルには、インデックスを柔軟に追加したり、一時的に使用する新しいフィールドを追加したりすることで、統計クエリーの効率化と統計クエリーの補助的な役割を果たすことができます.
指摘を惜しまない.