又是新的一年奮斗路的開啟，相信有不少人農(nóng)歷新年之后讼育，肯定會有所變動（跳槽加薪少不了）帐姻。所以，我把往期推送過的MySQL技術(shù)文章做了一個相關(guān)的整理奶段，基礎(chǔ)不好的可以從最基礎(chǔ)的學(xué)習(xí)一遍饥瓷，提高的也可以從中再提取深入一下。

碼字不易痹籍，如有幫助呢铆，請隨手?點在看?與?轉(zhuǎn)發(fā)朋友圈?支持一下民工哥，關(guān)注我词裤，一起學(xué)習(xí)更多的IT技術(shù)知識刺洒，共同進(jìn)步。

數(shù)據(jù)庫是什么

數(shù)據(jù)庫管理系統(tǒng)吼砂，簡稱為DBMS（Database Management System），是用來存儲數(shù)據(jù)的管理系統(tǒng)鼎文。

DBMS 的重要性

無法多人共享數(shù)據(jù)

無法提供操作大量數(shù)據(jù)所需的格式

實現(xiàn)讀取自動化需要編程技術(shù)能力

無法應(yīng)對突發(fā)事故

DBMS 的種類

層次性數(shù)據(jù)庫

最古老的數(shù)據(jù)庫之一渔肩，因為突出的缺點，所以很少使用了

關(guān)系型數(shù)據(jù)庫

采用行列二維表結(jié)構(gòu)來管理數(shù)據(jù)庫拇惋，類似Excel的結(jié)構(gòu)周偎，使用專用的SQL語言對數(shù)據(jù)進(jìn)行控制抹剩。

關(guān)系數(shù)據(jù)庫管理系統(tǒng)的常見種類

Oracle ==> 甲骨文

SQL Servce ==> 微軟

DB2 ==> IBM

PostgreSQL ==> 開源

MySQL ==> 開源

面向?qū)ο蟮臄?shù)據(jù)庫

XML數(shù)據(jù)庫

鍵值存儲系統(tǒng)

DB2

Redis

MongoDB

SQL 語句及其種類

DDL（數(shù)據(jù)定義語言）

create ==> 創(chuàng)建數(shù)據(jù)庫或者表等對象

drop ==> 刪除數(shù)據(jù)庫或者表等對象

alter ==> 修改數(shù)據(jù)庫或者表等對象的結(jié)構(gòu)

DML（數(shù)據(jù)操作語言）

select ==> 查詢表中數(shù)據(jù)

insert ==> 向表中插入數(shù)據(jù)

update ==> 更新表中數(shù)據(jù)

delete ==> 刪除表中數(shù)據(jù)

DCL（數(shù)據(jù)控制語言）

commit ==> 決定對數(shù)據(jù)庫中的數(shù)據(jù)進(jìn)行變更

rollback ==> 取消對數(shù)據(jù)庫中的數(shù)據(jù)進(jìn)行變更

grant ==> 賦予用戶操作權(quán)限

revoke ==> 取消用戶的操作權(quán)限

SQL 的基本書寫規(guī)則

SQL 語句要以；結(jié)尾

關(guān)鍵字不區(qū)分大小寫蓉坎，但是表中數(shù)據(jù)區(qū)分大小寫

關(guān)鍵字大寫

表名的首字母大寫

列明等小寫

常數(shù)的書寫方式是固定的

遇到字符串澳眷、日期等類型需要用到''

單詞間需要使用空格分割

命名規(guī)則

數(shù)據(jù)庫和表的名稱可以使用英文、數(shù)據(jù)以及下劃線

名稱必須以英文作為開頭

名稱不能重復(fù)

掌握 SQL 這些核心知識點蛉艾，出去吹牛逼再也不擔(dān)心了

數(shù)據(jù)類型

integer

數(shù)字型钳踊，但是不能存放小數(shù)

char

定長字符串類型，指定最大長度勿侯，不足使用空格填充

varchar

可變長度字符串類型拓瞪，指定最大長度，但是不足不填充

data

存儲日期助琐，年/月/日

以上內(nèi)容是對通用數(shù)據(jù)庫以及sql語句相關(guān)的知識點介紹祭埂，本文不做過多的贅述，本文主要針對關(guān)系型數(shù)據(jù)庫：MySQL 來進(jìn)行各方面的知識點總結(jié)兵钮。

MySQL 數(shù)據(jù)庫簡介

MySQL 是最流行的關(guān)系型數(shù)據(jù)庫管理系統(tǒng)蛆橡，在 WEB 應(yīng)用方面 MySQL 是最好的 RDBMS(Relational Database Management System：關(guān)系數(shù)據(jù)庫管理系統(tǒng))應(yīng)用軟件之一。

MySQL 是一個關(guān)系型數(shù)據(jù)庫管理系統(tǒng)掘譬，由瑞典 MySQL AB 公司開發(fā)泰演，目前屬于 Oracle 公司。MySQL 是一種關(guān)聯(lián)數(shù)據(jù)庫管理系統(tǒng)屁药，關(guān)聯(lián)數(shù)據(jù)庫將數(shù)據(jù)保存在不同的表中粥血，而不是將所有數(shù)據(jù)放在一個大倉庫內(nèi)，這樣就增加了速度并提高了靈活性酿箭。

MySQL 是開源的复亏，目前隸屬于 Oracle 旗下產(chǎn)品。

MySQL 支持大型的數(shù)據(jù)庫缭嫡〉抻可以處理擁有上千萬條記錄的大型數(shù)據(jù)庫。

MySQL 使用標(biāo)準(zhǔn)的 SQL 數(shù)據(jù)語言形式妇蛀。

MySQL 可以運行于多個系統(tǒng)上耕突，并且支持多種語言。這些編程語言包括 C评架、C++眷茁、Python、Java纵诞、Perl上祈、PHP、Eiffel、Ruby 和 Tcl 等登刺。

MySQL 對PHP有很好的支持籽腕，PHP 是目前最流行的 Web 開發(fā)語言。

MySQL 支持大型數(shù)據(jù)庫纸俭，支持 5000 萬條記錄的數(shù)據(jù)倉庫皇耗，32 位系統(tǒng)表文件最大可支持 4GB，64 位系統(tǒng)支持最大的表文件為8TB揍很。

