王者归来，Mysql8+ 香在哪？

作者: ___n | 来源:发表于2020-03-19 15:01 被阅读0次

1. Hash join

SELECT * 
    FROM t1 
    JOIN t2 
        ON t1.c1=t2.c1;

Hash join 不需要索引的支持。大多数情况下，hash join 比之前的 Block Nested-Loop 算法在没有索引时的等值连接更加高效。使用 EXPLAIN FORMAT=TREE 命令可以看到执行计划中的 hash join

mysql> EXPLAIN FORMAT=TREE
    -> SELECT * 
    ->     FROM t1 
    ->     JOIN t2 
    ->         ON t1.c1=t2.c1\G
*************************** 1. row ***************************
EXPLAIN: -> Inner hash join (t2.c1 = t1.c1)  (cost=0.70 rows=1)
    -> Table scan on t2  (cost=0.35 rows=1)
    -> Hash
        -> Table scan on t1  (cost=0.35 rows=1)

牛逼哄哄的 hash join 不负众望SQL执行时间提升了数百倍，不过要注意，如果任何连接语句（ON）中没有使用等值连接条件，将不会采用 hash join 连接方式，此时，将会采用性能更慢的 block nested loop 连接算法。

mysql> EXPLAIN FORMAT=TREE
    ->     SELECT * 
    ->         FROM t1
    ->         JOIN t2 
    ->             ON (t1.c1 = t2.c1)
    ->         JOIN t3 
    ->             ON (t2.c1 < t3.c1)\G
*************************** 1. row ***************************
EXPLAIN: <not executable by iterator executor>

2. instant add column 亿级数据秒速增加字段，8.0实现了 `Online DDL` 部分操作的高效性

3. 直方图

数据库中，查询优化器负责将SQL转换成最有效的执行计划。有时候，查询优化器会走不到最优的执行计划，导致花费了更多不必要的时间。造成这种情况的主要原因是，查询优化器有时无法准确的知道以下几个问题的答案：

每个表有多少行？
每一列有多少不同的值？
每一列的数据分布情况？

直方图能近似获得一列的数据分布情况，从而让数据库知道它含有哪些数据。

利用直方图，用户可以对一张表的一列做数据分布的统计，特别是针对没有索引的字段。这可以帮助查询优化器找到更优的执行计划。统计直方图的主要使用场景是用来计算字段选择性，即过滤效率。

可以通过以下方式来创建或者删除直方图：

ANALYZE TABLE tbl_name UPDATE HISTOGRAM ON col_name [, col_name] WITH N BUCKETS;
ANALYZE TABLE tbl_name DROP HISTOGRAM ON col_name [, col_name];

buckets默认是100。
统计直方图的信息存储在数据字典表 column_statistcs 中,可以通过视图information_schema.COLUMN_STATISTICS 访问。
直方图以灵活的JSON的格式存储。
ANALYZE TABLE会基于表大小自动判断是否要进行取样操作。
ANALYZE TABLE也会基于表中列的数据分布情况以及bucket的数量来决定是否要建立等宽直方图（singleton）还是等高直方图（equi-height）

4. 函数索引、表达式索引

函数索引（functional key parts），也就是将表达式的值作为索引的内容，而不是列值或列值前缀。
将函数作为索引键可以用于索引那些没有在表中直接存储的内容。例如：

CREATE TABLE t1 (col1 INT, col2 INT, INDEX func_index ((ABS(col1))));
CREATE INDEX idx1 ON t1 ((col1 + col2));
CREATE INDEX idx2 ON t1 ((col1 + col2), (col1 - col2), col1);
ALTER TABLE t1 ADD INDEX ((col1 * 40) DESC);

函数索引实际上是通过隐藏的虚拟字段来实现的，因此对于一般字段上的限制，在函数索引上也都会有。

函数索引的限制
只有在字段上可以使用的函数才被允许构建函数索引。
主键不能被包含在函数索引中。
空间索引和全文索引不能包含函数索引。
如果要删除的字段上有函数索引，必须先删除函数索引，才能删除字段。

