一、插入數據 優化

1.1? 普通插入（小數據量）

普通插入（小數據量）：

1. 采用批量插入（一次插入的數據不建議超過1000條）
2. 手動提交事務
3. 主鍵順序插入

?1.2? 大批量數據插入

?大批量插入：（主鍵順序插入的性能高于亂序插入）
如果一次性需要插入大批量數據，使用insert語句插入性能較低，此時可以使用MySQL數據庫提供的load指令插入。

1. # 客戶端連接服務端時，加上參數 --local-infile（這一行在bash/cmd界面輸入）
2. mysql --local-infile -u root -p
3. # 設置全局參數local_infile為1，開啟從本地加載文件導入數據的開關
4. set global local_infile = 1;
5. select @@local_infile;? ?#查重參數
6. # 執行load指令將準備好的數據，加載到表結構中
7. load data local infile '/root/sql1.log' into table 'tb_user' fields terminated by ',' lines terminated by '\n';

案例具體解釋如下：

• '/root/sql1.log'：表示要加載的文件路徑，這里假設文件位于根目錄下的sql1.log文件。
• 'tb_user'：表示要將數據加載到的目標表的名稱為'tb_user'。
• FIELDS TERMINATED BY ','：表示文件中的字段是由逗號進行分隔的。
• LINES TERMINATED BY '\n'：表示文件中的行是以換行符（\n）結束的。

二、主鍵優化

數據組織方式：在InnoDB存儲引擎中，表數據都是根據主鍵順序組織存放的，這種存儲方式的表稱為索引組織表（Index organized table, IOT）?

由于“表數據都是根據主鍵順序組織存放的”，所以順序插入的效率高。

主鍵設計原則：

• 滿足業務需求的情況下，盡量降低主鍵的長度
• 插入數據時，盡量選擇順序插入，選擇使用 AUTO_INCREMENT 自增主鍵
• 盡量不要使用 UUID 做主鍵或者是其他的自然主鍵，如身份證號
• 業務操作時，避免對主鍵的修改

三、order by 優化

1. Using filesort：通過表的索引或全表掃描，讀取滿足條件的數據行，然后在排序緩沖區 sort buffer 中完成排序操作，所有不是通過索引直接返回排序結果的排序都叫 FileSort 排序
2. Using index：通過有序索引順序掃描直接返回有序數據，這種情況即為 using index，不需要額外排序，操作效率高

如果order by字段全部使用升序排序或者降序排序，則都會走索引，但是如果一個字段升序排序，另一個字段降序排序，則不會走索引，explain的extra信息顯示的是Using index, Using filesort，如果要優化掉Using filesort，則需要另外再創建一個索引，如：create index idx_user_age_phone_ad on tb_user(age asc, phone desc);，此時使用select id, age, phone from tb_user order by age asc, phone desc;會全部走索引?

總結：

• 根據排序字段建立合適的索引，多字段排序時，也遵循最左前綴法則
• 盡量使用覆蓋索引
• 多字段排序，一個升序一個降序，此時需要注意聯合索引在創建時的規則（ASC/DESC）
• 如果不可避免出現filesort，大數據量排序時，可以適當增大排序緩沖區大小 sort_buffer_size（默認256k）

四、group by 優化

• 在分組操作時，可以通過索引來提高效率
• 分組操作時，索引的使用也是滿足最左前綴法則的

如索引為idx_user_pro_age_stat，則句式可以是select ... where profession order by age，這樣也符合最左前綴法則。索引 idx_user_pro_age_stat 所包含的列順序是 profession, age, stat。查詢的條件是 WHERE profession，而排序的條件是 ORDER BY age。由于 profession 列出現在了索引的最左邊，并且查詢和排序都是從最左邊的列開始的，所以這個查詢語句可以利用該索引進行優化。

五、limit 優化

常見的問題如limit 2000000, 10，此時需要 MySQL 排序前2000000條記錄，但僅僅返回2000000 - 2000010的記錄，其他記錄丟棄，查詢排序的代價非常大。
優化方案：一般分頁查詢時，通過創建覆蓋索引能夠比較好地提高性能，可以通過覆蓋索引加子查詢形式進行優化。

