1.主要內(nèi)容

1)mysql的架構(gòu),鎖,最佳實踐,事務(wù)

2)mysql的命令行,主從復(fù)制,索引,數(shù)據(jù)備份/恢復(fù)

3)mysql的集群,mysql的負(fù)載均衡,監(jiān)控,mysql proxy

4) mysql的MMM架構(gòu),mysql 的源碼,DBA

MySQL-從刪除到跑路

2.具體內(nèi)容

2.1)【架構(gòu)】：

【架構(gòu)圖】：

MySQL-MySQL的架構(gòu)圖

【概述】：Mysql是由SQL接口,解析器,優(yōu)化器,緩存,存儲引擎組成的

2.2)【鎖】：

【概念】：為了保證數(shù)據(jù)的一致完整性,任何一個數(shù)據(jù)庫都存在鎖定機制.鎖定機制的優(yōu)劣直接應(yīng)想到一個數(shù)據(jù)庫系統(tǒng)的并發(fā)處理能力和性能,所以鎖定機制的實現(xiàn)也就成為了各種數(shù)據(jù)庫的核心技術(shù)之一.

【分類】：MySQL各存儲引擎使用了三種類型(級別)的鎖定機制：行級鎖定,頁級鎖定和表級鎖定

1)行級鎖定：鎖定對象的顆粒度很小,消耗最大,行級鎖定也最容易發(fā)生死鎖,但是并發(fā)好

2)表級鎖定：死鎖情況少,但是并發(fā)不好,

3)頁級鎖定：介于前兩者之間.

【代碼】：

1)鎖定 LOCK TABLES tbl_name [AS alias]

2)解鎖 UNLOCK TABLES

2.3)【最佳實踐】：

【為查詢緩存優(yōu)化你的查詢】：

【mysql強制索引和禁止某個索引】：

1) mysql強制使用索引:force index(索引名或者主鍵PRI)：select * from table force index(PRI,ziduan1_index) limit 2;(強制使用索引”PRI和ziduan1_index”)

2) mysql禁止某個索引：ignore index(索引名或者主鍵PRI)：select * from table ignore index(PRI,ziduan1_index) limit 2;(禁止使用索引”PRI,ziduan1_index”)

2.4)【事務(wù)】：

【概念】：事務(wù)是指邏輯上的一組操作,組成這組操作的各個單元,要不全成功要不全失敗.其特點是ACID(原子性,一致性,隔離性,持久性)

【實現(xiàn)】：

1)事務(wù)開啟：START TRANSACTION; 或者 BEGIN;

2)事務(wù)提交：COMMIT;

3)事務(wù)回滾：ROLLBACK;

2.5)【mysql 命令行】：

【命令行執(zhí)行mysql】:使用mysql的-e參數(shù)可以執(zhí)行各種sql的(創(chuàng)建,刪除,增,刪,改、查)等各種操作

例如：#創(chuàng)建數(shù)據(jù)庫

create_db_sql="create database IF NOT EXISTS ${DBNAME}”

mysql -h${HOSTNAME} -P${PORT} -u${USERNAME} -p${PASSWORD} -e "${create_db_sql}"

2.6)【主從復(fù)制】：

【一主多從】：在一主多從的數(shù)據(jù)庫體系中,多個從服務(wù)器采用異步的方式更新主數(shù)據(jù)庫的變化,業(yè)務(wù)服務(wù)器在執(zhí)行寫或者相關(guān)修改數(shù)據(jù)庫的操作是在主服務(wù)器上進(jìn)行的,讀操作則是在各從服務(wù)器上進(jìn)行

MySQL-MySQL的主從復(fù)制

【原理】 ：MySQL之間數(shù)據(jù)復(fù)制的基礎(chǔ)是二進(jìn)制日志文件(binary log file).一臺MySQL數(shù)據(jù)庫一旦啟用二進(jìn)制日志后,其作為master,它的數(shù)據(jù)庫中所有操作都會以“事件”的方式記錄在二進(jìn)制日志中,其他數(shù)據(jù)庫作為slave通過一個I/O線程與主服務(wù)器保持通信,并監(jiān)控master的二進(jìn)制日志文件的變化,如果發(fā)現(xiàn)master二進(jìn)制日志文件發(fā)生變化,則會把變化復(fù)制到自己的中繼日志中,然后slave的一個SQL線程會把相關(guān)的“事件”執(zhí)行到自己的數(shù)據(jù)庫中,以此實現(xiàn)從數(shù)據(jù)庫和主數(shù)據(jù)庫的一致性,也就實現(xiàn)了主從復(fù)制.

