亚洲免费在线-亚洲免费在线播放-亚洲免费在线观看-亚洲免费在线观看视频-亚洲免费在线看-亚洲免费在线视频

MySQL的優化和架構

系統 1829 0
普通MySQL運行,數據量和訪問量不大的話,是足夠快的,但是當數據量和訪問量劇增的時候,那么就會明顯發現MySQL很慢,甚至down掉,那么就要考慮優化我們的MySQL了。

優化無非是從三個角度入手:
第一個是從硬件,增加硬件,增加服務器。
第二個就是對我們的MySQL服務器進行優化,增加緩存大小,開多端口,讀寫分開。
第三個就是我們的應用優化,建立索引,優化SQL查詢語句,建立緩存等等。

我就簡單的說說SQL查詢語句的優化。因為如果我們Web服務器比數據庫服務器多或者性能優良的話,我們完全可以把數據庫的壓力轉嫁到Web服務器 上,因為如果單臺MySQL,或者 Master/Slave 架構的數據庫服務器都負擔比較重,那么就可以考慮把MySQL的運算放到Web服務器上去進行。當然了,如果你Web服務器比數據庫服務器差,那就把壓力 放在數據庫服務器上吧,呵呵。 如果是把MySQL服務器的壓力放在Web服務器上,那么很多運算就需要我們的程序去執行,比如Web程序中全部交給PHP腳本去處理數據。單臺 MySQL服務器,查詢、更新、插入、刪除都在一臺服務器上的話,訪問量一大,你會明顯發現鎖表現象,當對一個表進行更新刪除操作的時候,就會拒絕其他操 作,這樣就會導致鎖表,解決這個問題最簡單直接的辦法就是拿兩臺MySQL服務器,一臺負責查詢(select)操作,另外一臺負責更改 (update/delete/insert),然后進行同步,這樣能夠避免鎖表,如果服務器更多,那么就更好處理了,可以采用分布式數據庫架構和數據的 散列存儲,驚天動地私服下面我們會簡單說一下。

一、SQL的優化和注意事項
現在我們假設我們只有一臺MySQL服務器,所有的select/update/insert/delete操作都是在這上面進行的,我們同時有三臺Web服務器,通過DNS輪巡來訪問,那么我們如何進行我們應用程序和SQL的優化。

1. Where條件 在查詢中,Where條件也是一個比較重要的因素,盡量少并且是合理的where條件是很重要的,在寫每一個where條件的時候都要仔細考慮,盡量在多 個條件的時候,把會提取盡量少數據量的條件放在前面,這樣就會減少后一個where條件的查詢時間。 有時候一些where條件會導致索引無效,當使用了Mysql函數的時候,索引將無效,比如:select * from tbl1 where left(name, 4) = ”hylr”,那么這時候索引無效,還有就是使用LIKE進行搜索匹配的時候,這樣的語句索引是無效的:select * from tbl1 where name like ”%xxx%”,但是這樣索引是有效的:select * from tbl1 where name like ”xxx%”,所以謹慎的寫你的SQL是很重要的。

2. 關聯查詢和子查詢 數據庫一個很重要的特點是關聯查詢,LEFT JOIN 和全關聯,特別是多個表進行關聯,因為每個關聯表查詢的時候,進行掃描的時候都是一個笛卡爾乘積的數量級,掃描數量很大,如果確實是需要進行關聯操作,請 給where或者on的條件進行索引。 關聯操作也是可能交給應用去操作的,看數據量的大小,如果數據量不是非常大,比如10萬條以下,那么就可以交給程序去處理(totododo提出筆誤,特 此修正),程序分別提取左右兩個表的數據,然后進行循環的掃描處理,返回結果,這個過程同樣非常耗費Web服務器的資源,那么就需要取決于你愿意把壓力放 在Web服務器上或者數據庫服務器上了。 子查詢是在mysql5中支持的功能,比如:select * from tbl1 where id in(select id from tbl1),那樣效率是非常非常低,要盡量避免使用子查詢,要是我,絕對不用真封神私服,呵呵。

3.?? 一些耗費時間和資源的操作 SQL語句中一些浪費的操作,比如 DISTINCT、COUNT、GROUP BY、各種MySQL函數。這些操作都是比較耗資源的,我想應用最多的是count字句吧,如果使用count,盡量不要count(*),最好 count一個字段,比如count(id),或者count(1),(據totododo測試效率其實是一樣的),同樣能夠起到統計的作用。如果不是十 分必要,盡量不要使用distinct操作,就是提取唯一值,你完全可以把這個操作交給腳本程序去執行提取唯一值,減少MySQL的負擔。group by 操作也是,確實需要分組的話,請謹慎的操作,如果是小批量的數據,可以考慮交給腳本程序去做。 至于MySQL的函數,估計很多常用,比如有人喜歡把截取字符串也交給MySQL去操作,或者時間轉換操作,使用比較多的函數像 SUBSTR(), CONCAT(), DATE_FORMAT(), TO_DAYS(), MAX(), MIN(), MD5() 等等,這些操作完全可以交給腳本程序去做,減輕MySQL的負擔。

