Mysql之案例分析(一)

肆玖爺發表於2021-04-07

可見性分析

CREATE TABLE `t` (
`id` int(11) NOT NULL,
`k` int(11) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB;

insert into t(id, k) values(1,1),(2,2);

img

  • A:1
  • B:3

資料修改的詭異現象

begin;

select * from t;
+--------+----+
| id | c |
+--------+----+
| 1  | 1 |
| 2  | 2 |
| 3  | 3 |
| 4  | 4 |
+--------+----+

update t set c=0 where id=c;

select * from t;
+--------+----+
| id | c |
+--------+----+
| 1  | 1 |
| 2  | 2 |
| 3  | 3 |
| 4  | 4 |
+--------+----+

上文中update無法修改的問題,為什麼會產生這種情況?

  • 場景1:update之前,另一個事務B中執行update t set c=c+1
    • update是當前讀,可以讀取最新的資料,id不等於c,更新失敗
    • select是快照讀,事務B是處於高水位之後紅色部分,對於select的事務不可見
  • 場景2:第一次select前啟動事務B,update前事務B執行update t set c=c+1,且提交
    • update是當前讀,可以讀取最新的資料,id不等於c,更新失敗
    • select是快照讀,事務B對於當前事務是活躍的,處於黃色部分,不可見

索引場景分析

CREATE TABLE `geek` (
`a` int(11) NOT NULL,
`b` int(11) NOT NULL,
`c` int(11) NOT NULL,
`d` int(11) NOT NULL,
PRIMARY KEY (`a`,`b`),
KEY `c` (`c`),
KEY `ca` (`c`,`a`),
KEY `cb` (`c`,`b`)
) ENGINE=InnoDB;

select * from geek where c=N order by a limit 1;
select * from geek where c=N order by b limit 1;

非主鍵索引的葉子節點上會掛著主鍵,因此:

  • 索引c+主鍵索引,可以看做是c、a、b
  • 索引ca+主鍵索引,可以看做是c、a、b,重疊部分合並

由上可以得出,索引c可以等價於ca,保留較小的索引,去除索引ca

重建索引

-- 非主鍵索引重建
alter table T drop index k;
alter table T add index(k);

-- 主鍵索引重建方式1
alter table T drop primary key;
1075 - Incorrect table definition; there can be only one auto column and it must be defined as a key
alter table T add primary key(id);

-- 主鍵索引重建方式2
alter table T engine=InnoDB;
  • 索引重建:碎片整理可通過索引重建進行
  • 主鍵索引:
    • InnoDB必須有一個主鍵索引,未主動宣告時,Mysql會預設給建立一列6位元組的整數列
    • 自增只能定義在索引列上,因此直接刪除自增列上索引異常:1075
    • 主鍵索引重建方式1中刪除並重建的方式,其實相當於建立了兩次索引,建議採用方式2

大批量刪除資料

-- 第一種,直接執行 
delete from T limit 10000;

-- 第二種,在一個連線中迴圈執行 20 次 
delete from T limit 500;

-- 第三種,在 20 個連線中同時執行 
delete from T limit 500
  • 第一種:長事務,索引時間較長,且可能導致主從延遲
  • 第三種:人為造成鎖衝突

IS NULL、IS NOT NULL是否走索引

mysql> show index from t;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| t     |          0 | PRIMARY  |            1 | id          | A         |       93536 | NULL     | NULL   |      | BTREE      |         |               |
| t     |          1 | a        |            1 | a           | A         |       93536 | NULL     | NULL   | YES  | BTREE      |         |               |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

mysql> explain select * from t where a is null;
+----+-------------+-------+------+---------------+-----+---------+-------+------+-----------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref   | rows | Extra                 |
+----+-------------+-------+------+---------------+-----+---------+-------+------+-----------------------+
|  1 | SIMPLE      | t     | ref  | a             | a   | 5       | const |    1 | Using index condition |
+----+-------------+-------+------+---------------+-----+---------+-------+------+-----------------------+
1 row in set

mysql> explain select * from t where a is not null;
+----+-------------+-------+------+---------------+------+---------+------+-------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows  | Extra       |
+----+-------------+-------+------+---------------+------+---------+------+-------+-------------+
|  1 | SIMPLE      | t     | ALL  | a             | NULL | NULL    | NULL | 93536 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+-------+-------------+
1 row in set

is null使用了索引,is not null未使用索引。那麼,是否可以得出結論:is null走索引,is not null不走索引呢?

img

對於二級索引來說,索引列的值可能為NULL,對於索引列值為NULL的二級索引記錄來說,它們被放在B+樹的最左邊。由此,可以看出SQL中的NULL值認為是列中最小的值。因此,is null使用了索引,is not null由於需要查詢所有值,最終還需要回表到主鍵索引,因此,直接使用全部掃描。

上述現象的本質還是優化器對索引成本的估算,如果上述案例中a is NULL的數量達到一定的程度,回表成本增加,可能就會被優化器放棄,改走全部掃描。

同理,!=、not in是否走索引,都是同樣的原理

select count()

在不同的 MySQL 引擎中,count(*) 有不同的實現方式。

  • MyISAM 引擎:表的總行數存在磁碟上,沒有where條件的情況下,會直接返回這個數,效率很高;
  • InnoDB 引擎:由於MVCC,不同事務中返回多少行是不確定的,需要把資料一行一行地從引擎裡面讀出來,然後累積計數。因此,優化器會找到最小的索引樹來遍歷

不同count的用法對比:

  • count(1):InnoDB 引擎遍歷整張表,但不取值。server 層對於返回的每一行,放一個數字“1”進去,判斷是不可能為空的,按行累加
  • count(*):MySQL專門進行了優化,不取值,等價於count(1),建議優先使用
  • count(主鍵id):InnoDB 引擎會遍歷整張表,把每一行的 id 值都取出來,返回給 server 層。server 層拿到 id 後,判斷是不可能為空的,就按行累加
  • count(欄位):
    • 如果這個“欄位”是定義為 not null 的話,一行行地從記錄裡面讀出這個欄位,判斷不能為 null,按行累加;
    • 如果這個“欄位”定義允許為 null,那麼執行的時候,判斷到有可能是 null,還要把值取出來再判斷一下,不是 null 才累加

order by工作方式

CREATE TABLE `t` (
	`id` INT (11) NOT NULL,
	`city` VARCHAR (16) NOT NULL,
	`name` VARCHAR (16) NOT NULL,
	`age` INT (11) NOT NULL,
	`addr` VARCHAR (128) DEFAULT NULL,
	PRIMARY KEY (`id`),
	KEY `city` (`city`)
) ENGINE = INNODB;

select city,name,age from t where city='杭州' order by name limit 1000;

--  MySQL中用於控制排序行資料長度的一個引數,如果單行的長度超過這個值,改用rowid排序
SET max_length_for_sort_data = 16;
全欄位排序 rowid 排序
img img
  • sort_buffer_size:如果要排序的資料量小於 sort_buffer_size,排序就在記憶體中完成。反之,利用磁碟臨時檔案輔助排序
  • rowid 排序多訪問了一次表 t 的主鍵索引,因此,MySQL會優先選擇全欄位排序,可以通過修改引數max_length_for_sort_data讓優化器選擇rowid排序演算法,預設16,當要查詢的單條資料全文字長度大於16採用rowid排序
  • 對於需要使用臨時表進行排序時,需要看臨時表是記憶體臨時表,還是磁碟臨時表,由tmp_table_size決定,預設16M。若是記憶體臨時表,回表在記憶體中完成,不會訪問磁碟,優先選用rowid排序

優化方案:使資料本身有序

alter table t add index city_user(city, name);

-- 利用索引中相同city下name有序性
select city,name,age from t where city='杭州' order by name limit 1000;

-- 進一步優化,使用覆蓋索引,減少回表
alter table t add index city_user_age(city, name, age);

-- city多值情況下,又該如何處理? sql拆分
select * from t where city in ('杭州'," 蘇州 ") order by name limit 100;

group by優化

CREATE TABLE t1 (
	id INT PRIMARY KEY,
	a INT,
	b INT,
	INDEX (a)
);

select id%10 as m,count(*) as c from t2 group by m;

首先分析下group by語句的執行計劃,如下:

-- 此處使用MySQL 8.0+,已取消group by隱式排序,否則Exta中還會多一個Using filesort
mysql> explain select id%10 as m,count(*) from t group by m;
+----+-------------+-------+------------+-------+---------------+-----+---------+------+--------+----------+------------------------------+
| id | select_type | table | partitions | type  | possible_keys | key | key_len | ref  | rows   | filtered | Extra                        |
+----+-------------+-------+------------+-------+---------------+-----+---------+------+--------+----------+------------------------------+
|  1 | SIMPLE      | t2    | NULL       | index | PRIMARY,a     | a   | 5       | NULL | 998529 |      100 | Using index; Using temporary |
+----+-------------+-------+------------+-------+---------------+-----+---------+------+--------+----------+------------------------------+
img
  • 只用到了主鍵id欄位,可以使用覆蓋索引,因此選擇了索引a,不用回表

  • 獲取主鍵id,id%10後放入臨時表,如果存在,計數列加1

  • MySQL 8.0前group by支援隱式排序,無排序需求時,建議加上order by null

如何優化?

  • 適合建立索引,直接加索引

    -- 此處舉例中分組欄位是不存在,新增一個,並建立索引
    -- 實際場景中可能會有已有分組欄位,但未加索引,加上索引即可
    mysql> alter table t1 add column z int generated always as(id % 100), add index(z);
    
    -- 使用索引欄位進行分組排序
    mysql> explain select z as m,count(*) from t1 group by z ;
    +----+-------------+-------+------------+-------+---------------+-----+---------+------+------+----------+-------------+
    | id | select_type | table | partitions | type  | possible_keys | key | key_len | ref  | rows | filtered | Extra       |
    +----+-------------+-------+------------+-------+---------------+-----+---------+------+------+----------+-------------+
    |  1 | SIMPLE      | t1    | NULL       | index | z             | z   | 5       | NULL | 1000 |      100 | Using index |
    +----+-------------+-------+------------+-------+---------------+-----+---------+------+------+----------+-------------+
    
    • 索引是有序的,順序掃描,依次累加,統計完一個再統計下一個,不需要暫存中間結果,也不需要額外排序。如果需要倒序排列,Backward index scan,從後掃描索引即可

    • 多個分組欄位,建議使用聯合索引

  • 不適合建立索引,資料量不大,走記憶體臨時表即可。如果資料量較大,使用SQL_BIG_RESULT告訴優化器,放棄記憶體臨時表,直接磁碟臨時表

    mysql> explain select SQL_BIG_RESULT id%10 as m,count(*) from t1 group by m ;
    +----+-------------+-------+------------+-------+---------------+-----+---------+------+------+----------+-----------------------------+
    | id | select_type | table | partitions | type  | possible_keys | key | key_len | ref  | rows | filtered | Extra                       |
    +----+-------------+-------+------------+-------+---------------+-----+---------+------+------+----------+-----------------------------+
    |  1 | SIMPLE      | t1    | NULL       | index | PRIMARY,a,z   | a   | 5       | NULL | 1000 |      100 | Using index; Using filesort |
    +----+-------------+-------+------------+-------+---------------+-----+---------+------+------+----------+-----------------------------+
    

    通過執行計劃可以看出實際並未使用臨時表,為什麼呢?

    因此,磁碟臨時表是B+樹儲存,儲存效率不高,從磁碟空間考慮,直接使用陣列儲存,流程如下:

    img

    直接把分組值m放在sort_buffer中,空間不足使用磁碟臨時檔案輔助排序,這樣就得到一個有序陣列。在有序陣列上計算相同值出現的次數就比較簡單了,和在索引上統計計數一樣,逐個累加計數即可。

慢查詢分析

  • 示例1:
session A session B
start transaction with consistent snapshot;
update t set c=c+1 where id=1;//執行100萬次
select * from t where id=1;
select * from t where id=1 lock in share mode;
img
  • 示例2:
-- 建立表t
CREATE TABLE `t` (
	`id` INT (11) NOT NULL,
	`b` VARCHAR (10) NOT NULL,
	PRIMARY KEY (`id`),
	KEY `b` (`b`)
) ENGINE = INNODB;

-- 值超出欄位長度,字串截斷後傳遞給執行引擎,可能匹配上大量資料,最終導致大量回表二次驗證b='1234567890abcd'
explain select * from t where b='1234567890abcd';

-- 型別隱式轉換,掃描全部索引樹
explain select * from t where b=1235

互關問題設計

業務上有這樣的需求,A、B 兩個使用者,如果互相關注,則成為好友。

-- 建立關注表
CREATE TABLE `like` (
	`id` INT (11) NOT NULL AUTO_INCREMENT,
	`user_id` INT (11) NOT NULL,
	`liker_id` INT (11) NOT NULL,
	PRIMARY KEY (`id`),
	UNIQUE KEY `uk_user_id_liker_id` (`user_id`, `liker_id`)
) ENGINE = INNODB;

-- 建立好友表
CREATE TABLE `friend` (
	`id` INT (11) NOT NULL AUTO_INCREMENT,
	`friend_1_id` INT (11) NOT NULL,
	`friend_2_id` INT (11) NOT NULL,
	PRIMARY KEY (`id`),
	UNIQUE KEY `uk_friend` (
		`friend_1_id`,
		`friend_2_id`
	)
) ENGINE = INNODB;
session1(A關注B,A=1,B=2) session2(B關注A,A=1,B=2)
begin;
select * from user_like where user_id=2 and liker_id=1;(Empty set) begin;
insert into user_like(user_id,liker_id) values(1,2);
select * from user_like where user_id=1 and liker_id=2;(Empty set)
insert into user_like(user_id,liker_id) values(2,1);
commit;
commit;

A、B兩個使用者同時關注對方,即使session2中select先於session1中insert操作,session2也無法感知其未提交的資料。從而兩個session執行完後建立了雙向關注,但未建立好友關係。如何解決?

方案1:按照規則,使AB互關對映到同一條資料上,通過行鎖衝突+on duplicate key實現好友關係的建立

-- 增加互關關係欄位
ALTER TABLE `user_like`
ADD COLUMN `relation_ship`  int NOT NULL AFTER `liker_id`;

-- 按照使用者編號正序排列,不關A關注B,還是B關注A,都會命中同一條資料,用relation_ship標識兩者之間的關係

-- A關注B,若A=1、B=2
insert into user_like(user_id,liker_id,relation_ship) values(1,2,1) on duplicate key update relation_ship = relation_ship|1;

-- A關注B,若A=2、B=1
insert into user_like(user_id,liker_id,relation_ship) values(1,2,2) on duplicate key update relation_ship = relation_ship|2;

-- 查詢AB之前的關係
select relation_ship from user_like where user_id=1 and liker_id=2;

-- 以上兩條insert執行後,上一步查詢的relation_ship=1|2=3,可執行好友插入
insert ignore into user_friend(friend_1_id, friend_2_id) values(1,2);
  • on duplicate key需要建立在主鍵或者唯一鍵的基礎上
  • insert ignore可保證好友插入的冪等性
  • 好處在於兩條資料記錄了關注和好友關係
  • 壞處在於不便於查詢場景實現(可在異構資料來源上進行查詢)
    • 查詢場景複雜化,例如:查詢使用者A關注的使用者,(user_id=A and relation_ship<>2) or (liker_id=A and relation_ship=3)
    • 現有索引無法滿足查詢場景
    • 分表的情況下,無法對映指定使用者到單一的表上,例如:查詢使用者A關注的使用者

方案2:新的事務中或者非同步呼叫好友關係建立服務

begin;

-- 驗證雙向關係是否存在,即存在兩條資料
select couny(*) from user_like where user_id in (1,2) and liker_id in (1,2);

-- 雙向關係存在,插入兩條雙向好友關係
insert ignore into user_friend (friend_1_id,friend_2_id)  select 
user_id,liker_id from user_like where user_id in (1,2) and liker_id in (1,2);
  • 好處在於關注和好友關係明確,查詢實現簡單
  • 壞處在於資料儲存量翻倍,且關注和友好的建立不在同一個事務中,好友的建立有可能失敗,需要提供補償機制

更新中當前讀問題

CREATE TABLE `t` (
	`id` INT (11) NOT NULL,
	`a` INT (11) NOT NULL,
	PRIMARY KEY (`id`)
) ENGINE = INNODB;

insert into t values(1,2);
session A session B
begin;
select * from t where id=1;
update t set a=3 where id=1;
update t set a=3 where id=1;
select * from t where id=1;
update t set a=4 where id=1;
select * from t where id=1;

session A中後兩次select返回結果是什麼?

有疑問的在於第二次,由於session B中已經把a修改為了3,session A中update是當前讀,就看是否可以感知a已變更為3。MySQL 8.0.11中已感知不會執行修改操作,第二次讀取的快照讀還是(1,2)。有說法是update中當前讀讀取的只是where條件中的列,無法感知a是否變更,執行了修改操作,第二次讀取結果為(1,3)

隨機顯示N條資料

  • 方案1:隨機函式排序

    -- 不建議採用:排序耗費資源
    select * from t order by rand() limit n;
    
  • 方案2:隨機主鍵

    -- 查詢主鍵取值區間
    select max(id),min(id) into @M,@N from t ;
    
    -- 隨機一個主鍵區間的值
    set @X=floor((@M-@N+1)*rand() + @N);
    
    -- 隨機的主鍵值可能不存在,使用範圍查詢
    select * from t where id >= @X limit 1;
    

    缺點:

    • 只適用取一條資料,多條資料返回查詢可能不夠
    • 主鍵分佈不均衡的情況下,不同行概率不一樣,例如:1、2、3、40000、400001
  • 方案3:隨機行數

    -- 獲取總行數
    select count(*) into @C from t;
    
    -- 設定隨機顯示數量
    set @N = 1;
    
    -- 計算起始行數
    set @Y = floor(@C * rand())-@N+1;
    
    -- 拼接sql
    set @sql = concat("select * from t limit ", @Y, ",", @N);
    
    -- 預處理語句
    prepare stmt from @sql;
    
    -- 執行語句
    execute stmt;
    
    -- prepare、execute、deallocate統稱為prepare statement,稱為預處理語句,deallocate用於釋放資源
    deallocate prepare stmt;
    

join優化

CREATE TABLE `t1` (
`id` int(11) NOT NULL,
`a` int(11) DEFAULT NULL,
`b` int(11) DEFAULT NULL,
`c` int(11) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB;

create table t2 like t1;
create table t3 like t2;
--  初始化三張表的資料
insert into ... 

-- 以下查詢需要加哪些索引來優化?
SELECT
	*
FROM
	t1
JOIN t2 ON (t1.a = t2.a)
JOIN t3 ON (t2.b = t3.b)
WHERE
	t1.c >= X
AND t2.c >= Y
AND t3.c >= Z;

索引原則,儘量使用BKA演算法,小表作為驅動表,假設第一個驅動表為:

  • t1:連線順序為t1->t2->t3,要在被驅動表欄位建立上索引,也就是 t2.a 和 t3.b 上建立索引
  • t2:連線順序不確定,需要評估另外兩個條件的過濾效果,都需要在t1.a、t3.b上建立索引
  • t3:連線順序是 t3->t2->t1,需要在 t2.b 和 t1.a 上建立索引

同時,還需要在第一個驅動表的欄位 c 上建立索引

自增主鍵是否連續

自增主鍵可能不連續,可能原因如下:

  • 自增值儲存策略
    • 在 MySQL 5.7 及之前的版本,自增值儲存在記憶體裡,並沒有持久化。每次重啟後,第一次開啟表的時候,都會去找自增值的最大值 max(id),然後將 max(id)+1 作為這個表當前的自增值
    • 在 MySQL 8.0 版本,將自增值的變更記錄在了 redo log 中,重啟的時候依靠 redo log 恢復重啟之前的值
  • 自增值修改機制
    • 如果插入資料時 id 欄位指定為 0、null 或未指定值,那麼就把這個表當前的 AUTO_INCREMENT 值填到自增欄位
    • 指定插入的主鍵值時,根據自增值生成演算法計算新的自增值,影響引數: auto_increment_offset 、auto_increment_increment
  • 自增值申請後未使用不允許回退
  • 同一個語句多次申請自增id,每一次申請是前一次的兩倍,可能造成浪費

MySQL 5.1.22 版本開始引入的引數 innodb_autoinc_lock_mode(預設1,語句結束後釋放自增鎖),控制了自增值申請時的鎖範圍。

預設值是 1。

  • 0 :表示採用之前 MySQL 5.0 版本的策略,即語句執行結束後才釋放鎖
  • 1 :普通 insert 語句,自增鎖在申請之後就馬上釋放;類似 insert … select 這樣的批量插入資料的語句,自增鎖還是要等語句結束後才被釋放。不包括普通的insert語句中包含多個value值的批量插入,因為可以計算需要多少個id,一次性申請後即可釋放
  • 2:申請後就釋放鎖(8.0預設值已改為2)

從併發效能的角度考慮,建議將其設定為 2,同時將 binlog_format 設定為 row

誤刪資料解決方案

誤刪資料分類:

  • 使用 delete 語句誤刪資料行
    • 確保 binlog_format=row 和 binlog_row_image=FULL的前提下,使用Flashback工具閃回恢復資料
    • 建議在從庫上執行,避免對資料的二次破壞(資料變更是有關聯的,有可能因為誤操作的資料觸發其他業務邏輯,從而導致其他資料的變更。因此,資料恢復需要再從庫上進行,驗證後再恢復回主庫)
    • 事前預防, sql_safe_updates=on關閉批量修改或刪除,增加SQL審計
  • 誤刪庫/表:drop table 、truncate table、drop database
    • 恢復方案:全量備份+實時備份binlog,可通過延遲複製備庫優化,相當於一個最近可用的全量備份
    • 預防方案:許可權控制、制定操作規範(例如:先備份後刪除,只能刪除指定字尾表)
  • rm刪除資料:高可用叢集即可,HA機制會重新選擇一個主庫

insert ...select加鎖分析

CREATE TABLE `t` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`c` int(11) DEFAULT NULL,
`d` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `c` (`c`)
) ENGINE=InnoDB;

insert into t values(null, 1,1);
insert into t values(null, 2,2);
insert into t values(null, 3,3);
insert into t values(null, 4,4);

create table t2 like t

-- 語句1:不走索引,加鎖範圍:所有行鎖和間隙鎖
mysql> explain insert into t2(c,d) select c,d from t;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
|  1 | INSERT      | t3    | NULL       | ALL  | NULL          | NULL | NULL    | NULL | NULL | NULL     | NULL  |
|  1 | SIMPLE      | t     | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    4 |      100 | NULL  |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+

-- 語句2:強制走索引c,倒序取第一條,加鎖範圍:(3,4]、(4,supremum] 
mysql> explain insert into t2(c,d) (select c+1, d from t force index(c) order by c desc limit 1);
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+---------------------+
| id | select_type | table | partitions | type  | possible_keys | key  | key_len | ref  | rows | filtered | Extra               |
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+---------------------+
|  1 | INSERT      | t2    | NULL       | ALL   | NULL          | NULL | NULL    | NULL | NULL | NULL     | NULL                |
|  1 | SIMPLE      | t     | NULL       | index | NULL          | c    | 5       | NULL |    1 |      100 | Backward index scan |
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+---------------------+

-- 語句3:從表t查詢資料,再插入到自身,需要暫存中間資料,使用了臨時表,在臨時表上limit,
-- 加鎖範圍:所有行鎖和間隙鎖(8.0.11上和語句2一樣,鎖範圍未發生變化)
mysql> explain insert into t(c,d) (select c+1, d from t force index(c) order by c desc limit 1);
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+--------------------------------------+
| id | select_type | table | partitions | type  | possible_keys | key  | key_len | ref  | rows | filtered | Extra                                |
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+--------------------------------------+
|  1 | INSERT      | t     | NULL       | ALL   | NULL          | NULL | NULL    | NULL | NULL | NULL     | NULL                                 |
|  1 | SIMPLE      | t     | NULL       | index | NULL          | c    | 5       | NULL |    1 |      100 | Backward index scan; Using temporary |
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+--------------------------------------+

-- 假設語句3,先把資料放入臨時表,再進行limit,會掃描所有行,如何優化?
create temporary table temp_t(c int,d int) engine=memory;
insert into temp_t (select c+1, d from t force index(c) order by c desc limit 1);
insert into t select * from temp_t;
drop table temp_t;

相關文章