1. SQL的生命周期执隧？

應(yīng)用服務(wù)器與數(shù)據(jù)庫服務(wù)器建立一個(gè)連接

數(shù)據(jù)庫進(jìn)程拿到請(qǐng)求sql

解析并生成執(zhí)行計(jì)劃忌愚，執(zhí)行

讀取數(shù)據(jù)到內(nèi)存并進(jìn)行邏輯處理

通過步驟一的連接曲管，發(fā)送結(jié)果到客戶端

關(guān)掉連接，釋放資源

2. 大表數(shù)據(jù)查詢硕糊，怎么優(yōu)化

優(yōu)化shema院水、sql語句+索引；

第二加緩存简十，memcached, redis檬某；

主從復(fù)制，讀寫分離勺远；

垂直拆分橙喘，根據(jù)你模塊的耦合度，將一個(gè)大的系統(tǒng)分為多個(gè)小的系統(tǒng)胶逢，也就是分布式系統(tǒng)厅瞎；

水平切分，針對(duì)數(shù)據(jù)量大的表初坠，這一步最麻煩和簸，最能考驗(yàn)技術(shù)水平，要選擇一個(gè)合理的sharding key, 為了有好的查詢效率碟刺，表結(jié)構(gòu)也要改動(dòng)锁保，做一定的冗余，應(yīng)用也要改半沽，sql中盡量帶sharding key爽柒，將數(shù)據(jù)定位到限定的表上去查，而不是掃描全部的表者填；

3. 超大分頁怎么處理浩村？

超大的分頁一般從兩個(gè)方向上來解決.

數(shù)據(jù)庫層面,這也是我們主要集中關(guān)注的(雖然收效沒那么大),類似于select * from table where age > 20 limit 1000000,10這種查詢其實(shí)也是有可以優(yōu)化的余地的. 這條語句需要load1000000數(shù)據(jù)然后基本上全部丟棄,只取10條當(dāng)然比較慢. 當(dāng)時(shí)我們可以修改為select * from table where id in (select id from table where age > 20 limit 1000000,10).這樣雖然也load了一百萬的數(shù)據(jù),但是由于索引覆蓋,要查詢的所有字段都在索引中,所以速度會(huì)很快. 同時(shí)如果ID連續(xù)的好,我們還可以select * from table where id > 1000000 limit 4,效率也是不錯(cuò)的,優(yōu)化的可能性有許多種,但是核心思想都一樣,就是減少load的數(shù)據(jù).

從需求的角度減少這種請(qǐng)求…主要是不做類似的需求(直接跳轉(zhuǎn)到幾百萬頁之后的具體某一頁.只允許逐頁查看或者按照給定的路線走,這樣可預(yù)測,可緩存)以及防止ID泄漏且連續(xù)被人惡意攻擊.

解決超大分頁,其實(shí)主要是靠緩存,可預(yù)測性的提前查到內(nèi)容,緩存至redis等k-V數(shù)據(jù)庫中,直接返回即可.

在阿里巴巴《Java開發(fā)手冊(cè)》中,對(duì)超大分頁的解決辦法是類似于上面提到的第一種.

【推薦】利用延遲關(guān)聯(lián)或者子查詢優(yōu)化超多分頁場景。

說明：MySQL并不是跳過offset行占哟，而是取offset+N行心墅，然后返回放棄前offset行，返回N行榨乎，那當(dāng)offset特別大的時(shí)候怎燥，效率就非常的低下，要么控制返回的總頁數(shù)蜜暑，要么對(duì)超過特定閾值的頁數(shù)進(jìn)行SQL改寫铐姚。

正例：先快速定位需要獲取的id段，然后再關(guān)聯(lián)：

SELECT a.* FROM 表1 a, (select id from 表1 where 條件 LIMIT 100000,20 ) b where a.id=b.id


5. mysql 分頁

LIMIT 子句可以被用于強(qiáng)制 SELECT 語句返回指定的記錄數(shù)史煎。LIMIT 接受一個(gè)或兩個(gè)數(shù)字參數(shù)谦屑。參數(shù)必須是一個(gè)整數(shù)常量驳糯。如果給定兩個(gè)參數(shù)，第一個(gè)參數(shù)指定第一個(gè)返回記錄行的偏移量氢橙，第二個(gè)參數(shù)指定返回記錄行的最大數(shù)目酝枢。初始記錄行的偏移量是 0(而不是 1)

