在面試的環(huán)節(jié)中，面試官問到：你是如何設(shè)計(jì)你的表結(jié)構(gòu)的，畫一下E-R圖？接著又繼續(xù)深挖，如果有慢查詢，你是如何優(yōu)化你的sql的？

成都創(chuàng)新互聯(lián)公司專注于武勝企業(yè)網(wǎng)站建設(shè),自適應(yīng)網(wǎng)站建設(shè),成都商城網(wǎng)站開發(fā)。武勝網(wǎng)站建設(shè)公司,為武勝等地區(qū)提供建站服務(wù)。全流程按需定制，專業(yè)設(shè)計(jì)，全程項(xiàng)目跟蹤，成都創(chuàng)新互聯(lián)公司專業(yè)和態(tài)度為您提供的服務(wù)

今天，我就來和大家講講要怎么回答這道問題。首先，我們要穩(wěn)住不要慌，自己是自己親手做的項(xiàng)目，第一個(gè)問題應(yīng)該都不大，第二個(gè)問題就需要在面試之前做好充分的準(zhǔn)備啦…

在回答問題之前先要了解查詢的流程：查詢是由一系列的子任務(wù)組成的，包括從客戶端，到服務(wù)器，然后在服務(wù)器上進(jìn)行解析，生成執(zhí)行計(jì)劃，執(zhí)行，并返回結(jié)果給客戶端。其中“執(zhí)行”可以認(rèn)為是整個(gè)生命周期中最重要的階段，這其中包括了大量為了檢索數(shù)據(jù)到存儲(chǔ)引擎的調(diào)用以及調(diào)用后的數(shù)據(jù)處理，包括排序、分組。為了完成這些任務(wù)，查詢需要在不同的地方花費(fèi)時(shí)間，包括網(wǎng)絡(luò)，CPU計(jì)算，生成統(tǒng)計(jì)信息和執(zhí)行計(jì)劃、鎖等待操作。進(jìn)行一些不必要的額外操作時(shí)或者某些重復(fù)執(zhí)行某些額外操作會(huì)消耗大量的時(shí)間。

查詢性能低下最基本的原因是訪問的數(shù)據(jù)太多。某些查詢可能不可避免地需要篩選大量的數(shù)據(jù)，大部分性能低下的查詢都可以通過減少訪問的數(shù)據(jù)量的方式進(jìn)行優(yōu)化。對(duì)于低效的查詢，可以通過以下兩個(gè)步驟來分析：

確認(rèn)應(yīng)用程序是否在檢索大量超過需要的數(shù)據(jù)。

確認(rèn)MySQL服務(wù)器是否在分析大量超過需要的數(shù)據(jù)行。

上面的都是理論，在實(shí)踐中，MySQL的優(yōu)化主要涉及SQL語句及索引的優(yōu)化、數(shù)據(jù)表結(jié)構(gòu)的優(yōu)化這三個(gè)方面。

SQL語句的優(yōu)化：
1、少用子查詢

盡量少用子查詢，因?yàn)樽硬樵儠?huì)產(chǎn)生臨時(shí)表；除非像count(*)臨時(shí)表很小的。

2、少用SELECT *

每次看到SELECT *都需要用懷疑的眼光審視，是否真的需要返回全部的列？取出全部的列，會(huì)讓優(yōu)化器無法完成索引覆蓋掃描這類優(yōu)化，還會(huì)為服務(wù)器帶來額外的I/O、內(nèi)存和CPU的消耗。

3、查詢必要的記錄

一個(gè)常見的錯(cuò)誤是常常會(huì)誤以為MySQL只會(huì)返回需要的數(shù)據(jù)，實(shí)際上MySQL卻是先返回全部結(jié)果集再進(jìn)行計(jì)算，建議在查詢后面加上LIMIT。

4、不要重復(fù)查詢相同的數(shù)據(jù)

不斷執(zhí)行相同的查詢，然后每次都會(huì)返回完全相同的數(shù)據(jù)。可以采用的方案是初次查詢的時(shí)候?qū)⑦@個(gè)數(shù)據(jù)緩存起來，需要的時(shí)候從緩存中取出，這樣性能顯然會(huì)更好。

