# TiDB Cloud Serverless 入門 ## 概要 このワークショップでは、TiDB Cloud Serverless の基本操作と先進的な機能を学びます。具体的には、クラスタの作成と MySQL Client からのクエリ発行、Python スクリプトを通じたデータベースアクセス、そして TiFlash による複雑なクエリの高速化技術を取り上げます。初心者でも、TiDB Cloud Serverless の基本から高度な機能まで一度に習得できる実践的な内容になっています。 ### 所要時間 + この演習は約 90 分程度かかります。 ### タスク + TiDB Cloud Serverless クラスタに接続し、MySQL クライアントからクエリを発行します。 + Python スクリプトを作成し、TiDB にアクセスします。 + 実行時間がかかる複雑なクエリをカラムストアオプションの TiFlash を活用し高速化します。 > [!TIP] > 演習環境を起動するには、**開始**をクリックしてください。 > 環境の準備が整ったら、下部のツールバーで **次のモジュール** を選択して **モジュール 1** に進みます。 > そして、ステップバイステップの演習ガイドに従って、ハンズオン環境に接続し、タスクを開始してください。 ## モジュール 1: TiDB Cloud Serverless への接続 ### 手順 1. リモート Linux にログインする + ここをクリック - [ターミナル 1](ssh://monitor1-ssh#1) して、ハンズオンラボ用に割り当てられた仮想マシンに接続された新しいターミナルを開きます。 ### 手順 2. TiDB Cloud Serverless クラスタへの接続を確認する + 以下の操作はリモート仮想マシンで実行されます。次の3つのパターンでTiDB Cloud Serverlessに接続します。（MySQLクライアントを終了したい場合は、`EXIT`を使用します。） + A. 接続（SSLあり）：次のコマンドを使用して接続します。 ```bash mysql \ --comments -u '${LAB:SERVERLESS_CLUSTER_USERNAME}' -p'${LAB:SERVERLESS_CLUSTER_PASSWORD}' \ -h ${LAB:SERVERLESS_CLUSTER_HOST} -P ${LAB:SERVERLESS_CLUSTER_PORT} \ --ssl-mode=VERIFY_IDENTITY --ssl-ca=/etc/pki/tls/certs/ca-bundle.crt ``` ```output mysql> ``` ```sql EXIT; ``` + B. 接続（SSLあり）：`--ssl-mode`オプションを指定しない場合、デフォルトモードは`PREFERRED`で、MySQLクライアントは最初にSSL接続を確立しようとし、SSLが失敗した場合は暗号化されていない接続にフォールバックします。 ```bash mysql \ --comments -u '${LAB:SERVERLESS_CLUSTER_USERNAME}' -p'${LAB:SERVERLESS_CLUSTER_PASSWORD}' \ -h ${LAB:SERVERLESS_CLUSTER_HOST} -P ${LAB:SERVERLESS_CLUSTER_PORT} ``` ```output mysql> ``` ```sql EXIT; ``` + C. 接続（SSLなし）：`--ssl-mode=DISABLED`に変更します。接続エラーが発生します。 ```bash mysql \ --comments -u '${LAB:SERVERLESS_CLUSTER_USERNAME}' -p'${LAB:SERVERLESS_CLUSTER_PASSWORD}' \ -h ${LAB:SERVERLESS_CLUSTER_HOST} -P ${LAB:SERVERLESS_CLUSTER_PORT} \ --ssl-mode=DISABLED ``` ```output mysql: [Warning] Using a password on the command line interface can be insecure. ERROR 1105 (HY000): Connections using insecure transport are prohibited. See https://docs.tidb.io/tidbcloud/secure-connections-to-serverless-tier-clusters ``` ### 手順 3. 基本的なデータベース操作 + TiDBに接続します。 ```bash mysql \ --comments -u '${LAB:SERVERLESS_CLUSTER_USERNAME}' -p'${LAB:SERVERLESS_CLUSTER_PASSWORD}' \ -h ${LAB:SERVERLESS_CLUSTER_HOST} -P ${LAB:SERVERLESS_CLUSTER_PORT} \ --ssl-mode=VERIFY_IDENTITY --ssl-ca=/etc/pki/tls/certs/ca-bundle.crt ``` ```output mysql> ``` + 新しいデータベースを作成します。 ```sql DROP DATABASE IF EXISTS ${LAB:SERVERLESS_CLUSTER_DATABASE_NAME}; CREATE DATABASE ${LAB:SERVERLESS_CLUSTER_DATABASE_NAME}; SHOW DATABASES\G ``` ```output *************************** 1. row *************************** Database: INFORMATION_SCHEMA *************************** 2. row *************************** Database: PERFORMANCE_SCHEMA *************************** 3. row *************************** Database: lab_demo_201_2370028 *************************** 4. row *************************** Database: mysql *************************** 5. row *************************** Database: test 5 rows in set (0.02 sec) ``` + 新しいテーブルを作成します。 ```sql USE ${LAB:SERVERLESS_CLUSTER_DATABASE_NAME}; CREATE TABLE color (id INT AUTO_INCREMENT, name TEXT, Point INT, PRIMARY KEY (id)); INSERT INTO color (name, Point) VALUES ('red', 1); INSERT INTO color (name, Point) VALUES ('orange', 1); INSERT INTO color (name, Point) VALUES ('yellow', 2); SELECT * FROM color; ``` ```output Database changed Query OK, 0 rows affected (0.14 sec) Query OK, 1 row affected (0.04 sec) Query OK, 1 row affected (0.01 sec) Query OK, 1 row affected (0.01 sec) +----+--------+-------+ | id | name | Point | +----+--------+-------+ | 1 | red | 1 | | 2 | orange | 1 | | 3 | yellow | 2 | +----+--------+-------+ 3 rows in set (0.01 sec) ``` ### 手順 4. MySQL互換性の確認 + TiDBはMySQLと100%互換性があるわけではありません。ここでは、ウィンドウ関数やサポートされていないストアドプロシージャなどのMySQL 8.0機能を確認します。次のURLから互換性を確認できます：[https://docs.pingcap.com/ja/tidb/stable/mysql-compatibility/](https://docs.pingcap.com/ja/tidb/stable/mysql-compatibility/)。 + デフォルトの文字セットと照合順序が異なることを確認します。 ```sql SHOW CREATE TABLE color\G ``` ```output *************************** 1. row *************************** Table: color Create Table: CREATE TABLE `color` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` text DEFAULT NULL, `Point` int(11) DEFAULT NULL, PRIMARY KEY (`id`) /*T![clustered_index] CLUSTERED */ ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin AUTO_INCREMENT=30001 1 row in set (0.00 sec) ``` + ウィンドウ関数の例。 ```sql SELECT id, name, Point, RANK() OVER w as 'rank' FROM color WINDOW w AS (ORDER BY Point); ``` ```output +----+--------+-------+------+ | id | name | Point | rank | +----+--------+-------+------+ | 1 | red | 1 | 1 | | 2 | orange | 1 | 1 | | 3 | yellow | 2 | 3 | +----+--------+-------+------+ 3 rows in set (0.01 sec) ``` + ストアドプロシージャ。これはサポートされていないため失敗します。 ```sql delimiter // CREATE PROCEDURE sample() BEGIN SELECT * FROM color; END // ``` ```output ERROR 8108 (HY000): Unsupported type *ast.ProcedureInfo ``` ```sql EXIT // ``` ```output Bye ``` ## モジュール 2: PythonからTiDB Cloud Serverlessへのアクセス ### 手順 1. サンプルデータのインポート + TiDBに接続します。 ```bash mysql \ --comments -u '${LAB:SERVERLESS_CLUSTER_USERNAME}' -p'${LAB:SERVERLESS_CLUSTER_PASSWORD}' \ -h ${LAB:SERVERLESS_CLUSTER_HOST} -P ${LAB:SERVERLESS_CLUSTER_PORT} \ --ssl-mode=VERIFY_IDENTITY --ssl-ca=/etc/pki/tls/certs/ca-bundle.crt ``` ```output mysql> ``` + サンプルデータベースを作成し、サンプル**S&P 500**データをインポートします。 > [!TIP] > この手順でインポートスクリプトから発生する`Access Denied`メッセージは安全に無視できます。`LOCK TABLE`はTiDBのような分散SQLデータベースでは必要ありません。 ```sql CREATE DATABASE ${LAB:SERVERLESS_CLUSTER_DATABASE_NAME}; USE ${LAB:SERVERLESS_CLUSTER_DATABASE_NAME}; SOURCE sp500insight.sql ``` ```output ... Query OK, 7947 rows affected (0.34 sec) Records: 7947 Duplicates: 0 Warnings: 0 Query OK, 7882 rows affected (0.34 sec) Records: 7882 Duplicates: 0 Warnings: 0 ... Query OK, 0 rows affected, 1 warning (0.01 sec) ``` ### 手順 2. サンプルデータへのアクセス ```sql USE ${LAB:SERVERLESS_CLUSTER_DATABASE_NAME}; SELECT sector, industry, COUNT(*) AS companies FROM companies c WHERE c.stock_symbol IN (SELECT stock_symbol FROM index_compositions WHERE index_symbol = 'SP500') GROUP BY sector, industry ORDER BY sector, companies DESC; ``` ```output Database changed +------------------------+------------------------------------------+-----------+ | sector | industry | companies | +------------------------+------------------------------------------+-----------+ | Basic Materials | Specialty Chemicals | 10 | | Basic Materials | Agricultural Inputs | 4 | | Basic Materials | Building Materials | 2 | | Basic Materials | Chemicals | 2 | | Basic Materials | Steel | 2 | | Basic Materials | Gold | 1 | | Basic Materials | Copper | 1 | | Communication Services | Entertainment | 7 | ... | Utilities | Utilities—Regulated Electric | 23 | | Utilities | Utilities—Diversified | 2 | | Utilities | Utilities—Regulated Gas | 2 | | Utilities | Utilities—Regulated Water | 1 | | Utilities | Utilities—Independent Power Producers | 1 | +------------------------+------------------------------------------+-----------+ 113 rows in set, 1 warning (0.19 sec) ``` ### 手順 3. 分析クエリを実行するスクリプトの作成 + OSプロンプトに戻ります。 ```sql EXIT; ``` + Pythonスクリプトを作成します。 ```bash cat > test01.py <<'EOF' import mysql.connector import datetime from prettytable import PrettyTable config = { 'user': '${LAB:SERVERLESS_CLUSTER_USERNAME}', 'password': '${LAB:SERVERLESS_CLUSTER_PASSWORD}', 'host': '${LAB:SERVERLESS_CLUSTER_HOST}', 'database': '${LAB:SERVERLESS_CLUSTER_DATABASE_NAME}', 'raise_on_warnings': False, 'port': ${LAB:SERVERLESS_CLUSTER_PORT}, } def execQuery(query, queryName): print(queryName) print("Query execution start time: ", datetime.datetime.now().strftime("%H:%M:%S.%f")) # クエリの実行 cursor.execute(query) print("Query execution end time: ", datetime.datetime.now().strftime("%H:%M:%S.%f")) # 最後に実行されたクエリからすべての行を取得 results = cursor.fetchall() # PrettyTableオブジェクトの作成 my_table = PrettyTable() # フィールド名（列名）をテーブルに追加 field_names = [i[0] for i in cursor.description] my_table.field_names = field_names # 行をテーブルに追加 for row in results: my_table.add_row(row) # テーブルの表示 print(my_table) print("==========================") try: # 接続の確立 conn = mysql.connector.connect(**config) # カーソルオブジェクトの作成 cursor = conn.cursor() # SQLクエリ queryName= "■■■クエリ1: 業種別時価総額■■■" query = """ SELECT `sector`, SUM(`market_cap`) AS `total_market_cap` FROM `companies` GROUP BY `sector`; """ execQuery(query, queryName) queryName= "■■■クエリ2: エネルギー業界の時価総額ランキング■■■" query = """ WITH index_companies AS ( SELECT c.stock_symbol, c.short_name, c.exchange_symbol, ic.weight, c.market_cap AS market_cap, c.revenue_growth AS revenue_growth FROM companies c JOIN index_compositions ic ON c.stock_symbol = ic.stock_symbol WHERE index_symbol = 'SP500' AND sector = 'Energy' ), companies_latest_price AS ( SELECT stock_symbol, DATE(last_day) AS last_change_day, last_close_price, last_2nd_close_price, last_close_price - last_2nd_close_price AS last_change, (last_close_price - last_2nd_close_price) / last_2nd_close_price AS last_change_percentage FROM ( SELECT LAST_VALUE(stock_symbol) OVER (PARTITION BY stock_symbol ORDER BY record_date) AS stock_symbol, LAST_VALUE(record_date) OVER (PARTITION BY stock_symbol ORDER BY record_date) AS last_day, NTH_VALUE(close, 1) OVER (PARTITION BY stock_symbol ORDER BY record_date DESC) AS last_close_price, NTH_VALUE(close, 2) OVER (PARTITION BY stock_symbol ORDER BY record_date DESC) AS last_2nd_close_price, ROW_NUMBER() OVER (PARTITION BY stock_symbol ORDER BY record_date DESC) AS row_num FROM stock_price_history sph WHERE record_date > DATE_SUB((SELECT MAX(record_date) FROM stock_price_history), INTERVAL 2 DAY) AND sph.stock_symbol IN (SELECT stock_symbol FROM index_companies ic) ) sub WHERE row_num = 2 ) SELECT ic.stock_symbol, ic.exchange_symbol, ic.short_name, ic.weight, ic.market_cap, ic.revenue_growth, clp.last_change, clp.last_change_day, clp.last_close_price, clp.last_2nd_close_price, clp.last_change_percentage FROM index_companies ic LEFT JOIN companies_latest_price clp ON ic.stock_symbol = clp.stock_symbol ORDER BY ic.weight DESC; """ execQuery(query, queryName) except mysql.connector.Error as err: print(f"Error: {err}") finally: # 接続のクローズ if conn.is_connected(): cursor.close() conn.close() print("MySQL connection is closed") EOF ls -l test01.py ``` ```output -rw-r--r--. 1 rocky rocky 3636 Apr 28 02:22 test01.py ``` ### 手順 4. スクリプトを実行して分析クエリを実行する + Pythonスクリプトを実行して、サンプルデータから2つのレポートを取得します。経過時間に注目してください。 ```bash python test01.py ``` ```output ■■■クエリ1: 業種別時価総額■■■ Query execution start time: 02:22:15.881417 Query execution end time: 02:22:15.902559 +------------------------+------------------+ | sector | total_market_cap | +------------------------+------------------+ | Real Estate | 927820086272 | | Utilities | 1023797270016 | | Healthcare | 5122903213056 | | Technology | 7906855563776 | | Financial Services | 4661122020864 | | Industrials | 3199253085184 | | Consumer Cyclical | 3238477340160 | | Consumer Defensive | 2872367655424 | | Energy | 1788549832704 | | Basic Materials | 889871172608 | | Communication Services | 3669841019392 | +------------------------+------------------+ ========================== ■■■クエリ2: エネルギー業界の時価総額ランキング■■■ Query execution start time: 02:22:15.904362 Query execution end time: 02:22:18.595003 +--------------+-----------------+---------------------------------+------------------------+--------------+----------------+---------------------+-----------------+----------------------+----------------------+-------------------------+ | stock_symbol | exchange_symbol | short_name | weight | market_cap | revenue_growth | last_change | last_change_day | last_close_price | last_2nd_close_price | last_change_percentage | +--------------+-----------------+---------------------------------+------------------------+--------------+----------------+---------------------+-----------------+----------------------+----------------------+-------------------------+ | XOM | NYQ | Exxon Mobil Corporation | 0.013441937585551282 | 474511933440 | 0.689 | 1.2800064086914100 | 2023-01-10 | 111.3700027465820300 | 110.0899963378906200 | 0.01162690935843785832 | | CVX | NYQ | Chevron Corporation | 0.009567226003564158 | 337731289088 | 0.81 | -0.8399963378906000 | 2023-01-10 | 175.1999969482422000 | 176.0399932861328000 | -0.00477162218772232275 | | COP | NYQ | ConocoPhillips | 0.004417814517419704 | 155952644096 | 1.237 | 0.1599960327148400 | 2023-01-10 | 118.2399978637695300 | 118.0800018310546900 | 0.00135497993084178221 | | SLB | NYQ | Schlumberger N.V. | 0.0022377960040345308 | 78996119552 | 0.279 | None | None | None | None | None | | EOG | NYQ | EOG Resources, Inc. | 0.0021024604924476343 | 74218659840 | 0.924 | -0.7600021362304600 | 2023-01-10 | 126.8499984741211000 | 127.6100006103515600 | -0.00595566282105957140 | | MPC | NYQ | Marathon Petroleum Corporation | 0.002050747487098508 | 72393146368 | 0.818 | 1.0999984741211000 | 2023-01-10 | 117.5999984741211000 | 116.5000000000000000 | 0.00944204698816394850 | | OXY | NYQ | Occidental Petroleum Corporatio | 0.001695676548826279 | 59858837504 | 0.792 | None | None | None | None | None | | PXD | NYQ | Pioneer Natural Resources Compa | 0.0016017013932190817 | 56541433856 | 0.641 | None | None | None | None | None | | VLO | NYQ | Valero Energy Corporation | 0.0015472850935959574 | 54620491776 | 0.593 | None | None | None | None | None | | HES | NYQ | Hess Corporation | 0.0012744122628788822 | 44987846656 | 0.776 | 1.6300048828125000 | 2023-01-10 | 145.2500000000000000 | 143.6199951171875000 | 0.01134942861878312162 | | PSX | NYQ | Phillips 66 | 0.0012589621320069535 | 44442443776 | 0.799 | None | None | None | None | None | | KMI | NYQ | Kinder Morgan, Inc. | 0.0012062691273686955 | 42582335488 | 0.354 | 0.0400009155273440 | 2023-01-10 | 18.7800006866455080 | 18.7399997711181640 | 0.00213452059850037319 | | DVN | NYQ | Devon Energy Corporation | 0.0012053375130788024 | 42549448704 | 0.789 | -0.2800025939941400 | 2023-01-10 | 62.8499984741210940 | 63.1300010681152340 | -0.00443533326875800678 | | WMB | NYQ | Williams Companies, Inc. (The) | 0.0011162172177908748 | 39403425792 | 0.091 | None | None | None | None | None | | HAL | NYQ | Halliburton Company | 0.0010393744130051477 | 36690808832 | 0.388 | 0.0500030517578160 | 2023-01-10 | 40.9900016784668000 | 40.9399986267089840 | 0.00122137404580161222 | | OKE | NYQ | ONEOK, Inc. | 0.0008854096904464799 | 31255721984 | 0.769 | None | None | None | None | None | | BKR | NMS | Baker Hughes Company | 0.0008807471517968449 | 31091130368 | 0.054 | -0.0400009155273450 | 2023-01-10 | 30.8299999237060550 | 30.8700008392334000 | -0.00129578602008027511 | | FANG | NMS | Diamondback Energy, Inc. | 0.000730020047064916 | 25770334208 | 0.565 | 0.2900085449218600 | 2023-01-10 | 142.2400054931640600 | 141.9499969482422000 | 0.00204303311839874783 | | MRO | NYQ | Marathon Oil Corporation | 0.0005965698099807201 | 21059426304 | 0.731 | 0.0399990081787110 | 2023-01-10 | 27.0499992370605470 | 27.0100002288818360 | 0.00148089625471161593 | | CTRA | NYQ | Coterra Energy Inc. | 0.0005690298110137097 | 20087240704 | 5.204 | -0.3899993896484380 | 2023-01-10 | 24.6900005340576170 | 25.0799999237060550 | -0.01555021494556719473 | | TRGP | NYQ | Targa Resources, Inc. | 0.0004842024080652878 | 17092760576 | 0.773 | None | None | None | None | None | | APA | NMS | APA Corporation | 0.00039910236086756015 | 14088655872 | 0.374 | 0.5799980163574200 | 2023-01-10 | 43.8199996948242200 | 43.2400016784668000 | 0.01341345961709929139 | | EQT | NYQ | EQT Corporation | 0.0003576031445812937 | 12623697920 | 1.092 | -0.6399993896484380 | 2023-01-10 | 33.4000015258789060 | 34.0400009155273440 | -0.01880139166966068805 | +--------------+-----------------+---------------------------------+------------------------+--------------+----------------+---------------------+-----------------+----------------------+----------------------+-------------------------+ ========================== MySQL connection is closed ``` ## モジュール 3: TiFlashを使用した複雑なクエリの高速化 ### 手順 1. 分析クエリを実行し、実行計画を確認する + TiDBに接続し、分析クエリを実行します。 ```bash mysql \ --comments -u '${LAB:SERVERLESS_CLUSTER_USERNAME}' -p'${LAB:SERVERLESS_CLUSTER_PASSWORD}' \ -h ${LAB:SERVERLESS_CLUSTER_HOST} -P ${LAB:SERVERLESS_CLUSTER_PORT} \ --ssl-mode=VERIFY_IDENTITY --ssl-ca=/etc/pki/tls/certs/ca-bundle.crt ``` ```sql USE ${LAB:SERVERLESS_CLUSTER_DATABASE_NAME}; WITH index_companies AS ( SELECT c.stock_symbol, c.short_name, c.exchange_symbol, ic.weight, c.market_cap AS market_cap, c.revenue_growth AS revenue_growth FROM companies c JOIN index_compositions ic ON c.stock_symbol = ic.stock_symbol WHERE index_symbol = 'SP500' AND sector = 'Energy' ), companies_latest_price AS ( SELECT stock_symbol, DATE(last_day) AS last_change_day, last_close_price, last_2nd_close_price, last_close_price - last_2nd_close_price AS last_change, (last_close_price - last_2nd_close_price) / last_2nd_close_price AS last_change_percentage FROM ( SELECT LAST_VALUE(stock_symbol) OVER (PARTITION BY stock_symbol ORDER BY record_date) AS stock_symbol, LAST_VALUE(record_date) OVER (PARTITION BY stock_symbol ORDER BY record_date) AS last_day, NTH_VALUE(close, 1) OVER (PARTITION BY stock_symbol ORDER BY record_date DESC) AS last_close_price, NTH_VALUE(close, 2) OVER (PARTITION BY stock_symbol ORDER BY record_date DESC) AS last_2nd_close_price, ROW_NUMBER() OVER (PARTITION BY stock_symbol ORDER BY record_date DESC) AS row_num FROM stock_price_history sph WHERE record_date > DATE_SUB((SELECT MAX(record_date) FROM stock_price_history), INTERVAL 2 DAY) AND sph.stock_symbol IN (SELECT stock_symbol FROM index_companies ic) ) sub WHERE row_num = 2 ) SELECT ic.stock_symbol, ic.exchange_symbol, ic.short_name, ic.weight, ic.market_cap, ic.revenue_growth, clp.last_change, clp.last_change_day, clp.last_close_price, clp.last_2nd_close_price, clp.last_change_percentage FROM index_companies ic LEFT JOIN companies_latest_price clp ON ic.stock_symbol = clp.stock_symbol ORDER BY ic.weight DESC; ``` ```output +--------------+-----------------+---------------------------------+------------------------+--------------+----------------+---------------------+-----------------+----------------------+----------------------+-------------------------+ | stock_symbol | exchange_symbol | short_name | weight | market_cap | revenue_growth | last_change | last_change_day | last_close_price | last_2nd_close_price | last_change_percentage | +--------------+-----------------+---------------------------------+------------------------+--------------+----------------+---------------------+-----------------+----------------------+----------------------+-------------------------+ | XOM | NYQ | Exxon Mobil Corporation | 0.013441937585551282 | 474511933440 | 0.689 | 1.2800064086914100 | 2023-01-10 | 111.3700027465820300 | 110.0899963378906200 | 0.01162690935843785832 | | CVX | NYQ | Chevron Corporation | 0.009567226003564158 | 337731289088 | 0.81 | -0.8399963378906000 | 2023-01-10 | 175.1999969482422000 | 176.0399932861328000 | -0.00477162218772232275 | | COP | NYQ | ConocoPhillips | 0.004417814517419704 | 155952644096 | 1.237 | 0.1599960327148400 | 2023-01-10 | 118.2399978637695300 | 118.0800018310546900 | 0.00135497993084178221 | | SLB | NYQ | Schlumberger N.V. | 0.0022377960040345308 | 78996119552 | 0.279 | NULL | NULL | NULL | NULL | NULL | | EOG | NYQ | EOG Resources, Inc. | 0.0021024604924476343 | 74218659840 | 0.924 | -0.7600021362304600 | 2023-01-10 | 126.8499984741211000 | 127.6100006103515600 | -0.00595566282105957140 | | MPC | NYQ | Marathon Petroleum Corporation | 0.002050747487098508 | 72393146368 | 0.818 | 1.0999984741211000 | 2023-01-10 | 117.5999984741211000 | 116.5000000000000000 | 0.00944204698816394850 | | OXY | NYQ | Occidental Petroleum Corporatio | 0.001695676548826279 | 59858837504 | 0.792 | NULL | NULL | NULL | NULL | NULL | | PXD | NYQ | Pioneer Natural Resources Compa | 0.0016017013932190817 | 56541433856 | 0.641 | NULL | NULL | NULL | NULL | NULL | | VLO | NYQ | Valero Energy Corporation | 0.0015472850935959574 | 54620491776 | 0.593 | NULL | NULL | NULL | NULL | NULL | | HES | NYQ | Hess Corporation | 0.0012744122628788822 | 44987846656 | 0.776 | 1.6300048828125000 | 2023-01-10 | 145.2500000000000000 | 143.6199951171875000 | 0.01134942861878312162 | | PSX | NYQ | Phillips 66 | 0.0012589621320069535 | 44442443776 | 0.799 | NULL | NULL | NULL | NULL | NULL | | KMI | NYQ | Kinder Morgan, Inc. | 0.0012062691273686955 | 42582335488 | 0.354 | 0.0400009155273440 | 2023-01-10 | 18.7800006866455080 | 18.7399997711181640 | 0.00213452059850037319 | | DVN | NYQ | Devon Energy Corporation | 0.0012053375130788024 | 42549448704 | 0.789 | -0.2800025939941400 | 2023-01-10 | 62.8499984741210940 | 63.1300010681152340 | -0.00443533326875800678 | | WMB | NYQ | Williams Companies, Inc. (The) | 0.0011162172177908748 | 39403425792 | 0.091 | NULL | NULL | NULL | NULL | NULL | | HAL | NYQ | Halliburton Company | 0.0010393744130051477 | 36690808832 | 0.388 | 0.0500030517578160 | 2023-01-10 | 40.9900016784668000 | 40.9399986267089840 | 0.00122137404580161222 | | OKE | NYQ | ONEOK, Inc. | 0.0008854096904464799 | 31255721984 | 0.769 | NULL | NULL | NULL | NULL | NULL | | BKR | NMS | Baker Hughes Company | 0.0008807471517968449 | 31091130368 | 0.054 | -0.0400009155273450 | 2023-01-10 | 30.8299999237060550 | 30.8700008392334000 | -0.00129578602008027511 | | FANG | NMS | Diamondback Energy, Inc. | 0.000730020047064916 | 25770334208 | 0.565 | 0.2900085449218600 | 2023-01-10 | 142.2400054931640600 | 141.9499969482422000 | 0.00204303311839874783 | | MRO | NYQ | Marathon Oil Corporation | 0.0005965698099807201 | 21059426304 | 0.731 | 0.0399990081787110 | 2023-01-10 | 27.0499992370605470 | 27.0100002288818360 | 0.00148089625471161593 | | CTRA | NYQ | Coterra Energy Inc. | 0.0005690298110137097 | 20087240704 | 5.204 | -0.3899993896484380 | 2023-01-10 | 24.6900005340576170 | 25.0799999237060550 | -0.01555021494556719473 | | TRGP | NYQ | Targa Resources, Inc. | 0.0004842024080652878 | 17092760576 | 0.773 | NULL | NULL | NULL | NULL | NULL | | APA | NMS | APA Corporation | 0.00039910236086756015 | 14088655872 | 0.374 | 0.5799980163574200 | 2023-01-10 | 43.8199996948242200 | 43.2400016784668000 | 0.01341345961709929139 | | EQT | NYQ | EQT Corporation | 0.0003576031445812937 | 12623697920 | 1.092 | -0.6399993896484380 | 2023-01-10 | 33.4000015258789060 | 34.0400009155273440 | -0.01880139166966068805 | +--------------+-----------------+---------------------------------+------------------------+--------------+----------------+---------------------+-----------------+----------------------+----------------------+-------------------------+ 23 rows in set, 1 warning (4.50 sec) ``` + クエリの実行計画を確認するために、複雑なクエリの先頭に `EXPLAIN` を追加します。（これは長いクエリになるので、事前に `PAGER less -S` を実行してください。元に戻すには `NOPAGER` を実行します。） ```sql PAGER less -S ``` ```sql EXPLAIN WITH index_companies AS ( SELECT c.stock_symbol, c.short_name, c.exchange_symbol, ic.weight, c.market_cap AS market_cap, c.revenue_growth AS revenue_growth FROM companies c JOIN index_compositions ic ON c.stock_symbol = ic.stock_symbol WHERE index_symbol = 'SP500' AND sector = 'Energy' ), companies_latest_price AS ( SELECT stock_symbol, DATE(last_day) AS last_change_day, last_close_price, last_2nd_close_price, last_close_price - last_2nd_close_price AS last_change, (last_close_price - last_2nd_close_price) / last_2nd_close_price AS last_change_percentage FROM ( SELECT LAST_VALUE(stock_symbol) OVER (PARTITION BY stock_symbol ORDER BY record_date) AS stock_symbol, LAST_VALUE(record_date) OVER (PARTITION BY stock_symbol ORDER BY record_date) AS last_day, NTH_VALUE(close, 1) OVER (PARTITION BY stock_symbol ORDER BY record_date DESC) AS last_close_price, NTH_VALUE(close, 2) OVER (PARTITION BY stock_symbol ORDER BY record_date DESC) AS last_2nd_close_price, ROW_NUMBER() OVER (PARTITION BY stock_symbol ORDER BY record_date DESC) AS row_num FROM stock_price_history sph WHERE record_date > DATE_SUB((SELECT MAX(record_date) FROM stock_price_history), INTERVAL 2 DAY) AND sph.stock_symbol IN (SELECT stock_symbol FROM index_companies ic) ) sub WHERE row_num = 2 ) SELECT ic.stock_symbol, ic.exchange_symbol, ic.short_name, ic.weight, ic.market_cap, ic.revenue_growth, clp.last_change, clp.last_change_day, clp.last_close_price, clp.last_2nd_close_price, clp.last_change_percentage FROM index_companies ic LEFT JOIN companies_latest_price clp ON ic.stock_symbol = clp.stock_symbol ORDER BY ic.weight DESC; ``` ```output | id | estRows | task | access object | operator info | +------------------------------------------------------+------------+-----------+---------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Sort_125 | 23.00 | root | | ${LAB:SERVERLESS_CLUSTER_DATABASE_NAME}.index_compositions.weight:desc | | └─Projection_127 | 23.00 | root | | ${LAB:SERVERLESS_CLUSTER_DATABASE_NAME}.companies.stock_symbol, ${LAB:SERVERLESS_CLUSTER_DATABASE_NAME}.companies.exchange_symbol, ${LAB:SERVERLESS_CLUSTER_DATABASE_NAME}.companies.short_name, ${LAB:SERVERLESS_CLUSTER_DATABASE_NAME}.index_compositions.weight, ${LAB:SERVERLESS_CLUSTER_DATABASE_NAME}.companies.market_cap, ${LAB:SERVERLESS_CLUSTER_DATABASE_NAME}.companies.revenue_growth, Column#118, Column#117, Column#109, Column#108, Column#119 | | └─HashJoin_129 | 23.00 | root | | left outer join, equal:[eq(${LAB:SERVERLESS_CLUSTER_DATABASE_NAME}.companies.stock_symbol, Column#111)] | | ├─CTEFullScan_156(Build) | 23.00 | root | CTE:index_companies AS ic | data:CTE_0 | | └─Projection_131(Probe) | 1572.00 | root | | Column#111, date(Column#110)->Column#117, Column#109, Column#108, minus(Column#109, Column#108)->Column#118, div(minus(Column#109, Column#108), Column#108)->Column#119 | | └─Selection_132 | 1572.00 | root | | eq(Column#107, 2), not(isnull(Column#111)) | | └─Window_133 | 1965.00 | root | | last_value(${LAB:SERVERLESS_CLUSTER_DATABASE_NAME}.stock_price_history.stock_symbol)->Column#111 over(partition by ${LAB:SERVERLESS_CLUSTER_DATABASE_NAME}.stock_price_history.stock_symbol order by ${LAB:SERVERLESS_CLUSTER_DATABASE_NAME}.stock_price_history.record_date range between unbounded preceding and current row) | | └─Window_134 | 1965.00 | root | | last_value(${LAB:SERVERLESS_CLUSTER_DATABASE_NAME}.stock_price_history.record_date)->Column#110 over(partition by ${LAB:SERVERLESS_CLUSTER_DATABASE_NAME}.stock_price_history.stock_symbol order by ${LAB:SERVERLESS_CLUSTER_DATABASE_NAME}.stock_price_history.record_date range between unbounded preceding and current row) | | └─Sort_154 | 1965.00 | root | | ${LAB:SERVERLESS_CLUSTER_DATABASE_NAME}.stock_price_history.stock_symbol, ${LAB:SERVERLESS_CLUSTER_DATABASE_NAME}.stock_price_history.record_date | | └─Window_136 | 1965.00 | root | | nth_value(${LAB:SERVERLESS_CLUSTER_DATABASE_NAME}.stock_price_history.close, 1)->Column#109 over(partition by ${LAB:SERVERLESS_CLUSTER_DATABASE_NAME}.stock_price_history.stock_symbol order by ${LAB:SERVERLESS_CLUSTER_DATABASE_NAME}.stock_price_history.record_date desc range between unbounded preceding and current row) | | └─Window_137 | 1965.00 | root | | nth_value(${LAB:SERVERLESS_CLUSTER_DATABASE_NAME}.stock_price_history.close, 2)->Column#108 over(partition by ${LAB:SERVERLESS_CLUSTER_DATABASE_NAME}.stock_price_history.stock_symbol order by ${LAB:SERVERLESS_CLUSTER_DATABASE_NAME}.stock_price_history.record_date desc range between unbounded preceding and current row) | | └─Window_139 | 1965.00 | root | | row_number()->Column#107 over(partition by ${LAB:SERVERLESS_CLUSTER_DATABASE_NAME}.stock_price_history.stock_symbol order by ${LAB:SERVERLESS_CLUSTER_DATABASE_NAME}.stock_price_history.record_date desc rows between current row and current row) | | └─Sort_151 | 1965.00 | root | | ${LAB:SERVERLESS_CLUSTER_DATABASE_NAME}.stock_price_history.stock_symbol, ${LAB:SERVERLESS_CLUSTER_DATABASE_NAME}.stock_price_history.record_date:desc | | └─HashJoin_144 | 1965.00 | root | | inner join, equal:[eq(${LAB:SERVERLESS_CLUSTER_DATABASE_NAME}.companies.stock_symbol, ${LAB:SERVERLESS_CLUSTER_DATABASE_NAME}.stock_price_history.stock_symbol)] | | ├─HashAgg_145(Build) | 18.40 | root | | group by:${LAB:SERVERLESS_CLUSTER_DATABASE_NAME}.companies.stock_symbol, funcs:firstrow(${LAB:SERVERLESS_CLUSTER_DATABASE_NAME}.companies.stock_symbol)->${LAB:SERVERLESS_CLUSTER_DATABASE_NAME}.companies.stock_symbol | | │ └─Selection_146 | 18.40 | root | | not(isnull(${LAB:SERVERLESS_CLUSTER_DATABASE_NAME}.companies.stock_symbol)) | | │ └─CTEFullScan_147 | 23.00 | root | CTE:index_companies AS ic | data:CTE_0 | | └─TableReader_150(Probe) | 1965.00 | root | | data:Selection_149 | | └─Selection_149 | 1965.00 | cop[tikv] | | gt(${LAB:SERVERLESS_CLUSTER_DATABASE_NAME}.stock_price_history.record_date, 2023-01-09), not(isnull(${LAB:SERVERLESS_CLUSTER_DATABASE_NAME}.stock_price_history.stock_symbol)) | | └─TableFullScan_148 | 1157000.00 | cop[tikv] | table:sph | keep order:false | | CTE_0 | 23.00 | root | | Non-Recursive CTE | | └─Projection_113(Seed Part) | 23.00 | root | | ${LAB:SERVERLESS_CLUSTER_DATABASE_NAME}.companies.stock_symbol, ${LAB:SERVERLESS_CLUSTER_DATABASE_NAME}.companies.short_name, ${LAB:SERVERLESS_CLUSTER_DATABASE_NAME}.companies.exchange_symbol, ${LAB:SERVERLESS_CLUSTER_DATABASE_NAME}.index_compositions.weight, ${LAB:SERVERLESS_CLUSTER_DATABASE_NAME}.companies.market_cap, ${LAB:SERVERLESS_CLUSTER_DATABASE_NAME}.companies.revenue_growth | | └─HashJoin_115 | 23.00 | root | | inner join, equal:[eq(${LAB:SERVERLESS_CLUSTER_DATABASE_NAME}.companies.stock_symbol, ${LAB:SERVERLESS_CLUSTER_DATABASE_NAME}.index_compositions.stock_symbol)] | | ├─TableReader_118(Build) | 23.00 | root | | data:Selection_117 | | │ └─Selection_117 | 23.00 | cop[tikv] | | eq(${LAB:SERVERLESS_CLUSTER_DATABASE_NAME}.companies.sector, "Energy"), not(isnull(${LAB:SERVERLESS_CLUSTER_DATABASE_NAME}.companies.stock_symbol)), or(1, not(isnull(${LAB:SERVERLESS_CLUSTER_DATABASE_NAME}.companies.stock_symbol))) | | │ └─TableFullScan_116 | 493.00 | cop[tikv] | table:c | keep order:false | | └─TableReader_121(Probe) | 493.00 | root | | data:Selection_120 | | └─Selection_120 | 493.00 | cop[tikv] | | eq(${LAB:SERVERLESS_CLUSTER_DATABASE_NAME}.index_compositions.index_symbol, "SP500"), not(isnull(${LAB:SERVERLESS_CLUSTER_DATABASE_NAME}.index_compositions.stock_symbol)) | | └─TableFullScan_119 | 493.00 | cop[tikv] | table:ic | keep order:false | +------------------------------------------------------+------------+-----------+---------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 29 rows in set (1.08 sec) ``` + `PAGER` ビューを終了するには `Q` を押します。 ```output mysql> ``` + `PAGER` をオフにします。 ```sql NOPAGER ``` ```output PAGER set to stdout mysql> ``` ### 手順 2. TiFlash同期設定 + 以下の3つのテーブルをTiFlashと同期するように設定します：`stock_price_history`、`index_companies`、および`companies`。 ```sql ALTER TABLE ${LAB:SERVERLESS_CLUSTER_DATABASE_NAME}.companies SET TIFLASH REPLICA 2; ALTER TABLE ${LAB:SERVERLESS_CLUSTER_DATABASE_NAME}.index_compositions SET TIFLASH REPLICA 2; ALTER TABLE ${LAB:SERVERLESS_CLUSTER_DATABASE_NAME}.stock_price_history SET TIFLASH REPLICA 2; ``` ```output ... Query OK, 0 rows affected, 1 warning (0.20 sec) ``` + 以下のクエリで同期状態を確認できます。（`PROGRESS`が1で`AVAILABLE`が1の場合、同期が完了しています。） ```sql SELECT * FROM information_schema.tiflash_replica WHERE TABLE_SCHEMA = '${LAB:SERVERLESS_CLUSTER_DATABASE_NAME}'; ``` + 進行中の状態。 ```output +----------------------+---------------------+----------+---------------+-----------------+-----------+----------+ | TABLE_SCHEMA | TABLE_NAME | TABLE_ID | REPLICA_COUNT | LOCATION_LABELS | AVAILABLE | PROGRESS | +----------------------+---------------------+----------+---------------+-----------------+-----------+----------+ | lab_demo_201_2370038 | companies | 114 | 2 | | 0 | 0.5 | | lab_demo_201_2370038 | index_compositions | 116 | 2 | | 0 | 0 | | lab_demo_201_2370038 | stock_price_history | 122 | 2 | | 0 | 0 | +----------------------+---------------------+----------+---------------+-----------------+-----------+----------+ 3 rows in set (0.05 sec) ``` + 完了した状態。 ```output +----------------------+---------------------+----------+---------------+-----------------+-----------+----------+ | TABLE_SCHEMA | TABLE_NAME | TABLE_ID | REPLICA_COUNT | LOCATION_LABELS | AVAILABLE | PROGRESS | +----------------------+---------------------+----------+---------------+-----------------+-----------+----------+ | lab_demo_201_2370038 | companies | 114 | 2 | | 1 | 1 | | lab_demo_201_2370038 | index_compositions | 116 | 2 | | 1 | 1 | | lab_demo_201_2370038 | stock_price_history | 122 | 2 | | 1 | 1 | +----------------------+---------------------+----------+---------------+-----------------+-----------+----------+ 3 rows in set (0.05 sec) ``` ### 手順 3. 分析クエリを再度実行して実行計画を確認する + 同じクエリを再度実行し、実行時間と実行計画を確認します。 ```sql WITH index_companies AS ( SELECT c.stock_symbol, c.short_name, c.exchange_symbol, ic.weight, c.market_cap AS market_cap, c.revenue_growth AS revenue_growth FROM companies c JOIN index_compositions ic ON c.stock_symbol = ic.stock_symbol WHERE index_symbol = 'SP500' AND sector = 'Energy' ), companies_latest_price AS ( SELECT stock_symbol, DATE(last_day) AS last_change_day, last_close_price, last_2nd_close_price, last_close_price - last_2nd_close_price AS last_change, (last_close_price - last_2nd_close_price) / last_2nd_close_price AS last_change_percentage FROM ( SELECT LAST_VALUE(stock_symbol) OVER (PARTITION BY stock_symbol ORDER BY record_date) AS stock_symbol, LAST_VALUE(record_date) OVER (PARTITION BY stock_symbol ORDER BY record_date) AS last_day, NTH_VALUE(close, 1) OVER (PARTITION BY stock_symbol ORDER BY record_date DESC) AS last_close_price, NTH_VALUE(close, 2) OVER (PARTITION BY stock_symbol ORDER BY record_date DESC) AS last_2nd_close_price, ROW_NUMBER() OVER (PARTITION BY stock_symbol ORDER BY record_date DESC) AS row_num FROM stock_price_history sph WHERE record_date > DATE_SUB((SELECT MAX(record_date) FROM stock_price_history), INTERVAL 2 DAY) AND sph.stock_symbol IN (SELECT stock_symbol FROM index_companies ic) ) sub WHERE row_num = 2 ) SELECT ic.stock_symbol, ic.exchange_symbol, ic.short_name, ic.weight, ic.market_cap, ic.revenue_growth, clp.last_change, clp.last_change_day, clp.last_close_price, clp.last_2nd_close_price, clp.last_change_percentage FROM index_companies ic LEFT JOIN companies_latest_price clp ON ic.stock_symbol = clp.stock_symbol ORDER BY ic.weight DESC; ``` + 結果は以前よりもはるかに速く返されます。 ```output +--------------+-----------------+---------------------------------+------------------------+--------------+----------------+---------------------+-----------------+----------------------+----------------------+-------------------------+ | stock_symbol | exchange_symbol | short_name | weight | market_cap | revenue_growth | last_change | last_change_day | last_close_price | last_2nd_close_price | last_change_percentage | +--------------+-----------------+---------------------------------+------------------------+--------------+----------------+---------------------+-----------------+----------------------+----------------------+-------------------------+ | XOM | NYQ | Exxon Mobil Corporation | 0.013441937585551282 | 474511933440 | 0.689 | 1.2800064086914100 | 2023-01-10 | 111.3700027465820300 | 110.0899963378906200 | 0.01162690935843785832 | | CVX | NYQ | Chevron Corporation | 0.009567226003564158 | 337731289088 | 0.81 | -0.8399963378906000 | 2023-01-10 | 175.1999969482422000 | 176.0399932861328000 | -0.00477162218772232275 | | COP | NYQ | ConocoPhillips | 0.004417814517419704 | 155952644096 | 1.237 | 0.1599960327148400 | 2023-01-10 | 118.2399978637695300 | 118.0800018310546900 | 0.00135497993084178221 | | SLB | NYQ | Schlumberger N.V. | 0.0022377960040345308 | 78996119552 | 0.279 | NULL | NULL | NULL | NULL | NULL | | EOG | NYQ | EOG Resources, Inc. | 0.0021024604924476343 | 74218659840 | 0.924 | -0.7600021362304600 | 2023-01-10 | 126.8499984741211000 | 127.6100006103515600 | -0.00595566282105957140 | | MPC | NYQ | Marathon Petroleum Corporation | 0.002050747487098508 | 72393146368 | 0.818 | 1.0999984741211000 | 2023-01-10 | 117.5999984741211000 | 116.5000000000000000 | 0.00944204698816394850 | | OXY | NYQ | Occidental Petroleum Corporatio | 0.001695676548826279 | 59858837504 | 0.792 | NULL | NULL | NULL | NULL | NULL | | PXD | NYQ | Pioneer Natural Resources Compa | 0.0016017013932190817 | 56541433856 | 0.641 | NULL | NULL | NULL | NULL | NULL | | VLO | NYQ | Valero Energy Corporation | 0.0015472850935959574 | 54620491776 | 0.593 | NULL | NULL | NULL | NULL | NULL | | HES | NYQ | Hess Corporation | 0.0012744122628788822 | 44987846656 | 0.776 | 1.6300048828125000 | 2023-01-10 | 145.2500000000000000 | 143.6199951171875000 | 0.01134942861878312162 | | PSX | NYQ | Phillips 66 | 0.0012589621320069535 | 44442443776 | 0.799 | NULL | NULL | NULL | NULL | NULL | | KMI | NYQ | Kinder Morgan, Inc. | 0.0012062691273686955 | 42582335488 | 0.354 | 0.0400009155273440 | 2023-01-10 | 18.7800006866455080 | 18.7399997711181640 | 0.00213452059850037319 | | DVN | NYQ | Devon Energy Corporation | 0.0012053375130788024 | 42549448704 | 0.789 | -0.2800025939941400 | 2023-01-10 | 62.8499984741210940 | 63.1300010681152340 | -0.00443533326875800678 | | WMB | NYQ | Williams Companies, Inc. (The) | 0.0011162172177908748 | 39403425792 | 0.091 | NULL | NULL | NULL | NULL | NULL | | HAL | NYQ | Halliburton Company | 0.0010393744130051477 | 36690808832 | 0.388 | 0.0500030517578160 | 2023-01-10 | 40.9900016784668000 | 40.9399986267089840 | 0.00122137404580161222 | | OKE | NYQ | ONEOK, Inc. | 0.0008854096904464799 | 31255721984 | 0.769 | NULL | NULL | NULL | NULL | NULL | | BKR | NMS | Baker Hughes Company | 0.0008807471517968449 | 31091130368 | 0.054 | -0.0400009155273450 | 2023-01-10 | 30.8299999237060550 | 30.8700008392334000 | -0.00129578602008027511 | | FANG | NMS | Diamondback Energy, Inc. | 0.000730020047064916 | 25770334208 | 0.565 | 0.2900085449218600 | 2023-01-10 | 142.2400054931640600 | 141.9499969482422000 | 0.00204303311839874783 | | MRO | NYQ | Marathon Oil Corporation | 0.0005965698099807201 | 21059426304 | 0.731 | 0.0399990081787110 | 2023-01-10 | 27.0499992370605470 | 27.0100002288818360 | 0.00148089625471161593 | | CTRA | NYQ | Coterra Energy Inc. | 0.0005690298110137097 | 20087240704 | 5.204 | -0.3899993896484380 | 2023-01-10 | 24.6900005340576170 | 25.0799999237060550 | -0.01555021494556719473 | | TRGP | NYQ | Targa Resources, Inc. | 0.0004842024080652878 | 17092760576 | 0.773 | NULL | NULL | NULL | NULL | NULL | | APA | NMS | APA Corporation | 0.00039910236086756015 | 14088655872 | 0.374 | 0.5799980163574200 | 2023-01-10 | 43.8199996948242200 | 43.2400016784668000 | 0.01341345961709929139 | | EQT | NYQ | EQT Corporation | 0.0003576031445812937 | 12623697920 | 1.092 | -0.6399993896484380 | 2023-01-10 | 33.4000015258789060 | 34.0400009155273440 | -0.01880139166966068805 | +--------------+-----------------+---------------------------------+------------------------+--------------+----------------+---------------------+-----------------+----------------------+----------------------+-------------------------+ 23 rows in set (0.62 sec) ``` + 実行計画がTiflash（`mpp[tiflash]`）を使用していることがわかります。 ```sql PAGER less -S EXPLAIN WITH index_companies AS ( SELECT c.stock_symbol, c.short_name, c.exchange_symbol, ic.weight, c.market_cap AS market_cap, c.revenue_growth AS revenue_growth FROM companies c JOIN index_compositions ic ON c.stock_symbol = ic.stock_symbol WHERE index_symbol = 'SP500' AND sector = 'Energy' ), companies_latest_price AS ( SELECT stock_symbol, DATE(last_day) AS last_change_day, last_close_price, last_2nd_close_price, last_close_price - last_2nd_close_price AS last_change, (last_close_price - last_2nd_close_price) / last_2nd_close_price AS last_change_percentage FROM ( SELECT LAST_VALUE(stock_symbol) OVER (PARTITION BY stock_symbol ORDER BY record_date) AS stock_symbol, LAST_VALUE(record_date) OVER (PARTITION BY stock_symbol ORDER BY record_date) AS last_day, NTH_VALUE(close, 1) OVER (PARTITION BY stock_symbol ORDER BY record_date DESC) AS last_close_price, NTH_VALUE(close, 2) OVER (PARTITION BY stock_symbol ORDER BY record_date DESC) AS last_2nd_close_price, ROW_NUMBER() OVER (PARTITION BY stock_symbol ORDER BY record_date DESC) AS row_num FROM stock_price_history sph WHERE record_date > DATE_SUB((SELECT MAX(record_date) FROM stock_price_history), INTERVAL 2 DAY) AND sph.stock_symbol IN (SELECT stock_symbol FROM index_companies ic) ) sub WHERE row_num = 2 ) SELECT ic.stock_symbol, ic.exchange_symbol, ic.short_name, ic.weight, ic.market_cap, ic.revenue_growth, clp.last_change, clp.last_change_day, clp.last_close_price, clp.last_2nd_close_price, clp.last_change_percentage FROM index_companies ic LEFT JOIN companies_latest_price clp ON ic.stock_symbol = clp.stock_symbol ORDER BY ic.weight DESC; ``` ```output +-------------------------------------------------------+---------+--------------+---------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | id | estRows | task | access object | operator info | +-------------------------------------------------------+---------+--------------+---------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Sort_164 | 23.00 | root | | ${LAB:SERVERLESS_CLUSTER_DATABASE_NAME}.index_compositions.weight:desc | | └─Projection_166 | 23.00 | root | | ${LAB:SERVERLESS_CLUSTER_DATABASE_NAME}.companies.stock_symbol, ${LAB:SERVERLESS_CLUSTER_DATABASE_NAME}.companies.exchange_symbol, ${LAB:SERVERLESS_CLUSTER_DATABASE_NAME}.companies.short_name, ${LAB:SERVERLESS_CLUSTER_DATABASE_NAME}.index_compositions.weight, ${LAB:SERVERLESS_CLUSTER_DATABASE_NAME}.companies.market_cap, ${LAB:SERVERLESS_CLUSTER_DATABASE_NAME}.companies.revenue_growth, Column#118, Column#117, Column#109, Column#108, Column#119 | | └─HashJoin_168 | 23.00 | root | | left outer join, equal:[eq(${LAB:SERVERLESS_CLUSTER_DATABASE_NAME}.companies.stock_symbol, Column#111)] | | ├─CTEFullScan_199(Build) | 23.00 | root | CTE:index_companies AS ic | data:CTE_0 | | └─Projection_170(Probe) | 1572.00 | root | | Column#111, date(Column#110)->Column#117, Column#109, Column#108, minus(Column#109, Column#108)->Column#118, div(minus(Column#109, Column#108), Column#108)->Column#119 | | └─Selection_171 | 1572.00 | root | | eq(Column#107, 2), not(isnull(Column#111)) | | └─Window_172 | 1965.00 | root | | last_value(${LAB:SERVERLESS_CLUSTER_DATABASE_NAME}.stock_price_history.stock_symbol)->Column#111 over(partition by ${LAB:SERVERLESS_CLUSTER_DATABASE_NAME}.stock_price_history.stock_symbol order by ${LAB:SERVERLESS_CLUSTER_DATABASE_NAME}.stock_price_history.record_date range between unbounded preceding and current row) | | └─Window_173 | 1965.00 | root | | last_value(${LAB:SERVERLESS_CLUSTER_DATABASE_NAME}.stock_price_history.record_date)->Column#110 over(partition by ${LAB:SERVERLESS_CLUSTER_DATABASE_NAME}.stock_price_history.stock_symbol order by ${LAB:SERVERLESS_CLUSTER_DATABASE_NAME}.stock_price_history.record_date range between unbounded preceding and current row) | | └─Sort_197 | 1965.00 | root | | ${LAB:SERVERLESS_CLUSTER_DATABASE_NAME}.stock_price_history.stock_symbol, ${LAB:SERVERLESS_CLUSTER_DATABASE_NAME}.stock_price_history.record_date | | └─Window_175 | 1965.00 | root | | nth_value(${LAB:SERVERLESS_CLUSTER_DATABASE_NAME}.stock_price_history.close, 1)->Column#109 over(partition by ${LAB:SERVERLESS_CLUSTER_DATABASE_NAME}.stock_price_history.stock_symbol order by ${LAB:SERVERLESS_CLUSTER_DATABASE_NAME}.stock_price_history.record_date desc range between unbounded preceding and current row) | | └─Window_176 | 1965.00 | root | | nth_value(${LAB:SERVERLESS_CLUSTER_DATABASE_NAME}.stock_price_history.close, 2)->Column#108 over(partition by ${LAB:SERVERLESS_CLUSTER_DATABASE_NAME}.stock_price_history.stock_symbol order by ${LAB:SERVERLESS_CLUSTER_DATABASE_NAME}.stock_price_history.record_date desc range between unbounded preceding and current row) | | └─Window_178 | 1965.00 | root | | row_number()->Column#107 over(partition by ${LAB:SERVERLESS_CLUSTER_DATABASE_NAME}.stock_price_history.stock_symbol order by ${LAB:SERVERLESS_CLUSTER_DATABASE_NAME}.stock_price_history.record_date desc rows between current row and current row) | | └─Sort_194 | 1965.00 | root | | ${LAB:SERVERLESS_CLUSTER_DATABASE_NAME}.stock_price_history.stock_symbol, ${LAB:SERVERLESS_CLUSTER_DATABASE_NAME}.stock_price_history.record_date:desc | | └─HashJoin_183 | 1965.00 | root | | inner join, equal:[eq(${LAB:SERVERLESS_CLUSTER_DATABASE_NAME}.companies.stock_symbol, ${LAB:SERVERLESS_CLUSTER_DATABASE_NAME}.stock_price_history.stock_symbol)] | | ├─HashAgg_184(Build) | 18.40 | root | | group by:${LAB:SERVERLESS_CLUSTER_DATABASE_NAME}.companies.stock_symbol, funcs:firstrow(${LAB:SERVERLESS_CLUSTER_DATABASE_NAME}.companies.stock_symbol)->${LAB:SERVERLESS_CLUSTER_DATABASE_NAME}.companies.stock_symbol | | │ └─Selection_185 | 18.40 | root | | not(isnull(${LAB:SERVERLESS_CLUSTER_DATABASE_NAME}.companies.stock_symbol)) | | │ └─CTEFullScan_186 | 23.00 | root | CTE:index_companies AS ic | data:CTE_0 | | └─TableReader_193(Probe) | 1965.00 | root | | MppVersion: 1, data:ExchangeSender_192 | | └─ExchangeSender_192 | 1965.00 | mpp[tiflash] | | ExchangeType: PassThrough | | └─Selection_191 | 1965.00 | mpp[tiflash] | | not(isnull(${LAB:SERVERLESS_CLUSTER_DATABASE_NAME}.stock_price_history.stock_symbol)) | | └─TableFullScan_190 | 1965.00 | mpp[tiflash] | table:sph | pushed down filter:gt(${LAB:SERVERLESS_CLUSTER_DATABASE_NAME}.stock_price_history.record_date, 2023-01-09), keep order:false | | CTE_0 | 23.00 | root | | Non-Recursive CTE | | └─Projection_131(Seed Part) | 23.00 | root | | ${LAB:SERVERLESS_CLUSTER_DATABASE_NAME}.companies.stock_symbol, ${LAB:SERVERLESS_CLUSTER_DATABASE_NAME}.companies.short_name, ${LAB:SERVERLESS_CLUSTER_DATABASE_NAME}.companies.exchange_symbol, ${LAB:SERVERLESS_CLUSTER_DATABASE_NAME}.index_compositions.weight, ${LAB:SERVERLESS_CLUSTER_DATABASE_NAME}.companies.market_cap, ${LAB:SERVERLESS_CLUSTER_DATABASE_NAME}.companies.revenue_growth | | └─HashJoin_135 | 23.00 | root | | inner join, equal:[eq(${LAB:SERVERLESS_CLUSTER_DATABASE_NAME}.companies.stock_symbol, ${LAB:SERVERLESS_CLUSTER_DATABASE_NAME}.index_compositions.stock_symbol)] | | ├─TableReader_146(Build) | 23.00 | root | | data:Selection_145 | | │ └─Selection_145 | 23.00 | cop[tikv] | | eq(${LAB:SERVERLESS_CLUSTER_DATABASE_NAME}.companies.sector, "Energy"), not(isnull(${LAB:SERVERLESS_CLUSTER_DATABASE_NAME}.companies.stock_symbol)), or(1, not(isnull(${LAB:SERVERLESS_CLUSTER_DATABASE_NAME}.companies.stock_symbol))) | | │ └─TableFullScan_144 | 493.00 | cop[tikv] | table:c | keep order:false | | └─TableReader_153(Probe) | 493.00 | root | | data:Selection_152 | | └─Selection_152 | 493.00 | cop[tikv] | | eq(${LAB:SERVERLESS_CLUSTER_DATABASE_NAME}.index_compositions.index_symbol, "SP500"), not(isnull(${LAB:SERVERLESS_CLUSTER_DATABASE_NAME}.index_compositions.stock_symbol)) | | └─TableFullScan_151 | 493.00 | cop[tikv] | table:ic | keep order:false | +-------------------------------------------------------+---------+--------------+---------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 30 rows in set (0.52 sec) ``` + `PAGER` ビューを終了するには `Q` を押してください。 ```output mysql> ``` ### 手順 4. 同じスクリプトを実行して結果を確認し、パフォーマンスが向上したことを確認する + mysqlクライアントを終了し、[ターミナル 1](ssh://monitor1-ssh#1) に切り替えます。 ```sql EXIT ``` + スクリプトを実行して分析クエリを再度実行します。以下の結果は、より高速になったことを示しています。 ```bash python test01.py ``` ```output ■■■Query 1: Market capitalization by industry■■■ Query execution start time: 02:25:59.895391 Query execution end time: 02:25:59.910081 +------------------------+------------------+ | sector | total_market_cap | +------------------------+------------------+ | Real Estate | 927820086272 | | Technology | 7906855563776 | | Financial Services | 4661122020864 | | Healthcare | 5122903213056 | | Utilities | 1023797270016 | | Consumer Defensive | 2872367655424 | | Communication Services | 3669841019392 | | Basic Materials | 889871172608 | | Energy | 1788549832704 | | Consumer Cyclical | 3238477340160 | | Industrials | 3199253085184 | +------------------------+------------------+ ========================== ■■■Query 2: Market capitalization ranking in the energy industry■■■ Query execution start time: 02:25:59.911859 Query execution end time: 02:26:00.290869 +--------------+-----------------+---------------------------------+------------------------+--------------+----------------+---------------------+-----------------+----------------------+----------------------+-------------------------+ | stock_symbol | exchange_symbol | short_name | weight | market_cap | revenue_growth | last_change | last_change_day | last_close_price | last_2nd_close_price | last_change_percentage | +--------------+-----------------+---------------------------------+------------------------+--------------+----------------+---------------------+-----------------+----------------------+----------------------+-------------------------+ | XOM | NYQ | Exxon Mobil Corporation | 0.013441937585551282 | 474511933440 | 0.689 | 1.2800064086914100 | 2023-01-10 | 111.3700027465820300 | 110.0899963378906200 | 0.01162690935843785832 | | CVX | NYQ | Chevron Corporation | 0.009567226003564158 | 337731289088 | 0.81 | -0.8399963378906000 | 2023-01-10 | 175.1999969482422000 | 176.0399932861328000 | -0.00477162218772232275 | | COP | NYQ | ConocoPhillips | 0.004417814517419704 | 155952644096 | 1.237 | 0.1599960327148400 | 2023-01-10 | 118.2399978637695300 | 118.0800018310546900 | 0.00135497993084178221 | | SLB | NYQ | Schlumberger N.V. | 0.0022377960040345308 | 78996119552 | 0.279 | None | None | None | None | None | | EOG | NYQ | EOG Resources, Inc. | 0.0021024604924476343 | 74218659840 | 0.924 | -0.7600021362304600 | 2023-01-10 | 126.8499984741211000 | 127.6100006103515600 | -0.00595566282105957140 | | MPC | NYQ | Marathon Petroleum Corporation | 0.002050747487098508 | 72393146368 | 0.818 | 1.0999984741211000 | 2023-01-10 | 117.5999984741211000 | 116.5000000000000000 | 0.00944204698816394850 | | OXY | NYQ | Occidental Petroleum Corporatio | 0.001695676548826279 | 59858837504 | 0.792 | None | None | None | None | None | | PXD | NYQ | Pioneer Natural Resources Compa | 0.0016017013932190817 | 56541433856 | 0.641 | None | None | None | None | None | | VLO | NYQ | Valero Energy Corporation | 0.0015472850935959574 | 54620491776 | 0.593 | None | None | None | None | None | | HES | NYQ | Hess Corporation | 0.0012744122628788822 | 44987846656 | 0.776 | 1.6300048828125000 | 2023-01-10 | 145.2500000000000000 | 143.6199951171875000 | 0.01134942861878312162 | | PSX | NYQ | Phillips 66 | 0.0012589621320069535 | 44442443776 | 0.799 | None | None | None | None | None | | KMI | NYQ | Kinder Morgan, Inc. | 0.0012062691273686955 | 42582335488 | 0.354 | 0.0400009155273440 | 2023-01-10 | 18.7800006866455080 | 18.7399997711181640 | 0.00213452059850037319 | | DVN | NYQ | Devon Energy Corporation | 0.0012053375130788024 | 42549448704 | 0.789 | -0.2800025939941400 | 2023-01-10 | 62.8499984741210940 | 63.1300010681152340 | -0.00443533326875800678 | | WMB | NYQ | Williams Companies, Inc. (The) | 0.0011162172177908748 | 39403425792 | 0.091 | None | None | None | None | None | | HAL | NYQ | Halliburton Company | 0.0010393744130051477 | 36690808832 | 0.388 | 0.0500030517578160 | 2023-01-10 | 40.9900016784668000 | 40.9399986267089840 | 0.00122137404580161222 | | OKE | NYQ | ONEOK, Inc. | 0.0008854096904464799 | 31255721984 | 0.769 | None | None | None | None | None | | BKR | NMS | Baker Hughes Company | 0.0008807471517968449 | 31091130368 | 0.054 | -0.0400009155273450 | 2023-01-10 | 30.8299999237060550 | 30.8700008392334000 | -0.00129578602008027511 | | FANG | NMS | Diamondback Energy, Inc. | 0.000730020047064916 | 25770334208 | 0.565 | 0.2900085449218600 | 2023-01-10 | 142.2400054931640600 | 141.9499969482422000 | 0.00204303311839874783 | | MRO | NYQ | Marathon Oil Corporation | 0.0005965698099807201 | 21059426304 | 0.731 | 0.0399990081787110 | 2023-01-10 | 27.0499992370605470 | 27.0100002288818360 | 0.00148089625471161593 | | CTRA | NYQ | Coterra Energy Inc. | 0.0005690298110137097 | 20087240704 | 5.204 | -0.3899993896484380 | 2023-01-10 | 24.6900005340576170 | 25.0799999237060550 | -0.01555021494556719473 | | TRGP | NYQ | Targa Resources, Inc. | 0.0004842024080652878 | 17092760576 | 0.773 | None | None | None | None | None | | APA | NMS | APA Corporation | 0.00039910236086756015 | 14088655872 | 0.374 | 0.5799980163574200 | 2023-01-10 | 43.8199996948242200 | 43.2400016784668000 | 0.01341345961709929139 | | EQT | NYQ | EQT Corporation | 0.0003576031445812937 | 12623697920 | 1.092 | -0.6399993896484380 | 2023-01-10 | 33.4000015258789060 | 34.0400009155273440 | -0.01880139166966068805 | +--------------+-----------------+---------------------------------+------------------------+--------------+----------------+---------------------+-----------------+----------------------+----------------------+-------------------------+ ========================== MySQL connection is closed ``` ### 手順 5. TiDB Cloud コンソールに移動してあなた自身の旅を始めましょう + [こちら](https://tidbcloud.com/clusters)から TiDB Cloud の旅を始めることができます。この演習で自分の無料のクラスタを使用した場合は、同じページに表示されています。クラスタ名は ${LAB:SERVERLESS_CLUSTER_NAME} です。 ## 結論 TiDB Cloud Serverlessは、TiDB Cloudの機能をさらに一歩進め、手間のかからない自動スケーリングのMySQL互換データベースを提供します。この完全に管理されたマルチテナント型サービスにより、ユーザーはインフラストラクチャを気にすることなく、アプリケーションに純粋に集中することができます。 Serverlessティアは、初心者向けの無料クラスタプランとビジネスニーズに合わせた成長が必要なアプリケーション向けのスケーラブルクラスタプランという2つの主要なサービスプランを提供しています。無料プランでは、行ベースとカラムベースのストレージそれぞれ最大5 GiBと月間5000万リクエストユニットを無料で利用でき、リスクなしのテスト環境を提供します。アプリケーションの需要が増加するにつれて、スケーラブルプランは自動スケーリングによる拡張機能を提供し、実際の使用量に基づく予測可能な価格設定を約束します。 ```hbsptform { "ui": { "titleLevel": 3 }, "portalId": "4466002", "formId": "62bfb32a-a60a-4f9d-b2dc-1107a93db568", "region": "na1", "sfdcCampaignId": "701RC00000NdFlAYAV" } ```