5. 消除了全局的buffer pool mutex。

MySQL 8+ 版本使用多个更细粒度的mutex代替buffer pool mutex这把大锁。

具体的替换方式如下：

LRU_list_mutex for the LRU_list;
zip_free mutex for the zip_free arrays;
zip_hash mutex for the zip_hash hash and in_zip_hash flag;
free_list_mutex for the free_list and withdraw list.

通过对锁的拆分，降低了全局锁的竞争，提升了申请buffer pool的并发处理能力。

6. 倒序索引

MySQL长期以来对索引的建立只允许正向asc存储，就算建立了desc，也是忽略掉。

比如对于以下的查询，无法发挥索引的最佳性能。

查询一：

select * from tb1 where f1 = ... order by id desc;

查询二：

select * from tb1 where f1 = ... order by f1 asc , f2 desc;

那对于上面的查询，尤其是数据量和并发到一定峰值的时候，则对OS的资源消耗非常大，一般这样的SQL在查询计划里面会出现using filesort等状态。

MySQL 8 + 给我们带来了倒序索引（Descending Indexes），也就是说反向存储的索引。( 这里不要跟搜索引擎中的倒排索引混淆了，MySQL这里只是反向排序存储而已)。虽然只是排序存储，但是已经解决了很大的问题，任你正序排，倒序排，还是混合顺序排，啥姿势都能满足

7. 不可见索引 INVISIBLE INDEX

不可见索引或者叫隐藏索引，就是对优化器不可见，查询的时候优化器不会把她它作为备选。

比如，我有张表t1，本来已经有索引idxf1，idxf2，idxf3。我通过数据字典检索到idxf3基本没有使用过，那我是不是可以判断这个索引直接删掉就好了？
那如果删掉后突然有新上的业务要大量使用呢？
难道我要频繁的 drop index/add index 吗？这个时候选择开销比较小的隐藏索引就好了。

再比如我想要测试下新建索引对我整个业务的影响程度，如果我直接建新索引，那我既有业务涉及到这个字段的有可能会受到很大影响，那这个时候隐藏索引也是非常合适的。

以后删个索引，想删又不太敢动手，就可以改为不可见，等观察一阵子，确认没影响了再放心删除。

mysql> alter table f1 add key idx_f1(f1), add key idx_f2(f2);
Query OK, 0 rows affected (0.12 sec)
Records: 0 Duplicates: 0 Warnings: 0
// 把f2列上的索引变为不可见
mysql> alter table f1 alter index idx_f2 invisible;
Query OK, 0 rows affected (0.05 sec)
Records: 0 Duplicates: 0 Warnings: 0

8. 引入WRITESET模式

writeset 信息记录了多个事务里是否会对相同数据进行变更，若无，则可以并发执行，比多线程复制上升了一个档次，通过参数 binlog_transaction_dependency_tracking 可以进行配置,此方案号称是彻底解决困扰MySQL运维人员多年的复制延迟问题，大大提高并行复制效率，主从复制延迟进一步缩小。

9. 针对JSON数据类型，增加 Multi-valued indexes

索引上对于同一个Primary key, 可以建立多个二级索引项，更方便JSON的搜索了。

Create Table: CREATE TABLE `customers` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `modified` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `custinfo` json DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `zips` ((cast(json_extract(`custinfo`,_latin1'$.zip') as unsigned array)))
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

+----+---------------------+-------------------------------------------------------------------+
| id | modified            | custinfo                                                          |
+----+---------------------+-------------------------------------------------------------------+
|  1 | 2019-08-14 16:08:50 | {"user": "Jack", "user_id": 37, "zipcode": [94582, 94536]}        |
|  2 | 2019-08-14 16:08:50 | {"user": "Jill", "user_id": 22, "zipcode": [94568, 94507, 94582]} |
|  3 | 2019-08-14 16:08:50 | {"user": "Bob", "user_id": 31, "zipcode": [94477, 94536]}         |
|  4 | 2019-08-14 16:08:50 | {"user": "Mary", "user_id": 72, "zipcode": [94536]}               |
|  5 | 2019-08-14 16:08:50 | {"user": "Ted", "user_id": 56, "zipcode": [94507, 94582]}         |
+----+---------------------+-------------------------------------------------------------------+

