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