mysql最佳化筆記，mysql最佳化

mysql最佳化筆記，mysql最佳化
【mysql最佳化部分】
最佳化大致思路：
a. 表的設計合理化(符合3NF)
b. 添加適當的索引(index)
   mysql的索引大致分為四類：
   普通索引、主鍵索引、唯一索引、全文索引
c. 分表技術(水平分割、垂直分割)
d. 讀寫分離(讀 select 寫 insert/delete/update)
e. 預存程序(模組化編程，可以提高速度)
f. 對MySQL的配置最佳化(如 最大並發數 max_connections等)
g. MySQL伺服器硬體升級
h. 定時清除不必要的資料 定時進行磁碟重組(尤其是myisam儲存引擎)


【一、表的設計合理化】1、表的設計原則

1NF：表的列屬性不可分割。(關係型資料庫都滿足，不必考慮)
2NF：表中的記錄唯一。(通過設定主鍵來實現)
     主鍵一般不含商務邏輯，自增長
3NF：表中不含冗餘資料(表的某些欄位能被推匯出來，
     就不應該單獨設計欄位來存放他們)


有時候會用到反3NF的欄位設計表。
例如：
[分類表]：id view
[詳細表]：id view name cid
其中[分類表]中的瀏覽量要通過計算[詳細表]中的瀏覽量得來
那麼我們可以，在[分類表]中也加一個view欄位，
在更新[詳細表]的時候，也增加[分類表]中的view值，那麼提取的
時候可以免於計算，提高查詢效率。


總的來說，需要計算得出的欄位，盡量不要在查詢的時候
進行計算，將他們改在更新或插入的時候作為一個欄位計算好。


2、欄位的設計原則(保短不保長)

能用tinyint 就不要用 int
如果能用char 就不要用varchar
能用varchar 就不要用 text
...
總而言之，就是盡量使用合適的欄位類型設計表的欄位。


【二、選擇合適的儲存引擎】1、選取原則myisam：表對事務的要求不高，主要以查詢和添加修改為主，
        考慮使用此引擎(如評價表等)。
innodb：對事務的要求高，儲存的都是重要資料，建議使用
        此引擎(如訂單表、帳號表)。
memory：資料變化頻繁，不需要入庫。同時頻繁的查詢和修改，
        考慮使用此引擎(如使用者的登入狀態等)。
註：memory資料存在記憶體中，重啟mysql會丟失。


2、myisam與innodb的區別① 事務安全(innodb)
② 查詢和添加速度(myisam)
③ 支援全文索引(myisam)
④ 鎖機制(innodb)
⑤ 外鍵(innodb)


3、大量資料寫入① 對於myisam，關閉索引
  alter table table_name disable keys;
  插入載入資料
  alter table table_name enable keys;
  因為建立表的時候會自動建立索引，這樣負載會加大


② 對於innodb 
  將要插入的資料按主鍵進行排序
  set unique_checks=0;#關閉唯一索引(唯一性檢查影響效率)
  set autocommit=0; #關閉自動認可


【三、建立合適的索引】

四種索引的使用(主鍵、唯一、全文、普通索引)


1、主鍵索引添加索引 alter table art add primary key(id);
刪除索引 alter table art drop primary key;


2、唯一索引表的某一列被指定為unique 關鍵字是時 即為唯一索引
唯一索引允許為null 和 ''
但是可以允許多個null值存在，不能有多個''(空串)存在
create unique index 索引名 on 表名 (列名1,...)


3、全文索引在建立表的時候建立
create table art(
   id int primary key,
   title varchar(20),
   body text,
   FULL TEXT(title,body)
) engine=myisam charset utf8;


注意：
① 全文索引只支援myisam引擎
② mysql 系統提供的全文索引，只支援英文，不支援中文
  如果要支援中文的話，需要下載sphinx外掛程式
③ 全文索引有一個停止詞，在一篇文章中，建立全文索引是一個
   無窮大的數，所以只會給不常見的詞建立全文索引。
④ 使用全文索引必須遵循使用規則 match() against();
   select * from art where match(title,body) against('daye');




4、普通索引create index 索引名 on 表名(列名);
alter table 表名 add index 索引名(列名);


