本文簡單講述了PHP數(shù)據(jù)庫編程之MySQL優(yōu)化策略。分享給大家供大家參考，具體如下：

前些天看到一篇文章說到PHP的瓶頸很多情況下不在PHP自身，而在于數(shù)據(jù)庫。我們都知道，PHP開發(fā)中，數(shù)據(jù)的增刪改查是核心。為了提升PHP的運行效率，程序員不光需要寫出邏輯清晰，效率很高的代碼，還要能對query語句進(jìn)行優(yōu)化。雖然我們對數(shù)據(jù)庫的讀取寫入速度上卻是無能為力，但在一些數(shù)據(jù)庫類擴(kuò)展像memcache、mongodb、redis這樣的數(shù)據(jù)存儲服務(wù)器的幫助下，PHP也能達(dá)到更快的存取速度，所以了解學(xué)習(xí)這些擴(kuò)展也是非常必要，這一篇先說一下MySQL常見的優(yōu)化策略。

幾條MySQL小技巧

1、SQL語句中的關(guān)鍵詞最好用大寫來書寫，第一易于區(qū)分關(guān)鍵詞和操作對象，第二，SQL語句在執(zhí)行時，MySQL會將其轉(zhuǎn)換為大寫，手動寫大寫能增加查詢效率(雖然很小)。
2、如果我們們經(jīng)對數(shù)據(jù)庫中的數(shù)據(jù)行進(jìn)行增刪，那么會出現(xiàn)數(shù)據(jù)ID過大的情況，用ALTER TABLE tablename AUTO_INCREMENT=N，使自增ID從N開始計數(shù)。
3、對int類型添加 ZEROFILL 屬性可以對數(shù)據(jù)進(jìn)行自動補(bǔ)0
4、導(dǎo)入大量數(shù)據(jù)時最好先刪除索引再插入數(shù)據(jù)，再加入索引，不然，mysql會花費大量時間在更新索引上。
5、創(chuàng)建數(shù)據(jù)庫書寫sql語句時 ，我們可以在IDE里創(chuàng)建一個后綴為.sql的文件，IDE會識別sql語法，更易于書寫。更重要的是，如果你的數(shù)據(jù)庫丟失了，你還可以找到這個文件，在當(dāng)前目錄下使用/path/mysql -uusername -ppassword databasename < filename.sql來執(zhí)行整個文件的sql語句（注意-u和-p后緊跟用戶名密碼，無空格）。

數(shù)據(jù)庫設(shè)計方面優(yōu)化

1、數(shù)據(jù)庫設(shè)計符合第三范式，為了查詢方便可以有一定的數(shù)據(jù)冗余。

2、選擇數(shù)據(jù)類型優(yōu)先級 int > date,time > enum,char>varchar > blob，選擇數(shù)據(jù)類型時，可以考慮替換，如ip地址可以用ip2long()函數(shù)轉(zhuǎn)換為unsign int型來進(jìn)行存儲。

3、對于char(n)類型，在數(shù)據(jù)完整的情況下盡量較小的的n值。

4、在建表時用partition命令對單個表分區(qū)可以大大提升查詢效率，MySQL支持RANGE，LIST，HASH，KEY分區(qū)類型，其中以RANGE最為常用，分區(qū)方式為：

CREATE TABLE tablename{
}ENGINE innodb/myisam CHARSET utf8 //選擇數(shù)據(jù)庫引擎和編碼
PARTITION BY RANGE/LIST(column),//按范圍和預(yù)定義列表進(jìn)行分區(qū)
PARTITION partname VALUES LESS THAN /IN(n),//命名分區(qū)并詳細(xì)限定分區(qū)的范圍

5、選擇數(shù)據(jù)庫引擎時要注意innodb 和 myisam的區(qū)別。

存儲結(jié)構(gòu)：MyISAM在磁盤上存儲成三個文件。而InnoDB所有的表都保存在同一個數(shù)據(jù)文件中，一般為2GB
事務(wù)支持：MyISAM不提供事務(wù)支持。InnoDB提供事務(wù)支持事務(wù)。
表鎖差異：MyISAM只支持表級鎖。InnoDB支持事務(wù)和行級鎖。
全文索引：MyISAM支持 FULLTEXT類型的全文索引(不適用中文，所以要用sphinx全文索引引擎)。InnoDB不支持。
表的具體行數(shù)：MyISAM保存有表的總行數(shù)，查詢count(*)很快。InnoDB沒有保存表的總行數(shù),需要重新計算。
外鍵：MyISAM不支持。InnoDB支持

