【MySQL進階】Mysql的體系結構概覽及存儲引擎以及索引的使用

1. Mysql的體系結構概覽

整個MySQL Server由以下組成

Connection Pool : 連接配接池元件
Management Services & Utilities : 管理服務和工具元件
SQL Interface : SQL接口元件
Parser : 查詢分析器元件
Optimizer : 優化器元件
Caches & Buffers : 緩沖池元件
Pluggable Storage Engines : 存儲引擎
File System : 檔案系統

1）連接配接層

最上層是一些用戶端和連結服務，包含本地sock 通信和大多數基于用戶端/服務端工具實作的類似于 TCP/IP的通信。主要完成一些類似于連接配接處理、授權認證、及相關的安全方案。在該層上引入了線程池的概念，為通過認證安全接入的用戶端提供線程。同樣在該層上可以實作基于SSL的安全連結。伺服器也會為安全接入的每個用戶端驗證它所具有的操作權限。

2）服務層

第二層架構主要完成大多數的核心服務功能，如SQL接口，并完成緩存的查詢，SQL的分析和優化，部分内置函數的執行。所有跨存儲引擎的功能也在這一層實作，如過程、函數等。在該層，伺服器會解析查詢并建立相應的内部解析樹，并對其完成相應的優化如确定表的查詢的順序，是否利用索引等，最後生成相應的執行操作。如果是select語句，伺服器還會查詢内部的緩存，如果緩存空間足夠大，這樣在解決大量讀操作的環境中能夠很好的提升系統的性能。

3）引擎層

存儲引擎層，存儲引擎真正的負責了MySQL中資料的存儲和提取，伺服器通過API和存儲引擎進行通信。不同的存儲引擎具有不同的功能，這樣我們可以根據自己的需要，來選取合适的存儲引擎。

4）存儲層

資料存儲層，主要是将資料存儲在檔案系統之上，并完成與存儲引擎的互動。

和其他資料庫相比，MySQL有點與衆不同，它的架構可以在多種不同場景中應用并發揮良好作用。主要展現在存儲引擎上，插件式的存儲引擎架構，将查詢處理和其他的系統任務以及資料的存儲提取分離。這種架構可以根據業務的需求和實際需要選擇合适的存儲引擎。

2. 存儲引擎

2.1 存儲引擎概述

和大多數的資料庫不同, MySQL中有一個存儲引擎的概念, 針對不同的存儲需求可以選擇最優的存儲引擎。

存儲引擎就是存儲資料，建立索引，更新查詢資料等等技術的實作方式。存儲引擎是基于表的，而不是基于庫的。是以存儲引擎也可被稱為表類型。

Oracle，SqlServer等資料庫隻有一種存儲引擎。MySQL提供了插件式的存儲引擎架構。是以MySQL存在多種存儲引擎，可以根據需要使用相應引擎，或者編寫存儲引擎。

MySQL5.0支援的存儲引擎包含： InnoDB 、MyISAM 、BDB、MEMORY、MERGE、EXAMPLE、NDB Cluster、ARCHIVE、CSV、BLACKHOLE、FEDERATED等，其中InnoDB和BDB提供事務安全表，其他存儲引擎是非事務安全表。

可以通過指定 show engines ，來查詢目前資料庫支援的存儲引擎：

建立新表時如果不指定存儲引擎，那麼系統就會使用預設的存儲引擎，MySQL5.5之前的預設存儲引擎是MyISAM，5.5之後就改為了InnoDB。

檢視Mysql資料庫預設的存儲引擎，指令：

show variables like '%storage_engine%' ；

2.2 各種存儲引擎特性

下面重點介紹幾種常用的存儲引擎，并對比各個存儲引擎之間的差別，如下表所示：

