---

一、視圖

1. 問題

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

解決辦法：定義視圖。

2. 視圖是什么

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

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

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

3. 定義視圖

create view 視圖名稱 as select 語句;

例如：create view goods_view as (select goods.id as id,goods.name as name,goods_cates.name as type from goods left outer join goods_cates on goods_cates.id=goods.cate_id);

4. 查看視圖

查看表會將所有的視圖也列出來 show tables;

5. 使用視圖

視圖的用途就是查詢 select * from v_stu_score;

6. 刪除視圖

drop view 視圖名稱;

例：drop view v_stu_sco;

7. 視圖的作用

• 提高了重用性，就像一個函數

• 對數據庫重構（改了字段名），卻不影響程序的運行

• 提高了安全性能，可以對不同的用戶

• 讓數據更加清晰

二、事務

1. 定義

事務廣泛的運用于訂單系統、銀行系統等多種場景。所謂事務，它是一個操作序列，這些操作要么都執行，要么都不執行，它是一個不可分割的工作單位。

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

事務四大特性（簡稱 ACID）

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

• 一致性（Consistency）：數據庫總是從一個一致性的狀態轉換到另一個一致性的狀態。

• 隔離性（Isolation）→ 針對高并發重要：通常來說，一個事務所做的修改在最終提交以前，對其他事務是不可見的。

• 持久性（Durability）→ 磁盤上真實的發生了變化：一旦事務提交，則其所做的修改會永久保存到數據庫。（此時即使系統崩潰，修改的數據也不會丟失。）

可以用 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;

2. 事務命令

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

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

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

• 開啟事務，命令如下：begin; 或者 start transaction; 。開啟事務后執行修改命令，變更會維護到本地緩存中，而不維護到物理表中。

• 提交事務，命令如下：commit; 。將緩存中的數據變更維護到物理表中。

• 回滾事務，命令如下：rollback; 。放棄緩存中變更的數據。

注意：

• 修改數據的命令會自動的觸發事務，包括 insert、update、delete

• 而在 SQL 語句中有手動開啟事務的原因是：可以進行多次數據的修改，如果成功一起成功，否則一起會滾到之前的數據

1）回滾

• 連接：終端 1 select * from goods_cates;

• 增加數據：

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

begin;
insert into goods_cates(name) values('游戲機');

終端 2：查詢數據，此時有新增的數據 select * from goods_cates;

• 查詢：終端 1 查詢數據，發現并沒有新增的數據 select * from goods_cates;

• 回滾：終端 2 完成回滾 rollback;

• 查詢：終端 1 查詢數據，發現沒有新增的數據 select * from goods_cates;

2）提交

• 連接：終端 1 查詢商品分類信息 select * from goods_cates;

• 增加數據

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

begin;
insert into goods_cates(name) values('游戲機');

終端 2 查詢數據，此時有新增的數據 select * from goods_cates;

• 查詢：終端 1 查詢數據，發現并沒有新增的數據 select * from goods_cates;

• 提交：終端 2 完成提交 commit;

• 查詢：終端 1 查詢，發現有新增的數據 select * from goods_cates;

3）臟寫、臟讀、不可重復讀和幻讀

臟寫、臟讀、不可重復讀和幻讀是數據庫事務中常見的數據一致性問題，了解它們有助于更好地管理并發事務。

參考文章：【大白話講解臟寫、臟讀、不可重復讀和幻讀】

• 臟寫就是：兩個事務沒提交的狀況下，都修改同一條數據，結果一個事務回滾了，把另外一個事務修改的值也撤銷了，所謂臟寫就是兩個事務沒提交狀態下修改同一個值。

• 臟讀就是一個事務修改了一條數據的值，結果還沒提交呢，另外一個事務就讀到了你修改的值，然后你回滾了，人家事務再次讀，就讀不到了，即人家事務讀到了你修改之后還沒提交的值，這就是臟讀。

• 不可重復讀，針對的是已經提交的事務修改的值，被你事務給讀到了，你事務內多次查詢，多次讀到的是別的已經提交的事務修改過的值，這就導致不可重復讀。

• 幻讀就是：你一個事務用一樣的 SQL 多次查詢，結果每次查詢都會發現查到一些之前沒看到過的數據。注意，幻讀特指的是你查詢到了之前查詢沒看到過的數據。

	臟寫	臟讀	不可重復讀	幻讀
read uncommitted	×	√	√	√
read committed	×	×	√	√
repeatable read	×	×	×	√
serializable	×	×	×	×

三、索引

1. 定義

當數據庫中數據量很大時，查找數據會變得很慢。優化方案：索引 → 就是數據結構（有序），B+ 樹索引，哈希索引。

2. 索引是什么

索引是一種特殊的文件（InnoDB 數據表上的索引是表空間的一個組成部分)，它們包含著對數據表里所有記錄的引用指針。更通俗的說，數據庫索引好比是一本書前面的目錄，能加快數據庫的查詢速度。

3. 索引目的

索引的目的在于提高查詢效率，可以類比字典，如果要查 “mysql” 這個單詞，我們肯定需要定位到 m 字母，然后從下往下找到 y 字母，再找到剩下的 sql 。如果沒有索引，那么你可能需要把所有單詞看一遍才能找到你想要的。

4. 索引原理

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

數據庫也是一樣，但顯然要復雜許多，因為不僅面臨著等值查詢，還有范圍查詢（>、<、between、in）、模糊查詢（like）、并集查詢（or）等等。

• 紅黑樹：不同節點的存在磁盤的不同位置，訪問 7 個節點就需要 2 次磁盤 I/O

• 哈希索引：因為需要連續的磁盤空間來存哈希表，找到連續的較大磁盤空間的難度較大（磁盤碎片嚴重）

• B+ 索引

5. 索引的使用

• 查看索引：show index from 表名;

• 創建索引：

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

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

• 刪除索引： drop index 索引名稱 on 表名;

注意：

• 當一列是沒有區分度，不適合建索引；

• 主鍵索引不僅僅是 B+ 樹索引，還是聚集索引（索引值和行數據存在一起，行數據直接就在葉子結點上）；

• 一個表中只能有一個聚集索引（主鍵默認就是聚集索引），其他索引都是非聚集索引。

6. 注意

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

• 建立索引會占用磁盤空間。

• 索引提高了查詢效率，修改效率，刪除效率；降低了一點新增效率。