存儲引擎

MySQL體系結構

• 連接層：
  • 最上層是一些客戶端和連接服務，主要完成一些類似于連接處理、授權認證、及相關的安全方案。服務器也會為安全接入的每個客戶端驗證它所具有的操作權限。
• 服務層：
  • 第二層架構主要完成大多數的核心服務功能，如SQL接口，經完成緩存的查詢，SQL的分析和優化，部分內置函數的執行。所有跨存儲引擎的功能也在這一層實現。如 過程、函數等。
• 引擎層：
  • 存儲引擎真正的負責了MySQL中數據的存儲和提取，服務器通過API和存儲引擎進行通信。不同的存儲引擎具有不同的功能，這樣我們可以感覺自己的需要，來選去合適的存儲引擎。
• 存儲層：
  • 主要是將數據存儲在文件系統之上，并完成與存儲引擎的交互。

存儲引擎簡介

存儲引擎就是存儲數據、建立索引、更新、查詢數據等技術的實現方式。存儲引擎是基于表的，而不是基于庫的，所有存儲引擎又稱為表類型。

-- -------------------------------------------------進階
-- 查詢創建表語句
show create table dept;
-- 查詢支持的存儲引擎
show engines ;
-- 創建一個my_MyISAMy表，用MyISAM引擎
create table my_MyISAM(id   int,name varchar(10)
)engine=MyISAM;

存儲引擎特點

• InnoDB
  • 介紹
    • InnoDB是一種兼顧可靠性和高性能的通用存儲引擎，在MySQL5.5之后，InnoDB是默認的MySQL存儲引擎。
  • 特點
    • DML操作遵循ACID模型，支持事務；
    • 行級鎖，提高并發訪問性能
    • 支持外鍵約束，保證數據的完整性和正確性
  • 文件
    • xxx.ibd:xxx代表的是表名，innoDB引擎的每張表都會對應這樣一個表空間文還能，存儲該表的表結構、數據和索引
    • 參數是：innodb_file_per_table
  • 
• MyISAM
  • 介紹
    • MyISAM是MySQL早期的默認存儲引擎。
  • 特點
    • 不支持事務，不支持外鍵
    • 支持表鎖，不支持行鎖
    • 訪問速度快
  • 文件
    • xxx.sdi:存儲表結構信息
    • xxx.MYD:存儲數據
    • xxx.MYI:存儲索引
• Memory
  • 介紹：
    • Memory引擎的表數據是存儲在內存中，由于受到硬件的問題或斷電問題的影響，只能做為臨時表或緩存使用
  • 特點：
    • 內存存放
    • hash索引（默認）
  • 文件：
    • xxx.sdi：存儲表結構信息
• 

存儲引擎選擇

在選擇存儲引擎時，應該根據應用系統的特點選擇合適的存儲引擎。對于復雜的應用系統，還可以根據實際情況選擇多種存儲引擎進行組 合。

• lnnoDB：是Mysql的默認存儲引擎，支持事務、外鍵。如果應用對事務的完整性有比較高的要求，在并發條件下要求數據的一致 性，數據操作除了插入和查詢之外，還包含很多的更新、刪除操作，那么InnoDB存儲引擎是比較合適的選擇。
• MylISAM：如果應用是以讀操作和插入操作為主，只有很少的更新和刪除操作，并且對事務的完整性、并發性要求不是很高，那 么選擇這個存儲引擎是非常合適的。
• MEMORY：將所有數據保存在內存中，訪問速度快，通常用于臨時表及緩存。MEMORY的缺陷就是對表的大小有限制，太大的表 無法緩存在內存中，而且無法保障數據的安全性。

小結

索引

介紹

索引（index)是幫助MySQL高效獲取數據的數據結構（有序）。在數據之外，數據庫系統還維護著滿足特定查找算法的數據結構，這些 數據結構以某種方式引用（指向）數據，這樣就可以在這些數據結構上實現高級查找算法，這種數據結構就是索引。

優缺點

優勢

• 提高數據檢索的效率，降低數據庫的I0成本
• 通過索引列對數據進行排序，降低數據排序的成本，降低CPU的消 耗。

劣勢

• 索引列也是要占用空間的。
• 索引大大提高了查詢效率，同時卻也降低更新表的速度，如對表進 行INSERT、UPDATE、DELETE時，效率降低。

索引結構

MySQL的索引是在存儲引擎層實現的，不同的存儲引擎有不同的結構，主要包含以下幾種：

