文章目錄

• 一、數據庫管理
• • 1）、SQL語言分類：
  • 2）、創建數據庫和表（DDL）：
  • 3）、管理數據表中的數據：
  • • 1、insert
    • 2、update
    • 3、delete（對數據操作用delete，對庫和表用drop）
  • 4）、數據庫高級操作
  • • 1、清空表
    • 2、臨時表
    • 3、克隆表
  • 5）、數據庫用戶授權
• 二、索引
• • 1、索引的概念
  • 2、索引的作用
  • 3、索引的分類
  • • 1）、普通索引
    • 2）、唯一性索引
    • 3）、主鍵索引
    • 4）、組合索引（單列索引與多列索引）
    • 5）、全文索引
  • 4、創建索引的原則
  • 5、查看索引的方法
  • 6、刪除索引的方法
• 三、事務
• • 1、概念
  • 2、特點
  • • 1）、原子性（Atomicity）
    • 2）、一致性（Consistency）
    • 3）、隔離性（Isolation）
    • 4）、持久性
  • 3、控制語句
  • 4、事務的操作
• 四、操作引擎
• • 1、概念
  • 2、Myisam
  • • 1）、特點：
    • 2）、適用生產場景
  • 3、Innodb
  • • 1）、特點：
    • 2）、適用生產場景
  • 4、修改存儲引擎
• 五、備份與恢復
• • 1、備份的分類
  • • 1）、從物理和邏輯的角度
    • 2）、從數據庫的備份策略角度
  • 2、常見的備份方法
  • • 1）、物理冷備
    • 2）、專業備份工具mysqldump或mysqlhotcopy
    • 3）、啟用二進制日志進行增量備份
    • 4）、第三方工具備份
  • 3、完全備份
  • • 1）、簡介
    • 2）、優點
    • 3）、缺點
    • 4）、分類
    • • 1、物理冷備份與恢復
      • 2)、mysqldump備份與恢復
  • 4、增量備份
  • 5、增量恢復
  • • 1）、一般恢復
    • 2）、斷點恢復
    • • 1、基于位置恢復
      • 2、基于時間點恢復
  • 六、主從復制
  • 1、原理
  • 故障及解決方法
  • • 開發設置的crontab計劃任務周期不合理：
    • MySQL中主從延遲高，怎么解決？
    • 主從同步延遲的解決辦法：
    • 3.判斷主從延遲的方法
  • 2、主服務器（Master）
  • • 1）、日志文件
    • 2）、授權
    • 3）、記錄日志文件及位置點
  • 3、從服務器
  • • 1）、日志文件
    • 2）、與Master同步
    • 3）、主從復制失敗原因
• 七、讀寫分離
• • 1、原理
  • 2、部署amoeba
  • • 1）、安裝jdk環境
    • 2）、部署amoeba代理
    • 3）、在MySQL上給amoeba授權
    • 4）、修改配置文件
    • • 1、amoeba.xml
      • 2、dbServer.xml
• 八、MHA高可用及故障切換
• • 1、概述
  • 2、具體配置
  • • 1）、配置一主兩從
    • 2）、MHA軟件安裝
    • 3）、配置ssh無密碼認證
    • 4）、配置虛擬IP
    • 5）、配置文件解析
    • 6）、ssh無密碼認證測試
    • 7）、MHA健康檢查
    • 8）、在MHA的master節點開啟虛擬IP
• 九、SQL高級語句
• • 1、按關鍵字排序（order by）
  • 2、對結果進行分組
  • 3、限制結果條目
  • 4、設置別名
  • 5、通配符
  • 6、子查詢
  • 7、null值
  • 8、正則表達式
  • 9、運算符
  • • 比較運算符
    • 邏輯運算符
    • • （1）邏輯非
      • （2）邏輯與
      • （3）邏輯或（最好用or）
      • （4）邏輯異或
    • 位運算符
  • 10、連接查詢
  • • 1、內連接
    • 2、外連接
• 十、MySQL函數
• • 數據庫函數
  • 1、常用的數學函數
  • 2、聚合函數
  • 3、字符串函數
  • 4、日期時間函數
• 十一、MySQL存儲過程
• • 1、存儲過程簡介
  • 2、創建存儲過程
  • 3、調用存儲過程
  • 4、查詢存儲過程
  • 5、修改存儲過程
  • 6、刪除存儲過程
  • 7、傳遞參數過程示例：
• 十二、死鎖＋慢查詢
• • • 1、行級鎖與死鎖
    • 2、慢查詢：
• 十三、優化范圍
• • MySQL 服務器硬件和操作系統調節
  • MySQL 配置
  • MySQL模式優化
  • 查詢優化
  • MySQL 備份過程

一、數據庫管理

1）、SQL語言分類：

DDL：（Data Definition Language，數據定義語言）：用來建立數據庫、數據庫對象和定義字段，如CERATE、ALTER、DROP；

DML：（Data Manipulation Language，數據操縱語言）：用來插入、刪除和修改數據庫中的數據，如INSERT、UPDATE、DELETE。

DQL：（Data Query Language，數據查詢語言）：用來查詢數據庫中的數據，如SELECT。

DCL：（Data Control Language，數據控制語言）：用來控制數據庫組件的存取許可、存取權限等，如COMMIT、ROLLBACK、GRANT、REVOKE。

2）、創建數據庫和表（DDL）：

1、create database a1；

2、use a1；

3、create table a2（）；

4、create table 表名（字段01名稱 字段01類型 字段01約束，字段02名稱 字段02類型 字段02約束,…）存儲引擎，字符集

字段01類型：

int（4） 整型 代表0000-9999

double 浮點型

**decimal（5,2）**有效數字是5位，小數點后面保留2位 100.00；099.50

float 單精度浮點 4字節

char 字符

**char （10） ** 固定長度字符串，字符串要用單引號引起來

**varchar（50） ** 可變長度字符串

字段01約束：

非空約束： not null

主鍵約束： primary key（主鍵）

默認約束：假如沒有填數據，默認預先設定的值填寫 default ‘未知’

自增特性： auto_increment（自動增長）

存儲引擎：myisam innodb

字符集：UTF-8

3）、管理數據表中的數據：

1、insert

insert into 表名 (字段1，字段2) values (字段1的值，字段2的值)

或insert into 表名 values (所有字段的值)

查看添加的信息：select * from 表名；

2、update

update 表名 set 字段名1=值1,字段名2=值2 where 條件表達式

3、delete（對數據操作用delete，對庫和表用drop）

格式：delete from 表名 where 條件表達式（不帶where代表刪除表中所有記錄）

4）、數據庫高級操作

1、清空表

delete from info；

truncate table info；

truncate清空表，表還在；drop是刪除表中所有記錄。

truncate和delete是兩者的新值初始id不同。

2、臨時表

臨時建立的表，用于保存一些臨時數據，不會長期存在

create temporary table temp_info(…)engine=innodb default charset=utf8;

innodb 支持事務；寫在括號外面的是對整張表的設定。

show tables；看不到臨時表

3、克隆表

like方法：從info表完整復制結構生成test表

create table test like info;

導入數據：insert into test select * from info;

5）、數據庫用戶授權

1、設置登錄密碼為abc123的lisi用戶，可以從任意終端登錄，對所有庫和所有表有全部權限：

create user ‘lisi’@’%’ identified by ‘abc123’;

grant all on . to ‘lisi’@’%’ identified by ‘abc123’;

2、設置登錄密碼為abc123的tom用戶，可以從本地終端登錄，對mysql庫中的user表有select權限

create user ‘tom’@‘localhost’ identified by ‘abc123’;

grant select on mysql.user to ‘tom’@‘abc123’ identified by ‘abc123’;

3、查看當前用戶的權限：show grants；

? 查看當前系統中的用戶：select user from mysql.user;

? 查看從本地登錄的tom用戶的權限：

? show grants for ‘tom’@‘localhost’;

4、撤銷用戶的權限：

? revoke select on mysql.user from ‘tom’@‘localhost’;

二、索引

1、索引的概念

1、是一個排序的列表，存儲著索引值和這個值所對應的物理地址；

2、無需對整個表進行掃描，通過物理地址就可以找到所需數據；

3、是表中一列或者若干列值排序的方法；

4、需要額外的磁盤空間；

補充：

索引需要的額外的磁盤空間伴隨著表直接存在

閾值：300行以上的才建立索引，不然浪費磁盤空間

2、索引的作用

1、數據庫利用各種快速定位技術，能夠大大加快查詢速率；

2、當表很大或查詢涉及到多個表時，可以成千上萬倍地提高查詢速度；

3、可以降低數據庫的IO成本，并且還可以降低數據庫的排序成本；

4、通過創建唯一性索引保證數據表數據的唯一性

IO：輸入（寫入、更改數據），輸出（讀取數據）；

5、可以加快表與表之間的連接；