刪除索引: alter table 表名 drop index 索引名


5、索引的查詢① 表結構查詢
desc 表名;


② 查詢單個索引
select index(索引名) from 表名\G


③ 查詢表的所有索引
show keys from 表名\G


④ 查看索引的使用方式
show status like 'handler_read%'
handler_read_key      高 說明索引使用率高
handler_read_rnd_next 高 說明查詢效率低


6、索引的使用原則① 建立了多列的索引，只有最左側的列被使用時，索引才會被使用
② 使用like 關鍵字進行查詢時，開頭不能有萬用字元'％'、'_'等
   否則不會使用索引
③ 條件中含or關鍵字 不會使用索引


7、索引添加原則 與 優缺點①優點 查詢速度快  使用二叉樹log2n次查詢
②缺點 佔用磁碟空間 
       對dml語句(非查詢語句) 頻繁操作的表 會導致速度變慢
③添加原則
  添加where子句中頻繁使用到的欄位為索引
  唯一性太差的欄位不適合單獨做索引






【四、表的分割技術】

1、水平分割即將一個表複製成多張表 結構不變
原則：
① 表結構不變
② 應根據業務的需求，找到分表的標準，並在檢索頁面
   約束使用者權限。


[例] 一張qq登入表，幾億條資料 qqlogin
我們根據使用者 id%3 的餘數決定將使用者存入哪一張表
uuid(自動產生使用者id)
qqlogin0(存入id求餘結果為0的使用者)
qqlogin1(存入id求餘結果為1的使用者)
qqlogin2(存入id求餘結果為2的使用者)


2、垂直分割即將一張表中 常用 和不常用的欄位分離出來，組成兩張不同表
原則：
① 將表中不常用的欄位分離出來
② 將表中資料量較大，會影響查詢速度的表分離出來
③ 注意分離表與原表的關聯關係


【五、讀寫分離】

1、表的主從複製insert  into tab1 
select col1 col2 ... from tab2;




【六、主從複製】(略) 詳細後面章節進行講解




【七、定位慢查詢sql】(注意：這裡慢查詢不一定只指select語句，其它語句執行速度
 比較慢的也叫慢查詢)
SQL最佳化一般思路：
1、通過show status 命令瞭解各種sql執行的效率
2、定位執行效率較低的sql語句
3、通過explain 分析低效率sql語句的執行情況
4、確定問題採取相應的措施


1、通過show status 命令瞭解各種sql執行的效率show [session|global] status like '%%';
其中：session為當前的會話視窗統計。預設項
      global 則為所有會話視窗統計。


① mysql的已耗用時間：
show status like 'uptime';


② 一共執行的次數：
select: show status like 'com_select';
update: show status like 'com_update';
insert: show status like 'com_insert';
delete: show status like 'com_delete';


③ 當前串連數
show status like 'connections';


④ 顯示慢查詢次數
show status like 'slow_queries';


2、定位執行效率較低的sql語句我們要通過以下幾步定位慢查詢sql語句：
① 關閉mysql服務
在windows下，開啟"服務"，找到mysql，關閉服務
在Linux下，直接找到mysqld 進程，kill掉


② 命令列進入mysql的安裝目錄 輸入
版本5.5及以後
bin\mysqld.exe --safe-mode --slow-query-log
版本5.0及以前
bin\mysqld.exe -log-slow-queries=d:/ab.log


斷行符號


③ 再次進入mysql命令列模式 更改慢查詢設定的時間限制為1s
set long_query_time = 1;


④ 此時慢查詢日誌已開啟
記錄地址在：my.ini 中的datadir所指的目錄中


3、通過explain 分析低效率sql語句的執行情況

mysql> explain select * from emp where empno = 345680\G
*************************** 1. row ***************************
           id: 1        #查詢序號
  select_type: SIMPLE   #查詢類型
                        PRIMARY/
        table: emp      #查詢的表名
         type: ALL      #掃描方式 ALL(全表掃描，盡量避免)
               SYSTEM 表僅有一行
CONST  表匹配到的僅有一行