索引結構	描述
B+Tree索引	最常見的索引類型，大部分引擎都支持B+樹索引
Hash索引	底層數據結構是用哈希表實現的，只有精確匹配索引列的查詢才有效，不支持范圍查詢
R-tree(空間索引)	空間索引是MylSAM引擎的一個特殊索引類型，主要用于地理空間數據類型，通常使用較少
Full-text(全文索引)	是一種通過建立倒排索引，快速匹配文檔的方式。類似于Lucene,Solr,ES

我們平常所說的索引，如果沒有特別指明，都是指B+樹結構組織的索引。

索引	InnoDB	MyISAM	Memory
B+tree索引	支持	支持	支持
Hash索引	不支持	不支持	支持
R-tree索引	不支持	支持	不支持
Full-text	5.6版本之后支持	支持	不支持

B-Tree(多路平衡查找樹)

以一顆最大度數（max-degree)為5(5階)的b-tree為例（每個節點最多存儲4個key，5個指針）：

具體動態變化的過程可以參考網站:https://www.cs.usfca.edu/~galles/visualization/BTree.html

B+Tree

相對于B-Tree區別：

• ①.所有的數據都會出現在葉子節點
• ②.葉子節點形成一個單向鏈表

MySQL索引數據結構對經典的B+Tree進行了優化。在原B+Tree的基礎上，增加一個指向相鄰葉子節點的鏈表指針，就形成了帶有順序指針的B+Tree，提高區間訪問的性能。

Hash

哈希索引就是采用一定的hash算法，將鍵值換算成新的hash值，映射到對應的槽位上，然后存儲在hash表中。 如果兩個(或多個)鍵值，映射到一個相同的槽位上，他們就產生了hash沖突(也稱為hash碰撞），可以通過鏈表來解決。

?Hash索引特點

• ?Hash索引只能用于對等比較(=，in)，不支持范圍查詢(between,>，<，.)
• 無法利用索引完成排序操作
• 查詢效率高，通常只需要一次檢索就可以了，效率通常要高于B+tree索引

存儲引擎支持

在MySQL中，支持hash索引的是Memory引擎，而InnoDB中具有自適應hash功能，hash索引是存儲引擎根據B+Tree索引在指定條件下自動構建的。

為什么InnoDB存儲引擎選擇使用B+tree索引結構？

• 相對于二叉樹，層級更少，搜索效率高；
• 對于B-tree，無論是葉子節點還是非葉子節點，都會保存數據，這樣導致一 頁中存儲的鍵值減少，指針跟著減少，要同樣保存大量數據，只能增加樹的 高度，導致性能降低；
• 相對Hash索引，B+tree支持范圍匹配及排序操作；

索引分類

在InnoDB存儲引擎中，根據索引的存儲形式，又可以分為以下兩種：

聚集索引選取規則：

• 如果存在主鍵，主鍵索引就是聚集索引。
• 如果不存在主鍵，將使用第一個唯一（UNIQUE）索引作為聚集索引。
• 如果表沒有主鍵，或沒有合適的唯一索引，則InnoDB會自動生成一個rowid作為隱藏的聚集索引。

回表查詢：先在二級索引中找到對應的主鍵值，再到聚集索引中拿到這一行的行數據。

索引語法?

創建索引

create [unique|fulltext] index index_name on table_name(index_col_name); 

查看索引

show index from table_name;

刪除索引

drop index index_name on table_name

SQL性能分析工具

?SQL執行頻率

MySQL客戶端連接成功后，通過show[sessionlglobal] status命令可以提供服務器狀態信息。通過如下指令，可以查看當前數據庫的 INSERT、UPDATE、DELETE、SELECT的訪問頻次：

 show global status like 'Com_______';

慢查詢日志

?慢查詢日志記錄了所有執行時間超過指定參數（long_query_time, 單位：秒，默認10秒）的所有SQL語句的日志。

查詢慢查詢日志狀態：

show variables like 'slow_query_log';

MySQL的慢查詢日志默認沒有開啟，需要在MySQL的配置文件（/etc/my.cnf)中配置如下信息：

#開啟MySQL慢日志查詢開關
slow_query_log=1
#設置慢日志的時間為2秒，SQL語句執行時間超過2秒，就會視為慢查詢，記錄慢查詢日志
long_query_time=2

?profile詳情

執行一系列的業務SQL的操作，然后通過如下指令查看指令的執行耗時：

#查看每一條SQL的耗時基本情況
show profiles;
#查看指定query_id的SQL語句各個階段的耗時情況
show profile for query query_id;
#查看指定query_id的SQL語句CPU的使用情況
show profile cpu for query query_id;

?explain執行計劃

EXPLAIN 或者 DESC命令獲取 MySQL如何執行SELECT語句的信息，包括在SELECT語句執行過程中表如何連接和連接的順序。?

