php視頻教程-大型門戶網(wǎng)站核心技術(shù)mysql優(yōu)化筆記圖解

1、對數(shù)據(jù)庫的優(yōu)化技術(shù)優(yōu)化時一個綜合性的技術(shù)，主要包括a: 表的設(shè)計合理化(符合 3NF)b: 添加適當(dāng)索引(index) 四種: 普通索引、主鍵索引、唯一索引 unique、全文索引 c: 分表技術(shù)(水平分割、垂直分割)d:e:f:g:讀寫寫: update/delete/add分離過程 模塊化編程，可以提高速度配置優(yōu)化 配置最大并發(fā)數(shù) my.ini, 調(diào)整緩存大小 服務(wù)器硬件升級對h: 定時的去清除不需要的數(shù)據(jù),定時進行碎片整理(MyISAM)什么樣的表才是符合 3NF (范式)表的范式，是首先符合 1NF, 才能滿足 2NF , 進一步滿足 3NF1NF: 即表的列的具有原子性,不可再分解，

2、即列的信息，不能分解, 只有數(shù)據(jù)庫是關(guān)系型數(shù)據(jù)庫(/oracle/db2/informix/sysbase/sql server)，就自動的滿足 1NF 數(shù)據(jù)庫的分類關(guān)系型數(shù)據(jù)庫:/oracle/db2/informix/sysbase/sql server非關(guān)系型數(shù)據(jù)庫: (特點: 面象或者集合)NoSql 數(shù)據(jù)庫: MongoDB(特點是面向文檔)2NF: 表中的是唯一的, 就滿足 2NF, 通常設(shè)計一個主鍵來實現(xiàn)3NF: 即表中不要有冗余數(shù)據(jù), 就是說，表的信息，如果能夠被推導(dǎo)出來，就不應(yīng)該單獨的設(shè)計一個字段來存放. 比如下面的設(shè)計就是不滿足 3NF:反 3NF : 但是，沒有冗余的數(shù)據(jù)

3、庫未必是最好的數(shù)據(jù)庫，有時為了提高運行效率，就必須降低范式標準，適當(dāng)保留冗余數(shù)據(jù)。具體做法是： 在概念數(shù)據(jù)模型設(shè)計時遵守第三范式，降低范式標準的工作放到物理數(shù)據(jù)模型設(shè)計時考慮。降低范式就是增加字段，允許冗余。案例 ：Sql 語句本身的優(yōu)化問題是： 如何從一個大項目中，迅速的定位執(zhí)行速度慢的語句. (定位慢查詢) 首先了解數(shù)據(jù)庫的一些運行狀態(tài)如何查詢(比如想知道當(dāng)前運行的時間/一共執(zhí)行了多少次 select/update/delete. / 當(dāng)前連接)show sus常用的:show sus like uptime ;showstauts like com_select show stauts

4、like com_insert .類推 updatedelete show ses|global sus like . 如果你不寫 ses|global 默認是 ses會話，指取出當(dāng)前窗口的執(zhí)行，如果你想看所有(從啟動到現(xiàn)在，則應(yīng)該 global)show sus like connections;/顯示慢查詢次數(shù)show sus like slow_queries; 如何去定位慢查詢構(gòu)建一個大表(400 萬)-過程構(gòu)建默認情況下，認為 10 秒才是一個慢查詢.修改的慢查詢.show variables like long_query_time ; /可以顯示當(dāng)前慢查詢時間set long_q

5、uery_time=1 ;/可以修改慢查詢時間構(gòu)建大表-大表中有要求,是不同才有用，否則測試效果和真實的相差大.創(chuàng)建:CREATE TABLE dept( /*部門表*/deptno MEDIUMUNSIGNEDNOT NULLDEFAULT 0,/*/dname VARCHAR(20)NOT NULL DEFAULT , /*名稱*/ loc VARCHAR(13) NOT NULL DEFAULT /*地點*/) ENGINE=MyISAM DEFAULT CHARSET=utf8 ;CREATE TABLE emp(empnoMEDIUMUNSIGNEDNOT NULL DEFAULT

6、0, /*/ename VARCHAR(20) NOT NULL DEFAULT , /*名字*/job VARCHAR(9) NOT NULL DEFAULT ,/*工作*/mgr MEDIUMUNSIGNED NOT NULL DEFAULT 0,/*/hiredate DATE NOT NULL,/*入職時間*/ sal DECIMAL(7,2)NOT NULL,/*薪水*/comm DECIMAL(7,2) NOT NULL,/*紅利*/deptno MEDIUMUNSIGNED NOT NULL DEFAULT 0 /*部門*/)ENGINE=MyISAM DEFAULT CHARS

