SQL 優化

插入數據

插入數據：批量插入，手動提交事務插入，主鍵順序插入，大批量插入數據可以使用 MySQL load 指令

mysql --local-infile -u root -p;
set global local_infile=1;
load data local infile into table table_name fields terminated by "," lines terminated by "\n";

主鍵優化

主鍵優化：在 innoDB 存儲引擎中，表數據都是根據主鍵順序組織存放，這種存儲方式稱為索引組織表（index organized table IOT）頁分裂：頁可以為空，也可以填充一半，也可以填充100%，每個頁包含2- N數據（如果一行數據過大，會行溢出），根據主鍵排列頁合併：當刪除一行數據時，實際上記錄並沒有被物理刪除，只是被標記為刪除並且他的空間變得允許被其他記錄聲明使用。當頁中刪除的記錄達到MERGE_THRESHOLD（默認為頁的50%），innoDB會開始尋找最靠近的頁（前或後）看看是否可以兩頁合併以優化空間減低主鍵長度插入數據選擇順序插入（自增主鍵）業務操作避免修改主鍵

Cache Flow

order by 優化

Using filesort：通過表的索引或全表掃描，讀取滿足條件的數據行，然後排序緩沖區 sort buffer 中完成排序操作，所以不是通過索引直接返回排序結果的排序都叫 Filesort 排序 Using index：通過有序索引順序掃描直接返回有序數據，這種情況即為 using index，不需額外排序，操作效率高盡量使用覆蓋索引多個字段排序，一個升一個降，需要注意聯合索引在創建時的規則如果不可避免出現 filesort，大數據量排序時，可以適當增大排序緩沖區大小 sort_buffer_size（默認256KB）

# 沒有創建索引，根據 age, phone 進行排序
explain select id, age, phone from tb_user order by age, phone;

# 創建索引
create index idx_user_age_phone_aa on tb_user(age,phone);

# 創建索引，根據 age, phone 進行升序排序
explain select id, age, phone from tb_user order by age, phone;

# 創建索引，根據 age, phone 進行降序排序
explain select id, age, phone from tb_user order by age desc, phone desc;

# 創建索引
create index idx_user_age_phone_ad on tb_user(age asc, phone desc);

# 創建索引，根據 age, phone 進行降序，一個升序，一個降序
explain select id, age, phone from tb_user order by age asc, phone desc;

group by 優化

將查詢使用的字段創建聯合索引，並符合最左前綴原則

limit 優化

一個常見的問題就是 limit 2000000, 10, 此時需要排序前 2000010 記錄，僅僅返回 2000000-2000010 的記錄，其他記錄丟棄，查詢代價非常大。通過創建覆蓋索引加子查詢較好地提昇性能。

explain select * from tb_sku t, (select id from tb_sku order by id limit 2000000, 10) a where t.id = a.id;

count 優化

MyISAM 引擎把一個表的總行數存在磁盤上，因此執行 count() 的時候直接返回這個數，效率很高。 InnoDB 引擎執行 count() 的時候需要把數據一行一行從引擎讀出來然後累加計數。

count（主鍵）：InnoDB 遍歷整張表，把每一行主鍵 id 取出返回給服務層。服務層拿到主鍵，直接按行進行累加（主鍵不可能為NULL） count（字段）：沒有 NOT NULL 約束，InnoDB 引擎會遍歷整張表把每行的字段值取出來，返回服務層，判斷是否為 NULL ，不為 NULL，計數進行累加。有 NOT NULL 約束，InnoDB 引擎遍歷整張表把每行字段值取出來，直接按行進行累加。 count（1）：InnoDB 引擎遍歷整張表，但不取值。服務層對於返回的每一行，放一個數字"1"，直接按行進行累加。 count（*）：InnoDB 引擎不會把全部字段取出來，而是專門做優化不取值，服務層直接按行進行累加。

update 優化

InnoDB的行鎖是針對索引加的鎖，不是針對記錄加鎖，並且該索引不能失效，否則會從行鎖升級為表鎖。

update student set no = "20001010" where id = 1;

update student set no = "20001010" where name = "Nick";

總結

插入數據 insert 批量插入、手動控制事務、主鍵順序插入大批量插入使用 MySQL 命令 load data local infile

主鍵優化主鍵長度短、順序插入

order by 優化 using index：直接通過索引 using filesort：返回的結果在緩沖區排序

group by 優化索引，多字段分組滿足最左前綴原則

limit 優化覆蓋索引加子查詢

count 優化性能：count（*）> count（1）> count（id）> count（字段

update 優化更具索引或主鍵進行數據更新

SQL 優化

插入數據​

主鍵優化​

order by 優化​

group by 優化​

limit 優化​

count 優化​

update 優化​

總結​