特點	InnoDB	MyISAM	MEMORY	MERGE	NDB
存儲限制	64TB	有	有	沒有	有
事務安全	==支援==
鎖機制	==行鎖(适合高并發)==	==表鎖==	表鎖	表鎖	行鎖
B樹索引	支援	支援	支援	支援	支援
哈希索引	支援
全文索引	支援(5.6版本之後)	支援
叢集索引	支援
資料索引	支援	支援	支援
索引緩存	支援	支援	支援	支援	支援
資料可壓縮	支援
空間使用	高	低	N/A	低	低
記憶體使用	高	低	中等	低	高
批量插入速度	低	高	高	高	高
支援外鍵	==支援==

下面我們将重點介紹最長使用的兩種存儲引擎： InnoDB、MyISAM ，另外兩種 MEMORY、MERGE ，了解即可。

2.2.1 InnoDB

InnoDB存儲引擎是Mysql的預設存儲引擎。InnoDB存儲引擎提供了具有送出、復原、崩潰恢複能力的事務安全。但是對比MyISAM的存儲引擎，InnoDB寫的處理效率差一些，并且會占用更多的磁盤空間以保留資料和索引。

InnoDB存儲引擎不同于其他存儲引擎的特點：

事務控制

create table goods_innodb(

id int NOT NULL AUTO_INCREMENT,

name varchar(20) NOT NULL,

primary key(id)

)ENGINE=innodb DEFAULT CHARSET=utf8;

start transaction;

insert into goods_innodb(id,name)values(null,'Meta20');

commit;

測試，發現在InnoDB中是存在事務的；

外鍵限制

MySQL支援外鍵的存儲引擎隻有InnoDB ，在建立外鍵的時候，要求父表必須有對應的索引，子表在建立外鍵的時候，也會自動的建立對應的索引。

下面兩張表中， country_innodb是父表， country_id為主鍵索引，city_innodb表是子表，country_id字段為外鍵，對應于country_innodb表的主鍵country_id 。

create table country_innodb(

country_id int NOT NULL AUTO_INCREMENT,

country_name varchar(100) NOT NULL,

primary key(country_id)

)ENGINE=InnoDB DEFAULT CHARSET=utf8;

create table city_innodb(

city_id int NOT NULL AUTO_INCREMENT,

city_name varchar(50) NOT NULL,

country_id int NOT NULL,

primary key(city_id),

key idx_fk_country_id(country_id),

CONSTRAINT `fk_city_country` FOREIGN KEY(country_id) REFERENCES country_innodb(country_id) ON DELETE RESTRICT ON UPDATE CASCADE

)ENGINE=InnoDB DEFAULT CHARSET=utf8;

insert into country_innodb values(null,'China'),(null,'America'),(null,'Japan');

insert into city_innodb values(null,'Xian',1),(null,'NewYork',2),(null,'BeiJing',1);

在建立索引時，可以指定在删除、更新父表時，對子表進行的相應操作，包括 RESTRICT、CASCADE、SET NULL 和 NO ACTION。

RESTRICT和NO ACTION相同，是指限制在子表有關聯記錄的情況下，父表不能更新；

CASCADE表示父表在更新或者删除時，更新或者删除子表對應的記錄；

SET NULL 則表示父表在更新或者删除的時候，子表的對應字段被SET NULL 。

針對上面建立的兩個表，子表的外鍵指定是ON DELETE RESTRICT ON UPDATE CASCADE 方式的，那麼在主表删除記錄的時候，如果子表有對應記錄，則不允許删除，主表在更新記錄的時候，如果子表有對應記錄，則子表對應更新。

表中資料如下圖所示：

外鍵資訊可以使用如下兩種方式檢視：

show create table city_innodb ;

删除country_id為1 的country資料：

delete from country_innodb where country_id = 1;

更新主表country表的字段 country_id :

update country_innodb set country_id = 100 where country_id = 1;

更新後，子表的資料資訊為：

存儲方式

InnoDB 存儲表和索引有以下兩種方式：

①. 使用共享表空間存儲，這種方式建立的表的表結構儲存在.frm檔案中，資料和索引儲存在 innodb_data_home_dir 和 innodb_data_file_path定義的表空間中，可以是多個檔案。