6、在使用分組和排序時，可大大減少分組和排序時間

3、索引的分類

1）、普通索引

最基本的索引類型，沒有唯一性之類的限制

1、創建表時創建

create table test(……index id_index(id));

2、直接創建

create index name_index on test(name);

3、修改表結構方式創建

alter table test add index id_index(id);

2）、唯一性索引

與“普通索引”基本相同

與普通索引的區別是索引列的所有值只能出現一次，即必須唯一

1、創建表時創建

create table test(……unique index id_index(id));

2、直接創建

create unique index name_index on test(name);

3、修改表結構方式創建

alter table test add unique index id_index(id);

3）、主鍵索引

是一種特殊的唯一索引，指定為“primary key”

一個表只能有一個主鍵，不允許有空值（非空且唯一）

1、創建表時創建

create table test(……primary key(id));

2、修改表時創建

alter table test add primary key(id);

4）、組合索引（單列索引與多列索引）

可以是單列上創建的索引，也可以是在多列上創建的索引

最左原則，從左往右依次執行

創建組合索引：create table test(……index ff(id,name,score));

5）、全文索引

1、創建表時創建全文索引

create table test(……fulltext (name));

2、在已存在的表上創建全文索引

create fulltext index name_index on test(name);

3、通過SQL語句alter table創建全文索引

alter table test add fulltext index name_index(score);

4、創建索引的原則

1、表的主鍵、外鍵必須有索引；

2、數據量超過300行的表應該有索引；

3、經常與其他表進行連接的表，在連接字段上應該建立索引；

4、唯一性太差的字段不適合建立索引；

5、更新太頻繁地字段不適合創建索引；

6、經常出現在 Where子句中的字段，特別是大表的字段，應該建立索引；

7、索引應該建在選擇性高的字段上；

8、索引應該建在小字段上，對于大的文本字段甚至超長字段，不要建索引

5、查看索引的方法

show index from table_name\G；（\G：豎著顯示）

6、刪除索引的方法

1、drop index index_name on table_name;

2、alter table table_name drop index index_name;

三、事務

1、概念

1、事務是一種機制、一個操作序列，包含了一組數據庫操作命令，并且把所有的命令作為一個整體一起向系統提交或撤銷操作請求，即這一組數據庫命令要么都執行，要么都不執行；

2、事務是一個不可分割的工作邏輯單元，在數據庫系統上執行并發操作時，事務是最小的控制單元；

3、適用于多用戶同時操作的數據庫系統的場景，如銀行、保險公司及證券交易系統等等；

4、通過事務的整體性以保證數據的一致性；

5、如果事務成功了一部分，一部分未成功，則執行回滾，回到事務的起點，重新開始操作

2、特點

1）、原子性（Atomicity）

事務是一個完整的操作，事務的各元素是不可分的

事務中的所有元素必須作為一個整體提交或回滾

如果事務中的任何元素失敗，則整個事務將失敗

2）、一致性（Consistency）

當事務完成時，數據必須處于一致狀態

在事務開始前，數據庫中存儲的數據處于一致狀態

在正在進行的事務中，數據可能處于不一致的狀態

當事務成功完成時，數據必須再回到已知的一致狀態

3）、隔離性（Isolation）

對數據進行修改的所有并發事務是彼此隔離的，表明事務必須是獨立的，它不應以任何方式依賴于或影響其他事務

修改數據的事務可在另一個使用相同數據的事務開始之前訪問這些數據，或者在另一個使用相同數據的事務結束之后訪問這些數據

4）、持久性

指不管系統是否發生故障，事務處理的結果都是永久的

一旦事務被提交，事務的效果會被永久地保留在數據庫中

3、控制語句

1、MySQL事務默認是自動提交的，當SQL語句提交時事務便自動提交；

2、事務控制語句

begin 事務的開始

commit 提交

rollback 回滾

savepoint 存檔點名稱 存檔點

release savepoint 存檔點名稱 刪除存檔點

rollback to 存檔點名稱 回滾到某個存檔點

set transaction 設置事務

set autocommit=0 禁止自動提交

set autocommit=1 開啟自動提交（默認）

4、事務的操作

創建的數據表存儲引擎必須是innodb，才支持事務（5.7版本默認就是innodb）

具體操作：

1、begin;

? insert into test values(1,‘zhangsan’);

? commit;（結束事務）

2、begin;

? insert into test values(1,‘zhangsan’);

? savepoint a;

? savepoint b;

? rollback to b;

? rollback to a

? 注意：只能向前回滾，無法向后回滾

3、三種情況事務開始：

begin；

set autocommit=0；

start transaction

三種情況結束事務：

commit；

set autocommit=1；

rollback

四、操作引擎

1、概念

1、MySQL中的數據用各種不同的技術存儲在文件中，每一種技術都使用不同的存儲機制、索引技巧、鎖定水平并最終提供不同的功能和能力，這些不同的技術以及配套的功能在 MySQL中稱為存儲引擎

2、存儲引擎就是 MySQL將數據存儲在文件系統中的存儲方式或者存儲格式

3、目前 MySQL常用的兩種存儲引擎

MyISAM

InnoDB

（innodb支持事務，myisam不支持事務）

4、MySQL存儲引擎是 MySQL數據庫服務器中的組件，負責為數據庫執行實際的數據I/O操作

使用特殊存儲引擎的主要優點之一在于：

僅需提供特殊應用所需的特性

數據庫中的系統開銷較小

具有更有效和更高的數據庫性能

5、MySQL系統中，存儲引擎處于文件系統之上，在數據保存到數據文件之前會傳輸到存儲引擎，之后按照各個存儲引擎的存儲格式進行存儲

2、Myisam

1）、特點：

1、myisam不支持事務，也不支持外鍵

2、訪問速度快

3、對事物完整性沒有要求

4、myisam在磁盤上存儲成三個文件

.frm文件存儲表定義

數據文件的擴展名為.MYD(MYData)

索引文件的擴展名是.MYI(MYIndex)

5、表級鎖定形式，數據在更新時鎖定整個表（不允許兩個人同時操作）

6、數據庫在讀寫過程中相互阻塞

會在數據寫入的過程阻塞用戶數據的讀取，也會在數據讀取的過程中阻塞用戶的數據寫入

7、數據單獨寫入或讀取，速度過程較快且占用資源相對少

8、myisam

靜態表

動態表 （直接寫入的，數據會隨時變的）

壓縮表

2）、適用生產場景

1、公司業務不需要事務的支持

2、單方面讀取或寫入數據比較多的業務

3、myisam存儲引擎數據讀寫都比較頻繁場景不適合

4、使用讀寫并發訪問相對較低的業務

5、數據修改相對較少的業務

6、對數據業務一致性要求不是非常高的業務

7、服務器硬件資源相對比較差

3、Innodb

1）、特點：

1、支持事務：支持4個事務隔離級別

2、行級（讀寫分離）鎖定，但是全表掃描仍然會是表級鎖定

3、讀寫阻塞與事務隔離級別相關

4、具有非常高效的緩存特性：能緩存索引，也能緩存數據

5、表與主鍵以簇的方式存儲

6、支持外鍵約束，5.5以前不支持全文索引，5.5版本以后支持全文索引

7、對硬件資源要求還是比較高的場合

2）、適用生產場景

1、業務需要事務的支持

2、行級鎖定對高并發有很好的適應能力，但需確保查詢是通過索引來完成

3、業務數據更新較為頻繁的場景，如：論壇、微博等

4、業務數據一致性要求較高，如：銀行業務

5、硬件設備內存較大（因為事務都先放內存），利用innodb較好的緩存能力來提高內存利用率，減少磁盤IO的壓力

4、修改存儲引擎

1、alter table 修改

alter table table_name engine=引擎；

2、修改my.cnf，指定默認存儲引擎并重啟服務

在[mysqld]下面添加default-storage-engine=InnoDB

3、create table創建表時指定存儲引擎

create table 表名（字段）engine=引擎

4、Mysql_convert_table_format轉化存儲引擎

Mysql_convert_table_format --user=root --password=密碼

–sock=/tmp/mysql.sock-engine=引擎 庫名 表名

五、備份與恢復

1、備份的分類

1）、從物理和邏輯的角度

1、物理備份：

對數據庫操作系統的物理文件（如數據文件、日志文件等）的備份

冷備份（脫機備份）：是在關閉數據庫的時候進行的

熱備份（聯機備份）：數據庫處于運行狀態，依賴于數據庫的日志文件

溫備份：數據庫鎖定表格（不可寫入但可讀）的狀態下進行備份操作

2、邏輯備份：對數據庫邏輯組件（如：表等數據庫對象）的備份

2）、從數據庫的備份策略角度

1、完全備份：每次對數據庫進行完整的備份

2、差異備份：備份自從上次完全備份之后被修改過的文件