案例：

1. -- 此語句耗時很長
2. select * from tb_sku limit 9000000, 10;
3. -- 通過覆蓋索引加快速度，直接通過主鍵索引進行排序及查詢
4. select id from tb_sku order by id limit 9000000, 10;
5. -- 下面的語句是錯誤的，因為 MySQL 不支持 in 里面使用 limit
6. -- select * from tb_sku where id in (select id from tb_sku order by id limit 9000000, 10);
7. -- 通過連表查詢即可實現第一句的效果，并且能達到第二句的速度
8. select * from tb_sku as s, (select id from tb_sku order by id limit 9000000, 10) as a where s.id = a.id;

在一般的分頁查詢中，需要獲取指定頁碼的數據行。傳統的做法是通過LIMIT語句來限制返回結果的行數，但這可能需要進行全表掃描，并且只有在獲取完整結果集后才會進行截取，效率較低。

通過創建覆蓋索引，可以避免對主表的訪問，直接從索引中獲取所需的列數據，提高查詢速度。覆蓋索引是一種包含所有查詢需要的列的索引。當查詢只需要返回覆蓋索引中的列時，不需要再回到主表查找相應的行，從而節省了IO操作。

此外，使用子查詢形式可以進一步優化分頁查詢。子查詢是將一個查詢嵌套在另一個查詢中的查詢方式。在分頁查詢中，可以先執行一個子查詢來獲取滿足條件的主鍵或唯一標識符，并將其作為條件用于主查詢，以獲取特定的頁碼數據。

六、count 優化

MyISAM 引擎把一個表的總行數存在了磁盤上，因此執行 count(*) 的時候會直接返回這個數，效率很高（前提是不適用where）；
InnoDB 在執行 count(*) 時，需要把數據一行一行地從引擎里面讀出來，然后累計計數。
優化方案：自己計數，如創建key-value表存儲在內存或硬盤，或者是用redis

count的幾種用法：

• 如果count函數的參數（count里面寫的那個字段）不是NULL（字段值不為NULL），累計值就加一，最后返回累計值
• 用法：count(*)、count(主鍵)、count(字段)、count(1)
• count(主鍵)跟count(*)一樣，因為主鍵不能為空；count(字段)只計算字段值不為NULL的行；count(1)引擎會為每行添加一個1，然后就count這個1，返回結果也跟count(*)一樣；count(null)返回0

各種用法的性能：

• count(主鍵)：InnoDB引擎會遍歷整張表，把每行的主鍵id值都取出來，返回給服務層，服務層拿到主鍵后，直接按行進行累加（主鍵不可能為空）
• count(字段)：沒有not null約束的話，InnoDB引擎會遍歷整張表把每一行的字段值都取出來，返回給服務層，服務層判斷是否為null，不為null，計數累加；有not null約束的話，InnoDB引擎會遍歷整張表把每一行的字段值都取出來，返回給服務層，直接按行進行累加
• count(1)：InnoDB 引擎遍歷整張表，但不取值。服務層對于返回的每一層，放一個數字 1 進去，直接按行進行累加
• count(*)：InnoDB 引擎并不會把全部字段取出來，而是專門做了優化，不取值，服務層直接按行進行累加

按效率排序：count(*) ≈ count(1) >count(主鍵) >= count(字段)，所以盡量使用 count(*)?

七、updata 優化

當使用UPDATE語句更新數據時，如果WHERE條件選擇的是不帶索引的字段，可能導致行鎖升級為表鎖。這是因為在沒有索引的情況下，數據庫無法快速定位到需要更新的行，只能對整個表或較大的數據范圍進行鎖定，以確保數據的一致性。

當WHERE條件選擇帶索引的字段時，數據庫可以利用索引來快速定位到滿足條件的行，只對特定的行進行鎖定，從而減少鎖沖突和提高并發性能。這樣可以避免行鎖升級為表鎖，并減少對其他事務的影響。

?