This is the 9th day of my participation in Gwen Challenge

Didi ~ ideal every day more text, reality a few days a text, hengpi: text?

Hahaha helpless ah

This is the second time to write an article for a friend! Last time was the taste of love, this time is the fragrance of learning ha ha 😋

This is a summary of the new features in the major version of MySQL

So, let’s take a look

Let’s go straight to 👇

MySQL official documentation

You can see there are three versions 5.6, 5.7, 8.0

Well, since 5.5 is missing, let’s remember its main features:

1. innodbIs from5.5This version starts to becomeDefault storage engine 😄
2. Introduced the trueUTF8— >utf8mb4

Why only two points? Because those are the only two things in 4YE’s brain hahaha

Let’s start exploring 5.6 ~😄

5.6

MySQL 5.6 Release Notes

Open the link above, 👆, as shown below, to introduce this new feature

What Is New in MySQL 5.6

If you need complete information, please go to 😄. After all, in addition to the new features, there is also an introduction to InnoDB as a whole!

innodb

Here are the following enhancements to the storage engine InnoDB 🐷

1. Full-text indexes can be created

2. You can change the InnoDB page size of an uncompressed table to 8KB or 4KB, the default is 16KB

   Note that only these three parameters are valid here (4, 8, 16).

   Parameters: innodb_page_size

3. The maximum size of InnoDB’s redo log file was increased from 4 gigabytes to 512 gigabytes.

   Parameters: innodb_log_file_size

4. Innodb compression level can be set from 0 to 9

   Parameters: innodb_compression_level

5. Innodb moves flush from main to another thread

6. The default is ibD, a separate tablespace

7. Undo logs can be saved in a separate table space

Partitioning

The following enhancements have been made to the partition 🐖

1. The maximum number of partitions increased to 8192

2. Support to display partition selection

   For example, SELECT * FROM t PARTITION (p0, p1) WHERE c < 5

MySQL NDB Cluster

Mysql Cluster, released as a standalone product in this release

Note that the storage engine is not the default InnoDB, but NDB

Replication and logging

Replication and logging

1. A binlog can be read from a remote server

   Parameters:

   – read – from – remote server, – R;

   --raw

2. Delay replication

   Parameter: CHANGE MASTER TO

Optimizer enhancements

Optimizer enhancement

1. This is where we start, and we can use EXPLAIN to view the execution plans for DELETE, INSERT, REPLACE, UPDATE, and other DML operations. Previously, it only supported SELECT operations.

2. Optimized subquery

3. The host cache

Configuration Changes

5.7

innodb

1. Add the DATA_GEOMETRY type to support spatial data types, previously bloBS

2. Innodb’s cache pool dump and load enhancements allow pages in the cache pool to be stored in percentage

   Parameter: Innodb_buffer_pool_dump_pct Default value is 25

3. InnoDB has added support for full-text parser plug-ins.

4. Multithreading is supported to refresh dirty pages in the buffer pool

   Memory data pages are called dirty because the contents of the data pages are inconsistent with those on disk

5. Innodb’s doublewrite is redundant because The fusion-io non-volatile Memory (NVM) file system provides atomic operations on Linux. Doublewrite is automatically disabled

6. The innodb_buffer_POOL_size parameter is dynamic, allowing you to resize the buffer pool without restarting the server.

7. InnoDB supports the use of spatial indexes to index spatial data types

8. Support for Local Partitions

9. The default row format is changed from Compact to DYNAMIC

other

Support JSON

As of MySQL 5.7.8, MySQL supports native JSON types.

Shocked (there is such a thing 🐖)

EXPLAIN can be used to get the execution plan of the explainable statement executed in the named connection

EXPLAIN [options] FOR CONNECTION connection_id;
Copy the code

Allow multiple trigger events at a certain point in time

BEFORE: For the combination of trigger events (INSERT, UPDATE, DELETE) and operation times (BEFORE, AFTER), a table can have at most one trigger, that is, only one trigger event at a certain point in time

4. Multi-source replication

You can back up the contents of multiple servers to one server

8.0

Suddenly found that each update is strengthening this innnodb 😄

innodb

1. You can check the number of index pages cached in the InnoDB buffer pool per index with INNODB_CACHED_INDEXES

   

2. Deadlock detection can be disabled using a new dynamic variable, innodb_deadlock_detect

3. Redo log optimization for concurrent writing to log buffers

4. The size of the log buffer can be dynamically adjusted with innodb_log_buffer_size

5. The undo log is no longer stored in the system table

6. Atomic DDL is supported to ensure complete success or failure of operations

7. As of MySQL 8.0.20, the Doublewrite buffer is stored in a Doublewrite file

other

Json enhancement

Default value

You can add default values BLOB, TEXT, GEOMETRY, and JSON for these types

Optimizer enhancement

1. Support for hiding indexes

ALTER TABLE t1 ALTER INDEX i_idx INVISIBLE;
ALTER TABLE t1 ALTER INDEX i_idx VISIBLE;
Copy the code

2. Indexes support descending sort

Support window function

Support regular expressions

(and 🐂 for this operation)

6. Backup lock

Allows the use of DML statements for online backup

conclusion

Which update will impress you the most after reading this article? 😝

For example, 4ye has a little impression on these points ~

1. Full text index of 5.6, innodb page size adjustment (4,8,16 KB), independent table space and execution plan enhancement, not only select. And the parameters of a table that’s that big at the end

2. 5.7 JSON, spatial data type DATA_GEOMETRY, and spatial index spatial, doubleWrite, optimization of dirty pages, and row format DYNAMIC (formerly Compact)

3. 8.0 re, hidden index and its index sort

Oh, and the first two sentences

1. innodbIs from5.5This version starts to becomeDefault storage engine 😄
2. Introduced the trueUTF8— >utf8mb4

The last

Welcome friends to discuss the question ~

If you think this article is good, please give it a thumbs-up 😝

Let’s start this unexpected meeting! ~

Welcome to leave a message! Thanks for your support! ヾ(≧▽≦*)o go!!

I’m 4ye and I’ll see you soon next time!!