操作數(shù)據(jù)庫

內(nèi)置函數(shù)

DDL操作（增刪改）表和字段

DCL主要用來創(chuàng)建用戶以及控制用戶權(quán)限

DDL-表操作-修改

alter table 表名 add 字段名 類型(長度) [comment 注釋] [約束]；

修改數(shù)據(jù)類型

alter table 表名 modify 字段名 新數(shù)據(jù)類型(長度);

修改字段名和字段類型

alter table 表名 change 舊字段名 新字段名 類型(長度)[comment 注釋] [約束]；

刪除字段

alter table 表名 drop 字段名；

修改表名

alter table 表名 rename to 新表名；

刪除表

drop table [if exists] 表名；

刪除指定表，并重新創(chuàng)建該表

truncate table 表名；

---------------------------------------------------

--------------------------------------

笛卡爾積：兩張表所有數(shù)據(jù)的組合情況

select * from emp,dept where emp.dept_id = dept.id.（隱式內(nèi)連接）

內(nèi)連接查詢兩張表的交集

內(nèi)連接和外連接有什么區(qū)別？

內(nèi)連接查詢的是多表之間的交集部分，外連接可以查詢左表也可以查詢右表，可以排除或包含交集。

左外連接可以改成右外連接，交換左右表就行。

自連接查詢，可以是內(nèi)連接查詢也可以是外連接查詢，用以查詢兩張表連接的部分，注意自連接必須取別名。

聯(lián)合查詢注意點 union 去重，union all 不去重，其次多張表查詢的字段和列必須一樣

外連接和單表查詢能否相互替代？

不能，外連接是A表通過B表相關(guān)的條件來查詢A表當(dāng)中符合的記錄。

======================================

存儲引擎是基于表的不是基于庫（數(shù)據(jù)庫）的

Innodb和MyISAM的主要區(qū)別：

Innodb支持事務(wù)、外鍵，MyISAM和Memory都不支持。

Innodb支持行鎖，MyISAM和Memory支持表鎖

PS：Memory引擎基于內(nèi)存來存儲數(shù)據(jù)，由于內(nèi)存斷電會丟失數(shù)據(jù)，所以這些表只能作為臨時表或緩存來使用。它的特點是支持hash索引

======================================

事務(wù)的4個特性：

原子性：事務(wù)是不可分隔的最小操作單位，要么全部成功，要么全部失敗。

一致性：事務(wù)完成時，必須使所有的數(shù)據(jù)都保持一致狀態(tài)。

隔離性：數(shù)據(jù)庫系統(tǒng)提供的隔離機(jī)制，保證事務(wù)在不受外部并發(fā)操作影響的獨(dú)立環(huán)境下運(yùn)行。

持久性：事務(wù)一旦提交或回滾，它對數(shù)據(jù)庫的改變就是永久的。

原子性：轉(zhuǎn)賬過程只要有任何一步失敗了，轉(zhuǎn)賬行為就失敗了。

一致性：a賬號給b賬號轉(zhuǎn)賬，a賬號不會多一塊錢，b賬號不會少一塊錢。

隔離性：a事務(wù)和b事務(wù)是獨(dú)立運(yùn)行的，它們不會相互干擾。

持久性：對數(shù)據(jù)庫的數(shù)據(jù)操作，影響是永久的。

并發(fā)事務(wù)問題

臟讀：一個事務(wù)讀到另一個事務(wù)還沒有提交的數(shù)據(jù)。

不可重復(fù)讀：一個事務(wù)先后讀取同一個記錄，但兩次讀取到的數(shù)據(jù)不同。

幻讀：一個事務(wù)按照條件查詢數(shù)據(jù)時，沒有對應(yīng)的數(shù)據(jù)行，但是在插入數(shù)據(jù)時，又發(fā)現(xiàn)這行數(shù)據(jù)已經(jīng)存在。（也是明明記錄不存在卻無法插入的原因）

