MySQL基礎技能與原理參考幻燈片

1、MySQL基礎技能與原理基礎技能,MySQL DBA Team,彭立勛（）,內(nèi)容概要,1. MySQL開發(fā)流程規(guī)范 2. MySQL數(shù)據(jù)類型與處理函數(shù) 3. MySQL高級特性 4. MySQL與Oracle差異比較 5. MySQL調(diào)優(yōu)基礎,MySQL開發(fā)流程規(guī)范,線上文檔：B2B-OPS 命名規(guī)范：數(shù)據(jù)庫/表/字段命名 對象設計規(guī)范：數(shù)據(jù)類型，表設計，約束使用 SQL編寫規(guī)范：綁定變量與替代變量，數(shù)據(jù)類型轉(zhuǎn)換，表連接，分頁查詢等,MySQL數(shù)據(jù)類型與處理函數(shù),數(shù)值類型與處理函數(shù) 字符類型與處理函數(shù) 時間類型與處理函數(shù) 類型處理小技巧 官方文檔： ,數(shù)值類型與處理函數(shù),BIT(M):位字段類

2、型。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到

3、9223372036854775807。 無符號的范圍是0到18446744073709551615。8字節(jié)。 Decimal(m,n): M是總位數(shù)，D是小數(shù)點(標度)后面的位數(shù)。實際上是VARCHAR存儲。,數(shù)值類型與處理函數(shù),算數(shù)符運算：SELECT expr; 數(shù)學函數(shù)：ABS(x)，x的絕對值。 CEIL(x)，返回不小于X 的最小整數(shù)值。 FLOOR(x)，返回不大于X的最大整數(shù)值 CRC32(x)，計算循環(huán)冗余碼校驗值。 RAND()，返回01直接隨機浮點數(shù)。 SIGN(x)，返回x的符號 TRUNCATE(X,D)，返回X被舍去至小數(shù)點后D位的數(shù)字。,字符類型與處理函數(shù),Cha

4、r(M): 長度固定為創(chuàng)建表時聲明的長度M。長度可以為從0到255的任何值。 當保存CHAR值時，在它的右邊填充空格以達到指定的長度。 當檢索到CHAR值時，尾部的空格被刪除。 在存儲或檢索過程中不進行大小寫轉(zhuǎn)換。 占用空間(M*每字符字節(jié)長度)，UTF8為3字節(jié)。 VarChar(M): 列中的值為可變長字符串。長度可以指定為0到65,535之間的值。 VARCHAR的最大有效長度由最大行大小和使用的字符集確定。 整體最大長度是65,532字節(jié)。 VARCHAR值保存時不進行填充。 當值保存和檢索時尾部的空格仍保留，符合標準SQL。 占用空間(M*每字符字節(jié)長度+長度記錄位)。,字符類型與處

5、理函數(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字

6、符相同的子字符串， 起始于位置 pos。 LOWER(str)：返回字符串 str 變?yōu)樾懽帜傅淖址?UPPER(str)：返回字符串str轉(zhuǎn)化為大寫字母的字符。,日期類型與處理函數(shù),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。 占用8字節(jié)。 DATE：用YYYY-MM-DD格

7、式檢索和顯示DATE值。 支持的范圍是1000-01-01到 9999-12-31。 占用4字節(jié)。,日期類型與處理函數(shù),DATE_SUB/DATE_ADD：對時間進行加減。 CURDATE()：將當前日期按照YYYY-MM-DD 或YYYYMMDD 格式的值返回。 NOW()：返回當前日期和時間值， 其格式為 YYYY-MM-DD HH:MM:SS 或YYYYMMDDHHMMSS。 組合使用：mysql SELECT DATE_ADD(1998-01-02, INTERVAL 31 DAY); - 1998-02-02,類型處理小技巧,IP存儲：INET_ATON(expr)，將IP轉(zhuǎn)換為整數(shù)

8、。 INET_NTOA(expr)，將整數(shù)轉(zhuǎn)換為IP。 mysql SELECT INET_ATON(209.207.224.40); - 3520061480 mysql SELECT INET_NTOA(3520061480); - 209.207.224.40 判斷IP段：INET_ATON(209.207.224.1) = IP AND IP = INET_ATON(209.207.224.255); VARCHAR存儲：(3*4+3)+1=16位，UTF8字符集15*3+1=46字節(jié) INT存儲：4字節(jié),MySQL高級特性,存儲過程基礎 觸發(fā)器基礎 分區(qū)表基礎 應用場景,存儲過程基礎

