MYSQL優(yōu)化主要分為以下四大方面：

設(shè)計(jì)：存儲(chǔ)引擎，字段類型弯菊，范式與逆范式

功能：索引，緩存止喷，分區(qū)分表禁舷。

架構(gòu)：主從復(fù)制，讀寫分離灭抑，負(fù)載均衡。

合理SQL：測(cè)試抵代，經(jīng)驗(yàn)腾节。

一、存儲(chǔ)引擎

在創(chuàng)建表的時(shí)候我們使用sql語句，Create table tableName () engine=myisam|innodb;

這里就指明了存儲(chǔ)引擎是myisam還是innodb案腺。存儲(chǔ)引擎是一種用來存儲(chǔ)MySQL中對(duì)象（記錄和索引）的一種特定的結(jié)構(gòu)（文件結(jié)構(gòu)）庆冕，處于MySQL服務(wù)器的最底層，直接存儲(chǔ)數(shù)據(jù)劈榨。導(dǎo)致上層的操作愧杯，依賴于存儲(chǔ)引擎的選擇。地位如下圖：

image

網(wǎng)絡(luò)接口層：與客戶端通信鞋既，比如傳輸數(shù)據(jù)等等。存儲(chǔ)引擎層：存儲(chǔ)數(shù)據(jù)的規(guī)則耍铜，方式邑闺。

本質(zhì)：存儲(chǔ)引擎就是特定的數(shù)據(jù)存儲(chǔ)格式（方案）。

（1）關(guān)于Innodb 和myisam的取舍：

Innodb ：數(shù)據(jù)完整性棕兼，并發(fā)性處理陡舅，擅長(zhǎng)更新，刪除伴挚。

myisam：高速查詢及插入靶衍。擅長(zhǎng)插入和查詢。

具體舉例：

那么對(duì)于微博項(xiàng)目來看茎芋，選擇哪一個(gè)存儲(chǔ)引擎呢颅眶？

a.微博主要是插入微博和查詢微博列表，較為適合MyISAM田弥；

b.微博在更新微博和刪除微博涛酗，要少的多，較為適合MyISAM偷厦；

c.對(duì)數(shù)據(jù)完整性的需求并沒有那么強(qiáng)烈商叹，比如用戶刪除微博，關(guān)聯(lián)的轉(zhuǎn)播和評(píng)論并不要求都做相應(yīng)的行為只泼，較為適合MyISAM剖笙；

那么對(duì)于記賬財(cái)務(wù)系統(tǒng)，選擇哪一款存儲(chǔ)引擎呢请唱？

a.財(cái)務(wù)系統(tǒng)除了讀取和插入弥咪，經(jīng)常要進(jìn)行數(shù)據(jù)的修改和刪除，較為適合InnoDB籍滴；

b.在進(jìn)行財(cái)務(wù)變更的時(shí)候酪夷，如果失敗需要回滾必須用到事務(wù)怀浆，較為適合InnoDB皮钠；

c.每個(gè)用戶的財(cái)務(wù)數(shù)據(jù)完整性和同步性非常重要，需要外鍵支持恭陡，否則財(cái)務(wù)將會(huì)混亂勋功，較為適合InnoDB坦报。

二库说、字段類型選擇

字段類型應(yīng)該要滿足需求，盡量要滿足以下需求片择。

盡可能星钡摹（占用存儲(chǔ)空間少）、盡可能定長(zhǎng)（占用存儲(chǔ)空間固定）字管、盡可能使用整數(shù)啰挪。

1.列類型之?dāng)?shù)值

（1）整型

MySQL數(shù)據(jù)庫支持五種整型類型，包括：TINYINT嘲叔、SMALLINT亡呵、MEDIUMINT、INT和BIGINT五種硫戈。

五種整型的適用場(chǎng)景：

TINYINT锰什，年齡，包含在0~255之間丁逝；

SMALLINT汁胆，端口號(hào)，包含在0~65535之間霜幼；