索引方面優(yōu)化

1、innodb是聚簇索引，存儲索引時必須有主鍵，如果沒有指定，引擎會自動生成一個隱藏的主鍵，生成一個主索引，索引內(nèi)存放的是主鍵的物理地址，數(shù)據(jù)靠主鍵存放，每次使用索引時要先找到主索引，然后找到主索引下的數(shù)據(jù)。

優(yōu)點通過主鍵查找特別快，缺點是次級索引會變慢，因為需要先通過次級索引（次級索引里是主索引的位置。）找到主索引，然后通過主索引找數(shù)據(jù)。并且如果主鍵無規(guī)律，插入新值時需要移動較多數(shù)據(jù)塊，會影響效率，所以要盡量使用有規(guī)律遞增的int型做主鍵。還有因為數(shù)據(jù)緊跟著主鍵放，所以如果數(shù)據(jù)中有數(shù)據(jù)量特別大的列(text/blob)，innodb查詢時會跳過很多數(shù)據(jù)塊，也會導(dǎo)致慢。

2、myisam的索引各個索引都相同統(tǒng)一指向磁盤上各個行的地址，都是輕量級的指針數(shù)據(jù)。缺點是各個索引的建立不是通過主鍵，查詢沒有聚簇索引查找主鍵快。但其因為存儲的是地址，所以在插入新值時比較方面移動改變。

3、進(jìn)行多條件查詢時，對多條件分別建立索引時，執(zhí)行sql查詢時，MySQL只會選擇一個最貼近的索引來使用，所以如果需要多條件查詢，要建立聯(lián)合索引，即使會造成數(shù)據(jù)冗余。

聯(lián)合索引的BTREE建立方法：對第一個條件建立索引，在第一個索引的BTREE區(qū)域?qū)Φ诙€條件建立索引，以此類推，所以，在使用索引時，不用第一個條件用第二個條件也不會用到聯(lián)合索引。使用索引時要條件要有順序，有序列的使用。

4、索引長度對查詢也有很大影響，我們應(yīng)該盡量建立短的索引長度，我們可以使用查詢列

SELECT COUNT(DISTINCT LEFT(column)) / COUNT(*) FROM tablename  來測試對column列建立索引時選取不同的長度，索引的覆蓋率有多大，我們選擇一下接近飽和的n個長度來建立索引
ALTER TABLE tablename ADD INDEX (column(n));  來對某一列的前n個字符建立索引。若前n個字符相同，我們甚至可以對字符串進(jìn)行反轉(zhuǎn)存儲，然后建立索引。

5、對于經(jīng)常修改導(dǎo)致的索引碎片的維護(hù)方式：ALTER TABLE tablename ENGINE oldengine;即再次應(yīng)用一下表存儲引擎，使其自動維護(hù)；也可以用 OPTIMIZE tablename 命令來進(jìn)行維護(hù)。

數(shù)據(jù)查詢方面優(yōu)化

數(shù)據(jù)庫操作盡量少查詢，有查詢時盡量不在數(shù)據(jù)庫層面上進(jìn)行數(shù)據(jù)操作，而是返回到PHP腳本中操作數(shù)據(jù)，減輕數(shù)據(jù)庫壓力。

一旦發(fā)現(xiàn)有數(shù)據(jù)庫性能問題，要及時解決，一般用慢查詢日志記錄查詢很"慢"的語句，用EXPLAIN分析查詢和索引使用情況，用PROFILE分析語句執(zhí)行時的具體資源消耗。

慢查詢?nèi)罩荆?/strong>

EXPLAIN SELECT * FROM user;

SHOW PFROFILE [type[,type]][FOR QUREY Query_ID][Limit rwo_count [OFFSET offset]]

GRANT REPLICATION SLAVE ON *.* to 'slavename'@'IP' identified by 'root'