认识 MariaDB 10.2 - MariaDB 数据库

FROM 子句中的子查询

SELECT * FROM t2,
  (SELECT a FROM t1 WHERE b >= 'c') AS sq
  WHERE t2.c=sq.a;

公用表表达式 (CTE)

WITH sq AS (SELECT a FROM t1 WHERE b >= 'c')
  SELECT * FROM t2, sq WHERE t2.c=sq.a;

再次...

SELECT * FROM t2,
  (SELECT a FROM t1 WHERE b >= 'c') AS sq
  WHERE t2.c=sq.a;



WITH sq AS (SELECT a FROM t1 WHERE b >= 'c')
  SELECT * FROM t2, sq WHERE t2.c=sq.a;

递归 CTE

WITH RECURSIVE ancestors AS (
  SELECT * FROM folks WHERE name = 'Sergei'
  UNION ALL
  SELECT folks.* FROM folks, ancestors
    WHERE folks.id = ancestors.father
    OR    folks.id = ancestors.mother
)
SELECT * FROM ancestors;

递归 CTE：阶乘

WITH RECURSIVE fact AS
  (SELECT 1 AS n,1 AS `n!` UNION ALL
   SELECT n+1,`n!`*(n+1) FROM fact WHERE n < 20
) SELECT * FROM fact;
+----+---------------------+
| n  | n!                  |
+----+---------------------+
|  1 |                   1 |
|  2 |                   2 |
|  3 |                   6 |
|  4 |                  24 |
|  5 |                 120 |
...
| 20 | 2432902008176640000 |
+----+---------------------+

递归 CTE：斐波那契

WITH RECURSIVE fib (a, b) AS
  (SELECT 1, 1 UNION ALL
   SELECT b, a+b FROM fib WHERE b < 200000
) SELECT a FROM fib;
+--------+
| a      |
+--------+
|      1 |
|      1 |
|      2 |
|      3 |
|      5 |
|      8 |
|     13 |
...

模式

SELECT * FROM information_schema.profiling;
+----------+-----+----------------------+----------+----------+------------+----
| QUERY_ID | SEQ | STATE                | DURATION | CPU_USER | CPU_SYSTEM | CON
+----------+-----+----------------------+----------+----------+------------+----
|        9 |  17 | query end            | 0.000054 | 0.000000 | 0.000000   |
|        7 |   2 | starting             | 0.000322 | 0.000000 | 0.000000   |
|       10 |  23 | updating status      | 0.000083 | 0.000000 | 0.000000   |
|       13 |  22 | closing tables       | 0.000046 | 0.000000 | 0.000000   |
|        9 |  11 | preparing            | 0.000110 | 0.000000 | 0.000000   |
|       11 |   3 | checking permissions | 0.000068 | 0.001000 | 0.000000   |
|        7 |   7 | Table lock           | 0.000079 | 0.000000 | 0.000000   |
|       16 |  17 | Creating sort index  | 0.000044 | 0.000000 | 0.000000   |
|        2 |  16 | Sending data         | 0.000127 | 0.000000 | 0.000000   |
|        8 |  18 | query end            | 0.000053 | 0.000000 | 0.000000   |
...

运行总计

SELECT query_id, seq, state, duration,
  (SELECT SUM(duration) FROM information_schema.profiling AS p
   WHERE p.query_id=pp.query_id AND p.seq <= pp.seq) AS rt
  FROM information_schema.profiling AS pp ORDER BY query_id, seq;
+----------+-----+----------------------+----------+----------+
| query_id | seq | state                | duration | rt       |
+----------+-----+----------------------+----------+----------+
|        1 |   2 | starting             | 0.000304 | 0.000304 |
|        1 |   3 | checking permissions | 0.000063 | 0.000367 |
...
|        1 |  27 | updating status      | 0.000085 | 0.002489 |
|        1 |  28 | cleaning up          | 0.000081 | 0.002570 |
|        2 |   2 | starting             | 0.000397 | 0.000397 |
|        2 |   3 | checking permissions | 0.000069 | 0.000466 |
...
+----------+-----+----------------------+----------+----------+
336 rows in set (3.55 sec)

运行总计

SELECT query_id, seq, state, duration,
  SUM(duration) OVER (PARTITION BY query_id ORDER BY seq) AS rt
  FROM information_schema.profiling ORDER BY query_id, seq;
