SQL優(yōu)化，怎么做？
一、插入優(yōu)化
批量插入
insert into tb_name values (1,"張三"),(2,"張三"),(3,"張三");
手動提交事務(wù)
由于mysql默認事務(wù)提交方式是自動提交的，意味著當我們執(zhí)行完一條insert語句之后，事務(wù)就自動提交了，可能會頻繁的涉及到事務(wù)的開始與提交，所以建議手動控制事務(wù)。
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指令進行插入。操作如下:
#客戶端連接服務(wù)端時，加上參數(shù)--local-infile
mysql --local-infile -U root -p
#設(shè)置全局參數(shù)local infile為1，開啟從本地加載文件導(dǎo)入數(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è)務(wù)需求的情況下，盡量降低主鍵的長度。
滿足業(yè)務(wù)需求的情況下，盡量降低主鍵的長度。
插入數(shù)據(jù)時，盡量選擇順序插入，選擇使用AUTO_ INCREMENT自增主鍵 。
業(yè)務(wù)操作時，避免對主鍵的修改。
三、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值都取出來,返回給服務(wù)層。服務(wù)層拿到主鍵后,直接按行進行累加(主鍵不可能為null)。
count(字段)
沒有not null約束: InnoDB引擎會遍歷整張表把每一行的字段值都取出來, 返回給服務(wù)層，服務(wù)層判斷是否為null,不為null，計數(shù)累加。
有not null約束: InnoDB引擎會遍歷整張表把每一行的字段值都取出來, 返回給服務(wù)層，直接按行進行累加。
count(1)
InnoDB引擎遍歷整張表，但不取值。服務(wù)層對于返回的每一行,放一個數(shù)字"1" 進去,直接按行進行累加。
count(*)
InnoDB引擎并不會把全部字段取出來,而是專門做了優(yōu)化,不取值，服務(wù)層直接按行進行累加。
按照效率排序的話，count(字段) < count(主鍵id) < count(1)≈count(*)，所以盡量使用count(*)。
七、update優(yōu)化
InnoDB的行鎖是針對索引加的鎖，不是針對記錄加的鎖,并且該索引不能失效，否則會從行鎖升級為表鎖。所以在使用update操作時，更新的條件最好要有索引，否則導(dǎo)致行鎖升級為表鎖，并發(fā)性能降低。
