MySQL設計規范與性能優化

jopen 9年前發布 | 15K 次閱讀 MySQL 數據庫服務器

引言

MySQL是目前使用最為廣泛的關系型數據庫之一，如果使用得當，可支撐企業級高并發、高可靠服務，使用不當甚至連并發量略高的個人網站都難以支撐；

就算使用了緩存，大量的數據庫訪問依舊在所難免，即使設置了較長的緩存有效期，而且緩存命中率較理想，但緩存的創建和過期后的重建都是需要訪問數據庫的； 

本文主要從 MySQL表結構設計規范 和 MySQL自身性能優化 兩方面來討論該如何對MySQL數據庫進行優化；

MySQL表結構設計規范

1. 數據庫設計命名規范

（1）數據庫,數據表一律使用前綴，前綴名稱一般不超過5字； 

# 正式數據庫名使用小寫英文以及下劃線組成,盡量說明是哪個應用或者系統在使用的；
mcs_webim
ops_portal


備份數據庫名使用正式庫名加上備份時間組成；


ops_portal_20150621
mcs_webim_20150622


相關應用的數據表使用同一前綴，前綴名稱一般不超過5字；


webim_user
webim_group


備份數據表名使用正式表名加上備份時間組成；


webim_user_20150620
webim_group_20150620</pre> 
（2）字段名稱使用單詞組合完成，首字母小寫，后面單詞的首字母大寫（駝峰式），最好是帶表名前綴； 


# 如表webim_user的字段
userId
userName
userPassword


表與表之間的相關聯字段要用統一名稱；


用盡量少的存儲空間來存儲一個字段的數據；</pre> 
2. 數據庫規范化設計 


（1）范式化設計 


實際關系模式設計中，一般遵循第三范式——在一個數據表中，非主鍵字段之間不能存在依賴關系； 


具體可參考： 規范化—數據庫設計原則 


（2）反范式化設計 


舉例：在頁面顯示我的好友列表；1.遵循第三范式 


(用戶ID, 好友ID)
(用戶ID, 用戶昵稱, 用戶郵箱, 注冊時間, 聯系電話)
 
2.反范式化設計 


(用戶ID, 好友ID, 好友昵稱)
(用戶ID, 用戶昵稱, 用戶郵箱, 注冊時間, 聯系電話)
 


反范式化問題： 


1. 數據冗余； 


2. 更新導致數據不一致問題，可通過定期同步的手段來修改不一致數據； 

</div>




反范式化優勢： 


減少讀取數據的開銷，這點非常重要，需要根據不同場景來適當使用反范式化設計； 

</div>


MySQL自身性能優化 


MySQL自身優化主要從如下幾方面來介紹： 


1. Query語句優化； 


    

  1. 慢查詢優化； 
    2. 

  2. MySQL鎖機制分析優化； 
    3. 

  3. 參數配置優化； </p>

    Query語句優化 
    

    1. MySQL狀態報告 
    

    show status;
show engine innodb status;
     
    一些更加友好的第三方工具： mysqlreport （ 下載地址 ）， mysqltunner ， mytop 等，可展示更加友好的狀態報告； 
    

    2. 正確使用索引 
    

    如果索引使用不當，其他任何優化將毫無意義； 
    

    索引目的 
    

    索引類似于書的目錄，通過不斷的縮小想要獲得數據的范圍來篩選出最終想要的結果，加快查詢速度； 
    

    索引類型 
    

    索引分單列索引和組合索引，組合索引的一個索引包含多列；MySQL索引類型包括： 
    

    （1） 普通索引 ——沒有任何限制 
    

    # 直接創建索引
create index indexName on tableName(columnName(length));
# 如果是char，varchar類型，length長度可以小于字段實際長度；
# 如果是blob和text類型，必須指定length；
# 修改表結構
alter tableName add index indexName on (columnName(length));
# 創建表時直接指定
create table tableName(
 id int not null,
 username varchar(16) not null,
 index indexName (columnName(length))
);
# 刪除索引
drop index indexName on tableName;
# 查看索引
show index from tableName;
     
    （2） 唯一索引 ——索引列的值必須唯一，允許有空值，如果是組合索引，則列值的組合必須唯一； 
    

    # 直接創建索引
