【MySQL】5.7新特性之六

写在前面
  本系列文章基于 5.7.12 版本讲述MySQL的新特性。从安装,文件结构,SQL ,优化 ,运维层面 复制,GITD等几个方面展开介绍 5.7 的新特性和功能。同时也建议大家跟踪官方blog和官方文档,以尽快知悉其新的变化。
6.1  优化(工具方面)增强
  5.7 版本中如果一个会话正在执行sql,且该sql 是支持explain的,那么我们可以通过指定会话id,查看该sql的执行计划。

  1. EXPLAIN [options] FOR CONNECTION connection_id
该功能可以在一个会话里面查看另外一个会话中正在执行的长查询。
  1. mysql> show processlist;
  2. +----+-------------+-----------------+------+---------+------+--------------------------------------------------------+------------------+
  3. | Id | User        | Host            | db   | Command | Time | State                                                  | Info             |
  4. +----+-------------+-----------------+------+---------+------+--------------------------------------------------------+------------------+
  5. | 1  | system user |                 | NULL | Connect | 78   | Connecting to master                                   | NULL             |
  6. | 2  | system user |                 | NULL | Connect | 78   | Slave has read all relay log; waiting for more updates | NULL             |
  7. | 3  | system user |                 | NULL | Connect | 78   | Waiting for an event from Coordinator                  | NULL             |
  8. | 4  | system user |                 | NULL | Connect | 78   | Waiting for an event from Coordinator                  | NULL             |
  9. | 5  | system user |                 | NULL | Connect | 78   | Waiting for an event from Coordinator                  | NULL             |
  10. | 6  | system user |                 | NULL | Connect | 78   | Waiting for an event from Coordinator                  | NULL             |
  11. | 8  | root        | localhost:47896 | NULL | Query   | 0    | starting                                               | show processlist |
  12. | 9  | root        | localhost:47897 | NULL | Query   | 3    | User sleep                                             | select sleep(10) |
  13. +----+-------------+-----------------+------+---------+------+--------------------------------------------------------+------------------+
  14. 8 rows in set (0.00 sec)
  15. mysql> explain FOR CONNECTION 9;
  16. +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
  17. | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
  18. +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
  19. | 1 | SIMPLE | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | No tables used |
  20. +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
  21. 1 row in set (0.00 sec)
可以参考官方文档 和 work log 

6.2 hint 功能增强
 相比于MySQL5.6版本的hint 主要是index 级别的hint和控制表join 顺序的hint,5.7.7之后,MySQL增加了优化器hint,来控制sql执行的方式,因为目前MySQL支持nest loop join,故暂时无hint来修改sql 的join方式。熟悉Oracle 的朋友是否会发现MySQL 和Oracle 在功能上越来越近了。话说回来5.7的hint (先别和 index hint 比较)的用法 ,和oracle 的类似: 
  1. SELECT /*+ NO_RANGE_OPTIMIZATION(t3 PRIMARY, f2_idx) */ f1 FROM t3 WHERE f1 > 30 AND f1 < 33;
  2. SELECT /*+ BKA(t1) NO_BKA(t2) */ * FROM t1 INNER JOIN t2 WHERE ...;
  3. SELECT /*+ NO_ICP(t1, t2) */ * FROM t1 INNER JOIN t2 WHERE ...;
  4. SELECT /*+ SEMIJOIN(FIRSTMATCH, LOOSESCAN) */ * FROM t1 ...;
  5. EXPLAIN SELECT /*+ NO_ICP(t1) */ * FROM t1 WHERE ...
优化器级别的hint分四种类型 
  1. Global: The hint affects the entire statement
  2. Query block: The hint affects a particular query block within a statement ,什么是query block
  3. (SELECT ... ) UNION (SELECT /*+ ... */ ... ) --后面的括号里面的称为 query block 。
  4. Table-level: The hint affects a particular table within a query block
  5. 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。
创建虚拟列语法:
  1. col_name data_type [GENERATED ALWAYS] AS (expression)
  2.   [VIRTUAL | STORED] [UNIQUE [KEY]] [COMMENT comment]
  3.   [[NOT] NULL] [[PRIMARY] KEY]
