MySQL介紹及sql語(yǔ)句

1、 數(shù)據(jù)類(lèi)型的優(yōu)化 創(chuàng)建高性能的索引 MySQL語(yǔ)句的優(yōu)化MySQL基礎(chǔ) 一、選擇優(yōu)化的數(shù)據(jù)類(lèi)型更小的通常更好。簡(jiǎn)單就好。盡量避免NULL。1、整數(shù)類(lèi)型存儲(chǔ)整數(shù)，有這幾種：TINYINT，SMALLINT，MEDIUMINT，INT，BIGINT。分別使用8，16，24,32,64為存儲(chǔ)空間。存儲(chǔ)的值范圍從-2(N-1)到2(N-1)-1，其中N是存儲(chǔ)空間的位數(shù)。UNSIGNED屬性表示不允許負(fù)值。例如，TINYINT存儲(chǔ)范圍是-128 127,TINYINT UNSIGNED存儲(chǔ)范圍是0 255。2、實(shí)數(shù)類(lèi)型實(shí)數(shù)是帶有小數(shù)部分的數(shù)字。它們不僅可以存儲(chǔ)小數(shù)部分，也可以使用DECIMAL存儲(chǔ)比BI

2、GINT還大的整數(shù)。MySQL數(shù)據(jù)類(lèi)型的優(yōu)化3、字符串類(lèi)型(1) VARCHAR和CHAR類(lèi)型VARCHAR是存儲(chǔ)可變長(zhǎng)字符串。列的長(zhǎng)度小于或等于255字節(jié)，則使用1字節(jié)表示，否則用兩個(gè)字節(jié)。使用：字符串列的最大長(zhǎng)度比平均長(zhǎng)度大很多；列的更新很少；使用像UTF-8復(fù)雜的字符集，每個(gè)字符使用不同的字節(jié)數(shù)進(jìn)行存儲(chǔ)。CHAR類(lèi)型是定長(zhǎng)的：適合存儲(chǔ)很短的字符串，或者值都接近同一個(gè)長(zhǎng)度。例如：密碼的MD5值，也適合經(jīng)常變更的數(shù)據(jù)，不容易產(chǎn)生碎片。VARCHAR會(huì)刪除末尾空格，CHAR不會(huì)。(2) BINARY和VARBINARY二進(jìn)制字符串，存儲(chǔ)的是字節(jié)碼。 (3) BLOB和TEXT類(lèi)型字符串?dāng)?shù)據(jù)類(lèi)型

3、，分別是二進(jìn)制和字符。二進(jìn)制類(lèi)型包括：TINYBLOB，SMALLBLOB，BLOB，MEDIUMBLOB，LONGBLOB。字符類(lèi)型包括：TINYTEXT，SMALLTEXT，TEXT，MEDIUMTEXT，LONGTEXT；MySQL數(shù)據(jù)類(lèi)型的優(yōu)化3、字符串類(lèi)型(4)使用枚舉(ENUM)代替字符串類(lèi)型枚舉列把不重復(fù)的字符串存儲(chǔ)成一個(gè)預(yù)定義集合，把每個(gè)值在列表中的位置保存為整數(shù)。CREATE TABLE enum_test( e ENUM(autohome,bitauto,pcauto) NOT NULL);INSERT INTO enum_test(e) VALUES (autohome)

4、 ,(pcauto), (bitauto) ;SELECT e+0 FROM enum_test;+-+|e|+-+| 1 | 3 | 2 |+-+枚舉字段是按照內(nèi)部存儲(chǔ)的整數(shù)而不是字符串進(jìn)行排序的。枚舉的缺點(diǎn)是，字符串列表是固定的，添加或刪除字符串必須使用ALTER TABLE。MySQL數(shù)據(jù)類(lèi)型的優(yōu)化4、日期和時(shí)間類(lèi)型MySQL能存儲(chǔ)的最小時(shí)間粒度為秒。(1) DATETIME它能保存從1001到9999年，精度為秒。它把日期和時(shí)間封裝為YYYYMMDDHHMMSS整數(shù)中。其中，DATE只保存日期YYYYMMDD，不保存時(shí)間。(2) TIMESTAMP它只能表示從1970到2038年的時(shí)間

