MySQL KEY, INDEX

KEY

Key 都會自動建立index
Key可以說是index加上某種限制

PRIMARY KEY

Only one PRIMARY KEY in a table
Value must be unique
Value can not be null
Indexing auto
Primary key允許欄位有null

UNIQUE KEY

Value of column must be unique
Can 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);

條件

使用innodb

Eg.

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效用

1. 利用 db schema 防止程式錯誤及阻擋不合法寫入。
2. 加速 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 是否用到索引