具体的例子
  1. CREATE TABLE triangle (
  2.   id int(10) not null primary key auto_increment,
  3.   sidea DOUBLE,
  4.   sideb DOUBLE,
  5.   sidec DOUBLE AS (SQRT(sidea * sidea + sideb * sideb))
  6. );
  7. INSERT INTO triangle (sidea, sideb) VALUES(1,1),(3,4),(6,8),(12,16);
  8. mysql> select * from triangle;
  9. +----+-------+-------+--------------------+
  10. | id | sidea | sideb | sidec              |
  11. +----+-------+-------+--------------------+
  12. | 1  | 1     | 1     | 1.4142135623730951 |
  13. | 2  | 3     | 4     | 5                  |
  14. | 3  | 6     | 8     | 10                 |
  15. | 4  | 12    | 16    | 20                 |
  16. +----+-------+-------+--------------------+
  17. 4 rows in set (0.00 sec)
  18. mysql> explain select * from triangle where sidec > 10;
  19. +----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+
  20. | id | select_type | table    | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
  21. +----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+
  22. | 1  | SIMPLE      | triangle | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 4    | 33.33    | Using where |
  23. +----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+
  24. 1 row in set, 1 warning (0.00 sec)
  25. mysql> alter table triangle add key idx_sidec(sidec);
  26. Query OK, 0 rows affected (0.03 sec)
  27. Records: 0 Duplicates: 0 Warnings: 0
  28. mysql> explain select * from triangle where sidec > 10;
  29. +----+-------------+----------+------------+-------+---------------+-----------+---------+------+------+----------+-------------+
  30. | id | select_type | table    | partitions | type  | possible_keys | key       | key_len | ref  | rows | filtered | Extra       |
  31. +----+-------------+----------+------------+-------+---------------+-----------+---------+------+------+----------+-------------+
  32. | 1  | SIMPLE      | triangle | NULL       | range | idx_sidec     | idx_sidec | 9       | NULL | 1    | 100.00   | Using where |
  33. +----+-------------+----------+------------+-------+---------------+-----------+---------+------+------+----------+-------------+
  34. 1 row in set, 1 warning (0.00 sec)
看到这个例子,熟悉oracle的朋友可能会和函数索引作比较,两者比较类似.使用虚拟列达到函数索引或者解决业务上的设计缺陷,但是个人不建议使用类似的功能,因为虚拟列在一定程度上也会给后期运维带来潜在的风险和复杂度。网络上的例子基本都是使用虚拟列解决业务逻辑上的问题,违背了数据库只存储数据的初衷,思考一下MVC 框架的基本逻辑,业务逻辑要放到C 层或者V层,M层只存放数据即可。
各位看官自己要了解更详细的虚拟列资料请移步官方文档 
小结
  本文算是对5.7版本功能类新特性做收尾,后面会介绍主从复制,GTID,InnoDB 性能优化方面以及数据库升级相关的知识,这些特性更具有操作性,相对前面几篇文章会更难写。另外学习5.7的过程中,我查看了很多MySQL的worklog,发现这里简直是一个MySQL资料宝库,很多功能的需求和原理讨论都可以在这里找到。想深入学习MySQL的朋友可以多钻研 http://dev.mysql.com/worklog/ 

相关文章
[1] MySQL 5.7 官方文档
[2] 《MySQL 5.7 初探
[3] 《MySQL 5.7新特性之一
[4] 《MySQL 5.7新特性之二
[5] 《MySQL 5.7新特性之三
[6] 《MySQL 5.7新特性之四
[7] 《MySQL 5.7新特性之

