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

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

——基礎(chǔ)技能

MySQLDBATeam

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

無符號的范圍是0到65535。1字節(jié)。SmallINT(M):帶符號的范圍是-32768到32767。

無符號的范圍是0到65535。2字節(jié)。MediumINT(M):帶符號的范圍是-8388608到8388607。

無符號的范圍是0到16777215。3字節(jié)。INT(M):帶符號的范圍是-2147483648到2147483647。

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

M是總位數(shù)，D是小數(shù)點(diǎn)(標(biāo)度)后面的位數(shù)。實(shí)際上是VARCHAR存儲。MySQL基礎(chǔ)技能與原理―-基礎(chǔ)技能篇共28頁，您現(xiàn)在瀏覽的是第4頁!字符類型與處理函數(shù)Char(M):長度固定為創(chuàng)建表時(shí)聲明的長度M。長度可以為從0到255的任何值。當(dāng)保存CHAR值時(shí)，在它的右邊填充空格以達(dá)到指定的長度。當(dāng)檢索到CHAR值時(shí)，尾部的空格被刪除。在存儲或檢索過程中不進(jìn)行大小寫轉(zhuǎn)換。占用空間(M*每字符字節(jié)長度)，UTF8為3字節(jié)。VarChar(M):列中的值為可變長字符串。長度可以指定為0到65,535之間的值。VARCHAR的最大有效長度由最大行大小和使用的字符集確定。

整體最大長度是65,532字節(jié)。VARCHAR值保存時(shí)不進(jìn)行填充。

當(dāng)值保存和檢索時(shí)尾部的空格仍保留，符合標(biāo)準(zhǔn)SQL。

