优化查询、访问量大时的优化
1.優化查詢的方法
1.1使用索引
應盡量避免全表掃描,首先應考慮在 where 及 order by 、group by 涉及的列上建立索引。
1.1.1 為出現在where字句的字段建立一個索引
SELECT category_name FROM mytable WHERE category_id=1;為category_id建立一個索引:
CREATE INDEX mytable_categoryid ON mytable(category_id);若語句為:
SELECT category_name FROM mytable WHERE category_id=1 AND user_id=2;若再給user_id建立一個索引,這不是最佳方法。可建立多重索引:
CREATE INDEX mytable_categoryid_userid ON mytable(category_id,user_id);命名方式:表名—字段1名—字段2名
1.1.2 為order by子句中的字段建立一個索引
SELECT category_name FROM mytable WHERE category_id=1 AND user_id=2 ORDER BY adddate DESC;創建索引:
CREATE INDEX mytable_categoryid_useris_adddate ON mytable(category_id,user_id,adddate);注意:mytable_categoryid_uerid_adddate將會被截斷為:mytable_categoryid_uerid_addda
1.1.3 索引機制
本質:通過不斷地縮小想要獲取數據的范圍來篩選出最終想要的結果,同時把隨機的事件變成順序的事件,也就是說,有了這種索引機制,我們可以總是用同一種查找方式來鎖定數據。
1.2優化SQL語句
1.2.1 通過explain來查看SQL語句執行效果
將explain移到前面,查看執行計劃;
可幫助選擇更好的索引和優化查詢語句,寫出更好的優化語句。
通常我們可以對比較復雜的尤其是涉及到多表的 SELECT 語句, 把關鍵字 EXPLAIN 加到前面, 查看執行計劃。
- 例如: explain select name from news;
1.2.2?任何地方都不要使用 select * from t
用具體的字段列表代替“*” , 不要返回用不到的任何字段.
1.2.3 不在索引列做運算或者使用函數。
1.2.4?使用 limit
查詢盡可能使用 limit 減少返回的行數, 減少數據傳輸時間和帶寬浪費。
1.3 優化數據庫對象
1.3.1?優化表的數據類型
使用 procedure analyse()函數對表進行分析, 該函數可以對表中列的數據類型提出優化建議。
能小就用小。
表數據類型第一個原則是: 使用能正確的表示和存儲數據的最短類型。 這樣可以減少對磁盤空間、 內存、 cpu 緩存的使用。
使用方法: select * from 表名 procedure analyse();
1.3.2?對表進行拆分
通過拆分表可以提高表的訪問效率。
有 2 種拆分方法:
1.3.3?使用中間表來提高查詢速度
創建中間表, 表結構和源表結構完全相同, 轉移要統計的數據到中間表, 然后在中間表上進行統計, 得出想要的結果。
1.4 硬件優化
1.4.1 CPU 的優化
選擇多核和主頻高的 CPU。
1.4.2 內存的優化使用更大的內存。
將盡量多的內存分配給 MYSQL 做緩存。
1.4.3 磁盤 I/O 的優化
1.使用磁盤陣列
RAID 0 沒有數據冗余, 沒有數據校驗的磁盤陳列。 實現 RAID 0至少需要兩塊以上的硬盤, 它將兩塊以上的硬盤合并成一塊, 數據
連續地分割在每塊盤上。
RAID1 是將一個兩塊硬盤所構成 RAID 磁盤陣列, 其容量僅等于一塊硬盤的容量, 因為另一塊只是當作數據“鏡像”。
使用 RAID-0+1 磁盤陣列。 RAID 0+1 是 RAID 0 和 RAID 1 的組合形式。 它在提供與 RAID 1 一樣的數據安全保障的同時, 也提供了與 RAID 0 近似的存儲性能。
2.調整磁盤調度算法
選擇合適的磁盤調度算法, 可以減少磁盤的尋道時間。
1.5 MySQL 自身的優化
對 MySQL 自身的優化主要是對其配置文件 my.cnf 中的各項參數進行優化調整。
- 如指定 MySQL 查詢緩沖區的大小,
- 指定 MySQL 允許的最大連接進程數等。
1.6?應用優化
1.6.1 使用數據庫連接池
1.6.2 使用查詢緩存
它的作用是存儲 select 查詢的文本及其相應結果。
如果隨后收到一個相同的查詢, 服務器會從查詢緩存中直接得到查詢結果。
查詢緩存適用的對象是更新不頻繁的表, 當表中數據更改后, 查詢緩存中的相關條目就會被清空。
2.如果有一個特別大的訪問量到數據庫上, 怎么做優化?
2.1.使用優化查詢的方法(見上面)
2.2.主從復制, 讀寫分離, 負載均衡
目前,大部分的主流關系型數據庫都提供了主從復制的功能,通過配置兩臺(或多臺)數據庫的主從關系,可以將一臺數據庫服務器的數據更新同步到另一臺服務器上。
網站可以利用數據庫的這一功能,實現數據庫的讀寫分離,從而改善數據庫的負載壓力。
一個系統的讀操作遠遠多于寫操作,因此寫操作發向 master,讀操作發向 slaves 進行操作(簡單的輪循算法來決定使用哪個 slave)。
利用數據庫的讀寫分離,Web 服務器在寫數據的時候,訪問主數據庫(Master),主數據庫通過主從復制機制將數據更新同步到從數據庫(Slave),這樣當 Web 服務器讀數據的時候,就可以通過從數據庫獲得數據。這一方案使得在大量讀操作的 Web 應用可以輕松地讀取數據,而主數據庫也只會承受少量的寫入操作,還可以實現數據熱備份,可謂是一舉兩得的方案。
2.2.1 主從復制的原理:
影響 MySQL-A 數據庫的操作,在數據庫執行后,都會寫入本地的日志系統 A 中。
假設,實時的將變化了的日志系統中的數據庫事件操作,通過網絡發給 MYSQL-B。
MYSQL-B 收到后,寫入本地日志系統 B,然后一條條的將數據庫事件在數據庫中完成。
那么,MYSQL-A 的變化,MYSQL-B 也會變化,
這樣就是所謂的 MYSQL 的復制。
在上面的模型中,MYSQL-A 就是主服務器,即 master,MYSQL-B 就是從服務器,即slave。
日志系統 A,其實它是 MYSQL 的日志類型中的二進制日志,也就是專門用來保存修改數據庫表的所有動作,即 bin log。
【注意 MYSQL 會在執行語句之后,釋放鎖之前,寫入二進制日志,確保事務安全】
日志系統 B,并不是二進制日志,由于它是從 MYSQL-A 的二進制日志復制過來的,并不是自己的數據庫變化產生的,有點接力的感覺,稱為中繼日志,即 relay log。
可以發現,通過上面的機制,可以保證 MYSQL-A 和 MYSQL-B 的數據庫數據一致,但是時間上肯定有延遲,即 MYSQL-B 的數據是滯后的。
2.2.2 簡化版
mysql 主(稱 master)從(稱 slave)復制的原理:
2.2.3 簡要原理圖:
2.2.4 主從復制的方式
1.同步復制
主服務器在將更新的數據寫入它的二進制日志(Binlog)文件中后,必須等待驗證所有的從服務器的更新數據是否已經復制到其中,之后才可以自由處理其它進入的事務處理請求。
2.異步復制
主服務器在將更新的數據寫入它的二進制日志(Binlog)文件中后,無需等待驗證更新數據是否已經復制到從服務器中,就可以自由處理其它進入的事務處理請求。
3.半同步復制
主服務器在將更新的數據寫入它的二進制日志(Binlog)文件中后,只需等待驗證其中一臺從服務器的更新數據是否已經復制到其中,就可以自由處理其它進入的事務處理請求,其他的從服務器不用管。
2.3 數據庫分表, 分區, 分庫
2.3.1 分表
見上面描述。
2.3.2 分區
把一張表的數據分成多個區塊,這些區塊可以在一個磁盤上,也可以在不同的磁盤上。
分區后,表面上還是一張表,但數據散列在多個位置,這樣一來,多塊硬盤同時處理不同的請求,從而提高磁盤 I/O 讀寫性能,實現比較簡單。
包括水平分區和垂直分區。
2.3.3 分庫
根據業務不同把相關的表切分到不同的數據庫中,比如 web、bbs、blog 等庫。
?
?
?
總結
以上是生活随笔為你收集整理的优化查询、访问量大时的优化的全部內容,希望文章能夠幫你解決所遇到的問題。
- 上一篇: Executors
- 下一篇: MySQL必知必会——了解SQL/SQL