1、Json支持及虚拟列

1.1 json

Json在5.7.8原生支持，在8.0引入了json字段的部分更新（json partial update）以及两个聚合函数，JSON_OBJECTAGG，JSON_ARRAYAGG

示例查询：

select JSON_UNQUOTE(json_json->'$.name') ,json_json->'$.name',json_json 
from t_json 
where json_json->'$.name'='李四';

5.7.13之后引入了->> 等同于 JSON_UNQUOTE

示例查询：

select JSON_UNQUOTE(json_json->'$.name') ,json_json->'$.name', json_json->>'$.name',json_json 
from t_json 
where json_json->>'$.name'='李四';

更新常用的操作

json_replace：只替换已经存在的旧值，不存在则忽略。

update t_json set json_json=JSON_REPLACE(json_json, '$.class_name', '小学') ,json_text=JSON_REPLACE(json_text, '$.class_name', '小学') where id=6;

json_set：替换旧值，并插入不存在的新值；

update t_json set json_json=JSON_set(json_json, '$.gride', '中学') where id=7;

json_insert：插入新值，但不替换已经存在的旧值；

update t_json set json_json=JSON_insert(json_json, '$.tianqi', 'daxue') where id=7;

json_remove() 删除元素。

update t_json set json_json=JSON_REMOVE(json_json, '$.tianqi','$.gride') where id=7;

要特别注意的是， -> 搜索，JSON 中的元素搜索是严格区分变量类型的，比如说整型和字符串是严格区分的. ->> 搜索，不严格区分

下表列出了json的常见操作，有兴趣的可以研究学习下，也可以查看官方文档

编辑

官方文档

https://dev.mysql.com/doc/refman/5.7/en/json-function-reference.html

json_objectagg 聚合函数通过JSON聚合函数，可以在 SQL 中直接把数据整合为JSON结构，非常简单。

示例：

#产品表
CREATE TABLE product  (
id int
name varchar(50),
manufacturer varchar(50),
price int
) ;

#产品说明
CREATE TABLE value  (
prod_id int,
attribute_id int,
value text
);

#属性表
CREATE TABLE attribute  (
id int,
name varchar(120),
description varchar(256)
); 

INSERT INTO product VALUES (1, 'LED Desk Lamp', 'X', 26);
INSERT INTO product VALUES (2, 'Laptop', 'Y', 800);
INSERT INTO product VALUES (3, 'Grill', 'Z', 300);


INSERT INTO value VALUES (1, 1, 'black');
INSERT INTO value VALUES (1, 2, 'plastic');
INSERT INTO value VALUES (1, 3, 'classic');
INSERT INTO value VALUES (1, 4, 'LED');
INSERT INTO value VALUES (1, 5, 'Indoor use only');
INSERT INTO value VALUES (2, 1, 'blue');
INSERT INTO value VALUES (2, 6, 'quad core');
INSERT INTO value VALUES (2, 7, '3400 mhz');
INSERT INTO value VALUES (2, 8, '2,1 kg');
INSERT INTO value VALUES (2, 9, '9h');
INSERT INTO value VALUES (3, 1, 'black');
INSERT INTO value VALUES (3, 8, '5 kg');
INSERT INTO value VALUES (3, 10, 'gas');


INSERT INTO attribute VALUES (1, 'color', NULL);
INSERT INTO attribute VALUES (2, 'material', NULL);
INSERT INTO attribute VALUES (3, 'style', NULL);
INSERT INTO attribute VALUES (4, 'bulb_type', NULL);
INSERT INTO attribute VALUES (5, 'usage', NULL);
INSERT INTO attribute VALUES (6, 'cpu_type', NULL);
INSERT INTO attribute VALUES (7, 'cpu_speed', NULL);
INSERT INTO attribute VALUES (8, 'weight', NULL);
INSERT INTO attribute VALUES (9, 'battery_life', NULL);
INSERT INTO attribute VALUES (10, 'fuel_type', NULL);

SELECT
JSON_OBJECT("key", p.id,
"title", p.name,
"manufacturer", p.manufacturer,
"price", p.price,
"specifications", JSON_OBJECTAGG(a.name, v.value)) as product
FROM 
product as p JOIN value as v 
ON p.id=v.prod_id 
JOIN attribute as a 
ON a.id=v.attribute_id
GROUP BY v.prod_id;

1.2 虚拟列

MySQL 5.7.5推出了Generated Columns （衍生列）虚拟列