root@test 04:09:00>SELECT * FROM customers WHERE 94507 MEMBER OF(custinfo->'$.zipcode');
+----+---------------------+-------------------------------------------------------------------+
| id | modified            | custinfo                                                          |
+----+---------------------+-------------------------------------------------------------------+
|  2 | 2019-08-14 16:08:50 | {"user": "Jill", "user_id": 22, "zipcode": [94568, 94507, 94582]} |
|  5 | 2019-08-14 16:08:50 | {"user": "Ted", "user_id": 56, "zipcode": [94507, 94582]}         |
+----+---------------------+-------------------------------------------------------------------+

2 rows in set (0.00 sec)
 
root@test 04:09:41>SELECT * FROM customers  WHERE JSON_CONTAINS(custinfo->'$.zipcode', CAST('[94507,94582]' AS JSON));
+----+---------------------+-------------------------------------------------------------------+
| id | modified            | custinfo                                                          |
+----+---------------------+-------------------------------------------------------------------+
|  2 | 2019-08-14 16:08:50 | {"user": "Jill", "user_id": 22, "zipcode": [94568, 94507, 94582]} |
|  5 | 2019-08-14 16:08:50 | {"user": "Ted", "user_id": 56, "zipcode": [94507, 94582]}         |
+----+---------------------+-------------------------------------------------------------------+
2 rows in set (0.00 sec)
 
root@test 04:09:54>SELECT * FROM customers   WHERE JSON_OVERLAPS(custinfo->'$.zipcode', CAST('[94507,94582]' AS JSON));
+----+---------------------+-------------------------------------------------------------------+
| id | modified            | custinfo                                                          |
+----+---------------------+-------------------------------------------------------------------+
|  1 | 2019-08-14 16:08:50 | {"user": "Jack", "user_id": 37, "zipcode": [94582, 94536]}        |
|  2 | 2019-08-14 16:08:50 | {"user": "Jill", "user_id": 22, "zipcode": [94568, 94507, 94582]} |
|  5 | 2019-08-14 16:08:50 | {"user": "Ted", "user_id": 56, "zipcode": [94507, 94582]}         |
+----+---------------------+-------------------------------------------------------------------+
3 rows in set (0.00 sec)

10. HINT语法增强，更方便在执行查询时动态设定选项，针对不同SQL采用不同策略。

HINT简单来说，就是在某些特定的场景下，人工协助MySQL优化器工作，使她生成最优的执行计划。
一般来说，优化器的执行计划都是最优化的，不过在某些特定场景下，执行计划可能不是最优化。

比如：表t1经过大量的频繁更新操作，（UPDATE,DELETE,INSERT），数据的可选择性基数已经很不准确了，这时候刚好执行了一条SQL，那么有可能这条SQL的执行计划就不是最优的。为什么说有可能呢？

11. 新的TempTable引擎

新的TempTable引擎，解决了 VARCHAR 字段的边长存储以及大对象的内存存储。
由变量 interal_tmp_mem_storage_engine 来控制，可选值为 TempTable（默认）和 Memory；
新引擎的大小由参数 temp_table_max_ram 来控制，默认为1G。
超过了则存储在磁盘上（ibtmp1）。
并且计数器由性能字典的表 memory_summary_global_by_event_name 来存储。

12 Group Relication

增加了展示信息

12 Clone Plugin 以及 ReplicaSet

Clone Plugin 以及 ReplicaSet 特性，允许用户可以将当前实例进行本地或者远程的clone,这在某些场景尤其想快速搭建复制备份或者在 group replication 里加入新成员时非常有用。

远程clone
CLONE INSTANCE FROM USER@HOST:PORT
UIDENTIFIED BY 'password'
[DATA DIRECTORY [=] 'clone_dir' ]
[REQUIRE [NO] SSL];