mysql> SELECT * FROM table LIMIT 5,10; // 檢索記錄行 6-15

為了檢索從某一個(gè)偏移量到記錄集的結(jié)束所有的記錄行，可以指定第二個(gè)參數(shù)為 -1：

mysql> SELECT * FROM table LIMIT 95,-1; // 檢索記錄行 96-last.

如果只給定一個(gè)參數(shù)悍手，它表示返回最大的記錄行數(shù)目：

mysql> SELECT * FROM table LIMIT 5; //檢索前 5 個(gè)記錄行

換句話說帘睦，LIMIT n 等價(jià)于 LIMIT 0,n。

6. 慢查詢?nèi)罩?/p>

用于記錄執(zhí)行時(shí)間超過某個(gè)臨界值的SQL日志坦康，用于快速定位慢查詢竣付，為我們的優(yōu)化做參考。

開啟慢查詢?nèi)罩?/p>

配置項(xiàng)：slow_query_log

可以使用show variables like ‘slov_query_log’查看是否開啟滞欠，如果狀態(tài)值為OFF古胆，可以使用set GLOBAL slow_query_log = on來開啟，它會(huì)在datadir下產(chǎn)生一個(gè)xxx-slow.log的文件筛璧。

設(shè)置臨界時(shí)間

配置項(xiàng)：long_query_time

查看：show VARIABLES like 'long_query_time'逸绎，單位秒

設(shè)置：set long_query_time=0.5

實(shí)操時(shí)應(yīng)該從長時(shí)間設(shè)置到短的時(shí)間，即將最慢的SQL優(yōu)化掉

查看日志夭谤，一旦SQL超過了我們?cè)O(shè)置的臨界時(shí)間就會(huì)被記錄到xxx-slow.log中

7. 關(guān)心過業(yè)務(wù)系統(tǒng)里面的sql耗時(shí)嗎棺牧？統(tǒng)計(jì)過慢查詢嗎？對(duì)慢查詢都怎么優(yōu)化過朗儒？

在業(yè)務(wù)系統(tǒng)中颊乘，除了使用主鍵進(jìn)行的查詢，其他的我都會(huì)在測試庫上測試其耗時(shí)醉锄，慢查詢的統(tǒng)計(jì)主要由運(yùn)維在做乏悄，會(huì)定期將業(yè)務(wù)中的慢查詢反饋給我們。

慢查詢的優(yōu)化首先要搞明白慢的原因是什么恳不？是查詢條件沒有命中索引纲爸？是load了不需要的數(shù)據(jù)列？還是數(shù)據(jù)量太大妆够？

所以優(yōu)化也是針對(duì)這三個(gè)方向來的，

首先分析語句负蚊，看看是否load了額外的數(shù)據(jù)神妹，可能是查詢了多余的行并且拋棄掉了，可能是加載了許多結(jié)果中并不需要的列家妆，對(duì)語句進(jìn)行分析以及重寫鸵荠。

分析語句的執(zhí)行計(jì)劃，然后獲得其使用索引的情況伤极，之后修改語句或者修改索引蛹找，使得語句可以盡可能的命中索引姨伤。

如果對(duì)語句的優(yōu)化已經(jīng)無法進(jìn)行，可以考慮表中的數(shù)據(jù)量是否太大庸疾，如果是的話可以進(jìn)行橫向或者縱向的分表乍楚。

8. 為什么要盡量設(shè)定一個(gè)主鍵？

主鍵是數(shù)據(jù)庫確保數(shù)據(jù)行在整張表唯一性的保障届慈，即使業(yè)務(wù)上本張表沒有主鍵徒溪，也建議添加一個(gè)自增長的ID列作為主鍵。設(shè)定了主鍵之后金顿，在后續(xù)的刪改查的時(shí)候可能更加快速以及確保操作數(shù)據(jù)范圍安全臊泌。

9. 主鍵使用自增ID還是UUID？

推薦使用自增ID揍拆，不要使用UUID渠概。

因?yàn)樵贗nnoDB存儲(chǔ)引擎中，主鍵索引是作為聚簇索引存在的嫂拴，也就是說播揪，主鍵索引的B+樹葉子節(jié)點(diǎn)上存儲(chǔ)了主鍵索引以及全部的數(shù)據(jù)(按照順序)，如果主鍵索引是自增ID顷牌，那么只需要不斷向后排列即可剪芍，如果是UUID，由于到來的ID與原來的大小不確定窟蓝，會(huì)造成非常多的數(shù)據(jù)插入罪裹，數(shù)據(jù)移動(dòng)，然后導(dǎo)致產(chǎn)生很多的內(nèi)存碎片运挫，進(jìn)而造成插入性能的下降状共。

