mysql5.5 5.6 5.7 New features

1,5.5 default storage engine is InnoDB

2, 5.5 increase CPU multi-core processing capacity: Innodb_read_io_threads

Innodb_write_io_threads

3,5.5 Improving Disk IO

Increase the number of dirty page brushes added and merged insert quantity: innodb_io_capacity

Add Adaptive Refresh Dirty page feature: innodb_adaptive_flushing

(InnoDB the rules for flushing dirty pages is to enter the Innodb_buffer_pool dirty pages into the disk in the following three scenarios:

1, when the value set by the innodb_max_dirty_pages_pct is exceeded,

2, redo log ib_logfile when the file is full,

3, when the machine is idle. )

Faster data recovery time for 4,5.5INNODB

Redo log maximum can be set to 4GB, reduce the IO requirements, brush dirty page frequency reduction, greatly improve the performance of InnoDB write

5,5.5innodb supports multiple bufferpool:innodb_buffer_pool_instances

6,5.5 support for turning off adaptive hash indexing

Hash indexes can improve query performance, but high concurrency can cause Rw-latch to be earned, blocking processes

Can: Show engine InnoDB status\g; Monitor Sema-phores, if waits a lot then you should turn off this feature to improve write performance

The Innodb_adaptive_hash_index parameter turns the hash index on or off

7,5.5innodb can pledge to use the memory allocation program

Tcmalloc each thread has buffers that can significantly improve MySQL performance and memory usage

MySQL multithreaded small memory allocation

8,5.5 increase the default number of InnoDB thread concurrency

Innodb_thread_concurrency defaults to 0, which means that the number of concurrent numbers is not displayed

9,5.5 implementation of asynchronous I/O

Innodb_use_native_aio parameters

10,5.5 Recovery Group Submission

The group submission mode only supports sync_binlog=0,innodb_support_xa=0 in cases where the purpose is to ensure that the InnoDB storage engine redo the log and the Binlog log order

11,5.5 Update CREATE index faster, increase delete index does not lock table

Creating and deleting nonclustered indexes instead of copying the entire table content, the clustered index also requires that the clustered index be primary key and row data together, while the secondary

Index is stored separately, with a pointer pointing to primary key

12,5.5 support for creating compressed data pages

Barracuda file format and stand-alone tablespace, data page compression is supported, and additional CPU consumption is caused by compression

13,5.5 can dynamically turn off the data element statistics function

Innodb_stats_on_metadata=off 5.6 Default is off improve MySQL performance

14,5.5 Open InnoDB Strict check mode for increased security

Innodb_strict_mode=1 default is Off

Sql_mode

15,5.5 supports dynamic change of stand-alone table spaces

Set global innodb_file_per_table=1 default shared table space

Support dynamic Change InnoDB lock timeout time

Set global innodb_lock_wait_timeout= 10 default 50 sec

16,5.5 to increase the semi-synchronous replication function

4 Related Parameters on Master

Rpl_semi_sync_master_enabled=on means master turns on the semi-sync function

rpl_semi_sync_master_timeout=10000 default of 10000 milliseconds is 10 seconds to wait for slave, that is, one thing waits more than 10 seconds to downgrade to asynchronous replication

Rpl_semi_sync_master_wait_no_slave, default on, indicates that master waits for slave to accept a confirmation signal after each transaction commits, and does not turn on semi-synchronous replication if Off,slave is catching up

RPL_SEMI_SYNC_MASTER_TRACE_LEVEL=32, default 32, debug level to turn on semi-synchronous replication mode

Slave of 2 parameters

Rpl_semi_sync_slave_enabled=on means master turns on the semi-sync function

RPL_SEMI_SYNC_SLAVE_TRACE_LEVEL=32, default 32, debug level to turn on semi-synchronous replication mode

Semi-synchronous replication and Io_therad have a direct relationship with Sql_therad, that is, slave received from the library to the binary log will give the master a confirmation, and will not control Relay-log relay log no is executed.

The performance of asynchronous replication (throughput rate) is better than semi-synchronous replication.

17,5.5 copy enhancement, increased trunk log relay_log self-healing function

Relay_log_recovery=1

5.6 New features for synchronous replication

MySQL can automatically find synchronization points through Gtid, no need to specify Binglog and POS points (based on transaction, MyISAM not supported) Another cannot skip error, Gtid mode is not supported: Sql_slave_skip_counter parameters

Open parameter: Gtid-mode=on

Disable-gtid-unsafe-statements=on

Increase multi-threaded replication based on the library, multiple library replication can use multi-threaded replication, the table of a library multi-threaded replication is invalid

