視圖、事務(wù)、索引、用戶管理

2.7 數(shù)據(jù)庫(kù)其它操作

學(xué)習(xí)目標(biāo)

? 1. 能夠說(shuō)出視圖的作用

? 2. 能夠說(shuō)出索引的作用

? 3. 能夠說(shuō)出事務(wù)的作用

--------------------------------------------------------------------------------

2.7.1 視圖

視圖是一種基于查詢結(jié)果產(chǎn)生的虛擬表。

視圖總結(jié)：

? 視圖封裝了對(duì)多張基本表的復(fù)雜操作，簡(jiǎn)化用戶操作

? 視圖只是一個(gè)虛表,并不存儲(chǔ)任何基本表的表數(shù)據(jù),當(dāng)用戶使用視圖的時(shí)候 視圖會(huì)從基本表中取出

? 通過(guò)視圖可以對(duì)用戶展示指定字段從而屏蔽其他字段數(shù)據(jù)，更加安全

<1>為什么要有視圖？

當(dāng)在執(zhí)行查詢操作時(shí)，經(jīng)常會(huì)出現(xiàn)查詢頻率高并且查詢語(yǔ)句非常復(fù)雜的情況。 每次都要將復(fù)雜的SQL語(yǔ)句重新書(shū)寫(xiě)，非常不便。

如果能將這個(gè)查詢的結(jié)果保存下來(lái)，下次在使用時(shí)，直接使用結(jié)果就非常完美了。這個(gè)需求通過(guò)視圖就可以完成。

<2>視圖的特性

? 1>視圖是一種基于查詢結(jié)果產(chǎn)生的虛擬表，實(shí)際它就是一條被封裝起來(lái)的 SQL 查詢語(yǔ)句。

? 2>在使用視圖時(shí)，就相當(dāng)執(zhí)行了被封裝的復(fù)雜SQL查詢語(yǔ)句。

? 3>視圖不存儲(chǔ)具體的數(shù)據(jù)。

? 4>視圖的基本表發(fā)生變化，那么視圖也隨之變化

<3>增--->定義視圖

? 視圖在定義時(shí)，建議視圖名稱以 v_xxx 形式命名，以便和普通的表區(qū)分。

? 語(yǔ)法：create view 視圖名稱 as select語(yǔ)句;

? ? ? create view v_goods_info as select goods.name as gname,goods_cates.name as gcname,goods_brands.name as gbname from goods inner join goods_cates on goods.cate_id = goods_cates.id inner join goods_brands on goods.brand_id = goods_brands.id;

<4>查看視圖

? 視圖以表的形式體現(xiàn)，通過(guò) show tables 即可查看視圖。

<5>使用視圖

? 視圖的作用就是用來(lái)查詢，視圖的作用類似將子查詢做了封裝。

? 語(yǔ)法：select 字段名[,(字段名…)] from 視圖名稱;

? ? ? select * from 視圖名稱;

? ? ? select * from v_goods_info;

<6>刪除視圖

? 語(yǔ)法： drop view 視圖名稱

? ? ? drop view v_goods_info;

2.7.2 事務(wù)

<1>什么是事務(wù)？

? 事務(wù)Transaction，是指作為一個(gè)基本工作單元執(zhí)行的一系列SQL語(yǔ)句的操作，要么完全地執(zhí)行，要么完全地都不執(zhí)行。

<2>為什么要有事務(wù)？

事務(wù)總結(jié)：

? 事務(wù)的存在是解決數(shù)據(jù)在操作過(guò)程中的 ACID 問(wèn)題。

以下內(nèi)容出自《高性能MySQL》第三版，了解事務(wù)的ACID有助于我們更好的理解事務(wù)運(yùn)作。 下面舉一個(gè)銀行應(yīng)用是解釋事務(wù)必要性的一個(gè)經(jīng)典例子。假如一個(gè)銀行的數(shù)據(jù)庫(kù)有兩張表：支票表（checking）和儲(chǔ)蓄表（savings）?，F(xiàn)在要從用戶Jane的支票賬戶轉(zhuǎn)移200美元到她的儲(chǔ)蓄賬戶，那么至少需要三個(gè)步驟：

