mysql 8.md

读写混合性能相比5.7有很大提升
高竞争场景的性能有很大提升
不支持query cache了
只有innodb支持分区(PARTITION)
expire_logs_days废弃了，使用binlog_expire_logs_seconds
关闭binlog需要使用--skip-log-bin或--disable-log-bin
innodb的auto_increment在重启后不再重置。

innodb_autoinc_lock_mode默认为2，binlog为statement时会不安全，所以默认为row。

information_schema.tables的AUTO_INCREMENT字段innodb表为null，保存在information_schema.innodb_tablestats的AUTOINC中。

支持窗口函数

mysql> SELECT employee, sale, date, SUM(sale) OVER (PARTITION by employee ORDER BY date) AS cum_sales FROM sales;
+----------+------+------------+-----------+
| employee | sale | date       | cum_sales |
+----------+------+------------+-----------+
| odin     |  200 | 2017-03-01 |       200 |
| odin     |  300 | 2017-04-01 |       500 |
| odin     |  400 | 2017-05-01 |       900 |
| thor     |  400 | 2017-03-01 |       400 |
| thor     |  300 | 2017-04-01 |       700 |
| thor     |  500 | 2017-05-01 |      1200 |
+----------+------+------------+-----------+

支持递归CTE(公用表表达式)

WITH RECURSIVE my_cte AS
(
  SELECT 1 AS n
  UNION ALL
  SELECT 1+n FROM my_cte WHERE n<10
)
SELECT * FROM my_cte;
 
+------+
| n    |
+------+
|    1 |
|    2 |
|    3 |
|    4 |
|    5 |
|    6 |
|    7 |
|    8 |
|    9 |
|   10 |
+------+

event_scheduler默认开启
可以使用 NOWAIT 和 SKIP LOCKED，放弃加锁和跳过已经加锁的行

https://mysqlserverteam.com/mysql-8-0-1-using-skip-locked-and-nowait-to-handle-hot-rows/

支持降序索引

好处是索引向前扫描比向后扫描快一些，并且在混合了asc/desc的order by语句中可以使用索引

支持GROUPING

mysql> select * from t1;
+------+------+------+
| a    | b    | c    |
+------+------+------+
|  111 |   11 |   11 |
|  222 |   22 |   22 |
|  111 |   12 |   12 |
|  222 |   23 |   23 |
| 1111 | NULL |  112 |
| NULL |  112 | NULL |
+------+------+------+

mysql> SELECT a, b, SUM(c) as SUM, GROUPING(a), GROUPING(b), GROUPING(a, b) FROM t1 GROUP BY a,b WITH ROLLUP;
+------+------+------+-------------+-------------+----------------+
| a    | b    | SUM  | GROUPING(a) | GROUPING(b) | GROUPING(a, b) |
+------+------+------+-------------+-------------+----------------+
| NULL |  112 | NULL |           0 |           0 |              0 |
| NULL | NULL | NULL |           0 |           1 |              1 |
|  111 |   11 |   11 |           0 |           0 |              0 |
|  111 |   12 |   12 |           0 |           0 |              0 |
|  111 | NULL |   23 |           0 |           1 |              1 |
|  222 |   22 |   22 |           0 |           0 |              0 |
|  222 |   23 |   23 |           0 |           0 |              0 |
|  222 | NULL |   45 |           0 |           1 |              1 |
| 1111 | NULL |  112 |           0 |           0 |              0 |
| 1111 | NULL |  112 |           0 |           1 |              1 |
| NULL | NULL |  180 |           1 |           1 |              3 |
+------+------+------+-------------+-------------+----------------+

https://mysqlserverteam.com/mysql-8-0-grouping-function/

支持/*+ */形式的优化提示及变量设置

mysql> insert into x (b) values (1), (2);
mysql> select * from x;
+---+------+
| i | b    |
+---+------+
| 1 |    1 |
| 2 |    2 |
+---+------+

mysql> insert /*+ SET_VAR(auto_increment_increment=10) */ into x (b) values (1), (2);
mysql> select * from x;
+----+------+
| i  | b    |
+----+------+
|  1 |    1 |
|  2 |    2 |
| 11 |    1 |
| 21 |    2 |
+----+------+

mysql> insert into x (b) values (1), (2);
mysql> select * from x;
+----+------+
| i  | b    |
+----+------+
|  1 |    1 |
|  2 |    2 |
| 11 |    1 |
| 21 |    2 |
| 22 |    1 |
| 23 |    2 |
+----+------+

https://mysqlserverteam.com/new-optimizer-hints-in-mysql/

https://mysqlserverteam.com/new-optimizer-hint-for-changing-the-session-system-variable/

更多更快的JSON函数用于操作JSON字段
增加地理支持
使用UTF8MB4作为默认字符集

UTF8MB4最大字符长度为4，是UTF8的超集，主要是多了一些emoji的编码

MySQL的元数据不再使用mysql数据库中单独的表存储，而是使用数据字典(Data Dictionary)表

mysql目录中的文件少了很多，原来：

columns_priv.frm  general_log.CSM         innodb_table_stats.frm  proxies_priv.MYI          tables_priv.MYD
columns_priv.MYD  general_log.CSV         innodb_table_stats.ibd  server_cost.frm           tables_priv.MYI
columns_priv.MYI  general_log.frm         ndb_binlog_index.frm    server_cost.ibd           time_zone.frm
db.frm            gtid_executed.frm       ndb_binlog_index.MYD    servers.frm               time_zone.ibd
db.MYD            gtid_executed.ibd       ndb_binlog_index.MYI    servers.ibd               time_zone_leap_second.frm
db.MYI            help_category.frm       plugin.frm              slave_master_info.frm     time_zone_leap_second.ibd
db.opt            help_category.ibd       plugin.ibd              slave_master_info.ibd     time_zone_name.frm
engine_cost.frm   help_keyword.frm        proc.frm                slave_relay_log_info.frm  time_zone_name.ibd
engine_cost.ibd   help_keyword.ibd        proc.MYD                slave_relay_log_info.ibd  time_zone_transition.frm
event.frm         help_relation.frm       proc.MYI                slave_worker_info.frm     time_zone_transition.ibd
event.MYD         help_relation.ibd       procs_priv.frm          slave_worker_info.ibd     time_zone_transition_type.frm
event.MYI         help_topic.frm          procs_priv.MYD          slow_log.CSM              time_zone_transition_type.ibd
func.frm          help_topic.ibd          procs_priv.MYI          slow_log.CSV              user.frm
func.MYD          innodb_index_stats.frm  proxies_priv.frm        slow_log.frm              user.MYD
func.MYI          innodb_index_stats.ibd  proxies_priv.MYD        tables_priv.frm           user.MYI

现在：

general_log_1242.sdi  general_log.CSV                  innodb_table_stats_backup57.ibd  slow_log.CSM
general_log.CSM       innodb_index_stats_backup57.ibd  slow_log_1244.sdi                slow_log.CSV

支持原子的DDL语句
支持索引不可见。索引对优化器不可见，但仍维护，可用于判断索引是否合理
可以使用SET PERSIST <variable_name> = <value>持久设置MySQL的参数

之前的版本设置参数需要用set global并且修改my.cnf文件，现在只需要使用set persist就可以做同样的事情。新的参数值会写入data目录下的mysqld-auto.cnf文件，重启时会生效。

增加SQL命令restart用于重启MySQL服务

升级步骤参见 https://dev.mysql.com/doc/refman/8.0/en/upgrading.html

jedy/mysql 8.md