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

This article will explain in detail the improvements in the mysql5.7 InnoDB storage engine, and the content of the article is of high quality, so the editor will share it with you for reference. I hope you will have a certain understanding of the relevant knowledge after reading this article.

Version: mysql5.7.16

One: security

Changes in the way ① initializes the database

Old version: mysql_install_db-- user=mysql-- basedir=/usr/local/mysql-- datadir=/opt/mysql

Mysql5.7: mysqld-- initialize--user=mysql-- basedir=/usr/local/mysql-- datadir=/opt/mysql (a temporary password will be generated. If you don't want a password, use the parameter-- initialize-insecure)

② can open ssl through mysql_ssl_ras_setup.

③ sql_mode changed from default null of mysql5.6 to default STRICT_TRANS_TABLES

Second: the improvement of InnoDB storage engine

1. Change the index name without locking the table, change it directly

Alter table xxx rename index ix_a to ix_b

2. Online DDL's improvement on the change of field property to varchar

1 > varchar (20)-> varchar (40) change the table directly without locking the table

2 > 40-> 20 copy method to lock table

3 > 256is a critical value, here refers to bytes, cannot span such as varchar (30)-> varchar (320), then ddl in the way of copy, lock the table

4 > it is still the old method for char and int, that is, the algorithm of copy performs ddl and locks the table.

In addition, 5.1,5.5 use the algorithm = inplace method to unlock the table for index additions and deletions, and 5.6 supports the use of inplace methods for field additions and deletions. Changes to the primary key are still implemented using the copy algorithm.

3. Full-text indexing support for Chinese

1 > mysql5.6 only has full-text index for innodb, but it does not support Chinese, Japanese, Korean and so on.

2 > select * from xxx where match (content) against ('+ horand-Happy Birthday'in boolean mode); means to find documents with 'horand' without' Happy Birthday'.

3 > the new parameter ngram_token_size is used to retrieve the number of Chinese words. Mutually exclusive to several parameters that set word length with 5.6

4. Improvement of BP preheating

1 > mysql5.6 is preheated by i_b_p_dump_at_shutdown=1 and i_b_p_load_at_startup=1 (mysql has to be turned off normally)

2 > mysql5.7 add the parameter i_b_p_dump_pct=xx to control the percentage of all thermal data in dump (from beginning to end). The default is 25, which can be between 1 and 100. Mysql5.6 is everything.

Properly lowering this value can reduce the time it takes to shut down and start mysql

5. Adjust BP online

1 > will block user requests, which is recommended during the business trough.

6. Reclaim undo log space

1 > starting with mysql5.6, you can specify the file directory (innodb_undo_direcrory), number (innodb_undo_tablespace) and size of undo log (number of innodb_undo_logs=128 rollback segments)

2 > mysql5.7 add two parameters to control the recovery of undo log, innodb_undo_log_truncate=1 (enable the recovery mechanism) and innodb_max_undo_log_size=1G (the recovery mechanism is triggered when the file size exceeds 1G)

7. Common tablespaces (turn on innodb_file_per_table=1)

1 > create tablespace ts_name

2 > create table xxx (id int) engine=innodb TABLESPACE ts_name

3 > multiple tables share one ibd file and ibdata1 in one directory

8. Independent tablespace specifies the storage path

1 > 5.6 and 5.7 create table T2 (id int) DATA DIRECTORY='/tmp' corresponding to the table data file idb generates a soft connection to the / tmp directory

9. Migrate an innodb table (enable innodb_file_per_table=1,mysql5.6 5.7)

1 > FLUSH TABLES xxx for EXPORT

2 > xxx.frm xxx.idb xxx.cfg (data dictionary information is stored and tested) copy three files to the new database

3 > change the owner of three files to mysql:mysql

4 > alter table xxx discart tablespace

5 > alter table xxx import tablespace

6 > check table xxx

10. Directly modify the size of redo log

Mysql5.5 (ib_logfile0 size supports up to 4G) and before

1 > set global innodb_fast_shutdown=0

2 > mysqladmin shutdown

3 > modify the size of innodb_log_file_size

4 > remove the old ib_logfile0 ib_logfile1

5 > mysqld_safe & start mysql

Mysql5.6 & & mysql5.7 (ib_logfile size up to dozens of GB)

1 > shutdown

2 > modify the configuration file innodb_log_file_size

3 > mysqld_safe & start

11. Deadlock prints to error log

1 > mysql5.5 and previous versions

Show engine innodb status\ G

2 > mysql5.6 & & mysql5.7

Set the parameter innodb_print_all_deadlocks=1 to print deadlock information to the err log

twelve。 Read-only transactions (5.6 5.7)

Start transaction read only

Third: SERVER layer promotion

1. Support for Json field types

two。 Support for virtual columns (functional indexes)

1 > create table xxx (id int, mod_id int GENERATED Alaways as ((id% 10)) virtual, key 'vir_col_inx' (mod_id)) engine=innodb

2 > select * from xxx where mod_id=3;, you can use the index vir_col_inx

3 > insert can only be done in default mode (insert into xxx values (13jindefault);)

3. Explain supports update/delete/insert

4. Log to Syslog

When server starts:-- syslog

When connecting to client:-- syslog

About the improvement of mysql5.7 InnoDB storage engine which is shared here, I hope the above content can be of some help to 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.