7、ET=utf8 ;CREATE TABLE salgrade(grade MEDIUMUNSIGNED NOT NULL DEFAULT 0,losal DECIMAL(17,2)NOT NULL, hisal DECIMAL(17,2)NOT NULL)ENGINE=MyISAM DEFAULT CHARSET=utf8;測試數(shù)據(jù)INSERT INSERT INSERT INSERTINSERTO salgrade VALUES (1,700,1200);O salgrade VALUES (2,1201,1400);O salgrade VALUES (3,1401,2000);O sal

8、grade VALUES (4,2001,3000);O salgrade VALUES (5,3001,9999);為了過程能夠正常執(zhí)行，需要把命令執(zhí)行結(jié)束符修改delimiter $create function rand_string(n)returns varchar(255) #該函數(shù)會返回一個字符串begin#chars_str定 義 一 個 變 量chars_str, 類 型 是varchar(100), 默 認 值abcdefghijklmnopqrstuvwxyzABCDEFJHIJKLMNOPQRSTUVWXYZ; declare chars_str varchar(100

9、) defaultabcdefghijklmnopqrstuvwxyzABCDEFJHIJKLMNOPQRSTUVWXYZ;declare return_str varchar(255) default ;declare idefault 0;while i sphinx (coreseek) 技術(shù)處理中文使用方法是 match(字段名.) against(關(guān)鍵字)全文索引一個止詞,因為在一個文本中，創(chuàng)建索引是一個無窮大的數(shù)，因此，對一些常用詞和字符，就不會創(chuàng)建，這些詞，稱為停止詞.1.4 唯一索引當(dāng)表的某列被指定為 unique 約束時，這列就是一個唯一索引create table ddd(

10、idprimary key auto_increment , name varchar(32) unique);這時, name 列就是一個唯一索引.unique 字段可以為NULL,并可以有多NULL, 但是如果是具體內(nèi)容，則不能重復(fù).主鍵字段，不能為NULL,也不能重復(fù).在創(chuàng)建表后，再去創(chuàng)建唯一索引create table eee(idprimary key auto_increment, name varchar(32);create unique index索引名 on 表名 (列表.);2.查詢索引desc 表名 【該方法的缺點是： 不能夠顯示索引名.】show index(es)

11、from 表名show keys from 表名3.刪除alter table 表名 drop index 索引名;如果刪除主鍵索引。alter table 表名 drop primary key這里有一個小問題4.修改先刪除，再重新創(chuàng)建.為什么創(chuàng)建索引后，速度就會變快?原理示意圖:.索引使用的注意事項索引的代價:1.2.占用磁盤空間對 dml 操作有影響，變慢在哪些列上適合添加索引?總結(jié): 滿足以下條件的字段，才應(yīng)該創(chuàng)建索引.a: 肯定在where 條經(jīng)常使用 b: 該字段的內(nèi)容不是唯一的幾個值(sex) c: 字段內(nèi)容不是頻繁變化.使用索引的注意事項把 dept 表中，我增加幾個部門:al

12、ter table dept add index my_ind (dname,loc); /dname 左邊的列,loc 就是右邊的列說明，如果的表中有復(fù)合索引(索引作用在多列上)， 此時注意:1， 對于創(chuàng)建的多列索引，只要查詢條件使用了最左邊的列，索引一般就會被使用。explain select * from dept where loc=aaaG就不會使用到索引2，對于使用 like 的查詢，查詢?nèi)绻?%aaa 不會使用到索引aaa% 會使用到索引。比如: explain select * from dept where dname like %aaaG不能使用索引，即，在 like 查