②. 使用多表空間存儲，這種方式建立的表的表結構仍然存在 .frm 檔案中，但是每個表的資料和索引單獨儲存在 .ibd 中。

2.2.2 MyISAM

MyISAM 不支援事務、也不支援外鍵，其優勢是通路的速度快，對事務的完整性沒有要求或者以SELECT、INSERT為主的應用基本上都可以使用這個引擎來建立表。有以下兩個比較重要的特點：

不支援事務

create table goods_myisam(

id int NOT NULL AUTO_INCREMENT,

name varchar(20) NOT NULL,

primary key(id)

)ENGINE=myisam DEFAULT CHARSET=utf8;

通過測試，我們發現，在MyISAM存儲引擎中，是沒有事務控制的；

檔案存儲方式

每個MyISAM在磁盤上存儲成3個檔案，其檔案名都和表名相同，但拓展名分别是：

.frm (存儲表定義)；

.MYD(MYData , 存儲資料)；

.MYI(MYIndex , 存儲索引)；

2.2.3 MEMORY

Memory存儲引擎将表的資料存放在記憶體中。每個MEMORY表實際對應一個磁盤檔案，格式是.frm ，該檔案中隻存儲表的結構，而其資料檔案，都是存儲在記憶體中，這樣有利于資料的快速處理，提高整個表的效率。MEMORY 類型的表通路非常地快，因為他的資料是存放在記憶體中的，并且預設使用HASH索引，但是服務一旦關閉，表中的資料就會丢失。

2.2.4 MERGE

MERGE存儲引擎是一組MyISAM表的組合，這些MyISAM表必須結構完全相同，MERGE表本身并沒有存儲資料，對MERGE類型的表可以進行查詢、更新、删除操作，這些操作實際上是對内部的MyISAM表進行的。

對于MERGE類型表的插入操作，是通過INSERT_METHOD子句定義插入的表，可以有3個不同的值，使用FIRST 或 LAST 值使得插入操作被相應地作用在第一或者最後一個表上，不定義這個子句或者定義為NO，表示不能對這個MERGE表執行插入操作。

可以對MERGE表進行DROP操作，但是這個操作隻是删除MERGE表的定義，對内部的表是沒有任何影響的。

下面是一個建立和使用MERGE表的示例：

1）. 建立3個測試表 order_1990, order_1991, order_all , 其中order_all是前兩個表的MERGE表：

create table order_1990(

order_id int ,

order_money double(10,2),

order_address varchar(50),

primary key (order_id)

)engine = myisam default charset=utf8;

create table order_1991(

order_id int ,

order_money double(10,2),

order_address varchar(50),

primary key (order_id)

)engine = myisam default charset=utf8;

create table order_all(

order_id int ,

order_money double(10,2),

order_address varchar(50),

primary key (order_id)

)engine = merge union = (order_1990,order_1991) INSERT_METHOD=LAST default charset=utf8;

2）. 分别向兩張表中插入記錄

insert into order_1990 values(1,100.0,'北京');

insert into order_1990 values(2,100.0,'上海');

insert into order_1991 values(10,200.0,'北京');

insert into order_1991 values(11,200.0,'上海');

3）. 查詢3張表中的資料。

order_1990中的資料：

order_1991中的資料：

order_all中的資料：

4）. 往order_all中插入一條記錄，由于在MERGE表定義時，INSERT_METHOD 選擇的是LAST，那麼插入的資料會想最後一張表中插入。

insert into order_all values(100,10000.0,'西安')；

2.3 存儲引擎的選擇

在選擇存儲引擎時，應該根據應用系統的特點選擇合适的存儲引擎。對于複雜的應用系統，還可以根據實際情況選擇多種存儲引擎進行組合。以下是幾種常用的存儲引擎的使用環境。

