MySQL基礎(chǔ)及開發(fā)注意事項(xiàng)課件

1、內(nèi)容概要數(shù)據(jù)類型與處理函數(shù)存儲引擎及常用存儲引擎特點(diǎn)及存儲結(jié)構(gòu)應(yīng)用層性能優(yōu)化MYSQL復(fù)制第1頁，共31頁。數(shù)據(jù)類型使用原則選擇表示數(shù)據(jù)的最小類型選擇簡單的數(shù)據(jù)類型盡量避免NULL數(shù)據(jù)類型第2頁，共31頁。整數(shù)類型TinyINT(M): 帶符號的范圍是-128到127。 無符號的范圍是0到255。1字節(jié)。SmallINT(M): 帶符號的范圍是-32768到32767 無符號的范圍是0到65535。2字節(jié)。MediumINT(M): 帶符號的范圍是-8388608到8388607。 無符號的范圍是0到16777215。3字節(jié)。INT(M): 帶符號的范圍是-2147483648到2147483

2、647。 無符號的范圍是0到4294967295。4字節(jié)。BigINT(n): 帶符號的范圍是-9223372036854775808到9223372036854775807。 無符號的范圍是0到18446744073709551615。8字節(jié)。第3頁，共31頁。字符類型Char(M): 長度固定為創(chuàng)建表時聲明的長度M。長度可以為從0到255的任何值。 當(dāng)保存CHAR值時，在它的右邊填充空格以達(dá)到指定的長度。 當(dāng)檢索到CHAR值時，尾部的空格被刪除。 在存儲或檢索過程中不進(jìn)行大小寫轉(zhuǎn)換。 占用空間(M*每字符字節(jié)長度)，UTF8為3字節(jié)。VarChar(M): 列中的值為可變長字符串。長度可以

3、指定為0到65,535之間的值。 VARCHAR的最大有效長度由最大行大小和使用的字符集確定。 整體最大長度是65,532字節(jié)。 VARCHAR值保存時不進(jìn)行填充。 當(dāng)值保存和檢索時尾部的空格仍保留，符合標(biāo)準(zhǔn)SQL。 占用空間(M*每字符字節(jié)長度+長度記錄位)。第4頁，共31頁。字符串函數(shù) MySQL中字符串連接方法，使用CONCAT() 或CONCAT_ WS()函數(shù)，語法如下： CONCAT(string1,string2,.) CONCAT_ WS(separator,string1,string2,.) 字符串長度統(tǒng)計： LENGTH(string) #返回string所占的字節(jié)數(shù) C

4、HAR_LENGTH(string) #返回string中的字符個數(shù) 統(tǒng)計字符個數(shù)，就不區(qū)分是漢字還是字母或數(shù)字，也跟字符集沒有關(guān)系，若統(tǒng)計的是字節(jié)數(shù)，則由字符是漢字、字母或數(shù)字類型，以及字符集共同決定。字符串函數(shù)第5頁，共31頁。日期類型TIMESTAMP：以YYYY-MM-DD HH:MM:SS格式檢索和顯示TIMESTAMP值。 支持的范圍為1970-01-01 00:00:00到2037年。 占用4字節(jié)DATETIME：以YYYY-MM-DD HH:MM:SS格式檢索和顯示DATETIME值。 支持的范圍為1000-01-01 00:00:00到9999-12-31 23:59:59。

5、 占用8字節(jié)。DATE：用YYYY-MM-DD格式檢索和顯示DATE值。 支持的范圍是1000-01-01到 9999-12-31。 占用4字節(jié)。第6頁，共31頁。日期操作函數(shù)日期操作函數(shù) 獲取當(dāng)前時間：NOW()，CURDATE()、CURTIME() NOW()函數(shù)精確到秒，格式：YYYY-MM-DD HH:MM:SS CURDATE函數(shù)精確到天，格式：YYYY-MM-DD CURTIME函數(shù)精確到秒，格式：HH:MM:SS日期數(shù)值的加減函數(shù)： DATE_ADD(date,INTERVAL expr type) DATE_SUB(date,INTERVAL expr type) 常用的幾種

