一、插入數據

普通插入：

1. 采用批量插入（一次插入的數據不建議超過1000條）

insert into tb_test values(1,'Tom'),(3, 'Cat'),(3, 'Jerry')....

2. 手動提交事務

start transaction;
insert into tb_test values(1,'Tom'),(3, 'Cat'),(3, 'Jerry');
insert into tb_test values(4,'Tom'),(5, 'Cat'),(6, 'Jerry');
insert into tb_test values(7,'Tom'),(8, 'Cat'),(9, 'Jerry');
commit;

3. 主鍵順序插入性能高于亂序插入

大批量插入：

如果一次性需要插入大批量數據，使用insert語句插入性能較低，此時可以使用MySQL數據庫提供的load指令插入。

# 客戶端連接服務端時，加上參數 --local-infile
mysql --local-infile -u root -p

# 查看全局參數local_infile是否開啟
select @@local_infile;
# 設置全局參數local_infile為1，開啟從本地加載文件導入數據的開關
set global local_infile = 1;
# 執行load指令將準備好的數據，加載到表結構中
# 將sql100w.sql文件的數據加載到tb_user表中
# 用逗號分隔字段
load data local infile '/root/sql100w.sql' into table 'tb_user' fields terminated by ',' lines terminated by '\n';

二、主鍵優化

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

• 頁分裂：頁可以為空，也可以填充一般，也可以填充100%，每個頁包含了2-N行數據（如果一行數據過大，會行溢出），根據主鍵排列。
• 頁合并：當刪除一行記錄時，實際上記錄并沒有被物理刪除，只是記錄被標記（flaged）為刪除并且它的空間變得允許被其他記錄聲明使用。當頁中刪除的記錄到達MERGE_THRESHOLD（默認為頁的50%），InnoDB會開始尋找最靠近的頁（前后）看看是否可以將這兩個頁合并以優化空間使用。

MERGE_THRESHOLD：合并頁的閾值，可以自己設置，在創建表或創建索引時指定。

主鍵設計原則：

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

三、order by優化

在MySQL中排序分為以下兩種清空：

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

#創建索引，兩個字段，全部降序或全部升序會走這個索引
create index idx_user_age_phone+aa on tb_user(age,phone);#創建索引，一個升，一個降
create index idx_user_age_phone_ad on tb_user(age asc, phone desc);

總結：

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

注： 在創建多列索引時，要根據業務需求，where子句中使用最頻繁的一列放在最左邊。

四、group by優化

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

如索引為idx_user_pro_age_stat，則句式可以是select ... where profession order by age，這樣也符合最左前綴法則。

五、limit優化

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

例如：

# 此語句耗時很長
select * from tb_sku limit 9000000, 10;
# 通過覆蓋索引加快速度，直接通過主鍵索引進行排序及查詢出id
select id from tb_sku order by id limit 9000000, 10;
# 通過子查詢查出所有字段
select * from tb_sku as s, (select id from tb_sku order by id limit 9000000, 10) as a where s.id = a.id;

六、count優化

select count(*) from tb_user;

• MyISAM 引擎把一個表的總行數存在了磁盤上，因此執行 count(*) 的時候會直接返回這個數，效率很高。
• 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(主鍵) < count(1) < count(*)，所以盡量使用 count(*)

七、update優化（避免行鎖升級為表鎖）

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

例如：
update student set no = '1' where id = 1; 這句由于id有主鍵索引，所以只會鎖這一行；
update student set no = '1' where name = 'zheng'; 這句由于name沒有索引，所以會把整張表都鎖住進行數據更新，解決方法是給name字段添加索引；