天天看點
傳回

MySQL 視圖、索引、外鍵關聯政策

目錄

      • 視圖
        • 視圖常用操作
        • 建立視圖的完整文法
        • 不可更新的視圖
      • 索引
        • 索引的優缺點
        • 常見的幾種索引
        • 常用的索引操作
        • 常見的索引方式
        • mysql對使用了索引的sql語句的優化
        • 聯合索引的最左比對原則
        • 索引的最佳實踐
          • 哪些字段适合建立索引
          • 建立索引的注意點
          • sql語句中合理運用索引
      • 主鍵
      • 外鍵關聯政策

視圖

視圖是一張虛表，将查詢結果集儲存起來，作為視圖使用。實際存在的表叫作基本表。

視圖的作用

  • 提高安全性。grant授權使用者隻能操作視圖，通過視圖來操作基本表，可以保護基本表中的資料
  • 提高查詢性能。視圖隻是基本表的一部分，查視圖比查全表快。尤其是多表查詢的時候，查視圖一張表比連接配接多張表查詢要快很多

視圖常用操作 

-- 建立視圖，as指定結果集，假設cs系的id是1
create view view_dep_cs as (select * from tb_student where dep_id=1);  -- 可以把這個視圖作為cs系學生資訊表來使用


-- 修改視圖定義，用新結果集覆寫原來的結果集。如果視圖不存在，會自動建立
create or replace view view_dep_cs as (select id,name from tb_student where dep_id=1);  


-- 從視圖查詢資料
select * from view_dep_cs where name='chy';  --查詢cs系名字是chy的學生


-- 删除視圖
drop view view_dep_cs;

建立視圖的完整文法

algorithm指定視圖執行機制，有3個可選的值

1、merge 合并

不建立臨時表，執行時會先用視圖定義替換視圖名，每次都是操作基本表，并不會提高查詢性能，但可以增改删查。

2、temptable 臨時表

把結果集儲存為臨時表，每次操作的都是臨時表，可以提高查詢性能，但隻讀，不能增删改。

3、undefined 未定義

不指定algorithm就是undefined，使用資料庫設定的預設值，mysql預設使用merge。

如果使用merge，還可以設定一個可選參數：with check option 檢查條件。

建立視圖時設定了條件where dep_id=1，即視圖中的記錄都是dep_id=1的。

如果指定了with check option，往視圖中插入、更新記錄時，都要滿足記錄的dep_id=1這個條件，否則不執行操作；如果不指定with check option，則不要求dep_id=1。

使用merge時往往要設定with check option。

不可更新的視圖

即使使用merge，也不一定可以進行增删改，as指定視圖資料來源，如果視圖來源中使用了一下任一項，則建立的視圖隻讀（隻能查詢）、不能增删改

  • 聚合函數
  • group by子句
  • having子句
  • distinct關鍵字
  • union運算符
  • from來源于多個表或者來源于不可更新的視圖

一句話：不是直接來源于一個基本表的視圖，則該視圖隻讀、不能更新視圖資料。

索引

不使用索引時，要操作某些記錄，需要周遊整張表來找到比對的記錄，時間開銷大。

索引相當于資料表的目錄，根據目錄可直接定位到章節，根據索引可直接定位到資料表的記錄，無需周遊整張表。

索引的優缺點

  • 極大提高了檢索速度，尤其是記錄數很多的時候（優）
  • 索引也是一張表，要占硬碟空間，有額外的空間開銷（缺）
  • 對基本表進行增改删時會同步到索引（維護索引），有額外的時間、資源開銷（缺）

相較于優點，索引的缺點微不足道。

常見的幾種索引

  • 單值索引：索引中隻包含資料表的一列（一個字段）
  • 唯一索引：索引中列的值唯一，一般是資料表的主鍵列
  • 聯合索引：也叫作符合索引，索引中包含資料表的多個列
  • 全文索引：隻能對MyISAM引擎的表使用，且索引中的列要是char、varchar、text等文本類型
  • 覆寫索引：如果索引中已經包含了查詢sql要用到的所有字段，則會直接在索引中進行查詢，不再回表。如果不是覆寫索引，則先查索引定位記錄在表中的位置，再回表查詢。

