MySQL基礎(chǔ)技能與原理

MySQL基礎(chǔ)技能與原理

——基礎(chǔ)技能

MySQLDBATeam

彭立勛（.penglixun.）MySQL基礎(chǔ)技能與原理共28頁(yè)，您現(xiàn)在瀏覽的是第1頁(yè)!內(nèi)容概要1.MySQL開(kāi)發(fā)流程規(guī)范2.MySQL數(shù)據(jù)類型與處理函數(shù)3.MySQL高級(jí)特性4.MySQL與Oracle差異比較5.MySQL調(diào)優(yōu)基礎(chǔ)MySQL基礎(chǔ)技能與原理共28頁(yè)，您現(xiàn)在瀏覽的是第2頁(yè)!MySQL開(kāi)發(fā)流程規(guī)范線上文檔：B2B-OPS命名規(guī)范：數(shù)據(jù)庫(kù)/表/字段命名對(duì)象設(shè)計(jì)規(guī)范：數(shù)據(jù)類型，表設(shè)計(jì)，約束使用SQL編寫(xiě)規(guī)范：綁定變量與替代變量，數(shù)據(jù)類型轉(zhuǎn)換，表連接，分頁(yè)查詢等MySQL基礎(chǔ)技能與原理共28頁(yè)，您現(xiàn)在瀏覽的是第3頁(yè)!數(shù)值類型與處理函數(shù)BIT(M):位字段類型。M表示每個(gè)值的位數(shù)，范圍為從1到64。M默認(rèn)為1。TinyINT(M):帶符號(hào)的范圍是-32768到32767。

無(wú)符號(hào)的范圍是0到65535。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到2147483647。

無(wú)符號(hào)的范圍是0到4294967295。4字節(jié)。BigINT(n):帶符號(hào)的范圍是-9223372036854775808到9223372036854775807。無(wú)符號(hào)的范圍是0到18446744073709551615。8字節(jié)。Decimal(m,n):

M是總位數(shù)，D是小數(shù)點(diǎn)(標(biāo)度)后面的位數(shù)。實(shí)際上是VARCHAR存儲(chǔ)。MySQL基礎(chǔ)技能與原理共28頁(yè)，您現(xiàn)在瀏覽的是第4頁(yè)!字符類型與處理函數(shù)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)度可以指定為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)度記錄位)。MySQL基礎(chǔ)技能與原理共28頁(yè)，您現(xiàn)在瀏覽的是第5頁(yè)!日期類型與處理函數(shù)TIMESTAMP：以'YYYY-MM-DDHH:MM:SS'格式檢索和顯示TIMESTAMP值。支持的范圍為‘1970-01-0100:00:00’到2037年。占用4字節(jié)DATETIME：以'YYYY-MM-DDHH:MM:SS'格式檢索和顯示DATETIME值。

支持的范圍為'1000-01-0100:00:00'到'9999-12-3123:59:59'。

占用8字節(jié)。DATE：用'YYYY-MM-DD'格式檢索和顯示DATE值。

支持的范圍是'1000-01-01'到'9999-12-31'。