MEDIUMINT嫩码，中小型網(wǎng)站注冊(cè)會(huì)員，1600萬夠用罪既；

INT谢谦，身份證編號(hào)，42億可以用很久萝衩；

BIGINT回挽，Twitter微博量，幾百億

（2）浮點(diǎn)型(非精確)

MySQL數(shù)據(jù)庫支持兩種浮點(diǎn)類型：FLOAT(單精度)和DOUBLE(雙精度)兩種

（3）定點(diǎn)型(精確)

浮點(diǎn)型由于內(nèi)部的存儲(chǔ)方式是數(shù)值猩谊，導(dǎo)致它在一定程度上取得的是近似值而非精確值千劈。如果使用定點(diǎn)型，那么就可以精確取得小數(shù)部分牌捷，因?yàn)樗鼉?nèi)部存儲(chǔ)方式是字符串形式墙牌。

2.列類型之日期

MySQL數(shù)據(jù)庫中有五個(gè)可用的日期時(shí)間數(shù)據(jù)類型，分別為：DATE暗甥、DATETIME喜滨、TIME、YEAR撤防、TIMESTAMP虽风。

3.列類型之字符

字符集校對(duì)規(guī)則utf8_general_ci表示校對(duì)時(shí)不區(qū)分大小寫，相對(duì)的cs表示區(qū)分大小寫。還有一個(gè)bin結(jié)尾的是字節(jié)比較辜膝。而general是地區(qū)名无牵，這里是通用，utf8表示編碼厂抖。如果是gbk茎毁，可以使用gbk_chinese_ci，如果是utf8則用utf8_general忱辅。MySQL提供了多種對(duì)字符數(shù)據(jù)的存儲(chǔ)類型七蜘，包括：CHAR、VARCHAR墙懂、VARBINARY崔梗、BLOB、TEXT垒在、ENUM和SET等多種字符類型。

綜上：短文本定長(zhǎng)用char扔亥，變長(zhǎng)用varchar场躯，長(zhǎng)文本用text

三、范式與逆范式

為了建立冗余較小旅挤、結(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儒搭，原子性

第二范式2NF，消除部分依賴

第三范式3NF芙贫，消除傳遞依賴

1搂鲫、范式

（1）第一范式：具有原子性，確保每列保持原子性磺平。

第一范式是最基本的范式魂仍。如果數(shù)據(jù)庫表中的所有字段值都是不可分解的原子值，就說明該數(shù)據(jù)庫表滿足了第一范式拣挪。第一范式的合理遵循需要根據(jù)系統(tǒng)的實(shí)際需求來定擦酌。比如某些數(shù)據(jù)庫系統(tǒng)中需要用到“地址”這個(gè)屬性本來直接將“地址”屬性設(shè)計(jì)成一個(gè)數(shù)據(jù)庫表的字段就行。但是如果系統(tǒng)經(jīng)常會(huì)訪問“地址”屬性中的“城市”部分菠劝，那么就非要將“地址”這個(gè)屬性重新拆分為省份赊舶、城市、詳細(xì)地址等多個(gè)部分進(jìn)行存儲(chǔ)，這樣在對(duì)地址中某一部分操作的時(shí)候?qū)⒎浅７奖憔忉＿@樣設(shè)計(jì)才算滿足了數(shù)據(jù)庫的第一范式介袜。
（2）第二范式：主鍵列與非主鍵列遵循完全函數(shù)依賴關(guān)系，確保表中的每列都和主鍵相關(guān)出吹。

第二范式在第一范式的基礎(chǔ)之上更進(jìn)一層遇伞。第二范式需要確保數(shù)據(jù)庫表中的每一列都和主鍵相關(guān)，而不能只與主鍵的某一部分相關(guān)（主要針對(duì)聯(lián)合主鍵而言）捶牢。也就是說在一個(gè)數(shù)據(jù)庫表中鸠珠，一個(gè)表中只能保存一種數(shù)據(jù)，不可以把多種數(shù)據(jù)保存在同一張數(shù)據(jù)庫表中秋麸。
（3）第三范式：非主鍵列之間沒有傳遞函數(shù)依賴關(guān)系索引渐排，確保每列都和主鍵列直接相關(guān),而不是間接相關(guān)。