常用的索引操作 

create index index_ts on tb_student(id,name);  -- on指定使用哪張表的哪些字段來建立索引。經常使用學号、姓名定位學生（記錄），是以使用這2列建立索引
 
show index from tb_student;  -- 檢視某個表上所有的索引
 
drop index index_ts on tb_student;  -- 删除索引

索引、視圖都可以在資料庫管理工具（比如Navicat）中直接操作。索引是在 “設計表” 中操作的。

常見的索引方式

1、b+樹

eg. 以id字段建立索引，假設有7條記錄，id 1~7

MySQL 視圖、索引、外鍵關聯政策

查找id=7的記錄的位址：4 -> 6 -> 7

2、hash  通過hash值直接定位記錄位置

eg. 使用id字段建立索引，查找id=7的記錄：計算7的hash值 -> 根據hash值直接确定記錄在表中的位置。

b+樹要一級一級地找，hash直接定位，效率遠高于b+樹。但一般都是使用b+樹，因為大多數存儲引擎都支援b+樹，hash隻有memory存儲引擎支援。

mysql對使用了索引的sql語句的優化

mysql會對使用了索引的sql語句進行優化，主要優化點如下

  • 自動調整同級别and條件的順序，把使用了索引的字段放前面，盡量走索引。不要過度依賴于mysql自身的優化，盡量把走索引的條件寫在前面。
  • 走索引的字段使用in限定範圍時，in()中的值可以亂序。
  • 如果使用in()的字段走的是唯一索引，且()中隻有一個值時，會自動優化為=等值判斷。 
--eg. username、tel都加了唯一索引, age沒加索引


--mysql會自動調整同級别and條件的順序、盡量用到索引、選擇合适的索引，用and連接配接時條件時條件可以亂序
where age>18 and username='xxx'
 

--使用了or後，同級别的條件不會走索引，慎用or

--三個字段都不會走索引
where username='xxx' and tel='xxx' or age>18
--三個字段都不會走索引，or左邊的()作為整體看待不會走索引，()裡面的各字段自然不會走索引
where (username='xxx' and tel='xxx') or age>18
--or所在級别的條件tel、age不會走索引，但username會走索引
where username='xxx' and (tel='xxx' or age>18)


--模糊查詢盡量使用字首比對，隻有字首比對的模糊查詢才會走索引

--username會走索引
where username like 'x%'
--以下2種的username都不會走索引
where username like '%x%'
where username like '%xx'

聯合索引的最左比對原則

聯合索引的最左比對原則：使用聯合索引時，會從聯合索引中最左邊的字段向右比對，直到遇到沒有使用到聯合索引中的字段，或者遇到<、>、between之類的範圍限定。

--eg. username、tel、email三個字段建立聯合索引

--隻會用到聯合索引的username字段，沒有涉及tel到tel就斷了，不會用到聯合索引中的email字段
where username='xxx' and email='xxx'

--隻會用到聯合索引的username字段，遇到範圍限定like直接斷了
where username='xxx' and tel like '%888%' and email='xxx'

索引的最佳實踐

使用好索引包括2個方面：設計、建立合理的索引， sql語句中合理運用索引。

哪些字段适合建立索引
  • 主鍵
  • 外鍵
  • 頻繁作為條件的字段。eg. 經常要用where name=’ '，那就給name字段建立索引
  • group by分組使用的字段
  • order by排序使用的字段
  • 統計（聚合函數）使用的字段

添加主鍵時，會自動給主鍵列建立唯一索引（Unique）；添加外鍵時，會自動給外鍵列建立普通索引（Normal）。

建立索引後，從表中查找比對的記錄時資料庫會自動使用合适的索引。

建立索引的注意點

1、記錄少的表不必建立索引，一般以1千行記錄為界限。

建立索引有額外的空間開銷，增改删時維護索引也有額外的時間開銷，記錄數少時全表掃描的性能相對較高，沒有必要建立索引。

2、頻繁增改删的字段，維護索引開銷大，不适合用于建立索引。

