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

This article will explain in detail what are the new features of each version of MySQL. The editor thinks it is very practical, so I share it with you as a reference. I hope you can get something after reading this article.

I. differences in commonly used commands among different versions

Show innodb statusG mysql-5.1

Show engines innodb statusG mysql-5.5

User@'%' for grant authorized mysql-5.5 does not contain localhost

II. The main differences between the versions of MySQL 4.1Compact 5.0Compact 5.1Universe 5.5Universe 5.5Accord 5.6

Mysql-server-4.1

Support for subqueries has been added, UTF-8,GROUP BY statements have been added to the character set, ROLLUP,mysql.user tables have adopted better encryption algorithms, and innodb has begun to support separate tablespaces.

Mysql-server-5.0

Added support for Stored procedures, Views, Cursors, Triggers, XA transactions, and added INFORATION_SCHEMA system database.

Mysql-server-5.1

Added support for dynamic modification of general query log and slow query log at Event scheduler,Partitioning,Pluggable storage engine API, Row-based replication and Global levels.

Important features of the minor version:

5.1.2 slow queries at the microsecond level are supported. For information about slow query, please refer to http://linuxguest.blog.51cto.com/195664/721042.

Mysql-server-5.5

1) the default storage engine is changed to InnoDB

2) improve performance and scalability

a. Increased default thread concurrency (innodb_thread_concurrency)

b. Background input / output thread control (innodb_read_io_threads, innodb_write_io_threads)

c. Main thread input / output rate control (innodb_io_capacity)

d. Operating system memory allocator usage Control (innodb_use_sys_malloc)

e. Adaptive hash index (Hash Index) control, users can turn off adaptive hashing.

f. Insert buffering (Insert Buffering) control, users can turn off the insert buffering function of innodb.

g. By improving scalability through fast locking algorithms, innodb no longer uses posix threads, but uses native independent operations to accomplish mutex and read-write locking.

h. Restore group submission (Restored Group Commit)

i. Improve recovery performanc

j. Multiple buffer pool instance

k. Multiple rollback segments (Multiple Rollback Segments). The previous version of innodb can handle up to 1023 concurrent processing operations, but now mysql5.5 can handle concurrent transactions up to 128K

L. The inherent asynchronous input / output of Linux system, mysql5.5 database system also increases the concurrent number of input and output requests of linux system.

m. Extended change buffer: added delete buffer and clear buffer

n. Improved log system mutex and Flush list mutex

o. Improve the progress of the cleanup program, make it clear in mysql5.5 that the operation thread is a separate thread, and supports concurrency, and can be configured using innodb_purge_treads.

p. Improve metadata locking in transactions. For example, a statement in a thing needs to lock a table, which will be released at the end of the transaction, rather than at the end of the statement as before.

3) improve practicality

a. Semi-synchronous replication (Semi-synchronous Replication)

b. Copy Heartbeat

c. Automatic recovery of Relay logs (Automatic Relay Log Recovery)

d. Copy based on server filter items (Replication Per Server Filtering)

e. Copy supported data type conversions (Replication Slave Side Data Type Conversions) from the server

4) improve manageability and efficiency

a. Set up a fast index (Faster Index Creation)

b. Efficient data compression (Efficient Data Compression)

c. Provide efficient storage for large objects and variable length columns

d. The INFORMATION_ schema table has been added, and the new table provides specific information about InnoDB compression and transaction locking.

5) improve availability

a. New SQL syntax for SIGNAL/RESIGNAL

b. New table / index partitioning options. MySQL5.5 extends the range of table and index RANG and LIST partitions to non-integer columns and dates, and adds the ability to partition multiple columns.

6) improve detection and diagnosis

Mysql5.5 introduces a new performance architecture (performancn_shema,P_S) to monitor the performance of the mysql monitoring server at runtime.

Important features of the minor version:

Percona-server-5.5.18.23 supports group commit reference: http://www.orczhou.com/index.php/2011/12/time-to-group-commit-2/

Mysql-server-5.6

1) InnoDB can now limit excessive memory consumption when a large number of tables are opened (such as the one mentioned here) (third parties already have patches)

2) enhanced performance of InnoDB. Such as splitting kernel mutex;flush operations from the main thread, multiple perge threads, large memory optimization, etc.

3) InnoDB deadlock information can be recorded in error log to facilitate analysis

4) MySQL5.6 supports delayed replication, which allows you to control a time interval between slave and master to facilitate data recovery in special cases.

5) enhanced function of table partitioning

6) the MySQL row-level replication function is enhanced, which can reduce the overhead of disk, memory, network and other resources (only record the fields that can determine the row record)

7) implement crash-safe with Binlog

8) crc32 check is used for replication events to enhance the consistency of master/slave replication data

9) add log_bin_basename (in the past, there is no binlog location information in variables, so it is very inconvenient to supervise the database)

This is the end of the article on "what are the new features of each version of MySQL". I hope the above content can be of some help to you, so that you can learn more knowledge. if you think the article is good, please share it for more people to see.

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.