TiDB 7.4 Release: Officially Compatible with MySQL 8.0

translator_bot · June 21, 2024, 6:33pm

Note:
This topic has been translated from a Chinese forum by GPT and might contain errors.

Original topic: TiDB 7.4 发版：正式兼容 MySQL 8.0

| username: Billmay表妹

MySQL is the world’s most popular open-source database, consistently ranking second on the DB-Engines Ranking and boasting a vast number of enterprise users and developers globally. However, over time, MySQL users are facing new challenges. Oracle has officially announced that it will terminate official technical support for MySQL 5.7 in October 2023. According to third-party statistics, more than half of MySQL servers are still running on version 5.7. In the coming months, a large number of MySQL instances must be upgraded to version 8.0 or higher, or they will not receive technical support and critical patch updates from Oracle, posing a significant challenge for enterprise users.

TiDB, as a new generation of distributed relational database, has embraced the MySQL ecosystem from day one, continuously ensuring compatibility with MySQL 5.7 and MySQL 8.0, providing users with a smoother migration and usage experience. This article will introduce the new advancements in MySQL 8.0 compatibility in TiDB 7.4 DMR and explore how TiDB fundamentally addresses the various challenges faced by MySQL users.

○ Business Continuity Impact During Upgrades: Upgrading MySQL running in single-instance or “master-slave mode” can cause database service downtime, potentially impacting business operations. Enterprise users running a large number of MySQL instances need to invest significant human and material resources in testing and rehearsals to mitigate potential upgrade risks.

○ Difficulty in Business Scale Expansion: As business scales and data usage scenarios increase, users often need to balance between single-machine capacity limits and the complexity of shard management, making database expansion challenging and restricting business scale and growth speed.

○ Lack of Extreme High Availability Solutions: For MySQL databases supporting core business scenarios, unpredictable downtime events make business recovery complex, and achieving extremely low recovery time objectives (RTO) becomes a challenge for database administrators.

○ Insufficient Real-Time Analysis Capabilities: MySQL’s performance in handling large-scale real-time data analysis is not as outstanding as in OLTP (Online Transaction Processing) scenarios. This poses a challenge for businesses requiring complex queries and data analysis.

○ Limited Native Managed Services: Although cloud service providers offer MySQL managed services, most lack official support from Oracle. This means users cannot get quick feedback and support from the database vendor when dealing with deep product issues and discovering common functional needs.

Therefore, migrating to a mature product that addresses the above challenges in one go is undoubtedly a wise choice. TiDB is the ideal choice for a comprehensive MySQL upgrade. Choosing TiDB not only frees users from MySQL upgrade and scalability dilemmas but also offers additional benefits such as HTAP and database integration.

TiDB is an enterprise-grade distributed relational database independently developed by PingCAP, featuring horizontal scalability, financial-grade high availability, real-time HTAP, cloud-native capabilities, and compatibility with MySQL 5.7 protocol and ecosystem. TiDB’s native distributed architecture design provides flexible elastic scaling capabilities, making the entire process transparent to the business without manual intervention. TiDB’s multi-replica storage and Multi-Raft protocol ensure strong data consistency and high availability, ensuring data availability even when some replicas fail. TiDB minimizes the impact of version updates through rolling upgrades and can add temporary nodes to control performance fluctuations and connection interruptions during the upgrade process to within 5%, significantly reducing the impact on business.

Additionally, as the creator of TiDB, PingCAP offers TiDB Cloud, a managed database service based on leading global cloud service providers, covering complex problem diagnosis, upgrade support, emergency rescue, and more, fully demonstrating the advantages of native services.

From the early stages of the project, TiDB has adhered to a product strategy of embracing the MySQL ecosystem, which continues to this day. TiDB is compatible with MySQL’s wire protocol and syntax commands, meaning MySQL clients, MySQL drivers, and some MySQL tools can run directly on TiDB. For most applications running on MySQL, almost no code modifications are needed.

With the release of MySQL 8.0, TiDB has actively expanded its compatibility with MySQL 8.0 based on its compatibility with MySQL 5.7. The TiDB v7.4.0 version has released support for commonly used MySQL 8.0 features, making it easy to smoothly migrate MySQL 8.0 applications. This article lists some features:

