一、索引操作
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 與慢查詢日誌決定增刪。
- 視圖簡化複雜查詢、隔離表結構變更、實現權限控制(授權視圖不授權基表)。
- 避免在視圖上再建視圖(嵌套視圖),優化器難以處理,效能不可控。