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

Editor to share with you what are the new features of MySQL5.7, I believe most people do not know much about it, so share this article for your reference, I hope you can learn a lot after reading this article, let's learn about it!

6.1 Optimization (tools) enhancements

In version 5. 7, if a session is executing sql and the sql supports explain, we can view the execution plan of the sql by specifying the session id.

EXPLAIN [options] FOR CONNECTION connection_id

This feature can view long queries that are being executed in one session in another session.

Mysql > show processlist

+-+-- + -+

| | Id | User | Host | db | Command | Time | State | Info |

+-+-- + -+

| | 1 | system user | | NULL | Connect | 78 | Connecting to master | NULL |

| | 2 | system user | | NULL | Connect | 78 | Slave has read all relay log; waiting for more updates | NULL |

| | 3 | system user | | NULL | Connect | 78 | Waiting for an event from Coordinator | NULL |

| | 4 | system user | | NULL | Connect | 78 | Waiting for an event from Coordinator | NULL |

| | 5 | system user | | NULL | Connect | 78 | Waiting for an event from Coordinator | NULL |

| | 6 | system user | | NULL | Connect | 78 | Waiting for an event from Coordinator | NULL |

| | 8 | root | localhost:47896 | NULL | Query | 0 | starting | show processlist | |

| | 9 | root | localhost:47897 | NULL | Query | 3 | User sleep | select sleep (10) |

+-+-- + -+

8 rows in set (0.00 sec)

Mysql > explain FOR CONNECTION 9

+-- +

| | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |

+-- +

| | 1 | SIMPLE | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | No tables used |

+-- +

1 row in set (0.00 sec)

6.2 hint feature Enhancement

Compared with MySQL5.6 version of hint, which is mainly index-level hint and control table join order hint,5.7.7, MySQL adds optimizer hint to control the execution mode of sql. Because MySQL currently supports nest loop join, there is no hint to modify the join mode of sql. Friends who are familiar with Oracle will find that MySQL and Oracle are getting closer and closer in function. Then again, the use of hint (don't compare with index hint) is similar to that of oracle:

SELECT / * + NO_RANGE_OPTIMIZATION (T3 PRIMARY, f2_idx) * / F1 FROM T3 WHERE F1 > 30 AND F1

< 33; SELECT /*+ BKA(t1) NO_BKA(t2) */ * FROM t1 INNER JOIN t2 WHERE ...; SELECT /*+ NO_ICP(t1, t2) */ * FROM t1 INNER JOIN t2 WHERE ...; SELECT /*+ SEMIJOIN(FIRSTMATCH, LOOSESCAN) */ * FROM t1 ...; EXPLAIN SELECT /*+ NO_ICP(t1) */ * FROM t1 WHERE ... 优化器级别的hint分四种类型 Global: The hint affects the entire statement Query block: The hint affects a particular query block within a statement ，什么是query block (SELECT ... ) UNION (SELECT /*+ ... */ ... ) --后面的括号里面的称为 query block 。 Table-level: The hint affects a particular table within a query block Index-level: The hint affects a particular index within a table 6.3 触发器功能增强 5.7版本之前一个表 对于每种action(INSERT,UPDATE, DELETE)和时机(BEFORE or AFTER) 只能支持一种类型的触发器。新版本可以针对同一个action支持多个触发器。 6.4 syslog 功能 之前的版本，*nix系统上的MySQL支持将错误日志发送到syslog是通过mysqld_safe捕获错误输出然后传递到syslog来实现的。新的版本原生支持将错误日志输出到syslog,且适用于windows系统，只需要通过简单的参数(log_syslog等)配置即可。参考 官方文档 MySQL支持-syslog选项，可将在交互式模式下执行过的命令输出到syslog中（*nix系统下一般是.mysql_history）。对于匹配"ignore"过滤规则（可通过 -histignore选项或者 MYSQL_HISTIGNORE环境变量进行设置）的语句不会被记入。关于mysql客户端的日志使用参见：官方文档 6.5 虚拟列 在MySQL 5.7中，支持两种Generated Column， 1 Virtual Generated Column :只将Generated Column保存在数据字典中表的元数据，每次读取该列时进行计算，并不会将这一列数据持久化到磁盘上； 注意:MySQL 5.7.8 以前 虚拟列字段不支持创建索引。5.7.8之后Innodb支持在虚拟列创建辅助索引。 2 Stored Generated Column : 将Column持久化到存储，会占用一定的存储空间。与Virtual Column相比并没有明显的优势，因此，MySQL 5.7中，不指定Generated Column的类型，默认是Virtual Column。 创建虚拟列语法： col_name data_type [GENERATED ALWAYS] AS (expression) [VIRTUAL | STORED] [UNIQUE [KEY]] [COMMENT comment] [[NOT] NULL] [[PRIMARY] KEY] 具体的例子 CREATE TABLE triangle ( id int(10) not null primary key auto_increment, sidea DOUBLE, sideb DOUBLE, sidec DOUBLE AS (SQRT(sidea * sidea + sideb * sideb)) ); INSERT INTO triangle (sidea, sideb) VALUES(1,1),(3,4),(6,8),(12,16); mysql>

Select * from triangle

+-- +

| | id | sidea | sideb | sidec | |

+-- +

| | 1 | 1 | 1 | 1.4142135623730951 | |

| | 2 | 3 | 4 | 5 |

| | 3 | 6 | 8 | 10 | |

| | 4 | 12 | 16 | 20 |

+-- +

4 rows in set (0.00 sec)

Mysql > explain select * from triangle where sidec > 10

+-- +

| | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |

+-- +

| | 1 | SIMPLE | triangle | NULL | ALL | NULL | NULL | NULL | NULL | 4 | 33.33 | Using where |

+-- +

1 row in set, 1 warning (0.00 sec)

Mysql > alter table triangle add key idx_sidec (sidec)

Query OK, 0 rows affected (0.03 sec)

Records: 0 Duplicates: 0 Warnings: 0

Mysql > explain select * from triangle where sidec > 10

+-- +

| | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |

+-- +

| | 1 | SIMPLE | triangle | NULL | range | idx_sidec | idx_sidec | 9 | NULL | 1 | 100.00 | Using where |

+-- +

1 row in set, 1 warning (0.00 sec)

Seeing this example, friends who are familiar with oracle may compare it with functional indexes, which are similar. Use virtual columns to achieve functional indexing or solve business design defects, but individuals do not recommend using similar features, because virtual columns will also bring potential risks and complexity to later operations and maintenance to a certain extent. The examples on the network basically use virtual columns to solve business logic problems, which runs counter to the original intention that the database only stores data. Consider the basic logic of the MVC framework. Business logic should be placed in C layer or V layer, and M layer can only store data.

These are all the contents of the article "what are the new features of MySQL5.7". Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to follow the industry information channel!

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.