1.存儲(chǔ)引擎的選擇(MyISAM和Innodb)

存儲(chǔ)引擎：MySQL中的數(shù)據(jù)、索引以及其他對(duì)象是如何存儲(chǔ)的，是一套文件系統(tǒng)的實(shí)現(xiàn)。

5.1之前默認(rèn)存儲(chǔ)引擎是MyISAM,5.1之后默認(rèn)存儲(chǔ)引擎是Innodb。

功能差異

image

選擇依據(jù)

MyISAM引擎設(shè)計(jì)簡(jiǎn)單，數(shù)據(jù)以緊密格式存儲(chǔ)，所以某些讀取場(chǎng)景下性能很好。

如果沒(méi)有特別的需求，使用默認(rèn)的Innodb即可。

MyISAM：以讀寫(xiě)插入為主的應(yīng)用程序，比如博客系統(tǒng)、新聞門(mén)戶(hù)網(wǎng)站。

Innodb：更新（刪除）操作頻率也高，或者要保證數(shù)據(jù)的完整性；并發(fā)量高，支持事務(wù)和外鍵保證數(shù)據(jù)完整性。比如OA自動(dòng)化辦公系統(tǒng)。

官網(wǎng)建議

官方建議使用Innodb,上面只是告訴大家,數(shù)據(jù)引擎是可以選擇,不過(guò)大多數(shù)情況還是不要選為妙

2.字段設(shè)計(jì)

數(shù)據(jù)庫(kù)設(shè)計(jì)3大范式

• 第一范式（確保每列保持原子性）
• 第二范式（確保表中的每列都和主鍵相關(guān)）
• 第三范式（確保每列都和主鍵列直接相關(guān)，而不是間接相關(guān)）

通常建議使用范式化設(shè)計(jì),因?yàn)榉妒交ǔ?huì)使得執(zhí)行操作更快。但這并不是絕對(duì)的,范式化也是有缺點(diǎn)的,通常需要關(guān)聯(lián)查詢(xún)，不僅代價(jià)昂貴,也可能使一些索引策略無(wú)效。

所以,我們有時(shí)需要混同范式化和反范式化,比如一個(gè)更新頻率低的字段可以冗余在表中,避免關(guān)聯(lián)查詢(xún)

單表字段不宜過(guò)多

建議最多30個(gè)以?xún)?nèi)

字段越多,會(huì)導(dǎo)致性能下降,并且增加開(kāi)發(fā)難度(一眼望不盡的字段,我們這些開(kāi)發(fā)仔會(huì)頓時(shí)傻掉的)

使用小而簡(jiǎn)單的合適數(shù)據(jù)類(lèi)型

a.字符串類(lèi)型

固定長(zhǎng)度使用char,非定長(zhǎng)使用varchar,并分配合適且足夠的空間

char在查詢(xún)時(shí),會(huì)把末尾的空格去掉;

b.小數(shù)類(lèi)型

一般情況可以使用float或double,占用空間小,但存儲(chǔ)可能會(huì)損失精度

decimal可存儲(chǔ)精確小數(shù),存儲(chǔ)財(cái)務(wù)數(shù)據(jù)或經(jīng)度要求高時(shí)使用decimal

c.時(shí)間日期

datetime:

• 范圍:1001年~9999年
• 存儲(chǔ):8個(gè)字節(jié)存儲(chǔ),以YYYYMMDDHHMMSS的格式存儲(chǔ)
• 時(shí)區(qū):與時(shí)區(qū)無(wú)關(guān)

timestamp:

• 范圍:1970年~2038年
• 存儲(chǔ):4個(gè)字節(jié)存儲(chǔ),存儲(chǔ)以UTC格式保存,與UNIX時(shí)間戳相同
• 時(shí)區(qū):存儲(chǔ)時(shí)對(duì)當(dāng)前的時(shí)區(qū)進(jìn)行轉(zhuǎn)換,檢索時(shí)再轉(zhuǎn)換回當(dāng)前的時(shí)區(qū)

1.通常盡量使用timestamp,因?yàn)樗加每臻g小,并且會(huì)自動(dòng)進(jìn)行時(shí)區(qū)轉(zhuǎn)換,無(wú)需關(guān)心地區(qū)時(shí)差

2.datetime和timestamp只能存儲(chǔ)最小顆粒度是秒,可以使用BIGINT類(lèi)型存儲(chǔ)微秒級(jí)別的時(shí)間戳