在MySQL 5.7中，支持两种Generated Column，即Virtual Generated Column和Stored Generated Column，前者只将Generated Column保存在数据字典中（表的元数据），并不会将这一列数据持久化到磁盘上；后者会将Generated Column持久化到磁盘上，而不是每次读取的时候计算所得。很明显，后者存放了可以通过已有数据计算而得的数据，需要更多的磁盘空间，与Virtual Column相比并没有优势，因此，MySQL 5.7中，不指定Generated Column的类型，默认是Virtual Column。

如果需要Stored Generated Golumn的话，可能在Virtual Generated Column上建立索引更加合适

MySQL默认的方式是使用Virtual Generated Column。

语法格式：

<type> [ GENERATED ALWAYS ] AS ( <expression> ) [ VIRTUAL|STORED ]

[ UNIQUE [KEY] ] [ [PRIMARY] KEY ] [ NOT NULL ] [ COMMENT <text> ]

有注意到我们很多健康上报的信息都是JSON对象，对于这种可以通过创建虚拟列提高查询效率。

例如提取JSON对象并创建查询索引：

ALTER TABLE `t_json` 
ADD COLUMN `name` varchar(20) GENERATED ALWAYS AS (json_unquote(json_extract(`json_json`,'$.name'))) Virtual NULL,
ADD INDEX `idx_v_name`(`name`) USING BTREE;

创建虚拟列的注意点：

1、衍生列的定义可以修改，但virtual和stored之间不能相互转换，必要时需要删除重建
2、虚拟列字段只读，不支持 INSRET 和 UPDATE。
3、只能引用本表的非 generated column 字段，不可以引用其它表的字段。
4、使用的表达式和操作符必须是不可变（Immutable）属性。
5、支持创建索引。
6、可以将已存在的普通列转化为stored类型的衍生列，但virtual类型不行；同样的，可以将stored类型的衍生列转化为普通列，但virtual类型的不行。
7、MySQL可以在衍生列上面创建索引。对于stored类型的衍生列，跟普通列创建索引无区别。
8、对于virtual类型的衍生列，创建索引时，会将衍生列值物化到索引键里，即把衍生列的值计算出来，然后存放在索引里。如果衍生列上的索引起到了覆盖索引的作用，那么衍生列的值将直接从覆盖索引里读取，而不再依据衍生定义去计算。

9、针对virtual类型的衍生列索引，在insert和update操作时会消耗额外的写负载，因为更新衍生列索引时需要将衍生列值计算出来，并物化到索引里。但即使这样，virtual类型也比stored类型的衍生列好，有索引就避免了每次读取数据行时都需要进行一次衍生计算，同时stored类型衍生列实际存储数据，使得聚簇索引更大更占空间。

10、virtual类型的衍生列索引使用 MVCC日志，避免在事务rollback或者purge操作时重新进行不必要的衍生计算。

2、分区表

为了保证MySQL的性能，我们都建议mysql单表不要太大，整体来说呢，建议是：单表小于2G，记录数小于1千万。如果字段数较少，那么记录数可以再偏大些，反之，可能记录数到百万级别就开始变慢了。

那么，业务量在增长，数据到瓶颈了怎么办呢，除了使用分布式数据库，我们也可以自行分库分表，或者利用mysql的分区功能实现。

分区的优势：

1、冷热分离：表非常大且只在表的最后部分有热点数据，冷数据根据分区规则自动归档。

2、定期淘汰历史数据：按时间写入，历史数据可淘汰，可快速删除，空间可快速回收。

3、优化查询：在where字句中包含分区列时，分区可以大大提高查询效率，减少缓存开销、减少IO开销。

4、统计性能提升：在涉及sum()和count()这类聚合函数的查询时，可以在每个分区上面并行处理，最终只需要汇总所有分区得到的结果。

MySQL的分区规则：

范围 ：PARTITIONED BY RANGE COLUMNS

列表 ：PARTITION BY LIST COLUMNS

HASH：PARTITION BY HASH

KEY ：PARTITION BY KEY

子分区：SUBPARTITION BY XXX

创建分区表：

CREATE TABLE test_year_p  (
id int(11) NOT NULL,
year_col int(4) NOT NULL,
PRIMARY KEY (id,year_col)
)
PARTITION BY RANGE (year_col)PARTITIONS 3(
PARTITION p0 VALUES LESS THAN (2021),
PARTITION p1 VALUES LESS THAN (2022), 
PARTITION p2 VALUES LESS THAN (2023)
);