InnoDB : 是Mysql的預設存儲引擎，用于事務處理應用程式，支援外鍵。如果應用對事務的完整性有比較高的要求，在并發條件下要求資料的一緻性，資料操作除了插入和查詢意外，還包含很多的更新、删除操作，那麼InnoDB存儲引擎是比較合适的選擇。InnoDB存儲引擎除了有效的降低由于删除和更新導緻的鎖定，還可以確定事務的完整送出和復原，對于類似于計費系統或者财務系統等對資料準确性要求比較高的系統，InnoDB是最合适的選擇。
MyISAM ：如果應用是以讀操作和插入操作為主，隻有很少的更新和删除操作，并且對事務的完整性、并發性要求不是很高，那麼選擇這個存儲引擎是非常合适的。
MEMORY：将所有資料儲存在RAM中，在需要快速定位記錄和其他類似資料環境下，可以提供幾塊的通路。MEMORY的缺陷就是對表的大小有限制，太大的表無法緩存在記憶體中，其次是要確定表的資料可以恢複，資料庫異常終止後表中的資料是可以恢複的。MEMORY表通常用于更新不太頻繁的小表，用以快速得到通路結果。
MERGE：用于将一系列等同的MyISAM表以邏輯方式組合在一起，并作為一個對象引用他們。MERGE表的優點在于可以突破對單個MyISAM表的大小限制，并且通過将不同的表分布在多個磁盤上，可以有效的改善MERGE表的通路效率。這對于存儲諸如資料倉儲等VLDB環境十分合适。

4. 索引的使用

索引是資料庫優化最常用也是最重要的手段之一, 通過索引通常可以幫助使用者解決大多數的MySQL的性能優化問題。

4.1 驗證索引提升查詢效率

在我們準備的表結構tb_item 中，一共存儲了 300 萬記錄；

A. 根據ID查詢

select * from tb_item where id = 1999;

查詢速度很快，接近0s ，主要的原因是因為id為主鍵，有索引；

2). 根據 title 進行精确查詢

select * from tb_item where title = 'iphoneX 移動3G 32G941';

檢視SQL語句的執行計劃：

處理方案，針對title字段，建立索引：

create index idx_item_title on tb_item(title);

索引建立完成之後，再次進行查詢：

通過explain ，檢視執行計劃，執行SQL時使用了剛才建立的索引

4.2 索引的使用

4.2.1 準備環境

create table `tb_seller` (
    `sellerid` varchar (100),
    `name` varchar (100),
    `nickname` varchar (50),
    `password` varchar (60),
    `status` varchar (1),
    `address` varchar (100),
    `createtime` datetime,
    primary key(`sellerid`)
)engine=innodb default charset=utf8mb4; 

insert into `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`) values('alibaba','阿裡巴巴','阿裡小店','e10adc3949ba59abbe56e057f20f883e','1','北京市','2088-01-01 12:00:00');
insert into `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`) values('baidu','百度科技有限公司','百度小店','e10adc3949ba59abbe56e057f20f883e','1','北京市','2088-01-01 12:00:00');
insert into `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`) values('huawei','華為科技有限公司','華為小店','e10adc3949ba59abbe56e057f20f883e','0','北京市','2088-01-01 12:00:00');
insert into `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`) values('itcast','傳智播客教育科技有限公司','傳智播客','e10adc3949ba59abbe56e057f20f883e','1','北京市','2088-01-01 12:00:00');
insert into `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`) values('itheima','黑馬程式員','黑馬程式員','e10adc3949ba59abbe56e057f20f883e','0','北京市','2088-01-01 12:00:00');
insert into `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`) values('luoji','羅技科技有限公司','羅技小店','e10adc3949ba59abbe56e057f20f883e','1','北京市','2088-01-01 12:00:00');
insert into `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`) values('oppo','OPPO科技有限公司','OPPO官方旗艦店','e10adc3949ba59abbe56e057f20f883e','0','北京市','2088-01-01 12:00:00');
insert into `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`) values('ourpalm','掌趣科技股份有限公司','掌趣小店','e10adc3949ba59abbe56e057f20f883e','1','北京市','2088-01-01 12:00:00');
insert into `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`) values('qiandu','千度科技','千度小店','e10adc3949ba59abbe56e057f20f883e','2','北京市','2088-01-01 12:00:00');
insert into `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`) values('sina','新浪科技有限公司','新浪官方旗艦店','e10adc3949ba59abbe56e057f20f883e','1','北京市','2088-01-01 12:00:00');
insert into `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`) values('xiaomi','小米科技','小米官方旗艦店','e10adc3949ba59abbe56e057f20f883e','1','西安市','2088-01-01 12:00:00');
insert into `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`) values('yijia','宜家家居','宜家家居旗艦店','e10adc3949ba59abbe56e057f20f883e','1','北京市','2088-01-01 12:00:00');


