MySQL基礎(chǔ)技能與原理參考幻燈片

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

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

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

4、r(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)度記錄位)。,字符類(lèi)型與處

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

6、符相同的子字符串， 起始于位置 pos。 LOWER(str)：返回字符串 str 變?yōu)樾?xiě)字母的字符。 UPPER(str)：返回字符串str轉(zhuǎn)化為大寫(xiě)字母的字符。,日期類(lèi)型與處理函數(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é)。,日期類(lèi)型與處理函數(shù),DATE_SUB/DATE_ADD：對(duì)時(shí)間進(jìn)行加減。 CURDATE()：將當(dāng)前日期按照YYYY-MM-DD 或YYYYMMDD 格式的值返回。 NOW()：返回當(dāng)前日期和時(shí)間值， 其格式為 YYYY-MM-DD HH:MM:SS 或YYYYMMDDHHMMSS。 組合使用：mysql SELECT DATE_ADD(1998-01-02, INTERVAL 31 DAY); - 1998-02-02,類(lèi)型處理小技巧,IP存儲(chǔ)：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存儲(chǔ)：(3*4+3)+1=16位，UTF8字符集15*3+1=46字節(jié) INT存儲(chǔ)：4字節(jié),MySQL高級(jí)特性,存儲(chǔ)過(guò)程基礎(chǔ) 觸發(fā)器基礎(chǔ) 分區(qū)表基礎(chǔ) 應(yīng)用場(chǎng)景,存儲(chǔ)過(guò)程基礎(chǔ)

9、,官方文檔： 變量定義順序必須是：存儲(chǔ)函數(shù)變量游標(biāo)定義游標(biāo)異常程序主體 定義務(wù)必加上授權(quán)： CREATE DEFINER=rootlocalhost PROCEDURE sp(); 存儲(chǔ)過(guò)程相關(guān)權(quán)限：被操作表的相關(guān)權(quán)限及EXECUTE(執(zhí)行權(quán)限)， ALTER ROUTINE(修改權(quán)限)，CREATE ROUTINE(創(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ǔ)句：SHOW CREATE PROCEDURE/FUNCTION ps; 查看所有存儲(chǔ)過(guò)程/函數(shù)：SHOW PROCEDURE/FUN

10、CTION STATUS LIKE ps; 調(diào)用存儲(chǔ)過(guò)程：CALL sp(); 調(diào)用存儲(chǔ)函數(shù)：SELECT sp();,觸發(fā)器基礎(chǔ),官方文檔： 定義務(wù)必加上授權(quán)： CREATE DEFINER=rootlocalhost Trigger tgr(); 定義語(yǔ)句：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ù)） 行級(jí)觸發(fā)器，每一行都會(huì)觸發(fā)動(dòng)作 內(nèi)部可以調(diào)用存儲(chǔ)過(guò)程和函數(shù) 每種類(lèi)型的Tri

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

12、分區(qū)的再次分割。,分區(qū)表基礎(chǔ)Range分區(qū),每個(gè)分區(qū)包含那些分區(qū)表達(dá)式的值位于一個(gè)給定的連續(xù)區(qū)間內(nèi)的行。這些區(qū)間要連續(xù)且不能相互重疊，使用VALUES LESS THAN操作符來(lái)進(jìn)行定義。 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ū)表基礎(chǔ)List分區(qū),LIST分區(qū)中每個(gè)分區(qū)的定義和選擇是基于某列的值從屬于一個(gè)值列表集中的一個(gè)值。LIST分區(qū)通過(guò)”VALUES IN (value_list)”的方式來(lái)定義每個(gè)分區(qū)，其中”value_list”是一個(gè)通過(guò)逗號(hào)分隔的整數(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ū)表基礎(chǔ)Hash分區(qū),在HASH分區(qū)中，MySQL自動(dòng)完成分區(qū)值，你所要做的只是基于將要被哈希的列值指定一個(gè)列值或表達(dá)式，以及指定被分區(qū)的表將要被分割成的分區(qū)數(shù)量。 CREATE TABLE employees ( id INT NOT NULL, store_id INT ) PARTITION BY HASH(store_id)