2.7)【索引】：

【概述】：索引對查詢的速度有著至關(guān)重要的影響,理解索引也是進(jìn)行數(shù)據(jù)庫性能調(diào)優(yōu)的起點

【種類】：普通索引,唯一索引,全文索引,單列/多列索引,組合索引(最左前綴)

【B+樹】：

MySQL-索引B+樹結(jié)構(gòu)

1)詳解B+樹：淺藍(lán)色的塊我們稱之為一個磁盤塊,可以看到每個磁盤塊包含幾個數(shù)據(jù)項(深藍(lán)色所示)和指針(黃色所示),如磁盤塊1包含數(shù)據(jù)項17和35,包含指針P1、P2、P3,P1表示小于17的磁盤塊,P2表示在17和35之間的磁盤塊,P3表示大于35的磁盤塊.真實的數(shù)據(jù)存在于葉子節(jié)點即3、5、9、10、13、15、28、29、36、60、75、79、90、99.非葉子節(jié)點只不存儲真實的數(shù)據(jù),只存儲指引搜索方向的數(shù)據(jù)項,如17、35并不真實存在于數(shù)據(jù)表中.

2)查找B+樹：如果要查找數(shù)據(jù)項29,那么首先會把磁盤塊1由磁盤加載到內(nèi)存,此時發(fā)生一次IO,在內(nèi)存中用二分查找確定29在17和35之間,鎖定磁盤塊1的P2指針,內(nèi)存時間因為非常短(相比磁盤的IO)可以忽略不計,通過磁盤塊1的P2指針的磁盤地址把磁盤塊3由磁盤加載到內(nèi)存,發(fā)生第二次IO,29在26和30之間,鎖定磁盤塊3的P2指針,通過指針加載磁盤塊8到內(nèi)存,發(fā)生第三次IO,同時內(nèi)存中做二分查找找到29,結(jié)束查詢,總計三次IO.真實的情況是,3層的b+樹可以表示上百萬的數(shù)據(jù),如果上百萬的數(shù)據(jù)查找只需要三次IO,性能提高將是巨大的,如果沒有索引,每個數(shù)據(jù)項都要發(fā)生一次IO,那么總共需要百萬次的IO,顯然成本非常非常高.

2.8)【數(shù)據(jù)備份/恢復(fù)】：

【數(shù)據(jù)備份】：

1)使用mysqldump命令備份mysqldump -u username -p --databases dbname2 dbname2 > Backup.sql

2)直接復(fù)制整個數(shù)據(jù)庫目錄 ： 需要停止服務(wù),不支持InnoDB引擎

3)熱備份：使用mysqlhotcopy工具快速備份,不用停止服務(wù). mysqlhotcopy [option] dbname1 dbname2 backupDir/

【數(shù)據(jù)還原】：

1)還原使用mysqldump命令備份的數(shù)據(jù)庫： mysql -u root -p < C:\backup.sql

2)還原直接復(fù)制目錄的備份： 必須保證兩個MySQL數(shù)據(jù)庫的版本號是相同的.MyISAM類型的表有效,對于InnoDB類型的表不可用

2.9)【mysql的集群】：

【概念】：MySQL 集群(MySQL Cluster )是mysql適合于分布式計算環(huán)境的高實用、高冗余版本.它采用了NDB Cluster 存儲引擎,允許在1個 Cluster 中運行多個MySQL服務(wù)器.在MyQL 5.0及以上的二進(jìn)制版本中、以及與最新的Linux版本兼容的RPM中提供了該存儲引擎.

MySQL-MySQL集群架構(gòu)圖

【分類】：分為同步集群和異步集群.

1)同步集群：(mysql cluster)

結(jié)構(gòu)：(data + sql + mgm節(jié)點)

特點：

1. 1) 內(nèi)存級別的,對硬件要求較低,但是對內(nèi)存要求較大.換算比例為：1：1.1；

1.2) 數(shù)據(jù)同時放在幾臺服務(wù)器上,冗余較好；

