亚洲在线久爱草,狠狠天天香蕉网,天天搞日日干久草,伊人亚洲日本欧美

認證插件更新

用戶創建和授權

賬戶與安全 優化器索引 json增強

#使用遞歸CTE生成斐波那契數列:0,1,1,2,3,5,8,...

#1.限定最大值

with recursive cte(m, n) as (

select 0, 1

union all

select n, m+n from cte where n<100

) select m from cte;

#2.限定位數

with recursive cte(id, m, n) as (

select 0, 0, 1

union all

select id+1, n, m+n from cte where id<10

) select m from cte;

測試表

create table t3(c1 varchar(10), c2 varchar(10));

create index idx on t3(c1);創建普通索引

create index fun_idx on t3( (UPPER(c2)) );創建函數索引語法，如c2轉換為大寫的結果作為索引

show index from t3\G

explain select * from t3 where upper(c2) = 'ABC'; 如果沒設置函數索引就是where全表查；反之可以走索引，如用戶名查詢

json建索引 直接建 json會超長，所以 index((CAST(data->>'$.name' as char(30))))

一、賬戶與安全

? ? 1、強制分開用戶創建和用戶授權

? ? #、prompt 字符：可以更改mysql命令提示符

二、認證插件更新

? ? 1、之前版本是mysql_native_password，8.0版本是caching_sha2_password

???? ??? mysql> show variables like '%default%';
???? ??? +---------------------------------+-----------------------+
???? ??? | Variable_name | Value |
???? ??? +---------------------------------+-----------------------+
???? ??? | default_authentication_plugin |?caching_sha2_password?|

? ? 2、由于認證插件的更新，客戶端需要升級才可以連接到8.0，或者服務器修改用戶認證插件：

???? ? ? alter user?root@'%'?identified with mysql_native_password by '123';

三、密碼管理

? ? 1、8.0版本開始限制重復使用以前的密碼

???? ? ? password_history=3 ????? ? ?--不允許和最近三次密碼一樣

???? ? ? password_reuse_interval=90 --不允許和最近90天內的密碼一樣

???? ? ? password_require_current=ON --修改密碼需要提供當前密碼

???? ? ? 語句：

???? ????? ? ?alter user?user@host?identified by 'new_password' replace 'cur_password';

? ? 2、這三個變量可以全局設置或者利用alter user?user@host?identified by '123' password_history 針對用戶設置

? ? 3、歷史密碼都是保存在mysql.password_history

? ? #、set persist var=value? --對變量持久化修改

????原理：將persist設置的變量寫入到數據目錄下的配置文件中（json格式），服務器啟動時也會讀取該配置文件

四、角色管理

? ? 1、8.0版本提供了角色管理功能，角色是一組權限的集合，即把一組權限放在一起并起一個名字，就成為了一個角色

? ? 2、角色分配步驟

???? ? ? 創建角色

???? ???? ????? ? ?create roll 'new_role'；? ? ?--創建了一個用戶

???? ? ? 給角色分配權限

???? ???? ????? ? ?grant insert,select on test.* to 'new_role';

???? ? ? 給用戶指定角色

???? ???? ????? ? ?grant [default] 'new_role' to 'user'@host; --不使用default的話，默認登錄后需要用set role激活角色，使用default后就已經激活

???? ? ? ?指定用戶

???? ???? ????? ? ?set role 'new_role';

? ? 3、查看用戶權限

???? ? ? show grant for 'user'@host using 'new_role';

? ? 4、顯式當前用戶使用的角色

???? ? ? select current_role();

五、優化器索引

? ? 1、隱藏索引（invisible index）

???? ????? ? ?不會被優化器使用，但是仍然需要進行維護

???? ????? ? ?create index index_name on tab(col) invisible;

? ? ??? #、如果想設置優化器對隱藏索引可見，可以修改optimizer_switch中的use_invisible_index=on;（可以會話級別修改）

???? ? ?#、修改索引可見性

???? ????? ? ?alter table tab alter index index_name visible;

???? ? ?#、主鍵不能設置為不可見

???? ? ? 使用場景：

???? ???? ?????軟刪除? ? ?--刪除一個索引，并不用真的刪除，將其設置為隱藏索引即可

? ? 2、降序索引

???? ? ? 8.0版本中只有innodb的Btree支持降序索引

???? ? ? 8.0版本中不再對group by操作進行隱式排序

? ? 3、函數索引

???? ? ? ?支持在索引中使用函數（表達式）的值

???? ? ? ?支持降序索引，支持json數據的索引