語法：#直接在select語句之前加上關鍵字explain/ desc 
EXPLAIN SELECT 字段列表 FROM 表名 WHERE 條件

?EXPLAIN 執行計劃各字段含義：

• ld select查詢的序列號，表示查詢中執行select子句或者是操作表的順序（id相同，執行順序從上到下；id不同，值越大，越先執行)。
• select_type 表示SELECT的類型，常見的取值有SIMPLE（簡單表，即不使用表連接或者子查詢）、PRIMARY（主查詢，即外層的查詢）、 UNION（UNION中的第二個或者后面的查詢語句）、SUBQUERY（SELECT/WHERE之后包含了子查詢）等
• type 表示連接類型，性能由好到差的連接類型為NULL、system、const、eq_ref、ref、range、index、all。
• possible_key 顯示可能應用在這張表上的索引，一個或多個。
• Key 實際使用的索引，如果為NULL，則沒有使用索引。
• Key_len 表示索引中使用的字節數，該值為索引字段最大可能長度，并非實際使用長度，在不損失精確性的前提下，長度越短越好。
• rows MySQL認為必須要執行查詢的行數，在innodb引擎的表中，是一個估計值，可能并不總是準確的。
• filtered 表示返回結果的行數占需讀取行數的百分比，filtered的值越大越好。

SQL優化

視圖/存儲過程/觸發器

視圖

介紹

視圖（View）是一種虛擬存在的表。視圖中的數據并不在數據庫中實際存在，行和列數據來自定義視圖的查詢中使用的表，并且是在使用視圖時動態生成的。

通俗的講，視圖只保存了查詢的SQL邏輯，不保存查詢結果。所以我們在創建視圖的時候，主要的工作就落在創建這條SQL查詢語句上。

語法

創建

CREATE [OR REPLACE] VIEW 視圖名稱[（列名列表)]AS SELECT語句[WITH[CASCADED|LOCAL]CHECK OPTION]

查詢

查看創建視圖語句：SHOW CREATE VIEW 視圖名稱；
查看視圖數據：SELECT*FROM 視圖名稱.…；

修改

方式一：CREATE OR REPLACE VIEW 視圖名稱[（列名列表）]AS SELECT語句[WITH[CASCADED|LOCAL]CHECK OPTION]
方式二：ALTER VIEW 視圖名稱[（列名列表）]AS SELECT語句[WITH[CASCADED|LOCAL]CHECK OPTION]

刪除

DROP VIEW [IF EXISTS] 視圖名稱[視圖名稱]…

視圖的檢查選項

當使用WITH CHECK OPTION子句創建視圖時，MySQL會通過視圖檢查正在更改的每個行，例如插入，更新，刪除，以使其符合視圖的定 義。

MySQL允許基于另一個視圖創建視圖，它還會檢查依賴視圖中的規則以保持一致性。為了確定檢查的范圍，mysql提供了兩個選項： CASCADED和LOCAL，默認值為CASCADED。 CASCADED :

create or replace view stu_v1 as select sid, sname from student where sid<20;
select * from stu_v1;
insert into stu_v1 values (21,'hi');-- 插入成功
insert into stu_v1 values (19,'ho');-- 插入成功，不會檢查增刪改操作create or replace view stu_v3 as select sid, sname from stu_v1 where sid>5 with cascaded check option ;
select * from stu_v3;
insert into stu_v3 values (21,'hii');-- 插入失敗
insert into stu_v3 values (3,'hoo');-- 插入失敗
insert into stu_v3 values (11,'hio');-- 插入成功create or replace view stu_v5 as select sid, sname from stu_v3 where sid<=15;
insert into stu_v5 values (17,'hii');-- 插入成功
insert into stu_v5 values (21,'hoo');-- 插入失敗
insert into stu_v5 values (5,'hio');-- 插入失敗

?創建視圖時加with cascaded（級聯的） check option的話，會檢查當前視圖還會檢查當前視圖所以依賴的所有視圖它的條件是否滿足；

local

create or replace view stu_v2 as select sid, sname from student where sid<20;
select * from stu_v2;
insert into stu_v2 values (21,'hi');-- 插入成功,不滿足v2
insert into stu_v2 values (19,'ho');-- 插入成功，不會檢查增刪改操作create or replace view stu_v4 as select sid, sname from stu_v2 where sid>5 with local check option ;
select * from stu_v4;
insert into stu_v4 values (21,'hii');-- 插入成功,不滿足v2
insert into stu_v4 values (3,'hoo');-- 插入失敗，不滿足v4
insert into stu_v4 values (11,'hio');-- 插入成功create or replace view stu_v6 as select sid, sname from stu_v4 where sid<=15;
insert into stu_v6 values (17,'hii');-- 插入成功，不滿足v6
insert into stu_v6 values (21,'hoo');-- 插入成功，不滿足v2
insert into stu_v6 values (5,'hio');-- 插入失敗,不滿足v4

