MySQL-DBA課程-Day08

實(shí)施過(guò)程:zabbix庫(kù)替換存儲(chǔ)引擎

1.部署 Mariadb 環(huán)境 10.0.38

[root@db01 mysql]# vim /etc/yum.repos.d/mariadb.repo 
[mariadb]
name = MariaDB
baseurl = http://yum.mariadb.org/10.1/centos7-amd64
gpgkey=https://yum.mariadb.org/RPM-GPG-KEY-MariaDB
gpgcheck=0
enabled=1
  
tar   xf mariadb-10.0.38-rhel-7-x86_64-rpms.tar
  1. 改配置文件
[root@db01 my.cnf.d]# vim /etc/my.cnf.d/tokudb.cnf 

[mariadb]
plugin-load-add=ha_tokudb.so
plugin-dir=/data/tokudb/plugin/
tokudb_commit_sync=ON
tokudb_cache_size=128M
tokudb_directio=ON
tokudb_row_format=tokudb_fast
tokudb_tmp_dir=/data/tokudb/tmp
tokudb_write_status_frequency =1
tokudb_read_status_frequency=1
tokudb_data_dir=/data/tokudb/data
tokudb_log_dir=/data/tokudb/log
~                                                                                                                                   
mkdir -p ~    /data/tokudb/{plugin,tmp,data,log}                                       
chown -R mysql.mysql /data/*
cd /usr/lib64/mysql/plugin/
cp -a * /data/tokudb/plugin/
chown -R mysql.mysql /data/*

  1. 啟動(dòng)數(shù)據(jù)庫(kù)
    [root@db01 data]# mysqld_safe &

  2. 生成批量替換語(yǔ)句

select concat('alter table ',table_schema,'.',table_name,' drop FOREIGN KEY ', CONSTRAINT_NAME,";") from information_schema.TABLE_CONSTRAINTS  where table_schema='zabbix'  and CONSTRAINT_TYPE='FOREIGN KEY';
select concat('alter table ',table_schema,'.'table_name,' engine=tokudb') from information_schema.tables where table_schema='zabbix' into outfile '/tmp/alter.sql';
percona-server+tokudb+zabbix
http://www.itdecent.cn/p/898d2e4bd3a7

上節(jié)回顧:

  1. 支持哪些存儲(chǔ)引擎

  2. 同源產(chǎn)品的存儲(chǔ)引擎: TokuDB ,Myrocks
    小項(xiàng)目: zabbix 監(jiān)控系統(tǒng) mariadb5.5 ----> mariadb 10.0.38 (Perconna 版本都支持)

  3. InnoDB引擎和MyISAM引擎區(qū)別
    MVCC
    CLusterd Index
    Transaction
    Row Level Lock
    FOREIGN KEY
    Hot Backup
    Adaptive HASH Index
    ACSR
    Replication

  4. 表空間遷移
    create table
    alter table discard
    alter table import

  5. 碎片整理
    alter table t1 engine=innodb;

  6. ICP,Insert buffer,AHI,MRR,SNL,BNL,BKA
    ICP : 索引下推
    減少了 Server和engine,engine和磁盤的次數(shù)
    Insert buffer : 插入緩沖
    insert數(shù)據(jù),不會(huì)立即更新到索引樹中,存儲(chǔ)在Insert buffer中.
    index merge 功能在內(nèi)存中合并查詢索引樹. 減少的大批量insert操作時(shí)索引的更新,減少IO和鎖表的時(shí)間.
    AHI : 自適應(yīng)HASH索引
    訪問(wèn)次數(shù)>=3的索引內(nèi)存也,InnoDB會(huì)自動(dòng)生成索引頁(yè)的HASH索引(內(nèi)存).加速索引頁(yè)的訪問(wèn)
    MRR : 輔助索引---回表->聚簇索引, 在回表之前自動(dòng)將主鍵值先排序,一次性回表查詢
    減少回表次數(shù),隨機(jī)IO盡可能轉(zhuǎn)換為順序IO
    SNL,BNL,BKA : Join 的三種經(jīng)典算法
    SNL : 關(guān)聯(lián)表中沒(méi)有索引.我們不建議出現(xiàn)
    BNL : 在驅(qū)動(dòng)表,得到所有數(shù)據(jù),一次性到內(nèi)循環(huán)中進(jìn)行匹配

mysql> SET @@optimizer_switch='mrr=on,mrr_cost_based=off';
mysql> show variables like 'optimizer_switch%' \G
*************************** 1. row ***************************

Variable_name: optimizer_switch
        Value: index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=off,block_nested_loop=on,batched_key_access=on,materialization=on,semijoin=on,loosescan=on,firstmatch=on,subquery_materialization_cost_based=on,use_index_extensions=on
1 row in set (0.00 sec)

===================================
1. 物理存儲(chǔ)結(jié)構(gòu)
1.1 表空間
支持兩類表空間: 共享,獨(dú)立
5.5 版本 : 默認(rèn)共享表空間. 包含: 數(shù)據(jù)字典\undo\tmp\用戶表數(shù)據(jù)和索引
5.6 版本 : 默認(rèn)獨(dú)立表空間. 包含: 數(shù)據(jù)字典\undo\tmp,將用戶數(shù)據(jù)和索引獨(dú)立,每個(gè)表單獨(dú)存儲(chǔ)
5.7 版本 : 默認(rèn)獨(dú)立表空間. 包含: 數(shù)據(jù)字典\undo,tmp獨(dú)立,將用戶數(shù)據(jù)和索引獨(dú)立,每個(gè)表單獨(dú)存儲(chǔ)
8.0 版本 : 默認(rèn)獨(dú)立表空間. 數(shù)據(jù)字典取消掉, undo,tmp獨(dú)立 將用戶數(shù)據(jù)和索引獨(dú)立,每個(gè)表單獨(dú)存儲(chǔ)

1.2 功能名詞介紹
transaction 事務(wù)
undo : ibdata1 回滾日志
tmp : ibtmp1 臨時(shí)表空間
redo : ib_logfile0~N 重做日志
ibd : t1.ibd 表空間數(shù)據(jù)文件

Innodb Buffer Pool 數(shù)據(jù)緩沖區(qū)池(70-80%)
log buffer 重做日志緩沖區(qū)
LSN 日志序列號(hào)
Trx_id 事務(wù)ID
checkpoint 檢查點(diǎn)

1.3 事務(wù) ?
1.3.1 什么是事務(wù)?
將多條DML(標(biāo)準(zhǔn)的事務(wù)語(yǔ)句),放在一個(gè)"組"中運(yùn)行,要么全成功要么全失敗.

  1. 交易?
    以物換物
    貨幣換物
    虛擬幣換物
    虛擬幣虛擬物

1.3.2 事務(wù)ACID特性atomicity, consistency, isolation, and durability.
A : 原子性 : 每一個(gè)事務(wù)都是一個(gè)完整整體,不可再分性 . 要么全執(zhí)行成功要么全失敗.
C : 一致性 : 在事務(wù)前,中,后,保證事務(wù)操作的數(shù)據(jù)前后一致.
I : 隔離性 : 多個(gè)事務(wù)之間,所做事務(wù)互不干擾,不能同時(shí)更新同一行數(shù)據(jù).
D : 持久性 : 事務(wù)完成之后,所涉及到的數(shù)據(jù),必須永久有效(落地)


兼容性.png

1.3.3 事務(wù)的生命周期管理
標(biāo)準(zhǔn)的事務(wù)生命周期:
(1) 開啟一個(gè)事務(wù)
begin / start transaction;
(2) 標(biāo)準(zhǔn)的事務(wù)語(yǔ)句
insert
update
delete
(3) 結(jié)束事務(wù)
commit; # 提交事務(wù)
rollback; # 回滾事務(wù)

非標(biāo)準(zhǔn)的事務(wù)生命周期
(1) 自動(dòng)提交機(jī)制
MySQL 5.6 以后:

  1. begin子句會(huì)自動(dòng)添加
  2. 每一條執(zhí)行完成之后都會(huì)自動(dòng)提交
mysql> select @@autocommit;
+--------------+
| @@autocommit |
+--------------+
|            1 |
+--------------+

[root@db01 data_3306]# vim /etc/my.cnf 
autocommit=0
[root@db01 data_3306]# /etc/init.d/mysqld restart

說(shuō)明: 默認(rèn)情況下,開啟事務(wù)時(shí)不加begin,逐條自動(dòng)提交. 手工開啟begin命令,按照正常事務(wù)工作過(guò)程.

(2) 隱式提交
用于隱式提交的 SQL 語(yǔ)句：
begin
a
b
begin
SET AUTOCOMMIT = 1
導(dǎo)致提交的非事務(wù)語(yǔ)句：
DDL語(yǔ)句： （ALTER、CREATE 和 DROP）
DCL語(yǔ)句： （GRANT、REVOKE 和 SET PASSWORD）
鎖定語(yǔ)句：（LOCK TABLES 和 UNLOCK TABLES）
導(dǎo)致隱式提交的語(yǔ)句示例：
TRUNCATE TABLE
LOAD DATA INFILE
SELECT FOR UPDATE

(3) 隱式回滾
會(huì)話斷開
數(shù)據(jù)庫(kù)重啟
死鎖

1.3.4 事務(wù)底層的工作過(guò)程
畫圖說(shuō)明.
(1) redo
分布:
內(nèi)存: log buffer
磁盤: ib_logfile0~N
功能:
1. 保存內(nèi)存數(shù)據(jù)頁(yè)的變化
2. commit時(shí), 實(shí)現(xiàn)事務(wù)的快速持久化的特性: 量少,順序IO
3. 宕機(jī)時(shí),通過(guò)redo實(shí)現(xiàn)重做事務(wù),將數(shù)據(jù)庫(kù)恢復(fù)到宕機(jī)之前的狀態(tài).
我們由把這步稱之為 ACSR 中的"前滾"操作

(2) undo 回滾日志
分布: 默認(rèn) ibdata1, 5.7開始可以獨(dú)立undo,8.0后自動(dòng)獨(dú)立

功能 :
1. 保存當(dāng)前事務(wù)操作的反操作
2. 在執(zhí)行rollback命令時(shí),undo提供回滾操作,在ACID中主要實(shí)現(xiàn)A的特性,CI也有部分功能
3. 宕機(jī)時(shí),ACSR過(guò)程中提供回滾操作(將沒(méi)有commit標(biāo)記的)

1.3.5 鎖(寫) 及 隔離級(jí)別(讀)主要保證隔離性
(1) 鎖 :
S : 共享鎖,讀鎖
X : 排它鎖,寫鎖
IS : 意向S
IX : 意向X

(2) X 鎖的細(xì)分
TX ------> 表鎖 DDL
全局鎖表:
備份時(shí),備份系統(tǒng)表時(shí)(非INOODB表),FTWRL
mysql> flush table with read lock;
mysql> unlock tables;

單表: DDL
mysql> lock table t1 read ;
mysql> unlock tables;

RX ------> 記錄鎖 DML
GAP LOCK X ------> 間隙鎖 特殊DML
Next LOCK X ------> 下一鍵鎖定

(3) 隔離級(jí)別(transaction_isolation)
mysql> select @@transaction_isolation;
RU :讀未提交 READ-UNCOMMITTED

模擬: 
session  A  
mysql> begin;
mysql> use world;
mysql> delete from city where id=1000;

session  B 
mysql> begin;
mysql> use world;
mysql> select *from city where id=1000;

會(huì)產(chǎn)生的問(wèn)題:
1. 臟讀
2. 不可重復(fù)讀現(xiàn)象
3. 幻讀

RC :讀已提交★★★★★ READ-COMMITTED
1. 不可重復(fù)讀現(xiàn)象
2. 幻讀

RR :可重復(fù)讀★★★★★ REPEATABLE-READ
1. 幻讀
說(shuō)明:

  1. RR級(jí)別+ GAP+ Next lock(GAP+RX)有效防止幻讀現(xiàn)象
  2. 通過(guò)MVCC,多版本并發(fā)控制中,一致性快照讀技術(shù),解決了不可重復(fù)讀問(wèn)題.
    SR :串行化

總結(jié):
AID 都是為了數(shù)據(jù)庫(kù)最終一致性 C
SQL_MODE
約束

自己擴(kuò)展:
MDL 原數(shù)據(jù)鎖
page lock 頁(yè)鎖
latch 內(nèi)存頁(yè)鎖

  1. InnoDB 存儲(chǔ)引擎核心參數(shù) ★★★★★
mysql> select @@innodb_data_file_path;
mysql> select @@innodb_file_per_table;
mysql> select @@innodb_buffer_pool_size;  #不要超過(guò)80%物理內(nèi)存 ----> Out of memory
mysql> select @@innodb_log_buffer_size;
mysql> select @@innodb_log_file_size;
mysql> select @@innodb_log_file_in_group;
mysql> select @@innodb_flush_log_at_trx_commit;#

雙1標(biāo)準(zhǔn)之一.控制redo刷寫的策略.
0 每秒鐘刷寫redo到磁盤.
1 每次事務(wù)提交,理解刷寫redo到磁盤
2 每次事務(wù)提交,立即寫日志到OS cache中,然后每秒鐘刷寫到磁盤.

mysql> select @@innodb_flush_method; 控制(buffer臟頁(yè),redo buffer日志)刷寫方式
建議設(shè)置:
O_DIRECT : 數(shù)據(jù)頁(yè)刷寫磁盤直接穿過(guò)文件系統(tǒng)緩存,redo 刷寫時(shí),先寫os cache,再寫到磁盤。
==================================

  1. 日志
  2. 備份恢復(fù)
  3. 主從復(fù)制
  4. 高可用
  5. 分布式
  6. 優(yōu)化
  7. NoSQL
最后編輯于 ：
?著作權(quán)歸作者所有,轉(zhuǎn)載或內(nèi)容合作請(qǐng)聯(lián)系作者
【社區(qū)內(nèi)容提示】社區(qū)部分內(nèi)容疑似由AI輔助生成，瀏覽時(shí)請(qǐng)結(jié)合常識(shí)與多方信息審慎甄別。
平臺(tái)聲明：文章內(nèi)容（如有圖片或視頻亦包括在內(nèi)）由作者上傳并發(fā)布，文章內(nèi)容僅代表作者本人觀點(diǎn)，簡(jiǎn)書系信息發(fā)布平臺(tái)，僅提供信息存儲(chǔ)服務(wù)。

友情鏈接更多精彩內(nèi)容