Mysql的優(yōu)化提升PHP的運(yùn)行效率

這篇文章主要講解了“Mysql的優(yōu)化提升PHP的運(yùn)行效率”，文中的講解內(nèi)容簡單清晰，易于學(xué)習(xí)與理解，下面請大家跟著小編的思路慢慢深入，一起來研究和學(xué)習(xí)“Mysql的優(yōu)化提升PHP的運(yùn)行效率”吧！

數(shù)據(jù)庫主從復(fù)制和讀寫分離

1、master將改變記錄到二進(jìn)制日志中，slave將master的二進(jìn)制拷貝到它的中繼日志中，重新將數(shù)據(jù)返回到它自己的數(shù)據(jù)中，達(dá)到復(fù)制主服務(wù)器數(shù)據(jù)的目的。
主從復(fù)制可以用作：數(shù)據(jù)庫負(fù)載均衡、數(shù)據(jù)庫備份、讀寫分離等功能。
2、配置主服務(wù)器master
　　修改my.ini/my.conf
　　[mysqld]
　　log-bin=mysql-bin //啟用二進(jìn)制日志
　　server-id=102 //服務(wù)器唯一ID
3、配置從服務(wù)器slave
　　log-bin=mysql-bin //啟用二進(jìn)制日志
　　server-id=226 //服務(wù)器唯一ID
4、在主服務(wù)器上授權(quán)從服務(wù)器
GRANT REPLICATION SLAVE ON *.* to 'slavename'@'IP' identified by 'root'
5、在從服務(wù)器上使用
　　change master to 
　　master_host="masterip",
　　master_user="masteruser",
　　master_password="masterpasswd";
6、然后使用start slave命令開始進(jìn)行主從復(fù)制。
不要忘記在每次修改配置后重啟服務(wù)器，然后可以在主從服務(wù)器上用show master/slave status查看主/從狀態(tài)。
實(shí)現(xiàn)數(shù)據(jù)庫的讀寫分離要依賴MySQL的中間件，如mysql_proxy，atlas等。通過配置這些中間件來對主從服務(wù)器進(jìn)行讀寫分離，使從服務(wù)器承擔(dān)被讀取的責(zé)任，從而減輕主服務(wù)器的負(fù)擔(dān)。

數(shù)據(jù)庫的sharding
在數(shù)據(jù)庫中數(shù)據(jù)表中的數(shù)據(jù)量非常龐大的時(shí)候，無論是索引還是緩存等壓力都很大，對數(shù)據(jù)庫進(jìn)行sharding，使之分別以多個(gè)數(shù)據(jù)庫服務(wù)器或多個(gè)表存儲(chǔ)，以減輕查詢壓力。方式有垂直切分、水平切分和聯(lián)合切分。

垂直切分：在數(shù)據(jù)表非常多的時(shí)候，把數(shù)據(jù)庫中關(guān)系緊密(如同一模塊，經(jīng)常連接查詢)的表切分出來分別放到不同的主從server上。

水平切分：在表不多，而表里的數(shù)據(jù)量非常大的時(shí)候，為了加快查詢，可以用哈希等算法，將一個(gè)數(shù)據(jù)表分為幾個(gè)，分別放到不同的服務(wù)器上，加快查詢。水平切分和數(shù)據(jù)表分區(qū)的區(qū)別在于其存儲(chǔ)介質(zhì)上的不同。

聯(lián)合切分：更多的情況是數(shù)據(jù)表和表中的數(shù)據(jù)量都非常大，則要進(jìn)行聯(lián)合切分，即同時(shí)進(jìn)行垂直和水平分表，將數(shù)據(jù)庫切分為一個(gè)分布式的矩陣來存儲(chǔ)。
這些數(shù)據(jù)庫的優(yōu)化方式，每一種拿出來都可以寫作一篇文章，可謂是博大精深，了解并記憶了這些方式，可以在有需要的時(shí)候進(jìn)行有目的的選擇優(yōu)化，達(dá)到數(shù)據(jù)庫效率的高效。

