私の考えを混乱させないために、最初の投稿では、あるオープンソース製品からのリクエストの例を取り上げます。 使用される「トリック」はPostgreSQL、Oracle、SQLite、DB2でも機能し、MySQL向けではありませんが、最適化は主にMySQL InnoDBを対象としています。
SELECT h.hostid,hg.groupid,h.name
FROM hosts h,hosts_groups hg
WHERE (hg.groupid IN ( '4' ))
AND hg.hostid=h.hostid
AND hg.groupid BETWEEN 000000000000000 AND 099999999999999
AND h.status IN (0,1)
AND EXISTS (
SELECT hh.hostid
FROM hosts hh, hosts_groups hgg, rights r, users_groups ug
WHERE hh.hostid=h.hostid
AND hh.hostid=hgg.hostid
AND r.id=hgg.groupid
AND r.groupid=ug.usrgrpid
AND ug.userid=3
AND r.permission>=3
AND NOT EXISTS (
SELECT hggg.groupid
FROM hosts_groups hggg, rights rr, users_groups gg
WHERE hggg.hostid=hgg.hostid
AND rr.id=hggg.groupid
AND rr.groupid=gg.usrgrpid
AND gg.userid=3
AND rr.permission<3
))
ORDER BY h.name ASC
LIMIT 1001
* This source code was highlighted with Source Code Highlighter .
状況：承認されたユーザーが存在し、そのユーザーはユーザーグループに属している。 ホストグループに属するホストがあります。
一番下の行：ユーザーがアクセスできるホストを引き出します。 ホストグループのユーザーグループにアクセスが許可されます。
最初に注意したいのは、 EXISTS状態です。 この構造の使用はめったにありません。 EXISTSは、サブクエリ内の行をチェックするサブクエリです。 このクエリデザインでは、一般的なクエリに関係なく、クエリで使用されるインデックス（メインクエリとサブクエリの両方）を操作できます。さらに、成功した場合、サブクエリはクエリを満たす最初に見つかった行で停止します。 リクエストでソートが使用される場合、キーの操作が必要になることがよくあります。 MySQLは検索とソートに異なるキーを使用できないためです。
クエリが1つのテーブルで発生する場合、次のトリックを使用できる場合があります。
CREATE TABLE events (
eventid bigint unsigned NOT NULL ,
source integer DEFAULT '0' NOT NULL ,
object integer DEFAULT '0' NOT NULL ,
objectid bigint unsigned DEFAULT '0' NOT NULL ,
clock integer DEFAULT '0' NOT NULL ,
value integer DEFAULT '0' NOT NULL ,
acknowledged integer DEFAULT '0' NOT NULL ,
ns integer DEFAULT '0' NOT NULL ,
value_changed integer DEFAULT '0' NOT NULL ,
PRIMARY KEY (eventid)
) ENGINE=InnoDB;
CREATE INDEX events_1 ON events ( object ,objectid,eventid);
CREATE INDEX events_2 ON events (clock,eventid);
* This source code was highlighted with Source Code Highlighter .
EXPLAIN
SELECT eventid,clock, value
FROM events
WHERE objectid=17131
AND object =0
AND clock>=1325635327
ORDER BY clock DESC , eventid DESC ;
* This source code was highlighted with Source Code Highlighter .
+----+-------------+--------+------+-------------------+----------+---------+-------------+------+-----------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------+------+-------------------+----------+---------+-------------+------+-----------------------------+
| 1 | SIMPLE | events | ref | events_1,events_2 | events_1 | 12 | const,const | 113056 | Using where; Using filesort |
+----+-------------+--------+------+-------------------+----------+---------+-------------+------+-----------------------------+
たとえば、MySQLはWHEREでevents_1インデックスを使用する方が収益性が高いと見なしました。これは、WHEREが2つのキーフィールドを使用するため理解しやすいものですが、結果に10万行が含まれ、ソートする必要があることを考慮していませんでした。
この場合、クエリで選択したMySQLインデックスの最初のフィールドの基準を変更します。
EXPLAIN
SELECT eventid,clock, value
FROM events
WHERE objectid=17131
AND object +0=0
AND clock>=1325635327
ORDER BY clock DESC , eventid DESC ;
* This source code was highlighted with Source Code Highlighter .
+----+-------------+--------+-------+---------------+----------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------+-------+---------------+----------+---------+------+------+-------------+
| 1 | SIMPLE | events | range | events_2 | events_2 | 4 | NULL | 113056 | Using where |
+----+-------------+--------+-------+---------------+----------+---------+------+------+-------------+
MySQLは修正されたフィールド値にインデックスを使用できません。この場合、算術割り当てを使用して、EXPLAINからわかるように、データの選択とソートの両方に適したevents_2インデックスの使用を強制します。
使用するフィールドが異なる順序でソートされている場合、MySQLはインデックスでソートできないことに注意してください。
EXPLAIN
SELECT eventid,clock, value
FROM events
WHERE objectid=17131
AND object +0=0
AND clock>=1325635327
ORDER BY clock ASC , eventid DESC ;
* This source code was highlighted with Source Code Highlighter .
+----+-------------+--------+-------+---------------+----------+---------+------+------+-----------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------+-------+---------------+----------+---------+------+------+-----------------------------+
| 1 | SIMPLE | events | range | events_2 | events_2 | 4 | NULL | 113056 | Using where; Using filesort |
+----+-------------+--------+-------+---------------+----------+---------+------+------+-----------------------------+
また、インデックスとは異なる順序でフィールドでソートが行われる場合、インデックスは使用されません。 一般に、MySQLはインデックスをBツリーとして保存するため、インデックスの中央または末尾のフィールドを使用すると失敗します。
選択とソートに1つのインデックスを使用することが不可能な場合、MySQLがソートのインデックスを正しく評価および選択しなかったことが起こりました。 これは、テーブル内の特定の数のレコードを通過するときに発生します。MySQLがインデックスを選択に使用し、ソートに使用するのではなく、その逆に使用する方がより有益になります。 そのような瞬間を予測することは困難であり、異なる数のレコードで同じクエリをチェックすることで明らかになります。
最初のリクエストに戻ります。 私の意見では、もちろん、選択範囲が数千行以内であれば、実行可能なスクリプトを使用してソートすることをお勧めします。
まず、この場合、MySQLはテーブルから完全に選択する必要はありませんが、LIMITに達すると停止します。
第二に、レポートでない限り、同時により多くの情報を表示することが本当に必要な場合はめったにありません。
第三に、多くの情報を含むページは、1000以上のオプションを備えた単なるドロップダウンリストであっても、遅くなります。これはもはや使用できません。
4番目に、MySQLには自然なソートはありません。
しかし、実際の例では、これはほとんど不可能です。 ユーザーにソート機能を提供する必要があります。ソートを指定しなくても、MySQLは同じ選択を保証しません。 または、インデックスに最初のフィールドを置くことができます。これにより、ソートがより頻繁に行われるため、MySQLは検索とソートの両方に1つのインデックスを使用します。
COUNT、多くの人が改ページの2回目のリクエストを行いますが、同じGoogleは、数百万件以上の一致が見つかったと言っていますが、実際には最初の1,000件について表示します。 さらに、最後のページで、実際に少ないものが見つかったことを通知します。 そのため、1001行を選択した後、1000以上の一致が見つかったため、この段階でこれ以上選択する必要がないことをユーザーに伝えるだけです。 ユーザーがさらにコードを要求すると、最初のページで段階的に選択します。 必要以上に1行をチェックしています。
インデックス。 テーブルへのクエリとして、これらのテーブルのインデックスはメモリにキャッシュされ、メモリがなくなるまでそこに残り、その後、終了するように「求められます」。 したがって、ギガバイトの情報がある場合、それらのインデックスの数に応じて、インデックスはスペースの±40％を占有します。 たとえば、MySQL用に16GBのRAMが割り当てられた控えめなサーバーがあるとします。 10GB以上のインデックスを持つテーブルをクエリすると、MySQLに割り当てられたすべてのメモリが解放され、このインデックスで満たされ、以前にキャッシュされたすべてのインデックスが忘却の対象になります。 したがって、1つの重い要求を行うと、サーバーのパフォーマンス全体を停止できます。 どうする 多くのオプションがありますが、単純で、 BigTable 、 NoSQL、MySQL向けの NoSQLなど、1000万以上の大規模なテーブルを別々のカストディアンに格納することに集中しているとは言いません。
今のところすべてです。 上記に関するあなたの決定と助言を聞いてうれしいです。