新特性
优化器索引
- 隐藏索引
- 创建隐藏索引
create index idx_j on t1(j) invisible;
- 测试
select @@optimizer_switch;
set session optimizer_switch="use_invisible_indexes=on"
explain select * from t1 where j=1;
- 正式使用
alter table t1 alter index idx_j visible
// 设置可见alter table t1 alter index idx_j invisible
// 设置不可见
- 创建隐藏索引
- 降序索引
create table t2(c1 int,c2 int,index idx_1(c1 asc,c2 desc));
- group by 不再使用隐式排序,语句需要指定order by
- 函数索引
- 创建
create index func_idx_c2 on t3((UPPER(c2)));
select * from t3 where upper(c2) = 'ABC';
- json索引
create table emp(data json,index((CAST(data->>'$.name' as char(30)))));
select * from emp where CAST(data->>'$.name' as char(30))='abc';
- 计算列
alter table t3 add column c3 varchar(30) generated always as (upper(c1));
- 创建
窗口函数(分析函数)
- 传统
select country,sum(profit) as country_profit from sales group by country order by country;
- 改进
select year,country,product,profit
sum(profile) OVER (partition by country) as country_profit
from sales
order by country,year,product,profit;
- 专用窗口函数
- ROW_NUMBER / RANK()/ DESEN_RANK() /PERCENT_RANK()
- FIRST_VALUES() LAST_VALUES() LEAD() LAG()
- CUME_DIST()
窗口函数定义
select year,country,product,profilt,
sum(profile) over (partition by country order by profit rows unbounded preceding) as running_total
from sales
order by country,profile;
select year,country,product,profit,
first_value(profilt) over w as 'first',
last_values(profit) over w as 'last'
from sales
WINDOW w as (partition by country order by profit rows unbounded preceding) order by country,profit;
原子DDL语句
drop table if exists t1,t2;
drop tablet1,t2;
// 如果t1表或T2表不存在,则抛出异常,表依然存在
自增列持久化
- MYSQL5.7 InnoDB自增计数器的值只存储在内存中
- MYSQL8.0 将自增计数器写入redo日志中,同时在每次检查点将期写入引擎私有系统表中