PHP mysql 最佳化-php教程-PHP中文網

這篇文章介紹的內容是關於PHP mysql 優化，有著一定的參考價值，現在分享給大家，有需要的朋友可以參考一下

幾條MySQL小技巧

1、SQL語句中的關鍵字最好用大寫#來書寫，第一個易於區分關鍵字和操作對象，第二，SQL語句在執行時，MySQL會將其轉換為大寫，手動寫大寫能增加查詢效率(雖然很小)。
2、如果我們們經對資料庫中的資料行進行增刪，那麼會出現資料ID過大的情況，用ALTER TABLE tablename AUTO_INCREMENT=N ，使自增ID從N開始計數。
3、新增int型別 ZEROFILL 屬性可以自動補0

# #4、匯入大量資料時最好先刪除索引再插入數據，再加入索引，不然，mysql會花費大量時間在更新索引上。

5、建立資料庫書寫sql語句時，我們可以在IDE裡建立一個後綴為.sql的文件，IDE會辨識sql語法，更容易書寫。更重要的是，如果你的資料庫遺失了，你還可以找到這個文件，在目前目錄下使用
/path/mysql -uusername -ppassword databasename < filename.sql來執行整個檔案的sql語句（注意-u和-p後面緊跟著使用者名稱密碼，無空格）。

#

#資料庫設計方面最佳化

1 、資料庫設計符合第三範式，為了查詢方便可以有一定的資料冗餘。
2、選擇資料型別優先權int > date,time > enum,char>varchar > blob，選擇資料型別時，可以考慮替換，如ip位址可以用ip2long()函式轉換為unsign int型來進行儲存。

3、對於char(n)類型，在資料完整的情況下盡量較小的的n值。

4、在建表時用partition指令對單一

表格分區

可以大幅提升查詢效率，MySQL支援RANGE，LIST，HASH，KEY分區類型，其中以RANGE最為常用，分區方式為：　　CREATE TABLE tablename{　　}ENGINE innodb/myisam CHARSET utf8 //選擇資料庫引擎和編碼　　PARTITION BY RANGE/LIST(column),///##　PARTITION BY RANGE/LIST(column),///列表進行分區
　　PARTITION partname VALUES LESS THAN /IN(n),//命名分區並詳細限定分區的範圍5、選擇資料庫引擎時要注意innodb 和myisam的區別

。

　　儲存結構：MyISAM在磁碟上儲存成三個檔案。而InnoDB所有的表格都保存在同一個資料檔案中，一般為2GB

　　事務支援：MyISAM不提供事務支援。 InnoDB提供事務支援事務。 ###　　表鎖差異：MyISAM只支援表級鎖。 InnoDB支援事務和行級鎖定。 ###　　全文索引：MyISAM支援 FULLTEXT類型的全文索引(不適用中文，所以要用sphinx全文索引引擎)。 InnoDB不支援。 ###　　表的具體行數：MyISAM保存有表格的總行數，查詢count(*)很快。 InnoDB沒有保存表格的總行數,需要重新計算。 ###　　外鍵：MyISAM不支援。 InnoDB支援######### ############索引方面最佳化#########1、innodb是叢集索引，儲存索引時必須有主鍵，如果沒有指定，引擎會自動產生一個隱藏的主鍵，產生一個######主索引######，索引內存放的是主鍵的實體位址，資料靠主鍵存放，每次使用索引時要先找到主索引，然後找到主索引下的資料。 ######優點透過主鍵查找特別快，缺點是次級索引會變慢，因為需要先透過次級索引（次級索引裡是主索引的位置。）找到主索引，然後再透過主索引找數據。且如果主鍵無規律，插入新值時需要移動較多資料塊，會影響效率，所以要盡量使用有規律遞增的int型做主鍵。還有因為資料緊跟著主鍵放，所以如果資料中有資料量特別大的列(text/blob)，innodb查詢時會跳過很多資料塊，也會導致慢。 ###

2、myisam的索引各個索引都相同統一指向磁碟上各個行的位址，都是輕量級的指標資料。缺點是各個索引的建立不是透過主鍵，查詢沒有叢集索引查找主鍵快。但其因為儲存的是位址，所以在插入新值時比較方面移動改變。
3、進行多條件查詢時，對多條件分別建立索引時，執行sql查詢時，MySQL只會選擇一個最貼近的索引來使用，所以如果需要多條件查詢，要建立聯合索引，即使會造成資料冗餘。
聯合索引的BTREE建立方法：對第一個條件建立索引，在第一個索引的BTREE區域對第二個條件建立索引，以此類推，所以，在使用索引時， #不用第一個條件用第二個條件也不會用到聯合索引。使用索引時要條件要有順序，有序列的使用。
4、索引長度對查詢也有很大影響，我們應該盡量建立短的索引長度，我們可以使用查詢列
SELECT COUNT(DISTINCT LEFT(column)) / COUNT(*) FROM tablename 來測試對column列建立索引時選取不同的長度，索引的覆蓋率有多大，我們選擇一下接近飽和的n個長度來建立索引
ALTER TABLE tablename ADD INDEX (column(n)); 來對某一列的前n個字元建立索引。若前n個字元相同，我們甚至可以對字串進行反轉存儲，然後建立索引。
5、對於經常修改導致的索引碎片的維護方式：ALTER TABLE tablename ENGINE oldengine;即再次應用一下表存儲引擎，使其自動維護；也可以用 OPTIMIZE tablename 命令來進行維護。