+----------+-----+----------------------+----------+----------+
| query_id | seq | state                | duration | rt       |
+----------+-----+----------------------+----------+----------+
|        1 |   2 | starting             | 0.000304 | 0.000304 |
|        1 |   3 | checking permissions | 0.000063 | 0.000367 |
...
|        1 |  27 | updating status      | 0.000085 | 0.002489 |
|        1 |  28 | cleaning up          | 0.000081 | 0.002570 |
|        2 |   2 | starting             | 0.000397 | 0.000397 |
|        2 |   3 | checking permissions | 0.000069 | 0.000466 |
...
+----------+-----+----------------------+----------+----------+
336 rows in set (0.04 sec)

Ntile (分桶)

SELECT query_id, state, duration,
  NTILE(10) OVER (ORDER BY duration) nt
  FROM information_schema.profiling
  WHERE query_id=30 ORDER BY nt DESC;
+----------+----------------------+----------+------+
| query_id | state                | duration | nt   |
+----------+----------------------+----------+------+
|       30 | Filling schema table | 0.012040 |   10 |
|       30 | Creating sort index  | 0.005448 |   10 |
|       30 | Sending data         | 0.004479 |   10 |
|       30 | Sending data         | 0.004017 |    9 |
|       30 | Opening tables       | 0.000545 |    9 |
|       30 | removing tmp table   | 0.000354 |    9 |
|       30 | starting             | 0.000346 |    8 |
|       30 | removing tmp table   | 0.000138 |    8 |
...

运行平均

SELECT query_id, seq, AVG(duration) OVER
  (ORDER BY query_id ROWS BETWEEN 5 PRECEDING AND 5 FOLLOWING) x
  FROM information_schema.profiling
  WHERE state='end' ORDER BY query_id;

10.2 之前：自连接

CREATE TEMPORARY TABLE employees (
  id INT PRIMARY KEY,
  name VARCHAR(100),
  reports_to INT);
Query OK, 0 rows affected (0.00 sec)

INSERT employees VALUES (1, 'Rasmus', NULL), (2, 'Sergei', 1);
Query OK, 1 rows affected (0.00 sec)

SELECT e.name, m.name AS manager FROM employees e, employees m
  WHERE e.reports_to=m.id;
ERROR 1137 (HY000): Can't reopen table: 'employees'

10.2 中的临时表

CREATE TEMPORARY TABLE employees (
  id INT PRIMARY KEY,
  name VARCHAR(100),
  reports_to INT);
INSERT employees VALUES (1, 'Rasmus', NULL), (2, 'Sergei', 1);

SELECT e.name, m.name AS manager FROM employees e, employees m
  WHERE e.reports_to=m.id;
+--------+---------+
| name   | manager |
+--------+---------+
| Sergei | Rasmus  |
+--------+---------+

CHECK 约束，1998-2016

CREATE TABLE t1 (a INT, b INT CHECK (b > 10), CHECK (a > b));
Query OK, 0 rows affected (0.00 sec)

INSERT t1 VALUES (5, 6);
Query OK, 1 row affected (0.00 sec)

等等，什么？

10.2 中的 CHECK 约束

CREATE TABLE t1 (a INT, b INT CHECK (b > 10), CHECK (a > b));

INSERT t1 VALUES (5, 6);
ERROR 4025 (23000): CONSTRAINT `b` failed for `test`.`t1`

INSERT t1 VALUES (5, 16);
ERROR 4025 (23000): CONSTRAINT `CONSTRAINT_1` failed for `test`.`t1`

10.2 中的 DEFAULT 子句

表达式
BLOB 类型的 DEFAULT 值

CREATE TABLE defs (
  uid CHAR(32) DEFAULT UUID(),
  c1 INT, c2 INT DEFAULT (c1 + 1),
  data BLOB DEFAULT 'foo'
);

虚拟列可以被索引

CREATE TABLE t1 (
  start_time TIMESTAMP,
  end_time TIMESTAMP,
  duration TIME GENERATED ALWAYS AS TIMEDIFF(start_time, end_time),
  INDEX (duration)
)

许多其他限制

每个表达式最大 64K (之前是 252 字节)
可以使用常量表达式
可以引用其他虚拟列
可以使用非确定性函数（UDF、服务器变量等）

CREATE TABLE t1 (
  a INT GENERATED ALWAYS AS (10),
  b INT GENERATED ALWAYS AS (a+1),
  c TIMESTAMP GENERATED ALWAYS AS (NOW() + INTERVAL 1 HOUR)
);

带表达式的 EXECUTE

PREPARE stmt FROM 'SELECT ?+?';

SET @a=5, @b=7;
EXECUTE stmt USING @a, @b;
+------+
| ?+?  |
+------+
|   12 |
+------+

