Information Technology Tips

サブクエリを用いたクエリの最適化 巨大なテーブルをFull Table Scanして件数を集計する場合に、JOINの少ないサブクエリで先に集計して後から他のテーブルをJOINすると、クエリの実行を高速化できる場合があります。 特にJOINするテーブルが多い時には実行時間に大きな差が出ます。 もちろん、データやINDEXの張り方にも依存するで、クエリを書き換える前にEXPLAINで必ずRows_examinedなどが減ることを確認することは必須です。 この記事では、huge_rows_tableに大量の行があり、他のテーブルの行数はそれほどない場合を想定したクエリ例を示します。 想定するテーブル CREATE TABLE `huge_rows_table` ( `id` bigint(20) NOT NULL AUTO_INCREMENT, `group_id_1` bigint(20) NOT NULL, `group_id_2` bigint(20) NOT NULL, `check_flg` tinyint(4) NOT NULL DEFAULT '0', PRIMARY KEY (`id`), KEY `IDX_group_id_1` (`group_id_1`), KEY `IDX_group_id_2` (`group_id_2`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 ; CREATE TABLE `group_table_1` ( `id` bigint(20) NOT NULL, `name` varchar(500) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 ; CREATE TABLE `group_table_2` ( `id` bigint(20) NOT NULL, `name` varchar(500) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 ; クエリ # 元クエリ SELECT gt1.name AS g

MySQLのSQL_CALC_FOUND_ROWSを使用する際の無駄なメモリ消費に注意 ページング機能の実装などのために、ヒットした全件数を取得する必要のある場合があるかと思います。 その場合、下記のようにSQL_CALC_FOUND_ROWSを使うと、検索結果に加えて、そのクエリの全ヒット件数が取得できます。 SELECT SQL_CALC_FOUND_ROWS table.column1, table.column2 ...(途中省略)... FROM table WHERE (条件) しかし、SQL_CALC_FOUND_ROWSを使うと、「絞り込んだ結果にヒットする全べての行の結果のセットを作成する」という大きな欠点があります。 これは、LIMIT, OFFSETを指定していても実行されてしまうので、 SELECTで指定するカラム数やデータ量が多い SELECTの結果返ってくる行数が多い 場合、無駄に領域(≒メモリ)を消費してしまうので注意が必要です。 例えば、100万行検索対象としてヒットするクエリの場合、仮にLIMIT 20と指定して最初の20行を取得するようにクエリを書いても、その100万行分の結果セットが作成されてしまうということになります。 対応策 根本的な対応策としては、SQL_CALC_FOUND_ROWSを使わない(結果行数の取得はCOUNTを用いた別クエリで取得する、結果行数をあらかじめサマリーテーブルに保持しておく)ことですが、 SQL_CALC_FOUND_ROWSをどうしても使う必要がある場合は、 SELECT SQL_CALC_FOUND_ROWS primary_id のように最低限のカラムを指定して結果行セットを取得 (LIMIT OFFSET指定前提) 取得したprimary_idを使って必要なデータを取得 して、SQL_CALC_FOUND_ROWSで使用する領域をできるだけ減らすことで、対応するしかないと思います。 世の中ではLIMIT, OFFSETは使わない方がよいとよく書かれていますが、 SQL_CALC_FOUND_ROWSは、書いてしまえばどんなときも検索にヒットする全結果行セットを作成するので、同じくらい使用する際には注意が必要です。

SELECTの結果をUNIONして ORDER BY する際には下記の点に注意する必要があります。 無駄なメモリ消費 ソートにINDEXが利かない (≒CPU負荷増大) 対応策 可能であればPush-down Condition (各サブクエリ内でORDER BY, LIMIT, OFFSETを適用してからUNION, ORDER BYを実行する)を利用することで、 パフォーマンスを改善できる場合があります。 下記に例を示します。 もともとのクエリ SELECT tmp.* FROM ( SELECT tableA.column1, tableA.column2 FROM tableA WHERE (条件) UNION ALL SELECT tableB.column1, tableB.column2 FROM tableB WHERE (条件) ) AS tmp ORDER BY tmp.column1, tmp.column2 LIMIT 100, 20 Push-down Conditionを用いて書き直したクエリ SELECT tmp.* FROM ( SELECT tableA.column1, tableA.column2 FROM tableA WHERE (条件) ORDER BY tableA.column1, tableA.column2 LIMIT 30 # <- 10 (offset) + 20 (limit) UNION ALL SELECT tableB.column1, tableB.column2 FROM tableB WHERE (条件) ORDER BY tableB.column1, tableB.column2 LIMIT 30 # <- 10 (offset) + 20 (limit) ) AS tmp ORDER BY tmp.column1, tmp.column2 LIMIT 10, 20 ただしこのPush-down Conditionの手法も下記の場合は、効果が半減しますので注意が必要です。 OFFSETの値が大きい場合は、結局全結果セットUNIONと変わらない サブクエリ内のソートで、INDEXが効かない場合

今回は、筆者が気をつけている、MySQLのクエリチューニングの基本的な考え方について、紹介しようと思います。 基本的な指針としては、下記を守ることが重要です。 MySQLがクエリの実行で使用する領域(≒メモリ使用量)を減らす。 クエリ対象の行を減らす (INDEX, サマリーテーブルを駆使)。 上記の基本的な指針に基づいて、クエリを組み立てる際は以下の点を考慮するとよいと思います。 SELECTで指定する行は少なくする。 JOINするテーブルの数は減らす。 TEXTや大きいサイズのVARACHARでのORDER BYは極力避ける。SUBSTRなどを使ってカラムの一部データのみを使うことも考慮。 EXPLAINを実行した際に Extra: Using where; Using temporary; Using filesort と表示される場合は要チェック。 確認部分: 巨大なTemporaryテーブルが作成されないように注意。 対策: 「ソート部分でINDEXを使えないか」「サマリーテーブルを使って余計なJOINを減らしソート対象の行数を減らせないか」を検討。 INDEXを効かせる。 テーブル設計段階でどのカラムにINDEXを張るかをしっかり検討 (当たり前ですが。) 特にソートの必要がある場合は、できるだけINDEXやPRIMARY KEYを利用できるようにクエリやテーブルを設計。 基本MySQLのクエリオプティマイザに従うべきですが、あえて特定のINDEXを効かせる/効かせない場合は FORCE INDEX STRAIGHT_JOIN の利用も検討。ただし、クエリの実行プランはデータによっても変わるので、基本、FORCE INDEXやSTRAIGHT_JOINは使うべきではないと筆者は考えています。 想定される状況が限定的 解決しようとしているクエリの問題が明確 性能改善度合いが大きい ときのみ、FORCE INDEXやSTRAIGHT_JOINは使用すべきかと思います。また強制したクエリの実行プランが最悪の実行プランになった場合も想定することをお勧めします。 複雑なネストクエリは避ける。 確認部分: Temporaryテーブル(INDEXが効かない！)が作成される可能性が高い上、メモリも大量に

SQL_CALC_FOUND_ROWS Performance Issue If you are working on MySQL (<=5.5) for a long time, you have an experience with performance issue on SQL_CALC_FOUND_ROWS with LIMIT OFFSET. The core cause is MySQL generates full result set as a temporary table even if user only requested first 20 rows in the query. This kind of situation often happens when you implement paging functionality. Please see following example. SELECT SQL_CALC_FOUND_ROWS tbl_a.a_id, tbl_b.*, tbl_c.*, FROM tbale_a AS tbl_a INNER JOIN table_b AS tbl_b ON tbl_b.a_id = tbl_a.a_id INNER JOIN table_c AS tbl_c ON tbl_c.b_id = tbl_b.b_id LEFT JOIN table_d AS tbl_d ON tbl_d.a_id = tbl_a.a_id WHERE tbl_b.field1 = "ABC" LIMIT 100, 20; In this example, a temporary table which has full result set is generated. If you configure SQL_CACHE, the second time query might be quite faster. However it is not a good choice because the query performance depends on status of tables, and not stable. Assume if tbl_b is fr

In this post, I will explain some performance tuning tips for MySQL query. 1) Don't use wildcard (e.g. *) in SELECT (=Pick only required column on SELECT). 2) Comare performance between with and without 'JOIN'. Sometimes JOIN helps MySQL engine will reduce examined number of rows. But in my exeprience, most of the case, removing LEFT JOIN makes performance improvement. 3) Comare performance between with and without 'ORDER BY'. 4) Comare performance between with and without 'SQL_CALC_FOUND_ROWS'. 5) Check INDEX is properly used for JOIN, GROUP BY, ORDER BY. 6) Compare performance between following 2 strategy. a) pick up all required columns (sometimes with ORDER BY, DISTINCT, LIMIT etc.) by single query b) pick only required id column, and then pull all required columns using JOIN or execute 2nd query based on id columns picked up from the 1st query. Okay the last 6) might need example. 6) says " comapre performance following a)