MySQL高級

2021 年 3 月 7 日
筆記
Python 高級

閱讀目錄：

一.視圖

1. 問題

對於複雜的查詢，往往是有多個數據表進行關聯查詢而得到，如果數據庫因為需求等原因發生了改變，為了保證查詢出來的數據與之前相同，則需要在多個地方進行修改，維護起來非常麻煩

解決辦法：定義視圖

2. 視圖是什麼

通俗的講，視圖就是一條SELECT語句執行後返回的結果集。所以我們在創建視圖的時候，主要的工作就落在創建這條SQL查詢語句上。

視圖是對若干張基本表的引用，一張虛表，查詢語句執行的結果，不存儲具體的數據（基本表數據發生了改變，視圖也會跟着改變）；

方便操作，特別是查詢操作，減少複雜的SQL語句，增強可讀性；

3. 定義視圖

建議以v_開頭

create view 視圖名稱 as select語句;

4. 查看視圖

查看錶會將所有的視圖也列出來

show tables;

5. 使用視圖

視圖的用途就是查詢

select * from v_stu_score;

6. 刪除視圖

drop view 視圖名稱;
例：
drop view v_stu_sco;

7. 視圖demo

8. 視圖的作用

提高了重用性，就像一個函數
對數據庫重構，卻不影響程序的運行
提高了安全性能，可以對不同的用戶
讓數據更加清晰

二.事務

1. 為什麼要有事務

事務廣泛的運用於訂單系統、銀行系統等多種場景

例如：

A用戶和B用戶是銀行的儲戶，現在A要給B轉賬500元，那麼需要做以下幾件事：

檢查A的賬戶餘額>500元；

A 賬戶中扣除500元;

B 賬戶中增加500元;

正常的流程走下來，A賬戶扣了500，B賬戶加了500，皆大歡喜。

那如果A賬戶扣了錢之後，系統出故障了呢？A白白損失了500，而B也沒有收到本該屬於他的500。

以上的案例中，隱藏着一個前提條件：A扣錢和B加錢，要麼同時成功，要麼同時失敗。事務的需求就在於此

所謂事務,它是一個操作序列，這些操作要麼都執行，要麼都不執行，它是一個不可分割的工作單位。

例如，銀行轉帳工作：從一個帳號扣款並使另一個帳號增款，這兩個操作要麼都執行，要麼都不執行。所以，應該把他們看成一個事務。事務是數據庫維護數據一致性的單位，在每個事務結束時，都能保持數據一致性

事務四大特性(簡稱ACID)

原子性(Atomicity)
一致性(Consistency)
隔離性(Isolation)
持久性(Durability)

以下內容出自《高性能MySQL》第三版，了解事務的ACID及四種隔離級有助於我們更好的理解事務運作。

下面舉一個銀行應用是解釋事務必要性的一個經典例子。假如一個銀行的數據庫有兩張表：支票表（checking）和儲蓄表（savings）。現在要從用戶Jane的支票賬戶轉移200美元到她的儲蓄賬戶，那麼至少需要三個步驟：

檢查支票賬戶的餘額高於或者等於200美元。
從支票賬戶餘額中減去200美元。
在儲蓄帳戶餘額中增加200美元。

上述三個步驟的操作必須打包在一個事務中，任何一個步驟失敗，則必須回滾所有的步驟。

可以用START TRANSACTION語句開始一個事務，然後要麼使用COMMIT提交將修改的數據持久保存，要麼使用ROLLBACK撤銷所有的修改。事務SQL的樣本如下：

start transaction;
select balance from checking where customer_id = 10233276;
update checking set balance = balance – 200.00 where customer_id = 10233276;
update savings set balance = balance + 200.00 where customer_id = 10233276;
commit;

一個很好的事務處理系統，必須具備這些標準特性：

原子性（atomicity）

一個事務必須被視為一個不可分割的最小工作單元，整個事務中的所有操作要麼全部提交成功，要麼全部失敗回滾，對於一個事務來說，不可能只執行其中的一部分操作，這就是事務的原子性

一致性（consistency）

