MySQLインデックスの分析と最適化
9255 ワード
1.MySQLは、nameのインデックスで「Mike」の値を検索し、データファイルの対応する行に直接移動し、その行のpeopleid(999)を正確に返すことができます.このプロセスでは、MySQLはローを1つ処理するだけで結果を返すことができます.「name」列のインデックスがない場合、MySQLはデータファイル内のすべてのレコード、すなわち1000レコードをスキャンします.MySQL処理が必要なレコードの数が少ないほど、タスクの完了速度が速くなることは明らかです.2.クエリーを実行するとき、MySQLは1つのインデックスしか使用できません.3つの単列インデックスがある場合、MySQLは最も制限の厳しいインデックスを選択しようとします.しかし、最も厳格な単一カラムインデックスを制限しても、firstname、lastname、ageの3つのカラムの複数カラムインデックスよりも制限能力が低いに違いありません.3.複数列インデックスには、最も左接頭辞(Leftmost Prefixing)と呼ばれる概念によって表現されるもう一つの利点がある.前の例を考えてみると、firstname、lastname、age列の複数の列インデックスがあります.このインデックスをfnameと呼びます.lname_age.検索条件が次の各列の組合せである場合、MySQLはfname_を使用します.lname_age索引:
別の側面から理解すると、(firstname,lastname,age)、(firstname,lastname)、および(firstname)これらのカラムの組合せのインデックスを作成したことに相当する.次のクエリでは、このfnameを使用できます.lname_age索引:
4.パフォーマンスの最適化において、インデックスを作成する列を選択することが最も重要なステップの1つです.インデックスを使用するには、主にWHERE句に現れる列、join句に現れる列の2種類が考えられます.5.WHERE句とjoin句のそれぞれの列をインデックスすべきだと簡単に考えられますか?差は多くないが、完全ではない.カラムを比較するオペレータタイプも考慮する必要があります.MySQLでは、<,<=,=,>,>=,BETWEEN,IN,およびある時のLIKEを使用するオペレータのみが使用されます.LIKEオペレーションでインデックスを使用できる場合は、他のオペレーション数がワイルドカード(%または_)でないことを意味します.冒頭の様子.例えば、「SELECT peopleid FROM people WHERE firstname LIKE'Mich%」;このクエリはインデックスを使用しますが、「SELECT peopleid FROM people WHERE firstname LIKE'%ike';」このクエリはインデックスを使用しません. 6.DELETE、UPDATE、INSERTなどのデータを書き込む操作では、インデックスが速度を低下させます.これは、MySQLが変更データをデータファイルに書き込むだけでなく、これらの変更をインデックスファイルに書き込むためです.
インデックスは、特定の値を持つレコードをすばやく検索するために使用され、すべてのMySQLインデックスはBツリーとして保存されます.インデックスがない場合は、クエリを実行するときにMySQLは、必要に応じたレコードが見つかるまで、最初のレコードからテーブル全体のすべてのレコードをスキャンする必要があります.表の記録数が多ければ多いほど、この操作の代価は高くなります.検索条件となるカラムにインデックスが作成されている場合、MySQLはレコードをスキャンすることなく、ターゲットレコードの場所を迅速に取得できます.テーブルに1000個のレコードがある場合、インデックス検索レコードは、シーケンススキャンレコードよりも少なくとも100倍速くなります.
1.索引の例
10.3節で作成したテーブルについて,各テーブルにインデックスがなく,データの配列も規則的ではないと仮定し,表13.3に示す.
表13.3インデックスのないstudents表
sid
sname
sgender
sage
52
zhang
M
21
22
wang
M
22
33
li
F
19
41
zhao
M
20
…
…
…
学生情報を検索する場合は、テーブルstudentsの各行を順番に表示し、必要な値と一致するかどうかを確認する必要があります.これは、テーブル全体をスキャンする必要があり、効率が低いです.
表13.4は、name列にインデックスを追加したstudentsテーブルを示す.
表13.4 name列にインデックスを追加したstudents表
インデックスはnameでソートされます.これにより、学生情報を検索するときに、テーブル全体を行単位で検索する必要がなくなり、インデックスを使用して秩序正しく検索(二分検索など)し、一致する値にすばやくナビゲートして、検索時間を大幅に節約できます.
2.索引の役割
インデックス列では、前述した順序検索に加えて、データベースはさまざまな高速位置決め技術を利用して、クエリーの効率を大幅に向上させることができます.特に、データ量が非常に大きく、クエリーが複数のテーブルに関連している場合、インデックスを使用すると、クエリーの速度が数千倍に速くなることがよくあります.
例えば、3つのインデックスされていないテーブルt 1,t 2,t 3があり、それぞれ列c 1,c 2,c 3のみを含み、各テーブルはそれぞれ1000行のデータからなり、1〜1000の数値を指し、対応する値の等しい行を検索するクエリは以下のようになる.
SELECT c1,c2,c3 FROM t1,t2,t3 WHERE c1=c2 AND c1=c3
このクエリーの結果は1000行で、各行に3つの等しい値が含まれます.インデックスなしでこのクエリを処理するには、WHERE句に一致するローを導くために、3つのテーブルのすべての組合せを検索する必要があります.可能な組み合わせの数は1000です×1000×1000(10億ドル)では、クエリーが非常に遅くなるのは明らかです.
各テーブルをインデックス化すると、クエリー・プロセスが大幅に高速化されます.インデックスを利用したクエリーは以下のように処理されます.
(1)表t 1から第1行を選択し、その行に含まれるデータを表示する.
(2)表t 2のインデックスを用いて、t 2のt 1の値に一致する行を直接位置決めする.同様に、表t 3のインデックスを用いて、t 3のt 1からの値に一致する行を直接位置決めする.
(3)テーブルt 1の次の行をスキャンし、t 1内のすべての行を巡回するまで、前の処理を繰り返す.
この場合、テーブルt 1に対して完全スキャンが実行されるが、インデックスを使用しない場合よりも100万倍速く、テーブルt 2およびt 3上でインデックス検索を行い、これらのテーブルのローを直接取り出すことができる.
インデックスを使用すると、MySQLはWHERE句が条件を満たす行の検索を高速化し、マルチテーブル接続クエリーでは、接続を実行するときに他のテーブルの行と一致する速度を高速化します.
索引の作成
CREATE TABLE文を実行するときにインデックスを作成するか、CREATE INDEXまたはALTER TABLEを使用してテーブルにインデックスを追加できます.
1．ALTER TABLE
ALTER TABLEは、通常のインデックス、UNIQUEインデックス、またはPRIMARY KEYインデックスを作成するために使用されます.
ALTER TABLE table_name ADD INDEX index_name (column_list)
ALTER TABLE table_name ADD UNIQUE (column_list)
ALTER TABLE table_name ADD PRIMARY KEY (column_list)
ここでtable_nameはインデックスを追加するテーブル名、column_listは、どのカラムをインデックスするかを示し、複数カラムの場合、各カラム間をカンマで区切る.インデックス名index_nameはオプションで、デフォルトではMySQLは最初のインデックス列に基づいて名前を付けます.また、ALTER TABLEでは、単一の文で複数のテーブルを変更できるため、同時に複数のインデックスを作成できます.
2．CREATE INDEX
CREATE INDEXは、テーブルに通常のインデックスまたはUNIQUEインデックスを追加します.
CREATE INDEX index_name ON table_name (column_list)
CREATE UNIQUE INDEX index_name ON table_name (column_list)
table_name、index_nameとcolumn_リストにはALTER TABLE文と同じ意味があり、インデックス名はオプションではありません.また、CREATE INDEX文でPRIMARY KEYインデックスを作成することはできません.
3.索引タイプ
インデックスを作成するときに、インデックスに重複値が含まれるかどうかを指定できます.含まない場合、インデックスはPRIMARY KEYまたはUNIQUEインデックスとして作成する必要があります.単一カラムの一意性インデックスの場合、単一カラムに重複する値が含まれないことを保証します.複数のカラムの一意性インデックスの場合、複数の値の組合せが重複しないことを保証します.
PRIMARY KEYインデックスはUNIQUEインデックスとよく似ています.実際、PRIMARY KEYインデックスは、PRIMARYという名前のUNIQUEインデックスのみです.これは、1つのテーブルに同じ名前のインデックスが2つあるはずがないため、1つのテーブルに1つのPRIMARY KEYしか含まれないことを示します.
次のSQL文はstudentsテーブルにPRIMARY KEYインデックスをsidに追加します.
ALTER TABLE students ADD PRIMARY KEY (sid)
13.4.3索引の削除
ALTER TABLEまたはDROP INDEX文を使用してインデックスを削除できます.CREATE INDEX文と同様に、DROP INDEXはALTER TABLE内部で文として処理できます.構文は次のとおりです.
DROP INDEX index_name ON talbe_name
ALTER TABLE table_name DROP INDEX index_name
ALTER TABLE table_name DROP PRIMARY KEY
ただし、最初の2つの文は等価で、table_を削除します.nameのインデックスindex_name.
3番目の文はPRIMARY KEYインデックスを削除する場合にのみ使用されます.1つのテーブルにPRIMARY KEYインデックスが1つしかないため、インデックス名を指定する必要はありません.PRIMARY KEYインデックスが作成されていないが、テーブルに1つ以上のUNIQEインデックスがある場合、MySQLは最初のUNIQEインデックスを削除します.
テーブルからカラムを削除すると、インデックスが影響を受けます.複数のカラムの組合せのインデックスの場合、カラムのいずれかを削除すると、そのカラムもインデックスから削除されます.インデックスを構成するすべてのカラムを削除すると、インデックス全体が削除されます.
ユニークインデックス
データ・テーブルに重複レコードがない場合は、テーブルにユニークなインデックスを追加できます.たとえば、userテーブルにuser_があります.id,user_nameの2つのフィールドは、同じuser_を2つタッチしたくない場合はidとuser_name、userテーブルに2つのフィールドの連合一意インデックスを追加できます:alter table user add unique index(user_id、user_name);これにより、テーブルに同じレコードが追加されると、1062の追加失敗情報が返される.しかし、テーブルにn個の重複するレコードがある場合があります.この場合、一意のインデックスを追加してから、上記の操作を実行すると、データベースは重複するレコードがあることを教えてくれます.インデックスの作成に失敗しました.この場合、alter ignore table user add unique index(user_id、user_name)を使用することができます.重複するレコードを削除し(恐れずに1つ保持します)、一意のインデックスを作成し、効率的で人間的になります.特に緑色のsqlは、インデックスを作成しながら重複データを削除するのに役立ちます.
<ccid_code>firstname，lastname，age
firstname，lastname
firstname別の側面から理解すると、(firstname,lastname,age)、(firstname,lastname)、および(firstname)これらのカラムの組合せのインデックスを作成したことに相当する.次のクエリでは、このfnameを使用できます.lname_age索引:
4.パフォーマンスの最適化において、インデックスを作成する列を選択することが最も重要なステップの1つです.インデックスを使用するには、主にWHERE句に現れる列、join句に現れる列の2種類が考えられます.5.WHERE句とjoin句のそれぞれの列をインデックスすべきだと簡単に考えられますか?差は多くないが、完全ではない.カラムを比較するオペレータタイプも考慮する必要があります.MySQLでは、<,<=,=,>,>=,BETWEEN,IN,およびある時のLIKEを使用するオペレータのみが使用されます.LIKEオペレーションでインデックスを使用できる場合は、他のオペレーション数がワイルドカード(%または_)でないことを意味します.冒頭の様子.例えば、「SELECT peopleid FROM people WHERE firstname LIKE'Mich%」;このクエリはインデックスを使用しますが、「SELECT peopleid FROM people WHERE firstname LIKE'%ike';」このクエリはインデックスを使用しません. 6.DELETE、UPDATE、INSERTなどのデータを書き込む操作では、インデックスが速度を低下させます.これは、MySQLが変更データをデータファイルに書き込むだけでなく、これらの変更をインデックスファイルに書き込むためです.
SELECT peopleid FROM people WHERE firstname='Mike' AND lastname='Sullivan' AND age='17';
SELECT peopleid FROM people WHERE firstname='Mike' AND
lastname='Sullivan';
SELECT peopleid FROM people WHERE firstname='Mike';
The following queries cannot use the index at all:
SELECT peopleid FROM people WHERE lastname='Sullivan'; SELECT peopleid FROM people WHERE age='17';
SELECT peopleid FROM people WHERE lastname='Sullivan' AND age='17';インデックスは、特定の値を持つレコードをすばやく検索するために使用され、すべてのMySQLインデックスはBツリーとして保存されます.インデックスがない場合は、クエリを実行するときにMySQLは、必要に応じたレコードが見つかるまで、最初のレコードからテーブル全体のすべてのレコードをスキャンする必要があります.表の記録数が多ければ多いほど、この操作の代価は高くなります.検索条件となるカラムにインデックスが作成されている場合、MySQLはレコードをスキャンすることなく、ターゲットレコードの場所を迅速に取得できます.テーブルに1000個のレコードがある場合、インデックス検索レコードは、シーケンススキャンレコードよりも少なくとも100倍速くなります.
1.索引の例
10.3節で作成したテーブルについて,各テーブルにインデックスがなく,データの配列も規則的ではないと仮定し,表13.3に示す.
表13.3インデックスのないstudents表
sid
sname
sgender
sage
52
zhang
M
21
22
wang
M
22
33
li
F
19
41
zhao
M
20
…
…
…
学生情報を検索する場合は、テーブルstudentsの各行を順番に表示し、必要な値と一致するかどうかを確認する必要があります.これは、テーブル全体をスキャンする必要があり、効率が低いです.
表13.4は、name列にインデックスを追加したstudentsテーブルを示す.
表13.4 name列にインデックスを追加したstudents表
インデックスはnameでソートされます.これにより、学生情報を検索するときに、テーブル全体を行単位で検索する必要がなくなり、インデックスを使用して秩序正しく検索(二分検索など)し、一致する値にすばやくナビゲートして、検索時間を大幅に節約できます.
2.索引の役割
インデックス列では、前述した順序検索に加えて、データベースはさまざまな高速位置決め技術を利用して、クエリーの効率を大幅に向上させることができます.特に、データ量が非常に大きく、クエリーが複数のテーブルに関連している場合、インデックスを使用すると、クエリーの速度が数千倍に速くなることがよくあります.
例えば、3つのインデックスされていないテーブルt 1,t 2,t 3があり、それぞれ列c 1,c 2,c 3のみを含み、各テーブルはそれぞれ1000行のデータからなり、1〜1000の数値を指し、対応する値の等しい行を検索するクエリは以下のようになる.
SELECT c1,c2,c3 FROM t1,t2,t3 WHERE c1=c2 AND c1=c3
このクエリーの結果は1000行で、各行に3つの等しい値が含まれます.インデックスなしでこのクエリを処理するには、WHERE句に一致するローを導くために、3つのテーブルのすべての組合せを検索する必要があります.可能な組み合わせの数は1000です×1000×1000(10億ドル)では、クエリーが非常に遅くなるのは明らかです.
各テーブルをインデックス化すると、クエリー・プロセスが大幅に高速化されます.インデックスを利用したクエリーは以下のように処理されます.
(1)表t 1から第1行を選択し、その行に含まれるデータを表示する.
(2)表t 2のインデックスを用いて、t 2のt 1の値に一致する行を直接位置決めする.同様に、表t 3のインデックスを用いて、t 3のt 1からの値に一致する行を直接位置決めする.
(3)テーブルt 1の次の行をスキャンし、t 1内のすべての行を巡回するまで、前の処理を繰り返す.
この場合、テーブルt 1に対して完全スキャンが実行されるが、インデックスを使用しない場合よりも100万倍速く、テーブルt 2およびt 3上でインデックス検索を行い、これらのテーブルのローを直接取り出すことができる.
インデックスを使用すると、MySQLはWHERE句が条件を満たす行の検索を高速化し、マルチテーブル接続クエリーでは、接続を実行するときに他のテーブルの行と一致する速度を高速化します.
索引の作成
CREATE TABLE文を実行するときにインデックスを作成するか、CREATE INDEXまたはALTER TABLEを使用してテーブルにインデックスを追加できます.
1．ALTER TABLE
ALTER TABLEは、通常のインデックス、UNIQUEインデックス、またはPRIMARY KEYインデックスを作成するために使用されます.
ALTER TABLE table_name ADD INDEX index_name (column_list)
ALTER TABLE table_name ADD UNIQUE (column_list)
ALTER TABLE table_name ADD PRIMARY KEY (column_list)
ここでtable_nameはインデックスを追加するテーブル名、column_listは、どのカラムをインデックスするかを示し、複数カラムの場合、各カラム間をカンマで区切る.インデックス名index_nameはオプションで、デフォルトではMySQLは最初のインデックス列に基づいて名前を付けます.また、ALTER TABLEでは、単一の文で複数のテーブルを変更できるため、同時に複数のインデックスを作成できます.
2．CREATE INDEX
CREATE INDEXは、テーブルに通常のインデックスまたはUNIQUEインデックスを追加します.
CREATE INDEX index_name ON table_name (column_list)
CREATE UNIQUE INDEX index_name ON table_name (column_list)
table_name、index_nameとcolumn_リストにはALTER TABLE文と同じ意味があり、インデックス名はオプションではありません.また、CREATE INDEX文でPRIMARY KEYインデックスを作成することはできません.
3.索引タイプ
インデックスを作成するときに、インデックスに重複値が含まれるかどうかを指定できます.含まない場合、インデックスはPRIMARY KEYまたはUNIQUEインデックスとして作成する必要があります.単一カラムの一意性インデックスの場合、単一カラムに重複する値が含まれないことを保証します.複数のカラムの一意性インデックスの場合、複数の値の組合せが重複しないことを保証します.
PRIMARY KEYインデックスはUNIQUEインデックスとよく似ています.実際、PRIMARY KEYインデックスは、PRIMARYという名前のUNIQUEインデックスのみです.これは、1つのテーブルに同じ名前のインデックスが2つあるはずがないため、1つのテーブルに1つのPRIMARY KEYしか含まれないことを示します.
次のSQL文はstudentsテーブルにPRIMARY KEYインデックスをsidに追加します.
ALTER TABLE students ADD PRIMARY KEY (sid)
13.4.3索引の削除
ALTER TABLEまたはDROP INDEX文を使用してインデックスを削除できます.CREATE INDEX文と同様に、DROP INDEXはALTER TABLE内部で文として処理できます.構文は次のとおりです.
DROP INDEX index_name ON talbe_name
ALTER TABLE table_name DROP INDEX index_name
ALTER TABLE table_name DROP PRIMARY KEY
ただし、最初の2つの文は等価で、table_を削除します.nameのインデックスindex_name.
3番目の文はPRIMARY KEYインデックスを削除する場合にのみ使用されます.1つのテーブルにPRIMARY KEYインデックスが1つしかないため、インデックス名を指定する必要はありません.PRIMARY KEYインデックスが作成されていないが、テーブルに1つ以上のUNIQEインデックスがある場合、MySQLは最初のUNIQEインデックスを削除します.
テーブルからカラムを削除すると、インデックスが影響を受けます.複数のカラムの組合せのインデックスの場合、カラムのいずれかを削除すると、そのカラムもインデックスから削除されます.インデックスを構成するすべてのカラムを削除すると、インデックス全体が削除されます.
ユニークインデックス
データ・テーブルに重複レコードがない場合は、テーブルにユニークなインデックスを追加できます.たとえば、userテーブルにuser_があります.id,user_nameの2つのフィールドは、同じuser_を2つタッチしたくない場合はidとuser_name、userテーブルに2つのフィールドの連合一意インデックスを追加できます:alter table user add unique index(user_id、user_name);これにより、テーブルに同じレコードが追加されると、1062の追加失敗情報が返される.しかし、テーブルにn個の重複するレコードがある場合があります.この場合、一意のインデックスを追加してから、上記の操作を実行すると、データベースは重複するレコードがあることを教えてくれます.インデックスの作成に失敗しました.この場合、alter ignore table user add unique index(user_id、user_name)を使用することができます.重複するレコードを削除し(恐れずに1つ保持します)、一意のインデックスを作成し、効率的で人間的になります.特に緑色のsqlは、インデックスを作成しながら重複データを削除するのに役立ちます.