5、COUNT查詢優(yōu)化

COUNT()聚合函數(shù)的作用：統(tǒng)計(jì)某一個(gè)列值的數(shù)量，也可以統(tǒng)計(jì)行數(shù)。需要注意的是統(tǒng)計(jì)列值時(shí)要求列值是非空的(不統(tǒng)計(jì)NULL)，COUNT()查詢盡可能少的行。

舉個(gè)例子：如果我們直接查 id>100 的記錄，涉及到的有兩千多萬行記錄掃描。但是由于COUNT()特性，我們可以用 count() - (id<100)的做法，這樣掃描的行就只有100行了。

6、Where子句中，where表之間的連接必須寫在其他Where條件之前，那些可以過濾掉最大數(shù)量記錄的條件必須寫在Where子句的末尾.HAVING最后。

7、用EXISTS替代IN、用NOT EXISTS替代NOT IN。

8、避免在索引列上使用計(jì)算。

9、避免在索引列上使用IS NULL和IS NOT NULL。

10、對(duì)查詢進(jìn)行優(yōu)化，應(yīng)盡量避免全表掃描，首先應(yīng)考慮在 where 及 order by 涉及的列上建立索引。

11、應(yīng)盡量避免在 where 子句中對(duì)字段進(jìn)行 null 值判斷，否則將導(dǎo)致引擎放棄使用索引而進(jìn)行全表掃描。

12、應(yīng)盡量避免在 where 子句中對(duì)字段進(jìn)行表達(dá)式操作，這將導(dǎo)致引擎放棄使用索引而進(jìn)行全表掃描。

索引優(yōu)化
1、關(guān)聯(lián)查詢優(yōu)化

確保ON 或則USING 子句的列上有索引。創(chuàng)建索引時(shí)就要考慮關(guān)聯(lián)的順序，當(dāng)表A和表B用列c關(guān)聯(lián)的時(shí)候，如果優(yōu)化器關(guān)聯(lián)順序是B、A，就只需要在表A上建立索引，沒用的索引會(huì)占用存儲(chǔ)。

2、GROUP BY 和 DISTINCT優(yōu)化

GROUP BY 和 DISTINCT的優(yōu)化最有效的就是使用索引。所有對(duì)于分組的列一定要建立索引。比如：

select product, count(*) from orders group by product;

這樣的一個(gè)查詢，對(duì)product要建立索引。

3、LIMIT分頁優(yōu)化

進(jìn)行分頁操作時(shí)，通常都會(huì)通過偏移量來查詢某些數(shù)據(jù)。然后再加上解釋的order by，性能一般都不錯(cuò)。對(duì)于order by的列 一定要加上索引。但是對(duì)于limit 10000，10 這樣檢索目標(biāo)10條記錄必須先先查詢前面的10000條記錄。代價(jià)很高，這種時(shí)候優(yōu)化最簡(jiǎn)單辦法就是使用覆蓋索引。

注意索引失效的情況，

1)以“%”開頭的LIKE語句，模糊匹配

2)OR語句前后沒有同時(shí)使用索引

3)數(shù)據(jù)類型出現(xiàn)隱式轉(zhuǎn)化（如varchar不加單引號(hào)的話可能會(huì)自動(dòng)轉(zhuǎn)換為int型）

數(shù)據(jù)庫優(yōu)化
選擇優(yōu)化數(shù)據(jù)類型的幾條建議：

更小的通常更好，盡量使用可以正確存儲(chǔ)數(shù)據(jù)的最小數(shù)據(jù)類型，因?yàn)檎加酶俚拇疟P、內(nèi)存和CPU緩存。

簡(jiǎn)單最好，選擇整數(shù)而不是字符串，選擇MySQL內(nèi)建的類型而不是字符串來存儲(chǔ)時(shí)間和日期，使用整數(shù)來存儲(chǔ)IP地址。

盡量避免NULL，很多表都包含可為NULL的列，這是因?yàn)镹ULL是列的默認(rèn)值，需要指定列為NOT NULL。

