一、插入優(yōu)化

批量插入

insert into tb_name values (1,"張三"),(2,"張三"),(3,"張三");

手動提交事務

由于mysql默認事務提交方式是自動提交的，意味著當我們執(zhí)行完一條insert語句之后，事務就自動提交了，可能會頻繁的涉及到事務的開始與提交，所以建議手動控制事務。

start transaction ;
insert into tb_name values (1,"張三"),(2,"張三"),(3,"張三");
insert into tb_name values (4,"張三"),(5,"張三"),(6,"張三");
insert into tb_name values (7,"張三"),(8,"張三"),(9,"張三");
commit ;

主鍵順序插入

主鍵順序插入的性能要高于亂序插入的性能，取決于MySQL的數(shù)據(jù)組織結(jié)構(gòu)的。

大批量插入數(shù)據(jù)

如果一次性需要插入大批量數(shù)據(jù),使用insert語句插入性能較低,此時可以使用MySQL數(shù)據(jù)庫提供的load指令進行插入。操作如下:

#客戶端連接服務端時，加上參數(shù)--local-infile
mysql --local-infile -U root -p

#設(shè)置全局參數(shù)local infile為1，開啟從本地加載文件導入數(shù)據(jù)的開關(guān)
set global local infile= 1;

#執(zhí)行l(wèi)oad指令將準備好的數(shù)據(jù)，加載到表結(jié)構(gòu)中     在使用load指令時，主鍵順序插入性能高于亂序插入
load data local infile '/xxx/sql.log' into table 'tb_name' fields terminated by ',' lines terminated by '\n' ;

/xxx/sql.log : 將要加載的本地文件
tb_name : 表名
, : 字段分割符
\n : 行分割符

二、主鍵SQL優(yōu)化

數(shù)據(jù)組織方式

在InnoDB存儲引擎中，表數(shù)據(jù)都是根據(jù)主鍵順序組織存放的,這種存儲方式的表稱為索引組織表(index organized table IOT)。

頁分裂

頁可以為空，也可以填充一半,也可以填充100%。每個頁包含了2-N行數(shù)據(jù)(如果一行數(shù)據(jù)過大, 會行溢出),根據(jù)主鍵排列。

頁合并

當刪除一行記錄時,實際上記錄并沒有被物理刪除，只是記錄被標記(flaged) 為刪除并且它的空間變得允許被其他記錄聲明使用。

當頁中刪除的記錄達到MERGE_THRESHOLD (默認為頁的50%) , InnoDB會開始尋找最靠近的頁(前或后)看看是否可以將兩個頁合并以優(yōu)化空間使用。

滿足業(yè)務需求的情況下，盡量降低主鍵的長度。

滿足業(yè)務需求的情況下，盡量降低主鍵的長度。

插入數(shù)據(jù)時，盡量選擇順序插入，選擇使用AUTO_ INCREMENT自增主鍵 。

業(yè)務操作時，避免對主鍵的修改。

三、order by 優(yōu)化

① Using filesort :通過表的索引或全表掃描，讀取滿足條件的數(shù)據(jù)行,然后在排序緩沖區(qū)sort buffer中完成排序操作,所有不是通過索引直接返回排序結(jié)果的排序都叫FileSort排序。

② Using index :通過有序索引順序掃描直接返回有序數(shù)據(jù)，這種情況即為using index,不需要額外排序,操作效率高。

#沒有創(chuàng)建索引時，根據(jù)age, phone進行排序
explain select id,age,phone from tb_user order by age , phone;

#創(chuàng)建索引
create index idx_user_age_ phone_aa on tb_user(age,phone);

#創(chuàng)建索引后，根據(jù)age, phone進行升序排序
explain select id,age,phone from tb_user order by age , phone;

#創(chuàng)建索引后，根據(jù)age, phone進行降序排序
explain select id,age,phone from tb_user order by age desc , phone desc ;

#根據(jù)age, phone進行降序一個升序，一個降序
explain select id,age,phone from tb_user order by age asc , phone desc;