?定義視圖時，有檢查條件，就進行檢查，沒有就不進行檢查；遞歸的檢查依賴

視圖的更新

要使視圖可更新，視圖中的行與基礎表中的行之間必須存在一對一的關系。

如果視圖包含以下任何一項，則該視圖不可更新：

1.聚合函數或窗口函數（SUM（）、MIN（）、MAX（）、COUNT（）等）

2. DISTINCT Aae

3. GROUP BY

4. HAVING

5.UNION或者UNION ALL

作用

• 簡單
  • 視圖不僅可以簡化用戶對數據的理解，也可以簡化他們的操作。那些被經常使用的查詢可以被定義為視圖，從而使得用戶不必為以后的操作 每次指定全部的條件。
• 安全
  • 數據庫可以授權，但不能授權到數據庫特定行和特定的列上。通過視圖用戶只能查詢和修改他們所能見到的數據
• 數據獨立
  • 視圖可幫助用戶屏蔽真實表結構變化帶來的影響。

觸發器

介紹

? ? 觸發器是與表有關的數據庫對象，指在insert/update/delete之前或之后，觸發并執行觸發器中定義的SQL語句集合。觸發器的這種特 性可以協助應用在數據庫端確保數據的完整性，日志記錄，數據校驗等操作。

? ? 使用別名OLD和NEW來引用觸發器中發生變化的記錄內容，這與其他的數據庫是相似的。現在觸發器還只支持行級觸發，不支持語句級觸發。

?語法

創建

CREATE TRIGGER trigger_name
BEFORE/AFTER INSERT/UPDATE/DELETE
ON tbl name FOR EACH ROW --行級觸發器
BEGIN
trigger_stmt ;
END;

查看

SHOW TRIGGERS ;

刪除

DROP TRIGGER [schema_name.]trigger_name;-如果沒有指定schema_name,默認為當前數據庫。

-- 準備工作：日志表
create table emoloyee_logs(id int(11) not null auto_increment primary key ,operate varchar(20) not null ,operate_time datetime not null,operate_id int(11) not null ,operate_params varchar(500)
);
-- 插入數據觸發器
create trigger tb_employee_insert_triggerafter insert on employee for each row
begininsert into emoloyee_logs(id,operate,operate_time,operate_id,operate_params) values(null,'insert',now(),new.eno,concat('插入的數為：id',new.eno));
end
-- 查看觸發器
show triggers ;-- 插入數據
insert into employee values (7,'小2','男','項目部',1500);-- 修改數據的觸發器
create trigger tb_employee_update_triggerafter update on employee for each row
begininsert into emoloyee_logs(id,operate,operate_time,operate_id,operate_params) values(null,'update',now(),new.eno,concat('更新之前的數為：name：',old.ename,'| 更新之后的數：name:',new.ename));
end-- 更新數據
update employee set ename='張1' where employee.eno<3;-- 刪除數據的觸發器
create trigger tb_employee_delete_triggerafter delete on employee for each row
begininsert into emoloyee_logs(id,operate,operate_time,operate_id,operate_params) values(null,'delete',now(),old.eno,concat('刪除之前的數為：name：',old.ename));
end-- 刪除數據
delete from employee where eno=6;

案例1：
create trigger sc_update_triggerbefore update on sc for each row
beginif old.grade-new.grade>=10 theninsert into sc_G values(new.sno,new.cno,old.grade,now());end if;
end;
-- ----
show triggers ;
-- 更新
update sc set grade=grade-30 where sno='10060101'&&cno='07294001';
update sc set grade=grade-5 where sno='10060102'&&cno='07294002';
update sc set grade=grade-10 where sno='10060103'&&cno='07294002';
-- ----------------------------------------2
create table avg_sno(sno varchar(20) not null ,avg_sno double comment '平均成績'
);
-- 觸發器
create trigger avg_sno_triggerafter update on sc for each row
begininsert into avg_sno values(NEW.sno,(select avg(new.grade) from sc));
end;-- ------
update sc set grade=grade-20 where sno='10060101'&&cno='07294001';
update sc set grade=grade-5 where sno='10060102'&&cno='07294002';
update sc set grade=grade-10 where sno='10060103'&&cno='07294002';