5、。它顯示的值依賴于時(shí)區(qū)。5、選擇標(biāo)識(shí)符一般我們可能用標(biāo)識(shí)列與其他值進(jìn)行比較(關(guān)聯(lián)操作中)，或通過(guò)標(biāo)識(shí)列尋找其他列。所以為標(biāo)識(shí)列選擇合適的數(shù)據(jù)類(lèi)型很重要。比如，在關(guān)聯(lián)表中對(duì)應(yīng)的列要一樣的類(lèi)型。整數(shù)通常是最好的選擇，它們很快并且可以使用AUTO_INCREMENT自增功能。MySQL數(shù)據(jù)類(lèi)型的優(yōu)化 二、MySQL scheme設(shè)計(jì)中的缺陷太多的列太多的關(guān)聯(lián)：MySQL每個(gè)關(guān)聯(lián)操作最多61張表，單個(gè)查詢最好在12個(gè)表以內(nèi)。全能的枚舉：ENUM( ,0,1,2,3,.30) 三、范式和反范式1、范式的有點(diǎn)和缺點(diǎn)優(yōu)點(diǎn)：范式化的更新操作快。 很少的重復(fù)數(shù)據(jù)，修改時(shí)只需要修改很少的數(shù)據(jù)。 缺點(diǎn)：需要關(guān)聯(lián)表操

6、作。2、反范式的有點(diǎn)和缺點(diǎn)優(yōu)點(diǎn)：不需要關(guān)聯(lián)表，單獨(dú)的表也能使用更有效的索引策略。缺點(diǎn)：一個(gè)表中會(huì)存在冗余的數(shù)據(jù)。MySQL數(shù)據(jù)類(lèi)型的優(yōu)化范式化和反范式化例子：范式化結(jié)構(gòu)：SELECT message_text,user_name FROM message INNER JOIN user ON message.user_id=user.id WHERE user.account_type=VIP ORDER BY time;MySQL需要掃描message表的time字段索引。然后去user表檢查是不是VIP，如果只有小部分是VIP，那么這種效率是低效的。反范式化結(jié)構(gòu)：user_meaasge表

7、，加索引(account_type ,time)SELECT message_text,user_name FROM user_message WHERE account_type=VIPORDER BY time;3、混用范式化和反范式化比如user表和message表都存儲(chǔ)account_type字段。但這樣更新account_type操作代價(jià)就高了，需要同時(shí)更新兩張表。MySQL數(shù)據(jù)類(lèi)型的優(yōu)化索引是用來(lái)快速地查找具有特定值的記錄的一種數(shù)據(jù)結(jié)構(gòu)，它是在存儲(chǔ)引擎中實(shí)現(xiàn)的。如果沒(méi)有建立索引，SQL查詢時(shí)要從表的第一個(gè)記錄開(kāi)始掃描整個(gè)表的所有記錄，直至找到符合要求的記錄。索引優(yōu)化應(yīng)該是對(duì)查詢性能

8、優(yōu)化最有效的手段了。 一、索引的類(lèi)型1、B-Tree索引默認(rèn)就是B-Tree索引，它把所有的值都是按順序存儲(chǔ)的。(1) 全值匹配全值匹配指的是和索引中的所有列進(jìn)行匹配。(2) 匹配最左前綴使用索引的第一列。(3) 匹配列前綴可以只匹配某一列的值的開(kāi)頭部分。(4) 匹配范圍值可以查找某一列的值的一定范圍的值。(5) 精確匹配某一列并范圍匹配另外一列第一列全匹配，第二列范圍匹配。創(chuàng)建高性能的索引下面是一些B-Tree索引的限制：(1) 如果不是按照索引的最左列開(kāi)始查找，則無(wú)法使用索引。Index(a,b,c)(2) 不能跳過(guò)索引的列。 Index(a,b,c)(3) 如果查詢中有某個(gè)列的范圍查詢，

9、則其右邊所有列都無(wú)法使用索引優(yōu)化。 index(a,b,c) a=1 AND b LIKE J% AND C = 2016。2、哈希索引哈希索引基于哈希表實(shí)現(xiàn)。對(duì)于每一行數(shù)據(jù)，存儲(chǔ)引擎都會(huì)對(duì)所有的索引列計(jì)算一個(gè)哈希碼，哈希索引將所有的哈希碼存儲(chǔ)在索引中，并在哈希表中保存指向每個(gè)數(shù)據(jù)行的指針。MySQL中只有Memory引擎顯示支持哈希索引。+-+-+ 假設(shè)索引使用哈希函數(shù)f()，它返回下面的值：|fname |lname| f(Arjen ) = 2323 +-+-+ f(Peter ) = 8784 槽(Slot) 值(Value)|Arjen |Lentz | f(Vadim ) = 24