整數(shù)類型數(shù)據(jù)一般用int，對(duì)于布爾類型的數(shù)據(jù)用tinyint，但是整數(shù)計(jì)算一般是使用64位的BIGINT整數(shù)。

在需要對(duì)小數(shù)進(jìn)行精確計(jì)算時(shí)，比如說存儲(chǔ)財(cái)務(wù)數(shù)據(jù)才使用DECIMAL（浮點(diǎn)存儲(chǔ)的float和double類型計(jì)算不精確），但是DECIMAL計(jì)算的代價(jià)很高，可以考慮使用BIGINT代替DECIMAL，將小數(shù)的位數(shù)乘以相應(yīng)的倍數(shù)即可。

varchar和char

當(dāng)需要存儲(chǔ)可變長(zhǎng)的字符串用varchar，比使用char存儲(chǔ)更節(jié)省空間，varchar使用1或者2個(gè)額外的字節(jié)來記錄長(zhǎng)度。至于用char來存儲(chǔ)適用于下列幾種情況，一是需要存儲(chǔ)很短的字符串時(shí)（存儲(chǔ)只有Y和N的值時(shí)），二是所有的值接近固定長(zhǎng)度（存儲(chǔ)MD5值），三是經(jīng)常需要變更的值。

BIT

在MySQL5.0之前，BIT是TINYINT的同義詞，在MySQL5.0以及更新的版本，是一個(gè)完全不同的數(shù)據(jù)類型。BIT類型的新行為：（1）可以使用BIT列在一列中存儲(chǔ)一個(gè)或者多個(gè)true/false值。MySQL把BIT當(dāng)做字符串類型，而不是數(shù)字類型。當(dāng)檢索BIT(1)的值時(shí)，結(jié)果是一個(gè)包含二進(jìn)制0或者1的字符串，而不是ASCII的“0”或“1”。

SET

如果需要保存很多的true/false值，可以考慮合并這些列到一個(gè)SET數(shù)據(jù)類型，它在MySQL內(nèi)部是一系列打包的位的集合來表示的。

使用枚舉代替常用的字符串類型，因?yàn)镸ySQL在存儲(chǔ)枚舉時(shí)非常緊湊，MySQL把每個(gè)枚舉的值保存為整數(shù)，并且在表的.firm文件中保存“數(shù)字-字符串”映射關(guān)系的“查找表”。

DATATIME存儲(chǔ)的范圍更廣，保存的值從1001年到9999年，精確到秒，與時(shí)區(qū)無關(guān)，使用8個(gè)字節(jié)的存儲(chǔ)空間，使用一種可排序、無歧義的格式顯示時(shí)間，TIMESTAMP類型保存了從1970年1月1日午夜以來的秒數(shù)，使用4個(gè)字節(jié)的存儲(chǔ)空間，只能表示從1970年到2038年，依賴于時(shí)區(qū)，空間效率更高，推薦使用TIMESTAMP

對(duì)于BOLB和TEXT類型他們都是為了存儲(chǔ)很大的數(shù)據(jù)而設(shè)計(jì)的字符串，分別采用二進(jìn)制和字符串方式存儲(chǔ)。

不能有太多的列

單個(gè)查詢最好在12個(gè)表以內(nèi)做關(guān)聯(lián)

當(dāng)遇到未知值的時(shí)候不要害怕使用NULL

在實(shí)際的應(yīng)用中需要混用范式和反范式，使用部分范式化的schema、緩存表、以及其他的技巧，最常見的反范式化數(shù)據(jù)的方法是復(fù)制或者緩存，在不同的表中存儲(chǔ)相同的特定列。

修改.frm文件來加快ALTER TABLE 操作的速度

選取最適用的字段屬性，盡可能減少定義字段寬度，盡量把字段設(shè)置NOTNULL，例如’省份’、’性別’最好適用ENUM

使用連接(JOIN)來代替子查詢

用聯(lián)合(UNION)來代替手動(dòng)創(chuàng)建的臨時(shí)表

鎖定表、優(yōu)化事務(wù)處理