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

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

2、站數據庫。MySQL官方網站：http://www.mysql.com,,誰在用MySQL,,MySQL歷史,1979年， 報表工具，數據引擎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, 支持事務，抽象出Storage Engine4.0 集成InnoDB4.1 重大改進，子查詢、unicode、c/s通信協議5.0 stored procedure、view、triggers、query optimizer5.1 File NDB、record replication......,,MySQL架構,,MySQL存儲引擎比較,,MyISAM 特點,MyIS

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

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

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

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

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

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

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

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

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

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

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

15、long-query-time 和 log-slow-queries 能夠 記錄服務器那些SQL執(zhí)行速度比較慢 另外有用的幾個查詢：SHOW VARIABLES、SHOW STATUS、SHOW ENGINES 使用 DESC TABLE xxx 來查看表結構，使用 SHOW INDEX FROM xxx 來查看表索引 使用 LOAD DATA 導入數據比 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認為在查詢中應該檢索的記錄數 Extra: 顯示了查詢中MySQL的附加信息，關心Using filesort 和 Using temporary，性能殺手,,MySQL 技巧分享,索引實踐,,MySQL 技巧分享,函數和索引,,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 結果：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 結果：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官方提供的慢查詢日志分析

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