10、58 2323 指向第1行的指針|Peter |Zaitsev| 則哈希索引的數(shù)據(jù)結(jié)構(gòu)是： 2456 指向第3行的指針|Vadim |Tkache| 8784 指向第2行的指針創(chuàng)建高性能的索引 二、高性能的索引策略1、獨(dú)立的列獨(dú)立的列指索引列不能是表達(dá)式的一部分，也不能是函數(shù)的參數(shù)。SELECT actor_id FROM actor WHERE actor_id+1 = 4;SELECT actor_id FROM actor WHERE TO_DAYS(CURRENT_DATE) TO_DAYS(date_col) DATE_SUB(NOW(),INTERVAL 7 DAY) AND ag

11、e BETWEEN 18 AND 25。last_online和age列兩個(gè)范圍條件，但是無(wú)法同時(shí)使用它們進(jìn)行索引。 一、MySQL查詢速度慢1、查詢性能低下最基本的原因是訪問(wèn)的數(shù)據(jù)太多，向數(shù)據(jù)庫(kù)請(qǐng)求了不需要的數(shù)據(jù)。一些典型案例：(1)、查詢不需要的數(shù)據(jù)，例如，查詢出100條數(shù)據(jù)只看前10條，此時(shí)可以在查詢后加上LIMIT 10 條件。(2)、多表關(guān)聯(lián)時(shí)返回全部列 select * from tableA inner join tableB USING(id) where正確的是: select tableA.* from tableA inner join tableB USING(id)

12、where(3)、總是取出全部列每次看到select *的時(shí)候要考慮下是否真的需要查詢?nèi)苛袛?shù)據(jù)。這樣會(huì)讓優(yōu)化器無(wú)法完成索引覆蓋掃描(覆蓋索引)，還會(huì)為服務(wù)器帶來(lái)額外的I/O、內(nèi)存和CPU消耗。2、MySQL是否在掃描額外的記錄(1)、掃描的行數(shù)和返回的行數(shù)(2)、掃描的行數(shù)和訪問(wèn)類(lèi)型(索引、非索引)MySQL語(yǔ)句的優(yōu)化 二、重構(gòu)查詢方式1、一個(gè)復(fù)雜大查詢OR多個(gè)簡(jiǎn)單查詢2、切分查詢：將大查詢切分成小查詢，每個(gè)查詢完全一樣且只完成一小部分查詢功能(鎖住數(shù)據(jù)、占滿整個(gè)事務(wù)日志、消耗系統(tǒng)資源、阻塞很多小查詢)DELETE FROM messages WHERE created DARE_SUB(N

13、OW(), INTERVAL 3 MONTH);DELETE FROM messages WHERE created DARE_SUB(NOW(), INTERVAL 3 MONTH) LIMIT 1000;3、分解關(guān)聯(lián)查詢很多高性能的應(yīng)用會(huì)把關(guān)聯(lián)查詢進(jìn)行分解.先對(duì)每一個(gè)表進(jìn)行單表查詢?cè)賹⒔Y(jié)果在應(yīng)用程序中關(guān)聯(lián)SELECT * FROM tag JOIN tag_post ON tag_post.id = tag.id JOIN post ON tag_post.id = post.id WHERE tag.tag = mysql;改成 SELECT * FROM tag WHERE tag =

14、 mysql; SELECT * FROM tag_post WHERE tag_id = 1234;SELECT * FROM post WHERE post.id in (123,456,567,9094);分解關(guān)聯(lián)查詢的優(yōu)勢(shì)：(1) 讓緩存的效率更高。應(yīng)用程序可以方便的緩存單表查詢對(duì)應(yīng)的結(jié)果對(duì)象。(2) 將查詢分解后，執(zhí)行單個(gè)查詢可以減少鎖的競(jìng)爭(zhēng)。(3) 可以減少冗余記錄的查詢。MySQL語(yǔ)句的優(yōu)化MySQL語(yǔ)句的優(yōu)化 三、查詢執(zhí)行的基礎(chǔ)下面是MySQL執(zhí)行一個(gè)查詢的過(guò)程：(1) 客戶端發(fā)送一條查詢給服務(wù)器(2)服務(wù)器先檢查查詢緩存，如果命中緩存，則立刻返回存儲(chǔ)在緩存中的結(jié)果。否則進(jìn)入下

