MySQL性能優(yōu)化包括表的優(yōu)化與列類型選擇，表的優(yōu)化可以細(xì)分為什么？ 1、定長與變長分離；2、常用字段與不常用字段要分離； 3、在1對(duì)多，需要關(guān)聯(lián)統(tǒng)計(jì)的字段上添加冗余字段。

一、表的優(yōu)化與列類型選擇

表的優(yōu)化：

1、定長與變長分離

如 id int,占4個(gè)字節(jié)，char(4)占4個(gè)字符長度，也是定長，time即每一單元值占的字節(jié)是固定的。

核心且常用字段，宜建成定長，放在一張表。

而varchar,text,blob這種變長字段，適合單放一張表，用主鍵與核心表關(guān)聯(lián)起來。

2、常用字段與不常用字段要分離

需要結(jié)合網(wǎng)站具體的業(yè)務(wù)來分析，分析字段的查詢場景，查詢頻率低的字段，單拆出來。

3、在1對(duì)多，需要關(guān)聯(lián)統(tǒng)計(jì)的字段上添加冗余字段。

看如下的效果：

每個(gè)版塊里，有N條帖子，在首頁顯示了版塊信息和版塊下的帖子數(shù)。

這是如何做的

如果board表只有前2列，則需要取出版塊后，

再查post表，select count(*) from post group by board_id，得出每個(gè)版塊的帖子數(shù)。

二、列類型選擇

1、字段類型優(yōu)先級(jí)

整型>date

time>enum

char>varchar>blob,text

整型：定長，沒有國家/地區(qū)之分，沒有字符集的差異。比如：

tinyint 1,2,3,4,5 <--> char(1) a,b,c,d,e

從空間上，都占1個(gè)字節(jié)，但是 order by 排序，前者快。原因，或者需要考慮字符集與校對(duì)集（就是排序規(guī)則）；

time定長，運(yùn)算快，節(jié)省空間。考慮時(shí)區(qū)，寫sql時(shí)不方便 where > `2018-08-08`;

enum,能起到約束的目的，內(nèi)部用整型來存儲(chǔ)，但與cahr聯(lián)查時(shí)，內(nèi)部要經(jīng)歷串與值的轉(zhuǎn)化；

char定長，考慮字符集和（排序）校對(duì)集；

varchar不定長，要考慮字符集的轉(zhuǎn)換與排序時(shí)的校對(duì)集，速度慢；

text/blob 無法使用內(nèi)存臨時(shí)表（排序等操作只能在磁盤上進(jìn)行）

附：關(guān)于date/time的選擇，大師的明確意見，直接選 int unsgined not null,存儲(chǔ)時(shí)間戳。

例如：

性別：以u(píng)tf8為例

char(1) ,3個(gè)字長字節(jié)

enum('男','女')； 內(nèi)部轉(zhuǎn)成數(shù)字來存，多一個(gè)轉(zhuǎn)換過程

tinyint()， 定長1個(gè)字節(jié)

2、夠用就行，不要慷慨（如 smallint varchar(N)）

原因：大的字節(jié)浪費(fèi)內(nèi)存，影響速度。

以年齡為例 tinyint unsigned not null，可以存儲(chǔ)255歲，足夠。用int浪費(fèi)了3個(gè)字節(jié)；

以varchar(10),varchar(300)存儲(chǔ)的內(nèi)容相同，但在表聯(lián)查時(shí)varchar(300)要花更多內(nèi)存。

3、盡量避免用NULL()

原因：NULL不利于索引，要用特殊的字符來標(biāo)注。

在磁盤上占據(jù)的空間其實(shí)更大（MySQL5.5已對(duì)null做的改進(jìn)，但查詢?nèi)允遣槐悖?/p>

三、索引優(yōu)化策略

1、索引類型

1.1 B-tree索引