MySQL 是可以定制的郎楼，采用了 GPL 協(xié)議，你可以修改源碼來開發(fā)自己的 MySQL 系統(tǒng)女轿。

在日常工作與學(xué)習(xí)中箭启，無論是開發(fā)、運維蛉迹、還是測試傅寡，對于數(shù)據(jù)庫的學(xué)習(xí)是不可避免的，同時也是日常工作的必備技術(shù)之一北救。在互聯(lián)網(wǎng)公司荐操，開源產(chǎn)品線比較多，互聯(lián)網(wǎng)企業(yè)所用的數(shù)據(jù)庫占比較重的還是MySQL珍策。

更多關(guān)于MySQL數(shù)據(jù)庫的介紹托启，有興趣的讀者可以參考官方網(wǎng)站的文檔和這篇文章：?可能是全網(wǎng)最好的MySQL重要知識點?，關(guān)于MySQL架構(gòu)的介紹可以參考：?MySQL 架構(gòu)總覽->查詢執(zhí)行流程->SQL 解析順序

MySQL 安裝

MySQL 8正式版8.0.11已發(fā)布攘宙，官方表示MySQL8要比MySQL 5.7快2倍屯耸，還帶來了大量的改進(jìn)和更快的性能！到底誰最牛呢蹭劈？請看：?MySQL 5.7 vs 8.0疗绣，哪個性能更牛？

詳細(xì)的安裝步驟請參閱：?CentOS 下 MySQL 8.0 安裝部署铺韧，超詳細(xì)多矮！?，介紹幾個 8.0 在關(guān)系數(shù)據(jù)庫方面的主要新特性：?MySQL 8.0 的 5 個新特性哈打，太實用了塔逃！

MySQL基礎(chǔ)入門操作

Windows服務(wù)

-- 啟動MySQLnetstartmysql-- 創(chuàng)建Windows服務(wù)sccreatemysql binPath= mysqld_bin_path(注意：等號與值之間有空格)

連接與斷開服務(wù)器

mysql -h 地址 -P 端口 -u 用戶名 -p 密碼SHOWPROCESSLIST-- 顯示哪些線程正在運行SHOWVARIABLES-- 顯示系統(tǒng)變量信息

數(shù)據(jù)庫操作

-- 查看當(dāng)前數(shù)據(jù)庫SELECTDATABASE();-- 顯示當(dāng)前時間、用戶名料仗、數(shù)據(jù)庫版本SELECTnow(),user(),version();-- 創(chuàng)建庫CREATEDATABASE[IFNOTEXISTS] 數(shù)據(jù)庫名 數(shù)據(jù)庫選項? ? 數(shù)據(jù)庫選項：CHARACTERSETcharset_nameCOLLATEcollation_name-- 查看已有庫SHOWDATABASES[LIKE'PATTERN']-- 查看當(dāng)前庫信息SHOWCREATEDATABASE數(shù)據(jù)庫名-- 修改庫的選項信息ALTERDATABASE庫名 選項信息-- 刪除庫DROPDATABASE[IFEXISTS] 數(shù)據(jù)庫名? ? ? ? 同時刪除該數(shù)據(jù)庫相關(guān)的目錄及其目錄內(nèi)容

表的操作

-- 創(chuàng)建表CREATE[TEMPORARY]TABLE[IFNOTEXISTS] [庫名.]表名 ( 表的結(jié)構(gòu)定義 )[ 表選項]每個字段必須有數(shù)據(jù)類型最后一個字段后不能有逗號TEMPORARY臨時表湾盗，會話結(jié)束時表自動消失對于字段的定義：? 字段名 數(shù)據(jù)類型 [NOTNULL|NULL] [DEFAULTdefault_value] [AUTO_INCREMENT] [UNIQUE[KEY] | [PRIMARY]KEY] [COMMENT'string']-- 表選項-- 字符集CHARSET= charset_name? 如果表沒有設(shè)定，則使用數(shù)據(jù)庫字符集-- 存儲引擎ENGINE= engine_name? 表在管理數(shù)據(jù)時采用的不同的數(shù)據(jù)結(jié)構(gòu)立轧，結(jié)構(gòu)不同會導(dǎo)致處理方式淹仑、提供的特性操作等不同? 常見的引擎：InnoDBMyISAMMemory/HeapBDBMergeExample CSV MaxDBArchive不同的引擎在保存表的結(jié)構(gòu)和數(shù)據(jù)時采用不同的方式? MyISAM表文件含義：.frm表定義丙挽，.MYD表數(shù)據(jù)肺孵，.MYI表索引InnoDB表文件含義：.frm表定義匀借，表空間數(shù)據(jù)和日志文件SHOWENGINES-- 顯示存儲引擎的狀態(tài)信息SHOWENGINE引擎名 {LOGS|STATUS}-- 顯示存儲引擎的日志或狀態(tài)信息-- 自增起始數(shù)AUTO_INCREMENT = 行數(shù)-- 數(shù)據(jù)文件目錄DATADIRECTORY='目錄'-- 索引文件目錄INDEXDIRECTORY='目錄'-- 表注釋COMMENT='string'-- 分區(qū)選項PARTITIONBY... (詳細(xì)見手冊)-- 查看所有表SHOWTABLES[LIKE'pattern']SHOWTABLESFROM表名-- 查看表機構(gòu)SHOWCREATETABLE表名 （信息更詳細(xì)）DESC表名 /DESCRIBE表名 /EXPLAIN表名 /SHOWCOLUMNSFROM表名 [LIKE'PATTERN']SHOWTABLESTATUS[FROMdb_name] [LIKE'pattern']-- 修改表-- 修改表本身的選項ALTERTABLE表名 表的選項? ? eg:ALTERTABLE表名ENGINE=MYISAM;-- 對表進(jìn)行重命名RENAMETABLE原表名TO新表名RENAMETABLE原表名TO庫名.表名 （可將表移動到另一個數(shù)據(jù)庫）-- RENAME可以交換兩個表名-- 修改表的字段機構(gòu)（13.1.2. ALTER TABLE語法）ALTERTABLE表名 操作名-- 操作名ADD[COLUMN] 字段定義-- 增加字段AFTER字段名-- 表示增加在該字段名后面FIRST-- 表示增加在第一個ADDPRIMARYKEY(字段名)-- 創(chuàng)建主鍵ADDUNIQUE[索引名] (字段名)-- 創(chuàng)建唯一索引ADDINDEX[索引名] (字段名)-- 創(chuàng)建普通索引DROP[COLUMN] 字段名-- 刪除字段MODIFY[COLUMN] 字段名 字段屬性-- 支持對字段屬性進(jìn)行修改，不能修改字段名(所有原有屬性也需寫上)CHANGE[COLUMN] 原字段名 新字段名 字段屬性-- 支持對字段名修改DROPPRIMARYKEY-- 刪除主鍵(刪除主鍵前需刪除其AUTO_INCREMENT屬性)DROPINDEX索引名-- 刪除索引DROPFOREIGNKEY外鍵-- 刪除外鍵-- 刪除表DROPTABLE[IFEXISTS] 表名 ...-- 清空表數(shù)據(jù)TRUNCATE[TABLE] 表名-- 復(fù)制表結(jié)構(gòu)CREATETABLE表名LIKE要復(fù)制的表名-- 復(fù)制表結(jié)構(gòu)和數(shù)據(jù)CREATETABLE表名 [AS]SELECT*FROM要復(fù)制的表名-- 檢查表是否有錯誤CHECKTABLEtbl_name [, tbl_name] ... [option] ...-- 優(yōu)化表OPTIMIZE[LOCAL|NO_WRITE_TO_BINLOG]TABLEtbl_name [, tbl_name] ...-- 修復(fù)表REPAIR[LOCAL|NO_WRITE_TO_BINLOG]TABLEtbl_name [, tbl_name] ... [QUICK] [EXTENDED] [USE_FRM]-- 分析表ANALYZE[LOCAL|NO_WRITE_TO_BINLOG]TABLEtbl_name [, tbl_name] ...

