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

This article will share with you about the new features, deprecated features and obsolete features in MySQL 5.6. The editor thinks it is very practical, so share it with you as a reference and follow the editor to have a look.

Add

Security improvements.

Login authentication information can be encrypted in a .mylogin.cnf file using mysql_config_editor, which clients can read from to avoid using login information in clear text.

Stronger account password encryption is supported through the built-in sha256_password authentication plug-in.

Password_expired is added to the mysql.user table to identify whether the password has expired. Password expiration can be set through the alter user statement.

Through the validate_password plug-in to achieve password security check.

A warning is given to the account password mysql_upgrade that was encrypted using the previous version of MySQL4.1 's Hash method.

Mysql_install_db supports random-passwords on the Unix platform, which can generate a stronger password for the initial root user and set the password to expire and remove anonymous users.

The plaintext password content will no longer appear in all kinds of logs (slow query, binary log, general log), including mysql client command history.

Improved START SLVAE syntax allows you to specify parameters to connect to MASTER in this statement, avoiding saving information such as passwords in master.info files

Changes to server defaults.

Provide better performance and reduce manual changes to settings by administrators

InnoDB enhancements.

Full-text indexing is supported.

Online DDL (that is, some ALTER TABLE operations do not need to copy tables and do not block additions, deletions, deletions and modifications).

The CREATE TABLE statement supports DATA DIRECTORY='directory' to store .ibd files for each table under file-per-table in a directory other than the data directory, improving flexibility and performance, such as placing .ibd on a separate SSD disk.

Support for transportable tablespaces, allowing dynamic import and export of single tablespaces (.ibd files for each table). The FOR EXPORT clause of FLUSH TABLE brushes unsaved changes in buffer to .ibd files. You can then copy .ibd files and metadata files to other server instances (such as SSD, HDD). The DISCARD TABLESPACE and IMPORT TABLESPACE clauses of ALTER TABLE load data into different MySQL instances.

The InnoDB page size can be set through innodb_page_size (the default 16KB can be set to 8KB, 4KB) to adapt to specific load and hardware to reduce redundancy and improve capacity.

The Buffer Pool refresh algorithm has been improved to enhance performance and concurrency and can be adjusted by a series of parameters.

Integration with memcached allows you to use NoSQL-style API to access data to avoid the overhead of parsing and building query plans (suitable for simple queries, updates, etc.).

The statistics used for optimization are more accurate and can be maintained and controlled after the service restart.

Optimizing read-only transactions improves performance and concurrency for ad-hoc queries and report-generating applications.

InnoDB undo log can be moved from system tablespaces to one or more independent tablespaces (such as to an SSD disk).

A faster checksum algorithm can be specified through innodb_checksum_algorithm=crc32.

The total size of redo log files can reach 512G (previously 4G). Specify a single file size through innodb_log_file_size, number of files by innodb_log_files_in_group, and storage path by innodb_log_group_home_dir.

InnoDB tables in MySQL can be configured in read-only mode (through-- innodb-read-only), so that InnoDB tables can be accessed on read-only media such as DVD or CD, or data warehouses can be built for multi-instance shared data directories.

You can specify the compression level of the InnoDB compressed table through innodb_compression_level and whether to store the re-compressed pages caused by the update operation of compressed pages in buffer pool in redo log through innodb_log_compressed_pages.

You can dynamically adjust the proportion of padding blocks in the InnoDB compression table through innodb_compression_failure_threshold_pct and innodb_compression_pad_pct_max (to avoid recompression during DML, but too many will increase the probability of compression failure) to reduce the compression failure rate without rebuilding the table with new parameters or rebuilding the entire instance with different page sizes.

InnoDB-related system tables are added to INFORMATION_SCHEMA, which can query InnoDB buffer pool, table metadata, indexes, foreign keys, underlying performance-related data, and so on.

The new InnoDB can use the LRU algorithm to release the memory occupied by open tables to ease the memory load on servers with a large number of tables. (the amount of memory available to the open InnoDB table can be adjusted through table_definition_cache)

Internal performance optimizations, such as reducing contention by splitting kernel mutex, moving flush operations from main threads to separate threads, using multiple refresh threads, and fewer contention for buffer pool on large memory systems.

Faster deadlock detection algorithm, all InnoDB-related deadlock information will be written to the error log.

You can reload the page to InnoDB buffer pool immediately after the server is restarted to avoid a long "warm-up" time after restart, especially for instances with a large InnoDB buffer pool. You can dump the data in the buffer pool when the server is shut down and load it after the next restart, or you can import and export during the run.

Innochecksum can support files above 2G after 5.6.16

You can use innodb_status_output and innodb_status_output_locks to dynamically turn on and off InnoDB Monitor and InnoDB Lock Monitor, respectively (the relevant information about InnoDB will be printed in the error log. The latter parameter is enabled only if the previous parameter is enabled, and the result of the latter parameter is a subset of the previous result).

After 5.6.17, you can use online DDL (ALGORITHM=INPLACE) by operating OPTIMIZE TABLE, ALTER TABLE, etc. FORCE 、 ALTER TABLE... ENGINE=INNODB to rebuild the table to reduce rebuild time and allow concurrent DML.

