①.數據庫服務存儲引擎介紹

存儲引擎官方網站資料:https://dev.mysql.com/doc/refman/8.4/en/innodb-storage-engine.html

1.什麼是存儲引擎/存儲引擎有什麼作用

利用存儲引擎可以有效管理磁盤和內存硬件，實現數據庫服務數據調取和數據存儲

1.客户端
包含連接工具信息（命令/工具/程序）

2.服務端
包含連接層：負責實現客户端與服務端建立連接會話（連接線程-show processlist;）
包含服務層：負責處理客户端發送SQL語句請求
	解析器：可以對SQL語句做語義和語法檢查（權限驗證），生成解析數信息（各種處理SQL語句方案）
	優化器：可以對解析樹中的執行方案做出選擇（資源消耗低的方案，作為最優方案選擇）
	執行器：可以根據最優SQL語句方案進行執行，執行後會得到語句的存儲信息

引入數據庫包含的引擎層
包含引擎層：可以有效控制管理磁盤或內存，進行數據存儲調取（CPU-內存-磁盤/內存）
控制管理磁盤或內存完成數據的存儲或調取？

2.存儲引擎的類型

show engines;		#		顯示存儲引擎的類型
mysql> show engines;
+--------------------+---------+------------------------------------------------------
| Engine             | Support | Comment                                              
+--------------------+---------+------------------------------------------------------
| InnoDB             | DEFAULT | Supports transactions, row-level locking, and foreign                   
| MyISAM             | YES     | MyISAM storage engine     
............
............


InnoDB		默認的數據存儲引擎（MySQL5.5之後的）
應用場景：適用於讀多寫多，可以保證數據存儲安全性和一致性，如：遊戲 金融 銀行
應用特點：支持事務（保證數據存儲安全性）
		支持行級鎖（提高併發處理能力）
		支持MVCC機制（可以實現熱備數據，並且備份期間不影響數據庫正常存儲業務）
		支持外鍵功能

MyISAM		早期的數據存儲引擎（MySQL5.5之前的）
應用場景：適用於讀多寫少，數據完整性要求不高的場景，如博客，新聞網站等
應用缺陷：不支持事務（無法保證數據存儲安全性）
		無法支持行級鎖，但可以支持表級鎖（改善數據庫併發處理能力）
     	不支持MVCC多版本併發控制機制（在數據備份時，可以實現不影響業務進行備份數據）
     	不支持外鍵約束功能

3.數據庫存儲引擎的管理操作（瞭解即可）

#	1.查看默認的存儲引擎
mysql> select @@default_storage_engine;
+--------------------------+
| @@default_storage_engine |
+--------------------------+
| InnoDB                   |
+--------------------------+


#	2.修改默認的存儲引擎
永久修改存儲引擎
vim /etc/my.cnf
[mysqld]
default_storage_engine=存儲引擎名稱

全局臨時修改存儲引擎
set global default_storage_engine='myisam';


#	3.針對指定數據表設置存儲引擎
可以對數據庫存儲碎片進行整理（可以提高磁盤應用效率）
數據庫碎片整理參考資料：https://cloud.tencent.com/developer/article/1848499
create table 表名 （...） engine='存儲引擎';
alter table 表名 engine='存儲引擎';

數據異常損壞修復方法

數據異常損壞，修復數據方法： 利用表空間文件修復數據（相同版本數據庫之間 引擎使用innodb）
修復數據常見方法--利用備份數據修復（備份文件 日誌文件 利用表空間文件修復）
步驟一：創建兩個數據庫實例
實例01 模擬早期使用數據庫服務
實例02 模擬數據庫服務故障後，重新建立的數據庫服務

步驟二：在實例01中模擬創建業務數據
mysql -p666666 -S /tmp/mysql.sock <./t100w_oldboy.sql
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| oldboy 

mysql> show tables;
+------------------+
| Tables_in_oldboy |
+------------------+
| t100w            |


步驟三：保存備份數據表結構信息   
mysql> SHOW CREATE TABLE t100w;