Open parameter: Slave_parallel_workers default is 0 does not turn on, the maximum can open 1024 threads.

Sub-query is changed to join mode after 18,5.6 version

19,5.6 new features Fast preheating Buffer_pool

Added in MY.CNF:

Innodb_buffer_pool_dump_at_shutdown=1 Backup of hot data to local Disk Ib_buffer_pool file when closed

Innodb_buffer_pool_load_at_startup=1 load hot data to memory at startup

20,5.6 new Add Binlog-row-imaeg parameter

When Binlog_format=row, Binlog-row-imaeg=full, the Binlog log records all records that affect the row

The Binlog-row-imaeg=minimal,binlog log records only the rows that are affected

21,5.6year (2) automatically converted to year (4) parameter

22,5.6 Online DDL function, that is, increase the field, add, delete, change, check are not affected, do not lock the table (5.5 lock table)

23,5.6 a SQL can use two indexes, support full-text indexing

24,5.6 Support explain update

mysql5.7 version

1. Security

Changes in the Mysql.user table

Changes in initialization mode

MySQL official has deleted the test database

Provides a more simple SSL secure access configuration, and the default connection is encrypted with SSL

You can set a password expiration policy for the user, and after a certain amount of time, force the user to change the password:

ALTER USER ' Jeffrey ' @ ' localhost ' PASSWORD EXPIRE INTERVAL;

You can "lock" the user to temporarily disable a user:

ALTER USER ' Jeffrey ' @ ' localhost ' account LOCK;

ALTER USER l ' Jeffrey ' @ ' localhost ' account UNLOCK;

2. Flexibility

JSON support

1. Hybrid storage of structured and unstructured data with the benefits of relational and non-relational databases

2. Able to provide complete transaction support

New features generated column

is in Create | When you ALTER table, you can perform operations on the new columns based on the existing columns of a table:

CREATE TABLE Triangle (

Sidea DOUBLE,

Sideb DOUBLE,

Sidec DOUBLE as (SQRT (Sidea * Sidea + sideb * sideb))

);

INSERT into triangle (Sidea, Sideb) VALUES (n), (3,4), (6,8);

Mysql> SELECT * from triangle;

+ ——-+ ——-+ —————— –+

| Sidea | Sideb | Sidec |

+ ——-+ ——-+ —————— –+

| 1 | 1 | 1.4142135623730951 |

| 3 | 4 | 5 |

| 6 | 8 | 10 |

+ ——-+ ——-+ —————— –+

Added SYS schema System library

The SYS schema quickly knows which statements use temporal tables, which user requests the most IO, which thread consumes the most memory, which indexes are useless indexes, etc.

The SYS schema contains a large number of views, using the performance schema information, to provide answers to practical questions in the form of views.

2. InnoDB Engine Enhancement

Optimized DDL operations

MySQL 5.7 supports renaming indexes and modifying the size of varchar, both of which need to be rebuilt in previous versions of the index or table

The temporary table InnoDB is optimized to be stored in a non-compressed, separate tablespace, each time the MySQL service is started, the table space is automatically created, stored by default in DataDir, whose path is specified by the parameter innodb_temp_data_file_path;

Modify the size of the buffer pool online

Support for online buffer pool Resize

Open Gtid Online

3, Sql_mode

Strict_trans_tables mode is enabled by default;

More complex feature support is implemented for Only_full_group_by mode, and is also enabled by default;

Other SQL mode, which is enabled by default, also has no_engine_substitution.

In 5.6, a 10-character varchar column is written to 15 characters, automatically truncated and given an alarm, and at 5.7, the error is thrown directly.

4. Optimize online operations, such as modifying the buffer pool, modifying the index name (non-primary key), modifying the replication FILTER, modifying the master without shutting down the slave thread, and many other features.

5, in 5.7, you can view the system parameters of MySQL in the table inside the INFORMATION_SCHEMA

6, support a table has multiple triggers, so that the original trigger table can also support the use of Pt-osc

7. Support direct view of execution plan for a connection online, such as explain for CONNECTION 1024

8, new Log_syslog option, can print the MySQL log to the system log file

9, before the MySQL 5.6, after the client CTRL + C will be directly exited AH MySQL client, after 5.7 will not quit the client but the current operation of the terminal

10.

11, support multi-source replication, you can merge multiple master data into one instance, if it is the same table, there will be a primary key and a unique index conflict risk, need to plan ahead.

12, support multi-threaded replication. Parallel replication

mysql5.5 5.6 5.7 New features