改造现有表为分区表: 注意要先修改主键为原主键+分区字段

ALTER TABLE `t_test_list_p` PARTITION BY LIST (p_list)
PARTITIONS 3
(PARTITION `p0` VALUES IN (1) MAX_ROWS = 0 MIN_ROWS = 0 ,
PARTITION `p1` VALUES IN (2) MAX_ROWS = 0 MIN_ROWS = 0 ,
PARTITION `p2` VALUES IN (3) MAX_ROWS = 0 MIN_ROWS = 0 )
;

添加分区：

ALTER TABLE test_year_p ADD PARTITION (
PARTITION p3 VALUES LESS THAN (2024)
);

清除分区表数据

alter table t1 truncate partition p0;

删除分区

ALTER TABLE test_year_p DROP PARTITION p0;

3、MySQL8 降序索引和不可见索引

3.1 降序索引

在8.0 之前，能够以相反的顺序对索引进行扫描，但是会降低性能。降序索引能够实现相同的效果，且不会损耗性能。另外一个例子，当一个查询 SQL，需要按多个字段，以不同的顺序进行排序时，8.0 之前无法使用索引已排序的特性，因为 order by 的顺序与索引的顺序不一致，而使用降序索引，就能够指定联合索引中每一个字段的顺序，以适应 SQL 语句中的 order by 顺序，让 SQL 能够充分使用索引已排序的特性，提升 SQL 性能。

示例：表结构如下

CREATE TABLE t_test_index_desc (
c1 INT, c2 INT,
INDEX idx1 (c1 ASC, c2 ASC),
INDEX idx2 (c1 ASC, c2 DESC),
INDEX idx3 (c1 DESC, c2 ASC),
INDEX idx4 (c1 DESC, c2 DESC)
);

表中有两个字段，c1 和 c2，根据 c1，c2 的不同顺序的组合，创建了 4 个索引，由于索引字段的排序不同，因此对于不同的 order by 顺序，优化器可以使用的索引也不相同，如果 order by 顺序与索引字段的顺序一致，那么就可以避免额外的 filesort ，从而提升性能。

select * from t_test_index_desc order by c1 asc, c2 asc

ORDER BY c1 ASC, c2 ASC -- 优化器选择使用 idx1

ORDER BY c1 DESC, c2 DESC -- 优化器选择使用 idx4

ORDER BY c1 ASC, c2 DESC -- 优化器选择使用 idx2

ORDER BY c1 DESC, c2 ASC -- 优化器选择使用 idx3

原表创建降序可以使用如下语句:

alter table test_index add index idx_1(date_time desc)

3.2 不可见索引

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

以前要想彻底不可见，只能用开销较大的drop index；现在有了新的方式，可以改变索引的属性，让其不可见，这一操作只更改metadata，开销非常小。

使用场景

有可能使用隐藏索引的场景：

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

2. 业务只有一个可能每个月固定执行一次的SQL用到这个索引，那选择隐藏索引太合适不过了。

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

用法：

开启索引不可见

alter table test_index alter index idx_1 invisible;

开启索引可见

alter table test_index alter index idx_1 visible;

show create table 效果如下：

show create table test_index;

CREATE TABLE `test_index` (

`id` int NOT NULL AUTO_INCREMENT,

`name` varchar(20) COLLATE utf8mb4_general_ci DEFAULT NULL,

`id_card` varchar(18) COLLATE utf8mb4_general_ci DEFAULT NULL,

`date_time` datetime DEFAULT CURRENT_TIMESTAMP,

PRIMARY KEY (`id`),

KEY `idx_1` (`date_time` DESC) /*!80000 INVISIBLE */

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

CREATE TABLE `test_index` (

`id` int NOT NULL AUTO_INCREMENT,

`name` varchar(20) COLLATE utf8mb4_general_ci DEFAULT NULL,

`id_card` varchar(18) COLLATE utf8mb4_general_ci DEFAULT NULL,

`date_time` datetime DEFAULT CURRENT_TIMESTAMP,

PRIMARY KEY (`id`),

KEY `idx_1` (`date_time` DESC)

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci

多了/*!80000 INVISIBLE */ 语句

注：/*! */是mysql特有的语句，在其他类型库不会执行

4、MySQL8 窗口函数（分析函数）

MySQL从8.0开始支持窗口函数，这个功能在大多商业数据库和部分开源数据库中早已支持，有的也叫分析函数。

函数的基本用法： 函数名OVER 子句

over 关键字用来指定函数执行的窗口范围，若后面括号中什么都不写，则意味着窗口包含满足WHERE条件的所有行，窗口函数基于所有行进行计算；如果不为空，则支持以下4种语法来设置窗口。

1) window_name：给窗口指定一个别名。如果SQL中涉及的窗口较多，采用别名可以看起来更清晰易读；

2) PARTITION BY 子句：窗口按照哪些字段进行分组，窗口函数在不同的分组上分别执行；

