簡介

AUTOBEAR的關(guān)系型數(shù)據(jù)庫的個人學習筆記，本筆記隨時增添钧大，用來鞏固知識翰撑，查缺補漏。

基礎(chǔ)概念

1.DB: DATABASE數(shù)據(jù)庫啊央，數(shù)據(jù)倉庫
2.DBMS：數(shù)據(jù)庫管理系統(tǒng)（MySQL眶诈、Oracle等）
3.SQL:結(jié)構(gòu)化查詢語言（通用性高）
4.DBA:數(shù)據(jù)庫管理員
數(shù)據(jù)庫的特點:數(shù)據(jù)-->表-->數(shù)據(jù)庫

數(shù)據(jù)庫引擎

1.InnoDB數(shù)據(jù)引擎（聚集索引）
2.MylSAM(非聚集索引)
所謂聚集就是索引字段和數(shù)據(jù)是否用地址代替。

基礎(chǔ)語法知識

DISTINCT去重復數(shù)據(jù)的查詢：SELECT DISTINCT col1,col2 FROM table;
刪除去重：DELECT t1 FROM table t1,table t2 WHERE t1.email=t2.email and t1.id>2.id;
+號的作用僅僅作為運算符：Ⅰ 'abc'+20=20; Ⅱ null+20=null; Ⅲ abc'+'efg'=0;
查詢拼接：SELECT CONCAT('a','b','c')=abc;
平均值：SELECT AVG(salary),department_id,job_id FROM employees
分組篩選：GROUP BY job_id,department_id
排序： ORDER BY AVG(salary) DESC;
右外連接查詢哪個部門沒員工：

SELECT d.*,e.*
FROM departments d
LEFT OUTER JOIN employees e
ON d.'department_id'=e.'department_id'
WHERE e.'employee_id' IS NULL

菜鳥教程的幾種連接圖：

sql-join.png

約束

約束（一種限制瓜饥，用于限制表中數(shù)據(jù)）逝撬，六大約束：
1.NOT NULL;
2.DEFAULT;
3.PRIMARY KEY;
4.UNIQUE(唯一約束，可以為空乓土，比如座位號)宪潮；
5.CHECK(檢查約束)；
6.FOREIGN KEY(外鍵約束)
創(chuàng)建語法：

CREATE TABLE 表明(
  字段名 字段類型 約束
)
CREATE TABLE 表明(
  字段名 字段類型 
  
  CONSTRAINT 約束名 PRIMARY KEY(id)
)

CREATE TABLE 表名(
    id INT PRIMARY KEY ,
    name VARCHAR(20) NOT NULL,
    gender CHAR(1) CHECK(gender='男' or gender='女'),
    seat INT UNIQUE,
    age INT DEFAULT 18,
    majorId INT FOREIGN KEY REFERENCES major(id)
)

查看表結(jié)構(gòu)趣苏、修改索引語句：

DESC 表名
SHOW INDEX FROM 表名
ALTER TABLE student ADD CONSTRAINT uk_name unique(name);

外鍵：主從表關(guān)聯(lián)列類型要求一致或者兼容狡相、主表的關(guān)聯(lián)列必須是一個Key(主鍵或者唯一鍵)、插入數(shù)據(jù)時先插主再插從拦键，先刪從再刪主谣光。

標識列

標識列又叫自增長列，一個表至多一個標識列必須在創(chuàng)建時就定義芬为，相關(guān)語句：

CREATE TABLE table_ii(
  id INT PRIMARY KEY AUTO_INCREMENT,
  NAME VARCHAR(20)
)
SHOW VARIABLES LIKE '%auto_increment%';
SET auto_increment_increment(步長)=3;
SET auto_increment_offset(起始值)=20

事務(wù)