Partitioning.

Partitions can be up to 8192 (including the amount of subpartitions)

You can use ALTER TABLE... EXCHANGE PARTITION swaps a partition or sub-partition of a partitioned table with a non-partitioned table (provided that the two tables have the same appearance structure of the partition, and the non-partitioned table is not a temporary table and has no foreign key reference, or the referenced non-partitioned table does not contain values other than the partition boundary to be swapped.)

Queries can be limited to one or more partitions or subpartitions. Supported statements include SELECT, DELETE, INSERT, REPLACE, UPDATE, LOAD DATA, LOAD XML.

Partition lock pruning greatly improves the performance of DML and DDL operations on multi-partitioned tables by eliminating locks on partitions.

Performance Schema.

Richer information. Table input and output, table event filtering, thread event filtering, table, index I / O and table lock summary information, statement and statement phase information, etc. The two related parameters are-- gtid-mode and-- enforce-gtid-consistency. If you use GTIDs to start a new slave or point to a new master, you don't have to specify the log file and the location in the file, which makes the process much easier. In addition, because GTID-based replication is entirely transaction-based, it is easy to check for master-slave consistency, as long as transactions committed on master are also committed on slave.

Replication and logging.

Transaction-based replication using GTIDs (global transaction identifiers) is initially supported, making it possible to identify and track every transaction committed on master and applied on slave.

The columns contained in row image in row-based replication can be controlled through binlog_row_image so that they can contain all columns or no BLOB and TEXT columns, or only individual columns necessary to uniquely identify and perform changes on each row, in order to save disk overhead, network overhead, and memory usage.

MySQL Server reads and writes binary logs to crash-safe because only complete transactions / events are recorded and read. By default, the event itself and the length of the event are recorded, and then this information is used to verify that the event is written correctly. You can also use the binlog_checksum variable to make server use the CRC32 algorithm to write checksums for events, and master_verify_checksum to make server read checksums from log files-- slave-sql-verify-checksum enables slave's SQL thread to read checksums from relay logs.

You can choose to save the connection information of master and the relay log information of slave in files or tables. The related parameters are-- master-info-repository and-- relay-log-info-repository. However, in order to ensure the replicated crash-safe, when you choose to save the connection information of master and the relay log information of slave in the table, the relevant table must be a transactional engine such as InnoDB (versions after 5.6.6 will automatically be created as InnoDB tables, but previous versions will create MyISAM tables, so be sure to change to InnoDB engine before replication starts)

The mysqlbinlog tool can back up binary log files in the original binary format from a specified host. The tool-related command line arguments are-- read-from-remote-server and-- raw

Deferred replication is supported, that is, deferring slave to master for a specific period of time. The modification feature can be used to test the impact of delays or to save DBA from accidentally making mistakes on master (specified by MASTER_DELAY in CHANGE MASTER TO).

For salve machines with multiple network interfaces, only one of them can be specified (specified by MASTER_BIND in the CHANGE MASTER TO statement)

The new log_bin_basename parameter is used to specify the complete path and file name of the binary log file.

Support multiple threads on the slave to execute transactions passed down on the master in parallel. Specifies the number of worker for concurrent transactions on the slave through slave_parallel_workers. This concurrency is based on the database, that is, there is a sequence of operations within the database, and the master and slave need to be performed in a consistent order, but this order is not needed between different databases, that is, an worker does not have to wait for other worker to update a library after updating a library.

Optimizer enhancements.

Optimized SELECT... FROM single_table... ORDER BY non_index_column [DESC] LIMIT [M,] N; query performance

The realization of Disk-Sweep Multi-Range Read. It effectively reduces random disk access caused by range scanning on the secondary index in the case of a large table and no cache. The method adopted is to scan first to find out the key associated with the relevant rows, then sort the key according to the primary key, and finally retrieve the data from the table according to the sorted key.

ICP (Index Comndition Pushdowndown) is implemented. Without ICP, the memory engine layer locates the rows in the table based on the index and returns them to the Server layer, and then filters the results here using the conditions in the WHERE clause. With ICP, if some of the columns in the WHERE condition can be evaluated by fields in the index, the server layer pushes that part of the WHERE condition down to the storage engine layer. Only the rows that meet the requirements are taken out after evaluation by the engine layer. This can effectively reduce the number of times the engine accesses the table and the number of times server accesses the engine.

EXPLAIN supports DELETE, INSERT, REPLACE and UPDATE. Previously, it only supported SELECT and can be exported in JSON format.

It is more efficient to process subqueries (derived tables) in FROM clauses. First of all, the materialization process of the subquery after FROM is not carried out until this part of the content is needed when the query is executed, and the query may be indexed on the derived table to speed up the query.

Optimize subqueries using semi-join and materialization strategies.

Use the BKA (Batched Key Access) join algorithm to improve join performance. Inner join, outer join, semi-join, nested outer joins and nested semi-joins are supported.

The new tracing function is mainly for developers. Provided through columns of optimizer_trace_xxx variables and INFORMATION_SCHEMA.OPTIMIZER_TRACE