3) ORDER BY子句：按照哪些字段进行排序，窗口函数将按照排序后的记录顺序进行编号；

4) FRAME子句：FRAME是当前分区的一个子集，子句用来定义子集的规则，通常用来作为滑动窗口使用

5) 按功能划分，可将MySQL支持的窗口函数分为如下几类：

演示一下常用的序号函数。

示例表：主键，学号，课程号，分数

create table score (
	id int(10) NOT NULL,
	sid int(10) NOT NULL,
	lid varchar(10) NOT NULL,
	score int(10) NOT NULL,
	PRIMARY KEY(id)
	);

INSERT INTO score(id,sid,lid,score) VALUES 
('1','1001','L01','100'),('2','1001','L02','98'),('3','1001','L03','99'),
('4','1001','L04','100'),('5','1001','L05','89'),('6','1002','L01','99'),
('7','1002','L02','90'),('8','1002','L03','89'),('9','1002','L04','87'),
('10','1002','L05','89'),('11','1003','L01','90'),('12','1003','L02','96'),
('13','1003','L03','79'),('14','1003','L04','89'),('15','1003','L05','96'),
('16','1004','L01','100'),('17','1004','L02','89'),('18','1004','L03','97'),
('19','1004','L04','79'),('20','1004','L05','85');

row_number() : 排序序号连续，不重复（相同分数的人序号递增）

SELECT	sid,	lid,	score,
	ROW_NUMBER () OVER (PARTITION BY sid ORDER BY score desc) AS srank
FROM
	score;

效果：

编辑

Rank() : 排序序号不连续，相同分数的人序号相同，但会占用序号位置，如 1、1、3 、4

SELECT	sid,	lid,	score,
	RANK () OVER (PARTITION BY sid ORDER BY score desc) AS srank
FROM
	score;

效果：

编辑

dense_rank() : 序号连续，且分数相同的人序号相同，如1、1 、2 、3

SELECT	sid,	lid,	score,
	dense_rank() OVER (PARTITION BY sid ORDER BY score desc) AS srank
FROM
	score;

效果：

编辑

其实，聚合函数也可以作为窗口函数

示例：

SELECT
	sid,
	lid,
	score,
	sum(score) over w as sumscore,
	avg(score) over w as avgscore,
	max(score) over w as maxscore,
	min(score) over w as minscore
FROM score 
	window w AS (	PARTITION BY sid ORDER BY score desc,lid asc );

效果如下：

编辑

其余用法，大家有兴趣可自行学习。

5、公用表表达式

MySQL 8支持公用表表达式，CTE(公用表表达式)是一个命名的临时结果集，仅在单个SQL语句的执行范围内存在。与派生表类似，CTE不作为对象存储，仅在查询执行期间持续。与派生表不同，CTE可以是自引用。此外，与派生表相比，CTE提供了更好的可读性和性能。CTE的结构包括:名称，可选列列表和定义CTE的查询。定义CTE后，可以像SELECT，INSERT，UPDATE，DELETE或视图一样使用

With as 语法：

WITH
cte1 AS (SELECT id, amount FROM t1),
cte2 AS (SELECT id, amount FROM t2)
SELECT cte1.amount, cte2.amount FROM cte1 JOIN cte2
WHERE cte1.id = cte2.id;

示例：偷懒直接拿第4条窗口函数的最后一个示例的sql包装。

with aa as(
SELECT
	sid,
	lid,
	score,
	sum(score) over w as sumscore,
	avg(score) over w as avgscore,
	max(score) over w as maxscore,
	min(score) over w as minscore
FROM score 
	window w AS (	PARTITION BY sid ORDER BY score desc ,lid asc ))
	select * from aa where aa.sid in (1001,1002);

效果：

编辑

一些复杂的sql，不需要写大量的union，join了完美继承了Oracle的with as。

递归CTE:

这是一种特殊的CTE，其子查询会引用自己的名字。WITH子句必须以WITH RECURSIVE开头。递归CTE子查询包括两部分：seed查询和recursive查询，由UNION[ALL]或UNION DISTINCT分隔。