create unique index indexName on tableName(columnName(length));
    4. 



修改表結構


alter tableName add unique indexName on (columnName(length));


創建表時直接指定


create table tableName(
    id int not null,
    username varchar(16) not null,
    unique indexName columnName(length)
);</pre> 
（3）主鍵索引——是唯一索引的一種，但不允許有空值，一般是建表的時候直接創建主鍵索引； 


create table tableName(
    id int not null,
    username varchar(16) not null,
    primary key(id)
);
 
（4）組合索引——為多列添加索引； 


# 假設數據表中name, age, address, zip等多個字段，需要為name, age, zip建立組合索引；
create index indexName on tableName(name(10), age, zip);


或


alter table tableName add index indexName (name(10), age, zip);</pre> 
對于varchar類型字段，如果長度過長，最好限制一下索引的長度，可以加快索引查詢速度，減少索引文件的大小； 


最左前綴匹配 


如上面的name，age，zip的組合索引，如下的組合都會用到該索引，可使用 explain 來進行分析： 


select  from tableName where name="lee" and age=20 and zip="050000";
select  from tableName where name="lee" and age=20;
select * from tableName where name="lee";


組合索引對于包含order by和group by的查詢也可發揮作用，同樣遵循最左前綴原則（對于Hash索引，對order by無效）；


select  from tableName order by name, age, zip;
select  from tableName where name="lee" order by age, zip;


對于group by，一般需要先將記錄分組后放在新的臨時表中，然后分別進行函數運算，如count()，sum()，max()等；


如有恰當的索引，可使用索引來取代創建臨時表；


select count(id) from tableName where sex='m' group by age, zip;</pre> 
查詢優化器會自動調整條件表達式的順序，以匹配組合索引；建立索引時一定要注意順序，(key1, key2)和(key2, key1)完全不同； 


建立索引的時機 


一般來說，在 where 和 join 中出現的列需要建立索引，mysql只對<，<=，=，>，>=，between，in以及某些時候（不以通配符%和_開頭的查詢）的like才會使用索引； 


select a.name from table1 as a left join table2 b on a.name=b.username where b.age=20 and b.zip='053000';


此時，需要對username，age和zip建立索引；</pre> 
索引的不足之處 


索引有很大優勢，但是不能濫用，需要根據實際情況來決定到底使不使用索引，該為哪些字段建索引，一般在查詢量占比較多的表才會建立索引； 


    

  • 索引會降低更新表的速度，如insert，update，delete操作，更新表時不僅需要保存數據，還要保存索引文件； 
    • 

  • 過多的組合索引會大大加劇索引文件的膨脹速度，引起磁盤空間存儲的問題，一個包含多個字段的組合索引的尺寸可能已經超過了數據本身，而且索引過多，可也能會使MySQL選擇不到要使用的最好的索引（可使用use index(key_list)來指定查詢時使用的索引）； 
    • 

  • 對于唯一值的列，索引效果最好，對于具有多個重復值的列，如年齡或性別，建立索引不是好辦法； 
    • 
</ul>
    

    使用索引注意事項 
    

      

    1. 索引不會包含有NULL值的列，在數據庫設計時盡量不要讓字段的默認值為NULL，否則無法建立相關字段的索引； 
      2. 

    2. 使用短索引，對varchar類型字段建索引時最好指定長度，只要保證前n個字符多數值是唯一的即可，提高查詢速度，節省磁盤空間，降低I/O操作； 
      3. 

    3. MySQL查詢只是用一個索引，因此如果一條查詢語句中有多個字段需要建索引，最好按照最左前綴匹配原則建立組合索引； 
      4. 

    4. like語句一般不鼓勵使用，在數據量大的情況下，非常容易造成性能問題，如果非使用，通配符%一定要放到后面，如like "abc%"； 
      5. 

    5. 不要在列上進行運算，如select * from users where YEAR(datetime)<2015;，會導致索引失效，進行全表掃描； 
      6. 

    6. 不要使用NOT IN和IN； 
      7. 