更多相關(guān)的操作基礎(chǔ)知識點請參閱以下文章：

MySQL數(shù)據(jù)庫入門———常用基礎(chǔ)命令

1047 行 MySQL 詳細(xì)學(xué)習(xí)筆記（值得學(xué)習(xí)與收藏）

MySQL基礎(chǔ)入門之常用命令介紹

MySQL 多實例配置

MySQL數(shù)據(jù)庫入門——多實例配置

MySQL 主從同步復(fù)制

復(fù)制概述

Mysql內(nèi)建的復(fù)制功能是構(gòu)建大型平窘，高性能應(yīng)用程序的基礎(chǔ)吓肋。將Mysql的數(shù)據(jù)分布到多個系統(tǒng)上去，這種分布的機制瑰艘，是通過將Mysql的某一臺主機的數(shù)據(jù)復(fù)制到其它主機（slaves）上是鬼，并重新執(zhí)行一遍來實現(xiàn)的。復(fù)制過程中一個服務(wù)器充當(dāng)主服務(wù)器紫新，而一個或多個其它服務(wù)器充當(dāng)從服務(wù)器均蜜。主服務(wù)器將更新寫入二進(jìn)制日志文件，并維護(hù)文件的一個索引以跟蹤日志循環(huán)芒率。這些日志可以記錄發(fā)送到從服務(wù)器的更新囤耳。當(dāng)一個從服務(wù)器連接主服務(wù)器時，它通知主服務(wù)器從服務(wù)器在日志中讀取的最后一次成功更新的位置偶芍。從服務(wù)器接收從那時起發(fā)生的任何更新充择，然后封鎖并等待主服務(wù)器通知新的更新。

請注意當(dāng)你進(jìn)行復(fù)制時匪蟀，所有對復(fù)制中的表的更新必須在主服務(wù)器上進(jìn)行椎麦。否則，你必須要小心材彪，以避免用戶對主服務(wù)器上的表進(jìn)行的更新與對從服務(wù)器上的表所進(jìn)行的更新之間的沖突观挎。 mysql支持的復(fù)制類型：

L默認(rèn)采用基于語句的復(fù)制，效率比較高段化。一旦發(fā)現(xiàn)沒法精確復(fù)制時嘁捷， ? 會自動選著基于行的復(fù)制。

l5.0開始支持

采用基于行的復(fù)制穗泵。

復(fù)制解決的問題

MySQL復(fù)制技術(shù)有以下一些特點：

數(shù)據(jù)分布 (Data distribution )

負(fù)載平衡(load balancing)

備份(Backups)

高可用性和容錯行 High availability and failover

復(fù)制如何工作

整體上來說普气，復(fù)制有3個步驟：

master將改變記錄到二進(jìn)制日志(binary log)中（這些記錄叫做二進(jìn)制日志事件，binary log events）佃延；

slave將master的binary log events拷貝到它的中繼日志(relay log)现诀；

slave重做中繼日志中的事件，將改變反映它自己的數(shù)據(jù)履肃。

更多相關(guān)的更深入的介紹參考：?Mysql主從架構(gòu)的復(fù)制原理及配置詳解

MySQL 復(fù)制有兩種方法：

傳統(tǒng)方式：基于主庫的bin-log將日志事件和事件位置復(fù)制到從庫仔沿，從庫再加以 應(yīng)用來達(dá)到主從同步的目的。

Gtid方式：global transaction identifiers是基于事務(wù)來復(fù)制數(shù)據(jù)尺棋，因此也就不 依賴日志文件位置封锉，同時又能更好的保證主從庫數(shù)據(jù)一致性。

MySQL數(shù)據(jù)庫主從同步實戰(zhàn)過程

基于 Gtid 的 MySQL 主從同步實踐

MySQL 主從同步架構(gòu)中你不知道的“坑”?（上）

MySQL 主從同步架構(gòu)中你不知道的“坑”（下）

MySQL復(fù)制有多種類型：

異步復(fù)制：一個主庫，一個或多個從庫成福，數(shù)據(jù)異步同步到從庫碾局。

同步復(fù)制：在MySQL Cluster中特有的復(fù)制方式。

