來(lái)自公眾號(hào)：非科班的科班
作者黎杜
概述
對(duì)于mysql的優(yōu)化是一個(gè)綜合性的技術(shù)沪袭，sql的優(yōu)化只是其中的一種，其中主要包括
表的設(shè)計(jì)合理化(符合3大范式)妒御。
添加索引(index) [普通索引解愤、主鍵索引、唯一索引unique乎莉、全文索引]送讲。
分表技術(shù)(水平分割、垂直分割)惋啃。
讀寫(xiě)[寫(xiě): update/delete/add]分離哼鬓。
合理設(shè)計(jì)表
在表的設(shè)計(jì)中一定條件下要滿足三范式，表的范式边灭，是首先符合第一范式, 才能滿足第二范式 , 進(jìn)一步滿足第三范式异希。
第一范式: 即表的列的具有原子性,不可再分解，即列的信息绒瘦，不能分解, 只有數(shù)據(jù)庫(kù)是關(guān)系型數(shù)據(jù)庫(kù)(mysql/oracle/db2/sql server)称簿，就自動(dòng)的滿足第一范式扣癣。
第二范式: 表中的記錄是唯一的, 就滿足第二范式, 通常我們?cè)O(shè)計(jì)一個(gè)主鍵來(lái)實(shí)現(xiàn)。
第三范式: 即表中不要有冗余數(shù)據(jù), 就是說(shuō)予跌，表的信息搏色，如果能夠被推導(dǎo)出來(lái)，就不應(yīng)該單獨(dú)的設(shè)計(jì)一個(gè)字段來(lái)存放. 比如下面的設(shè)計(jì)就是不滿足第三范式：
表1存在冗余表2的數(shù)據(jù)券册，正常的設(shè)計(jì)都會(huì)設(shè)計(jì)成如下：
注意： 反第三范式: 但是沒(méi)有冗余的數(shù)據(jù)庫(kù)未必是最好的數(shù)據(jù)庫(kù)频轿，有時(shí)為了提高運(yùn)行效率，就必須降低范式標(biāo)準(zhǔn)烁焙，適當(dāng)保留冗余數(shù)據(jù)航邢。具體做法是：在概念數(shù)據(jù)模型設(shè)計(jì)時(shí)遵守第三范式，降低范式標(biāo)準(zhǔn)的工作放到物理數(shù)據(jù)模型設(shè)計(jì)時(shí)考慮骄蝇。降低范式就是增加字段膳殷，允許冗余。
在1對(duì)N的情況下九火，為了提高查詢的效率赚窃，是允許部分字段冗余的。
Sql優(yōu)化
Sql的優(yōu)化中岔激，主要是對(duì)字段添加索引勒极，主要包含有這四種索引(主鍵索引/唯一索引/全文索引/普通索引)
1.主鍵索引添加
當(dāng)一張表，把某個(gè)列設(shè)為主鍵的時(shí)候虑鼎，則該列就是主鍵索引,下面的id 列就是主鍵索引
create table user
(id int unsigned primary key auto_increment ,
name varchar(32) not null defaul ‘’);
如果你創(chuàng)建表時(shí)辱匿，沒(méi)有指定主鍵索引，也可以在創(chuàng)建表后炫彩，在添加, 指令:
alter table 表名 add primary key (列名);
//舉例
alter table user add primary key (id);
2.普通索引
一般來(lái)說(shuō)匾七，普通索引的創(chuàng)建，是先創(chuàng)建表江兢，然后在創(chuàng)建普通索引
比如:
create table user(
id int unsigned,
name varchar(32)
)
create index 索引名 on 表 (列1,列名2);
3.創(chuàng)建全文索引
全文索引昨忆，主要是針對(duì)對(duì)文件，文本的檢索, 比如文章, 全文索引針對(duì)MyISAM有用杉允。創(chuàng)建如下：
CREATE TABLE articles (
id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
title VARCHAR(200),
body TEXT,
FULLTEXT (title,body)
)engine=myisam charset utf8;
如何使用全文索引:
select * from articles where body like ‘%非科班%’; //不會(huì)使用到全文索引
// 查看是否使用索引:
explain select * from articles where body like ‘%非科班%’
// 正確的用法是:
select * from articles where match(title,body) against(‘非科班’);
說(shuō)明:
在mysql中fulltext 索引只針對(duì) myisam生效
mysql自己提供的fulltext針對(duì)英文生效->sphinx (coreseek) 技術(shù)處理中文
使用方法是match(字段名..) against(‘關(guān)鍵字’)
全文索引一個(gè) 叫 停止詞, 因?yàn)樵谝粋€(gè)文本中扔嵌，創(chuàng)建索引是一個(gè)無(wú)窮大的數(shù)，因此夺颤，對(duì)一些常用詞和字符痢缎，就不會(huì)創(chuàng)建，這些詞世澜，稱為停止詞.
4.唯一索引
當(dāng)表的某列被指定為unique約束時(shí)独旷，這列就是一個(gè)唯一索引
// 創(chuàng)建
create table user(id int primary key auto_increment , name varchar(32) unique);
這時(shí), name 列就是一個(gè)唯一索引，unique字段可以為NULL,并可以有多NULL, 但是如果是具體內(nèi)容，則不能重復(fù)嵌洼，主鍵字段案疲，不能為NULL,也不能重復(fù)。
創(chuàng)建唯一索引
create table user(id int primary key auto_increment, name varchar(32));
create unique index 索引名 on 表名 (列表..);
查詢索引
desc 表名 //不能夠顯示索引名
show index(es) from 表名
show keys from 表名
刪除索引
alter table 表名 drop index 索引名;
//如果刪除主鍵索引麻养。
alter table 表名 drop primary key
索引使用的注意事項(xiàng)
由于索引本身很大褐啡，占用磁盤(pán)空間，對(duì)dml操作有影響鳖昌，變慢备畦，滿足以下條件的字段，才應(yīng)該創(chuàng)建索引许昨。
肯定在where條經(jīng)常使用
該字段的內(nèi)容不是唯一的幾個(gè)值
字段內(nèi)容不是頻繁變化
explain 可以幫助我們?cè)诓徽嬲龍?zhí)行某個(gè)sql語(yǔ)句時(shí)懂盐，就執(zhí)行mysql怎樣執(zhí)行，這樣利用我們?nèi)シ治鰏ql指令糕档。
id：查詢的序列號(hào)莉恼。
select_type：查詢類型。
table：查詢表名速那。
type：掃描方式俐银，all表示全表掃描。
possible_keys：可是使用到的索引端仰。
key：實(shí)際使用到的索引悉患。
rows：該sql掃面了多少行。
Extra：sql語(yǔ)句額外的信息榆俺，比如排序方式
sql語(yǔ)句的小技巧
- 在使用group by 分組查詢時(shí)，默認(rèn)分組后坞淮，還會(huì)排序茴晋，可能會(huì)降低速度，在group by 后面增加 order by null 就可以防止排序回窘。如下圖所示
- 有些情況下诺擅，可以使用連接來(lái)替代子查詢。因?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;
正確的選擇mysql的存儲(chǔ)引擎
Myisam : 如果表對(duì)事務(wù)要求不高，同時(shí)是以查詢和添加為主的酒觅，我們考慮使用myisam存儲(chǔ)引擎. ,比如 bbs 中的 發(fā)帖表撮执，回復(fù)表。
INNODB : 對(duì)事務(wù)要求高舷丹，保存的數(shù)據(jù)都是重要數(shù)據(jù)抒钱，我們建議使用INNODB,比如訂單表，賬號(hào)表。
如果你的數(shù)據(jù)庫(kù)的存儲(chǔ)引擎是myisam,請(qǐng)一定記住要定時(shí)進(jìn)行碎片整理
分表技術(shù)
為什么要分表谋币？
（1） 如果一個(gè)表的每條記錄的內(nèi)容很大仗扬，那么就需要更多的IO操作，如果字段值比較大蕾额，而使用頻率相對(duì)比較低早芭，可以將大字段移到另一張表中，當(dāng)查詢不查大字段的時(shí)候诅蝶，這樣就減少了I/O操作
（2）如果表的數(shù)據(jù)量非常非常大退个，那么查詢就變的比較慢；也就是表的數(shù)據(jù)量影響這查詢的性能秤涩。
（3）表中的數(shù)據(jù)本來(lái)就有獨(dú)立性帜乞，例如分別記錄各個(gè)地區(qū)的數(shù)據(jù)或者不同時(shí)期的數(shù)據(jù)，特別是有些數(shù)據(jù)常用筐眷，而另外一些數(shù)據(jù)不常用黎烈。
（4） 分表技術(shù)有(水平分割和垂直分割)
垂直分割
垂直分割是指數(shù)據(jù)表列的拆分，把一張列比較多的表拆分為多張表匀谣。垂直分割一般用于拆分大字段和訪問(wèn)頻率低的字段照棋，分離冷熱數(shù)據(jù)。
垂直分割比較常見(jiàn)：例如博客系統(tǒng)中的文章表武翎，比如文章tbl_articles
(id, titile, summary, content, user_id, create_time)烈炭，因?yàn)槲恼轮械膬?nèi)容content會(huì)比較長(zhǎng)，放在tbl_articles中會(huì)嚴(yán)重影響表的查詢速度宝恶，所以將內(nèi)容放到tbl_articles_detail(article_id, content)符隙，像文章列表只需要查詢tbl_articles中的字段即可。
垂直拆分的優(yōu)點(diǎn)：可以使得行數(shù)據(jù)變小垫毙，在查詢時(shí)減少讀取的Block數(shù)霹疫，減少I(mǎi)/O次數(shù)。此外综芥，垂直分區(qū)可以簡(jiǎn)化表的結(jié)構(gòu)丽蝎，易于維護(hù)。
垂直拆分的缺點(diǎn)：主鍵會(huì)出現(xiàn)冗余膀藐，需要管理冗余列屠阻，并會(huì)引起Join操作，可以通過(guò)在應(yīng)用層進(jìn)行Join來(lái)解決额各。此外国觉，垂直分區(qū)會(huì)讓事務(wù)變得更加復(fù)雜。
水平分割
水平拆分是指數(shù)據(jù)表行數(shù)據(jù)的拆分虾啦，表的行數(shù)超過(guò)500萬(wàn)行或者單表容量超過(guò)10GB時(shí)蛉加，查詢就會(huì)變慢蚜枢，這時(shí)可以把一張的表的數(shù)據(jù)拆成多張表來(lái)存放。水平分表盡可能使每張表的數(shù)據(jù)量相當(dāng)针饥，比較均勻厂抽。
水平拆分會(huì)給應(yīng)用增加復(fù)雜度，它通常在查詢是需要多個(gè)表名丁眼，查詢所有數(shù)據(jù)需要union操作筷凤。在許多數(shù)據(jù)庫(kù)應(yīng)用中，這種復(fù)雜性會(huì)超過(guò)它帶來(lái)的優(yōu)點(diǎn)苞七。
因?yàn)橹灰饕P(guān)鍵字不大藐守，則在索引用于查詢時(shí)，表中增加2-3倍數(shù)據(jù)量蹂风，查詢時(shí)也就增加讀一個(gè)索引層的磁盤(pán)次數(shù)卢厂，所以水平拆分要考慮數(shù)據(jù)量的增長(zhǎng)速度，根據(jù)實(shí)際情況決定是否需要對(duì)表進(jìn)行水平拆分惠啄。
水平分割最重要的是找到分割的標(biāo)準(zhǔn)慎恒，不同的表應(yīng)根據(jù)業(yè)務(wù)找出不同的標(biāo)準(zhǔn)
用戶表可以根據(jù)用戶的手機(jī)號(hào)段進(jìn)行分割如user183、user150撵渡、user153融柬、user189等，每個(gè)號(hào)段就是一張表
用戶表也可以根據(jù)用戶的id進(jìn)行分割趋距，加入分3張表user0,user1,user2粒氧，如果用戶的id%3=0就查詢user0表，
如果用戶的id%3=1就查詢user1表對(duì)于訂單表可以按照訂單的時(shí)間進(jìn)行分表
讀寫(xiě)分離
實(shí)現(xiàn)MySQL讀寫(xiě)分離的前提是我們已經(jīng)將MySQL主從復(fù)制配置完畢节腐，讀寫(xiě)分離實(shí)現(xiàn)方式：
（1）配置多數(shù)據(jù)源外盯。
（2）使用mysql的proxy中間件代理工具。
主從復(fù)制的原理
MySQL的主從復(fù)制和讀寫(xiě)分離兩者有著緊密的聯(lián)系翼雀，首先要部署主從復(fù)制饱苟，只有主從復(fù)制完成了才能在此基礎(chǔ)上進(jìn)行數(shù)據(jù)的讀寫(xiě)分離。
讀寫(xiě)分離的原理
讀寫(xiě)分離就是只在主服務(wù)器上寫(xiě)锅纺，只在從服務(wù)器上讀±吲梗基本原理是讓主數(shù)據(jù)庫(kù)處理事務(wù)性查詢囤锉，而從服務(wù)器處理select查詢。數(shù)據(jù)庫(kù)復(fù)制被用來(lái)把事務(wù)性查詢導(dǎo)致的變更同步到從數(shù)據(jù)庫(kù)中护锤。
