數(shù)據(jù)庫(kù)的基礎(chǔ)知識(shí)介紹完了，下面介紹的是在開發(fā)過(guò)程要需要注意的知識(shí)點(diǎn)，這樣對(duì)提高數(shù)據(jù)庫(kù)的性能十分重要！

1.表類型（存儲(chǔ)引擎）的選擇

InnoDB

InnoDB存儲(chǔ)引擎提供了具有提交、回滾和崩潰恢復(fù)能力的事務(wù)安全。但是對(duì)比與MyISAM的存儲(chǔ)引擎，InnoDB寫的處理效率 差一些，并且會(huì)占用更多地磁盤空間以保留數(shù)據(jù)和索引。

InnoDB不同于其他存儲(chǔ)引擎的表的特點(diǎn)：

• 自動(dòng)增長(zhǎng)列：InnoDB表的自動(dòng)增長(zhǎng)列可以手工插入，但是如果是插入的值是空或者是0，則實(shí)際插入的將是自動(dòng)增長(zhǎng)后的值。通過(guò)ALTER TABLE *** AUTO_INCREMENT = n 語(yǔ)句強(qiáng)制設(shè)置自動(dòng)增長(zhǎng)列的初始值，默認(rèn)從1開始，但是該強(qiáng)制的默認(rèn)值是保留在內(nèi)存中的，如果該值在使用之前數(shù)據(jù)庫(kù)重新啟動(dòng)，那么該值會(huì)失效。對(duì)于InnoDB表，自動(dòng)增長(zhǎng)列是索引，如果是組合索引，也必須是組合索引的第一列。
• 外鍵約束：MySQL支持外鍵的存儲(chǔ)引擎只有InnoDB，在創(chuàng)建外鍵的時(shí)候，要求父表必須有對(duì)應(yīng)的索引，子表在創(chuàng)建外鍵的時(shí)候也會(huì)自動(dòng)創(chuàng)建對(duì)應(yīng)的索引。
• 存儲(chǔ)方式：InnoDB存儲(chǔ)表和索引有以下兩種存儲(chǔ)方式1.使用共享表空間2.使用多表空間存儲(chǔ)（多表空間存儲(chǔ)可以比較方便進(jìn)行單表備份和恢復(fù)操作，共享表空間存儲(chǔ)InnoDB把內(nèi)部數(shù)據(jù)詞典和在線重做日志放在這個(gè)文件中）
  1.1MySQL中Myisam與Innodb的區(qū)別，至少5點(diǎn)：

一、5點(diǎn)不同：

• 1、InnoDB支持事務(wù)，而MyISAM不支持事務(wù)。
• 2、InnoDB支持行級(jí)鎖，而MyISAM不支持行級(jí)鎖。
• 3、InnoDB支持MVCC，而MyISAM不支持。
• 4、InnoDB支持外鍵，而MyISAM不支持。
• 5、InnoDB不支持全文索引，而MyISAM支持。

二、InnoDB引擎的4大特性

• 1、插入緩沖（insert buffer)
• 2、二次寫(double write)
• 3、自適應(yīng)哈希索引(ahi)
• 4、預(yù)讀(read ahead)

三、、2者select count(*)哪個(gè)更快，為什么

myisam更快，因?yàn)閙yisam內(nèi)部維護(hù)了一個(gè)計(jì)數(shù)器，可以直接調(diào)取。

2.選擇合適的數(shù)據(jù)類型

在項(xiàng)目中創(chuàng)建一個(gè)表時(shí)，需要為各個(gè)屬性值定義數(shù)據(jù)類型，通常我們很少考慮到如何選擇合適的數(shù)據(jù)類型，導(dǎo)致查詢的速度慢的問(wèn)題，下面介紹不同數(shù)據(jù)類型的差別。

CHAR與VARCHAR

char和varchar類型相似，都是用來(lái)存儲(chǔ)字符串的，但是它們保存和檢索的方式不同。char屬于固定長(zhǎng)度的字符類型，varchar是屬于可變長(zhǎng)度的字符類型。

總結(jié)：固定長(zhǎng)度使用char比較好，缺點(diǎn)是浪費(fèi)存儲(chǔ)空間，但是總的來(lái)說(shuō)，char的平均占用時(shí)間多余varchar，一般是使用varchar。

TEXT與BLOB