4. 合理的建立索引 索引的提升速度的一個非常重要的手段,索引在對一些經常進行select操作,并且值比較唯一的字段是相當有效的,比如主鍵的id字段,唯一的名字 name字段等等。 但是索引對于唯一值比較少的字段,比如性別gender字段,寥寥無幾的類別字段等,意義不大,因為性別是50%的幾率,索引幾乎沒有意義。對于 update/delete/insert非常頻繁的表,建立索引要慎重考慮,因為這些頻繁的操作同樣對于索引的維護工作量也是很大的,最后反而得不償 失,這個需要自己仔細考慮。索引同樣不是越多越好,適當的索引會起到很關鍵的作用,不適當的索引,反而減低效率維護,增加維護機戰私服索引的負擔。

5. 監控sql執行效率 在select語句前面使用EXPLAIN字句能夠查看當前這個select字句的執行情況,包括使用了什么操作、返回多少幾率、對索引的使用情況如何等 等,能夠有效分析SQL語句的執行效率和合理程度。 另外使用MySQL中本身的慢查詢日志:slow-log,同樣能夠記錄查詢中花費時間比較多的SQL語句,好對相應的語句進行優化和改寫。 另外在MySQL終端下,使用show processlist命令能夠有效的查看當前MySQL在進行的線程,包括線程的狀態,是否鎖表等等,可以實時的查看SQL執行情況,同時對一些鎖表操 作進行優化。

二、數據庫服務器的架構和分布想法
對于服務器的架構設計,這個其實是比較重要的,一個合理的設計,能夠讓應用更好的運行。當然,架構的設計,取決于你的應用和你硬件的實際情況。我就簡單的說說幾種不同的數據庫架構設計方式,權當是一個個人的想法,希望能夠有幫助。
1. 單臺服務器開多進程和端口
單臺MySQL服務器,如果使用長鏈接等等都無法解決負載太大,連接太多的問題,不凡考慮采用一臺MySQL上使用多個端口開啟多個MySQL守護進程的方法來緩解壓力。當然,前提是你的應用必須支持多端口,并且你的cpu和內存足夠運行多個守護進程。
優點 是能夠很好的緩解暫時服務器的壓力,把不同的操作放在不同的端口,或者把不同的項目模塊放在不同的端口去操作,良好的分擔單個守護進程的壓力。
缺點 是數據可能會產生紊亂,同時可能會導致很多未知的莫名風云私服錯誤。呵呵
2. 使用Master/Slave的服務器結構
Mysql本身具有同步功能,完全可以利用這個功能。構建 Master/Slave 的主從服務器結構,最少只需要兩臺MySQL服務器,我們可以把 Master 服務器用戶更新操作,包括 update/delete/insert,把Slave服務器用于查詢操作,包括 select 操作,然后兩機進行同步。 優點 是合理的把更新和查詢的壓力分擔,并且能夠避免鎖表的問題。 缺點 是更新部實時,如果網絡繁忙,可能會存在延遲的問題,并且任何一臺服務器down掉了都很麻煩。
3. 使用分布式的散列存儲
這種結構適合大數據量,并且負載比較大,然后服務器比較充足的情況。分布式存儲結構,簡單的可以是多臺服務器,每臺服務器功能是類似的,但是存儲的數據不 一樣,比如做一個用戶系統,那么把用戶ID在1-10萬以內的存儲在A服務器,用戶ID在10-20萬存儲在B服務器,20-3-萬存儲在C服務器,以此 類推。如果每個用戶訪問的服務器不足,可以構建組服務器,就是每組用戶擁有多臺服務器,比如可以在某用戶組建立兩臺MySQL服務器,一臺Master, 一臺Slave,同樣分離他們的更新和查詢操作,或者可以設計成雙向同步。同時,你的應用程序必須支持跨數據庫和跨服務器的操作能力。 優點 是服務器的負載合理的被平攤,每臺服務器都是負責一部分用戶,如果一臺服務器down掉了,不會影響其他用戶ID的用戶正常訪問。同時添加節點比較容易, 如果又增加了10萬用戶,那么又可以增加一個節點服務器,升級很方便。 缺點 是任何一臺數據庫服務器down掉或者數據丟失,那么這部分服務器的用戶將很郁悶,數據都沒了,當然,這個需要良好的備份機制
補充一: .數據庫的設計
盡量把數據庫設計的更小的占磁盤空間. 1).盡可能使用更小的整數類型.(mediumint就比int更合適). 2).盡可能的定義字段為not null,除非這個字段需要null.(這個規則只適合字段為KEY的情形) 3).如果沒有用到變長字段的話比如varchar,那就采用固定大小的紀錄格式比如char.(CHAR 總是比VARCHR快) 4).表的主索引應該盡可能的短.這樣的話每條紀錄都有名字標志且更高效. 5).只創建確實需要的索引。索引有利于檢索記錄,但是不利于快速保存記錄。如果總是要在表的組合字段上做搜索,那么就在這些字段上創建索引。索引的第一 部分必須是最常使用的字段.如果總是需要用到很多字段,首先就應該多復制這些字段,使索引更好的壓縮。 (這條只適合MYISAM引擎的表,對于INNODB則在保存記錄的時候關系不大,因為INNODB是以事務為基礎的,如果想快速保存記錄的話,特別是大 批量的導入記錄的時候) 6).所有數據都得在保存到數據庫前進行處理。 7).所有字段都得有默認值。 .在某些情況下,把一個頻繁掃描的表分成兩個速度會快好多。在對動態格式表掃描以取得相關記錄時,它可能使用更小的靜態格式表的情況下更是如此。 (具體的表現為:MYISAM表的MERGE類型,以及MYISAM和INNODB通用的分區,詳情見手冊) 9).不會用到外鍵約束的地方盡量不要使用外鍵。
補充二:系統用途
1).及時的關閉對MYSQL的連接。 2).explain 復雜的SQL語句。(這樣能確定你的Select 語句怎么優化最佳) 3).如果兩個關聯表要做比較話,做比較的字段必須類型和長度都一致.(在數據龐大的時候建立INDEX) 4).LIMIT語句盡量要跟order by或者 distinct.這樣可以避免做一次full table scan. 5).如果想要清空表的所有紀錄,建議用truncate table tablename而不是delete from tablename. 不過有一個問題,truncate 不會在事務處理中回滾。因為她要調用create table 真封神私服 語句。 (Truncate Table 語句先刪除表然后再重建,這個是屬于文件級別的,所以自然快N多) 實測例子: song2為INNODB表。 mysql> select count(1) from song2; +———-+ | count(1) | +———-+ |?? 500000 | +———-+ 1 row in set (0.91 sec)
mysql> delete from song2; Query OK, 500000 rows affected (15.70 sec) mysql> truncate table song2; Query OK, 502238 rows affected (0.17 sec)
6).能使用STORE PROCEDURE 或者 USER FUNCTION的時候.(ROUTINE總是減少了服務器端的開銷) 7).在一條insert語句中采用多重紀錄插入奇跡世界私服格式.而且使用load data infile來導入大量數據,這比單純的indert快好多.(在MYSQL中具體表現為:Insert INTO TABLEQ VALUES (),(),…();) (還有就是在MYISAM表中插入大量記錄的時候先禁用到KEYS后面再建立KEYS,具體表現語句: Alter TABLE TABLE1 DISABLE KEYS;Alter TABLE TABLE1 ENABLE KEYS; 而對于INNNODB 表在插入前先 set autocommit=0;完了后:set autocommit=1;這樣效率比較高。) .經常OPTIMIZE TABLE 來整理碎片. 9).還有就是date 類型的數據如果頻繁要做比較的話盡量保存在unsigned int 類型比較快。