EXECUTE stmt USING 10, 4*5;
+------+
| ?+?  |
+------+
|   30 |
+------+

移除更多限制

更长的 DECIMAL 类型
```
CREATE TABLE t1 (a DECIMAL(65,38));
```

FROM 子句中的视图和子查询

CREATE VIEW v1 AS
      SELECT * FROM (SELECT a+1 FROM t1) x;

10.2 中的 JSON

SQL 标准 2014, ISO/IEC DIS 9075-2
- JSON_VALUE, JSON_QUERY, JSON_EXISTS, JSON_OBJECT, JSON_ARRAY
MySQL 兼容扩展
- JSON_ARRAY_INSERT, JSON_CONTAINS_PATH, JSON_INSERT, JSON_REPLACE, ...
没有 JSON 类型！

GeoJSON

SELECT ST_AsGeoJSON(ST_GeomFromText('POINT(5.3 7.2)'));
+-------------------------------------------------+
| ST_AsGeoJSON(ST_GeomFromText('POINT(5.3 7.2)')) |
+-------------------------------------------------+
| {"type": "Point", "coordinates": [5.3, 7.2]}    |
+-------------------------------------------------+

SELECT ST_AsText(ST_GeomFromGeoJSON(
             '{ "type": "Point", "coordinates": [5.3, 15.0]}'));
+---------------------------------------------------------------------------------+
| ST_AsText(ST_GeomFromGeoJSON('{ "type": "Point", "coordinates": [5.3, 15.0]}')) |
+---------------------------------------------------------------------------------+
| POINT(5.3 15)                                                                   |
+---------------------------------------------------------------------------------+

使用视图：MERGE 很好

CREATE TABLE t1 (a INT UNIQUE, b INT);
CREATE TABLE t2 (a INT UNIQUE, b INT);
CREATE VIEW v1 AS SELECT a+b AS c FROM t1 WHERE a>2;
EXPLAIN EXTENDED SELECT * FROM v1, t2 WHERE a=c AND c<100;
+----+-------------+-------+------+..+------+..+------+----------+-----------------------+
| id | select_type | table | type |  | key  |  | rows | filtered | Extra                 |
+----+-------------+-------+------+..+------+..+------+----------+-----------------------+
|  1 | SIMPLE      | t1    | ALL  |  | NULL |  | 1000 |    99.80 | Using where           |
|  1 | SIMPLE      | t2    | ref  |  | a    |  |   11 |   100.00 | Using index condition |
+----+-------------+-------+------+..+------+..+------+----------+-----------------------+

Note (Code 1003): select t1.a + t1.b AS c,t2.a,t2.b from t1 join t2
where t2.a = t1.a + t1.b and t1.a + t1.b < 100 and t1.a > 2

使用视图：无 MERGE 不好

CREATE VIEW v2 AS SELECT a+b AS c FROM t1 WHERE a > 2 GROUP BY c;
EXPLAIN EXTENDED SELECT * FROM v2, t2 WHERE a=c AND c<100;
+----+-------------+------------+------+..+----------------------------------------------+
| id | select_type | table      | type |  | Extra                                        |
+----+-------------+------------+------+..+----------------------------------------------+
|  1 | PRIMARY     | t2         | ALL  |  | Using where                                  |
|  1 | PRIMARY     | <derived2> | ref  |  | Using where                                  |
|  2 | DERIVED     | t1         | ALL  |  | Using where; Using temporary; Using where; Using filesort |
+----+-------------+------------+------+..+----------------------------------------------+
3 rows in set, 1 warning (0.00 sec)

Note (Code 1003): select v2.c,t2.a,t2.b from v2 join t2 where
t2.a = v2.c and v2.c < 100

条件下推到不可合并的视图中

SET optimizer_switch='condition_pushdown_for_derived=on';
EXPLAIN FORMAT=JSON SELECT * FROM v2, t2 WHERE a=c AND c<100;
...
      "materialized": {
        "query_block": {
            "temporary_table": {
            ...
                "attached_condition": "t1.a>2 and t1.a+t1.b<100"
            }
        }
      }
...

新用户管理命令

CREATE USER foo@bar REQUIRE SSL WITH MAX_QUERIES_PER_HOUR 10;
Query OK, 0 rows affected (0.00 sec)

ALTER USER foo@bar IDENTIFIED VIA pam WITH MAX_USER_CONNECTIONS 3;
Query OK, 0 rows affected (0.00 sec)