半同步復(fù)制：在異步復(fù)制的基礎(chǔ)上奴艾，確保任何一個主庫上的事務(wù)在提交之前至 少有一個從庫已經(jīng)收到該事務(wù)并日志記錄下來。

延遲復(fù)制：在異步復(fù)制的基礎(chǔ)上蕴潦，人為設(shè)定主庫和從庫的數(shù)據(jù)同步延遲時間忽冻， 即保證數(shù)據(jù)延遲至少是這個參數(shù)秀菱。

MySQL主從復(fù)制延遲解決方案：?高可用數(shù)據(jù)庫主從復(fù)制延時的解決方案

MySQL 數(shù)據(jù)備份與恢復(fù)

數(shù)據(jù)備份多種方式：

物理備份是指通過拷貝數(shù)據(jù)庫文件的方式完成備份辫呻，這種備份方式適用于數(shù)據(jù)庫很大，數(shù)據(jù)重要且需要快速恢復(fù)的數(shù)據(jù)庫

邏輯備份是指通過備份數(shù)據(jù)庫的邏輯結(jié)構(gòu)（create database/table語句)和數(shù)據(jù)內(nèi)容（insert語句或者文本文件）的方式完成備份琼锋。這種備份方式適用于數(shù)據(jù)庫不是很大放闺，或者你需要對導(dǎo)出的文件做一定的修改，又或者是希望在另外的不同類型服務(wù)器上重新建立此數(shù)據(jù)庫的情況

通常情況下物理備份的速度要快于邏輯備份缕坎，另外物理備份的備份和恢復(fù)粒度范圍為整個數(shù)據(jù)庫或者是單個文件怖侦。對單表是否有恢復(fù)能力取決于存儲引擎，比如在MyISAM存儲引擎下每個表對應(yīng)了獨立的文件谜叹，可以單獨恢復(fù)匾寝；但對于InnoDB存儲引擎表來說，可能每個表示對應(yīng)了獨立的文件荷腊，也可能表使用了共享數(shù)據(jù)文件

物理備份通常要求在數(shù)據(jù)庫關(guān)閉的情況下執(zhí)行艳悔，但如果是在數(shù)據(jù)庫運行情況下執(zhí)行，則要求備份期間數(shù)據(jù)庫不能修改

邏輯備份的速度要慢于物理備份女仰，是因為邏輯備份需要訪問數(shù)據(jù)庫并將內(nèi)容轉(zhuǎn)化成邏輯備份需要的格式猜年；通常輸出的備份文件大小也要比物理備份大抡锈；另外邏輯備份也不包含數(shù)據(jù)庫的配置文件和日志文件內(nèi)容；備份和恢復(fù)的粒度可以是所有數(shù)據(jù)庫乔外，也可以是單個數(shù)據(jù)庫床三，也可以是單個表；邏輯備份需要再數(shù)據(jù)庫運行的狀態(tài)下執(zhí)行袁稽；它的執(zhí)行工具可以是mysqldump或者是select … into outfile兩種方式

生產(chǎn)數(shù)據(jù)庫備份方案：?高逼格企業(yè)級MySQL數(shù)據(jù)庫備份方案

MySQL數(shù)據(jù)庫物理備份方式：?Xtrabackup實現(xiàn)數(shù)據(jù)的備份與恢復(fù)

MySQL 定時備份：?MySQL 數(shù)據(jù)庫定時備份的幾種方式（非常全面）

MySQL 高可用架構(gòu)設(shè)計與實戰(zhàn)

先來了解一下MySQL高可用架構(gòu)簡介：?淺談MySQL集群高可用架構(gòu)

MySQL高可用方案：?MySQL 同步復(fù)制及高可用方案總結(jié)

官方也提供一種高可用方案：?官方工具｜MySQL Router 高可用原理與實戰(zhàn)

MHA

MHA（Master High Availability）目前在MySQL高可用方面是一個相對成熟的解決方案勿璃，該軟件由兩部分組成：MHA Manager（管理節(jié)點）和MHA Node（數(shù)據(jù)節(jié)點。

MHA Manager: 可以單獨部署在一臺獨立的機器上管理多個master-slave集群推汽，也可以部署在一臺slave節(jié)點上。

MHA Node: 行在每臺MySQL服務(wù)器上歧沪。

MHA Manager會定時探測集群中的master節(jié)點歹撒，當(dāng)master出現(xiàn)故障時，它可以自動將最新數(shù)據(jù)的slave提升為新的master诊胞，然后將所有其他的slave重新指向新的master暖夭。整個故障轉(zhuǎn)移過程對應(yīng)用程序完全透明。

MHA高可用方案實戰(zhàn)：?MySQL集群高可用架構(gòu)之MHA

MGR

Mysql Group Replication(MGR)是從5.7.17版本開始發(fā)布的一個全新的高可用和高擴張的MySQL集群服務(wù)撵孤。

高一致性迈着，基于原生復(fù)制及paxos協(xié)議的組復(fù)制技術(shù)，以插件方式提供一致數(shù)據(jù)安全保證邪码；

高容錯性裕菠，大多數(shù)服務(wù)正常就可繼續(xù)工作，自動不同節(jié)點檢測資源征用沖突闭专，按順序優(yōu)先處理奴潘，內(nèi)置動防腦裂機制；

高擴展性影钉，自動添加移除節(jié)點画髓，并更新組信息；

高靈活性平委，單主模式和多主模式奈虾。單主模式自動選主，所有更新操作在主進(jìn)行廉赔；多主模式肉微，所有server同時更新。

MySQL 數(shù)據(jù)庫讀寫分離高可用

海量數(shù)據(jù)的存儲和訪問成為了系統(tǒng)設(shè)計的瓶頸問題昂勉，日益增長的業(yè)務(wù)數(shù)據(jù)浪册，無疑對數(shù)據(jù)庫造成了相當(dāng)大的負(fù)載，同時對于系統(tǒng)的穩(wěn)定性和擴展性提出很高的要求岗照。隨著時間和業(yè)務(wù)的發(fā)展村象，數(shù)據(jù)庫中的表會越來越多笆环，表中的數(shù)據(jù)量也會越來越大，相應(yīng)地厚者，數(shù)據(jù)操作的開銷也會越來越大躁劣；另外，無論怎樣升級硬件資源库菲，單臺服務(wù)器的資源（CPU账忘、磁盤、內(nèi)存熙宇、網(wǎng)絡(luò)IO鳖擒、事務(wù)數(shù)、連接數(shù)）總是有限的烫止，最終數(shù)據(jù)庫所能承載的數(shù)據(jù)量蒋荚、數(shù)據(jù)處理能力都將遭遇瓶頸。分表馆蠕、分庫和讀寫分離可以有效地減小單臺數(shù)據(jù)庫的壓力期升。

