MySQL第六篇:索引與子查詢

我巴巴發表於2017-10-28

我把MySQL的內容整理成9篇部落格,學完這9篇部落格雖不能說能成為大神,但是應付一般中小企業的開發已經足夠了,有疑問或建議的歡迎留言討論。

子查詢

子查詢,從原有的查詢語句中 嵌入新的查詢 來得到我們想要的結果,也可稱為巢狀查詢。

一、where 型

1、查詢課程名為“Java”的學生資訊

-- 使用關聯查詢實現
SELECT
    s.*
FROM
    students s,
    class c
WHERE
    s.class_id = c.class_id
AND c.class_name = `JAVA` 

-- 使用子查詢實現
SELECT
    *
FROM
    STUDENTS
WHERE
    CLASS_ID = (
        SELECT
            CLASS_ID
        FROM
            CLASS
        WHERE
            CLASS_NAME = `JAVA`
    )

2、用子查詢實現查詢出PHP 和 Java的學生

select * from students
where class_id IN(
SELECT
    class_id
FROM
    class
WHERE
    class_name = `JAVA`
OR class_name = `PHP`
)

注意:
1、IN 的元素不能超過1000個。
2、in的效率會較低。有時候會用exists代替 in。
3、所有的關聯查詢都可以轉換為子查詢。但是並不是所有的子查詢都能轉化成關聯查詢。

二、from型

把內層的查詢結果集作為臨時表 供外層sql再次查詢。

SELECT
    s.*, c.class_name
FROM
    (
        SELECT
            *
        FROM
            students
        WHERE
            height > 170
    ) s,
    class c
WHERE
    c.class_id = s.class_id

SELECT
    s.*,(select class_name from class c where c.class_id = s.class_id) 課程名稱
FROM
    (
        SELECT
            *
        FROM
            students
        WHERE
            height > 170
    ) s

三、子查詢與inert update delete的結合使用

例:

INSERT INTO students (
    s_id,
    s_name,
    age,
    height,
    sex,
    class_id
)
VALUES
    (
        `J1609004`,
        `餘志堅`,
        26,
        175,
        1,
        (
            SELECT
                class_id
            FROM
                class
            WHERE
                class_name = `JAVA`
        )
    )

將學號為J1609004 的class_id更新成PHP的class_id

UPDATE students
SET class_id = (
    SELECT
        class_id
    FROM
        class
    WHERE
        class_name = `PHP`
)
WHERE
    s_id = `J1609004`

索引

資料庫索引:資料庫管理系統中的一個排序的資料結構,以協助快速查詢,更新資料庫表中的資料。簡單來說,建立了索引後會在資料庫裡面單獨一塊區域建立一個小空間,把資料排好序,這樣查詢起來就快得多。

一、為表新增索引的代價:

  • a.資料庫的儲存空間的佔用
  • b.插入或修改資料的時候要花費較多的時間 – 重新建立索引

二、使用索引的三大原則

  • a.單表資料太少,索引反而會影響速度,資料較少的表不需要建立索引
  • b.資料較多的情況下, where 後的條件、order by 、group by等過濾時,後面的欄位最好加上索引,根據實際情況,選擇primary key,unique index,索引不是越多越好。
  • c.聯合查詢,子查詢等多表操作時對關聯欄位加索引。

三、索引型別

1、單欄位索引:最常用的索引,只有一個欄位,並且不保證欄位值的唯一性。

語法:create index 索引名 on 表名(列名)

create index index_name  on students(s_name);

2、唯一索引:可以根據唯一索引確定唯一的一條資料,用於改善效能和保證資料的完整性。

語法:create unique index 索引名 on 表名(列名)

create unique index index_id on students(s_id);

3、組合索引

語法:create index 索引名 on 表名(列名1,列名2.);

create index index_idname on students(s_name,s_id);
SELECT * FROM STUDENTS WHERE S_ID=`3` and s_name=`小紅`

注意:
1、組合索引頁可以是唯一索引。
2、當查詢條件有組合查詢的第一個欄位(s_name)的時候就一定會用該索引去查詢,無論where後面的查詢條件的順是怎樣的,無論有沒有其他查詢條件。

4、隱含索引:資料庫建立物件的時候自動穿件。主鍵約束 和唯一約束會自動建立索引。

補充:

  • 1、在上例中,如果不存在索引,查詢時將會掃描全表的s_name欄位。
  • 2、在一張表中可以建立多個所以,但是所以不是越多越好。
  • 3、唯一索引的意思是這個欄位的每個值都不一樣,可以根據索引值唯一確定一條資料,而其他索引不能根據索引值確定唯一的資料。

四、修改與刪除索引

修改語法:alter index 索引名 (生產系統不建議使用索引的修改,資料庫執行時無法進行查詢操作)
刪除語法:drop index 索引名 on 表名

DROP INDEX index_name ON talbe_name;
ALTER TABLE table_name DROP INDEX index_name;

五、建立索引指導原則

1.越小的資料型別越好。
2.簡單的資料型別越好,給日期做索引的 使用datetime 。
3.儘量避免使用NULL,指定列為 not null 有空值的列很難進行查詢優化。可以使用0或者一個特殊的值或者空字串來代替。

六、索引的優缺點

  • 優點:加快查詢速度
  • 缺點:降低增 改 刪的速度,增大了表的檔案大小


相關文章