Comparison and detailed analysis of different official versions of Mysql database

Preface

MySQL is a popular relational database management system with many versions, and different versions are different in terms of functions, performance, security and applicable scenarios. The following is a detailed analysis of the major versions of MySQL and their differences:

1. Version classification

The main versions of MySQL can be divided into the following categories:

• Official version: Community Edition and Business Edition released by Oracle.
• Branch version: Derivative versions developed by the community or other companies based on MySQL, such as MariaDB, Percona Server.
• Major version number: For example, 5.7 and 8.0 represent large functional iterations.
• Minor version number: For example, 8.0.34 indicates a bug fix or a small feature enhancement.

2. Comparison of different official versions

Community Edition

• Features: Free and open source, available to everyone.
• Applicable scenarios: Small and medium-sized enterprises, development environment.
• limit：
  • No official technical support is provided.
  • Lack of advanced features (such as backup, encryption and other commercial functions).

Enterprise Edition

• Features: Paid version, with full features and technical support.
• Advantages：
  • Includes backup tool (MySQL Enterprise Backup).
  • Provides advanced security features such as data encryption, audit logs, etc.
  • Provides performance monitoring and tuning tools (MySQL Enterprise Monitor).
• Applicable scenarios: Large enterprises, high security demand scenarios.

3. Comparison of main versions

MySQL 5.6

• Release time: 2013.
• New Features：
  • InnoDB Storage Engine Optimization: Support full-text indexing.
  • Copy function enhancement: Supports GTID (global transaction ID) to improve master-slave replication.
  • Performance optimization: Improved query optimizer.
• Applicable scenarios: Suitable for small and medium-sized businesses, but it is already outdated.

MySQL 5.7

• Release time: 2015.
• New Features：
  • JSON data support: Add JSON data type and related functions.
  • Performance optimization: Supports virtual columns and generated columns to improve query performance.
  • Copy Enhancement: Improve parallel replication.
  • Improved safety: SSL and password strength policies are supported by default.
• Applicable scenarios: Suitable for most application scenarios and is still widely used.

MySQL 8.0

• Release time: 2018.
• Major improvements：
  • Character set improvements: The default character set is changed to UTF-8 (utf8mb4), which is better supported by multilinguals.
  • Window Functions: Supports window functions and common table expressions (CTEs).
  • JSON Enhancement: Add more JSON functions.
  • Data persistence: Adopt the redo log refresh mechanism to improve crash recovery performance.
  • Performance optimization: Improve index management (such as invisible indexes), and support Hash Join.
  • Security: Enhanced password management and permission system.
• Applicable scenarios: Modern business systems, especially scenarios that require complex queries and multilingual support.

4. Comparison of derivative versions

MariaDB

• background: Developed by the founder of MySQL, based on the MySQL branch, aiming to remain open source.
• Features：
  • Provides faster query performance and more storage engine support (such as Aria, TokuDB).
  • Compatible with MySQL, but some functions and syntax are different.
• Applicable scenarios: Companies focusing on open source ecosystems.

Percona Server

• Features：
  • Developed based on MySQL Community Edition, enterprise-level features (such as stronger backup and monitoring).
  • Focus on performance and high availability.
• Applicable scenarios: High performance and high stability scenarios are required.

5. Summary of functions and performance comparison

6. "Partial Support" for JSON in MySQL 5.7

MySQL supports JSON data types since 5.7, but compared with 8.0, it has limited functionality, so it is called "partial support". Here are the main features and limitations of MySQL 5.7 support for JSON:

MySQL 5.7 support for JSON

• JSON data type：

  • Provides nativeJSONData type, which can store data in JSON format to ensure the syntax accuracy of the data.
  • The underlying layer will perform binary optimization storage of JSON data, which is more efficient than string storage.