13、詢時，關(guān)鍵的 關(guān)鍵字 , 最前面，不能使用 % 或者 _這樣的字符.， 如果一定要前面有變化的值，則考慮使用 全文索引-sphinx.3.如果條件中有 or，即使其中有條件帶索引也不會使用。換言之，就是要求使用的所有字段，都必須建立索引,建議大家盡量避免使用 or 關(guān)鍵字select * from dept where dname= or loc=xx or deptno=454.如果列類型是字符串，那一定要在條件中將數(shù)據(jù)使用引號起來。否則不使用索引。(添加時,字符串必須), 也就是，如果列是字符串類型，就一定要用 把他包括起來.5.如果估計使用全表掃描要比使用索引快，則不使用索引。expla

14、in可以幫助在不真正執(zhí)行某個 sql 語句時，就執(zhí)行怎樣執(zhí)行，這樣利用我們?nèi)シ治?sql 指令.如何查看索引使用的情況:show sus like Handler_read%;大家可以注意：handler_read_key:這個值越好，越高表示使用索引查詢到的次數(shù)。handler_read_rnd_next:這個值越高，說明查詢低效。sql 語句的小技巧1.在使用 group by 分組查詢是，默認分組后，還會排序，可能會降低速度.比如:在 group by 后面增加 order by null 就可以防止排序.2.有些情況下，可以使用連接來替代子查詢。因為使用 join，創(chuàng)建臨時表。不需要在

15、內(nèi)存中select * from dept, emp where dept.deptno=emp.deptno; 簡單處理方式select * from dept left join emp on dept.deptno=emp.deptno;左外連接，更ok!如何選擇的引擎在開發(fā)中，myisam myisam經(jīng)常使用的引擎 myisam / innodb/ memory: 如果表對事務(wù)要求不高，同時是以查詢和添加為主的，考慮使用引擎. ,比如中的 發(fā)帖表，回復(fù)表.INNODB: 對事務(wù)要求高，保存的數(shù)據(jù)都是重要數(shù)據(jù)，建議使用 INNODB,比如訂單表，賬號表.問 MyISAM 和 INNOD