MySQL讀寫分離高可用架構(gòu)實戰(zhàn)案例：

ProxySQL+Mysql實現(xiàn)數(shù)據(jù)庫讀寫分離實戰(zhàn)

Mysql+Mycat實現(xiàn)數(shù)據(jù)庫主從同步與讀寫分離

MySQL性能優(yōu)化

史上最全的MySQL高性能優(yōu)化實戰(zhàn)總結(jié)！

MySQL索引原理：?MySQL 的索引是什么互躬？怎么優(yōu)化播赁？

顧名思義，B-tree索引使用B-tree的數(shù)據(jù)結(jié)構(gòu)存儲數(shù)據(jù)吼渡，不同的存儲引擎以不同的方式使用B-Tree索引容为，比如MyISAM使用前綴壓縮技術(shù)使得索引空間更小，而InnoDB則按照原數(shù)據(jù)格式存儲诞吱，且MyISAM索引在索引中記錄了對應(yīng)數(shù)據(jù)的物理位置舟奠，而InnoDB則在索引中記錄了對應(yīng)的主鍵數(shù)值。B-Tree通常意味著所有的值都是按順序存儲房维，并且每個葉子頁到根的距離相同沼瘫。

B-Tree索引驅(qū)使存儲引擎不再通過全表掃描獲取數(shù)據(jù)，而是從索引的根節(jié)點開始查找咙俩，在根節(jié)點和中間節(jié)點都存放了指向下層節(jié)點的指針耿戚，通過比較節(jié)點頁的值和要查找值可以找到合適的指針進(jìn)入下層子節(jié)點胆剧，直到最下層的葉子節(jié)點维贺，最終的結(jié)果就是要么找到對應(yīng)的值茎芭，要么找不到對應(yīng)的值沸柔。整個B-tree樹的深度和表的大小直接相關(guān)。

全鍵值匹配：和索引中的所有列都進(jìn)行匹配烫葬，比如查找姓名為zhang san荷辕，出生于1982-1-1的人

匹配最左前綴：和索引中的最左邊的列進(jìn)行匹配昧甘，比如查找所有姓為zhang的人

匹配列前綴：匹配索引最左邊列的開頭部分命黔，比如查找所有以z開頭的姓名的人

匹配范圍值：匹配索引列的范圍區(qū)域值呜呐，比如查找姓在li和wang之間的人

精確匹配左邊列并范圍匹配右邊的列：比如查找所有姓為Zhang就斤，且名字以K開頭的人

只訪問索引的查詢：查詢結(jié)果完全可以通過索引獲得，也叫做覆蓋索引蘑辑，比如查找所有姓為zhang的人的姓名

MySQL 常用30種SQL查詢語句優(yōu)化方法?|

MySQL太慢洋机？試試這些診斷思路和工具

MySQL 性能優(yōu)化的 9 種姿勢，面試再也不怕了洋魂！

MySQL表分區(qū)介紹：?一文徹底搞懂MySQL分區(qū)

可以允許在?個表?存儲更多的數(shù)據(jù)绷旗，突破磁盤限制或者?件系統(tǒng)限制。

對于從表?將過期或歷史的數(shù)據(jù)移除在表分區(qū)很容易實現(xiàn)副砍，只要將對應(yīng)的分區(qū)移除即可

對某些查詢和修改語句來說衔肢，可以?動將數(shù)據(jù)范圍縮?到?個或?個表分區(qū)上，優(yōu)化語句執(zhí)?效率址晕。?且可以通過顯示指定表分區(qū)來執(zhí)?語句膀懈，?如 select * from temp partition(p1,p2) where store_id < 5;

表分區(qū)是將?個表的數(shù)據(jù)按照?定的規(guī)則?平劃分為不同的邏輯塊，并分別進(jìn)?物理存儲谨垃，這個規(guī)則就叫做分區(qū)函數(shù)，可以有不同的分區(qū)規(guī)則硼控。

MySQL5.7版本可以通過show plugins語句查看當(dāng)前MySQL是否?持表分區(qū)功能刘陶。

MySQL8.0版本移除了show plugins?對partition的顯示，但社區(qū)版本的表分區(qū)功能是默認(rèn)開啟的牢撼。

但當(dāng)表中含有主鍵或唯?鍵時匙隔，則每個被?作分區(qū)函數(shù)的字段必須是表中唯?鍵和主鍵的全部或?部分，否則就?法創(chuàng)建分區(qū)表熏版。

MySQL分庫分表

能不分就不分纷责，1000萬以內(nèi)的表，不建議分片撼短，通過合適的索引再膳，讀寫分離等方式，可以很好的解決性能問題曲横。

分片數(shù)量盡量少喂柒，分片盡量均勻分布在多個DataHost上，因為一個查詢SQL跨分片越多禾嫉，則總體性能越差灾杰，雖然要好于所有數(shù)據(jù)在一個分片的結(jié)果，只在必要的時候進(jìn) 行擴容熙参，增加分片數(shù)量艳吠。

分片規(guī)則需要慎重選擇，分片規(guī)則的選擇孽椰，需要考慮數(shù)據(jù)的增長模式昭娩，數(shù)據(jù)的訪 問模式凛篙，分片關(guān)聯(lián)性問題，以及分片擴容問題题禀，最近的分片策略為范圍分片鞋诗，枚舉分片， 一致性Hash分片迈嘹，這幾種分片都有利于擴容削彬。

盡量不要在一個事務(wù)中的SQL跨越多個分片，分布式事務(wù)一直是個不好處理的問題秀仲。