</ol>
      

      索引的原理 
      

      主要參考： MySQL索引原理及慢查詢優化 
      

      3. join語句優化 
      

      join語句分為內連接和外連接； 
      

      內連接： 
      

      select * from a inner join b on a.id = b.id;
      

      等價于
      

      select * from a,b where a.id = b.id;</pre> 
      內連接就是檢索出與連接條件完全匹配的數據行； 
      

      外連接： 
      

      select id, name, action from user as u left join user_action a on u.id = a.user_id;
       
      外連接保留了所有驅動表的數據，匹配表中無法匹配的數據則以NULL輸出； 
      

      外連接工作原理 
      

      從左表讀取一條記錄，選出所有與on中條件匹配的右表記錄的（n條）數據，進行連接，形成n條記錄（包括重復的行），如果右邊沒有與on條件匹配的記錄，那連接的字段都是null，繼續讀下一條； 
      

      找出所有在左表而不在右表中的記錄： 
      

      # 注意：a.user_id必須聲明為NOT NULL，如果a，u兩表連接條件中的兩個列具有相同的名字，可使用using(col);
select id, name, action from user as u
    left join user_action a on u.id = a.user_id
    where a.user_id is NULL;
      

      查詢時手動指定索引
      

      select  from table1 use index (key1, key2) where key1=1 and key2=2 and key3=3;
select  from table1 ignore index (key3) where key1=1 and key2=2 and key3=3;</pre> 
      慢查詢優化 
      

      開啟慢查詢日志： 
      

      # 在my.cnf中增加如下配置：
long_query_time = 1
log-slow-queries = /var/log/mysql/mysql_slow.log
      # 將所有沒有使用索引的查詢記錄也記錄下來（根據需要決定是否開啟）：
log-queries-not-using-indexes
      

      慢查詢工具 mysqlsla ，可使用此工具對慢查詢日志進行分析； 
      

      # mysqlsla -lt slow /var/log/mysql/mysql_slow.log
       
      大多數慢查詢都是因為 索引使用不當 造成的，使用索引時一定要謹慎，其他原因還有 查詢語句本身太過復雜（多表聯合查詢） ， 數據表記錄數太多 等； 
      

      鎖機制分析與優化 
      

      鎖機制是影響查詢性能的另一個重要因素； 
      

      查詢的時間開銷主要包括兩部分： 
      

      

      1. 查詢本身的計算時間；（主要受索引影響）  
      

      2. 查詢開始前的等待時間；（受鎖機制影響） 
      
</div>
      

      減少表鎖定等待 
      

      MyISAM類型表提供了表級別鎖定，可使用mysqlreport來查看等待表鎖定查詢所占的比例； 
      

      MyISAM的表鎖定允許多線程同時讀取數據，如select查詢，無需鎖等待； 
      

      對于更新操作，如update、insert、delete操作，會 排斥對當前表的所有查詢 ，并且更新操作有著默認的更高優先級，即當表鎖釋放后，更新操作將先獲得鎖定，全部執行完畢后，才輪到讀取操作，應盡量避免在有大量查詢請求時，批量更改數據表，否則非常容易造成慢查詢； 
      

      可使用如下命令監視所有線程的狀態： 
      

      show processlist\G;
       
      結論： 
      

      對于以查詢操作為主，并且更新操作耗時較低的應用，將不會存在太多的鎖等待，可以使用MyISAM存儲引擎；
對于有頻繁數據更新并且查詢請求量也不低的站點，必須使用提供行鎖定功能的Innodb存儲引擎；
       
      行鎖定 
      

      Innodb存儲引擎提供了行鎖定的支持； 
      

      行鎖定優勢：在select和update混合的情況下，行鎖定可以解決讀和寫互斥的問題，由于update操作和select操作來自不同的線程，并且針對的是不同行的記錄，可以并發進行； 
      

      行鎖定并不一定總是好的： 
      

      1. 行鎖定的開銷并不比表鎖定小； 
      

      2. 在全部都是更新操作的場景下，行鎖定耗時可能會更長，雖然表鎖定每次只有一個線程處于Updating狀態，而行鎖定所有線程都是Updating狀態，但鎖定只是一種邏輯層面的約束，即使全部線程都是Updating狀態，但是磁盤的物理寫操作還是串行執行的； 
      

      3. 對于全部查詢的場景，行鎖定也需要更多額外的開銷，速度相對表鎖定略慢； 
      

      存儲引擎查看 
      

      show table status from DataBaseName where name='TableName';