一般在保存少量的字符串的時(shí)候，我們會(huì)選擇使用char或者varchar；而在保存較大文本是，通常會(huì)選擇使用text或者blob。二者之間的差別是blob能用來(lái)保存二進(jìn)制數(shù)據(jù)，比如照片；而text只能保存字符數(shù)據(jù)，比如一篇文章或者日記。

常見問(wèn)題分析：

• blob和text值會(huì)引起一些性能問(wèn)題，特別是在執(zhí)行了大量的刪除操作時(shí)。刪除操作會(huì)在數(shù)據(jù)表中留下很大的空洞，以后填入這些空洞的記錄在插入的性能會(huì)有影響。為了提高性能，建議定期使用optimize table功能對(duì)這類表進(jìn)行碎片整理。
• 可以使用合成的（Synthetic）索引來(lái)提高大文本字段的查詢性能。（簡(jiǎn)單的來(lái)說(shuō)，合成索引就是根據(jù)大文本字段的內(nèi)容建立一個(gè)散列值，并把這個(gè)值存儲(chǔ)在單獨(dú)的數(shù)據(jù)列中，接下來(lái)就可以通過(guò)檢索散列值找到數(shù)據(jù)行了）
• 在不必要的時(shí)候避免檢索大型的blob或text值。
• 把blob或text列分離到單獨(dú)的表中。

浮點(diǎn)數(shù)和定點(diǎn)數(shù)

浮點(diǎn)數(shù)一般用于表示含有小數(shù)部分的數(shù)值，而定點(diǎn)數(shù)實(shí)際上是以字符串形式存放的，所以定點(diǎn)數(shù)可以更精確的保存數(shù)據(jù)。如果實(shí)際插入的數(shù)據(jù)精度大于實(shí)際定義的精度，則MySQL會(huì)進(jìn)行警告，但是數(shù)據(jù)還是按照實(shí)際精度四舍五入后插入。

3.索引的設(shè)計(jì)和使用

3.1 什么是索引？

索引是對(duì)數(shù)據(jù)庫(kù)表中一或多個(gè)列的值進(jìn)行排序的結(jié)構(gòu)，是幫助MySQL高效獲取數(shù)據(jù)的數(shù)據(jù)結(jié)構(gòu)。索引就是加快檢索表中數(shù)據(jù)的方法。

例如：這樣的一個(gè)查詢select * from t_measure_data where update_timestamp ='1475045438';。如果沒(méi)有索引，必須遍歷整個(gè)表，直到update_timestamp的這一行被找到為止；有了索引之后（必須是在update_timestamp 這一列建立的索引），即可在索引中查找。由于索引是經(jīng)過(guò)某種算法優(yōu)化過(guò)的，因而查找次數(shù)要少的多?？梢?，索引插敘的速度要比沒(méi)有索引的速度要快很多。

下面進(jìn)行一個(gè)查詢時(shí)間的對(duì)比：

沒(méi)有建立索引：

image.png

建立索引后：

1524901607281.png

很明顯，建立索引后的查詢速度是很快的。

3.2 索引的原理

索引(index)是幫助MySQL高效獲取數(shù)據(jù)的數(shù)據(jù)結(jié)構(gòu)。InnoDB存儲(chǔ)引擎的表默認(rèn)創(chuàng)建的是BTree索引。請(qǐng)看MySQL的Btree索引原理

3.3 索引的類型

從邏輯角度，索引可以分為以下幾種：

• 1、普通索引：最基本的索引，它沒(méi)有任何限制。

  create index 索引名稱 on 表名(列名)
  

• 2、唯一索引：唯一性索引unique index和一般索引normal index最大的差異就是在索引列上增加了一層唯一約束。添加唯一性索引的數(shù)據(jù)列可以為空，但是只要存在數(shù)據(jù)值，就必須是唯一的。

  create unique index 索引名 on 表名(列名)
  

• 3、主鍵索引：它是一種特殊的唯一索引，用于唯一標(biāo)識(shí)數(shù)據(jù)庫(kù)中的某一條記錄，不允許有空值，一般使用Primary Key來(lái)約束。

  alter table 表名 add primary key;
  

• 4、聯(lián)合索引（復(fù)合索引）：多個(gè)字段上建立的索引，能夠加速?gòu)?fù)合查詢條件的檢索。

  create index S_Sno_Sname on Student(Sno,Sname);
  #建立這樣的組合索引，其實(shí)是相當(dāng)于分別建立了下面兩兩組合索引
  -Sno,Sname
  -Sno
  #為什么沒(méi)有Sname這樣的組合索引？這是因?yàn)镸ySQL組合索引“最左匹配原則”
  

