柚子快報激活碼778899分享：數(shù)據(jù)庫優(yōu)化sql的方案

http://yzkb.51969.com/

一： 插入數(shù)據(jù)的優(yōu)化

1.insert

如果我們需要一次性往數(shù)據(jù)庫表中插入多條記錄，可以從以下三個方面進(jìn)行優(yōu)化

優(yōu)化方案一

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

Insert into tb_test values(1,'Tom'),(2,'Cat'),(3,'Jerry');

優(yōu)化方案二

start transaction;

insert into tb_test values(1,'Tom'),(2,'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;

優(yōu)化方案三

主鍵順序插入，性能要高于亂序插入，由于B+樹的原因，可以減輕檢索重量，減低檢索速度

主鍵亂序插入 : 8 1 9 21 88 2 4 15 89 5 7 3

主鍵順序插入 : 1 2 3 4 5 7 8 9 15 21 88 89

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

一次性需要插入大批量數(shù)據(jù)(比如: 幾百萬的記錄)，使用insert語句插入性能較低，此時可以使用MySQL數(shù)據(jù)庫提供的load指令進(jìn)行插入

-- 客戶端連接服務(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指令將準(zhǔn)備好的數(shù)據(jù)，加載到表結(jié)構(gòu)中

load data local infile '/root/sql1.log' into table tb_user fields terminated by ',' lines terminated by '\n' ;

二： 主鍵優(yōu)化

在load時，主鍵順序插入性能高于亂序插入

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

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

2. 頁分裂

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

順序插入：沒有也分裂的現(xiàn)象

亂序插入：出現(xiàn)頁分裂

過程：當(dāng)亂序插入，該頁已經(jīng)滿了，但是我們還要插入數(shù)據(jù)的時候，將50%的數(shù)據(jù)移動到新頁取，將數(shù)據(jù)插入以后，再建立新的連接

3. 頁合并

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

過程：刪除數(shù)據(jù)達(dá)到閾值的時候會進(jìn)行合并頁

4. 索引設(shè)計原則

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

三： order by優(yōu)化

Using filesort:

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

Using index:

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

A. 根據(jù)排序字段建立合適的索引，多字段排序時，也遵循最左前綴法則 B. 盡量使用覆蓋索引，否則造成回表查詢降低了查詢速度 C. 多字段排序, 一個升序一個降序，此時需要注意聯(lián)合索引在創(chuàng)建時的規(guī)則（ASC/DESC） D. 如果不可避免的出現(xiàn)filesort，大數(shù)據(jù)量排序時，可以適當(dāng)增大排序緩沖區(qū)大小 sort_buffer_size(默認(rèn)256k)

四：group by優(yōu)化

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

五：limit優(yōu)化

在數(shù)據(jù)量比較大時，如果進(jìn)行l(wèi)imit分頁查詢，在查詢時，越往后，分頁查詢效率越低。

優(yōu)化思路:

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

explain select * from tb_sku t , (select id from tb_sku order by id

limit 2000000,10) a where t.id = a.id;

六：count優(yōu)化

MyISAM 引擎把一個表的總行數(shù)存在了磁盤上，因此執(zhí)行 count(*) 的時候會直接返回這個數(shù)，效率很高； 但是如果是帶條件的count，MyISAM也慢。 InnoDB 引擎就麻煩了，它執(zhí)行 count(*) 的時候，需要把數(shù)據(jù)一行一行地從引擎里面讀出來，然后累積計數(shù)

如果說要大幅度提升InnoDB表的count效率，主要的優(yōu)化思路：自己計數(shù)(可以借助于redis這樣的數(shù)據(jù)庫進(jìn)行,但是如果是帶條件的count又比較麻煩了)

?

七：update優(yōu)化

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

柚子快報激活碼778899分享：數(shù)據(jù)庫優(yōu)化sql的方案

http://yzkb.51969.com/

文章來源