所謂傳遞函數(shù)依賴灸蟆，指的是如果存在"A→B→C"的決定關(guān)系驯耻，則C傳遞函數(shù)依賴于A。因此炒考，滿足第三范式的數(shù)據(jù)庫表應(yīng)該不存在如下依賴關(guān)系：

關(guān)鍵字段→非關(guān)鍵字段x→非關(guān)鍵字段y

比如在設(shè)計(jì)一個(gè)訂單數(shù)據(jù)表的時(shí)候可缚，可以將客戶編號(hào)作為一個(gè)外鍵和訂單表建立相應(yīng)的關(guān)系。而不可以在訂單表中添加關(guān)于客戶其它信息（比如姓名斋枢、所屬公司等）的字段帘靡。

先滿足第一范式，再滿足第二范式瓤帚，才能滿足第三范式描姚。

2、逆范式

逆范式是指打破范式戈次，通過增加冗余或重復(fù)的數(shù)據(jù)來提高數(shù)據(jù)庫的性能轩勘。

示例： 假如有一個(gè)商品表Goods：

字段有Goods_id(商品表), goods_name(商品名稱), cat_id(所屬類別的id)。

還有一個(gè)分類表Category：

字段有Cat_id(類別id), cat_name(類別名稱)怯邪。

現(xiàn)在要查詢類別id為3的商品的數(shù)量赃阀，例如分類列表查詢：

分類ID 分類名稱 商品數(shù)量

四、索引

1．索引概述

利用關(guān)鍵字擎颖，就是記錄的部分?jǐn)?shù)據(jù)（某個(gè)字段榛斯，某些字段，某個(gè)字段的一部分）搂捧，建立與記錄位置的對(duì)應(yīng)關(guān)系驮俗，就是索引。索引的關(guān)鍵字一定是排序的允跑。索引本質(zhì)上是表字段的有序子集王凑，它是提高查詢速度最有效的方法搪柑。一個(gè)沒有建立任何索引的表，就相當(dāng)于一本沒有目錄的書索烹，在每次查詢時(shí)就會(huì)進(jìn)行全表掃描工碾，這樣會(huì)導(dǎo)致查詢效率極低、速度也極慢百姓。如果建立索引渊额，那么就好比一本添加的目錄，通過目錄的指引垒拢，迅速翻閱到指定的章節(jié)旬迹，提升的查詢性能，節(jié)約了查詢資源求类。

2．索引種類

從索引的定義方式和用途中來看：主鍵索引奔垦，唯一索引，普通索引尸疆，全文索引椿猎。

無論任何類型，都是通過建立關(guān)鍵字與位置的對(duì)應(yīng)關(guān)系來實(shí)現(xiàn)的寿弱。索引是通過關(guān)鍵字找對(duì)應(yīng)的記錄的地址犯眠。

以上類型的差異：對(duì)索引關(guān)鍵字的要求不同。

關(guān)鍵字：記錄的部分?jǐn)?shù)據(jù)（某個(gè)字段脖捻，某些字段，某個(gè)字段的一部分）兆衅。

普通索引,index：對(duì)關(guān)鍵字沒有要求地沮。

唯一索引,unique index：要求關(guān)鍵字不能重復(fù)。同時(shí)增加唯一約束羡亩。

主鍵索引,primary key：要求關(guān)鍵字不能重復(fù)摩疑，也不能為NULL。同時(shí)增加主鍵約束畏铆。

全文索引,fulltext key：關(guān)鍵字的來源不是所有字段的數(shù)據(jù)雷袋，而是從字段中提取的特別關(guān)鍵詞。

關(guān)鍵字含義：可以是某個(gè)字段辞居，也可以是某些字段楷怒。如果一個(gè)索引通過在多個(gè)字段上提取的關(guān)鍵字，稱之為復(fù)合索引瓦灶。 命令:alter table exp add index (field1, field2);

