文章目錄
- MySQL資料庫進階篇
-
- 視圖
- 事務
-
- 事務四大特性(簡稱ACID)
- 索引
-
- 索引的使用
- 索引Demo
MySQL資料庫進階篇
視圖
對于複雜的查詢SQL語句，往往是由多個表進行關聯查詢才得到的，如果資料庫因為需求等原因發生了改變，為了保證查詢出來的資料與之前相同，則需要在多個地方進行修改，而且維護起來非常麻煩，這個時候我們就可以通過
定義視圖
的方式來解決。
什麼是視圖？
通俗一點來說，'視圖' 其實就是一個 'SELECT' 語句的結果集。
是以我們在
使用視圖
的時候，主要的工作就落在建立這條SQL查詢語句上。
視圖的本質是對若幹個基本表/引用表的引用，一張虛表（也可以了解為假的表），查詢語句執行的結果，
實際上并不存儲實際的資料（基本表/引用表資料發生改變的情況下，視圖的查詢執行的結果，也會跟着改變）。
視圖
的優點是查詢操作，減少複雜的SQL語句，增強可讀性。
定義視圖
- 建議視圖命名以’v_'開頭
create view 視圖名稱 as select語句;
檢視試圖
- 檢視表會将所有的視圖也列出來
show tables;
使用視圖
- 視圖的用途就是查詢
select * from v_stu_score;
删除視圖
- drop view 視圖名稱;
drop view v_stu_sco;
視圖的Demo
視圖的作用
- 提高了重用性，就像一個函數
- 對資料庫重構，卻不影響程式的運作
- 提高了安全性能，可以對不同的使用者
- 讓資料更加清晰
一般大公司有自己的資料庫設計規範，比如禁止使用視圖之類的情況。
事務
什麼是事務？
事務廣泛的運用于訂單系統、銀行系統等多種場景
例如：
A使用者和B使用者是銀行的儲戶，現在A要給B轉賬500元，那麼需要做以下幾件事：
1、檢查A的賬戶餘額>500元；
2、A 賬戶中扣除500元;
3、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的樣本如下：
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;
一個很好的事務處理系統，必須具備這些标準特性
- 原子性（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語句中有手動開啟事務的原因是：可以進行多次資料的修改，如果成功一起成功，否則一起會滾到之前的資料
索引
一般的應用系統對比資料庫的讀寫比例在10:1左右(即有10次查詢操作時有1次寫的操作)，而且插入操作和更新操作很少出現性能問題，遇到最多、最容易出問題還是一些複雜的查詢操作，是以查詢語句的優化顯然是重中之重。
當資料庫中資料量很大時，查找資料會變得很慢，這時候就會使用到
優化解決方案：索引。
什麼是索引？
索引是一種特殊的檔案(InnoDB資料表上的索引是表空間的一個組成部分)，它們包含着對資料表裡所有記錄的引用指針。
更通俗的說，資料庫索引好比是一本書前面的目錄，能加快資料庫的查詢速度。
索引的目的
索引的目的在于提高查詢效率，可以類比字典，如果要查“mysql”這個單詞，我們肯定需要定位到m字母，然後從下往下找到y字母，再找到剩下的sql。如果沒有索引，那麼你可能需要把所有單詞看一遍才能找到你想要的，如果我想找到m開頭的單詞呢？或者ze開頭的單詞呢？是不是覺得如果沒有索引，這個事情根本無法完成？
索引的原理
除了詞典，生活中随處可見索引的例子，如火車站的車次表、圖書的目錄等。它們的原理都是一樣的，通過不斷的縮小想要獲得資料的範圍來篩選出最終想要的結果，同時把随機的事件變成順序的事件，也就是我們總是通過同一種查找方式來鎖定資料。
資料庫也是一樣，但顯然要複雜許多，因為不僅面臨着等值查詢，還有範圍查詢(>、<、between、in)、模糊查詢(like)、并集查詢(or)等等。資料庫應該選擇怎麼樣的方式來應對所有的問題呢？我們回想字典的例子，能不能把資料分成段，然後分段查詢呢？最簡單的如果1000條資料，1到100分成第一段，101到200分成第二段，201到300分成第三段……這樣查第250條資料，隻要找第三段就可以了，一下子去除了90%的無效資料。
索引的使用
- 檢視索引
show index from 表名;
- 建立索引
- 如果指定字段是字元串，需要指定長度，建議長度與定義字段時的長度一緻
- 字段類型如果不是字元串，可以不填寫長度部分
create index 索引名稱 on 表名(字段名稱(長度))
- 删除索引
drop index 索引名稱 on 表名;
索引Demo
建立測試表testindex
create table test_index(title varchar(10));
使用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()
查詢
- 開啟運作時間監測：
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;
注意：
要注意的是，建立太多的索引将會影響更新和插入的速度，因為它需要同樣更新每個索引檔案。對于一個
經常需要更新和插入的表格，就沒有必要為一個很少使用的where字句單獨建立索引了，對于比較小的表，
排序的開銷不會很大，也沒有必要建立另外的索引。
建立索引會占用磁盤空間