程式師世界 >> 數據庫知識 >> MYSQL數據庫 >> MySQL綜合教程 >> 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定時完成數據庫備份

① 手動備份命令
備份數據庫
# 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實現定時數據庫備份

<?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