transaction control language事務(wù)控制語言（需要支持事務(wù)的引擎配合）萄金，是一個或一組sql語句組成一個執(zhí)行單元。
事務(wù)的（ACID）屬性：
1.原子性
2.一致性
3.隔離性（隔離級別）
4.持久性（一旦提交就是永久性）
隱式事務(wù)：沒有明顯的開啟和結(jié)束的標志媚朦，普通的增刪改查就是這種隱式事務(wù)氧敢。
顯式事務(wù)：1.禁用自動提交（SET autocommit=0）只對當前會話有效。2.start transaction（可以省略）3.編寫sql語句询张。4.commit/rollback孙乖。

事務(wù)隔離機制

數(shù)據(jù)庫使用中常出現(xiàn)的問題現(xiàn)象：
1臟讀（T1/T2兩個事務(wù)，T1讀取了T2更新但還沒被提交的字段，若T2回滾唯袄，T1讀取的內(nèi)容就是臨時無效的）
2不可重復讀（T2讀取后T1修改并提交弯屈，T2再次讀取，兩次數(shù)據(jù)沖突）
3幻讀（針對插入恋拷、刪除數(shù)據(jù)行资厉，操作前后進行范圍查找得出的結(jié)果不同）
為了解決以上問題MYSQL提供4種事務(wù)隔離級別:
1.READ UNCOMMITTED 讀取未提交，會出現(xiàn)所有問題蔬顾。
2.READ COMMITTED 讀取已提交宴偿，只解決臟讀。
3.REPEATABLE READ 可重復讀诀豁，默認窄刘。解決臟讀和不可重復讀問題。
4.SERIALIZABLE 串行化(會造成嚴重的事務(wù)阻塞)舷胜，解決全部問題娩践。
Oracle有兩種事務(wù)隔離級別(READ COMMITTED /SERIALIZABLE)

事務(wù)語法

相關(guān)設(shè)置語句：

SELECT @@tx_isolation; 或者@@transaction_isolation;
set seesion transaction isolation level 隔離級別;

事務(wù)種設(shè)置回滾點：

SAVEPOINT A;
ROLLBACK TO A;
TRUNCATE不支持回滾DROP和DELETE都支持

視圖

視圖是一種虛擬表。

CREAT VIEW v1 AS
SELECT name,major FROM studenttable s INNER JOIN major m ON s.majorid=m.id 
--之后就可以把V1當做表來操作逞带。
ALTER VIEW v1 AS ....
DORP VIEW v1;
SHOW v1;

一般不直接對視圖內(nèi)的數(shù)據(jù)進行增刪改操作欺矫。

變量

分為全局變量纱新、會話變量展氓、自定義變量、局部變量脸爱。
會話變量：只對當前會話session有效镀脂。
查看變量值：SHOW (GLOBAL/SEESION) VARIABLES;
自定義變量：SET @自定義變量名:=值也可以直接用=號
局部變量：只在BEGIN/END內(nèi)有效憔狞。DECLARE 變量名 類型 DEFAULT 值； SET 局部變量名:=值；

存儲過程

存儲過程是一種預(yù)先編譯好的SQL語句的集合锁孟，批處理語句，減少了和數(shù)據(jù)庫服務(wù)器連接的次數(shù)硬鞍。
一些語法：

DELIMITER $;設(shè)置結(jié)束標記呼奢。

CREAT PROCEDURE function1()
BEGIN
     INSERT INTO admintable(username,password) VALUES('mike','123456');
END $
CALL function1()$

CREAT PROCEDURE function2(IN Myname VARCHAR(20))
BEGIN
     SELECT * FROM table t WHERE t.name=Myname
END $
CALL function2('張飛')$

CREAT PROCEDURE function3(IN username VARCHAR(20),IN password VARCHAR(20))
BEGIN
     DECLARE result INT DEFAULT 0;
     SELECT COUNT(*) INTO result FROM table t WHERE t.username=username AND t.password=password;
     SELECT IF(result>0,'成功','失敗');
END $
CALL function3('admin','123456')$

CREAT PROCEDURE function4(IN username VARCHAR(20),OUT password VARCHAR(20))
BEGIN
    SELECT t.password INTO password FROM table t WHERE t.username=username;

END $
SET @mypassword
CALL function4('admin',@mypassword)$
SELECT @mypassword$
DROP PROCEDURE function1;