3、增量備份：只有在上次完全備份或者增量備份后被修改的文件才會被備份

2、常見的備份方法

1）、物理冷備

備份時數據庫處于關閉狀態，直接打包數據庫文件

備份速度快，恢復時也是最簡單的

2）、專業備份工具mysqldump或mysqlhotcopy

mysqldump常用的邏輯備份工具

mysqlhotcopy僅擁有備份MyISAM和ARCHIVE表

3）、啟用二進制日志進行增量備份

進行增量備份，需要刷新二進制日志

4）、第三方工具備份

免費的MySQL熱備份軟件Percona XtraBackup

3、完全備份

1）、簡介

1、是對整個數據庫、數據庫結構和文件結構的備份

2、保存的是備份完成時刻的數據庫

3、是差異備份與增量備份的基礎

4、每次對數據進行完整的備份，完全備份是增量備份的基礎，完全備份保存的是備份完成時刻的數據庫

2）、優點

備份與恢復操作簡單方便

3）、缺點

數據存在大量的重復

占用大量的備份空間

備份與恢復時間長

4）、分類

1、物理冷備份與恢復

關閉MySQL數據庫

使用tar命令直接打包數據庫文件夾（/usr/local/mysql/data）

直接替換現有MySQL目錄即可

2)、mysqldump備份與恢復

MySQL自帶的備份工具，可方便實現對MySQL的備份

可以將指定的庫、表導出為SQL腳本（.sql結尾）

使用命令MySQL導入備份的數據

操作步驟：

mysqldump -u root -p --all-databses > all-data-$(date +%F).sql ###備份所有數據庫mysqldump -u root -p -databases auth mysql > auth-mysql.sql ###備份auth和mysql庫mysqldump -u root -p auth > auth-$(data +%F).sql ###備份auth數據庫mysqldump -u root -p mysql user > mysql-user-$(date +%F).sql ###備份mysql的user表mysqldump -u root -p -d mysql user > /tmp/desc-mysql-user.sql ###備份mysql庫user表的結構

方法一：

[root@server1 ~]# mysqldump -u root -p test > test-$(date +%F).sqlmysql> drop database test;mysql> create database test2; ###建立空庫[root@server1 ~]# mysql -u root -p test2 < test-2020-10-23.sql

方法二：

[root@server1 ~]# mysqldump -u root -p test > test-$(date +%F).sqlmysql> drop database test;mysql> create database test2;mysql> use test2;mysql> source /root/test-2020-10-24.sql;

4、增量備份

1、使用mysqldump進行完全備份存在的問題

備份數據中有重復數據

備份時間與恢復時間過長

2、MySQL增量備份是自上一次備份后增加/變化的文件或者內容

3、特點

沒有重復數據，備份量不大，時間短

恢復需要上次完全備份及完全備份之后所有的增量備份才能恢復，而且要對所有增量備份進行逐個反推恢復

4、MySQL沒有提供直接的增量備份方法

可通過MySQL提供的二進制日志間接實現增量備份

5、MySQL二進制日志對備份的意義

二進制日志保存了所有更新或者可能更新數據庫的操作

二進制日志在啟動MySQL服務器后開始記錄，并在文件達到max_binlog_size所設置的大小或者接收到flush logs命令后重新創建新的日志文件

只需定時執行flush logs方法重新創建新的日志，生成二進制文件序列，并及時把這些日志保存到安全的地方就完成了一個時間段的增量備份

5、增量恢復

1）、一般恢復

將所有備份的二進制日志內容全部恢復

2）、斷點恢復

1、基于位置恢復

數據庫在某一時間點可能既有錯誤的操作也有正確的操作

可以基于精準的位置跳過錯誤的操作

[root@server1 ~]# mkdir -p /opt/bak_sql[root@server1 ~]# mysqldump -uroot -p test2 > /opt/bak_sql/test2-$(date +%F).sql; ###完整備份[root@server1 ~]# vi /etc/my.cnf[mysqld]log_bin=/usr/local/mysql/data/mysql_bin ###開啟增量備份[root@server1 ~]# systemctl restart mysqld[root@server1 ~]# mysqladmin -uroot -p flush-logs ###將二進制日志更新，產生新的日志文件[root@server1 ~]# cd /usr/local/mysql/data/[root@server1 data]# ll ###查詢增量備份結果[root@server1 ~]# mysqlbinlog --no-defaults --base64-output=decode-rows -v /usr/local/mysql/data/mysql_bin.000002 ###查詢該二進制日志內容是否正確[root@server1 ~]# mysqladmin -u root -p flush-logs; ###將二進制日志更新，產生新的日志文件[root@server1 ~]# cd /usr/local/mysql/data/[root@server1 data]# ll ###查詢增量備份結果[root@server1 ~]# mysqlbinlog --no-defaults --base64-output=decode-rows -v /usr/local/mysql/data/mysql_bin.000003 ###查詢該二進制日志內容是否正確mysql> use test2;mysql> drop table aa; ###先刪掉壞的那張表[root@server1 ~]# mysql -u root -p test2 < /opt/bak_sql/test2-2020-10-24.sql ###還原完全備份的數據庫[root@server1 ~]# mysqlbinlog --no-defaults --base64-output=decode-rows -v /usr/local/mysql/data/mysql_bin.000002 ###查詢該二進制日志內容[root@server1 ~]# mysqlbinlog --no-defaults --stop-datetime='2020-10-24 0:55:25' /usr/local/mysql/data/mysql_bin.000002 | mysql -u root -p ###停止錯誤的時間[root@server1 ~]# mysqlbinlog --no-defaults --start-datetime='2020-10-24 0:55:48' /usr/local/mysql/data/mysql_bin.000002 | mysql -u root -p ###開始正確的時間

2、基于時間點恢復

跳過某個發生錯誤的時間點實現數據恢復

前面步驟同基于時間恢復

[root@server1 ~]# mysqlbinlog --no-defaults --base64-output=decode-rows -v /usr/local/mysql/data/mysql_bin.000003 ###查詢該二進制日志內容[root@server1 ~]# mysqlbinlog --no-defaults --stop-position='2168' /usr/local/mysql/data/mysql_bin.000003 | mysql -u root -p ###上一次操作正確的位置點停止[root@server1 ~]# mysqlbinlog --no-defaults --start-position='2537' /usr/local/mysql/data/mysql_bin.000003 | mysql -u root -p ###下一次操作正確的位置點開始

六、主從復制

1、原理

1、Master將用戶對數據庫更新的操作以二進制格式保存到Binary Log日志文件中；

2、Slave上面的IO進程連接上Master，并請求從指定日志文件的指定位置（或者從最開始的日志）之后的日志內容；

3、Master接收來自Slave的IO進程的請求后，通過負責復制的IO進程根據請求信息讀取制定日志指定位置之后的日志信息，返回給Slave的IO進程。返回信息中除了日志所包含的信息之外，還包括本次返回的信息已經到Master端的bin-log文件的名稱以及bin-log的位置；

4、Slave的IO進程接收到信息后，將接收到的日志內容依次添加到Slave端的relay-log文件的最末端，并將讀取到的Master端的bin-log的文件名和位置記錄到master-info文件中，以便在下一次讀取的時候能夠清除的告訴Master“我需要從某個bin-log的哪個位置開始往后的日志內容，請發給我”；

5、Slave的Sql進程檢測到relay-log中新增了內容后，會馬上解析relay-log的內容稱為在Master端真實執行時候的那些可執行的內容，并在自身執行。

故障及解決方法

MySQL復制方式：

半同步復制 --》同步速度慢一點能保證slave同步二進制日志的完整性

異步復制–》同步速度快，但是不能確保二進制日志確實到達slave上

在半同步復制的架構下，當master在將自己binlog發給slave上的時候，要確保slave已經接受到了這個二進制日志以后，才會返回數據給客戶端。

對比兩種架構：異步復制對于用戶來說，可以確保得到快速的響應結構，但是不能確保二進制日志確實到達了slave上；半同步復制對于客戶的請求響應稍微慢點，但是他可以保證二進制日志的完整性。

============================================================================

故障一：

開發設置的crontab計劃任務周期不合理：

感覺前端的頁面（LAMP）打開有點延遲，平時正常都很快就加載出來了，查找原因：

發現數據庫占滿了，因為數據要從數據庫拿，影響了讀取速度；

問題：周期性計劃開發設計不合理，本來1個小時的，結果三分鐘一次寫業務數據到數據庫，導致那個數據庫很大占滿了，

解決方法：通知開發去修改

故障二：

MySQL中主從延遲高，怎么解決？

主從同步的延遲的原因：

我們知道， 一個服務器開放Ｎ個鏈接給客戶端來連接的，這樣有會有大并發的更新操作, 從服務器通過I/O的線程去主服務器同步二進制日志，當某個SQL在從服務器上執行的時間稍長 或者由于某個SQL要進行鎖表就會導致主服務器的SQL大量積壓，未被同步到從服務器里。這就導致了主從不一致，也就是主從延遲。