占用4字節(jié)。MySQL基礎(chǔ)技能與原理共28頁(yè)，您現(xiàn)在瀏覽的是第6頁(yè)!類型處理小技巧IP存儲(chǔ)：INET_ATON(expr)，將IP轉(zhuǎn)換為整數(shù)。INET_NTOA(expr)，將整數(shù)轉(zhuǎn)換為IP。mysql>SELECTINET_ATON('209.207.224.40');->3520061480mysql>SELECTINET_NTOA(3520061480);->'209.207.224.40‘判斷IP段：INET_ATON('209.207.224.1')<=IPANDIP<=INET_ATON('209.207.224.255');VARCHAR存儲(chǔ)：(3*4+3)+1=16位，UTF8字符集15*3+1=46字節(jié)INT存儲(chǔ)：4字節(jié)MySQL基礎(chǔ)技能與原理共28頁(yè)，您現(xiàn)在瀏覽的是第7頁(yè)!存儲(chǔ)過(guò)程基礎(chǔ)官方文檔：dev.mysql./doc/refman/5.1/zh/stored-procedures.html變量定義順序必須是：存儲(chǔ)函數(shù)變量游標(biāo)定義游標(biāo)異常程序主體定義務(wù)必加上授權(quán)：CREATEDEFINER=`root`@`localhost`PROCEDUREsp();存儲(chǔ)過(guò)程相關(guān)權(quán)限：被操作表的相關(guān)權(quán)限及EXECUTE(執(zhí)行權(quán)限)，ALTERROUTINE(修改權(quán)限)，CREATEROUTINE(創(chuàng)建權(quán)限)。存儲(chǔ)過(guò)程/刪除內(nèi)不能調(diào)用存儲(chǔ)過(guò)程，但可以調(diào)用函數(shù)。不能使用動(dòng)態(tài)游標(biāo)，CURSOR中不能有動(dòng)態(tài)的表名。查看創(chuàng)建存儲(chǔ)過(guò)程/函數(shù)的語(yǔ)句：SHOWCREATEPROCEDURE/FUNCTIONps;查看所有存儲(chǔ)過(guò)程/函數(shù)：SHOWPROCEDURE/FUNCTIONSTATUS[LIKEps];調(diào)用存儲(chǔ)過(guò)程：CALLsp();調(diào)用存儲(chǔ)函數(shù)：SELECTsp();MySQL基礎(chǔ)技能與原理共28頁(yè)，您現(xiàn)在瀏覽的是第8頁(yè)!分區(qū)表基礎(chǔ)官方文檔：dev.mysql./doc/refman/5.1/zh/partitioning.html分區(qū)類型：RANGE分區(qū)：基于屬于一個(gè)給定連續(xù)區(qū)間的列值，把多行分配給分區(qū)。LIST分區(qū)：類似于按RANGE分區(qū)，區(qū)別在于LIST分區(qū)是基于列值匹配一個(gè)離散值集合中的某個(gè)值來(lái)進(jìn)行選擇。HASH分區(qū)：基于用戶定義的表達(dá)式的返回值來(lái)進(jìn)行選擇的分區(qū)，該表達(dá)式使用將要插入到表中的這些行的列值進(jìn)行計(jì)算。這個(gè)函數(shù)可以包含MySQL中有效的、產(chǎn)生非負(fù)整數(shù)值的任何表達(dá)式。KEY分區(qū)：類似于按HASH分區(qū)，區(qū)別在于KEY分區(qū)只支持計(jì)算一列或多列，且MySQL服務(wù)器提供其自身的哈希函數(shù)。必須有一列或多列包含整數(shù)值。子分區(qū)：子分區(qū)是分區(qū)表中每個(gè)分區(qū)的再次分割。MySQL基礎(chǔ)技能與原理共28頁(yè)，您現(xiàn)在瀏覽的是第9頁(yè)!分區(qū)表基礎(chǔ)—List分區(qū)LIST分區(qū)中每個(gè)分區(qū)的定義和選擇是基于某列的值從屬于一個(gè)值列表集中的一個(gè)值。LIST分區(qū)通過(guò)”VALUESIN(value_list)”的方式來(lái)定義每個(gè)分區(qū)，其中”value_list”是一個(gè)通過(guò)逗號(hào)分隔的整數(shù)列表。CREATETABLEemployees(idINTNOTNULL,store_idINT)PARTITIONBYLIST(store_id)PARTITIONp1VALUESIN(3,5,6,9,17),PARTITIONp2VALUESIN(1,2,10,11,19,20),PARTITIONp3VALUESIN(4,12,13,14,18),PARTITIONp4VALUESIN(7,8,15,16))；MySQL基礎(chǔ)技能與原理共28頁(yè)，您現(xiàn)在瀏覽的是第10頁(yè)!分區(qū)表基礎(chǔ)—子分區(qū)子分區(qū)是分區(qū)表中每個(gè)分區(qū)的再次分割，子分區(qū)既可以使用HASH希分區(qū)，也可以使用KEY分區(qū)，每個(gè)分區(qū)必須有相同數(shù)量的子分區(qū)。CREATETABLEts(idINT,purchasedDATE)PARTITIONBYRANGE(YEAR(purchased))SUBPARTITIONBYHASH(TO_DAYS(purchased))(PARTITIONp0VALUESLESSTHAN(1990)(SUBPARTITIONs0,SUBPARTITIONs1),PARTITIONp1VALUESLESSTHAN(2000)(SUBPARTITIONs2,SUBPARTITIONs3));MySQL基礎(chǔ)技能與原理共28頁(yè)，您現(xiàn)在瀏覽的是第11頁(yè)!MySQL與Oracle差異比較優(yōu)化器差異SQL方言差異常用功能差異MySQL基礎(chǔ)技能與原理共28頁(yè)，您現(xiàn)在瀏覽的是第12頁(yè)!SQL方言差異選擇部分行：Oracle使用Rownum，MySQL使用LIMITstart,end;執(zhí)行函數(shù)：Oracle需要SELECTf()FROMdual;MySQL可以直接SELECTf();復(fù)制數(shù)據(jù)：Oracle必須有as，CREATETABLEtASSELECT*FROMtable;MySQL的as是可選，CREATETABLEtSELECT*FROMtable;MySQL基礎(chǔ)技能與原理共28頁(yè)，您現(xiàn)在瀏覽的是第13頁(yè)!MySQL調(diào)優(yōu)基礎(chǔ)索引優(yōu)化基礎(chǔ)Schema設(shè)計(jì)優(yōu)化SQL書(shū)寫(xiě)優(yōu)化MySQL基礎(chǔ)技能與原理共28頁(yè)，您現(xiàn)在瀏覽的是第14頁(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ǔ)，《計(jì)數(shù)表的技巧》。MySQL基礎(chǔ)技能與原理共28頁(yè)，您現(xiàn)在瀏覽的是第15頁(yè)!MySQL數(shù)據(jù)類型與處理函數(shù)數(shù)值類型與處理函數(shù)字符類型與處理函數(shù)時(shí)間類型與處理函數(shù)類型處理小技巧官方文檔：dev.mysql./doc/refman/5.1/zh/column-types.html