3.1 Common Table Expressions (CTE)

As an important capability introduced in MySQL 8.0, TiDB has supported ANSI SQL 99 standard CTE and its recursive writing since version 5.1. When writing complex queries, using Common Table Expressions (CTE) can construct a temporary intermediate result set that can be referenced multiple times in SQL statements, improving SQL statement writing efficiency, readability, and execution efficiency. In the current version, TiFlash also supports CTE.

For example, the table authors stores information about authors, and book_authors records the correspondence between author IDs and the IDs of the books they wrote.

Using CTE, you can easily write SQL to list the number of books written by the 50 oldest authors.

mysql> WITH top_50_eldest_authors_cte AS (
    ->     SELECT a.id, a.name, (IFNULL(a.death_year, YEAR(NOW())) - a.birth_year) AS age
    ->     FROM authors a
    ->     ORDER BY age DESC
    ->     LIMIT 50
    -> )
    -> SELECT
    ->     ANY_VALUE(ta.id) AS author_id,
    ->     ANY_VALUE(ta.age) AS author_age,
    ->     ANY_VALUE(ta.name) AS author_name,
    ->     COUNT(*) AS books
    -> FROM top_50_eldest_authors_cte ta
    -> LEFT JOIN book_authors ba ON ta.id = ba.author_id
    -> GROUP BY ta.id;
+-----------+------------+----------------------+-------+
| author_id | author_age | author_name          | books |
+-----------+------------+----------------------+-------+
| 524470241 |         80 | Alexie Kirlin        |     7 |
|  67511645 |         80 | Bridgette Tromp      |     9 |
...
|  48355494 |         80 | Audrey Mayert        |     7 |
+-----------+------------+----------------------+-------+
50 rows in set (0.23 sec)

Related documentation: 公共表表达式 (CTE) | PingCAP 文档中心

3.2 Window Functions

Window functions, also known as analytical functions, are used in data analysis, summarization, and sorting. Window functions can complete complex data organization tasks in SQL form, helping users discover data value. For example, data grouping and sorting, trend analysis, etc.

TiDB currently fully supports the window functions provided by MySQL 8.0, most of which can be pushed down to TiFlash for execution.

Related documentation: 窗口函数 | PingCAP 文档中心

3.3 Resource Control

TiDB introduced resource control in version 7.1 to reasonably allocate cluster resources, improve database stability, and reduce database usage costs. In scenarios where multiple applications share a TiDB cluster, resource isolation can effectively reduce the impact of application load changes on other applications. Resource management can also address the impact of batch jobs and background tasks on core business and sudden SQL performance issues that slow down the entire cluster, making it an important capability for improving large cluster stability.

Although the implementation differs from MySQL, TiDB is compatible with MySQL’s syntax for specifying resource groups and hints, reducing user learning and migration costs. Additionally, TiDB’s resource isolation can more effectively control the most important I/O resources, achieving the same or even better results than MySQL.

The following demonstrates using resource control to limit all resources used by usr1 to within 500 RU per second.

● Estimate cluster capacity

mysql> CALIBRATE RESOURCE

● Create the app1 resource group with a quota of 500 RU per second

mysql> CREATE RESOURCE GROUP IF NOT EXISTS app1 RU_PER_SEC = 500;

● Associate the user with the resource group, so usr1’s sessions automatically associate with the app1 resource group

mysql> ALTER USER usr1 RESOURCE GROUP app1;

You can also change the session’s resource group

mysql> SET RESOURCE GROUP `app1`;

Or use the hint RESOURCE_GROUP() to specify the resource group for a statement

mysql> SELECT /*+ RESOURCE_GROUP(rg1) */ * FROM t limit 10;

Related documentation: 使用资源管控 (Resource Control) 实现资源隔离 | PingCAP 文档中心

3.4 Role-Based Access Control