• 5、全文索引： MySQL
  自帶的全文索引只能用于數(shù)據(jù)庫(kù)引擎為 MyISAM的數(shù)據(jù)表，新版本 MySQL 5.6 的 InnoDB支持全文索引。默認(rèn) MySQL不支持中文全文檢索，可以通過(guò)擴(kuò)展 MySQL，添加中文全文檢索或?yàn)橹形膬?nèi)容表提供一個(gè)對(duì)應(yīng)的英文索引表的方式來(lái)支持中文。

3.4 設(shè)計(jì)索引的基本原則

索引的設(shè)計(jì)可以遵循一些已有的原則，創(chuàng)建索引的時(shí)候請(qǐng)盡量考慮符合這些原則，便于提高索引的使用效率，更有效的使用索引。

• 搜索的索引列，不一定是要選擇的列。換句話說(shuō)，最合適索引的列是出現(xiàn)在where字句中的列，或連接子句中指定的列，而不是出現(xiàn)在select關(guān)鍵字后的選擇列表中的列。
• 使用唯一列?？紤]某列中值的分布，索引的列的基數(shù)越大，索引的效果越好。
• 使用短索引。如果對(duì)字符串列進(jìn)行索引，應(yīng)該指定一個(gè)前綴長(zhǎng)度，只要有可能就應(yīng)該這樣做。
• 利用最左前綴(組合索引)。組合索引可以更好的提高M(jìn)ySQL效率，最左索引遵循"最左索引"原則。創(chuàng)建符合索引時(shí)應(yīng)該將最常用（頻率）作限制條件的列放在最左邊，依次遞減

• 不要過(guò)度索引。每個(gè)額外的索引都要占用額外的磁盤空間，并降低些操作的性能。
• 對(duì)于InnoDB存儲(chǔ)引擎的表，記錄默認(rèn)會(huì)按照一定的順序保存，如果有明確定義的主鍵，則按照主鍵順序保存。

下面介紹一下建立索引的字段和時(shí)機(jī)：

image

創(chuàng)建索引

索引在創(chuàng)建表的時(shí)候可以同時(shí)創(chuàng)建，也可以隨時(shí)增加新的索引。創(chuàng)建新索引的語(yǔ)法為：

create [unique\fulltext\spatial] index index_name [using index_type] on tbl_name(index_clo_name...)

image.png

修改索引

在MySQL中并沒(méi)有提供修改索引的直接指令，一般情況下，我們需要先刪除掉原索引，再根據(jù)需要?jiǎng)?chuàng)建一個(gè)同名的索引，從而變相地實(shí)現(xiàn)修改索引操作。

#先刪除
ALTER TABLE user DROP INDEX idx_user_username;
#再修改后內(nèi)容創(chuàng)建同名索引
CREATE INDEX idx_user_username ON user (username(8));

刪除索引

索引的刪除語(yǔ)法為：

drop index index_name on tbl_name

#想要?jiǎng)h除Student表上的索引Stusno
drop index Stusno on Student;

image.png

查看索引

show index from Student\G

image.png

索引的優(yōu)化

索引的好處很多，但是過(guò)多的使用索引將會(huì)造成濫用。因此索引也會(huì)有它的缺點(diǎn)：

• 雖然索引大大提高了查詢速度，同時(shí)卻會(huì)降低更新表的速度，如對(duì)表進(jìn)行INSERT、UPDATE和DELETE。因?yàn)楦卤頃r(shí)，MySQL不僅要保存數(shù)據(jù)，還要保存索引文件。
• 建立索引會(huì)占用磁盤空間的索引文件。

了解MySQL的優(yōu)化：MySQL優(yōu)化那些事兒

4.視圖

什么是視圖

視圖（View）是一種虛擬存在的表，對(duì)于使用視圖的用戶來(lái)說(shuō)基本是透明的。視圖并不在數(shù)據(jù)庫(kù)匯總實(shí)際存在，行和列數(shù)據(jù)來(lái)自定義視圖的查詢中使用的表，并且是在使用視圖時(shí)動(dòng)態(tài)生成的。

視圖的作用

