存儲(chǔ)引擎

概念

存儲(chǔ)引擎這個(gè)名字只有在mysql中存在嚷往。

Oracle中有對(duì)應(yīng)的機(jī)制闪萄，但不叫做存儲(chǔ)引擎啸如，Oracle中稱為"表的存儲(chǔ)方式"

mysql支持很多存儲(chǔ)引擎缘挑，每個(gè)存儲(chǔ)引擎都對(duì)應(yīng)了一種不同的存儲(chǔ)方式

每一個(gè)存儲(chǔ)引擎都有自己的優(yōu)缺點(diǎn)，需要在合適的時(shí)機(jī)選擇合適的存儲(chǔ)引擎

查看當(dāng)前mysql支持的存儲(chǔ)引擎绍刮？

show engines \G

常見的存儲(chǔ)引擎

1. MyISAM
   • MyISAM這種存儲(chǔ)引擎不支持事務(wù)
   • MyISAM是mysql最常用的存儲(chǔ)引擎温圆，但是這種存儲(chǔ)引擎不是默認(rèn)的。
   • MyISAM采用三個(gè)文件組織一個(gè)表：
     • xxx.frm(存儲(chǔ)格式的文件)
     • xxx.MYD(存儲(chǔ)表中數(shù)據(jù)的文件)
     • xxx.MYI(存儲(chǔ)表中索引的文件)
   • 優(yōu)點(diǎn)：可被壓縮孩革，節(jié)省存儲(chǔ)空間岁歉。并且可以轉(zhuǎn)換為只讀表，提高檢索效率膝蜈。
   • 缺點(diǎn):不支持事務(wù)

Engine: MyISAM
Support: YES
Comment: MyISAM storage engine
Transactions: NO
XA: NO
Savepoints: NO

2. InnoDB

   • 表的結(jié)構(gòu)存儲(chǔ)在xxx.frm文件中
   • 數(shù)據(jù)存儲(chǔ)在tablespace這樣的表空間中(邏輯概念)锅移，無法被壓縮，無法轉(zhuǎn)換成只讀饱搏。
   • 這種InnoDB存儲(chǔ)引擎在MySQL數(shù)據(jù)庫(kù)崩潰之后提供自動(dòng)恢復(fù)機(jī)制非剃。
   • InoDB支持級(jí)聯(lián)刪除和級(jí)聯(lián)更新

   優(yōu)點(diǎn)：支持事務(wù)、行級(jí)鎖推沸、外鍵等备绽。這種存儲(chǔ)引擎數(shù)據(jù)的安全得到保障

Engine: InnoDB
Support: DEFAULT
Comment: Supports transactions, row-level locking, and foreign keys
Transactions: YES
XA: YES
Savepoints: YES

3. MEMORY

   • 以前叫做HEPA引擎

   缺點(diǎn)：

   • 不支持事務(wù)
   • 數(shù)據(jù)容易丟失（因?yàn)樗袛?shù)據(jù)和索引都是存儲(chǔ)在內(nèi)存當(dāng)中的）

   優(yōu)點(diǎn)：查詢速度最快，因?yàn)閿?shù)據(jù)直接就在運(yùn)存里面

Engine: MEMORY
Support: YES
Comment: Hash based, stored in memory, useful for temporary tables
Transactions: NO
XA: NO
Savepoints: NO

唯一性約束(unique)

唯一性約束修飾的字段具有唯一性鬓催，不能重復(fù)肺素，但可以為null

案例一

給某一列添加unique

      drop table if exists t_user;
      create table t_user(
        id int,
    username varchar(255) unique  //列級(jí)約束
    );
    insert into t_user values(1,'zhangsan');        
    insert into t_user values(2,'zhangsan');  //出現(xiàn)編譯錯(cuò)誤，唯一性約束宇驾，該字段與上一行字段重復(fù)倍靡，但可以為null！
    ERROR 1062 (23000) : Duplicate entry 'zhangsan' for key 'username'

    insert into t_user(id) values(2);
        insert into t_user(id) values(3);
    insert into t_user(id) values(4);

案例二

給兩個(gè)列或者多個(gè)列添加unique

drop table if exists t_user;
    create table t_user(
        id int,
        usercode varchar(255),
        username varchar(255),
        unique(usercode,username)  //多個(gè)字段聯(lián)合起來添加一個(gè)約束unique 【表級(jí)約束】
      );
