1.存儲(chǔ)引擎的選擇(MyISAM和Innodb)
存儲(chǔ)引擎：MySQL中的數(shù)據(jù)泳姐、索引以及其他對象是如何存儲(chǔ)的效拭，是一套文件系統(tǒng)的實(shí)現(xiàn)。
5.1之前默認(rèn)存儲(chǔ)引擎是MyISAM,5.1之后默認(rèn)存儲(chǔ)引擎是Innodb胖秒。
功能差異
選擇依據(jù)
MyISAM引擎設(shè)計(jì)簡單缎患，數(shù)據(jù)以緊密格式存儲(chǔ)，所以某些讀取場景下性能很好阎肝。
如果沒有特別的需求挤渔，使用默認(rèn)的Innodb即可。
MyISAM：以讀寫插入為主的應(yīng)用程序风题，比如博客系統(tǒng)蚂蕴、新聞門戶網(wǎng)站。
Innodb：更新（刪除）操作頻率也高俯邓，或者要保證數(shù)據(jù)的完整性；并發(fā)量高熔号，支持事務(wù)和外鍵保證數(shù)據(jù)完整性稽鞭。比如OA自動(dòng)化辦公系統(tǒng)。
官網(wǎng)建議
官方建議使用Innodb,上面只是告訴大家,數(shù)據(jù)引擎是可以選擇,不過大多數(shù)情況還是不要選為妙
2.字段設(shè)計(jì)
數(shù)據(jù)庫設(shè)計(jì)3大范式
- 第一范式（確保每列保持原子性）
- 第二范式（確保表中的每列都和主鍵相關(guān)）
- 第三范式（確保每列都和主鍵列直接相關(guān)引镊，而不是間接相關(guān)）
通常建議使用范式化設(shè)計(jì),因?yàn)榉妒交ǔ?huì)使得執(zhí)行操作更快朦蕴。但這并不是絕對的,范式化也是有缺點(diǎn)的,通常需要關(guān)聯(lián)查詢，不僅代價(jià)昂貴,也可能使一些索引策略無效弟头。
所以,我們有時(shí)需要混同范式化和反范式化,比如一個(gè)更新頻率低的字段可以冗余在表中,避免關(guān)聯(lián)查詢
單表字段不宜過多
建議最多30個(gè)以內(nèi)
字段越多,會(huì)導(dǎo)致性能下降,并且增加開發(fā)難度(一眼望不盡的字段,我們這些開發(fā)仔會(huì)頓時(shí)傻掉的)
使用小而簡單的合適數(shù)據(jù)類型
a.字符串類型
固定長度使用char,非定長使用varchar,并分配合適且足夠的空間
char在查詢時(shí),會(huì)把末尾的空格去掉;
b.小數(shù)類型
一般情況可以使用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ū)無關(guān)
timestamp:
- 范圍:1970年~2038年
- 存儲(chǔ):4個(gè)字節(jié)存儲(chǔ),存儲(chǔ)以UTC格式保存,與UNIX時(shí)間戳相同
- 時(shí)區(qū):存儲(chǔ)時(shí)對當(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)換,無需關(guān)心地區(qū)時(shí)差
2.datetime和timestamp只能存儲(chǔ)最小顆粒度是秒,可以使用BIGINT類型存儲(chǔ)微秒級別的時(shí)間戳
d.大數(shù)據(jù) blob和text
blob和text是為存儲(chǔ)很大的數(shù)據(jù)的而設(shè)計(jì)的字符串?dāng)?shù)據(jù)類型,但通常建議避免使用
MySQL會(huì)把每個(gè)blob和text當(dāng)做獨(dú)立的對象處理,存儲(chǔ)引擎存儲(chǔ)時(shí)會(huì)做特殊處理,當(dāng)值太大,innoDB使用專門的外部存儲(chǔ)區(qū)域進(jìn)行存儲(chǔ),行內(nèi)存儲(chǔ)指針,然后在外部存儲(chǔ)實(shí)際的值吩抓。這些都會(huì)導(dǎo)致嚴(yán)重的性能開銷
盡量將列設(shè)置為NOT NULL
a.可為NULL的列占用更多的存儲(chǔ)空間
b.可為NULL的列,在使用索引和值比較時(shí),mySQL需要做特殊的處理,損耗一定的性能
建議:通常最好指定列為NOT NULL,除非真的需要存儲(chǔ)NULL值
盡量使用整型做主鍵
a.整數(shù)類型通常是標(biāo)識(shí)列最好的選擇,因?yàn)樗鼈兒芸觳⑶铱梢允褂肁UTO_INCREMENT
b.應(yīng)該避免使用字符串類型作為標(biāo)識(shí)列,因?yàn)樗鼈兒芟目臻g,并且通常比數(shù)字類型慢
c.對于完全"隨機(jī)"的字符串也需要多加注意。例如：MD5(),SHAI()或者UUID()產(chǎn)生的字符串赴恨。這些函數(shù)生成的新值也任意分布在很大空間內(nèi)疹娶，這會(huì)導(dǎo)致INSERT和一些SELECT語句很緩慢
索引
使用索引為什么快
- 索引相對于數(shù)據(jù)本身,數(shù)據(jù)量小
- 索引是有序的，可以快速確定數(shù)據(jù)位置
- InnoDB的表示索引組織表,表數(shù)據(jù)的分布按照主鍵排序
就好比書的目錄,想要找到某一個(gè)內(nèi)容,直接看目錄便可找到對應(yīng)的頁
索引的存儲(chǔ)結(jié)構(gòu)
a.B+樹(具體的結(jié)構(gòu)就不說了,自己去了解)
b.哈希(鍵值對的結(jié)構(gòu))
MySQL中的主鍵索引用的是B+樹結(jié)構(gòu),非主鍵索引可以選擇B+樹或者哈希
通常建議使用B+樹索引
因?yàn)楣Ｋ饕秉c(diǎn)比較多:
1.無法用于排序
2.無法用于范圍查詢
3.數(shù)據(jù)量大時(shí),可能會(huì)出現(xiàn)大量哈希碰撞,導(dǎo)致效率低下
索引的類型
按作用分類:
1.主鍵索引:不解釋,都知道
2.普通索引:沒有特殊限制,允許重復(fù)的值
3.唯一索引:不允許有重復(fù)的值,速度比普通索引略快
4.全文索引:用作全文搜索匹配,但基本用不上,只能索引英文單詞,而且操作代價(jià)很大
按數(shù)據(jù)存儲(chǔ)結(jié)構(gòu)分類:
1.聚簇索引
定義：數(shù)據(jù)行的物理順序與列值（一般是主鍵的那一列）的邏輯順序相同伦连，一個(gè)表中只能擁有一個(gè)聚集索引雨饺。
主鍵索引是聚簇索引,數(shù)據(jù)的存儲(chǔ)順序是和主鍵的順序相同的
2.非聚簇索引
定義：該索引中索引的邏輯順序與磁盤上行的物理存儲(chǔ)順序不同钳垮，一個(gè)表中可以擁有多個(gè)非聚集索引。
聚簇索引以外的索引都是非聚集索引,細(xì)分為普通索引额港、唯一索引饺窿、全文索引,它們也被稱為二級索引。
如下圖<高性能MySQL> Innodb存儲(chǔ)數(shù)據(jù)和索引的關(guān)系
主鍵索引的葉子節(jié)點(diǎn)存儲(chǔ)的是"行指針",直接指向物理文件的數(shù)據(jù)行移斩。
二級索引的葉子結(jié)點(diǎn)存儲(chǔ)的是主鍵值
覆蓋索引:可直接從非主鍵索引直接獲取數(shù)據(jù)無需回表的索引
比如:
假設(shè)t表有一個(gè)(clo1,clo2)的多列索引
select clo1,clo2 from t where clo = 1
那么,使用這條sql查詢,可直接從(clo1,clo2)索引樹中獲取數(shù)據(jù),無需回表查詢
因此我們需要盡可能的在select后只寫必要的查詢字段肚医，以增加索引覆蓋的幾率。
多列索引:使用多個(gè)列作為索引,比如(clo1,clo2)
使用場景:當(dāng)查詢中經(jīng)常使用clo1和clo2作為查詢條件時(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è)開始組合。
索引優(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ù)庫層
6.如果有 order by、group by 的場景超升，請注意利用索引的有序性入宦。
- order by 最后的字段是組合索引的一部分，并且放在索引組合順序的最后室琢，避免出現(xiàn) file_sort 的情況乾闰，影響查詢性能。
例如對于語句 where a=? and b=? order by c盈滴，可以建立聯(lián)合索引(a,b,c)涯肩。
order by 最后的字段是組合索引的一部分，并且放在索引組合順序的最后巢钓，避免出現(xiàn) file_sort(外部排序) 的情況病苗，影響查詢性能。
- 例如對于語句 where a=? and b=? order by c症汹，可以建立聯(lián)合索引(a,b,c)硫朦。
- 如果索引中有范圍查找，那么索引有序性無法利用背镇，如 WHERE a>10 ORDER BY b;索引(a,b)無法排序咬展。
可能導(dǎo)致無法使用索引的情況
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查詢以%開頭
5.or (or兩邊的列都建立了索引則可以使用索引)
6.類型不一致
如果列是字符串類型，傳入條件是必須用引號(hào)引起來瞒斩，不然無法使用索引
select * from tb1 where email = 999;
3.Sql優(yōu)化建議
1.首先了解一下sql的執(zhí)行順序,使我們更好的優(yōu)化
(1)FROM:數(shù)據(jù)從硬盤加載到數(shù)據(jù)緩沖區(qū)破婆，方便對接下來的數(shù)據(jù)進(jìn)行操作
(2)ON:join on實(shí)現(xiàn)多表連接查詢,先篩選on的條件,再連接表
(3)JOIN:將join兩邊的表根據(jù)on的條件連接
(4)WHERE:從基表或視圖中選擇滿足條件的元組
(5)GROUP BY:分組，一般和聚合函數(shù)一起使用
(6)HAVING:在元組的基礎(chǔ)上進(jìn)行篩選胸囱，選出符合條件的元組（必須與GROUP BY連用）
(7)SELECT:查詢到得所有元組需要羅列的哪些列
(8)DISTINCT:去重
(9)UNION:將多個(gè)查詢結(jié)果合并
(10)ORDER BY：進(jìn)行相應(yīng)的排序
(11)LIMIT:顯示輸出一條數(shù)據(jù)記錄
- join on實(shí)現(xiàn)多表連接查詢祷舀，推薦該種方式進(jìn)行多表查詢，不使用子查詢(子查詢會(huì)創(chuàng)建臨時(shí)表,損耗性能)。
- 避免使用HAVING篩選數(shù)據(jù),而是使用where
- ORDER BY后面的字段建立索引,利用索引的有序性排序,避免外部排序
- 如果明確知道只有一條結(jié)果返回蔑鹦，limit 1 能夠提高效率
2.超過三個(gè)表最好不要 join
3.避免 SELECT *夺克，從數(shù)據(jù)庫里讀出越多的數(shù)據(jù)，那么查詢就會(huì)變得越慢
4.盡可能的使用 NOT NULL列,可為NULL的列占用額外的空間,且在值比較和使用索引時(shí)需要特殊處理,影響性能
5.用exists嚎朽、not exists和in铺纽、not in相互替代
原則是哪個(gè)的子查詢產(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è)包含一對多表信息（比如部門表和雇員表）的查詢時(shí)，避免在select子句中使用distinct锅很，一般可以考慮使用exists代替其馏，exists使查詢更為迅速，因?yàn)樽硬樵兊臈l件一旦滿足爆安，立馬返回結(jié)果叛复。
低效寫法：
select distinct dept_no,dept_name from dept d,emp e where d.dept_no=e.dept_no
高效寫法：
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只是看子查詢是否有結(jié)果返回，而不關(guān)心返回的什么內(nèi)容扔仓，因此建議寫一個(gè)常量褐奥，性能較高！
用exists的確可以替代distinct翘簇，不過以上方案僅適用dept_no為唯一主鍵的情況撬码，如果要去掉重復(fù)記錄，需要參照以下寫法：
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ù)類型轉(zhuǎn)換
隱式數(shù)據(jù)類型轉(zhuǎn)換不能適用索引呜笑，導(dǎo)致全表掃描！t_tablename表的phonenumber字段為varchar類型
以下代碼不符合規(guī)范：
select column1 into i_l_variable1 from t_tablename where phonenumber=18519722169;
應(yīng)編寫如下：
select column1 into i_lvariable1 from t_tablename where phonenumber='18519722169';
8.分段查詢
在一些查詢頁面中彻犁，當(dāng)用戶選擇的時(shí)間范圍過大叫胁，造成查詢緩慢。主要的原因是掃描行數(shù)過多汞幢。這個(gè)時(shí)候可以通過程序曹抬，分段進(jìn)行查詢，循環(huán)遍歷急鳄，將結(jié)果合并處理進(jìn)行展示。
4.Expalin 分析執(zhí)行計(jì)劃
explain顯示了mysql如何使用索引來處理select語句以及連接表堰酿〖埠辏可以幫助選擇更好的索引和寫出更優(yōu)化的查詢語句。
例:
explain SELECT user_name from sys_user where user_id <10
該語句連接類型為range,使用主鍵索引進(jìn)行了范圍查詢,估計(jì)掃描了100行數(shù)據(jù)
更多含義詳看下面表格從上可看出