• JSON functions：

  • Some basic JSON operation functions are provided, such as:
    • JSON_EXTRACT(json_doc, path): Extract the value of the specified path from JSON.
    • JSON_UNQUOTE(json_doc): Remove quotes from JSON values.
    • JSON_ARRAY(elements): Create a JSON array.
    • JSON_OBJECT(key, value): Create a JSON object.
  • Example:

    -- Create a JSON Data table
    CREATE TABLE test (
        id INT AUTO_INCREMENT PRIMARY KEY,
        data JSON
    );
    
    -- insert JSON data
    INSERT INTO test (data) VALUES ('{"name": "Alice", "age": 25}');
    
    -- extract JSON Values ​​in
    SELECT JSON_EXTRACT(data, '$.name') AS name FROM test; -- Output: "Alice"
    

• Index support：

  • MySQL 5.7 supports creating virtual columns on fields extracted from JSON data, thus indirectly supporting indexing.
  • Example:

    ALTER TABLE test ADD COLUMN name VARCHAR(50) GENERATED ALWAYS AS (JSON_UNQUOTE(JSON_EXTRACT(data, '$.name'))) STORED;
    CREATE INDEX idx_name ON test(name);
    

limitation

• Less functionality: The types of JSON functions in MySQL 5.7 are limited and cannot perform complex JSON operations, such as array operations.
• Inadequate performance optimization: The operation efficiency of JSON data is low, and some complex queries require manual optimization.
• Path expressions are not flexible enough: The support of JSON path expressions is relatively simple and lacks flexible processing of multi-level and arrays.

7. What is a window function?

Window Function is a powerful analysis tool in SQL, which is used to query results.Each lineExecute the calculation on the line and be able to access itData for rows before or after. Window functions allow users to performComplex calculations within a group, without the need to aggregate the data into a single result.

Basic features of window functions

• The result set will not be collapsed：

  • With aggregate functions (such asSUM、AVGetc.) Different, the calculation of the window function will not reduce the number of rows in the result set.
  • For example, apply a window function in a table with 100 rows, and the result is still 100 rows.

• Window definition：

  • passOVER()The clause defines a window (data range) that can specify grouping and sorting conditions.
  • Keywords:
    • PARTITION BY: Define the grouping range.
    • ORDER BY: Specify the order of calculations.

Common window functions

• Ranking function：

  • RANK(): Return to ranking, skip rankings with the same value.
  • DENSE_RANK(): Returns ranking, but does not skip rankings with the same value.
  • ROW_NUMBER(): Return the line number.

• Aggregation function extension：

  • SUM() OVER()、AVG() OVER()etc. Aggregate the data in the window.

• Offset function：

  • LAG(column, offset): Returns the value of a certain row before the current row.
  • LEAD(column, offset): Returns the value of a certain row after the current row.

• Other functions：

  • NTILE(n): Divide the result into n groups and return the group number to which each row belongs.

Syntax of window functions

SELECT 
    column,
    ROW_NUMBER() OVER (PARTITION BY column2 ORDER BY column3) AS row_num,
    SUM(column4) OVER (PARTITION BY column2) AS group_sum
FROM table_name;

Example

Suppose there is a sales data sheetsales：

Calculate the ranking of each row by region：

SELECT 
    id, 
    region, 
    sales,
    RANK() OVER (PARTITION BY region ORDER BY sales DESC) AS rank
FROM sales;

result:

Applicable scenarios for window functions

• Group ranking: For example, rank employees within each department.
• Cumulative sum: If the cumulative sales are calculated based on time.
• Monthly analysis: For example, compare the sales difference between the current line and the previous line.
• Data grouping: If the result is divided into a specified number of groups.

Limitations of MySQL 5.7

MySQL 5.7 Window functions are not supported, it is necessary to simulate the behavior of window functions through complex subqueries or user-defined variables. This is one of its important shortcomings.

Improvements to MySQL 8.0

MySQL 8.0 natively supports window functions, simple and efficient syntax, suitable for data analysis and complex query scenarios.

Summarize

This is all about this article about comparing different official versions of Mysql database. For more information about comparing different official versions of Mysql, please search for my previous articles or continue browsing the related articles below. I hope everyone will support me in the future!