函數(shù)：

存儲過程類似，但是函數(shù)有且僅有一個返回单料，適合查值埋凯。存儲過程主要增刪改。
語法塊：

CREAT FUNCTION 函數(shù)名(參數(shù)列表) RETURNS 返回類型
BEGIN
    DECLARE c INT DEFAULT 0;
    SELECT COUNT(*) INTO c FROM table ;
    RETURNS c;
END
SELECT function('sb')$
SHOW CREAT FUNCTION function;#查看函數(shù)
DROP FUNCTION function
表proc內(nèi)有所有的函數(shù)和存儲過程

IF(FLAG,1,0);

IF score>=90 AND score<=100 THEN RETURNS 'A';
ELSEIF score>=80 THEN RETURNS 'B';
ELSE RETURNS 'C';
END IF 

CASE 變量|表達式|字段 
WHEN 1 THEN 100;
WHEN 2 THEN 200;
WHEN 3 THEN 300;
ELSE 400;
END CASE

CASE
WHEN score>=90 AND score<=100 THEN SELECT 'A';
WHEN score>=80 AND score<=90 THEN SELECT 'B';
ELSE SELECT 'C';
END CASE;

循環(huán)：iterate=continue ,結(jié)束本次循環(huán)繼續(xù)下一次
      leave  =break    ,跳出循環(huán)扫尖。
DECLARE i INT DEFAULT 1;
aaaname:WHILE i<=count DO
    INSERT INTO table VALUES (CONCAT('USER',i),'666');
    SET i=i+1;(IF i>=20 THEN LEAVE aaaname);
END WHILE

雜記幾個函數(shù)：

SELECT DATE_FORMAT(date,'%Y年%M月%D日');  
NOW()  ;獲得當前時間白对，不同的數(shù)據(jù)庫還不太一樣。
CONCAT(str1,str2,…)  字符串拼接换怖，不要再用+了甩恼，容易出事情啊。

索引

索引是一種幫助數(shù)據(jù)庫高效獲數(shù)據(jù)的一種排好序的數(shù)據(jù)結(jié)構(gòu)，也就是是一種（高效獲取數(shù)據(jù)的）數(shù)據(jù)結(jié)構(gòu)条摸。幫助查找降低數(shù)據(jù)庫的IO成本同時降低排序的CPU消耗悦污。解決IO時間比較高的問題。
但是索引需要占用磁盤空間钉蒲，降低了增刪改速度塞关，需要花時間優(yōu)化索引查詢。
索引類型：
1.單值索引：一個表可以有多個單列索引（銀行卡號子巾、電話號碼）最多不超5個帆赢；
2.唯一索引：唯一但允許空。
3.聯(lián)合索引：一個索引包含多個列
4.全文索引
索引語法：

    CREATE [UNIQUE] INDEX indexNAME on mytable(col(length));
    ALTER mytable ADD [UNIQUE] INDEX [indexNAME] ON (col(length));
    ALTER TABLE mytable ADD FULLTEXT  indexNAMEON(col); 全文索引
    ALTER TABLE table ADD unique index(aa,bb);聯(lián)合唯一索引
    SHOW INDEX FROM table_name; \G
    SHOW GLOBAL STATUS like 'Innodb_page_size';  葉子節(jié)點大小默認16kb

    CREATE INDEX INDEX_NAME ON TABLE(ID,COL1,COL2);
    CREATE INDEX INDEX_NAME1 ON tb_product('id','price','caption');

 CREATE INDEX INDEX_TABLE1_UNITED ON TABLE_NAME(COL1,COL2,COL3);

