mysql8.0新特性

新特性

优化器索引

• 隐藏索引
  • 创建隐藏索引
    • 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日志中，同时在每次检查点将期写入引擎私有系统表中