數據庫總是從一個一致性的狀態轉換到另一個一致性的狀態。（在前面的例子中，一致性確保了，即使在執行第三、四條語句之間時系統崩潰，支票賬戶中也不會損失200美元，因為事務最終沒有提交，所以事務中所做的修改也不會保存到數據庫中。）

隔離性（isolation）

通常來說，一個事務所做的修改在最終提交以前，對其他事務是不可見的。（在前面的例子中，當執行完第三條語句、第四條語句還未開始時，此時有另外的一個賬戶匯總程序開始運行，則其看到支票帳戶的餘額並沒有被減去200美元。）

持久性（durability）

一旦事務提交，則其所做的修改會永久保存到數據庫。（此時即使系統崩潰，修改的數據也不會丟失。）

事務命令

表的引擎類型必須是innodb類型才可以使用事務，這是mysql表的默認引擎

查看錶的創建語句，可以看到engine=innodb

-- 選擇數據庫
use jing_dong;
-- 查看goods表
show create table goods;

開啟事務，命令如下：

開啟事務後執行修改命令，變更會維護到本地緩存中，而不維護到物理表中

begin;
或者
start transaction;

提交事務，命令如下

將緩存中的數據變更維護到物理表中

commit;

回滾事務，命令如下：

放棄緩存中變更的數據

rollback;

注意

修改數據的命令會自動的觸發事務，包括insert、update、delete
而在SQL語句中有手動開啟事務的原因是：可以進行多次數據的修改，如果成功一起成功，否則一起會滾到之前的數據

提交

為了演示效果，需要打開兩個終端窗口，使用同一個數據庫，操作同一張表（用到之前的jing_dong數據，可以回到mysql第3天中查看）

step1：連接

終端1：查詢商品分類信息

select * from goods_cates;

step2：增加數據

終端2：開啟事務，插入數據

begin;
insert into goods_cates(name) values('小霸王遊戲機');

終端2：查詢數據，此時有新增的數據

select * from goods_cates;

step3：查詢

終端1：查詢數據，發現並沒有新增的數據

select * from goods_cates;

step4：提交

終端2：完成提交

commit;

step5：查詢

終端1：查詢，發現有新增的數據

select * from goods_cates;

回滾

為了演示效果，需要打開兩個終端窗口，使用同一個數據庫，操作同一張表

step1：連接

終端1

select * from goods_cates;

step2：增加數據

終端2：開啟事務，插入數據

begin;
insert into goods_cates(name) values('小霸王遊戲機');

終端2：查詢數據，此時有新增的數據

select * from goods_cates;

step3：查詢

終端1：查詢數據，發現並沒有新增的數據

select * from goods_cates;

step4：回滾

終端2：完成回滾

rollback;

step5：查詢

終端1：查詢數據，發現沒有新增的數據

select * from goods_cates;

三.索引

1. 思考

在圖書館中是如何找到一本書的？

一般的應用系統對比數據庫的讀寫比例在10:1左右(即有10次查詢操作時有1次寫的操作)，

而且插入操作和更新操作很少出現性能問題，

遇到最多、最容易出問題還是一些複雜的查詢操作，所以查詢語句的優化顯然是重中之重

2. 解決辦法

當數據庫中數據量很大時，查找數據會變得很慢

優化方案：索引

3. 索引是什麼

索引是一種特殊的文件(InnoDB數據表上的索引是表空間的一個組成部分)，它們包含着對數據表裡所有記錄的引用指針。

更通俗的說，數據庫索引好比是一本書前面的目錄，能加快數據庫的查詢速度

4. 索引目的

索引的目的在於提高查詢效率，可以類比字典，如果要查「mysql」這個單詞，我們肯定需要定位到m字母，然後從下往下找到y字母，再找到剩下的sql。如果沒有索引，那麼你可能需要把所有單詞看一遍才能找到你想要的，如果我想找到m開頭的單詞呢？或者ze開頭的單詞呢？是不是覺得如果沒有索引，這個事情根本無法完成？

5. 索引原理

除了詞典，生活中隨處可見索引的例子，如火車站的車次表、圖書的目錄等。它們的原理都是一樣的，通過不斷的縮小想要獲得數據的範圍來篩選出最終想要的結果，同時把隨機的事件變成順序的事件，也就是我們總是通過同一種查找方式來鎖定數據。