占用空間(M*每字符字節(jié)長度+長度記錄位)。MySQL基礎(chǔ)技能與原理―-基礎(chǔ)技能篇共28頁，您現(xiàn)在瀏覽的是第5頁!日期類型與處理函數(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ǔ)技能與原理―-基礎(chǔ)技能篇共28頁，您現(xiàn)在瀏覽的是第6頁!類型處理小技巧IP存儲：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存儲：(3*4+3)+1=16位，UTF8字符集15*3+1=46字節(jié)INT存儲：4字節(jié)MySQL基礎(chǔ)技能與原理―-基礎(chǔ)技能篇共28頁，您現(xiàn)在瀏覽的是第7頁!存儲過程基礎(chǔ)官方文檔：dev.mysql./doc/refman/5.1/zh/stored-procedures.html變量定義順序必須是：存儲函數(shù)變量游標(biāo)定義游標(biāo)異常程序主體定義務(wù)必加上授權(quán)：CREATEDEFINER=`root`@`localhost`PROCEDUREsp();存儲過程相關(guān)權(quán)限：被操作表的相關(guān)權(quán)限及EXECUTE(執(zhí)行權(quán)限)，ALTERROUTINE(修改權(quán)限)，CREATEROUTINE(創(chuàng)建權(quán)限)。存儲過程/刪除內(nèi)不能調(diào)用存儲過程，但可以調(diào)用函數(shù)。不能使用動態(tài)游標(biāo)，CURSOR中不能有動態(tài)的表名。查看創(chuàng)建存儲過程/函數(shù)的語句：SHOWCREATEPROCEDURE/FUNCTIONps;查看所有存儲過程/函數(shù)：SHOWPROCEDURE/FUNCTIONSTATUS[LIKEps];調(diào)用存儲過程：CALLsp();調(diào)用存儲函數(shù)：SELECTsp();MySQL基礎(chǔ)技能與原理―-基礎(chǔ)技能篇共28頁，您現(xiàn)在瀏覽的是第8頁!分區(qū)表基礎(chǔ)官方文檔：dev.mysql./doc/refman/5.1/zh/partitioning.html分區(qū)類型：RANGE分區(qū)：基于屬于一個給定連續(xù)區(qū)間的列值，把多行分配給分區(qū)。LIST分區(qū)：類似于按RANGE分區(qū)，區(qū)別在于LIST分區(qū)是基于列值匹配一個離散值集合中的某個值來進(jìn)行選擇。HASH分區(qū)：基于用戶定義的表達(dá)式的返回值來進(jìn)行選擇的分區(qū)，該表達(dá)式使用將要插入到表中的這些行的列值進(jìn)行計(jì)算。這個函數(shù)可以包含MySQL中有效的、產(chǎn)生非負(fù)整數(shù)值的任何表達(dá)式。KEY分區(qū)：類似于按HASH分區(qū)，區(qū)別在于KEY分區(qū)只支持計(jì)算一列或多列，且MySQL服務(wù)器提供其自身的哈希函數(shù)。必須有一列或多列包含整數(shù)值。子分區(qū)：子分區(qū)是分區(qū)表中每個分區(qū)的再次分割。MySQL基礎(chǔ)技能與原理―-基礎(chǔ)技能篇共28頁，您現(xiàn)在瀏覽的是第9頁!分區(qū)表基礎(chǔ)—List分區(qū)LIST分區(qū)中每個分區(qū)的定義和選擇是基于某列的值從屬于一個值列表集中的一個值。LIST分區(qū)通過”VALUESIN(value_list)”的方式來定義每個分區(qū)，其中”value_list”是一個通過逗號分隔的整數(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ǔ)技能與原理―-基礎(chǔ)技能篇共28頁，您現(xiàn)在瀏覽的是第10頁!分區(qū)表基礎(chǔ)—子分區(qū)子分區(qū)是分區(qū)表中每個分區(qū)的再次分割，子分區(qū)既可以使用HASH希分區(qū)，也可以使用KEY分區(qū)，每個分區(qū)必須有相同數(shù)量的子分區(qū)。CREATETABLEts(idINT,purchasedDATE)PARTITIONBYRANGE(YEAR(purchased))SUBPARTITIONBYHASH(TO_DAYS(purchased))(PARTITIONp0VALUESLESSTHAN(1990)(SUBPARTITIONs0,SUBPARTITIONs1),PARTITIONp1VALUESLESSTHAN(2000)(SUBPARTITIONs2,SUBPARTITIONs3));MySQL基礎(chǔ)技能與原理―-基礎(chǔ)技能篇共28頁，您現(xiàn)在瀏覽的是第11頁!MySQL與Oracle差異比較優(yōu)化器差異SQL方言差異常用功能差異MySQL基礎(chǔ)技能與原理―-基礎(chǔ)技能篇共28頁，您現(xiàn)在瀏覽的是第12頁!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ǔ)技能與原理―-基礎(chǔ)技能篇共28頁，您現(xiàn)在瀏覽的是第13頁!MySQL調(diào)優(yōu)基礎(chǔ)索引優(yōu)化基礎(chǔ)Schema設(shè)計(jì)優(yōu)化SQL書寫優(yōu)化MySQL基礎(chǔ)技能與原理―-基礎(chǔ)技能篇共28頁，您現(xiàn)在瀏覽的是第14頁!Schema設(shè)計(jì)優(yōu)化TEXT/BLOB字段分開單表存儲與原表主鍵一一對應(yīng)。經(jīng)常查詢的字段與不經(jīng)常查詢的字段分開存儲，用主鍵一一對應(yīng)，例如帖子標(biāo)題和內(nèi)容。頻繁進(jìn)行統(tǒng)計(jì)的SQL，可以轉(zhuǎn)化為表存儲，將查詢壓力分散到更新時(shí)。頻繁的對表進(jìn)行count也可以轉(zhuǎn)化為統(tǒng)計(jì)表存儲，《計(jì)數(shù)表的技巧》。MySQL基礎(chǔ)技能與原理―-基礎(chǔ)技能篇共28頁，您現(xiàn)在瀏覽的是第15頁!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ǔ)技能與原理―-基礎(chǔ)技能篇共28頁，您現(xiàn)在瀏覽的是第16頁!數(shù)值類型與處理函數(shù)算數(shù)符運(yùn)算：SELECTexpr;數(shù)學(xué)函數(shù)：ABS(x)，x的絕對值。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的符號TRUNCATE(X,D)，返回X被舍去至小數(shù)點(diǎn)后D位的數(shù)字。MySQL基礎(chǔ)技能與原理―-基礎(chǔ)技能篇共28頁，您現(xiàn)在瀏覽的是第17頁!字符類型與處理函數(shù)CHAR_LENGTH(str)：返回值為字符串str的長度，長度的單位為字符。LENGTH(str)：返回值為字符串str的長度，單位為字節(jié)。CONCAT(str1,str2,...)：返回結(jié)果為連接參數(shù)產(chǎn)生的字符串。CONCAT_WS(separator,str1,str2,...)：個參數(shù)是其它參數(shù)的分隔符。

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

