このトピックでは、ApsaraDB RDS for MySQLインスタンスの読み取りパフォーマンスを向上させるために最適なインデックスを選択または作成する方法について説明します。
背景情報
RDSインスタンスを使用する場合、SQLパフォーマンスの問題によりインスタンスに障害が発生する可能性があります。 インデックスの不足、暗黙的な変換、不適切なインデックス作成などのインデックスの問題は、SQLパフォーマンスの問題の一般的なタイプです。
インデックスの不足: SQL文を使用してインデックスが作成されていないテーブルにアクセスする場合、RDSインスタンスはテーブル全体のスキャンを実行します。 テーブル内のデータ量が多い場合、SQL文は低速で実行され、RDSインスタンスへの接続が消費されます。 RDSインスタンスに対して確立できる接続の最大数に達すると、アプリケーションからの新しい要求は拒否され、エラーが発生します。
暗黙的な変換: SQL文で指定されたデータ型がテーブルのフィールドのデータ型と異なる場合、インデックス作成は失敗します。
注意事項
likeキーワードを使用し、プレフィックスとしてパーセント記号 (%) を追加すると、インデックス作成は失敗します。
ほとんどの場合、null値を含む列にはインデックスは作成されません。 null値を含む列は、インデックス作成の対象となる列から除外されます。
orキーワードを使用し、orキーワードの左または右のフィールドにインデックスが付けられていない場合、インデックス作成は失敗します。
あなたが使用する場合! =演算子、インデックス作成が失敗します。 この場合、インデックス付きの範囲は不確定であり、RDSインスタンスは自動的にフルテーブルスキャンを実行します。 その結果、インデックスはクエリのパフォーマンスを大幅に向上させることはできません。
インデックス付きフィールドに対して操作を実行しないでください。
複合インデックスを使用してクエリのパフォーマンスを向上させる場合は、左端のプレフィックスマッチングの原則に従い、複合インデックスの最初のフィールドを使用する必要があります。 そうでない場合、インデックス作成は失敗します。 フィールドの順序がインデックスの順序と一致していることを確認してください。
暗黙的な変換を防ぐために、SQL文で指定されたデータ型がテーブルのフィールドのデータ型と一致していることを確認してください。
使用上の注意
頻繁に照会されるが、追加、削除、および変更操作によって頻繁に更新されないフィールドにインデックスを作成します。
ORDER BYおよびGROUP BYステートメントの直後にインデックス付きフィールドを使用します。
テーブルに作成されるインデックスの数が6を超えないようにしてください。
インデックスフィールドの長さが固定され、短いことを確認します。
インデックス付きフィールドの中で重複するフィールドの数が少ないことを確認してください。
高いfilterabilityのフィールドにインデックスを作成します。
例1: インデックスがないテーブルの最適化
SHOW CREATE TABLE customers;
ステートメントを実行して、テーブルのスキーマを表示します。説明customersテーブルは20エントリを含む。
CREATE TABLEの顧客 ( 'cust_id' int(11) NOT NULL AUTO_INCREMENT、 'cust_name' char(50) NOT NULL、 'cust_address' char(50) DEFAULT NULL、 'cust_cityy' char(50) DEFAULT NULL、 'cust_state' char(5) DEFAULT NULL、 'cust_zip 'char(10) DEFAULT NULL、 'cust_country' char(50) DEFAULT NULL、 'cust_contact' char(50) DEFAULT NULL、 'cust_email' char(255) DEFAULT NULL、 プライマリーキー ('cust_id') ) エンジン=InnoDB AUTO_INCREMENT=10006デフォルト料金=utf8;
EXPLAIN SELECT * FROM customers where cust_zip = '44444' limit 0,1;
ステートメントを実行して、データクエリに使用されるSQLステートメントの実行プランを表示します。
使用id: 1 select_type: シンプル テーブル: 顧客 パーティション: NULL タイプ: すべて possible_keys: NULL キー: NULL key_len: NULL ref: NULL 行: 20 filtered: 10.00 エクストラ: どこで
説明実行プランでは、typeパラメーターの値はALLです。これは、テーブル全体のスキャンを示します。 毎回合計20行のデータがスキャンされます。 これにより、データベースのパフォーマンスが大幅に低下します。
ALTER TABLE customers add index idx_cus(cust_zip);
ステートメントを実行してインデックスを作成します。EXPLAIN SELECT * FROM customers where cust_zip = '44444' limit 0,1;
ステートメントを再実行して、データクエリに使用されるSQLステートメントの新しい実行プランを表示します。id: 1 select_type: シンプル テーブル: 顧客 パーティション: NULL タイプ: ref possible_keys: idx_cus キー: idx_cus key_len: 31 ref: const 行: 1 filtered: 100.00 エクストラ: NULL
説明新しい実行プランでは、typeパラメーターの値はrefです。 この設定では、RDSインスタンスがインデックスベースの同等のクエリまたはテーブル間のequi-joinを実行することを指定します。 データの行が走査される。 これにより、クエリのパフォーマンスが大幅に向上します。
例2: インデックスがないテーブルの最適化
DROP TABLE if exists customers;
ステートメントを実行して、以前に作成したcustomersという名前のテーブルを削除します。データベースで次のステートメントを実行して、customersという名前のテストテーブルを作成します。
CREATE TABLEの顧客 ( 'cust_id' int(11) NOT NULL AUTO_INCREMENT、 'cust_name' char(50) NOT NULL、 'cust_address' char(50) DEFAULT NULL、 'cust_cityy' char(50) DEFAULT NULL、 'cust_state' char(5) DEFAULT NULL、 'cust_zip 'char(10) DEFAULT NULL、 'cust_country' char(50) DEFAULT NULL、 'cust_contact' char(50) DEFAULT NULL、 'cust_email' char(255) DEFAULT NULL、 プライマリーキー ('cust_id') ) エンジン=InnoDB AUTO_INCREMENT=10006デフォルト料金=utf8;
customersテーブルに20のデータエントリを挿入します。
INSERT INTO customers (cust_name、cust_address、cust_city、cust_state、cust_zip、cust_country、cust_contact、cust_email) 値 (「ジョン・スミス」、「123メインストリート」、「ニューヨーク」、「ニューヨーク」、「1000」、「USA」、「ジョン・ドゥ」、「ジョン @ example.com」) 、(「ジェーン・ドゥ」、「456エルム・スト」、「ロサンゼルス」、「CA」、「90001」、「USA」、「ジェーン・スミス」、「ジェーン @ example.com」) 、(「ボブジョンソン」、「789オークスト」、「シカゴ」、「IL」、「60601」、「USA」、「ボブスミス」、「ボブ @ example.com」) 、(「アリスブラウン」、「987メープルアベニュー」、「ヒューストン」、「TX」、「77001」、「USA」、「アリススミス」、「アリス @ example.com」) 、(「マイケル・デイビス」、「654パイン・スト」、「サンフランシスコ」、「CA」、「94101」、「USA」、「マイケル・スミス」、「マイケル @ example.com」) 、(「サラウィルソン」、「321シーダーSt」、「シアトル」、「WA」、「98101」、「USA」、「サラスミス」、「サラ @ example.com」) 、(「デビッド・リー」、「876バーチSt' 」、「ボストン」、「MA」、「0241」、「USA」、「デビッド・スミス」、「デビッド・example.com」) 、(「カレン・テイラー」、「543ウィローSt」、「マイアミ」、「FL」、「33101」、「USA」、「カレン・スミス」、「カレン @ example.com」) 、(「スティーブンミラー」、「210オークSt」、「デンバー」、「CO」、「80201」、「USA」、「スティーブンスミス」、「スティーブン @ example.com」) 、(「リサ・アンダーソン」、「876エルムSt」、「アトランタ」、「GA」、「30301」、「USA」、「リサ・スミス」、「リサ @ example.com」) 、(「マシューウィルソン」、「567パインSt」、「ダラス」、「TX」、「75201」、「USA」、「マシュースミス」、「マシュー @ example.com」) 、(「エミリージョンソン」、「654シーダーSt」、「フェニックス」、「AZ」、「85001」、「USA」、「エミリースミス」、「エミリー @ example.com」) 、(「ジェームズ・デイビス」、「321バーチSt' 」、「サンディエゴ」、「CA」、「92210」、「USA」、「ジェームズ・スミス」、「ジェームズ @ example.com」) 、(「オリビア・ブラウン」、「987ウィローSt' 」、「ポートランド」、「OR」、「97201」、「USA」、「オリビア・スミス」、「オリビア @ example.com」) 、(「ダニエル・ウィルソン」、「543オーク・スト」、「ラスベガス」、「NV」、「89101」、「USA」、「ダニエル・スミス」、「ダニエル @ example.com」) 、(「エマ・テイラー」、「210エルムSt」、「フィラデルフィア」、「PA」、「19101」、「USA」、「エマ・スミス」、「エマ @ example.com」) 、(「クリストファーミラー」、「876メープルアベニュー」、「オースティン」、「TX」、「78701」、「USA」、「クリストファースミス」、「クリストファー @ example.com」) 、(「ソフィア・アンダーソン」、「567パイン・スト」、「サンアントニオ」、「TX」、「78201」、「USA」、「ソフィア・スミス」、「ソフィア @ example.com」) 、(「ジェイコブ・ウィルソン」、「654シーダーSt」、「ナッシュビル」、「TN」、「37201」、「USA」、「ジェイコブ・スミス」、「example.com」) 、('Ava Johnson' 、'321 Birch St' 、'Charlot' 、'NC' 、'28201' 、'USA' 、'Ava Smith' 、'ava @ example.com ');
EXPLAIN SELECT cust_id,cust_name,cust_zip from customers where cust_zip = '42222'order by cust_zip,cust_name;
ステートメントを実行して、データクエリに使用されるSQLステートメントの新しい実行プランを表示します。
の使用id: 1 select_type: シンプル テーブル: 顧客 パーティション: NULL タイプ: すべて possible_keys: NULL キー: NULL key_len: NULL ref: NULL 行: 20 filtered: 10.00 Extra: どこで使用する; ファイルソート
ALTER TABLE customers add index idx_cu_zip_name(cust_zip,cust_name);
ステートメントを実行してインデックスを作成します。EXPLAIN SELECT cust_id,cust_name,cust_zip from customers where cust_zip = '42222' order by cust_zip,cust_name;
ステートメントを再実行して、データクエリに使用されるSQLステートメントの新しい実行プランを表示します。
の使用id: 1 select_type: シンプル テーブル: 顧客 パーティション: NULL タイプ: ref possible_keys: idx_cu_zip_name キー: idx_cu_zip_name key_len: 31 ref: const 行: 1 filtered: 100.00 エクストラ: どこで使用する; インデックス
例1: 暗黙的な変換が発生するテーブルの最適化
DROP TABLE if exists customers;
ステートメントを実行して、以前に作成したcustomersという名前のテーブルを削除します。データベースで次のステートメントを実行して、customersという名前のテストテーブルを作成します。
CREATE TABLEの顧客 ( 'cust_id' int(11) NOT NULL AUTO_INCREMENT、 'cust_name' char(50) NOT NULL、 'cust_address' char(50) DEFAULT NULL、 'cust_cityy' char(50) DEFAULT NULL、 'cust_state' char(5) DEFAULT NULL、 'cust_zip 'char(10) DEFAULT NULL、 'cust_country' char(50) DEFAULT NULL、 'cust_contact' char(50) DEFAULT NULL、 'cust_email' char(255) DEFAULT NULL、 プライマリーキー ('cust_id') ) エンジン=InnoDB AUTO_INCREMENT=10006デフォルト料金=utf8;
customersテーブルに20のデータエントリを挿入します。
INSERT INTO customers (cust_name、cust_address、cust_city、cust_state、cust_zip、cust_country、cust_contact、cust_email) 値 (「ジョン・スミス」、「123メインストリート」、「ニューヨーク」、「ニューヨーク」、「1000」、「USA」、「ジョン・ドゥ」、「ジョン @ example.com」) 、(「ジェーン・ドゥ」、「456エルム・スト」、「ロサンゼルス」、「CA」、「90001」、「USA」、「ジェーン・スミス」、「ジェーン @ example.com」) 、(「ボブジョンソン」、「789オークスト」、「シカゴ」、「IL」、「60601」、「USA」、「ボブスミス」、「ボブ @ example.com」) 、(「アリスブラウン」、「987メープルアベニュー」、「ヒューストン」、「TX」、「77001」、「USA」、「アリススミス」、「アリス @ example.com」) 、(「マイケル・デイビス」、「654パイン・スト」、「サンフランシスコ」、「CA」、「94101」、「USA」、「マイケル・スミス」、「マイケル @ example.com」) 、(「サラウィルソン」、「321シーダーSt」、「シアトル」、「WA」、「98101」、「USA」、「サラスミス」、「サラ @ example.com」) 、(「デビッド・リー」、「876バーチSt' 」、「ボストン」、「MA」、「0241」、「USA」、「デビッド・スミス」、「デビッド・example.com」) 、(「カレン・テイラー」、「543ウィローSt」、「マイアミ」、「FL」、「33101」、「USA」、「カレン・スミス」、「カレン @ example.com」) 、(「スティーブンミラー」、「210オークSt」、「デンバー」、「CO」、「80201」、「USA」、「スティーブンスミス」、「スティーブン @ example.com」) 、(「リサ・アンダーソン」、「876エルムSt」、「アトランタ」、「GA」、「30301」、「USA」、「リサ・スミス」、「リサ @ example.com」) 、(「マシューウィルソン」、「567パインSt」、「ダラス」、「TX」、「75201」、「USA」、「マシュースミス」、「マシュー @ example.com」) 、(「エミリージョンソン」、「654シーダーSt」、「フェニックス」、「AZ」、「85001」、「USA」、「エミリースミス」、「エミリー @ example.com」) 、(「ジェームズ・デイビス」、「321バーチSt' 」、「サンディエゴ」、「CA」、「92210」、「USA」、「ジェームズ・スミス」、「ジェームズ @ example.com」) 、(「オリビア・ブラウン」、「987ウィローSt' 」、「ポートランド」、「OR」、「97201」、「USA」、「オリビア・スミス」、「オリビア @ example.com」) 、(「ダニエル・ウィルソン」、「543オーク・スト」、「ラスベガス」、「NV」、「89101」、「USA」、「ダニエル・スミス」、「ダニエル @ example.com」) 、(「エマ・テイラー」、「210エルムSt」、「フィラデルフィア」、「PA」、「19101」、「USA」、「エマ・スミス」、「エマ @ example.com」) 、(「クリストファーミラー」、「876メープルアベニュー」、「オースティン」、「TX」、「78701」、「USA」、「クリストファースミス」、「クリストファー @ example.com」) 、(「ソフィア・アンダーソン」、「567パイン・スト」、「サンアントニオ」、「TX」、「78201」、「USA」、「ソフィア・スミス」、「ソフィア @ example.com」) 、(「ジェイコブ・ウィルソン」、「654シーダーSt」、「ナッシュビル」、「TN」、「37201」、「USA」、「ジェイコブ・スミス」、「example.com」) 、('Ava Johnson' 、'321 Birch St' 、'Charlot' 、'NC' 、'28201' 、'USA' 、'Ava Smith' 、'ava @ example.com ');
EXPLAIN SELECT * FROM customers where cust_zip = 44444 limit 0,1;
ステートメントを実行して、データクエリに使用されるSQLステートメントの新しい実行プランを表示します。
使用id: 1 select_type: シンプル テーブル: 顧客 パーティション: NULL タイプ: すべて possible_keys: NULL キー: NULL key_len: NULL ref: NULL 行: 20 filtered: 10.00 エクストラ: どこで
例2: 暗黙的な変換が発生する2つのテーブルの最適化
SHOW CREATE TABLE customers1;
ステートメントとSHOW CREATE TABLE customers2;
ステートメントを実行して、customers 1テーブルとcustomers 2テーブルのスキーマを表示します。テーブル 'customers1' を作成する ( 'cust_id' varchar(10) 文字セットlatin1 COLLATE latin1_bin DEFAULT NULL、 'cust_name' char(50) NOT NULL、 KEY 'idx_cu_id ' ('cust_id') ) エンジン=InnoDBデフォルト料金セット=utf8;
CREATE TABLE 'customers2' ( 'cust_id' varchar(10) 文字SET utf8 COLLATE utf8_bin DEFAULT NULL、 'cust_name' char(50) NOT NULL、 KEY 'idx_cu_id ' ('cust_id') ) エンジン=InnoDBデフォルト料金=utf8;
EXPLAIN SELECT customers1.* from customers2 left join customers1 on customers1.cust_id=customers2.cust_id where customers2.cust_id='x';
ステートメントを実行して、データクエリに使用されるSQLステートメントの新しい実行計画を表示します。*************************** 1。 行 *************************** id: 1 select_type: シンプル テーブル: customers2 タイプ: ref possible_keys: idx_cu_id キー: idx_cu_id key_len: 33 ref: const 行: 1 Extra: Using where; Using index
*************************** 2. 行 *************************** id: 1 select_type: シンプル テーブル: customers1 タイプ: すべて possible_keys: NULL キー: NULL key_len: NULL ref: NULL 行: 1 Extra: Using where; joinバッファの使用 (ブロックネストループ)
説明上記の結果は、2つのテーブルのcust_idフィールドの文字セットに指定されているデータ型が異なることを示しています。 その結果、暗黙の変換が発生し、インデックスは使用できません。
ALTER TABLE customers1 modify column cust_id varchar (10) COLLATE utf8_bin;
ステートメントを実行して、customers 1テーブルのcust_idフィールドの文字セットのデータ型をUTF-8に変更します。 変更後、2つのテーブルのcust_idフィールドの文字セットのデータ型は一致します。説明この文を実行すると、customers 2テーブルのcust_idフィールドの文字セットのデータ型がUTF-8に変更されます。
EXPLAIN SELECT customers1.* from customers2 left join customers1 on customers1.cust_id=customers2.cust_id where customers2.cust_id='x';
ステートメントを再実行して、データクエリに使用されるSQLステートメントの新しい実行計画を表示します。*************************** 1。 行 *************************** id: 1 select_type: シンプル テーブル: customers2 タイプ: ref possible_keys: idx_cu_id キー: idx_cu_id key_len: 33 ref: const 行: 1 Extra: Using where; Using index
使用*************************** 2. 行 *************************** id: 1 select_type: シンプル テーブル: customers1 タイプ: ref possible_keys: idx_cu_id キー: idx_cu_id key_len: 33 ref: const 行: 1 エクストラ: どこで
説明データ型が一致すると、インデックスが使用され、データベースのパフォーマンスが向上します。