-
读写混合性能相比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