6、type類型：YEAR、MONTH、DAY、HOUR、MINUTE，其中expr可以為正數(shù)或負(fù)數(shù)，我們在開過程中，一般使用DATE_ADD()函數(shù)，若要作日期減去一個數(shù)字的方式，就使用負(fù)數(shù)。 DATEDIFF(expr1,expr2)，是返回 開始日期expr1與 結(jié)束日期expr2之間，相差的天數(shù) ，返回值為正數(shù)或負(fù)數(shù)。返回日期某部分信息的函數(shù)：YEAR(expr1) 返回日期expr1部分的年份； MONTH(expr1) 返回日期expr1部分的月份；DAY(expr1)返回expr1部分的天數(shù)；WEEKDAY(expr1)返回expr1對應(yīng)的星期數(shù)字第7頁，共31頁。類型轉(zhuǎn)換類型轉(zhuǎn)換函

7、數(shù) 字符串轉(zhuǎn)換成日期方式，DATE_FORMAT()或STR_TO_DATE()， 兩個函數(shù)的格式如下： DATE_FORMAT(expr1,format) STR_TO_DATE(expr1, format) 常用的日期格式Y(jié)YYY-MM-DD HH:MM:SS 對應(yīng)的format為 %Y-%m-%d %H:%i:%S 通用的類型轉(zhuǎn)換函數(shù)： CAST(expr AS type) CONVERT(expr,type) CONVERT(expr USING transcoding_name) 第8頁，共31頁。注意事項(xiàng)數(shù)據(jù)類型轉(zhuǎn)換規(guī)范基本原則： 在所有 Query 的 Where 條件中必須使用

8、和過濾字段完全一致的數(shù)據(jù)類型，杜絕任何隱式類型轉(zhuǎn)換，避免造成因?yàn)閿?shù)據(jù)類型不匹配而導(dǎo)致 Query 執(zhí)行計劃的出錯，造成性能問題.詳細(xì)說明：1 所有 Where 條件的字段上不允許使用函數(shù)做類型轉(zhuǎn)換，如有需要轉(zhuǎn)換類型，只能轉(zhuǎn)換過濾值,而不是轉(zhuǎn)換字段.2 在表連接 Query 中，如果連接條件兩端的數(shù)據(jù)類型不一致，必須保證將驅(qū)動表的連接條件數(shù)據(jù)類型轉(zhuǎn)換為與被驅(qū)動表一致的數(shù)據(jù)類型. 第9頁，共31頁。注意事項(xiàng)字段上添加函數(shù)使用規(guī)范基本原則： 禁止在 WHERE 條件中出現(xiàn)的過濾字段上，使用任何函數(shù)進(jìn)行類型或格式的轉(zhuǎn)換；正確的做法是把傳入比較的值轉(zhuǎn)換為列類型所需要的。錯誤的寫法：SELECT user

9、name FROM gl_user WHERE DATE_FORMAT(gmt_create, %Y%m%d%H%i%s)=20090501022300;正確的寫法：SELECT username FROM gl_user WHERE gmt_create=DATE_FORMAT(20090501022300, %Y-%m-%d %H:%i:s);第10頁，共31頁。存儲引擎 MyISAM：默認(rèn)的MySQL插件式存儲引擎，它是在Web、數(shù)據(jù)倉儲和其他應(yīng)用環(huán)境下最常使用的存儲引擎之一。注意，通過更改STORAGE_ENGINE配置變量，能夠方便地更改MySQL服務(wù)器的默認(rèn)存儲引擎。 InnoDB

10、：用于事務(wù)處理應(yīng)用程序，具有眾多特性，包括ACID事務(wù)支持。 BDB：可替代InnoDB的事務(wù)引擎，支持COMMIT、ROLLBACK和其他事務(wù)特性。 Memory：將所有數(shù)據(jù)保存在RAM中，在需要快速查找引用和其他類似數(shù)據(jù)的環(huán)境下，可提供極快的訪問。Merge：允許MySQL DBA或開發(fā)人員將一系列等同的MyISAM表以邏輯方式組合在一起，并作為1個對象引用它們。對于諸如數(shù)據(jù)倉儲等VLDB環(huán)境十分適合。 Archive：為大量很少引用的歷史、歸檔、或安全審計信息的存儲和檢索提供了完美的解決方案。 Federated：能夠?qū)⒍鄠€分離的MySQL服務(wù)器鏈接起來，從多個物理服務(wù)器創(chuàng)建一個邏輯數(shù)據(jù)