從用戶角度來(lái)看，一個(gè)視圖是從一個(gè)特定的角度來(lái)查看數(shù)據(jù)庫(kù)中的數(shù)據(jù)。從數(shù)據(jù)庫(kù)系統(tǒng)內(nèi)部來(lái)看，一個(gè)視圖是由SELECT語(yǔ)句組成的查詢定義的虛擬表。從數(shù)據(jù)庫(kù)系統(tǒng)內(nèi)部來(lái)看，視圖是由一張或多張表中的數(shù)據(jù)組成的，從數(shù)據(jù)庫(kù)系統(tǒng)外部來(lái)看，視圖就如同一張表一樣，對(duì)表能夠進(jìn)行的一般操作都可以應(yīng)用于視圖，例如查詢，插入，修改，刪除操作等。

在經(jīng)常用到的查詢或者復(fù)雜的多表查詢、權(quán)限限制的時(shí)候使用視圖比較好什么時(shí)候使用視圖

視圖相對(duì)于普通表的優(yōu)勢(shì)主要包括以下幾點(diǎn)：

• 簡(jiǎn)單：使用視圖的用戶完全不需要關(guān)心后面對(duì)應(yīng)的表的結(jié)構(gòu)、關(guān)聯(lián)條件和篩選條件，對(duì)用戶來(lái)說(shuō)已經(jīng)是過(guò)濾好的符合條件的結(jié)果集。
• 安全：使用視圖的用戶只能訪問(wèn)他們被允許查詢的結(jié)果集，對(duì)表的權(quán)限管理并不能限制到某個(gè)列某個(gè)行，但是通過(guò)視圖就可以簡(jiǎn)單的實(shí)現(xiàn)。
• 數(shù)據(jù)獨(dú)立：一旦視圖的結(jié)構(gòu)確定了，可以屏蔽表結(jié)構(gòu)變化對(duì)用戶的影響，源表增加列對(duì)視圖沒(méi)有影響；源表修改列名，則可以通過(guò)修改視圖來(lái)解決，不會(huì)造成對(duì)訪問(wèn)者的影響。

視圖操作

視圖的操作包括創(chuàng)建或者修改視圖、刪除視圖以及查看視圖定義

創(chuàng)建或者修改視圖

創(chuàng)建視圖的語(yǔ)法為：

create [or replace][algorithm={undefined\merge\temptable}]
view view_name[(column_list)]
as select_statement
[with [cascaded\local] check option]

#對(duì)payment表創(chuàng)建視圖
create or replace view payment_view as 
select payment_id,amount from payment
where amount < 10 
with check option 

修改視圖的語(yǔ)法為：

alter [algorthm={undefined\merge\temptable}]
view view_name[(column_list)]
as select_statement
[with [cascaded\local] check option]

刪除視圖

用戶可以一次刪除一個(gè)或者多個(gè)視圖，前提是必須有該視圖的drop權(quán)限。

drop view [if exists] view_name [,view_name]... [restrict|cascade] 

#刪除staff_list視圖
drop view staff_list

查看視圖

#show tables不僅顯示表的名字，同時(shí)也顯示視圖的名字
show tabels
show tabels status [from db_name] [like 'pattern']
#查看staff_list視圖信息
show table status like 'staff_list' \G

更新視圖

更新視圖時(shí)，可以先用drop再用create，也可以直接用create or replace view。

如果視圖定義中有以下操作，怎不能進(jìn)行視圖的更新：

• 分組（使用group by和having）
• 聯(lián)結(jié)
• 子查詢
• 并
• 聚集函數(shù)（Min()、Count()、Sum()）等
• distinct
• 導(dǎo)出（計(jì)算）列

理解視圖的例子（例3.86）

Create view IS_S1 (Sno,Sname,Grade)
as
select Student.Sno,Sname,Grade
from Student,SC
where Sdept='CS' and Student.Sno = SC.Sno and SC.Cno ='1' ;

建立信息系選修了1號(hào)課程的學(xué)生的視圖（包括學(xué)號(hào)、姓名、成績(jī)）。

現(xiàn)在，假如可以把整個(gè)查詢包裝成一個(gè)名為IS_S1的虛擬表，則可以如下輕松的檢索出相同得數(shù)據(jù)：

select * from IS_S1;

這就是視圖的作用。IS_S1是一個(gè)視圖，作為視圖，它不包含表中應(yīng)該有的任何列或數(shù)據(jù)，它包含的是一個(gè)SQL查詢（與上面用以正確聯(lián)結(jié)表的相同的查詢）。

image.png

視圖提供了一種MySQL的select語(yǔ)句層次的封裝，可用來(lái)簡(jiǎn)化數(shù)據(jù)處理以及重新格式化基礎(chǔ)數(shù)據(jù)或者保護(hù)基礎(chǔ)數(shù)據(jù)。