--- title: "New Features in TiDB 8.1 and 8.5" version: "v2" status: "GA" description: "In this workshop, we will explore some key new features introduced in TiDB version 8. Our focus will center on six pivotal modules: the TiKV MVCC in-memory engine, Foreign Keys, Global Index for Partitioned Tables, Monitoring Indexes Usage Statistics, TiProxy, and Vector Search." --- # New Features in TiDB 8.1 and 8.5 ## Overview In this workshop, we will explore some key new features introduced in TiDB 8.x. Our focus will center on six pivotal modules: TiProxy, Foreign Keys, Global Unique Indexes for Partitioned Tables, Monitoring Indexes Usage Statistics, Vector Search, and the TiKV MVCC in-memory engine. For a complete list of the new features introduced in TiDB v8, please visit the [TiDB Release Notes](https://docs.pingcap.com/tidb/stable/release-notes). ### Duration + This lab should take you approximately 90 minutes to complete. ### Prerequisites + A baseline understanding of SQL and database management. + Prior experience with MySQL or similar relational databases and any distributed NoSQL databases will be beneficial. + Familiarity with TiDB's [core components](https://docs.pingcap.com/tidb/stable/tidb-architecture/) and [MVCC](https://docs.pingcap.com/tidb/stable/tidb-storage/#mvcc). + Familiarity with [TiUP](https://docs.pingcap.com/tidb/stable/tiup-documentation-guide/) utility. > [!TIP] > Click **Start** to launch the lab environment. > Once the environment is ready, select **Next** on the bottom toolbar to advance to **Module 1**. > And, follow the step-by-step lab guide to connect to the hands-on environment and begin your tasks. ## Module 1: The Load Balancer And Session Manager - TiProxy TiProxy is the official proxy component for TiDB, designed to enhance the database's functionality by providing load balancing, connection persistence, and service discovery. It acts as an intermediary between the client and the TiDB server, ensuring that client connections remain stable and uninterrupted during various maintenance operations such as scaling, rolling upgrades, and restarts. This is particularly useful in scenarios where maintaining continuous client connectivity is crucial, such as during planned server downtime or when balancing workloads across multiple TiDB server instances. In this module, you will deploy a new TiDB cluster along with the TiProxy component and validate its load balancing and session migration functionalities. ### Step 1. Connect to the TiUP control machine + Click here - [Terminal 1](ssh://monitor1-ssh#1) to open a new terminal connected to the virtual machine assigned for your hands-on lab. + The banner after you logged in: ```output , #_ ~\_ ####_ Amazon Linux 2 ~~ \_#####\ ~~ \###| ~~ \#/ ___ ~~ V~' '-> ~~~ / A newer version of Amazon Linux is available! ~~._. _/ _/ _/ Amazon Linux 2023, GA and supported until 2028-03-15. _/m/' https://aws.amazon.com/linux/amazon-linux-2023/ ``` + Get an overview of all server instances IP addresses that had been assigned to you for this hands-on lab. Environment variables prefixes stand for their roles in the TiDB cluster respectively. Note that the IP addresses shown in the `sample_output` will differ from yours. + `HOST_MONITOR` will host Prometheus and Grafana. + `HOST_CM` will host the TiUP Control Machine. + `HOST_PD` for Placement Driver server. + `HOST_DB` for TiDB server. + `HOST_KV` for TiKV server. + `HOST_TIFALSH` for TiFlash server. + `HOST_TIPROXY` for TiProxy server. ```bash cat hosts-env.sh ``` ```output export HOST_MONITOR1_PRIVATE_IP=10.90.4.40 export HOST_MONITOR1_PUBLIC_IP=35.166.47.245 export HOST_CM_PRIVATE_IP=10.90.4.40 export HOST_CM_PUBLIC_IP=35.166.47.245 export HOST_PD1_PRIVATE_IP=10.90.2.51 export HOST_PD1_PUBLIC_IP=52.27.45.10 export HOST_DB1_PRIVATE_IP=10.90.2.14 export HOST_DB1_PUBLIC_IP=44.244.238.105 export HOST_DB2_PRIVATE_IP=10.90.2.43 export HOST_DB2_PUBLIC_IP=34.219.136.55 export HOST_KV1_PRIVATE_IP=10.90.1.93 export HOST_KV1_PUBLIC_IP=52.12.133.66 export HOST_KV2_PRIVATE_IP=10.90.1.30 export HOST_KV2_PUBLIC_IP=18.236.119.16 export HOST_KV3_PRIVATE_IP=10.90.1.208 export HOST_KV3_PUBLIC_IP=35.90.161.51 export HOST_TIFLASH1_PRIVATE_IP=10.90.4.243 export HOST_TIFLASH1_PUBLIC_IP=34.210.114.26 export HOST_TIPROXY1_PRIVATE_IP=10.90.4.154 ``` ### Step 2. Create a new TiDB cluster equipped with TiProxy + Check the provided topology file `topology-with-tiproxy.yaml`, which is used to deploy the TiDB cluster. Pay attention to the TiProxy settings (`security.auto-tls: true`, `graceful-wait-before-shutdown: 10` under TiDB server configurations, and `tiproxy_servers`) in the topology file. Note that the IP addresses shown in the `sample_output` will differ from yours. ```bash cat topology-with-tiproxy.yaml ``` ```output ... server_configs: ... tidb: security.auto-tls: true graceful-wait-before-shutdown: 10 ... tiproxy_servers: - host: 10.90.4.113 ``` + The commands in the following code block will execute a series of operations in sequence. Which are: 1. Change [TiUP mirror](https://docs.pingcap.com/tidb/stable/production-deployment-using-tiup) setting, 2. Check system settings, 3. Fix the failed checks, 4. Deploy the TiDB cluster, and finally 5. Start the TiDB cluster. It takes approximately 3 minutes to complete. ```bash tiup mirror set tidb-community-server-tidb_version-linux-amd64 tiup cluster check ./topology-with-tiproxy.yaml \ --user ec2-user \ -i /home/ec2-user/.ssh/pe-class-key-oregon.pem \ --apply tiup cluster deploy tidb-demo tidb_version ./topology-with-tiproxy.yaml \ --user ec2-user \ -i /home/ec2-user/.ssh/pe-class-key-oregon.pem \ --yes tiup cluster start tidb-demo sleep 5; tiup cluster display tidb-demo ``` ```output Successfully set mirror to /home/ec2-user/tidb-community-server-tidb_version-linux-amd64 /home/ec2-user/tidb-community-server-tidb_version-linux-amd64 ... Cluster type: tidb Cluster name: tidb-demo Cluster version: v8.5.1 Deploy user: ec2-user SSH type: builtin Dashboard URL: http://52.27.45.10:2379/dashboard Grafana URL: http://10.90.4.40:3000 ID Role Host Ports OS/Arch Status Data Dir Deploy Dir -- ---- ---- ----- ------- ------ -------- ---------- 10.90.4.40:9093 alertmanager 10.90.4.40 9093/9094 linux/x86_64 Up /tidb-data/alertmanager-9093 /tidb-deploy/alertmanager-9093 10.90.4.40:3000 grafana 10.90.4.40 3000 linux/x86_64 Up - /tidb-deploy/grafana-3000 10.90.2.51:2379 pd 10.90.2.51 2379/2380 linux/x86_64 Up|L /tidb-data/pd-2379 /tidb-deploy/pd-2379 10.90.4.40:9090 prometheus 10.90.4.40 9090/12020 linux/x86_64 Up /tidb-data/prometheus-9090 /tidb-deploy/prometheus-9090 10.90.2.14:4000 tidb 10.90.2.14 4000/10080 linux/x86_64 Up - /tidb-deploy/tidb-4000 10.90.2.43:4000 tidb 10.90.2.43 4000/10080 linux/x86_64 Up - /tidb-deploy/tidb-4000 10.90.4.243:9000 tiflash 10.90.4.243 9000/3930/20170/20292/8234/8123 linux/x86_64 Down /tidb-data/tiflash-9000 /tidb-deploy/tiflash-9000 10.90.1.208:20160 tikv 10.90.1.208 20160/20180 linux/x86_64 Up /tidb-data/tikv-20160 /tidb-deploy/tikv-20160 10.90.1.30:20160 tikv 10.90.1.30 20160/20180 linux/x86_64 Up /tidb-data/tikv-20160 /tidb-deploy/tikv-20160 10.90.1.93:20160 tikv 10.90.1.93 20160/20180 linux/x86_64 Up /tidb-data/tikv-20160 /tidb-deploy/tikv-20160 10.90.4.154:6000 tiproxy 10.90.4.154 6000/3080 linux/x86_64 Up - /tidb-deploy/tiproxy-6000 Total nodes: 11 ``` + Note that the `tiproxy` component is also listed in the cluster display output. The Self-Managed cluster consists of one TiProxy instance and two TiDB server instances. Additionally, although we typically use at least three PD servers, only one PD server is used here, as this hands-on lab does not cover PD-related topics. ### Step 3. Verify the load balancing functionality + By default, TiProxy instances listen on port 6000 instead of port 4000. Let's connect to TiDB server instance via TiProxy. ```bash mysql -h ${HOST_TIPROXY1_PRIVATE_IP} --port 6000 -u root ``` ```output mysql> ``` + Create a table named `test.tiproxy_test` and exit the connection. we will use it prove TiProxy's connection load balancing ability. ```sql DROP TABLE IF EXISTS test.tiproxy_test; CREATE TABLE test.tiproxy_test (id INT AUTO_INCREMENT PRIMARY KEY, tidb_server_instance VARCHAR(30)); EXIT ``` ```output Query OK, 0 rows affected, 1 warning (0.00 sec) Query OK, 0 rows affected (0.06 sec) Bye ``` + Generate 50 database connections via TiProxy, in each connection, it will insert a new row, and then close the connection. Note that the `tidb_server_instance` column in `test.tiproxy_test` table will reveal the TiDB server instance's identity (in the format of **TIDB_SERVER_IP_ADDRESS:10080**) that had accepted the connection and processed the insertion DML. ```bash cat > insert_one_row.sql < insert_one_row.sh < `tidb-demo` > `tidb-demo-TiProxy-Summary` > `Balance`. Observe the `Backend Connections` panel, each TiDB server should has 2 connections. ![90%](https://lab-static.pingcap.com/quick-demo/TiProxyBalance_2x2.png) + Change the `Refresh time` to `10s`. Keep the `Balance` panel open in the browser, and keep the Java program running. + Click here - [Terminal 2](ssh://monitor1-ssh#2) to open a new terminal connected to the virtual machine assigned for your hands-on lab. + The banner after you logged in: ```output , #_ ~\_ ####_ Amazon Linux 2 ~~ \_#####\ ~~ \###| ~~ \#/ ___ ~~ V~' '-> ~~~ / A newer version of Amazon Linux is available! ~~._. _/ _/ _/ Amazon Linux 2023, GA and supported until 2028-03-15. _/m/' https://aws.amazon.com/linux/amazon-linux-2023/ ``` + Scale in the SQL layer by removing the TiDB server with the IP address ${HOST_DB2_PRIVATE_IP} from the cluster. Once completed, there is only 1 TiDB server in the cluster. ```bash tiup cluster scale-in tidb-demo --node ${HOST_DB2_PRIVATE_IP}:4000 -y sleep 2 tiup cluster display tidb-demo ``` ```output ... Stopping component tidb ... Scaled cluster `tidb-demo` in successfully ... Cluster type: tidb Cluster name: tidb-demo Cluster version: v8.5.1 Deploy user: ec2-user SSH type: builtin Dashboard URL: http://13.115.186.238:2379/dashboard Grafana URL: http://10.90.4.53:3000 ID Role Host Ports OS/Arch Status Data Dir Deploy Dir -- ---- ---- ----- ------- ------ -------- ---------- 10.90.4.53:9093 alertmanager 10.90.4.53 9093/9094 linux/x86_64 Up /tidb-data/alertmanager-9093 /tidb-deploy/alertmanager-9093 10.90.4.53:3000 grafana 10.90.4.53 3000 linux/x86_64 Down - /tidb-deploy/grafana-3000 10.90.3.19:2379 pd 10.90.3.19 2379/2380 linux/x86_64 Up|L /tidb-data/pd-2379 /tidb-deploy/pd-2379 10.90.4.53:9090 prometheus 10.90.4.53 9090/12020 linux/x86_64 Up /tidb-data/prometheus-9090 /tidb-deploy/prometheus-9090 10.90.1.202:4000 tidb 10.90.1.202 4000/10080 linux/x86_64 Up - /tidb-deploy/tidb-4000 10.90.4.75:9000 tiflash 10.90.4.75 9000/3930/20170/20292/8234/8123 linux/x86_64 Up /tidb-data/tiflash-9000 /tidb-deploy/tiflash-9000 10.90.3.10:20160 tikv 10.90.3.10 20160/20180 linux/x86_64 Up /tidb-data/tikv-20160 /tidb-deploy/tikv-20160 10.90.3.207:20160 tikv 10.90.3.207 20160/20180 linux/x86_64 Up /tidb-data/tikv-20160 /tidb-deploy/tikv-20160 10.90.3.208:20160 tikv 10.90.3.208 20160/20180 linux/x86_64 Up /tidb-data/tikv-20160 /tidb-deploy/tikv-20160 10.90.4.56:6000 tiproxy 10.90.4.56 6000/3080 linux/x86_64 Up - /tidb-deploy/tiproxy-6000 Total nodes: 10 ``` + On [终端 1](ssh://monitor1-ssh#1) , the mini Java program continues running without interruption. ```output ... Inserting 1295 by worker 4 Inserting 1295 by worker 3 Inserting 1295 by worker 1 Inserting 1295 by worker 2 Inserting 1296 by worker 4 Inserting 1296 by worker 3 ... ``` + Check the Grafana TiProxy `Balance` panel from your browser. 4 connections are now on single TiDB server. 2 connections had been migrated. ![90%](https://lab-static.pingcap.com/quick-demo/TiProxyBalance_4x0.png) + Switch to [终端 2](ssh://monitor1-ssh#2) , scale out the SQL layer by adding a TiDB server to the cluster. Once completed, there are 2 TiDB servers in the cluster. ```bash tiup cluster scale-out tidb-demo scale-out-tidb.yaml \ --user ec2-user \ -i ~/.ssh/pe-class-key-oregon.pem \ -y tiup cluster display tidb-demo ``` ```output ... Deploy TiDB instance ... Scaled cluster `tidb-demo` out successfully ... Cluster type: tidb Cluster name: tidb-demo Cluster version: v8.5.1 Deploy user: ec2-user SSH type: builtin Dashboard URL: http://13.115.186.238:2379/dashboard Grafana URL: http://10.90.4.53:3000 ID Role Host Ports OS/Arch Status Data Dir Deploy Dir -- ---- ---- ----- ------- ------ -------- ---------- 10.90.4.53:9093 alertmanager 10.90.4.53 9093/9094 linux/x86_64 Up /tidb-data/alertmanager-9093 /tidb-deploy/alertmanager-9093 10.90.4.53:3000 grafana 10.90.4.53 3000 linux/x86_64 Up - /tidb-deploy/grafana-3000 10.90.3.19:2379 pd 10.90.3.19 2379/2380 linux/x86_64 Up|L /tidb-data/pd-2379 /tidb-deploy/pd-2379 10.90.4.53:9090 prometheus 10.90.4.53 9090/12020 linux/x86_64 Up /tidb-data/prometheus-9090 /tidb-deploy/prometheus-9090 10.90.1.202:4000 tidb 10.90.1.202 4000/10080 linux/x86_64 Up - /tidb-deploy/tidb-4000 10.90.1.233:4000 tidb 10.90.1.233 4000/10080 linux/x86_64 Up - /tidb-deploy/tidb-4000 10.90.4.75:9000 tiflash 10.90.4.75 9000/3930/20170/20292/8234/8123 linux/x86_64 Up /tidb-data/tiflash-9000 /tidb-deploy/tiflash-9000 10.90.3.10:20160 tikv 10.90.3.10 20160/20180 linux/x86_64 Up /tidb-data/tikv-20160 /tidb-deploy/tikv-20160 10.90.3.207:20160 tikv 10.90.3.207 20160/20180 linux/x86_64 Up /tidb-data/tikv-20160 /tidb-deploy/tikv-20160 10.90.3.208:20160 tikv 10.90.3.208 20160/20180 linux/x86_64 Up /tidb-data/tikv-20160 /tidb-deploy/tikv-20160 10.90.4.56:6000 tiproxy 10.90.4.56 6000/3080 linux/x86_64 Up - /tidb-deploy/tiproxy-6000 Total nodes: 11 ``` + Return to [终端 1](ssh://monitor1-ssh#1) again, you will find that the mini Java program continues running without interruption. ```output ... Inserting 1707 by worker 1 Inserting 1707 by worker 3 Inserting 1706 by worker 4 Inserting 1708 by worker 2 Inserting 1708 by worker 1 Inserting 1708 by worker 3 Inserting 1707 by worker 4 ... ``` + Again, check the Grafana TiProxy `Balance` panel from your browser. Each TiDB server has 2 connections. 2 connections had been migrated. ![90%](https://lab-static.pingcap.com/quick-demo/TiProxyBalance_2x2_2.png) + That concludes the session/connection migration ability of TiProxy. For supporting the planned database online rolling upgrade by using TiProxy, we have another standalone hands-on lab to demonstrate this capability please visit [Database Zero Downtime Online Upgrade with TiProxy](https://labs.tidb.io/labs/demo_005) for more detail. + Go to [终端 1](ssh://monitor1-ssh#1) , press **CTRL-C** to stop the Java workload. Then, proceed to the next module. ## Module 2: Foreign Key (v8.5.0) Foreign keys are common in traditional RDBMS, but they are rarely seen in distributed databases. Many distributed databases fail to implement foreign keys, which presents a challenge for maintaining in-database data integrity. However, as a distributed database, TiDB supports foreign keys. Foreign Key constraints in TiDB allow you to define relationships between tables, utilizing these relationships to enforce data integrity. At a basic level, a Foreign Key constraint ensures that a set of columns in a child table corresponds to a set of columns in a parent table, prohibiting actions that might break the link. Consider a table of orders and a table of customers; a Foreign Key constraint on the orders table could ensure that every order corresponds to an existing customer, thus maintaining data accuracy and integrity. ### Step 1. Create a bookshop schema and load data via TiDB SQL layer + On [终端 1](ssh://monitor1-ssh#1) , connect to TiDB server instance. ```bash mysql -h ${HOST_TIPROXY1_PRIVATE_IP} --port 6000 -u root ``` ```output mysql> ``` + Run the following command in `mysql> ` prompt to create the database objects and load data for our book shop schema. The script creates a schema called `bookshop`. ```sql SOURCE bookshop_demo.sql SHOW TABLES; ``` ```output Query OK, 0 rows affected (0.08 sec) ... +--------------------+ | Tables_in_bookshop | +--------------------+ | authors | | book_authors | | books | | orders | | ratings | | users | +--------------------+ 6 rows in set (0.00 sec) ``` + The following is an excerpt of the contents of the `bookshop_demo.sql` script in the previous step, showing the creation of the tables and indexes for our book shop application. You don't need to run these statements now, since they were already executed, but you can see how we've modeled our data for our application. It's worth noting that you'll find that except for `AUTO_RANDOM`, all the declarations are just like those used in MySQL. ```output CREATE TABLE `authors` ( `id` bigint(20) AUTO_RANDOM, `name` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL, `gender` tinyint(1) DEFAULT NULL, `birth_year` smallint(6) DEFAULT NULL, `death_year` smallint(6) DEFAULT NULL, PRIMARY KEY (`id`) ); CREATE TABLE `book_authors` ( `book_id` bigint(20) NOT NULL, `author_id` bigint(20) NOT NULL, PRIMARY KEY (`book_id`, `author_id`) ); CREATE TABLE `books` ( `id` bigint AUTO_RANDOM, `title` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL, `type` enum( 'Magazine', 'Novel', 'Life', 'Arts', 'Comics', 'Education & Reference', 'Humanities & Social Sciences', 'Science & Technology', 'Kids', 'Sports' ) COLLATE utf8mb4_unicode_ci NOT NULL, `published_at` datetime NOT NULL, `stock` int DEFAULT '0', `price` decimal(15, 2) DEFAULT '0.0', PRIMARY KEY (`id`) ) PARTITION BY HASH (`id`) PARTITIONS 8; CREATE TABLE `orders` ( `id` bigint(20) NOT NULL, `book_id` bigint(20) NOT NULL, `user_id` bigint(20) NOT NULL, `quality` tinyint(4) NOT NULL, `ordered_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (`id`), KEY `orders_book_id_idx` (`book_id`) ); CREATE TABLE `ratings` ( `book_id` bigint(20) NOT NULL, `user_id` bigint(20) NOT NULL, `score` tinyint(4) NOT NULL, `rated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP, UNIQUE KEY `uniq_book_user_idx` (`book_id`, `user_id`), PRIMARY KEY (`book_id`, `user_id`) ); CREATE TABLE `users` ( `id` bigint(20) AUTO_RANDOM, `balance` decimal(15, 2) DEFAULT '0.0', `nickname` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL, PRIMARY KEY (`id`), UNIQUE KEY `nickname` (`nickname`) ); ``` + You can explore the schema and describe the objects in the database with mysql commands, just as you would with any MySQL database. ```sql DESC bookshop.books; ``` ```output +--------------+-----------------------------------------------------------------------------------------------------------------------------------------------+------+------+---------+-------+ | Field | Type | Null | Key | Default | Extra | +--------------+-----------------------------------------------------------------------------------------------------------------------------------------------+------+------+---------+-------+ | id | bigint(20) | NO | PRI | NULL | | | title | varchar(100) | NO | | NULL | | | type | enum('Magazine','Novel','Life','Arts','Comics','Education & Reference','Humanities & Social Sciences','Science & Technology','Kids','Sports') | NO | | NULL | | | published_at | datetime | NO | | NULL | | | stock | int(11) | YES | | 0 | | | price | decimal(15,2) | YES | | 0.0 | | +--------------+-----------------------------------------------------------------------------------------------------------------------------------------------+------+------+---------+-------+ 6 rows in set (0.00 sec) ``` + In the `mysql> ` prompt, run the following query to see a few books records. ```sql SELECT * FROM bookshop.books ORDER BY title LIMIT 3; ``` ```output +----+-------------------------------+------------------------------+---------------------+--------+-------+ | id | title | type | published_at | stock | price | +----+-------------------------------+------------------------------+---------------------+--------+-------+ | 43 | (I've Got a Gal In) Kalamazoo | Sports | 2006-05-22 05:19:49 | 231321 | 85.72 | | 88 | (Let Me Be Your) Teddy Bear | Sports | 2003-04-08 08:37:52 | 140 | 22.14 | | 40 | (You Keep Me) Hangin' On | Humanities & Social Sciences | 2005-02-12 05:29:04 | 163 | 32.72 | +----+-------------------------------+------------------------------+---------------------+--------+-------+ 3 rows in set (0.05 sec) ``` ### Step 2. Enforce the referential integrity with a foreign key + As in our example bookshop schema, we didn't add the foreign keys during the schema creation in module 1. Now, we need to enforce the entity relationship illustrated in the following diagram. ```mermaid --- title: Book Authors Example --- erDiagram AUTHORS ||--|{ BOOK_AUTHORS : references AUTHORS { BIGINT id PK VARCHAR name TINYINT gender SMALLINT birth_year SMALLINT death_year } BOOK_AUTHORS { BIGINT book_id PK BIGINT author_id PK,FK } ``` + Execute the following command to enforce the referencing constraint between `book_authors` and `authors` on `book_authors.auth_id` and `authors.id`. Let's execute it on [Terminal 1](ssh://monitor1-ssh#1) under the `mysql> ` prompt. ```sql ALTER TABLE bookshop.book_authors ADD CONSTRAINT book_author_fk FOREIGN KEY (author_id) REFERENCES bookshop.authors (id); ``` ```output Query OK, 0 rows affected (0.10 sec) ``` + Try to add a record into `book_authors` which violates the foreign key constraint you just declared. An **ERROR** is expected because author with `auth_id` 99999999 is not exist. ```sql INSERT INTO bookshop.book_authors (book_id, author_id) VALUES (100, 99999999); ``` ```output ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`bookshop`.`book_authors`, CONSTRAINT `book_author_fk` FOREIGN KEY (`author_id`) REFERENCES `authors` (`id`)) ``` + Try to add a record into `book_authors` which consistent with the foreign key constraint you just declared. An successful insertion is expected. For example, declaring Dr. Lindsey Welch II (an existing author with author id 1) is also an author for the book "Till The End of Time" (an existing book with book id 100). ```sql INSERT INTO bookshop.book_authors (book_id, author_id) VALUES (100, 1); ``` ```output Query OK, 1 row affected (0.00 sec) ``` + At this point, you have experienced the foreign key constraint capability on a distributed database - TiDB. Keep the mysql client connection open, and proceed to the next module. ## Module 3: Global Unique Indexes for Partitioned Tables (v8.5.0) Similar to indexes, table partitions in databases are data structures designed to accelerate data retrieval operations. When a query statement is executed, table partitions enable the database to quickly find data that meets the query conditions. This leads to time savings as opposed to scanning the entire table sequentially (`TableFullScan`), thus enhancing query efficiency. Before the introduction of global indexes, TiDB supports local indexes for each partition, leading to a limitation that primary keys and unique keys had to include the partition key to ensure data uniqueness. Additionally, when querying data across multiple partitions, TiDB needed to scan the data of each partition to return results. To address above issues, TiDB introduces the global indexes feature. A global index covers the data of the entire table with a single index, allowing primary keys and unique keys to maintain global uniqueness without including all partition keys. Moreover, global indexes can access index data across multiple partitions in a single operation, significantly improving query performance for non-partitioned keys instead of looking up in one local index for each partition. ### Step 1. Review the characteristics of a partitioned table + In module 1, we already created a partitioned table - `books`. It is partitioned on column `id` with 8 hash partitions. + Verify the table partitions by executing following SQL commands. Note the `PARTITION BY` clause in the output. ```sql SHOW CREATE TABLE books\G ``` ```output *************************** 1. row *************************** Table: books Create Table: CREATE TABLE `books` ( `id` bigint NOT NULL /*T![auto_rand] AUTO_RANDOM(5) */, `title` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL, `type` enum('Magazine','Novel','Life','Arts','Comics','Education & Reference','Humanities & Social Sciences','Science & Technology','Kids','Sports') COLLATE utf8mb4_unicode_ci NOT NULL, `published_at` datetime NOT NULL, `stock` int DEFAULT '0', `price` decimal(15,2) DEFAULT '0.0', PRIMARY KEY (`id`) /*T![clustered_index] CLUSTERED */ ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin /*T![auto_rand_base] AUTO_RANDOM_BASE=30002 */ PARTITION BY HASH (`id`) PARTITIONS 8 1 row in set (0.00 sec) ``` ```sql SELECT table_name, partition_name, partition_method FROM information_schema.partitions WHERE table_name='books'; ``` ```output +------------+----------------+------------------+ | table_name | partition_name | partition_method | +------------+----------------+------------------+ | books | p0 | HASH | | books | p1 | HASH | | books | p2 | HASH | | books | p3 | HASH | | books | p4 | HASH | | books | p5 | HASH | | books | p6 | HASH | | books | p7 | HASH | +------------+----------------+------------------+ 8 rows in set (0.01 sec) ``` + Because `books` table is partitioned by 8 partitions, it at least has 8 TiKV regions for storing rows. ```sql SHOW TABLE books REGIONS; ``` ```output +-----------+-----------+---------+-----------+-----------------+---------------+------------+---------------+------------+----------------------+------------------+------------------------+------------------+ | REGION_ID | START_KEY | END_KEY | LEADER_ID | LEADER_STORE_ID | PEERS | SCATTERING | WRITTEN_BYTES | READ_BYTES | APPROXIMATE_SIZE(MB) | APPROXIMATE_KEYS | SCHEDULING_CONSTRAINTS | SCHEDULING_STATE | +-----------+-----------+---------+-----------+-----------------+---------------+------------+---------------+------------+----------------------+------------------+------------------------+------------------+ | 286 | t_119_ | t_120_ | 287 | 1 | 287, 288, 289 | 0 | 0 | 1301 | 1 | 8 | | | | 290 | t_120_ | t_121_ | 291 | 1 | 291, 292, 293 | 0 | 39 | 1321 | 1 | 0 | | | | 294 | t_121_ | t_122_ | 295 | 1 | 295, 296, 297 | 0 | 0 | 1329 | 1 | 44 | | | | 298 | t_122_ | t_123_ | 299 | 1 | 299, 300, 301 | 0 | 39 | 1332 | 1 | 8 | | | | 302 | t_123_ | t_124_ | 305 | 2 | 303, 304, 305 | 0 | 39 | 1374 | 1 | 125 | | | | 306 | t_124_ | t_125_ | 307 | 1 | 307, 308, 309 | 0 | 0 | 1278 | 1 | 2 | | | | 310 | t_125_ | t_126_ | 313 | 2 | 311, 312, 313 | 0 | 0 | 1231 | 1 | 14 | | | | 314 | t_126_ | t_128_ | 315 | 1 | 315, 316, 317 | 0 | 39 | 1248 | 1 | 9 | | | +-----------+-----------+---------+-----------+-----------------+---------------+------------+---------------+------------+----------------------+------------------+------------------------+------------------+ 8 rows in set (0.02 sec) ``` + Since `books` table is hash partitioned on its primary key - `id`, the rows should be approximately evenly distributed across 8 partitions. Execute the following query to confirm that. ```sql SELECT 'P0_COUNT', COUNT(1) FROM books PARTITION (p0) UNION ALL SELECT 'P1_COUNT', COUNT(1) FROM books PARTITION (p1) UNION ALL SELECT 'P2_COUNT', COUNT(1) FROM books PARTITION (p2) UNION ALL SELECT 'P3_COUNT', COUNT(1) FROM books PARTITION (p3) UNION ALL SELECT 'P4_COUNT', COUNT(1) FROM books PARTITION (p4) UNION ALL SELECT 'P5_COUNT', COUNT(1) FROM books PARTITION (p5) UNION ALL SELECT 'P6_COUNT', COUNT(1) FROM books PARTITION (p6) UNION ALL SELECT 'P7_COUNT', COUNT(1) FROM books PARTITION (p7) ORDER BY 1; ``` ```output +----------+----------+ | P0_COUNT | COUNT(1) | +----------+----------+ | P0_COUNT | 12 | | P1_COUNT | 13 | | P2_COUNT | 13 | | P3_COUNT | 13 | | P4_COUNT | 13 | | P5_COUNT | 12 | | P6_COUNT | 12 | | P7_COUNT | 12 | +----------+----------+ 8 rows in set (0.00 sec) ``` ### Step 2. Add a global secondary index on a partitioned table + Use the following command to try to create a global unique index on table `books`. For example, you want to ensure that the book title and type combinations are unique. An **ERROR** is expected. ```sql CREATE UNIQUE INDEX book_title_uk ON bookshop.books (title, type); ``` ```output ERROR 8264 (HY000): Global Index is needed for index 'book_title_uk', since the unique index is not including all partitioning columns, and GLOBAL is not given as IndexOption ``` + The error message shows that you want to create a local unique index (no `GLOBAL` keyword is given) and you did not include the partitioning column (local unique index must include the partitioning column(s), which is `id` for our case), TiDB can not comply the execution. To solve this issue, you can do one of the followings: > Create a **local unique index** on `title` and `type`, since it is a local index you have to additionally include column `id`, despite you don't actually need the `id` column in the index. OR > Create a **global unique index** on `title` and `type`, no need to include `id`, it is a single column index. **This is exactly the new feature we are discussing in the module.** + Let's try to create the global unique index again by adding the `GLOBAL` keyword, this time no error is expected. ```sql CREATE UNIQUE INDEX book_title_uk ON bookshop.books (title, type) GLOBAL; ``` ```output Query OK, 0 rows affected (3.06 sec) ``` + Verity that your index is `GLOBAL` by querying `information_schema.tidb_indexes` table and check the `is_global` column in the output. ```sql SELECT key_name, seq_in_index, column_name, clustered, is_global FROM information_schema.tidb_indexes WHERE table_name='books'; ``` ```output +---------------+--------------+-------------+-----------+-----------+ | key_name | seq_in_index | column_name | clustered | is_global | +---------------+--------------+-------------+-----------+-----------+ | PRIMARY | 1 | id | YES | 0 | | book_title_uk | 1 | title | NO | 1 | | book_title_uk | 2 | type | NO | 1 | +---------------+--------------+-------------+-----------+-----------+ 3 rows in set (0.00 sec) ``` ### Step 3. Typical scenarios for global unique indexes + Keep in mind that the combination of `title` and `type` is unique in `books` table. + Let's drop the global unique index `book_title_uk` and run the query explanation. Note that all partitions are scanned for the result. Pay attention to the `partition:all` under the `access object` column in the explain output. In case our dataset is huge, this operation is expensive regarding IO. ```sql DROP INDEX book_title_uk ON bookshop.books; EXPLAIN SELECT stock, price FROM bookshop.books WHERE type = 'Science & Technology' AND title = 'Play That Funky Music'; ``` ```output Query OK, 0 rows affected (0.14 sec) +---------------------------+---------+-----------+---------------+----------------------------------------------------------------------------------------------------+ | id | estRows | task | access object | operator info | +---------------------------+---------+-----------+---------------+----------------------------------------------------------------------------------------------------+ | TableReader_11 | 0.00 | root | partition:all | data:Projection_5 | | └─Projection_5 | 0.00 | cop[tikv] | | bookshop.books.stock, bookshop.books.price | | └─Selection_10 | 0.00 | cop[tikv] | | eq(bookshop.books.title, "Play That Funky Music"), eq(bookshop.books.type, "Science & Technology") | | └─TableFullScan_9 | 100.00 | cop[tikv] | table:books | keep order:false, stats:pseudo | +---------------------------+---------+-----------+---------------+----------------------------------------------------------------------------------------------------+ 4 rows in set (0.00 sec) ``` + Let's add a **local** unique index on the `(type, title)`, and run the query explanation again. This time we find out that `IndexRangeScan` is used on all partitions. The good news is indexes are leveraged to speed up the query, the bad news is all 8 local indexes on 8 partitions are range scanned for the results. For the `type` and `title` unique combination, this is not necessary. Pay attention to the `partition:all` under the `access object` column on the row of `IndexLookUp` in the explain output. It's better than before, but still not the best solution for our case. ```sql CREATE UNIQUE INDEX book_title_idx ON bookshop.books (type, title, id); EXPLAIN SELECT stock, price FROM bookshop.books WHERE type = 'Science & Technology' AND title = 'Play That Funky Music'; ``` ```output Query OK, 0 rows affected (2.98 sec) +---------------------------------+---------+-----------+----------------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | id | estRows | task | access object | operator info | +---------------------------------+---------+-----------+----------------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Projection_4 | 0.00 | root | | bookshop.books.stock, bookshop.books.price | | └─IndexLookUp_8 | 0.00 | root | partition:all | | | ├─IndexRangeScan_6(Build) | 0.00 | cop[tikv] | table:books, index:book_title_idx(type, title, id) | range:["Science & Technology" "\x0f\xa7\x0f.\x0e3\x10^\x02\t\x10\x02\x0e\xe1\x0e3\x10\x02\x02\t\x0e\xb9\x10\x1f\x0fd\x0f!\x10^\x02\t\x0f[\x10\x1f\x0f\xea\x0e\xfb\x0e`","Science & Technology" "\x0f\xa7\x0f.\x0e3\x10^\x02\t\x10\x02\x0e\xe1\x0e3\x10\x02\x02\t\x0e\xb9\x10\x1f\x0fd\x0f!\x10^\x02\t\x0f[\x10\x1f\x0f\xea\x0e\xfb\x0e`"], keep order:false, stats:pseudo | | └─TableRowIDScan_7(Probe) | 0.00 | cop[tikv] | table:books | keep order:false, stats:pseudo | +---------------------------------+---------+-----------+----------------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 4 rows in set (0.00 sec) ``` + Let's change the **local** index to **global** unique index. ```sql DROP INDEX book_title_idx ON bookshop.books; CREATE UNIQUE INDEX book_title_uk ON bookshop.books (title, type) GLOBAL; ``` ```output Query OK, 0 rows affected (0.17 sec) Query OK, 0 rows affected (2.38 sec) ``` + Run the following query to verity that the global index is now working. You will see that `index:book_title_uk` object is accessed under `access object` column in the explain output. And the operation is a `Point_Get`. No `IndexLookUp` scans on all partitions, neither `TableFullScan` on all partitions, just one `Point_Get`, which is the most efficient query in this module, especially when the dataset is huge. ```sql EXPLAIN SELECT stock, price FROM bookshop.books WHERE type = 'Science & Technology' AND title = 'Play That Funky Music'; ``` ```output +-------------+---------+------+-----------------------------------------------+---------------+ | id | estRows | task | access object | operator info | +-------------+---------+------+-----------------------------------------------+---------------+ | Point_Get_1 | 1.00 | root | table:books, index:book_title_uk(title, type) | | +-------------+---------+------+-----------------------------------------------+---------------+ 1 row in set (0.00 sec) ``` + At this point, you have learned the typical scenario for using the global unique index. Keep the mysql client connection open, and proceed to the next module. ## Module 4: Monitoring Index Usage Statistics Proper index design is a crucial prerequisite to maintaining database performance. TiDB v8.0.0 introduces the `information_schema.tidb_index_usage` table and the `sys.schema_unused_indexes` view to provide usage statistics of indexes. This feature helps you assess the efficiency of indexes in the database and optimize the index design. The following supplemental information from the [TiDB SQL Tuning](https://www.pingcap.com/education/course-tidb-sql-tuning/) training is a prerequisite knowledge for working with this module. Show it here for your convenience. ![70%](https://lab-static.pingcap.com/quick-demo/SelCarCost.png) ### Step 1. Verify the indexes efficiency by querying tidb_index_usage + Get the `book_title_uk` index usage baseline by querying `tidb_index_usage` table. Note that statistics are all zeros, because the `EXPLAIN` commands in the previous module did not actually execute the queries. ```sql SELECT table_schema, table_name, index_name, query_total, rows_access_total, last_access_time, percentage_access_0, percentage_access_0_1, percentage_access_1_10, percentage_access_10_20, percentage_access_20_50, percentage_access_50_100, percentage_access_100 FROM information_schema.tidb_index_usage WHERE index_name='book_title_uk'\G ``` ```output *************************** 1. row *************************** table_schema: bookshop table_name: books index_name: book_title_uk query_total: 0 rows_access_total: 0 last_access_time: NULL percentage_access_0: 0 percentage_access_0_1: 0 percentage_access_1_10: 0 percentage_access_10_20: 0 percentage_access_20_50: 0 percentage_access_50_100: 0 percentage_access_100: 0 1 row in set (0.01 sec) ``` + Gather the CBO statistics. ```sql ANALYZE TABLE bookshop.books; ``` ```output Query OK, 0 rows affected, 8 warnings (0.69 sec) ``` + Execute a query that might use the index `book_title_uk`. ```sql SELECT stock, price FROM bookshop.books WHERE type = 'Science & Technology' AND title = 'Play That Funky Music'; ``` ```output +-------+--------+ | stock | price | +-------+--------+ | 147 | 185.76 | +-------+--------+ 1 row in set (0.00 sec) ``` + Check the usage statistics again. Note the `query_total`, `rows_access_total`, and `percentage_access_1_10` will increase. ```sql SELECT table_schema, table_name, index_name, query_total, rows_access_total, last_access_time, percentage_access_0, percentage_access_0_1, percentage_access_1_10, percentage_access_10_20, percentage_access_20_50, percentage_access_50_100, percentage_access_100 FROM information_schema.tidb_index_usage WHERE index_name='book_title_uk'\G ``` ```output *************************** 1. row *************************** table_schema: bookshop table_name: books index_name: book_title_uk query_total: 1 rows_access_total: 1 last_access_time: 2025-03-09 05:33:47 percentage_access_0: 0 percentage_access_0_1: 0 percentage_access_1_10: 1 percentage_access_10_20: 0 percentage_access_20_50: 0 percentage_access_50_100: 0 percentage_access_100: 0 1 row in set (0.01 sec) ``` + Execute another query that does not likely to use the index `book_title_uk`. In this case, `type` is the predicate and `title` is omitted. While the `book_title_uk` index's leading column is `title`, so the CBO won't favor an `book_title_uk` index access path. ```sql SELECT stock, price FROM bookshop.books WHERE type = 'Science & Technology'; ``` ```output +------------+--------+ | stock | price | +------------+--------+ | 2147483647 | 101.02 | | 30 | 195.32 | | 141 | 13.20 | | 138 | 94.34 | | 38 | 138.97 | | 66 | 13.00 | | 147 | 185.76 | | 6 | 109.95 | | 20 | 75.24 | | 990 | 197.15 | | 4981 | 133.59 | | 9888998 | 106.51 | | 288 | 197.48 | | 30000 | 56.56 | | 5555555 | 104.12 | | 6 | 187.77 | +------------+--------+ 16 rows in set (0.02 sec) ``` + However, for the previous query based on the result set only, we don't know the query was executed via the index access path or the full table scan access path. Let's get the answer from `tidb_index_usage` table. You will find that no counters were incremented, which means the index was not used for the previous query. ```sql SELECT table_schema, table_name, index_name, query_total, rows_access_total, last_access_time, percentage_access_0, percentage_access_0_1, percentage_access_1_10, percentage_access_10_20, percentage_access_20_50, percentage_access_50_100, percentage_access_100 FROM information_schema.tidb_index_usage WHERE index_name='book_title_uk'\G ``` ```output *************************** 1. row *************************** table_schema: bookshop table_name: books index_name: book_title_uk query_total: 1 rows_access_total: 1 last_access_time: 2025-03-09 05:33:47 percentage_access_0: 0 percentage_access_0_1: 0 percentage_access_1_10: 1 percentage_access_10_20: 0 percentage_access_20_50: 0 percentage_access_50_100: 0 percentage_access_100: 0 1 row in set (0.01 sec) ``` + Confirm that the `type = 'Science & Technology'` predicate cannot trigger the index usage. ```sql EXPLAIN ANALYZE SELECT stock, price FROM bookshop.books WHERE type = 'Science & Technology'; ``` ```output +---------------------------+---------+---------+-----------+---------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------+-----------+------+ | id | estRows | actRows | task | access object | execution info | operator info | memory | disk | +---------------------------+---------+---------+-----------+---------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------+-----------+------+ | TableReader_11 | 16.00 | 16 | root | partition:all | time:1.5ms, loops:2, RU:4.149196, cop_task: {num: 8, max: 1.33ms, min: 955.8µs, avg: 1.17ms, p95: 1.33ms, max_proc_keys: 13, p95_proc_keys: 13, tot_proc: 662.4µs, tot_wait: 4.26ms, copr_cache_hit_ratio: 0.00, build_task_duration: 23.7µs, max_distsql_concurrency: 8}, rpc_info:{Cop:{num_rpc:8, total_time:9.31ms}} | data:Projection_5 | 994 Bytes | N/A | | └─Projection_5 | 16.00 | 16 | cop[tikv] | | tikv_task:{proc max:0s, min:0s, avg: 0s, p80:0s, p95:0s, iters:8, tasks:8}, scan_detail: {total_process_keys: 100, total_process_keys_size: 8414, total_keys: 108, get_snapshot_time: 4.02ms, rocksdb: {key_skipped_count: 100, block: {}}}, time_detail: {total_process_time: 662.4µs, total_wait_time: 4.26ms, tikv_wall_time: 6.22ms} | bookshop.books.stock, bookshop.books.price | N/A | N/A | | └─Selection_10 | 16.00 | 16 | cop[tikv] | | tikv_task:{proc max:0s, min:0s, avg: 0s, p80:0s, p95:0s, iters:8, tasks:8} | eq(bookshop.books.type, "Science & Technology") | N/A | N/A | | └─TableFullScan_9 | 100.00 | 100 | cop[tikv] | table:books | tikv_task:{proc max:0s, min:0s, avg: 0s, p80:0s, p95:0s, iters:8, tasks:8} | keep order:false | N/A | N/A | +---------------------------+---------+---------+-----------+---------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------+-----------+------+ 4 rows in set (0.00 sec) ``` + At this point, you may wonder that another reason for CBO choosing `TableFullScan` is because our data set are too small (only 100 rows in the table). You are correct because when the data set is not large enough there will be no obvious performance difference choosing between `IndexLookUp` and `TableFullScan`. But the execution plan does reflect CBO's decision. Let's do another experiment to test CBO's decision. + Create a new global unique index, this time it's on the columns combination `(type, title)`, `type` is the leading column. ```sql CREATE UNIQUE INDEX book_type_uk ON bookshop.books (type, title) GLOBAL; ANALYZE TABLE bookshop.books; ``` ```output Query OK, 0 rows affected (6.02 sec) Query OK, 0 rows affected, 8 warnings (0.82 sec) ``` + Execute the `type = 'Science & Technology'` predicate query again. This time we have two indexes, `book_type_uk` and `book_title_uk`, which access method will the CBO choose? `book_title_uk` is not likely, the choice will be made between the `book_type_uk` and the `TableFullScan`. As we already know, the rows in the result set account for 16% of the total rows. The query selectivity may not be optimal enough for the CBO to favor an index access path. ```sql SELECT stock, price FROM bookshop.books WHERE type = 'Science & Technology'; ``` ```output +------------+--------+ | stock | price | +------------+--------+ | 30000 | 56.56 | | 5555555 | 104.12 | | 6 | 187.77 | | 2147483647 | 101.02 | | 30 | 195.32 | | 141 | 13.20 | | 138 | 94.34 | | 38 | 138.97 | | 990 | 197.15 | | 4981 | 133.59 | | 9888998 | 106.51 | | 66 | 13.00 | | 147 | 185.76 | | 6 | 109.95 | | 20 | 75.24 | | 288 | 197.48 | +------------+--------+ 16 rows in set (0.00 sec) ``` + Consult the `tidb_index_usage` table again. You will find that no counters were incremented for `book_type_uk`, especially for the `percentage_access_10_20` counter, it's 0. Which means the index was not used for the previous query. However, if there are a lot of rows in `books` table, and the selectivity keeps as 16%, CBO might choose index access path for the same query. ```sql SELECT table_schema, table_name, index_name, query_total, rows_access_total, last_access_time, percentage_access_0, percentage_access_0_1, percentage_access_1_10, percentage_access_10_20, percentage_access_20_50, percentage_access_50_100, percentage_access_100 FROM information_schema.tidb_index_usage WHERE index_name in ('book_title_uk','book_type_uk')\G ``` ```output *************************** 1. row *************************** table_schema: bookshop table_name: books index_name: book_title_uk query_total: 1 rows_access_total: 1 last_access_time: 2025-03-09 05:33:47 percentage_access_0: 0 percentage_access_0_1: 0 percentage_access_1_10: 1 percentage_access_10_20: 0 percentage_access_20_50: 0 percentage_access_50_100: 0 percentage_access_100: 0 *************************** 2. row *************************** table_schema: bookshop table_name: books index_name: book_type_uk query_total: 0 rows_access_total: 0 last_access_time: NULL percentage_access_0: 0 percentage_access_0_1: 0 percentage_access_1_10: 0 percentage_access_10_20: 0 percentage_access_20_50: 0 percentage_access_50_100: 0 percentage_access_100: 0 2 rows in set (0.01 sec) ``` + As a general rule, in TiDB, a selectivity lower than 10% is considered the sweet spot for an index access path, but it is not an absolute rule for every edge case. ### Step 2. Drop unused indexes based on indexes usage statistics + The only purpose of a useless index is to slow down DML operations (INSERT, DELETE, UPDATE, etc.) and some DDL operations. Dropping it is the correct course of action. + Since the `tidb_index_usage` table can tell which index is begin used or not, TiDB provides another interesting in-memory table named `sys.schema_unused_indexes` to tell DBAs and DevOps engineers those useless indexes, and it will be cleared upon cluster restart. It is necessary to ensure that at least one TiDB node has been running for a sufficient amount of time before relying on `sys.schema_unused_indexes`. + Find the unused indexes on `books` table so far. ```sql SELECT * FROM sys.schema_unused_indexes WHERE object_schema='bookshop' AND object_name='books'; ``` ```output +---------------+-------------+--------------+ | object_schema | object_name | index_name | +---------------+-------------+--------------+ | bookshop | books | book_type_uk | +---------------+-------------+--------------+ 1 row in set (0.01 sec) ``` + Make the unused index invisible to CBO. CBO then won't use such index in the execution plan. In real-world scenarios, you should monitor the system for any potential performance regression. ```sql ALTER TABLE bookshop.books ALTER INDEX book_type_uk INVISIBLE; ``` ```output Query OK, 0 rows affected (0.51 sec) ``` + After completing certain business cycles, if no performance regression is observed related to the invisible index, it should be dropped. ```sql ALTER TABLE bookshop.books DROP INDEX book_type_uk; ``` ```output Query OK, 0 rows affected (0.51 sec) ``` + Now that you understand how to get assistance from the index usage statistics to help you making operation decisions. Please proceed to the next module. ## Module 5: Vector Search (v8.5.0) Driven by advancements in AI and the need for efficient data handling in high-dimensional spaces, TiDB supports vector data types and in-database vector search. In this module, you will create a table with the vector data type, and insert records with vector data, use the HNSW index to improve the vector searching performance. + You can employ TiDB as a vectors store that provides immediate advantages, including: + Efficient approximates nearest neighbor searches. + Zero planned and unplanned downtime with limitless scale. + Unified storage for both relational and vector data, scalable to extreme large volumes. ### Step 1. Create a test table with vector data type + Create a table with vector data type and populate random data (32768 rows) into it. The table name is `vs_example.vector_table`. Note that as shown in the output the vector data type column is `embedding`. ```sql CREATE DATABASE vs_example; USE vs_example; DROP SEQUENCE IF EXISTS vs_seq1; CREATE SEQUENCE vs_seq1; DROP TABLE IF EXISTS vector_table; CREATE TABLE vector_table ( id BIGINT AUTO_RANDOM PRIMARY KEY, doc TEXT, embedding VECTOR(3) ); SOURCE populate_vector_table.sql DESC vector_table; SELECT COUNT(1) FROM vector_table; SHOW CREATE TABLE vector_table\G ``` ```output ... Query OK, 16384 rows affected (0.06 sec) Records: 16384 Duplicates: 0 Warnings: 0 Query OK, 0 rows affected (0.10 sec) +-----------+-----------+------+------+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-----------+-----------+------+------+---------+-------+ | id | bigint | NO | PRI | NULL | | | doc | text | YES | | NULL | | | embedding | vector(3) | YES | | NULL | | +-----------+-----------+------+------+---------+-------+ 3 rows in set (0.00 sec) +----------+ | COUNT(1) | +----------+ | 32768 | +----------+ 1 row in set (0.02 sec) *************************** 1. row *************************** Table: vector_table Create Table: CREATE TABLE `vector_table` ( `id` bigint NOT NULL /*T![auto_rand] AUTO_RANDOM(5) */, `doc` text DEFAULT NULL, `embedding` vector(3) DEFAULT NULL, PRIMARY KEY (`id`) /*T![clustered_index] CLUSTERED */ ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin /*T![auto_rand_base] AUTO_RANDOM_BASE=625585 */ 1 row in set (0.01 sec) ``` ### Step 2. Run a K-Nearest neighbor search and examine the execution plan + Review the following query. It shows a typical K-nearest neighbor search by using the cosine distance. + The algorithm seeks the nearest nodes (top 3) to a reference node `[0.10752534,0.3524283,0.46134815]` within the dataset - table `vector_table`. Your result will differ from the sample output due to the use of random data in Step 1. ```sql SELECT doc, embedding FROM vector_table ORDER BY VEC_COSINE_DISTANCE(embedding, '[0.10752534,0.3524283,0.46134815]') LIMIT 3; ``` ```output +-------+-----------------------------------+ | doc | embedding | +-------+-----------------------------------+ | 3343 | [0.10953534,0.3534283,0.46134815] | | 20717 | [0.18479975,0.59234893,0.7714548] | | 19597 | [0.20609,0.6679932,0.8865328] | +-------+-----------------------------------+ 3 rows in set (0.02 sec) ``` + Review the execution plan to verify that a `FullTableScan` is used in the query. Considering the table `vector_table` comprises only 32768 rows, the response time is notably swift, it's crucial to consider the potential performance ramifications, especially when dealing with billions of rows where the impact is substantial and cannot be overlooked. Take note of the operation at the bottom of the explain output, where the estimated number of rows (`estRows`) is a number much larger than `3.00`. ```sql EXPLAIN SELECT doc, embedding FROM vector_table ORDER BY VEC_COSINE_DISTANCE(embedding, '[0.10752534,0.3524283,0.46134815]') LIMIT 3; ``` ```output +--------------------------------+----------+-----------+--------------------+----------------------------------------------------------------------------------------------------------------------------------------------------+ | id | estRows | task | access object | operator info | +--------------------------------+----------+-----------+--------------------+----------------------------------------------------------------------------------------------------------------------------------------------------+ | Projection_14 | 3.00 | root | | vs_example.vector_table.doc, vs_example.vector_table.embedding | | └─TopN_7 | 3.00 | root | | Column#4, offset:0, count:3 | | └─Projection_15 | 3.00 | root | | vs_example.vector_table.doc, vs_example.vector_table.embedding, vec_cosine_distance(vs_example.vector_table.embedding, [0.11,0.35,0.46])->Column#4 | | └─TableReader_13 | 3.00 | root | | data:TopN_12 | | └─TopN_12 | 3.00 | cop[tikv] | | vec_cosine_distance(vs_example.vector_table.embedding, [0.11,0.35,0.46]), offset:0, count:3 | | └─TableFullScan_11 | 30179.00 | cop[tikv] | table:vector_table | keep order:false | +--------------------------------+----------+-----------+--------------------+----------------------------------------------------------------------------------------------------------------------------------------------------+ 6 rows in set (0.00 sec) ``` ### Step 3. Add a HNSW index on vector column + Enable TiFlash replica. ```sql ALTER TABLE vector_table SET TIFLASH REPLICA 1; ``` + Create a HNSW vector index on column `embedding`. The HNSW index provides a good performance with approximated accuracy (greater than 98% in most cases). ```sql CREATE VECTOR INDEX embedding_idx ON vector_table ((VEC_COSINE_DISTANCE(embedding))) USING HNSW; ``` + Confirm the column is completely indexed. Verify that the `ROWS_STABLE_INDEXED` is `32768`. ```sql SELECT * FROM information_schema.tiflash_indexes WHERE tidb_table='vector_table'\G ``` ```output *************************** 1. row *************************** TIDB_DATABASE: vs_example TIDB_TABLE: vector_table TABLE_ID: 114 COLUMN_NAME: embedding INDEX_NAME: embedding_idx COLUMN_ID: 3 INDEX_ID: 1 INDEX_KIND: HNSW ROWS_STABLE_INDEXED: 32768 ROWS_STABLE_NOT_INDEXED: 0 ROWS_DELTA_INDEXED: 0 ROWS_DELTA_NOT_INDEXED: 0 ERROR_MESSAGE: TIFLASH_INSTANCE: 10.90.4.131:3930 1 row in set (0.00 sec) ``` ### Step 4. Run the K-Nearest neighbor query against the indexed table + Run the same k-nearest neighbors search against table `vector_table`. ```sql SELECT doc, embedding FROM vector_table ORDER BY VEC_COSINE_DISTANCE(embedding, '[0.10752534,0.3524283,0.46134815]') LIMIT 3; ``` ```output +-------+------------------------------------+ | doc | embedding | +-------+------------------------------------+ | 3343 | [0.10953534,0.3534283,0.46134815] | | 20717 | [0.18479975,0.59234893,0.7714548] | | 24641 | [0.21949558,0.73159087,0.94375384] | +-------+------------------------------------+ 3 rows in set (0.03 sec) ``` ### Step 5. Review the execution plan + Review the execution plan to verify that an approximate nearest neighbor index (`index:embedding_idx(embedding)` object under `access object` column and `annIndex:COSINE` operator under `operator info` column in the output) is used in the query. In a production environment operating at scale, the table `vector_table` is expected to outperform `vector_table_noidx` when serving this query. Take note of the operation at the bottom of the explain output, where the estimated number of rows (`estRows`) is ONLY `3.00`, which is significantly more efficient than the `TableFullScan` in Step 2. In addition to the index optimization, you can also find the `mpp[tiflash]` tasks under `task` column in the output, it's a columnar storage query optimization implemented by TiDB. ```sql EXPLAIN SELECT doc, embedding FROM vector_table ORDER BY VEC_COSINE_DISTANCE(embedding, '[0.10752534,0.3524283,0.46134815]') LIMIT 3; ``` ```output +--------------------------------------+---------+--------------+----------------------------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------+ | id | estRows | task | access object | operator info | +--------------------------------------+---------+--------------+----------------------------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------+ | Projection_28 | 3.00 | root | | vs_example.vector_table.doc, vs_example.vector_table.embedding | | └─TopN_10 | 3.00 | root | | Column#5, offset:0, count:3 | | └─Projection_29 | 3.00 | root | | vs_example.vector_table.doc, vs_example.vector_table.embedding, vec_cosine_distance(vs_example.vector_table.embedding, [0.11,0.35,0.46])->Column#5 | | └─TableReader_25 | 3.00 | root | | MppVersion: 2, data:ExchangeSender_24 | | └─ExchangeSender_24 | 3.00 | mpp[tiflash] | | ExchangeType: PassThrough | | └─Projection_26 | 3.00 | mpp[tiflash] | | vs_example.vector_table.doc, vs_example.vector_table.embedding | | └─TopN_23 | 3.00 | mpp[tiflash] | | Column#4, offset:0, count:3 | | └─Projection_27 | 3.00 | mpp[tiflash] | | vs_example.vector_table.doc, vs_example.vector_table.embedding, vec_cosine_distance(vs_example.vector_table.embedding, [0.11,0.35,0.46])->Column#4 | | └─TableFullScan_22 | 3.00 | mpp[tiflash] | table:vector_table, index:embedding_idx(embedding) | keep order:false, annIndex:COSINE(embedding..[0.11,0.35,0.46], limit:3) | +--------------------------------------+---------+--------------+----------------------------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------+ 9 rows in set (0.00 sec) ``` + As previously mentioned, HNSW indexing relies on TiFlash. If you are new to TiDB's columnar storage TiFlash, please check the other hands-on lab - Getting Started with TiDB Cloud Serverless or enroll the self-paced e-learning [Introduction to TiDB](https://www.pingcap.com/education/course-introduction-to-tidb/). Proceed to the next module. + Unlike the 3-dimensional vectors in this module, real-world scenarios often involve vectors with many more dimensions, such as 1536-dimensional vectors generated by an embedding model. We also offer hands-on labs focused on these topics - [Generative AI at TiDB](https://labs.tidb.io/labs?category=Generative+AI+at+TiDB). + Please proceed to the next module. ## Module 6: TiKV MVCC In-memory Engine (v8.5.0) Multi-Version Concurrency Control (MVCC) is a critical aspect of modern databases, allowing concurrent access without compromising integrity. TiKV MVCC In-Memory Engine is primarily used to accelerate queries that need to scan a large number of MVCC historical versions. A well optimized query may become a slow query because it scans lots of MVCC versions. TiKV MVCC in-memory engine is suitable for the following scenarios to improve the query performance: + The application that requires querying records that are frequently updated or deleted. + The application that requires adjusting [tidb_gc_life_time](https://docs.pingcap.com/tidb/stable/system-variable-reference/#tidb_gc_life_time) to retain historical versions in TiDB for a longer period. ### Step 1. Check the default in-memory-engine settings + Connect to TiDB. ```bash mysql -h ${HOST_TIPROXY1_PRIVATE_IP} --port 6000 -u root ``` ```output mysql> ``` + Verify that the TiKV MVCC `in-memory-engine` is turned off by default. ```sql SHOW CONFIG WHERE type='tikv' AND name='in-memory-engine.enable'; ``` ```output +------+-------------------+-------------------------+-------+ | Type | Instance | Name | Value | +------+-------------------+-------------------------+-------+ | tikv | 10.90.1.41:20160 | in-memory-engine.enable | false | | tikv | 10.90.1.223:20160 | in-memory-engine.enable | false | | tikv | 10.90.1.231:20160 | in-memory-engine.enable | false | +------+-------------------+-------------------------+-------+ 3 rows in set (0.01 sec) ``` + Increase the `tidb_gc_life_time` so that the previous versions can be kept during the lifetime of this workshop. ```sql SET GLOBAL tidb_gc_life_time='1440m'; ``` ### Step 2. Verify the behavior while the in-memory-engine setting is OFF + Set up two test tables named `ime_test1` and `ime_test2` with the same schema. Note that there is an index on column `content` for each table. ```sql DROP TABLE IF EXISTS test.ime_test1; DROP TABLE IF EXISTS test.ime_test2; CREATE TABLE test.ime_test1 ( `id` BIGINT AUTO_RANDOM PRIMARY KEY, `content` INT, `created_at` DATETIME, `updated_at` DATETIME, `version` INT, KEY content_idx (`content`) ); CREATE TABLE test.ime_test2 ( `id` BIGINT AUTO_RANDOM PRIMARY KEY, `content` INT, `created_at` DATETIME, `updated_at` DATETIME, `version` INT, KEY content_idx (`content`) ); ``` + Insert one row into both tables. ```sql INSERT INTO test.ime_test1 ( content, created_at, updated_at, version ) VALUES ( FLOOR(1 + (RAND() * 99999999)), NOW(), NOW(), 1 ); INSERT INTO test.ime_test2 ( content, created_at, updated_at, version ) VALUES ( FLOOR(1 + (RAND() * 99999999)), NOW(), NOW(), 1 ); SELECT * FROM test.ime_test1; SELECT * FROM test.ime_test2; EXIT ``` + Expand both table with 32768 rows. ```bash cat > double.sql < double.sh < update_hog.sql < update_hog.sh < ``` + Run two SQL statements that perform a full table scan on the tables `test.ime_test1` and `test.ime_test2`. ```sql SELECT COUNT(*) FROM test.ime_test1; SELECT COUNT(*) FROM test.ime_test2; EXIT ``` ```output +----------+ | COUNT(*) | +----------+ | 32768 | +----------+ 1 row in set (0.03 sec) +----------+ | COUNT(*) | +----------+ | 32768 | +----------+ 1 row in set (1.09 sec) Bye ``` + Go to the [TiDB PD Dashboard](http://${LAB:HOST_PD1_PUBLIC_IP}:2379/dashboard) and log in with the username `root`, note that a password is not required at the moment. Navigate to `SQL Statements` panel, change the display time to `Recent 5 min`. ![60%](https://lab-static.pingcap.com/quick-demo/ime-off-dashboard-time-1.png) + Select the database as `test`. ![60%](https://lab-static.pingcap.com/quick-demo/ime-off-dashboard-db-2.png) + Set the displayed operations to `Select` and `Update`. ![60%](https://lab-static.pingcap.com/quick-demo/ime-off-dashboard-kind-3.png) + In the top right corner of the Dashboard, select `Columns` and check the menu items `Mean Visible Version Per Query` and `Mean Meet Versions Per Query`, then click the `Query` button. ![60%](https://lab-static.pingcap.com/quick-demo/ime-off-dashboard-columns-4.png) + From the query results, for the scan of the table `test.ime_test1`, `Mean Visible Version Per Query` is equal to `Mean Meet Versions Per Query`, indicating that there are no MVCC snapshots for processing. In contrast, for the scan of the table `test.ime_test2`, `Mean Meet Versions Per Query` is significantly higher than `Mean Visible Version Per Query`, suggesting that there is a large amount of MVCC snapshot processing during the scan. This is also evident from the `Total Latency` and `Mean Latency` columns, which show that for the same number of rows scanned, the execution times differ, with a amount of time consumed by the scanning of MVCC snapshots for the table `test.ime_test2`. ![95%](https://lab-static.pingcap.com/quick-demo/ime-off-dashboard-sql-5.png) + Click on the Grafana Dashboard and log in using the username and password (both are "admin"). Navigate to `Home` > `tidb-demo` > `tidb-demo-TikV-Details` > `In Memory Engine`, scroll down to the panels about `Region Cache Hit`，`Region Cache Hit Rate`，`Region Cache Miss Reason`，`Memory Usage` and `Region Count`. ![90%](https://lab-static.pingcap.com/quick-demo/ime-off-grafana-no-data-1.png) + Currently, they both show `No data`, because the TiKV MVCC In-memory Engine is not enabled in the current database cluster. Change the `Absolute time range` to `Last 5 minutes`. ![60%](https://lab-static.pingcap.com/quick-demo/ime-off-grafana-time-range-2.png) + Change the `Refresh time` to `10s`. ![60%](https://lab-static.pingcap.com/quick-demo/ime-off-grafana-refresh-time-3.png) ### Step 3. Enable the TiKV MVCC In-memory Engine. + Return to [Terminal 1](ssh://monitor1-ssh#1) , use `tiup cluster edit-config` to open the TiDB cluster configuration in `vi`. ```bash tiup cluster edit-config tidb-demo ``` + To enable the TiKV MVCC In-memory Engine, set `in-memory-engine.capacity` to `1GiB`, `in-memory-engine.enable` to `true`, and `in-memory-engine.mvcc-amplification-threshold` to `100` for ALL THREE `tikv_servers`. + Copy the following section below each TiKV's `os: linux` entry, then use `:wq` to save and quit. ```yaml config: in-memory-engine.capacity: 1GiB in-memory-engine.enable: true in-memory-engine.mvcc-amplification-threshold: 50 ``` ```output ... tikv_servers: - host: 10.90.1.41 ssh_port: 22 port: 20160 status_port: 20180 deploy_dir: /tidb-deploy/tikv-20160 data_dir: /tidb-data/tikv-20160 log_dir: /tidb-deploy/tikv-20160/log arch: amd64 os: linux config: in-memory-engine.capacity: 1GiB in-memory-engine.enable: true in-memory-engine.mvcc-amplification-threshold: 50 - host: 10.90.1.223 ssh_port: 22 port: 20160 status_port: 20180 deploy_dir: /tidb-deploy/tikv-20160 data_dir: /tidb-data/tikv-20160 log_dir: /tidb-deploy/tikv-20160/log arch: amd64 os: linux config: in-memory-engine.capacity: 1GiB in-memory-engine.enable: true in-memory-engine.mvcc-amplification-threshold: 50 - host: 10.90.1.231 ssh_port: 22 port: 20160 status_port: 20180 deploy_dir: /tidb-deploy/tikv-20160 data_dir: /tidb-data/tikv-20160 log_dir: /tidb-deploy/tikv-20160/log arch: amd64 os: linux config: in-memory-engine.capacity: 1GiB in-memory-engine.enable: true in-memory-engine.mvcc-amplification-threshold: 50 ... ``` + Answer `y` to the prompt. ```output Please check change highlight above, do you want to apply the change? [y/N]:(default=N) y Applying changes... Applied successfully, please use `tiup cluster reload tidb-demo [-N ] [-R ]` to reload config. ``` + Run `tiup cluster reload` to reload the config. ```bash tiup cluster reload tidb-demo --yes ``` ```output ... Reloaded cluster `tidb-demo` successfully ``` + After the config is reloaded, connect to TiDB. ```bash mysql -h ${HOST_TIPROXY1_PRIVATE_IP} --port 6000 -u root ``` ```output mysql> ``` + Verify that the TiKV MVCC `in-memory-engine` is turned on. ```sql SHOW CONFIG WHERE type='tikv' AND name='in-memory-engine.enable'; EXIT ``` ```output +------+-------------------+-------------------------+-------+ | Type | Instance | Name | Value | +------+-------------------+-------------------------+-------+ | tikv | 10.90.1.41:20160 | in-memory-engine.enable | true | | tikv | 10.90.1.223:20160 | in-memory-engine.enable | true | | tikv | 10.90.1.231:20160 | in-memory-engine.enable | true | +------+-------------------+-------------------------+-------+ 3 rows in set (0.02 sec) Bye ``` ### Step 4. Verify the behavior while the in-memory-engine setting is ON + Click on the [Grafana Prometheus Dashboard](http://${LAB:HOST_MONITOR1_PUBLIC_IP}:3000) and log in using the username and password (both are "admin"). Navigate to `Home` > `tidb-demo` > `tidb-demo-TikV-Details` > `In Memory Engine`, scroll down to the panels about `Region Cache Hit`，`Region Cache Hit Rate`，`Region Cache Miss Reason`，`Memory Usage` and `Region Count`. Now, these panels no longer display `No data`，however, the values for `Region Cache Hit`, `Region Cache Hit Rate`, `Region Cache Miss Reason`, `Memory Usage`, and `Region Count` are very low, almost zero, because there are still no queries for MVCC snapshots currently. ![90%](https://lab-static.pingcap.com/quick-demo/ime-on-grafana-no-query-1.png) + Return to [Terminal 1](ssh://monitor1-ssh#1) , execute 10,000 scans on the `test.ime_test1` table via the command line, while monitoring the `Home` > `tidb-demo` > `tidb-demo-TikV-Details` > `In Memory Engine` panel in the [Grafana Prometheus Dashboard](http://${LAB:HOST_MONITOR1_PUBLIC_IP}:3000) during the scanning process. ```bash for i in `seq 10000`; do mysql -uroot -h ${HOST_TIPROXY1_PRIVATE_IP} -P 6000 -e "SELECT COUNT(*) FROM test.ime_test1;"; done; ``` + During the scanning process of the `test.ime_test1` table, it can be observed from the `tidb-demo-TikV-Details` > `In Memory Engine` panel that there is little change in both `Region Cache Hit` and `Region Cache Hit Rate`. (The `Region Cache Hit` indicates the number of times data is successfully retrieved from the Region cache, while the `Region Cache Hit Rate` represents the hit rate of the Region cache.) The reason for their minimal changes is that the `test.ime_test1` table itself does not have MVCC historical snapshots. ![90%](https://lab-static.pingcap.com/quick-demo/ime-on-grafana-ime-test1.png) + After the 10,000 scans on the `test.ime_test1` table finish, return to [Terminal 1](ssh://monitor1-ssh#1) , execute another 50,000 scans on the `test.ime_test2` table via the command line, while monitoring the `Home` > `tidb-demo` > `tidb-demo-TikV-Details` > `In Memory Engine` panel in the [Grafana Prometheus Dashboard](http://${LAB:HOST_MONITOR1_PUBLIC_IP}:3000) again during the scanning process. ```bash for i in `seq 50000`; do mysql -uroot -h ${HOST_TIPROXY1_PRIVATE_IP} -P 6000 -e "SELECT COUNT(*) FROM test.ime_test2;"; done; ``` + During the scanning process of the `test.ime_test2` table, it can be observed from the `tidb-demo-TikV-Details` > `In Memory Engine` panel that there are significant changes in both `Region Cache Hit` and `Region Cache Hit Rate`. The reason for this is that the `test.ime_test2` table itself contains a large number of MVCC historical snapshots. The TiKV MVCC in-memory engine caches the latest written MVCC versions in memory and implements an MVCC GC mechanism independent of TiDB. As a result, a substantial amount of data is read from the cache of the TiKV MVCC in-memory engine during the scanning process. ![90%](https://lab-static.pingcap.com/quick-demo/ime-on-grafana-ime-test2.png) + The TiKV MVCC In-memory Engine may sometimes not cache data in a timely manner; in this case, you can try repeating the steps above, scanning the `ime_test2` table again and observing the monitoring panel. ## Conclusion For a complete list of the new features introduced in TiDB v8, please visit the [TiDB Release Notes](https://docs.pingcap.com/tidb/stable/release-notes). ```hbsptform { "ui": { "titleLevel": 3 }, "portalId": "4466002", "formId": "89fa8368-eba1-4b84-b91e-eb9eb6613ddf", "region": "na1", "sfdcCampaignId": "701RC00000VqCODYA3" } ```