起始于位置pos。LOWER(str)：返回字符串str變?yōu)樾懽帜傅淖址?。UPPER(str)：返回字符串str轉(zhuǎn)化為大寫字母的字符。MySQL基礎(chǔ)技能與原理―-基礎(chǔ)技能篇共28頁，您現(xiàn)在瀏覽的是第18頁!日期類型與處理函數(shù)DATE_SUB/DATE_ADD：對時(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ǔ)技能與原理―-基礎(chǔ)技能篇共28頁，您現(xiàn)在瀏覽的是第19頁!MySQL高級特性存儲過程基礎(chǔ)觸發(fā)器基礎(chǔ)分區(qū)表基礎(chǔ)應(yīng)用場景MySQL基礎(chǔ)技能與原理―-基礎(chǔ)技能篇共28頁，您現(xiàn)在瀏覽的是第20頁!觸發(fā)器基礎(chǔ)官方文檔：dev.mysql./doc/refman/5.1/en/create-trigger.html定義務(wù)必加上授權(quán)：CREATEDEFINER=`root`@`localhost`Triggertgr();定義語句：CREATEDEFINER=`root`@`localhost`Triggertgr()AFTER/BEFOREINSERT/UPDATE/DELETEONtableFOREACHROW;數(shù)據(jù)調(diào)用：NEW.*（更新后數(shù)據(jù)）OLD.*（更新前數(shù)據(jù)）行級觸發(fā)器，每一行都會觸發(fā)動作內(nèi)部可以調(diào)用存儲過程和函數(shù)每種類型的Trigger在一張表上只能建立一個MySQL基礎(chǔ)技能與原理―-基礎(chǔ)技能篇共28頁，您現(xiàn)在瀏覽的是第21頁!分區(qū)表基礎(chǔ)—Range分區(qū)每個分區(qū)包含那些分區(qū)表達(dá)式的值位于一個給定的連續(xù)區(qū)間內(nèi)的行。這些區(qū)間要連續(xù)且不能相互重疊，使用VALUESLESSTHAN操作符來進(jìn)行定義。CREATETABLEemployees(idINTNOTNULL,store_idINTNOTNULL)PARTITIONBYRANGE(store_id)(PARTITIONp0VALUESLESSTHAN(6),//1~5PARTITIONp1VALUESLESSTHAN(11),//6~10PARTITIONp2VALUESLESSTHAN(16),//11~15PARTITIONp3VALUESLESSTHANMAXVALUE//16~MAX)；MySQL基礎(chǔ)技能與原理―-基礎(chǔ)技能篇共28頁，您現(xiàn)在瀏覽的是第22頁!分區(qū)表基礎(chǔ)—Hash分區(qū)在HASH分區(qū)中，MySQL自動完成分區(qū)值，你所要做的只是基于將要被哈希的列值指定一個列值或表達(dá)式，以及指定被分區(qū)的表將要被分割成的分區(qū)數(shù)量。CREATETABLEemployees(idINTNOTNULL,store_idINT)PARTITIONBYHASH(store_id)PARTITIONS4；MySQL基礎(chǔ)技能與原理―-基礎(chǔ)技能篇共28頁，您現(xiàn)在瀏覽的是第23頁!高級特性應(yīng)用場景存儲過程不得包含業(yè)務(wù)邏輯，一般只做臨時(shí)數(shù)據(jù)訂正或固定邏輯用。觸發(fā)器目前只用于Otter，一般不使用。分區(qū)表適合數(shù)據(jù)量大，但查詢會固定在若干個數(shù)據(jù)集中的場景，但不要把分區(qū)表混合排序使用，可能帶來嚴(yán)重的性能故障。（BRMMS發(fā)生過）MySQL基礎(chǔ)技能與原理―-基礎(chǔ)技能篇共28頁，您現(xiàn)在瀏覽的是第24頁!優(yōu)化器差異MySQL對子查詢會處理為臨時(shí)表，所以一般JOIN效率比做子查詢高。MySQL在JOIN中對ORDERBY、GROUPBY優(yōu)化較弱，JOIN中的排序分組幾乎都會使用FilleSort和臨時(shí)表MySQL只有RBO，沒有CBO，所以總是選擇類型上最優(yōu)的執(zhí)行計(jì)劃，而不一定是開銷最小的。MySQL對于OR，很多時(shí)候并不會優(yōu)化成兩次索引訪問或合并索引，所以O(shè)R最好寫成兩條SQL，做UNIONALL。IN中存在NULL條件，可能會讓優(yōu)化器走成全表掃描。MySQL基礎(chǔ)技能與原理―-基礎(chǔ)技能篇共28頁，您現(xiàn)在瀏覽的是第25頁!常用功能差異鎖差異：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)自動提交，Oracle默認(rèn)手動提交，MySQL大批量操作最好改變?yōu)槭謩犹峤唬琒ETAUTOCOMMIT=0SQL緩存：MySQL只能緩存結(jié)果集，不能緩存SQL解析結(jié)果數(shù)據(jù)庫對象：Oracle將數(shù)據(jù)庫對象編譯存儲，直接執(zhí)行二進(jìn)制碼，MySQL只存儲代碼，臨時(shí)解析執(zhí)行，所以My