insert into t_user values(1,'111','zs');
insert into t_user values(2,'111','ls');
insert into t_user values(3,'222','zs');
select * from t_user;
insert into t_user values(4,'111','zs');  //出現(xiàn)編譯錯(cuò)誤课舍！
ERROR 1062 (23000) : Duplicate entry '111-zs' for key 'usercode'

drop table if exists t_user;
create table t_suer(
    id int,
    usercode varchar(255) unique,
    username varchar(255) unique
  );
insert into t_user values(1,'111','zs');
insert into t_user values(2,'111','ls');
ERROR 1062 (23000) : Duplicate entry '111' for key 'usercode'

• 注意：not null約束只有列級(jí)約束塌西，沒有表級(jí)約束他挎。

主鍵約束(primary key)

主鍵相關(guān)術(shù)語(yǔ)

  主鍵約束 ：`primary key`
  主鍵字段 : `id`字段添加`primary key`之后，id叫做主鍵字段
  主鍵值 ：主鍵字段中的每一個(gè)值都是主鍵值

添加主鍵

如何給一張表添加主鍵約束呢雨让？

drop table if exists t_user;
      create table t_user(
          id int primary key,  //列級(jí)約束
      username varchar(255),
      email varchar(255)
      );
      insert into t_user(id,username,email) values(1,'zs','zs@123.com');
      insert into t_user(id,username,email) values(2,'ls','ls@123.com');
      insert into t_user(id,username,email) values(3,'ww','ww@123.com');
      select * from t_user;
      +-----------------------------+
      | id | username | email       |
      +-----------------------------+
      |  1 | zs       | zs@123.com  |
      |  2 | ls       | ls@123.com  |
      |  3 | ww       | ww@123.com  |
      +----+----------+-------------+

注意：主鍵約束雇盖，不能為null也不能重復(fù)

insert into t_user(id,username,email) values(1,'jack','jack@123.com'); 
ERROR 1364 (HY000) : Field 'id' doesn't have a default value

主鍵作用

根據(jù)主鍵字段的字段數(shù)量來劃分：

• 單一主鍵 (推薦的，常用的）
• 復(fù)合主鍵(多個(gè)字段聯(lián)合起來添加一個(gè)主鍵約束)
• 復(fù)合主鍵不建議使用栖忠，因?yàn)閺?fù)合主鍵違背三范式

根據(jù)主鍵性質(zhì)來劃分：

• 自然主鍵 ：主鍵值最好就是一個(gè)和業(yè)務(wù)沒有任何關(guān)系的自然數(shù)崔挖。(這種方式是推薦的)
• 業(yè)務(wù)主鍵 : 主鍵值和系統(tǒng)的業(yè)務(wù)掛鉤，例如：拿著銀行卡的卡號(hào)做主鍵庵寞、拿著身份證號(hào)做為主鍵狸相。(不推薦使用）

最好不要拿著和業(yè)務(wù)掛鉤的字段做為主鍵

因?yàn)橐院蟮臉I(yè)務(wù)一旦發(fā)生改變的時(shí)候，主鍵也可能需要隨著發(fā)生變化捐川，但有的時(shí)候沒有辦法變化脓鹃，因?yàn)樽兓赡軙?huì)導(dǎo)致主鍵重復(fù)。

注意：一張表的主鍵約束只能有1個(gè)

主鍵值自增

mysql提供主鍵值自增：auto_increment

drop table if exists t_user;
create table t_user(
    id int primary key auto_increment,  //id字段自動(dòng)維護(hù)一個(gè)自增的數(shù)字古沥，從1開始瘸右，以1遞增。
    username varchar(255)
);

提示：Oracle當(dāng)中也提供了一個(gè)自增機(jī)制岩齿，叫做：序列(sequence)對(duì)象

外鍵約束

相關(guān)術(shù)語(yǔ)

外鍵約束：foreign key
外鍵字段：添加有外鍵約束的字段
外鍵值：外鍵字段中的每一個(gè)值

使用外鍵約束的業(yè)務(wù)背景

外鍵太颤，說簡(jiǎn)單點(diǎn)，就是在已有一張表的基礎(chǔ)上盹沈，選擇該表的一個(gè)字段的內(nèi)容約束下一張表的某一字段的輸入內(nèi)容

外鍵可以為null

外鍵字段引用其他表的某個(gè)字段的時(shí)候龄章，被引用的字段不一定是主鍵，但至少是具有unique約束乞封，具有唯一性做裙，不可重復(fù)！

事務(wù)(Transaction)

什么是事務(wù)?

一個(gè)事務(wù)就是一個(gè)不可再分肃晚，完整的業(yè)務(wù)邏輯單元