16、B 的區(qū)別.事務(wù)安全查詢和添支持全文索引鎖機制度5. 外鍵 MyISAM 不支持外鍵，INNODB 支持外鍵. (在通常是在程序中保證數(shù)據(jù)的一致)開發(fā)中，通常不設(shè)置外鍵，Memory，比如數(shù)據(jù)變化頻繁，不需要入庫，同時又頻繁的查詢和修改，考慮使用 memory,速度極快.如果你的數(shù)據(jù)庫的引擎是 myisam,請一定記住要定時進行碎片整理舉例說明:create table test100(idunsigned ,name varchar(32)engine=myisam;insert insertinserto test100 values(1,aaaaa);o test100 v

17、alues(2,bbbb);o test100 values(3,ccccc);應(yīng)該定義對 myisam 進行整理optimize table test100;_query(“optimize tables $表名”);技術(shù)就是窗戶紙.-經(jīng)常和技術(shù)好人。定時完成數(shù)據(jù)庫的備份 手動備份數(shù)據(jù)庫(表的)方法cmd 控制臺:dump u root proot 數(shù)據(jù)庫 表名 1 表名 2. 文件路徑比如: 把 temp 數(shù)據(jù)庫備份到 d:temp.bakdump u root proot temp d:temp.bak如果你希望備份是，數(shù)據(jù)庫的某幾張表dump u root prot temp dept

18、 d:temp.dept.bak如何使用備份文件恢復(fù)的數(shù)據(jù).控制臺source d:temp.dept.bak 使用定時器來自定完成把備份數(shù)據(jù)庫的指令，寫入到 bat 文件, 然后通過任務(wù)管理器去定時調(diào)用 bat 文件.mytask.bat 內(nèi)容是:C:myenv5.5.27bysqldump -u root -proot temp dept d:temp.dept.bak 如果你的dump.exe 文件路徑有空格，則一定要使用 “” 包括.把 mytask.bat做成一個任務(wù)，并定時調(diào)用在 2:00 調(diào)用一次步驟 任務(wù)計劃-增加一個任務(wù)，選中你的 mytask.bat 文件 ，最后配置:測試

19、 ok現(xiàn)在問題是，每次都是覆蓋原來的備份文件，不利用分時間段進行備份,可以這樣處理;示意圖:代碼是: mytask2.bat 內(nèi)容:C:myenv-5.3.5.exe C:myenvapachehtdocsmytask.mytask.代碼:d:$bakfilename;exec($d);最后測試 ok!作用是，寫一個數(shù)據(jù)庫, 數(shù)據(jù)庫中有三張表，然后每天來命名. 測試.2:00備份一次，文件名以時間使用完成定時發(fā)送郵件的功能 看一個實際的需求 設(shè)計一張郵件表create tabllist(idunsigned primary key auto_increment,getter varchar(6

20、4) not null default , sender varchar(64) not null default , title varchar(32) not null default ,content varchar(2048) not null default ,sendtimeunsigned not null default 0,flag tinyunsigned not null default 0)engine=myisamcharset utf8;insertolistvalues(null,hanspin,o100,abco,unix_timestamp()+10*3600

21、,0);insertolistvalues(null,hanspin,o200,abco200,unix_timestamp()+10*3600,0);1.寫代碼怎樣可以定時的去檢索哪些郵件該發(fā)送.,只能每隔一定時間(1min)就看看哪些郵件該發(fā)送,task.2.在上面的代碼是模擬發(fā)送郵件，看看如何真正發(fā)送郵件.中，有一個函數(shù), 是用于發(fā)送郵件，實際上可以使用er 類，我們使用他完成.要正確的使用er 發(fā)送郵件，需要滿足如下條件1.本身機器是可以聯(lián)網(wǎng)的2.需要搭建自己的 smtp 郵件服務(wù)器-示意圖3.搭建自己的郵件服務(wù)器.卸載.安裝時傻瓜式的，一步一步的走 ok配置 ：3.1 選擇ac3.2

22、s 數(shù)據(jù)庫3.3 配置郵件服務(wù)器點擊設(shè)置-郵箱設(shè)置點擊設(shè)置-服務(wù)器設(shè)置設(shè)置一個賬號(試用版本只能設(shè)置 5 個賬號)代碼:?/ 練習(xí)用er 發(fā)送郵件require(./er/class.hp);$er = newer();/*from 來自于誰to :寄給誰 cc : 抄送subject: 郵件Body: 郵件正文/ 發(fā)送怎么發(fā) ?*/$cont = CharSet = utf-8;er-ContentType = text/html; / 設(shè)置內(nèi)容類型為html,這樣 charset 才能發(fā)揮作用er-Encoding = base64;/ 防止服務(wù)器中繼時,服務(wù)器能接收的編碼不一致,帶來er

23、-From = s52;er-FromName = 順平;er-Subject = 一份問候,你好,韓順平; er-Body = $cont;/$設(shè)置一下語言包er-SetLanguage();/增加收件人地址/ $er-AddAddress(,saozi);$er-AddAddress(hanspin,s);if($er-Send() echo 發(fā)送 okok; elseecho fail ;配置.ini 啟用賬號 s52function; For Win32 only.;SMTP = localhost;smtp_port = 25; For Win32 only.;send_from =

24、 s52測試一把 成功!如何在 linux 下完成定時任務(wù):linux 如何備份.1. 直接執(zhí)行# crontab -e, 需要在同一個服務(wù)器上執(zhí)行.00 * * * * /usr/local/bin/home/htdocs/timhp2.通過 HTTP 請求來觸發(fā)# crontab -e,文件允許不在同一服務(wù)器上00 * * * * /usr/bin/wget -q -O temp.txt http:/timhp上面是通過wget 來請求# crontab -e文件,輸出會保存在臨時文件 temp.txt 中00 * * * * /usr/bin/curl -o temp.txt http:

25、/timhp上面是通過curl -o 來請求# crontab -e00 * * * * lynx -dump http:文件,輸出會保存在臨時文件 temp.txt 中/文件timhp上面是通過Lynx 文本瀏覽器來請求分表技術(shù)分表技術(shù)有(水平分割和垂直分割)當(dāng)一張越來越大時候，即使添加索引還慢的話，可以使用分表以用戶表來具體的說明一下分表的操作.思路如圖 ：首先我創(chuàng)建三張表 user0 / user1 /user2 , 然后我再創(chuàng)建 uuid 表，該表的作用就是提供自增的id,走代碼:create table user0(idunsigned primary key ,name varchar(32) not null default ,pwdvarchar(32) not null default ) engine=myisam charset utf8;create table user1(idunsigned primary key ,name varchar(32) not null default