mysqlでインデックスを歩かない場合の集約(全量実験待ち)
11173 ワード
説明
MySQLでは、インデックスを作成したわけではありません.SQLでこの列を使用すると、MySQLは必ずインデックスを使用します.知らず知らずのうちに、MySQLのすべてのインデックスを「避けることに成功した」可能性があります.
インデックス列が計算に関与
計算がwhere条件の
インデックス列には関数が使用されています
同じように、インデックス列には関数が使用され、同じ結果が得られます.
インデックス列にはLike%XXXが使用されています
したがって、email列を検索する必要がある.comの末尾の文字列でemailにインデックスを付けたい場合は、データベースに逆方向のコンテンツreverse_を格納することを考慮します.email
注意:REVERSE(email)=REVERSE('%.com')を使用すると、インデックス列email列で関数を使用しているため、MySQLでは同じインデックスは使用されず、インデックス列で正規表現を使用してもインデックスは使用されません.
文字列と数値の直接比較
これはピットです.テーブルがあると仮定します.中のa列は文字charタイプで、aにインデックスが作成されています.数値タイプと比較して判断すると、次のようになります.
ただし、そのテーブルのカラムが数値タイプである場合、文字タイプと比較してもインデックスの使用には影響しません.
ただし、いずれにしても、このような追加の暗黙タイプ変換はオーバーヘッドであり、文字と数字の比がある場合はインデックスを移動しないため、すべての暗黙タイプ変換を回避することをお勧めします.
OR操作は極力避ける
したがって、各カラムにインデックスが作成されていない限りORは推奨されません.複数カラムORではUNIONで置き換えることが考えられます.
ORDER BY操作
ORDER BY操作でソートされた列が同時にWHEREにある場合、MYSQLはインデックスを使用できません.
MySQLインデックスは、通常、WHERE条件のデータ行の一致または結合操作を実行する際に他のテーブルのデータ行に一致する検索速度を向上させるために使用される.
MySQLは、インデックスを使用してORDER BY文とGROUP BY文の並べ替えとグループ化を迅速に実行することもできます.
インデックス最適化によりMySQLのORDER BY文最適化を実現する:
1、ORDER BYのインデックス最適化.SQL文がSELECT[column 1],[column 2],....FROM [TABLE] ORDER BY [sort];
[sort]というカラムにインデックスを作成すると,インデックスによるorder by最適化が実現される.
2、WHERE+ORDER BYのインデックスの最適化、例えば:SELECT[column 1],[column 2],....FROM [TABLE] WHERE [columnX] = [value] ORDER BY [sort];
order by最適化を実現するために、結合インデックス(columnX,sort)を確立する.
注:columnXが複数の値に対応する場合、次の文のようにインデックスを使用してorder byの最適化を実現できません.
SELECT [column1],[column2],…. FROM [TABLE] WHERE [columnX] IN ([value1],[value2],…) ORDER BY[sort];
3、WHERE+複数フィールドORDER BY SELECT*FROM[table]WHERE uid=1 ORDER x,y LIMIT 0,10;
インデックス(uid,x,y)を確立してorder byの最適化を実現することは、(x,y,uid)インデックスを確立するよりもずっと効果的である.
MySQL Order Byがインデックスを使用してソートを最適化できない場合
*異なるインデックスキーに対してORDER BY:(key 1,key 2はそれぞれインデックスを作成)
SELECT * FROM t1 ORDER BY key1, key2;
*連続していないインデックスキー部分でORDER BY:(key_part 1,key_part 2は結合インデックスを確立し、key 2はインデックスを確立する)
SELECT * FROM t1 WHERE key2=constant ORDER BY key_part2;
*ASCとDESCを併用しています:(key_part 1,key_part 2によるインデックスの結合)
SELECT * FROM t1 ORDER BY key_part1 DESC, key_part2 ASC;
*レコードを検索するためのインデックスキーとORDER BYは同じではありません:(key 1,key 2はそれぞれインデックスを作成します)
SELECT * FROM t1 WHERE key2=constant ORDER BY key1;
*WHEREとORDER BYのカラムに式(関数)を適用した場合、インデックスを使用してorder byの最適化はできません
SELECT * FROM t1 ORDER BY YEAR(logindate) LIMIT 0,10;
ヒント:1>mysqlクエリーでは1つのインデックスしか使用できません.複数のフィールドにインデックスを使用する場合は、複合インデックスを作成します.
2>ORDER BY操作では、MySQLはソート条件がクエリー条件式でない場合にのみインデックスを使用します.
Offset Limit操作
パフォーマンスに問題がある方法
このようなSQL文を書いた人は、MySQLデータベースが条件に合った100000位に直接位置し、30個のデータを取得すると思っているに違いありません.しかし、実際にMySQLはそうではありません.
LIMIT 100000,30とは、条件を満たす100030行をスキャンし、前の100000行を捨てて最後の30行に戻ることを意味します.
mysqlのlimitはページングに大きな便利さをもたらしたが,データオフセット量が大きいとlimitの性能が急激に低下した.
次の2つのクエリ文は、いずれも10個のデータを取得しますが、パフォーマンスは大きく異なります.
したがってlimit文を単純に使用してデータのページングを実現することはできません.
探究する
なぜoffsetが大きくなるとlimit検索が遅くなるのですか?これはlimit操作がどのように動作するかを理解する必要があります.次のクエリを例に挙げます.
このSQLの実行ロジックは1です.データテーブルから読み出すN番目のデータをデータセット2に追加する.N=10000+103まで第1ステップを繰り返す.offsetによって前の10000本の数4を捨てる.残りの10のデータを返します
明らかに、このSQLの速度が遅い問題は第2歩に現れます!この前の10000件のデータは完全に今回のクエリーに意味がありませんが、ほとんどのクエリー時間を占めています.どのように解決しますか?まず、データベースがなぜこのようにクエリーされるのかを理解しなければなりません.
まず、データベースのデータストレージは私たちが想像していたように、テーブルごとにデータを順番に格納するのではなく、コンピュータストレージ自体がランダム読み書きである一方で、データの操作に大きなランダム性があるため、最初はデータのストレージがシーケンス的であっても、一連の削除・変更を経ても乱れてしまいます.したがって,データベースのデータ格納はランダムであり,B+Tree,Hashなどを用いてインデックスを組織する.したがって、データベースに10001番目のデータを読み込ませると、データベースは1つ1つの数しか調べられません.
初回最適化
データベースのこのような検索の特性に基づいて、自増インデックス(idと仮定)を利用する当然の方法があります.
通常の検索は全表検索であるため,WHERE条件を適切に追加することで検索を全表検索から範囲検索に変換し,検索の範囲を大幅に縮小し,検索効率を向上させることができる.
この最適化の考え方はデータベースに教えることです.「数えないでください.10001番目のデータはこのようなものですから、直接取りに行きましょう.」
でも!!!このクエリーは簡単すぎて、追加のクエリー条件がありません.もし私がユーザーのデータさえあれば、この方法は通用しません.
このような考え方には限界があり、まず自己増加インデックス列が必要であり、データは論理的に連続しなければならない.次に、特徴値も知らなければならない.
このような厳しい要求は,実際の応用では満たすことができない.
さいてきさいてきか
データベース・クエリーの最適化といえば、最初に思いついたのがインデックスなので、2回目の最適化があります.まず、データが必要なインデックス・カラム(idと仮定)を検索し、インデックス・カラムで必要なデータを検索します.
比較結果は(500 w本のデータ):1本目の費用の平均消費時間は2本目の約1/3程度であった.
同じ大きなoffsetでも、最初のクエリは複雑ですが、なぜパフォーマンスが向上したのでしょうか.
これはmysqlプライマリインデックスのデータ構造b+Treeに関連し、ここでは展開しない.基本原理は以下の通りである.サブクエリはインデックス列のみ使用され、実際のデータは取得されていないため、ディスクIOには触れないので、比較的大きなoffsetクエリでも速度はあまり悪くありません. サブクエリを用いて,従来のuserベースの探索をプライマリキー(id)ベースの探索に変換し,プライマリクエリは正確なインデックス値が得られたため,クエリプロセスも比較的速い.
3回目の最適化
データ量が大きいときはin操作の効率はあまりよくありません.in操作を置き換える必要があります.joinを使うのはいい選択です.
これでlimitのクエリーでの最適化は一段落しました.もっと良い最適化方法があれば、伝言を歓迎します.
最終最適化
技術的な最適化は常に天井があり、ビジネスの最適化効果は往々にしてより顕著である.
例えば、本例では、データの時効性のため、最近15日間の操作ログのみを提供することを最終的に決定したが、この前提の下でオフセット値offsetは基本的に1万を超えることはない.これにより、最適化されていないsqlであっても、その実行効率は許容できるようになるので、最適化は技術面に限定されず、時には需要を調整して、思いがけない効果を達成する可能性があります.
MySQLでは、インデックスを作成したわけではありません.SQLでこの列を使用すると、MySQLは必ずインデックスを使用します.知らず知らずのうちに、MySQLのすべてのインデックスを「避けることに成功した」可能性があります.
インデックス列が計算に関与
計算がwhere条件の
age列で使用されている場合、インデックスは使用されません.計算が必要な場合は、インデックス列を計算しないで、式の反対側に計算させようとします. SELECT `sname` FROM `t_stu` WHERE `age`=20; --
SELECT `sname` FROM `t_stu` WHERE `age`+10=30; -- ！！
SELECT `sname` FROM `t_stu` WHERE `age`=30-10; --
インデックス列には関数が使用されています
同じように、インデックス列には関数が使用され、同じ結果が得られます.
SELECT `sname` FROM `stu` WHERE concat(`sname`,'abc') ='Jaskeyabc'; -- , ,
SELECT `sname` FROM `stu` WHERE `sname`=concat('Jaskey','abc'); -- インデックス列にはLike%XXXが使用されています
SELECT * FROM `houdunwang` WHERE `uname` LIKE ' %' --
SELECT * FROM `houdunwang` WHERE `uname` LIKE '% ' -- ，
したがって、email列を検索する必要がある.comの末尾の文字列でemailにインデックスを付けたい場合は、データベースに逆方向のコンテンツreverse_を格納することを考慮します.email
SELECT * FROM `table` WHERE `reverse_email` LIKE REVERSE('%.com'); --
注意:REVERSE(email)=REVERSE('%.com')を使用すると、インデックス列email列で関数を使用しているため、MySQLでは同じインデックスは使用されず、インデックス列で正規表現を使用してもインデックスは使用されません.
文字列と数値の直接比較
これはピットです.テーブルがあると仮定します.中のa列は文字charタイプで、aにインデックスが作成されています.数値タイプと比較して判断すると、次のようになります.
CREATE TABLE `t1` (`a` char(10));
SELECT * FROM `t1` WHERE `a`='1' --
SELECT * FROM `t2` WHERE `a`=1 -- ， ！ただし、そのテーブルのカラムが数値タイプである場合、文字タイプと比較してもインデックスの使用には影響しません.
CREATE TABLE `t2` (`b` int);
SELECT * FROM `t2` WHERE `b`='1' -- b ， '1'
ただし、いずれにしても、このような追加の暗黙タイプ変換はオーバーヘッドであり、文字と数字の比がある場合はインデックスを移動しないため、すべての暗黙タイプ変換を回避することをお勧めします.
OR操作は極力避ける
select * from dept where dname='jaskey' or loc='bj' or deptno=45
-- or, 。 , ,
したがって、各カラムにインデックスが作成されていない限りORは推奨されません.複数カラムORではUNIONで置き換えることが考えられます.
select * from dept where dname='jaskey' union
select * from dept where loc='bj' union
select * from dept where deptno=45ORDER BY操作
ORDER BY操作でソートされた列が同時にWHEREにある場合、MYSQLはインデックスを使用できません.
MySQLインデックスは、通常、WHERE条件のデータ行の一致または結合操作を実行する際に他のテーブルのデータ行に一致する検索速度を向上させるために使用される.
MySQLは、インデックスを使用してORDER BY文とGROUP BY文の並べ替えとグループ化を迅速に実行することもできます.
インデックス最適化によりMySQLのORDER BY文最適化を実現する:
1、ORDER BYのインデックス最適化.SQL文がSELECT[column 1],[column 2],....FROM [TABLE] ORDER BY [sort];
[sort]というカラムにインデックスを作成すると,インデックスによるorder by最適化が実現される.
2、WHERE+ORDER BYのインデックスの最適化、例えば:SELECT[column 1],[column 2],....FROM [TABLE] WHERE [columnX] = [value] ORDER BY [sort];
order by最適化を実現するために、結合インデックス(columnX,sort)を確立する.
注:columnXが複数の値に対応する場合、次の文のようにインデックスを使用してorder byの最適化を実現できません.
SELECT [column1],[column2],…. FROM [TABLE] WHERE [columnX] IN ([value1],[value2],…) ORDER BY[sort];
3、WHERE+複数フィールドORDER BY SELECT*FROM[table]WHERE uid=1 ORDER x,y LIMIT 0,10;
インデックス(uid,x,y)を確立してorder byの最適化を実現することは、(x,y,uid)インデックスを確立するよりもずっと効果的である.
MySQL Order Byがインデックスを使用してソートを最適化できない場合
*異なるインデックスキーに対してORDER BY:(key 1,key 2はそれぞれインデックスを作成)
SELECT * FROM t1 ORDER BY key1, key2;
*連続していないインデックスキー部分でORDER BY:(key_part 1,key_part 2は結合インデックスを確立し、key 2はインデックスを確立する)
SELECT * FROM t1 WHERE key2=constant ORDER BY key_part2;
*ASCとDESCを併用しています:(key_part 1,key_part 2によるインデックスの結合)
SELECT * FROM t1 ORDER BY key_part1 DESC, key_part2 ASC;
*レコードを検索するためのインデックスキーとORDER BYは同じではありません:(key 1,key 2はそれぞれインデックスを作成します)
SELECT * FROM t1 WHERE key2=constant ORDER BY key1;
*WHEREとORDER BYのカラムに式(関数)を適用した場合、インデックスを使用してorder byの最適化はできません
SELECT * FROM t1 ORDER BY YEAR(logindate) LIMIT 0,10;
ヒント:1>mysqlクエリーでは1つのインデックスしか使用できません.複数のフィールドにインデックスを使用する場合は、複合インデックスを作成します.
2>ORDER BY操作では、MySQLはソート条件がクエリー条件式でない場合にのみインデックスを使用します.
Offset Limit操作
パフォーマンスに問題がある方法
SELECT * FROM myTable ORDER BY `id` LIMIT 1000000, 30このようなSQL文を書いた人は、MySQLデータベースが条件に合った100000位に直接位置し、30個のデータを取得すると思っているに違いありません.しかし、実際にMySQLはそうではありません.
LIMIT 100000,30とは、条件を満たす100030行をスキャンし、前の100000行を捨てて最後の30行に戻ることを意味します.
mysqlのlimitはページングに大きな便利さをもたらしたが,データオフセット量が大きいとlimitの性能が急激に低下した.
次の2つのクエリ文は、いずれも10個のデータを取得しますが、パフォーマンスは大きく異なります.
したがってlimit文を単純に使用してデータのページングを実現することはできません.
探究する
なぜoffsetが大きくなるとlimit検索が遅くなるのですか?これはlimit操作がどのように動作するかを理解する必要があります.次のクエリを例に挙げます.
select * from table_name limit 10000,10 このSQLの実行ロジックは1です.データテーブルから読み出すN番目のデータをデータセット2に追加する.N=10000+103まで第1ステップを繰り返す.offsetによって前の10000本の数4を捨てる.残りの10のデータを返します
明らかに、このSQLの速度が遅い問題は第2歩に現れます!この前の10000件のデータは完全に今回のクエリーに意味がありませんが、ほとんどのクエリー時間を占めています.どのように解決しますか?まず、データベースがなぜこのようにクエリーされるのかを理解しなければなりません.
まず、データベースのデータストレージは私たちが想像していたように、テーブルごとにデータを順番に格納するのではなく、コンピュータストレージ自体がランダム読み書きである一方で、データの操作に大きなランダム性があるため、最初はデータのストレージがシーケンス的であっても、一連の削除・変更を経ても乱れてしまいます.したがって,データベースのデータ格納はランダムであり,B+Tree,Hashなどを用いてインデックスを組織する.したがって、データベースに10001番目のデータを読み込ませると、データベースは1つ1つの数しか調べられません.
初回最適化
データベースのこのような検索の特性に基づいて、自増インデックス(idと仮定)を利用する当然の方法があります.
select * from table_name where (id >= 10000) limit 10 通常の検索は全表検索であるため,WHERE条件を適切に追加することで検索を全表検索から範囲検索に変換し,検索の範囲を大幅に縮小し,検索効率を向上させることができる.
この最適化の考え方はデータベースに教えることです.「数えないでください.10001番目のデータはこのようなものですから、直接取りに行きましょう.」
でも!!!このクエリーは簡単すぎて、追加のクエリー条件がありません.もし私がユーザーのデータさえあれば、この方法は通用しません.
このような考え方には限界があり、まず自己増加インデックス列が必要であり、データは論理的に連続しなければならない.次に、特徴値も知らなければならない.
このような厳しい要求は,実際の応用では満たすことができない.
さいてきさいてきか
データベース・クエリーの最適化といえば、最初に思いついたのがインデックスなので、2回目の最適化があります.まず、データが必要なインデックス・カラム(idと仮定)を検索し、インデックス・カラムで必要なデータを検索します.
Select * From table_name Where id in (Select id From table_name where ( user = xxx )) limit 10000, 10; select * from table_name where( user = xxx ) limit 10000,10 比較結果は(500 w本のデータ):1本目の費用の平均消費時間は2本目の約1/3程度であった.
同じ大きなoffsetでも、最初のクエリは複雑ですが、なぜパフォーマンスが向上したのでしょうか.
これはmysqlプライマリインデックスのデータ構造b+Treeに関連し、ここでは展開しない.基本原理は以下の通りである.
3回目の最適化
データ量が大きいときはin操作の効率はあまりよくありません.in操作を置き換える必要があります.joinを使うのはいい選択です.
select * from table_name inner join ( select id from table_name where (user = xxx) limit 10000,10) b using (id) これでlimitのクエリーでの最適化は一段落しました.もっと良い最適化方法があれば、伝言を歓迎します.
最終最適化
技術的な最適化は常に天井があり、ビジネスの最適化効果は往々にしてより顕著である.
例えば、本例では、データの時効性のため、最近15日間の操作ログのみを提供することを最終的に決定したが、この前提の下でオフセット値offsetは基本的に1万を超えることはない.これにより、最適化されていないsqlであっても、その実行効率は許容できるようになるので、最適化は技術面に限定されず、時には需要を調整して、思いがけない効果を達成する可能性があります.