索引的數(shù)據(jù)結(jié)構(gòu)：
1.B+Tree結(jié)構(gòu)
2.HASH結(jié)構(gòu)
需要創(chuàng)建索引的情況：
1.主鍵自動創(chuàng)建唯一索引
2.頻繁作為查詢條件的字段應(yīng)該創(chuàng)建索引
3.查詢中與其他表關(guān)聯(lián)的字段线梗，外鍵關(guān)系建立索引
4.頻繁更新的字段不適合創(chuàng)建索引
5.WHERE條件里用不到的字段不創(chuàng)建索引
6.查詢中排序的字段
7.查詢中統(tǒng)計或者分組的字段
不必要建的情況：
1.表記錄太少（百萬以內(nèi)）
2.經(jīng)常增刪改的表
3.數(shù)據(jù)重復且分布平均的表字段（性別椰于、國籍）

索引數(shù)據(jù)結(jié)構(gòu)的底層分析

常見數(shù)據(jù)結(jié)構(gòu)：二叉樹、紅黑樹仪搔、HASH表瘾婿、B-Tree.
數(shù)據(jù)結(jié)構(gòu)演示網(wǎng)站：https://www.cs.usfca.edu/~galles/visualization/Algorithms.html
二叉搜索樹：根節(jié)點-->左小，右大烤咧。
紅黑樹（二叉平衡樹）：自動平衡偏陪。
B-Tree與B+Tree,區(qū)別：
1.非葉子節(jié)點只存儲鍵值信息。
2.所有葉子節(jié)點之間都有一個鏈指針煮嫌。
3.數(shù)據(jù)記錄都存放在葉子節(jié)點中笛谦。

B+tree鏈接：https://www.cnblogs.com/vianzhang/p/7922426.html.png

執(zhí)行計劃

性能分析中常見瓶頸
1.CPU飽和瓶頸，一般發(fā)生在數(shù)據(jù)裝入內(nèi)存或者從磁盤上讀取數(shù)據(jù)的時候茬祷。
2.磁盤I/O瓶頸清焕，發(fā)生在裝入數(shù)據(jù)遠大于內(nèi)存容量的時候。
3.服務(wù)器硬件的性能瓶頸，top/free/iostat/vmstat查看性能
EXPLAIN：查看執(zhí)行計劃秸妥。explain select * from table滚停； --explain +SQL語句；
執(zhí)行計劃表的閱讀理解：
1.ID加載順序,select查詢的序列號三種情況
①ID相同粥惧，執(zhí)行順序由上到下键畴。
②ID不同，如果是（子查詢id序列號遞增突雪，id值越大越先執(zhí)行）起惕。
③ID相同不同同時存在，數(shù)字大優(yōu)先級最高咏删，平級時順序執(zhí)行惹想。
2.select_type:SIMPLE簡單查詢、PRIMARY主查詢督函、SUBQUERY子查詢嘀粱、DERIVERD衍生虛擬表查詢、UNION聯(lián)合查詢辰狡、UNION RESULT聯(lián)合查詢結(jié)果集锋叨。
3.type:訪問類型，最好到最壞system>const>eq_ref>ref>range>index>ALL宛篇。ALL全表掃描最差勁娃磺，const常出現(xiàn)主鍵和唯一鍵內(nèi)，eq_ref唯一性索引掃描些己，ref非唯一性索引掃描豌鸡，range范圍搜索嘿般，index全索引掃描段标。
4.possible_keys：可能應(yīng)用在這張表中的索引，但不一定實際使用炉奴。
5.key:實際使用的索引逼庞。
6.key_len：表示索引中使用的字節(jié)數(shù)的最大可能長度，并非實際使用長度瞻赶。
7.ref:顯示索引的哪一列被使用了赛糟。
8.rows：每張表有多少行被優(yōu)化器查詢。
9.Extra：額外擴展信息
①Using filesort 使用“文件排序“砸逊，性能低璧南。
②Using temporary 使用臨時表保存中間結(jié)果，常見于：order by和group by中师逸，性能更低了司倚。
③Using index 使用“覆蓋索引”，性能較高,如果出現(xiàn)using where表明索引被用來執(zhí)行索引鍵值的查找，沒有出現(xiàn)using where表明索引用來讀數(shù)據(jù)而非執(zhí)行查找動作动知。
④Using where 使用WHERE語句過濾
⑤Using join buffer使用連接緩存
⑥Impossible where 過濾語句錯誤

