Mysql優(yōu)化技巧、存儲過程、視圖講解一、存儲過程
存儲過程（ ）是在大型數(shù)據(jù)庫系統(tǒng)中，一組為了完成特定功能的SQL 語句集，它存儲在數(shù)據(jù)庫中，一次編譯后永久有效，用戶通過指定存儲過程的名字并給出參數(shù)（如果該存儲過程帶有參數(shù)）來執(zhí)行它。存儲過程是數(shù)據(jù)庫中的一個(gè)重要對象。在數(shù)據(jù)量特別龐大的情況下利用存儲過程能達(dá)到倍速的效率提升
1. 存儲過程特點(diǎn)
有輸入輸出參數(shù)，可以聲明變量，有if/else, case,while等控制語句，通過編寫存儲過程，可以實(shí)現(xiàn)復(fù)雜的邏輯功能
函數(shù)的普遍特性：模塊化，封裝，代碼復(fù)用
速度快，只有首次執(zhí)行需經(jīng)過編譯和優(yōu)化步驟，后續(xù)被調(diào)用可以直接執(zhí)行，省去以上步驟；
2. 存儲過程優(yōu)缺點(diǎn)優(yōu)點(diǎn) 在生產(chǎn)環(huán)境下，可以通過直接修改存儲過程的方式修改業(yè)務(wù)邏輯（或bug），而不用重啟服務(wù)器。 執(zhí)行速度快。存儲過程經(jīng)過編譯之后會比單獨(dú)一條一條執(zhí)行要快。 減少網(wǎng)絡(luò)傳輸。存儲過程直接就在數(shù)據(jù)庫服務(wù)器上跑，所有的數(shù)據(jù)訪問都在服務(wù)器內(nèi)部進(jìn)行，不需要傳輸數(shù)據(jù)到其它終端。缺點(diǎn) SQL本身是一種結(jié)構(gòu)化查詢語言，加上了一些控制（賦值、循環(huán)和異常處理等），但不是OO的，本質(zhì)上還是過程化的，面對復(fù)雜的業(yè)務(wù)邏輯，過程化的處理會很吃力。這一點(diǎn)算致命傷。 不便于調(diào)試。 沒辦法應(yīng)用緩存。雖然有全局臨時(shí)表之類的方法可以做緩存，但同樣加重了數(shù)據(jù)庫的負(fù)擔(dān)。 無法適應(yīng)數(shù)據(jù)庫的切割（水平或垂直切割）。數(shù)據(jù)庫切割之后，存儲過程并不清楚數(shù)據(jù)存儲在哪個(gè)數(shù)據(jù)庫中。
文章末尾：具體講解了存儲過程用法。
二、慢查詢1. 概念
MySQL默認(rèn)10秒內(nèi)沒有響應(yīng)SQL結(jié)果,則為慢查詢，可以去修改MySQL慢查詢默認(rèn)時(shí)間
2. 查看慢查詢的時(shí)間
show variables like 'long_query_time';
3. 修改慢查詢的時(shí)間
set long_query_time=1; （重啟后失效）
4. 將慢查詢寫入日志
：是否開啟慢查詢?nèi)罩荆?表示開啟，0表示關(guān)閉。
log-slow- ：舊版（5.6以下版本）MySQL數(shù)據(jù)庫慢查詢?nèi)罩敬鎯β窂健？梢圆辉O(shè)置該參數(shù)，系統(tǒng)則會默認(rèn)給一個(gè)缺省的文件-slow.log
slow-query-log-file：新版（5.6及以上版本）MySQL數(shù)據(jù)庫慢查詢?nèi)罩敬鎯β窂健？梢圆辉O(shè)置該參數(shù)，系統(tǒng)則會默認(rèn)給一個(gè)缺省的文件-slow.log
：慢查詢閾值，當(dāng)查詢時(shí)間多于設(shè)定的閾值時(shí)，記錄日志。
：未使用索引的查詢也被記錄到慢查詢?nèi)罩局校蛇x項(xiàng)）。
：日志存儲方式。=’FILE’表示將日志存入文件，默認(rèn)值是’FILE’。=’TABLE’表示將日志存入數(shù)據(jù)庫，這樣日志信息就會被寫入到mysql.表中。MySQL數(shù)據(jù)庫支持同時(shí)兩種日志存儲方式，配置的時(shí)候以逗號隔開即可，如：=’FILE,TABLE’。日志記錄到系統(tǒng)的專用日志表中，要比記錄到文件耗費(fèi)更多的系統(tǒng)資源，因此對于需要啟用慢查詢?nèi)罩荆中枰軌颢@得更高的系統(tǒng)性能，那么建議優(yōu)先記錄到文件。
5. 開啟慢查詢?nèi)罩?
set global slow_query_log=1
6. 修改f文件方式:
slow_query_log =1
slow_query_log_file=/tmp/mysql_slow.log
log--not-using-：未使用索引的查詢也被記錄到慢查詢?nèi)罩局校蛇x項(xiàng)）。如果調(diào)優(yōu)的話，建議開啟這個(gè)選項(xiàng)。另外，開啟了這個(gè)參數(shù)，其實(shí)使用full index scan的sql也會被記錄到慢查詢?nèi)罩尽?/p>
ments:表示是否將慢管理語句例如 TABLE和ALTER TABLE等記入慢查詢?nèi)罩?/p>
三、優(yōu)化和索引
表的設(shè)計(jì)合理化(符合3NF)
添加適當(dāng)索引(index) [四種: 普通索引、主鍵索引、唯一索引、全文索引]
SQL語句優(yōu)化
分表技術(shù)(水平分割、垂直分割)
讀寫[寫: //add]分離
存儲過程 [模塊化編程，可以提高速度]
對mysql配置優(yōu)化 [配置最大并發(fā)數(shù)my.ini, 調(diào)整緩存大小 ]
mysql服務(wù)器硬件升級
定時(shí)的去清除不需要的數(shù)據(jù),定時(shí)進(jìn)行碎片整理()
1. 數(shù)據(jù)庫范式
概念
為了建立冗余較小、結(jié)構(gòu)合理的數(shù)據(jù)庫，設(shè)計(jì)數(shù)據(jù)庫時(shí)必須遵循一定的規(guī)則。在關(guān)系型數(shù)據(jù)庫中這種規(guī)則就稱為范式。范式是符合某一種設(shè)計(jì)要求的總結(jié)。要想設(shè)計(jì)一個(gè)結(jié)構(gòu)合理的關(guān)系型數(shù)據(jù)庫，必須滿足一定的范式。
三大范式
第一范式：1NF是對屬性的原子性約束，要求屬性(列)具有原子性，不可再分解；(只要是關(guān)系型數(shù)據(jù)庫都滿足1NF)
第二范式：2NF是對記錄的惟一性約束，表中的記錄是唯一的, 就滿足2NF, 通常我們設(shè)計(jì)一個(gè)主鍵來實(shí)現(xiàn)，主鍵不能包含業(yè)務(wù)邏輯。
第三范式：3NF是對字段冗余性的約束，它要求字段沒有冗余。 沒有冗余的數(shù)據(jù)庫設(shè)計(jì)可以做到。
2. 索引優(yōu)化
索引用來快速地尋找那些具有特定值的記錄，所有MySQL索引都以B-樹的形式保存。如果沒有索引，執(zhí)行查詢時(shí)MySQL必須從第一個(gè)記錄開始掃描整個(gè)表的所有記錄，直至找到符合要求的記錄。表里面的記錄數(shù)量越多，這個(gè)操作的代價(jià)就越高。如果作為搜索條件的列上已經(jīng)創(chuàng)建了索引，MySQL無需掃描任何記錄即可迅速得到目標(biāo)記錄所在的位置。如果表有1000個(gè)記錄，通過索引查找記錄至少要比順序掃描記錄快100倍。
索引的分類 主鍵索引:主鍵是一種唯一性索引，但它必須指定為“ KEY”。如果你曾經(jīng)用過類型的列，你可能已經(jīng)熟悉主鍵之類的概念了。主鍵一般在創(chuàng)建表的時(shí)候指定，例如“ TABLE ( […], KEY (列的列表) ); ”。但是，我們也可以通過修改表的方式加入主鍵存儲過程中創(chuàng)建視圖，例如“ALTER TABLE ADD KEY (列的列表); ”。每個(gè)表只能有一個(gè)主鍵。需要查詢索引時(shí)可以通過以下語句查詢。 全文索引示例：
CREATE TABLE articles (
id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
title VARCHAR(200),
body TEXT,
FULLTEXT (title,body)
)engine=myisam charset utf8;
錯(cuò)誤用法:
* from where body like ‘%mysql%’; 錯(cuò)誤用法 索引不會生效
正確用法:
* from where match(title,body) ( ‘’)
說明:
在mysql中 索引只針對 生效
mysql自己提供的針對英文生效-> () 技術(shù)處理中文
使用方法是 match(字段名…) (‘關(guān)鍵字’)
全文索引：停止詞, 因?yàn)樵谝粋€(gè)文本中，創(chuàng)建索引是一個(gè)無窮大的數(shù)，因此，對一些常用詞和字符，就不會創(chuàng)建，這些詞，稱為停止詞.比如（a，b，mysql，the）
mysql> match(title,body) (‘’) from ;（輸出的是每行和的匹配度）
唯一索引：這種索引和前面的“普通索引”基本相同存儲過程中創(chuàng)建視圖，但有一個(gè)區(qū)別：索引列的所有值都只能出現(xiàn)一次，即必須唯一。唯一性索引可以用以下幾種方式創(chuàng)建：
創(chuàng)建索引，例如 INDEX ON (列的列表)；
修改表，例如ALTER TABLE ADD [索引的名字] (列的列表)；
創(chuàng)建表的時(shí)候指定索引，例如 TABLE ( […], [索引的名字] (列的列表) )；
示例:創(chuàng)建表結(jié)構(gòu)
create table ddd(id int primary key auto_increment , name varchar(32) unique);
注意
字段可以為NULL,并可以有多NULL, 但是如果是具體內(nèi)容，則不能重復(fù)，但是不能存有重復(fù)的空字符串
普通索引:普通索引（由關(guān)鍵字KEY或INDEX定義的索引）的唯一任務(wù)是加快對數(shù)據(jù)的訪問速度。因此，應(yīng)該只為那些最經(jīng)常出現(xiàn)在查詢條件（=）或排序條件（）中的數(shù)據(jù)列創(chuàng)建索引。只要有可能，就應(yīng)該選擇一個(gè)數(shù)據(jù)最整齊、最緊湊的數(shù)據(jù)列（如一個(gè)整數(shù)類型的數(shù)據(jù)列）來創(chuàng)建索引。
示例:創(chuàng)建表結(jié)構(gòu)
create table ccc( id int unsigned, name varchar(32));
索引的代價(jià)
占用磁盤空間
對DML(、、)語句的效率影響
增刪改會對索引影響，因?yàn)樗饕匦抡怼?/p>
適合添加索引的列
查詢作為查詢條件字段應(yīng)該創(chuàng)建索引
唯一性太差的字段不適合單獨(dú)創(chuàng)建索引,即使頻繁
頻繁更新字段，也不要定義索引。
不會出現(xiàn)在where語句的字段不要?jiǎng)?chuàng)建索引
總結(jié):滿處一下條件的字段，才應(yīng)該創(chuàng)建索引
肯定在where條件經(jīng)常使用 該字段的內(nèi)容不是唯一的幾個(gè)值 字段內(nèi)容不是頻繁變化3. 注意事項(xiàng)
創(chuàng)建一個(gè)聯(lián)合索引
alter table dept add index (dname,loc); // dname 左邊的列,loc就是右邊的列
注意:
對于創(chuàng)建的多列索引，如果不是使用第一部分，則不會創(chuàng)建索引。
* from dept where loc=’aaa’\G
就不會使用到索引
模糊查詢在like前面有百分號開頭會失效。
如果條件中有or，即使其中有條件帶索引也不會使用。換言之，就是要求使用的所有字段，都必須建立索引, 我們建議大家盡量避免使用or 關(guān)鍵字
如果列類型是字符串，那一定要在條件中將數(shù)據(jù)使用引號引用起來。否則不使用索引。(添加時(shí),字符串必須’’), 也就是，如果列是字符串類型，就一定要用 ‘’ 把他包括起來.
如果mysql估計(jì)使用全表掃描要比使用索引快，則不使用索引。
4. 查詢所用使用率
show like ‘%’;
注意：
:這個(gè)值越高越好，越高表示使用索引查詢到的次數(shù)。
t:這個(gè)值越高，說明查詢低效。
四、SQL優(yōu)化技巧 使用group by 分組查詢是，默認(rèn)分組后，還會排序，可能會降低速度，
在group by 后面增加 order by null 就可以防止排序.
explain select * from emp group by deptno order by null;
有些情況下，可以使用連接來替代子查詢。因?yàn)槭褂胘oin，MySQL不需要在內(nèi)存中創(chuàng)建臨時(shí)表。
select * from dept, emp where dept.deptno=emp.deptno; [簡單處理方式]
select * from dept left join emp on dept.deptno=emp.deptno; [左外連接，更ok!]
對查詢進(jìn)行優(yōu)化，要盡量避免全表掃描，首先應(yīng)考慮在 where 及 order by 涉及的列上建立索引，應(yīng)盡量避免在 where 子句中對字段進(jìn)行 null 值判斷，否則將導(dǎo)致引擎放棄使用索引而進(jìn)行全表掃描，如：
select id from t where num is null
最好不要給數(shù)據(jù)庫留 NULL，盡可能的使用 NOT NULL 填充數(shù)據(jù)庫.
備注、描述、評論之類的可以設(shè)置為 NULL，其他的，最好不要使用 NULL。
不要以為 NULL 不需要空間，比如：char(100) 型，在字段建立時(shí)，空間就固定了， 不管是否插入值（NULL 也包含在內(nèi)），都是占用 100 個(gè)字符的空間的，如果是 這樣的變長字段， null 不占用空間。
可以在 num 上設(shè)置默認(rèn)值 0，確保表中 num 列沒有 null 值，然后這樣查詢：
id from t where num = 0
4. 減少>=或-1