總之，在數(shù)據(jù)量大一些的情況下谁帕，用自增主鍵性能會(huì)好一些峡继。

關(guān)于主鍵是聚簇索引，如果沒有主鍵匈挖，InnoDB會(huì)選擇一個(gè)唯一鍵來作為聚簇索引碾牌，如果沒有唯一鍵，會(huì)生成一個(gè)隱式的主鍵儡循。

10. 字段為什么要求定義為not null舶吗？

null值會(huì)占用更多的字節(jié)，且會(huì)在程序中造成很多與預(yù)期不符的情況择膝。

11. 如果要存儲(chǔ)用戶的密碼散列誓琼，應(yīng)該使用什么字段進(jìn)行存儲(chǔ)？

密碼散列，鹽腹侣，用戶身份證號(hào)等固定長度的字符串應(yīng)該使用char而不是varchar來存儲(chǔ)叔收，這樣可以節(jié)省空間且提高檢索效率。


12. 優(yōu)化查詢過程中的數(shù)據(jù)訪問

訪問數(shù)據(jù)太多導(dǎo)致查詢性能下降

確定應(yīng)用程序是否在檢索大量超過需要的數(shù)據(jù)傲隶，可能是太多行或列

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

避免犯如下SQL語句錯(cuò)誤

查詢不需要的數(shù)據(jù)饺律。解決辦法：使用limit解決

多表關(guān)聯(lián)返回全部列。解決辦法：指定列名

總是返回全部列伦籍。解決辦法：避免使用SELECT *

重復(fù)查詢相同的數(shù)據(jù)蓝晒。解決辦法：可以緩存數(shù)據(jù)，下次直接讀取緩存

是否在掃描額外的記錄帖鸦。解決辦法：

使用explain進(jìn)行分析芝薇，如果發(fā)現(xiàn)查詢需要掃描大量的數(shù)據(jù)，但只返回少數(shù)的行作儿，可以通過如下技巧去優(yōu)化：

使用索引覆蓋掃描洛二，把所有的列都放到索引中，這樣存儲(chǔ)引擎不需要回表獲取對(duì)應(yīng)行就可以返回結(jié)果攻锰。

改變數(shù)據(jù)庫和表的結(jié)構(gòu)晾嘶，修改數(shù)據(jù)表范式

重寫SQL語句，讓優(yōu)化器可以以更優(yōu)的方式執(zhí)行查詢娶吞。

13. 優(yōu)化長難的查詢語句

一個(gè)復(fù)雜查詢還是多個(gè)簡單查詢

MySQL內(nèi)部每秒能掃描內(nèi)存中上百萬行數(shù)據(jù)垒迂，相比之下，響應(yīng)數(shù)據(jù)給客戶端就要慢得多

使用盡可能小的查詢是好的妒蛇，但是有時(shí)將一個(gè)大的查詢分解為多個(gè)小的查詢是很有必要的机断。

切分查詢

將一個(gè)大的查詢分為多個(gè)小的相同的查詢

一次性刪除1000萬的數(shù)據(jù)要比一次刪除1萬，暫停一會(huì)的方案更加損耗服務(wù)器開銷绣夺。

分解關(guān)聯(lián)查詢吏奸，讓緩存的效率更高。

執(zhí)行單個(gè)查詢可以減少鎖的競爭陶耍。

在應(yīng)用層做關(guān)聯(lián)更容易對(duì)數(shù)據(jù)庫進(jìn)行拆分奋蔚。

查詢效率會(huì)有大幅提升。

較少冗余記錄的查詢烈钞。

14. 優(yōu)化特定類型的查詢語句

count(*)會(huì)忽略所有的列泊碑，直接統(tǒng)計(jì)所有列數(shù)，不要使用count(列名)

MyISAM中毯欣，沒有任何where條件的count(*)非扯旯罚快。

當(dāng)有where條件時(shí)仪媒，MyISAM的count統(tǒng)計(jì)不一定比其它引擎快。

可以使用explain查詢近似值，用近似值替代count(*)

增加匯總表

使用緩存

15. 優(yōu)化關(guān)聯(lián)查詢

確定ON或者USING子句中是否有索引算吩。

