MySQL資料庫的最佳化

標籤：des   style   使用   os   io   strong   檔案   for   

MySQL資料庫的最佳化技術：  對資料庫的最佳化是一個綜合性的技術，主要包括：    1.表的設計是否合理（符合三範式，3NF）    2.添加適當索引（常見索引有四種：普通索引，主鍵索引，唯一索引，全文索引，（空間索引，複合索引）    3.分表技術（水平分割，垂直分割）    4.讀寫分離    5.預存程序（SQL語句每次執行都需要編譯，預存程序只編譯一次，模組化編程）    6.對MySQL的配置最佳化（配置最大並發數 my.ini，調整緩衝大小）    7.MySQL伺服器的硬體升級    8.定時清除不需要的資料，定時進行磁碟重組    9.SQL語句最佳化***************************************************************************************資料庫表的設計：  什麼樣的表才是符合3NF--首先複合1NF，然後複合2NF,進一步滿足3NF三範式：  1.表的列具有原子性，不可再分割，即列的資訊不能分解，只要資料庫是關係型資料庫，就自動滿足1NF。  2.表中的記錄的主鍵唯一。  3.表中不要有冗餘資料（如果能被推匯出來，就不應該單獨設計一個欄位來表示）。反三範式（適當冗餘）：  但是，沒有冗餘的資料庫未必是最好的資料庫，有時為了提高運行效率，就必須降低範式標準，適當保留冗餘資料。降低範式標準的工作放到物理資料模型設計時考慮。降低範式就是增加欄位，允許冗餘（在表的1對N的情況下，為了提高效率，可能在1方的表中設計欄位，提高效率）。 舉例說明什麼是適當冗餘？  一個年級有多個班，一個班有多個學生，可以在年級表中增加一個欄位來存放年級的全體學生數量。***************************************************************************************SQL語句最佳化：  如何從一個大項目中迅速的定位執行速度比較慢的語句（定位慢查詢）？  1.MySQL資料庫的一些運行狀態如何查詢，如查詢當前MySQL的已耗用時間/一共執行了多少次select/update/delete/有多少串連數？    show status;    已經啟動並執行時間：show status like ‘uptime‘;    已經執行的select/update/delete/insert的次數：show status like ‘com_select‘;show status like ‘com_update‘;show status like ‘com_delete‘;show status like ‘com_insert‘;      show [session|globe] status like ...  //預設為session層級（當前視窗），globe為整個MySQL資料庫    試圖串連MySQL伺服器的次數：show status like ‘connections‘;    顯示慢查詢的次數：show status like ‘slow_queries‘; //實際包括查詢、添加、刪除等操作  2.如何定位慢查詢（預設情況下MySQL認為10秒鐘才是一個慢查詢，可以修改）：    顯示慢查詢的時間：show variabkles like ‘long_query_time‘;    修改MySQL的慢查詢的時間：set long _query_time=1;    delimiter $$  可以定義一個新的命令結束符        若出現一條語句指向時間超過慢查詢設定的時間，就會統計；    預設情況下，MySQL不會記錄慢查詢，需要在啟動時指定：      bin\mysqld.exe --sqfe-mode--slow-query-log  //MySQL5.5版本      bin\mysqld.exe-log-slow-queries=d:/abc.log  //MySQL5.0版本    需要關閉MySQL服務，再重新啟動（不是關閉當前視窗而是關閉整個服務）。    記錄檔放置的路徑可以查看my.ini中的datadir。    此時再出現慢查詢將記錄到記錄檔中。

常用SQL最佳化：  大批量插入資料的情況：    對於MyISAM：      1.alter table table_name disable keys;      2.loading data;  //insert語句;       3.alter table table_name enable keys;    對於Innodb：      1.將要匯入的資料按照主鍵排序      2.set unique_checks=0,關閉唯一性校正。      3.set autocommit=0,關閉自動認可。  最佳化group by 語句    預設情況，MySQL對所有的group by col1,col2進行排序。    這與在查詢中指定order by col1, col2類似。    如果查詢中包括group by但使用者想要避免排序結果的消耗，則可以使用order by null禁止排序。  有些情況下，可以使用串連來替代子查詢。    因為使用join，MySQL不需要在記憶體中建立暫存資料表。

*************************************************************************************** 增加索引： 提高資料庫效能的方法中索引是最物美價廉的。不用加記憶體，不用改程式，不用調整SQL語句，只要執行個正確的‘create index ... ‘，查詢速度就可能提高百倍千倍，但查詢速度的提高是以插入、更新、刪除的速度為代價的，這些寫操作，增加了大量的I/O。

