如何選擇正確的存儲設(shè)備和方案-linuxtone.org

1、MySQL介紹和優(yōu)化分享,,2009-07,,MySQL基本介紹MySQL優(yōu)化方式MySQL技巧分享Q & A,目錄索引,,MySQL基本介紹,,什么是MySQL,MySQL是一個小型關(guān)系型數(shù)據(jù)庫管理系統(tǒng)，開發(fā)者為瑞典MySQL AB公司。目前MySQL被廣泛地應(yīng)用在Internet上的中小型網(wǎng)站中。由于其體積小、速度快、總體擁有成本低，尤其是開放源碼這一特點，許多中小型網(wǎng)站為了降低網(wǎng)站總體擁有成本而選擇了MySQL作為網(wǎng)

2、站數(shù)據(jù)庫。MySQL官方網(wǎng)站：http://www.mysql.com,,誰在用MySQL,,MySQL歷史,1979年， 報表工具，數(shù)據(jù)引擎1996年，MySQL 1.0 (3.11.1) 發(fā)布，支持SQL2000年，成立 MySQL AB 公司2008年1月，Sun公司以10億美元收購MySQL AB公司2009年4月，Oracle公司以74億美元收購Sun公司,,MySQL里程碑,3.11.1 First public

3、 release3.23 集成Berkeley DB, 支持事務(wù)，抽象出Storage Engine4.0 集成InnoDB4.1 重大改進(jìn)，子查詢、unicode、c/s通信協(xié)議5.0 stored procedure、view、triggers、query optimizer5.1 File NDB、record replication......,,MySQL架構(gòu),,MySQL存儲引擎比較,,MyISAM 特點,MyIS

4、AM vs InnoDB,數(shù)據(jù)存儲方式簡單，使用 B+ Tree 進(jìn)行索引 使用三個文件定義一個表：.MYI .MYD .frm 少碎片、支持大文件、能夠進(jìn)行索引壓縮 二進(jìn)制層次的文件可以移植 (Linux ? Windows) 訪問速度飛快，是所有MySQL文件引擎中速度最快的 不支持一些數(shù)據(jù)庫特性，比如 事務(wù)、外鍵約束等 Table level lock，性能稍差，更適合讀取多的操作 表數(shù)據(jù)容量有限，一般建議單

5、表數(shù)據(jù)量介于 50w–200w 增刪查改以后要使用 myisamchk 檢查優(yōu)化表,MyISAM 存儲結(jié)構(gòu),MyISAM vs InnoDB,,MyISAM 索引結(jié)構(gòu),MyISAM vs InnoDB,,InnoDB 特點,MyISAM vs InnoDB,使用 Table Space 的方式來進(jìn)行數(shù)據(jù)存儲 (ibdata1, ib_logfile0) 支持 事務(wù)、外鍵約束等數(shù)據(jù)庫特性 Rows level lock , 讀寫

6、性能都非常優(yōu)秀 能夠承載大數(shù)據(jù)量的存儲和訪問 擁有自己獨立的緩沖池，能夠緩存數(shù)據(jù)和索引 在關(guān)閉自動提交的情況下，與MyISAM引擎速度差異不大,InnoDB 數(shù)據(jù)結(jié)構(gòu),MyISAM vs InnoDB,,MyISAM vs InnoDB 性能測試,,測試結(jié)果,MyISAM vs InnoDB 性能測試,可以看出在MySQL 5.0里面，MyISAM和InnoDB存儲引擎性能差別并不是很大，針對InnoDB來說，影響性能的主要是

7、innodb_flush_log_at_trx_commit 這個選項，如果設(shè)置為1的話，那么每次插入數(shù)據(jù)的時候都會自動提交，導(dǎo)致性能急劇下降，應(yīng)該是跟刷新日志有關(guān)系，設(shè)置為0效率能夠看到明顯提升，當(dāng)然，同樣你可以SQL中提交“SET AUTOCOMMIT = 0”來設(shè)置達(dá)到好的性能。同時也可以看出值得使用 InnoDB 來替代 MyISAM 引擎來進(jìn)行開發(fā)，畢竟InnoDB 有多數(shù)據(jù)庫特性、更良好的數(shù)據(jù)存儲性能和查詢性能,,MyS