索引優(yōu)化分析

先看執(zhí)行計劃皿伺，再建索引，調(diào)優(yōu)盒粮。索引是按順序的鸵鸥，中間出現(xiàn)范圍查詢?nèi)菀追嚒Ｗ詈冒阎虚g字段刪了丹皱。
JOIN語句優(yōu)化：盡可能減少JOIN中的NestedLoop的循環(huán)總次數(shù)妒穴，小的結(jié)果集驅(qū)動大的結(jié)果集。優(yōu)先優(yōu)化內(nèi)層循環(huán)摊崭。保證被驅(qū)動表上join字段被索引宰翅。同時 joinbuffer適當調(diào)大一點。
如何避免索引失效：
1.聯(lián)合索引有順序要求遵循最佳左前綴法則爽室。
2.不能在索引列上做任何操作汁讼，否則會導致索引失效而轉(zhuǎn)向全表掃描。
3.范圍條件右邊的列索引失效阔墩。盡量取什么用什么嘿架，少用select *。
4.使用不等于（!=）時啸箫，索引失效耸彪。is null ;is not null;也無法使用索引。
5.like以通配符開頭時候查找索引之外的數(shù)據(jù)索引會失效忘苛，最好查找id和其他索引字段就好（覆蓋索引）蝉娜。
6.百分號最好在右邊寫，比如july%扎唾，不要%july%或者%july召川。
7.字符串不加單引號會導致索引失效。
8.order by也要按順序,group by容易出現(xiàn)臨時表胸遇。
永遠小表驅(qū)動大表：
比如A比B大荧呐，就先查B，再用B的數(shù)據(jù)去匹配A的數(shù)據(jù)纸镊。SELECT * FROM A WHERE id IN(select id from B); 反過來用就是SELECT * FROM A WHERE EXISTS (SELECT 1 FROM B WHERE A.id=B.id);
主外鍵表倍阐，左連接的特性：左表驅(qū)動，左表全有逗威，所以右表要加索引峰搪。

慢查詢?nèi)罩?/h2>

可以直接my.cnf修改slow_query_log為ON然后重啟
或者使用語句

SHOW VARIABLES LIKE '%slow_query_log';
SET slow_query_log =1;
設(shè)置閥值   ：SHOW VARIABLES LIKE 'long_query_time%';
查看慢sql數(shù)：SHOW GLOBAL STATUS LIKE '%slow_queries%';

慢日志分析工具 ：mysqldumpslow
Show Profile:查看資源消耗情況

SHOW VARIABLES LIKE 'profiling';SET profiling=on;Show Profiles;
Show profile cpu,block io for query 4(編號);

四種情況需要注意：
1.converting HEAP to MyISAM查詢結(jié)果太大，內(nèi)存不夠用凯旭。
2.Creating tmp table 創(chuàng)建臨時表
3.Copying to temp table on disk 把內(nèi)存中臨時表復制到磁盤概耻，危險
4.locked 出現(xiàn)鎖
數(shù)據(jù)庫優(yōu)化步驟：
1.觀察至少跑一天楣颠，看看生產(chǎn)的慢SQL情況。
2.開啟慢查詢?nèi)沼浉莉牵O(shè)置閥值童漩，超過5秒的慢SQL并抓出來。
3.explain+慢SQL分析
4.SHOW PROFILE
5.運維進行SQL服務(wù)器的參數(shù)調(diào)優(yōu)春锋。

鎖

鎖是計算機調(diào)協(xié)多個進程或線程并發(fā)訪問某一資源的機制（防止爭搶）矫膨。
分為表鎖（偏讀MyISAM）、行鎖（偏寫Innodb）期奔、頁鎖 會話關(guān)閉會自動解鎖侧馅。
語法：

