Comparing MySQL® 5.7 and 8.0

MySQL® 8.0 is a major update compared to MySQL® 5.7, released almost ten years ago. This version offers significant improvements in the DBMS architecture and functionality.

Transactional data dictionaryTransactional data dictionary

MySQL® 8.0 has replaced the separate .frm file-based metadata storage system with the unified transactional data dictionary.

Advantages of the new approach:

• Eliminates the metadata desynchronization issues in case of faults.
• No locks when executing DDL operations.
• Optimized table structure modification without long-term locks.
• More reliable database structure backup and restore operations.

SecuritySecurity

MySQL® 8.0 implements improved security mechanisms.

Key improvements:

• caching_sha2_password instead of mysql_native_password as the default mechanism.
• Improved password storage system.
• Advanced protection against modern attack vectors.
• Backward compatibility with previous authentication mechanisms.

SQL featuresSQL features

• MySQL® 8.0 provides built-in support of window functions to facilitate complex computations:

  SELECT
      sale_date,
      product_id,
      sales_amount,
      ROUND(AVG(sales_amount) OVER (
          ORDER BY sale_date
          ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
      ), 2) as moving_average_3days
  FROM daily_sales;
  

• MySQL® 8.0 implements Common Table Expressions (CTE), which allows you to create better structured queries:

  WITH customer_totals AS (
      SELECT
          customer_id,
          region,
          SUM(order_amount) as total_spent
      FROM orders
      GROUP BY customer_id, region
  )
  SELECT * FROM customer_totals;
  

Working with JSONWorking with JSON

MySQL® 8.0 offers advanced features for working with JSON data:

• Creating indexes:

  ALTER TABLE users
  ADD INDEX idx_profile_age ((CAST(profile->>'$.age' AS UNSIGNED)));
  

• Optimized syntax for JSON:

  -- Value extraction
  SELECT 
      id,
      profile->>'$.name' as name,
      profile->>'$.contacts.email' as email
  FROM users;
  

Additional featuresAdditional features

New features in MySQL® 8.0:

• Invisible indexes for testing index structures in the production environment.
• EXPLAIN ANALYZE for analyzing actual query plans.
• Optimized metadata processing system to reduce the number of locks.

PerformancePerformance

MySQL® 8.0 performance test results:

• Single-host configurations demonstrate higher performance with MySQL® 5.7.
• Multi-host configurations show improved stability with MySQL® 8.0.

The choice of version depends on your specific performance requirements.

Upgrading to MySQL® 8.0Upgrading to MySQL® 8.0

Starting October 2023, official support of MySQL® 5.7 has been discontinued. Advantages of upgrading to MySQL® 8.0:

• Access to new features and improved security mechanisms.
• Yandex Cloud migration tools.
• Long-term support of the new version.

For more details about migration, see MySQL® version upgrade.