數據庫也是一樣，但顯然要複雜許多，因為不僅面臨著等值查詢，還有範圍查詢(>、<、between、in)、模糊查詢(like)、並集查詢(or)等等。數據庫應該選擇怎麼樣的方式來應對所有的問題呢？我們回想字典的例子，能不能把數據分成段，然後分段查詢呢？最簡單的如果1000條數據，1到100分成第一段，101到200分成第二段，201到300分成第三段……這樣查第250條數據，只要找第三段就可以了，一下子去除了90%的無效數據。

6. 索引的使用

查看索引

show index from 表名;

創建索引
- 如果指定字段是字符串，需要指定長度，建議長度與定義字段時的長度一致
- 字段類型如果不是字符串，可以不填寫長度部分

create index 索引名稱 on 表名(字段名稱(長度))

刪除索引：

drop index 索引名稱 on 表名;

7. 索引demo

7.1. 創建測試表testindex

create table test_index(title varchar(10));

7.2 使用python程序（ipython也可以）通過pymsql模塊向表中加入十萬條數據

from pymysql import connect

def main():
    # 創建Connection連接
    conn = connect(host='localhost',port=3306,database='jing_dong',user='root',password='mysql',charset='utf8')
    # 獲得Cursor對象
    cursor = conn.cursor()
    # 插入10萬次數據
    for i in range(100000):
        cursor.execute("insert into test_index values('ha-%d')" % i)
    # 提交數據
    conn.commit()

if __name__ == "__main__":
    main()

7.3. 查詢

開啟運行時間監測：

set profiling=1;

查找第1萬條數據ha-99999

select * from test_index where title='ha-99999';

查看執行的時間：

show profiles;

為表title_index的title列創建索引：

create index title_index on test_index(title(10));

執行查詢語句：

select * from test_index where title='ha-99999';

再次查看執行的時間

show profiles;

8. 注意：

要注意的是，建立太多的索引將會影響更新和插入的速度，因為它需要同樣更新每個索引文件。對於一個經常需要更新和插入的表格，就沒有必要為一個很少使用的where字句單獨建立索引了，對於比較小的表，排序的開銷不會很大，也沒有必要建立另外的索引。

建立索引會佔用磁盤空間

四.賬戶管理

在生產環境下操作數據庫時，絕對不可以使用root賬戶連接，而是創建特定的賬戶，授予這個賬戶特定的操作權限，然後連接進行操作，主要的操作就是數據的crud
MySQL賬戶體系：根據賬戶所具有的權限的不同，MySQL的賬戶可以分為以下幾種賬戶的操作主要包括創建賬戶、刪除賬戶、修改密碼、授權權限等
- 服務實例級賬號：，啟動了一個mysqld，即為一個數據庫實例；如果某用戶如root,擁有服務實例級分配的權限，那麼該賬號就可以刪除所有的數據庫、連同這些庫中的表
- 數據庫級別賬號：對特定數據庫執行增刪改查的所有操作
- 數據表級別賬號：對特定表執行增刪改查等所有操作
- 字段級別的權限：對某些表的特定字段進行操作
- 存儲程序級別的賬號：對存儲程序進行增刪改查的操作

注意：

進行賬戶操作時，需要使用root賬戶登錄，這個賬戶擁有最高的實例級權限
通常都使用數據庫級操作權限

授予權限

需要使用實例級賬戶登錄後操作，以root為例

主要操作包括：

查看所有用戶
修改密碼
刪除用戶

1. 查看所有用戶

所有用戶及權限信息存儲在mysql數據庫的user表中
查看user表的結構

desc user;

主要字段說明：
- Host表示允許訪問的主機
- User表示用戶名
- authentication_string表示密碼，為加密後的值

查看所有用戶

select host,user,authentication_string from user;

結果

