php之MySQL資料庫最佳化策略的詳解

本文簡單講述了PHP資料庫程式設計之MySQL最佳化策略。分享給大家供大家參考，具體如下：

前幾天看到一篇文章說到PHP的瓶頸很多情況下不在PHP自身，而在於資料庫。我們都知道，PHP開發中，資料的增刪改查才是核心。為了提升PHP的運作效率，程式設計師不光需要寫出邏輯清晰，效率很高的程式碼，還要能對query語句進行最佳化。雖然我們對資料庫的讀取寫入速度上卻是無能為力，但在一些資料庫類別擴充像memcache、mongodb、redis這樣的資料儲存伺服器的幫助下，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 644255225ddd3043d11a6c0f6afdd4d3 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 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="masterip",
master_user="masteruser",
master_password="masterpasswd";

6、然後使用start slave指令開始進行主從複製。

不要忘記每次修改設定後重新啟動伺服器，然後可以在主從伺服器上用show master/slave status查看主/從狀態。

實作資料庫的讀寫分離要依賴MySQL的中間件，如mysql_proxy，atlas等。透過配置這些中間件來對主從伺服器進行讀寫分離，使從伺服器承擔被讀取的責任，從而減輕主伺服器的負擔。

資料庫的sharding

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

方式有垂直切分、水平切分和聯合切分。

垂直切分：在資料表非常多的時候，把資料庫中關係緊密(如同一模組，經常連接查詢)的表切分出來分別放到不同的主從server上。

水平切分：在表不多，而表裡的資料量非常大的時候，為了加快查詢，可以用哈希等演算法，將一個資料表分為幾個，分別放到不同的伺服器上，加快查詢。水平切分和資料表分區的區別在於其儲存媒體上的不同。

聯合切分：更多的情況是資料表和表中的資料量都非常大，則要進行聯合切分，即同時進行垂直和水平分錶，將資料庫切分為一個分散式的矩陣來儲存。

這些資料庫的最佳化方式，每一種拿出來都可以寫一篇文章，可謂是博大精深，了解並記憶了這些方式，可以在有需要的時候進行有目的的選擇優化，達到資料庫效率的高效能。

接下來我們會進一步總結常用的PHP資料庫類別擴充memcache、redis和mongodb的基本使用場景和使用方式。

以上是php之MySQL資料庫最佳化策略的詳解的詳細內容。更多資訊請關注PHP中文網其他相關文章！