SHOW CREATE USER foo@bar;
+------------------------------------------------------------
| CREATE USER for foo@bar
+------------------------------------------------------------
| CREATE USER 'foo'@'bar' IDENTIFIED VIA pam REQUIRE SSL
WITH MAX_QUERIES_PER_HOUR 10 MAX_USER_CONNECTIONS 3
+------------------------------------------------------------

NO PAD 排序规则

select 'test' = 'test ' collate utf8_general_ci;
+------------------------------------------+
| 'test' = 'test ' collate utf8_general_ci |
+------------------------------------------+
|                                        1 |
+------------------------------------------+

select 'test' = 'test ' collate utf8_general_nopad_ci;
+------------------------------------------------+
| 'test' = 'test ' collate utf8_general_nopad_ci |
+------------------------------------------------+
|                                              0 |
+------------------------------------------------+

PARTITION BY LIST 时的默认分区

CREATE TABLE t1 (f int) PARTITION BY LIST (f)
  (PARTITION p0 VALUES IN (1,3,5),
   PARTITION p1 VALUES IN (2,4,6),
   PARTITION pdef DEFAULT);

INSERT t1 VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9),(0);

SELECT partition_name,table_rows FROM INFORMATION_SCHEMA.PARTITIONS
       WHERE table_name='t1';
+----------------+------------+
| partition_name | table_rows |
+----------------+------------+
| p0             |          3 |
| p1             |          3 |
| pdef           |          4 |
+----------------+------------+

INFORMATION_SCHEMA.USER_VARIABLES

SET @a='something';

SHOW USER_VARIABLES
+---------------+-----------+
| Variable_name | Value     |
+---------------+-----------+
| a             | something |
+---------------+-----------+

SELECT * FROM information_schema.user_variables;
+---------------+----------------+---------------+--------------------+
| VARIABLE_NAME | VARIABLE_VALUE | VARIABLE_TYPE | CHARACTER_SET_NAME |
+---------------+----------------+---------------+--------------------+
| a             | something      | VARCHAR       | utf8               |
+---------------+----------------+---------------+--------------------+

谢谢！

Daniil Medvedev：NO PAD 排序规则 (GSoC)
Galina Shalygina：条件下推到不可合并视图 (GSoC)，公用表表达式
Daniel Black：更快的 CHECKSUM TABLE（还有很多）
等等...

谢谢！

IBM：Power8 上的 CRC32
阿里巴巴：回溯，InnoDB 持久化自增 (~50 个以上正在开发中)
腾讯：read_binlog_speed_limit，压缩 binlog 事件，延迟复制（更多正在开发中）
等等...

您也可以贡献！

代码： https://github.com/mariadb/server
知识库： https://mariadb.com/kb/
Bug 跟踪： https://jira.mariadb.org/
IRC： Freenode 上的 #maria
邮件列表： maria-discuss@lists.launchpad.net
反馈插件： 在 my.cnf 中启用 enable-feedback

认识 10.2

10.2

10.2

分析查询

公用表表达式 (CTE)

FROM 子句中的子查询

公用表表达式 (CTE)

再次...

递归 CTE

递归 CTE：阶乘

递归 CTE：斐波那契

分析查询

窗口函数

模式

运行总计

运行总计

Ntile (分桶)

运行平均

移除限制

临时表

10.2 之前：自连接

10.2 中的临时表

移除限制

CHECK 约束

CHECK 约束，1998-2016

等等，什么？

10.2 中的 CHECK 约束

移除限制

DEFAULT 子句

10.2 中的 DEFAULT 子句

移除限制

生成列

虚拟列可以被索引

许多其他限制

带表达式的 EXECUTE

移除更多限制

InnoDB 5.7

完整内容请参阅 Marko Mäkelä 的演讲

RocksDB!

敬请期待 Yoshinori Matsunobu 的内容和 Sergey Petrunia

新客户端库

和协议增强

客户端库

协议增强

JSON 和 GeoJSON

10.2 中的 JSON

GeoJSON

优化器

条件下推到不可合并的派生表和视图中

使用视图：MERGE 很好

使用视图：无 MERGE 不好

条件下推到不可合并的视图中

复制

复制

性能

性能改进

其他特性

新用户管理命令

NO PAD 排序规则

PARTITION BY LIST 时的默认分区

INFORMATION_SCHEMA.USER_VARIABLES

安全插件

更多特性

还有更多

重构

减少技术债务

贡献

谢谢！

谢谢！

您也可以贡献！

问题？

敬请期待 Yoshinori Matsunobu 的内容
和 Sergey Petrunia

条件下推到不可合并的
派生表和视图中