8、QL優(yōu)化方式,,系統(tǒng)優(yōu)化：硬件、架構(gòu)服務(wù)優(yōu)化應(yīng)用優(yōu)化,MySQL優(yōu)化方式,使用好的硬件，更快的硬盤、大內(nèi)存、多核CPU，專業(yè)的存儲服務(wù)器（NAS、SAN）設(shè)計合理架構(gòu)，如果 MySQL 訪問頻繁，考慮 Master/Slave 讀寫分離；數(shù)據(jù)庫分表、數(shù)據(jù)庫切片（分布式），也考慮使用相應(yīng)緩存服務(wù)幫助 MySQL 緩解訪問壓力,系統(tǒng)優(yōu)化,配置合理的MySQL服務(wù)器，盡量在應(yīng)用本身達(dá)到一個MySQL最合理的使用針對 MyISAM 或

9、 InnoDB 不同引擎進(jìn)行不同定制性配置針對不同的應(yīng)用情況進(jìn)行合理配置針對 my.cnf 進(jìn)行配置，后面設(shè)置是針對內(nèi)存為2G的服務(wù)器進(jìn)行的合理設(shè)置,服務(wù)優(yōu)化,MySQL配置原則,服務(wù)優(yōu)化,公共選項,服務(wù)優(yōu)化,MyISAM 選項,服務(wù)優(yōu)化,InnoDB 選項,設(shè)計合理的數(shù)據(jù)表結(jié)構(gòu)：適當(dāng)?shù)臄?shù)據(jù)冗余對數(shù)據(jù)表建立合適有效的數(shù)據(jù)庫索引數(shù)據(jù)查詢：編寫簡潔高效的SQL語句,應(yīng)用優(yōu)化,應(yīng)用優(yōu)化方式,,應(yīng)用優(yōu)化,表結(jié)構(gòu)設(shè)計原則 選擇合適的數(shù)

10、據(jù)類型：如果能夠定長盡量定長 不要使用無法加索引的類型作為關(guān)鍵字段，比如 text類型 為了避免聯(lián)表查詢，有時候可以適當(dāng)?shù)臄?shù)據(jù)冗余，比如 郵箱、姓名這些不容易更改的數(shù)據(jù) 選擇合適的表引擎，有時候 MyISAM 適合，有時候 InnoDB適合 為保證查詢性能，最好每個表都建立有 auto_increment 字段， 建立合適的數(shù)據(jù)庫索引 最好給每個字段都設(shè)定 default 值,,應(yīng)用優(yōu)化,索引建立

11、原則 一般針對數(shù)據(jù)分散的關(guān)鍵字進(jìn)行建立索引，比如ID、QQ， 像性別、狀態(tài)值等等建立索引沒有意義 盡量使用短索引，一般對int、char/varchar、date/time 等 類型的字段建立索引 需要的時候建立聯(lián)合索引，但是要注意查詢SQL語句的編寫 謹(jǐn)慎建立 unique 類型的索引（唯一索引） 一般建議每條記錄最好有一個能快速定位的獨一無二定位的 唯一標(biāo)示（索引） 不要過度索引，單表建立的索引

12、不要超過5個，否則更新索 引將很耗時,,應(yīng)用優(yōu)化,編寫高效的 SQL 能夠快速縮小結(jié)果集的 WHERE 條件寫在前面，如果有恒量條件， 也盡量放在前面 盡量避免使用 GROUP BY、DISTINCT 、OR、IN 等語句的使用， 避免使用聯(lián)表查詢和子查詢，因為將使執(zhí)行效率大大下降 能夠使用索引的字段盡量進(jìn)行有效的合理排列，如果使用了 聯(lián)合索引，請注意提取字段的前后順序 針對索引字