d.大數(shù)據(jù) blob和text

blob和text是為存儲(chǔ)很大的數(shù)據(jù)的而設(shè)計(jì)的字符串?dāng)?shù)據(jù)類(lèi)型,但通常建議避免使用

MySQL會(huì)把每個(gè)blob和text當(dāng)做獨(dú)立的對(duì)象處理,存儲(chǔ)引擎存儲(chǔ)時(shí)會(huì)做特殊處理,當(dāng)值太大,innoDB使用專(zhuān)門(mén)的外部存儲(chǔ)區(qū)域進(jìn)行存儲(chǔ),行內(nèi)存儲(chǔ)指針,然后在外部存儲(chǔ)實(shí)際的值。這些都會(huì)導(dǎo)致嚴(yán)重的性能開(kāi)銷(xiāo)

盡量將列設(shè)置為NOT NULL

a.可為NULL的列占用更多的存儲(chǔ)空間

b.可為NULL的列,在使用索引和值比較時(shí),mySQL需要做特殊的處理,損耗一定的性能

建議:通常最好指定列為NOT NULL,除非真的需要存儲(chǔ)NULL值

盡量使用整型做主鍵

a.整數(shù)類(lèi)型通常是標(biāo)識(shí)列最好的選擇,因?yàn)樗鼈兒芸觳⑶铱梢允褂肁UTO_INCREMENT

b.應(yīng)該避免使用字符串類(lèi)型作為標(biāo)識(shí)列,因?yàn)樗鼈兒芟目臻g,并且通常比數(shù)字類(lèi)型慢

c.對(duì)于完全"隨機(jī)"的字符串也需要多加注意。例如：MD5(),SHAI()或者UUID()產(chǎn)生的字符串。這些函數(shù)生成的新值也任意分布在很大空間內(nèi)，這會(huì)導(dǎo)致INSERT和一些SELECT語(yǔ)句很緩慢

索引

使用索引為什么快

• 索引相對(duì)于數(shù)據(jù)本身,數(shù)據(jù)量小
• 索引是有序的，可以快速確定數(shù)據(jù)位置
• InnoDB的表示索引組織表,表數(shù)據(jù)的分布按照主鍵排序

就好比書(shū)的目錄,想要找到某一個(gè)內(nèi)容,直接看目錄便可找到對(duì)應(yīng)的頁(yè)

索引的存儲(chǔ)結(jié)構(gòu)

a.B+樹(shù)(具體的結(jié)構(gòu)就不說(shuō)了,自己去了解)

b.哈希(鍵值對(duì)的結(jié)構(gòu))

MySQL中的主鍵索引用的是B+樹(shù)結(jié)構(gòu),非主鍵索引可以選擇B+樹(shù)或者哈希

通常建議使用B+樹(shù)索引

因?yàn)楣Ｋ饕秉c(diǎn)比較多:

1.無(wú)法用于排序

2.無(wú)法用于范圍查詢(xún)

3.數(shù)據(jù)量大時(shí),可能會(huì)出現(xiàn)大量哈希碰撞,導(dǎo)致效率低下

索引的類(lèi)型

按作用分類(lèi):

1.主鍵索引:不解釋,都知道

2.普通索引:沒(méi)有特殊限制,允許重復(fù)的值

3.唯一索引:不允許有重復(fù)的值,速度比普通索引略快

4.全文索引:用作全文搜索匹配,但基本用不上,只能索引英文單詞,而且操作代價(jià)很大

按數(shù)據(jù)存儲(chǔ)結(jié)構(gòu)分類(lèi):

1.聚簇索引

定義：數(shù)據(jù)行的物理順序與列值（一般是主鍵的那一列）的邏輯順序相同，一個(gè)表中只能擁有一個(gè)聚集索引。

主鍵索引是聚簇索引,數(shù)據(jù)的存儲(chǔ)順序是和主鍵的順序相同的

2.非聚簇索引

定義：該索引中索引的邏輯順序與磁盤(pán)上行的物理存儲(chǔ)順序不同，一個(gè)表中可以擁有多個(gè)非聚集索引。

聚簇索引以外的索引都是非聚集索引,細(xì)分為普通索引、唯一索引、全文索引,它們也被稱(chēng)為二級(jí)索引。

如下圖<高性能MySQL> Innodb存儲(chǔ)數(shù)據(jù)和索引的關(guān)系

image

