總體內(nèi)容
1、視圖
2、事務
3、索引
4、賬戶管理
5、MySQL主從同步配置
一、視圖
1.1、問題？
對于復雜的查詢，往往是有多個數(shù)據(jù)表進行關聯(lián)查詢而得到，如果數(shù)據(jù)庫因為需求等原因發(fā)生了改變，為了保證查詢出來的數(shù)據(jù)與之前相同，則需要在多個地方進行修改，維護起來非常麻煩
解決辦法：定義視圖
1.2、視圖是什么
通俗的講，視圖就是一條SELECT語句執(zhí)行后返回的結果集。所以我們在創(chuàng)建視圖的時候，主要的工作就落在創(chuàng)建這條SQL查詢語句上。
視圖是對若干張基本表的引用，一張?zhí)摫?，查詢語句執(zhí)行的結果，不存儲具體的數(shù)據(jù)（基本表數(shù)據(jù)發(fā)生了改變，視圖也會跟著改變）；顧名思義：也就是視圖創(chuàng)建后，如果其他的表發(fā)生了變化，視圖表也會跟著變，它也是一種表，虛擬的表，只可以查數(shù)據(jù)，不能增/刪/改。
方便操作，特別是查詢操作，減少復雜的SQL語句，增強可讀性；
1.3、定義視圖(下面我們以數(shù)據(jù)庫 jing_dong 為例)
建議以v_開頭，增加可讀性，語法如下：
create view 視圖名稱 as select語句;
例如：下面語句中 as 后面是 sql語句
create view v_goods_info as select g.*,c.name as cate_name,b.name as brand_name from goods as g left join good_cates as c on g.cate_id = c.id left join good_brands as b on g.brand_id = b.id;
1.4、使用視圖：視圖的用途就是查詢：select * from v_goods_info;,查出的數(shù)據(jù)和其他連表查詢的結果一樣
1.5、刪除視圖
drop view 視圖名稱;
例如：刪除上面創(chuàng)建的視圖表 v_goods_info
drop view v_goods_info;
.6、當其他表發(fā)生變化的時候，視圖表也會發(fā)生變化，如下，我們修改一下goods表里面 x240 超極本 改為 x86 超極本
update goods set name =? 'x86 超極本' where name = 'x240 超極本 ';
1.7、視圖的作用
提高了重用性，就像一個函數(shù)
對數(shù)據(jù)庫重構，卻不影響程序的運行
提高了安全性能，可以對不同的用戶
讓數(shù)據(jù)更加清晰
二、事務
2.1、為什么要有事務？
事務廣泛的運用于訂單系統(tǒng)、銀行系統(tǒng)等多種場景
例如：
A用戶和B用戶是銀行的儲戶，現(xiàn)在A要給B轉賬500元，那么需要做以下幾件事：
1、檢查A的賬戶余額>500元；
2、A 賬戶中扣除500元;
3、B 賬戶中增加500元;
正常的流程走下來，A賬戶扣了500，B賬戶加了500，皆大歡喜。
那如果A賬戶扣了錢之后，系統(tǒng)出故障了呢？A白白損失了500，而B也沒有收到本該屬于他的500。
以上的案例中，隱藏著一個前提條件：A扣錢和B加錢，要么同時成功，要么同時失敗。事務的需求就在于此
2.2、所謂事務,它是一個操作序列，這些操作要么都執(zhí)行，要么都不執(zhí)行，它是一個不可分割的工作單位。
例如，銀行轉帳工作：從一個帳號扣款并使另一個帳號增款，這兩個操作要么都執(zhí)行，要么都不執(zhí)行。所以，應該把他們看成一個事務。事務是數(shù)據(jù)庫維護數(shù)據(jù)一致性的單位，在每個事務結束時，都能保持數(shù)據(jù)一致性
2.3、事務四大特性(簡稱ACID)
原子性 (Atomicity)
一致性 (Consistency)
隔離性 (Isolation)
持久性 (Durability)
下面舉一個銀行應用是解釋事務必要性的一個經(jīng)典例子。假如一個銀行的數(shù)據(jù)庫有兩張表：支票表（checking）和儲蓄表（savings）?，F(xiàn)在要從用戶Jane的支票賬戶轉移200美元到她的儲蓄賬戶，那么至少需要三個步驟：
檢查支票賬戶的余額高于或者等于200美元。
從支票賬戶余額中減去200美元。
在儲蓄帳戶余額中增加200美元。
上述三個步驟的操作必須打包在一個事務中，任何一個步驟失敗，則必須回滾所有的步驟。
可以用START TRANSACTION語句開始一個事務，然后要么使用COMMIT提交將修改的數(shù)據(jù)持久保存，要么使用ROLLBACK撤銷所有的修改。事務SQL的樣本如下：
2.4、一個很好的事務處理系統(tǒng)，必須具備這些標準特性：
(1)、原子性（atomicity）：保證了要么成功要么不成功
一個事務必須被視為一個不可分割的最小工作單元，整個事務中的所有操作要么全部提交成功，要么全部失敗回滾，對于一個事務來說，不可能只執(zhí)行其中的一部分操作，這就是事務的原子性
(2)、一致性（consistency）：保證了在commit之前，不會因為系統(tǒng)掛了而出現(xiàn)錯誤
數(shù)據(jù)庫總是從一個一致性的狀態(tài)轉換到另一個一致性的狀態(tài)。（在前面的例子中，一致性確保了，即使在執(zhí)行第三、四條語句之間時系統(tǒng)崩潰，支票賬戶中也不會損失200美元，因為事務最終沒有提交，所以事務中所做的修改也不會保存到數(shù)據(jù)庫中。）
(3)、隔離性（isolation）：一個sql的執(zhí)行不會影響另外一個sql的執(zhí)行，也就是一個sql執(zhí)行完，才會執(zhí)行另外一個sql語句
通常來說，一個事務所做的修改在最終提交以前，對其他事務是不可見的。（在前面的例子中，當執(zhí)行完第三條語句、第四條語句還未開始時，此時有另外的一個賬戶匯總程序開始運行，則其看到支票帳戶的余額并沒有被減去200美元。）
(4)、持久性（durability）： 永久性存儲
一旦事務提交，則其所做的修改會永久保存到數(shù)據(jù)庫。（此時即使系統(tǒng)崩潰，修改的數(shù)據(jù)也不會丟失。）
2.5、事務命令
表的引擎類型必須是innodb類型才可以使用事務，這是mysql表的默認引擎
(1)、查看表的創(chuàng)建語句，可以看到engine=innodb
-- 選擇數(shù)據(jù)庫
use jing_dong;
-- 查看goods表
show create table goods;
(2)、開啟事務，命令如下：
開啟事務后執(zhí)行修改命令，變更會維護到本地緩存中，而不維護到物理表中將緩存中的數(shù)據(jù)變更維護到物理表中
begin;
或者
start transaction;
(3)、提交事務，命令如下
commit;
(4)、回滾事務，命令如下：
回滾事務，命令如下：
rollback;
2.6、注意
(1)、修改數(shù)據(jù)的命令會自動的觸發(fā)事務，包括insert、update、delete
(2)、而在SQL語句中有手動開啟事務的原因是：可以進行多次數(shù)據(jù)的修改，如果成功一起成功，否則一起會滾到之前的數(shù)據(jù)
2.7、提交
為了演示效果，需要打開兩個終端窗口，使用同一個數(shù)據(jù)庫(jing_dong)，操作同一張表
(1)、打開兩個終端 1 與 2，都進行查詢商品,內(nèi)容是一模一樣的
select * from good_cates;
(2)、增加數(shù)據(jù)
終端1：開啟事務，插入數(shù)據(jù)
begin;
insert into good_cates(name) values('小米筆記本');
終端1：查詢數(shù)據(jù)，此時有新增的數(shù)據(jù)
select * from good_cates;
(3)、查詢
終端2：查詢數(shù)據(jù)，發(fā)現(xiàn)并沒有新增的數(shù)據(jù)(因為終端1修改的數(shù)據(jù)還沒有進行提交)
select * from good_cates;
(4)、提交
終端1：完成提交
commit;
(5)、查詢
終端2：查詢，發(fā)現(xiàn)有新增的數(shù)據(jù)
select * from goods_cates;
2.8、回滾
為了演示效果，需要打開兩個終端窗口，使用同一個數(shù)據(jù)庫?jing_dong，操作同一張表
step1：連接
終端1
select * from good_cates;
step2：增加數(shù)據(jù)
終端2：開啟事務，插入數(shù)據(jù)
begin;
insert into good_cates(name) values('游戲機');
終端2：查詢數(shù)據(jù)，此時有新增的數(shù)據(jù)
select * from good_cates;
step3：查詢
終端1：查詢數(shù)據(jù)，發(fā)現(xiàn)并沒有新增的數(shù)據(jù)
select * from good_cates;
step4：回滾
終端2：完成回滾
rollback;
step5：查詢
終端1：查詢數(shù)據(jù)，發(fā)現(xiàn)沒有新增的數(shù)據(jù)
select * from good_cates;
三、索引
3.1、思考：在圖書館中是如何找到一本書的？
答：一般的應用系統(tǒng)對比數(shù)據(jù)庫的讀寫比例在10:1左右(即有10次查詢操作時有1次寫的操作)，而且插入操作和更新操作很少出現(xiàn)性能問題，遇到最多、最容易出問題還是一些復雜的查詢操作，所以查詢語句的優(yōu)化顯然是重中之重
3.2、解決辦法
當數(shù)據(jù)庫中數(shù)據(jù)量很大時，查找數(shù)據(jù)會變得很慢
優(yōu)化方案：索引
3.3、索引是什么
索引 是 一種特殊的文件(InnoDB數(shù)據(jù)表上的索引是表空間的一個組成部分)，它們包含著對數(shù)據(jù)表里所有記錄的引用指針。更通俗的說，數(shù)據(jù)庫索引好比是一本書前面的目錄，能加快數(shù)據(jù)庫的查詢速度
3.4、索引目的
索引的目的在于提高查詢效率，可以類比字典，如果要查“mysql”這個單詞，我們肯定需要定位到m字母，然后從下往下找到y(tǒng)字母，再找到剩下的sql。如果沒有索引，那么你可能需要把所有單詞看一遍才能找到你想要的，如果我想找到m開頭的單詞呢？或者ze開頭的單詞呢？是不是覺得如果沒有索引，這個事情根本無法完成？
3.5、索引原理
除了詞典，生活中隨處可見索引的例子，如火車站的車次表、圖書的目錄等。它們的原理都是一樣的，通過不斷的縮小想要獲得數(shù)據(jù)的范圍來篩選出最終想要的結果，同時把隨機的事件變成順序的事件，也就是我們總是通過同一種查找方式來鎖定數(shù)據(jù)。
數(shù)據(jù)庫也是一樣，但顯然要復雜許多，因為不僅面臨著等值查詢，還有范圍查詢(>、<、between、in)、模糊查詢(like)、并集查詢(or)等等。數(shù)據(jù)庫應該選擇怎么樣的方式來應對所有的問題呢？我們回想字典的例子，能不能把數(shù)據(jù)分成段，然后分段查詢呢？最簡單的如果1000條數(shù)據(jù)，1到100分成第一段，101到200分成第二段，201到300分成第三段……這樣查第250條數(shù)據(jù)，只要找第三段就可以了，一下子去除了90%的無效數(shù)據(jù)。
3.6、 索引的使用?(數(shù)據(jù)很大，且經(jīng)常用，我們就建立索引)
查看索引
show index from 表名;
創(chuàng)建索引
(1)、如果指定字段是字符串，需要指定長度，建議長度與定義字段時的長度一致
(2)、字段類型如果不是字符串，可以不填寫長度部分
create index 索引名稱 on 表名(字段名稱(長度))
刪除索引：
drop index 索引名稱 on 表名;
3.7、索引 demo 測試
(1)、創(chuàng)建測試表testindex
create tabletest_index(titlevarchar(10));
(2)、使用python程序（ipython也可以）通過pymsql模塊 向表中加入十萬條數(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對象
? ? cursor = conn.cursor()
? ? # 插入10萬次數(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()
(3)、查詢
開啟運行時間監(jiān)測：
set profiling=1;
查找第1萬條數(shù)據(jù)ha-99999
select * from test_index where title='ha-99999';
查看執(zhí)行的時間：
show profiles;
為表title_index的title列創(chuàng)建索引：
create index title_index on test_index(title(10));
執(zhí)行查詢語句：
select * from test_index where title='ha-99999';
再次查看執(zhí)行的時間
show profiles;
我們可以看到在創(chuàng)建索引后，查詢的時間明顯縮短了
3.8、注意：
要注意的是，建立太多的索引將會影響更新和插入的速度，因為它需要同樣更新每個索引文件。對于一個經(jīng)常需要更新和插入的表格，就沒有必要為一個很少使用的where字句單獨建立索引了，對于比較小的表，排序的開銷不會很大，也沒有必要建立另外的索引。
建立索引會占用磁盤空間
作者：IIronMan
鏈接：http://www.itdecent.cn/p/127c479a4265
來源：簡書
著作權歸作者所有。商業(yè)轉載請聯(lián)系作者獲得授權，非商業(yè)轉載請注明出處。