查詢條件盡量優(yōu)化融痛，盡量避免Select * 的方式，大量數(shù)據(jù)結(jié)果集下神僵，會消耗大量 帶寬和CPU資源雁刷，查詢盡量避免返回大量結(jié)果集，并且盡量為頻繁使用的查詢語句建立索引保礼。

數(shù)據(jù)庫分庫分表概述：?數(shù)據(jù)庫分庫分表沛励，何時分？怎樣分炮障？

Mysql分庫分表方案：?MySQL 分庫分表方案目派，總結(jié)的非常好！

Mysql分庫分表的思路：?解救 DBA—數(shù)據(jù)庫分庫分表思路及案例分析

MySQL性能監(jiān)控

MySQL性能監(jiān)控的指標(biāo)大體可以分為以下4大類：

查詢吞吐量

查詢延遲與錯誤

客戶端連接與錯誤

緩沖池利用率

對于MySQL性能監(jiān)控胁赢，官方也提供了相關(guān)的服務(wù)插件：MySQL-Percona企蹭，下面簡單介紹一下插件的安裝

[root@db01 ~]# yum -y install php php-mysql[root@db01 ~]# wget https://www.percona.com/downloads/percona-monitoring-plugins/percona-monitoring-plugins-1.1.8/binary/redhat/7/x86_64/percona-zabbix-templates-1.1.8-1.noarch.rpm[root@db01 ~]# rpm -ivh percona-zabbix-templates-1.1.8-1.noarch.rpmwarning: percona-zabbix-templates-1.1.8-1.noarch.rpm: Header V4 DSA/SHA1 Signature, key ID cd2efd2a: NOKEYPreparing...################################# [100%]

Updating / installing...

? 1:percona-zabbix-templates-1.1.8-1 #################################[100%]Scripts are installed to /var/lib/zabbix/percona/scriptsTemplates are installed to /var/lib/zabbix/percona/templates

最后，可以配合其它監(jiān)控工具來實現(xiàn)對MySQL的性能監(jiān)控智末。

MySQL服務(wù)器配置插件：

修改php腳本連接MySQL的monitor@localhost用戶

修改MySQL的sock文件路徑

[root@db01 ~]# sed -i'30c$mysql_user ="monitor";' /var/lib/zabbix/percona/scripts/ss_get_mysql_stats.php[root@db01 ~]# sed -i'31c$mysql_pass ="123456";' /var/lib/zabbix/percona/scripts/ss_get_mysql_stats.php[root@db01 ~]# sed -i'33c$mysql_socket ="/tmp/mysql.sock";' /var/lib/zabbix/percona/scripts/ss_get_mysql_stats.php

測試是否可用（ 可以從MySQL中獲取到監(jiān)控值 ）

[root@db01 ~]# /usr/bin/php -q /var/lib/zabbix/percona/scripts/ss_get_mysql_stats.php --host localhost --items gggg:12# 確保當(dāng)前文件的 屬主 屬組 是zabbix谅摄，否則zabbix監(jiān)控取值錯誤。[root@db01 ~]# ll -sh /tmp/localhost-mysql_cacti_stats.txt4.0K -rw-rw-r--1zabbix zabbix1.3K Dec517:34/tmp/localhost-mysql_cacti_stats.txt

移動zabbix-agent配置文件到 /etc/zabbix/zabbix_agentd.d/目錄

[root@db01 ~]# mv/var/lib/zabbix/percona/templates/userparameter_percona_mysql.conf/etc/zabbix/zabbix_agentd.d/[root@db01 ~]# systemctl restart zabbix-agent.service

導(dǎo)入并配置Zabbix模板與主機：

默認(rèn)模板監(jiān)控時間為 5分鐘 （ 當(dāng)前測試修改為 30s） 同時也要修改Zabbix模板時間

# 如果要修改監(jiān)控獲取值的時間不但要在zabbix面板修改取值時間系馆，bash腳本也要修改送漠。[root@db01 scripts]# sed -n '/TIMEFLM/p' /var/lib/zabbix/percona/scripts/get_mysql_stats_wrapper.shTIMEFLM=`stat-c %Y /tmp/$HOST-mysql_cacti_stats.txt`if[ `expr$TIMENOW-$TIMEFLM`-gt300 ];then# 這個 300 代表 300s 同時也要修改。

默認(rèn)模板版本為2.0.9它呀，無法在4.0版本使用螺男，可以先從3.0版本導(dǎo)出，然后再導(dǎo)入4.0版本 纵穿。

Zabbix自帶模板監(jiān)控MySQL服務(wù)

其實下隧，在實際生產(chǎn)過程中，還是有相關(guān)的專業(yè)監(jiān)控數(shù)據(jù)庫的第三方開源軟件的谓媒，民工哥之前也寫過相關(guān)的文章淆院，今天發(fā)出來供大家參考：?強大的開源企業(yè)級數(shù)據(jù)庫監(jiān)控利器Lepus

MySQL 管理工具

MySQL 是最廣泛使用和流行的開源數(shù)據(jù)庫之一，圍繞它有許多工具句惯，可以讓設(shè)計土辩，創(chuàng)建和管理數(shù)據(jù)庫的過程變得更加容易和便捷支救。但是如何選擇最適合自己需求的工具，并不容易拷淘。這里為大家推薦：10款MySQL的GUI工具各墨，它們對開發(fā)人員和DBA來說都是不錯的解決方案。

很早之前民工哥就給大家介紹過一款開源的SQL管理工具：?自動補全启涯、回滾贬堵！介紹一款可視化 sql 診斷利器?。

今天结洼，民工哥再給大家推薦一款SQL審核利器：?MySQL 自動化運維工具 goinception?黎做。

可視化管理工具，大家可以試試這個：?介紹一款免費好用的可視化數(shù)據(jù)庫管理工具

俗話說工欲善其事松忍，必先利其器蒸殿，定期對你的MYSQL數(shù)據(jù)庫進(jìn)行一個體檢，是保證數(shù)據(jù)庫安全運行的重要手段鸣峭，因為宏所，好的工具是使你的工作效率倍增！

今天和大家分享幾個mysql 優(yōu)化的工具摊溶，你可以使用它們對你的mysql進(jìn)行一個體檢楣铁，生成awr報告，讓你從整體上把握你的數(shù)據(jù)庫的性能情況更扁。

