最佳化MYSQL資料庫的方法

標籤：類型   index   完整   admin   建立   reference   amp   建立表   like   

最佳化MYSQL資料庫的方法 編輯刪除轉載 2016-09-12 14:17:15 標籤：it

1、選取最適用的欄位屬性,儘可能減少定義欄位長度,盡量把欄位設定NOT NULL,例如‘省份,性別‘,最好設定為ENUM

2、使用串連（JOIN）來代替子查詢: a.刪除沒有任何訂單客戶:DELETE FROM customerinfo WHERE customerid NOT in(SELECT customerid FROM orderinfo) b.提取所有沒有訂單客戶:SELECT FROM customerinfo WHERE customerid NOT in(SELECT customerid FROM orderinfo) c.提高b的速度最佳化:SELECT FROM customerinfo LEFT JOIN orderid customerinfo.customerid=orderinfo.customerid WHERE orderinfo.customerid IS NULL

3、使用聯合(UNION)來代替手動建立的暫存資料表 a.建立暫存資料表:SELECT name FROM `nametest` UNION SELECT username FROM `nametest2`

4、交易處理: a.保證資料完整性,例如添加和修改同時,兩者成立則都執行,一者失敗都失敗 mysql_query("BEGIN"); mysql_query("INSERT INTO customerinfo (name) VALUES (‘$name1‘)"; mysql_query("SELECT * FROM `orderinfo` where customerid=".$id"); mysql_query("COMMIT");

5、鎖定表,最佳化交易處理: a.我們用一個 SELECT 語句取出初始資料，通過一些計算，用 UPDATE 語句將新值更新到表中。 包含有 WRITE 關鍵字的 LOCK TABLE 語句可以保證在 UNLOCK TABLES 命令被執行之前， 不會有其它的訪問來對 inventory 進行插入、更新或者刪除的操作 mysql_query("LOCK TABLE customerinfo READ, orderinfo WRITE"); mysql_query("SELECT customerid FROM `customerinfo` where id=".$id); mysql_query("UPDATE `orderinfo` SET ordertitle=‘$title‘ where customerid=".$id); mysql_query("UNLOCK TABLES");

6、使用外鍵,最佳化鎖定表 a.把customerinfo裡的customerid映射到orderinfo裡的customerid, 任何一條沒有合法的customerid的記錄不會寫到orderinfo裡 CREATE TABLE customerinfo ( customerid INT NOT NULL, PRIMARY KEY(customerid) )TYPE = INNODB; CREATE TABLE orderinfo ( orderid INT NOT NULL, customerid INT NOT NULL, PRIMARY KEY(customerid,orderid), FOREIGN KEY (customerid) REFERENCES customerinfo (customerid) ON DELETE CASCADE )TYPE = INNODB; 注意:‘ON DELETE CASCADE‘,該參數保證當customerinfo表中的一條記錄刪除的話同時也會刪除order 表中的該使用者的所有記錄,注意使用外鍵要定義事務安全類型為INNODB;

7、建立索引: a.格式: (普通索引)-> 建立:CREATE INDEX <索引名> ON tablename (索引欄位) 修改:ALTER TABLE tablename ADD INDEX [索引名] (索引欄位) 創表指定索引:CREATE TABLE tablename([...],INDEX[索引名](索引欄位)) (唯一索引

)-> 建立:CREATE UNIQUE <索引名> ON tablename (索引欄位)

修改:ALTER TABLE tablename ADD UNIQUE [索引名] (索引欄位) 創表指定索引:CREATE TABLE tablename([...],UNIQUE[索引名](索引欄位))

(主鍵)-> 它是

唯一索引

,一般在建立表是建立,格式為: CREATA TABLE tablename ([...],PRIMARY KEY[索引欄位])

8、最佳化查詢語句 a.最好在相同欄位進行比較操作,在建立好的索引欄位上盡量減少函數操作

例子1: SELECT * FROM order WHERE YEAR(orderDate)<2008;(慢)

SELECT * FROM order WHERE orderDate<"2008-01-01";(快) 例子2:

SELECT * FROM order WHERE addtime/7<24;(慢) SELECT * FROM order WHERE addtime<24*7;(快)

例子3: SELECT * FROM order WHERE title like "%good%";

SELECT * FROM order WHERE title>="good" and name<"good";

最佳化MYSQL資料庫的方法