Mysql5.5&Mysql5.6&Mysql5.7特性

Mysql5.5 features, compared to Mysql5.1

Performance improvement

• Default InnoDB plugin engine. It has commit, rollback and crash recovery functions and is ACID compatible.

• Row-level lock (consistent non-locking read MVCC).

• Tables and indexes are stored in table space, and the table size is unlimited.

• Supports dynamic (primary key cache memory to avoid IO caused by primary key query) and compressed (supports data and index compression) row formats.

• InnoDB plugin file format Barracuda, supports table compression, saves storage, provides memory hit rate, and truncate table faster.

• The original InnoDB had only one UndoSegment, supporting up to 1023 concurrencies; now there are 128 Segments, supporting 128K concurrency (similarly, to solve the transaction rollback caused by high concurrency).

• Innodb_thread_concurrency defaults to 0, and the number of concurrent threads is unlimited. The optimal value can be set according to specific applications.

• Innodb_io_capacity can dynamically adjust the number of dirty pages refreshed to improve the performance degradation problem caused by the inability to keep up with the dirty pages refreshed during large batch updates. Default: 200, related to the IOPS of the hard disk.

• Make full use of the multi-core processing power of the CPU. innodb_read_io_threads threshold: 1-64 innodb_write_io_threads threshold: 1-64 can be flexibly set according to the read-write ratio of the database to give full play to the performance of multi-CPU and high-performance storage devices. Dynamic loading is not supported.

• Adaptively refresh dirty pages

• Hot data survives longer

• buffer pool multiple instances : The innodb_buffer_pool_instances parameter increases the number of innodb_buffer_pool instances, which greatly reduces the buffer pool's mutex contention and overheating.

• Asynchronous IO on Linux

• Re-support group submission

Stability improvement

• Supports semi-synchronous Replication.

• Add Relay Log self-healing function.

• Crash recovery.

• Introducing the red-black tree as an intermediate data structure for insertion sorting greatly reduces the time complexity and reduces recovery time.

• Thread Pool group queuing current limit

Mysql5.6 features, compared to 5.5

• Changes in default parameters

• Back_log queue

• Support full-text index

• Support online DDL create ,alter,drop

• You can specify the table space location when creating the table

  Create table external (x int unsigned not null primary key)data directory = '/volumes/ external1/data';

• The new parameter innodb_page_size can set the page size

• Integrated memcached API, you can use the API to directly access the innodb table , not SQL (reducing SQL parsing and query optimization costs)

• innodb read-only transaction, no need to set the TRX_ID field,

• Reduce internal data Structural overhead, reduce read view

• Only non-read-only transactions still require TRX_ID

innodb improvement points

• Innodb table space online migration (TransportableTablespaces)

• undo log can be independent from the system table space

• redo log can grow to a maximum of 512G

• Innodb background thread is independent

Optimizer improvements

• ICP

  You can directly filter data at the engine layer to avoid secondary table returns

  Save BP space and improve query performance

• ##BKA
  Full name Batch Key Access:
  When SQL accesses table data through the auxiliary index, a large number of random accesses are put into the cache and handed over to the MRR interface to merge them into sequential accesses.

• MRR
  Full name Multi Range Read:
  After the BKA algorithm is applied, random access is merged into sequential access through the MRR interface, and then the table is retrieved data.
  Change a large number of random accesses into sequential access. When retrieving a large amount of data through the auxiliary index, the performance is significantly improved
  The magnetic head does not need to seek back and forth, the page only needs to be read once, and the innodb linear read-ahead function is better utilized (64 consecutive pages are read in advance each time ).

• Statistical information is persistent and will not be lost after mysqld restarts

• explain statement supports insert, update, delete, replace statements, and supports JSON format

• Subquery optimization improvement.

• The plugin field of the user table mysql.user is not allowed to be empty. The default value is mysql_native_password, not mysql_old_password. The old password format is no longer supported;

• Add password expiration mechanism, you need to change the password after expiration, otherwise it may be disabled, or enter sandbox mode;

• Add password expiration mechanism, expire You need to change the password later, otherwise it may be disabled or enter sandbox mode;