主從同步延遲的解決辦法：

軟件方面：

因為所有的SQL必須都要在從服務器里面執行一遍，但是主服務器如果不斷的有更新操作源源不斷的寫入，那么一旦有延遲產生，那么延遲加重的可能性就會原來越大。 當然我們可以做一些緩解的措施。

a. 我們知道因為主服務器要負責更新操作， 他對安全性的要求比從服務器高， 所有有些設置可以修改，比如sync_binlog=1，innodb_flush_log_at_trx_commit = 1 之類的設置，而slave則不需要這么高的數據安全，完全可以將sync_binlog設置為0或者關閉binlog，innodb_flushlog，innodb_flush_log_at_trx_commit 也可以設置為0來提高sql的執行效率 這個能很大程度上提高效率。另外就是使用比主庫更好的硬件設備作為slave。

b. 就是把，一臺從服務器當作為備份使用，而不提供查詢，那邊他的負載下來了，執行relay log 里面的SQL效率自然就高了。

硬件方面：

1.主或者從服務器負載過高，給服務器擴容；

2.增加從服務器，這個目的還是分散讀的壓力， 從而降低服務器負載。

3.判斷主從延遲的方法

可以通過 show slave status 進行查看，比如可以看看Seconds_Behind_Master參數的值來判斷，是否有發生主從延時。

其值有這么幾種：

NULL - 表示io_thread或是sql_thread有任何一個發生故障，也就是該線程的Running狀態是No,而非Yes.

0 - 該值為零，是我們極為渴望看到的情況，表示主從復制狀態正常

2、主服務器（Master）

1）、日志文件

修改配置文件

server-id=1 ###mysql服務器id，每個服務器不能相同log_bin=master-bin ###主服務器日志文件log_slave_updates=true ###允許中繼日志讀取主服務器的二進制日志

2）、授權

mysql> grant replication slave on *.* to 'myslave'@'192.168.73.%' identified by 'abc123'; ###為所有從服務器授權所有數據庫mysql> flush privileges;

3）、記錄日志文件及位置點

mysql> show master status; ###記下二進制日志文件及position的值

3、從服務器

1）、日志文件

server-id = 12 ###MySQL服務器的id，需要配置不同數字relay_log=relay-log-bin ###從主服務器上同步日志文件記錄到本地中繼日志relay_log_index=slave-relay-bin.index ###定義中繼日志的索引

2）、與Master同步

mysql> change master to master_host='192.168.73.10',master_user='myslave',master_password='abc123',master_log_file='master-bin.000001',master_log_pos=863;mysql> start slave; ###啟動從服務器mysql> show slave status\G ###查看從服務器狀態...省略Slave_IO_Running: Yes Slave_SQL_Running: Yes ###這兩項需要為YES

3）、主從復制失敗原因

1、I/O線程顯示為NO: 主庫與從庫網絡不通、主庫未授權給從庫、

2、SQL線程顯示為NO：從庫日志和位置點與主不同步

3、若從庫查看連接主庫I/0線程狀態為conneting，一直是這個狀態，考慮雙方的防火墻是否開啟。

七、讀寫分離

1、原理

1、只在主服務器上寫，只在從服務器上讀

2、主數據庫處理事務性查詢，從數據庫處理select查詢

3、數據庫復制用于將事務性查詢的變更同步到集群中的從數據庫

讀寫分離原理示意圖

2、部署amoeba

基于讀寫分離

1）、安裝jdk環境

2）、部署amoeba代理

[root@amoeba opt]# unzip amoeba-mysql-3.0.5-RC-distribution.zip -d /usr/local [root@amoeba ~]# mv /usr/local/amoeba-mysql-3.0.5-RC/ /usr/local/amoeba [root@amoeba ~]# chmod -R 755 /usr/local/amoeba/ [root@amoeba ~]# vim /usr/local/amoeba/jvm.properties 32 #JVM_OPTIONS="-server -Xms256m -Xmx1024m -Xss196k -XX:PermSize=16m -XX:MaxPe rmSize=96m" 33 JVM_OPTIONS="-server -Xms1024m -Xmx1024m -Xss256k"

3）、在MySQL上給amoeba授權

mysql> grant all on *.* to 'test'@'192.168.73.%' identified by 'abc123';

4）、修改配置文件

1、amoeba.xml

1、修改連接amoeba使用的用戶名和密碼

2、給master開放默認池和寫池，給slave開放讀池

2、dbServer.xml

1、指定為amoeba創建的允許讀取數據庫的用戶名和密碼（5.7版本沒有默認的test數據庫）

2、配置三個服務器的主機名和地址

3、指定名為slaves的poolName中pools的主機名

八、MHA高可用及故障切換

1、概述

1、傳統的MySQL主從架構存在的問題

MySQL主服務器出故障后就無法寫入數據了

2、MHA簡介
一套優秀的MySQL高可用環境下故障切換和主從復制的軟件
MySQL故障過程中，MHA能做到0-30秒內自動完成故障切換

3、MHA組成
MHA Manager（管理節點）和 MHA Node（數據節點）
MHA Manager 可以單獨部署在一臺獨立的機器上，管理多個 master-slave 集群（Manger是單獨一臺監控master服務器健康狀態的服務器。）；也可以部署在一臺 slave 節點上。MHA Node 運行在每臺 MySQL 服務器上，MHA Manager 會定時探測集群中的 master 節點。當 master 出現故障時，它可以自動將最新數據的 slave 提升為新的 master，然后將所有其他的 slave 重新指向新的 master。整個故障轉移過程對應用程序完 全透明。

4、MHA特點（優勢）
在 MHA 自動故障切換過程中，MHA 試圖從宕機的主服務器上保存二進制日志，最大程度的保證數據的不丟失，但這并不總是可行的。例如，如果主服務器硬件故障或無法通過 SSH 訪問，MHA 沒法保存二進制日志，就會出現只進行故障轉移但丟失了最新的數據的情況。
使用 MySQL 5.5 的半同步復制，可以大大降低數據丟失的風險。MHA 可以與半同步復 制結合起來。如果只有一個 slave 已經收到了最新的二進制日志，MHA 可以將最新的二進 制日志應用于其他所有的 slave 服務器上，因此可以保證所有節點的數據一致性。

5、MHA架構
目前MHA支持一主多從架構，最少三臺服務，即一主兩從

2、具體配置

1）、配置一主兩從

MySQL主配置文件修改：

[root@Master ~]# vi /etc/my.cnf [mysqld] server-id = 1 log_bin = master-bin log-slave-updates = true

MySQL從服務器配置：

1、主備服務器：

[root@Slave1 ~]# vi /etc/my.cnf[mysqld]server-id = 2log_bin = master-binrelay_log = relay-log-binrelay_log_index = slave-relay-bin.index

2、從服務器

[root@Slave2 ~]# vi /etc/my.cnf[mysqld]server-id = 3relay_log = relay-log-binrelay_log_index = slave-relay-bin.index

2）、MHA軟件安裝

Centos7.6必須安裝0.57版本，所有服務器上必須先安裝node組件，最后在MHA-Manager節點上安裝manager組件，因為manager依賴node組件

3）、配置ssh無密碼認證

4）、配置虛擬IP

[root@Manager ~]# vi /usr/local/bin/master_ip_failover #!/usr/bin/env perl use strict; use warnings FATAL => 'all';use Getopt::Long;my ( $command, $ssh_user, $orig_master_host, $orig_master_ip, $orig_master_port, $new_master_host, $new_master_ip, $new_master_port ); #############################添加內容部分######################################### my $vip = '192.168.73.200'; my $brdc = '192.168.73.255'; my $ifdev = 'ens33'; my $key = '1'; my $ssh_start_vip = "/sbin/ifconfig ens33:$key $vip"; my $ssh_stop_vip = "/sbin/ifconfig ens33:$key down"; my $exit_code = 0; #my $ssh_start_vip = "/usr/sbin/ip addr add $vip/24 brd $brdc dev $ifdev label $ifdev:$key;/usr/sbin/arping -q -A -c 1 -I $ifdev $vip;iptables -F;"; #my $ssh_stop_vip = "/usr/sbin/ip addr del $vip/24 dev $ifdev label $ifdev:$key"; ################################################################################## GetOptions( 'command=s' => \$command, 'ssh_user=s' => \$ssh_user, 'orig_master_host=s' => \$orig_master_host, 'orig_master_ip=s' => \$orig_master_ip, 'orig_master_port=i' => \$orig_master_port, 'new_master_host=s' => \$new_master_host, 'new_master_ip=s' => \$new_master_ip, 'new_master_port=i' => \$new_master_port, );exit &main();sub main {print "\n\nIN SCRIPT TEST====$ssh_stop_vip==$ssh_start_vip===\n\n";if ( $command eq "stop" || $command eq "stopssh" ) {my $exit_code = 1; eval { print "Disabling the VIP on old master: $orig_master_host \n"; &stop_vip(); $exit_code = 0; }; if ($@) { warn "Got Error: $@\n"; exit $exit_code; } exit $exit_code; } elsif ( $command eq "start" ) {my $exit_code = 10; eval { print "Enabling the VIP - $vip on the new master - $new_master_host \n"; &start_vip(); $exit_code = 0; }; if ($@) { warn $@; exit $exit_code; } exit $exit_code; } elsif ( $command eq "status" ) { print "Checking the Status of the script.. OK \n"; exit 0; } else { &usage(); exit 1; } } sub start_vip() { `ssh $ssh_user\@$new_master_host \" $ssh_start_vip \"`; } # A simple system call that disable the VIP on the old_master sub stop_vip() { `ssh $ssh_user\@$orig_master_host \" $ssh_stop_vip \"`; }sub usage { print "Usage: master_ip_failover --command=start|stop|stopssh|status --orig_master_host=host --orig_master_ip=ip --orig_master_port=port --new_master_host=host --new_master_ip=ip --new_master_port=port\n"; }