mysql> select host,user,authentication_string from user;
+-----------+------------------+-------------------------------------------+
| host      | user             | authentication_string                     |
+-----------+------------------+-------------------------------------------+
| localhost | root             | *E74858DB86EBA20BC33D0AECAE8A8108C56B17FA |
| localhost | mysql.sys        | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
| localhost | debian-sys-maint | *EFED9C764966EDB33BB7318E1CBD122C0DFE4827 |
+-----------+------------------+-------------------------------------------+
3 rows in set (0.00 sec)

2. 創建賬戶、授權

需要使用實例級賬戶登錄後操作，以root為例
常用權限主要包括：create、alter、drop、insert、update、delete、select
如果分配所有權限，可以使用all privileges

2.1 創建賬戶&授權

grant 權限列表 on 數據庫 to '用戶名'@'訪問主機' identified by '密碼';

2.2 示例1

創建一個laowang的賬號，密碼為123456，只能通過本地訪問, 並且只能對jing_dong數據庫中的所有表進行讀操作

step1：使用root登錄

mysql -uroot -p
回車後寫密碼，然後回車

step2：創建賬戶並授予所有權限

grant select on jing_dong.* to 'laowang'@'localhost' identified by '123456';

說明

可以操作python數據庫的所有表，方式為:jing_dong.*
訪問主機通常使用百分號% 表示此賬戶可以使用任何ip的主機登錄訪問此數據庫
訪問主機可以設置成 localhost或具體的ip，表示只允許本機或特定主機訪問

查看用戶有哪些權限

show grants for laowang@localhost;

step3：退出root的登錄

quit

step4：使用laowang賬戶登錄

mysql -ulaowang -p
回車後寫密碼，然後回車

登錄後效果如下圖

2.3 示例2

創建一個laoli的賬號，密碼為12345678，可以任意電腦進行鏈接訪問, 並且對jing_dong數據庫中的所有表擁有所有權限

grant all privileges on jing_dong.* to "laoli"@"%" identified by "12345678"

賬戶操作

1. 修改權限

grant 權限名稱 on 數據庫 to 賬戶@主機 with grant option;

2. 修改密碼

使用root登錄，修改mysql數據庫的user表

使用password()函數進行密碼加密

update user set authentication_string=password('新密碼') where user='用戶名';
例：
update user set authentication_string=password('123') where user='laowang';

注意修改完成後需要刷新權限
```
刷新權限：flush privileges
```

3. 遠程登錄（危險慎用）

如果向在一個Ubuntu中使用msyql命令遠程連接另外一台mysql服務器的話，通過以下方式即可完成，但是此方法僅僅了解就好了，不要在實際生產環境中使用

修改 /etc/mysql/mysql.conf.d/mysqld.cnf 文件

vim /etc/mysql/mysql.conf.d/mysqld.cnf

然後重啟msyql

service mysql restart

在另外一台Ubuntu中進行連接測試

如果依然連不上，可能原因：

1) 網絡不通

通過 ping xxx.xxx.xx.xxx可以發現網絡是否正常

2)查看數據庫是否配置了bind_address參數

本地登錄數據庫查看my.cnf文件和數據庫當前參數show variables like ‘bind_address’;

如果設置了bind_address=127.0.0.1 那麼只能本地登錄

3)查看數據庫是否設置了skip_networking參數

如果設置了該參數，那麼只能本地登錄mysql數據庫

4)端口指定是否正確

4. 刪除賬戶

語法1：使用root登錄

drop user '用戶名'@'主機';
例：
drop user 'laowang'@'%';

語法2：使用root登錄，刪除mysql數據庫的user表中數據

delete from user where user='用戶名';
例：
delete from user where user='laowang';

-- 操作結束之後需要刷新權限
flush privileges

推薦使用語法1刪除用戶, 如果使用語法1刪除失敗，採用語法2方式

3. 忘記 root 賬戶密碼怎麼辦 !!

一般也輪不到我們來管理 root 賬戶,所以別瞎賣白粉的心了
萬一呢? 到時候再來查//blog.csdn.net/lxpbs8851/article/details/10895085

五.MySQL主從同步配置

1. 主從同步的定義

