mysql8.0 is coming

MySQL is an open source small relational database management system, developed by the Swedish MySQL AB company. Currently, MySQL is widely used in small and medium-sized websites on the Internet. Due to its small size, fast speed, low total cost of ownership, and especially the characteristics of open source, many small and medium-sized websites choose MySQL as their website database in order to reduce the total cost of website ownership.

The MySQL development team announced the release of MySQL 8.0.0 development milestone version (DMR) on the 12th! Some people may be surprised why MySQL jumped from 5.x to 8.0. In fact, the MySQL 5.x series has lasted for many years. It was 5.1 before the acquisition by Oracle, and has been maintained at 5.x since the acquisition, such as 5.5, 5.6, 5.7 and so on. In fact, if you follow the original release rhythm, you can think of 5.6.x as 6.x and 5.7.x as 7.x. Therefore, we just changed the version naming method.

However, the MySQL 8.0.0 development version released this time still has many highlights.

Highlights of MySQL 8.0.0

Transactional data dictionary, completely separated from the MyISAM storage engine

Really put the data dictionary into some tables in InnoDB, no longer FRM, TRG, PAR files are needed! Information Schema now appears as a view of the data dictionary tables. In principle, there is no need for the MyISAM data table type at all, and all system tables can be placed in InnoDB.

SQL Role

A role is a collection of permissions. You can create roles, grant and remove roles to a user. This is convenient for permission management.

utf8mb4 character set will become the default character set and support Unicode 9

The default character set will be changed from latin1 to utf8mb4, and the default ordering collation will be changed from latin1_swedish_ci to utf8mb4_800_ci_ai.

Invisible indexes

You can set some indexes to be invisible so that the SQL optimizer will not use it, but it will continue to be updated in the background. Visibility can be restored at any time when needed.

Bit operations can be performed on binary data

Not only can bit operations be performed on BIGINT, but also supports bit operations on [VAR]BINARY/[TINY|MEDIUM|LONG]BLOB starting from 8.0 .

Improved operations on IPv6 and UUID

INET6_ATON() and INET6_NTOA() can now perform bit operations, because INET6_ATON() now returns the VARBINARY(16) data type (128 Bit). UUID operations have been improved, and three new functions UUID_TO_BIN(), BIN_TO_UUID() and IS_UUID() have been introduced. MySQL does not have special IPv6 and UUID data types, but is stored in the VARBINARY(16) data type.

Persistent global variables

You can use SET PERSIST to set persistent global variables, which will remain even if the server is restarted.

Improvements in Performance Schema of Performance Database

For example, more than 100 indexes have been added to the performance database to enable faster retrieval.

Reconstruct the SQL analyzer

Continuously and gradually improve the SQL analyzer. The old parser had severe limitations due to its syntactic complexity and top-down parsing approach, making it difficult to maintain and extend.

Cost Model

InnoDB buffers can now estimate how many tables and indexes are in the main memory cache. This allows the optimizer to know whether the data can be stored in memory or not when choosing an access method. Must be stored on disk.

Histograms

By using histograms, users or DBAs can make statistics on data distribution, which can be used for query optimization to find optimized query solutions.

Improve scanning performance

Improved the performance of InnoDB range query, which can improve the performance of full table query and range query by 5-20%.

Reconstructing BLOB

Reconstructing BLOB speeds up fragment read/update operations and can speed up JSON data operations.

Persistent auto-increment value

InnoDB will persist the maximum value of the auto-increment sequence to the redo log. This improvement also fixes a very old bug number 199.

Temporary table

Cancel support for compressed temporary tables and store the metadata of temporary tables in memory.

For more important improvements and details, please refer to the MySQL 8.0.0 release announcement [1] and here [2].

Download

Currently 8.0.0 is still a development version. If you want to experience and test the latest features, you can download the installation packages for each platform from dev.mysql.com[3]. However, the MySQL software package is getting larger and larger, and the binary package on the Linux platform is nearly 1 GB. If used in a production environment, please continue to use the 5.7 series before 8.0 enters the stable version. The latest version is the 5.7.15 GA version - which is only more than 600 M.

The latest source code is placed on GitHub. Interested friends can check it out. Many of them are contributions from Chinese people.

Starting from MySQL8.0, the hidden index feature is supported, which is the so-called invisible index. For invisible indexes, the optimizer will simply ignore them. We can influence the behavior of the optimizer through this feature. In addition, this can also be regarded as a buffer before dropping an index. After temporarily setting the index to be invisible, then observe whether the application is normal or if there is an error or something. If everything is OK, then delete it finally.