注意：臟讀、幻讀、不可重復(fù)讀的共同點都是讀到實際上不存在的數(shù)據(jù)。

各種隔離級別會出現(xiàn)的問題：

======================================

MyISAM使用場景：適用于以讀寫操作為主，少量的更新和刪除操作，但現(xiàn)在更多用新Nosql數(shù)據(jù)庫如Mongodb來滿足這種需求。

Memory，通常用來做臨時表或者緩存，一般可以用緩存數(shù)據(jù)庫像redis緩存來滿足這種需求。

=====================================

Mysql體系結(jié)構(gòu)：連接層、服務(wù)層（sql解析和優(yōu)化）、引擎層（不同的引擎的索引結(jié)構(gòu)是不同的）、存儲層（數(shù)據(jù)、索引、日志文件存儲在磁盤當(dāng)中）

不同的存儲引擎影響數(shù)據(jù)的存儲、獲取、更新、查詢的方式

======================================

“索引結(jié)構(gòu)、索引分類、索引語法、SQL性能分析、索引使用、索引設(shè)計原則”

索引是什么？有什么作用？優(yōu)缺點是什么？

索引是一種數(shù)據(jù)結(jié)構(gòu)（有序），它是用來高效獲取數(shù)據(jù)的。

索引可以提高查詢效率和排序效率，缺點是降低了增刪改的效率。

默認(rèn)全表掃描

建立索引，維護(hù)索引

-------------------------------------------------------------------

B+tree索引（最常見的）、

Hash索引（不支持范圍查詢、只有精確匹配索引的列有效）、

R-tree空間索引（地理空間數(shù)據(jù)，用的少）、

Full-text全文索引（如倒排索引）

InnoDB支持b+tree、全文索引

MyISAM不支持hash索引，其余支持

Memory支持b+tree、hash索引，其余不支持

-------------------------------------------------------------------

二叉樹缺點：順序插入時，會形成一個單向鏈表，導(dǎo)致查詢性能大大降低。大數(shù)據(jù)量情況下，層級較深，檢索速度慢。

紅黑樹缺點：層級較深，檢索速度慢。

B-Trees（多路平衡樹【用key來劃分范圍查找】）

每個節(jié)點都會存放數(shù)據(jù)

-------------------------------------------------------------------

B+Tree

分頁子節(jié)點只起到索引的作用

所有的元素都會出現(xiàn)在葉子節(jié)點

所有的數(shù)據(jù)都出現(xiàn)在葉子節(jié)點，葉子節(jié)點形成一個單向鏈表

MySQL索引數(shù)據(jù)結(jié)構(gòu)對經(jīng)典B+Tree進(jìn)行了優(yōu)化，在B+Tree基礎(chǔ)上，增加了一個指向鄰葉子的鏈表指針，形成了帶有順序指針的B+Tree，提高區(qū)間訪問的性能。

======================================

hash索引結(jié)構(gòu)

特點：

1.hash索引只能用于對等比較（=，in），不吹；范圍查詢（between，>，<）

2.無法利用索引完成排序操作

3.查詢效率高，通常只需一次檢索就可以，效率通常要高于B+Tree索引

InnoDB存儲引擎支持自適應(yīng)hash索引的功能，InnoDB根據(jù)存儲引擎B+Tree索引在指定條件下自動構(gòu)建hash索引。

------------------------------------------------------------------

為何mysql不選擇b樹？b+樹有什么優(yōu)勢？

b樹不論是葉子節(jié)點還是非葉子節(jié)點都會保存數(shù)據(jù)，這樣會導(dǎo)致頁中存儲的鍵值減少，要保存同樣的大量數(shù)據(jù)，只能增加樹的高度，最后導(dǎo)致性能降低。

======================================

索引分類

如果表有主鍵，主鍵就是聚集索引

聚集索引下面掛的是一行數(shù)據(jù)

二級索引（也叫輔助索引一張表的第二個索引？）下面（葉子節(jié)點）掛的是該行數(shù)據(jù)的id。

