筆記mysql優化

Michael_DD發表於2014-02-26
筆記mysql優化

資料庫優化工作

 1  資料庫(表)設計合理
     我們的表設計要符合3NF  3正規化   有時需要適當的逆正規化
 2  sql語句的優化  
 3  資料庫引數修改,配置
 4  適當的硬體配置和作業系統

資料的 3NF
1NF  就是具有原子性,不可分割(只要使用的是關係型資料庫,自動符合)
2NF  在滿足1NF的基礎上,我們考慮是否滿足2NF,只要表的記錄滿足唯一性,也是說,你的同一張表,不可能出現完全相同的記錄,一般說我們在表中設計一個主鍵既可。
3NF  在滿足2NF的基礎上,我們考慮是否滿足3NF,即我們的欄位資訊可以通過關聯的關係,派生即可(通常我們通過外來鍵來處理)

逆正規化舉例:
相簿功能舉例:(正確逆正規化舉例)
  (錯誤逆正規化舉例)

 sql語句的優化
面試題:sql語句有幾類
  DDL(資料定義語言(define))(create  alter  drop)
  DML(資料操作語言)(insert  delete   update)
  SELECT (單獨)
  DTL(資料事物語句)(commit  rollback  savepoint)
  DCL(資料控制語句)(grant   revoke)

 show status 命令
該命令可以顯示你的mysql資料庫的當前狀態,我們主要關係的是“com”開頭的指令
mysql> show status like "Com%";  <=>show session status like "Com%";
//顯示當前操作檯對資料庫的操作次數
mysql>show global status like "Com%";   //顯示資料庫從啟動帶現在的操作次數


mysql> create table tem(id int)engine=MyISAM;
顯示連線資料庫次數
mysql> show status like 'Connections';

顯示伺服器工作的時間(單位秒)
mysql> show status like 'Uptime';

顯示慢查詢的次數(預設是10)
mysql> show status like 'Slow_queries';
可以自定義慢查詢時間
我們優化的重點在  慢查詢  (預設是10s)
修改預設的慢查詢時間:  mysql> set long_query_time=1;
顯示慢查詢的情況
mysql> show variables like "long_query_time";

建立一個海量表:
壓力測試指令碼
mysql> set names gbk;

#定義一個新的命令結束符合
delimiter $$
在海量表中,查詢速度很快:
mysql> select * from emp where empno=123456;

 需求:如何在一個專案中,找到慢查詢的select, mysql資料庫支援包慢查詢語句,記錄到日誌中,供程式設計師分析。(但是注意:預設情況下,不啟用)
1,要這樣啟動mysql
  進入到mysql安裝目錄bin
2,啟動 xx>bin\mysqld.exe –slow-query-log  
    mysql> set long_query_time=1;   修改預設慢查詢時間

快速體驗,在表裡面加索引
(增加索引,select變快,但是insert,update, delete變慢)
(磁碟佔用)
索引原理:

建立主鍵索引:
mysql> alter table emp add primary key (empno);
刪除索引:
mysql> alter table emp drop primary key;

介紹一款非常重要工具explain,這個分析工具可以對sql語句進行分析,可以預測你的sql執行效率。
分析:
mysql> explain select *from emp where empno=456723\G;
       執行的效率.
       他的基本用法是:
       explain sql語句\G
       //根據返回的資訊,我們可知,該sql語句是否使用索引,從多少記錄中取出,可以看    
        到排序的方式.
         
如果你的資料庫的儲存引擎室MyISAM的,則當建立一個表後,三個檔案:*frm 記錄表結構  *myd  資料  *myi 索引

 在什麼列上新增索引:
1,比較頻繁的作為查詢條件欄位應該建立索引(經常查詢的列)
  select *from emp where empno =1;
2,唯一性  太差 的欄位不適合建立索引,即使作為頻繁的查詢條件
  select *from emp where sex='男';
3,更新比較頻繁的,不合適加索引
  select *from emp where logincount = 1;
4,不會出現在where字句中欄位不該建立索引

索引的種類
1,主鍵索引 (把某列或者多列設為主鍵,則就是主鍵索引)
2,唯一索引  (unique)(即該列具有唯一性,同時又是索引)
3,index (普通索引)
4,全文索引(FULLTEXT)(英文中使用)(只有MyISAM引擎支援)
  sphinx+中文分詞  coreseek  (中文中使用)
 
 (文章管理系統)
  select *from where content like "%成龍%";
5,綜合使用=>複合索引   (多列合在一起使用)
  create index myind on 表名 (列1,列2);


檢視某表的索引
mysql> show indexes from aa\G;

 索引管理
1,如果建立 unique/index/fulltext 索引
  create unique/index/fulltext 索引名 on 表名 (列名)
  alter table [table_name] add INDEX [index_name] ([column_name]);
  //如果新增主鍵索引
  alter table [table_name] add primary key [column_name];
 
2,刪除索引
  drop index [index_name] on [table_name];
  alter table [table_name] drop index [index_name];
  alter table [table_name] drop primary key;

3,顯示索引
  show index(es) from [table_name];
  show keys from [table_name];
  desc [table_name];
 

