Shulou(Shulou.com)05/31 Report--

This article mainly explains "what are the new features of MySQL 8.0.23". The content of the explanation is simple and clear, and it is easy to learn and understand. Please follow the editor's train of thought to study and learn what are the new features of MySQL 8.0.23.

Prior to MySQL 8.0.23, all the columns in the table were visible (if you had permissions). You can now specify an invisible column that will be hidden from the query. If explicitly referenced, it can be found.

Let's see what it looks like:

Create table table1 (id int auto_increment primary key, name varchar (20), age int invisible)

We can see the INVISIBLE keyword in the Extra column in the table structure:

Desc table1 +-+ | Field | Type | Null | Key | Default | Extra | +-+ -+-+ | id | int | NO | PRI | NULL | auto_increment | | name | varchar (20) | YES | | NULL | | age | int | YES | | NULL | INVISIBLE | +- -+

Looking at the show create table statement, notice that there is one difference. When I create the table, I want to see the INVISIBLE keyword, but this is not the case:

Show create table table1\\ G * 1. Row * Table: table1 Create Table: CREATE TABLE `table1` (id int NOT NULL AUTO_INCREMENT, name varchar (20) DEFAULT NULL, age int DEFAULT NULL / *! 80023 INVISIBLE * /, PRIMARY KEY (id)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

But I confirm that this statement makes the age column invisible when the table is created. So we have two different syntax to create invisible columns.

You can also see the relevant information in INFORMATION_SCHEMA:

SELECT TABLE_NAME, COLUMN_NAME, EXTRA FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = 'test' AND TABLE_NAME =' table1' +-+ | TABLE_NAME | COLUMN_NAME | EXTRA | +-+ | table1 | id | auto_increment | | table1 | name | table1 | age | INVISIBLE | +-+ |

Insert some data and continue to observe:

Insert into table1 values (0Magnum mysql lefred','44', 25), (0Personnal Column count doesn't match value count at row, 35), (0Jing'ql); ERROR: 1136: Column count doesn't match value count at row 1

As expected, an error will be reported if we do not refer to it in the insert statement. Reference these columns:

Insert into table1 (id, name, age) values (0Magnum MySQL, 25), (0rect Kennyama, 35), (0Coe 'lefred','44'); Query OK, 3 rows affected (0.1573 sec)

Query the data in the table:

Select * from table1; +-+-+ | id | name | +-+-+ | 1 | mysql | | 2 | kenny | | 3 | lefred | +-+-+

Once again, as expected, we see that the invisible column is not displayed.

If we specify it:

Select name, age from table1; +-+-+ | name | age | +-+-+ | mysql | 25 | kenny | 35 | lefred | 44 | +-+-+

Of course, we can change the column from visible to invisible or invisible to visible:

Alter table table1 modify name varchar (20) invisible, modify age integer visible; Query OK, 0 rows affected (0.1934 sec) select * from table1; +-+-+ | id | age | +-+-+ | 1 | 25 | 2 | 35 | 3 | 44 | +-+-+

I am very happy with this new feature, and we will see why this is an important feature for InnoDB in the next article.

This article is the second in a series of articles related to MySQL invisible columns.

This article explains why invisible columns are important to the InnoDB storage engine.

First of all, let me briefly explain how InnoDB handles primary keys and why a good primary key is important. Finally, why the primary key is also important.

How does InnoDB store data?

InnoDB stores data in the tablespace. These records are stored and sorted with a clustered index (primary key): they are called indexes to organize tables.

All secondary indexes also use the primary key as the rightmost column in the index (even if it is not exposed). This means that when a record is retrieved using a secondary index, two indexes are used: the secondary index points to the primary key used to eventually retrieve the record.

The primary key affects the ratio between random and sequential Imax O as well as the size of the secondary index.

Random primary key or sequential primary key?

As mentioned above, the data is stored in the tablespace in the clustered index. This means that if you don't use sequential indexes, InnoDB has to rebalance all pages in the tablespace when inserting.

If we use InnoDB Ruby to illustrate this process, the following picture shows how the tablespace is updated when a record is inserted with a random string as the primary key:

Every time there is an insert, almost all pages are touched.

When using the self-incrementing type as the primary key, the same insert:

In the case of adding the primary key, only the first and last pages will be touched.

Let's explain this with a high-level example:

Assuming that an InnoDB page can store four records (disclaimer: this is just a fictional example), we insert some records using a random primary key:

Insert a new record with the primary key AA!

Modify all pages to "rebalance" the clustered index, and in the case of consecutive primary keys, only the last page will be modified. Imagine the extra work that has to be done when thousands of inserts occur.

This means that it is important to choose a good primary key. There are two points to note:

Primary keys must be contiguous.

The primary key must be short.

What about UUID?

I usually recommend using self-incrementing (or bigint) as the primary key, but don't forget to monitor them!

But I also understand that more and more developers like to use uuid.

If you are going to use UUID, you should read the support for UUID in MySQL8.0, and this article recommends that you use binary (16) to store UUID.

Such as:

CREATE TABLE t (id binary (16) PRIMARY KEY); INSERT INTO t VALUES (UUID_TO_BIN (UUID ()

However, I don't totally agree with this point of view. Why?

Because using uuid_to_bin () may change the sequential behavior of MySQL's UUID implementation (see the additional section for more information).

But if you need UUID, you need to pay a price for large indexes, so don't waste storage and memory on secondary indexes that you don't need:

Select * from sys.schema_unused_indexes where object_schema not in ('performance_schema',' mysql')

There are no primary keys?

For the InnoDB table, when no primary key is defined, the first unique non-null column is used. If no columns are available, InnoDB creates a hidden primary key (6 bits).

The problem with this type of primary key is that you can't control it, and to make matters worse, this value is global for all tables that don't have a primary key, and contention can occur if you write multiple times to these tables at the same time (dict_sys- > mutex).

The use of invisible columns

With the new invisible columns, if the application does not allow the addition of new columns, we can now add the appropriate primary key to the table without a primary key.

First find these tables:

SELECT tables.table_schema, tables.table_name, tables.engine FROM information_schema.tables LEFT JOIN (SELECT table_schema, table_name FROM information_schema.statistics GROUP BY table_schema, table_name Index_name HAVING SUM (case when non_unique = 0 and nullable! = 'YES' then 1 else 0 end) = count (*) puks ON tables.table_schema = puks.table_schema AND tables.table_name = puks.table_name WHERE puks.table_name IS null AND tables.table_type =' BASE TABLE' AND Engine= "InnoDB" +-+ | TABLE_SCHEMA | TABLE_NAME | ENGINE | +-+ | test | table2 | InnoDB | +- -- +

You can also use the check plug-in in MySQL Shell: https://github.com/lefred/mysqlshell-plugins/wiki/check#getinnodbtableswithnopk

Let's look at the table definition:

Show create table table2\\ G * 1. Row * Table: table2 Create Table: CREATE TABLE table2 (name varchar (20) DEFAULT NULL, age int DEFAULT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

The data in it:

Select * from table2; +-+-+ | name | age | +-+-+ | mysql | 25 | kenny | 35 | lefred | 44 | +-+-+

Now add the specified invisible primary key:

Alter table table2 add column id int unsigned auto_increment primary key invisible first

Insert a new record:

Insert into table2 (name, age) values ('PHP', 25); select * from table2; +-+-+ | name | age | +-+-+ | mysql | 25 | kenny | 35 | lefred | 44 | PHP | 25 | +-+-+

If we want to view the primary key:

Select id, table2.* from table2; +-+ | id | name | age | +-+ | 1 | mysql | 25 | 2 | kenny | 35 | 3 | lefred | 44 | | 4 | PHP | 25 | +-+

Summary

Now you know why primary keys are important in InnoDB and why a good primary key is more important.

Starting with MySQL8.0.23, you can use invisible columns to solve tables without primary keys.

Extra

Just for fun and to illustrate my view of using UUID_TO_BIN (UUID ()) as the primary key, let's repeat this example again using UUID as an invisible column.

Alter table table2 add column id binary (16) invisible first; alter table table2 modify column id binary (16) default (UUID_TO_BIN (UUID () invisible; update table2 set id=uuid_to_bin (uuid ()); alter table table2 add primary key (id)

Nothing special so far, but it takes some skill to create an invisible primary key.

Query:

Select * from table2; +-+-+ | name | age | +-+-+ | mysql | 25 | kenny | 35 | lefred | 44 | +-+-+

Now, let's insert a new piece of data into this table:

Insert into table2 (name, age) values ('PHP', 25); select * from table2; +-+-+ | name | age | +-+ | PHP | 25 | mysql | 25 | kenny | 35 | lefred | 44 | +-+-+

Mmmm... Why is PHP the first line now?

Because uuid () is not continuous.

Select bin_to_uuid (id), table2.* from table2 +-- + | bin_to_uuid (id) | name | age | +- -+ | 05aedcbd-5b36-11eb-94c0-c8e0eb374015 | PHP | 25 | | af2002e8-5b35-11eb-94c0-c8e0eb374015 | mysql | 25 | | af20117a-5b35-11eb-94c0-c8e0eb374015 | kenny | 35 | | af201296-5b35-11eb-94c0-c8e0eb374015 | lefred | 44 | + -+

What other choice do we have?

Yes, if we refer to the official file, we can use the uuid_to_bin () function.

Alter table table2 add column id binary (16) invisible first; alter table table2 modify column id binary (16) default (UUID_TO_BIN (UUID (), 1)) invisible; update table2 set id=uuid_to_bin (uuid (), 1)

Now each time we insert a new record, the insertion is in the order as expected:

Select bin_to_uuid (id,1), table2.* from table2 +-- + | bin_to_uuid (id 1) | name | age | +-+ | 5b3711eb-023c-e634-94c0-c8e0eb374015 | mysql | 25 | | 5b3711eb-0439-e634-94c0-c8e0eb374015 | kenny | 35 | | 5b3711eb-0471-e634-94c0-c8e0eb374015 | lefred | 44 | | f9f075f4 | -5b37-11eb-94c0-c8e0eb374015 | PHP | 25 | | 60ccffda-5b38-11eb-94c0-c8e0eb374015 | PHP8 | 1 | | 9385cc6a-5b38-11eb-94c0-c8e0eb374015 | Python | 20 | +-+

We have seen the new invisible column function from MySQL8.0.23 before. If the primary key is not defined, how can we use it to add the primary key to the InnoDB table.

As mentioned earlier, good primary keys are important for InnoDB (storage, IOPS, secondary indexes, memory, etc.), but primary keys also play an important role in MySQL: copy!

Asynchronous replication

When using traditional replication, if you modify a row of records (update and delete), the records to be modified on the copy are identified by an index and, of course, if there is a primary key. The hidden global 6-byte primary key automatically generated by InnoDB will never be used because it is global, so there is no guarantee that the source and replica will be the same. You shouldn't even think about it.

If the algorithm cannot find a suitable index, or can only find a non-unique index or contains a null value, a hash table is needed to identify the table record. The algorithm creates a hash table that contains a record of the update or delete operation and uses the key as the complete image before the row. The algorithm then iterates through all the records in the target table and uses the selected index if it is found, otherwise a full table scan is performed (see official file).

Therefore, if your application does not support the use of additional keys as primary keys, using hidden columns as primary keys is one way to speed up replication.

Mysql > create table T1 (name varchar (20), age int); mysql > insert into T1 values ('mysql',25), (' kenny', 35), ('lefred', 44)

Now add a self-incrementing column as the primary key:

Mysql > alter table T1 add id int auto_increment primary key first

Then add a record according to the INSERT statement specified in the application:

Mysql > insert into T1 values ('python',20); ERROR: 1136: Column count doesn't match value count at row 1

The best way is to modify the INSERT statement of the application, but is it possible?

How many applications still use SELECT * and reference columns such as col [2]?

If so, you have two ways:

Analyze all queries and use the rewrite query plug-in

Use invisible columns

In this case, the choice is easy (at least for a lazy person like me).

Mysql > alter table T1 modify id int auto_increment invisible; mysql > insert into T1 values ('python',20); Query OK, 1 row affected (0.0887 sec)

It's simple, isn't it?

Group replication

MySQL InnoDB Cluster uses another kind of replication: Group Replication.

One of the requirements for using group replication is to have a primary key (which is why sql_require_primary_key can be used).

We use the example above to restructure the table without a primary key to check whether the instance can be used as an InnoDB Cluster:

Https://lefred.be/wp-content/uploads/2021/01/Selection_9991017-1024x561.png

The tip is clear that changes on this table are not copied to other nodes.

Add an invisible primary key and recheck:

This means that if the application uses a table that does not have a primary key, it is not allowed to migrate to a highly available schema such as MySQL InnoDB Cluster, thanks to invisible columns.

Thank you for your reading, the above is the content of "what are the new features of MySQL 8.0.23?" after the study of this article, I believe you have a deeper understanding of what the new features of MySQL 8.0.23 have, and the specific use needs to be verified in practice. Here is, the editor will push for you more related knowledge points of the article, welcome to follow!

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.