MySQL優化技巧全接觸

2018-09-18 19:02:45 濟南網站建設

前些天看到一篇文章說到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 < 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最為常用,分區方式為:

1.CREATE TABLE tablename{

2.}ENGINE innodb/myisam CHARSET utf8 //選擇數據庫引擎和編碼

3.PARTITION BY RANGE/LIST(column),//按范圍和預定義列表進行分區

4.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

1.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語句。

我們可以使用

1.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、在主服務器上授權從服務器

1. 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上。

水平切分:在表不多,而表里的數據量非常大的時候,為了加快查詢,可以用哈希等算法,將一個數據表分為幾個,分別放到不同的服務器上,加快查詢。水平切分和數據表分區的區別在于其存儲介質上的不同。

聯合切分:更多的情況是數據表和表中的數據量都非常大,則要進行聯合切分,即同時進行垂直和水平分表,將數據庫切分為一個分布式的矩陣來存儲。

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

十大色情片电影