possible_keys: NULL     #表中可能使用到的索引
          key: NULL     #實際使用的索引
      key_len: NULL     
          ref: NULL
         rows: 4000000     #該sql語句掃描了多少行，可能得到記錄數
        Extra: Using where #額外資訊 比如排序方式 如filesort等
1 row in set (0.00 sec)




4、確定問題採取相應的措施最佳化sql語句


① 最佳化group by 語句
使用group by子句後 系統會預設進行排序
如果不需要進行排序，則建議加上 order by null


② 使用串連 join 代替子查詢


【八、磁碟重組】

針對myisam引擎進行磁碟重組


//對指定的表進行磁碟重組


mysql> optimize table table_name; 




【九、備份/還原】

1、PHP定時完成Database Backup① 手動備份 命令
備份資料庫
# mysqldump -uroot -psa 資料庫名  > /檔案路徑
備份表
#mysqldump -uroot -psa 資料庫名.表1 資料庫名.表2... > 檔案路徑


② 手動資料 恢複
mysql> source 備份檔案路徑


2、定時任務① windows 批處理 (工作管理員)
(1)建立 .bat 批次檔 (如 my.bat )
找到mysql檔案的安裝的bin 目錄 複製檔案路徑
在my.bat中加入如下語句
D:\phpservice\mysql\bin\mysqldump -uroot -psa demp > d:demp.bak.sql


(2)將my.bat 檔案加入定時任務
控制台 -> 管理工具 -> 工作排程器 -> 操作 -> 建立任務 ->
  操作   中匯入任務
  觸發器 中建立設定觸發時間
  常規   中設定任務名稱
  條件   中設定任務時間
  設定   中設定任務的相關條件


(3) 設定好以後，就會按時觸發了


② linux  crontab -e 計劃任務
這個更簡單
直接將上面寫入的指令碼路徑 與 程式路徑 更改一下就ok


3、PHP實現定時Database Backup

<?php
//設定時區
date_default_timezone_set("PRC");
//設定檔案名稱
$bakfile_path = date('Y-m-d H:i:s', time());
//拼裝命令
$commond = "D:\phpservice\mysql\bin\mysqldump -uroot -psa demp > d:{$bakfile_path}.bak.sql";
//執行命令
exec($commond);


?>


4、mysql的增量備份

   mysql資料庫會以二進位的形式，將mysql對資料庫的操作，記錄到檔案
當使用者希望恢複的時候，可以使用該檔案進行備份恢複。


增量備份原理
① 記錄dml語句(不含查詢語句)
② 記錄 a. 動作陳述式本身
        b. 操作時間
c. 操作position


如何進行增量備份/與恢複
(1) 配置mysql.ini 啟用二進位的備份
在[mysqld] 下增加語句
log-bin = d:\binlog\mylog


(2) 重啟mysql服務 (這一步很關鍵)
windows 下 在服務裡找到 mysql 並重啟
linux 下 restart mysql 進程


(3) 查看mysql的日誌
找到記錄檔的位置
使用命令:
# mysqlbinlog 記錄檔路徑


日誌分析
a. end_log_pos 記錄檔中操作 所處的位置
b. TIMESTAMP 操作所處的時間點
c. 根據上面兩點進行資料庫的恢複


(4) 按時間戳記/位置恢複
按時間恢複
# mysqlbinlog --stop-datetime="2015-01-14 18:23:43" d:\binlog\mylog000001 | mysql -uroot -psa
按位置恢複
# mysqlbinlog --start-position="112"  d:\binlog\mylog000001 | mysql -uroot -psa




【十、配置最佳化】

1、連接埠號碼更改如果要設定多個mysql 在同一伺服器上使用，需要更改連接埠號碼
如果不使用3306，則需要在mysql_connect串連函數使用的時候帶上
連接埠號碼


2、更改最大串連數(mysql.ini/mysql.conf)max_connections = 100 (最佳範圍100-1000)


3、開啟查詢快取query_cache_size = 15M


4、針對不同的引擎設定不同的緩衝大小myisam ---> key_buffer_size
innodb ---> innodb_additonal_new_pool_size = 64M
            innodb_buffer_pool_size = 1G


5、如果伺服器記憶體超過4G，可考慮使用64位作業系統和
64位mysql伺服器