???? ? ? ?函數索引基于虛擬列功能實現

? ? 虛擬列建立：

? ? ? ? ?alter table tab add c3 varchar(30) generated always as (upper(c2));

? ? 創建：

???? ? ? create index index_name on tab(? (upper(c2))? )

六、通用表表達式（CTE）

? ? 1、即，with子句：

???? ? ? with cte_name as （select * from tab）

???? ? ? ?高級用法：

???? ? ? ????? ? ?with etc1(col1) as(select co1 from tab1 ),

???? ???? ???? ????? ? ?with etc2(col2) as(select co1*2 from etc1)

? ? 2、遞歸cte

???? ? ? with recursive cte_name(n) as(

???? ???? ? select 1

???? ??? ??? union all

???? ????? ? ?select n+1 from cte where n<5

???? ??? ??? )

???? ? ? ?select * from ct;

? ? 例：

???? ? ??mysql> with recursive etc(n) as( select 1 union select n*(n+1) from etc where n<=5) select * from etc;
???? ??? +------+
???? ??? | n |
???? ??? +------+
???? ??? | 1 |
???? ??? | 2 |
???? ??? | 6 |
???? ??? +------+

???? mysql> with recursive cte(id,name,path) as ( select id ,name ,cast(id as char(200)) from t where boss is null union all select t.id ,t.name ,concat(ep.id,',',t.id) from cte as ep join t on ep.id=t.boss ) select * from cte;
???? ??? +------+------+------+
???? ??? | id | name | path |
???? ??? +------+------+------+
???? ??? | 6 | f | 6 |
???? ??? | 3 | c | 6,3 |
???? ??? | 1 | a | 3,1 |
???? ??? | 4 | d | 1,4 |
? ? ? ? ?| 5 | e | 4,5 |
???? ??? | 2 | b | 5,2 |
???? ??? +------+------+------+

? ? 3、遞歸限制

???? ? ? 正常操作應該在cte中設置一個停止條件，否則將會陷入死循環，但是mysql為了避免死循環的發生，對遞歸深度有了限制，cte_max_recursion_depth、max_execution_time

? ? 例1：階乘

???? ????MySQL [test]> with recursive cte as( select 1 x,2 y union all select x*y,y+1 from cte where x<200 ) select * from cte;
???? ??? ??? +------+------+
???? ??? ? ? ?|x | y |
???? ??? ??? +------+------+
???? ??? ??? | 1 | 2 |
???? ??? ??? | 2 | 3 |
???? ??? ??? | 6 | 4 |
???? ??? ??? | 24 | 5 |
???? ??? ??? | 120 | 6 |
???? ??? ??? | 720 | 7 |
???? ??? ??? +------+------+

? ? 例2：斐波那契數列

???? ????MySQL [test]> with recursive cte as(select 1 x,1 y union all select y x,x+y y from cte where cte.x<20) select * from cte;
???? ??? +------+------+
???? ??? | x | y |
???? ??? +------+------+
???? ??? | 1 | 1 |
???? ??? | 1 | 2 |
???? ??? | 2 | 3 |
???? ??? | 3 | 5 |
???? ??? | 5 | 8 |
???? ??? | 8 | 13 |
???? ??? | 13 | 21 |
???? ??? | 21 | 34 |
???? ??? +------+------+

七、每門課程的第一名

???? ????MySQL [test]> select * from score a where? (select distinct count(*) num from score b where? a.course=b.course and a.id!=b.id and b.score>a.score) = 0;?? ? ---0為第一，1為第二，2為第三

八、窗口函數

? ? 1、聚合函數都可以用窗口函數改寫，如：

???? ???? ????mysql> select *,avg(score)over(partition by stuid) from score;

? ? 2、專用窗口函數

???? ? ? ?ROW_NUMBER()/RANK()/DENSE_RANK()/PERCENT_RANK()? ? ?--獲取排名

???? ? ? ?FIRST_VALUE()/LAST_VALUE/LEAD()/LAG()? ? ?

???? ?????CUME_DIST()/NTH_VALUE()/NTILE()

? ? 3、ROW_NUMBER():分組后內部編號，編號至于前后位置相關，與內容無關

? ? 4、RANK():分組后內部編號，排序后編號，如果值一樣則編號一樣

? ? 5、窗口定義

???? ? ? window_function(expr)

???? ? ? ?OVER(PARTITION BY ...

???? ??? ??? ??? ??? ?ORDER BY ...

???? ??? ??? ??? ??? ? frame_clause...)

???? ? ? ?CURRENT ROW :當前處理的行