9、,官方文檔： 變量定義順序必須是：存儲函數(shù)變量游標定義游標異常程序主體 定義務必加上授權(quán)： CREATE DEFINER=rootlocalhost PROCEDURE sp(); 存儲過程相關權(quán)限：被操作表的相關權(quán)限及EXECUTE(執(zhí)行權(quán)限)， ALTER ROUTINE(修改權(quán)限)，CREATE ROUTINE(創(chuàng)建權(quán)限)。 存儲過程/刪除內(nèi)不能調(diào)用存儲過程，但可以調(diào)用函數(shù)。 不能使用動態(tài)游標， CURSOR中不能有動態(tài)的表名。 查看創(chuàng)建存儲過程/函數(shù)的語句：SHOW CREATE PROCEDURE/FUNCTION ps; 查看所有存儲過程/函數(shù)：SHOW PROCEDURE/FUN

10、CTION STATUS LIKE ps; 調(diào)用存儲過程：CALL sp(); 調(diào)用存儲函數(shù)：SELECT sp();,觸發(fā)器基礎,官方文檔： 定義務必加上授權(quán)： CREATE DEFINER=rootlocalhost Trigger tgr(); 定義語句：CREATE DEFINER=rootlocalhost Trigger tgr() AFTER/BEFORE INSERT/UPDATE/DELETE ON table FOR EACH ROW; 數(shù)據(jù)調(diào)用：NEW.*（更新后數(shù)據(jù)） OLD.*（更新前數(shù)據(jù)） 行級觸發(fā)器，每一行都會觸發(fā)動作 內(nèi)部可以調(diào)用存儲過程和函數(shù) 每種類型的Tri

11、gger在一張表上只能建立一個,分區(qū)表基礎,官方文檔： 分區(qū)類型： RANGE分區(qū)：基于屬于一個給定連續(xù)區(qū)間的列值，把多行分配給分區(qū)。 LIST 分區(qū)：類似于按RANGE分區(qū)，區(qū)別在于LIST分區(qū)是基于列值匹配一個離散值集合中的某個值來進行選擇。 HASH分區(qū)：基于用戶定義的表達式的返回值來進行選擇的分區(qū)，該表達式使用將要插入到表中的這些行的列值進行計算。這個函數(shù)可以包含MySQL 中有效的、產(chǎn)生非負整數(shù)值的任何表達式。 KEY 分區(qū)：類似于按HASH分區(qū)，區(qū)別在于KEY分區(qū)只支持計算一列或多列，且MySQL服務器提供其自身的哈希函數(shù)。必須有一列或多列包含整數(shù)值。 子分區(qū)：子分區(qū)是分區(qū)表中每個

12、分區(qū)的再次分割。,分區(qū)表基礎Range分區(qū),每個分區(qū)包含那些分區(qū)表達式的值位于一個給定的連續(xù)區(qū)間內(nèi)的行。這些區(qū)間要連續(xù)且不能相互重疊，使用VALUES LESS THAN操作符來進行定義。 CREATE TABLE employees ( id INT NOT NULL, store_id INT NOT NULL ) PARTITION BY RANGE (store_id) ( PARTITION p0 VALUES LESS THAN (6), /15 PARTITION p1 VALUES LESS THAN (11), /610 PARTITION p2 VALUES LESS TH

13、AN (16), /1115 PARTITION p3 VALUES LESS THAN MAXVALUE / 16MAX )；,分區(qū)表基礎List分區(qū),LIST分區(qū)中每個分區(qū)的定義和選擇是基于某列的值從屬于一個值列表集中的一個值。LIST分區(qū)通過”VALUES IN (value_list)”的方式來定義每個分區(qū)，其中”value_list”是一個通過逗號分隔的整數(shù)列表。 CREATE TABLE employees ( id INT NOT NULL, store_id INT ) PARTITION BY LIST(store_id) PARTITION p1 VALUES IN (3,

14、5,6,9,17), PARTITION p2 VALUES IN (1,2,10,11,19,20), PARTITION p3 VALUES IN (4,12,13,14,18), PARTITION p4 VALUES IN (7,8,15,16) )；,分區(qū)表基礎Hash分區(qū),在HASH分區(qū)中，MySQL自動完成分區(qū)值，你所要做的只是基于將要被哈希的列值指定一個列值或表達式，以及指定被分區(qū)的表將要被分割成的分區(qū)數(shù)量。 CREATE TABLE employees ( id INT NOT NULL, store_id INT ) PARTITION BY HASH(store_id)

