總結mysql資料庫優化操作

這篇文章主要為大家介紹了關於mysql資料庫常見的最佳化操作，文章總結的都是個人日常開發使用mysql資料庫的經驗所得，其中包括Index索引、少用SELECT*、EXPLAIN SELECT以及開啟查詢緩存等相關資料，相信會對大家有一定的參考價值，需要的朋友們下面來一起看看吧。

前言

對於一個以資料為中心的應用，資料庫的好壞直接影響到程式的效能，因此資料庫效能至關重要。所以mysql資料庫的最佳化操作大家都要有所了解，本文就主要總結了mysql資料庫中常見的最佳化操作，下面話不多說了，來看看詳細的介紹吧。

一、Index索引

將Index放第一位，不用說，這種優化方式我們一直都在悄悄使用，那便是主鍵索引。有時候我們可能並不在意，如果定義適合的索引，資料庫查詢效能(速度)將提高幾倍甚至幾十倍。

普通索引

作用是提高查詢速度。

建表，建立索引

CREATE TABLE tbl_name(
字段名称 字段类型 [完整性约束条件],
~
index [索引名] (column_name)
);

建立索引

CREATE INDEX index_name ON tab_name (column_name)

刪除索引

DROP INDEX index_name FROM tab_name

查看索引

SHOW index FROM tab_name

主鍵索引

作用是加速查詢和唯一約束

建表，建立索引

CREATE TABLE tbl_name(
字段名称 字段类型 [完整性约束条件],
~
PRIMARY KEY(column_name)
);

建立索引

ALTER TABLE tab_name ADD PRIMARY KEY(column_name)

刪除索引

ALTER TABLE tab_name DROP PRIMAY KEY(column_name)

唯一索引

作用是加速查詢和唯一約束

建表，建立索引

CREATE TABLE tbl_name(
字段名称 字段类型 [完整性约束条件],
~
unique [索引名] (column_name)
);

建立索引

CREATE UNIQUE INDEX index_name ON tab_name (column_name)

刪除索引

DROP UNIQUE INDEX index_name FROM tab_name

二、少用SELECT*

#可能有的人查詢資料庫時，遇到要查詢的都會select，這是不恰當的行為。我們應該取我們要使用的數據，而不是全取，因為當我們select時，會增加web伺服器的負擔，增加網路傳輸的負載，查詢速度自然就下降 。

三、EXPLAIN SELECT

#對於這個功能估計很多人都沒見過，但這裡強烈建議使用。 explain顯示了mysql如何使用索引來處理select語句以及連接表。可以幫助選擇更好的索引和寫出更優化的查詢語句。主要用髮就是在select前加上explain即可。

EXPLAIN SELECT [查找字段名] FROM tab_name ...

四、開啟查詢快取

#大多數的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 t where num=10 or Name = 'admin'

可以这样查询：

select id from t where num = 10
union all
select id from t where Name = 'admin'

八、多使用varchar/nvarchar

使用varchar/nvarchar代替 char/nchar ，因为首先变长字段存储空间小，可以节省存储空间，其次对于查询来说，在一个相对较小的字段内搜索效率显然要高些。

九、避免大数据量返回

这里要考虑使用limit，来限制返回的数据量，如果每次返回大量自己不需要的数据，也会降低查询速度。

十、where子句优化

where 子句中使用参数，会导致全表扫描,因为SQL只有在运行时才会解析局部变量，但优化程序不能将访问计划的选择推迟到运行时；它必须在编译时进行选择。然 而，如果在编译时建立访问计划，变量的值还是未知的，因而无法作为索引选择的输入项。

应尽量避免在 where 子句中对字段进行表达式操作，避免在where子句中对字段进行函数操作这将导致引擎放弃使用索引而进行全表扫描。不要在 where 子句中的“=”左边进行函数、算术运算或其他表达式运算，否则系统将可能无法正确使用索引。

以上是總結mysql資料庫優化操作的詳細內容。更多資訊請關注PHP中文網其他相關文章！