create index idx_seller_name_sta_addr on tb_seller(name,status,address);

4.2.2 避免索引失效

1). 全值比對，對索引中所有列都指定具體值。

該情況下，索引生效，執行效率高。

explain select * from tb_seller where name='小米科技' and status='1' and address='北京市';

2). 最左字首法則

如果索引了多列，要遵守最左字首法則。指的是查詢從索引的最左前列開始，并且不跳過索引中的列。

比對最左字首法則，走索引：

違法最左字首法則，索引失效：

如果符合最左法則，但是出現跳躍某一列，隻有最左列索引生效：

3). 範圍查詢右邊的列，不能使用索引。

根據前面的兩個字段name ， status 查詢是走索引的，但是最後一個條件address 沒有用到索引。

4). 不要在索引列上進行運算操作，索引将失效。

5). 字元串不加單引号，造成索引失效。

由于，在查詢是，沒有對字元串加單引号，MySQL的查詢優化器，會自動的進行類型轉換，造成索引失效。

6). 盡量使用覆寫索引，避免select *

盡量使用覆寫索引（隻通路索引的查詢（索引列完全包含查詢列）），減少select * 。

如果查詢列，超出索引列，也會降低性能。

TIP : 
    
    using index ：使用覆寫索引的時候就會出現

    using where：在查找使用索引的情況下，需要回表去查詢所需的資料

    using index condition：查找使用了索引，但是需要回表查詢資料

    using index ; using where：查找使用了索引，但是需要的資料都在索引列中能找到，是以不需要回表查詢資料

7). 用or分割開的條件，如果or前的條件中的列有索引，而後面的列中沒有索引，那麼涉及的索引都不會被用到。

示例，name字段是索引列，而createtime不是索引列，中間是or進行連接配接是不走索引的：

explain select * from tb_seller where name='黑馬程式員' or createtime = '2088-01-01 12:00:00';

8). 以%開頭的Like模糊查詢，索引失效。

如果僅僅是尾部模糊比對，索引不會失效。如果是頭部模糊比對，索引失效。

解決方案：

通過覆寫索引來解決（走索引全表掃描）

9). 如果MySQL評估使用索引比全表更慢，則不使用索引。

10). is NULL ， is NOT NULL 有時索引失效。

11). in 走索引， not in 索引失效。

補：in與not in,exists與not exists的差別以及性能分析

1、in和exists

in是把外表和内表作hash連接配接，而exists是對外表作loop循環，每次loop循環再對内表進行查詢，一直以來認為exists比in效率高的說法是不準确的。如果查詢的兩個表大小相當，那麼用in和exists差别不大；如果兩個表中一個較小一個較大，則子查詢表大的用exists，子查詢表小的用in；

例如：表A(小表)，表B(大表)

select * from A where cc in(select cc from B)　　-->效率低，用到了A表上cc列的索引；select * from A where exists(select cc from B where cc=A.cc)　　-->效率高，用到了B表上cc列的索引。

相反的：

select * from B where cc in(select cc from A)　　-->效率高，用到了B表上cc列的索引select * from B where exists(select cc from A where cc=B.cc)　　-->效率低，用到了A表上cc列的索引。

not in 和not exists

not in 邏輯上不完全等同于not exists，如果你誤用了not in，小心你的程式存在緻命的BUG，請看下面的例子：

create table t1(c1 int,c2 int);

create table t2(c1 int,c2 int);

insert into t1 values(1,2);

insert into t1 values(1,3);

