重要
このページは英語版のページを機械翻訳しています。原文はこちらからご覧ください。

SQL性能チューニング

このドキュメントでは、SQLステートメントが遅い一般的な理由とSQLパフォーマンスを調整するための手法を紹介します。

あなたが始める前に

tiup demoのインポートを使用してデータを準備できます。

tiup demo bookshop prepare --host 127.0.0.1 --port 4000 --books 1000000

またはTiDB Cloudのインポート機能を使用するを使用して、事前に準備されたサンプルデータをインポートします。

問題:全表スキャン

SQLクエリが遅い最も一般的な理由は、 SELECTステートメントが全表スキャンを実行するか、誤ったインデックスを使用することです。

TiDBが、主キーまたは2次インデックスにない列に基づいて、大きなテーブルから少数の行を取得する場合、通常、パフォーマンスは低下します。

SELECT * FROM books WHERE title = 'Marian Yost';
+------------+-------------+-----------------------+---------------------+-------+--------+
| id         | title       | type                  | published_at        | stock | price  |
+------------+-------------+-----------------------+---------------------+-------+--------+
| 65670536   | Marian Yost | Arts                  | 1950-04-09 06:28:58 | 542   | 435.01 |
| 1164070689 | Marian Yost | Education & Reference | 1916-05-27 12:15:35 | 216   | 328.18 |
| 1414277591 | Marian Yost | Arts                  | 1932-06-15 09:18:14 | 303   | 496.52 |
| 2305318593 | Marian Yost | Arts                  | 2000-08-15 19:40:58 | 398   | 402.90 |
| 2638226326 | Marian Yost | Sports                | 1952-04-02 12:40:37 | 191   | 174.64 |
+------------+-------------+-----------------------+---------------------+-------+--------+
5 rows in set
Time: 0.582s

このクエリが遅い理由を理解するには、 EXPLAINを使用して実行プランを確認します。

EXPLAIN SELECT * FROM books WHERE title = 'Marian Yost';
+---------------------+------------+-----------+---------------+-----------------------------------------+
| id                  | estRows    | task      | access object | operator info                           |
+---------------------+------------+-----------+---------------+-----------------------------------------+
| TableReader_7       | 1.27       | root      |               | data:Selection_6                        |
| └─Selection_6       | 1.27       | cop[tikv] |               | eq(bookshop.books.title, "Marian Yost") |
|   └─TableFullScan_5 | 1000000.00 | cop[tikv] | table:books   | keep order:false                        |
+---------------------+------------+-----------+---------------+-----------------------------------------+

実行プランのTableFullScan_5からわかるように、TiDBはbooksのテーブルに対して全表スキャンを実行し、 titleが各行の条件を満たすかどうかを確認します。 TableFullScan_5estRowsの値は1000000.00です。これは、オプティマイザーがこの全表スキャンで1000000.00行のデータが必要であると推定していることを意味します。

EXPLAINの使用法の詳細については、 EXPLAINウォークスルーを参照してください。

解決策:セカンダリインデックスを使用する

上記のこのクエリを高速化するには、 books.title列にセカンダリインデックスを追加します。

CREATE INDEX title_idx ON books (title);

クエリの実行ははるかに高速です。

SELECT * FROM books WHERE title = 'Marian Yost';
+------------+-------------+-----------------------+---------------------+-------+--------+
| id         | title       | type                  | published_at        | stock | price  |
+------------+-------------+-----------------------+---------------------+-------+--------+
| 1164070689 | Marian Yost | Education & Reference | 1916-05-27 12:15:35 | 216   | 328.18 |
| 1414277591 | Marian Yost | Arts                  | 1932-06-15 09:18:14 | 303   | 496.52 |
| 2305318593 | Marian Yost | Arts                  | 2000-08-15 19:40:58 | 398   | 402.90 |
| 2638226326 | Marian Yost | Sports                | 1952-04-02 12:40:37 | 191   | 174.64 |
| 65670536   | Marian Yost | Arts                  | 1950-04-09 06:28:58 | 542   | 435.01 |
+------------+-------------+-----------------------+---------------------+-------+--------+
5 rows in set
Time: 0.007s

パフォーマンスが向上する理由を理解するには、 EXPLAINを使用して新しい実行プランを確認します。

EXPLAIN SELECT * FROM books WHERE title = 'Marian Yost';
+---------------------------+---------+-----------+-------------------------------------+-------------------------------------------------------+
| id                        | estRows | task      | access object                       | operator info                                         |
+---------------------------+---------+-----------+-------------------------------------+-------------------------------------------------------+
| IndexLookUp_10            | 1.27    | root      |                                     |                                                       |
| ├─IndexRangeScan_8(Build) | 1.27    | cop[tikv] | table:books, index:title_idx(title) | range:["Marian Yost","Marian Yost"], keep order:false |
| └─TableRowIDScan_9(Probe) | 1.27    | cop[tikv] | table:books                         | keep order:false                                      |
+---------------------------+---------+-----------+-------------------------------------+-------------------------------------------------------+