“回表查詢”指的是先根據(jù)二級索引找到對應(yīng)的主鍵值，然后根據(jù)主鍵值再找聚集索引中的葉子節(jié)點拿到行數(shù)據(jù)

-------------------------------------------------------------------

為什么根據(jù)主鍵查詢比非主鍵查詢更快？

因為根據(jù)主鍵不用回表查詢，而非主間需要先通過二級索引進(jìn)行回表查詢。

InnoDB主鍵索引的B+tree高度有多高？3層

*指針比key多一個，每一個節(jié)點能存放多少個key？

*主鍵占用的字節(jié)數(shù)*當(dāng)前節(jié)點所能存儲的key的數(shù)量*指針占用的空間

ps：公式關(guān)系即：指針?biāo)加玫目臻g+所有key所占用的空間=一張表的數(shù)據(jù)（3層樹所承載的數(shù)據(jù)量，超過了就要增加樹的深度導(dǎo)致性能下降）

=======================================索引語法

CREATE INDEX idx_user_name ON tb_user(name);

CREATE UNIQUE INDEX idx_user_phone ON tb_user(phone);

CRAETE INDEX idx_user_pro_age_sta ON tb_user(professsion,age,status);

CREATE INDEX idx_email ON tb_user(email);

SQL性能分析的目的是為了做SQL優(yōu)化

如何查詢數(shù)據(jù)庫的增、刪、改、查的頻率？

SHOW GLOBAL STATUS LIKE 'Com____";

慢查詢?nèi)罩尽ㄎ籗QL語句

慢查詢?nèi)罩镜拈_關(guān)怎么打開？

show variable like 'slow_query_log";

修改配置文件：show_query_log=1開啟

long_query_time=2 超過2秒設(shè)為慢查詢

systemctl restart mysqld

tail -f localhost-slow.log

-------------------------------

profile詳情

當(dāng)前數(shù)據(jù)庫是否支持profile查詢？

select @@have_profiling;

查詢profile查詢范圍

select @@profiling;

查詢一條sql每個耗時階段可不了解

explain執(zhí)行計劃

explain | desc + sql語句 即可分析sql語句

explain執(zhí)行計劃各字段的含義：

id表示不同SQL的執(zhí)行順序，id相同排前面先執(zhí)行，id值不同，值大先執(zhí)行

type表示連接類型，性能由好到差依次是

NULL、system、const、eq_ref、ref、range、index、all

possible_key表示可能用到的索引

key表示實際用到的索引

主要關(guān)注的字段如下表：

======================================

索引的使用規(guī)則

最左前綴法則，要想用到聯(lián)合索引就得保證查詢條件最左邊的列存在（如where id=“” and name="" and ...），如果不存在，聯(lián)合索引就會失效，如果中間跳過某一列，跳過的列會引起后面的索引列失效。

范圍查詢（右側(cè)的列索引失效），聯(lián)合索引中出現(xiàn)范圍查詢(<,>)，范圍查詢右側(cè)的列所有失效（如where id=“” and age>30 and sex=0），但范圍查詢加等號如大于等于，右側(cè)的索引就不會失效，業(yè)務(wù)允許的話可以加。

不要在索引列做運(yùn)算，否則索引列將失效，比如對索引列進(jìn)行函數(shù)計算substring（phone，0，2）

數(shù)值字符串不加引號導(dǎo)致索引失效

模糊查詢，左模糊索引失效（%工程），右模糊不失效（軟件%），左右模糊（%工程%）也失效。

or連接的條件，or連接的列只要有一個沒有建立索引就會導(dǎo)致or關(guān)聯(lián)的列索引失效

數(shù)據(jù)分布影響（特殊規(guī)則）

1.如果走索引的速度不如全表掃描就不會走索引

2.“is null”和“is not null”會不會走索引都不是固定的，數(shù)據(jù)庫會根據(jù)數(shù)據(jù)分布情況來決策，取快優(yōu)先