確保GROUP BY和ORDER BY只有一個(gè)表中的列留凭，這樣MySQL才有可能使用索引。

16. 優(yōu)化子查詢

用關(guān)聯(lián)查詢替代

優(yōu)化GROUP BY和DISTINCT

這兩種查詢據(jù)可以使用索引來優(yōu)化偎巢，是最有效的優(yōu)化方法

關(guān)聯(lián)查詢中蔼夜，使用標(biāo)識(shí)列分組的效率更高

如果不需要ORDER BY，進(jìn)行GROUP BY時(shí)加ORDER BY NULL压昼，MySQL不會(huì)再進(jìn)行文件排序求冷。

WITH ROLLUP超級(jí)聚合，可以挪到應(yīng)用程序處理

17. 優(yōu)化LIMIT分頁

LIMIT偏移量大的時(shí)候窍霞，查詢效率較低

可以記錄上次查詢的最大ID匠题，下次查詢時(shí)直接根據(jù)該ID來查詢

18. 優(yōu)化UNION查詢

UNION ALL的效率高于UNION


19. 優(yōu)化WHERE子句

解題方法

對(duì)于此類考題，先說明如何定位低效SQL語句但金，然后根據(jù)SQL語句可能低效的原因做排查韭山，先從索引著手，如果索引沒有問題冷溃，考慮以上幾個(gè)方面钱磅，數(shù)據(jù)訪問的問題，長難查詢句的問題還是一些特定類型優(yōu)化的問題似枕，逐一回答盖淡。

SQL語句優(yōu)化的一些方法？

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

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

select id from t where num is null

-- 可以在num上設(shè)置默認(rèn)值0，確保表中num列沒有null值攀唯，然后這樣查詢：

select id from t where num=

3.應(yīng)盡量避免在 where 子句中使用!=或<>操作符洁桌，否則引擎將放棄使用索引而進(jìn)行全表掃描。

4.應(yīng)盡量避免在 where 子句中使用or 來連接條件侯嘀，否則將導(dǎo)致引擎放棄使用索引而進(jìn)行全表掃描另凌，如：

select id from t where num=10 or num=20

-- 可以這樣查詢：

select id from t where num=10 union all select id from t where num=20

5.in 和 not in 也要慎用，否則會(huì)導(dǎo)致全表掃描戒幔，如：

select id from t where num in(1,2,3)

-- 對(duì)于連續(xù)的數(shù)值吠谢，能用 between 就不要用 in 了：

select id from t where num between 1 and 3

6.下面的查詢也將導(dǎo)致全表掃描：select id from t where name like ‘%李%’若要提高效率，可以考慮全文檢索诗茎。

7.如果在 where 子句中使用參數(shù)工坊，也會(huì)導(dǎo)致全表掃描。因?yàn)镾QL只有在運(yùn)行時(shí)才會(huì)解析局部變量，但優(yōu)化程序不能將訪問計(jì)劃的選擇推遲到運(yùn)行時(shí)王污；它必須在編譯時(shí)進(jìn)行選擇罢吃。然 而，如果在編譯時(shí)建立訪問計(jì)劃昭齐，變量的值還是未知的尿招，因而無法作為索引選擇的輸入項(xiàng)。如下面語句將進(jìn)行全表掃描：

select id from t where num=@num

-- 可以改為強(qiáng)制查詢使用索引：

select id from t with(index(索引名)) where num=@num

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

select id from t where num/2=100

-- 應(yīng)改為:

select id from t where num=100*2

9.應(yīng)盡量避免在where子句中對(duì)字段進(jìn)行函數(shù)操作，這將導(dǎo)致引擎放棄使用索引而進(jìn)行全表掃描里覆。如：

select id from t where substring(name,1,3)=’abc’

-- name以abc開頭的id應(yīng)改為:

select id from t where name like ‘a(chǎn)bc%’

10.不要在 where 子句中的“=”左邊進(jìn)行函數(shù)丧荐、算術(shù)運(yùn)算或其他表達(dá)式運(yùn)算，否則系統(tǒng)將可能無法正確使用索引租谈。

小編分類整理了許多java進(jìn)階學(xué)習(xí)材料和BAT面試題篮奄，需要資料的請(qǐng)加QQ群：731611386就能領(lǐng)取2019年java進(jìn)階學(xué)習(xí)資料和BAT面試題以及《EffectiveJava》（第3版）電子版書籍。