Corresponding release note of 8.0.0:

Test

# 创建一个普通的表t1，只带主键
mysql> create table t1 (a int primary key auto_increment, b int, c int, d int);
Query OK, 0 rows affected (0.67 sec)
# 增加一个索引
mysql> alter table t1 add key(b);
Query OK, 0 rows affected (0.06 sec)
Records: 0  Duplicates: 0  Warnings: 0
mysql> show indexes from t1\G
*************************** 1. row ***************************
        Table: t1
   Non_unique: 0
     Key_name: PRIMARY
 Seq_in_index: 1
  Column_name: a
    Collation: A
  Cardinality: 0
     Sub_part: NULL
       Packed: NULL
         Null:
   Index_type: BTREE
      Comment:
Index_comment:
      Visible: YES
*************************** 2. row ***************************
        Table: t1
   Non_unique: 1
     Key_name: b
 Seq_in_index: 1
  Column_name: b
    Collation: A
  Cardinality: 0
     Sub_part: NULL
       Packed: NULL
         Null: YES
   Index_type: BTREE
      Comment:
Index_comment:
      Visible: YES
2 rows in set (0.01 sec)
从show indexes的visible列显示了，这两个索引都是可见的。
# Load some data
insert into t1 select NULL, rand()*100000, rand()*10000,rand()*10000;
insert into t1 select NULL, rand()*100000, rand()*10000,rand()*10000 from t1;
insert into t1 select NULL, rand()*100000, rand()*10000,rand()*10000 from t1;
....
analyze table t1;
mysql> explain select * from t1 where b > 5000 limit 10;
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+-----------------------+
| id | select_type | table | partitions | type  | possible_keys | key  | key_len | ref  | rows | filtered | Extra            |
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE     | t1   | NULL     | range | b         | b   | 5     | NULL | 1932 |   100.00| Using index condition |
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec
可以看到索引b被使用到
# 修改索引b为不可见
mysql> alter table t1 alter index b invisible;
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0
mysql> show indexes from t1\G
*************************** 1. row ***************************
        Table: t1
   Non_unique: 0
     Key_name: PRIMARY
 Seq_in_index: 1
  Column_name: a
    Collation: A
  Cardinality: 2048
     Sub_part: NULL
       Packed: NULL
         Null:
   Index_type: BTREE
      Comment:
Index_comment:
      Visible: YES
*************************** 2. row ***************************
        Table: t1
   Non_unique: 1
     Key_name: b
 Seq_in_index: 1
  Column_name: b
    Collation: A
  Cardinality: 2029
     Sub_part: NULL
       Packed: NULL
         Null: YES
   Index_type: BTREE
      Comment:
Index_comment:
      Visible: NO
2 rows in set (0.01 sec)
mysql> explain select * from t1 where b > 5000 limit 10\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t1
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 2048
     filtered: 33.33
        Extra: Using where
1 row in set, 1 warning (0.00 sec)
当索引被修改为invisible后，优化器将不再选择这个索引
# 将索引重新修改为可见
mysql> alter table t1 alter index b visible;
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0
mysql> explain select * from t1 where b > 5000 limit 10\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t1
   partitions: NULL
         type: range
possible_keys: b
          key: b
      key_len: 5
          ref: NULL
         rows: 1932
     filtered: 100.00
        Extra: Using index condition
1 row in set, 1 warning (0.00 sec)
# 你也可以在创建索引的时候显式指定是否可见
mysql> alter table t1 add key(c) invisible;
Query OK, 0 rows affected (0.12 sec)
Records: 0  Duplicates: 0  Warnings: 0
mysql> show indexes from t1 where key_name = 'c'\G
*************************** 1. row ***************************
        Table: t1
   Non_unique: 1
     Key_name: c
 Seq_in_index: 1
  Column_name: c
    Collation: A
  Cardinality: 1848
     Sub_part: NULL
       Packed: NULL
         Null: YES
   Index_type: BTREE
      Comment:
Index_comment:
      Visible: NO
1 row in set (0.01 sec)
# 或者在建表时指定关键字
mysql> create table t2 (a int primary key, b int, key(b) invisible);
Query OK, 0 rows affected (0.67 sec)
# 但primary key不可以设置为不可见
mysql> drop table t2;
Query OK, 0 rows affected (0.03 sec)
mysql> create table t2 (a int, b int, primary key(a) invisible);
ERROR 3522 (HY000): A primary key index cannot be invisible