15、一階段。(3)服務(wù)器進(jìn)行SQL解析、預(yù)處理，再由優(yōu)化器生成對(duì)應(yīng)的執(zhí)行計(jì)劃。(4)MySQL根據(jù)執(zhí)行計(jì)劃，調(diào)用存儲(chǔ)引擎的API來(lái)執(zhí)行查詢。(5)將結(jié)果返回給客戶端。1.MySQL客戶端/服務(wù)器通信協(xié)議通信協(xié)議是”半雙工”的，即在任何一個(gè)時(shí)刻，要么由客戶端向服務(wù)器發(fā)送數(shù)據(jù)，要么有服務(wù)器向客戶端發(fā)送數(shù)據(jù)。并且一旦一端開(kāi)始發(fā)送消息，另一端要接收完整個(gè)消息才能響應(yīng)它。 三、查詢執(zhí)行的基礎(chǔ)2、查詢緩存SQL語(yǔ)句來(lái)了后，如果查詢緩存是打開(kāi)的，MySQL會(huì)通過(guò)一個(gè)對(duì)大小寫(xiě)敏感的哈希查找來(lái)優(yōu)先檢查該SQL語(yǔ)句是否命中查詢緩存。有一個(gè)字節(jié)不同也不會(huì)匹配緩存結(jié)果。命中緩存后會(huì)再檢查用戶權(quán)限，可以的花會(huì)直接從緩存中拿

16、到結(jié)果返回給客戶端。3、查詢優(yōu)化處理查詢的生命周期的下一步是將SQL轉(zhuǎn)換成一個(gè)執(zhí)行計(jì)劃再和存儲(chǔ)引擎進(jìn)行交互。3.1 語(yǔ)法解析器和預(yù)處理SQL語(yǔ)句被解析成一顆”解析樹(shù)”。再使用MySQL語(yǔ)法規(guī)則驗(yàn)證和解析查詢。(關(guān)鍵字和其順序是否正確等)預(yù)處理進(jìn)一步檢查解析樹(shù)是否合法(表和數(shù)據(jù)列是否存在 解析名字是否有歧義)MySQL語(yǔ)句的優(yōu)化 三、查詢執(zhí)行的基礎(chǔ)3.2、查詢優(yōu)化器 3.3、MySQL如何執(zhí)行關(guān)聯(lián)查詢MySQL中每一個(gè)查詢，每一個(gè)片段(包括子查詢，單表的SELECT)都可能是關(guān)聯(lián)。MySQL關(guān)聯(lián)執(zhí)行的策略很簡(jiǎn)單:嵌套循環(huán)關(guān)聯(lián)操作，即先在一個(gè)表中循環(huán)取出單條數(shù)據(jù)，再嵌套循環(huán)下一個(gè)表尋找匹配的行，依

17、次下去找到所有表匹配度行。然后根據(jù)這些行返回查詢中需要的列。例如：查詢先查找第一個(gè)表記錄，再嵌套查詢下一個(gè)關(guān)聯(lián)表，再回溯到上一個(gè)表。SELECT tab1.col1, tab2.col2 FROM tab1 INNER JOIN tab2 USING(col3) WHERE tab1.col1 IN (5,6);假設(shè)MySQL按照查詢中表的順序進(jìn)行關(guān)聯(lián)操作，偽代碼如下：outer_iter = iterator over tab1 where col1 IN(5,6)out_row = outer_iter.nextwhile outer_row inner_iter = iterator o

18、ver tab2 where col3 = outer_row.col3 inner_row = inner_iter.next while inner_row output outer_row.col1, inner_row.col2 MySQL語(yǔ)句的優(yōu)化 三、查詢執(zhí)行的基礎(chǔ)3.3、MySQL如何執(zhí)行關(guān)聯(lián)查詢偽代碼: inner_row = inner_iter.next end outer_row = outer_iter.nextend另一種可視化查詢執(zhí)行計(jì)劃的方法是根據(jù)優(yōu)化器執(zhí)行的路徑繪制出對(duì)應(yīng)的“泳道圖”。如圖所示：MySQL語(yǔ)句的優(yōu)化4、查詢執(zhí)行引擎存儲(chǔ)引擎根據(jù)前面的解析和優(yōu)化階段

