SQL性能チューニング
このドキュメントでは、SQL ステートメントが遅くなる一般的な理由と、SQL パフォーマンスをチューニングする手法について説明します。
あなたが始める前に
tiup demoインポート使用してデータを準備できます。
tiup demo bookshop prepare --host 127.0.0.1 --port 4000 --books 1000000
または、事前に準備されたサンプル データをインポートする場合はTiDB Cloudのインポート機能を使用する 。
問題: テーブル全体のスキャン
SQL クエリが遅くなる最も一般的な理由は、 SELECTステートメントが完全なテーブル スキャンを実行するか、間違ったインデックスを使用することです。
TiDB が主キーではない列またはセカンダリ インデックス内の列に基づいて大規模なテーブルから少数の行を取得する場合、通常はパフォーマンスが低下します。
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_5のestRows値は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 実行プラン参照。