dev.mysql./doc/refman/5.1/zh/functions.htmlMySQL基礎(chǔ)技能與原理共28頁(yè)，您現(xiàn)在瀏覽的是第16頁(yè)!數(shù)值類型與處理函數(shù)算數(shù)符運(yùn)算：SELECTexpr;數(shù)學(xué)函數(shù)：ABS(x)，x的絕對(duì)值。CEIL(x)，返回不小于X的最小整數(shù)值。FLOOR(x)，返回不大于X的最大整數(shù)值CRC32(x)，計(jì)算循環(huán)冗余碼校驗(yàn)值。RAND()，返回0~1直接隨機(jī)浮點(diǎn)數(shù)。SIGN(x)，返回x的符號(hào)TRUNCATE(X,D)，返回X被舍去至小數(shù)點(diǎn)后D位的數(shù)字。MySQL基礎(chǔ)技能與原理共28頁(yè)，您現(xiàn)在瀏覽的是第17頁(yè)!字符類型與處理函數(shù)CHAR_LENGTH(str)：返回值為字符串str的長(zhǎng)度，長(zhǎng)度的單位為字符。LENGTH(str)：返回值為字符串str的長(zhǎng)度，單位為字節(jié)。CONCAT(str1,str2,...)：返回結(jié)果為連接參數(shù)產(chǎn)生的字符串。CONCAT_WS(separator,str1,str2,...)：個(gè)參數(shù)是其它參數(shù)的分隔符。

分隔符的位置放在要連接的兩個(gè)字符串之間。LEFT(str,len)：返回從字符串str開(kāi)始的len最左字符。RIGHT(str,len)；從字符串str開(kāi)始，返回最右len字符。SUBSTRING(str,pos,len)：從字符串str返回一個(gè)長(zhǎng)度同len字符相同的子字符串，

起始于位置pos。LOWER(str)：返回字符串str變?yōu)樾?xiě)字母的字符。UPPER(str)：返回字符串str轉(zhuǎn)化為大寫(xiě)字母的字符。MySQL基礎(chǔ)技能與原理共28頁(yè)，您現(xiàn)在瀏覽的是第18頁(yè)!日期類型與處理函數(shù)DATE_SUB/DATE_ADD：對(duì)時(shí)間進(jìn)行加減。CURDATE()：將當(dāng)前日期按照‘YYYY-MM-DD’或YYYYMMDD格式的值返回。NOW()：返回當(dāng)前日期和時(shí)間值，

