KEY
Key 都會自動建立indexKey可以說是index加上某種限制
PRIMARY KEY
Only one PRIMARY KEY in a tableValue must be unique
Value can not be null
Indexing auto
Primary key允許欄位有null
UNIQUE KEY
Value of column must be uniqueCan be null
Indexing auto
Unique key允許欄位有null
CREATE TABLE table_name( //... UNIQUE KEY(index_column_,index_column_2,...) ); //set unique index CREATE UNIQUE INDEX index_name ON table_name(index_column_1,index_column_2,...);
FOREIGN KEY
Indexing auto
Only references to PRIMARY KEY in another table (NO)
Parameter:
ON DELETE {CASCADE | SET NULL | NO ACTION}
ON UPDATE {CASCADE | SET NULL | NO ACTION | RESTRICT}
Eg.
CREATE TABLE item ( id INT AUTO_INCREMENT, PRIMARY KEY(id), user_id INT NOT NULL, FOREIGN KEY(user_id) REFERENCES user(id) ON DELETE CASCADE ON UPDATE CASCADE);
條件
使用innodbEg.
CREATE TABLE product ( prod_id INT NOT NULL, prod_name CHAR(20), PRIMARY KEY (prod_id)) ENGINE = INNODB;
CREATE TABLE order ( order_id INT NOT NULL, prod_id INT, FOREIGN KEY(prod_id) REFERENCES product(prod_id) ON DELETE CASCADE ON UPDATE CASCADE, PRIMARY KEY (order_id)) ENGINE = INNODB;
設定 foreign key
order table 的 prod_id 為foreign key
設定參數
ON DELETE CASCADE
ON UPDATE CASCADE
效果
此時如果 delete product table的某筆資料，
也會 delete order 有foreign key參考到的資料。
此時如果update product table的某筆 prod_id，
也會 update order 有foreign key參考到的 prod_id。
此時drop product table 會出錯，因為 order table 參考到它。
FOREIGN KEY 參數說明
[ON DELETE {CASCADE | SET NULL | NO ACTION | RESTRICT}][ON UPDATE {CASCADE | SET NULL | NO ACTION | RESTRICT}]
當關聯父資料表的主鍵紀錄行被刪除或修改時，InnoDB 對子資料表中紀錄行的處理方式：
CASCADE - 會將有所關聯的紀錄行也會進行刪除或修改。
SET NULL - 會將有所關聯的紀錄行設定成 NULL。
NO ACTION - 有存在的關聯紀錄行時，會禁止父資料表的刪除或修改動作。
RESTRICT - 與 NO ACTION 相同。
FOREIGN KEY效用
- 利用 db schema 防止程式錯誤及阻擋不合法寫入。
- 加速 join 效率。
INDEX
參考Create a indexing data in another table for searching faster.
Index 允許欄位有null
Index 不必要是唯一值
Eg.
CREATE TABLE index_sample( id INT, index id_idx(id));
Eg.1
CREATE INDEX index＿name ON table_name(column_name(length));
If column is CHAR, VARCHAR type, lenght can less then real length.
If is BLOB or TEXT ,must have length assigned.
Eg.2
ALTER table_name ADD INDEX index_name ON column_name(length))
組合索引
把常用的幾個一起查詢的欄位，建立成一個組合索引，效率更好。CREATE TABLE my_table( id INT NOT NULL, user_name VARCHAR(16) NOT NULL, city VARCHAR(50) NOT NULL, age INT NOT NULL ); //三個欄位建立組合索引 ALTER TABLE my_table ADD INDEX name_city_age (uer_name(10),city,age);
等於建立以下三個索引
user_name, city, age
user_name, city
user_name
因為索引組合是從最左邊開始的。
以下查詢會用到這個組合索引
SELECT * FROM my_table WHERE user_name="name" AND city="zz" SELECT * FROM my_table WHERE user_name="abc"以下查詢不會用到這個組合索引
SELECT * FROM my_table WHERE age=10 AND city="zz" SELECT * FROM my_table WHERE city="qqq"
INDEX使用時機
Mysql 在以下時機使用//在 WHERE 中使用 //在 JOIN 中使用 < <= = > >= BETWEEN IN 及某些LIKE LIKE "%qee"，用％開頭的LIKE不會用索引 LIKE "qee%"這樣會用以下要對city 及 age建立索引，
my_table的user_name也要。
SELECT t.Name FROM my_table t LEFT JOIN my_table m ON t.Name=m.user_name WHERE m.age=20 AND m.city="xxx"
索引的缺點
- 索引加快搜尋速度，降低更改速度。因為INSERT, UPDATE, DELETE也要更新索引。
- 索引會佔用一點空間。
使用索引注意事項，以下不會使用索引
- 當mysql優化器判斷全表掃描比索引快
- 索引不會包含有NULL的欄位
- 多使用短索引
- 不鼓勵使用LIKE
- 不要在SQL語句上計算
- 不要在SQL語句上使用函數運算
//這樣有計算 select * from users where YEAR(adddate) //改成這樣 select * from users where adddate<‘2007-01-01’
- 不使用NOT IN 和 <>
- 類型錯誤
//假設 X type 是 char select * from users where X =1 //改成這樣 select * from users where X ='1'
強制使用索引
force index 索引名
優化
my.cnf 中的 key_buffer 值能夠把所有索引容納進去最好SQL 指令前加上 explain 來顯示該 SQL 是否用到索引