mysql>SET GLOBAL clone_valid_donor_list = 'xxx.xxx.xxx.xxx:3306';
mysql>CLONE INSTANCE FROM clone_user@xxx.xxx.xxx.xxx:3306 IDENTIFIED BY 'password'
mysql>CLONE INSTANCE FROM user_name@xxx.xxx.xxx.xxx:3306 IDENTIFIED BY 'password'

注意：
需要指定绝对路径，并且路径目录必须不存在
在接受机器上启动mysqld，执行上述语句连接到目标机器，就能从目标机器上clone数据到本地，注意如果没有指定data directory的话，就默认配置的目录，已有的文件会被清理掉，并在clone完成后重启
两个实例上都需要安装clone plugin
必须有相同的字符集设置

13. sqlrequireprimary_key

选项设定强制要求每个表都得有个主键

14. 实例重启后的自增ID持久化

自增主键没有持久化是个比较早的bug

+----+
| id |
+----+
|  1 |
|  2 |
|  3 |

mysql> delete from t1 where id=3;
Query OK, 1 row affected (0.36 sec)

mysql> insert into t1 values(null);
Query OK, 1 row affected (0.35 sec)
// 未重用 3
mysql> select * from t1;
+----+
| id |
+----+
|  1 |
|  2 |
|  4 |
+----+
mysql> delete from t1 where id=4;

# service mysqld restart

mysql> insert into t1 values(null);
Query OK, 1 row affected (0.00 sec)
// 按上面的逻辑，怎么不是 5？
mysql> select * from t1;
+----+
| id |
+----+
|  1 |
|  2 |
|  3 |
+----+
3 rows in set (0.00 sec)

究其原因，在于自增主键的分配，是由InnoDB数据字典内部一个计数器来决定的，而该计数器只在内存中维护，并不会持久化到磁盘中,当数据库重启时，该计数器会通过下面这种方式初始化。

15. extra admin port

管理员可以给自己开后门了

主要包含几个配置参数:
admin_address: 用于指定管理员发起tcp连接的主机地址，可以是ipv4,ipv6, 或者Host name等等，他类似bind-address，但不同的是只能接受一个ip地址
admin_port: 顾名思义，就是管理员用来连接的端口号，注意如果admin_address没有设置的话，这个端口号是无效的
create_admin_listener_thread: 是否创建一个单独的listener线程来监听admin的链接请求，默认值是关闭的，建议打开，否则其会使用已有的监听线程去监听admin连接。该参数同样需要admin_address打开, 否则没有任何影响
注意必须要有权限service_connection_admin才能登陆该端口，否则会报错

根据文档描述，admin port的连接个数不受max_connection或者Max_user_connection的限制。

16. SET PERSIST

在线修改完配置参数后，SET PERSIST 语法实现持久化，不用再手工修改一次my.cnf了。

show variables like '%max_connections%';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| max_connections        | 151  |
| mysqlx_max_connections | 100  |
+------------------------+-------+
2 rows in set (0.00 sec)

mysql> set persist max_connections=200;
Query OK, 0 rows affected (0.00 sec)

mysql> show variables like '%max_connections%';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| max_connections        | 200  |
| mysqlx_max_connections | 100  |
+------------------------+-------+
2 rows in set (0.00 sec)

全局变量的修改会保存在两处，数据目录下mysqld-auto.cnf文件( 注意，不是启动时--defaults-file 指定的配置文件。),以json格式保存，其中，Metadata 记录了这次修改的用户及时间信息。

{ 
   "Version" : 1 , "mysql_server" : {
       "max_connections" : {
            "Value" : "200" , "Metadata" : { "Timestamp" : 1525509217566258 , "User" : "root" , "Host" : "localhost" } 
       }
   }
}

在数据库启动时，如果因为修改了mysqld-auto.cnf文件而失败，可手动删除mysqld-auto.cnf文件或将persisted_globals_load变量设置为off来避免该文件的加载。

17. 锁增强，增加SKIP LOCK、NOWAIT LOCK锁模式，以及BACKUP LOCk。

