筆記mysql最佳化

筆記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複製實現多臺機器同步，將資料庫的壓力分散。