> 1. 檢查支票賬戶的余額高于或者等于200美元。

> 2. 從支票賬戶余額中減去200美元。

> 3. 在儲(chǔ)蓄帳戶余額中增加200美元。

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

可以用START TRANSACTION語(yǔ)句開(kāi)始一個(gè)事務(wù)，然后要么使用COMMIT提交將修改的數(shù)據(jù)持久保存，要么使用ROLLBACK撤銷所有的修改。事務(wù)SQL的樣本如下：

> 1. start transaction;

> 2. select balance from checking where customer_id = 10233276;

> 3. update checking set balance = balance - 200.00 where customer_id = 10233276;

> 4. update savings set balance = balance + 200.00 where customer_id = 10233276;

> 5. commit;

<3>事務(wù)的四大特性 ACID

? 1>原子性(Atomicity)

? ? ? 一個(gè)事務(wù)必須被視為一個(gè)不可分割的最小工作單元，整個(gè)事務(wù)中的所有操作要么全部提交成功，要么全部失敗回滾，對(duì)于一個(gè)事務(wù)來(lái)說(shuō)，不可能只執(zhí)行其中的一部分操作，這就是事務(wù)的原子性

? 2>一致性(Consistency)

? ? ? 數(shù)據(jù)庫(kù)總是從一個(gè)一致性的狀態(tài)轉(zhuǎn)換到另一個(gè)一致性的狀態(tài)。（在前面的例子中，一致性確保了，即使在執(zhí)行第三、四條語(yǔ)句之間時(shí)系統(tǒng)崩潰，支票賬戶中也不會(huì)損失200美元，因?yàn)槭聞?wù)最終沒(méi)有提交，所以事務(wù)中所做的修改也不會(huì)保存到數(shù)據(jù)庫(kù)中。）

? 3>隔離性(Isolation)

? ? ? 通常來(lái)說(shuō)，一個(gè)事務(wù)所做的修改在最終提交以前，對(duì)其他事務(wù)是不可見(jiàn)的。（在前面的例子中，當(dāng)執(zhí)行完第三條語(yǔ)句、第四條語(yǔ)句還未開(kāi)始時(shí)，此時(shí)有另外的一個(gè)賬戶匯總程序開(kāi)始運(yùn)行，則其看到支票帳戶的余額并沒(méi)有被減去200美元。）

? 4>持久性(Durability)

? ? ? 一旦事務(wù)提交，則其所做的修改會(huì)永久保存到數(shù)據(jù)庫(kù)。（此時(shí)即使系統(tǒng)崩潰，修改的數(shù)據(jù)也不會(huì)丟失。）

<4>事務(wù)操作

MySQL使用的InnoDB引擎支持事務(wù)操作。(ENGINE=InnoDB)

? 1>開(kāi)啟事務(wù)

? ? ? 開(kāi)啟事務(wù)后執(zhí)行修改命令，變更會(huì)維護(hù)到本地緩存中，而不維護(hù)到物理表中

? ? ? 語(yǔ)句：begin; 或 start transaction;

? 2>提交事務(wù)

? ? ? 將緩存中的數(shù)據(jù)變更維護(hù)到物理表中

? ? ? 語(yǔ)句：commit;

? 3>回滾事務(wù)

? ? ? 放棄緩存中變更的數(shù)據(jù) 表示事務(wù)執(zhí)行失敗 應(yīng)該回到開(kāi)始事務(wù)前的狀態(tài)

? ? ? 語(yǔ)句：rollback;

<5>驗(yàn)證事務(wù)提交

為了查看效果，需要打開(kāi)兩個(gè)終端窗口，使用同一個(gè)數(shù)據(jù)庫(kù)，操作同一張表。

step 1:

兩個(gè)終端同時(shí)連接同一個(gè)數(shù)據(jù)庫(kù)并查詢同一張表