SKIP LOCKED表示加锁的记录如果已被其他线程占有锁，则跳过，而非等待。
NOWAIT表示加锁记录有锁则报错，而非等待

select* fromstock whereskuId = 1for updateskip locked;
Empty set(0.00sec)
select* fromstock whereskuId = 1for updatenowait;
ERROR 3572(HY000): Do not wait for lock

BACKUP LOCk 允许在online备份的时候进行DML操作，同时可防止快照不一致。备份锁由 lock instance for backup和unlock instance 语法支持,使用这些语句需要BACKUP_ADMIN权限。

18. 窗口函数

窗口的概念非常重要，它可以理解为记录集合，窗口函数也就是在满足某种条件的记录集合上执行的特殊函数。
对于每条记录都要在此窗口内执行函数，有的函数随着记录不同，窗口大小都是固定的，这种属于静态窗口；有的函数则相反，不同的记录对应着不同的窗口，这种动态变化的窗口叫滑动窗口。

窗口函数和普通聚合函数也很容易混淆，二者区别如下：
聚合函数是将多条记录聚合为一条；而窗口函数是每条记录都会执行，有几条记录执行完还是几条。
聚合函数也可以用于窗口函数中
下面是一个窗口函数的简单例子：

image.png

上面例子中，row_number()over(partition by user_no order by amount desc)这部分都属于窗口函数，它的功能是显示每个用户按照订单金额从大到小排序的序号。

按照功能划分，可以把MySQL支持的窗口函数分为如下几类：
序号函数：row_number() / rank() / dense_rank()
分布函数：percent_rank() / cume_dist()
前后函数：lag() / lead()
头尾函数：first_val() / last_val()
其他函数：nth_value() / nfile()

窗口函数的基本用法 : 函数名（[expr]）over 子句

岁月更迭中 MySQL从 “基本可用”，“边缘系统可以用" , "小系统可用" 再到，“哇操！你怎么不用MySQL” ！！

王者归来，Mysql8+ 香在哪？

1. Hash join

2. instant add column 亿级数据秒速增加字段，8.0实现了 `Online DDL` 部分操作的高效性

3. 直方图

4. 函数索引、表达式索引

5. 消除了全局的buffer pool mutex。

6. 倒序索引

7. 不可见索引 INVISIBLE INDEX

8. 引入WRITESET模式

9. 针对JSON数据类型，增加 Multi-valued indexes

10. HINT语法增强，更方便在执行查询时动态设定选项，针对不同SQL采用不同策略。

11. 新的TempTable引擎

12 Group Relication

12 Clone Plugin 以及 ReplicaSet

13. sqlrequireprimary_key

14. 实例重启后的自增ID持久化

15. extra admin port

16. SET PERSIST

17. 锁增强，增加SKIP LOCK、NOWAIT LOCK锁模式，以及BACKUP LOCk。

18. 窗口函数

相关文章

网友评论

延伸阅读

深度阅读

栏目导航

热点阅读

数据库

王者归来，Mysql8+ 香在哪？

1. Hash join

2. instant add column 亿级数据秒速增加字段，8.0实现了 Online DDL 部分操作的高效性

3. 直方图

4. 函数索引、表达式索引

5. 消除了全局的buffer pool mutex。

6. 倒序索引

7. 不可见索引 INVISIBLE INDEX

8. 引入WRITESET模式

9. 针对JSON数据类型，增加 Multi-valued indexes

10. HINT语法增强，更方便在执行查询时动态设定选项，针对不同SQL采用不同策略。

11. 新的TempTable引擎

12 Group Relication

12 Clone Plugin 以及 ReplicaSet

13. sqlrequireprimary_key

14. 实例重启后的自增ID持久化

15. extra admin port

16. SET PERSIST

17. 锁增强，增加SKIP LOCK、NOWAIT LOCK锁模式，以及BACKUP LOCk。

18. 窗口函数

相关文章

网友评论

延伸阅读

深度阅读

栏目导航

热点阅读

数据库

2. instant add column 亿级数据秒速增加字段，8.0实现了 `Online DDL` 部分操作的高效性