SQL（索引使用）提示，提示數(shù)據(jù)庫使用某個索引或忽略某個索引或強(qiáng)制使用某個索引

eg:“select * from tb_user use index(idx_user_pro) where...." 建議數(shù)據(jù)庫使用指定的索引

“select * from tb_user ignore index(idx_user_pro) where...."

“select * from tb_user force index(idx_user_pro) where...." 強(qiáng)制數(shù)據(jù)庫使用指定的索引

覆蓋索引，盡量使用覆蓋索引（覆蓋索引即查詢使用了索引，并且需要返回的列，在該索引中已經(jīng)全部能夠找到），避免使用select*（原因是全字段查詢可能會涉及到二級索引沒有覆蓋的字段，導(dǎo)致回表查詢降低了查詢性能。）。

注意：聚集索引的葉子節(jié)點下儲存的是行數(shù)據(jù)，輔助索引的葉子節(jié)點下儲存的是id。

如果經(jīng)過輔助索引的查詢，又經(jīng)過聚集索引來查詢，就是回表查詢。

實踐（也是面試題）：

select id,username,pwd from tb_user where username="zhangsan";如何優(yōu)化？

前綴索引，是提取索引的一部分前綴來建立索引， 它是用來解決長字符串或者大文本字段在整個字段進(jìn)行索引的時候，索引的體積龐大造成浪費(fèi)大量磁盤IO的情況，使用前綴索引可以降低索引的體積來提高索引效率。

eg：create index idx_xxx on tb(column(n));

前綴長度考量，可以根據(jù)不重復(fù)的索引值和數(shù)據(jù)表的記錄總數(shù)的比值來決定，這種選擇性越高查詢效率越高。唯一索引的選擇性是1，是性能最好的。

eg：select count(distinct email)/count(*) from tb;

select count(distinct substring()email)/count(*) from tb;

截取前9個字符測試選擇性。

根據(jù)業(yè)務(wù)需求，如果要求選擇性高，前綴就取長一點。

-------------------

單列索引&聯(lián)合索引

數(shù)據(jù)庫會優(yōu)先使用單列索引，這可能會導(dǎo)致回表查詢，因此我們在建立索引的時候應(yīng)該優(yōu)先考慮創(chuàng)建聯(lián)合索引，避免使用單列索引，在創(chuàng)建聯(lián)合索引的時候需要考慮索引的順序，必須保證最左側(cè)的列存在

=====================================

=====================================

SQL優(yōu)化

1. insert優(yōu)化
2. 主鍵優(yōu)化
3. order by優(yōu)化
4. group by優(yōu)化
5. limit優(yōu)化
6. count優(yōu)化
7. update優(yōu)化

大批量插入load，默認(rèn)關(guān)閉

--------------------------------------------------------------

由于在InnoDB存儲引擎中表數(shù)據(jù) 都是根據(jù)主鍵的順序來存放的，所以亂序插入數(shù)據(jù)時可能導(dǎo)致頁分裂，刪除數(shù)據(jù)時可能導(dǎo)致頁合并。

--------------------------------------------

排序優(yōu)化

創(chuàng)建索引時默認(rèn)采用升序排列，如果排序時使用降序會使索引失效。

解決：

create index idx_user_age_pho_ad on tb_user(age asc, phone desc);

order by優(yōu)化

