面試提高及日常學習積累（二）——資料庫篇

這一篇主要講mysql的一些積累和學習體會。

Innodb引擎提供了對資料庫ACID事務的支持，並且實現了SQL標準的四種隔離級別，關於資料庫事務與其隔離級別的內容請見資料庫事務與其隔離級別這篇文章。該引擎還提供了行級鎖和外鍵約束，它的設計目標是處理大容量資料庫系統，它本身其實就是基於MySQL後台的完整資料庫系統，MySQL運行時Innodb會在內存中建立緩衝池，用於緩衝數據和索引。但是該引擎不支持FULLTEXT類型的索引，而且它沒有保存表的行數，當SELECT COUNT(*) FROM TABLE時需要掃描全表。當需要使用資料庫事務時，該引擎當然是首選。由於鎖的粒度更小，寫操作不會鎖定全表，所以在並發較高時，使用Innodb引擎會提升效率。但是使用行級鎖也不是絕對的，如果在執行一個SQL語句時MySQL不能確定要掃描的範圍，InnoDB表同樣會鎖全表。

MyIASM是MySQL默認的引擎，但是它沒有提供對資料庫事務的支持，也不支持行級鎖和外鍵，因此當INSERT(插入)或UPDATE(更新)數據時即寫操作需要鎖定整個表，效率便會低一些。不過和Innodb不同，MyIASM中存儲了表的行數，於是SELECT COUNT(*) FROM TABLE時只需要直接讀取已經保存好的值而不需要進行全表掃描。如果表的讀操作遠遠多於寫操作且不需要資料庫事務的支持，那麼MyIASM也是很好的選擇。

單個表只能有一個唯一索引，索引列的值必須唯一，但允許有空值。如果是組合索引，則列值的組合必須唯一。數據存儲結構和索引存儲結構順序一致。（一般主鍵為唯一索引）

創建單列索引：CREATE UNIQUE INDEX indexName ON tableName(tableColumns(length));如果是CHAR,VARCHAR類型，length可以小於欄位實際長度;如果是BLOB 和 TEXT 類型，必須指定length。

創建組合索引：CREATE UNIQUE INDEX indexName ON tableName( `column1`, `column2`, `column3` );

刪除索引：DROP INDEX indexName;

單個表可以有多個非聚集索引，可以是單列也可以是組合索引，數據存儲結構和索引存儲結構順序無關。

創建單列索引：CREATE INDEX indexName ON tableName(tableColumns(length));如果是CHAR,VARCHAR類型，length可以小於欄位實際長度;如果是BLOB 和 TEXT 類型，必須指定length。

創建組合索引：CREATE INDEX indexName ON tableName( `column1`, `column2`, `column3` );

刪除索引：DROP INDEX indexName;

創建組合索引：CREATE INDEX indexName ON tableName( `column1`, `column2`, `column3` );

對於複合索引:Mysql從左到右的使用索引中的欄位，一個查詢可以只使用索引中的一部份，但只能是最左側部分。

例如索引是key index (a,b,c). 可以支持a | a,b| a,b,c 3種組合進行查找，但不支持 b|c|b,c進行查找 ,當最左側欄位是常量引用時，索引就十分有效。(組合索引最多2-3列)

這兩塊的內容可以更具具體的業務需要進行建立，一般對於查詢較多的相似組合的表，或者需要隱藏原表信息的可以合并提供查詢視圖，對於複雜業務和需要特殊效率的業務可以提供存儲過程。

常用函數

-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

以下內容來自請去這裡查看更詳細 http://www.jb51.net/article/40179.htm

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

1、聚合函數(常用於GROUP BY從句的SELECT查詢中)

AVG(col)返回指定列的平均值

COUNT(col)返回指定列中非NULL值的個數

MIN(col)返回指定列的最小值

MAX(col)返回指定列的最大值

SUM(col)返回指定列的所有值之和

GROUP_CONCAT(col) 返回由屬於一組的列值連接組合而成的結果

2、字元串函數

CONCAT(s1,s2...,sn)將s1,s2...,sn連接成字元串

CONCAT_WS(sep,s1,s2...,sn)將s1,s2...,sn連接成字元串，並用sep字元間隔

LCASE(str)或LOWER(str) 返回將字元串str中所有字元改變為小寫後的結果

REVERSE(str) 返回顛倒字元串str的結果

3、日期和時間函數

CURDATE或CURRENT_DATE 返回當前的日期

CURTIME或CURRENT_TIME 返回當前的時間

DATE_ADD(date,INTERVAL int keyword)返回日期date加上間隔時間int的結果(int必須按照關鍵字進行格式化),如：SELECTDATE_ADD(CURRENT_DATE,INTERVAL 6 MONTH);

DATE_FORMAT(date,fmt) 依照指定的fmt格式格式化日期date值

DATE_SUB(date,INTERVAL int keyword)返回日期date加上間隔時間int的結果(int必須按照關鍵字進行格式化),如：SELECTDATE_SUB(CURRENT_DATE,INTERVAL 6 MONTH);

DAYOFWEEK(date) 返回date所代表的一星期中的第幾天(1~7)

DAYOFMONTH(date) 返回date是一個月的第幾天(1~31)

DAYOFYEAR(date) 返回date是一年的第幾天(1~366)

DAYNAME(date) 返回date的星期名，如：SELECT DAYNAME(CURRENT_DATE);

FROM_UNIXTIME(ts,fmt) 根據指定的fmt格式，格式化UNIX時間戳ts