索引方面優(yōu)化

在MySQL中，索引屬于存儲(chǔ)引擎級別的概念，不同存儲(chǔ)引擎對索引的實(shí)現(xiàn)方式是不同的，下面主要討論MyISAM和InnoDB兩個(gè)存儲(chǔ)引擎的索引實(shí)現(xiàn)方式。

MyISAM索引實(shí)現(xiàn)

MyISAM引擎使用B+Tree作為索引結(jié)構(gòu)，葉節(jié)點(diǎn)的data域存放的是數(shù)據(jù)記錄的地址。下圖是MyISAM索引的原理圖：

這里設(shè)表一共有三列，假設(shè)我們以Col1為主鍵，則圖1是一個(gè)MyISAM表的主索引（Primary key）示意?？梢钥闯鯩yISAM的索引文件僅僅保存數(shù)據(jù)記錄的地址。在MyISAM中，主索引和輔助索引（Secondary key）在結(jié)構(gòu)上沒有任何區(qū)別，只是主索引要求key是唯一的，而輔助索引的key可以重復(fù)。如果我們在Col2上建立一個(gè)輔助索引，則此索引的結(jié)構(gòu)如下圖所示：

同樣也是一顆B+Tree，data域保存數(shù)據(jù)記錄的地址。因此，MyISAM中索引檢索的算法為首先按照B+Tree搜索算法搜索索引，如果指定的Key存在，則取出其data域的值，然后以data域的值為地址，讀取相應(yīng)數(shù)據(jù)記錄。

MyISAM的索引方式也叫做“非聚集”的，之所以這么稱呼是為了與InnoDB的聚集索引區(qū)分。

InnoDB索引實(shí)現(xiàn)

雖然InnoDB也使用B+Tree作為索引結(jié)構(gòu)，但具體實(shí)現(xiàn)方式卻與MyISAM截然不同。

第一個(gè)重大區(qū)別是InnoDB的數(shù)據(jù)文件本身就是索引文件。從上文知道，MyISAM索引文件和數(shù)據(jù)文件是分離的，索引文件僅保存數(shù)據(jù)記錄的地址。而在InnoDB中，表數(shù)據(jù)文件本身就是按B+Tree組織的一個(gè)索引結(jié)構(gòu)，這棵樹的葉節(jié)點(diǎn)data域保存了完整的數(shù)據(jù)記錄。這個(gè)索引的key是數(shù)據(jù)表的主鍵，因此InnoDB表數(shù)據(jù)文件本身就是主索引。

圖3

圖3是InnoDB主索引（同時(shí)也是數(shù)據(jù)文件）的示意圖，可以看到葉節(jié)點(diǎn)包含了完整的數(shù)據(jù)記錄。這種索引叫做聚集索引。因?yàn)镮nnoDB的數(shù)據(jù)文件本身要按主鍵聚集，所以InnoDB要求表必須有主鍵（MyISAM可以沒有），如果沒有顯式指定，則MySQL系統(tǒng)會(huì)自動(dòng)選擇一個(gè)可以唯一標(biāo)識數(shù)據(jù)記錄的列作為主鍵，如果不存在這種列，則MySQL自動(dòng)為InnoDB表生成一個(gè)隱含字段作為主鍵，這個(gè)字段長度為6個(gè)字節(jié)，類型為長整形。

第二個(gè)與MyISAM索引的不同是InnoDB的輔助索引data域存儲(chǔ)相應(yīng)記錄主鍵的值而不是地址。換句話說，InnoDB的所有輔助索引都引用主鍵作為data域。例如，圖4為定義在Col3上的一個(gè)輔助索引：

圖4

這里以英文字符的ASCII碼作為比較準(zhǔn)則。聚集索引這種實(shí)現(xiàn)方式使得按主鍵的搜索十分高效，但是輔助索引搜索需要檢索兩遍索引：首先檢索輔助索引獲得主鍵，然后用主鍵到主索引中檢索獲得記錄。