Condition handling.

All kinds of diagnostic information can be obtained through GET DIAGNOSTICS statement

Data types.

Values of TIME, DATETIME, and TIMESTAMP types support decimal seconds and up to millisecond level (that is, 6-digit precision).

In previous versions, at most one TIMESTAMP column per table could be initialized to date-time of the day (DEFAULT CURRENT_TIMESTAMP) or updated to date-time of the day (ON UPDATE CURRENT_TIMESTAMP). This restriction has been removed in the new version. In addition, previously only TIMESTAMP columns could use DEFAULT CURRENT_TIMESTAMP and ON UPDATE CURRENT_TIMESTAMP, but now datetime columns can also be used.

In previous versions, if nothing was specified in the TIMESTAMP column definition, it automatically defaults to DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP. You can now turn off this behavior through the explicit_defaults_for_timestamp variable.

Host cache.

Provides more information about the cause of connection errors and improvements to host cache access, including client IP and hostname to avoid DNS queries. The specific implementation is as follows:

A series of Connection_errors_xxx state variables have been added to provide information about connection errors, which is a global aggregate of all types of error messages for all connections, not for a particular IP address.

The host_cache table is added to the performance_schema library and there are more count fields about error messages to track all kinds of errors (not for specific IP errors). In this way, you can query the table to know how many hosts are cached, what kind of errors occurred on which hosts, and how close the number of connection errors is close to the max_connect_errors limit.

Host cache can be resized through host_cache_size

OpenGIS.

A series of functions defined by the OPENGIS specification that measure the relationship between two geometric values are natively implemented.

Downgrade

ERROR_FOR_DIVISION_BY_ZERO, NO_ZERO_DATE and NO_ZERO_IN_DATE sql mode are not recommended, these values in MySQL5.7 will not have any effect, and the effect will be included in strict SQL mode ((STRICT_ALL_TABLES or STRICT_TRANS_TABLES). Pay attention to these issues when upgrading MySQL. Ensure that the SQL MODE in the new version does not affect the application.

Implicit sorting in GROUP BY is not recommended, which is an extension with MySQL and may be removed in future releases for performance reasons. So it's best to use the ORDER BY statement to explicitly specify the sort.

Password and mysql_old_password authentication plug-ins prior to version 4.1 are not recommended and are not secure. Secure_auth can be turned on to prevent the use of the old version of password hash. For DBA, it is recommended that accounts that use the mysql_old_password authentication plug-in be converted to the mysql_native_password authentication plug-in.

It is not recommended to use OLD_PASSWORD () and old_passwords=1 to generate passwords prior to version 4.1.

The-- skip-innodb or-- innodb=OFF,-- disable-innodb;date_format, datetime_format and time_format; have_profiling, profiling and profiling_history_size; innodb_use_sys_malloc and innodb_additional_mem_pool_size; timed_mutexes variables are not recommended. All of these may be removed in future releases.

Use-- lc-messages-dir and-- lc-messages instead of-- language (used to indicate the language in which the error message is used).

ALTER IGNORE TABLE is not recommended.

Msql2mysql, mysql_convert_table_format, mysql_find_rows, mysql_fix_extensions, mysql_setpermission, mysql_waitpid, mysql_zap, mysqlaccess and mysqlbug are not recommended.

The mysqlhotcopy tool is not recommended.

Remove

-- log option, using-- general_log and-- general_log_file=file_name instead.

-- log-slow-queries option. Use-- slow_query_log and-- slow_query_log_file=file_name instead.

-- one-thread option. Use-- thread_handling=no-threads instead.

-- safe-mode.

-- skip-thread-priority.

-- table-cache. Use the table_oopen_cache variable instead.

Remove the-- init-rpl-role,-- rpl-recovery-rank options options, and the rpl_recovery_rank, Rpl_status state variables.

Cancel the engine_condition_pushdown variable and change it to a flag engine_condition_pushdown in the optimizer_switch variable.

Cancel the have_csv, have_innodb, have_ndbcluster and have_partitioning variables. Use SHOW PLUGIN or query the PLUGINS table in INFORMATION--SCHEMA.

Replace the sql_big_tables variable with big_tables.

Replace the sql_low_priority_updates variable with low_priority_updates.

Replace the sql_max_join_size variable with max_join_size.

Replace the max_log_data_size variable with max_allowed_packet.

FLUSH MASTER and FLUSH SLAVE statements are replaced with RESET MASTER and RESET SLAVE.

SLAVE START and SLAVE STOP are replaced by START SLAVE and STOP SLAVE.

Cancel SHOW AUTHORS and SHOW CONTRIBUTORS.

Cancel the OPTION and ONE_SHOT modifiers in the SET command.

It is not allowed to add parameters or local variables to DEFAULT (SET var_name = DEFAULT) in stored procedures or stored functions. However, you can set the system variable to DEFAULT.

Remove SHOW ENGINE INNODB MUTEX.

Thank you for reading! This is the end of this article on "what are the new features, deprecated functions and obsolete functions in MySQL 5.6.I hope the above content can be helpful to you, so that you can learn more knowledge. if you think the article is good, you can 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.