主從同步使得數據可以從一個數據庫服務器複製到其他服務器上，在複製數據時，一個服務器充當主服務器（master），其餘的服務器充當從服務器（slave）。因為複製是異步進行的，所以從服務器不需要一直連接着主服務器，從服務器甚至可以通過撥號斷斷續續地連接主服務器。通過配置文件，可以指定複製所有的數據庫，某個數據庫，甚至是某個數據庫上的某個表。

使用主從同步的好處：

通過增加從服務器來提高數據庫的性能，在主服務器上執行寫入和更新，在從服務器上向外提供讀功能，可以動態地調整從服務器的數量，從而調整整個數據庫的性能。
提高數據安全，因為數據已複製到從服務器，從服務器可以終止複製進程，所以，可以在從服務器上備份而不破壞主服務器相應數據
在主服務器上生成實時數據，而在從服務器上分析這些數據，從而提高主服務器的性能

2. 主從同步的機制

Mysql服務器之間的主從同步是基於二進制日誌機制，主服務器使用二進制日誌來記錄數據庫的變動情況，從服務器通過讀取和執行該日誌文件來保持和主服務器的數據一致。

在使用二進制日誌時，主服務器的所有操作都會被記錄下來，然後從服務器會接收到該日誌的一個副本。從服務器可以指定執行該日誌中的哪一類事件（譬如只插入數據或者只更新數據），默認會執行日誌中的所有語句。

每一個從服務器會記錄關於二進制日誌的信息：文件名和已經處理過的語句，這樣意味着不同的從服務器可以分別執行同一個二進制日誌的不同部分，並且從服務器可以隨時連接或者中斷和服務器的連接。

主服務器和每一個從服務器都必須配置一個唯一的ID號（在my.cnf文件的[mysqld]模塊下有一個server-id配置項），另外，每一個從服務器還需要通過CHANGE MASTER TO語句來配置它要連接的主服務器的ip地址，日誌文件名稱和該日誌裏面的位置（這些信息存儲在主服務器的數據庫里）

3. 配置主從同步的基本步驟

有很多種配置主從同步的方法，可以總結為如下的步驟：

在主服務器上，必須開啟二進制日誌機制和配置一個獨立的ID
在每一個從服務器上，配置一個唯一的ID，創建一個用來專門複製主服務器數據的賬號
在開始複製進程前，在主服務器上記錄二進制文件的位置信息
如果在開始複製之前，數據庫中已經有數據，就必須先創建一個數據快照（可以使用mysqldump導出數據庫，或者直接複製數據文件）
配置從服務器要連接的主服務器的IP地址和登陸授權，二進制日誌文件名和位置

4. 詳細配置主從同步的方法

主和從的身份可以自己指定，我們將虛擬機Ubuntu中MySQL作為主服務器，將Windows中的MySQL作為從服務器。在主從設置前，要保證Ubuntu與Windows間的網絡連通。

4.1 備份主服務器原有數據到從服務器

如果在設置主從同步前，主服務器上已有大量數據，可以使用mysqldump進行數據備份並還原到從服務器以實現數據的複製。

4.1.1 在主服務器Ubuntu上進行備份，執行命令：

mysqldump -uroot -pmysql --all-databases --lock-all-tables > ~/master_db.sql

說明

-u ：用戶名
-p ：示密碼
–all-databases ：導出所有數據庫
–lock-all-tables ：執行操作時鎖住所有表，防止操作時有數據修改
~/master_db.sql :導出的備份數據（sql文件）位置，可自己指定

4.1.2 在從服務器Windows上進行數據還原

找到Windows上mysql命令的位置

新打開的命令窗口，在這個窗口中可以執行類似在Ubuntu終端中執行的mysql命令

將從主服務器Ubuntu中導出的文件複製到從服務器Windows中，可以將其放在上面mysql命令所在的文件夾中，方便還原使用

在剛打開的命令黑窗口中執行還原操作:

mysql –uroot –pmysql < master_db.sql

4.2 配置主服務器master（Ubuntu中的MySQL）

4.2.1 編輯設置mysqld的配置文件，設置log_bin和server-id

sudo vim /etc/mysql/mysql.conf.d/mysqld.cnf

4.2.2 重啟mysql服務

sudo service mysql restart