`select * from goods_cates;`

step 2:

終端1開(kāi)啟事務(wù)，插入數(shù)據(jù)，查看

```sql

begin;

insert into goods_cates(name) values('手機(jī)')

select * from goods_cates;? -- 插入的數(shù)據(jù)顯示插入成功了

```

setp 3：

終端2查詢數(shù)據(jù)

```sql

select * from goods_cates; -- 并沒(méi)有看到新數(shù)據(jù)，因?yàn)榻K端1的操作緩存在本地，還沒(méi)有提交。隔離性

```

step 4：

終端1提交數(shù)據(jù),并查詢

```sql

commit; -- 提交后，事務(wù)完成，原子性操作結(jié)束

select * from goods_cates;

```

step 5:

終端2查詢

```sql

select * from goods_cates; -- 可以查到新數(shù)據(jù)，一致性，持久性。

```

<6>驗(yàn)證事務(wù)回滾

為了查看效果，需要打開(kāi)兩個(gè)終端窗口，使用同一個(gè)數(shù)據(jù)庫(kù)，操作同一張表。

step 1:

兩個(gè)終端同時(shí)連接同一個(gè)數(shù)據(jù)庫(kù)并查詢同一張表

`select * from goods_cates;`

step 2:

終端1開(kāi)啟事務(wù)，刪除所有數(shù)據(jù)，查看

```sql

begin;

insert into goods_cates(name) values('HIFI播放器');

select * from goods_cates;? -- 插入的數(shù)據(jù)顯示插入成功了

```

setp 3：

終端2查詢數(shù)據(jù)

```sql

select * from goods_cates; -- 并沒(méi)有看到新數(shù)據(jù)，因?yàn)榻K端1的操作緩存在本地，還沒(méi)有提交。隔離性

```

step 4：

終端1因?yàn)楦鞣N原因不想或不能提交數(shù)據(jù)，取消之前的操作

```sql

rollback;

```

step 5:

終端2查詢

```sql

select * from goods_cates; -- 沒(méi)有查到新數(shù)據(jù)，一致性，持久性。

```

2.7.3 索引

索引總結(jié)：

? 1>索引可以明顯提高某些字段的查詢效率。

? 2>但不是所有的表都需要建立索引

? 3>如果表中數(shù)據(jù)很少，沒(méi)有必要建立索引

? 4>如果一個(gè)表中的數(shù)據(jù)增刪很頻繁，不能建立索引 ，因?yàn)橹灰獢?shù)據(jù)發(fā)生增減，索引就要重新建立。增加了系統(tǒng)開(kāi)銷，反而慢了。

? 5>索引只適合查詢操作頻繁的表。

<1>什么是索引？

? 能夠快速查詢數(shù)據(jù)的線索就稱之為索引。

<2>為什么需要索引？

思考: 如何在一個(gè)圖書(shū)館中找到一本書(shū)的？

在圖書(shū)館中如果沒(méi)有其他輔助手段只能一條道走到黑，一本書(shū)->一本書(shū)的掃。 終于經(jīng)過(guò)1個(gè)小時(shí)的連續(xù)掃描發(fā)現(xiàn)你需要看的那本書(shū)在一分鐘之前被人借走了。這種就是順序查找。 圖書(shū)館管理員發(fā)現(xiàn)這個(gè)問(wèn)題，于是決定減少這樣的(＞﹏＜)悲劇故事。 為同學(xué)們購(gòu)置了一套圖書(shū)館管理系統(tǒng)。 大家要找書(shū)籍先在系統(tǒng)上查找到書(shū)籍所在的房屋編號(hào)和貨架編號(hào)，然后就可以直接大搖大擺的去取書(shū)了。 這個(gè)房屋編號(hào)和貨架編號(hào)就是索引。

<3>索引目的

? 索引的目的在于提高查詢效率。

<4>索引原理

? 通過(guò)不斷的縮小想要獲得數(shù)據(jù)的范圍來(lái)篩選出最終想要的結(jié)果。