1. 根據(jù)排序字段建立合適的索引，多字段排序時，也遵循最左前綴法則。
2. 盡量使用覆蓋索引。
3. 多字段排序，一個升序一個降序，此時需要注意聯(lián)合索引在創(chuàng)建時的規(guī)則（ASC/DESC)。
4. 如果不可避免的出現(xiàn)filesort，大數(shù)據(jù)量排序時，可以適當(dāng)增大排序緩沖區(qū)大小sort_buffer_size(默認(rèn)256K）

------------------------------------------

-------------------------------------------------

limit優(yōu)化

通過覆蓋索引+子查詢的方式來優(yōu)化

eg：explain select * from tb_sku t,(select id from tb_sku order by id limit 2000000,10) a where.id=a.id;

eg2:select s.* from tb_sku s,(select id from tb_sku order by id limit 9000000,10) a where si.id=a.id;

count優(yōu)化

• MyISAM引擎把一個表的總行數(shù)存在了磁盤上，因此 執(zhí)行count(*)的時候會直接返回這個數(shù)，效率很高；
• InnoDB引擎執(zhí)行count(*)的時候需要把數(shù)據(jù)一行一行地從引擎里面讀出來，然后計數(shù)。

優(yōu)化思路：自己維護(hù)計數(shù)

count的幾種用法

count() 是一個聚合函數(shù)，對于返回的結(jié)果集，一行行地判斷，如果count函數(shù)的參數(shù)不是null，累計值就加1，否則不加，最后返回累計值。

用法：count(*)（性能最高） 、count(主鍵)、count(字段)、count(1)

-------------------------------------------------------

update優(yōu)化

InnoDB的行鎖是針對索引加的鎖，不是針對記錄加的鎖，并且該索引不能失效，否則會從行鎖升級為表鎖。（id也是索引，所以id是行鎖）

-------------------------------------------------------

總結(jié)

======================================

mysql存儲對象——視圖/存儲過程/觸發(fā)器

視圖是一種虛擬表，不保存數(shù)據(jù)，只保存SQL邏輯

語法：create view view_name as sql語句

視圖的作用：針對的是數(shù)據(jù)庫用戶（數(shù)據(jù)庫維護(hù)角度）

---------------------------------------

存儲過程，用以優(yōu)化發(fā)送sql網(wǎng)絡(luò)IO，減少網(wǎng)絡(luò)交互

對SQL語句進(jìn)行封裝以便重用

存儲函數(shù)只入?yún)⒉怀鰠?，但有return返回值，存儲過程能夠完全取代存儲函數(shù)，至于存儲函數(shù)強(qiáng)制要求返回值，這既是優(yōu)點也是缺點，但沒有存儲過程可以靈活選擇。

------------------------------------

觸發(fā)器的作用是監(jiān)控數(shù)據(jù)庫表中行的操作，可以把監(jiān)控的結(jié)果記錄到日志表當(dāng)中。

總結(jié)：

======================================

數(shù)據(jù)庫鎖

鎖是計算機(jī)協(xié)協(xié)調(diào)多個進(jìn)程或線程并發(fā)訪問某一資源的機(jī)制。由于數(shù)據(jù)是一種共享資源，因此并發(fā)訪問會帶來一致性和有效性的問題，而鎖是影響并發(fā)訪問性能的一個重要因素。

鎖分類

全局鎖，鎖住數(shù)據(jù)庫的所有表，數(shù)據(jù)庫實例加鎖，在備份的時候可加全局鎖，只提供訪問，不支持增刪改操作。

表級鎖，每次操作鎖住整張表。

行級鎖，每次操作鎖住對應(yīng)的行數(shù)據(jù)。

全局鎖實現(xiàn)數(shù)據(jù)備份：

加鎖：flush tables with read lock;

解鎖：unlock tables;

不加鎖實現(xiàn)數(shù)據(jù)備份：

mysqldomp --single-transation -h 192... db01 > 地址

-----------------------------------

表級鎖分類：表鎖、元數(shù)據(jù)鎖、意向鎖

表鎖又分兩類：表共享讀鎖、表獨(dú)占寫鎖

語法：

1.加鎖：lock tables 表名 read/write.

2.釋放鎖：unlock tables

讀鎖不會阻塞其他客戶端的讀，但會阻塞寫，寫鎖既會阻塞其他客戶端的讀，也會阻塞其他客戶端的寫。（兩方都不能寫）

寫鎖會阻塞其他客戶端的讀寫，不會阻塞自己的讀寫

----------------------------------

