MySQL8.0のヒント句が便利になっていたので基本動作確認。
バッチ処理前にSETコマンドで動的に設定していたセッション変数を実行するクエリーにヒントとして追加する事が出来るようです。

WL#681: Hint to temporarily set session variable for current statement
https://dev.mysql.com/worklog/task/?id=681

Syntax of the SET_VAR hint is:


 /*+ SET_VAR( = ) */

大量のデータをOrder by, Group by等でソート処理していて、Sort_merge_passesが多発している場合にソートバッファーの不足をヒント句で回避してディスクI/Oの発生を抑える事が出来る。
大量データのソートが多いバッチ処理などに組み込んでおくと、処理が早く終わらせる事が出来る。
(例)


mysql> SELECT /*+ SET_VAR(sort_buffer_size = 4M) */ name FROM city ORDER BY name;


実際にデータを準備してsort_buffer_sizeヒント句を検証した結果
Duration: 12.71 sec → 6.45 sec
sort_merge_passes: 216 → 0


mysql> select count(*) from ( select v.name, count(*) from t_group g join tbnode n on g.groupid = n.groupid join t_virual v on n.nodeid = v.nodeid group by v.name with rollup) t;
+----------+
| count(*) |
+----------+
|     1001 |
+----------+
1 row in set (12.71 sec)