lock table table_name read(write),table_name2 read(write);
Show open tables; 查看表鎖
unlock tables; 全局多表解鎖；
行鎖呐萌，配合事務(wù)隔離級別使用：
共享鎖（S）：SELECT * FROM table_name WHERE ID=8 LOCK IN SHARE MODE
排他鎖（X）：SELECT * FROM table_name WHERE ID=8 FOR UPDATE
事務(wù)隔離級別為RR的行鎖是悲觀鎖馁痴。
SHOW STATUS LIKE 'innodb_row_lock%';
current_wait當前鎖數(shù)量、lock_wait等待總次數(shù)肺孤、lock_time_avg等待平均時長罗晕、lock_time等待總時長

索引失效會導致行鎖變表鎖。
間隙鎖赠堵，使用范圍檢索INNODB會給符合條件的已有數(shù)據(jù)記錄的索引項加鎖小渊，對于鍵值在條件范圍內(nèi)但并不存在的記錄，叫做"間隙GAP",NEXT-KEY鎖茫叭。
關(guān)于鎖的建議：
1.盡可能讓所有數(shù)據(jù)檢索通過索引完成酬屉，避免無索引行鎖升級為表鎖
2.合理設(shè)計索引，盡量縮小鎖范圍
3.盡量可能較少檢索條件避免間隙鎖
4.盡量控制事務(wù)大小揍愁，減少鎖定資源量和時間長度
5.盡可能低級別事務(wù)隔離呐萨。

關(guān)于死鎖

死鎖是一個程序概念，不僅限于在JAVA編程時會出現(xiàn)莽囤，在數(shù)據(jù)庫操作時也會出現(xiàn)谬擦。
是由于多個并發(fā)進程因爭奪系統(tǒng)資源而產(chǎn)生相互等待導致的現(xiàn)象。
死鎖四要素：
1.資源互斥： 某種資源一次只允許一個進程訪問烁登，即該資源一旦分配給某個進程怯屉，其他進程就不能再訪問，直到該進程訪問結(jié)束饵沧。
2.占有且等待： 一個進程本身占有資源（一種或多種），同時還有資源未得到滿足赌躺，正在等待其他進程釋放該資源狼牺。
3.不可搶占： 別人已經(jīng)占有了某項資源，你不能因為自己也需要該資源礼患，就去把別人的資源搶過來是钥。
4.循環(huán)等待： 存在一個進程鏈掠归，使得每個進程都占有下一個進程所需的至少一種資源。

在數(shù)據(jù)庫中悄泥，由于存在事務(wù)的隔離等級虏冻，“鎖"便被利用起來，第一個條件資源互斥滿足弹囚。
由于事務(wù)開啟后厨相，如果不提交事務(wù)，"鎖"不會主動釋放鸥鹉，第二個條件占有且等待滿足蛮穿。
一個事務(wù)無法搶占其他事務(wù)的鎖，第三個條件不可搶占滿足毁渗。
那么如果這時候循環(huán)等待滿足了践磅，就會產(chǎn)生死鎖，所以數(shù)據(jù)庫的死鎖很大部分是和循環(huán)等待有關(guān)的灸异。
比如：
A事務(wù)拿到a數(shù)據(jù)府适，判斷后還需要拿b數(shù)據(jù)，最后提交數(shù)據(jù)肺樟。
B事務(wù)拿到b數(shù)據(jù)细溅，判斷后還需要拿c數(shù)據(jù)，最后提交數(shù)據(jù)儡嘶。
C事務(wù)拿到c數(shù)據(jù)喇聊，判斷后還需要拿a數(shù)據(jù)，最后提交數(shù)據(jù)蹦狂。
假設(shè)他們同時開始誓篱，那么三個事務(wù)都無法提交。

主從復制（備份）凯楔。

原理：
1.master將改變記錄到二進制文件（binary log）窜骄，這個記錄過程叫做二進制日志事件--binary log events
2.slave將matera的binary log events拷貝到它的中繼日志relay log
3.slave重做中繼日志中的事件，將改變應(yīng)用到自己的數(shù)據(jù)庫中摆屯，MYSQL的復制是異步邻遏、串行化的。
要求版本一致

致謝

尚硅谷MySql的網(wǎng)課
李玉婷老師的課很好聽虐骑，推薦大家有空可以看看自己需要的那部分准验。