MySQL基礎技能與原理―-基礎技能篇

MySQL基礎技能與原理

——基礎技能

MySQLDBATeam

彭立勛（.penglixun.）MySQL基礎技能與原理―-基礎技能篇共28頁，您現(xiàn)在瀏覽的是第1頁!內容概要1.MySQL開發(fā)流程規(guī)范2.MySQL數(shù)據(jù)類型與處理函數(shù)3.MySQL高級特性4.MySQL與Oracle差異比較5.MySQL調優(yōu)基礎MySQL基礎技能與原理―-基礎技能篇共28頁，您現(xiàn)在瀏覽的是第2頁!MySQL開發(fā)流程規(guī)范線上文檔：B2B-OPS命名規(guī)范：數(shù)據(jù)庫/表/字段命名對象設計規(guī)范：數(shù)據(jù)類型，表設計，約束使用SQL編寫規(guī)范：綁定變量與替代變量，數(shù)據(jù)類型轉換，表連接，分頁查詢等MySQL基礎技能與原理―-基礎技能篇共28頁，您現(xiàn)在瀏覽的是第3頁!數(shù)值類型與處理函數(shù)BIT(M):位字段類型。M表示每個值的位數(shù)，范圍為從1到64。M默認為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ù)點(標度)后面的位數(shù)。實際上是VARCHAR存儲。MySQL基礎技能與原理―-基礎技能篇共28頁，您現(xiàn)在瀏覽的是第4頁!字符類型與處理函數(shù)Char(M):長度固定為創(chuàng)建表時聲明的長度M。長度可以為從0到255的任何值。當保存CHAR值時，在它的右邊填充空格以達到指定的長度。當檢索到CHAR值時，尾部的空格被刪除。在存儲或檢索過程中不進行大小寫轉換。占用空間(M*每字符字節(jié)長度)，UTF8為3字節(jié)。VarChar(M):列中的值為可變長字符串。長度可以指定為0到65,535之間的值。VARCHAR的最大有效長度由最大行大小和使用的字符集確定。

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

當值保存和檢索時尾部的空格仍保留，符合標準SQL。

