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

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

點(diǎn)此加入我的企鵝群

幾條MySQL小技巧

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

2、如果我們們經(jīng)對數(shù)據(jù)庫中的數(shù)據(jù)行進(jìn)行增刪，那么會出現(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會花費(fèi)大量時(shí)間在更新索引上。

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

數(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)行存儲。

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ū)別。

存儲結(jié)構(gòu)：MyISAM在磁盤上存儲成三個(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支持

索引方面優(yōu)化

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

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

2、myisam的索引各個(gè)索引都相同統(tǒng)一指向磁盤上各個(gè)行的地址，都是輕量級的指針數(shù)據(jù)。缺點(diǎn)是各個(gè)索引的建立不是通過主鍵，查詢沒有聚簇索引查找主鍵快。但其因?yàn)榇鎯Φ氖堑刂?#xff0c;所以在插入新值時(shí)比較方面移動(dòng)改變。

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

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

4、索引長度對查詢也有很大影響，我們應(yīng)該盡量建立短的索引長度，我們可以使用查詢列
SELECT COUNT(DISTINCT LEFT(column)) / COUNT(*) FROM tablename 來測試對column列建立索引時(shí)選取不同的長度，索引的覆蓋率有多大，我們選擇一下接近飽和的n個(gè)長度來建立索引
ALTER TABLE tablename ADD INDEX (column(n)); 來對某一列的前n個(gè)字符建立索引。若前n個(gè)字符相同，我們甚至可以對字符串進(jìn)行反轉(zhuǎn)存儲，然后建立索引。

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

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

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

一旦發(fā)現(xiàn)有數(shù)據(jù)庫性能問題，要及時(shí)解決，一般用慢查詢?nèi)罩居涗洸樵兒?#34;慢"的語句，用EXPLAIN分析查詢和索引使用情況，用PROFILE分析語句執(zhí)行時(shí)的具體資源消耗。

慢查詢?nèi)罩?#xff1a;

1、在my.ini或my.cnf的[mysqld]下添加

• slow_query_log_file=/path //設(shè)置日志存儲路徑
• long_query_time=n //設(shè)置如果語句執(zhí)行時(shí)間達(dá)到n秒，就會被記錄下來

2、然后在MySQL里設(shè)置SET slow_query_log='ON'來開啟慢查詢。

3、記錄下日志后，我們用/bin/目錄下的mysqldumpslow filename來查看日志，其常用參數(shù)如下:

• -g pattern 使用正則表達(dá)式
• -t n返回前n條數(shù)據(jù)
• -s c/t/l/r 以記錄次數(shù)/時(shí)間/查詢時(shí)間/返回記錄數(shù)來排序

EXPLAIN語句

使用方法，在要執(zhí)行的查詢語句前面加EXPLAIN

EXPLAIN SELECT * FROM user;

得到形如下圖的結(jié)果：

下面是對每一項(xiàng)的解釋：

id 查詢語句的id，簡單查詢無意義，多重查詢時(shí)可以看出執(zhí)行查詢的順序

select-type 執(zhí)行的查詢語句的類型，對應(yīng)多重查詢，有simple/primary/union等。

tabel 查詢語句查詢的數(shù)據(jù)表

type 獲得數(shù)據(jù)的類型 常見的類型效率從高到低為 null>const>eq_ref>ref>range>index>all

possible-keys:可能使用到的索引

key 使用到的索引

key_len索引長度

ref 使用哪個(gè)列與索引一起從表中選擇。

rows 查找到數(shù)據(jù)要掃描的大概行數(shù)，可看出索引的優(yōu)劣

extra 常見的有

using filesort 查詢到數(shù)據(jù)后進(jìn)行文件排序，較慢，需要優(yōu)化索引

using where 讀取整行數(shù)據(jù)后進(jìn)行判斷過濾，是否符合where條件

using index 索引覆蓋，即在牽引中已經(jīng)有這存儲了目標(biāo)數(shù)據(jù)，直接讀取索引，很快。

PROFILE

用SELECT @@frofiling來查看PROFILE的開啟狀態(tài)。

如果未開啟，用SET profiling=1來開啟。

開啟之后，再執(zhí)行查詢語句，MySQL會自動(dòng)記錄profile信息。

應(yīng)用show profiles查看所有的sql信息，結(jié)果為 Query_ID Duration Query三列結(jié)果，分別是查詢ID，用時(shí)和所用的sql語句。

我們可以使用

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

type常見有ALL(全部) BLOCK IO(顯示IO相關(guān)開銷) CPU(CPU開銷) MEMORY(內(nèi)存開銷)等

大型存儲方面優(yōu)化

數(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è)表存儲，以減輕查詢壓力。

方式有垂直切分、水平切分和聯(lián)合切分。

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

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

聯(lián)合切分：更多的情況是數(shù)據(jù)表和表中的數(shù)據(jù)量都非常大，則要進(jìn)行聯(lián)合切分，即同時(shí)進(jìn)行垂直和水平分表，將數(shù)據(jù)庫切分為一個(gè)分布式的矩陣來存儲。

這些數(shù)據(jù)庫的優(yōu)化方式，每一種拿出來都可以寫作一篇文章，可謂是博大精深，了解并記憶了這些方式，可以在有需要的時(shí)候進(jìn)行有目的的選擇優(yōu)化，達(dá)到數(shù)據(jù)庫效率的高效。

點(diǎn)關(guān)注，不迷路

好了各位，以上就是這篇文章的全部內(nèi)容了，能看到這里的人呀，都是人才。之前說過，PHP方面的技術(shù)點(diǎn)很多，也是因?yàn)樘嗔?#xff0c;實(shí)在是寫不過來，寫過來了大家也不會看的太多，所以我這里把它整理成了PDF和文檔，如果有需要的可以

點(diǎn)擊進(jìn)入暗號: PHP+「平臺」

---

更多學(xué)習(xí)內(nèi)容可以訪問

阿布阿布：【對標(biāo)大廠】精品PHP架構(gòu)師教程目錄大全，只要你能看完保證薪資上升一個(gè)臺階（持續(xù)更新）??zhuanlan.zhihu.com

以上內(nèi)容希望幫助到大家，很多PHPer在進(jìn)階的時(shí)候總會遇到一些問題和瓶頸，業(yè)務(wù)代碼寫多了沒有方向感，不知道該從那里入手去提升，對此我整理了一些資料，包括但不限于：分布式架構(gòu)、高可擴(kuò)展、高性能、高并發(fā)、服務(wù)器性能調(diào)優(yōu)、TP6，laravel，YII2，Redis，Swoole、Swoft、Kafka、Mysql優(yōu)化、shell腳本、Docker、微服務(wù)、Nginx等多個(gè)知識點(diǎn)高級進(jìn)階干貨需要的可以免費(fèi)分享給大家，需要的可以加入我的 PHP技術(shù)交流群

總結(jié)

以上是生活随笔為你收集整理的mysql编程_PHP数据库编程之一MySQL优化策略概述的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯(cuò)，歡迎將生活随笔推薦給好友。