5）、配置文件解析

[server default]manager_log=/var/log/masterha/app1/manager.log #manager日志manager_workdir=/var/log/masterha/app1 #manager工作目錄master_binlog_dir=/usr/local/mysql/data #master保存binlog的位置master_ip_failover_script=/usr/local/bin/master_ip_failover #設置自動failover時候的切換腳本master_ip_online_change_script=/usr/local/bin/master_ip_online_change #設置手動切換時的切換腳本password=manager #設置mysql中root用戶的密碼，這個密碼是前面創建監控用戶的密碼ping_interval=1 #設置監控主庫，發送ping包的時間間隔，默認是3秒，嘗試三次沒有回應的時候自動進行failoverremote_workdir=/tmp #設置遠端mysql在發生切換時binlog的保存位置repl_password=abc123 #設置復制用戶的密碼repl_user=myslave #設置復制用戶的賬戶report_script=/usr/local/send_report #設置發生切換后發送的報警的腳本secondary_check_script=/usr/local/bin/masterha_secondary_check -s 14.0.0.30 -s 14.0.0.40shutdown_script="" #設置故障發生后關閉故障主機腳本（該腳本的主要作用是關閉主機防止發生腦裂）ssh_user=root #設置ssh的登錄用戶名user=mha #設置監控用戶

6）、ssh無密碼認證測試

7）、MHA健康檢查

8）、在MHA的master節點開啟虛擬IP

九、SQL高級語句

1、按關鍵字排序（order by）

1、使用order by語句來實現排序
2、排序可針對一個或多個字段
3、ASC：升序，默認排序方式
4、DESC：降序
5、order by的語法結構

select 字段1，字段2 from 表名 order by 字段1 desc|asc，字段2 desc|asc；

2、對結果進行分組

1、使用group by語句來實現分組
2、通常結合聚合函數一起使用
3、可以按一個或多個字段對結果進行分組
4、group by的語法結構

select count(name),score from a where score > 80 group by score;

3、限制結果條目

1、只返回select查詢結果的第一行或前幾行
2、使用limit語句限制條目
3、limit語法結構

offset：位置偏移量，從0開始
number：返回記錄行的最大數目

select 字段1，字段2 from 表名 limit [offset,] number；

offset：位置偏移量，從0開始
number：返回記錄行的最大數目

例子：

select * from a limit 2,3; ##從索引2開始，顯示3行

4、設置別名

1、使用as語句設置別名，關鍵字as可省略
2、設置別名時，保證不能與庫中其他表或字段名稱沖突
3、別名的語法結構
字段別名：

select 字段 as 別名 from 表名；

表的別名：

select 字段 from 表名 as 別名；

5、通配符

1、用于替換字符串中的部分字符
2、通常配合like一起使用，并協同where完成查詢
3、常用通配符

% 表示零個、一個或多個即任意字符_ 表示單個字符

6、子查詢

1、也稱作內查詢或者嵌套查詢
2、先于主查詢被執行，其結果將作為外層主查詢的條件
3、在增刪改查中都可以使用子查詢
4、支持多層嵌套
5、in語句用來判斷某個值是否在給定的結果集中

例： select id,name from a where id in(1,2);select * from a where id in(select id from a where score>80);

7、null值

null：真空（什么都沒有）
‘’：空氣（還有空氣）
1、表示缺失的值
2、與數字0或者空白（spaces）是不同的
3、使用is null或is not null進行判斷
4、null值和空值（’’）的區別
空值長度為0，不占空間；null值的長度為null，占用空間
is null無法判斷空值
空值使用“=”或者“<>”來處理
count()計算時，null會忽略，空值會加入計算

8、正則表達式

^匹配文本的開始字符

$	匹配文本的結束字符
.	匹配任何單個字符
*	匹配前面的字符零次或多次
+	匹配前面的字符一次或多次
字符串	匹配包含指定的字符串
p1lp2	匹配p1或p2
[…]	匹配字符集合中任一字符
[^…]	匹配不在括號中的任一字符
{n}	匹配前面的字符串n次
{n，m}	匹配前面的字符串至少n次，之多m次

9、運算符

比較運算符

邏輯運算符

（1）邏輯非

邏輯非將跟在它后面的邏輯測試取反，把真變為假，把假變為真。
如果 NOT 后面的操作數為 0 時，所得值為 1；如果操作數為非 0 時，所得值為 0；如果操作數為 NULL 時，所得值為 NULL。
注意：非0值都是1

（2）邏輯與

如果所有值都是真返回 1，否則返回 0。

（3）邏輯或（最好用or）

邏輯或表示包含的操作數，任意一個為非零值并且不是 NULL 值時，返回 1，否則返回0。

（4）邏輯異或

兩個非 NULL 值的操作數，如果兩者都是 0 或者都是非 0，則返回 0；如果一個為 0， 另一個為非 0，則返回結果為 1；
當任意一個值為 NULL 時，返回值為 NULL。

運算總結：
and運算，只要碰到0就是0，（非0和null是null）
or運算，只要碰到非0值就是1，（0和null是null）
異或運算，只要碰到null都是null

位運算符

位運算符實際上是對二進制數進行計算的運算符。


位運算方法：
按位與運算
10–》1010
15–》1111
1010 --》10
按位與運算（&），是對應的二進制位都是 1 的，它們的運算結果為 1，否則為 0

按位或運算
10–》1010
15–》1111
1111–》15
按位或運算（|），是對應的二進制位只要是 1 的，它們的運算結果就為 1，否則為 0

按位異或運算
10–》1010
15–》1111
0101–》5
按位異或運算（^），是對應的二進制位不相同時，運算結果 1，否則為 0

按位取反運算
1–》0001
~1–》1110
5–》0101
0100–》4
按位取反（~），是對應的二進制數逐位反轉，即 1 取反后變為 0, 0 取反后變為 1

按位左移運算
1<<2
1–》0001
按位左移2位，空缺處補0
0100–》4

10<<3
10–》1010
按位左移3位，空缺處補0
1010000–》80
按位右移運算
10>>2
10–》1010
按位右移2位，多余的位數直接刪除
0010–》2

15>>2
15–》1111
按位右移2位，多余的位數直接刪除
0011–》3
常用的運算符優先級

10、連接查詢

通常都是將來自兩個或多個表的行結合起來，基于這些表之間的共同字段，進行數據的拼接。
要先確定一個主表作為結果集，然后將其他表的行有選擇性的連接到選定的主表結果集上。

使用較多的連接查詢包括：內連接、左連接和右連接

1、內連接

在from子句中使用關鍵字 inner join 來連接多張表，并使用 on子句設置連接條件。
mysql> select info.name,hob.hobbyname from info inner join hob on info.hobby=hob.id;
內連接是系統默認的表連接，所以在 FROM 子句后可以省略 INNER 關鍵字，只使用關鍵字 JOIN。同時有多個表時，也可以連續使用 INNER JOIN 來實現多表的內連接，不過為了更好的性能，建議最好不要超過三個表。

2、外連接

左連接，主表在左邊，主表內容會全部顯示出來，在從表中沒匹配到的以NULL顯示出來

右連接，主表在右邊，主表內容會全部顯示出來，在從表中沒匹配到的以NULL顯示出來

十、MySQL函數

數據庫函數

常用的函數分類
1、數學函數
2、聚合函數
3、字符串函數
4、日期時間函數

1、常用的數學函數

