存儲引擎

概念

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

Oracle中有對應(yīng)的機(jī)制，但不叫做存儲引擎，Oracle中稱為"表的存儲方式"

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

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

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

show engines \G

常見的存儲引擎

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

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

2. InnoDB

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

   優(yōu)點(diǎn)：支持事務(wù)、行級鎖、外鍵等。這種存儲引擎數(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ù)和索引都是存儲在內(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  //列級約束
    );
    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 【表級約束】
      );
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約束只有列級約束，沒有表級約束。

主鍵約束(primary key)

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

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

添加主鍵

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

drop table if exists t_user;
      create table t_user(
          id int primary key,  //列級約束
      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ù)掛鉤，例如：拿著銀行卡的卡號做主鍵、拿著身份證號做為主鍵。(不推薦使用）

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

因?yàn)橐院蟮臉I(yè)務(wù)一旦發(fā)生改變的時(shí)候，主鍵也可能需要隨著發(fā)生變化，但有的時(shí)候沒有辦法變化，因?yàn)樽兓赡軙?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字段自動維護(hù)一個(gè)自增的數(shù)字，從1開始，以1遞增。
    username varchar(255)
);

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

外鍵約束

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

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

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

外鍵，說簡單點(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語句。

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

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

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

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

只有DML語句

• insert
• delete
• update

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

事務(wù)的特性

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

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

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

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

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

開啟事務(wù)

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

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

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

start transaction;

回滾事務(wù)

rollback;   

提交事務(wù)

commit

索引

索引的概念與作用

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

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

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

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

索引雖然可以提高檢索效率，但是不能隨意的添加索引，因?yàn)樗饕彩菙?shù)據(jù)庫當(dāng)中的對象，也需要數(shù)據(jù)庫不斷的維護(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語句會進(jìn)行全表掃描，掃描ename字段中所有的值。
當(dāng)ename字段添加索引的時(shí)候，以上sql語句會根據(jù)索引掃描，快速定位。

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

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

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

刪除索引對象：

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ū)，索引會攜帶數(shù)據(jù)在表中的"物理地址"，最終通過索引檢索到數(shù)據(jù)之后，獲取到關(guān)聯(lián)的物理地址。

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

索引分類

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

索引失效

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

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

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

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

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

多對多？三張表，關(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

一對多？兩張表，多的表加外鍵。

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

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

一對一怎么設(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