• provides a simpler SSL security access configuration, and the default connection uses SSL encryption.

Flexibility

• MySQL database also provides support for JSON starting from version 5.7.8.

• Can store structured data and unstructured data in a mixed manner, while having the advantages of relational databases and non-relational databases

• can provide Complete transaction support

• generated column is a new feature introduced in MySQL 5.7. The so-called generated column means that this column in the database is calculated from other columns

Ease of use

• Before MySQL 5.7, if the user entered an incorrect SQL statement and pressed ctrl+c, although the execution of the SQL statement could be "ended", however, The current session will also be exited. MySQL 5.7 has improved this counterintuitive feature and no longer exits the session.

• MySQL 5.7 can explain a running SQL, which will be very useful for DBAs to analyze statements that take a long time to run.

• sys schema is a system library introduced in MySQL 5.7.7. It contains a series of views, functions and stored procedures. This project focuses on the ease of use of MySQL.

• For example: how to view redundant indexes in the database; how to obtain unused indexes; how to view SQL statements using full table scans.

Availability

• Online settings Copy filtering rules No longer need to restart MySQL, just stop the SQLthread. After the modification is completed, start the SQLthread.

• Modify the buffer pool size online.

• Online DDL MySQL 5.7 supports renaming indexes and modifying the size of varchar. In previous versions, these two operations required rebuilding the index or table.

• Enable GTID online. In previous versions, since enabling GTID online was not supported, if users want to upgrade a lower version database to a database version that supports GTID, they need to close the database first. Then it starts in GTID mode, which makes upgrading particularly troublesome.

Performance

• Performance improvements for temporary tables.

  Temporary tables are only visible in the current session

  The life cycle of the temporary table is the current connection (MySQL is down or restarted, the current connection ends)

• Read-only transaction performance improvements.

  MySQL 5.7 optimizes the overhead of read-only transactions and improves the overall performance of the database by avoiding allocating transaction IDs for read-only transactions, not allocating rollback segments for read-only transactions, and reducing lock competition. .

  Accelerate connection processing.

  Before MySQL 5.7, the initialization operations of variables (THD, VIO) were completed in the connection receiving thread. Now these tasks are sent to the working thread to reduce the workload of the connection receiving thread and improve The processing speed of the connection. This optimization will be very useful for applications that frequently establish short connections.

• Improvement of replication performance (supports multi-threaded replication (Multi-Threaded Slaves, referred to as MTS))

  The default configuration of MySQL is library-level parallel replication. In order to give full play to For the parallel replication function of MySQL 5.7, we need to configure slave-parallel-type to LOGICAL_CLOCK

• Support multi-source replication

Strictness changes

• STRICT_TRANS_TABLES mode is enabled by default

• ##More complex feature support is implemented for ONLY_FULL_GROUP_BY mode and is also supported. Enabled by default.

##Other sql modes enabled by default include NO_ENGINE_SUBSTITUTION

• ##Changes in default parameters

• The default binlog error operation is adjusted to ABORT_SERVER
• Under the previous option (binlog_error_action=IGNORE_ERROR), if If an error occurs and the binlog cannot be written, mysql-server will record the error in the error log and forcibly turn off the binlog function. This will cause the mysql-server to continue running in the mode of not recording binlog, causing the slave library to be unable to continue to obtain the master. Binlog of the library.

  ##Enable binlog security when mysql crashes.

• #The default is to lower slave_net_timeout.

  #Different installations

• mysql_install_db is no longer recommended. It is recommended to change to mysqld --initialize to complete the instance initialization. If there is already a data file in the target directory pointed by datadir, then There will be [ERROR] Aborting;

• If --initial-insecure is added during initialization, a root@localhost account with an empty password will be created. Otherwise, a root@localhost account with a password will be created, and the password will be written directly in the log-error In the log file; new users need to change their password immediately after logging in, otherwise they will not be able to continue their work.

The above is the content of the features of Mysql5.5&Mysql5.6&Mysql5.7. For more related content, please pay attention to the PHP Chinese website (m.sbmmt.com)!