mysql 數(shù)據(jù)庫(kù)優(yōu)化 包括 a.表的設(shè)計(jì)合理化（符合3NF） b.添加適當(dāng)索引（index[4種：普通索引 主鍵索引 唯一索引unique 全文索引]） c.分表技術(shù)（水平分割，垂直分割） d.讀寫[寫：update/delete/add]分離 e.存儲(chǔ)過程[模塊化編程 可以提高速度] 數(shù)據(jù)庫(kù)的三層結(jié)構(gòu) orale MySQL db2 sql server php程序通過dbms(數(shù)據(jù)庫(kù)管理系統(tǒng))操作數(shù)據(jù)庫(kù)文件，數(shù)據(jù)庫(kù)執(zhí)行相關(guān)操作返回給dbms，然后再返回給PHP dbms 首先編譯PHP代碼，然后執(zhí)行操作，然后緩存結(jié)果，但是編譯很費(fèi)時(shí)間 直接編譯耗時(shí)嚴(yán)重，所以可以直接編程寫存儲(chǔ)過程(分頁(yè)存儲(chǔ)/觸發(fā)器) PHP中執(zhí)行 call proc(’參數(shù)’)調(diào)用存儲(chǔ)過程 利于模塊化編程 f.對(duì)MySQL配置優(yōu)化[配置最大并發(fā)數(shù) my.ini] # 最大并發(fā)數(shù) # 一般網(wǎng)站調(diào)整到 1000 左右 max_connections = 100 # 調(diào)整緩存大小 g.MySQL 服務(wù)器硬件升級(jí) h.定時(shí)的去清除不需要的數(shù)據(jù)，并且定時(shí)進(jìn)行碎片整理(尤其對(duì)搜索引擎是MyISAM) 數(shù)據(jù)庫(kù)設(shè)計(jì)的三范式 3NF 表的范式，是首先符合 1NF 才能滿足2NF 進(jìn)一步滿足3NF 1NF： 是對(duì)屬性的原子性約束，要求屬性（列）具有原子性，不可再分解 只要是關(guān)系型數(shù)據(jù)庫(kù)都滿足1NF 數(shù)據(jù)庫(kù)的分類： 關(guān)系型數(shù)據(jù)庫(kù) ：MySQL/Oracle/db2/informix/sysbase/sql server 非關(guān)系型數(shù)據(jù)庫(kù) ：特點(diǎn)是面向?qū)ο蠡蛘呒系?NoSql數(shù)據(jù)庫(kù) ：MongoDB(特點(diǎn)是面向文檔) 2NF： 是對(duì)記錄的唯一性約束，要求記錄有唯一標(biāo)識(shí)，即實(shí)體的唯一性 3NF： 是對(duì)字段冗余性的約束，要求字段沒有冗余，即 表中不要有冗余數(shù)據(jù) 也就是說，表的信息，如果能夠被推導(dǎo)出來，就不應(yīng)該單獨(dú)的設(shè)計(jì)一個(gè)字段來存放 反 3NF 實(shí)際上一定的冗余是允許的 就是反 3NF 在表的 1對(duì)N 情況下，為了提高效率，可能會(huì)在 1 這表中設(shè)計(jì)字段 提速 譬如 相冊(cè)的瀏覽量 相冊(cè) 字段 id name views 相片 字段 id name path views 這里的views字段，在兩個(gè)表中都有，可以避免顯示相冊(cè)瀏覽量的時(shí)候再去查詢計(jì)算相冊(cè)內(nèi)相片的瀏覽量總和 以此冗余提高查詢效率 SQL優(yōu)化的一般步驟 ① 通過 show status 命令了解各種SQL的執(zhí)行頻率 ② 定位執(zhí)行效率較低的SQL語句 （重點(diǎn)select） ③ 通過 explain 分析低效率的SQL語句的執(zhí)行情況 ④ 確定問題并采取相應(yīng)的優(yōu)化措施 SQL語句本身的優(yōu)化 問題：如果從一個(gè)大型項(xiàng)目中，迅速的定位執(zhí)行速度慢的語句 ① 首先了解MySQL數(shù)據(jù)庫(kù)的一些運(yùn)行狀態(tài)如何查詢 show status 比如想知道當(dāng)前MySQL運(yùn)行時(shí)間，一共執(zhí)行了多少次select/update/delete 當(dāng)前連接 等等 常用的 show status like ’uptime’ 當(dāng)前MySQL運(yùn)行時(shí)間 show status like ’com_select’ 當(dāng)前MySQL執(zhí)行了多少次查詢 show status like ’com_insert’ 當(dāng)前MySQL執(zhí)行了多少次添加 show status like ’com_update’ 當(dāng)前MySQL執(zhí)行了多少次更新 show status like ’com_delete’ 當(dāng)前MySQL執(zhí)行了多少次刪除 show status 語法： show [session|global] status like ’’; 如果不寫 [session|global] 表示默認(rèn)是 session 指取出當(dāng)前窗口的執(zhí)行情況 如果想看所有(mysql啟動(dòng)到現(xiàn)在)的情況 加上 global show global status like ’com_insert’; show status like ’connections’;試圖連接MySQL的連接數(shù) 顯示慢查詢次數(shù) show status like ’slow_queries’; ② 如何去定位慢查詢 構(gòu)建一個(gè)大表(400萬數(shù)據(jù))->存儲(chǔ)過程構(gòu)建 默認(rèn)情況下，MySQL認(rèn)為 10秒鐘 才是一個(gè)慢查詢 修改MySQL的慢查詢時(shí)間 // 顯示慢查詢時(shí)間 show variables like ’long_query_time’; // 修改慢查詢時(shí)間 set long_query_time=2;// 即修改慢查詢時(shí)間為2秒 構(gòu)建大表->大表中記錄有要求，記錄是不同才有用，否則測(cè)試效果與真實(shí)的相差很大 為了存儲(chǔ)過程能正常執(zhí)行，需要修改命令執(zhí)行結(jié)束符修改一下 語法 delimiter $$ $$表示修改后的結(jié)束符 當(dāng)你想使用函數(shù)時(shí)，可以指向一個(gè)dual表，這個(gè)表是亞元表，就是個(gè)空表 select rand_string(6) from dual; ③ 這時(shí)我們?nèi)绻霈F(xiàn)一條語句執(zhí)行時(shí)間超過1秒，就會(huì)被統(tǒng)計(jì)到 call insert_emp 執(zhí)行存儲(chǔ)過程這個(gè)也會(huì)被記錄 ④ 如果把慢查詢的 SQL 語句記錄到我們的一個(gè)日志中 在默認(rèn)情況下，我們的MySQL不會(huì)記錄慢查詢，需要啟動(dòng)MySQL時(shí)， 指定記錄慢查詢才可以 bin/mysqld.exe --safe-mode --slow-query-log mysql5.5在my.ini指定 bin/mysqld.exe -log-slow-queries=d:/abc.log 低版本mysql5.0可以在my.ini指定 先關(guān)閉MySQL 再重新啟動(dòng) 如果啟用了慢查詢?nèi)罩?，默認(rèn)存放在 my.ini 文件中記錄的位置 即 datadir設(shè)置的目錄 ⑤ 測(cè)試，可以看到在日志中就記錄下我們的MySQL慢sql語句 優(yōu)化問題 通過 explain 語句可以分析，MySQL如何執(zhí)行你的SQL語句 添加索引 四種索引 主鍵索引 唯一索引 全文索引 普通索引 1.添加 1.1 主鍵索引的添加 當(dāng)一張表中，把某個(gè)列設(shè)為主鍵的時(shí)候，則該列就是主鍵索引 如果你創(chuàng)建表時(shí)，沒有指定主鍵索引，也可以在創(chuàng)建表后添加索引 語句：alter table 表名 add primary key (列名); 1.2 普通索引 一般來說，普通索引的創(chuàng)建，是先創(chuàng)建表，然后再創(chuàng)建索引 語句：create index 索引名 on 表名 (列名); 1.3 全文索引 全文索引：主要是針對(duì)文件，文本的索引，比如文章 全文索引針對(duì)MyISAM有用 如何使用全文索引 錯(cuò)誤用法： select * from articles where body like ’%mysql%’; 這種方法是不會(huì)用到全文索引的 正確用法： // title,body是全文索引，匹配database的句子 select * from articles where match(title,body) against(’database’); 說明： 1.在MySQL中fulltext索引只針對(duì)myISAM生效 2.針對(duì)英文生效，對(duì)中文需要sphinx(coreseek)技術(shù)處理 3.使用方法是match(字段名) against(’關(guān)鍵字’) 4.全文索引有一個(gè)停止詞概念： 因?yàn)樵谝粋€(gè)文本中，創(chuàng)建索引是一個(gè)無窮大的數(shù)，因此，對(duì)一些常用詞和字符， 就不會(huì)創(chuàng)建，這些詞，稱為 停止詞。 1.4 唯一索引 當(dāng)表的某列被指定為unique約束，這列就是一個(gè)唯一索引 唯一索引的列可以為null，并且可以有多個(gè) 在創(chuàng)建表后，再去創(chuàng)建唯一索引 創(chuàng)建語法：create unique index 索引名 on 表名 (列名); 2.查詢 ① desc 表名 該方法缺點(diǎn)：不能夠顯示索引的名字 ② show index(es) from 表名 ③ show keys from 表名 3.刪除 語法：alter table 表名 drop index 索引名; 主鍵索引刪除：alter table 表名 drop primary key; 4.修改 先刪除，再重新創(chuàng)建 索引注意事項(xiàng)： 索引占用磁盤空間 對(duì)dml(insert/update/delete)語句效率有影響 在哪些列上適合添加索引？ 較頻繁的作為查詢條件字段創(chuàng)建索引 例如 select * from emp where empno=1; 唯一性太差的字段不適合單獨(dú)創(chuàng)建索引，即使頻繁作為查詢條件 例如 select * from emp where sex=’男’; 更新非常頻繁的字段不適合創(chuàng)建索引 例如 select * from emp where logincount=1; 不會(huì)出現(xiàn)在where子句中字段不該創(chuàng)建索引 總結(jié)：滿足以下條件的字段，才能創(chuàng)建索引 a.肯定在where條件中經(jīng)常使用的 b.該字段的內(nèi)容不是唯一的幾個(gè)值 c.字段內(nèi)容變化不能太頻繁 使用索引的注意事項(xiàng) alter table dept add index myind (dname,loc); // dname就是左邊的列，loc是右邊的列 下列情況有可能使用到索引 a.對(duì)于創(chuàng)建的多列索引，只要查詢條件使用了最左邊的列，索引一般就會(huì)被使用 explain select * from dept where dname=’aaa’; b.對(duì)于使用like的查詢，查詢條件如果是’%aaa’則不會(huì)使用到索引，’aaa%’會(huì)使用到索引 下列情況不會(huì)使用索引 a.如果條件中有or，即使其中有條件帶索引也不會(huì)使用 換言之，就是要求使用的所有字段都創(chuàng)建索引，建議：盡量避免使用or關(guān)鍵字 b.對(duì)于多列索引，不是使用的第一部分，則不會(huì)使用索引 explain select * from dept where loc=’aaa’;// 多列索引時(shí)，loc為右邊列，索引不會(huì)使用到 c.like查詢是以%開頭 如果一定要使用，則使用全文索引去查詢 d.如果列類型是字符串，那一定要在條件中將數(shù)據(jù)使用引號(hào)引起來，否則不使用索引 e.如果MySQL估計(jì)使用全表掃描要比使用索引塊，則不使用索引 explain select * from dept where loc=’aaa’; explain 語句詳解： 告訴我們MySQL將使用怎樣的執(zhí)行計(jì)劃來優(yōu)化query id:1 查詢序列號(hào) select_type:SIMPLE 查詢類型 table:dept 查詢的表名 type:ALL掃描的方式 all表示全表掃描 possible_keys:null 可能使用到的索引 key:null實(shí)際使用的索引 key_len:null ref:null rows:10 該SQL語句掃描了多少行，可能得到結(jié)果數(shù) Extra:Using where SQL語句的額外信息，比如排序方式filesort等等 select_type 類型 primary : 子查詢中最外層查詢 subquery : 子查詢內(nèi)層第一個(gè)select，結(jié)果不依賴于外部查詢 dependent subquery : 子查詢內(nèi)層第一個(gè)select，依賴于外部查詢 union　: union語句中第二個(gè)select開始后面所有select simple : 簡(jiǎn)單模式 union result : union中合并結(jié)果 type 類型 all : 完整的表掃描 通常不好 system : 表僅有一行(=系統(tǒng)表) 這是const聯(lián)接類型的一個(gè)特例 const : 表最多有一個(gè)匹配行 extra 類型 no table : query語句中使用 from dual 或不含任何from子句 Using filesort : 當(dāng)query中包含 order by 操作，而且無法利用索引完成排序 impossible WHERE noticed after reading const tables:Mysql query optimizer 通過收集統(tǒng)計(jì)信息不可能存在結(jié)果 Using temporary : 某些操作必須使用臨時(shí)表，常見 group by ,order by Using where : 不用讀取表中所有信息，僅通過索引就可以獲取所需數(shù)據(jù) explain可以幫助我們?cè)诓徽嬲龍?zhí)行某個(gè)SQL語句時(shí)，就知道MySQL怎樣執(zhí)行，利于我們?nèi)シ治鯯QL指令 查看索引的使用情況 show status like ’Handler_read%’; handler_read_key:這個(gè)值越高越好，代表使用索引查詢到的次數(shù) handler_read_rnd_next:這個(gè)值越高，說明查詢低效 SQL語句的小技巧 ① 優(yōu)化 group by 語句 默認(rèn)情況下，MySQL對(duì)所有的group by col1,col2 進(jìn)行排序，這與在查詢中指定 order by col1,col2 類似 如果查詢中包括 group by 但用戶想盡量避免排序結(jié)果的消耗，則可以使用 order by null 禁止排序 ② 有些情況下，可以使用連接來替代子查詢 因?yàn)槭褂?join MySQL不需要在內(nèi)存中創(chuàng)建臨時(shí)表 ③ 如果想要在含有 or 的查詢語句中利用索引，則 or 之間的每個(gè)條件列都必須用到索引， 如果沒有索引，則應(yīng)該考慮增加索引。 如何選擇MySQL的存儲(chǔ)引擎 1.myISAM: 如果表對(duì)事務(wù)要求不高，同時(shí)是以查詢和添加為主的。 比如 BBS中的發(fā)帖表，回復(fù)表 2.InnoDB: 對(duì)事務(wù)要求高，保存的數(shù)據(jù)都是重要數(shù)據(jù) 比如 訂單表，賬戶表 3.Memory: 數(shù)據(jù)變化頻繁，不需要入庫(kù)同時(shí)又經(jīng)常查詢和修改 myISAM 與 InnoDB 主要區(qū)別 1.myisam 批量插入速度快，InnoDB慢，myisam插入數(shù)據(jù)時(shí)不排序 2.InnoDB支持事務(wù) 3.myisam支持全文索引 4.鎖機(jī)制，myisam是表鎖，InnoDB是行鎖 5.myisam不支持外鍵，InnoDB支持外鍵 外鍵 classes表 create table classes( id int unsigned not null auto_increment primary key, name varchar(64) not null )engine=innoDB; insert into classes values (1,’aaa’); stu表 create table student( id int unsigned not null auto_increment primary key, name varchar(64) not null, classid int unsigned not null, foreign key (classid) references classes(id) /* 外鍵 */ )engine=innoDB; 當(dāng)設(shè)置了外鍵的時(shí)候，企圖添加一個(gè)外鍵沒有的數(shù)據(jù)，會(huì)報(bào)錯(cuò)，無法插入數(shù)據(jù) insert into student values (1,’hello’,1); 這個(gè)是正確的 insert into student values (1,’hello’,2); 當(dāng)classes表中id=2不存在時(shí)，這個(gè)是錯(cuò)誤的 在PHP開發(fā)中，通常不設(shè)置外鍵，通常在程序中保持?jǐn)?shù)據(jù)的一致性。 選擇合適的數(shù)據(jù)類型 ① 在精度要求高的應(yīng)用中，建議使用定點(diǎn)數(shù)來存儲(chǔ)數(shù)值，以保證數(shù)據(jù)的準(zhǔn)確性。deciaml精度比float高，盡量使用 ② 對(duì)于存儲(chǔ)引擎是myisam的數(shù)據(jù)庫(kù)，如果經(jīng)常做刪除和修改記錄的操作， 要定時(shí)執(zhí)行optimize table table_name;功能對(duì)表進(jìn)行碎片整理。 ③ 日期類型要根據(jù)實(shí)際需要選擇能夠滿足應(yīng)用的最小存儲(chǔ)的早期類型 create table bbs (id int unsigned not null ,con varchar(1024) , pub_time int ); php備份數(shù)據(jù)庫(kù) ① 手動(dòng)備份數(shù)據(jù)庫(kù)(表)的方法 cmd控制臺(tái) mysqldump -uroot -proot 數(shù)據(jù)庫(kù)[表名1 表名2 ...] > 文件路徑 例如 把 temp 數(shù)據(jù)庫(kù)備份到 d:/temp.bak mysqldump -uroot -proot temp > d:/temp.bak 如果希望備份數(shù)據(jù)庫(kù)中某幾張表 mysqldump -uroot -proot temp dept > d:/temp.dept.bak 如何使用備份文件恢復(fù)數(shù)據(jù) 在MySQL控制臺(tái) source d:/temp.dept.bak ② 使用定時(shí)器自動(dòng)完成 a.把備份數(shù)據(jù)庫(kù)的指令，寫入到bat文件，然后通過任務(wù) crontab mytask.bat 內(nèi)容是 d:/myweb/mysql/bin/mysqldump -uroot -p2012o912@ bigtest dept > d:/bigtest.dept.bak 注意事項(xiàng)：如果mysqldump.exe 文件路徑有空格，則一定要使用雙引號(hào)包起來 把 mytask.bat 做成一個(gè)任務(wù)，并定時(shí)調(diào)用 例如 每天 2：00 調(diào)用一次 windows下：打開控制面板--任務(wù)計(jì)劃--添加任務(wù)計(jì)劃--下一步--瀏覽--找到mytask.bat--選擇執(zhí)行任務(wù)時(shí)間 --下一步--起始時(shí)間--下一步--輸入密碼--下一步--完成 現(xiàn)在的問題是，每次都是覆蓋原來的備份文件，不利于分時(shí)段備份，解決這種問題 可以采用如下方式解決： b.建立一個(gè) mytask.php 文件 內(nèi)容是 <?php date_default_timezone_set(’PRC’); $bakfilename = date(’YmdHis’,time()); $command = 'd:mywebmysqlbinmysqldump -uroot -p2012o912@ bigtest dept > f:{$bakfilename}.bak'; exec($command); ?> 建立一個(gè)bat文件 mytask2.bat,內(nèi)容是 d:mywebphpphp.exe d:mywebapachehtdocsmytask.php 該方法是利用PHP自身的php.exe執(zhí)行PHP文件 然后將mytask2.bat做成一個(gè)任務(wù)，定時(shí)的去執(zhí)行 linux 下 使用 crontab命令 crontab 0 0 0 0 0 mytask.sh mysql中當(dāng)前時(shí)間戳函數(shù) unix_timestamp(); 案例 定時(shí)發(fā)送郵件 1.怎樣可以定時(shí)的去檢索哪些郵件該發(fā)送： 只能每隔一定時(shí)間就看看哪些郵件該發(fā)送 mailtask.php 在PHP中，有一個(gè)函數(shù)mail，用于發(fā)送郵件，實(shí)際中通過phpmailer進(jìn)行發(fā)送郵件 要正確使用phpmailer發(fā)送郵件，需要滿足如下條件 a.本身機(jī)器是可以聯(lián)網(wǎng)的 b.需要搭建自己的 SMTP 郵件服務(wù)器 表的分割 1.水平分割 案例 大數(shù)據(jù)量的用戶表 三張表：qqlogin0,qqlogin1,qqlogin2 將用戶id%3,按結(jié)果放入不同的表當(dāng)中 create table qqlogin0( id int unsigned not null primary key,/* 這個(gè)id不能設(shè)置自增長(zhǎng) */ name varchar(32) not null default ’’, pwd varchar(32) not null default ’’ )engine=myisam default charset=utf8; create table qqlogin1( id int unsigned not null primary key,/* 這個(gè)id不能設(shè)置自增長(zhǎng) */ name varchar(32) not null default ’’, pwd varchar(32) not null default ’’ )engine=myisam default charset=utf8; create table qqlogin2( id int unsigned not null primary key,/* 這個(gè)id不能設(shè)置自增長(zhǎng) */ name varchar(32) not null default ’’, pwd varchar(32) not null default ’’ )engine=myisam default charset=utf8; 開發(fā) addUser.php ，因?yàn)樵谔砑佑脩魰r(shí)，各個(gè)用戶id應(yīng)該確認(rèn)下，通常我們使用一個(gè)輔助表 uuid 表， 它可以幫助我們生成一個(gè)編號(hào) uuid表： create table uuid ( id int unsigned not null auto_increment primary key )engine=myisam default charset=utf8; 分享一句話： 我們?cè)谔峁z索時(shí)，應(yīng)該根據(jù)業(yè)務(wù)的需求，找到分表的標(biāo)準(zhǔn)，并在檢索頁(yè)面約束用戶的檢索方式，而且要配合分頁(yè) 如果有大表檢索的需求，也是少數(shù)的。 添加用戶時(shí)：addUser.php <?php $conn = mysql_connect(’localhost’,’root’,’2012o912@’); if (!$conn) { die(’mysql connect error’); } mysql_select_db(’temp’,$conn); $sql = 'insert into uuid values (null)'; $res = mysql_query($sql,$conn); if ($res) { $uuid = mysql_insert_id(); $tablename = ’qqlogin’.$uuid%4; $sql = 'insert into $tablename values ($uuid,’abc’,’abc’)'; $res = mysql_query($sql,$conn); if ($res) { echo ’insert success’; }else { echo ’insert user error’; } }else { die(’insert error’); } ?> 查詢用戶時(shí)，checkUser.php <?php $conn = mysql_connect(’localhost’,’root’,’2012o912@’); if (!$conn) { die(’mysql connect error’); } mysql_select_db(’temp’,$conn); $qqid = intval($_GET[’id’]); $tablename = ’qqlogin’.$qqid%4; $sql = 'select * from $tablename where id=’$qqid’'; $res = mysql_query($sql,$conn); if ($res) { $row = mysql_fetch_assoc($res); print_r($row); }else { die(’no user’); } ?> 2.垂直分割 案例 學(xué)生答題系統(tǒng) 考試結(jié)果表 id stuno questionid answer(text) grade 1 1 20 [結(jié)果。。。] 30 問題表 id question 20 請(qǐng)寫一篇散文 需求: 查處1號(hào)學(xué)生20題得分情況，但answer字段內(nèi)容非常大，對(duì)查詢速度有影響 解決：把a(bǔ)nswer(對(duì)查詢速度影響較大的字段)單獨(dú)的提出來，放到另外一張表 回答表 answer id answer 1 結(jié)果。。。 相應(yīng)的修改考試結(jié)果表 id stuno questionid grade 1 1 20 30 總結(jié)：把某個(gè)表的某些字段，這些字段，在查詢時(shí)，并不實(shí)時(shí)關(guān)心，但數(shù)據(jù)量很大， 我們建議大家可以 把這些字段單獨(dú)的放到另外一張表，從而提高效率。但是不要忘記關(guān)聯(lián)關(guān)系 表的字段定義原則是保小不保大，盡量節(jié)省空間 查看索引 mysql> show index from tblname; mysql> show keys from tblname; · Table 表的名稱。 · Non_unique 如果索引不能包括重復(fù)詞，則為0。如果可以，則為1。 · Key_name 索引的名稱。 · Seq_in_index 索引中的列序列號(hào)，從1開始。 · Column_name 列名稱。 · Collation 列以什么方式存儲(chǔ)在索引中。在MySQL中，有值‘A’（升序）或NULL（無分類）。 · Cardinality 索引中唯一值的數(shù)目的估計(jì)值。通過運(yùn)行ANALYZE TABLE或myisamchk -a可以更新。基數(shù)根據(jù)被存儲(chǔ)為整數(shù)的統(tǒng)計(jì)數(shù)據(jù)來計(jì)數(shù)，所以即使對(duì)于小型表，該值也沒有必要是精確的?；鶖?shù)越大，當(dāng)進(jìn)行聯(lián)合時(shí)，MySQL使用該索引的機(jī) 會(huì)就越大。 · Sub_part 如果列只是被部分地編入索引，則為被編入索引的字符的數(shù)目。如果整列被編入索引，則為NULL。 · Packed 指示關(guān)鍵字如何被壓縮。如果沒有被壓縮，則為NULL。 · Null 如果列含有NULL，則含有YES。如果沒有，則該列含有NO。 · Index_type 用過的索引方法（BTREE, FULLTEXT, HASH, RTREE）。 · Comment 關(guān)于網(wǎng)站的圖片和視頻的存放： 我們的數(shù)據(jù)表中，一般只是存放圖片或者視頻的路徑，真正的資源是放在文件系統(tǒng)上的，往往會(huì)配合獨(dú)立的服務(wù)器 優(yōu)化MySQL的配置 my.ini port=3306 默認(rèn)端口是3306， 如果想修改端口 port=3309,在mysql_connect(’localhost:3309’,’root’,’root’);要注意 修改最大連接數(shù) max_connections=100 最大連接數(shù)可以修改到2000，再大沒有用 query_cache_size=15M 這個(gè)是查詢緩存的大小 innodb參數(shù)也可以調(diào)大以下兩個(gè)參數(shù) innodb_additional_mem_pool_size=64M innodb_buffer_pool_size=1G myisam需要調(diào)整 key_buffer_size 調(diào)整參數(shù)還要看狀態(tài)，用 show status 可以看到當(dāng)前狀態(tài)，以決定該調(diào)整哪些參數(shù) 如果你的機(jī)器內(nèi)存超過4G，則應(yīng)當(dāng)采用64位操作系統(tǒng)和64位MySQL5.5.19 讀寫分離 如果數(shù)據(jù)庫(kù)壓力很大，一臺(tái)機(jī)器支撐不了，可以用MySQL復(fù)制實(shí)現(xiàn)多臺(tái)機(jī)器同步，將數(shù)據(jù)庫(kù)壓力分散 增量備份 定義：MySQL數(shù)據(jù)庫(kù)會(huì)以二進(jìn)制的形式，把用戶對(duì)MySQL數(shù)據(jù)庫(kù)的操作，記錄到文件 當(dāng)用戶希望恢復(fù)的時(shí)候，可以使用備份文件進(jìn)行恢復(fù)。 增量備份會(huì)記錄 dml語句，創(chuàng)建表的語句，但不會(huì)記錄select語句 記錄的是 a.操作語句本身 b.操作的時(shí)間 c.操作的位置 position 案例：如何進(jìn)行增量備份和恢復(fù) 步驟： 1.配置my.ini 文件 或者 my.conf 啟用二進(jìn)制備份 在my.ini 中增加一句話 #指定備份文件放在哪個(gè)目錄下 log-bin='d:/backup/mylog' 2.重啟MySQL得到文件 d:/backup/mylog.index 索引文件 有哪里增量備份文件 d:/backup/mylog.000001 存放用戶對(duì)數(shù)據(jù)庫(kù)操作的文件 3.當(dāng)我們進(jìn)行操作(除了select) 可以使用 mysql/bin/mysqlbinlog 程序來查看備份文件的內(nèi)容 進(jìn)入到 cmd 控制臺(tái) cmd>mysqlbinlog 備份文件路徑 在這里 end_log_pos 526表示執(zhí)行某個(gè)命令在文件中的位置，可以根據(jù)這個(gè)位置恢復(fù)相應(yīng)的數(shù)據(jù) set timestamp=xxxxxxx 這個(gè)表示命令執(zhí)行時(shí)間 MySQL把每一個(gè)操作的時(shí)間記錄下來，同時(shí)分配了一個(gè)位置position 我們可以根據(jù)時(shí)間或者位置來恢復(fù) a.根據(jù)時(shí)間點(diǎn)恢復(fù) 在 mylog.000001 文件開始 到 2013-05-15 14:25:00 結(jié)束 mysqlbinlog --stop-datetime='2013-05-15 14:25:00' d:/backup/mylog.000001 | mysql -uroot -p 在 mylog.000001 文件 2013-05-15 14：25：00 開始到文件結(jié)束 mysqlbinlog --start-datetime='2013-05-15 14:25:00' d:/backup/mylog.000001 | mysql -uroot -p 恢復(fù)某個(gè)時(shí)間段數(shù)據(jù) mysqlbinlog --start-datetime='2013-05-15 14:24:00' --stop-datetime='2013-05-15 14:25:00' d:backupmylog.000001 | mysql -uroot -p b.根據(jù)位置恢復(fù) 在 mylog.000001 文件開始->21114 mysqlbinlog --stop-position='21114' d:/backup/mylog.000001 | mysql -uroot -p 在mylog.000001 文件2111開始->最后 mysqlbinlog --start-position='2111' d:/backup/mylog.000001 | mysql -uroot -p 在mylog.000001 文件 751->1195 之間 mysqlbinlog --start-position='751' --stop-position='1195' | mysql -uroot -p 4.如何在工作中將全備份和增量備份配合使用 方案：每周一做一個(gè)全備份：mysqldump， 然后啟用增量備份，把過期時(shí)間設(shè)為>=7，最好設(shè)大一點(diǎn) 如果出現(xiàn)數(shù)據(jù)庫(kù)崩潰，可以通過時(shí)間或者位置恢復(fù) 需要去看增量日志文件