數(shù)據(jù)存儲Innodb
創(chuàng)建數(shù)據(jù)庫后會在data目錄下創(chuàng)建文件夾
C:\Program Files\mysql-8.0.17-winx64\data\test_db
create database test_db;
創(chuàng)建表后會在文件夾生成一個.ibd文件tb_emp1包含tablespace id
mysql> create table tb_emp1(
-> id int(11),
-> name varchar(25),
-> deptId int(11),
-> salary float);
插入數(shù)據(jù)后數(shù)據(jù)存儲在公共空間啤它。
insert (1,1,1,1) into tb_emp1;
引擎
InnoDB不創(chuàng)建目錄,使用 InnoDB時, MySQL將在 MySQL數(shù)據(jù)目錄下創(chuàng)建一個名為ibdata1的10MB大小的自動擴展數(shù)據(jù)文件,以及兩個名為 ib_logfile0和 ib_logfile的5MB大小的日志文件舱卡。
使用 MyISAM引擎創(chuàng)建數(shù)據(jù)庫,將生產(chǎn)3個文件胖笛。文件的名字以表的名字開始,擴展名指出文件類型:fm文件存儲表定義,數(shù)據(jù)文件的擴展名為MYD( MYData),索引文件的擴展名是MYI( MYIndex)孟害。
索引
索引是一個單獨的她紫、存儲在磁盤上的數(shù)據(jù)庫結構,它們包含著對數(shù)據(jù)表里所有記錄的引用指針嫌松。使用索引用于快速找出在某個或多個列中有一特定值的行,所有 MySQL列類型都可以被索引,對相關列使用索引是提高查詢操作速度的最佳途徑沧奴。innodb表的索引會存放于s1.ibd文件中粤剧，而myisam表的索引則會有單獨的索引文件table1.MYI
通過不斷地縮小想要獲取數(shù)據(jù)的范圍來篩選出最終想要的結果歇竟，同時把隨機的事件變成順序的事件，也就是說抵恋，有了這種索引機制焕议，我們可以總是用同一種查找方式來鎖定數(shù)據(jù)』」兀考慮到磁盤IO是非常高昂的操作盅安，計算機操作系統(tǒng)做了一些優(yōu)化，當一次IO時世囊，不光把當前磁盤地址的數(shù)據(jù)别瞭，而是把相鄰的數(shù)據(jù)也都讀取到內(nèi)存緩沖區(qū)內(nèi)。
優(yōu)點
(1)通過創(chuàng)建唯一索引,可以保證數(shù)據(jù)庫表中每一行數(shù)據(jù)的唯一性株憾。
(2)可以大大加快數(shù)據(jù)的查詢速度,這也是創(chuàng)建索引的最主要的原因蝙寨。
(3)在實現(xiàn)數(shù)據(jù)的參考完整性方面,可以加速表和表之間的連接晒衩。
(4)在使用分組和排序子句進行數(shù)據(jù)查詢時,也可以顯著減少查詢中分組和排序的時間。
缺點
(1)創(chuàng)建索引和維護索引要耗費時間,并且隨著數(shù)據(jù)量的增加所耗費的時間也會增加墙歪。
(2)索引需要占磁盤空間,除了數(shù)據(jù)表占數(shù)據(jù)空間之外,每一個索引還要占一定的物理听系。
空間,如果有大量的索引,索引文件可能比數(shù)據(jù)文件更快達到最大文件尺寸。
(3)當對表中的數(shù)據(jù)進行增加虹菲、刪除和修改的時候,索引也要動態(tài)地維護,這樣就降低靠胜。
使用 CREATE TABLE創(chuàng)建表時,除了可以定義列的數(shù)據(jù)類型,還可以定義主鍵約束、外鍵約束或者唯一性約束,而不論創(chuàng)建那種約束,在定義約束的同時相當于在指定列上創(chuàng)建了一個索引毕源。
普通索引
最基本的索引類型,沒有唯一性之類的限制,其作用只是加快對數(shù)據(jù)的訪問速度浪漠。
INDEX(...)
唯一索引
創(chuàng)建唯一索引的主要原因是減少查詢索引列操作的執(zhí)行時間,尤其是對比較龐大的數(shù)據(jù)表。它與前面的普通索引類似,不同的就是:索引列的值必須唯一,但允許有空值脑豹。如果是組合索引,則列值的組合必須唯郑藏。
UNIQUE INDEX UniqueIdx(id)
單列索引
單列索引是在數(shù)據(jù)表中的某一個字段上創(chuàng)建的索引,一個表中可以創(chuàng)建多個單列索引前，面兩個例子中創(chuàng)建的索引都為單列索引瘩欺。
INDEX SingelIdxname(20))
組合索引
在多個字段上增加索引必盖。
INDEX MultipleIdex(id,name,age(100))
組合索引可起幾個索引的作用,但是使用時并不是隨便查詢哪個字段都可以使用索引,而是遵從“最左前綴”:利用索引中最左邊的列集來匹配行,這樣的列集稱為最左前綴。例如這里由id俱饿、name和age3個字段構成的索引,索引行中按 id/name/age的順序存放,索引可以搜索下面字段組合:(id,name,age)歌粥、(id,name)或者id。如果列不構成索引最左面的前綴MySQL不能使用局部索引,如(age)或(name,age)組合則不能使用索引查詢.
全文索引
FULLTEXT(全文索引)可以用于全文搜索拍埠。只有 MyISAM存儲引擎支持 FULLTEXT索引,并且只為CHAR失驶、 VARCHAR和TEXT列創(chuàng)建索引。索引總是對整個列進行,不支持局部(前綴)索引枣购。
FULLTEXT INDEX FullTxtIdx(info)
空間索引
只有 MyISAM存儲引擎支持SPATIAL索引嬉探。
b+樹性質(zhì)
索引字段要盡量的小，b+樹要求把真實的數(shù)據(jù)放到葉子節(jié)點而不是內(nèi)層節(jié)點棉圈，一旦放到內(nèi)層節(jié)點涩堤，磁盤塊的數(shù)據(jù)項會大幅度下降，導致樹增高分瘾；索引的最左匹配特性（即從左往右匹配）胎围。
存儲過程
一組為了完成特定功能的SQL 語句集，它存儲在數(shù)據(jù)庫中德召，一次編譯后永久有效白魂，用戶通過指定存儲過程的名字并給出參數(shù)（如果該存儲過程帶有參數(shù)）來執(zhí)行它。
存儲程序可以分為存儲過程和函數(shù), MySQL中創(chuàng)建存儲過程和函數(shù)使用的語句分別是:CREATE PROCEDURE和 CREATE FUNCTION上岗。使用CAL語句來調(diào)用存儲過程,只能用輸出變量返回值福荸。函數(shù)可以從語句外調(diào)用(即通過引用函數(shù)名),也能返回標量值。存儲過程也可以調(diào)用其他存儲過程肴掷。
視圖
視圖是從一個或幾個基本表導出的表逞姿。它本身不獨立存儲在數(shù)據(jù)庫中,即數(shù)據(jù)庫中只存放視圖的定義而不存放視圖對應的數(shù)據(jù)辞嗡。這些數(shù)據(jù)仍存放在導出視圖的基本表中,因此視圖是一個虛表捆等。視圖在概念上與基本表等同,用戶可以在視圖上再定義視圖滞造。
作用
1.簡單化
看到的就是需要的。視圖不僅可以簡化用戶對數(shù)據(jù)的理解,也可以簡化他們的操作栋烤。那些被經(jīng)常使用的查詢可以被定義為視圖,從而使得用戶不必為以后的操作每次指定全部的條件谒养。
2.安全性
通過視圖用戶只能查詢和修改他們所能見到的數(shù)據(jù)。數(shù)據(jù)庫中的其他數(shù)據(jù)則既看不見也取不到明郭。數(shù)據(jù)庫授權命令可以使每個用戶對數(shù)據(jù)庫的檢索限制到特定的數(shù)據(jù)庫對象上,但不能授權到數(shù)據(jù)庫特定行和特定的列上买窟。
3.邏輯數(shù)據(jù)獨立性
視圖可幫助用戶屏蔽真實表結構變化帶來的影響。
觸發(fā)器
觸發(fā)器(trigger)是個特殊的存儲過程,不同的是,執(zhí)行存儲過程要使用CALL語句來調(diào)用觸發(fā)器的執(zhí)行不需要使用CALL語句來調(diào)用,也不需要手工啟動,只要當一個預定義的事件發(fā)生的時候,就會被 MySQL自動調(diào)用薯定。比如當對 fruits表進行操作( INSERT始绍、 DELETE或 UPDATE)時就會激活它執(zhí)行。
優(yōu)化
優(yōu)化查詢
優(yōu)化查詢語句话侄、索引對查詢速度的影響亏推、使用索引查詢、優(yōu)化子查詢年堆。
優(yōu)化數(shù)據(jù)庫結構
將字段很多的表分解成多個表吞杭、增加中間表、增加冗余字段变丧、優(yōu)化插入記錄的速度芽狗、分析表、檢查表和優(yōu)化表
優(yōu)化 MySQL服務器
服務器硬件痒蓬、 MySQL的參數(shù)
