<Next>
Better application availability: Online DDL/mode changesToday, Web-based applications are designed to rapidly evolve and adapt to business and production needs. When an application must quickly adapt to a new product line or product, the backend database model must also adapt, but the application must be able to operate normally at the same time. Mysql5.6 provides the online mode to this extent. The new syntax is alter table ddl, and other additions include: Create index.
Drop Index
Change auto_increment value for a column
Add/drop foreign key
Rename Column
Change row format, key_block_size for a table
Change column null, not_null
Add, drop, reorder column DBAs and developers can add indexes or other modification operations on standard InnoDB tables, and the database can still be used by applications. This is very beneficial for fast iterative applications. In particular, developers need the flexibility of the model to adapt to the changing business needs.
Better development flexibility: Adding nosql to InnoDB
Mysql5.6 provides memcached APIs for InnoDB. Add a new memcached daemon plug-in for mysqld, so that the memcached protocol is directly mapped to the InnoDB API of the Local Machine, allowing developers to use the existing memcached client to directly ignore the parsing consumption of the query syntax, search and modify data directly. These APIs can reuse the existing memcached database and client, and integrate a persistent, accident security, and transaction database backend to expand the memcached function. The implementation is as follows: The test results show that the performance of the Set/insert operation must be improved by about 9 times in terms of the overall throughput: not only do developers and DBAs achieve better performance and flexibility, it also reduces the complexity and compresses the previous separate cache and database layer to a single data management layer, eliminating the cache consistency maintenance overhead.
Better development flexibility: Use Cases of InnoDB Extension
New Features and optimizers of mysql5.6 extend InnoDB to more use cases, so that developers can standardize a single storage engine to simplify applications.
New full-text index (FTS)-To replace MyISAM's FTS, InnoDB now allows developers to create Fulltext indexes on tables to mark text content and accelerate app search for words and phrases. Full-text search of InnoDB supports natural language/logic mode, adjacent search, and relevance sorting. A simple example is as follows: Create Table quotes
(ID int unsigned auto_increment primary key, author varchar (64), quote varchar (4000), source varchar (64), Fulltext (quote)
) Engine = InnoDB;
Select author as "apple" From quotes
Where match (quote) against ('apple' in natural language mode );New removable tablespace-InnoDB generates one for each table. the IDB file can be freely transmitted between the physical storage device and the database server. When a new table is created, the developer can specify a path other than the MySQL DATA space to store the file. IDB file. This allows you to transfer the more "hot" or busy table data to an external network storage device (SSD, HDD), so as not to compete for the costs of applications or databases. This new feature also supports fast and seamless applications, allowing users to conveniently export/import InnoDB tables
Transfer to another running MySQL server. Example export: Create Table T (C1 INT) engine = InnoDB;
Flush table t for export; -- quiesce the table and create the meta data file
$ Innodb_data_home_dir/test/T. cfg
Unlock tables;
Corresponding import:
Create Table T (C1 INT) engine = InnoDB; -- if it doesn't already exist
Alter table t discard tablespace;
-- The user must stop all updates on the tables, prior to the import
Alter table t import tablespace;
Improved replication and high availabilityReplication is the most commonly used MySQL function, and is often manifested in scalability and high availability (ha ). Mysql5.6 includes new features that allow developers to build self-healing replication topologies and high-performance master-slave clusters for next-generation networks, cloud computing, Social and mobile applications and services. Key points include:
New global transaction identifier (gtids)(Not familiar with the introduction, You can Google related gtid information)-gtids to strengthen database Master/Slave consistency, fault recovery, and fault tolerance capabilities. Allows tracking replication transaction integrity, technologies related to the implementation are used to copy the Master/Slave topology, provide a self-healing recovery point, and enable DBAs and developers to easily identify the latest slave after the master fails. By directly constructing a binary log stream, gtid does not require other third-party plug-ins to provide such a level of tracking reports.
New MySQL replication tool-A new Python tool can be used to use gtid to provide replication management and monitoring functions. It can disable or replace the failed master. In this way, if a planned or unplanned shutdown accident occurs without operator intervention, additional third-party high availability solutions, protective networks, and cloud services are not required.
New multi-thread slave-Mode-based division of working threads allows parallel update operations, not in the original sequence. This will bring about the benefits of updating the data isolated from applications in the database. For example, multi-tenant system: the test shows that 10 worker threads are enabled simultaneously in a certain environment, and the performance is improved by more than 5 times.
New binary log group submission (BGC)-In mysql5.6, the replication of the master group is written into the binary file in the form of a group instead of a one-time commit, which significantly improves the performance of the master topology. BGC also supports better fine-grained locks, which reduces lock wait and increases performance gain again. The effect is as follows: in terms of the throughput of the master server, the performance of mysql5.6 is 5.5 higher than that of 180%. BGC largely eliminates the need for users to choose between master performance overhead, scalability, and high-performance benefits provided by MySQL replication.
NewOptimization Based on Row Replication-Mysql5.6 provides a new option BINLOG-row-image = minimal, which allows the application to only copy the image data of rows, instead of worrying about whether the DML operation has been performed on the row. This improves the replication throughput of the Master/Slave machine and reduces the disk space, network resources, and memory occupied by binary logs.
New accident Security slave-Mysql5.6 stores the table-based binary log file, so that the machine can automatically roll back to the last copied version without the Administrator's intervention. She not only reduces operation overhead, but also reduces the risk of disk data loss when the database tries to recover corrupted files. Furthermore, if the primary server suddenly suffers an accident and the binary file is lost, the server can automatically restore it to the location where the current file can be correctly read.
New Replication Validation and-Mysql5.6 can ensure the integrity of the data being copied to the slave server by checking whether the data is damaged and returning an error message before the slave server data is damaged, this prevents the slave server from being damaged.
New delayed Replication-Mysql5.6 provides a protection measure that allows developers to add definition latencies in the replication stream to prevent errors generated on the master server from being transmitted to the slave server. By configuring the time delay between the master and slave, if a fault or accident occurs, the slave server can be upgraded to a new master server, facilitating the database to restore to the previous state. It can also check the status of a database before errors and failures, instead of re-loading a backup.
Improved performance modelIn MySQL, the MySQL performance model is introduced. It is designed to provide a measurement time point for key performance indicators. Mysql5.6 improves the performance model. The new test points include:
Statements/stages-Which of my queries consume the most resources? Where did they spend their time?
Table/index I/O, table locks-Which applications use most of the load on tables or indexes?
Users/hosts/accounts-Which application's users, hosts, and accounts occupy the most resources?
Network I/O-What is the network load? How long has the session been idle? Mysql5.6 is now in my. the performance mode is enabled by default in the CNF file, and optimization and automatic correction settings are enabled by default, which reduces the overhead, therefore, using the performance mode production server to monitor the use cases of the most common applications is not a big problem. In addition, the new atomic level test can capture more fine-grained resource consumption, such as users, hosts, accounts, and applications, especially for payment and refund in cloud computing environments. Mysql5.6 has many unique patents in the performance mode, many of which have been published in the blog. You can refer to the technical and practical details here. Here you can read the Blog content mark Leith and Marc alff.
Other performance enhancements
New Automatic Configuration Optimization-Mysql5.6 changes the default configuration of the original server and supports better out-of-the-box performance in the current system architecture. When you change most of the general configurations, these new default configurations will reduce the time required for preparation. Currently, many configuration options can automatically set the size value based on the environment, and can also be set or changed manually when the server is started.
Improved Time/timestamp/datetime data types:
- Time/timestamp/datetime-currently supports precision at a subtle level.
- Timestamp/datetime-supported in 5.5, allowing developers to specify the current timestamp or automatically update the value or use both of them as the default values of Timestamp and datetime.
- Timestamp-the default value is null. When the value is no longer specified, the timestamp column does not automatically obtain the default now () or on update now () values as originally, but uses null instead.
Better condition Processing-
Get diagnostics
Mysql5.6 allows developers to check errors and code exceptions more easily by using the newly introduced MySQL diagnostic region and get diagnostics command interface. You can use multiple options in the diagnostic area to provide the following information:
- Statement-it provides the number of affected lines and the number of executed commands;
- Condition-return the error code and error message generated by the previous operation of all execution conditions.
The new get diagnostics command provides a standard interface for the diagnostic area. Through CLI or application code, you can easily retrieve and process the execution results of recent statements:
Mysql> drop table test. no_such_table;
Error 1051 (42s02): Unknown table 'test. no_such_table'
Mysql> Get diagnostics condition 1
-> @ P1 = returned_sqlstate, @ P2 = message_text;
Mysql> select @ P1, @ P2;
+ ------- + ------------------------------------ +
| @ P1 | @ P2 |
+ ------- + ------------------------------------ +
| 42s02 | unknown table 'test. no_such_table '|
+ ------- + ------------------------------------ +Options for leveraging the MySQL diagnostics area are detailed here. You can learn more about get
Diagnostics here.
Improved IPv6 support
- Mysql5.6 improved inet_aton () to convert and store string-based IPv6 addresses and store them as binary data, which has reached the minimum space consumption.
- Mysql5.6 changes the default value of the address binding option, from "0.0.0.0" to "0: 0", so that the MySQL Server accepts all IPv4 and IPv6 addresses. You can learn more here.
Improved Partition
- Increase the number of partitions to improve the table performance-mysql5.6 is now executed and expanded in a high-partition system, especially the insert operation, involving hundreds of partitions.
- Import/export a table from/to a partition table-mysql5.6 allows you to swap table partitions or subpartitions. The command is alter table... exchange partition. rows that already exist in the original partition or subpartition will be transferred to a non-partition table, any row that exists in a non-partitioned table can also be transferred to a partitioned table or a sub-partitioned table.
- Clear partition selection-mysql5.6 supports explicit selection of partitions and subpartitions. When a where condition is given, the above check will be performed on the row. Similar to automatic partitioning, the checked partitions can also be specified/controlled. They support queries and a large number of DML statements (select, delete, insert, replace, update, load data, load XML ).
Improved GIS: precise spatial operations-Mysql5.6 provides support for geometric operations through precise object shapes, and tests the relationship between two geometric values according to OpenGIS standards.SummaryMysql5.5 is already known as the best MySQL version in history. On this basis, MySQL improves performance, scalability, availability, and transaction throughput to meet the most demanding requirements of web, cloud, and embedded systems.