比如：銀行賬戶锚贱，從A賬戶向B賬戶轉(zhuǎn)賬10000元，需要執(zhí)行兩條update語(yǔ)句陷揪。

update t_act set balance = balance - 10000 where actno = 'act-001';
update t_act set balance = balance + 10000 where actno = 'act-002';

以上兩條DML語(yǔ)句必須同時(shí)成功惋鸥，或者同時(shí)失敗，不允許出現(xiàn)一條成功悍缠，一條失敗。

想要保證以上的兩條DML語(yǔ)句同時(shí)成功或者同時(shí)失敗耐量，那么就要使用數(shù)據(jù)庫(kù)的"事務(wù)機(jī)制"

事務(wù)相關(guān)的語(yǔ)句

只有DML語(yǔ)句

• insert
• delete
• update

為什么飞蚓？因?yàn)樗麄冞@三個(gè)語(yǔ)句都是和數(shù)據(jù)庫(kù)表當(dāng)中的"數(shù)據(jù)"相關(guān)的，而事務(wù)的存在是為了保證數(shù)據(jù)的完整性廊蜒，安全性趴拧。

事務(wù)的特性

事務(wù)包括四大特性：ACID

• A:原子性：事務(wù)是最小的工作單元溅漾，不可再分。
• B:一致性：事務(wù)必須保證多條DML語(yǔ)句同時(shí)成功或者同時(shí)失敗著榴。
• C:隔離性：事務(wù)A與事務(wù)B之間具有隔離添履。
• D:持久性：持久性說的是最終數(shù)據(jù)必須持久化到硬盤中，事務(wù)才算成功結(jié)束脑又。

事務(wù)之間的隔離性

事務(wù)隔離性存在隔離級(jí)別暮胧，理論上隔離級(jí)別包括4個(gè)：

• 第一級(jí)別：讀未提交(read uncommitted)
  • 對(duì)方事務(wù)還沒有提交，我們當(dāng)前事務(wù)可以讀取到對(duì)方未提交的數(shù)據(jù)问麸。
  • 讀未提交存在臟讀(Dirty Read) 現(xiàn)象：表示讀到了臟數(shù)據(jù)往衷。
• 第二級(jí)別：讀已提交(read committed)
  • 對(duì)方事務(wù)提交之后的數(shù)據(jù)我方可以讀取到。
  • 讀已提交存在的問題是：不可重復(fù)讀严卖。
• 第三級(jí)別：可重復(fù)讀(repeatable read)
• 這種隔離級(jí)別解決了：不可重復(fù)讀問題席舍。
• 這種隔離級(jí)別存在的問題是：讀取到的數(shù)據(jù)是幻象。
• 第四級(jí)別：序列化讀/串行化讀(serialized)
  • 解決了以上所有問題哮笆。
  • 但效率低来颤，因?yàn)樾枰聞?wù)排隊(duì)。

開啟事務(wù)

1. MySQL事務(wù)默認(rèn)情況下是自動(dòng)提交的

2. 只要執(zhí)行任意一條DML語(yǔ)句則提交一次

開啟事務(wù)采用如下語(yǔ)句：

start transaction;

回滾事務(wù)

rollback;   

提交事務(wù)

commit

索引

索引的概念與作用

索引就相當(dāng)于一本書的目錄稠肘，通過目錄可以快速的找到對(duì)應(yīng)的資源福铅。

在數(shù)據(jù)庫(kù)方面，查詢一張表的時(shí)候有兩種檢索方式：

• 第一種方式：全表掃描
• 第二種方式：根據(jù)索引檢索(效率很高)

索引為什么可以提高檢索效率呢启具？
其實(shí)最根本的原理是縮小了掃描的范圍

索引雖然可以提高檢索效率本讥，但是不能隨意的添加索引，因?yàn)樗饕彩菙?shù)據(jù)庫(kù)當(dāng)中的對(duì)象鲁冯，也需要數(shù)據(jù)庫(kù)不斷的維護(hù)拷沸。是有維護(hù)成本的。

比如：表中的數(shù)據(jù)經(jīng)常被修改薯演，這樣就不適合添加索引撞芍，因?yàn)閿?shù)據(jù)一旦修改，索引需要重新排序跨扮，進(jìn)行維護(hù)序无。

添加索引是給某一個(gè)字段，或者說某些字段添加索引衡创。