主鍵索引的葉子節(jié)點(diǎn)存儲(chǔ)的是"行指針",直接指向物理文件的數(shù)據(jù)行。

二級(jí)索引的葉子結(jié)點(diǎn)存儲(chǔ)的是主鍵值

覆蓋索引:可直接從非主鍵索引直接獲取數(shù)據(jù)無(wú)需回表的索引

比如:

假設(shè)t表有一個(gè)(clo1,clo2)的多列索引

select clo1,clo2 from t where clo = 1

那么,使用這條sql查詢(xún),可直接從(clo1,clo2)索引樹(shù)中獲取數(shù)據(jù),無(wú)需回表查詢(xún)

因此我們需要盡可能的在select后只寫(xiě)必要的查詢(xún)字段，以增加索引覆蓋的幾率。

多列索引:使用多個(gè)列作為索引,比如(clo1,clo2)

使用場(chǎng)景:當(dāng)查詢(xún)中經(jīng)常使用clo1和clo2作為查詢(xún)條件時(shí),可以使用組合索引,這種索引會(huì)比單列索引更快

需要注意的是,多列索引的使用遵循最左索引原則

假設(shè)創(chuàng)建了多列索引index(A,B,C)，那么其實(shí)相當(dāng)于創(chuàng)建了如下三個(gè)組合索引：

1.index(A,B,C)

2.index(A,B)

3.index(A)

這就是最左索引原則，就是從最左側(cè)開(kāi)始組合。

索引優(yōu)化

1.索引不是越多越好,索引是需要維護(hù)成本的

2.在連接字段上應(yīng)該建立索引

3.盡量選擇區(qū)分度高的列作為索引,區(qū)分度count(distinct col)/count(*)表示字段不重復(fù)的比例,比例越大掃描的記錄數(shù)越少，狀態(tài)值、性別字段等區(qū)分度低的字段不適合建索引

4.幾個(gè)字段經(jīng)常同時(shí)以AND方式出現(xiàn)在Where子句中,可以建立復(fù)合索引,否則考慮單字段索引

5.把計(jì)算放到業(yè)務(wù)層而不是數(shù)據(jù)庫(kù)層

6.如果有 order by、group by 的場(chǎng)景，請(qǐng)注意利用索引的有序性。

• order by 最后的字段是組合索引的一部分，并且放在索引組合順序的最后，避免出現(xiàn) file_sort 的情況，影響查詢(xún)性能。

例如對(duì)于語(yǔ)句 where a=? and b=? order by c，可以建立聯(lián)合索引(a,b,c)。

order by 最后的字段是組合索引的一部分，并且放在索引組合順序的最后，避免出現(xiàn) file_sort(外部排序) 的情況，影響查詢(xún)性能。

• 例如對(duì)于語(yǔ)句 where a=? and b=? order by c，可以建立聯(lián)合索引(a,b,c)。
• 如果索引中有范圍查找，那么索引有序性無(wú)法利用，如 WHERE a>10 ORDER BY b;索引(a,b)無(wú)法排序。

可能導(dǎo)致無(wú)法使用索引的情況

1.is null 和 is not null

2.!= 和 <> (可用in代替)

3."非獨(dú)立列":索引列為表達(dá)式的一部分或是函數(shù)的參數(shù)

例如:

表達(dá)式的一部分:select id from t where id +1 = 5

函數(shù)參數(shù):select id from t where to_days(date_clo) >= 10

4.like查詢(xún)以%開(kāi)頭

5.or (or兩邊的列都建立了索引則可以使用索引)

6.類(lèi)型不一致

如果列是字符串類(lèi)型，傳入條件是必須用引號(hào)引起來(lái)，不然無(wú)法使用索引

select * from tb1 where email = 999;

3.Sql優(yōu)化建議

1.首先了解一下sql的執(zhí)行順序,使我們更好的優(yōu)化

(1)FROM:數(shù)據(jù)從硬盤(pán)加載到數(shù)據(jù)緩沖區(qū)，方便對(duì)接下來(lái)的數(shù)據(jù)進(jìn)行操作

(2)ON:join on實(shí)現(xiàn)多表連接查詢(xún),先篩選on的條件,再連接表

(3)JOIN:將join兩邊的表根據(jù)on的條件連接

(4)WHERE:從基表或視圖中選擇滿(mǎn)足條件的元組

(5)GROUP BY:分組，一般和聚合函數(shù)一起使用

