視圖（一般只針對(duì)查詢(xún)）
1. 問(wèn)題
對(duì)于復(fù)雜的查詢(xún)榕堰，往往是有多個(gè)數(shù)據(jù)表進(jìn)行關(guān)聯(lián)查詢(xún)而得到竖慧，如果數(shù)據(jù)庫(kù)因?yàn)樾枨蟮仍虬l(fā)生了改變，為了保證查詢(xún)出來(lái)的數(shù)據(jù)與之前相同逆屡，則需要在多個(gè)地方進(jìn)行修改圾旨，維護(hù)起來(lái)非常麻煩
- 解決辦法：定義視圖
2. 視圖是什么
通俗的講，視圖就是一條SELECT語(yǔ)句執(zhí)行后返回的結(jié)果集康二。所以我們?cè)趧?chuàng)建視圖的時(shí)候碳胳，主要的工作就落在創(chuàng)建這條SQL查詢(xún)語(yǔ)句上。
視圖是對(duì)若干張基本表的引用沫勿，一張?zhí)摫恚樵?xún)語(yǔ)句執(zhí)行的結(jié)果味混，不存儲(chǔ)具體的數(shù)據(jù)（基本表數(shù)據(jù)發(fā)生了改變产雹，視圖也會(huì)跟著改變）；
方便操作翁锡，特別是查詢(xún)操作蔓挖，減少?gòu)?fù)雜的SQL語(yǔ)句，增強(qiáng)可讀性馆衔；
3. 定義視圖
建議以v_開(kāi)頭
create view 視圖名稱(chēng) as select語(yǔ)句;
4. 查看視圖
查看表會(huì)將所有的視圖也列出來(lái)
show tables;
5. 使用視圖
視圖的用途就是查詢(xún)
select * from v_stu_score;
6. 刪除視圖
drop view 視圖名稱(chēng);
drop view v_stu_sco;
-
視圖demo
1.png
- 視圖的作用
提高了重用性瘟判，就像一個(gè)函數(shù)
對(duì)數(shù)據(jù)庫(kù)重構(gòu)，卻不影響程序的運(yùn)行
提高了安全性能角溃，可以對(duì)不同的用戶(hù)
讓數(shù)據(jù)更加清晰
事務(wù)
1. 為什么要有事務(wù)
事務(wù)廣泛的運(yùn)用于訂單系統(tǒng)拷获、銀行系統(tǒng)等多種場(chǎng)景
- 例如：
A用戶(hù)和B用戶(hù)是銀行的儲(chǔ)戶(hù)，現(xiàn)在A要給B轉(zhuǎn)賬500元减细，那么需要做以下幾件事：
檢查A的賬戶(hù)余額>500元匆瓜；
A 賬戶(hù)中扣除500元;
B 賬戶(hù)中增加500元;
正常的流程走下來(lái)，A賬戶(hù)扣了500未蝌，B賬戶(hù)加了500驮吱，皆大歡喜。
那如果A賬戶(hù)扣了錢(qián)之后萧吠，系統(tǒng)出故障了呢左冬？A白白損失了500，而B(niǎo)也沒(méi)有收到本該屬于他的500纸型。
以上的案例中拇砰，隱藏著一個(gè)前提條件：A扣錢(qián)和B加錢(qián)九昧，要么同時(shí)成功，要么同時(shí)失敗毕匀。事務(wù)的需求就在于此
所謂事務(wù),它是一個(gè)操作序列铸鹰，這些操作要么都執(zhí)行，要么都不執(zhí)行皂岔，它是一個(gè)不可分割的工作單位蹋笼。
例如，銀行轉(zhuǎn)帳工作：從一個(gè)帳號(hào)扣款并使另一個(gè)帳號(hào)增款躁垛，這兩個(gè)操作要么都執(zhí)行剖毯，要么都不執(zhí)行。所以教馆，應(yīng)該把他們看成一個(gè)事務(wù)逊谋。事務(wù)是數(shù)據(jù)庫(kù)維護(hù)數(shù)據(jù)一致性的單位，在每個(gè)事務(wù)結(jié)束時(shí)土铺，都能保持?jǐn)?shù)據(jù)一致性
事務(wù)四大特性(簡(jiǎn)稱(chēng)ACID)
- 原子性(Atomicity)
- 一致性(Consistency)
- 隔離性(Isolation)
- 持久性(Durability)
以下內(nèi)容出自《高性能MySQL》第三版胶滋，了解事務(wù)的ACID及四種隔離級(jí)有助于我們更好的理解事務(wù)運(yùn)作。
下面舉一個(gè)銀行應(yīng)用是解釋事務(wù)必要性的一個(gè)經(jīng)典例子悲敷。假如一個(gè)銀行的數(shù)據(jù)庫(kù)有兩張表：支票表（checking）和儲(chǔ)蓄表（savings）【啃簦現(xiàn)在要從用戶(hù)Jane的支票賬戶(hù)轉(zhuǎn)移200美元到她的儲(chǔ)蓄賬戶(hù)，那么至少需要三個(gè)步驟：
- 檢查支票賬戶(hù)的余額高于或者等于200美元后德。
- 從支票賬戶(hù)余額中減去200美元部宿。
- 在儲(chǔ)蓄帳戶(hù)余額中增加200美元。
上述三個(gè)步驟的操作必須打包在一個(gè)事務(wù)中瓢湃，任何一個(gè)步驟失敗理张，則必須回滾所有的步驟。
可以用START TRANSACTION語(yǔ)句開(kāi)始一個(gè)事務(wù)绵患，然后要么使用COMMIT提交將修改的數(shù)據(jù)持久保存雾叭，要么使用ROLLBACK撤銷(xiāo)所有的修改。事務(wù)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;
一個(gè)很好的事務(wù)處理系統(tǒng)藏雏，必須具備這些標(biāo)準(zhǔn)特性：
- 原子性（atomicity）
一個(gè)事務(wù)必須被視為一個(gè)不可分割的最小工作單元拷况，整個(gè)事務(wù)中的所有操作要么全部提交成功，要么全部失敗回滾掘殴，對(duì)于一個(gè)事務(wù)來(lái)說(shuō)赚瘦，不可能只執(zhí)行其中的一部分操作，這就是事務(wù)的原子性
- 一致性（consistency）
數(shù)據(jù)庫(kù)總是從一個(gè)一致性的狀態(tài)轉(zhuǎn)換到另一個(gè)一致性的狀態(tài)奏寨。（在前面的例子中起意，一致性確保了，即使在執(zhí)行第三病瞳、四條語(yǔ)句之間時(shí)系統(tǒng)崩潰揽咕，支票賬戶(hù)中也不會(huì)損失200美元悲酷，因?yàn)槭聞?wù)最終沒(méi)有提交，所以事務(wù)中所做的修改也不會(huì)保存到數(shù)據(jù)庫(kù)中亲善。）
- 隔離性（isolation）
通常來(lái)說(shuō)设易，一個(gè)事務(wù)所做的修改在最終提交以前，對(duì)其他事務(wù)是不可見(jiàn)的蛹头。（在前面的例子中顿肺，當(dāng)執(zhí)行完第三條語(yǔ)句、第四條語(yǔ)句還未開(kāi)始時(shí)渣蜗，此時(shí)有另外的一個(gè)賬戶(hù)匯總程序開(kāi)始運(yùn)行屠尊，則其看到支票帳戶(hù)的余額并沒(méi)有被減去200美元。）
- 持久性（durability）
一旦事務(wù)提交耕拷，則其所做的修改會(huì)永久保存到數(shù)據(jù)庫(kù)讼昆。（此時(shí)即使系統(tǒng)崩潰，修改的數(shù)據(jù)也不會(huì)丟失骚烧。）
事務(wù)命令
表的引擎類(lèi)型必須是innodb類(lèi)型才可以使用事務(wù)浸赫，這是mysql表的默認(rèn)引擎
查看表的創(chuàng)建語(yǔ)句，可以看到engine=innodb
-- 選擇數(shù)據(jù)庫(kù)
use jing_dong;
-- 查看goods表
show create table goods;
開(kāi)啟事務(wù)止潘，命令如下：
- 開(kāi)啟事務(wù)后執(zhí)行修改命令掺炭，變更會(huì)維護(hù)到本地緩存中，而不維護(hù)到物理表中
begin;
或者
start transaction;
提交事務(wù)凭戴，命令如下
將緩存中的數(shù)據(jù)變更維護(hù)到物理表中
commit;
回滾事務(wù)，命令如下：
放棄緩存中變更的數(shù)據(jù)
rollback;
注意
- 修改數(shù)據(jù)的命令會(huì)自動(dòng)的觸發(fā)事務(wù)炕矮，包括insert么夫、update、delete
- 而在SQL語(yǔ)句中有手動(dòng)開(kāi)啟事務(wù)的原因是：可以進(jìn)行多次數(shù)據(jù)的修改肤视，如果成功一起成功档痪，否則一起會(huì)滾到之前的數(shù)據(jù)
索引
1. 思考
在圖書(shū)館中是如何找到一本書(shū)的？
一般的應(yīng)用系統(tǒng)對(duì)比數(shù)據(jù)庫(kù)的讀寫(xiě)比例在10:1左右(即有10次查詢(xún)操作時(shí)有1次寫(xiě)的操作)邢滑，
而且插入操作和更新操作很少出現(xiàn)性能問(wèn)題腐螟，
遇到最多、最容易出問(wèn)題還是一些復(fù)雜的查詢(xún)操作困后，所以查詢(xún)語(yǔ)句的優(yōu)化顯然是重中之重
2. 解決辦法
當(dāng)數(shù)據(jù)庫(kù)中數(shù)據(jù)量很大時(shí)乐纸，查找數(shù)據(jù)會(huì)變得很慢.
優(yōu)化方案：索引
3. 索引是什么
索引是一種特殊的文件(InnoDB數(shù)據(jù)表上的索引是表空間的一個(gè)組成部分)，它們包含著對(duì)數(shù)據(jù)表里所有記錄的引用指針摇予。
更通俗的說(shuō)汽绢，數(shù)據(jù)庫(kù)索引好比是一本書(shū)前面的目錄，能加快數(shù)據(jù)庫(kù)的查詢(xún)速度
4. 索引目的
索引的目的在于提高查詢(xún)效率侧戴，可以類(lèi)比字典宁昭，如果要查“mysql”這個(gè)單詞跌宛，我們肯定需要定位到m字母，然后從下往下找到y(tǒng)字母积仗，再找到剩下的sql疆拘。如果沒(méi)有索引，那么你可能需要把所有單詞看一遍才能找到你想要的寂曹，如果我想找到m開(kāi)頭的單詞呢哎迄？或者ze開(kāi)頭的單詞呢？是不是覺(jué)得如果沒(méi)有索引稀颁，這個(gè)事情根本無(wú)法完成芬失？
5. 索引原理
除了詞典，生活中隨處可見(jiàn)索引的例子匾灶，如火車(chē)站的車(chē)次表棱烂、圖書(shū)的目錄等。它們的原理都是一樣的阶女，通過(guò)不斷的縮小想要獲得數(shù)據(jù)的范圍來(lái)篩選出最終想要的結(jié)果颊糜，同時(shí)把隨機(jī)的事件變成順序的事件，也就是我們總是通過(guò)同一種查找方式來(lái)鎖定數(shù)據(jù)秃踩。
數(shù)據(jù)庫(kù)也是一樣衬鱼，但顯然要復(fù)雜許多，因?yàn)椴粌H面臨著等值查詢(xún)憔杨，還有范圍查詢(xún)(>鸟赫、<、between消别、in)抛蚤、模糊查詢(xún)(like)、并集查詢(xún)(or)等等寻狂。數(shù)據(jù)庫(kù)應(yīng)該選擇怎么樣的方式來(lái)應(yīng)對(duì)所有的問(wèn)題呢岁经？我們回想字典的例子，能不能把數(shù)據(jù)分成段蛇券，然后分段查詢(xún)呢缀壤？最簡(jiǎn)單的如果1000條數(shù)據(jù)，1到100分成第一段纠亚，101到200分成第二段塘慕，201到300分成第三段……這樣查第250條數(shù)據(jù)，只要找第三段就可以了菜枷，一下子去除了90%的無(wú)效數(shù)據(jù)苍糠。
1.jpg
6. 索引的使用
- 查看索引
show index from 表名;
- 創(chuàng)建索引
- 如果指定字段是字符串，需要指定長(zhǎng)度啤誊，建議長(zhǎng)度與定義字段時(shí)的長(zhǎng)度一致
- 字段類(lèi)型如果不是字符串岳瞭，可以不填寫(xiě)長(zhǎng)度部分
create index 索引名稱(chēng) on 表名(字段名稱(chēng)(長(zhǎng)度))
- 刪除索引
drop index 索引名稱(chēng) on 表名;
7. 索引demo
- 創(chuàng)建測(cè)試表testindex
create table test_index(title varchar(10));
- 使用python程序（ipython也可以）通過(guò)pymsql模塊 向表中加入十萬(wàn)條數(shù)據(jù)
from pymysql import connect
def main():
# 創(chuàng)建Connection連接
conn = connect(host='localhost',port=3306,database='jing_dong',user='root',password='mysql',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()
7.3. 查詢(xún)
- 開(kāi)啟運(yùn)行時(shí)間監(jiān)測(cè)：
set profiling=1;
- 查找第1萬(wàn)條數(shù)據(jù)ha-99999
select * from test_index where title='ha-99999';
- 查看執(zhí)行的時(shí)間：
show profiles;
- 為表title_index的title列創(chuàng)建索引：
create index title_index on test_index(title(10));
- 執(zhí)行查詢(xún)語(yǔ)句：
select * from test_index where title='ha-99999';
- 再次查看執(zhí)行的時(shí)間
show profiles;
- 注意：
要注意的是拥娄，建立太多的索引將會(huì)影響更新和插入的速度，因?yàn)樗枰瑯痈旅總€(gè)索引文件瞳筏。對(duì)于一個(gè)經(jīng)常需要更新和插入的表格稚瘾，就沒(méi)有必要為一個(gè)很少使用的where字句單獨(dú)建立索引了，對(duì)于比較小的表姚炕，排序的開(kāi)銷(xiāo)不會(huì)很大摊欠，也沒(méi)有必要建立另外的索引。
建立索引會(huì)占用磁盤(pán)空間