select ename,sal from emp where ename = 'SMITH';
當(dāng)ename字段沒有添加索引的時(shí)候帝嗡，以上sql語(yǔ)句會(huì)進(jìn)行全表掃描，掃描ename字段中所有的值璃氢。
當(dāng)ename字段添加索引的時(shí)候哟玷，以上sql語(yǔ)句會(huì)根據(jù)索引掃描，快速定位一也。

創(chuàng)建與刪除索引

創(chuàng)建索引對(duì)象：

create index 索引名稱 on 表名(字段名);

刪除索引對(duì)象：

drop index 索引名稱 on 表名;

什么時(shí)候需要索引巢寡？

1. 數(shù)據(jù)量龐大喉脖。(根據(jù)客戶的需求，根據(jù)線上的環(huán)境)
2. 該字段很少的DML操作抑月。(因?yàn)樽侄芜M(jìn)行修改操作树叽，索引也需要維護(hù))
3. 該字段經(jīng)常出現(xiàn)在where子句中。(經(jīng)常根據(jù)哪個(gè)字段維護(hù))

索引底層實(shí)現(xiàn)原理

通過B Tree縮小掃描范圍谦絮，底層索引進(jìn)行了排序题诵，分區(qū)，索引會(huì)攜帶數(shù)據(jù)在表中的"物理地址"挨稿，最終通過索引檢索到數(shù)據(jù)之后仇轻，獲取到關(guān)聯(lián)的物理地址。

而通過物理索引檢索到數(shù)據(jù)之后奶甘，獲取到關(guān)聯(lián)的物理地址篷店，通過物理地址定位表中的數(shù)據(jù)，效率是最高的臭家。

索引分類

• 單一索引：給單個(gè)字段添加索引
• 復(fù)合索引：給多個(gè)字段聯(lián)合起來添加一個(gè)索引
• 主鍵索引：主鍵上會(huì)自動(dòng)添加索引
• 唯一索引：有unique約束的字段會(huì)自動(dòng)添加索引

索引失效

模糊查詢的時(shí)候疲陕，第一個(gè)通配符使用的是%，這個(gè)時(shí)候索引是是失效的钉赁。

select ename from emp where ename like ' %A% ';

數(shù)據(jù)庫(kù)設(shè)計(jì)三范式

1. 第一范式：任何一張表都應(yīng)該有主鍵蹄殃，并且每一個(gè)字段原子性不可再分。
2. 第二范式：建立在第一范式的基礎(chǔ)上你踩，所有非主鍵字段完全依賴主鍵诅岩，不能產(chǎn)生部份依賴。
3. 第三范式：建立在第二范式的基礎(chǔ)上带膜，所有非主鍵字段直接依賴主鍵吩谦，不能產(chǎn)生傳遞依賴。

數(shù)據(jù)庫(kù)設(shè)計(jì)小技巧

多對(duì)多膝藕？三張表式廷，關(guān)系表兩個(gè)外鍵

        t_student學(xué)生表
        sno(pk)       sname
        ---------------------
         1             張三
         2             李四 
         3             王五

         t_teacher 講師表
         tno(pk)            tname
         ----------------------
          1         王老師
          2         張老師
          3         李老師

          t_student_teacher_relation 學(xué)生講師關(guān)系表
          id(pk)        sno(fk)          tno(fk)
          -------------------------------------------
           1          1                 3
           2          1         1
           3          2         2
           4          2         3
           5          3         1
           6          3         3

一對(duì)多？?jī)蓮埍戆磐欤嗟谋砑油怄I滑废。

        班級(jí)t_class
        cno(pk)          cname
        --------------------------
          1              班級(jí)1
          2              班級(jí)2

        學(xué)生t_student
        sno(pk)         sname         classno(fk)
        --------------------------------------------
         101         張1       1
         102         張2       2
         103         張3       2
         104         張4       1
         105         張5       2

一對(duì)一怎么設(shè)計(jì)?

1. 主鍵共享

   _user_login 用戶登陸表
       id(pk)       username        password
       ----------------------------------------
        1             zs                123
        2             ls                456
        
        t_user_detail 用戶詳細(xì)信息表
         id(pk+fk)         realname          tel          ...
        ----------------------------------------------------
           1              張三            11111111112234
    2          李四            12112523432412
   

2. 外鍵唯一

   t_user_login 用戶登陸表
       id(pk)       username        password
       ----------------------------------------
        1             zs                123
        2             ls                456
        
        t_user_detail 用戶詳細(xì)信息表
         id(pk)         realname          tel            userid(fk+unique)      
        ----------------------------------------------------
          1                張三         111111114          2
          2         李四         121432412          1