4.2.3 登入主服務器Ubuntu中的mysql，創建用於從服務器同步數據使用的帳號

mysql –uroot –pmysql

GRANT REPLICATION SLAVE ON *.* TO 'slave'@'%' identified by 'slave';

FLUSH PRIVILEGES;

4.2.4 獲取主服務器的二進制日誌信息

SHOW MASTER STATUS;

File為使用的日誌文件名字，Position為使用的文件位置，這兩個參數須記下，配置從服務器時會用到

4.3 配置從服務器slave（Windows中的MySQL）

4.3.1 找到Windows中MySQL的配置文件

4.3.2 編輯my.ini文件，將server-id修改為2，並保存退出。

4.3.3 打開windows服務管理

可以在開始菜單中輸入services.msc找到並運行

4.3.4 在打開的服務管理中找到MySQL57，並重啟該服務

5. 進入windows的mysql，設置連接到master主服務器

change master to master_host='10.211.55.5', master_user='slave', master_password='slave',master_log_file='mysql-bin.000006', master_log_pos=590;

註：

master_host：主服務器Ubuntu的ip地址
master_log_file: 前面查詢到的主服務器日誌文件名
master_log_pos: 前面查詢到的主服務器日誌文件位置

6. 開啟同步，查看同步狀態

7. 測試主從同步

在Ubuntu的MySQL中（主服務器）創建一個數據庫

在Windows的MySQL中（從服務器）查看新建的數據庫是否存在

Tags: Python 高級

MySQL高級

閱讀目錄：

一.視圖

1. 問題

2. 視圖是什麼

3. 定義視圖

4. 查看視圖

5. 使用視圖

6. 刪除視圖

7. 視圖demo

8. 視圖的作用

二.事務

1. 為什麼要有事務

事務四大特性(簡稱ACID)

事務命令

查看錶的創建語句，可以看到engine=innodb

開啟事務，命令如下：

提交事務，命令如下

回滾事務，命令如下：

注意

提交

step1：連接

step2：增加數據

step3：查詢

step4：提交

step5：查詢

回滾

step1：連接

step2：增加數據

step3：查詢

step4：回滾

step5：查詢

三.索引

1. 思考

2. 解決辦法

3. 索引是什麼

4. 索引目的

5. 索引原理

6. 索引的使用

7. 索引demo

7.1. 創建測試表testindex

7.2 使用python程序（ipython也可以）通過pymsql模塊 向表中加入十萬條數據

7.3. 查詢

8. 注意：

四.賬戶管理

授予權限

1. 查看所有用戶

2. 創建賬戶、授權

2.1 創建賬戶&授權

2.2 示例1

step1：使用root登錄

step2：創建賬戶並授予所有權限

step3：退出root的登錄

step4：使用laowang賬戶登錄

2.3 示例2

賬戶操作

1. 修改權限

2. 修改密碼

3. 遠程登錄（危險慎用）

4. 刪除賬戶

3. 忘記 root 賬戶密碼怎麼辦 !!

五.MySQL主從同步配置

1. 主從同步的定義

2. 主從同步的機制

3. 配置主從同步的基本步驟

4. 詳細配置主從同步的方法

4.1 備份主服務器原有數據到從服務器

4.1.1 在主服務器Ubuntu上進行備份，執行命令：

4.1.2 在從服務器Windows上進行數據還原

4.2 配置主服務器master（Ubuntu中的MySQL）

4.2.1 編輯設置mysqld的配置文件，設置log_bin和server-id

4.2.2 重啟mysql服務

4.2.3 登入主服務器Ubuntu中的mysql，創建用於從服務器同步數據使用的帳號

4.2.4 獲取主服務器的二進制日誌信息

4.3 配置從服務器slave（Windows中的MySQL）

4.3.1 找到Windows中MySQL的配置文件

4.3.2 編輯my.ini文件，將server-id修改為2，並保存退出。

4.3.3 打開windows服務管理

4.3.4 在打開的服務管理中找到MySQL57，並重啟該服務

5. 進入windows的mysql，設置連接到master主服務器

7.2 使用python程序（ipython也可以）通過pymsql模塊向表中加入十萬條數據