其格式為‘YYYY-MM-DDHH:MM:SS’或YYYYMMDDHHMMSS。組合使用：mysql>SELECTDATE_ADD('1998-01-02',INTERVAL31DAY);->'1998-02-02'MySQL基礎(chǔ)技能與原理共28頁(yè)，您現(xiàn)在瀏覽的是第19頁(yè)!MySQL高級(jí)特性存儲(chǔ)過(guò)程基礎(chǔ)觸發(fā)器基礎(chǔ)分區(qū)表基礎(chǔ)應(yīng)用場(chǎng)景MySQL基礎(chǔ)技能與原理共28頁(yè)，您現(xiàn)在瀏覽的是第20頁(yè)!觸發(fā)器基礎(chǔ)官方文檔：dev.mysql./doc/refman/5.1/en/create-trigger.html定義務(wù)必加上授權(quán)：CREATEDEFINER=`root`@`localhost`Triggertgr();定義語(yǔ)句：CREATEDEFINER=`root`@`localhost`Triggertgr()AFTER/BEFOREINSERT/UPDATE/DELETEONtableFOREACHROW;數(shù)據(jù)調(diào)用：NEW.*（更新后數(shù)據(jù)）OLD.*（更新前數(shù)據(jù)）行級(jí)觸發(fā)器，每一行都會(huì)觸發(fā)動(dòng)作內(nèi)部可以調(diào)用存儲(chǔ)過(guò)程和函數(shù)每種類型的Trigger在一張表上只能建立一個(gè)MySQL基礎(chǔ)技能與原理共28頁(yè)，您現(xiàn)在瀏覽的是第21頁(yè)!分區(qū)表基礎(chǔ)—Range分區(qū)每個(gè)分區(qū)包含那些分區(qū)表達(dá)式的值位于一個(gè)給定的連續(xù)區(qū)間內(nèi)的行。這些區(qū)間要連續(xù)且不能相互重疊，使用VALUESLESSTHAN操作符來(lái)進(jìn)行定義。CREATETABLEemployees(idINTNOTNULL,store_idINTNOTNULL)PARTITIONBYRANGE(store_id)(PARTITIONp0VALUESLESSTHAN(6),//1~5PARTITIONp1VALUESLESSTHAN(11),//6~10PARTITIONp2VALUESLESSTHAN(16),//11~15PARTITIONp3VALUESLESSTHANMAXVALUE//16~MAX)；MySQL基礎(chǔ)技能與原理共28頁(yè)，您現(xiàn)在瀏覽的是第22頁(yè)!分區(qū)表基礎(chǔ)—Hash分區(qū)在HASH分區(qū)中，MySQL自動(dòng)完成分區(qū)值，你所要做的只是基于將要被哈希的列值指定一個(gè)列值或表達(dá)式，以及指定被分區(qū)的表將要被分割成的分區(qū)數(shù)量。CREATETABLEemployees(idINTNOTNULL,store_idINT)PARTITIONBYHASH(store_id)PARTITIONS4；MySQL基礎(chǔ)技能與原理共28頁(yè)，您現(xiàn)在瀏覽的是第23頁(yè)!高級(jí)特性應(yīng)用場(chǎng)景存儲(chǔ)過(guò)程不得包含業(yè)務(wù)邏輯，一般只做臨時(shí)數(shù)據(jù)訂正或固定邏輯用。觸發(fā)器目前只用于Otter，一般不使用。分區(qū)表適合數(shù)據(jù)量大，但查詢會(huì)固定在若干個(gè)數(shù)據(jù)集中的場(chǎng)景，但不要把分區(qū)表混合排序使用，可能帶來(lái)嚴(yán)重的性能故障。（BRMMS發(fā)生過(guò)）MySQL基礎(chǔ)技能與原理共28頁(yè)，您現(xiàn)在瀏覽的是第24頁(yè)!優(yōu)化器差異MySQL對(duì)子查詢會(huì)處理為臨時(shí)表，所以一般JOIN效率比做子查詢高。MySQL在JOIN中對(duì)ORDERBY、GROUPBY優(yōu)化較弱，JOIN中的排序分組幾乎都會(huì)使用FilleSort和臨時(shí)表MySQL只有RBO，沒(méi)有CBO，所以總是選擇類型上最優(yōu)的執(zhí)行計(jì)劃，而不一定是開(kāi)銷最小的。MySQL對(duì)于OR，很多時(shí)候并不會(huì)優(yōu)化成兩次索引訪問(wèn)或合并索引，所以O(shè)R最好寫(xiě)成兩條SQL，做UNIONALL。IN中存在NULL條件，可能會(huì)讓優(yōu)化器走成全表掃描。MySQL基礎(chǔ)技能與原理共28頁(yè)，您現(xiàn)在瀏覽的是第25頁(yè)!常用功能差異鎖差異：Oracle鎖加在數(shù)據(jù)塊上，InnoDB鎖加在主鍵索因上，所以InnoDB總是有主鍵索引。導(dǎo)入導(dǎo)出數(shù)據(jù)：Oracle采用EXP/IMP導(dǎo)入導(dǎo)出。MySQL采用mysqldump導(dǎo)出，導(dǎo)入可以采用管道或source。mysqldump–u用戶–p密碼DB[table1table2…]>db.sql提交方式：MySQL默認(rèn)自動(dòng)提交，Oracle默認(rèn)手動(dòng)提交，MySQL大批量操作最好改變?yōu)槭謩?dòng)提交，SETAUTOCOMMIT=0SQL緩存：MySQL只能緩存結(jié)果集，不能緩存SQL解析結(jié)果數(shù)據(jù)庫(kù)對(duì)象：Oracle將數(shù)據(jù)庫(kù)對(duì)象編譯存儲(chǔ)，直接執(zhí)行二進(jìn)制碼，MySQL只存儲(chǔ)代碼，臨時(shí)解析執(zhí)行，所以