PS：這里主鍵索引和唯一索引的區(qū)別在于：主鍵索引不能為空值鸠删，唯一索引允許空值；主鍵索引在一張表內(nèi)只能創(chuàng)建一個(gè)贼陶，唯一索引可以創(chuàng)建多個(gè)刃泡。主鍵索引肯定是唯一索引巧娱，但唯一索引不一定是主鍵索引。

3.索引原則
a烘贴、不要過度索引禁添。索引越多，占用空間越大桨踪，反而性能變慢老翘；

b.只對(duì)WHERE子句中頻繁使用的建立索引；

c.盡可能使用唯一索引馒闷，重復(fù)值越少酪捡，索引效果越強(qiáng)；

d.使用短索引纳账，如果char(255)太大逛薇，應(yīng)該給它指定一個(gè)前綴長(zhǎng)度，大部分情況下前10位或20位值基本是唯一的疏虫，那么就不要對(duì)整個(gè)列進(jìn)行索引永罚；

e.充分利用左前綴，這是針對(duì)復(fù)合索引卧秘，因?yàn)閃HERE語句如果有AND并列呢袱，只能識(shí)別一個(gè)索引(獲取記錄最少的那個(gè))，索引需要使用復(fù)合索引翅敌，那么應(yīng)該將WHERE最頻繁的放置在左邊羞福。

f.索引存在，如果沒有滿足使用原則蚯涮，也會(huì)導(dǎo)致索引無效：

4.索引的使用場(chǎng)景

5.前綴索引

6.全文索引

7.索引結(jié)構(gòu)-b-tree介紹

Hash治专、B-Tree（B樹）兩種數(shù)據(jù)結(jié)構(gòu)。指的是mysql存儲(chǔ)索引所采用的數(shù)據(jù)結(jié)構(gòu)遭顶。其中张峰，用戶所維護(hù)的所有的索引結(jié)構(gòu) B-Tree結(jié)構(gòu)。

B-Tree的結(jié)構(gòu)如下：

每個(gè)節(jié)點(diǎn)棒旗，存儲(chǔ)多個(gè)關(guān)鍵字喘批。關(guān)鍵字也會(huì)對(duì)應(yīng)記錄地址

以上設(shè)計(jì)為了解決一次性磁盤IO開銷，可以讀取到更多的關(guān)鍵字?jǐn)?shù)量铣揉。

每個(gè)關(guān)鍵字之間饶深，存在子節(jié)點(diǎn)指針：

image

如果是復(fù)合索引：

關(guān)鍵字的排序先排左側(cè)字段，在左側(cè)字段相同的情況下逛拱，再排序右側(cè)字段：

8.聚集索引(聚簇索引)

B+Tree(B-Tree的變種)

在innodb的存儲(chǔ)引擎上粥喜，主鍵索引是與數(shù)據(jù)記錄存儲(chǔ)在一起的（聚簇在一起的）。

image

帶來的問題：

Innodb的其他索引橘券，非主鍵索引（二級(jí)索引）：

關(guān)鍵字對(duì)應(yīng)的不再是記錄的地址额湘，而是記錄的主鍵卿吐。

image

可見，檢索需要二次檢索锋华。先檢索到主鍵ID嗡官，再檢索記錄。

五毯焕、查詢緩存query_cache

將select的結(jié)果衍腥，存取起來共二次使用的緩存區(qū)域：

image

MySQL提供的緩存區(qū)：

未開啟前：

image

兩次查詢時(shí)間消耗一致。

開啟查詢緩存纳猫，通過變量控制：

image

開啟并設(shè)置大衅畔獭：

image

image

再次執(zhí)行查詢：

image

可見，第二次查詢芜辕，使用了開啟的緩存尚骄！

注意事項(xiàng)：查詢緩存存在判斷是嚴(yán)格依賴于select語句本身的：嚴(yán)格保證SQL一致。