本页内容版权归属为原作者,如有侵犯您的权益,请通知我们删除。
工具用于查看指定时间内show global status指标的变化,能够帮助运维人员了解系统负载的走势 本工具在5.5 5.6 5.7中测试通过 源码我放在百度云盘了 http://pan.baidu.com/s/1mhIeKp6 源码一共5个文件 conmysql.c main.c findv.c other.c type.h 如果源码编译使用如下方法 1、建立一个MYSQL用户用于监控,不需要什么权限只要能够show global status 即可 mysql create user mmon@'l
本文以双节点RAC为例,揭示了在执行select及DML操作过程中,Cache Fusion在幕后是如何对block进行锁管理的。 ###实例1上查询scott.t0820_1表 select * from scott.t0820_1;         ID ----------          2 select dbms_rowid.rowid_relative_fno(rowid) rfno,dbms_rowid.rowid_block_number(rowid) blkno from scott.
【故障处理】队列等待之 enq: US - contention 案例 1    BLOG 文档结构图       2    前言部分 2.1    导读和注意事项 各位技术爱好者,看完本文后,你可以掌握如下的技能,也可以学到一些其它你所不知道的知识, ~O(∩_∩)O~ : ① enq: US - contention 等待事件的解决 ② 一般等待事件的解决办法 ③ 队列等待的基本知识   Tips: ① 本文在 ITpub ( http://blog.itpub.net/26736162 )、博客园
    在Oracle 10g的中搭建Data Guard环境真是一个纠结,目前大体都是采用两种方式,一种是rman备份,一种是duplicate的方式,但是这两个地方不够让我满意,一来是rman备份数据量不小,需要先在本地生成备份,然后拷贝到备库去,这个搭建周期略长,另外一个就是推荐的方式duplicate,在10g中有些鸡肋的味道,本地备份,然后拷贝到备库,然后动用duplicate的方式,这样的方式还不如手工rman的方式同步来得顺心顺意,所以在10g中我是不怎么喜欢duplicate方式。当然11g
【故障处理】队列等待之 TX - allocate ITL entry 引起的死锁处理 1    BLOG 文档结构图       2    前言部分 2.1    导读和注意事项 各位技术爱好者,看完本文后,你可以掌握如下的技能,也可以学到一些其它你所不知道的知识, ~O(∩_∩)O~ : ① enq: TX - allocate ITL entry 等待事件的解决 ② 一般等待事件的解决办法 ③ 队列等待的基本知识 ④  ITL 死锁解决 ⑤  ITL 死锁模拟 ⑥  Merge 语句的非关联形式的查
【技巧】如何使用客户端发布 BLOG+ 如何快速发布微信公众号文章   1    BLOG 文档结构图     2    前言部分   2.1    导读和注意事项 各位技术爱好者,看完本文后,你可以掌握如下的技能,也可以学到一些其它你所不知道的知识, ~O(∩_∩)O~ : ①  如何使用客户端发布 BLOG (重点) ②  如何快速发布微信公众号文章 ③  如何批量上传图片到blog ④  如何批量上传图片到微信公众号上     Tips: ① 本文在 itpub ( http://blog.itpu

降低高水位线的方法 - 2016-09-13 14:09:09

准备工作,创建一张表: CREATE TABLE TEST2 (     ID    NUMBER(10),     NAME  VARCHAR2(32) ); 1、移动表: SQL begin   2  for i in 1..10000 loop   3  insert into test2 values(i,'bbb');   4  end loop;   5  commit;   6  end;   7  / PL/SQL procedure successfully completed. SQL
预备知识: 1)row cache lock事件   --内存share pool分为library cache,dictionary cache;而row cache lock对象分布在dictionary cache中,是由于对于字典缓冲的访问造成的;   --这类属于latch类的资源竞争,相当耗CPU,如果并发量大的话,容易down机 如果发现这个等待十分高,一般来说可能由于2种原因,一是共享池太小了,需要增加共享池,另外一种情况是SQL分析过于频繁,对于共享池的并发访问量过大。对于任何一种情况,绝
【故障处理】队列等待之 TX - allocate ITL entry 案例 1    BLOG 文档结构图       2    前言部分 2.1    导读和注意事项 各位技术爱好者,看完本文后,你可以掌握如下的技能,也可以学到一些其它你所不知道的知识, ~O(∩_∩)O~ : ① enq: TX - allocate ITL entry 等待事件的解决 ② 一般等待事件的解决办法 ③ 队列等待的基本知识 Tips: ① 本文在 ITpub ( http://blog.itpub.net/267361
Oracle 一次缩小表空间的处理过程 1    BLOG 文档结构图       2    前言部分 2.1    导读和注意事项 各位技术爱好者,看完本文后,你可以掌握如下的技能,也可以学到一些其它你所不知道的知识, ~O(∩_∩)O~ : ①  收缩表空间的几种办法 ② 表空间大小查询 ③  AIX 下查询磁盘空间大小的 shell 脚本 ④ 删除数据文件的正确方法 ⑤  ORA-03262 处理 ⑥ 缩小数据文件 ⑦  su - grid asmcmd lsdg 的使用 ⑧ 其他常用命令   Ti