MySQL-体系结构以及常用存储引擎MyISAM和InnoDB初探
文章目錄
- 生猛干貨
- 官方文檔
- MySQL體系結(jié)構(gòu)
- 常用存儲引擎之MyISAM
- MyISAM的組成
- MyISAM的特征
- MyISAM的限制
- MyISAM的適用場景
- 常用存儲引擎之Innodb
- Innodb的特征
- innodb 狀態(tài)檢查
- 適用場景
- 多種存儲引擎小結(jié)
- 搞定MySQL
生猛干貨
帶你搞定MySQL實戰(zhàn)，輕松對應(yīng)海量業(yè)務(wù)處理及高并發(fā)需求，從容應(yīng)對大場面試
官方文檔
https://dev.mysql.com/doc/
如果英文不好的話，可以參考 searchdoc 翻譯的中文版本
http://www.searchdoc.cn/rdbms/mysql/dev.mysql.com/doc/refman/5.7/en/index.com.coder114.cn.html
MySQL體系結(jié)構(gòu)
一個SQL的執(zhí)行過程：
客戶端/服務(wù)端通信;
查詢緩存;
查詢優(yōu)化處理;
調(diào)用查詢存儲引擎;
返回客戶端結(jié)果集;
簡單了解Mysql 體系結(jié)構(gòu)，MYSQL是如何執(zhí)行一條SQL的
MySQL區(qū)別于其他數(shù)據(jù)庫最主要的特點是： 插件式存儲引擎 。
比較常見的存儲引擎： innodb 、myisam、XtraDB、CSV、Memory、MRG_MYISAM、archive 、federated 、tokudb等
存儲引擎是針對表的而不是針對庫的，功能上支持 不同的表使用不同的存儲引擎，但非常不建議這么做。
常用存儲引擎之MyISAM
MySQL5.5之前默認的 存儲引擎。
MyISAM的組成
MYD （數(shù)據(jù)文件） + MYI（索引文件）
創(chuàng)建一個表t_myisam, 存儲引擎使用 MyISAM ( engine=myisam)
[root@artisan ~]# mysql -u root -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 37 Server version: 5.7.29-log MySQL Community Server (GPL)…
mysql> mysql> use artisan; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -ADatabase changed mysql> create table t_myisam(id int,c1 varchar(10)) engine=myisam; # 指定存儲引擎為 myisam Query OK, 0 rows affected (0.00 sec) mysql>看下磁盤上存儲
MYD和MYI是MyISAM存儲引擎特有的文件。
另外還有個frm結(jié)尾的文件，并不是MyISAM存儲引擎特有的，對于MySQL數(shù)據(jù)庫，是通用的，用來記錄表結(jié)構(gòu)等信息的。
MyISAM的特征
-
表級鎖 ，并發(fā)性比較差
-
表損壞修復(fù) ，可以通過 check table tablename 來檢查， 使用 repair table tablename 來修復(fù) . 另外mysql還提供了命令行來修復(fù)，但最好需要停庫。
mysql> check table t_myisam;+------------------+-------+----------+----------+| Table | Op | Msg_type | Msg_text |+------------------+-------+----------+----------+| artisan.t_myisam | check | status | OK |+------------------+-------+----------+----------+1 row in set (0.32 sec)mysql> repair table t_myisam;+------------------+--------+----------+----------+| Table | Op | Msg_type | Msg_text |+------------------+--------+----------+----------+| artisan.t_myisam | repair | status | OK |+------------------+--------+----------+----------+1 row in set (0.04 sec -
支持的索引： 全文索引 、text blob 前綴索引
-
支持數(shù)據(jù)壓縮 命令行： myisampack
-
不支持事務(wù)
[root@artisan artisan]# myisampack -b -f t_myisam.MYI
MyISAM的限制
- 版本<MySQL5.0 默認表大小為4G，如果要存儲大表則需要修改Max_Rows 和 Avg_Row_Length
- 版本> MySQL5.0 默認支持為256TB
MyISAM的適用場景
- 非事務(wù)型應(yīng)用
- 只讀類應(yīng)用
- 空間類應(yīng)用。 5.7以前 MyISAM是唯一支持空間函數(shù)的存儲引擎
常用存儲引擎之Innodb
MySQL5.5之后默認的 存儲引擎。
Innodb的特征
-
使用表空間儲存數(shù)據(jù)
mysql> show variables like 'innodb_file_per_table'; +-----------------------+-------+ | Variable_name | Value | +-----------------------+-------+ | innodb_file_per_table | ON | +-----------------------+-------+ 1 row in set (0.00 sec)mysql>
innodb_file_per_table參數(shù)決定 ，ON ：存儲在獨立的表空間 tablename.ibd ; OFF :系統(tǒng)表空間 ibdataX (X代表數(shù)字)如果關(guān)閉呢，看下如何存儲的
mysql> set global innodb_file_per_table = off; Query OK, 0 rows affected (0.00 sec)mysql> show variables like 'innodb_file_per_table'; +-----------------------+-------+ | Variable_name | Value | +-----------------------+-------+ | innodb_file_per_table | OFF | +-----------------------+-------+ 1 row in set (0.01 sec)mysql>
那該如何選擇呢？
1） 系統(tǒng)表空間無法簡單的收縮，獨立的表空間可以通過optimize table明林收縮系統(tǒng)文件
2）系統(tǒng)表空間，多個表順序?qū)懭?#xff0c;會產(chǎn)生I/O瓶頸。獨立表空間可以同時向多個文件刷新數(shù)據(jù)對Innodb使用獨立表空間，并且5.6版本以后的MYSQL默認使用獨立表空間
如果原來存在系統(tǒng)表空間中，要遷移到獨立表空間呢？
-
支持事務(wù) （依靠Redo Log【負責持久性】 和 Undo Log 【負責回滾】）
Redo Log 存儲已提交的數(shù)據(jù)
UndoLog 存儲未提交的數(shù)據(jù)Redo log的大小設(shè)置
mysql> show variables like 'innodb_log_buffer_size'; +------------------------+----------+ | Variable_name | Value | +------------------------+----------+ | innodb_log_buffer_size | 16777216 | +------------------------+----------+ 1 row in set (0.00 sec)mysql>這個參數(shù)不用太大，1秒提交一次 。
另外一個參數(shù)
看到參數(shù)為2 ， 所以 有2個文件
-
行級鎖 ，可以支持最大程度的并發(fā)。 行級鎖由存儲引擎實現(xiàn)的。
1）鎖主要用來管理共享資源的并發(fā)訪問。
2）鎖用于實現(xiàn)事務(wù)的隔離性鎖的類型
- 共享鎖 （也稱為讀鎖）
- 獨占鎖 （也稱為寫鎖）
鎖的粒度
- 表級鎖
來演示下
會話一：
mysql> use artisan; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -ADatabase changedmysql> show create table t_innodb; # 查看建表語句 +----------+--------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +----------+--------------------------------------------------------------------------------------------------------------------------------+ | t_innodb | CREATE TABLE `t_innodb` (`id` int(11) DEFAULT NULL,`c1` varchar(10) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 | +----------+--------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec)mysql> insert into t_innodb values(1,'aa'),(2,'bb'); # 寫入2條數(shù)據(jù) Query OK, 2 rows affected (0.01 sec) Records: 2 Duplicates: 0 Warnings: 0mysql> select * from t_innodb; +------+------+ | id | c1 | +------+------+ | 1 | aa | | 2 | bb | +------+------+ 2 rows in set (0.00 sec)mysql> lock table t_innodb write; # 加表級鎖 Query OK, 0 rows affected (0.00 sec)mysql>會話二
緊接著，這個Select 馬上就出來結(jié)果了
-
行級鎖 ， 存儲引擎實現(xiàn)的，并不是MySQL實現(xiàn)的
innodb 狀態(tài)檢查
命令： show engine innodb status;
mysql> pager more; # 設(shè)置多頁展示 PAGER set to 'more' mysql> show engine innodb status\G ; # \G 格式化查看 *************************** 1. row ***************************Type: InnoDBName: Status: ===================================== 2020-01-30 15:26:27 0x7fbe901e8700 INNODB MONITOR OUTPUT ===================================== Per second averages calculated from the last 6 seconds ----------------- BACKGROUND THREAD ----------------- srv_master_thread loops: 32 srv_active, 0 srv_shutdown, 166260 srv_idle srv_master_thread log flush and writes: 166292 ---------- SEMAPHORES ---------- OS WAIT ARRAY INFO: reservation count 40 OS WAIT ARRAY INFO: signal count 40 RW-shared spins 0, rounds 83, OS waits 39 RW-excl spins 0, rounds 0, OS waits 0 RW-sx spins 0, rounds 0, OS waits 0 Spin rounds per wait: 83.00 RW-shared, 0.00 RW-excl, 0.00 RW-sx ------------ TRANSACTIONS ------------ Trx id counter 109372 Purge done for trx's n:o < 109372 undo n:o < 0 state: running but idle History list length 15 LIST OF TRANSACTIONS FOR EACH SESSION: ---TRANSACTION 421931820292832, not started 0 lock struct(s), heap size 1136, 0 row lock(s) ---TRANSACTION 421931820291920, not started 0 lock struct(s), heap size 1136, 0 row lock(s) -------- FILE I/O -------- I/O thread 0 state: waiting for completed aio requests (insert buffer thread) I/O thread 1 state: waiting for completed aio requests (log thread) --More--適用場景
- 適用大多數(shù)OLTP應(yīng)用
- 5.7以后 支持全文索引和 空間函數(shù)
多種存儲引擎小結(jié)
搞定MySQL
總結(jié)
以上是生活随笔為你收集整理的MySQL-体系结构以及常用存储引擎MyISAM和InnoDB初探的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。
- 上一篇： Linux-Raid0、Raid1、Ra
- 下一篇： MySQL-DB参数、内存、I/O、安全