image

如果查詢時(shí)包含動(dòng)態(tài)數(shù)據(jù)侵续，則不能被緩存倔丈。

image

一旦開啟查詢緩存，MySQL會(huì)將所有可以被緩存的select語句都緩存状蜗。如果存在不想使用緩存的SQL執(zhí)行需五，則可以使用 SQL_NO_CACHE語法提示達(dá)到目的：

image

注意：這里的緩存僅當(dāng)數(shù)據(jù)表的記錄改變時(shí)，緩存才會(huì)被刪除轧坎。而不是依靠過期時(shí)間的宏邮。

六、分區(qū)分表

日常開發(fā)中我們經(jīng)常會(huì)遇到大表的情況缸血，所謂的大表是指存儲(chǔ)了百萬級(jí)乃至千萬級(jí)條記錄的表蜜氨。這樣的表過于龐大，導(dǎo)致數(shù)據(jù)庫在查詢和插入的時(shí)候耗時(shí)太長(zhǎng)属百，性能低下记劝，如果涉及聯(lián)合查詢的情況变姨，性能會(huì)更加糟糕族扰。分表和表分區(qū)的目的就是減少數(shù)據(jù)庫的負(fù)擔(dān)，提高數(shù)據(jù)庫的效率定欧，通常點(diǎn)來講就是提高表的增刪改查效率渔呵。

分區(qū)，partition砍鸠，分區(qū)是將數(shù)據(jù)分段劃分在多個(gè)位置存放扩氢，可以是同一塊磁盤也可以在不同的機(jī)器。分區(qū)后爷辱，表面上還是一張表录豺，但數(shù)據(jù)散列到多個(gè)位置了朦肘。app讀寫的時(shí)候操作的還是大表名字，db自動(dòng)去組織分區(qū)的數(shù)據(jù)双饥。

image

其實(shí)每個(gè)分區(qū)媒抠，就是獨(dú)立的表。都要存儲(chǔ)該分區(qū)數(shù)據(jù)的數(shù)據(jù)咏花，索引等信息趴生。

創(chuàng)建分區(qū)：在創(chuàng)建表時(shí)，指定分區(qū)的選項(xiàng)：

Create table table_name (定義)

Partition by 分區(qū)算法 (參數(shù)) 分區(qū)選項(xiàng)昏翰。

例如：Partition by key (id) partitions 5;

image

采用key取余算法苍匆，根據(jù)id的值進(jìn)行取余，即對(duì)5取余棚菊，然后分配到5個(gè)區(qū)里浸踩。

分區(qū)結(jié)果如下：myisam下

image

Innodb下

image

Tip：分區(qū)與存儲(chǔ)引擎無關(guān)，是MySQL邏輯層完成的窍株。

可以通過變量查看當(dāng)前mysql是否支持分區(qū)：

image

1.分區(qū)算法

MySQL提供4種分區(qū)算法：取余：Key民轴，hash 條件：List，range 球订。

參與分區(qū)的參數(shù)字段需要為主鍵的一部分后裸。

（1）KEY – 取余 ，按照某個(gè)字段進(jìn)行取余

image

分成5個(gè)區(qū)冒滩，就是對(duì)5取余微驶。將id對(duì)5取余。

（2）Hash – 取余开睡，按照某個(gè)表達(dá)式的值進(jìn)行取余

示例：學(xué)生表分區(qū)因苹，按照生日的月份，劃分到12個(gè)表中篇恒。

image

注意：Key扶檐，hash都是取余算法，要求分區(qū)參數(shù)（括號(hào)里的）胁艰，返回的數(shù)據(jù)必須為整數(shù)款筑。

（3）List – 條件 – 列表，需要指定的每個(gè)分區(qū)數(shù)據(jù)的存儲(chǔ)條件腾么。

示例：按照生日中的月份奈梳，分成春夏秋冬四個(gè)分區(qū)。

image

image

List解虱，條件依賴的數(shù)據(jù)是列表形式攘须。