名叫btree索引，大的方面看，都用的平衡樹，但具體的實(shí)現(xiàn)上，各引擎稍有不同，比如，嚴(yán)格的說，NDB引擎，使用的是T-tree.

但抽象一下 B-tree系統(tǒng)，可理解為“排好序的快速查詢結(jié)構(gòu)”。

1.2 hash索引

在memory表里默認(rèn)是hash索引，hash的理論查詢時(shí)間復(fù)雜度為O(1)。

疑問：既然hash的查找如此高效，為什么不都用hash索引？

回答：

1、hash函數(shù)計(jì)算后的結(jié)果，是隨機(jī)的，如果是在磁盤上放置數(shù)據(jù)，以主鍵為id為例，那么隨著id的增長，id對(duì)應(yīng)的行，在磁盤上隨機(jī)放置。

2、無法對(duì)范圍查詢進(jìn)行優(yōu)化。

3、無法利用前綴索引，比如在btree中，field列的值“helloworld”,并加索引查詢 x=helloworld自然可以利用索引，x=hello也可以利用索引（左前綴索引）。

4、排序也無法優(yōu)化。

5、必須回行，就是說通過索引拿到數(shù)據(jù)位置，必須回到表中取數(shù)據(jù)。

2、btree索引的常見誤區(qū)

2.1 在where條件常用的列上加索引，例如：

where cat_id = 3 and price>100;查詢第三個(gè)欄目，100元以上的商品。

誤區(qū)：cat_id 上和price上都加上索引。

錯(cuò)：只能用上cat_id 或 price索引，因?yàn)槭仟?dú)立的索引，同時(shí)只能用一個(gè)。

2.2 在多列上建立索引后（聯(lián)合索引），查詢哪個(gè)列，索引都會(huì)將發(fā)揮作用

誤區(qū)：多列索引上，索引發(fā)揮作用，需要滿足左前綴要求。

以 index(a,b,c) 為例，（注意和順序有關(guān)）

四、索引實(shí)驗(yàn)

例如：select * from t4 where c1=3 and c2 = 4 and c4>5 and c3=2;

用到了哪些索引：

explain select * from t4 where c1=3 and c2 = 4 and c4>5 and c3=2 \G

如下：

注：（key_len : 4 ）

五、聚簇索引與非聚簇索引

Myisam與innodb引擎，索引文件的異同

Myisam：由news.myd和new.myi兩個(gè)文件，索引文件和數(shù)據(jù)文件是分開的，叫非聚簇索引。主索引和次索引都指向物理行（磁盤的位置）

innodb：索引和數(shù)據(jù)是聚在一起的，所以是聚簇索引。innodb的主索引文件上直接存放該行數(shù)據(jù)，次索引指向?qū)χ麈I索引的引用。

注意：innodb來說：

1、主鍵索引 即存放索引值，又在葉子中存儲(chǔ)行的數(shù)據(jù)。

2、如果沒有主鍵（primary key），則會(huì)unique key做主鍵。

3、如果沒有unique，則系統(tǒng)生成一個(gè)內(nèi)部的rowid做主鍵。

4、像innodb中，主鍵的索引結(jié)構(gòu)中，即存儲(chǔ)了主鍵值又存儲(chǔ)了行數(shù)據(jù)，這種結(jié)構(gòu)稱為聚簇索引。

聚簇索引

優(yōu)勢(shì)：根據(jù)主鍵查詢條目比較少時(shí)，不用回行（數(shù)據(jù)就在主鍵節(jié)點(diǎn)下）

劣勢(shì)：如果碰到不規(guī)則數(shù)據(jù)插入時(shí)，造成頻繁的頁分裂

相關(guān)文章：

Mysql 性能優(yōu)化

相關(guān)視頻：

MySQL優(yōu)化視頻教程

以上就是MySQL大數(shù)據(jù)查詢性能優(yōu)化教程（圖）的詳細(xì)內(nèi)容，更多請(qǐng)關(guān)注php中文網(wǎng)其它相關(guān)文章！