???? ? ? ?M PRECEDING? ? ?:當前處理行第前M行

???? ?????N? FOLLOWING? ?:當前處理行第后N行

???? ? ? ?UNBOUNDED PRECEDING? ? ?:分組內部最前沿

???? ? ? ?UNBOUNDED FOLLOWING? ? ?:分組內最下沿

? ? 6、高級定義（可以省去多次寫窗口內容）

???? ? ? ?window_function1(expr)

???? ? ? ? OVER w as 'col1',

?????? ? ?window_function2(expr)

?????? ? ?OVER w as 'col2'

?????? ? ?FROM table

?????? ? ?WINDOW w AS(PARTITION BY col ORDER BY col ROW 1 PRECEDING AND 1 FOLLOWING);

九、集成數據字典

?????? ? ?1、刪除了之前版本的元數據文件，如opt、frm文件，對innodb只剩余了ibd文件

??????????2、系統表（mysql）和數據字典全部改為innodb存儲引擎

?????? ? ?3、支持ddl原子性

?????? ? ?4、簡化了information_schema的實現，提高了訪問性能

?????? ? ?5、提供了序列化字典信息（SDI）的支持，以及ibd2sdi工具

?????? ? ?6、innodb_read_only影響所有存儲引擎，因為數據字典是innodb存儲引擎的；對普通用戶數據字典是不可見，無法查詢和修改

?????? ? 7、自增列持久化，將自增列計數器的最大值寫入redo log，同時在每次檢查點將其寫入引擎私有的系統表，會感知到每次對自增列中值的修改

?????? ? ?8、死鎖檢查控制

??????????????mysql> show variables like '%innodb_deadlock%';
???????????????? +------------------------+-------+
???????????????? | Variable_name | Value |
???????????? ???? +------------------------+-------+
???????????????? | innodb_deadlock_detect | ON |
???????????????? +------------------------+-------+

?????????? ? ?死鎖檢測會有性能消耗，在高并發場景下可以考慮關閉死鎖檢測，以提高系統性能

?????? ? ?9、鎖定語句新增選項（僅針對行鎖起作用）

?????????? ? ?select ... for share/select for update [NOWAIT|SKIP LOCKED]

?????????? ? ?NOWAIT:如果請求的行被其它事務鎖定，語句立即返回錯誤信息

?????????? ? ?SKIP LOCKED,從返回的結果集中移除被鎖定的行，只返回未被鎖定的行

?????? ? ?10、支持部分快速DDL，ALTER TABLE ... ALGORITHM=INSTANT;

?????? ? ?11、新增靜態變量innodb_dedicated_server：可以自動配置innodb_buffer_pool_size/innodb_log_buffer_size等

?????? ? ?12、默認創建兩個undo表空間，不再使用共享表空間

十、json內聯路徑操作符

? ? 1、column>>path? = json_unquote(column->path)

十一、json聚合函數

? ? 1、json_arrayagg(),用于生產json數組，將多行數據組合成json數組

? ? 2、json_objectagg(),用于生成json對象，對于同一屬性的對個值，選取最后一個值

十二、json使用函數

? ? 1、json_pretty()：輸出json內容時，進行格式化

? ? 2、json_storage_size()：返回json數據占用的存儲空間

十三、json合并函數

? ? 1、json_merge_patch()：用于將兩個json對象合并為一個對象，如果有相同節點，則第二個中的覆蓋第一個json中的節點

? ? 2、json_merge_preserv()：用于將兩個json對象合并為一個對象，如果有相同節點，都會保留并將值合并為數組

十四、json表函數

? ? 1、json_table()：將json數據轉換為關系表

8.0 新增角色管理功能（可先將權限賦給角色，再將角色賦給用戶）

create role 'xxx_role'; //角色在mysql.user中創建一個沒有密碼的用戶

grant XXX,XXX,XXX on XXXdb.* to 'xxx_role';

grant 'xxx_role' to 'user1';

show grants for 'user1';

show grants for 'user1' using 'xxx_role';

set default role 'xxx_role' to 'user1'; // 修改用戶使用的默認角色，否則用戶登錄后默認角色為NONE，用戶有多個角色需要默認啟用，使用set default role all to 'user1';

select * from mysql.default_roles;

revoke XXX,XXX,XXX on XXXdb.* from 'xxx_role'; //回收角色權限

MySQL5.7自增列bug

原子DDL操作

移動平均值

生成利潤累計和

角色是一組權限的集合

隱藏索引不會被優化器使用，但仍然需要進行維護。

應用場景：軟刪除、灰度發布。