首頁 > 資料庫 > mysql教程 > 對mysql資料庫進行最佳化總結

對mysql資料庫進行最佳化總結

韦小宝
發布: 2018-03-09 10:56:15
原創
1745 人瀏覽過

在PHP的開發中使用mysql資料庫視乎已經成了每個PHP程式設計師的習慣性,而要想PHP操作mysql時,速度可以更快,更方便,那麼我們就得對mysql資料庫進行最佳化處理,對mysql優化在面試中也是很長被提問的,那我們就一起來看看吧!

1. 最佳化你的MySQL查詢快取

#在MySQL伺服器上查詢,可以啟用高速查詢快取。讓資料庫引擎在後台悄悄的處理是提高效能最有效的方法之一。當同一個查詢被執行多次時,如果結果是從快取中提取,那是相當快的。
但主要的問題是,它是那麼容易被隱藏起來以至於我們大多數程式設計師會忽略它。在有些處理任務中,我們實際上是可以阻止查詢快取工作的。

// query cache does NOT work
$r = mysql_query("SELECT username FROM user WHERE signup_date >= CURDATE()");
 // query cache works!
 $today = date("Y-m-d");
$r = mysql_query("SELECT username FROM user WHERE signup_date >= '$today'");
 // query cache does NOT work
$r = mysql_query("SELECT username FROM user WHERE signup_date >= CURDATE()");
 // query cache works!
$today = date("Y-m-d");
$r = mysql_query("SELECT username FROM user WHERE signup_date >= '$today'");
登入後複製

2. 用EXPLAIN讓你的SELECT查詢更清晰





使用EXPLAIN關鍵字是另一個MySQL優化技巧,可以讓你了解MySQL正在進行什麼樣的查詢操作,這可以幫助你發現瓶頸的所在,並顯示出查詢或表結構在哪裡出了問題。
EXPLAIN查詢的結果,可以告訴你那些索引正在被引用,表是如何被掃描和排序的等等。

實作一個SELECT查詢(最好是比較複雜的一個,帶joins方式的),在裡面添加上你的關鍵字解釋,在這裡我們可以使用phpMyAdmin,他會告訴你表中的結果。舉例來說,如果我在執行joins時,正忘記往一個索引中添加列,EXPLAIN能幫助我找到問題的所在。

新增索引到group_id field後

3.利用LIMIT 1取得唯一行



############有時,當你要查詢一張表格是,你知道自己只需要看一行。你可能會去的一條十分獨特的記錄,或者只是剛好檢查了任何存在的記錄數,他們都滿足了你的WHERE子句。 #########在這種情況下,增加一個LIMIT 1會令你的查詢更有效。這樣資料庫引擎發現只有1後就會停止掃描,而不是去掃描整個表或索引。 #########
 // do I have any users from Alabama?   
 // what NOT to do:   
 $r = mysql_query("SELECT * FROM user WHERE state = 'Alabama'");   
 if (mysql_num_rows($r) > 0) {   
     // ...   
 }     
 // much better:   
 $r = mysql_query("SELECT 1 FROM user WHERE state = 'Alabama' LIMIT 1");   
 if (mysql_num_rows($r) > 0) {   
     // ...   
 }