HOUR(time) 返回time的小時值(0~23)

MINUTE(time) 返回time的分鐘值(0~59)

MONTH(date) 返回date的月份值(1~12)

MONTHNAME(date) 返回date的月份名，如：SELECT MONTHNAME(CURRENT_DATE);

NOW 返回當前的日期和時間

QUARTER(date) 返回date在一年中的季度(1~4)，如SELECT QUARTER(CURRENT_DATE);

WEEK(date) 返回日期date為一年中第幾周(0~53)

YEAR(date) 返回日期date的年份(1000~9999)

4、控制流函數

MySQL有4個函數是用來進行條件操作的，這些函數可以實現SQL的條件邏輯，允許開發者將一些應用程序業務邏輯轉換到資料庫後台。

MySQL控制流函數：

CASE WHEN[test1] THEN [result1]...ELSE [default] END如果testN是真，則返回resultN，否則返回default

CASE [test] WHEN[val1] THEN [result]...ELSE [default]END 如果test和valN相等，則返回resultN，否則返回default

IF(test,t,f) 如果test是真，返回t；否則返回f

IFNULL(arg1,arg2) 如果arg1不是空，返回arg1，否則返回arg2

NULLIF(arg1,arg2) 如果arg1=arg2返回NULL；否則返回arg1

這些函數的第一個是IFNULL，它有兩個參數，並且對第一個參數進行判斷。如果第一個參數不是NULL，函數就會向調用者返回第一個參數；如果是NULL,將返回第二個參數。

代碼如下:

SELECT CASE "green"

WHEN "red" THEN "stop"

WHEN "green" THEN "go" END;

SELECT CASE 9 WHEN 1 THEN "a" WHEN 2 THEN "b" ELSE "N/A" END;

SELECT CASE WHEN (2+2)=4 THEN "OK" WHEN(2+2)<>4 THEN "not OK" END ASSTATUS;

SELECT Name,IF((IsActive = 1),"已激活","未激活") AS RESULT FROMUserLoginInfo;

SELECT fname,lname,(math+sci+lit) AS total,

CASE WHEN (math+sci+lit) < 50 THEN "D"

WHEN (math+sci+lit) BETWEEN 50 AND 150 THEN "C"

WHEN (math+sci+lit) BETWEEN 151 AND 250 THEN "B"

ELSE "A" END

AS grade FROM marks;

SELECT IF(ENCRYPT("sue","ts")=upass,"allow","deny") AS LoginResultFROM users WHERE uname = "sue";#一個登陸驗證

-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

在官方學習的時候這部分是要符合三範式規則的，但是這種規則下對於大多數的數據業務來說都是在理論上太完整，不太實用，所以可以學習這種設計的思想，但是適不適用可以按照自己的業務規劃。

在設計資料庫的過程主要可以參考以下幾個方向（個人總結）：

1.首先分析自己的業務，抽象好數據對象以及對象之間的關聯關係。

2.對於通用的一些數據欄位可以抽象總結出來，比如創建時間，更新時間，操作者。。。這些通用的欄位。

3.針對自己的業務主要分析查詢的部分，對於重複查詢較多，修改機會較少的欄位可以適當的在多個表中進行冗餘，以提高查詢和簡化sql代碼的複雜度。

4.在建表時可以考慮後期業務的擴展性，但是不必強制流出欄位等，因為業務變化比較平凡，過多的預留會讓表看起來非常混亂。

5.當業務擴展性和項目需求有衝突時可以優先考慮項目需求，至於擴展性可看業務發展再定，甚至修改。（個人觀點，可不理會）

6.做好欄位說明，備註。

1.級聯查詢優化，多個單標查詢的優化，可以考慮用多表級聯查詢優化。

2.考慮索引使用情況優化，對於破壞索引，未使用索引的sql，可以考慮建立索引或者使現有索引生效方面優化。

3.對於大數據量分頁的優化。

4.通過分析業務，用更多的條件確定更少的列。

四：資料庫架構

1.對於大數據和有讀寫壓力的業務可以考慮從幾個方向去設計

表分區：http://www.cnblogs.com/freeton/p/4265228.html這個網址寫的不錯，值得瀏覽下。

分表存儲：按業務需求可以有水平分表和垂直分表兩種不同的技術。

水平分表是按照一定的規則將一個大的表格水平分割到不同的子表中，所以每個表格的列都是相同的。

垂直分表是將一個大的表格按照不同的業務需求把不同的列分割到不同的子表中，以某一個相同的屬性（例ID）關聯起來。

對於分表後的查詢可以按照業務先進行邏輯判斷，然後定位到某個子表。對於一些統計功能，則循環所有子表，合并。

分庫技術：就是採用不同的資料庫部署資料庫集群，可以搜搜MyCat中間件。

讀寫分離：http://blog.csdn.net/zoomyj/article/details/50610349這片文章寫了好多 有興趣的可以多了解下，作為一個了解和積累。

總結：零零散散寫了一些，這裡寫的主要是一些方向性的東西，可能沒有具體的一些實現或者是代碼。所以只是一個指引，希望能幫助大家在這些方面去學習，去積累。也有一些東西是網路摘抄的，只要是好的就學習吧。

以上內適用一到三年java開發面試，個人見解，值得參考,覺得適用的順手推薦下，讓更多的人看到

最後分享一個不錯的雞湯給想要幹事的朋友們，祝大家成功！http://www.iheima.com/zixun/2017/0628/163820.shtml

喜歡這篇文章嗎？立刻分享出去讓更多人知道吧！