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
How to install wordpress after downloading it
What does win11 activation status activity mean?
Website creation software
What are the regular expressions in php
The difference between array pointer and pointer array
What are the cloud servers?
python absolute value
Implementation method of VUE next page function
![[Web front-end] Node.js quick start](https://img.php.cn/upload/course/000/000/067/662b5d34ba7c0227.png)
