MySQL注意知識點
INT(N)表示什么？
N是顯示寬度，不表示存儲數(shù)字的長度限制物赶。使用zerofill表示長度小于N時速种，使用0填充高位玻佩，直到長度為N，長度大于N時，按照實際顯示。
自動增長注意點
自動增長需要設置在主鍵上
主鍵插入為null或者0時肌割，會自動增長
主鍵插入負數(shù)時，會按照實際值插入
字符類型
char(n)和varchar(n) 其中n表示的是字符長度帐要。
其它例如text(n)把敞，longtext(n) n表示的是字節(jié)
數(shù)據(jù)庫排列規(guī)則Collation
設置為utf8_general_ci時，會忽略字符的大小寫榨惠。
而使用utf8mb4_bin時奋早，_bin結尾的，不會忽略大小寫
時間類型
| 日期類型 | 占用空間 | 表示范圍 |
|---|---|---|
| DATETIME | 8 | 1000-01-01 00:00:00 ~ 9999-12-31 23:59:59 |
| DATE | 3 | 1000-01-01 ~ 9999-12-31 |
| TIMESTAMP | 4 | 1970-01-01 00:00:00UTC ~ 2038-01-19 03:14:07UTC |
| YEAR | 1 | YEAR(2):1970-2070, YEAR(4):1901-2155 |
| TIME | 3 | -838:59:59 ~ 838:59:59 |
DATETIME 沒有時區(qū)的概念赠橙，而TIMESTAMP有時區(qū)的概念
顯示MySQL的最大連接數(shù)：
show VARIABLES like '%max_connections%'
MySql緩存默認關閉耽装，開啟后要命中要求2次SQL要完全一樣，包括SQL語句期揪，連接的數(shù)據(jù)庫剂邮，協(xié)議版本，字符集等横侦。
默認關閉緩存：
show variables like '%query_cache_type%'
在 mysql 中 database 和 schema 是等價的
MySQL存儲引擎
Innodb
MySQL5.5以后默認的存儲引擎，完全支持事務和ACID特性绰姻，支持行級鎖枉侧。
MyISAM
MySQL5.5之前默認的存儲引擎，有myd(數(shù)據(jù))和myi(索引)組成
支持表壓縮狂芋，但是表壓縮后榨馁，就不能再插入數(shù)據(jù)了。適用于非事務類型應用帜矾，只讀應用翼虫，空間類應用屑柔。基本上大多數(shù)場景都不適合了珍剑。
CSV
以 csv 格式進行數(shù)據(jù)存儲
所有列都不能為 null 的
不支持索引（不適合大表掸宛，不適合在線處理）
可以對數(shù)據(jù)文件直接編輯（保存文本文件內容）
Archive
以 zlib 對表數(shù)據(jù)進行壓縮，磁盤 I/O 更少,數(shù)據(jù)存儲在 ARZ 為后綴的文件中招拙。
只支持 insert 和 select 操作唧瘾，只允許在自增 ID 列上加索引
Memory
文件系統(tǒng)存儲特點，也稱 HEAP 存儲引擎别凤，所以數(shù)據(jù)保存在內存中
支持 HASH 索引和 BTree 索引
所有字段都是固定長度 varchar(10) = char(10)
不支持 Blog 和 Text 等大字段
Memory 存儲引擎使用表級鎖
最大大小由 max_heap_table_size 參數(shù)決定
使用場景
hash 索引用于查找或者是映射表（郵編和地區(qū)的對應表）
用于保存數(shù)據(jù)分析中產(chǎn)生的中間表
用于緩存周期性聚合數(shù)據(jù)的結果表
Ferderated
提供了訪問遠程 MySQL 服務器上表的方法
本地不存儲數(shù)據(jù)饰序，數(shù)據(jù)全部放到遠程服務器上
本地需要保存表結構和遠程服務器的連接信息
使用場景
偶爾的統(tǒng)計分析及手工查詢（某些游戲行業(yè)）
MySQL中的鎖
表級鎖，適合以查詢?yōu)橹鞴婺模挥猩倭堪此饕龡l件更新數(shù)據(jù)的應用求豫。會把整個表鎖住
行級鎖，適合按照索引條件并發(fā)更新少量不同數(shù)據(jù)诉稍，同時又有并發(fā)查詢的應用蝠嘉。
頁面鎖（gap鎖，間隙鎖）
表鎖
表共享讀鎖均唉。（讀鎖）
表獨占寫鎖是晨。（寫鎖）
讀鎖
語法：
lock table 表名 read # 加鎖 UNLOCK TABLES # 解除鎖
多個session可共享讀，當前session寫操作會報錯舔箭，其它session寫操作會阻塞罩缴。當前session查詢時，不能設置表別名层扶，否則報錯箫章。
寫鎖
lock table 表名 write # 加鎖 UNLOCK TABLES # 解除鎖
同一個session中，可以進行增刪改操作镜会，其它session的增刪改操作會阻塞檬寂。所有的讀操作都會被阻塞。
行鎖
讀鎖（共享鎖）
BEGIN SELECT * FROM 表名 WHERE 條件 LOCK IN SHARE MODE
COMMIT
一個session開啟一個事務戳表，設置一個行級讀鎖桶至，另外一個session就不能對已經(jīng)上鎖的數(shù)據(jù)進行寫操作。比如設置條件為id <=13,那么對id為小于等于13的數(shù)據(jù)匾旭，都不能進行寫操作镣屹，會阻塞。同個session可以進行操作价涝。
寫鎖（排它鎖）
同一個session中可以進行讀寫操作女蜈，另外一個session就不能對已經(jīng)上鎖的數(shù)據(jù)進行寫操作。比如設置條件為id <=13,那么對id為小于等于13的數(shù)據(jù)，都不能進行寫操作伪窖，會阻塞逸寓。
注意點
兩個事務不能鎖同一個索引。
insert 覆山，delete 竹伸， update 在事務中都會自動默認加上排它鎖。
行鎖必須有索引才能實現(xiàn)汹买，否則會自動鎖全表佩伤，那么就不是行鎖了。**
事務
事務特性
ACID
原子性atomicity晦毙，一個事務為一個不可分割的最小單元生巡，事務中的操作，要么全部成功见妒，要么全部失敗孤荣。
一致性consistency，事務將數(shù)據(jù)庫從一種一致性轉換到另外一種一致性狀態(tài)须揣，數(shù)據(jù)庫中的數(shù)據(jù)完整性沒有被破壞盐股。
隔離性isolation，一個事務的執(zhí)行不能被其他事務干擾耻卡。即一個事務內部的操作及使用的數(shù)據(jù)對并發(fā)的其他事務是隔離的疯汁，并發(fā)執(zhí)行的各個事務之間不能互相干擾。
持久性durability卵酪，事務一旦提交幌蚊，其所做的修改就會永久保存到數(shù)據(jù)庫中。
事務并發(fā)問題
未提交讀（READ UNCOMMITED）臟讀
已提交讀 （READ COMMITED）不可重復讀
可重復讀（REPEATABLE READ）
可串行化（SERIALIZABLE）
MySQL的默認隔離級別為REPEATABLE-READ
show variables like '%isolation%';
間隙鎖(gap鎖)
MySQL中溃卡，可重復讀就已經(jīng)解決了幻讀的問題溢豆，借助的就是間隙鎖。
事務語法
- 開啟事務：
begin
start transaction（推薦）
begin work
事務回滾：rollback
事務提交：commit
還原點：savepoint point名稱a瘸羡。 rollback to savepoint a 回滾到還原點a
業(yè)務設計
邏輯設計
范式設計
第一大范式：數(shù)據(jù)庫表中的所有字段都只具有單一屬性漩仙，單一屬性的列是由基本數(shù)據(jù)類型所構成的。
第二大范式：表中只有一個業(yè)務主鍵犹赖。
第三大范式：每一個非主屬性既不部分依賴于也不傳遞依賴于業(yè)務主鍵队他。
優(yōu)點：
可以盡量得減少數(shù)據(jù)冗余
范式化的更新操作比反范式化更快
范式化的表通常比反范式化的表更小
缺點：
對于查詢需要對多個表進行關聯(lián)
更難進行索引優(yōu)化
反范式設計
為了性能和讀取效率考慮，適當違反數(shù)據(jù)庫設計范式要求峻村，允許存在少量冗余麸折。使用空間來換取時間
優(yōu)點：
可以減少表的關聯(lián)
可以更好的進行索引優(yōu)化
缺點：
存在數(shù)據(jù)冗余及數(shù)據(jù)維護異常
對數(shù)據(jù)的修改需要更多的成本
物理設計
命名規(guī)范
數(shù)據(jù)庫、表雀哨、字段的命名要遵守可讀性原則，使用下劃線或者大小寫來格式化命名以獲得良好的可讀性
數(shù)據(jù)庫、表雾棺、字段的命名要遵守表意性原則膊夹，對象的名字應該能夠描述它所表示的對象
數(shù)據(jù)庫、表捌浩、字段的命名要遵守長名原則 放刨，盡可能少使用或者不使用縮寫
數(shù)據(jù)類型選擇
當一個列可以選擇多種數(shù)據(jù)類型時
優(yōu)先考慮數(shù)字類型
其次是日期、時間類型
最后是字符類型
對于相同級別的數(shù)據(jù)類型尸饺，應該優(yōu)先選擇占用空間小的數(shù)據(jù)類型
慢查詢
分析工具： Mysqldumpslow进统，pt_query_digest
索引
索引分類
普通索引：一個索引只包含單個列，一個表可以有多個單列索引
唯一索引：索引的值必須唯一浪听，允許有空值
復合索引：一個索引包含多個列
聚簇索引（聚集索引）：并不是一種單獨的索引類型螟碎，而是一種數(shù)據(jù)存儲方式。具體細節(jié)取決于不同的實現(xiàn)迹栓，InnoDB 的聚簇索引其實就是在同一個結構中保存了 B-Tree 索引(技術上來說是 B+Tree)和數(shù)據(jù)行掉分。
非聚簇索引：不是聚簇索引，就是非聚簇索引
基礎語法
查看索引：SHOW INDEX FROM 表名
創(chuàng)建索引
CREATE [UNIQUE ] INDEX indexName ON 表名(columnName(length));
ALTER TABLE 表名 ADD [UNIQUE ] INDEX [indexName] ON (columnName(length))
刪除索引
DROP INDEX [indexName] ON 表名;
執(zhí)行計劃
使用EXPLAIN關鍵字可以模擬優(yōu)化器執(zhí)行SQL查詢語句克伊，從而知道MySQL是如何處理SQL語句的酥郭。分析查詢語句或者表結構性能瓶頸
作用
可以查詢到以下信息：
表的讀取順序
數(shù)據(jù)讀取操作的操作類型
哪些索引可以使用
哪些索引被實際使用
表之間的引用
每張表有多少行被優(yōu)化器查詢
詳解
通過explain關鍵字分析的結果由以下列組成。
id列
描述 select 查詢的序列號,包含一組數(shù)字愿吹，表示查詢中執(zhí)行 select 子句或操作表的順序
根據(jù) ID 的數(shù)值結果可以分成一下三種情況
id 相同：執(zhí)行順序由上至下
id 不同：如果是子查詢不从，id 的序號會遞增，id 值越大優(yōu)先級越高犁跪，越先被執(zhí)行
id 相同和不同：同時存在時椿息，相同id執(zhí)行順序由下至上，不同的大的優(yōu)先執(zhí)行
select_type列
查詢的類型耘拇，用于區(qū)分普通查詢撵颊、聯(lián)合查詢、子查詢等復雜查詢
table列
標識這行數(shù)據(jù)來源于哪張表
type列
顯示的是訪問類型惫叛，結果值從最好到最壞依次是：
system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery >
index_subquery > range > index > ALL
關注的結果值：
system>const>eq_ref>ref>range>index>ALL
一般來說倡勇，得保證查詢至少達到 range 級別，最好能達到 ref嘉涌。
type列各個值含義
- system：表只有一行記錄（等于系統(tǒng)表）妻熊，這是 const 類型的特列，平時不會出現(xiàn)仑最，這個也
可以忽略不計
- const：表示通過索引一次就找到了
const 用于比較 primary key 或者 unique 索引扔役。因為只匹配一行數(shù)據(jù)，所以很快如將主鍵置于 where條件中警医，MySQL 就能將該查詢轉換為一個常量
- eq_ref: 唯一性索引掃描亿胸，對于每個索引鍵坯钦，表中只有一條記錄與之匹配。常見于主鍵或唯一索引
掃描
- ref: 非唯一性索引掃描侈玄，返回匹配某個單獨值的所有行.
本質上也是一種索引訪問婉刀，它返回所有匹配某個單獨值的行，然而序仙，它可能會找到多個符合
條件的行突颊，所以他應該屬于查找和掃描的混合體
range: 只檢索給定范圍的行,使用一個索引來選擇行。key 列顯示使用了哪個索引一般就是在你的 where 語句中出現(xiàn)了 between潘悼、<律秃、>、in 等的查詢
index : 當查詢的結果全為索引列的時候治唤，雖然也是全部掃描棒动，但是只查詢的索引庫，而沒有去查詢
數(shù)據(jù)肝劲。
- all : 將遍歷全表以找到匹配的行
possible_keys 與 key
possible_keys:可能使用的 key
Key:實際使用的索引迁客。如果為 NULL，則沒有使用索引
查詢中若使用了覆蓋索引辞槐，則該索引和查詢的 select 字段重疊
key_len
表示索引中使用的字節(jié)數(shù)掷漱，可以通過該列計算查詢中使用的索引的長度。在不損失精確性的情況下榄檬，長度越短越好
key_len 顯示的值為索引字段的最大可能長度卜范，并非實際使用長度，即 key_len 是根據(jù)表定義計算而得鹿榜，不是通過表內檢索出的
key_len 表示索引使用的字節(jié)數(shù)海雪，
根據(jù)這個值，就可以判斷索引使用情況舱殿，特別是在組合索引的時候奥裸，判斷所有的索引字段是否都被查詢用到。
char 和 varchar 跟字符編碼也有密切的聯(lián)系,
-
latin1占用 1 個字節(jié)沪袭，gbk 占用 2 個字節(jié)湾宙，utf8 占用3 個字節(jié)。（不同字符編碼占用的存儲空間不同）
-
字符類型-索引字段為char類型+不可為Null時
如果索引定義為char(20) 冈绊，則key_len= 20 * 3（utf-8b編碼）
-
字符類型-索引字段為char類型+可為Null時
如果索引定義為char(20) 侠鳄，則key_len= 20 * 3（utf-8b編碼）+ 1 = 31
可以為Null，占用一個字節(jié)長度死宣，需要加1
-
索引字段為varchar類型+不可為Null時
Keylen=varchar(n)變長字段+不允許 Null=n*(utf8=3,gbk=2,latin1=1)+2
-
索引字段為varchar類型+可為Null時
Keylen=varchar(n)變長字段+不允許 Null=n*(utf8=3,gbk=2,latin1=1)+2+1
總結
字符類型:
變長字段需要額外的 2 個字節(jié)（VARCHAR 值保存時只保存需要的字符數(shù)伟恶，另加一個字節(jié)來記錄長度(如果列聲明的長度超過 255，則使用兩個字節(jié))毅该，所以 VARCAHR 索引長度計算時候要加 2）博秫，固定長度字段不需要額外的字節(jié)潦牛。
NULL 需要 1 個字節(jié)的額外空間,所以索引字段最好不要為 NULL，因為 NULL 讓統(tǒng)計更加復雜并且需要額外的存儲空間挡育。
復合索引有最左前綴的特性罢绽，如果復合索引能全部使用上，則是復合索引字段的索引長度之 和静盅，這也可以用來判定復合索引是否部分使用，還是全部使用寝殴。
整數(shù)/浮點數(shù)/時間類型的索引長度
NOT NULL=字段本身的字段長度
NULL=字段本身的字段長度+1(因為需要有是否為空的標記蒿叠，這個標記需要占用 1
個字節(jié))
- datetime 類型在 5.6 中字段長度是 5 個字節(jié)，datetime 類型在 5.5 中字段長度是 8 個字節(jié)
ref
顯示索引的哪一列被用到了
rows
根據(jù)表統(tǒng)計信息及索引選用情況蚣常，大致估算出找到所需的記錄需要讀取的行數(shù)市咽。不準確。
extra
包含不適合在其它列中展示但十分重要的額外信息抵蚊。
extra的值說明using filesort
當發(fā)現(xiàn)using filesort時施绎，就是有優(yōu)化的地方了。
比如聯(lián)合索引是a,b,c三個字段贞绳，查詢時卻是只按照c排序谷醉，就會導致索引失效。
using Index
表名使用了覆蓋索引冈闭，避免了訪問表的數(shù)據(jù)行俱尼，效率高。
覆蓋索引
select 的數(shù)據(jù)列只用從索引中就能夠取得萎攒，不必讀取數(shù)據(jù)行遇八，MySQL 可以利用索引返回 select 列表中的字段，而不必根據(jù)索引再次讀取數(shù)據(jù)文件,換句話說查詢列要被所建的索引覆蓋耍休。
如果要使用覆蓋索引刃永，一定要注意 select 列表中只取出需要的列，不可 select *羊精，因為如果將所有字段一起做索引會導致索引文件過大斯够，查詢性能下降。
SQL優(yōu)化
優(yōu)化策略
盡量全值匹配
最佳左前綴法則（復合索引）园匹。查詢從索引的最左前列開始雳刺，并且不跳過索引中的列。
不在索引上做任何操作裸违。包括計算掖桦、函數(shù)、類型轉換供汛。會導致索引失效而全表掃描
范圍條件放最后枪汪。中間有范圍查詢會導致后面的索引列全部失效涌穆。
覆蓋索引盡量用。
不等于要慎用雀久。使用不等于會導致索引失效而全表掃描宿稀。需要使用不等于時，使用覆蓋索引赖捌。
Null/not null有影響祝沸。 查詢is not null 時，會導致索引失效越庇。查詢is null時罩锐，如果索引是not null的，就會導致索引失效卤唉，如果不是涩惑，索引有效。解決方案：覆蓋索引
like查詢要當心桑驱。like 以通配符開頭('%abc...')索引失效會變成全表掃描的操作竭恬。
字符類型加引號。字符串不加單引號會導致索引失效熬的。
or改成union效率高痊硕。
導入導出數(shù)據(jù)
load data infile
select * into OUTFILE 'D:\product.txt' from product_info
load data INFILE 'D:\product.txt' into table product_info
load data INFILE '/soft/product3.txt' into table product_info
show VARIABLES like 'secure_file_priv'
secure_file_priv 為 NULL 時，表示限制 mysqld 不允許導入或導出押框。
secure_file_priv 為 /tmp 時寿桨，表示限制 mysqld 只能在/tmp 目錄中執(zhí)行導入導出，其他目錄不能執(zhí)行强戴。
secure_file_priv 沒有值時亭螟，表示不限制 mysqld 在任意目錄的導入導出。
-