（4）Range - 條件 – 范圍， 條件依賴的數(shù)據(jù)是一個(gè)條件表達(dá)式殴泰。

邏輯：按照生日的年份分成不同的年齡段于宙。

image

image

2.分區(qū)的管理與選擇

（1）取余：key浮驳，hash

增加分區(qū)數(shù)量： add partition partitions N

image

減少分區(qū)數(shù)量： COALESCE partition N

image

采用取余算法的分區(qū)數(shù)量的修改，不會(huì)導(dǎo)致已有分區(qū)數(shù)據(jù)的丟失捞魁，因?yàn)闀?huì)重新分配數(shù)據(jù)到新的分區(qū)抹恳。

（2）條件：list，range

添加分區(qū)

image

刪除分區(qū)：

Drop partition partition_name;

image

注意：刪除條件算法的分區(qū)署驻，會(huì)導(dǎo)致分區(qū)數(shù)據(jù)丟失奋献。添加分區(qū)不會(huì)。

（3）選擇分區(qū)算法

平均分配：就按照主鍵進(jìn)行key(primary key)即可(非常常見)

按照某種業(yè)務(wù)邏輯分區(qū)：選擇那種最容易被篩選的字段旺上，整數(shù)型

3.分表

分表是將一個(gè)大表按照一定的規(guī)則分解成多張具有獨(dú)立存儲(chǔ)空間的實(shí)體表瓶蚂，我們可以稱為子表，每個(gè)表都對(duì)應(yīng)三個(gè)文件宣吱，MYD數(shù)據(jù)文件窃这，.MYI索引文件，.frm表結(jié)構(gòu)文件征候。這些子表可以分布在同一塊磁盤上杭攻，也可以在不同的機(jī)器上。app讀寫的時(shí)候根據(jù)事先定義好的規(guī)則得到對(duì)應(yīng)的子表名疤坝，然后去操作它兆解。分表技術(shù)是比較麻煩的，需要手動(dòng)去創(chuàng)建子表跑揉，app服務(wù)端讀寫時(shí)候需要計(jì)算子表名锅睛。采用merge好一些，但也要?jiǎng)?chuàng)建子表和配置子表間的union關(guān)系历谍。（需要手動(dòng)分表）

分表是分區(qū)之前用的现拒，MYSQL5.1后，就開始用分區(qū)代替分表了望侈。分表很少用了印蔬。

（1）水平分表

創(chuàng)建結(jié)構(gòu)相同的N個(gè)表；

image

再創(chuàng)建用于管理學(xué)生ID的表student_id：(該表是為了提供自增的ID)

image

PHP客戶端邏輯：

image

Merge脱衙，mrg_myisam

image

是MySQL提供一個(gè)可以將多個(gè)結(jié)構(gòu)相同的myisam表侥猬，合并到一起的存儲(chǔ)引擎：

image

（2）垂直分表

一張表中存在多個(gè)字段。這些字段可以分為常用字段和非常用字段岂丘，為了提高查表速度陵究，我們可以把這兩類字段分開來存儲(chǔ)眠饮。主要目的奥帘，減少每條記錄的長(zhǎng)度。

通常我們按以下原則進(jìn)行垂直拆分:把不常用的字段單獨(dú)放在一張表;把text仪召，blog等大字段拆分出來放在附表中;經(jīng)常組合查詢的列放在一張表中;

例如學(xué)生表可以分成：

基礎(chǔ)表(Student_base)和額外表(Student_extra)寨蹋，兩張表中記錄為1:1的關(guān)系松蒜。

基礎(chǔ)信息表Student_base

Id name age

額外信息表Student_extra

Id 籍貫 政治面貌

七、服務(wù)器架構(gòu)介紹

服務(wù)器架構(gòu)已旧，不僅僅是用一臺(tái)MySQL

主從復(fù)制：

