## Module 3: Columnar Engine for Analytical Workloads ![demo_203-tidb-x-multi-modal.jpg 85%](https://lab-static.pingcap.com/images/2025/11/27/3486dd500a36fa4012367da02f3e59ceebdc7908.jpg) TiFlash is a columnar storage engine for TiDB that significantly accelerates analytical queries by enabling faster scanning and processing of large datasets, making it ideal for complex aggregations and analytical workloads. The columnar storage stays automatically synchronized with the row store in real time, eliminating the need for any manual ETL processes. ### Step 1. Execute analytical queries and review execution plan - In [Terminal 1](ssh://monitor1-ssh#1), connect to TiDB if you are not already connected. ```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/ssl/certs/ca-certificates.crt \ -D ${LAB:SERVERLESS_CLUSTER_DATABASE_NAME} ``` - Check the execution plan for the following aggregation query. Pay attention to the row where the `access object` field is **table:books**. The `task` field shows **cop[tikv]**, it indicates that the **TableFullScan** is being performed on the TiKV row store. ```sql USE ${LAB:SERVERLESS_CLUSTER_DATABASE_NAME}; EXPLAIN SELECT SUM(stock), AVG(stock) FROM books GROUP BY type ORDER BY type; ``` ```output +--------------------------------+---------+-----------+---------------+----------------------------------+ | id | estRows | task | access object | operator info | +--------------------------------+---------+-----------+---------------+----------------------------------+ ... | └─TableFullScan_15 | 100.00 | cop[tikv] | table:books | keep order:false, stats:pseudo | +--------------------------------+---------+-----------+---------------+----------------------------------+ 6 rows in set (0.01 sec) ``` ### Step 2. Enable the data sync from row store to columnar store - Set `books` table to replicate data from TiKV row store to TiFlash columnar store and keep it synchronized. Set the table replica count to 2. ```sql ALTER TABLE books SET TIFLASH REPLICA 2; ``` ```output ... Query OK, 0 rows affected, 1 warning (0.20 sec) ``` - You can check the synchronization status with the following query. ```sql SELECT * FROM information_schema.tiflash_replica WHERE TABLE_SCHEMA = '${LAB:SERVERLESS_CLUSTER_DATABASE_NAME}'; ``` - Wait for synchronization to complete. This happens when both `PROGRESS` and `AVAILABLE` show 1 in the output. For example: ```output +-------------------------+------------+----------+---------------+-----------------+-----------+----------+ | TABLE_SCHEMA | TABLE_NAME | TABLE_ID | REPLICA_COUNT | LOCATION_LABELS | AVAILABLE | PROGRESS | +-------------------------+------------+----------+---------------+-----------------+-----------+----------+ | ${LAB:SERVERLESS_CLUSTER_DATABASE_NAME} | books | 138 | 2 | | 1 | 1 | +-------------------------+------------+----------+---------------+-----------------+-----------+----------+ 2 rows in set (0.01 sec) ``` ### Step 3. Re-examine the execution plan as the table size increases - Gather the table statistics. ```sql ANALYZE TABLE books; ``` - Run the query again and review the execution plan. If the plan has not changed. This is because the books table currently contains too little data, so the cost-based optimizer determines that using the TiKV row store is more efficient than using the TiFlash columnar store for this query. Note that there are very few rows in the books table, the optimizer could also decide to use TiFlash; in this case, the performance would neither be significantly better nor worse. ```sql EXPLAIN SELECT SUM(stock), AVG(stock) FROM books; ``` ```output +--------------------------------+---------+-----------+---------------+----------------------------------------+ | id | estRows | task | access object | operator info | +--------------------------------+---------+-----------+---------------+----------------------------------------+ ... | └─TableFullScan_15 | 100.00 | cop[tikv] | table:books | keep order:false, stats:pseudo | +--------------------------------+---------+-----------+---------------+----------------------------------------+ 6 rows in set (0.01 sec) ``` - Let's make some changes so that the optimizer has to automatically choose TiFlash. Let's double books table for 13 times to make it larger. As a result, the books table will grow to 819200 rows. ```sql INSERT INTO books (title, type, published_at, stock, price) SELECT title, type, published_at, stock, price FROM books; INSERT INTO books (title, type, published_at, stock, price) SELECT title, type, published_at, stock, price FROM books; INSERT INTO books (title, type, published_at, stock, price) SELECT title, type, published_at, stock, price FROM books; INSERT INTO books (title, type, published_at, stock, price) SELECT title, type, published_at, stock, price FROM books; INSERT INTO books (title, type, published_at, stock, price) SELECT title, type, published_at, stock, price FROM books; INSERT INTO books (title, type, published_at, stock, price) SELECT title, type, published_at, stock, price FROM books; INSERT INTO books (title, type, published_at, stock, price) SELECT title, type, published_at, stock, price FROM books; INSERT INTO books (title, type, published_at, stock, price) SELECT title, type, published_at, stock, price FROM books; INSERT INTO books (title, type, published_at, stock, price) SELECT title, type, published_at, stock, price FROM books; INSERT INTO books (title, type, published_at, stock, price) SELECT title, type, published_at, stock, price FROM books; INSERT INTO books (title, type, published_at, stock, price) SELECT title, type, published_at, stock, price FROM books; INSERT INTO books (title, type, published_at, stock, price) SELECT title, type, published_at, stock, price FROM books; INSERT INTO books (title, type, published_at, stock, price) SELECT title, type, published_at, stock, price FROM books; ``` ```output ... Query OK, 409600 rows affected (4.87 sec) Records: 409600 Duplicates: 0 Warnings: 0 ``` - Gather the table statistics. ```sql ANALYZE TABLE books; ``` - Review the execution plan again. The execution plan now uses `mpp[tiflash]` (the columnar store) for **table:books**. This time optimizer determines that using the TiFlash columnar store is more efficient than using the TiKv row store for **TableFullScan**. ```sql EXPLAIN SELECT SUM(stock), AVG(stock) FROM books; ``` ```output +----------------------------------+-----------+--------------+---------------+-------------------------------------------+ | id | estRows | task | access object | operator info | +----------------------------------+-----------+--------------+---------------+-------------------------------------------+ ... | └─TableFullScan_19 | 301456.00 | mpp[tiflash] | table:books | keep order:false, stats:pseudo | +----------------------------------+-----------+--------------+---------------+-------------------------------------------+ 7 rows in set, 2 warnings (0.02 sec) ``` - You can also try running the complex query from Module 1 to see whether the columnar store will be used to execute it. ```sql EXPLAIN SELECT b.type, COUNT(b.id) AS book_count, AVG(r.score) avg_score, v.top_3_rated_title FROM books b JOIN ratings r ON b.id = r.book_id JOIN (SELECT ov.type, GROUP_CONCAT(ov.title) top_3_rated_title FROM (SELECT iv.type, iv.title, RANK() OVER (PARTITION BY iv.type ORDER BY book_avg_score DESC) rank_in_type FROM (SELECT books.type, books.title, AVG(ratings.score) as book_avg_score FROM books as books JOIN ratings as ratings ON books.id = ratings.book_id GROUP BY books.type, books.title) iv ) ov WHERE rank_in_type < 4 GROUP BY ov.type ) v ON v.type = b.type GROUP BY type, top_3_rated_title ORDER BY avg_score DESC; ``` - The result is that the TiFlash columnar store was not used for this query. The main reason is that this query joins the `books` and `ratings` tables, but except for some of the first 100 books, the subsequently inserted books do not have corresponding ratings. As a result, the number of rows participating in the analyze is still too small, so the optimizer determines that using the TiKV row store is more efficient in this case. ```output +------------------------------------------------------------+---------+-----------+---------------+-------------------------------------------------------------------------------------------+ | id | estRows | task | access object | operator info | +------------------------------------------------------------+---------+-----------+---------------+-------------------------------------------------------------------------------------------+ ... | └─TableRangeScan_38 | 849.00 | cop[tikv] | table:b | range: decided by [lab_bgl_migrate_6840006.ratings.book_id], keep order:false | +------------------------------------------------------------+---------+-----------+---------------+-------------------------------------------------------------------------------------------+ 24 rows in set (0.02 sec) ``` - You can add a hint to guide the query optimizer to use TiFlash for the `books` table (aliased as `b`) in this query. However, unless necessary, it is generally not recommended to use hints. ```sql EXPLAIN SELECT /*+ READ_FROM_STORAGE(TIFLASH[b])*/ b.type, COUNT(b.id) AS book_count, AVG(r.score) avg_score, v.top_3_rated_title FROM books b JOIN ratings r ON b.id = r.book_id JOIN (SELECT ov.type, GROUP_CONCAT(ov.title) top_3_rated_title FROM (SELECT iv.type, iv.title, RANK() OVER (PARTITION BY iv.type ORDER BY book_avg_score DESC) rank_in_type FROM (SELECT books.type, books.title, AVG(ratings.score) as book_avg_score FROM books as books JOIN ratings as ratings ON books.id = ratings.book_id GROUP BY books.type, books.title) iv ) ov WHERE rank_in_type < 4 GROUP BY ov.type ) v ON v.type = b.type GROUP BY type, top_3_rated_title ORDER BY avg_score DESC; ``` ```output +-----------------------------------------------------------+-------------+--------------+---------------+-------------------+ | id | estRows | task | access object | operator info | +-----------------------------------------------------------+-------------+--------------+---------------+-------------------+ ... | └─TableFullScan_52 | 1120556.00 | mpp[tiflash] | table:b | keep order:false | +-----------------------------------------------------------+-------------+--------------+---------------+-------------------+ 25 rows in set (0.01 sec) ``` ### Step 4. Compare analytical query performance by using hints - Let's override the optimizer's decision with a hint and compare the performance difference of the same query when executed using row store versus columnar store. ```sql SELECT /*+ READ_FROM_STORAGE(TIFLASH[books])*/ SUM(stock), AVG(stock) FROM books; SELECT /*+ READ_FROM_STORAGE(TIKV[books])*/ SUM(stock), AVG(stock) FROM books; ``` - The first result, which uses the column store, performs better than the second result that uses the row store. ```output ... +----------------+---------------+ | SUM(stock) | AVG(stock) | +----------------+---------------+ | 45180795265024 | 55152337.9700 | +----------------+---------------+ 1 row in set (0.26 sec) ... +----------------+---------------+ | SUM(stock) | AVG(stock) | +----------------+---------------+ | 45180795265024 | 55152337.9700 | +----------------+---------------+ 1 row in set (0.60 sec) ``` ### Step 5. Cleanup - Keep the first 100 books, delete additional rows from `books` table. ```sql DELETE FROM books WHERE id > 100; ALTER TABLE books SET TIFLASH REPLICA 0; ANALYZE TABLE books; ``` ```output Query OK, 819100 rows affected (4.74 sec) ... Query OK, 0 rows affected (1.07 sec) ... Query OK, 0 rows affected, 1 warning (2.21 sec) ``` - In this module, you observed that the query optimizer can automatically choose between row storage and columnar storage based on the specific scenario to better support analytical queries. This demonstrates TiDB's hybrid transactional and analytical processing (HTAP) capability. If you want to learn more about the query optimizer, please visit [Query Optimizer](/labs/dba_307_lab_ff2). Now let's proceed to the next module.