CREATE TABLE `t100w` (
  `id` int DEFAULT NULL,
  `num` int DEFAULT NULL,
  `k1` char(2) DEFAULT NULL,
  `k2` char(4) DEFAULT NULL,
  `dt` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

步驟四：模擬數據庫服務故障
mv ibdata1 ibdata11

步驟五：需要進行數據庫數據修復
create database oldboy;
-- 在新的實例02中，創建好oldboy數據庫
CREATE TABLE `t100w` (
  `id` int DEFAULT NULL,
  `num` int DEFAULT NULL,
  `k1` char(2) DEFAULT NULL,
  `k2` char(4) DEFAULT NULL,
  `dt` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
-- 在新的實例02中，創建新的表信息

alter table oldboy.t100w discard tablespace;
-- 將指定表空間文件，從物理層面進行刪除，但數據庫邏輯層面依然存在表信息    

cp -a /data/3306/data/oldboy/t100w.ibd /data/3307/data/oldboy

alter table oldboy.t100w import tablespace;

步驟六：查看驗證數據信息是否恢復成功
mysql> select count(*) from t100w;
+----------+
| count(*) |
+----------+
|  1000000 |
+----------+
1 row in set (0.12 sec)數據異常損壞，修復數據方法： 利用表空間文件修復數據（相同版本數據庫之間 引擎使用innodb）
修復數據常見方法--利用備份數據修復（備份文件 日誌文件 利用表空間文件修復）
步驟一：創建兩個數據庫實例
實例01 模擬早期使用數據庫服務
實例02 模擬數據庫服務故障後，重新建立的數據庫服務

步驟二：在實例01中模擬創建業務數據
mysql -p123456 -S /tmp/mysql80.sock <./t100w_oldboy.sql
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| oldboy 

mysql> show tables;
+------------------+
| Tables_in_oldboy |
+------------------+
| t100w            |


步驟三：保存備份數據表結構信息   
CREATE TABLE `t100w` (
  `id` int DEFAULT NULL,
  `num` int DEFAULT NULL,
  `k1` char(2) DEFAULT NULL,
  `k2` char(4) DEFAULT NULL,
  `dt` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

步驟四：模擬數據庫服務故障
mv ibdata1 ibdata11

步驟五：需要進行數據庫數據修復
create database oldboy;
-- 在新的實例02中，創建好oldboy數據庫
CREATE TABLE `t100w` (
  `id` int DEFAULT NULL,
  `num` int DEFAULT NULL,
  `k1` char(2) DEFAULT NULL,
  `k2` char(4) DEFAULT NULL,
  `dt` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
-- 在新的實例02中，創建新的表信息

alter table oldboy.t100w discard tablespace;
-- 將指定表空間文件，從物理層面進行刪除，但數據庫邏輯層面依然存在表信息    

cp -a /data/3306/data/oldboy/t100w.ibd /data/3307/data/oldboy

alter table oldboy.t100w import tablespace;

步驟六：查看驗證數據信息是否恢復成功
mysql> select count(*) from t100w;
+----------+
| count(*) |
+----------+
|  1000000 |
+----------+
1 row in set (0.12 sec)

②.數據庫服務索引功能應用

1.什麼是數據庫索引功能

數據庫索引功能類似書籍的目錄，可以實現快速檢索數據（可以減少磁盤IO資源消耗）

2.如何設置創建索引信息

方法一：創建設置主鍵索引——PRI

使用場景：針對主鍵約束會創建主鍵索引，可以實現快速查詢整行數據信息

創建索引
create table table_name (....,primary key (column));
ALTER TABLE `table_name` ADD PRIMARY KEY ( `column` );

查看索引
desc 表名  
-- 查看輸出中key信息，確認是否有PRI標識信息即可
show index from stu02\G
-- 可以詳細查看錶中索引信息
需要關注的輸出信息：
Non_unique: 0/1
               0 表示創建的可能是唯一或主鍵索引  
               1 表示創建的是普通或聯合和索引
Key_name:   索引名稱信息，主鍵索引名只能是 PRIMARY 其他索引名稱可以自定義
            區分索引信息應用情況  根據索引名稱可以正確刪除索引
Cardinality: 索引選擇度參考數值，數值越大的列越優選設置為索引信息（列的重複值越少）
Index_type:  索引應用結構方式 利用索引檢索數據算法應用  B+Tree -- 拼音  B-Tree -- 筆畫

刪除索引：
alter table stu01 drop primary key;

方法二：創建設置唯一索引——UNI

創建設置唯一索引	UNI

使用場景：
類似與主鍵索引功能，但不同於主鍵索引，唯一索引列可以存儲空值
利用唯一索引功能，只能調取對應主鍵列信息，無法直接調取所有行數據信息

創建索引：
create table table_name (......,unique index index_name(column));
create unique index 索引名稱 on 表名(列名);
ALTER TABLE 表名 ADD UNIQUE 索引名 (列名);

查看索引：
desc stu05;					#	查看輸出中key信息，確認是否有UNI標識信息
show index from 表名;		   #	可以詳細查看錶中索引信息

刪除索引
alter table 表名 drop index 索引名;
drop index 索引名 on 數據表名;

方法三：創建設置普通索引

使用場景：
當某個列經常作為條件查詢數據時，可以設置為普通索引（列數據可以重複/可以為空）
利用普通索引查詢信息時，只能直接獲取對應主鍵信息，不能直接獲取整行數據信息

創建索引：
create table table_name (......,index index_name(column));
create index 索引名稱 on 表名(列名);
ALTER TABLE 表名 ADD 索引名 (列名);

查看索引：
desc stu05;					#	查看輸出中key信息，確認是否有MUL標識信息
show index from 表名;		   #	可以詳細查看錶中索引信息

刪除索引：
alter table 表名 drop index 索引名;
drop index 索引名 on 數據表名;

方法四：創建設置聯合索引

聯合索引應用：當進行數據查詢時，需要利用多列信息作為條件，查詢所需數據，可以多列組合創建索引
select * from 表名 where 

聯合索引創建和應用原則：最左原則
1）創建索引：將需要創建索引的多列信息中，重複值少的列放在聯合索引創建的最左邊
2）應用索引：在利用聯合索引列查詢數據時，最左列一定要作為條件進行應用
--- 可以提高數據庫數據查詢效率，減少磁盤的IO資源消耗

創建索引：
create table table_name (....,index  index_name(name,gender,age))
create index 索引名稱 on 表名(列名) ;
ALTER TABLE 表名 ADD 索引名 (列名);   

查看索引：
show index from 表名
-- 可以詳細查看錶中索引信息

刪除索引：
alter table 表名 drop index 索引名; 
drop index 索引名 on 數據表名;

③數據庫性能壓力測試

3）數據庫性能壓力測試
mysqlslap --defaults-file=/data/3307/my80.cnf --concurrency=100 --iterations=1 --create-schema='oldboy' --query="select * from oldboy.t100w where k2='VWlm'" engine=innodb --number-of-queries=2000 -uroot -S /tmp/mysql87.sock -verbose


--defaults-file  -- 表示加載配置文件信息
--concurrency    -- 表示模擬併發連接數量
--create-schema  -- 表示壓力測試哪個庫中數據
--query          -- 表示指定查詢數據請求語句
--number-of-queries  -- 表示總的訪問數據庫請求處理的語句次數  100 -- 每個連接向數據庫發送20次SQL查詢請求
--iterations     -- 表示以上壓力測試過程反覆進行幾回

④數據庫索引結構介紹

索引算法結構：
https://www.cs.usfca.edu/~galles/visualization/Algorithms.html
B(Blance)-TREE
主要包含3層結構：
最上層結構：根節點結構 只有有一個數據頁作為根節點
中間層結構：支節點結構（非頁節點結構） 可以有多層 可以包含多個數據頁
最低層結構：葉節點結構 只有一層 可以有多個數據頁
利用結構存儲數據情況：
每個頁節點中，都會存儲索引列數據信息 會存儲索引列之外數據列信息
id  name   age  gender
01  xiaoA  18   男      -- page 01 xiaoA 18 男  page
在根節點數據頁中，還會存儲關聯葉節點支節點數據頁指針信息
在支節點數據頁中，還會存儲關聯葉節點支節點數據頁指針信息
在頁節點數據頁中，沒有指針信息存儲
-- 以上索引結構存在數據查詢缺陷
   1）查詢不同數據信息，消耗IO資源不均衡 
   2）更適合做等值數據查詢，不太適合做範圍數據查詢   
 
B(Blance)+TREE
主要包含3層結構：
最上層結構：根節點結構 只有有一個數據頁作為根節點
中間層結構：支節點結構（非頁節點結構） 可以有多層 可以包含多個數據頁
最低層結構：葉節點結構 只有一層 可以有多個數據頁
利用結構存儲數據情況：
每個頁節點中，都會存儲索引列數據信息 不會所有數據頁都存儲數據列信息
id  name   age  gender
01  xiaoA  18   男      -- page 01 xiaoA 18 男  page
在根節點數據頁中，只會存儲索引列信息，以及關聯下層數據頁節點的指針信息
在支節點數據頁中，只會存儲索引列信息，以及關聯下層數據頁節點的指針信息
在頁節點數據頁中，會存儲索引列信息和對應索引列的數據信息  以及橫向關聯的數據頁指針信息
-- 以上存儲方式的改進，可以保證查詢任何數據信息，消耗IO資源是相同的
-- 利用橫向指針可以提高數據範圍查詢效率
-- 在索引結構中，每個方塊代表一個page（數據頁-4個block=16k 一個數據頁輸入輸出過程稱為1個IO消耗）
-- 有了以上索引算法結構，都是為了在查詢數據信息時，可以有效定位數據，減少不必要的IO資源損耗

索引應用結構
聚簇索引結構：
- 數據表創建時，顯示的構建了主鍵信息（pk），主鍵（pk）就是聚簇索引；
- 數據表創建時，沒有顯示的構建主鍵信息時，會將第一個不為空的UK的列做為聚簇索引；
- 數據表創建時，以上條件都不符合時，生成一個6字節的隱藏列作為聚簇索引；

輔助索引結構：
- 輔助索引主要是：主要用於輔助聚簇索引查詢的索引，一般按照業務查找條件，建立合理的索引信息，也可以稱之為一般索引；
- 輔助索引作用是：主要是將需要查詢的列信息可以和聚合索引信息建立有效的關聯，從而使數據查詢過程更高效，節省IO和CPU消耗
- 輔助索引的存儲：調取需要建立的輔助索引列信息，並加上相應主鍵列的所有信息，存儲在特定的數據頁中；


課程知識總結：
01 數據庫引擎知識介紹
   1）數據庫引擎功能作用  基於數據庫體系結構，利用引擎可以管理內存和磁盤進行數據讀取或存儲
   2）數據庫常用引擎區別  innodb/myisam
   3）數據庫引擎功能設置  設置默認引擎 修改指定表的引擎
   4）實現數據信息修復    獨立表空間遷移修復數據操作
02 數據庫索引知識介紹
   1）數據庫索引功能作用  加快數據存儲或讀取效率 減少磁盤IO消耗
   2）數據庫索引應用方法  主鍵索引/唯一索引/普通索引/聯合索引（創建/查看/刪除）
   3）數據庫壓力測試方法  驗證索引創建是否有效 （瞭解）
   4）數據庫索引結構層次  算法結構（B-TREE、B+TREE） 應用結構（聚簇索引/輔助索引）

愛喝可樂的北極熊博客

愛喝可樂的北極熊博客

博客 / 詳情

數據庫服務存儲引擎

①.數據庫服務存儲引擎介紹

1.什麼是存儲引擎/存儲引擎有什麼作用

2.存儲引擎的類型

3.數據庫存儲引擎的管理操作（瞭解即可）

數據異常損壞修復方法

②.數據庫服務索引功能應用

1.什麼是數據庫索引功能

2.如何設置創建索引信息

方法一：創建設置主鍵索引——PRI

方法二：創建設置唯一索引——UNI

方法三：創建設置普通索引

方法四：創建設置聯合索引

③數據庫性能壓力測試

④數據庫索引結構介紹

發佈評論

Product

Company

Support

Company

博客 / 詳情

數據庫服務存儲引擎

①.數據庫服務存儲引擎介紹

1.什麼是存儲引擎/存儲引擎有什麼作用

2.存儲引擎的類型

3.數據庫存儲引擎的管理操作（瞭解即可）

數據異常損壞修復方法

②.數據庫服務索引功能應用

1.什麼是數據庫索引功能

2.如何設置創建索引信息

方法一：創建設置主鍵索引——PRI

方法二：創建設置唯一索引——UNI

方法三：創建設置普通索引

方法四：創建設置聯合索引

③數據庫性能壓力測試

④數據庫索引結構介紹

發佈 評論

發佈評論