TiDB supports MySQL-compatible role management. Role-based authorization simplifies permission management and reduces the risk of errors. By associating permissions with roles, database access can be better controlled. Customers can classify different work scenarios, create corresponding roles, and grant roles to authorized database users. Database users can switch roles based on different scenarios during actual operations, reducing the possibility of misoperations.

Here is an example of using roles to split permissions. The user dev_adm_usr is an application administrator who operates the app_db database. Most of the time, they only query data and occasionally modify data when necessary. To prevent misoperations by dev_adm_usr, we split the two parts of the permissions using roles, granting read-write roles only when necessary.

● Create roles app_read_role and app_write_role

mysql> CREATE ROLE 'app_read_role', 'app_write_role';

● Grant corresponding permissions to the roles, here granting read and write permissions for app_db to the two roles respectively

mysql> GRANT SELECT ON app_db.* TO 'app_read_role'@'%';
mysql> GRANT INSERT, UPDATE, DELETE ON app_db.* TO 'app_write_role'@'%';

● Grant the two roles to the user dev_adm_usr

mysql> GRANT 'app_read_role','app_write_role' TO 'dev_adm_usr'@'localhost';

● Set app_read_role as the default role for dev_adm_usr, so the user dev_adm_usr has read-only permissions by default when logging in

mysql> SET DEFAULT ROLE 'app_read_role' TO 'dev_adm_usr'@'localhost';

● When dev_adm_usr needs to modify data, enable the role app_write_role

mysql> SET ROLE app_read_role,app_write_role;

Or enable all roles

mysql> SET ROLE ALL;

Related documentation: 基于角色的访问控制 | PingCAP 文档中心

3.5 Enhanced utf8mb4 Character Set

One significant change in MySQL 8.0 is the default character set becoming the more universal utf8mb4, with the default collation becoming utf8mb4_0900_ai_ci. TiDB has also added the utf8mb4_0900_ai_ci collation in the new version to facilitate system migration.

To be compatible with both MySQL 5.7 and MySQL 8.0, TiDB supports the MySQL-compatible variable default_collation_for_utf8mb4, allowing users to adjust the default collation for the utf8mb4 character set. This ensures a smooth transition between different MySQL versions and meets the needs of different applications.

If migrating from MySQL 8.0, set it to the 8.0 default collation utf8mb4_0900_ai_ci

set global default_collation_for_utf8mb4='utf8mb4_0900_ai_ci';

If migrating from MySQL 5.7, set it to the 5.7 default collation utf8mb4_general_ci

set global default_collation_for_utf8mb4='utf8mb4_general_ci';

3.6 JSON Multi-Valued Index

After supporting the complete functions of MySQL 5.7, TiDB continues to add support for new features released in MySQL 8.0. The recent version supports “multi-valued indexes,” allowing indexing on an “array” in JSON type, thereby improving the retrieval efficiency of JSON data. The usage is entirely the same as MySQL, meaning no modifications to data modeling or applications are needed during migration, and users can continue to operate JSON data in familiar ways.

Multi-valued indexes are an extension of the regular index structure. Unlike the 1:1 correspondence between regular indexes and tables, multi-valued indexes correspond to tables in an N:1 relationship. Similar to MySQL, conditions using MEMBER OF(), JSON_CONTAINS(), JSON_OVERLAPS() functions may choose multi-valued indexes.

For example, suppose we have a customer information table where all detailed information is embedded in a JSON type column, and an array structure stores the cities where the customer resides.

When we need to retrieve customers in Beijing, without a multi-valued index, this query needs to scan the entire table.

SELECT name FROM customer
WHERE 'beijing' MEMBER OF $.city;

We can create a multi-valued index on the city array, allowing the above query to use the index to retrieve matching records, significantly improving query performance.

ALTER TABLE customers add index idx_city (name, (CAST(custinfo->'$.city' AS char(20) ARRAY)));

Like regular indexes, when the optimizer does not choose the multi-valued index, you can use optimizer hints USE_INDEX() or USE_INDEX_MERGE() to force the optimizer to make a choice.

Related documentation: 索引的选择 | PingCAP 文档中心

3.7 Hint for Modifying Session Variables (SET_VAR())