元數(shù)據(jù)鎖

為了避免DML與DDL沖突，保證讀寫的正確性。

加鎖是系統(tǒng)自動控制的

----------------------------------

意向鎖（自動加）

用以解決InnoDB引擎下加表鎖與行鎖的沖突

使得使用表鎖不用檢查每行數(shù)據(jù)是否加鎖，減少了表鎖所需的逐行檢查。

分類：

意向共享鎖（IS）：與表鎖共享鎖兼容，與表鎖排他鎖互斥。

語法 select……lock in share mode添加；

意向排他鎖（IX）：與表鎖共享鎖及排他鎖都互斥，意向鎖之間不會互斥。

crud……for update 添加；

-----------------------------------------------------------------

行級鎖

分類：行鎖、間隙鎖、臨建鎖

行鎖的分類

共享鎖（s）：允許一個事務(wù)去讀一行，阻止其他事務(wù)獲得相同數(shù)據(jù)集的排它鎖。

排它鎖（x）：允許獲取排它鎖的事務(wù)更新數(shù)據(jù)，阻止其他事務(wù)獲取相同數(shù)據(jù)集的共享鎖和排它鎖。

增刪改自動加排它鎖，讀不加任何鎖，

共享鎖實現(xiàn)：SELECT&LOCK IN SHARE MODE

排它鎖實現(xiàn)：SELECT&FOR UPDATE

------------------------------------

間隙鎖和臨鍵鎖的唯一目的是防止其他事務(wù)插入間隙造成幻讀現(xiàn)象，間隙鎖可以共存，一個事務(wù)采用的間隙鎖不會阻止另一個事務(wù)在同一間隙上采用間隙鎖。

間隙鎖鎖的是間隙不包含對應(yīng)的數(shù)據(jù)記錄，而臨鍵鎖既會鎖定包含的對應(yīng)記錄也會鎖定該數(shù)據(jù)記錄之前的部分間隙。

關(guān)于間隙鎖和臨鍵鎖只要知道它為什么要加就行了。

-------------------------------------

總結(jié)：

======================================

InnoDB引擎詳解

• 邏輯存儲結(jié)構(gòu)
• 架構(gòu)
• 事務(wù)原理
• MVCC

邏輯存儲結(jié)構(gòu)

架構(gòu)

---------------------------------------------------------------

----------------------------------------------------------------

后臺線程

當(dāng)我們業(yè)務(wù)在操作的時候那么會直接操作緩沖區(qū)，如果緩存區(qū)沒有數(shù)據(jù)會將磁盤中的數(shù)據(jù)加載回來，然后再存儲在緩沖區(qū)當(dāng)中，我們在增刪改查的時候都會去操作緩沖區(qū)，然后緩沖區(qū)的數(shù)據(jù)會以一定的頻率或時機(jī)要通過后臺線程去刷新到磁盤當(dāng)中，然后在磁盤當(dāng)中進(jìn)行永久化的保存。

--------------------------------------------------------------------

事務(wù)原理

事務(wù)是一組操作的集合，這些操作要么同時成功，要么同時失敗。

原子性：事務(wù)是不可分割的最小操作單元，要么全部成功，要么全部失敗。

一致性：事務(wù)完成時，必須使所有的數(shù)據(jù)保持一致的狀態(tài)。

隔離性：數(shù)據(jù)庫提供的隔離機(jī)制來保證事務(wù)不受外部并發(fā)操作影響可在獨(dú)立環(huán)境下運(yùn)行。

隔離級別：讀未提交、讀已提交、可重復(fù)讀（innodb默認(rèn)）、串行化

持久性：事務(wù)一旦提交或回滾，對數(shù)據(jù)庫中的數(shù)據(jù)的改變是永久的

事務(wù)原理：

事務(wù)的原子性、一致性、持久性是由redo log和undo log 來保障的，而隔離性是通過鎖機(jī)制和MVCC（多版本并發(fā)控制）來保障的。

