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
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
##Enable binlog security when mysql crashes.
#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 (www.php.cn)!