(6)HAVING:在元組的基礎(chǔ)上進(jìn)行篩選，選出符合條件的元組（必須與GROUP BY連用）

(7)SELECT:查詢(xún)到得所有元組需要羅列的哪些列

(8)DISTINCT:去重

(9)UNION:將多個(gè)查詢(xún)結(jié)果合并

(10)ORDER BY：進(jìn)行相應(yīng)的排序

(11)LIMIT:顯示輸出一條數(shù)據(jù)記錄

• join on實(shí)現(xiàn)多表連接查詢(xún)，推薦該種方式進(jìn)行多表查詢(xún)，不使用子查詢(xún)(子查詢(xún)會(huì)創(chuàng)建臨時(shí)表,損耗性能)。
• 避免使用HAVING篩選數(shù)據(jù),而是使用where
• ORDER BY后面的字段建立索引,利用索引的有序性排序,避免外部排序
• 如果明確知道只有一條結(jié)果返回，limit 1 能夠提高效率

2.超過(guò)三個(gè)表最好不要 join

3.避免 SELECT *，從數(shù)據(jù)庫(kù)里讀出越多的數(shù)據(jù)，那么查詢(xún)就會(huì)變得越慢

4.盡可能的使用 NOT NULL列,可為NULL的列占用額外的空間,且在值比較和使用索引時(shí)需要特殊處理,影響性能

5.用exists、not exists和in、not in相互替代

原則是哪個(gè)的子查詢(xún)產(chǎn)生的結(jié)果集小，就選哪個(gè)

select * from t1 where x in (select y from t2)
select * from t1 where exists (select null from t2 where y =x)

IN適合于外表大而內(nèi)表小的情況；exists適合于外表小而內(nèi)表大的情況

6、使用exists替代distinct

當(dāng)提交一個(gè)包含一對(duì)多表信息（比如部門(mén)表和雇員表）的查詢(xún)時(shí)，避免在select子句中使用distinct，一般可以考慮使用exists代替，exists使查詢(xún)更為迅速，因?yàn)樽硬樵?xún)的條件一旦滿(mǎn)足，立馬返回結(jié)果。

低效寫(xiě)法：

select distinct dept_no,dept_name from dept d,emp e where d.dept_no=e.dept_no

高效寫(xiě)法：

select dept_no,dept_name from dept d where exists (select 'x' from emp e where e.dept_no=d.dept_no)

備注：其中x的意思是：因?yàn)閑xists只是看子查詢(xún)是否有結(jié)果返回，而不關(guān)心返回的什么內(nèi)容，因此建議寫(xiě)一個(gè)常量，性能較高！

用exists的確可以替代distinct，不過(guò)以上方案僅適用dept_no為唯一主鍵的情況，如果要去掉重復(fù)記錄，需要參照以下寫(xiě)法：

select * from emp where dept_no exists (select Max(dept_no)) from dept d, emp e where e.dept_no=d.dept_no group by d.dept_no)

7、避免隱式數(shù)據(jù)類(lèi)型轉(zhuǎn)換

隱式數(shù)據(jù)類(lèi)型轉(zhuǎn)換不能適用索引，導(dǎo)致全表掃描！t_tablename表的phonenumber字段為varchar類(lèi)型

以下代碼不符合規(guī)范：

select column1 into i_l_variable1 from t_tablename where phonenumber=18519722169;

應(yīng)編寫(xiě)如下：

select column1 into i_lvariable1 from t_tablename where phonenumber='18519722169';

8.分段查詢(xún)

在一些查詢(xún)頁(yè)面中，當(dāng)用戶(hù)選擇的時(shí)間范圍過(guò)大，造成查詢(xún)緩慢。主要的原因是掃描行數(shù)過(guò)多。這個(gè)時(shí)候可以通過(guò)程序，分段進(jìn)行查詢(xún)，循環(huán)遍歷，將結(jié)果合并處理進(jìn)行展示。

4.Expalin 分析執(zhí)行計(jì)劃

explain顯示了mysql如何使用索引來(lái)處理select語(yǔ)句以及連接表?？梢詭椭x擇更好的索引和寫(xiě)出更優(yōu)化的查詢(xún)語(yǔ)句。

例:

explain SELECT user_name from sys_user where user_id <10

image

該語(yǔ)句連接類(lèi)型為range,使用主鍵索引進(jìn)行了范圍查詢(xún),估計(jì)掃描了100行數(shù)據(jù)

更多含義詳看下面表格從上可看出

image

image