<5>索引的使用(增、刪、查)

? 1>查看表中已有索引

? ? ? show index from 表名

? 2>創(chuàng)建索引

? ? ? create index 索引名稱 on 表名(字段名稱(長(zhǎng)度))`

? ? ? ? ? ■ 如果指定字段是字符串，需要指定長(zhǎng)度，建議長(zhǎng)度與定義字段時(shí)的長(zhǎng)度一致

? ? ? ? ? ■ 字段類型如果不是字符串，可以不填寫(xiě)長(zhǎng)度部分

? 3>刪除索引：

? ? ? drop index 索引名稱 on 表名;

<6>驗(yàn)證索引的效率

創(chuàng)建一個(gè)新表（不帶索引）

```sql

create table test_index(title varchar(10));

```

準(zhǔn)備10萬(wàn)條數(shù)據(jù)

```python

from pymysql import connect

def main():

? ? # 創(chuàng)建Connection連接

? ? conn = connect(host='localhost',port=3306,database='jddb',user='root',password='123123',charset='utf8')

? ? # 獲得Cursor對(duì)象

? ? cursor = conn.cursor()

? ? # 插入10萬(wàn)次數(shù)據(jù)

? ? for i in range(100000):

? ? ? ? cursor.execute("insert into test_index values('ha-%d')" % i)

? ? # 提交數(shù)據(jù)

? ? conn.commit()

if __name__ == "__main__":

? ? main()