3、區分度低的字段建立索引沒有多大意義，盡量選擇區分度高（列值重複少）的字段建立索引；列值較長的字段不适合作為索引，就算要作為索引，也盡量隻取列值的字首作為索引。

4、聯合索引會覆寫其中包含的單列索引，存在聯合索引時再建立其中的單列索引沒有意義。

5、聯合索引遵循最左比對原則，設計聯合索引時，盡量将最左字段設定為值區分度高、使用頻率高的字段。

6、一張表建立的索引不宜太多，一般不超過5個索引。

一張表建立太多的索引，空間占用多，資料庫會花費更多的時間在索引的選擇上，且增删改需要同時維護幾個索引，對性能的影響較大。

7、及時删除廢棄的、沒有必要的索引，避免維護索引帶來的不必要開銷。

sql語句中合理運用索引

字段加了索引後，sql語句使用索引時需要注意以下幾點

1、資料庫字段為字元串類型時，sql語句中該字段的值加不加引号都行，但加了引号才會走索引，不加引号則不會走索引。

--值沒有加引号，username加了索引也不會走索引
select ... from user where username=xxx;

--username會走索引
select ... from user where username='xxx';

2、where子句中，比較運算符左邊的列參與了數學運算，則該列不會走索引；參與了函數調用的列不管是放在比較運算符的哪邊，都不會走索引。

--比較運算符>=左邊的age參與了數學運算，不會走索引
select ... from user where age-18>=0

--可以将數學運算移到右邊 (以下2個sql的age都會走索引)
select ... from user where age>=0+18
--或者把要走索引的列移到比較運算符的右邊
select ... from user where 0+18<=age

3、使用聯合索引時，考慮到最左比對原則，盡量把條件中聯合索引中的字段放在範圍限定的前面，且字段順序盡量與聯合索引中的字段順序保持一緻，保證盡可能多的使用聯合索引中的字段。

主鍵

mysql建立表時，如果沒有指定主鍵，且存在 整型+not null+自增 的字段，則該字段必須設定為主鍵才合法。

mysql允許建立表時不指定主鍵，未指定主鍵時mysql會自動把 整型+not null 的字段作為主鍵，如果不存在這樣的字段，InnoDB會自動生成一個隐式主鍵，這個隐式主鍵我們看不到。

盡量手動給每個表設定主鍵，保證主鍵可控。

外鍵關聯政策

eg. tb_order通過外鍵user_id關聯tb_user的主鍵id，當update、delete tb_user的id時，如何處理與之對應的tb_order中的記錄？

MySQL 視圖、索引、外鍵關聯政策

設計外鍵時，mysql提供了4種外鍵關聯政策

1、RESTRICT  限制（預設）

如果有外鍵關聯了tb_user的id，則tb_user不能删除被關聯的記錄、不能更新關聯記錄id字段的值（會報錯）。

如果要删除記錄、更新id字段的值，需要先切斷關聯關系，比如先删除tb_order中與之關聯的記錄、或者把相關記錄外鍵字段的值置為null。

2、CASCADE  級聯（最常用）

删除tb_user中的記錄時，會自動删除tb_order中與之關聯的記錄；修改tb_user中id字段的值時，會自動修改tb_order中與之關聯的記錄的外鍵字段的值（同步變化）。

3、NO ACTION  什麼都不做

删除、更新tb_user中的user_id字段的值時，tb_order中與之關聯的記錄不作任何處理。此種政策需要存儲引擎支援，如果存儲引擎不支援，會自動換為RESTRICT。

4、SET NULL  置為NULL

删除tb_user的記錄，或者更新id字段的值，會自動将tb_order中與之關聯的記錄的外鍵字段的值置為NULL。

這種方式有一個要求：設計tb_order時，外鍵字段不能用NOT NULL限制。

一般使用CASCADE，未設定外鍵關聯政策時預設為RESTRICT（為了資料安全）。

mysql 視圖 索引 外鍵關聯政策
上一篇: 720. 詞典中最長的單詞
下一篇: mysql索引的選擇_MySQL索引 - 字首索引和索引選擇性

繼續閱讀