Shulou(Shulou.com)11/24 Report--

Recently, PingCAP, an enterprise-level open source distributed database manufacturer, officially released TiDB 7.4 (DMR) version. TiDB 7.4 will be the last DMR version of the TiDB 7 series, with many optimizations for MySQL 8.0. As a comprehensive upgrade of MySQL, TiDB's technology leadership helps users cope with changing business data challenges and achieve continuous business growth and innovation. While TiDB is highly compatible with MySQL 8.0 features, it will continue to provide technical support to ensure that users can smoothly migrate various business applications, thereby reducing the workload and risk in the migration process.

As a new generation of distributed relational database, TiDB embraces MySQL ecology from the first day of its birth, and continues to be compatible with MySQL 5.7and MySQL 8.0. it brings users a smoother migration and use experience. TiDB is compatible with MySQL syntax commands, and MySQL clients, MySQL drivers and some MySQL tools can be run directly on TiDB. For the vast majority of applications running on MySQL, there is almost no need to modify any code.

MySQL 8.0 adds or optimizes many features, such as support for common table expressions (CTE), window functions, and resource control, and provides stronger performance through multi-valued indexing and optimized query execution. With the release of MySQL 8.0, TiDB extends its compatibility with MySQL 8.0 on the basis of compatibility with MySQL 5.7.According to the release of MySQL 8.0, TiDB extends compatibility with MySQL 8.0. TiDB 7.4 now supports the following key MySQL 8.0 features:

Common table expression (CTE)

TiDB has supported the ANSI SQL 99 standard CTE and its recursive writing since version 5.1. When writing complex queries, common table expressions (CTE) can be used to build a temporary intermediate result set, which can be referenced many times in SQL statements to improve the writing efficiency, readability and execution efficiency of SQL statements. In TiDB 7.4, the column storage engine TiFlash also supports CTE.

Window function (window function)

Window function can be written in the form of SQL to complete some complex data collation work and help users to explore the value of data. For example, data grouping sorting, change trend analysis and so on. TiDB 7.4 fully supports the window functions provided by MySQL 8.0, and most of them can be pushed down to TiFlash to run.

Resource management and control

In the scenario 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 solve the impact of batch jobs and background tasks on core business, and sudden SQL performance problems slow down the entire cluster, which is an important ability to improve the stability of large clusters. With the introduction of resource control from version 7.1, TiDB can allocate cluster resources reasonably, improve the stability of the database, and reduce the cost of using the database. TiDB 7.4 is compatible with the syntax of MySQL specified resource groups and hint, reducing user learning costs and migration costs. At the same time, the resource isolation of TiDB can manage the most important I / O resources more effectively, and achieve the same or even better effect as MySQL.

Role-based privilege management

TiDB 7.4 supports MySQL-compatible role management. Role-based authorization can simplify the work of rights management and reduce the risk of errors. By associating permissions with roles, you can better control access to the database. Users can classify the work in different scenarios, create corresponding roles, and grant roles to database users with permissions. Database users can switch roles according to different scenarios to reduce the possibility of misoperation.

Enhanced uft8mb4 character set

Another important change in MySQL 8.0 is that the default character set has become a more generic uft8mb4, and the default sorting method has become utf8mb4_0900_ai_ci. TiDB 7.4 also adds utf8mb4_0900_ai_ci sorting to make system migration easier. The MySQL compatible variable default_collation_for_utf8mb4 is supported in order to be compatible with both MySQL 5.7and MySQL 8.0J TiDB. Allows users to adjust the default sorting of the utf8mb4 character set. This approach ensures a smooth transition of TiDB between different MySQL versions and can adapt to the needs of different applications.

JSON Multi-valued Index (Multi-valued Index)

Multi-valued index is an extension of common index structure. Unlike the correspondence between a normal index and a table at 1:1, the correspondence between a multi-valued index and a table is NVR 1. As with MySQL, when you use MEMBER OF (), JSON_CONTAINS (), and JSON_OVERLAPS () functions in the condition to retrieve, you may choose a multi-valued index. TiDB 7.4 supports "multi-valued indexing", which allows indexing of an "array" in the JSON type, thus improving the efficiency of JSON data retrieval. Using exactly the same as MySQL means that during the migration, users can continue to manipulate JSON data in a familiar way without modifying the data modeling or application.

Modify the hint of the session variable (SET_VAR ())

MySQL 8.0 introduces a special hint SET_VAR (). With this hint, you can modify a session-level system variable while the statement is running. TiDB 7.4 supports this hint, improves the flexibility of setting system variables, and can be "customized" for SQL statements.

CHECK constraint

CHECK constraint is a kind of consistency constraint checking, which is used to maintain the accuracy of data. The CHECK constraint can be used to restrict that the value of a field in the table must meet the specified condition. When you add a CHECK constraint to a table, when you insert or update data, TiDB checks to see if the constraint is met, and if not, an error is reported.

MySQL only supports the syntax of CHECK constraints before 8.0, but does not really check it in practice, and does not fully support it until after 8.0. This feature has been added in TiDB 7.4, which can be manually opened by users through the variable tidb_enable_check_constraint.

In order to reduce the complexity of user data migration, TiDB launched a tool TiDB Data Migration (DM). It can assist users with full data migration and incremental data synchronization from databases compatible with MySQL protocol (MySQL, MariaDB, Aurora MySQL) to TiDB. DM supports DDL synchronization, merging of sub-libraries and tables, and built-in filters to flexibly adapt to different scenarios, effectively improving the efficiency of data migration.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.