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

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

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

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

4、HAR_LENGTH(string) #返回string中的字符個(gè)數(shù) 統(tǒng)計(jì)字符個(gè)數(shù)，就不區(qū)分是漢字還是字母或數(shù)字，也跟字符集沒(méi)有關(guān)系，若統(tǒng)計(jì)的是字節(jié)數(shù)，則由字符是漢字、字母或數(shù)字類型，以及字符集共同決定。字符串函數(shù)第5頁(yè)，共31頁(yè)。日期類型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頁(yè)，共31頁(yè)。日期操作函數(shù)日期操作函數(shù) 獲取當(dāng)前時(shí)間：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ù)，我們?cè)陂_(kāi)過(guò)程中，一般使用DATE_ADD()函數(shù)，若要作日期減去一個(gè)數(shù)字的方式，就使用負(fù)數(shù)。 DATEDIFF(expr1,expr2)，是返回 開(kāi)始日期expr1與 結(jié)束日期expr2之間，相差的天數(shù) ，返回值為正數(shù)或負(fù)數(shù)。返回日期某部分信息的函數(shù)：YEAR(expr1) 返回日期expr1部分的年份； MONTH(expr1) 返回日期expr1部分的月份；DAY(expr1)返回expr1部分的天數(shù)；WEEKDAY(expr1)返回expr1對(duì)應(yīng)的星期數(shù)字第7頁(yè)，共31頁(yè)。類型轉(zhuǎn)換類型轉(zhuǎn)換函

7、數(shù) 字符串轉(zhuǎn)換成日期方式，DATE_FORMAT()或STR_TO_DATE()， 兩個(gè)函數(shù)的格式如下： DATE_FORMAT(expr1,format) STR_TO_DATE(expr1, format) 常用的日期格式Y(jié)YYY-MM-DD HH:MM:SS 對(duì)應(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頁(yè)，共31頁(yè)。注意事項(xiàng)數(shù)據(jù)類型轉(zhuǎn)換規(guī)范基本原則： 在所有 Query 的 Where 條件中必須使用

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

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

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

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

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

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

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

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

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

17、后，表內(nèi)的行才按照特地的順序存儲(chǔ)，按照 需要可以是asc或desc方式。 如果索引由多個(gè)字段組成將最用來(lái)查詢過(guò)濾的字段放在前面 可能會(huì)有更好的性能。索引優(yōu)化第20頁(yè)，共31頁(yè)。索引優(yōu)化MySQL只在認(rèn)為走索引可以篩去85%以上數(shù)據(jù)的時(shí)候，才使用索引，如果達(dá)不到這個(gè)標(biāo)準(zhǔn)，索引是無(wú)意義的。前綴索引：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)化：可以在條件是等于的時(shí)候繼續(xù)使用索引排序，或者條件中的不等于字段就是排序字段。 例如：SELECT col1 FROM table WHERE col2=1 AND col30 ORDER BY col3，可以完全使用索引(col2,col3)避免排序。但條件是col2, =, =, , =, IF NULL和BETWEEN 將會(huì)

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