持久性是有redo log重做日志來保證的，重做日志由兩部分組成分別是“重做日志緩沖”和“重做日志文件”，前者在內(nèi)存中，后者在磁盤中，當(dāng)事務(wù)提交時會把所有修改信息都存到該日志文件中，用于在刷新臟頁到磁盤發(fā)生錯誤時進(jìn)行數(shù)據(jù)恢復(fù)。

小結(jié)：redo log就是為了保證我們在進(jìn)行臟頁刷新發(fā)生錯誤時進(jìn)行數(shù)據(jù)恢復(fù)，從而保證事務(wù)的持久性。

臟頁：在內(nèi)存中修改的數(shù)據(jù)頁。

緩沖區(qū)的臟頁直接刷新到磁盤中不好嗎為什么還要通過redo log緩沖區(qū)去異步刷新？（性能差別大）

事務(wù)一般都會涉及操作隨機(jī)數(shù)據(jù)頁，因此這種操作會產(chǎn)生大量的隨機(jī)磁盤IO，性能是很地下的，如果我們在進(jìn)行操作的時候用到redo log不會把脹頁直接刷新，先把日志異步刷新到磁盤中，

由于日志文件都是追加的，所以它是順序磁盤IO，那么它的性能是要高于隨機(jī)磁盤IO，這種機(jī)制就叫WAL（Write-Ahead Logging先寫日志），過一段時間再將臟頁數(shù)據(jù)刷入磁盤中。

----------------------------------------

undo log

它是用來解決失誤的原子性，undo log也叫回滾日志，記錄數(shù)據(jù)修改前的信息，主要的作用是提供回滾和多版本并發(fā)控制

undo log記錄的是邏輯日志（記錄原始數(shù)據(jù)），redo log記錄的是物理日志

----------------------------------------

MVCC

基本概念

多版本并發(fā)控制是維護(hù)一個數(shù)據(jù)的多個版本使得讀寫操作沒有沖突，具體實現(xiàn)還依賴三個隱式字段、undo log日志、readView。

當(dāng)前讀，即讀取到的是最新的數(shù)據(jù)，加鎖

快照讀，即讀取的到數(shù)據(jù)可能是最新數(shù)據(jù)，不加鎖

----------------------------------------

事務(wù)id，回滾指針、隱藏主鍵

------------------------------------------

undo log 回滾日志

在插入數(shù)據(jù)時產(chǎn)生數(shù)據(jù)回滾日志。提交事務(wù)后會立即刪除操作日志，而在修改和刪除不會被立即刪除，因為在快照讀時還需要用到。

undo log版本鏈

當(dāng)我們在進(jìn)行查詢的時候到底應(yīng)該返回哪一個版本？這個不是由版本鏈來控制的，而是由readview來控制的。

------------------------------------------

readview

主要是用于記錄并維護(hù)系統(tǒng)當(dāng)前活躍的事務(wù)（未提交的）id。

mvcc提取數(shù)據(jù)的是依據(jù)以下四個核心字段

版本鏈數(shù)據(jù)訪問規(guī)則不用記

----------------------------------------

總結(jié)：

=====================================

總結(jié)：

存儲引擎、索引、常見SQL的優(yōu)化、數(shù)據(jù)庫對象（視圖、存儲過程、存儲函數(shù)、觸發(fā)器）、鎖（全局鎖、表級鎖、行級鎖）、InnoDB引擎

======================================

運(yùn)維篇

• 日志
• 主從復(fù)制
• 分庫分表
• 讀寫分離

日志

• 錯誤日志
• 二進(jìn)制日志
• 查詢?nèi)罩?/li>
• 慢查詢?nèi)罩?/span>

查看錯誤日志：

show variables like '%log_error%'

查看錯誤日志前50行：

tail -50 /var/log/mysqld.log

--------------------------

二進(jìn)制日志包含了DDL（SQL定義語句）和DML（增刪改語句）語句

作用是1.災(zāi)備恢復(fù) 2.主從復(fù)制