insert into t2 values(1,2);

insert into t2 values(1,null);

select * from t1 where c2 not in(select c2 from t2);　　-->執行結果：無

select * from t1 where not exists(select 1 from t2 where t2.c2=t1.c2)　　-->執行結果：1　　3

1、對于not exists查詢，内表存在空值對查詢結果沒有影響；對于not in查詢，内表存在空值将導緻最終的查詢結果為空。

2、對于not exists查詢，外表存在空值，存在空值的那條記錄最終會輸出；對于not in查詢，外表存在空值，存在空值的那條記錄最終将被過濾，其他資料不受影響。

對于in 和 exists的性能差別: 如果子查詢得出的結果集記錄較少，主查詢中的表較大且又有索引時應該用in,反之如果外層的主查詢記錄較少，子查詢中的表大，又有索引時使用exists。

其實我們區分in和exists主要是造成了驅動順序的改變（這是性能變化的關鍵），如果是exists，那麼以外層表為驅動表，先被通路，如果是IN，那麼先執行子查詢，是以我們會以驅動表的快速傳回為目标，那麼就會考慮到索引及結果集的關系了

12). 單列索引和複合索引。

盡量使用複合索引，而少使用單列索引。

建立複合索引

create table t1(c1 int,c2 int);

create table t2(c1 int,c2 int);

insert into t1 values(1,2);

insert into t1 values(1,3);

insert into t2 values(1,2);

insert into t2 values(1,null);

select * from t1 where c2 not in(select c2 from t2);　　-->執行結果：無

select * from t1 where not exists(select 1 from t2 where t2.c2=t1.c2)　　-->執行結果：1　　3

建立單列索引

create index idx_seller_name on tb_seller(name);
create index idx_seller_status on tb_seller(status);
create index idx_seller_address on tb_seller(address);

show status like 'Handler_read%'; 

show global status like 'Handler_read%';

Handler_read_first：索引中第一條被讀的次數。如果較高，表示伺服器正執行大量全索引掃描（這個值越低越好）。

Handler_read_key：如果索引正在工作，這個值代表一個行被索引值讀的次數，如果值越低，表示索引得到的性能改善不高，因為索引不經常使用（這個值越高越好）。

Handler_read_next ：按照鍵順序讀下一行的請求數。如果你用範圍限制或如果執行索引掃描來查詢索引列，該值增加。

Handler_read_prev：按照鍵順序讀前一行的請求數。該讀方法主要用于優化ORDER BY ... DESC。

Handler_read_rnd ：根據固定位置讀一行的請求數。如果你正執行大量查詢并需要對結果進行排序該值較高。你可能使用了大量需要MySQL掃描整個表的查詢或你的連接配接沒有正确使用鍵。這個值較高，意味着運作效率低，應該建立索引來補救。

Handler_read_rnd_next：在資料檔案中讀下一行的請求數。如果你正進行大量的表掃描，該值較高。通常說明你的表索引不正确或寫入的查詢沒有利用索引。

【MySQL進階】Mysql的體系結構概覽及存儲引擎以及索引的使用

1. Mysql的體系結構概覽

2. 存儲引擎

4. 索引的使用

繼續閱讀

Java String.format方法的簡單使用

neo4j之cypher使用文檔

GitHub連夜封殺！這份阿裡 10W 字内部 Java 字面試手冊到底有多強？

spark/scala關于【資源檔案】加載方法概述外部檔案加載方案測試資源檔案打包入jar包中小結

NOSQL安全攻擊

mybatis_入門程式Mybatis入門

mysql使用source指令導入.sql檔案

AOP程式設計_Android優雅權限架構(1)概念基礎，2021金三銀四前言正文大綱正文

登入plsql 報錯 the account is locked --使用者被鎖

Effective Java 8:通用程式設計

SequoiaDB巨杉資料庫C++驅動概述

OOM三種類型

工廠模式-三種類型

【遞歸】高效率求2的n次幂

win10本地scala和spark安裝安裝scala安裝spark

scala (3) Function 和 Method