索引是對資料庫表中一列或多列的值進行排序的一種結構。索引的作用相當於圖書的目錄，可以根據目錄中的頁碼快速找到所需的內容。當表中有大量記錄時，若要對錶進行查詢，有兩種方法：  第一種搜尋資訊方式是全表搜尋，是將所有記錄一一取出，和查詢條件進行一一對比，然後返回滿足條件的記錄，這樣做會消耗大量資料庫系統時間，並造成大量磁碟I/O操作；  第二種就是在表中建立索引，然後在索引中找到符合查詢條件的索引值，最後通過儲存在索引中的ROWID（相當於頁碼）快速找到表中對應的記錄。

  主要的四種索引：（普通索引，主鍵索引，唯一索引，全文索引）    1.索引的添加：      1.1.主鍵索引的添加：        當一張表，把某個列設為主鍵的時候，則該列就是主鍵索引。        create table aaa(id int unsigned primary key auto_increment,name varchar(32) not null default ‘‘);          主鍵索引也可以在建立表之後再添加：        alter table [表名] add primary key [列名];      1.2.普通索引添加：        一般來說，普通索引的建立，是先建立表，然後建立普通索引。        create table ccc(int unsigned,name varchar(32));        create index [索引名] on [表明/列名]      1.3.全文索引的添加：        全文索引主要是針對文本的檢索，如：文章。        全文索引針對MyISAM（一種儲存引擎）有效，只針對英文有效（中文用sphinx(coreseek)技術）。        停止詞的概念：因為在一個文本中，建立索引是一個無窮大的數，因此，對一些常用詞和字元，就不會建立，這些詞，稱為停止詞。        CREATE TABLE articles (          id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,          title VARCHAR(200),          body TEXT,          FULLTEXT (title,body)        )engine=myisam charset utf8;        INSERT INTO articles (title,body) VALUES          (‘MySQL Tutorial‘,‘DBMS stands for DataBase ...‘),          (‘How To Use MySQL Well‘,‘After you went through a ...‘),          (‘Optimizing MySQL‘,‘In this tutorial we will show ...‘),          (‘1001 MySQL Tricks‘,‘1. Never run mysqld as root. 2. ...‘),          (‘MySQL vs. YourSQL‘,‘In the following database comparison ...‘),          (‘MySQL Security‘,‘When configured properly, MySQL ...‘);        如何使用全文索引：          select *from articles where body like ‘%mysql%‘;  //錯誤用法          select *from articles where match(title,body) against(‘database‘);  //正確用法          通過explain語句可以分析，MySQL如何執行你的SQL語句(explain select * from articles where body like ‘%mysql%‘ /G  //解釋執行過程)。            explain告訴我們MySQL將使用怎麼樣的執行計畫來最佳化Query(這個語句還沒有被真正執行)。      1.4.唯一索引的添加：        當表的某一列被指定為unique約束時，這個列就是一個唯一索引。        create table ddd(id int primary key auto_increment,name varchar(32) unique);        這是name列就是一個唯一索引。        唯一索引也可以在建立表之後在建立唯一索引：        create table eee(id int primary key auto_increment,name varchar(32));        create unique index [索引名] on [表明/列名]        unique欄位可以為null（不是‘‘），並且可以有多個，有具體內容（包括‘‘）則不能重複。主鍵欄位不能為null也不能重複。    2.索引的查詢：      desc [表名] //但是不能顯示索引名      show index(es) from [表名]      show keys from [表名]    3.索引的刪除：      alter table [表名] drop index [索引名];      ?alter table [表名] drop primary key;  //刪除主鍵索引    4.索引的修改：      先刪除，再重新建立。為什麼建立索引後，尋找速度會變快？  利用二叉樹演算法(BinaryTree)構建索引檔案，時間複雜度log2N。（第六集）  不同的儲存引擎，會使用不同的演算法來構建索引檔案。MySQL的常用儲存引擎：+--------------+----------------+|  儲存引擎    | 允許的索引類型    |+--------------+----------------+|  MyISAM      |   BTREE        | +--------------+----------------+|  InnoDB      |   BTREE        |+--------------+----------------+|  MENORY/HEAP |   BTREE/HASH   |+--------------+----------------+索引的代價：  1.磁碟佔用--索引檔案會佔用磁碟空間  2.對dml(update delete insert)語句的效率影響--需要同時維護索引檔案在哪些列上適合添加索引：  較頻繁的作為查詢條件欄位應該建立索引    select * from emp where empno = 1    唯一性太差的欄位不適合單獨建立索引，即使頻繁作為查詢條件    select * from emp where sex = ‘男‘    更新非常頻繁的欄位不適合建立索引    select * from emp where logincount = 1  不會出現在WHERE子句中欄位不該建立索總結:（滿足以下條件的欄位，才應該建立索引）  a: 肯定在where條經常使用  b: 該欄位的內容不是唯一的幾個值(比如性別)  c: 欄位內容不是頻繁變化索引的使用：  查詢時使用索引最重要的條件是查詢條件中需要使用索引。  下列幾種情況下有可能使用到索引：    1.對於建立的多列索引，只要查詢條件使用了最左邊的列，索引一般就會被使用。      alter table dept add index my_ind (dname,loc);  //dname左邊的列,loc就是右邊的列      explain select * from dept where loc=‘aaa‘\G  //這樣則不會使用索引    2.對於使用like的查詢，查詢如果是‘%aaa‘不會使用到索引，而‘aaa%‘會使用到索引。     （在模糊查詢時，關鍵字最前面不能使用%或者_這樣的字元，如果一定要前面有變化的值，則考慮使用全文索引->sphinx.）      explain select * from dept where dname like ‘%aaa‘\G  下列的表將不使用索引：    1.如果條件中有or，即使其中有條件帶索引也不會使用（即要求使用的所有欄位，都必須建立索引, 我們建議大家盡量避免使用or關鍵字）。    2.對於多列索引，不是使用的第一部分，則不會使用索引。    3.like查詢是以%開頭。    4.如果列類型是字串，那一定要在條件中將資料使用引號引用起來。否則不使用索引。(添加時,字串必須‘‘)    5.如果mysql估計使用全表掃描要比使用索引快，則不使用索引。查看索引的使用方式：  show status like‘Handler_read%‘;    handler_read_key:這個值越高越好，越高表示使用索引查詢到的次數。    handler_read_rnd_next:這個值越高，說明查詢低效。***************************************************************************************選擇合適的儲存引擎：  myisam：表對事務的要求不高，同時操作是以查詢和添加為主（如bbs的發帖表和回複表）。  innoda：對事務要求高，儲存的資料都是重要資料（如訂單表、帳號表）。  memory：資料變化非常頻繁，不需要入庫，同時頻繁的查詢和修改。MyISAM、InnoDB的區別：  主要從交易管理、查詢和添加速度、支援全文索引、鎖機制、是否支援外鍵幾個方面來說。如果儲存引擎是myisam，必須定時進行磁碟重組。    create table test100(id int unsigned ,name varchar(32))engine=myisam; //利用myisam儲存引擎建立表我們應該定義對myisam進行整理    optimize table [表名]; //對錶進行磁碟重組選擇合適的資料類型：  在精度要求高的應用中，建議使用定點數來儲存數值，以保證結果的準確性。    deciaml不要用float***************************************************************************************定時完成Database Backup：  1.手動備份資料庫/表的方法：    mysqldump -u [使用者名稱] -p[密碼] [資料庫] > [檔案路徑]  //在cmd控制台輸入，備份的是資料庫    如：mysqldump -u root -pabc123 temp > d:\temp.bak  //檔案名稱和副檔名可以隨便寫（-p和abc123是沒有空格的）    mysqldump -u [使用者名稱] -p [密碼] [資料庫] [表名1] [表名2] [表名3] ... > [檔案路徑]  //在cmd控制台輸入，備份的是資料庫中的某幾張表  2.使用備份檔案恢複資料：    source d:dept.bak //在MySQL控制台輸入  3.使用定時器自動完成備份（將備份資料的指令寫入到bat檔案--批次檔，通過任務調度器定時執行）：  ----mytask.bat----%mysql安裝目錄的bin的絕對路徑(如果有空格需要用雙引號將路徑括起來)%/mysqldump -u root -pabc123 temp > d:\temp.bak  4.利用工作管理員定時運行批次檔：    控制台--任務管理    預設是以覆蓋檔案的方式，考慮如何以日期為檔案名稱儲存?***************************************************************************************表的分割：