了解不同存儲(chǔ)引擎的索引實(shí)現(xiàn)方式對于正確使用和優(yōu)化索引都非常有幫助，例如知道了InnoDB的索引實(shí)現(xiàn)后，就很容易明白為什么不建議使用過長的字段作為主鍵，因?yàn)樗休o助索引都引用主索引，過長的主索引會(huì)令輔助索引變得過大。

再例如，用非單調(diào)的字段作為主鍵在InnoDB中不是個(gè)好主意，因?yàn)镮nnoDB數(shù)據(jù)文件本身是一顆B+Tree，非單調(diào)的主鍵會(huì)造成在插入新記錄時(shí)數(shù)據(jù)文件為了維持B+Tree的特性而頻繁的分裂調(diào)整，十分低效，而使用自增字段作為主鍵則是一個(gè)很好的選擇。

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

在每一個(gè)消耗大量時(shí)間的查詢案例中，都能看到一些不必要的額外操作、某些操作被額外地重復(fù)了很多次、某些操作執(zhí)行得太慢等。優(yōu)化查詢的目的就是減少和消除這些操作所花費(fèi)的時(shí)間。

一、首選要優(yōu)化數(shù)據(jù)訪問

查詢性能底下最基本的原因是訪問的數(shù)據(jù)太多。所以，對于低效的查詢，一般通過兩個(gè)步驟來分析：

確認(rèn)應(yīng)用程序是否在檢索大量超過需要的數(shù)據(jù)。這通常意味著訪問了太多的行，但有時(shí)候也可能是訪問了太多的列。確認(rèn)MySQL服務(wù)器層是否在分析大量超過需要的數(shù)據(jù)行。

1.1、是否向數(shù)據(jù)庫請求了不需要的數(shù)據(jù)

在訪問數(shù)據(jù)庫時(shí)，應(yīng)該只請求需要的行和列，請求多余的行和列會(huì)消耗MySQL服務(wù)器的CPU和內(nèi)存資源，并增加網(wǎng)絡(luò)開銷。

1、在處理分頁時(shí)，應(yīng)該使用LIMIT限制MySQL只返回需要的數(shù)據(jù)，而不是向應(yīng)用程序返回全部數(shù)據(jù)后，再由應(yīng)用程序過濾不需要的行。

2、多表關(guān)聯(lián)時(shí)，或獲取單表數(shù)據(jù)時(shí)，盡量避免不加思考地使用SELECT *

3、當(dāng)一些數(shù)據(jù)被多次使用時(shí)可以考慮將數(shù)據(jù)緩存起來，避免每次使用都要到MySQL查詢。

1.2、MySQL是否在掃描額外的記錄，應(yīng)該讓MySQL使用最合適的方式查詢數(shù)據(jù)

對于MySQL，最簡單的衡量查詢開銷有三個(gè)指標(biāo)：響應(yīng)時(shí)間、掃描的行數(shù)和返回的行數(shù)。這里主要考慮提高掃描的方式，即查詢數(shù)據(jù)的方式。

查詢數(shù)據(jù)的方式有全表掃描、索引掃描、范圍掃描、唯一索引查詢、常數(shù)引用等。這些查詢方式，速度從慢到快，掃描的行數(shù)也是從多到少?？梢酝ㄟ^EXPLAIN語句中的type列反應(yīng)查詢采用的是哪種方式。

通常可以通過添加合適的索引改善查詢數(shù)據(jù)的方式，使其盡可能減少掃描的數(shù)據(jù)行，加快查詢速度。

例如，當(dāng)發(fā)現(xiàn)查詢需要掃描大量的數(shù)據(jù)行但只返回少數(shù)的行，那么可以考慮使用覆蓋索引，即把所有需要用到的列都放到索引中。這樣存儲(chǔ)引擎無須回表獲取對應(yīng)行就可以返回結(jié)果了。

二、重構(gòu)查詢的方法