実行プランのIndexLookup_10からわかるように、TiDBはtitle_idxインデックスでデータをクエリします。そのestRowsの値は1.27です。これは、オプティマイザーが1.27行のみがスキャンされると推定することを意味します。スキャンされる推定行は、全表スキャンの1000000.00行のデータよりもはるかに少なくなります。

IndexLookup_10実行プランでは、最初にIndexRangeScan_8演算子を使用して、 title_idxインデックスを介して条件を満たすインデックスデータを読み取り、次にTableLookup_9演算子を使用して、インデックスデータに格納されている行IDに従って対応する行をクエリします。

TiDB実行プランの詳細については、 TiDBクエリ実行プランの概要を参照してください。

解決策:カバーインデックスを使用する

インデックスが、SQLステートメントによってクエリされたすべての列を含むカバーインデックスである場合、クエリにはインデックスデータをスキャンするだけで十分です。

たとえば、次のクエリでは、 titleに基づいて対応するpriceをクエリするだけで済みます。

SELECT title, price FROM books WHERE title = 'Marian Yost';
+-------------+--------+
| title       | price  |
+-------------+--------+
| Marian Yost | 435.01 |
| Marian Yost | 328.18 |
| Marian Yost | 496.52 |
| Marian Yost | 402.90 |
| Marian Yost | 174.64 |
+-------------+--------+
5 rows in set
Time: 0.007s

title_idxインデックスにはtitle列のデータしか含まれていないため、TiDBは最初にインデックスデータをスキャンしてから、テーブルからprice列をクエリする必要があります。

EXPLAIN SELECT title, price FROM books WHERE title = 'Marian Yost';
+---------------------------+---------+-----------+-------------------------------------+-------------------------------------------------------+
| id                        | estRows | task      | access object                       | operator info                                         |
+---------------------------+---------+-----------+-------------------------------------+-------------------------------------------------------+
| IndexLookUp_10            | 1.27    | root      |                                     |                                                       |
| ├─IndexRangeScan_8(Build) | 1.27    | cop[tikv] | table:books, index:title_idx(title) | range:["Marian Yost","Marian Yost"], keep order:false |
| └─TableRowIDScan_9(Probe) | 1.27    | cop[tikv] | table:books                         | keep order:false                                      |
+---------------------------+---------+-----------+-------------------------------------+-------------------------------------------------------+

パフォーマンスを最適化するには、 title_idxのインデックスを削除し、新しいカバーインデックスtitle_price_idxを作成します。

ALTER TABLE books DROP INDEX title_idx;
CREATE INDEX title_price_idx ON books (title, price);

priceのデータはtitle_price_idxのインデックスに格納されているため、次のクエリではインデックスデータをスキャンするだけで済みます。

EXPLAIN SELECT title, price FROM books WHERE title = 'Marian Yost';
--------------------+---------+-----------+--------------------------------------------------+-------------------------------------------------------+
| id                 | estRows | task      | access object                                    | operator info                                         |
+--------------------+---------+-----------+--------------------------------------------------+-------------------------------------------------------+
| IndexReader_6      | 1.27    | root      |                                                  | index:IndexRangeScan_5                                |
| └─IndexRangeScan_5 | 1.27    | cop[tikv] | table:books, index:title_price_idx(title, price) | range:["Marian Yost","Marian Yost"], keep order:false |
+--------------------+---------+-----------+--------------------------------------------------+-------------------------------------------------------+

これで、このクエリはより高速に実行されます。

SELECT title, price FROM books WHERE title = 'Marian Yost';
+-------------+--------+
| title       | price  |
+-------------+--------+
| Marian Yost | 174.64 |
| Marian Yost | 328.18 |
| Marian Yost | 402.90 |
| Marian Yost | 435.01 |
| Marian Yost | 496.52 |
+-------------+--------+
5 rows in set
Time: 0.004s

booksのテーブルは後の例で使用されるため、 title_price_idxのインデックスを削除します。

ALTER TABLE books DROP INDEX title_price_idx;

解決策:プライマリインデックスを使用する

クエリが主キーを使用してデータをフィルタリングする場合、クエリは高速に実行されます。たとえば、 booksテーブルの主キーはid列であるため、 id列を使用してデータをクエリできます。

SELECT * FROM books WHERE id = 896;
+-----+----------------+----------------------+---------------------+-------+--------+
| id  | title          | type                 | published_at        | stock | price  |
+-----+----------------+----------------------+---------------------+-------+--------+
| 896 | Kathryne Doyle | Science & Technology | 1969-03-18 01:34:15 | 468   | 281.32 |
+-----+----------------+----------------------+---------------------+-------+--------+
1 row in set
Time: 0.004s

EXPLAINを使用して、実行プランを確認します。

EXPLAIN SELECT * FROM books WHERE id = 896;
+-------------+---------+------+---------------+---------------+
| id          | estRows | task | access object | operator info |
+-------------+---------+------+---------------+---------------+
| Point_Get_1 | 1.00    | root | table:books   | handle:896    |
+-------------+---------+------+---------------+---------------+

Point_Getは非常に高速な実行プランです。

適切な結合タイプを使用する

JOIN実行プランを参照してください。

も参照してください