【mysql優(yōu)化部分】
優(yōu)化大致思路：
a. 表的設計合理化(符合3NF)
b. 添加適當?shù)乃饕?index)
mysql的索引大致分為四類：
普通索引、主鍵索引,、唯一索引,、全文索引
c. 分表技術(水平分割、垂直分割)
d. 讀寫分離(讀 select 寫 insert/delete/update)
e. 存儲過程(模塊化編程,，可以提高速度)
f. 對MySQL的配置優(yōu)化(如 最大并發(fā)數(shù) max_connections等)
g. MySQL服務器硬件升級
h. 定時清除不必要的數(shù)據(jù) 定時進行碎片整理(尤其是myisam存儲引擎)
【一,、表的設計合理化】
1、表的設計原則
1NF：表的列屬性不可分割,。(關系型數(shù)據(jù)庫都滿足,，不必考慮)
2NF：表中的記錄唯一。(通過設置主鍵來實現(xiàn))
主鍵一般不含業(yè)務邏輯,，自增長
3NF：表中不含冗余數(shù)據(jù)(表的某些字段能被推導出來,，
就不應該單獨設計字段來存放他們)
有時候會用到反3NF的字段設計表,。
例如：
[分類表]：id view
[詳細表]：id view name cid
其中[分類表]中的瀏覽量要通過計算[詳細表]中的瀏覽量得來
那么我們可以，在[分類表]中也加一個view字段,，
在更新[詳細表]的時候,，也增加[分類表]中的view值，那么提取的
時候可以免于計算,，提高查詢效率。
總的來說,，需要計算得出的字段,，盡量不要在查詢的時候
進行計算，將他們改在更新或插入的時候作為一個字段計算好,。
2,、字段的設計原則(保短不保長)
能用tinyint 就不要用 int
如果能用char 就不要用varchar
能用varchar 就不要用 text
...
總而言之，就是盡量使用合適的字段類型設計表的字段,。
【二,、選擇合適的存儲引擎】
1、選取原則
myisam：表對事務的要求不高,，主要以查詢和添加修改為主,，
考慮使用此引擎(如評價表等)。
innodb：對事務的要求高,，保存的都是重要數(shù)據(jù),，建議使用
此引擎(如訂單表、賬號表),。
memory：數(shù)據(jù)變化頻繁,，不需要入庫。同時頻繁的查詢和修改,，
考慮使用此引擎(如用戶的登錄狀態(tài)等),。
注：memory數(shù)據(jù)存在內存中，重啟mysql會丟失,。
2,、myisam與innodb的區(qū)別
① 事務安全(innodb)
② 查詢和添加速度(myisam)
③ 支持全文索引(myisam)
④ 鎖機制(innodb)
⑤ 外鍵(innodb)
3、大量數(shù)據(jù)寫入
① 對于myisam,，關閉索引
alter table table_name disable keys;
插入加載數(shù)據(jù)
alter table table_name enable keys;
因為創(chuàng)建表的時候會自動創(chuàng)建索引,，這樣負載會加大
② 對于innodb
將要插入的數(shù)據(jù)按主鍵進行排序
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、全文索引
在創(chuàng)建表的時候創(chuàng)建
create table art(
id int primary key,
title varchar(20),
body text,
FULL TEXT(title,body)
) engine=myisam charset utf8;
注意：
① 全文索引只支持myisam引擎
② mysql 系統(tǒng)提供的全文索引,，只支持英文,，不支持中文
如果要支持中文的話，需要下載sphinx插件
③ 全文索引有一個停止詞,，在一篇文章中,，創(chuàng)建全文索引是一個
無窮大的數(shù)，所以只會給不常見的詞創(chuàng)建全文索引,。
④ 使用全文索引必須遵循使用規(guī)則 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,、索引的使用原則
① 創(chuàng)建了多列的索引,，只有最左側的列被使用時，索引才會被使用
② 使用like 關鍵字進行查詢時,，開頭不能有通配符'％',、'_'等
否則不會使用索引
③ 條件中含or關鍵字 不會使用索引
7、索引添加原則 與 優(yōu)缺點
①優(yōu)點 查詢速度快 使用二叉樹log2n次查詢
②缺點 占用磁盤空間
對dml語句(非查詢語句) 頻繁操作的表 會導致速度變慢
③添加原則
添加where子句中頻繁使用到的字段為索引
唯一性太差的字段不適合單獨做索引
【四,、表的分割技術】
1,、水平分割
即將一個表復制成多張表 結構不變
原則：
① 表結構不變
② 應根據(jù)業(yè)務的需求，找到分表的標準,，并在檢索頁面
約束用戶權限,。
[例] 一張qq登錄表，幾億條數(shù)據(jù) qqlogin
我們根據(jù)用戶 id%3 的余數(shù)決定將用戶存入哪一張表
uuid(自動生成用戶id)
qqlogin0(存入id求余結果為0的用戶)
qqlogin1(存入id求余結果為1的用戶)
qqlogin2(存入id求余結果為2的用戶)
2,、垂直分割
即將一張表中 常用 和不常用的字段分離出來,，組成兩張不同表
原則：
① 將表中不常用的字段分離出來
② 將表中數(shù)據(jù)量較大，會影響查詢速度的表分離出來
③ 注意分離表與原表的關聯(lián)關系
【五,、讀寫分離】
1,、表的主從復制
insert into tab1
select col1 col2 ... from tab2;
【六、主從復制】
(略) 詳細后面章節(jié)進行講解
【七,、定位慢查詢sql】
(注意：這里慢查詢不一定只指select語句,，其它語句執(zhí)行速度
比較慢的也叫慢查詢)
SQL優(yōu)化一般思路：
1、通過show status 命令了解各種sql執(zhí)行的效率
2,、定位執(zhí)行效率較低的sql語句
3,、通過explain 分析低效率sql語句的執(zhí)行情況
4、確定問題采取相應的措施
1,、通過show status 命令了解各種sql執(zhí)行的效率
show [session|global] status like '%%';
其中：session為當前的會話窗口統(tǒng)計,。默認項
global 則為所有會話窗口統(tǒng)計。
① mysql的運行時間：
show status like 'uptime';
② 一共執(zhí)行的次數(shù)：
select: show status like 'com_select';
update: show status like 'com_update';
insert: show status like 'com_insert';
delete: show status like 'com_delete';
③ 當前連接數(shù)
show status like 'connections';
④ 顯示慢查詢次數(shù)
show status like 'slow_queries';
2,、定位執(zhí)行效率較低的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語句的執(zhí)行情況
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語句掃描了多少行,，可能得到記錄數(shù)
Extra: Using where #額外信息 比如排序方式 如filesort等
1 row in set (0.00 sec)
4、確定問題采取相應的措施
優(yōu)化sql語句
① 優(yōu)化group by 語句
使用group by子句后 系統(tǒng)會默認進行排序
如果不需要進行排序,，則建議加上 order by null
② 使用連接 join 代替子查詢
【八,、碎片整理】
針對myisam引擎進行碎片整理
//對指定的表進行碎片整理
mysql> optimize table table_name;
【九、備份/還原】
1,、PHP定時完成數(shù)據(jù)庫備份
① 手動備份 命令
備份數(shù)據(jù)庫
# mysqldump -uroot -psa 數(shù)據(jù)庫名 > /文件路徑
備份表
#mysqldump -uroot -psa 數(shù)據(jù)庫名.表1 數(shù)據(jù)庫名.表2... > 文件路徑
② 手動數(shù)據(jù) 恢復
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 文件加入定時任務
控制面板 -> 管理工具 -> 任務計劃程序 -> 操作 -> 創(chuàng)建任務 ->
操作 中導入任務
觸發(fā)器 中新建設置觸發(fā)時間
常規(guī) 中設置任務名稱
條件 中設置任務時間
設置 中設置任務的相關條件
(3) 設置好以后,，就會按時觸發(fā)了
② linux crontab -e 計劃任務
這個更簡單
直接將上面寫入的腳本路徑 與 程序路徑 更改一下就ok
3,、PHP實現(xiàn)定時數(shù)據(jù)庫備份
<?php
//設置時區(qū)
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";
//執(zhí)行命令
exec($commond);
?>
4、mysql的增量備份
mysql數(shù)據(jù)庫會以二進制的形式,，將mysql對數(shù)據(jù)庫的操作,，記錄到文件
當用戶希望恢復的時候，可以使用該文件進行備份恢復,。
增量備份原理
① 記錄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. 根據(jù)上面兩點進行數(shù)據(jù)庫的恢復
(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
【十,、配置優(yōu)化】
1、端口號更改
如果要設定多個mysql 在同一服務器上使用,，需要更改端口號
如果不使用3306,，則需要在mysql_connect連接函數(shù)使用的時候帶上
端口號
2、更改最大連接數(shù)(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位操作系統(tǒng)和
64位mysql服務器
|