資料查詢方面優化
#資料庫操作盡量少查詢，有查詢時盡量不在資料庫層面上進行數據操作，而是返回PHP腳本操作數據，減輕資料庫壓力。
一旦發現有資料庫效能問題，要及時解決，一般用慢查詢日誌記錄查詢很"慢"的語句，用EXPLAIN分析查詢和索引使用情況，以PROFILE分析語句執行時的具體資源消耗。
慢查詢日誌：
1、在my.ini或my.cnf的[mysqld]下加上
slow_query_log_file=/ path //設定日誌儲存路徑
long_query_time=n //設定如果語句執行時間達到n秒，就會被記錄下來
2、然後在MySQL裡設定SET slow_query_log='ON'來開啟慢查詢。
3、記錄下日誌後，我們用/bin/目錄下的mysqldumpslow filename來查看日誌，其常用參數如下:
　　-g pattern 使用正規表示式
　　-t n傳回前n條資料
　　-s c/t/l/r 以記錄次數/時間/查詢時間/傳回記錄數來排序

EXPLAIN語句
#使用方法，在要執行的查詢語句前面加上EXPLAIN
EXPLAIN SELECT * FROM user;
得到形如下圖的結果：

下面是對每一項的解釋：
id 查詢語句的id，簡單查詢無意義，多重查詢時可以看出執行查詢的順序
select-type 執行的查詢語句的類型，對應多重查詢，有simple/primary/union等。
tabel 查詢語句查詢的資料表
type 取得資料的型別常見的型別效率由高到低為null>const>eq_ref>ref>range>index> ;all
possible-keys:可能使用的索引
key 所使用的索引
key_len索引長度
ref 使用哪個欄位與索引一起從表中選擇。
rows 查找到資料要掃描的大概行數，可看出索引的優劣
extra 常見的有
using filesort 查詢到資料後進行檔案排序，較慢，需要優化索引
using where 讀取整行資料後進行判斷過濾，是否符合where條件
using index 索引覆蓋，即在牽引中已經有這儲存了目標數據，直接讀取索引，很快。

PROFILE
用SELECT @@frofiling來查看PROFILE的開啟狀態。
如果未開啟，用SET profiling=1來開啟。
開啟之後，再執行查詢語句，MySQL會自動記錄profile資訊。
應用show profiles查看所有的sql信息，結果為 Query_ID Duration Query三列結果，分別是查詢ID，用時和所用的sql語句。
我們可以使用
SHOW PFROFILE [type[,type]][FOR QUREY##Query_ID][Limit rwo_count [OFFSET offset]]type常見有ALL(全部) BLOCK IO(顯示IO相關開銷) CPU(CPU開銷) MEMORY(記憶體開銷)等

#大型儲存方面最佳化
資料庫主從複製和讀寫分離1、master將改變記錄到二進位日誌中，slave將master的二進位拷貝到它的中繼日誌中，重新將資料傳回它自己的資料中，達到複製主伺服器資料的目的。

主從複製可以用作：資料庫負載平衡、資料庫備份、讀寫分離等功能。

2、設定主伺服器master
　　修改my.ini/my.conf
　　[mysqld]
　　log-bin=mysql-bin //啟用二進位日誌
　　server-id=102 / /伺服器唯一ID
3、設定從伺服器slave
　　log-bin=mysql-bin //啟用二進位日誌
　　server-id=226 //伺服器唯一ID
4、在主伺服器上授權從伺服器
　　GRANT REPLICATION SLAVE ON *.* to 'slavename'@'IP' identified by 'root'
5、在從伺服器上使用
　　change master to
　　　　master_host="master" ,
　　　　master_user="masteruser",
　　　　master_password="masterpasswd";
6、然後使用start slave指令開始進行主從複製。
不要忘記在每次修改設定後重新啟動伺服器，然後可以在主從伺服器上用show master/slave status查看主/從狀態。
實作資料庫的讀寫分離要依賴MySQL的中間件，如mysql_proxy，atlas等。透過配置這些中間件來對主從伺服器進行讀寫分離，使從伺服器承擔被讀取的責任，從而減輕主伺服器的負擔。

資料庫的sharding在資料庫中資料表中的資料量非常龐大的時候，無論是索引或快取等壓力都很大，對資料庫進行sharding，使其分別以多個資料庫伺服器或多個表存儲，以減輕查詢壓力。
方式有垂直切分、水平切分和聯合切分。

垂直切分：在資料表非常多的時候，把資料庫中關係緊密(如同一模組，經常連接查詢)的表切分出來分別放到不同的主從server。
水平切分：在表不多，而表裡的資料量非常大的時候，為了加快查詢，可以用哈希等演算法，將一個資料表分成幾個，分別放到不同的伺服器上，加快查詢。水平切分和資料表分區的區別在於其儲存媒體上的不同。
聯合切分：更多的情況是資料表和表中的資料量都非常大，則要進行聯合切分，即同時進行垂直和水平分表，將資料庫切分為一個分散式的矩陣來儲存。這些資料庫的最佳化方式，每一種拿出來都可以寫一篇文章，可謂是博大精深，了解並記憶了這些方式，可以在有需要的時候進行有目的的選擇優化，達到資料庫效率的高效。