設(shè)計(jì)查詢的時(shí)候需要考慮是否需要把一個(gè)復(fù)雜的查詢分成多個(gè)簡單的查詢。在我的印象中，曾經(jīng)無數(shù)次聽到一個(gè)經(jīng)驗(yàn)法則：可以在數(shù)據(jù)庫中做的事不要放在應(yīng)用程序中，數(shù)據(jù)庫比我們想象的要厲害的多。這個(gè)經(jīng)驗(yàn)法則是在華夏基金使用Oracle編寫SQL時(shí)一位Oracle牛人告訴我的，后來我把它使用到MySQL上，真是吃盡苦頭。

當(dāng)然這其中的原因有Oracle和MySQL原本就不是一樣的處理邏輯，并且現(xiàn)在的網(wǎng)絡(luò)通信、查詢解析和優(yōu)化的代價(jià)并沒有以前那么高啦。再次說明，經(jīng)驗(yàn)法則有在某種特定籠子里才有效。

分解復(fù)雜的查詢：

可以將一個(gè)大查詢切分成多個(gè)小查詢執(zhí)行，每個(gè)小查詢只完成整個(gè)查詢?nèi)蝿?wù)的一小部分，每次只返回一小部分結(jié)果。

刪除舊的數(shù)據(jù)是一個(gè)很好的例子。

如果只用一條語句一次性執(zhí)行一個(gè)大的刪除操作，則可能需要一次鎖住很多數(shù)據(jù)，占滿整個(gè)事務(wù)日志，耗盡系統(tǒng)資源、阻塞很多小的但重要的查詢。將一個(gè)大的刪除操作分解成多個(gè)較小的刪除操作可以將服務(wù)器上原本一次性的壓力分散到多次操作上，盡可能小地影響MySQL性能，減少刪除時(shí)鎖的等待時(shí)間，同時(shí)也減少了MySQL主從復(fù)制的延遲。這個(gè)方法我一直在用。

另一個(gè)例子是分解關(guān)聯(lián)查詢，即對每個(gè)要關(guān)聯(lián)的表進(jìn)行單表查詢，然后將結(jié)果在應(yīng)用程序中進(jìn)行關(guān)聯(lián)。我在之前一家公司和一位在阿里待過很多年的同事一起編碼時(shí)，他就是這么干的。后來我在心中默默地鄙視著他，因?yàn)槲倚睦镉羞@么一個(gè)經(jīng)驗(yàn)法則（可以在數(shù)據(jù)庫中做的事不要放在應(yīng)用程序中，數(shù)據(jù)庫比我們想象的要厲害的多），并且我在行動(dòng)上也是保持能用一個(gè)SQL解決的事絕對不會(huì)用兩個(gè)SQL。

這么做當(dāng)然處理經(jīng)驗(yàn)法則的原因之外還有一個(gè)原因是：獲取數(shù)據(jù)的邏輯盡量與業(yè)務(wù)代碼分離，這樣以后在切換數(shù)據(jù)庫時(shí)也很方便。實(shí)際上是這樣嗎？未必啊。那次的無知讓我吃盡苦頭啊，后來因?yàn)镾QL的性能問題再把我寫的大部分SQL進(jìn)行分解。

用分解關(guān)聯(lián)查詢的方式重構(gòu)查詢有如下的優(yōu)勢：

讓緩存的效率更高。許多應(yīng)用程序可以方便地緩存單表查詢對應(yīng)的結(jié)果對象。將查詢分解后，執(zhí)行單個(gè)查詢可以減少鎖的競爭。在應(yīng)用層做關(guān)聯(lián)，可以更容易對數(shù)據(jù)庫進(jìn)行拆分，更容易做到高性能和可擴(kuò)展。查詢本身效率也可能會(huì)有所提升?？梢詼p少冗余記錄的查詢。在應(yīng)用層做關(guān)聯(lián)查詢，

意味著對于某條記錄應(yīng)用只需要查詢一次，而在數(shù)據(jù)庫中做關(guān)聯(lián)查詢，則可能需要重復(fù)地訪問一部分?jǐn)?shù)據(jù)。從這點(diǎn)看，這樣的重構(gòu)還可能會(huì)減少網(wǎng)絡(luò)和內(nèi)存的消耗。更進(jìn)一步，這樣做相當(dāng)于在應(yīng)用中實(shí)現(xiàn)了哈希關(guān)聯(lián)，而不是使用MySQL的嵌套循環(huán)關(guān)聯(lián)。某些場景哈希關(guān)聯(lián)的效率要高很多。

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