占用空間(M*每字符字節(jié)長度+長度記錄位)。MySQL基礎技能與原理―-基礎技能篇共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基礎技能與原理―-基礎技能篇共28頁，您現(xiàn)在瀏覽的是第6頁!類型處理小技巧IP存儲：INET_ATON(expr)，將IP轉換為整數(shù)。INET_NTOA(expr)，將整數(shù)轉換為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基礎技能與原理―-基礎技能篇共28頁，您現(xiàn)在瀏覽的是第7頁!存儲過程基礎官方文檔：dev.mysql./doc/refman/5.1/zh/stored-procedures.html變量定義順序必須是：存儲函數(shù)變量游標定義游標異常程序主體定義務必加上授權：CREATEDEFINER=`root`@`localhost`PROCEDUREsp();存儲過程相關權限：被操作表的相關權限及EXECUTE(執(zhí)行權限)，ALTERROUTINE(修改權限)，CREATEROUTINE(創(chuàng)建權限)。存儲過程/刪除內不能調用存儲過程，但可以調用函數(shù)。不能使用動態(tài)游標，CURSOR中不能有動態(tài)的表名。查看創(chuàng)建存儲過程/函數(shù)的語句：SHOWCREATEPROCEDURE/FUNCTIONps;查看所有存儲過程/函數(shù)：SHOWPROCEDURE/FUNCTIONSTATUS[LIKEps];調用存儲過程：CALLsp();調用存儲函數(shù)：SELECTsp();MySQL基礎技能與原理―-基礎技能篇共28頁，您現(xiàn)在瀏覽的是第8頁!分區(qū)表基礎官方文檔：dev.mysql./doc/refman/5.1/zh/partitioning.html分區(qū)類型：RANGE分區(qū)：基于屬于一個給定連續(xù)區(qū)間的列值，把多行分配給分區(qū)。LIST分區(qū)：類似于按RANGE分區(qū)，區(qū)別在于LIST分區(qū)是基于列值匹配一個離散值集合中的某個值來進行選擇。HASH分區(qū)：基于用戶定義的表達式的返回值來進行選擇的分區(qū)，該表達式使用將要插入到表中的這些行的列值進行計算。這個函數(shù)可以包含MySQL中有效的、產生非負整數(shù)值的任何表達式。KEY分區(qū)：類似于按HASH分區(qū)，區(qū)別在于KEY分區(qū)只支持計算一列或多列，且MySQL服務器提供其自身的哈希函數(shù)。必須有一列或多列包含整數(shù)值。子分區(qū)：子分區(qū)是分區(qū)表中每個分區(qū)的再次分割。MySQL基礎技能與原理―-基礎技能篇共28頁，您現(xiàn)在瀏覽的是第9頁!分區(qū)表基礎—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基礎技能與原理―-基礎技能篇共28頁，您現(xiàn)在瀏覽的是第10頁!分區(qū)表基礎—子分區(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基礎技能與原理―-基礎技能篇共28頁，您現(xiàn)在瀏覽的是第11頁!MySQL與Oracle差異比較優(yōu)化器差異SQL方言差異常用功能差異MySQL基礎技能與原理―-基礎技能篇共28頁，您現(xiàn)在瀏覽的是第12頁!SQL方言差異選擇部分行：Oracle使用Rownum，MySQL使用LIMITstart,end;執(zhí)行函數(shù)：Oracle需要SELECTf()FROMdual;MySQL可以直接SELECTf();復制數(shù)據(jù)：Oracle必須有as，CREATETABLEtASSELECT*FROMtable;MySQL的as是可選，CREATETABLEtSELECT*FROMtable;MySQL基礎技能與原理―-基礎技能篇共28頁，您現(xiàn)在瀏覽的是第13頁!MySQL調優(yōu)基礎索引優(yōu)化基礎Schema設計優(yōu)化SQL書寫優(yōu)化MySQL基礎技能與原理―-基礎技能篇共28頁，您現(xiàn)在瀏覽的是第14頁!Schema設計優(yōu)化TEXT/BLOB字段分開單表存儲與原表主鍵一一對應。經常查詢的字段與不經常查詢的字段分開存儲，用主鍵一一對應，例如帖子標題和內容。頻繁進行統(tǒng)計的SQL，可以轉化為表存儲，將查詢壓力分散到更新時。頻繁的對表進行count也可以轉化為統(tǒng)計表存儲，《計數(shù)表的技巧》。MySQL基礎技能與原理―-基礎技能篇共28頁，您現(xiàn)在瀏覽的是第15頁!MySQL數(shù)據(jù)類型與處理函數(shù)數(shù)值類型與處理函數(shù)字符類型與處理函數(shù)時間類型與處理函數(shù)類型處理小技巧官方文檔：dev.mysql./doc/refman/5.1/zh/column-types.html

dev.mysql./doc/refman/5.1/zh/functions.htmlMySQL基礎技能與原理―-基礎技能篇共28頁，您現(xiàn)在瀏覽的是第16頁!數(shù)值類型與處理函數(shù)算數(shù)符運算：SELECTexpr;數(shù)學函數(shù)：ABS(x)，x的絕對值。CEIL(x)，返回不小于X的最小整數(shù)值。FLOOR(x)，返回不大于X的最大整數(shù)值CRC32(x)，計算循環(huán)冗余碼校驗值。RAND()，返回0~1直接隨機浮點數(shù)。SIGN(x)，返回x的符號TRUNCATE(X,D)，返回X被舍去至小數(shù)點后D位的數(shù)字。MySQL基礎技能與原理―-基礎技能篇共28頁，您現(xiàn)在瀏覽的是第17頁!字符類型與處理函數(shù)CHAR_LENGTH(str)：返回值為字符串str的長度，長度的單位為字符。LENGTH(str)：返回值為字符串str的長度，單位為字節(jié)。CONCAT(str1,str2,...)：返回結果為連接參數(shù)產生的字符串。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轉化為大寫字母的字符。MySQL基礎技能與原理―-基礎技能篇共28頁，您現(xiàn)在瀏覽的是第18頁!日期類型與處理函數(shù)DATE_SUB/DATE_ADD：對時間進行加減。CURDATE()：將當前日期按照‘YYYY-MM-DD’或YYYYMMDD格式的值返回。NOW()：返回當前日期和時間值，

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