15、PARTITIONS 4；,分區(qū)表基礎(chǔ)子分區(qū),子分區(qū)是分區(qū)表中每個(gè)分區(qū)的再次分割，子分區(qū)既可以使用HASH希分區(qū)，也可以使用KEY分區(qū)，每個(gè)分區(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 );,高級(jí)特性應(yīng)用場(chǎng)景,存儲(chǔ)過(guò)程不得包含業(yè)務(wù)邏輯，一般只做臨時(shí)數(shù)據(jù)訂正或固定邏輯用。 觸發(fā)器目前只用于Otter，一般不使用。 分區(qū)表適合數(shù)據(jù)量大，但查詢(xún)會(huì)固定在若干個(gè)數(shù)據(jù)集中的場(chǎng)景，但不要把分區(qū)表混合排序使用，可能帶來(lái)嚴(yán)重的性能故障。（BRMMS發(fā)生過(guò)）,MySQL與Oracle差異比較,優(yōu)化器差異 SQL方言差異 常用功能差異,優(yōu)化器差異,MySQL對(duì)子查詢(xún)會(huì)處理為臨時(shí)表，所以一般JOIN效率比做子查詢(xún)高。 MySQL在JOIN中對(duì)ORDER BY、GROUP BY優(yōu)化較弱，JOIN中的排序

17、分組幾乎都會(huì)使用Fille Sort和臨時(shí)表 MySQL只有RBO，沒(méi)有CBO，所以總是選擇類(lèi)型上最優(yōu)的執(zhí)行計(jì)劃，而不一定是開(kāi)銷(xiāo)最小的。 MySQL對(duì)于OR，很多時(shí)候并不會(huì)優(yōu)化成兩次索引訪(fǎng)問(wèn)或合并索引，所以O(shè)R最好寫(xiě)成兩條SQL，做UNION ALL。 IN中存在NULL條件，可能會(huì)讓優(yōu)化器走成全表掃描。,SQL方言差異,選擇部分行：Oracle使用Rownum，MySQL使用LIMIT start,end; 執(zhí)行函數(shù)：Oracle需要SELECT f() FROM dual; MySQL可以直接SELECT f(); 復(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總是有主鍵索引。 導(dǎo)入導(dǎo)出數(shù)據(jù)：Oracle采用EXP/IMP導(dǎo)入導(dǎo)出。 MySQL采用mysqldump導(dǎo)出，導(dǎo)入可以采用管道或source。 mysqldump u用戶(hù) p密碼 DB table1 table2 db.sql 提交方式：MySQL默認(rèn)自動(dòng)提交，Oracle默認(rèn)手動(dòng)提交，MySQL大批量操作最好改變?yōu)槭謩?dòng)提交，SET AUTOCOMMIT=0 SQL

19、緩存：MySQL只能緩存結(jié)果集，不能緩存SQL解析結(jié)果 數(shù)據(jù)庫(kù)對(duì)象：Oracle將數(shù)據(jù)庫(kù)對(duì)象編譯存儲(chǔ)，直接執(zhí)行二進(jìn)制碼，MySQL只存儲(chǔ)代碼，臨時(shí)解析執(zhí)行，所以MySQL觸發(fā)器、存儲(chǔ)過(guò)程等對(duì)象創(chuàng)建時(shí)僅檢查語(yǔ)法，并不檢查邏輯,MySQL調(diào)優(yōu)基礎(chǔ),索引優(yōu)化基礎(chǔ) Schema設(shè)計(jì)優(yōu)化 SQL書(shū)寫(xiě)優(yōu)化,索引優(yōu)化基礎(chǔ),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中是等于，那么可以使用索引，如果索引的前向部分再WHERE中是不等比較，那么不