注意：

不能在同一查询中两次引用派生表（子查询），因为那样的话，查询会根据派生表的引用次数计算两次或多次，这会引发严重的性能问题。使用CTE后，子查询只会计算一次。

递归最大次数有限制，mysql8默认限制为1000，超过即报错。

递归查询示例：

测试表：

create table employees_mgr ( 
id int primary key not null,
name varchar(100) not null,
manager_id int null,
index (manager_id),
foreign key (manager_id) references employees_mgr (id)
);

插入测试数据：

insert into employees_mgr values 
(1, "zhangsan", null), 
(2, "lisi", 1), 
(3, "wangwu", 1),
(4, "chenliu", 2),
(5, "sunqi", 4),
(6, "zhaoba", 4),
(7, "liujiu", 3);

示例Sql:

with recursive employee_paths ( id, name, path ) as (
	select id, name, cast(	id as char( 200 )) 
	from employees_mgr 
	where	manager_id is null
	union all
	select e.id, e.name, concat( ep.path, '<--', e.id ) 
	from employee_paths as ep
	join employees_mgr as e on ep.id = e.manager_id 
	) 
select * from employee_paths;

效果：

编辑

如上图：递归查询生成的每一行，会查找直接向前一行生成的员工做汇报的所有员工。对于每个员工，该行的信息包括员工ID、 姓名和员工管理链，该链是在最后添加了员工ID的管理链。

窗口函数和CTE(公用表表达式)的增加，简化了SQL代码的编写和逻辑的实现，新特性的增加，可以用更优雅和可读性的方式来写SQL。不过这都是在MySQL8.0中实现的新功能，在MySQL8.0之前，只能按照较为复杂的方式实现。

6、快速加列algorithm=instant

Instant由腾讯的DBA团队贡献的，MySQL在8.0.12版本引入。

使用场景：

ALGORITHM=INSTANT 目前对6种ddl有效：添加列， 添加或删除virtual 列， 添加或删除列默认值，修改 ENUM 定义， 修改索引类型， 重命名表。

ALTER TABLE `test`.`book` ADD COLUMN `gg` varchar(255) NULL,algorithm=instant;

ALTER TABLE `test`.`book` DROP COLUMN `gg`,algorithm=INPLACE;

ALTER TABLE book RENAME book1,algorithm=INPLACE;

ALTER TABLE `test`.`book` MODIFY COLUMN `mj` enum('one','two','three','4') CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL AFTER `gg`;

ALTER TABLE book RENAME book1,algorithm=INPLACE;

一些限制：

Instant Add Column只能将新字段添加到表的尾巴上，不能添加到中间；不支持压缩表，即该表行格式不能是 COMPRESSED；不支持包含全文索引的表；不支持临时表；不支持在数据字典表空间中创建的表。

7、mysql8的一些坑。

7.1 密码规则

mysql8数据库服务器中指定需要使用sha2这种算法进行加密，但是大多数客户端都不支持。因为sha1加密算法被破解了，所以mysql8之后都升级了成了sha2算法。mysq8默认的认证插件是cacheing_sha2_password插件，原来的版本使用的是mysql_native_password插件，这使得原来的mysql连接客户端出现认证失败的问题，所以有两种方式可以解决问题：

1. 将服务端的加密方式修改为sha1，如下：

ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '你的密码';

2.强客户端的加密方式修改为sha2

将客户端的加密方式修改为sha2，例如将mysql-connector-java更换为最新的版本，如果使用一些图形化连接工具，那么可以通过修改配置信息解决。

7.2 时区问题

往数据库插入datetime类型的时间发现，数据库里存储的比实际晚13个小时，而服务器的时间是东八区没问题。解决方法：连接数据库url中加上serverTimezone=GMT%2b8，一般如下配置：

&serverTimezone=GMT%2B8&useSSL=false

7.3 驱动问题

Mysql的数据库驱动com.mysql.jdbc.Driver已经在8被弃用了，应当使用新的驱动com.mysql.cj.jdbc.Driver

7.4 MySQL8的表名规则

mysql8 不支持后修改lower-case-table-names=1，所以必须在初始化数据库的时候加上

lower_case_table_names 的值：

如果设置为0，表名将按指定方式存储，并且在对比表名时区分大小写；

如果设置为1，表名将以小写形式存储在磁盘上，在对比表名时不区分大小写；

如果设置为2，则表名按给定格式存储，但以小写形式进行比较；

此选项还适用于数据库名称和表别名。