萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> MySQL 教學

MySQL 索引與視圖完整操作指南

一、索引操作

1. 建表時定義索引

CREATE TABLE student (
    stu_id INT PRIMARY KEY,
    stu_name VARCHAR(5) NOT NULL,
    stu_tel INT(5) UNIQUE,
    stu_score INT(2),
    INDEX idx_stu_id (stu_id)
) ENGINE=InnoDB;

2. 事後建立索引

-- 普通索引
CREATE INDEX idx_name ON student(stu_name);

-- 唯一索引
CREATE UNIQUE INDEX uk_tel ON student(stu_tel);

-- 多列索引(最左前綴原則)
CREATE INDEX idx_name_score ON student(stu_name, stu_score);

-- 指定索引類型(B-Tree / Hash)
CREATE INDEX idx_hash ON student(stu_id) USING HASH;  -- 僅 MEMORY/HEAP 支援

3. ALTER TABLE 新增索引

ALTER TABLE student ADD INDEX idx_score (stu_score);
ALTER TABLE student ADD UNIQUE INDEX uk_name (stu_name);

4. 刪除索引

DROP INDEX idx_name ON student;
ALTER TABLE student DROP INDEX uk_tel;

5. 查看索引

SHOW INDEX FROM student\G
SELECT * FROM information_schema.STATISTICS WHERE TABLE_NAME='student';

二、視圖操作

1. 建立視圖

CREATE VIEW view_student AS
SELECT stu_id, stu_name, stu_score FROM student WHERE stu_score >= 60;

2. 使用視圖

SELECT * FROM view_student;
-- 視圖可像表一樣 JOIN、WHERE、GROUP BY

3. 可更新視圖條件

  • 視圖定義來自單一基表。
  • 無聚合、DISTINCT、GROUP BY、UNION、子查詢。
  • 選單包含基表所有非預設欄位(插入時)。

4. 修改/刪除視圖

ALTER VIEW view_student AS SELECT ...;  -- 或 DROP VIEW + CREATE
DROP VIEW view_student;

5. WITH CHECK OPTION

CREATE VIEW v_high_score AS
SELECT * FROM student WHERE stu_score > 90
WITH CHECK OPTION;  -- 插入/更新時強制滿足 WHERE 條件

效能與維護提示

  • 索引提升讀取、降低寫入;根據 EXPLAIN 與慢查詢日誌決定增刪。
  • 視圖簡化複雜查詢、隔離表結構變更、實現權限控制(授權視圖不授權基表)。
  • 避免在視圖上再建視圖(嵌套視圖),優化器難以處理,效能不可控。
copyright © 萬盛學電腦網 all rights reserved