數(shù)據(jù)庫(kù)

SQL之連接查詢

外連接

1.左連接(左外連接)以左表為基準(zhǔn)進(jìn)行查詢,左表數(shù)據(jù)會(huì)全部顯示出來(lái),右表如果和左表匹配的數(shù)據(jù)則顯示
相應(yīng)字段的數(shù)據(jù),如果不匹配,則顯示為 NULL。

2.右連接（右外連接）以右表為基準(zhǔn)進(jìn)行查詢,右表數(shù)據(jù)會(huì)全部顯示出來(lái),右表如果和左表匹配的數(shù)據(jù)則顯示
相應(yīng)字段的數(shù)據(jù),如果不匹配,則顯示為 NULL。

3.全連接就是先以左表進(jìn)行左外連接，然后以右表進(jìn)行右外連接。

內(nèi)連接:顯示表之間有連接匹配的所有行。

SQL之聚合函數(shù)

聚合函數(shù)是對(duì)一組值執(zhí)行計(jì)算并返回單一的值的函數(shù)，它經(jīng)常與 SELECT 語(yǔ)句的 GROUP BY

1.AVG 返回指定組中的平均值，空值被忽略； COUNT 返回指定組中項(xiàng)目的數(shù)量。
例：

select prd_no,avg(qty) from sales group by prd_no

2. MAX 返回指定數(shù)據(jù)的最大值；MIN 返回指定數(shù)據(jù)的最小值；SUM 返回指定數(shù)據(jù)的和，只能用于數(shù)字列，空值被忽略。
   例：

select prd_no,max(qty) from sales group by prd_no

3.使用 group by 子句對(duì)數(shù)據(jù)進(jìn)行分組；對(duì) group by 子句形成的組運(yùn)行聚集函數(shù)計(jì)算每一組的值；最后用 having 子句去掉
不符合條件的組；having 子句中的每一個(gè)元素也必須出現(xiàn)在 select 列表中。有些數(shù)據(jù)庫(kù)例外，如 oracle. 例：

select prd_no,max(qty) from sales group by prd_no having prd_no>10

SQL 之 SQL 注入

舉例：

select admin from user where username='admin' or 'a'='a' and passwd=''or 'a'='a' 

防止 SQL 注入，使用預(yù)編譯語(yǔ)句是預(yù)防 SQL 注入的最佳方式，如

select admin from user where username=？And password=?

使用預(yù)編譯的 SQL 語(yǔ)句語(yǔ)義不會(huì)發(fā)生改變，在 SQL 語(yǔ)句中，變量用問(wèn)號(hào)？表示。像上面例子中，username 變量傳遞的'admin' or
'a'='a' 參數(shù)，也只會(huì)當(dāng)作 username 字符串來(lái)解釋查詢，從根本上杜絕了 SQL 注入攻擊的發(fā)生。

注意：使用 mybaits 時(shí) mapper 中#方式能夠很大程度防止 sql 注入，$方式無(wú)法防止 sql 注入

什么是存儲(chǔ)過(guò)程？它有什么優(yōu)點(diǎn)？

答：存儲(chǔ)過(guò)程是一組予編譯的 SQL 語(yǔ)句

它的優(yōu)點(diǎn)有：

允許模塊化程序設(shè)計(jì)，就是說(shuō)只需要?jiǎng)?chuàng)建一次過(guò)程，以后在程序中就可以調(diào)用該過(guò)程任意次。
允許更快執(zhí)行，如果某操作需要執(zhí)行大量 SQL 語(yǔ)句或重復(fù)執(zhí)行，存儲(chǔ)過(guò)程比 SQL 語(yǔ)句執(zhí)行的要快。
減少網(wǎng)絡(luò)流量，例如一個(gè)需要數(shù)百行的 SQL 代碼的操作有一條執(zhí)行語(yǔ)句完成，不需要在網(wǎng)絡(luò)中發(fā)送數(shù)百行代碼。
更好的安全機(jī)制，對(duì)于沒(méi)有權(quán)限執(zhí)行存儲(chǔ)過(guò)程的用戶，也可授權(quán)他們執(zhí)行存儲(chǔ)過(guò)程。

MySQL 存儲(chǔ)過(guò)程的創(chuàng)建

1.格式

MySQL 存儲(chǔ)過(guò)程創(chuàng)建的格式：

CREATE PROCEDURE 存儲(chǔ)過(guò)程名(參數(shù)列表)
BEGIN
SQL 語(yǔ)句代碼塊
END

舉例：

CREATE PROCEDURE proc1(OUT s int)
BEGIN
SELECT COUNT(*) INTO s FROM user;
END

2.參數(shù)

MySQL 存儲(chǔ)過(guò)程參數(shù)有三種類型：

in、out、inout。
如果僅僅想把數(shù)據(jù)傳給 MySQL 存儲(chǔ)過(guò)程，那就使用“in”類型參數(shù)；
如果僅僅從 MySQL 存儲(chǔ)過(guò)程返回值，那就使用“out”類型參數(shù)；
如果需要把數(shù)據(jù)傳給 MySQL存儲(chǔ)過(guò)程，還要經(jīng)過(guò)一些計(jì)算后再傳回給我們，此時(shí)，要使用“inout”類型參數(shù)。