```

查詢驗(yàn)證

? ? 開(kāi)啟運(yùn)行時(shí)間監(jiān)測(cè)

? ? `set profiling=1;`

? ? - 查詢 ha-99999 的數(shù)據(jù)

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

? ? - 為表 test_index 創(chuàng)建索引

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

? ? -? 再次查詢 ha-99999 的數(shù)據(jù)

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

? ? - 查看執(zhí)行時(shí)間

? ? `show profiles;`

? ? <img src='images/118.png'>

? ? - 使用 desc 命令也可以查看索引的效率

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

? ? <img src='images/119.png'>

2.7.4 用戶管理

root用戶是數(shù)據(jù)庫(kù)系統(tǒng)中的超級(jí)管理員，可以對(duì)數(shù)據(jù)庫(kù)做任何操作。root用戶一般會(huì)由DBA(數(shù)據(jù)庫(kù)管理員)或運(yùn)維人員統(tǒng)一管理。

給開(kāi)發(fā)人員針對(duì)項(xiàng)目的數(shù)據(jù)庫(kù)建立專門(mén)的用戶來(lái)操作。

<1>查看所有用戶

MySQL中所有的用戶及權(quán)限信息都存儲(chǔ)在MySQL數(shù)據(jù)庫(kù)的user表中。

? 通過(guò) desc user; 可以查看user表的結(jié)構(gòu)

? ? ? 主要字段：

? ? ? ? ? ■ Host 允許訪問(wèn)的主機(jī)地址? localhost 為本機(jī)， % 為任何主機(jī)

? ? ? ? ? ■ User 用戶名

? ? ? ? ? ■ authentication_string? ? (身份驗(yàn)證-字符串)密碼，保存的是加密后的值

? 查看所有的用戶

? ? ? select host ,user,authentication_string from user;

<2>創(chuàng)建帳戶、授權(quán)

注意：用戶管理操作的權(quán)限 都是管理員來(lái)操作的，只能使用 root 用戶操作。

? 常用權(quán)限： create、alter、drop、insert、update、delete、select

? 全部權(quán)限： all privileges

? 語(yǔ)法：grant 權(quán)限列表 on 數(shù)據(jù)庫(kù).表名(或者*.*或數(shù)據(jù)庫(kù).*或者直接表名(當(dāng)前正在使用的數(shù)據(jù)庫(kù)下的表)) to '用戶名'@'訪問(wèn)主機(jī)' identified by '密碼';

? ? ? 多個(gè)權(quán)限用,隔開(kāi)

? ? ? 創(chuàng)建賬戶并授予查詢權(quán)限

? ? ? 如果是給一個(gè)數(shù)據(jù)庫(kù)下的多個(gè)表（又不是全部表）授權(quán)，需要授權(quán)多次，每次數(shù)據(jù)庫(kù).表名或者直接表名

? ? ? 加入testuser用戶，密碼123123，只能對(duì)jddb中的表做查詢操作

? ? ? grant select on jddb.* to 'testuser'@'localhost' identified by '123123';

? 驗(yàn)證用戶權(quán)限

<3>查看權(quán)限

? 語(yǔ)法： show grants for 用戶名@主機(jī)地址

? ? ? show grants for testuser@localhost;

<4>修改權(quán)限

? 語(yǔ)法：grant 權(quán)限名稱 on 數(shù)據(jù)庫(kù) to 賬戶@主機(jī) with grant option;

? ? ? - 將testuser權(quán)限改為對(duì)所有庫(kù)有所有權(quán)限

? ? ? grant all privileges on *.* to 'testuser'@'localhost' with grant option;

? ? ? - 刷新，讓修改權(quán)限的操作即時(shí)生效

? ? ? flush privileges;

<5>刪除用戶

? 語(yǔ)法：drop user 用戶名@主機(jī)地址 drop user 'testuser'@'localhost';

<6>撤銷用戶權(quán)限r(nóng)evoke

? 撤銷已經(jīng)賦予給 MySQL 用戶權(quán)限的權(quán)限。 revoke 跟 grant 的語(yǔ)法差不多，只需要把關(guān)鍵字 “to” 換成 “from” 即可：

? revoke 權(quán)限名稱 on 數(shù)據(jù)庫(kù) from 賬戶@主機(jī);

<7>用戶管理小結(jié)：

用戶管理是MySQL當(dāng)中一塊非常重要的內(nèi)容，它涉及到了數(shù)據(jù)的安全問(wèn)題。 但是對(duì)于開(kāi)發(fā)人員來(lái)講，這塊內(nèi)容在實(shí)際工作中涉及又不多，因?yàn)閿?shù)據(jù)庫(kù)的維護(hù)會(huì)有專門(mén)的運(yùn)維人員或DBA來(lái)完成。 一般在操作數(shù)據(jù)時(shí)，不會(huì)使用root用戶。DBA會(huì)針對(duì)不同項(xiàng)目需求給你專門(mén)創(chuàng)建用戶，甚至是視圖。

數(shù)據(jù)庫(kù)高級(jí)部分的內(nèi)容，大家主要做一個(gè)了解即可。當(dāng)遇到問(wèn)題時(shí)，要知道如何解決?；蛘呖梢钥焖僬业浇鉀Q辦法。 對(duì)于數(shù)據(jù)庫(kù)編程。對(duì)表的CURD才是重要的。

2.8 主從配置（了解 ）

2.8.1 學(xué)習(xí)目標(biāo)

? 能夠說(shuō)出主從配置的意義

2.8.2 主從配置

為什么需要主從配置 一臺(tái)計(jì)算機(jī)，不管是從硬件上還是軟件上都可能產(chǎn)生崩潰。一旦作為提供數(shù)據(jù)服務(wù)的數(shù)據(jù)庫(kù)服務(wù)器崩潰了，那么整個(gè)后臺(tái)系統(tǒng)將會(huì)陷入停滯的狀態(tài)而無(wú)法繼續(xù)執(zhí)行，對(duì)公司的業(yè)務(wù)產(chǎn)生停業(yè)的影響。

主從同步使得數(shù)據(jù)可以從一個(gè)數(shù)據(jù)庫(kù)服務(wù)器復(fù)制到其他服務(wù)器上，在復(fù)制數(shù)據(jù)時(shí)，一個(gè)服務(wù)器充當(dāng)主服務(wù)器（master），其余的服務(wù)器充當(dāng)從服務(wù)器

（slave）。因?yàn)閺?fù)制是異步進(jìn)行的，所以從服務(wù)器不需要一直連接著主服務(wù)器，從服務(wù)器甚至可以通過(guò)撥號(hào)斷斷續(xù)續(xù)地連接主服務(wù)器。通過(guò)配置文

件，可以指定復(fù)制所有的數(shù)據(jù)庫(kù)，某個(gè)數(shù)據(jù)庫(kù)，甚至是某個(gè)數(shù)據(jù)庫(kù)上的某個(gè)表。

使用主從同步的好處：

? 1>提供服務(wù)可用性

? 2>通過(guò)增加從服務(wù)器來(lái)提高數(shù)據(jù)庫(kù)的性能，在主服務(wù)器上執(zhí)行寫(xiě)入和更新，在從服務(wù)器上向外提供讀功能，可以動(dòng)態(tài)地調(diào)整從服務(wù)器的數(shù)量，從而調(diào)整整個(gè)數(shù)據(jù)庫(kù)的性能。

? 3>提高數(shù)據(jù)安全，因?yàn)閿?shù)據(jù)已復(fù)制到從服務(wù)器，從服務(wù)器可以終止復(fù)制進(jìn)程，所以，可以在從服務(wù)器上備份而不破壞主服務(wù)器相應(yīng)數(shù)據(jù)

? 4>通過(guò)主從服務(wù)器，實(shí)現(xiàn)讀寫(xiě)分離，負(fù)載均衡

2.8.2 主從同步的機(jī)制

Mysql服務(wù)器之間的主從同步是基于二進(jìn)制日志機(jī)制，主服務(wù)器使用二進(jìn)制日志來(lái)記錄數(shù)據(jù)庫(kù)的變動(dòng)情況，從服務(wù)器通過(guò)讀取和執(zhí)行該日志文件來(lái)保持和主服務(wù)器的數(shù)據(jù)一致。

在使用二進(jìn)制日志時(shí)，主服務(wù)器的所有操作都會(huì)被記錄下來(lái)，然后從服務(wù)器會(huì)接收到該日志的一個(gè)副本。從服務(wù)器可以指定執(zhí)行該日志中的哪一類事件（譬如只插入數(shù)據(jù)或者只更新數(shù)據(jù)），默認(rèn)會(huì)執(zhí)行日志中的所有語(yǔ)句。

每一個(gè)從服務(wù)器會(huì)記錄關(guān)于二進(jìn)制日志的信息：文件名和已經(jīng)處理過(guò)的語(yǔ)句，這樣意味著不同的從服務(wù)器可以分別執(zhí)行同一個(gè)二進(jìn)制日志的不同部分，并且從服務(wù)器可以隨時(shí)連接或者中斷和服務(wù)器的連接。

主服務(wù)器和每一個(gè)從服務(wù)器都必須配置一個(gè)唯一的ID號(hào)（在my.cnf文件的[mysqld]模塊下有一個(gè)server-id配置項(xiàng)），另外，每一個(gè)從服務(wù)器還需要通過(guò)CHANGE MASTER TO語(yǔ)句來(lái)配置它要連接的主服務(wù)器的ip地址，日志文件名稱和該日志里面的位置（這些信息存儲(chǔ)在主服務(wù)器的數(shù)據(jù)庫(kù)里）

2.8.3 備份恢復(fù)-配置主從需要了解的一個(gè)小知識(shí)

? <1>備份

? ? ? 運(yùn)行mysqldump命令

? ? ? mysqldump –uroot –p 數(shù)據(jù)庫(kù)名 > python.sql;

? ? ? ? ? ■ # 按提示輸入mysql的密碼

? <2>恢復(fù)

? ? ? 連接mysql，創(chuàng)建新的數(shù)據(jù)庫(kù)

? ? ? 退出連接，執(zhí)行如下命令

? ? ? mysql -uroot –p 新數(shù)據(jù)庫(kù)名 < python.sql

? ? ? ? ? ■ # 根據(jù)提示輸入mysql密碼