常見的Mysql資料庫優化總結_ZenDei技術網路在線

索引 1.主鍵索引作用：唯一約束和提高查詢速度 2.普通索引作用：提高查詢速度 3.唯一索引作用：唯一約束和提高查詢速度 4.主鍵和唯一索引的區別主鍵是一種約束，唯一索引是一種索引，兩者在本質上是不同的。主鍵創建後一定包含一個唯一性索引，唯一性索引並不一定就是主鍵。唯一性索引列允許空值，而主 ...

索引

1.主鍵索引

作用：唯一約束和提高查詢速度

#建表時創建主鍵索引
create table `table_name`(
    `id` int unsigned not null auto_increment,
    primary key(`id`)
);

#刪除主鍵索引
alter table `table_name` drop primary key(`id`);

#已有表增加主鍵索引
alter table `table_name` add primary key(`id`);

2.普通索引

作用：提高查詢速度

#建表時創建普通索引
create table `table_name`(
    `name` char(50) not null,
    index [index_name] (`name`)
);

#創建普通索引
create index index_name on table_name (name);

#刪除普通索引
drop index index_name from table_name;

#查看索引
show indx from table_name;

3.唯一索引

作用：唯一約束和提高查詢速度

#建表時創建唯一索引
create table `table_name`(
    `name` char(50) not null,
    unique [index_name] (`name`)
);

#創建唯一索引
create unique index index_name on table_name (name);

#刪除唯一索引
drop unique index index_name from table_name;

4.主鍵和唯一索引的區別

　　主鍵是一種約束，唯一索引是一種索引，兩者在本質上是不同的。主鍵創建後一定包含一個唯一性索引，唯一性索引並不一定就是主鍵。唯一性索引列允許空值，而主鍵列不允許為空值。主鍵列在創建時，已經預設為空值 + 唯一索引了。

　　主鍵可以被其他表引用為外鍵，而唯一索引不能。一個表最多只能創建一個主鍵，但可以創建多個唯一索引。主鍵更適合那些不容易更改的唯一標識，如自動遞增列、身份證號等。

　　在 RBO 模式下，主鍵的執行計劃優先順序要高於唯一索引。兩者可以提高查詢的速度。

少用SELECT *

　　使用select時，應該取我們要用的數據，而不是全取。因為當我們select *時，會增加web伺服器的負擔，增加網路傳輸的負載，查詢速度自然就下降。

EXPLAIN SELECT

　　explain顯示了mysql如何使用索引來處理select語句以及連接表。可以幫助選擇更好的索引和寫出更優化的查詢語句。主要用法就是在select前加上explain即可。

EXPLAIN SELECT [查找欄位名] FROM tab_name ...

　　在 EXPLAIN 的幫助下，你就知道什麼時候該給表添加索引，以使用索引來查找記錄從而讓 SELECT 運行更快。

開啟查詢緩存

　　大多數的MySQL伺服器都開啟了查詢緩存。這是提高性最有效的方法之一，而且這是被MySQL的資料庫引擎處理的。當有很多相同的查詢被執行了多次的時候，這些查詢結果會被放到一個緩存中，這樣，後續的相同的查詢就不用操作表而直接訪問緩存結果了。

　　第一步把query_cache_type設置為ON，然後查詢系統變數have_query_cache是否可用：

show variables like 'have_query_cache'

　　之後，分配記憶體大小給查詢緩存，控制緩存查詢結果的最大值。相關操作在配置文件中進行修改。

使用NOT NULL

　　很多表都包含可為 NULL （空值）的列，即使應用程式井不需要保存 NULL 也是如此，這是因為可為 NULL 是列的預設屬性。通常情況下最好指定列為 NOT NULL，除非真的需要存儲 NULL 值。
如果查詢中包含可為 NULL 的列，對 MySQL 來說更難優化，因為可為 NULL 的列使得索引、索引統計和值比較都更複雜。可為NULL 的列會使用更多的存儲空間，在 MySQL 里也需要特殊處理。當可為NULL 的列被索引肘，每個索引記錄需要一個額外的位元組，在 MyISAM 里甚至還可能導致固定大小的索引（例如只有一個整數列的索引）變成可變大小的索引。
　　通常把可為 NULL 的列改為 NOT NULL 帶來的性能提升比較小，所以（調優時）沒有必要首先在現有schema中查找井修改掉這種情況，除非確定這會導致問題。但是，如果計劃在列上建索引，就應該儘量避免設計成可為 NULL 的列。當然也有例外，例如值得一提的是，InnoDB 使用單獨的位（bit ）存儲 NULL 值，所以對於稀疏數據由有很好的空間效率。但這一點不適用於MyISAM 。

存儲引擎的選擇

　　對於如何選擇MyISAM和InnoDB,如果你需要事務處理或是外鍵，那麼InnoDB可能是比較好的方式。如果你需要全文索引，那麼通常來說MyISAM是好的選擇，因為這是系統內建的，然而，我們其實並不會經常地去測試兩百萬行記錄。所以，就算是慢一點，我們可以通過使用Sphinx從InnoDB中獲得全文索引。
　　數據的大小，是一個影響你選擇什麼樣存儲引擎的重要因素，大尺寸的數據集趨向於選擇InnoDB方式，因為其支持事務處理和故障恢復。資料庫的在小決定了故障恢復的時間長短，InnoDB可以利用事務日誌進行數據恢復，這會比較快。而MyISAM可能會需要幾個小時甚至幾天來乾這些事，InnoDB只需要幾分鐘。
　　你操作資料庫表的習慣可能也會是一個對性能影響很大的因素。比如： COUNT() 在 MyISAM表中會非常快，而在InnoDB表下可能會很痛苦。而主鍵查詢則在InnoDB下會相當相當的快，但需要小心的是如果我們的主鍵太長了也會導致性能問題。大批的inserts語句在MyISAM下會快一些，但是updates在InnoDB 下會更快一些——尤其在併發量大的時候。
　　所以，到底你檢使用哪一個呢？根據經驗來看，如果是一些小型的應用或項目，那麼MyISAM也許會更適合。當然，在大型的環境下使用MyISAM也會有很大成功的時候，但卻不總是這樣的。如果你正在計劃使用一個超大數據量的項目，而且需要事務處理或外鍵支持，那麼你真的應該直接使用InnoDB方式。但需要記住InnoDB的表需要更多的記憶體和存儲，轉換100GB的MyISAM 表到InnoDB 表可能會讓你有非常壞的體驗。

避免在 where 子句中使用 or 來連接

　　如果一個欄位有索引，一個欄位沒有索引，將導致引擎放棄使用索引而進行全表掃描，如：

select id from table where num=10 or name = 'admin'

　　可以這樣查詢：

select id from table where num = 10
union all
select id from table where name = 'admin'

避免大數據量返回

這裡要考慮使用limit，來限制返回的數據量，如果每次返回大量自己不需要的數據，也會降低查詢速度。

where子句優化

　　where 子句中使用參數，會導致全表掃描,因為SQL只有在運行時才會解析局部變數，但優化程式不能將訪問計劃的選擇推遲到運行時；它必須在編譯時進行選擇。然而，如果在編譯時建立訪問計劃，變數的值還是未知的，因而無法作為索引選擇的輸入項。
　　應儘量避免在 where 子句中對欄位進行表達式操作，避免在where子句中對欄位進行函數操作這將導致引擎放棄使用索引而進行全表掃描。不要在 where 子句中的“=”左邊進行函數、算術運算或其他表達式運算，否則系統將可能無法正確使用索引。

（註：文章出處）