MySQL介紹及sql語句

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

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

3、，分別是二進制和字符。二進制類型包括：TINYBLOB，SMALLBLOB，BLOB，MEDIUMBLOB，LONGBLOB。字符類型包括：TINYTEXT，SMALLTEXT，TEXT，MEDIUMTEXT，LONGTEXT；MySQL數(shù)據(jù)類型的優(yōu)化3、字符串類型(4)使用枚舉(ENUM)代替字符串類型枚舉列把不重復(fù)的字符串存儲成一個預(yù)定義集合，把每個值在列表中的位置保存為整數(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)部存儲的整數(shù)而不是字符串進行排序的。枚舉的缺點是，字符串列表是固定的，添加或刪除字符串必須使用ALTER TABLE。MySQL數(shù)據(jù)類型的優(yōu)化4、日期和時間類型MySQL能存儲的最小時間粒度為秒。(1) DATETIME它能保存從1001到9999年，精度為秒。它把日期和時間封裝為YYYYMMDDHHMMSS整數(shù)中。其中，DATE只保存日期YYYYMMDD，不保存時間。(2) TIMESTAMP它只能表示從1970到2038年的時間

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

6、作。2、反范式的有點和缺點優(yōu)點：不需要關(guān)聯(lián)表，單獨的表也能使用更有效的索引策略。缺點：一個表中會存在冗余的數(shù)據(jù)。MySQL數(shù)據(jù)類型的優(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表都存儲account_type字段。但這樣更新account_type操作代價就高了，需要同時更新兩張表。MySQL數(shù)據(jù)類型的優(yōu)化索引是用來快速地查找具有特定值的記錄的一種數(shù)據(jù)結(jié)構(gòu)，它是在存儲引擎中實現(xiàn)的。如果沒有建立索引，SQL查詢時要從表的第一個記錄開始掃描整個表的所有記錄，直至找到符合要求的記錄。索引優(yōu)化應(yīng)該是對查詢性能

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

9、則其右邊所有列都無法使用索引優(yōu)化。 index(a,b,c) a=1 AND b LIKE J% AND C = 2016。2、哈希索引哈希索引基于哈希表實現(xiàn)。對于每一行數(shù)據(jù)，存儲引擎都會對所有的索引列計算一個哈希碼，哈希索引將所有的哈希碼存儲在索引中，并在哈希表中保存指向每個數(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、獨立的列獨立的列指索引列不能是表達式的一部分，也不能是函數(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列兩個范圍條件，但是無法同時使用它們進行索引。 一、MySQL查詢速度慢1、查詢性能低下最基本的原因是訪問的數(shù)據(jù)太多，向數(shù)據(jù)庫請求了不需要的數(shù)據(jù)。一些典型案例：(1)、查詢不需要的數(shù)據(jù)，例如，查詢出100條數(shù)據(jù)只看前10條，此時可以在查詢后加上LIMIT 10 條件。(2)、多表關(guān)聯(lián)時返回全部列 select * from tableA inner join tableB USING(id) where正確的是: select tableA.* from tableA inner join tableB USING(id)

12、where(3)、總是取出全部列每次看到select *的時候要考慮下是否真的需要查詢?nèi)苛袛?shù)據(jù)。這樣會讓優(yōu)化器無法完成索引覆蓋掃描(覆蓋索引)，還會為服務(wù)器帶來額外的I/O、內(nèi)存和CPU消耗。2、MySQL是否在掃描額外的記錄(1)、掃描的行數(shù)和返回的行數(shù)(2)、掃描的行數(shù)和訪問類型(索引、非索引)MySQL語句的優(yōu)化 二、重構(gòu)查詢方式1、一個復(fù)雜大查詢OR多個簡單查詢2、切分查詢：將大查詢切分成小查詢，每個查詢完全一樣且只完成一小部分查詢功能(鎖住數(shù)據(jù)、占滿整個事務(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)用會把關(guān)聯(lián)查詢進行分解.先對每一個表進行單表查詢再將結(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)勢：(1) 讓緩存的效率更高。應(yīng)用程序可以方便的緩存單表查詢對應(yīng)的結(jié)果對象。(2) 將查詢分解后，執(zhí)行單個查詢可以減少鎖的競爭。(3) 可以減少冗余記錄的查詢。MySQL語句的優(yōu)化MySQL語句的優(yōu)化 三、查詢執(zhí)行的基礎(chǔ)下面是MySQL執(zhí)行一個查詢的過程：(1) 客戶端發(fā)送一條查詢給服務(wù)器(2)服務(wù)器先檢查查詢緩存，如果命中緩存，則立刻返回存儲在緩存中的結(jié)果。否則進入下

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

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

17、次下去找到所有表匹配度行。然后根據(jù)這些行返回查詢中需要的列。例如：查詢先查找第一個表記錄，再嵌套查詢下一個關(guān)聯(lián)表，再回溯到上一個表。SELECT tab1.col1, tab2.col2 FROM tab1 INNER JOIN tab2 USING(col3) WHERE tab1.col1 IN (5,6);假設(shè)MySQL按照查詢中表的順序進行關(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ōu)化 三、查詢執(zhí)行的基礎(chǔ)3.3、MySQL如何執(zhí)行關(guān)聯(lián)查詢偽代碼: inner_row = inner_iter.next end outer_row = outer_iter.nextend另一種可視化查詢執(zhí)行計劃的方法是根據(jù)優(yōu)化器執(zhí)行的路徑繪制出對應(yīng)的“泳道圖”。如圖所示：MySQL語句的優(yōu)化4、查詢執(zhí)行引擎存儲引擎根據(jù)前面的解析和優(yōu)化階段

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

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

21、實際上就是在統(tǒng)計行數(shù)，直接用COUNT(*)就可以了。(1) 簡單的優(yōu)化如何在一個查詢中統(tǒng)計同一個列的不同值的數(shù)量，以減少查詢的語句？MySQL語句的優(yōu)化(1) 簡單的優(yōu)化如何在一個查詢中統(tǒng)計同一個列的不同值的數(shù)量，以減少查詢的語句？假設(shè)通過一個查詢返回各種不同顏色的商品數(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字句中的列有索引。當表A和表B用列c關(guān)聯(lián)時，如果優(yōu)化器的關(guān)聯(lián)順序是B、A，那么就不需要在B表的對應(yīng)列上建立索引。(2) 確保任何的GROUP BY和ORDER BY中的表達式只涉及到一個表中的列。這樣MySQL才可能使用索引來優(yōu)化。 MySQL語句的優(yōu)化MySQL語句的優(yōu)化1、使用連接(join)來代替子查詢 使用子查詢可以一次性的完成很多邏輯上需要多個步驟才能完成的SQL操作： SQL語句：SELECT * FROM comments2014 WHERE post_id NOT in (SELECT post_id FROM comments2015); 如果使用連接(join)來完成這個查詢，速度會快很多。尤其是給post_id字段建立索引的話，性能會更好。但join表時目標字段的數(shù)據(jù)類型要一致。SQL語句：SELECT * FROM comments20