1.支持带有多个ORDER/LIMIT操作外部级别的圆括号查询表达式
在8.0.28版本上报错：
在8.0.31上执行成功：
2.支持并行索引
innodb_ddl_buffer_size定义了创建二级索引分配的内存
innodb_ddl_threads 定义索引创建的排序和构建阶段的最大并行线程数
每个DDL线程最大缓存计算是 innodb_ddl_buffer_size/innodb_ddl_threads
以上配置后，建二级索引会自动以并行方式创建索引。
限制：
不支持并行创建包含虚拟列的索引
不支持并行创建全文索引
不支持并行创建空间索引
3.支持交集，差集
mysql [localhost:8031] {msandbox} (test) > select * from a INTERSECT
-> select * from b;
±-----±-----+
| id | c1 |
±-----±-----+
| 3 | 3 |
| 4 | 4 |
±-----±-----+
2 rows in set (0.00 sec)
mysql [localhost:8031] {msandbox} (test) > select * from a EXCEPT
-> select * from b;
±-----±-----+
| id | c1 |
±-----±-----+
| 1 | 1 |
| 2 | 2 |
±-----±-----+
2 rows in set (0.00 sec)
4.支持直方图用json格式
直方图是mysql8.0新加的特性，对表中数据分布不均匀的列值，直方图可以使优化器分析出更优的执行计划。
1.对表收集直方图
mysql [localhost:8031] {root} (sbtest) > ANALYZE TABLE sbtest1 UPDATE HISTOGRAM ON k WITH 10 BUCKETS;
±---------------±----------±---------±---------------------------------------------+
| Table | Op | Msg_type | Msg_text |
±---------------±----------±---------±---------------------------------------------+
| sbtest.sbtest1 | histogram | status | Histogram statistics created for column ‘k’. |
±---------------±----------±---------±---------------------------------------------+
1 row in set (18.64 sec)
- 查看直方图信息
mysql [localhost:8031] {root} (sbtest) > select * from information_schema.column_statistics\G
*************************** 1. row ***************************
SCHEMA_NAME: sbtest
TABLE_NAME: sbtest1
COLUMN_NAME: k
HISTOGRAM: {“buckets”: [[68075, 239629, 0.10025832924935002, 31792], [239632, 249140, 0.20033114874836294, 6079], [249141, 249634, 0.3003485922024991, 494], [249635, 250128, 0.4005017069665833, 494], [250129, 250625, 0.5007295793912512, 497], [250626, 251118, 0.600974064629382, 493], [251119, 251613, 0.7011908618450744, 495], [251614, 252108, 0.8012830629597942, 495], [252109, 261678, 0.9015386234069004, 6172], [261681, 419429, 1.0, 31218]], “data-type”: “int”, “null-values”: 0.0, “collation-id”: 8, “last-updated”: “2022-10-18 05:35:50.836267”, “sampling-rate”: 0.724133935812768, “histogram-type”: “equi-height”, “number-of-buckets-specified”: 10}
1 row in set (0.00 sec)
3.删掉直方图
mysql [localhost:8031] {root} (sbtest) > ANALYZE TABLE sbtest1 drop HISTOGRAM ON k;
±---------------±----------±---------±---------------------------------------------+
| Table | Op | Msg_type | Msg_text |
±---------------±----------±---------±---------------------------------------------+
| sbtest.sbtest1 | histogram | status | Histogram statistics removed for column ‘k’. |
±---------------±----------±---------±---------------------------------------------+
1 row in set (0.00 sec)
再次查看直方图
mysql [localhost:8031] {root} (sbtest) > select * from information_schema.column_statistics\G
Empty set (0.00 sec)
- 当直方图没有数据时，我们可以用json格式再次生成直方图，这种生成的直方图高效，可以秒级完成。
对性能没有影响，因为只修改元数据。
mysql [localhost:8031] {root} (sbtest) > ANALYZE TABLE sbtest1 UPDATE HISTOGRAM ON k USING DATA ‘{“buckets”: [[68075, 239629, 0.10025832924935002, 31792], [239632, 249140, 0.20033114874836294, 6079], [249141, 249634, 0.3003485922024991, 494], [249635, 250128, 0.4005017069665833, 494], [250129, 250625, 0.5007295793912512, 497], [250626, 251118, 0.600974064629382, 493], [251119, 251613, 0.7011908618450744, 495], [251614, 252108, 0.8012830629597942, 495], [252109, 261678, 0.9015386234069004, 6172], [261681, 419429, 1.0, 31218]], “data-type”: “int”, “null-values”: 0.0, “collation-id”: 8, “last-updated”: “2022-10-18 05:35:50.836267”, “sampling-rate”: 0.724133935812768, “histogram-type”: “equi-height”, “number-of-buckets-specified”: 10}’;
±---------------±----------±---------±---------------------------------------------+
| Table | Op | Msg_type | Msg_text |
±---------------±----------±---------±---------------------------------------------+
| sbtest.sbtest1 | histogram | status | Histogram statistics created for column ‘k’. |
±---------------±----------±---------±---------------------------------------------+
1 row in set (0.00 sec)
再次查看直方图
mysql [localhost:8031] {root} (sbtest) > select * from information_schema.column_statistics\G
*************************** 1. row ***************************
SCHEMA_NAME: sbtest
TABLE_NAME: sbtest1
COLUMN_NAME: k
HISTOGRAM: {“buckets”: [[68075, 239629, 0.10025832924935002, 31792], [239632, 249140, 0.20033114874836297, 6079], [249141, 249634, 0.3003485922024991, 494], [249635, 250128, 0.4005017069665833, 494], [250129, 250625, 0.5007295793912512, 497], [250626, 251118, 0.600974064629382, 493], [251119, 251613, 0.7011908618450744, 495], [251614, 252108, 0.8012830629597942, 495], [252109, 261678, 0.9015386234069004, 6172], [261681, 419429, 1.0, 31218]], “data-type”: “int”, “null-values”: 0.0, “collation-id”: 8, “last-updated”: “2022-10-18 05:38:19.890071”, “sampling-rate”: 0.724133935812768, “histogram-type”: “equi-height”, “number-of-buckets-specified”: 10}
1 row in set (0.00 sec)
直方图使用注意场景：
1.不支持加密表和临时表
2.适用于除几何类型(空间数据)和JSON之外的所有数据类型的列
3.可以为存储的和虚拟生成的列生成直方图
4.单列唯一索引不能生成直方图
5.新增full为保留关键字
mysql [localhost:8031] {msandbox} (information_schema) > select * from information_schema.keywords where word like ‘%full%’;
±---------±---------+
| WORD | RESERVED |
±---------±---------+
| FULL | 0 |
| FULLTEXT | 1 |
±---------±---------+
2 rows in set (0.01 sec)
6.离线模式需要CONNECTION_ADMIN权限才可连接
mysql [localhost:8031] {root} (sbtest) > set global offline_mode=on;
Query OK, 0 rows affected (0.00 sec)
用test1用户连接时，报错The server is currently in offline mode
[root@localhost msb_8_0_31]# ./use
ERROR 3032 (HY000): The server is currently in offline mode
当授权CONNECTION_ADMIN时，连接成功。
mysql [localhost:8031] {root} (sbtest) > grant CONNECTION_ADMIN on . to test1@’%’;
Query OK, 0 rows affected (0.00 sec)
[root@localhost msb_8_0_31]# ./use
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 33
Server version: 8.0.31 MySQL Community Server - GPL
Copyright © 2000, 2022, Oracle and/or its affiliates.
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 [localhost:8031] {test1} ((none)) >
参考：https://dev.mysql.com/doc/relnotes/mysql/8.0/en/news-8-0-31.html