登入後複製
###############4.索引中的檢索欄位################## ###索引不僅是主鍵或唯一鍵。如果你想搜尋表中的任何列,你應該一直指向索引。 ##################5. 保證連接的索引是相同的類型##################如果應用程式中包含多個連接查詢,你需要確保你連結的列在兩邊的表上都被索引。這會影響MySQL如何最佳化內部聯結操作。 ###此外,加入的列,必須是相同類型。例如,你加入一個DECIMAL列,而同時加入另一個表中的int列,MySQL將無法使用其中至少一個指標。即使字元編碼必須同為字串類型。 ###
 // looking for companies in my state   
 $r = mysql_query("SELECT company_name FROM users  
     LEFT JOIN companies ON (users.state = companies.state)  
     WHERE users.id = $user_id");   

 // both state columns should be indexed   
 // and they both should be the same type and character encoding   
 // or MySQL might do full table scans
登入後複製
#########6. 不要使用BY RAND()指令#########

这是一个令很多新手程序员会掉进去的陷阱。你可能不知不觉中制造了一个可怕的平静。这个陷阱在你是用BY RAND()命令时就开始创建了。
如果您真的需要随机显示你的结果,有很多更好的途径去实现。诚然这需要写更多的代码,但是能避免性能瓶颈的出现。问题在于,MySQL可能会为表中每一个独立的行执行BY RAND()命令(这会消耗处理器的处理能力),然后给你仅仅返回一行。

 // what NOT to do:   
 $r = mysql_query("SELECT username FROM user ORDER BY RAND() LIMIT 1");   
 // much better:   
 $r = mysql_query("SELECT count(*) FROM user");   
 $d = mysql_fetch_row($r);   
 $rand = mt_rand(0,$d[0] - 1);   
 $r = mysql_query("SELECT username FROM user LIMIT $rand, 1");
登入後複製

7. 尽量避免SELECT *命令

从表中读取越多的数据,查询会变得更慢。他增加了磁盘需要操作的时间,还是在数据库服务器与WEB服务器是独立分开的情况下。你将会经历非常漫长的网络延迟,仅仅是因为数据不必要的在服务器之间传输。
始终指定你需要的列,这是一个非常良好的习惯。

 // not preferred   
 $r = mysql_query("SELECT * FROM user WHERE user_id = 1");   
 $d = mysql_fetch_assoc($r);   
 echo "Welcome {$d['username']}";   
 // better:   
 $r = mysql_query("SELECT username FROM user WHERE user_id = 1");   
 $d = mysql_fetch_assoc($r);   
 echo "Welcome {$d['username']}";    
 // the differences are more significant with bigger result sets
登入後複製

8. 从PROCEDURE ANALYSE()中获得建议

PROCEDURE ANALYSE()可让MySQL的柱结构分析和表中的实际数据来给你一些建议。如果你的表中已经存在实际数据了,能为你的重大决策服务。

9. 准备好的语句

准备好的语句,可以从性能优化和安全两方面对大家有所帮助。
准备好的语句在过滤已经绑定的变量默认情况下,能给应用程序以有效的保护,防止SQL注入攻击。当然你也可以手动过滤,不过由于大多数程序员健忘的性格,很难达到效果。

 // create a prepared statement   
 if ($stmt = $mysqli->prepare("SELECT username FROM user WHERE state=?")) {    
     // bind parameters   
     $stmt->bind_param("s", $state);    
     // execute   
     $stmt->execute();    
     // bind result variables   
     $stmt->bind_result($username);     
     // fetch value   
     $stmt->fetch();    
     printf("%s is from %s\n", $username, $state);     
     $stmt->close();   
 }
登入後複製

10. 将IP地址存储为无符号整型

许多程序员在创建一个VARCHAR(15)时并没有意识到他们可以将IP地址以整数形式来存储。当你有一个INT类型时,你只占用4个字节的空间,这是一个固定大小的领域。
你必须确定你所操作的列是一个UNSIGNED INT类型的,因为IP地址将使用32位unsigned integer。

$r = "UPDATE users SET ip = INET_ATON('{$_SERVER['REMOTE_ADDR']}') WHERE user_id = $user_id";
登入後複製

11.永远为每张表设置一个ID

我们应该为数据库里的每张表都设置一个ID做为其主键,而且最好的是一个INT型的(推荐使用UNSIGNED),并设置上自动增加的AUTO_INCREMENT标志。
就算是你users表有一个主键叫“email”的字段,你也别让它成为主键。使用VARCHAR类型来当主键会使用得性能下降。另外,在你的程序中,你应该使用表的ID来构造你的数据结构。
而且,在MySQL数据引擎下,还有一些操作需要使用主键,在这些情况下,主键的性能和设置变得非常重要,比如,集群,分区……
在这里,只有一个情况是例外,那就是“关联表”的“外键”,也就是说,这个表的主键,通过若干个别的表的主键构成。我们把这个情况叫做“外键”。比如:有一个“学生表”有学生的ID,有一个“课程表”有课程ID,那么,“成绩表”就是“关联表”了,其关联了学生表和课程表,在成绩表中,学生ID和课程ID叫“外键”其共同组成主键。

12.使用ENUM而不是VARCHAR

#ENUM類型是非常快速且緊湊的。在實際上,其保存的是TINYINT,但其外表上顯示為字串。這樣一來,用這個字段來做一些選項清單變得相當的完美。
如果你有一個字段,例如“性別”,“國家”,“民族”,“狀態”或“部門”,你知道這些字段的取值是有限而且固定的,那麼,你應該使用ENUM而不是VARCHAR。
MySQL也有一個「建議」(見第十條)告訴你怎麼去重新組織你的表格結構。當你有一個VARCHAR字段時,這個建議會告訴你把其改成ENUM類型。使用PROCEDURE ANALYSE() 你可以得到相關的建議。

13.從PROCEDURE ANALYSE()取得建議p程式設計師站

PROCEDURE ANALYSE() 會讓MySQL幫你分析你的字段和其實際的數據,並會給你一些有用的建議。只有表中有實際的數據,這些建議才會變得有用,因為要做一些大的決定是需要有數據作為基礎的。
例如,如果你建立了一個INT欄位作為你的主鍵,然而並沒有太多的數據,那麼,PROCEDURE ANALYSE()會建議你把這個欄位的型別改成MEDIUMINT。或者你使用了一個VARCHAR字段,因為數據不多,你可能會得到一個讓你把它改成ENUM的建議。這些建議,都是可能因為數據不夠多,所以決策做得就不夠準。
在phpmyadmin裡,你可以在查看表時,點擊「Propose table structure」來查看這些建議

一定要注意,這些只是建議,只有當你的表裡的資料越來越多時,這些建議才會變得準確。一定要記住,你才是最終做決定的人

14.盡可能的使用NOT NULL php程式設計師站

##除非你有一個很特別的原因去使用NULL值,你應該總是讓你的欄位保持NOT NULL。這看起來好像有點爭議,請往下看。

首先,問問你自己「Empty」和「NULL」有多大的區別(如果是INT,那就是0和NULL)?如果你覺得它們之間沒有什麼區別,那麼你就不要使用NULL。 (你知道嗎?在Oracle裡,NULL 和Empty的字串是一樣的!)
不要以為NULL 不需要空間,其需要額外的空間,並且,在你進行比較的時候,你的程式會更複雜。當然,這裡並不是說你就不能使用NULL了,現實情況是很複雜的,還是會有些情況下,你需要使用NULL值。
下面摘自MySQL自己的文件:

15. Prepared Statements

Prepared Statements很像儲存過程,是一種運行在背景的SQL語句集合,我們可以從使用prepared statements獲得許多好處,無論是效能問題還是安全性問題。 Prepared Statements可以檢查一些你綁定好的變量,這樣可以保護你的程式不會受到「SQL注入式」攻擊。當然,你也可以手動地檢查你的這些變量,然而,手動的檢查容易出問題,而且很常會被程式設計師忘了。當我們使用一些framework或是ORM的時候,這樣的問題會好一點。
在效能方面,當一個相同的查詢被使用多次的時候,這會為你帶來可觀的效能優勢。你可以為這些Prepared Statements定義一些參數,而MySQL只會解析一次。
雖然最新版本的MySQL在傳輸Prepared Statements是使用二進位形勢,所以這會讓網路傳輸非常有效率。
當然,也有一些情況下,我們需要避免使用Prepared Statements,因為其不支援查詢快取。但據說版本5.1後支援了。 在PHP中要使用prepared statements,你可以查看其使用手冊:mysqli擴充或是使用資料庫抽象層,如:PDO.

16.無緩衝的查詢

正常的情況下,當你在當你在你的腳本中執行一個SQL語句的時候,你的程式會停在那裡直到沒這個SQL語句返回,然後你的程式再往下繼續執行。你可以使用無緩衝查詢來改變這個行為。
關於這個事情,在PHP的文檔中有一個非常不錯的說明:mysql_unbuffered_query()函數:
上面那句話翻譯過來是說,mysql_unbuffered_query()發送一個SQL語句到MySQL而不像mysql_query()一樣去自動fethch和快取結果。這會相當節約很多可觀的內存,尤其是那些會產生大量結果的查詢語句,並且,你不需要等到所有的結果都返回,只需要第一行數據返回的時候,你就可以開始馬上開始工作於查詢結果了。
然而,這會有一些限制。因為你要嘛把所有行都讀走,或是你要在進行下一次的查詢前呼叫 mysql_free_result() 清除結果。而且, mysql_num_rows() 或 mysql_data_seek() 將無法使用。所以,是否使用無緩衝的查詢你需要仔細考慮。

17.把IP位址存成UNSIGNED INT

很多程式設計師都會建立一個VARCHAR(15) 欄位來存放字符串形式的IP而不是整形的IP。如果你用整形來存放,只需要4個字節,並且你可以有定長的字段。而且,這會為你帶來查詢上的優勢,尤其是當你需要使用這樣的WHERE條件:IP between ip1 and ip2。
我們必需要使用UNSIGNED INT,因為IP位址會使用整個32位元的無符號整形。
而你的查詢,你可以使用 INET_ATON()來把一個字串IP轉成一個整形,並使用INET_NTOA()把一個整形轉成一個字串IP。在PHP中,也有這樣的函數 ip2long()和long2ip()。

18.固定長度的表格會更快

#如果表格中的所有欄位都是「固定長度」的,整個表會被認為是“static” 或“fixed-length”。 例如,表中沒有以下類型的欄位: VARCHAR,TEXT,BLOB。只要你包括了其中一個這些字段,那麼這個表就不是「固定長度靜態表」了,這樣,MySQL 引擎會用另一種方​​法來處理。
固定長度的表會提高效能,因為MySQL搜尋得會更快一些,因為這些固定的長度是很容易計算下一個資料的偏移量的,所以讀取的自然也會很快。而如果欄位不是定長的,那麼,每次要找下一條的話,就需要程式找到主鍵。
並且,固定長度的表也更容易被快取和重建。不過,唯一的副作用是,固定長度的字段會浪費一些空間,因為定長的字段無論你用不用,他都是要分配那麼多的空間。 php程式設計師站
使用「垂直分割」技術(見下一條),你可以分割你的表成為兩個一個是定長的,一個則是不定長的。

19.垂直分割

「垂直分割」是一種把資料庫中的表按列變成幾張表的方法,這樣可以降低表格的複雜度和欄位的數目,從而達到最佳化的目的。 (以前,在銀行做過項目,見過一張表有100多個字段,很恐怖)
示例一:在Users表中有一個字段是家庭地址,這個字段是可選字段,相比起,而且你在資料庫操作的時候除了個人資訊外,你並不需要經常讀取或是改寫這個欄位。那麼,為什麼不把他放到另外一張表中呢?這樣會讓你的表有更好

#相關推薦:

MySQL最佳化總結-查詢總條數

#

以上是對mysql資料庫進行最佳化總結的詳細內容。更多資訊請關注PHP中文網其他相關文章!

相關標籤:
來源:php.cn
本網站聲明
本文內容由網友自願投稿,版權歸原作者所有。本站不承擔相應的法律責任。如發現涉嫌抄襲或侵權的內容,請聯絡admin@php.cn
熱門教學
更多>
最新下載
更多>
網站特效
網站源碼
網站素材
前端模板