13、段使用 >, >=, =, <, <=, IF NULL和BETWEEN 將會使用 索引， 如果對某個索引字段進(jìn)行 LIKE 查詢，使用 LIKE ‘%abc%’ 不能使用索引，使用 LIKE ‘a(chǎn)bc%’ 將能夠使用索引 如果在SQL里使用了MySQL部分自帶函數(shù)，索引將失效，同時將無法 使用 MySQL 的 Query Cache，比如 LEFT(), SUBSTR()

14、, TO_DAYS() DATE_FORMAT(), 等，如果使用了 OR 或 IN，索引也將失效 使用 Explain 語句來幫助改進(jìn)我們的SQL語句,,MySQL技巧分享,,MySQL技巧分享,常用技巧 使用 Explain/ DESC 來分析SQL的執(zhí)行情況 使用 SHOW PROCESSLIST 來查看當(dāng)前MySQL服務(wù)器線程 執(zhí)行情況，是否鎖表，查看相應(yīng)的SQL語句 設(shè)置 my.cnf 中的

15、long-query-time 和 log-slow-queries 能夠 記錄服務(wù)器那些SQL執(zhí)行速度比較慢 另外有用的幾個查詢：SHOW VARIABLES、SHOW STATUS、SHOW ENGINES 使用 DESC TABLE xxx 來查看表結(jié)構(gòu)，使用 SHOW INDEX FROM xxx 來查看表索引 使用 LOAD DATA 導(dǎo)入數(shù)據(jù)比 INSERT INTO 快多了 S

16、ELECT COUNT(*) FROM Tbl 在 InnoDB 中將會掃描全表 MyISAM 中則效率很高,,MySQL 技巧分享,Explain 使用 語法：EXPLAIN SELECT select_options Type: 類型，是否使用了索引還是全表掃描, const,eg_reg,ref,range,index,ALL Key: 實際使用上的索引是哪個字段 Ken_len

17、: 真正使用了哪些索引，不為 NULL 的就是真實使用的索引Ref: 顯示了哪些字段或者常量被用來和 key 配合從表中查詢記錄出來 Rows: 顯示了MySQL認(rèn)為在查詢中應(yīng)該檢索的記錄數(shù) Extra: 顯示了查詢中MySQL的附加信息，關(guān)心Using filesort 和 Using temporary，性能殺手,,MySQL 技巧分享,索引實踐,,MySQL 技巧分享,函數(shù)和索引,,M

18、ySQL 技巧分享,使用 UNION 來取代 IN 和 OR原SQL：select * from city where id in (1,3,4) 或 select * from city where id = 1 or id = 3 or id = 4 explain 結(jié)果：id select_type table type possible_keys key key_len ref rows Extra 1

19、60;  SIMPLE  city ALL  PRIMARY  NULL NULL  NULL 5  Using where 修改后SQL：SELECT * FROM city where id = 1 UNION ALL SELECT * FROM city where id = 3 UNION ALL SELECT * FROM city expl

20、ain 結(jié)果：id   select_type table        type possible_keys key      key_len ref   rows Extra 1    PRIMARY   

21、60;  city         const PRIMARY       PRIMARY 4       const 1   2    UNION  

22、60;     city         const PRIMARY       PRIMARY 4       const 1   3    UN

23、ION        city         const PRIMARY       PRIMARY 4       const 1   NULL

24、 UNION RESULT ALL   NULL          NULL     NULL    NULL NULL,,MySQL 技巧分享,MySQL Slow Log 分析工具 mysqldumpslow - mysql官方提供的慢查詢?nèi)罩痉治?/p>

25、工具 mysqlsla - hackmysql.com推出的一款日志分析工具，功能 非常強大 mysql-explain-slow-log – 德國工程師使用Perl開發(fā)的把Slow Log 輸出到屏幕，功能簡單 mysql-log-filter - Google code 上一個開源產(chǎn)品，報表簡潔 myprofi - 純PHP開發(fā)的開源log查看工具，功能詳細(xì),,MySQL 技巧分享,MySQ