Clever Geek Handbook

任意のMySQLレコードの選択の高速化

最近、聴衆はテーブルからのランダムサンプリングの問題で復活しました。 最適化ソリューションはたくさんありますが、今はおそらく新しいものは何も表示していませんが、主な最適化方法-クエリとインデックス作成の簡素化-を思い出してください。 フリーランサーについての序文なしで、要点を説明します;）



テーブルを作成します。
CREATE TABLE `a` (

`id` int(8) unsigned NOT NULL AUTO_INCREMENT,

`md5` char(32) NOT NULL

PRIMARY KEY (`id`)

)

INSERT INTO `a` (`id`) VALUES (null),(null),(null),(null)... 163712 ;)

UPDATE `a` SET md5 = MD5(`id`);





私の時代遅れのコンピューター上のそのようなテーブルは、有効性をテストするのに十分です。

以下は、ORDER BY RANDの簡単な選択です。
SELECT * FROM `a` ORDER BY RAND() LIMIT 10; -> (10 rows, Query took 0.3345 sec)

SELECT * FROM `a` ORDER BY RAND() LIMIT 10; -> (10 rows, Query took 0.2538 sec)

SELECT * FROM `a` ORDER BY RAND() LIMIT 10; -> (10 rows, Query took 0.2299 sec)





テーブル全体をフルスキャンしないように、インデックス付きフィールドを作成します。
ALTER TABLE `a` ADD `f` SMALLINT(3) UNSIGNED NOT NULL, ADD INDEX (`f`);

UPDATE `a` SET `f` = RAND()*1000;



数値1000は、主要な「加速」要因です。 彼らに、私は通常のORDER BY RANDが1000回行くテーブルを減らします。 163712行のテーブルでは、fあたり約164行を取得する必要があります。 私たちはチェックします：
SELECT COUNT(1) FROM `a` WHERE `f` = 123; -> 169





ランダムはランダムであり、分布さえも良いでしょうが、素晴らしいです（ご存知のように、MD5の最初の文字（ `id`）を使用してINTに変換できます。これ以上均一ではありません）。 だから、今私は1つのfと200行と100に出くわしました。このインジケーターが時間の経過とともに無効になる場合は、いつでも係数を増やして、たとえばインデックスごとに25〜75行を取得できます。 主なことは、少なくともランダムに取り出す必要のある行があることです。 列fは、定期的に、またはテーブルへの1000回の呼び出し後に再生成できます。 挿入時に、新しい行は値f = 0を取得しますが、これはサンプルの品質に大きな影響を与えたり、挿入のランダムな値を設定したりしません。



インデックスを作成したものを使用して、10行のテストサンプルを作成します。
SELECT * FROM `a` WHERE `f` = 231 ORDER BY RAND() LIMIT 10; -> (10 rows, Query took 0.0801 sec)

SELECT * FROM `a` WHERE `f` = 231 ORDER BY RAND() LIMIT 10; -> (10 rows, Query took 0.0017 sec)



ええ、2回目はmysqlキャッシュからの再ソートされた選択でした。 結果の繰り返しがそれほど怖くない場合は、このようなより速い結果が機能しますが、リクエストごとに数値fを変更することをお勧めします。



fを変更してテストを繰り返します。
SELECT * FROM `a` WHERE `f` = 396 ORDER BY RAND() LIMIT 10; -> (10 rows, Query took 0.0147 sec)

SELECT * FROM `a` WHERE `f` = 753 ORDER BY RAND() LIMIT 10; -> (10 rows, Query took 0.0020 sec)

SELECT * FROM `a` WHERE `f` = 189 ORDER BY RAND() LIMIT 10; -> (10 rows, Query took 0.0019 sec)

SELECT * FROM `a` WHERE `f` = 945 ORDER BY RAND() LIMIT 10; -> (10 rows, Query took 0.0235 sec)





一般に、サンプリングのパフォーマンスは120倍に向上しましたが、これには問題はありません。 このソリューションには多くの設備があります： 160行の1つのギャップでは不十分な場合、必要な数のギャップを含めることができます。
SELECT * FROM `a` WHERE `f` IN (100,500) ORDER BY RAND() LIMIT 10;





もっと人生の例



この例では、隣の投稿からトップのコメントを取得してみましょう。これはこの方法で解決されます。 フィード番号を含むフィードフィールドを追加して、RSSフィードのテーブルをエミュレートします。
ALTER TABLE `a` ADD `feed` TINYINT(1) UNSIGNED NOT NULL, ADD INDEX (`feed`);

UPDATE `a` SET feed = RAND()*9;



そして今、実際には、耳のフェイント：
(SELECT * FROM `a` WHERE `f` = 283 AND `feed` = 0 ORDER BY RAND() LIMIT 10)

UNION (SELECT * FROM `a` WHERE `f` = 283 AND `feed` = 1 ORDER BY RAND() LIMIT 10)

UNION (SELECT * FROM `a` WHERE `f` = 283 AND `feed` = 2 ORDER BY RAND() LIMIT 10)

UNION (SELECT * FROM `a` WHERE `f` = 283 AND `feed` = 3 ORDER BY RAND() LIMIT 10)

UNION (SELECT * FROM `a` WHERE `f` = 283 AND `feed` = 4 ORDER BY RAND() LIMIT 10)

UNION (SELECT * FROM `a` WHERE `f` = 283 AND `feed` = 5 ORDER BY RAND() LIMIT 10)

UNION (SELECT * FROM `a` WHERE `f` = 283 AND `feed` = 6 ORDER BY RAND() LIMIT 10)

UNION (SELECT * FROM `a` WHERE `f` = 283 AND `feed` = 7 ORDER BY RAND() LIMIT 10)

UNION (SELECT * FROM `a` WHERE `f` = 283 AND `feed` = 8 ORDER BY RAND() LIMIT 10)

UNION (SELECT * FROM `a` WHERE `f` = 283 AND `feed` = 9 ORDER BY RAND() LIMIT 10)

ORDER BY feed; -> (97 rows, Query took 0.7973 sec)

f -> (99 rows, Query took 0.0093 sec)

f -> (98 rows, Query took 0.0197 sec)



ここでは、忠実度のために10行以上を選択することをお勧めします;）そして、PHPで余分な部分をフィルタリングします。



PHPでは、MySQLサーバーに2つのリクエストを送信しないように、番号fを指定することをお勧めします。 ただし、これは重要ではありません。 これも非常に迅速に機能します。
SET @rnd = RAND(); SELECT * FROM `a` WHERE `f` = @rnd ORDER BY RAND() LIMIT 10;





ご覧のとおり、合併症だけで最適化を達成できるわけではありません（この記事では速度を最適化しました）。 質問はあなたが考えることであり、私は将来の記事で説明することです。 任意のサンプルの品質をどのように最適化することができますか？ ;）



よろしく

マヤム


More articles:


All Articles