mysql数据库优化韩顺平_韩顺平 Mysql数据库优化(一) 优化概述
第 1 章Mysql優化概述
網站的瓶頸在web層(web吞吐量),程序對mysql的操作.我們前面講的頁面靜態化技術和memcached技術目的減少對mysql訪問,但是總是訪問數據庫,所以我們需要對數據庫本身進行優化.在PHP和Java開發中,主要從7個方面優化
①數據庫(表)本身設計要規范(至少要求滿足3NF) 3范式
②創建適當索引(主鍵索引,普通索引,唯一索引,全文索引sphinx->coreseek,空間索引)
③優化SQL語句->如何定位慢查詢
④使用分表技術[思路:大->小],水平分割,垂直分割
⑤創建適當存儲過程,觸發器,自定義函數,視圖(1,模塊化編程,2提高數據庫的訪問速度)
⑥優化my.ini文件(調整mysql的各級緩存.)
⑦升級mysql硬件和軟件 , 操作系統64, mysql就使用64
未完待續...
我們的表要滿足1NF基礎上,才可以談滿足2NF,目前最高級6NF,對PHP網站說,我們只要滿足3NF。
比如mysql, oracle, sql server ,postgresql,informix,DB2
面向對象和集合數據庫.
mongodb數據庫面向文檔
所謂1NF:屬性(列)具有原子性,不可在分割,還有就是把同一張表不可以有兩個相同列.
所謂2NF:說表的記錄具有唯一性.,即不能出現完全相同的兩條記錄.一般說,通過設置主鍵即可.
注意;主鍵最好是非業務邏輯主鍵,使用自增長.
3NF要保證數據沒有冗余.即如果數據可能通過顯示或者隱式的推導出,就不要單獨設計一列.
比如下圖就是滿足3NF:
但是說明; 有時我們設計表的時候,可能會使用反3NF.,舉例:
上面的相冊表的設計就使用到了反3NF,但是他提高了效率
使用 showstatus 可以參考到mysql的各個參數,我們需要掌握的是以下參數, 其它請參考手冊.
比如 com_select com_update com_insert com_delete , 比如我們在選擇表究竟時候用MyISAM 還是 InnoDB ,可以看看該網站是以讀和寫操作為主,則可以使用MyISAM.
這里注意當我們使用 showstatus 來查詢參數時,默認是當前會話.
show session status like xxxx
如果你要查看從數據庫啟動到現在狀態
show global status like xxx
show status like ‘connections’ 可以查看當前連接的數量.
表示數據庫啟動時間
show status like ‘slow_queries’
顯示慢查詢次數, 默認情況下mysql認為慢查詢時間是10s
我們使用兩個方法,蠕蟲復制,可以構建大表,但是測試效果不好.
使用存儲過程來創建海量表
模擬一個雇員管理系統
#模擬一個雇員管理系統
CREATE TABLE dept( /*部門表*/
deptno MEDIUMINT UNSIGNED NOT NULL DEFAULT 0,
dname VARCHAR(20) NOT NULL DEFAULT "",
loc VARCHAR(13) NOT NULL DEFAULT ""
) ENGINE=MyISAM DEFAULT CHARSET=utf8 ;
#創建表EMP雇員
CREATE TABLE emp
(empno MEDIUMINT UNSIGNED NOT NULL DEFAULT 0, /*編號*/
ename VARCHAR(20) NOT NULL DEFAULT "", /*名字*/
job VARCHAR(9) NOT NULL DEFAULT "",/*工作*/
mgr MEDIUMINT UNSIGNED NOT NULL DEFAULT 0,/*上級編號*/
hiredate DATE NOT NULL,/*入職時間*/
sal DECIMAL(7,2) NOT NULL,/*薪水*/
comm DECIMAL(7,2) NOT NULL,/*紅利*/
deptno MEDIUMINT UNSIGNED NOT NULLDEFAULT 0 /*部門編號*/
)ENGINE=MyISAM DEFAULT CHARSET=utf8 ;
CREATE TABLE salgrade
(
grade MEDIUMINT UNSIGNED NOT NULL DEFAULT 0,
losal DECIMAL(17,2) NOT NULL,
hisal DECIMAL(17,2) NOT NULL
)ENGINE=MyISAM DEFAULT CHARSET=utf8;
#測試數據
INSERT INTO salgrade VALUES (1,700,1200);
INSERT INTO salgrade VALUES (2,1201,1400);
INSERT INTO salgrade VALUES (3,1401,2000);
INSERT INTO salgrade VALUES (4,2001,3000);
INSERT INTO salgrade VALUES (5,3001,9999);
delimiter $$
#創建一個函數,可以返回一個隨機的字符串
create function rand_string(n INT)
returns varchar(255) #該函數會返回一個字符串
begin
#定義了一個變量 chars_str,類型 varchar(100)
#默認給 chars_str 初始值 'abcdefghijklmnopqrstuvwxyzABCDEFJHIJKLMNOPQRSTUVWXYZ'
declare chars_str varchar(100)default
'abcdefghijklmnopqrstuvwxyzABCDEFJHIJKLMNOPQRSTUVWXYZ';
declare return_str varchar(255)default '';
declare i int default 0;
while i < n do
set return_str=concat(return_str,substring(chars_str,floor(1+rand()*52),1));
set i = i + 1;
end while;
return return_str;
end $$
#這里我們又自定了一個函數,返回一個隨機的部門號
create function rand_num( )
returns int(5)
begin
declare i int default 0;
set i = floor(10+rand()*500);
return i;
end $$
#隨即添加雇員[光標] 400w,Mysql開發中,可以在存儲過程中調用你自己
#編寫的函數
create procedure insert_emp(in start int(10),in max_num int(10))
begin
declare i int default 0;
#set autocommit =0 把autocommit設置成0
#autocommit = 0 含義: 不要自動提交
set autocommit = 0;
repeat
set i = i + 1;
insert into emp values ((start+i),rand_string(6),'SALESMAN',0001,curdate(),2000,400,rand_num());
until i = max_num
end repeat;
commit;
end $$
調用存儲過程添加400w數據
call insert_emp(100001,4000000);
步驟
①在默認情況下,mysql是不會記錄慢查詢, 所以我們要使用另外一種方式啟動mysql,指令: cmd>bin\mysqld.exe � safe-mode �slow-query-log
這樣就會在mysql 的 data目錄,生成一個日志文件,該文件可以把慢查詢語句記錄到文件.
②為了測試我們修改默認的慢查詢時間
show variables like ‘long_query_time’ 【查詢當前慢查詢時間】
set long_query_time=1
③當執行一個時間超過1秒的sql語句,就會被記錄下來.
# Query_time: 1.500000 Lock_time: 0.000000 Rows_sent: 1 Rows_examined: 4000000
use temp100;
SET timestamp=1371870578;
select * from emp whereempno=456784;
④分析慢查詢時如何導致explain 工具
基本語法: explain sql \G
可以來分析mysql是如何執行你的sql語句
細致的說明請參考優化.ppt
⑤解決問題: 我們發現目前這個語句沒有使用到索引,因此我們先考慮使用索引解決.
創建普通索引: CREATE INDEX 索引名 ON 表名(列)
⑥看看此時速度怎樣
圖:
①當一個表的存儲引擎是MyISAM 時,對應三個文件
表名.frm 【表的結構】
表名.myd 【表的數據】
表名.myi 【索引的數據】
未完待續...
總結
以上是生活随笔為你收集整理的mysql数据库优化韩顺平_韩顺平 Mysql数据库优化(一) 优化概述的全部內容,希望文章能夠幫你解決所遇到的問題。
- 上一篇: mysql慢查询单位_MySQL慢查询
- 下一篇: php mysql 安装错误_Apach