mysql 性能優(yōu)化舉例

1.當(dāng)只要一行數(shù)據(jù)時(shí)使用 LIMIT 1

    當(dāng)你查詢表的有些時(shí)候，你已經(jīng)知道結(jié)果只會(huì)有一條結(jié)果，在這種情況下，加上 LIMIT 1 可以增加性能。
    這樣一樣，MySQL數(shù)據(jù)庫(kù)引擎會(huì)在找到一條數(shù)據(jù)后停止搜索，而不是繼續(xù)往后查少下一條符合記錄的數(shù)據(jù)。

2.選擇正確的存儲(chǔ)引擎

    在 MySQL 中有兩個(gè)存儲(chǔ)引擎 MyISAM 和 InnoDB，每個(gè)引擎都有利有弊。
    MyISAM 適合于一些需要大量查詢的應(yīng)用，但其對(duì)于有大量寫(xiě)操作并不是很好。
    甚至你只是需要 update一個(gè)字段，整個(gè)表都會(huì)被鎖起來(lái)，而別的進(jìn)程，就算是讀進(jìn)程都無(wú)法操作直到讀操作完成。
    另外，MyISAM 對(duì)于 SELECT COUNT(*) 這類的計(jì)算是超快無(wú)比的。

    InnoDB 的趨勢(shì)會(huì)是一個(gè)非常復(fù)雜的存儲(chǔ)引擎，對(duì)于一些小的應(yīng)用，它會(huì)比 MyISAM還慢。
    他是它支持“行鎖” ，于是在寫(xiě)操作比較多的時(shí)候，會(huì)更優(yōu)秀。并且，他還支持更多的高級(jí)應(yīng)用，比如：事務(wù)。

3.用 Not Exists 代替 Not In Not Exists

    3.用 Not Exists 代替 Not In Not Exists 允許用戶使用相關(guān)子查詢已排除一個(gè)表中能夠與另一個(gè)表成功連接的所有記錄。Not Exists 用到了連接，能夠發(fā)揮已經(jīng)建好的索引的作用，
    而 Not In 不能使用索引。
    Not In 是最慢的方式，要同每條記錄比較，在數(shù)據(jù)量比較大的查詢中不建議使用這種方式。
    
    Select a.mobileid from Log_user a where not exists (select b.mobileid from magazineitem b where b.mobileid=a.mobileid);

4.對(duì)操作符的優(yōu)化

    4.對(duì)操作符的優(yōu)化 盡量不采用不利用索引的操作符
    如：in ，not in ， is nul， is not null，<>等
    某個(gè)字段你總要會(huì)經(jīng)常用來(lái)做搜索，為其建立索引：
    Mysql 中可以使用 alter table 語(yǔ)句來(lái)為表中的字段添加索引的基本語(yǔ)法是：
    ALTER TABLE <表名> ADD INDEX (<字段>);
    例：mysql> alter table test add index(t_name);

5.mysql 分庫(kù)分表：

    
    分庫(kù)分表有垂直切分和水平切分兩種。
    
    垂直切分：即將表按照功能模塊、關(guān)系密切程度劃分出來(lái)，部署到不同的庫(kù)上。例如，我們會(huì)建立定義數(shù)據(jù)庫(kù) workDB、商品數(shù)
    據(jù)庫(kù) payDB、用戶數(shù)據(jù)庫(kù) userDB、日志數(shù)據(jù)庫(kù) logDB 等，分別用于存儲(chǔ)項(xiàng)目數(shù)據(jù)定義表、商品定義表、用戶數(shù)據(jù)表、日志數(shù)據(jù)表
    等。
    
    水平切分：當(dāng)一個(gè)表中的數(shù)據(jù)量過(guò)大時(shí)，我們可以把該表的數(shù)據(jù)按照某種規(guī)則，例如 userID 散列，進(jìn)行劃分，然后存儲(chǔ)到多個(gè)
    結(jié)構(gòu)相同的表，和不同的庫(kù)上。例如，我們的 userDB 中的用戶數(shù)據(jù)表中，每一個(gè)表的數(shù)據(jù)量都很大，就可以把 userDB 切分為結(jié)
    構(gòu)相同的多個(gè) userDB：part0DB、part1DB 等，再將 userDB 上的用戶數(shù)據(jù)表 userTable，切分為很多 userTable：userTable0、
    userTable1 等，然后將這些表按照一定的規(guī)則存儲(chǔ)到多個(gè) userDB 上。
    
    ===========================================================================================
    
    應(yīng)該使用哪一種方式來(lái)實(shí)施數(shù)據(jù)庫(kù)分庫(kù)分表，這要看數(shù)據(jù)庫(kù)中數(shù)據(jù)量的瓶頸所在，并綜合項(xiàng)目的業(yè)務(wù)類型進(jìn)行考慮。
    
    如果數(shù)據(jù)庫(kù)是因?yàn)楸硖喽斐珊Ａ繑?shù)據(jù)，并且項(xiàng)目的各項(xiàng)業(yè)務(wù)邏輯劃分清晰、
    低耦合，那么規(guī)則簡(jiǎn)單明了、容易實(shí)施的垂直切分必是首選。
    
    而如果數(shù)據(jù)庫(kù)中的表并不多，但單表的數(shù)據(jù)量很大、或數(shù)據(jù)熱度很高，這種情況之下就應(yīng)該選擇水平切分，水平切分比垂直切分要復(fù)雜一些，
    它將原本邏輯上屬于一體的數(shù)據(jù)進(jìn)行了物理分割，除了在分割時(shí)要對(duì)分割的粒度做好評(píng)估，
    考慮數(shù)據(jù)平均和負(fù)載平均，后期也將對(duì)項(xiàng)目人員及應(yīng)用程序產(chǎn)生額外的數(shù)據(jù)管理負(fù)擔(dān)。
    
    
    在現(xiàn)實(shí)項(xiàng)目中，往往是這兩種情況兼而有之，這就需要做出權(quán)衡，甚至既需要垂直切分，又需要水平切分。
    我們的游戲項(xiàng)目便綜合使用了垂直與水平切分，我們首先對(duì)數(shù)據(jù)庫(kù)進(jìn)行垂直切分，
    然后，再針對(duì)一部分表，通常是