mysql> select * from sys.statement_analysis limit 1\G
*************************** 1. row ***************************
            query: SELECT COUNT ( * ) FROM ( SELE ... v` . `name` WITH ROLLUP ) `t` 
               db: PERF
        full_scan: *
       exec_count: 1
        err_count: 0
       warn_count: 0
    total_latency: 12.71 s
      max_latency: 12.71 s
      avg_latency: 12.71 s
     lock_latency: 1.47 ms
        rows_sent: 1
    rows_sent_avg: 1
    rows_examined: 3102001
rows_examined_avg: 3102001
    rows_affected: 0
rows_affected_avg: 0
       tmp_tables: 2
  tmp_disk_tables: 0
      rows_sorted: 1000000
sort_merge_passes: 216
           digest: 1e941ce49e8f810963a8468995dc2eaf
       first_seen: 2017-09-28 20:29:46.768069
        last_seen: 2017-09-28 20:29:46.768069
1 row in set (0.01 sec)

mysql> 


mysql> CALL sys.ps_truncate_all_tables(FALSE);
+---------------------+
| summary             |
+---------------------+
| Truncated 49 tables |
+---------------------+
1 row in set (0.17 sec)

Query OK, 0 rows affected (0.18 sec)

mysql> select  /*+ SET_VAR(sort_buffer_size = 16M) */ count(*) from (select v.name, count(*) from t_group g join tbnode n on g.groupid = n.groupid join t_virual v on n.nodeid = v.nodeid  group by v.name with rollup) t;
+----------+
| count(*) |
+----------+
|     1001 |
+----------+
1 row in set (6.45 sec)

mysql> select * from sys.statement_analysis limit 1\G
*************************** 1. row ***************************
            query: SELECT /*+ SET_VAR ( `sort_buf ... v` . `name` WITH ROLLUP ) `t` 
               db: PERF
        full_scan: *
       exec_count: 1
        err_count: 0
       warn_count: 0
    total_latency: 9.94 s
      max_latency: 9.94 s
      avg_latency: 9.94 s
     lock_latency: 406.00 us
        rows_sent: 1
    rows_sent_avg: 1
    rows_examined: 3102001
rows_examined_avg: 3102001
    rows_affected: 0
rows_affected_avg: 0
       tmp_tables: 2
  tmp_disk_tables: 0
      rows_sorted: 1000000
sort_merge_passes: 0
           digest: bdf5d04f4574930fef467d6c359c49bf
       first_seen: 2017-09-28 20:37:42.923895
        last_seen: 2017-09-28 20:37:42.923895
1 row in set (0.01 sec)

使いどころが難しいが、以下のようにauto_increment_increment等の値もヒント句で変更出来るようです。


mysql> CREATE TABLE `T_SET_VAR` (
    ->   `id` int(10) NOT NULL AUTO_INCREMENT,
    ->   `memo` varchar(100) DEFAULT NULL,
    ->   PRIMARY KEY (`id`)
    -> ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4;
Query OK, 0 rows affected (0.18 sec)

mysql> insert into T_SET_VAR(memo) values('auto_increment_increment test');
Query OK, 1 row affected (0.08 sec)

mysql> insert into T_SET_VAR(memo) values('auto_increment_increment test2');
Query OK, 1 row affected (0.03 sec)

mysql> select * from T_SET_VAR;                                                                                                  
+----+--------------------------------+
| id | memo                           |
+----+--------------------------------+
|  1 | auto_increment_increment test  |
|  2 | auto_increment_increment test2 |
+----+--------------------------------+
2 rows in set (0.00 sec)

mysql> select * from T_SET_VAR;                                             
+----+--------------------------------+
| id | memo                           |
+----+--------------------------------+
|  1 | auto_increment_increment test  |
|  2 | auto_increment_increment test2 |
+----+--------------------------------+
2 rows in set (0.00 sec)

mysql> insert /*+ SET_VAR(auto_increment_increment = 10) */ into T_SET_VAR(memo) values('auto_increment_increment test3?');
Query OK, 1 row affected (0.03 sec)

mysql> select * from T_SET_VAR;                                                                                            
+----+---------------------------------+
| id | memo                            |
+----+---------------------------------+
|  1 | auto_increment_increment test   |
|  2 | auto_increment_increment test2  |
| 11 | auto_increment_increment test3? |
+----+---------------------------------+
3 rows in set (0.00 sec)

その他、Worklogによると以下のように、色々なセッション変数をSETコマンドを利用しないでヒント句で指定出来るようです。

List of settable variables:
———————————————————————————-
auto_increment_increment
auto_increment_offset
big_tables
bulk_insert_buffer_size
default_tmp_storage_engine
div_precision_increment
end_markers_in_json
eq_range_index_dive_limit
foreign_key_checks
group_concat_max_len
insert_id
internal_tmp_mem_storage_engine
join_buffer_size
lock_wait_timeout
max_error_count
max_execution_time
max_heap_table_size
max_join_size
max_length_for_sort_data
max_points_in_geometry
max_seeks_for_key
max_sort_length
optimizer_prune_level
optimizer_search_depth variables
optimizer_switch
range_alloc_block_size
range_optimizer_max_mem_size
read_buffer_size
read_rnd_buffer_size
sort_buffer_size
sql_auto_is_null
sql_big_selects
sql_buffer_result
sql_mode
sql_safe_updates
sql_select_limit
timestamp
tmp_table_size
updatable_views_with_limit
unique_checks
windowing_use_high_precision
———————————————————————————-

参照: The MySQL 8.0.3 Release Candidate is available


MySQL8.0 RCのDockerイメージがリリースされていたので、今後の検証やデモ用に設定しました。
手軽に検証出来るので、軽く検証するにはお勧めです。

Docker Image: https://github.com/mysql/mysql-docker


[root@DockerHost oracle]# docker pull mysql/mysql-server:8.0
8.0: Pulling from mysql/mysql-server
323fb8f65502: Pull complete 
b2a15600aac3: Pull complete 
a1116f4203e9: Pull complete 
8be6f234356c: Pull complete 
a09590e34bdc: Pull complete 
554cdb588e9e: Pull complete 
851fce189663: Pull complete 
ca60670c6cb3: Pull complete 
98a8195f4fc5: Pull complete 
ec8c0ade6c51: Pull complete 
73919c529833: Pull complete 
285b77036a3a: Pull complete 
270395aafb1e: Pull complete 
Digest: sha256:183772d6f5a1decd1eb0252e542d338a5ef8c02fe4cc2cc909b58788f8728c58
Status: Downloaded newer image for mysql/mysql-server:8.0
[root@DockerHost oracle]#

[root@DockerHost oracle]# docker run --name mysql83 -v /docker/docker83:/var/lib/mysql -v /docker/option83:/etc/mysql/conf.d -v /docker/init_script:/docker-entrypoint-initdb.d -e MYSQL_ROOT_PASSWORD=mysql -d mysql/mysql-server:8.0
ecd2156cdd36d735b5d01f6d7b89ea24cc7d499cbc59e1014bc42ba92c764365
[root@DockerHost oracle]# 

[root@DockerHost oracle]# docker exec -it mysql83 mysql --default-character-set=utf8mb4 -uroot -pmysql
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 8.0.3-rc-log MySQL Community Server (GPL)

Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> select @@version;
+--------------+
| @@version    |
+--------------+
| 8.0.3-rc-log |
+--------------+
1 row in set (0.00 sec)

mysql> show variables like '%char%';
+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | utf8mb4                    |
| character_set_connection | utf8mb4                    |
| character_set_database   | utf8mb4                    |
| character_set_filesystem | binary                     |
| character_set_results    | utf8mb4                    |
| character_set_server     | utf8mb4                    |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.03 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sakila             |
| sys                |
| world              |
+--------------------+
7 rows in set (0.00 sec)


メモ:CTEの確認

mysql> WITH RECURSIVE  
    -> emp_ext (id, name, path) AS ( 
    ->    SELECT id, name, CAST(id AS CHAR(200)) 
    ->    FROM employees WHERE manager_id IS NULL 
    ->  UNION ALL 
    ->    SELECT s.id, s.name,CONCAT(m.path, ",", s.id) 
    ->    FROM emp_ext m JOIN  employees s ON m.id=s.manager_id )
    -> SELECT * FROM emp_ext ORDER BY path; 
+------+---------+-----------------+
| id   | name    | path            |
+------+---------+-----------------+
|  333 | Yasmina | 333             |
|  198 | John    | 333,198         |
|   29 | Pedro   | 333,198,29      |
| 4610 | Sarah   | 333,198,29,4610 |
|   72 | Pierre  | 333,198,29,72   |
|  692 | Tarek   | 333,692         |
|  123 | Adil    | 333,692,123     |
+------+---------+-----------------+
7 rows in set (0.00 sec)

mysql> 

メモ:Windows Functionの確認


mysql> select employee,date,sale,SUM(sale)
    -> OVER (PARTITION BY employee) AS sum FROM sales;
+----------+------------+------+------+
| employee | date       | sale | sum  |
+----------+------------+------+------+
| A        | 2017-03-01 |  200 |  900 |
| A        | 2017-04-01 |  300 |  900 |
| A        | 2017-05-01 |  400 |  900 |
| B        | 2017-03-01 |  400 | 1200 |
| B        | 2017-04-01 |  300 | 1200 |
| B        | 2017-05-01 |  500 | 1200 |
| C        | 2017-03-01 |  100 | 1000 |
| C        | 2017-04-01 |  600 | 1000 |
| C        | 2017-05-01 |  300 | 1000 |
+----------+------------+------+------+
9 rows in set (0.00 sec)

mysql> 

MySQL8.0には管理者と開発者にとって使い易い機能や関数も増えているので、
色々な場面で活用する事が出来るかと思います。

詳細情報:
http://mysqlserverteam.com/

バグ報告:
https://bugs.mysql.com/

ブログにはRC1と書いてあったけど、RC2とかもリリース予定なのかな?
Please enjoy it.


MySQL8.0がリリース候補版になりました。

Changes in MySQL 8.0.3 (2017-09-21, Release Candidate)
リリースノート:https://dev.mysql.com/doc/relnotes/mysql/8.0/en/news-8-0-3.html

【主な変更点】
Histograms, Force Index, Hints, Invisible Indexes
Common Table Expressions, Windows Functions, Character Sets,
JSON, GIS, Resource Groups, Performance Schema, Security,
Protocol, Service Infrastructure, X Protocol / X Plugin,
Performance, Tablespaces, DDL, Replication, Group Replication,
Data Dictionary, MTR Tests, Library Upgrade, Changes to Defaults

詳細:http://mysqlserverteam.com/the-mysql-8-0-3-release-candidate-is-available/

全てをカバーしてませんが、先日のセミナー資料で概要を紹介しています。


DB Tech Showcase 2017にてMySQL InnoDB Clusterの概要説明とデモをさせて頂きました。
プレゼン資料をスライドシェアにアップロードしたので、2017年4月にリリースされたMySQL標準の高可用性構成に興味あるある方、若しくは高可用性構成を検討の方はご覧ください。

余談ですが、WP Social Bookmarking Lightプラグインをアップグレードしたら古いPHPで動かなかったので、PHP5.6のバージョンにアップグレードしました。もし、WordPressのアップグレードのタイミングでwp-social-bookmarking-light.phpがエラーになったら、PHPを5.5以上にアップグレードしてみて下さい。