alter table tableName type=myisam;
       
      參數配置優化 
      

      事務性表性能優化 
      

      

      Innodb存儲引擎除了支持行鎖定，外鍵以及其易于修復的特性，另一個優勢就是其支持事務（ACID），當然，事實上大多數站點都不需要事務級別的保障； 
      

      Innodb是通過預寫日志（WAL）方式來實現事務的，即當有事務提交時，首先寫入內存中的事務日志緩沖區，隨后當事務日志寫入磁盤時，Innodb才更新實際的數據和索引； 
      

      如果選擇使用事務，那事務日志何時寫入磁盤，就是一個優化點了； 
      
</div>
      

      # 事務提交時立即將事務日志寫入磁盤，數據和索引也立即更新，符合持久性原則；
innodb_flush_log_at_trx_commit = 1
      

      事務提交時不立即寫入磁盤，每隔1S寫入磁盤文件一次，并刷新到磁盤，同時更新數據和索引；
      

      如果mysql崩潰，事務日志緩沖區中最近1秒內的數據永久性丟失；
      

      innodb_flush_log_at_trx_commit = 0
      

      事務提交時立即寫入磁盤文件，但間隔1S才會刷新磁盤，同時更新數據和索引；
      

      操作系統崩潰才會造成數據損失；
      

      innodb_flush_log_at_trx_commit = 2</pre> 
      注意： 
      

      “寫入磁盤文件”只是將數據寫入位于物理內存中的內核緩沖區，“刷新到磁盤“是將內核緩沖區中的數據真正寫入到磁盤； 
      

      將innodb_flush_log_at_trx_commit設置為0，可以獲得最好的性能，同時數據丟失的可能性也最大；如果希望盡量避免數據丟失，可設置為2； 
      

      # 設置Innodb數據和索引的內存緩沖池大小，一般可設置為服務器物理內存的80%；
innodb_buffer_pool_size = 12G
       
      使用查詢緩存 
      

      目的：將select的查詢結果緩存在內存中，以供下次直接獲取； 
      

      query_cache_type = 1
query_cache_size = 64M
query_cache_limit = 1M
       
      對于緩存過期策略，MySQL采用的機制是：當一個表有更新操作后，涉及這個表的所有查詢緩存都會失效； 
      

      這個看場景，對于密集select操作且很少更新的表，比較適合使用查詢緩存；對于select和update混合的應用，不適合使用查詢緩存； 
      

      臨時表 
      

      目的：在磁盤上創建臨時表非常耗時，開銷大，需要降低在磁盤上創建臨時表的次數； 
      

      # 盡量給臨時表設置較大的內存空間，當內存空間不夠時，MySQL將會啟用磁盤來保存臨時表；
tmp_table_size = 512M
       
      線程池 
      

      MySQL采用多線程來處理并發連接，如果每次都新建連接，都要創建新的線程，在系統繁忙的時候，也會增加MySQL的開銷； 
      

      # 盡量使用持久連接，減少線程的重復創建；
thread_cache_size = 100
      

      可以使MySQL緩存100個線程；</pre> 
      參考 
      

      
                    
      
                        
       本文由用戶 jopen 自行上傳分享，僅供網友學習交流。所有權歸原作者，若您的權利被侵害，請聯系管理員。
      
                        
       轉載本站原創文章，請注明出處，并保留原始鏈接、圖片水印。
      
                        
       本站是一個以用戶分享為主的開源技術平臺，歡迎各類分享！
      
                        
                        
                    
      

                
      
                
      
                    
      
                        
      相關經驗
      
                        
                    
      
                    
      
                        
      相關資訊
      
                        
                    
      
                    
      
                        
      相關文檔