MySQL的優化和架構


更多文章、技術交流、商務合作、聯系博主

微信掃碼或搜索:z360901061

微信掃一掃加我為好友

QQ號聯系: 360901061

您的支持是博主寫作最大的動力,如果您喜歡我的文章,感覺我的文章對您有幫助,請用微信掃描下面二維碼支持博主2元、5元、10元、20元等您想捐的金額吧,狠狠點擊下面給點支持吧,站長非常感激您!手機微信長按不能支付解決辦法:請將微信支付二維碼保存到相冊,切換到微信,然后點擊微信右上角掃一掃功能,選擇支付二維碼完成支付。

【本文對您有幫助就好】

您的支持是博主寫作最大的動力,如果您喜歡我的文章,感覺我的文章對您有幫助,請用微信掃描上面二維碼支持博主2元、5元、10元、自定義金額等您想捐的金額吧,站長會非常 感謝您的哦!!!

發表我的評論
最新評論 總共0條評論
主站蜘蛛池模板: 手机在线精品视频每日更新 | 男人资源在线 | 久久精品观看影院2828 | 国产欧美综合在线一区二区三区 | 午夜一级免费视频 | 欧美色精品天天在线观看视频 | 天天干天天操天天操 | 国产日韩欧美在线一区二区三区 | 国产精品九九久久一区hh | 成年女人毛片免费观看中文w | 奇米精品 | 天天干狠狠干 | 神马影院我不卡在线观看 | 久久这里只精品热免费99 | 久久国产免费福利永久 | 成人a大片高清在线观看 | 亚洲欧美日韩中文综合在线不卡 | 国产精品tv | 午夜视频福利 | 麻豆精品一区 | 国产 magnet | 在线免费小视频 | 手机在线国产精品 | 免费一级a毛片夜夜看 | 波多野结衣一二区 | 欧美专区亚洲专区 | 久久国产免费福利永久 | 亚洲已满18点击进入在线观看 | 欧洲美女啪啪 | 色婷婷综合在线视频最新 | 久草视频免费播放 | 色综合91久久精品中文字幕 | 欧美日韩亚洲无线码在线观看 | 亚洲另类视频在线观看 | 四虎成人影院网址 | 国产欧美亚洲精品第一区 | 五月婷婷在线视频 | 亚洲精品福利在线观看 | 日本强不卡在线观看 | 亚洲欧美一区二区久久 | 国模私拍视频在线观看 |