11、庫。十分適合于分布式環(huán)境或數(shù)據(jù)集市環(huán)境。 Cluster/NDB：MySQL的簇式數(shù)據(jù)庫引擎，尤其適合于具有高性能查找要求的應(yīng)用程序，這類查找需求還要求具有最高的正常工作時間和可用性。 Other：其他存儲引擎包括CSV（引用由逗號隔開的用作數(shù)據(jù)庫表的文件）， Blackhole（用于臨時禁止對數(shù)據(jù)庫的應(yīng)用程序輸入），以及Example引擎（可為快速創(chuàng)建定制的插件式存儲引擎提供幫助）。 第11頁，共31頁。MyISAM MyISAM 特點(diǎn) 數(shù)據(jù)存儲方式簡單，使用 B+ Tree 進(jìn)行索引 使用三個文件定義一個表：.MYI .MYD .frm 少碎片、支持大文件、能夠進(jìn)行索引壓縮 二進(jìn)制層次的文

12、件可以移植 (Linux Windows) 訪問速度飛快，是所有MySQL文件引擎中速度最快的 不支持一些數(shù)據(jù)庫特性，比如 事務(wù)、外鍵約束等 Table level lock，性能稍差，更適合讀取多的操作 表數(shù)據(jù)容量有限，一般建議單表數(shù)據(jù)量介于 50w200w 第12頁，共31頁。MyISAMMyISAM 索引結(jié)構(gòu)第13頁，共31頁。InnoDBInnoDB 特點(diǎn)使用 Table Space 的方式來進(jìn)行數(shù)據(jù)存儲 (ibdata1, ib_logfile0) 支持 事務(wù)、外鍵約束等數(shù)據(jù)庫特性 Rows level lock , 讀寫性能都非常優(yōu)秀 能夠承載大數(shù)據(jù)量的存儲和訪問 擁有自己獨(dú)立的緩

13、沖池，能夠緩存數(shù)據(jù)和索引 在關(guān)閉自動提交的情況下，與MyISAM引擎速度差異不大第14頁，共31頁。InnoDBInnoDB 數(shù)據(jù)結(jié)構(gòu)第15頁，共31頁。InnoDB實(shí)現(xiàn)兩種B+Tree索引，一種是列值為Key，主鍵位置為Value即 (列值, 主鍵位置) 的非主鍵索引（Secondary Index），另一種是主鍵索引，兩種索引的每個葉子節(jié)點(diǎn)都有一個雙向指針分別指向前驅(qū)和后繼節(jié)點(diǎn)。主鍵索引即聚集索引（Cluster Index），它不僅有主鍵，而且有主鍵所屬的全部數(shù)據(jù)，所以在InnoDB中，主鍵索引即數(shù)據(jù)。在InnoDB中，即使用戶不指定主鍵，InnoDB也會生成一個隱含主鍵，這種情況下，I

14、nnoDB的性能比采用序列主鍵性能下降30%左右。InnoDB第16頁，共31頁。應(yīng)用優(yōu)化設(shè)計合理的數(shù)據(jù)表結(jié)構(gòu)：適當(dāng)?shù)臄?shù)據(jù)冗余對數(shù)據(jù)表建立合適有效的數(shù)據(jù)庫索引數(shù)據(jù)查詢：編寫簡潔高效的SQL語句第17頁，共31頁。Schema設(shè)計優(yōu)化TEXT/BLOB字段分開單表存儲與原表主鍵一一對應(yīng)。經(jīng)常查詢的字段與不經(jīng)常查詢的字段分開存儲，用主鍵一一對應(yīng)，例如帖子標(biāo)題和內(nèi)容。頻繁進(jìn)行統(tǒng)計的SQL，可以轉(zhuǎn)化為表存儲，將查詢壓力分散到更新時。頻繁的對表進(jìn)行count也可以轉(zhuǎn)化為統(tǒng)計表存儲選擇合適的數(shù)據(jù)類型：如果能夠定長盡量定長使用 ENUM 而不是 VARCHAR,ENUM類型是非?？旌途o湊的，在實(shí)際上，其保存