19、生成的執(zhí)行計(jì)劃來(lái)完成整個(gè)查詢。5、返回結(jié)果給客戶端MySQL將結(jié)果集返回客戶端是一個(gè)增量、逐步返回的過(guò)程。例如，關(guān)聯(lián)操作查詢中，一旦服務(wù)器處理完最后一個(gè)關(guān)聯(lián)表，開(kāi)始生產(chǎn)第一條結(jié)果時(shí)，就可以開(kāi)始返回結(jié)果集了。如果查詢可以被緩存，那么MySQL會(huì)將結(jié)果放到查詢緩存中。 四、MySQL查詢優(yōu)化器的局限性1、關(guān)聯(lián)子查詢MySQL的一些子查詢實(shí)現(xiàn)的比較糟糕，特別是where中包含IN()的子查詢。我們可以用EXISTS和(LEFT/INNER)JOIN 關(guān)聯(lián)表(有時(shí)候性能也是非常好的甚至是最優(yōu)的)SELECT film_id FROM file WHERE EXISTS(SELECT * FROM fi

20、lm_actor WHEREfilm_id=film_actor.film_id;2、在同一個(gè)表上查詢和更新 UPDATE tab INNER JOIN (SELECT type,count(*) AS cnt FROM tab GROUP BY type) AS der USING(type) SET t = t;MySQL語(yǔ)句的優(yōu)化 五、優(yōu)化特定類(lèi)型的查詢1、優(yōu)化COUNT()查詢COUNT()的兩種作用：它可以統(tǒng)計(jì)某個(gè)列值的數(shù)量，也可以統(tǒng)計(jì)行數(shù)。在統(tǒng)計(jì)列值時(shí)要求列值非空(不統(tǒng)計(jì)NULL)。COUNT(col)時(shí)統(tǒng)計(jì)的是col有值的結(jié)果數(shù)。它統(tǒng)計(jì)結(jié)果集的行數(shù)，當(dāng)它括號(hào)內(nèi)的值不為NULL時(shí)，

21、實(shí)際上就是在統(tǒng)計(jì)行數(shù)，直接用COUNT(*)就可以了。(1) 簡(jiǎn)單的優(yōu)化如何在一個(gè)查詢中統(tǒng)計(jì)同一個(gè)列的不同值的數(shù)量，以減少查詢的語(yǔ)句？MySQL語(yǔ)句的優(yōu)化(1) 簡(jiǎn)單的優(yōu)化如何在一個(gè)查詢中統(tǒng)計(jì)同一個(gè)列的不同值的數(shù)量，以減少查詢的語(yǔ)句？假設(shè)通過(guò)一個(gè)查詢返回各種不同顏色的商品數(shù)量。SELECT SUM(IF(color = blue,1,0) AS bule,SUM(IF(color = red,1,0)AS red FROM color_id;SELECT color,COUNT(color) FROM color_id GROUP BY color;2、優(yōu)化關(guān)聯(lián)查詢(1) 確保ON或者USIN

22、G字句中的列有索引。當(dāng)表A和表B用列c關(guān)聯(lián)時(shí)，如果優(yōu)化器的關(guān)聯(lián)順序是B、A，那么就不需要在B表的對(duì)應(yīng)列上建立索引。(2) 確保任何的GROUP BY和ORDER BY中的表達(dá)式只涉及到一個(gè)表中的列。這樣MySQL才可能使用索引來(lái)優(yōu)化。 MySQL語(yǔ)句的優(yōu)化MySQL語(yǔ)句的優(yōu)化1、使用連接(join)來(lái)代替子查詢 使用子查詢可以一次性的完成很多邏輯上需要多個(gè)步驟才能完成的SQL操作： SQL語(yǔ)句：SELECT * FROM comments2014 WHERE post_id NOT in (SELECT post_id FROM comments2015); 如果使用連接(join)來(lái)完成這個(gè)查詢，速度會(huì)快很多。尤其是給post_id字段建立索引的話，性能會(huì)更好。但join表時(shí)目標(biāo)字段的數(shù)據(jù)類(lèi)型要一致。SQL語(yǔ)句：SELECT * FROM comments20