6.單庫(kù)多表 ：

    隨著用戶數(shù)量的增加，user 表的數(shù)據(jù)量會(huì)越來(lái)越大，當(dāng)數(shù)據(jù)量達(dá)到一定程度的時(shí)候?qū)?user表的查詢會(huì)漸漸的變慢，從而影響整個(gè) DB 的性能。
    如果使用 mysql, 還有一個(gè)更嚴(yán)重的問(wèn)題是，當(dāng)需要添加一列的時(shí)候，mysql會(huì)鎖表，期間所有的讀寫(xiě)操作只能等待。
    
    可以將 user 進(jìn)行水平的切分，產(chǎn)生兩個(gè)表結(jié)構(gòu)完全一樣的 user_0000,user_0001 等表，user_0000 + user_0001 + …的數(shù)據(jù)剛好是一份完整的數(shù)據(jù)。

7.多庫(kù)多表 ：

    隨著數(shù)據(jù)量增加也許單臺(tái) DB 的存儲(chǔ)空間不夠，隨著查詢量的增加單臺(tái)數(shù)據(jù)庫(kù)服務(wù)器已經(jīng)沒(méi)辦法支撐。這個(gè)時(shí)候可以再對(duì)數(shù)據(jù)庫(kù)
    進(jìn)行水平區(qū)分。
    
分庫(kù)分表規(guī)則舉例：
    通過(guò)分庫(kù)分表規(guī)則查找到對(duì)應(yīng)的表和庫(kù)的過(guò)程。如分庫(kù)分表的規(guī)則是 user_id 除以 4 的方式，當(dāng)用戶新注冊(cè)了一個(gè)賬號(hào)，賬
    號(hào) id 的 123,我們可以通過(guò) id 除以 4 的方式確定此賬號(hào)應(yīng)該保存到 User_0003 表中。當(dāng)用戶 123 登錄的時(shí)候，我們通過(guò) 123 除
    以 4 后確定記錄在 User_0003 中。
    
mysql 讀寫(xiě)分離：
    在實(shí)際的應(yīng)用中，絕大部分情況都是讀遠(yuǎn)大于寫(xiě)。Mysql 提供了讀寫(xiě)分離的機(jī)制，所有的寫(xiě)操作都必須對(duì)應(yīng)到 Master，讀操作
    可以在 Master 和 Slave 機(jī)器上進(jìn)行，Slave 與 Master 的結(jié)構(gòu)完全一樣，一個(gè) Master 可以有多個(gè) Slave,甚至 Slave 下還可以掛
    Slave,通過(guò)此方式可以有效的提高 DB 集群的每秒查詢率. 所有的寫(xiě)操作都是先在 Master 上操作，然后同步更新到 Slave 上，所以從 Master 同步到 Slave 機(jī)器有一定的延遲，當(dāng)系統(tǒng)很
    繁忙的時(shí)候，延遲問(wèn)題會(huì)更加嚴(yán)重，Slave 機(jī)器數(shù)量的增加也會(huì)使這個(gè)問(wèn)題更加嚴(yán)重。
    此外，可以看出 Master 是集群的瓶頸，當(dāng)寫(xiě)操作過(guò)多，會(huì)嚴(yán)重影響到 Master 的穩(wěn)定性，如果 Master 掛掉，整個(gè)集群都將不能
    正常工作。
    所以，1. 當(dāng)讀壓力很大的時(shí)候，可以考慮添加 Slave 機(jī)器的分式解決，但是當(dāng) Slave 機(jī)器達(dá)到一定的數(shù)量就得考慮分庫(kù)了。 2. 當(dāng)寫(xiě)壓力很大的時(shí)候，就必須得進(jìn)行分庫(kù)操作。

--內(nèi)容整理自傳智面試寶典