15、的是 TINYINT，但其外表上顯示為字符串。這樣一來，用這個字段來做一些選項(xiàng)列表變得相當(dāng)?shù)耐昝?。為了避免聯(lián)表查詢，有時候可以適當(dāng)?shù)臄?shù)據(jù)冗余，比如郵箱、姓名這些不容易更改的數(shù)據(jù)最好給每個字段都設(shè)定 default 值第18頁，共31頁。索引優(yōu)化索引建立原則（一） 一般針對數(shù)據(jù)分散的關(guān)鍵字進(jìn)行建立索引，比如ID、QQ， 像性別、狀態(tài)值等等建立索引沒有意義 字段唯一，最少，不可為null 對大數(shù)據(jù)量表建立聚集索引，避免更新操作帶來的碎片。 盡量使用短索引，一般對int、char/varchar、date/time 等 類型的字段建立索引 需要的時候建立聯(lián)合索引，但是要注意查詢SQL語句的編寫 謹(jǐn)

16、慎建立 unique 類型的索引（唯一索引） 大文本字段不建立為索引，如果要對大文本字段進(jìn)行檢索， 可以考慮全文索引 頻繁更新的列不適合建立索引第19頁，共31頁。索引建立原則（二） order by 字句中的字段，where 子句中字段，最常用的sql 語句中字段，應(yīng)建立索引。 唯一性約束，系統(tǒng)將默認(rèn)為改字段建立索引。 對于只是做查詢用的數(shù)據(jù)庫索引越多越好，但對于在線實(shí)時 系統(tǒng)建議控制在5個以內(nèi)。 索引不僅能提高查詢SQL性能，同時也可以提高帶where字句 的update，Delete SQL性能。 Decimal 類型字段不要單獨(dú)建立為索引，但覆蓋索引可以包 含這些字段。 只有建立索引以

17、后，表內(nèi)的行才按照特地的順序存儲，按照 需要可以是asc或desc方式。 如果索引由多個字段組成將最用來查詢過濾的字段放在前面 可能會有更好的性能。索引優(yōu)化第20頁，共31頁。索引優(yōu)化MySQL只在認(rèn)為走索引可以篩去85%以上數(shù)據(jù)的時候，才使用索引，如果達(dá)不到這個標(biāo)準(zhǔn)，索引是無意義的。前綴索引：MySQL可以利用索引的前向部分，但不可以利用后向部分，不支持反向索引。 例如：SELECT col2 FROM table WHERE col1=1;可以使用索引(col1,col2)，但不可以使用(col2,col1).不等比較的優(yōu)化：如果索引的前向部分在WHERE中是等于，那么可以使用索引，如果索

18、引的前向部分再WHERE中是不等比較，那么不可以為后面的等于比較使用索引 例如：SELECT col1 FROM table WHERE col2=1 AND col3 10;可以完全使用索引(col2,col3)，但只可以使用(col3,col2)的前綴，排序的優(yōu)化：可以在條件是等于的時候繼續(xù)使用索引排序，或者條件中的不等于字段就是排序字段。 例如：SELECT col1 FROM table WHERE col2=1 AND col30 ORDER BY col3，可以完全使用索引(col2,col3)避免排序。但條件是col2, =, =, , =, IF NULL和BETWEEN 將會

19、使用 索引， 如果對某個索引字段進(jìn)行 LIKE 查詢，使用 LIKE %abc% 不能使用索引，使用 LIKE abc% 將能夠使用索引 如果在SQL里使用了MySQL部分自帶函數(shù)，索引將失效，同時將無法 使用 MySQL 的 Query Cache，比如 LEFT(), SUBSTR(), TO_DAYS() DATE_FORMAT(), 等，如果使用了 OR 或 IN，索引也將失效 使用 Explain 語句來幫助改進(jìn)我們的SQL語句第22頁，共31頁。編寫高效的 SQL （二） 不要在where 子句中的“=”左邊進(jìn)行算術(shù)或表達(dá)式運(yùn)算，否則系統(tǒng)將 可能無法正確使用索引 盡量不要在where條件中使用函數(shù)，否則將不能使用索引 避免使用 select *, 只取需要的字段 對于大數(shù)據(jù)量的查詢，盡量避免在SQL語句中使用order by 字句，避免 額為的開銷，替代為使用ADO.NET 來實(shí)現(xiàn)。 如果插入的數(shù)據(jù)量很大，用select into 替代 insert into 能帶來更好的性能 采用連接操作，避免過多的子查詢，產(chǎn)生的CPU和IO開銷 只關(guān)心需要的表和滿足條件的數(shù)據(jù) 適當(dāng)使用臨