使用索引的注意事項
查詢要使用索引最重要的條件是查詢條件中需要使用索引。
下列幾種情況下有可能使用到索引:
1,對於建立的多列索引,只要查詢條件使用了最左邊的列,索引一般就會被使用。
2,對於使用like的查詢,查詢如果是  ‘%aaa’ 不會使用到索引
‘aaa%’ 會使用到索引。
下列的表將不使用索引:
1,如果條件中有or,即使其中有條件帶索引也不會使用。
2,對於多列索引,不是使用的第一部分,則不會使用索引。
3,like查詢是以%開頭
4,如果列型別是字串,那一定要在條件中將資料使用引號引用起來。否則不使用索引。
5,如果mysql估計使用全表掃描要比使用索引快,則不使用索引。

如何檢測你的索引是否有效

結論: Handler_read_key 越大越少
Handler_read_rnd_next 越小越好

常用的SQL優化
大批量插入資料
對於MyISAM
alter table  table_name disable keys;  (keys不可用後再匯入資料)
loading data;
alter table table_name enable keys;
對於Innodb
1, 將要匯入的資料按照主鍵排序
2, set unique_hcecks=0, 關閉唯一性校驗。
3, set autocommit=0, 關閉自動提交

常用的SQL優化
大批量插入資料
對於MyISAM
alter table  table_name disable keys;  (keys不可用後再匯入資料)
loading data;
alter table table_name enable keys;
對於Innodb
1, 將要匯入的資料按照主鍵排序
2, set unique_hcecks=0, 關閉唯一性校驗。
3, set autocommit=0, 關閉自動提交

 MyISAM和Innodb區別是什麼
1,MyISAM不支援外來鍵,Innodb支援
2,MyISAM不支援事務,不支援外來鍵
3,對資料資訊的儲存處理方式不同 (如果儲存引擎室MyISAM的,則建立一張表,對應三個檔案。如果是Innodb則建立一張表,對應一個檔案 *.frm,資料存放在ibdata1)

對於MyISAM資料庫,需要定時清理
mysql> optimize table user;  (清理user表)


 常見的sql優化手法
1,使用order by null 禁用排序
  mysql> select *from dept group by dname order by null;(groupby 預設排序)

2,有些情況下,可以使用連線來替代子查詢。
  因為使用囧,MySQL不需要再記憶體中建立臨時表

3,如果想要在含有or的查詢語句中使用索引,則or之間的每個新增列都
  必須要到索引,如果沒有索引,則應該考慮增加索引
  select *from [table_name] where 條件1 = ‘’or 條件2 =‘’;

4,選擇合適的儲存引擎,MyISAM與Innodb區別
  MyISAM:以讀操作也插入操作為主,很少更新和刪除操作,並且對於事務的完整性,併發性要求不是很高,其優勢是訪問的速度快。
  InnoDB:提供了具有提交,回滾和崩潰恢復能力的事務安裝,但是對比MyISAM,寫的處理效率差一些,並且會佔用更多的磁碟空間。

5,在精度要求高的應用中,建議使用定點數(decimal)來儲存數值,以保證結果的準確性。(特別對於財務系統)
  100000.32萬
  create table sal(t1  float(10.2));  (不使用浮點數)
  create table sal(t1  decimal(10.2));  (使用decimal)
6,對於使用儲存引擎室MyISAM的資料庫,如果經常做刪除和修改記錄的操作,要定時執行 optimize table [table_name]; 功能:對整個表進行碎片整理。

7,日期型別要根據實際需要選擇能夠滿足應用的最小儲存的早期型別。
  create table bbs (id int, con varchar(1024), pub_time int);
  使用數值存時間,使用date函式轉換即可
  date(‘Ymd’, 時間-3*24*60*60)

   問?在php中 ,int 如果是一個有符號數,最大值. int- 4*8=32   2 的31次方 -1

表的水平劃分

表的垂直分割

【試題內容】、【答案資訊】兩個表,最初是作為幾個欄位新增到【試題資訊】裡的,可以看到試題內容和答案這兩個欄位很長,在表裡有3萬記錄時,表已經佔 了1G的空間,在列試題列表時非常慢。經過分析,發現系統很多時候是根據【冊】、【單元】、型別、類別、難易程度等查詢條件,分頁顯示試題詳細內容。而每 次檢索都是這幾個表做join,每次要掃描一遍1G的表。我們完全可以把內容和答案拆分成另一個表,只有顯示詳細內容的時候才讀這個大表,由此 就產生了【試題內容】、【答案資訊】兩個表。


選擇合適的欄位型別,特別是主鍵
原則:保小不保大, 能用小的就不用大的
幾個表做join時,效果就更明顯了。
es1234 , 建議使用一個不含業務邏輯的id做主角

檔案、圖片等大檔案用檔案系統儲存
資料庫只儲存路徑。圖片和檔案存放在檔案系統,甚至單獨放在一臺伺服器(圖床).


資料庫引數配置
  最重要的引數就是記憶體,
innodb引擎:所以下面兩個引數調的很大
  innodb_additional_mem_pool_size = 64M
  innodb_buffer_pool_size =1G
 myisam引擎:需要調整key_buffer_size
當然調整引數還是要看狀態,用show status語句可以看到當前狀態,以決定改調整哪些引數


合理的硬體資源和作業系統
1,如果你的機器記憶體超過4G,那麼,應當採用64位的作業系統和64位的mysql

2,讀寫分離
  如果資料庫壓力很大,一臺機器支撐不了,那麼可以用mysql複製實現多臺機器同步,將資料庫的壓力分散。

來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/29500582/viewspace-1092408/,如需轉載,請註明出處,否則將追究法律責任。

相關文章