#創(chuàng)建索引
create index idx_user_age_hone_ad on tb_user(age asc ,phone desc);

#根據(jù)age, phone進行降序一個升序，一個降序
explain select id,age,phone from tb_user order by age asc , phone desc;

根據(jù)排序字段建立合適的索引，多字段排序時，也遵循最左前綴法則。

盡量使用覆蓋索引。

多字段排序,一個升序一個降序,此時需要注意聯(lián)合索引在創(chuàng)建時的規(guī)則(ASC/DESC) 。

如果不可避免的出現(xiàn)filesort,大數(shù)據(jù)量排序時，可以適當增大排序緩沖區(qū)大小sort_ buffer_ size(默認256k)。

四、group by 優(yōu)化

#執(zhí)行分組操作，根據(jù)profession字段分組
explain select profession , count(*) from tb_user group by profession ;

#創(chuàng)建索引
Create index idx_user_pro_age_sta on tb_user(profession , age , status);

#執(zhí)行分組操作，根據(jù)profession字段分組
explain select profession , count(*) from tb_user group by profession;

#執(zhí)行分組操作，根據(jù)profession字 段分組
explain select profession, count(*) from tb_user group by profession,age;

在分組操作時，可以通過索引來提高效率。

分組操作時，索引的使用也是滿足最左前綴法則的。

五、limit 優(yōu)化

一個常見又非常頭疼的問題就是limit 2000000,10，此時需要MySQL排序前2000010記錄,僅僅返回2000000 - 2000010的記錄,其他記錄丟棄,查詢排序的代價非常大。

優(yōu)化思路:一般分頁查詢時，通過創(chuàng)建覆蓋索引能夠比較好地提高性能，可以通過覆蓋索引加子查詢形式進行優(yōu)化。

如：

explain select from tb_ sku t，(select id from tb_ _sku order by id limit 
2000000,10) a wheret.id = a.id;

六、count 優(yōu)化

explain select count(*) from tb user ;

MyISAM 引擎把一個表的總行數(shù)存在了磁盤上，因此執(zhí)行count(*)的時候會直接返回這個數(shù),效率很高;

InnoDB 引擎就麻煩了,它執(zhí)行count(*)的時候，需要把數(shù)據(jù)一-行一行地從引擎里面讀出來，然后累積計數(shù)。

優(yōu)化思路:自己計數(shù)。可以配合redis

count的幾種用法

count()是一個聚合函數(shù),對于返回的結(jié)果集，一行行地判斷, 如果count函數(shù)的參數(shù)不是NULL,累計值就加1,否則不加，最后返回累計值。

用法: count (*)、count (主鍵)、count (字段)、count (1)。

count(主鍵)

InnoDB引擎會遍歷整張表,把每一行的主鍵id值都取出來,返回給服務層。服務層拿到主鍵后,直接按行進行累加(主鍵不可能為null)。

count(字段)

沒有not null約束: InnoDB引擎會遍歷整張表把每一行的字段值都取出來, 返回給服務層，服務層判斷是否為null,不為null，計數(shù)累加。

有not null約束: InnoDB引擎會遍歷整張表把每一行的字段值都取出來, 返回給服務層，直接按行進行累加。

count(1)

InnoDB引擎遍歷整張表，但不取值。服務層對于返回的每一行,放一個數(shù)字"1" 進去,直接按行進行累加。

count(*)

InnoDB引擎并不會把全部字段取出來,而是專門做了優(yōu)化,不取值，服務層直接按行進行累加。

按照效率排序的話，count(字段) < count(主鍵id) < count(1)≈count(*)，所以盡量使用count(*)。

七、update優(yōu)化

InnoDB的行鎖是針對索引加的鎖，不是針對記錄加的鎖,并且該索引不能失效，否則會從行鎖升級為表鎖。所以在使用update操作時，更新的條件最好要有索引，否則導致行鎖升級為表鎖，并發(fā)性能降低。