show variables like '%log_bin%'

默認(rèn)的日志格式是ROW，記錄每一行的日志記錄

--------------------------

查詢?nèi)罩?/p>

默認(rèn)關(guān)閉(忙時很占用磁盤空間)，開啟方式

在my.cnf文件添加general_log=1

general_log_file=mysql_query.log

-------------------------------------------------------------------主從復(fù)制

主從復(fù)制是指將主數(shù)據(jù)庫的DDL和DML操作通過二進(jìn)制日志傳到從庫服務(wù)器中，然后在從庫上重新執(zhí)行這些操作日志，從而使得從庫與主庫的數(shù)據(jù)保持同步。

增刪改操作主庫，查詢請求操作從庫，可降低主庫的壓力。

在從庫進(jìn)行備份，用全局鎖去鎖從庫，主庫依然可以進(jìn)行增刪改的相關(guān)操作，但數(shù)據(jù)同步可能會存在一定的延遲。

主從復(fù)制的原理

主庫的數(shù)據(jù)是怎么同步到從庫當(dāng)中？

主庫會把所有DML和DDL操作寫入binlog日志，在從數(shù)據(jù)庫當(dāng)中有兩個線程，其中一個IO線程就會去連接主庫讀取其中的binlog日志然后寫入從庫，這份寫入的日志也叫中繼日志，然后從庫的另一個SQL線程會來讀取中繼日志，把中繼日志中的數(shù)據(jù)變化反應(yīng)到自身的數(shù)據(jù)變化，這樣保證了主從數(shù)據(jù)數(shù)據(jù)同步。

主從復(fù)制-主庫配置

1. 修改配置文件配置讀寫權(quán)限
2. 開放指定端口號
3. 授予主從復(fù)制權(quán)限
4. 查看二進(jìn)制日志坐標(biāo)

主從復(fù)制-從庫配置 參考主庫配置

重啟mysql服務(wù)的指令：systemctl restart mysqld

===================================

分庫分表

Mycat入門

Mycat配置

Mycat分片

Mycat管理及監(jiān)控

用以解決性能瓶頸（IO瓶頸和CPU瓶頸）

分庫和分表是粒度的不同，垂直拆分和水平拆分是維度的不同。

垂直分庫垂直分表，水平分庫水平分表

實現(xiàn)技術(shù)

Mycat不存儲數(shù)據(jù)，只是處理分片數(shù)據(jù)

邏輯庫配置

Mycat啟動命令：bin/mycat start ，默認(rèn)端口8066

------------------------------------

邏輯表

Mycat分片

跨庫查詢有會什么問題？

設(shè)置全局表可以跨庫查詢

按范圍分片規(guī)則

分片規(guī)則（數(shù)據(jù)插入的分配規(guī)則）——

• 范圍
• mod-long（取模），根據(jù)指定字段值與節(jié)點數(shù)量進(jìn)行求模運(yùn)算，根據(jù)運(yùn)算結(jié)果來決定數(shù)據(jù)屬于哪個分片。
• 一致性hash
• 枚舉分片
• 應(yīng)用指定算法
• 固定hash算法
• 字符串hash解析
• 按天分片
• 按自然月分片

這些分片規(guī)則需要了解它們的適用場景和特點

分片規(guī)則分類

主鍵為數(shù)字類型的字段適用范圍、取模

主鍵為字符串適用一致性hash、固定hash

日期類型適用按自然月、按天

----------------------------------

Mycat管理與監(jiān)控原理

--------------------------------------

Mycat管理工具

mycat的自帶管理指令使用不方便，建議使用監(jiān)控工具

---------------------------------------------------

讀寫分離

• 一主一從
• 一主一從讀寫分離
• 雙主雙從
• 雙主雙從讀寫分離

讀寫分離基于主從復(fù)制

增刪改走主節(jié)點，查詢走從節(jié)點

balance取值分配

===================================

課程總結(jié)：