溫馨提示×
您好，登錄后才能下訂單哦！
點擊 登錄注冊 即表示同意《億速云用戶服務條款》
您好，登錄后才能下訂單哦！
體系結構分為兩大層：
l mysql server層：包括連接層，SQL層；
l 存儲引擎層；
query cache只能緩存靜態數據。適用于數據倉庫。
5.6前默認開啟，5.6后默認關閉。
徹底關閉query cache
涉及query_cache的兩個核心參數
mysql>show variables like “%query_cache_size%”
mysql>show variables like “$query_cache_type%”
必須一開始就將query_cache_type設置未off
備注：
壓力測試工具sysbench是一個開源的、模塊化、跨平臺的多線程性能測試工具，可進行CPU、內存、磁盤IO、線程、數據庫的性能測試。，支持mysql,oracle,postgresql
下載地址：https://dev.mysql.com/downloads/benchmarks.html
具體測試過程見書 p44
存儲引擎MyISAM建議停用，轉換到InnoDB存儲引擎上。
mysql數據庫是單進程多線程模型的數據庫
l 實例是進程加內存的組合。
l 線程：將內存數據刷新到硬盤上。
l 磁盤文件：redolog，數據文件，Undo log
InnoDB邏輯存儲單元主要分為表空間，段，區，頁。
層級關系：tablespace>segment>extent(64個page,1M)>page
表空間
系統表空間；ibdata1
共享表空間 獨立表空間更好
獨立表空間：每表有自己表空間，可實現表空間轉移，回收方便。alter table tablename engine=innodb 或pt-online_schema_change即可
共享表空間不能在線回收空間，需導出刪除再導入，統計分析、日志類不適合。
mysql 5.7后多了臨時表空間，通用表空間
段
數據段，回滾段，索引段。
每個段由N個區和32個零散的頁組成。
創建一個索引會創建兩個段，分別為非葉子節點和葉子節點段。
一個表有4個段，是索引個數的兩倍。
區
連續的頁組成，物理上連續分配的一段空間，每個區大小固定1M。
頁
InnoDB的最小物理存儲分配單位是page，有數據頁、回滾頁，一般情況一個區由64個連續的頁組成，頁默認16KB。
mysql5.6 可調低為8KB或4KB
mysql5.7 可調高為32KB或64KB
行
頁記錄行記錄的信息
InnoDB存儲引擎面向列，也就是數據按照行存儲，行記錄數據按照行格式進行存放。
InnoDB存儲引擎有兩種文件格式
1. Antelope：有corrpact和redundant兩種行記錄格式
2. Barracuda：有commpressed和dynamic兩種行記錄格式。
行溢出： 需要存儲的數據再當前存儲頁面之外，拆分到多個頁進行存儲。
redundant:最早的行記錄格式，比compact要消耗更多的存儲空間，不建議使用。
commpressed:壓縮行格式，對物理存儲層面上的數據庫和索引頁進行壓縮，內存不壓縮，調用到內存需轉換，消耗CPU,壓縮率只有1/2，不建議使用。
compact:溢出的列只存放768各前綴字節。
dynamic:新版本默認的行記錄格式，溢出數據存放溢出頁中，數據頁只存指針。溢出列所在的新頁利用率高，推薦使用。
mysql5.7 默認使用dynamic行記錄格式和Barracuda文件格式；
通過查看參數row_format可查看
show table status like ‘%user%’\G;
row_format
查看文件格式
show variables like ‘%innodb_file%’;
innodb_file_format
內存分為SGA(系統全局區)和PGA(程序緩存區)，
查看配置參數：show variables like ‘%buffer%’
參數介紹，見p54
SGA（系統全局區）
innodb_buffer_pool:緩存InnoDB表的數據、索引、插入緩沖、數據字典等信息；
innodb_log_buffer:事務在內存中的緩沖，即redo log buffer的大小；
Query Cache:高速查詢緩存，生產環境建議關閉。只能緩存靜態數據。適用于數據倉庫。
key_buffer_size:只用于MyISAM存儲引擎表，緩存MyISAM存儲。
innodb_additional_mem_pool_size:保存數據字典信息和其他內部數據結構的內存池大小，在5.7.4中被移除。
SGA（程序緩沖區）
sort_buffer_size:用于SQL語句在內存中的臨時排序。
join_buffer_size:表連接使用，用于BKA
read_buffer_size:表順序掃描的緩存，只能用于MyISAM存儲引擎。
read_rnd_buffer_size:隨機讀緩沖區大小，用于做mrr。
特別的
tmp_table_size:SQL語句在排序或者分組時沒有用到索引，就使用臨時表空間。
max_heap_table_size:管理heap、memory存儲引擎表。
查詢參數：
[mysql]>show variables like'%heap%';
[mysql]>show variables like'%tmp_table%'
兩個參數以最小為準，建議設置一樣大。
default_tmp_storage_engine:臨時表默認的存儲引擎；
interal_tmp_disk_storage_engine:磁盤臨時表的管理，決定（create temporary table）,5.7新增。
page是InnoDB磁盤的IO最小單位，對應到內存中是一個個buffer，每個buffer分為3種狀態：
l free buffer:：未被使用。
l clean buffer：buffer數據和磁盤page數據一致。
l dirty buffer:內存數據還未寫入到磁盤。
三種不同的buffer衍生三條鏈表
master thread線程 ：后臺主線程，優先級最高。其內部有4個循環：
l 主循環loop，
l 后臺循環background loop，
l 刷新循環flush loop，
l 暫停循環suspend loop。
根據數據庫運行狀態再4個循環間切換。在loop主循環中又包含兩種操作，分為：
四大IO線程
l read thread: 數據庫讀寫請求線程，默認4個，可擴大。
l write thread：
l redo log thread：日志緩沖區中內容刷新到redo log中；
l change buffer thread：把插入緩沖區內內容刷新到磁盤。
page cleaner thread 負責臟頁刷新的線程。show variables like ‘%innodb_page%’
purge thread: 負責刪除無用undo頁，由于進行DML語句的操作都會生成UNDO，系統需要定期對undo頁進行清理，就需要purge操作。
checkpoint： 在redo log切換時，執行checkpoint，redo log發生切換會觸發把臟頁刷新到磁盤。
error monitor thread: 負責數據庫報錯的監控線程；
lock monitor thread 負責鎖的監控線程。
三個內存部分刷新
redo log buffer ,data buffer ,binlog cache
redo log
重做日志文件，記錄事務操作的變化，記錄數據修改之后得值，不管事務是否提交都會記錄下來。至少2個redo log，磁盤用ib_logfile(0-N)命名；影響redo log buffer刷新到磁盤得條件
a.通過innodb_flush_log_at_trx_commit控制，分別為0，1，2；
0:redo log thread每隔1s 觸發redo log buffer和數據寫磁盤，但事務提交不啟動刷新。
1：每次事務提交，觸發redo log buffer和數據寫到磁盤。
2：每次事務提交，觸發redo log buffer寫，但不刷新磁盤。
b.mater thread：每秒刷新
c.redo log buffer，使用超過1半觸發刷新。
binlog
DML語句既寫redo log，也寫binlog文件。
binlog叫mysql的二進制日志文件，功能用于備份恢復和主從復制。sync_binlog參數決定刷新條件
0 ：事務提交后，不立即將binlog_cache信息刷新到磁盤，而讓filesystem自行決定同步。
n:：每n次事務提交后，binlog_cache刷新到磁盤。
確保數據庫安全的雙一模式：即sync_binlog=1，innodb_flush_log_at_trx_commit=1
redo log 和binlog的區別
第一：記錄內容不同
l binlog是邏輯日志，記錄所有數據的改變信息。
l redo log是物理日志，記錄所有InnoDB表數據的變化。
第二：記錄內容的時間不同
l binlog記錄commit完畢之后的DML和DDL SQL語句。
l redo log 記錄事務發起之后的DML和DDL SQL語句。
第三：文件使用方式的不同
l binlog不是循環使用，在寫滿或實例重啟之后，會生成新文件；
l relo log是循環使用，最后一個寫滿再寫第一個。
第四：作用不同
l binlog可以作為恢復數據使用，主從復制搭建。
l redo log作為異常關機或介質故障后數據恢復使用。
MySQL兩階段提交過程 ：
兩階段提交分為prepare和commit階段。
l 準備階段（transaction prepare）：事務SQL先寫入到redo log buffer，然后做一個事務準備標志，再將log buffer中數據刷新到redo log。
l 提交階段（commit）:將事務產生的binlog寫入文件，刷入磁盤。
l 再在redo log中做一個事務提交標志，把binlog寫成功的標記一并寫入redo log文件。
總結：只要binlog寫入完成，在主從復制環境中，都會正常完成事務。
臟頁的刷新機制
l redo log切換時，執行checkpoint，會觸發臟頁的刷新。
l 通過innodb_max_dirty_pages_pct參數控制，表示buffer pool中dirty page的百分百。達到閾值啟動刷新。默認75%，建議設置25%-50%，為避免后期影響性能。
l 由innodb_adaptive_flushing控制每秒刷新臟頁的數目，智能控制，默認開啟。
三大特性為：插入緩沖（change buffer）,兩次寫（double write），自適應哈希索引（adaptive hash index）
插入緩沖：
將普通索引上的DML操作從隨機IO變成順序IO，提高IO效率。
原理：1.先判斷插入的普通索引頁是否在緩沖池中，如在就直接插入；如不在就先放到change buffer中，然后進行change buffer和普通索引的合并操作，多個插入合并到一個操作中，提高普通索引插入性能。
涉及參數：
[mysql]>show variables like'%change%';
+-------------------------------+-------+
| Variable_name | Value |
+-------------------------------+-------+
| innodb_change_buffer_max_size | 25 |
| innodb_change_buffering | all |
| session_track_state_change | OFF |
+-------------------------------+-------+
innodb_change_buffer_max_size:占innodb_buffer_pool的最大比例，默認25%，建議50.
innodb_change_buffering:change buffer的類型，有如下幾種。
l all:緩沖全部insert,delete標記操作和purge操作, 建議選擇默認的all。
l none:關閉insert buffer;
l inserts:insert 標記操作
l delete:delete標記操作
l changes:未進行實際insert和delete，只標記，等待后續purge;
l purges:緩沖后臺進程的purges(物理刪除)操作.
兩次寫（dourble write）
插入緩沖帶來針對普通索引插入性能上的提升，而double write就是保證吸入的安全性，防止系統宕機。InnoDB發生數據頁部分寫（partial page write）問題，redo log文件記錄的是頁的物理記錄，如果頁損壞就無法進行恢復，就用binlog恢復原來的頁，再通過redo log恢復。
雙寫緩沖位于系統表空間中的存儲區域，吸入順序如下：
l InnoDB緩沖池中臟頁被寫入數據文件之前，讀先寫入double write buffer。
l 然后分兩次從double write buffer,每次將1MB大小數據寫入磁盤共享表空間（double write）.
l 最后再從double write buffer寫入數據文件。
雙寫緩沖不占用兩倍IO，double 是一個大型的連續快，會通過一次fsync()通知操作系統。
自使用哈希索引
InnoDB可以監控索引的搜索，如注意到查詢可通過建立哈希索引得到優化，就會自動完成。通過innodb_adaptive_hash_index參數控制。
從5.7.8開始，自適應哈希索引搜索系統是分區的，每個索引綁定到一個特殊的分區上面，各個分區都有自己的鎖存器來進行維護。分區通過innodb_adaptive_hash_index_parts參數控制，默認值8各，最大512，通過分區設置，可降低爭用，提高并發。
通過show engine innodb status命令輸出的semaphores部分監控自使用哈希索引的使用及其競爭情況。如看到許多線程等待一個再btr0sea.c中創建的RW-latch,則它可能被用于禁用自適應哈希索引。
免責聲明：本站發布的內容（圖片、視頻和文字）以原創、轉載和分享為主，文章觀點不代表本網站立場，如果涉及侵權請聯系站長郵箱：is@yisu.com進行舉報，并提供相關證據，一經查實，將立刻刪除涉嫌侵權內容。