1、數(shù)據(jù)庫設(shè)計(jì)符合第三范式，為了查詢方便可以有一定的數(shù)據(jù)冗余。
2、選擇數(shù)據(jù)類型優(yōu)先級 int > date,time > enum,char>varchar > blob，選擇數(shù)據(jù)類型時(shí)，可以考慮替換，如ip地址可以用ip2long()函數(shù)轉(zhuǎn)換為unsign int型來進(jìn)行存儲(chǔ)。
3、對于char(n)類型，在數(shù)據(jù)完整的情況下盡量較小的的n值。
4、在建表時(shí)用partition命令對單個(gè)表分區(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ù)庫引擎時(shí)要注意innodb 和 myisam的區(qū)別。
　　存儲(chǔ)結(jié)構(gòu)：MyISAM在磁盤上存儲(chǔ)成三個(gè)文件。而InnoDB所有的表都保存在同一個(gè)數(shù)據(jù)文件中，一般為2GB
　　事務(wù)支持：MyISAM不提供事務(wù)支持。InnoDB提供事務(wù)支持事務(wù)。
　　表鎖差異：MyISAM只支持表級鎖。InnoDB支持事務(wù)和行級鎖。
　　全文索引：MyISAM支持 FULLTEXT類型的全文索引(不適用中文，所以要用sphinx全文索引引擎)。InnoDB不支持。
　　表的具體行數(shù)：MyISAM保存有表的總行數(shù)，查詢count(*)很快。InnoDB沒有保存表的總行數(shù),需要重新計(jì)算。
　　外鍵：MyISAM不支持。InnoDB支持

幾條MySQL小技巧

1、SQL語句中的關(guān)鍵詞最好用大寫來書寫，第一易于區(qū)分關(guān)鍵詞和操作對象，第二，SQL語句在執(zhí)行時(shí)，MySQL會(huì)將其轉(zhuǎn)換為大寫，手動(dòng)寫大寫能增加查詢效率(雖然很小)。

2、如果我們們經(jīng)對數(shù)據(jù)庫中的數(shù)據(jù)行進(jìn)行增刪，那么會(huì)出現(xiàn)數(shù)據(jù)ID過大的情況，用ALTER TABLE tablename AUTO_INCREMENT=N，使自增ID從N開始計(jì)數(shù)。

3、對int類型添加 ZEROFILL 屬性可以對數(shù)據(jù)進(jìn)行自動(dòng)補(bǔ)0

4、導(dǎo)入大量數(shù)據(jù)時(shí)最好先刪除索引再插入數(shù)據(jù)，再加入索引，不然，mysql會(huì)花費(fèi)大量時(shí)間在更新索引上。

5、創(chuàng)建數(shù)據(jù)庫書寫sql語句時(shí) ，我們可以在IDE里創(chuàng)建一個(gè)后綴為.sql的文件，IDE會(huì)識別sql語法，更易于書寫。更重要的是，如果你的數(shù)據(jù)庫丟失了，你還可以找到這個(gè)文件，在當(dāng)前目錄下使用/path/mysql -uusername -ppassword databasename < filename.sql來執(zhí)行整個(gè)文件的sql語句（注意-u和-p后緊跟用戶名密碼，無空格）。

感謝各位的閱讀，以上就是“Mysql的優(yōu)化提升PHP的運(yùn)行效率”的內(nèi)容了，經(jīng)過本文的學(xué)習(xí)后，相信大家對Mysql的優(yōu)化提升PHP的運(yùn)行效率這一問題有了更深刻的體會(huì)，具體使用情況還需要大家實(shí)踐驗(yàn)證。這里是億速云，小編將為大家推送更多相關(guān)知識點(diǎn)的文章，歡迎關(guān)注！