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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

18、ySQL 技巧分享,使用 UNION 來(lái)取代 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官方提供的慢查詢(xún)?nèi)罩痉治?/p>

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