　　當一個表很大，擁有海量的資料，可以將一個大表分割成多個小表。水平表的分割（表的結構不發生變化）：    比如將一張使用者表分成三張表的時候，可以利用主鍵id%3的值來確定使用者在哪張表上。    1.首先我建立三張表 user0 / user1 /user2 ,     2.然後我再建立 uuid表，該表的作用就是提供自增的id。      create table user0(      id int unsigned primary key ,      name varchar(32) not null default ‘‘,      pwd  varchar(32) not null default ‘‘)      engine=myisam charset utf8;      create table user1(      id int unsigned primary key ,      name varchar(32) not null default ‘‘,      pwd  varchar(32) not null default ‘‘)      engine=myisam charset utf8;      create table user2(      id int unsigned primary key ,      name varchar(32) not null default ‘‘,      pwd  varchar(32) not null default ‘‘)      engine=myisam charset utf8;      create table uuid(      id int unsigned primary key auto_increment)engine=myisam charset utf8;    3.添加使用者的時候根據id判斷應該加入哪張表。垂直表的分割（表的結構發生變化）：    把某些表的某些欄位（這些欄位的查詢量很大，而又不需要經常查詢該欄位）,單獨放到另外一張表，從而提高效率。選擇欄位的一般原則是保小不保大，能用佔用位元組小的欄位就不用大欄位。    比如主鍵， 建議使用自增類型，這樣省空間,空間就是效率。按4個位元組和按32個位元組定位一條記錄，誰快誰慢太明顯了。涉及到幾個表做join時，效果就更明顯了。  ***特別注意：建議使用一個不含商務邏輯的id做主鍵。***************************************************************************************對MySQL的配置最佳化:  最重要的參數就是記憶體，我們主要用的innodb引擎，所以下面兩個參數調的很大。　　innodb_additional_mem_pool_size = 64M　　innodb_buffer_pool_size =1G  對於myisam，需要調整key_buffer_size；    當然調整參數還是要看狀態，用show status語句可以看到目前狀態，以決定改調整哪些參數。  在my.ini修改連接埠3306，預設儲存引擎和最大串連數。***************************************************************************************讀寫分離：  如果資料庫壓力很大，一台機器支撐不了，那麼可以用mysql複製實現多台機器同步，將資料庫的壓力分散（負載平衡）。例如：  主庫master用來寫入，slave1—slave3都用來做select，每個資料庫分擔的壓力小了很多;　要實現這種方式，需要程式特別設計，寫都操作master，讀都操作slave，給程式開發帶來了額外負擔;  當然目前已經有中介軟體來實現這個代理，對程式來讀寫哪些資料庫是透明的，官方有個mysql-proxy，但是還是alpha版本的。***************************************************************************************增量備份（5.0無法增量備份，5.1後可以）：  MySQL資料庫會以二進位的形式，自動把使用者對MySQL資料庫的操作，記錄到檔案，當使用者希望恢複的時候可以使用備份檔案，進行恢複。  增量備份會記錄增、刪、改、建表操作，不會記錄查詢操作（查詢不改變資料庫）。  記錄的內容有操作的sql語句、操作的時間、position。步驟：  1.配置my.ini檔案或者my.cof，啟用二進位備份（即增量備份）    #The TCP/IP Port the MySQL Server will listen on    port=3307    #指定備份檔案的路徑    log-bin=d:/binlog/mylog  2.啟動MySQL得到檔案    d:/binlog/mylog/mylog.index //索引檔案，存放有哪些備份檔案    d:/binlog/mylog/mylog.000001  //真正的備份檔案  可以使用mysqlbinlog程式來查看備份檔案的內容。  mysqlbinlog [備份檔案路徑]  //在cmd中進入mysql目錄下的bin中再執行  通過備份檔案恢複資料庫：    通過時間點來恢複：      mysqlbinlog --stop-datetime="2013-01-14 18:20:21" d:/binlog/shunping.000001 | mysql -uroot -p    通過位置來恢複：      mysqlbinlog --stop-position="110" d:/binlog/shunping.000001 | mysql -uroot -p    取出時間段操作：      mysqlbinlog --start-datetime="2013-01-14 18:20:21" --stop-datetime="2013-01-14 18:40:21" d:/binlog/shunping.000001 | mysql -uroot -p    重新執行位置段的操作：      mysqlbinlog --start-position="110" --stop-position="2345" d:/binlog/shunping.000001 | mysql -uroot -p隨著時間的推移，二進位檔案裡面的資料越來越大，所以要週期性做一些清理工作。  1.reset master 可以刪除列於索引檔案中的所有二進位日誌，把二進位日誌索引檔案重新設定為空白，並建立一個新的二進位記錄檔  2.PURGE {MASTER | BINARY} LOGS TO ‘log_name‘    PURGE {MASTER | BINARY} LOGS BEFORE ‘date‘    用於刪除列於在指定的日誌或日期之前的日誌索引中的所有二進位日誌。這些日誌也會從屬記錄在日誌索引檔案中的清單中被刪除，這樣被給定的日誌成為第一個。  3.設定my.ini中的參數[mysqld]下的-EXPIRE_LOGS_DAYS。    此參數是設定日誌的到期天數，到期的日誌將會被自動刪除，如何在工作中將全備份和增量備份配合使用：  方案：每周一做一次全備份(mysqldump)，啟用增量備份，把到期時間設定為大於等於全備份的周期時間。  如果資料庫全奔潰了，先全恢複，在增量恢複；如果誤操作可以看增量日誌進行增量恢複。（需要查看記錄檔的詳細內容）***************************************************************************************