New Features of mysql5.6

My colleagues summarized the new features of mysql5.6 and the corresponding official instruction documents, which are very detailed. I have prepared four major innodb, replication, optimization, and nosql support documents, the improvements have been made in these four aspects, which are detailed in the relevant official documents. 1. Changes in the default server parameter settings: dev. mysql. comdocrefman5.6en

My colleagues summarized the new features of mysql 5.6 and the corresponding official instruction documents. They have prepared four major innodb, replication, optimization, and nosql support documents, the improvements have been made in these four aspects, which are detailed in the relevant official documents. 1. Changes in the default settings of server parameters: http://dev.mysql.com/doc/refman/5.6/en/

My colleagues summarized the new features of mysql 5.6 and their corresponding official documents, which are very detailed and have been prepared.

The four major innodb, replication, optimization, and nosql support modules have been greatly improved in these four aspects, which are detailed in the relevant official documents.

1. Changes in the default settings of server parameters:
Http://dev.mysql.com/doc/refman/5.6/en/server-default-changes.html

Ii. innodb enhancements
1. fulltext indexes are supported.
2. online DDL is supported.
3. The. ibd file of the independent tablespace can specify the directory when creating the table:

4. Import and export tablespaces: The copy file method is much faster than mysqldump.

5. added the innodb_page_size parameter to set the page size.
6. Enhanced buffer pool flush algorithm:
Http://dev.mysql.com/doc/refman/5.6/en/innodb-performance.html#innodb-lru-background-flushing
7. Support Using nosql APIs to access innodb tables (Integrating memcached ):

8. Continuous optimizer statistics: restart is not lost.
Http://dev.mysql.com/doc/refman/5.6/en/innodb-performance.html#innodb-persistent-stats
9. Supports read-only transactions:
Http://dev.mysql.com/doc/refman/5.6/en/innodb-performance.html#innodb-performance-ro-txn
10. the undo log can independently output the system tablespace:
Http://dev.mysql.com/doc/refman/5.6/en/innodb-performance.html#innodb-undo-tablespace
11. The maximum redo log count is 512 GB.
12. innodb supports read_only media, such as DVDs. The parameter -- innodb-read-only
13. INFORMATION_SCHEMA added several related dictionary tables.
14. innodb internal performance enhancement: including separating flushing operations from the main thread to reduce core mutex locks. Multiple cleanup threads can be set to reduce resource contention for large memory systems.
15. Improved Deadlock Detection Algorithm.
16. buffer pool push: http://dev.mysql.com/doc/refman/5.6/en/innodb-performance.html#innodb-preload-buffer-pool
Iii. Enhanced partitioning
1. the maximum number of partitions is increased by 8192, including partitions and subpartitions.
2. Supports PARTITION (or sub-PARTITION) EXCHANGE with non-PARTITION tables: alter table... EXCHANGE PARTITION
3. query supports explicit PARTITION statements, such as SELECT * FROM t PARTITION (p0, p1) WHERE c <5
Http://dev.mysql.com/doc/refman/5.6/en/partitioning-selection.html
4. Simplified partition lock enhanced performance: http://dev.mysql.com/doc/refman/5.6/en/partitioning-limitations-locking.html

Iv. replication and log Enhancement
1. Added GTID replication:
2. Added binlog_row_image. Parameter. You can only modify the fields used in the row format (rather than record all columns as before), saving space and other resources.
3.master.info and relay-log.info support storage in tables.
4. The mysqlbinlog command supports binlog backup (the backup is still binary binlog instead of text)
5. Support for delayed replication: add the MASTER_DELAY option to change master.
6. You can specify one slave with multiple network interfaces: Add MASTER_BIND to change master.
7. Added log_bin_basename. , Relay_log_basename System variable.
8. schema-level multi-threaded replication.
9. binlog supports crash-safe.

5. optimizer enhancements
1. limit optimization:
2. MRR optimization (Multi-Range Read )
3. Index Condition Pushdown ):
4. The explain statements support insert, delete, update, and replace statements, and support json format.
5. added the connection algorithm BKA (Batched Key Access ):
6. subquery optimization: including materialized and semi-join optimization.
7. developer-oriented optimizer tracing feature.

6. Data Types
1. The time, datetime, and timestamp types can be subtle (six digits after the decimal point). Table slowlog will not be truncated.
2. The datetime type supports DEFAULT CURRENT_TIMESTAMP and on update CURRENT_TIMESTAMP.
3. added the explicit_defaults_for_timestamp system variable to specify the default value of timestamp.
4. year (2) is limited and will be converted to year (4 ).

VII. Host cache Enhancement
1. Added Connection_errors_xxx. Series status parameters.
2. added the host_cache_size system variable.

8. Deletion features
1. -- remove log and log, and use -- general_log And -- general_log_file = file_name Replace.
2. log_slow_queries is removed, and only slow_query_log is used.
3. -- remove the one-thread system parameter and use -- thread_handling = no-threads. Replace.
4. -- remove the safe-mode system parameter.
5. -- remove the skip-thread-priority system parameter.
6. -- remove table-cache with table_open_cache Replace.
7. -- init-rpl-role, -- rpl-recovery-rank, rpl_recovery_rank, and Rpl_status are removed.
8. The engine_condition_pushdown system variable is removed. engine_condition_pushdown indicates that the system variable is integrated into optimizer_switch. Variable.
9. Remove have_csv, have_innodb, have_ndbcluster, and have_partitioning with show engines.
10. Remove SQL _big_tables with big_tables .
11. SQL _low_priority_updates is low_priority_updates .
12. SQL _max_join_size is max_join_size .
13. max_long_data_size is max_allowed_packet .
14. The flush master and flush slave statements are removed and replaced by the reset master and reset slave statements.
15. Remove the slave start and slave stop statements and replace them with start slave and stop slave.
16. Remove the show authors and show contributors statements.
17. Remove OPTION and nd ONE_SHOT from the set statement.