性能優(yōu)化診斷工具：?別小看這幾個工具！關(guān)鍵時能幫你快速解決數(shù)據(jù)庫瓶頸

MySQL 常見錯誤代碼說明

先給大家看幾個實例的錯誤分析與解決方案赫冬。

1.ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/data/mysql/mysql.sock'

問題分析：可能是數(shù)據(jù)庫沒有啟動或者是端口被防火墻禁止浓镜。

解決方法：啟動數(shù)據(jù)庫或者防火墻開放數(shù)據(jù)庫監(jiān)聽端口。

2.ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)

問題分析：密碼不正確或者沒有權(quán)限訪問劲厌。

解決方法：

1）修改 my.cnf 主配置文件膛薛，在[mysqld]下添加 skip-grant-tables，重啟數(shù)據(jù)庫补鼻。最后修改密碼命令如下：

mysql> use mysql;mysql>updateuser set password=password("123456")whereuser="root";

再刪除剛剛添加的 skip-grant-tables 參數(shù)哄啄，再重啟數(shù)據(jù)庫，使用新密碼即可登錄风范。

2）重新授權(quán)咨跌，命令如下:

mysql> grant allon*.*to'root'@'mysql-server'identifiedby'123456';

3.客戶端報 Too many connections

問題分析：連接數(shù)超出 Mysql 的最大連接限制。

解決方法：

1硼婿、在 my.cnf 配置文件里面增加連接數(shù)锌半，然后重啟 MySQL 服務(wù)。max_connections = 10000

2寇漫、臨時修改最大連接數(shù)刊殉，重啟后不生效殉摔。需要在 my.cnf 里面修改配置文件，下次重啟生效记焊。

setGLOBALmax_connections=10000;

4.Warning: World-writable config file '/etc/my.cnf' is ignored ERROR! MySQL is running but PID file could not be found

問題分析：MySQL 的配置文件/etc/my.cnf 權(quán)限不對逸月。

解決方法：

chmod644/et/my.cnf

5.InnoDB: Error: page 14178 log sequence number 29455369832 InnoDB: is in the future! Current system log sequence number 29455369832

問題分析：innodb 數(shù)據(jù)文件損壞。

解決方法：修改 my.cnf 配置文件遍膜，在?[mysqld]?下添加?innodb_force_recovery=4?, 啟動數(shù)據(jù)庫后備份數(shù)據(jù)文件碗硬，然后去掉該參數(shù)，利用備份文件恢復(fù)數(shù)據(jù)捌归。

6.從庫的 Slave_IO_Running 為 NO

問題分析：主庫和從庫的 server-id 值一樣.

解決方法：修改從庫的 server-id 的值肛响，修改為和主庫不一樣，比主庫低惜索。修改完后重啟特笋，再同步即可！

7.從庫的 Slave_IO_Running 為 NO問題

問題分析：造成從庫線程為 NO 的原因會有很多巾兆，主要原因是主鍵沖突或者主庫刪除或更新數(shù)據(jù)猎物， 從庫找不到記錄，數(shù)據(jù)被修改導(dǎo)致角塑。通常狀態(tài)碼報錯有 1007蔫磨、1032、1062圃伶、1452 等堤如。

解決方法一：

mysql>stopslave;mysql>setGLOBALSQL_SLAVE_SKIP_COUNTER=1;mysql> start slave;

解決方法二：設(shè)置用戶權(quán)限，設(shè)置從庫只讀權(quán)限

setglobalread_only=true;8.Errorinitializing relay log position: I/Oerrorreading the headerfromthebinarylog

分析問題：從庫的中繼日志 relay-bin 損壞. 解決方法：手工修復(fù)窒朋，重新找到同步的 binlog 和 pos 點搀罢，然后重新同步即可。

mysql> CHANGE MASTERTOMASTER_LOG_FILE='mysql-bin.xxx',MASTER_LOG_POS=xxx;

維護(hù)過MySQL的運維或DBA都知道侥猩，經(jīng)常會遇到的一些錯誤信息中有一些類似10xx的代碼榔至。

Replicate_Wild_Ignore_Table:? ? ? ? Last_Errno: 1032? ? ? ? Last_Error: Could notexecuteUpdate_rowseventontablexuanzhi.test; Can't find record in 'test', Error_code: 1032;handlererrorHA_ERR_KEY_NOT_FOUND; the event's master log mysql-bin.000004, end_log_pos 3704

但是，如果不深究或者之前遇到過欺劳，還真不太清楚唧取，這些代碼具體的含義是什么？這也給我們排錯造成了一定的阻礙划提。

所以枫弟，今天民工哥就把主從同步過程中一些常見的錯誤代碼，它的具體說明給大家整理出來了:?建議收藏備查腔剂！MySQL 常見錯誤代碼說明

MySQL 開發(fā)規(guī)范與使用技巧

命名規(guī)范

1.庫名媒区、表名、字段名必須使用小寫字母，并采用下劃線分割袜漩。

a)MySQL有配置參數(shù)lower_case_table_names绪爸，不可動態(tài)更改，Linux系統(tǒng)默認(rèn)為 0宙攻，即庫表名以實際情況存儲奠货，大小寫敏感。如果是1座掘，以小寫存儲递惋，大小寫不敏感。如果是2溢陪，以實際情況存儲萍虽，但以小寫比較。

b)如果大小寫混合使用形真，可能存在abc杉编，Abc，ABC等多個表共存咆霜，容易導(dǎo)致混亂邓馒。

c)字段名顯示區(qū)分大小寫，但實際使?用不區(qū)分蛾坯，即不可以建立兩個名字一樣但大小寫不一樣的字段光酣。

d)為了統(tǒng)一規(guī)范， 庫名脉课、表名救军、字段名使用小寫字母。

2.庫名倘零、表名缤言、字段名禁止超過32個字符。

庫名视事、表名、字段名支持最多64個字符庆揩，但為了統(tǒng)一規(guī)范俐东、易于辨識以及減少傳輸量，禁止超過32個字符订晌。

3.使用INNODB存儲引擎虏辫。

