MySQLでのインデックス組織構造の作成と最適化の考え方
7904 ワード
【ガイド】実際の生産環境におけるデータアクセス要件により、このストレージ構造をどのように設計するか、ストレージのデータをどのように操作するか、および操作のコストまたはコストをより低くし、システムのオーバーヘッドを最小限に抑えるかを分析します.同時に、データストレージのテーブル上のインデックスがどのように構成されているかをより多くの初心者に理解させ、参照テンプレートの価値の役割を果たしたいと考えています.
テストケース記述テストケースB 2 C領域は、ユーザが物品を選択して生成した製品受注情報テーブルを格納するためのものであるが、テストケースに使用するための他のフィールドを削除し、そのテーブルのデータ項目も特に記述せず、フィールドの意味は表を参照する.
ここで、主キー情報:PRIMARY KEY(order_id,`goods_id`)、なぜ主キーインデックスフィールドの順序は:order_id,`goods_id`、`goods_ではなくid`, order_idは?理由は簡単だgoodsid受注情報テーブルでの繰返し率はorder_よりも大きいid高、すなわちorder_idのフィルタリング率が高く、スキャンインデックスのレコード数を減らすことで、より効率的になります.また、以下に示すSQLも、一部のSQL文のWHERE文にorder_のみが表示されていることを示しています.idフィールドをより強固にするには、フィールド:order_idは連合プライマリキーインデックスのヘッダとして、`goods_id`は、連合プライマリ・キー・インデックスの末尾です.
データストレージテーブル設計のまとめ:データを格納するためのテーブル構造を設計し、まずどのデータ項目、すなわち行内でよく言われるデータストリーム、および格納されたデータ型、値域範囲および長さ、データ整合性などの各データ項目の属性を知って、データ項目の属性定義を決定します.記憶されたデータ項目情報の決定後、少なくとも以下の3段階の分析を行う:lまず、どのデータ項目または組合せが記録の一意性フラグとして使用できるかを決定する.l次に、データ記録にどのような操作があるか、各操作の頻度がどうなのか、ウェブサイトなどのタイプのアプリケーションに対して、フロント操作とバックグラウンド操作、すなわち外部ユーザーの操作、それとも内部ユーザーの操作を区別する必要がある.l最後に、データ記録操作の条件部分であるデータ項目について、そのデータ項目のフィルタ率を分析する.すなわち、データ項目の異なる値が総データ記録数に占める割合に関心を持ち、割合が1に近づくほどフィルタ率が良くなり、各分布率に値する.以上のように、データ修正操作の優先度を読み取り専用操作よりも高くすると、要件を満たし、パフォーマンスの良いインデックス組織構造を作成できます.データのアクセス設計は、リレーショナル・データベースの基礎知識とリレーショナル・データ理論のモデルという非常に重要な知識に関連しています.パターンの知識点については、特に説明すると、BCNFパターンを学ぶまで、1 NF、2 NF、3 NFとBCNFの違いは、それぞれ回避する問題、存在する欠陥ははっきりしていることをお勧めしますが、実際の作業環境では、アクセス設計がパターンに頼りたくないので、仏語で正確に表現します:空は色で、色は空です.
テストデータを生成するためのストアド・プロシージャ・コードインデックスを作成するには、テーブルに格納されている実際のデータが欠かせません.そのため、ストアド・プロシージャが実際の本番環境でのデータをシミュレートする可能性があります.また、このストアド・プロシージャを使用するのに便利です.自分のテスト環境では、検証を実感し、ストアド・プロシージャ・コードを格納します.
ビジネスロジック記述l非登録ユーザー、またはウェブサイトの登録ユーザーがログインしない場合、オプションで物品を購入することができ、注文番号に対応するユーザーUIDを生成することはシステムのデフォルトである.l注文とユーザーUIDの関連、説明などの情報は、他の表において、注文番号のパターンによって関連付けられている.lユーザーの注文情報は、未払いの前に変更できますが、支払い後は変更できません.lすでに支払った注文情報は、自動的に物流部門に送信され、後続工程の操作を行う.処理が完了すると、注文書に含まれる物品の格納位置情報が更新されます.l定期的に一部のデータをデータ倉庫分析システムに読み出し、統計分析に用いる.l個人注文照会、フロントバックグラウンドにあります.lショッピング記録照会表示;
操作データを使用する必要があるSQL文(1)をビジネスルールに従って記述する.EXPLAIN SELECT * FROM goods_order WHERE `order_id`=40918986; (2). SELECT * FROM goods_order WHERE `order_id` IN (40918986,40717328,30923040…) ORDER BY gmt_modify DESC; (3). UPDATE goods_order SET gmt_modify=NOW(),…. WHERE `order_id`=40717328 AND goods_id=4248; (4). SELECT COUNT(*) FROM goods_order WHERE depot_id=0 ORDER BY gmt_modify DESC LIMIT 0,50; (5). SELECT * FROM goods_order WHERE depot_id=6 AND packet_id=0 ORDER BY gmt_modify DESC LIMIT 0,50; (6). SELECT COUNT(*) FROM goods_order WHERE goods_id=4248 AND order_status=0 AND order_type=1 (7). SELECT * FROM goods_order WHERE goods_id=4248 AND order_status=0 AND order_type=1 ORDER BY gmt_modify DESC LIMIT 0,50; (8). SELECT * FROM goods_order WHERE gmt_modify>=’ 2011-04-06’; 8本のSQL文はその実行をトリガするユーザーによって分類される:lフロントユーザーがトリガする操作をクリックして実行するSQL文は:(1)、(2)、(3);lバックグラウンド内部のユーザーがトリガした操作をクリックして実行するSQL文は、(1)、(2)、(3)、(4)、(5)、(6)、(7);lバックグラウンドシステムは自動的に定期的に実行する:(4)、(5)、(6)、(7)、勤務時間の正常な状況は15分おきに実行し、支払い済みで貨物を準備していない注文、入金があって出荷されていない注文などがあるかどうかを検査する.l統計分析システムが定期的にデータをエクスポートして実行するSQL文は以下の通りである:(8)、頻度は24時間ごとである;さらに上記のSQLを分析すると、2種類に分けられ、1種類は読解操作のSQL(備考:SELECT操作)であり、もう1種類は修正操作(備考:UPDATE、DELETE操作)であり、それぞれ以下の通りである:SELECTのWHERE句、GROUP BY子、ORDER BY句、HAVING句に出現するフィールド:(1).order_id (2). order_id+gmt_modify (3). depot_id+gmt_modify (4). depot_id+packet_id+gmt_modify (5). goods_id+order_status+order_type (6). goods_id+order_status+order_type+gmt_modify (7). gmt_modify修正操作のWHERE句に出現する条件フィールド:(8).order_id+ goods_id
プライマリ・キー・インデックス:PRIMARY KEY(order_id,`goods_id`)は既に存在し、また、このテーブルのデータの操作はSELECTとINSERTを主とし、UPDATEのSQL量は次に、上記のSQL文に基づいて、作成するインデックス:ALTER TABLE goods_を初歩的に決定することができます.order ADD INDEX idx_goodsID_orderType_orderStatus_gmtmodify(goods_id,order_type,order_status,gmt_modify), ADD INDEX idx_depotID_packetID_gmtmodify(depot_id,packet_id,gmt_modify);
まとめ:プライマリ・キー・インデックスを結合する順序がorder_である理由も分析されています.id,`goods_id`は、プライマリ・キーとしてのコンビネーション・インデックスのフィールド・プロパティの他の特性を補足します.フィールド値が書き込まれた後も変化せず、フィールド値の長さが短く、数値タイプが望ましいです.番号SQL:(8)については、毎日更新日ごとにデータを読み出す操作は、全表スキャンを採用することで実現され、そのデータ読み出しの性能を犠牲にして、更新フィールドが日付の値を修正することによるインデックスメンテナンスのオーバーヘッドを減らす.番号SQL:(4)、(5)については、毎回最新の50件のレコードを読み取ることと、読み取ったデータが基本的にホットデータであることを考慮して、そのためにSQLの1件のデータの読み取り性能を犠牲にしなければならず、連合インデックスを作成することを少なくし、インデックスフィールドのIO量を維持しなければならない.SQL:(6)、(7)の番号で作成された連合インデックスについては、連合インデックス:idx_に特に注意する必要があります.goodsID_orderType_orderStatus_gmtmodify(goods_id,order_type,order_status,gmt_modify)のフィールド順:l goods_idフィールドのフィルタリング率はorder_より高いtype,order_status、またgmt_modifyフィールドはORDER BY句にのみ表示されるためgoods_idフィールドは、インデックスのフィルタリング率を向上させるために、インデックスを結合するヘッダとして機能し、インデックスの効率を向上させ、論理的または物理的な読み取りを減少させる.l order_statusフィールドには0または1の2つの値しかありませんが、order_typeには複数の種類があり、SQL文に基づいてorder_が必要です.typeが連合に現れる位置はorder_よりもstatusは頭に近い.l gmt_modifyフィールドはORDER BY句に表示されます.そのため、連合インデックスフィールドの最後に配置する必要があります.
最後に、需要からストレージ構造の設計、SQLの作成とインデックス構造の作成まで、私たちがすべきステップを整理します:l業務で発生したデータストリームを整理し、データを読み取る方法;lデータストリーム中の各データ項目の属性情報を整理する.l業務指標を分析し、データを保存する必要がある規模を推測する(備考:必ず何GBを容量単位とする);lビジネスをサポートするために使用可能なハードウェアデバイスとデータベースアーキテクチャを選択する.lすべての操作可能なデータの条件と操作タイプを整理する.l操作データ条件フィールドのそれぞれのデータフィルタ率を分析する.l各SQLの性能とIO量を比較する.つまり、どの操作の重みが高いか、それらの操作の重みが適切に低いかに似ている.lインデックス組織構造の作成;lテストと生産環境のフィードバック情報を収集し、インデックス組織構造を最適化する.
備考:更にテスト環境で業務の方式を結合すると思って、1セットの模擬テストスクリプトプログラムを走って、みんなにもっと直観的に異なっているインデックスの組織の情況を見させて、同じSQL操作と周波数、データベースサーバーの処理能力と負荷の変化と比較情報、残念ながら唯一のサーバーは使うことができなくて、放棄するしかありません.同じSQLを分析するには、インデックスが通じません.論理IOと物理IOの量も必要です.今回は分析しません.必要な友达が游びに行くことができます.また、初心者はmysqlマニュアルの関連章の内容をよく読むことをお勧めします.7.2.6.Index Merge Optimization.
テストケース記述テストケースB 2 C領域は、ユーザが物品を選択して生成した製品受注情報テーブルを格納するためのものであるが、テストケースに使用するための他のフィールドを削除し、そのテーブルのデータ項目も特に記述せず、フィールドの意味は表を参照する.
USE `test`;
DROP TABLE IF EXISTS `test`.`goods_order`;
CREATE TABLE `goods_order`(
`order_id` INT UNSIGNED NOT NULL COMMENT ‘ ’,
`goods_id` INT UNSIGNED NOT NULL DEFAULT ’0′ COMMENT ‘ ’,
`order_type` TINYINT UNSIGNED NOT NULL DEFAULT ’0′ COMMENT ‘ ’,
`order_status` TINYINT UNSIGNED NOT NULL DEFAULT ’0′ COMMENT ‘ ’,
`color_id` SMALLINT UNSIGNED NOT NULL DEFAULT ’0′ COMMENT ‘ id’,
`size_id` SMALLINT UNSIGNED NOT NULL DEFAULT ’0′ COMMENT ‘ id’,
`goods_number` MEDIUMINT UNSIGNED NOT NULL DEFAULT ’0′ COMMENT ‘ ’,
`depot_id` INT UNSIGNED NOT NULL DEFAULT ’0′ COMMENT ‘ id’,
`packet_id` INT UNSIGNED NOT NULL DEFAULT ’0′ COMMENT ‘ code’,
`gmt_create` TIMESTAMP NOT NULL DEFAULT ’0000-00-00 00:00:00′ COMMENT ‘ ’,
`gmt_modify` TIMESTAMP NOT NULL DEFAULT ’0000-00-00 00:00:00′ COMMENT ‘ ’,
PRIMARY KEY(order_id,`goods_id`)
)ENGINE=InnoDB AUTO_INCREMENT=1 CHARACTER SET ‘utf8′ COLLATE ‘utf8_general_ci’;ここで、主キー情報:PRIMARY KEY(order_id,`goods_id`)、なぜ主キーインデックスフィールドの順序は:order_id,`goods_id`、`goods_ではなくid`, order_idは?理由は簡単だgoodsid受注情報テーブルでの繰返し率はorder_よりも大きいid高、すなわちorder_idのフィルタリング率が高く、スキャンインデックスのレコード数を減らすことで、より効率的になります.また、以下に示すSQLも、一部のSQL文のWHERE文にorder_のみが表示されていることを示しています.idフィールドをより強固にするには、フィールド:order_idは連合プライマリキーインデックスのヘッダとして、`goods_id`は、連合プライマリ・キー・インデックスの末尾です.
データストレージテーブル設計のまとめ:データを格納するためのテーブル構造を設計し、まずどのデータ項目、すなわち行内でよく言われるデータストリーム、および格納されたデータ型、値域範囲および長さ、データ整合性などの各データ項目の属性を知って、データ項目の属性定義を決定します.記憶されたデータ項目情報の決定後、少なくとも以下の3段階の分析を行う:lまず、どのデータ項目または組合せが記録の一意性フラグとして使用できるかを決定する.l次に、データ記録にどのような操作があるか、各操作の頻度がどうなのか、ウェブサイトなどのタイプのアプリケーションに対して、フロント操作とバックグラウンド操作、すなわち外部ユーザーの操作、それとも内部ユーザーの操作を区別する必要がある.l最後に、データ記録操作の条件部分であるデータ項目について、そのデータ項目のフィルタ率を分析する.すなわち、データ項目の異なる値が総データ記録数に占める割合に関心を持ち、割合が1に近づくほどフィルタ率が良くなり、各分布率に値する.以上のように、データ修正操作の優先度を読み取り専用操作よりも高くすると、要件を満たし、パフォーマンスの良いインデックス組織構造を作成できます.データのアクセス設計は、リレーショナル・データベースの基礎知識とリレーショナル・データ理論のモデルという非常に重要な知識に関連しています.パターンの知識点については、特に説明すると、BCNFパターンを学ぶまで、1 NF、2 NF、3 NFとBCNFの違いは、それぞれ回避する問題、存在する欠陥ははっきりしていることをお勧めしますが、実際の作業環境では、アクセス設計がパターンに頼りたくないので、仏語で正確に表現します:空は色で、色は空です.
テストデータを生成するためのストアド・プロシージャ・コードインデックスを作成するには、テーブルに格納されている実際のデータが欠かせません.そのため、ストアド・プロシージャが実際の本番環境でのデータをシミュレートする可能性があります.また、このストアド・プロシージャを使用するのに便利です.自分のテスト環境では、検証を実感し、ストアド・プロシージャ・コードを格納します.
DELIMITER $$
DROP PROCEDURE IF EXISTS `usp_make_data` $$
CREATE PROCEDURE `usp_make_data`()
BEGIN
DECLARE iv_goods_id INT UNSIGNED DEFAULT 0;
DECLARE iv_depot_id INT UNSIGNED DEFAULT 0;
DECLARE iv_packet_id INT UNSIGNED DEFAULT 0;
SET iv_goods_id=5000;
SET iv_depot_id=10;
SET iv_packet_id=20;
WHILE iv_goods_id>0
DO
START TRANSACTION;
WHILE iv_depot_id>0
DO
WHILE iv_packet_id>0
DO
INSERT INTO goods_order(order_id,goods_id,order_type,order_status,color_id,size_id,goods_number,depot_id,packet_id,gmt_create,gmt_modify)
VALUES(SUBSTRING(RAND(),3,8),iv_goods_id,SUBSTRING(RAND(),3,1),SUBSTRING(RAND(),5,1)%2,SUBSTRING(RAND(),3,3),SUBSTRING(RAND(),4,3),SUBSTRING(RAND(),5,2),
iv_depot_id,SUBSTRING(RAND(),4,2)*iv_packet_id,DATE_ADD(NOW(),INTERVAL -SUBSTRING(RAND(),2,3) DAY),DATE_ADD(NOW(),INTERVAL -SUBSTRING(RAND(),3,2) DAY)
);
SET iv_packet_id=iv_packet_id-1;
END WHILE;
SET iv_packet_id=20;
SET iv_depot_id=iv_depot_id-1;
END WHILE ;
COMMIT;
SET iv_depot_id=10;
SET iv_goods_id=iv_goods_id-1;
END WHILE ;
END $$
DELIMITER ;ビジネスロジック記述l非登録ユーザー、またはウェブサイトの登録ユーザーがログインしない場合、オプションで物品を購入することができ、注文番号に対応するユーザーUIDを生成することはシステムのデフォルトである.l注文とユーザーUIDの関連、説明などの情報は、他の表において、注文番号のパターンによって関連付けられている.lユーザーの注文情報は、未払いの前に変更できますが、支払い後は変更できません.lすでに支払った注文情報は、自動的に物流部門に送信され、後続工程の操作を行う.処理が完了すると、注文書に含まれる物品の格納位置情報が更新されます.l定期的に一部のデータをデータ倉庫分析システムに読み出し、統計分析に用いる.l個人注文照会、フロントバックグラウンドにあります.lショッピング記録照会表示;
操作データを使用する必要があるSQL文(1)をビジネスルールに従って記述する.EXPLAIN SELECT * FROM goods_order WHERE `order_id`=40918986; (2). SELECT * FROM goods_order WHERE `order_id` IN (40918986,40717328,30923040…) ORDER BY gmt_modify DESC; (3). UPDATE goods_order SET gmt_modify=NOW(),…. WHERE `order_id`=40717328 AND goods_id=4248; (4). SELECT COUNT(*) FROM goods_order WHERE depot_id=0 ORDER BY gmt_modify DESC LIMIT 0,50; (5). SELECT * FROM goods_order WHERE depot_id=6 AND packet_id=0 ORDER BY gmt_modify DESC LIMIT 0,50; (6). SELECT COUNT(*) FROM goods_order WHERE goods_id=4248 AND order_status=0 AND order_type=1 (7). SELECT * FROM goods_order WHERE goods_id=4248 AND order_status=0 AND order_type=1 ORDER BY gmt_modify DESC LIMIT 0,50; (8). SELECT * FROM goods_order WHERE gmt_modify>=’ 2011-04-06’; 8本のSQL文はその実行をトリガするユーザーによって分類される:lフロントユーザーがトリガする操作をクリックして実行するSQL文は:(1)、(2)、(3);lバックグラウンド内部のユーザーがトリガした操作をクリックして実行するSQL文は、(1)、(2)、(3)、(4)、(5)、(6)、(7);lバックグラウンドシステムは自動的に定期的に実行する:(4)、(5)、(6)、(7)、勤務時間の正常な状況は15分おきに実行し、支払い済みで貨物を準備していない注文、入金があって出荷されていない注文などがあるかどうかを検査する.l統計分析システムが定期的にデータをエクスポートして実行するSQL文は以下の通りである:(8)、頻度は24時間ごとである;さらに上記のSQLを分析すると、2種類に分けられ、1種類は読解操作のSQL(備考:SELECT操作)であり、もう1種類は修正操作(備考:UPDATE、DELETE操作)であり、それぞれ以下の通りである:SELECTのWHERE句、GROUP BY子、ORDER BY句、HAVING句に出現するフィールド:(1).order_id (2). order_id+gmt_modify (3). depot_id+gmt_modify (4). depot_id+packet_id+gmt_modify (5). goods_id+order_status+order_type (6). goods_id+order_status+order_type+gmt_modify (7). gmt_modify修正操作のWHERE句に出現する条件フィールド:(8).order_id+ goods_id
プライマリ・キー・インデックス:PRIMARY KEY(order_id,`goods_id`)は既に存在し、また、このテーブルのデータの操作はSELECTとINSERTを主とし、UPDATEのSQL量は次に、上記のSQL文に基づいて、作成するインデックス:ALTER TABLE goods_を初歩的に決定することができます.order ADD INDEX idx_goodsID_orderType_orderStatus_gmtmodify(goods_id,order_type,order_status,gmt_modify), ADD INDEX idx_depotID_packetID_gmtmodify(depot_id,packet_id,gmt_modify);
まとめ:プライマリ・キー・インデックスを結合する順序がorder_である理由も分析されています.id,`goods_id`は、プライマリ・キーとしてのコンビネーション・インデックスのフィールド・プロパティの他の特性を補足します.フィールド値が書き込まれた後も変化せず、フィールド値の長さが短く、数値タイプが望ましいです.番号SQL:(8)については、毎日更新日ごとにデータを読み出す操作は、全表スキャンを採用することで実現され、そのデータ読み出しの性能を犠牲にして、更新フィールドが日付の値を修正することによるインデックスメンテナンスのオーバーヘッドを減らす.番号SQL:(4)、(5)については、毎回最新の50件のレコードを読み取ることと、読み取ったデータが基本的にホットデータであることを考慮して、そのためにSQLの1件のデータの読み取り性能を犠牲にしなければならず、連合インデックスを作成することを少なくし、インデックスフィールドのIO量を維持しなければならない.SQL:(6)、(7)の番号で作成された連合インデックスについては、連合インデックス:idx_に特に注意する必要があります.goodsID_orderType_orderStatus_gmtmodify(goods_id,order_type,order_status,gmt_modify)のフィールド順:l goods_idフィールドのフィルタリング率はorder_より高いtype,order_status、またgmt_modifyフィールドはORDER BY句にのみ表示されるためgoods_idフィールドは、インデックスのフィルタリング率を向上させるために、インデックスを結合するヘッダとして機能し、インデックスの効率を向上させ、論理的または物理的な読み取りを減少させる.l order_statusフィールドには0または1の2つの値しかありませんが、order_typeには複数の種類があり、SQL文に基づいてorder_が必要です.typeが連合に現れる位置はorder_よりもstatusは頭に近い.l gmt_modifyフィールドはORDER BY句に表示されます.そのため、連合インデックスフィールドの最後に配置する必要があります.
最後に、需要からストレージ構造の設計、SQLの作成とインデックス構造の作成まで、私たちがすべきステップを整理します:l業務で発生したデータストリームを整理し、データを読み取る方法;lデータストリーム中の各データ項目の属性情報を整理する.l業務指標を分析し、データを保存する必要がある規模を推測する(備考:必ず何GBを容量単位とする);lビジネスをサポートするために使用可能なハードウェアデバイスとデータベースアーキテクチャを選択する.lすべての操作可能なデータの条件と操作タイプを整理する.l操作データ条件フィールドのそれぞれのデータフィルタ率を分析する.l各SQLの性能とIO量を比較する.つまり、どの操作の重みが高いか、それらの操作の重みが適切に低いかに似ている.lインデックス組織構造の作成;lテストと生産環境のフィードバック情報を収集し、インデックス組織構造を最適化する.
備考:更にテスト環境で業務の方式を結合すると思って、1セットの模擬テストスクリプトプログラムを走って、みんなにもっと直観的に異なっているインデックスの組織の情況を見させて、同じSQL操作と周波数、データベースサーバーの処理能力と負荷の変化と比較情報、残念ながら唯一のサーバーは使うことができなくて、放棄するしかありません.同じSQLを分析するには、インデックスが通じません.論理IOと物理IOの量も必要です.今回は分析しません.必要な友达が游びに行くことができます.また、初心者はmysqlマニュアルの関連章の内容をよく読むことをお勧めします.7.2.6.Index Merge Optimization.