MySQL 8.0 introduced a special hint SET_VAR(). Using this hint, you can modify a session-level system variable during the execution of a statement. TiDB also supports this hint in version v7.4.0, enhancing the flexibility of system variable settings and allowing “customization” for SQL statements. Multiple variables related to the optimizer and execution are supported for modification using hints.

For example, increase the execution parallelism of SQL for large table analysis processing.

SELECT /*+ set_var(tidb_executor_concurrency=20) */
    l_orderkey,
    SUM(
        l_extendedprice * (1 - l_discount)
    ) AS revenue,
    o_orderdate,
    o_shippriority
FROM
    customer,
    orders,
    lineitem
WHERE
    c_mktsegment = 'BUILDING'
AND c_custkey = o_custkey
AND l_orderkey = o_orderkey
AND o_orderdate < DATE '1996-01-01'
AND l_shipdate > DATE '1996-02-01'
GROUP BY
    l_orderkey,
    o_orderdate,
    o_shippriority
ORDER BY
    revenue DESC,
    o_orderdate
limit 10;

You can also use this hint to force the previous query to choose TiFlash, while other queries remain unchanged.

SELECT /*+ set_var(tidb_isolation_read_engines='tidb,tiflash') */
    l_orderkey,
    SUM(
        l_extendedprice * (1 - l_discount)
    ) AS revenue,
    o_orderdate,
    o_shippriority
FROM
    customer,
    orders,
    lineitem
WHERE
    c_mktsegment = 'BUILDING'
AND c_custkey = o_custkey
AND l_orderkey = o_orderkey
AND o_orderdate < DATE '1996-01-01'
AND l_shipdate > DATE '1996-02-01'
GROUP BY
    l_orderkey,
    o_orderdate,
    o_shippriority
ORDER BY
    revenue DESC,
    o_orderdate
limit 10;

Related documentation: https://docs.pingcap.com/zh/tidb/v7.4/optimizer-hints#set_varvar_namevar_value

3.8 CHECK Constraints

CHECK constraints are a type of consistency constraint check used to maintain data accuracy. CHECK constraints can be used to restrict the values of a field in a table to meet specified conditions. When a CHECK constraint is added to a table, TiDB checks whether the constraint conditions are met during data insertion or update. If not, an error is reported.

Before MySQL 8.0, CHECK constraints were only supported in syntax but were not actually checked during execution. Full support was added in MySQL 8.0. TiDB has also added this feature in the new version. To prevent issues caused by residual CHECK conditions in customers’ DDL, TiDB does not enable CHECK constraint checks by default. Instead, it can be manually enabled through the variable tidb_enable_check_constraint, fully reflecting TiDB’s strategy of being compatible with both MySQL 5.7 and 8.0.

mysql> set global tidb_enable_check_constraint=on;

mysql> CREATE TABLE t
    -> ( a INT CHECK(a > 10) NOT ENFORCED, -- Non-enforced check
    ->   b INT,
    ->   c INT,
    ->   CONSTRAINT c1 CHECK (b > c)
    -> );

mysql> insert into t values (20,20,20);
ERROR 3819 (HY000): Check constraint 'c1' is violated.

Related documentation: https://docs.pingcap.com/zh/tidb/dev/constraints#check-%E7%BA%A6%E6%9D%9F

To reduce the complexity of user data migration, TiDB has introduced a tool called TiDB Data Migration (DM). It assists users in full data migration and incremental data synchronization from MySQL protocol-compatible databases (MySQL, MariaDB, Aurora MySQL) to TiDB. DM supports DDL synchronization, sharding merge, and has built-in filters to flexibly adapt to different scenarios, significantly improving data migration efficiency.

TiDB 7.4 will be the last DMR version of the TiDB 7 series, with numerous optimizations for MySQL 8.0. As a comprehensive upgrade of MySQL, TiDB’s technological leadership helps users cope with ever-changing business data challenges, achieving continuous business growth and innovation. While highly compatible with MySQL 5.7 and MySQL 8.0 features, TiDB will continue to provide technical support, ensuring users can smoothly migrate various business applications, thereby reducing the workload and risks during the migration process.