1.3) 速度一般；

1.4) 建表需要聲明為engine=ndbcluster

1.5) 擴展性強；

1.6) 可以實現(xiàn)高可用性和負(fù)載均衡,實現(xiàn)對大型應(yīng)用的支持；

1.7) 必須是特定的mysql版本,如：已經(jīng)編譯好的max版本；

1.8) 配置和管理方便,不會丟失數(shù)據(jù)

2)異步集群(mysql replication)

結(jié)構(gòu)：(master + slave)

特點：

2.1) 主從數(shù)據(jù)庫異步數(shù)據(jù)；

2.2) 數(shù)據(jù)放在幾臺服務(wù)器上,冗余一般；

2.3) 速度較快；

2.4) 擴展性差；

2.5) 無法實現(xiàn)高可用性和負(fù)載均衡(只能在程序級別實現(xiàn)讀寫分離,減輕對主數(shù)據(jù)庫的壓力)；

2.6) 配置和管理較差,可能會丟失數(shù)據(jù)

2.10)【mysql的負(fù)載均衡】：

【定義】： 通過director,將用戶的請求分發(fā)到real server服務(wù)器上,然后返回給用戶.負(fù)載均衡部署靈活、能夠滿足各種需求.

MySQL-LVS+Keepalived實現(xiàn)mysql負(fù)載均衡

【實現(xiàn)方式】：

硬件：BIG/IP、Cisco、IBM(昂貴)

軟件：LVS(免費)

LVS系統(tǒng)將用戶的請求的數(shù)據(jù)包在數(shù)據(jù)層和網(wǎng)絡(luò)層進(jìn)行了封裝和轉(zhuǎn)發(fā),由三種方式滿足各種需求.

1) DR：直接路由

2) Tuning：tcp/ip隧道

3) NAT：網(wǎng)絡(luò)地址轉(zhuǎn)換

2.11)【mysql的監(jiān)控】：

【監(jiān)控內(nèi)容】：監(jiān)控數(shù)據(jù)庫的日常會話情況.碎片、剩余表空間監(jiān)控,及時了解表空間的擴展情況、以及剩余空間分布情況

【代碼實現(xiàn)】：https://github.com/wozhuzaisi/shell-work/blob/master/mysqlmonitor.sh

2.12)【mysql proxy】：

【定義】：MySQL Proxy是一個處于你的client端和MySQL server端之間的簡單程序,它可以監(jiān)測、分析或改變它們的通信.它使用靈活,沒有限制,常見的用途包括：負(fù)載平衡,故障、查詢分析,查詢過濾和修改等等

【實現(xiàn)讀寫分離】：

1)安裝并配置mysql proxy.

2)測試：主服務(wù)器mysql> grant all on *.* to'lin3615'@'192.168.179.142'identified by'123456’;

2.14)【mysql MMM架構(gòu)】：

【概述】：是一套支持雙主故障切換和雙主日常管理的腳本程序.由于MMM無法完全的保證數(shù)據(jù)一致性,所以MMM適用于對數(shù)據(jù)的一致性要求不是很高,但是又想最大程度的保證業(yè)務(wù)可用性的場景.對于那些對數(shù)據(jù)的一致性要求很高的業(yè)務(wù),非常不建議采用MMM這種高可用架構(gòu).

MySQL-MMM架構(gòu)圖

2.15)【mysql 源碼】：

【內(nèi)容】：MySQL是由C++寫的,入口函數(shù)main()直接調(diào)用了mysqld_main().Server啟動的代碼都在這里,直到最后的 handle_connections_sockets().

MySQL-MySQL的源碼第一書

【資料】：

下載pdf:《深入MySQL源碼》

官網(wǎng)手冊: https://dev.mysql.com/doc/internals/en/

閱讀源碼：https://zhuanlan.zhihu.com/p/26118810

書籍：《深入理解MySQL核心技術(shù)》

2.16)【DBA】：

【職責(zé)】：

MySQL-DBA思維導(dǎo)圖

1)數(shù)據(jù)庫安裝；

2)數(shù)據(jù)庫配置和管理；

3)權(quán)限設(shè)置和安全管理；

4)監(jiān)控和性能調(diào)節(jié)；

5)備份和恢復(fù)；

6)解決一般的問題；

MySQL-DBA的一天