abs(x)：返回x的絕對值 rand()：返回0到1的隨機數（0-0.9999…，1是取不到的） mod(x,y)：返回x除以y以后的余數 power(x,y)：返回x的y次方 round(x)：返回離x最近的整數（四舍五入，只看小數點后第一位） round(x,y)：保留x的y位小數四舍五入后的值 sqrt(x)：返回x的平方根 truncate(x,y)：返回數字x截斷為y位小數的值 ceil(x)：返回大于或等于x的最小整數（向上取整數） floor(x)：返回小于或等于x的最大整數（向下取整數） greatest(x1,x2…)：返回集合中最大的值 least(x1,x2…)：返回集合中最小的值

abs(x)：返回x的絕對值

rand()：返回0到1的隨機數（0-0.9999…，1是取不到的）

mod(x,y)：返回x除以y以后的余數

power(x,y)：返回x的y次方

round(x)：返回離x最近的整數（四舍五入，只看小數點后第一位）

round(x,y)：保留x的y位小數四舍五入后的值

sqrt(x)：返回x的平方根

truncate(x,y)：返回數字x截斷為y位小數的值

ceil(x)：返回大于或等于x的最小整數（向上取整數）

floor(x)：返回小于或等于x的最大整數（向下取整數）

greatest(x1,x2…)：返回集合中最大的值

least(x1,x2…)：返回集合中最小的值

2、聚合函數

●對表中數據記錄進行集中概括而設計的一類函數
●常用的聚合函數（只會產生一個值）

avg(字段名) 返回指定字段的平均值count(字段名) 返回指定字段中非NULL值的個數 min(字段名) 返回指定字段的最小值max(字段名) 返回指定字段的最大值sum(字段名) 返回指定字段的所有值之和

avg(字段名) 返回指定字段的平均值

count(字段名) 返回指定字段中非NULL值的個數

min(字段名) 返回指定字段的最小值

max(字段名) 返回指定字段的最大值

