資料庫的索引和存儲引擎

項目目錄

• 什麼是資料庫索引？
• 索引的作用
• 普通索引
• 唯一索引
• 主鍵索引
• 組合索引
• 全文索引
• 檢視索引
• 删除索引
• 倆張表之間建立内聯
• 建立索引視圖
• 事務的操作
• 修改存儲引擎
• 檢視建立表的預設引擎

什麼是資料庫索引？

是一個排序的清單，存儲着索引值和這個值所對應的實體位址

無須對整個表進行掃描，通過實體位址就可以找到所需資料

是表中—列或者若幹列值排序的方法

需要額外的磁盤空間

索引的作用

資料庫利用各種快速定位技術，能夠大大加快查詢速率

當表很大或查詢涉及到多個表時，可以成千上萬倍地提高查詢速度

可以降低資料庫的IO成本，并且還可以降低資料庫的排序成本

通過建立唯一性索引保證資料表資料的唯一性

可以加快表與表之間的連接配接

在使用分組和排序時，可大大減少分組和排序時間

普通索引

最基本的索引類型，沒有唯一性之類的限制

1、直接建立索引

2、修改表方式建立索引

3、建立表時建立索引

唯一索引

與“普通索引”基本相同

與普通索引的差別是索引列的所有值隻能出現一次，即必須唯一

1、直接建立索引

2、修改表方式建立索引

3、建立表時建立索引

主鍵索引

是一種特殊的唯一索引，指定為"PRIMARY KEY"

一個表隻能有一個主鍵，不允許有空值

組合索引

可以是單列上建立的索引，也可以是在多列上建立的索引

最左原則，從左往右依次執行

全文索引

MySQL從3.23.23版開始支援全文索引和全文檢索

索引類型為FULLTEXT

可以在CHAR、VARCHAR或者TEXT類型的列上建立

1、直接建立索引

2、修改表結構方式添加索引

檢視索引

mysql> show index from 表名;			
mysql> show keys from 表名;
           

删除索引

mysql> drop index 索引名 on 表名;
mysql> alter table index 表名 drop index 索引名;
           

倆張表之間建立内聯

建立索引視圖

create  view  視圖名稱  as  select  表1.字段,表2.字段  from 表1  inner join 表2  on 表1.字段=表2.字段;        #建立視圖
mysql> select * from 視圖名稱;      #查詢視圖
           

事務的操作

begin   #事務的開始
commit	#結束
rollback	#回到前一個狀态
savepoint  identifier	#儲存目前事務

如果一個事務開始了，如果想回到某一個狀态，這個狀态是必須儲存過的不然直接恢複bengin(開始)狀态
例如：
mysql>  begin
mysql>  create database user1;
mysql>  savepoint  identifier;
mysql>  create database user2;
mysql>  rollback;			#可以回到create database user1完成這個狀态
如果：沒有儲存則直接回到begin這個狀态
mysql>  begin
mysql>  create database user1;
mysql>  create database user2;
mysql>  rollback;			#直接回到begin這個狀态
           

修改存儲引擎

myisam存儲引擎：

1、表級鎖定形式,資料在更新時鎖定整個表

2、資料庫在讀寫過程中互相阻塞

會在資料寫入的過程阻塞使用者資料的讀取

也會在資料讀取的過程中阻塞使用者的資料寫入

3、資料單獨寫入或讀取,速度過程較快且占用資源相對少

3、MylAM支援的存儲格式

靜态表

動态表

壓縮表

myisam存儲引擎使用場景：

公司業務不需要事務的支援

單方面讀取或寫入資料比較多的業務

MyISAM存儲引擎資料讀寫都比較頻繁場景不适合使

用讀寫并發通路相對較低的業務

資料修改相對較少的業務

對資料業務一緻性要求不是非常高的業務

伺服器硬體資源相對比較差

InnoDB存儲引擎：

1、支援4個事務隔離級别

2、行級鎖定,但是全表掃描仍然會是表級鎖定

3、讀寫阻塞與事務隔離級别相關

4、能非常高效的緩存索引和資料

5、表與主鍵以簇的方式存儲

6、支援分區、表空間，類似oracle資料庫

7、支援外鍵限制,5.5前不支援全文索引,5.5後支援全文索引

8、對硬體資源要求還是比較高的場合

InnoDB存儲引擎使用場景：

業務需要事務的支援

行級鎖定對高并發有很好的适應能力,但需確定查詢是通過索引來完成

業務資料更新較為頻繁的場景。如:論壇，微網誌等

業務資料一緻性要求較高。如:銀行業務

硬體裝置記憶體較大，利用Innodb較好的緩存能力來提高記憶體使用率，減少磁盤IO的壓力

檢視建立表的預設引擎

1、直接修改表的存儲引擎

2、修改資料庫配置檔案(vim /etc/my.cnf)

[[email protected] ~]# vim /etc/my.cnf
在[mysqld]下面添加一條
default-storage-engine=InnoDB		#設定資料庫的預設引擎為InnoDB
           

3、建立表時指定存儲引擎