15、PARTITIONS 4；,分區(qū)表基礎子分區(qū),子分區(qū)是分區(qū)表中每個分區(qū)的再次分割，子分區(qū)既可以使用HASH希分區(qū)，也可以使用KEY分區(qū)，每個分區(qū)必須有相同數(shù)量的子分區(qū)。 CREATE TABLE ts (id INT, purchased DATE) PARTITION BY RANGE(YEAR(purchased) SUBPARTITION BY HASH(TO_DAYS(purchased) ( PARTITION p0 VALUES LESS THAN (1990) ( SUBPARTITION s0, SUBPARTITION s1 ), PARTITION p1 VALUES LE

16、SS THAN (2000) ( SUBPARTITION s2, SUBPARTITION s3 );,高級特性應用場景,存儲過程不得包含業(yè)務邏輯，一般只做臨時數(shù)據(jù)訂正或固定邏輯用。 觸發(fā)器目前只用于Otter，一般不使用。 分區(qū)表適合數(shù)據(jù)量大，但查詢會固定在若干個數(shù)據(jù)集中的場景，但不要把分區(qū)表混合排序使用，可能帶來嚴重的性能故障。（BRMMS發(fā)生過）,MySQL與Oracle差異比較,優(yōu)化器差異 SQL方言差異 常用功能差異,優(yōu)化器差異,MySQL對子查詢會處理為臨時表，所以一般JOIN效率比做子查詢高。 MySQL在JOIN中對ORDER BY、GROUP BY優(yōu)化較弱，JOIN中的排序

17、分組幾乎都會使用Fille Sort和臨時表 MySQL只有RBO，沒有CBO，所以總是選擇類型上最優(yōu)的執(zhí)行計劃，而不一定是開銷最小的。 MySQL對于OR，很多時候并不會優(yōu)化成兩次索引訪問或合并索引，所以OR最好寫成兩條SQL，做UNION ALL。 IN中存在NULL條件，可能會讓優(yōu)化器走成全表掃描。,SQL方言差異,選擇部分行：Oracle使用Rownum，MySQL使用LIMIT start,end; 執(zhí)行函數(shù)：Oracle需要SELECT f() FROM dual; MySQL可以直接SELECT f(); 復制數(shù)據(jù)：Oracle必須有as，CREATE TABLE t AS SE

18、LECT * FROM table; MySQL的as是可選，CREATE TABLE t SELECT * FROM table;,常用功能差異,鎖差異：Oracle鎖加在數(shù)據(jù)塊上，InnoDB鎖加在主鍵索因上，所以InnoDB總是有主鍵索引。 導入導出數(shù)據(jù)：Oracle采用EXP/IMP導入導出。 MySQL采用mysqldump導出，導入可以采用管道或source。 mysqldump u用戶 p密碼 DB table1 table2 db.sql 提交方式：MySQL默認自動提交，Oracle默認手動提交，MySQL大批量操作最好改變?yōu)槭謩犹峤?，SET AUTOCOMMIT=0 SQL

19、緩存：MySQL只能緩存結(jié)果集，不能緩存SQL解析結(jié)果 數(shù)據(jù)庫對象：Oracle將數(shù)據(jù)庫對象編譯存儲，直接執(zhí)行二進制碼，MySQL只存儲代碼，臨時解析執(zhí)行，所以MySQL觸發(fā)器、存儲過程等對象創(chuàng)建時僅檢查語法，并不檢查邏輯,MySQL調(diào)優(yōu)基礎,索引優(yōu)化基礎 Schema設計優(yōu)化 SQL書寫優(yōu)化,索引優(yōu)化基礎,MySQL只在認為走索引可以篩去85%以上數(shù)據(jù)的時候，才使用索引，如果達不到這個標準，索引是無意義的。 前綴索引：MySQL可以利用索引的前向部分，但不可以利用后向部分，不支持反向索引。 例如：SELECT col2 FROM table WHERE col1=1;可以使用索引(col1,col2)，但不可以使用(col2,col1). 不等比較的優(yōu)化：如果索引的前向部分在WHERE中是等于，那么可以使用索引，如果索引的前向部分再WHERE中是不等比較，那么不