Mysql服務(wù)器內(nèi)部支持復(fù)制功能秸苗，僅僅需要通過配置完成下面的拓?fù)浣Y(jié)構(gòu)。一主多從典型結(jié)果：主服務(wù)器負(fù)責(zé)寫數(shù)據(jù)运褪。從服務(wù)器負(fù)責(zé)讀數(shù)據(jù)惊楼。復(fù)制功能mysql會(huì)自帶。

image

讀寫分離秸讹，負(fù)載均衡：

php不再操作MYSQL數(shù)據(jù)庫服務(wù)器檀咙，而是去操作讀寫分離、負(fù)載均衡服務(wù)器璃诀，只要服務(wù)器安裝了mysql proxy或Ameoba軟件就可以實(shí)現(xiàn)讀寫分離和負(fù)載均衡弧可，讀寫分離是指該服務(wù)器會(huì)判斷客戶端的操作是讀還是寫，從而選擇操作mysql主服務(wù)器還是從服務(wù)器劣欢。負(fù)載均衡算法是指棕诵，客戶端讀操作時(shí)，該服務(wù)器會(huì)根據(jù)取余算法去選擇一臺(tái)從服務(wù)器凿将。

image

上面的架構(gòu)可以提升整體服務(wù)器的效率校套，高性能。

同時(shí)牧抵，服務(wù)器架構(gòu)需要保證搔确，高可用（穩(wěn)定），7x24不宕機(jī)灭忠。因此需要增加一些冗余服務(wù)器以便備用膳算。時(shí)時(shí)檢測(cè)正在用的服務(wù)器。

image

八弛作、SQL優(yōu)化

1.對(duì)于并發(fā)性的SQL

少用（不用）多表操作（子查詢涕蜂，聯(lián)合查詢），而是將復(fù)雜的SQL拆分多次執(zhí)行映琳。如果查詢很原子（很谢丁），會(huì)增加查詢緩存的利用率萨西。

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

多條 insert或者Load data into table（從文件里載入數(shù)據(jù)到表里）

建議有鹿，先關(guān)閉約束及索引，完成數(shù)據(jù)插入谎脯，再重新生成索引及約束葱跋。

針對(duì)于myisam，步驟：

Alter table table_name disable keys; 禁用索引約束

大量的插入

Alter table table_name enable keys; 啟用

針對(duì)innodb，步驟：

Drop index, drop constraint 刪除索引及約束娱俺，要保留主鍵

Begin transaction|set autocommit=0; 開啟事務(wù)稍味，不讓他自動(dòng)提交

[數(shù)據(jù)本身已經(jīng)按照主鍵值排序]

大量的插入

Commit;

Add index, add constraint

3.分頁

分頁假定Limit offset, size; size = 10;

|

Page

|

offset

|
|

5

|

40, 10

|
|

50

|

490, 10

|
|

5000

|

4990, 10

|
|

500000

|

499990, 10

|

Limit 的使用，會(huì)大大提升無效數(shù)據(jù)的檢索（被跳過）荠卷，因?yàn)槭窍葯z索模庐，檢索會(huì)檢索全部，再取得想要的油宜。好的做法是使用條件等過濾方式掂碱，將檢索到的數(shù)據(jù)盡可能精確定位到需要的數(shù)據(jù)上。

4.隨機(jī)選一些數(shù)據(jù)慎冤，不要使用Order by Rand()

image

上面的查詢顶吮，會(huì)導(dǎo)致每條記錄都執(zhí)行rand()，成本很高粪薛！

建議悴了，通過mt_rand()，先確定的隨機(jī)主鍵违寿，再從數(shù)據(jù)表中獲取數(shù)據(jù)湃交。

九、慢查詢?nèi)罩镜氖褂?/h1>

定位執(zhí)行較慢的查詢語句方案藤巢。

show variables like 'slow_query%'; show variables like '%long_query%';

image

Slow_query_log = 0|1

Long_query_time = N 超過該時(shí)間臨界點(diǎn)搞莺，就為慢查詢。

開啟日志

set global slow_query_log=1; set long_query_time=0.5掂咒；

image

執(zhí)行SQL才沧，查看：

image