## 模块 3：在上游 MySQL 进行更改并在下游 TiDB 运行实时分析查询 ### 步骤 1. 从远程 Linux 的 monitor1_address 连接到 TiDB server 实例 + 点击这里 - [终端 2](ssh://monitor1-ssh#2) 打开一个新的终端，然后连接到 monitor1_address，接着连接到 TiDB 集群。 + 连接到 TiDB 集群。 ```bash export MYSQL_PS1="tidb:db1> " mysql -h ${HOST_DB1_PRIVATE_IP} -P 4000 -u root ``` ```output tidb:db1> ``` ### 步骤 2. 在下游 TiDB 运行查询 + 查询 TiDB 上 `sp500insight.stock_price_history` 表的行数。首次执行查询耗时不到 0.1 秒。 ```sql SELECT COUNT(*) FROM sp500insight.stock_price_history; ``` ```output +----------+ | COUNT(*) | +----------+ | 1157000 | +----------+ 1 row in set (0.03 sec) ``` ### 步骤 3. 在上游 MySQL 运行 INSERT 操作 + 点击这里 - [终端 3](ssh://monitor1-ssh#3) 打开一个新的终端，连接到 monitor1_address，然后连接到 MySQL server。 + 连接到 MySQL server 节点。 ```bash ssh -i /home/ec2-user/.ssh/pe-class-key-${LAB:REGION_NAME}.pem rocky@${HOST_MYSQL1_PRIVATE_IP} ``` ```output Last login: Thu Aug 1 11:58:47 2024 from 10.90.4.90 [rocky@ip-10-90-1-235 ~]$ ``` + 通过数据库会话连接到 MySQL server。 ```bash mysql -h localhost -P 3306 -u root -pq1w2e3R4_ --connect-expired-password ``` ```output mysql> ``` + 在[终端 3](ssh://monitor1-ssh#3)（MySQL 端），向 `sp500insight.stock_price_history` 表中 `INSERT` a一行新数据。验证行数已从 `1157000` 增加到 `1157001`。 ```sql SELECT COUNT(*) FROM sp500insight.stock_price_history; INSERT INTO sp500insight.stock_price_history VALUES ( '2020-01-12','MMM',60.3188858032226560,77.0199966430664000,67.4499969482421900,91.6699981689453100,91.0899963378906200,3043700); SELECT COUNT(*) FROM sp500insight.stock_price_history; ``` ```output +----------+ | COUNT(*) | +----------+ | 1157000 | +----------+ 1 row in set (0.17 sec) ... Query OK, 1 row affected (0.01 sec) ... +----------+ | COUNT(*) | +----------+ | 1157001 | +----------+ 1 row in set (0.17 sec) ``` ### 步骤 4. 再次在下游 TiDB 运行查询以观察更改 + 回到[终端 2](ssh://monitor1-ssh#2) （TiDB 端），观察来自 TiDB 端的更改结果，该更改已从 MySQL 同步过来。行数应为 `1157001`。 ```sql SELECT COUNT(*) FROM sp500insight.stock_price_history; ``` ```output +----------+ | COUNT(*) | +----------+ | 1157001 | +----------+ 1 row in set (0.04 sec) ``` ### 步骤 5. 观察 TiDB 上的执行计划 + 查看 TiDB 中之前 `COUNT()` 查询的执行计划。注意 `task` 列，其中 `mpp[tiflash]` 表示这些任务将在列式存储层 TiFlash 中利用大规模并行处理。 ```sql EXPLAIN SELECT COUNT(*) FROM sp500insight.stock_price_history; ``` ```output +------------------------------+------------+--------------+---------------------------+----------------------------------------------------------------------+ | id | estRows | task | access object | operator info | +------------------------------+------------+--------------+---------------------------+----------------------------------------------------------------------+ | HashAgg_22 | 1.00 | root | | funcs:count(Column#11)->Column#9 | | └─TableReader_24 | 1.00 | root | | MppVersion: 2, data:ExchangeSender_23 | | └─ExchangeSender_23 | 1.00 | mpp[tiflash] | | ExchangeType: PassThrough | | └─HashAgg_9 | 1.00 | mpp[tiflash] | | funcs:count(sp500insight.stock_price_history.record_date)->Column#11 | | └─TableFullScan_21 | 1157000.00 | mpp[tiflash] | table:stock_price_history | keep order:false | +------------------------------+------------+--------------+---------------------------+----------------------------------------------------------------------+ ``` + 如果时间允许，在 MySQL 端进行更多修改实验，并观察 TiDB 中的结果，以及在 TiDB 中执行其他分析查询。例如： ```sql USE sp500insight; 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.01 sec) ```