INNODB引擎是MySQL5.5版本以后的默認(rèn)引擘，支持事務(wù)锈拨、行級鎖砌庄，有更好的數(shù)據(jù)恢復(fù)能力、更好的并發(fā)性能，同時對多核娄昆、大內(nèi)存佩微、SSD等硬件支持更好，支持?jǐn)?shù)據(jù)熱備份等萌焰，因此INNODB相比MyISAM有明顯優(yōu)勢哺眯。

4.庫名、表名扒俯、字段名禁止使用MySQL保留字奶卓。

當(dāng)庫名、表名撼玄、字段名等屬性含有保留字時夺姑，SQL語句必須用反引號引用屬性名稱，這將使得SQL語句書寫掌猛、SHELL腳本中變量的轉(zhuǎn)義等變得?非常復(fù)雜盏浙。

5.禁止使用分區(qū)表。

分區(qū)表對分區(qū)鍵有嚴(yán)格要求留潦；分區(qū)表在表變大后只盹，執(zhí)?行DDL、SHARDING兔院、單表恢復(fù)等都變得更加困難殖卑。因此禁止使用分區(qū)表，并建議業(yè)務(wù)端手動SHARDING坊萝。

6.建議使用UNSIGNED存儲非負(fù)數(shù)值孵稽。

同樣的字節(jié)數(shù)，非負(fù)存儲的數(shù)值范圍更大十偶。如TINYINT有符號為 -128-127菩鲜，無符號為0-255。

7.建議使用INT UNSIGNED存儲IPV4惦积。

用UNSINGED INT存儲IP地址占用4字節(jié)接校，CHAR(15)則占用15字節(jié)。另外狮崩，計算機處理整數(shù)類型比字符串類型快蛛勉。使用INT UNSIGNED而不是CHAR(15)來存儲IPV4地址，通過MySQL函數(shù)inet_ntoa和inet_aton來進(jìn)行轉(zhuǎn)化睦柴。IPv6地址目前沒有轉(zhuǎn)化函數(shù)诽凌，需要使用DECIMAL或兩個BIGINT來存儲。

例如：

SELECTINET_ATON('209.207.224.40'); 3520061480SELECT INET_NTOA(3520061480);209.207.224.40

8.強烈建議使用TINYINT來代替ENUM類型坦敌。

ENUM類型在需要修改或增加枚舉值時侣诵，需要在線DDL痢法，成本較高；ENUM列值如果含有數(shù)字類型杜顺，可能會引起默認(rèn)值混淆财搁。

9.使用VARBINARY存儲大小寫敏感的變長字符串或二進(jìn)制內(nèi)容。

VARBINARY默認(rèn)區(qū)分大小寫哑舒，沒有字符集概念妇拯，速度快。

10.INT類型固定占用4字節(jié)存儲

例如INT(4)僅代表顯示字符寬度為4位洗鸵，不代表存儲長度越锈。數(shù)值類型括號后面的數(shù)字只是表示寬度而跟存儲范圍沒有關(guān)系，比如INT(3)默認(rèn)顯示3位膘滨，空格補齊甘凭，超出時正常顯示，Python火邓、Java客戶端等不具備這個功能丹弱。

11.區(qū)分使用DATETIME和TIMESTAMP。

存儲年使用YEAR類型铲咨。存儲日期使用DATE類型躲胳。存儲時間(精確到秒)建議使用TIMESTAMP類型。

DATETIME和TIMESTAMP都是精確到秒纤勒，優(yōu)先選擇TIMESTAMP坯苹，因為TIMESTAMP只有4個字節(jié)，而DATETIME8個字節(jié)摇天。同時TIMESTAMP具有自動賦值以及?自動更新的特性粹湃。注意：在5.5和之前的版本中，如果一個表中有多個timestamp列泉坐，那么最多只能有一列能具有自動更新功能为鳄。

如何使用TIMESTAMP的自動賦值屬性?

a)自動初始化，而且自動更新：column1 TIMESTAMPDEFAULTCURRENT_TIMESTAMPONUPDATECURRENT_TIMESTAMPb)只是自動初始化：column1 TIMESTAMPDEFAULTCURRENT_TIMESTAMPc)自動更新腕让，初始化的值為0：column1 TIMESTAMPDEFAULT0ONUPDATE CURRENT_TIMESTAMPd)初始化的值為0：column1 TIMESTAMPDEFAULT0

12.索引字段均定義為NOT NULL孤钦。

a)對表的每一行，每個為NULL的列都需要額外的空間來標(biāo)識纯丸。

b)B樹索引時不會存儲NULL值司训，所以如果索引字段可以為NULL，索引效率會下降液南。

c)建議用0、特殊值或空串代替NULL值勾徽。

以下內(nèi)容主要受眾為開發(fā)人員,所以不涉及到MySQL的服務(wù)部署等操作,且內(nèi)容較多,大家準(zhǔn)備好耐心和瓜子礦泉水.

前一陣系統(tǒng)的學(xué)習(xí)了一下MySQL,也有一些實際操作經(jīng)驗,偶然看到一篇和MySQL相關(guān)的面試文章,發(fā)現(xiàn)其中的一些問題自己也回答不好,雖然知識點大部分都知道,但是無法將知識串聯(lián)起來.

因此決定搞一個MySQL靈魂100問,試著用回答問題的方式,讓自己對知識點的理解更加深入一點.

此文不會事無巨細(xì)的從select的用法開始講解mysql,主要針對的是開發(fā)人員需要知道的一些MySQL的知識點,主要包括索引,事務(wù),優(yōu)化等方面,以在面試中高頻的問句形式給出答案.

MySQL用戶行為安全

假設(shè)這么一個情況滑凉，你是某公司mysql-DBA统扳，某日突然公司數(shù)據(jù)庫中的所有被人為刪了。

盡管有數(shù)據(jù)備份畅姊，但是因服務(wù)停止而造成的損失上千萬咒钟，現(xiàn)在公司需要查出那個做刪除操作的人。

但是擁有數(shù)據(jù)庫操作權(quán)限的人很多若未，如何排查朱嘴，證據(jù)又在哪？

是不是覺得無能為力粗合？

mysql本身并沒有操作審計的功能萍嬉，那是不是意味著遇到這種情況只能自認(rèn)倒霉呢？