[外鏈圖片轉存失敗,源站可能有防盜鏈機制,建議將圖片保存下來直接上傳(img-C02oOP4Q-1635472794014)(https://gitee.com/julyjo/blogimage/raw/master/img/20210127104234.png)]

sum(字段名) 返回指定字段的所有值之和

3、字符串函數

●常用的字符串函數

length(x)：返回字符串x的長度（空格也算）trim()：返回去除指定格式的值（只能去除前后的空格）concat(x,y)：將提供的參數x和y拼接成一個字符串upper(x)：將字符串x的所有字母變成大寫字母lower(x)：將字符串x的所有字母變成小寫字母left(x,y)：返回字符串x的前y個字符right(x,y)：返回字符串x的后y個字符repeat(x,y)：將字符串x重復y次space(x)：返回x個空格（結合concat使用）replace(x,y,z)：將字符串z替代字符串x中的字符串ystrcmp(x,y)：比較x和y，返回的值可以為-1 <，0 =，1 >substring(x,y,z)：獲取從字符串x中的第y個位置開始長度為z的字符串reverse(x)：將字符串x反轉

length(x)：返回字符串x的長度（空格也算）

trim()：返回去除指定格式的值（只能去除前后的空格）

concat(x,y)：將提供的參數x和y拼接成一個字符串

upper(x)：將字符串x的所有字母變成大寫字母

lower(x)：將字符串x的所有字母變成小寫字母

left(x,y)：返回字符串x的前y個字符

right(x,y)：返回字符串x的后y個字符

repeat(x,y)：將字符串x重復y次

space(x)：返回x個空格（結合concat使用）

replace(x,y,z)：將字符串z替代字符串x中的字符串y

strcmp(x,y)：比較x和y，返回的值可以為-1 <，0 =，1 >

substring(x,y,z)：獲取從字符串x中的第y個位置開始長度為z的字符串

格式：substring(完整字符串,起始位置,長度)； ##起始位置從1開始

reverse(x)：將字符串x反轉

4、日期時間函數

●常用的日期時間函數

curdate()：返回當前時間的年月日curtime()：返回當前時間的時分秒now()：返回當前時間的日期和時間month(x)：返回日期x中的月份值hour(x)：返回x中的小時值minute(x)：返回x中的分鐘值second(x)：返回x中的秒鐘值dayofweek(x)：返回x是星期幾，1星期日，2星期一，3星期二…dayofmonth(x)：計算日期x是本月的第幾天dayofyear(x)：計算日期x是本年的第幾天

curdate()：返回當前時間的年月日

curtime()：返回當前時間的時分秒

now()：返回當前時間的日期和時間

month(x)：返回日期x中的月份值

hour(x)：返回x中的小時值

minute(x)：返回x中的分鐘值

second(x)：返回x中的秒鐘值

dayofweek(x)：返回x是星期幾，1星期日，2星期一，3星期二…

dayofmonth(x)：計算日期x是本月的第幾天

dayofyear(x)：計算日期x是本年的第幾天

十一、MySQL存儲過程

1、存儲過程簡介

1、從 5.0 版本才開始支持
2、是一組為了完成特定功能的SQL語句集合（封裝）
3、比傳統SQL速度更快、執行效率更高
4、存儲過程的優點
執行一次后，會將生成的二進制代碼駐留緩沖區（便于下次執行），提高執行效率
SQL語句加上控制語句的集合，靈活性高
在服務器端存儲，客戶端調用時，降低網絡負載
可多次重復被調用，可隨時修改，不影響客戶端調用
可完成所有的數據庫操作，也可控制數據庫的信息訪問權限
5、為什么要用存儲過程？
1.減輕網絡負載；2.增加安全性

傳統SQL訪問MySQL服務端過程：

[外鏈圖片轉存失敗,源站可能有防盜鏈機制,建議將圖片保存下來直接上傳(img-qE1GkThT-1635472794030)(C:/%E6%9C%89%E9%81%93/%E7%AC%94%E8%AE%B0/weixinobU7Vjr77MsBJ6yQf29GVWik52DI/713d719be6ee4ed9857fc02c1efabc16/clipboard.png)]

使用存儲過程訪問MySQL服務端：

[外鏈圖片轉存失敗,源站可能有防盜鏈機制,建議將圖片保存下來直接上傳(img-0gMQqboW-1635472794030)(C:/%E6%9C%89%E9%81%93/%E7%AC%94%E8%AE%B0/weixinobU7Vjr77MsBJ6yQf29GVWik52DI/4a6f1d5a1d82492aaef0fbc523945aa0/clipboard.png)]

2、創建存儲過程

1、使用create procedure語句創建存儲過程
2、參數分為
輸入參數：in
輸出參數：out
輸入/輸出參數：inout
3、存儲過程的主體部分，被稱為過程體；以begin開始，以end$$結束
4、具體格式

delimiter $$create procedure 存儲過程名（in 參數名 參數類型）begin#定義變量declare 變量名 變量類型#變量賦值set 變量名 = 值 sql 語句1； sql 語句2； ...end$$delimiter ；（有空格）123456789101112

示例：

3、調用存儲過程

call 存儲過程名(實際參數);1

4、查詢存儲過程

show procedure status where db='數據庫'；1

5、修改存儲過程

存儲過程的修改分為特征的修改和業務內容的修改。
特征的修改語法結構如下：

alter procedure 存儲過程名 [ <特征> … ]1

存儲過程內容的修改方法是通過刪除原有存儲過程，之后再以相同的名稱創建新的存儲過程。

6、刪除存儲過程

刪除存儲過程的語法：

drop {procedure|function|if exits} <過程名>1

7、傳遞參數過程示例：

1、

2、

總結：in和inout參數會將全局變量的值傳入存儲過程中，而out參數不會將全局變量的值傳入存儲過程中。在存儲過程使用中，參數值in，out，inout都會發生改變。

3、

總結：調用完存儲過程后，發現in參數不會對全局變量的值引起變化，而out和inout參數調用完存儲過程后，會對全局變量的值產生變化，會將存儲過程引用后的值賦值給全局變量。

in參數賦值類型可以是變量還有定值，而out和inout參數賦值類型必須為變量。

十二、死鎖＋慢查詢

MySQL引擎默認的鎖級別：

MyISAM和MEMORY采用表級鎖(table-level locking)。

BDB采用頁面鎖(page-level locking)或表級鎖，默認為頁面鎖。

InnoDB支持行級鎖(row-level locking)和表級鎖，默認為行級鎖。

Innodb引擎中的行鎖與表鎖

在Innodb引擎中既支持行鎖也支持表鎖，那么什么時候會鎖住整張表，什么時候或只鎖住一行呢？

InnoDB行鎖是通過給索引上的索引項加鎖來實現的，這一點MySQL與Oracle不同，Oracle者是通過在數據塊中對相應數據行加鎖來實現的。

InnoDB這種行鎖實現特點意味著：只有通過索引條件檢索數據，InnoDB才使用行級鎖，否則，InnoDB將使用表鎖！

在實際應用中，要特別注意InnoDB行鎖的這一特性，不然的話，可能導致大量的鎖沖突，從而影響并發性能。

行級鎖都是基于索引的，如果一條SQL語句用不到索引是不會使用行級鎖的，會使用表級鎖。

行級鎖的缺點是：如果并發請求大量的鎖資源，所以速度慢，內存消耗大。

1、行級鎖與死鎖

MyISAM中是不會產生死鎖的，因為MyISAM總是一次性獲得所需的全部鎖，要么全部滿足，要么全部等待。而在InnoDB中，鎖是逐步獲得的，就造成了死鎖的可能。

在MySQL中，行級鎖并不是直接鎖記錄，而是鎖索引。索引分為主鍵索引和非主鍵索引兩種，如果一條sql語句操作了主鍵索引，MySQL就會鎖定這條主鍵索引；如果一條語句操作了非主鍵索引，MySQL會先鎖定該非主鍵索引，再鎖定相關的主鍵索引。 在UPDATE、DELETE操作時，MySQL不僅鎖定WHERE條件掃描過的所有索引記錄，而且會鎖定相鄰的鍵值，即所謂的next-key locking。

當兩個事務同時執行，一個鎖住了主鍵索引，在等待其他相關索引。另一個鎖定了非主鍵索引，在等待主鍵索引。這樣就會發生死鎖。

發生死鎖后，InnoDB一般都可以檢測到，并使一個事務釋放鎖回退，另一個獲取鎖完成事務。

如何怎么避免死鎖？

1）以固定的順序訪問表和行。

分為兩種情景：

對于不同事務訪問不同的表，盡量做到訪問表的順序一致；

對于不同事務訪問相同的表，盡量對記錄的id做好排序，執行順序一致；

2）大事務拆小。大事務更傾向于死鎖，如果業務允許，將大事務拆小。

3）在同一個事務中，盡可能做到一次鎖定所需要的所有資源，減少死鎖概率。

4）降低隔離級別。如果業務允許，將隔離級別調低也是較好的選擇，比如將隔離級別從RR調整為RC，可以避免掉很多因為gap鎖造成的死鎖。

5）為表添加合理的索引?？梢钥吹饺绻蛔咚饕龑楸淼拿恳恍杏涗浱砑由湘i，死鎖的概率大大增大。

怎么解決死鎖？

第一步，查出已鎖的進程

查看正在鎖的事務

SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS;

查看等待鎖的事務

SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS;

INNODB_TRX表主要是包含了正在InnoDB引擎中執行的所有事務的信息，包括waiting for a lock和running的事務

select * from information_schema.innodb_trx

第二步，kill進程

2、慢查詢：

https://blog.csdn.net/weixin_42030357/article/details/104105932

進行SQL優化的手段也主要是修改SQL寫法，或者新增索引。

（1）數據庫中設置SQL慢查詢

一、第一步.開啟mysql慢查詢

方式一：

? 修改配置文件在 my.cnf增加幾行: 主要是慢查詢的定義時間（超過2秒就是慢查詢），以及慢查詢log日志記錄（ slow_query_log）

方法二：通過MySQL數據庫開啟慢查詢:

（2）分析慢查詢日志

? 直接分析mysql慢查詢日志 ,利用explain關鍵字可以模擬優化器執行SQL查詢語句，來分析sql慢查詢語句

例如：執行EXPLAIN SELECT * FROM res_user ORDER BYmodifiedtime LIMIT 0,1000得到如下結果： 顯示結果分析：

table | type | possible_keys | key |key_len | ref | rows | Extra EXPLAIN列的解釋： table 顯示這一行的數據是關于哪張表的 type 這是重要的列，顯示連接使用了何種類型。從最好到最差的連接類型為const、eq_reg、ref、range、indexhe和ALL rows 顯示需要掃描行數key 使用的索引

（3）常見的慢查詢優化

①、索引沒起作用的情況

a、使用LIKE關鍵字的查詢語句在使用LIKE關鍵字進行查詢的查詢語句中，如果匹配字符串的第一個字符為“%”，索引不會起作用。只有“%”不在第一個位置索引才會起作用。2、使用多列索引的查詢語句MySQL可以為多個字段創建索引。一個索引最多可以包括16個字段。對于多列索引，只有查詢條件使用了這些字段中的第一個字段時，索引才會被使用。

②、優化數據庫結構

合理的數據庫結構不僅可以使數據庫占用更小的磁盤空間，而且能夠使查詢速度更快。數據庫結構的設計，需要考慮數據冗余、查詢和更新的速度、字段的數據類型是否合理等多方面的內容。a、將字段很多的表分解成多個表對于字段比較多的表，如果有些字段的使用頻率很低，可以將這些字段分離出來形成新表。因為當一個表的數據量很大時，會由于使用頻率低的字段的存在而變慢。b、增加中間表對于需要經常聯合查詢的表，可以建立中間表以提高查詢效率。通過建立中間表，把需要經常聯合查詢的數據插入到中間表中，然后將原來的聯合查詢改為對中間表的查詢，以此來提高查詢效率。

③、分解關聯查詢

將一個大的查詢分解為多個小查詢是很有必要的。 很多高性能的應用都會對關聯查詢進行分解，就是可以對每一個表進行一次單表查詢，然后將查詢結果在應用程序中進行關聯，很多場景下這樣會更高效，例如： SELECT * FROM tag JOIN tag_post ON tag_id = tag.id JOIN post ON tag_post.post_id = post.id WHERE tag.tag = 'mysql'; 分解為： SELECT * FROM tag WHERE tag = 'mysql';SELECT * FROM tag_post WHERE tag_id = 1234;SELECT * FROM post WHERE post.id in (123,456,567);

④、優化LIMIT分頁

在系統中需要分頁的操作通常會使用limit加上偏移量的方法實現，同時加上合適的order by 子句。如果有對應的索引，通常效率會不錯，否則MySQL需要做大量的文件排序操作。

一個非常令人頭疼問題就是當偏移量非常大的時候，例如可能是limit 10000,20這樣的查詢，這是mysql需要查詢10020條然后只返回最后20條，前面的10000條記錄都將被舍棄，這樣的代價很高。

? 優化此類查詢的一個最簡單的方法是盡可能的使用索引覆蓋掃描，而不是查詢所有的列。然后根據需要做一次關聯操作再返回所需的列。對于偏移量很大的時候這樣做的效率會得到很大提升。

對于下面的查詢：

? select id,title from collect limit 90000,10;

該語句存在的最大問題在于limit M,N中偏移量M太大（我們暫不考慮篩選字段上要不要添加索引的影響），導致每次查詢都要先從整個表中找到滿足條件 的前M條記錄，之后舍棄這M條記錄并從第M+1條記錄開始再依次找到N條滿足條件的記錄。如果表非常大，且篩選字段沒有合適的索引，且M特別大那么這樣的代價是非常高的。 試想，如我們下一次的查詢能從前一次查詢結束后標記的位置開始查找，找到滿足條件的100條記錄，并記下下一次查詢應該開始的位置，以便于下一次查詢能直接從該位置 開始，這樣就不必每次查詢都先從整個表中先找到滿足條件的前M條記錄，舍棄，在從M+1開始再找到100條滿足條件的記錄了。

方法一：慮篩選字段（title）上加索引

? title字段加索引 （此效率如何未加驗證）

方法二：先查詢出主鍵id值

select id,title from collect where id>=(select id from collect order by id limit 90000,1) limit 10;

原理：先查詢出90000條數據對應的主鍵id的值，然后直接通過該id的值直接查詢該id后面的數據。

方法三：關延遲聯”

如果這個表非常大，那么這個查詢可以改寫成如下的方式：

Select news.id, news.description from news inner join (select id from news order by title limit 50000,5) as myNew using(id);

? 這里的**“關延遲聯”**將大大提升查詢的效率，它讓MySQL掃描盡可能少的頁面，獲取需要的記錄后再根據關聯列回原表查詢需要的所有列。這個技術也可以用在優化關聯查詢中的limit。

十三、優化范圍

MySQL 服務器硬件和操作系統調節

1. 擁有足夠的物理內存來把整個InnoDB文件加載到內存中——在內存中訪問文件時的速度要比在硬盤中訪問時快的多.2. 不惜一切代價避免使用Swap交換分區 – 交換時是從硬盤讀取的，它的速度很慢.3. 使用電池供電的RAM（注：RAM即隨機存儲器）.4. 使用高級的RAID（注：Redundant Arrays of Inexpensive Disks，即磁盤陣列） – 最好是RAID10或更高.5. 避免RAID5（注：一種存儲性能、數據安全和存儲成本兼顧的存儲解決方案） – 確保數據庫完整性的校驗是要付出代價的.6. 將操作系統和數據分區分開，不僅僅是邏輯上，還包括物理上 – 操作系統的讀寫操作會影響數據庫的性能.7. 把MySQL臨時空間和復制日志與數據放到不同的分區 – 當數據庫后臺從磁盤進行讀寫操作時會影響數據庫的性能.8. 更多的磁盤空間等于更快的速度.9. 更好更快的磁盤.10. 使用SAS（注： Serial Attached SCSI，即串行連接SCSI）代替SATA（注：SATA，即串口硬盤）.11. 較小的硬盤 比 較大的硬盤快，尤其是在RAID配置的情況下.12. 使用電池支持的高速緩存RAID控制器.13. 避免使用軟件磁盤陣列.14. 考慮為數據分區使用固態IO卡 (不是磁盤驅動器) – 這些卡能夠為幾乎任何數量的數據支持2GB/s的寫入速度.15. 在[Linux](http://www.ttlsa.com/linux/)中設置swappiness的值為0 – 在數據庫服務器中沒有理由緩存文件，這是一個服務器或臺式機的優勢.16. 如果可以的話，使用 noatime 和 nodirtime 掛載文件系統 – 沒有理由更新訪問數據庫文件的修改時間.17. 使用 XFS 文件系統 – 一種比ext3更快、更小的文件系統，并且有許多日志選項， 而且ext3 已被證實與MySQL有雙緩沖問題.18. 調整 XFS 文件系統日志和緩沖變量 – 為了最高性能標準.19. 在 Linux 系統中, 使用 NOOP 或者 DEADLINE IO 定時調度程序 – 同 NOOP 和 DEADLINE定時調度程序相比，這個 CFQ 和 ANTICIPATORY 定時調度程序 顯得非常慢.20. 使用64位的操作系統 – 對于MySQL，會有更大的內存支持和使用.21. 刪除服務器上未使用的安裝包和守護進程 – 更少的資源占用.22. 把使用MySQL的host和你的MySQL host放到一個hosts文件中 – 沒有DNS查找.23. 切勿強制殺死一個MySQL進程 – 你會損壞數據庫和正在運行備份的程序.24. 把服務器貢獻給MySQL – 后臺進程和其他服務能夠縮短數據庫占用CPU的時間.

MySQL 配置

25. 當寫入時，使用 innodb_flush_method=O_DIRECT 來避免雙緩沖.26. 避免使用 O_DIRECT 和 EXT3 文件系統 – 你將序列化所有要寫入的.27. 分配足夠的 innodb_buffer_pool_size 來加載整個 InnoDB 文件到內存中– 少從磁盤中讀取.28. 不要將 innodb_log_file_size 參數設置太大， 這樣可以更快同時有更多的磁盤空間 – 丟掉多的日志通常是好的，在數據庫崩潰后可以降低恢復數據庫的時間.29. 不要混用 innodb_thread_concurrency 和 thread_concurrency 參數– 這2個值是不兼容的.30. 分配一個極小的數量給 max_connections 參數 – 太多的連接會用盡RAM并鎖定MySQL服務.31. 保持 thread_cache 在一個相對較高的數字，大約 16 – 防止打開連接時緩慢.32. 使用skip-name-resolve參數 – 去掉 DNS 查找.33. 如果你的查詢都是重復的，并且數據不常常發生變化，那么可以使用查詢緩存. 但是如果你的數據經常發生變化，那么使用查詢緩存會讓你感到失望.34. 增大temp_table_size值，以防止寫入磁盤35. 增大max_heap_table_size值，以防止寫入磁盤36. 不要把sort_buffer_size值設置的太高，否則的話你的內存將會很快耗盡37. 根據key_read_requests和key_reads值來決定key_buffer的大小，一般情況下key_read_requests應該比key_reads值高，否則你不能高效的使用key_buffer38. 將innodb_flush_log_at_trx_commit設置為0將會提高性能，但是如果你要保持默認值（1）的話，那么你就要確保數據的完整性，同時你也要確保復制不會滯后.39. 你要有一個測試環境，來測試你的配置，并且在不影響正常生產的情況下，可以常常進行重啟.

MySQL模式優化

40. 保持你的數據庫整理性.41. 舊數據歸檔 - 刪除多余的行返回或搜索查詢.42. 將您的數據加上索引.43. 不要過度使用索引，比較與查詢.44. 壓縮文字和BLOB數據類型 - 以節省空間和減少磁盤讀取次數.45. UTF 8和UTF16都低于latin1執行效率.46. 有節制地使用觸發器.47. 冗余數據保持到最低限度 - 不重復不必要的數據.48. 使用鏈接表，而不是擴展行.49. 注意數據類型，在您的真實數據中，盡可能使用最小的一個.50. 如果其他數據經常被用于查詢時，而BLOB / TEXT數據不是，就把BLOB / TEXT數據從其他數據分離出來.51. 檢查和經常優化表.52. 經常重寫InnoDB表優化.53. 有時，當添加列時刪除索引，然后在添加回來索引，這樣就會更快.54. 針對不同的需求，使用不同的存儲引擎.55. 使用歸檔存儲引擎日志表或審計表-這是更有效地寫道.56. 會話數據存儲在緩存（[memcache](http://www.ttlsa.com/nosql/memcache/)）的而不是MySQL中 - 緩存允許自動自動填值的，并阻止您創建難以讀取和寫入到MySQL的時空數據.57. 存儲可變長度的字符串時使用VARCHAR而不是CHAR - 節省空間，因為固定長度的CHAR，而VARCHAR長度不固定（UTF8不受此影響）.58. 逐步進行模式的變化 - 一個小的變化，可以有巨大的影響.59. 在開發環境中測試所有模式，反映生產變化.60. 不要隨意更改你的配置文件中的值，它可以產生災難性的影響.61. 有時候，在MySQL的configs少即是多.62. 有疑問時使用一個通用的MySQL配置文件.

查詢優化

63. 使用慢查詢日志去發現慢查詢.64. 使用執行計劃去判斷查詢是否正常運行.65. 總是去測試你的查詢看看是否他們運行在最佳狀態下 –久而久之性能總會變化.66. 避免在整個表上使用count(*),它可能鎖住整張表.67. 使查詢保持一致以便后續相似的查詢可以使用查詢緩存.68. 在適當的情形下使用GROUP BY而不是DISTINCT.69. 在WHERE, GROUP BY和ORDER BY子句中使用有索引的列.70. 保持索引簡單,不在多個索引中包含同一個列.71. 有時候MySQL會使用錯誤的索引,對于這種情況使用USE INDEX.72. 檢查使用SQL_MODE=STRICT的問題.73. 對于記錄數小于5的索引字段，在UNION的時候使用LIMIT不是是用OR.74. 為了 避免在更新前SELECT，使用INSERT ON DUPLICATE KEY或者INSERT IGNORE ,不要用UPDATE去實現.75. 不要使用 MAX,使用索引字段和ORDER BY子句.76. 避免使用ORDER BY RAND().77. LIMIT M，N實際上可以減緩查詢在某些情況下，有節制地使用.78. 在WHERE子句中使用UNION代替子查詢.79. 對于UPDATES（更新），使用 SHARE MODE（共享模式），以防止獨占鎖.80. 在重新啟動的MySQL，記得來溫暖你的數據庫，以確保您的數據在內存和查詢速度快.81. 使用DROP TABLE，CREATE TABLE DELETE FROM從表中刪除所有數據.82. 最小化的數據在查詢你需要的數據，使用*消耗大量的時間.83. 考慮持久連接，而不是多個連接，以減少開銷.84. 基準查詢，包括使用服務器上的負載，有時一個簡單的查詢可以影響其他查詢.85. 當負載增加您的服務器上，使用SHOW PROCESSLIST查看慢的和有問題的查詢.86. 在開發環境中產生的鏡像數據中 測試的所有可疑的查詢.

MySQL 備份過程

87. 從二級復制服務器上進行備份.88. 在進行備份期間停止復制，以避免在數據依賴和外鍵約束上出現不一致.89. 徹底停止MySQL，從數據庫文件進行備份.90. 如果使用 MySQL dump進行備份，請同時備份二進制日志文件 – 確保復制沒有中斷.91. 不要信任LVM 快照 – 這很可能產生數據不一致，將來會給你帶來麻煩.92. 為了更容易進行單表恢復，以表為單位導出數據 – 如果數據是與其他表隔離的.93. 當使用mysqldump時請使用 –opt.94. 在備份之前檢查和優化表.95. 為了更快的進行導入，在導入時臨時禁用外鍵約束.96. 為了更快的進行導入，在導入時臨時禁用唯一性檢測.97. 在每一次備份后計算數據庫，表以及索引的尺寸，以便更夠監控數據尺寸的增長.98. 通過自動調度腳本監控復制實例的錯誤和延遲.99. 定期執行備份.100. 定期測試你的備份.101: 執行MySQL 監控: Monitis Unveils The World’s First Free On-demand MySQL Monitoring.

總結

以上是生活随笔為你收集整理的Mysql 概述的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。