Mysql數(shù)據(jù)庫優(yōu)化性能你知道嗎？

今天，數(shù)據(jù)庫的操作越來越成為整個應用的性能瓶頸了，這點對于Web應用尤其明顯。關(guān)于數(shù)據(jù)庫的性能，這并不只是DBA才需要擔心的事，而這更是我們程序員需要去關(guān)注的事情。當我們?nèi)?a target="_blank">設(shè)計數(shù)據(jù)庫表結(jié)構(gòu)，對操作數(shù)據(jù)庫時（尤其是查表時的SQL語句），我們都需要注意數(shù)據(jù)操作的性能。這里，我們不會講過多的SQL語句的優(yōu)化，而只是針對MySQL這一Web應用最多的數(shù)據(jù)庫。

mysql的性能優(yōu)化無法一蹴而就，必須一步一步慢慢來，從各個方面進行優(yōu)化，最終性能就會有大的提升。

Mysql數(shù)據(jù)庫的優(yōu)化技術(shù)

對mysql優(yōu)化是一個綜合性的技術(shù)，主要包括

?表的設(shè)計合理化(符合3NF)

?添加適當索引(index) [四種: 普通索引、主鍵索引、唯一索引unique、全文索引]

?分表技術(shù)(水平分割、垂直分割)

?讀寫[寫: update/delete/add]分離

?存儲過程 [模塊化編程，可以提高速度]

?對mysql配置優(yōu)化 [配置最大并發(fā)數(shù)my.ini, 調(diào)整緩存大小 ]

?mysql服務(wù)器硬件升級

?定時的去清除不需要的數(shù)據(jù),定時進行碎片整理(MyISAM)

數(shù)據(jù)庫優(yōu)化工作

對于一個以數(shù)據(jù)為中心的應用，數(shù)據(jù)庫的好壞直接影響到程序的性能，因此數(shù)據(jù)庫性能至關(guān)重要。一般來說，要保證數(shù)據(jù)庫的效率，要做好以下四個方面的工作：

① 數(shù)據(jù)庫設(shè)計

② sql語句優(yōu)化

③ 數(shù)據(jù)庫參數(shù)配置

④ 恰當?shù)挠布�資源和操作系統(tǒng)

此外，使用適當?shù)拇鎯�過程，也能提升性能。

這個順序也表現(xiàn)了這四個工作對性能影響的大小

數(shù)據(jù)庫表設(shè)計

通俗地理解三個范式，對于數(shù)據(jù)庫設(shè)計大有好處。在數(shù)據(jù)庫設(shè)計中，為了更好地應用三個范式，就必須通俗地理解三個范式(通

俗地理解是夠用的理解，并不是最科學最準確的理解)：

第一范式：1NF是對屬性的原子性約束，要求屬性(列)具有原子性，不可再分解；(只要是關(guān)系型數(shù)據(jù)庫都滿足1NF)

第二范式：2NF是對記錄的惟一性約束，要求記錄有惟一標識，即實體的惟一性；

第三范式：3NF是對字段冗余性的約束，它要求字段沒有冗余。 沒有冗余的數(shù)據(jù)庫設(shè)計可以做到。

但是，沒有冗余的數(shù)據(jù)庫未必是最好的數(shù)據(jù)庫，有時為了提高運行效率，就必須降低范式標準，適當保留冗余數(shù)據(jù)。具體做法是： 在概念數(shù)據(jù)模型設(shè)計時遵守第三范式，降低范式標準的工作放到物理數(shù)據(jù)模型設(shè)計時考慮。降低范式就是增加字段，允許冗余。

? 數(shù)據(jù)庫的分類

關(guān)系型數(shù)據(jù)庫: mysql/oracle/db2/informix/sysbase/sql server

非關(guān)系型數(shù)據(jù)庫: (特點: 面向?qū)ο?/a>或者集合)

NoSql數(shù)據(jù)庫: MongoDB(特點是面向文檔)

舉例說明什么是適度冗余，或者說有理由的冗余!

上面這個就是不合適的冗余，原因是:

在這里，為了提高學生活動記錄的檢索效率，把單位名稱冗余到學生活動記錄表里。單位信息有500條記錄，而學生活動記錄在

一年內(nèi)大概有200萬數(shù)據(jù)量。 如果學生活動記錄表不冗余這個單位名稱字段，只包含三個int字段和一個timestamp字段，只占用了16字節(jié)，是一個很小的表。而冗余了一個 varchar(32)的字段后則是原來的3倍，檢索起來相應也多了這么多的I/O。而且記錄數(shù)相差懸殊，500 VS 2000000 ，導致更新一個單位名稱還要更新4000條冗余記錄。由此可見，這個冗余根本就是適得其反。

訂單表里面的Price就是一個冗余字段，因為我們可以從訂單明細表中統(tǒng)計出這個訂單的價格，但是這個冗余是合理的，也能提升查詢性能。

從上面兩個例子中可以得出一個結(jié)論:

1---n 冗余應當發(fā)生在1這一方.

SQL語句優(yōu)化

SQL優(yōu)化的一般步驟

1.通過show status命令了解各種SQL的執(zhí)行頻率。

2.定位執(zhí)行效率較低的SQL語句-（重點select）

3.通過explain分析低效率的SQL

4.確定問題并采取相應的優(yōu)化措施

-- select語句分類
Select
Dml數(shù)據(jù)操作語言(insert update delete)
dtl 數(shù)據(jù)事物語言(commit rollback savepoint)
Ddl數(shù)據(jù)定義語言(create alter drop..)
Dcl(數(shù)據(jù)控制語言) grant revoke
-- Show status 常用命令
--查詢本次會話
Show session status like 'com_%'; //show session status like 'Com_select'
--查詢?nèi)��?
Show global status like 'com_%';
-- 給某個用戶授權(quán)
grant all privileges on *.* to 'abc'@'%';
--為什么這樣授權(quán) 'abc'?表示用戶名 '@' 表示host, 查看一下mysql->user表就知道了
--回收權(quán)限
revoke all on *.* from 'abc'@'%';
--刷新權(quán)限[也可以不寫]
flush privileges;

SQL語句優(yōu)化-show參數(shù)

MySQL客戶端連接成功后，通過使用show [session global] status 命令可以提供服務(wù)器狀態(tài)信息。其中的session來表示當前的連接的統(tǒng)計結(jié)果，global來表示自數(shù)據(jù)庫上次啟動至今的統(tǒng)計結(jié)果。默認是session級別的。

下面的例子：

show status like 'Com_%';

其中Com_XXX表示XXX語句所執(zhí)行的次數(shù)。

重點注意：Com_select,Com_insert,Com_update,Com_delete通過這幾個參數(shù)，可以容易地了解到當前數(shù)據(jù)庫的應用是以插入更新為主還是以查詢操作為主，以及各類的SQL大致的執(zhí)行比例是多少。

還有幾個常用的參數(shù)便于用戶了解數(shù)據(jù)庫的基本情況。

Connections：試圖連接MySQL服務(wù)器的次數(shù)

Uptime：服務(wù)器工作的時間（單位秒）

Slow_queries：慢查詢的次數(shù) (默認是慢查詢時間10s)

show status like 'Connections'
show status like 'Uptime'
show status like 'Slow_queries'

如何查詢mysql的慢查詢時間

Show variables like 'long_query_time';

修改mysql 慢查詢時間

set long_query_time=2

SQL語句優(yōu)化-定位慢查詢

問題是： 如何從一個大項目中，迅速的定位執(zhí)行速度慢的語句. (定位慢查詢)

首先我們了解mysql數(shù)據(jù)庫的一些運行狀態(tài)如何查詢(比如想知道當前mysql運行的時間/一共執(zhí)行了多少次

select/update/delete.. / 當前連接)

為了便于測試，我們構(gòu)建一個大表(400 萬)-> 使用存儲過程構(gòu)建

默認情況下，mysql認為10秒才是一個慢查詢.

修改mysql的慢查詢.

show variables like 'long_query_time' ; //可以顯示當前慢查詢時間
set long_query_time=1 ;//可以修改慢查詢時間

構(gòu)建大表->大表中記錄有要求, 記錄是不同才有用，否則測試效果和真實的相差大.創(chuàng)建:

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 ;

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 NULL DEFAULT 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;

測試數(shù)據(jù)

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);

為了存儲過程能夠正常執(zhí)行，我們需要把命令執(zhí)行結(jié)束符修改delimiter $$
創(chuàng)建函數(shù)，該函數(shù)會返回一個指定長度的隨機字符串

create function rand_string(n INT) 
returns varchar(255) #該函數(shù)會返回一個字符串
begin 
#chars_str定義一個變量 chars_str,類型是 varchar(100),默認值'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

創(chuàng)建一個存儲過程

create procedure insert_emp(in start int(10),in max_num int(10))
begin
declare i int default 0; 
#set autocommit =0 把autocommit設(shè)置成0
set autocommit = 0; 
repeat
set i = i + 1;
insert into emp values ((start+i) ,rand_string(6),'SALESMAN',0001,curdate(),2000,400,rand());
until i = max_num
end repeat;
commit;
end 
#調(diào)用剛剛寫好的函數(shù), 1800000條記錄,從100001號開始
call insert_emp(100001,4000000);

這時我們?nèi)绻�出現(xiàn)一條語句執(zhí)行時間超過1秒中，就會統(tǒng)計到.

如果把慢查詢的sql記錄到我們的一個日志中

在默認情況下，低版本的mysql不會記錄慢查詢，需要在啟動mysql時候，指定記錄慢查詢才可以

bin\mysqld.exe - -safe-mode - -slow-query-log [mysql5.5 可以在my.ini指定]

bin\mysqld.exe –log-slow-queries=d:/abc.log [低版本mysql5.0可以在my.ini指定]

該慢查詢?nèi)罩緯�放在data目錄下[在mysql5.0這個版本中時放在 mysql安裝目錄/data/下],在 mysql5.5.19下是需要查看

my.ini 的 datadir="C:/Documents and Settings/All Users/Application Data/MySQL/MySQL Server 5.5/Data/“來確定.

在mysql5.6中，默認是啟動記錄慢查詢的，my.ini的所在目錄為：C:\ProgramData\MySQL\MySQL Server 5.6，其中有一個配置項

slow-query-log=1

針對 mysql5.5啟動慢查詢有兩種方法

bin\mysqld.exe - -safe-mode - -slow-query-log

也可以在my.ini 文件中配置:

[mysqld]
# The TCP/IP Port the MySQL Server will listen on
port=3306
slow-query-log

通過慢查詢?nèi)罩径ㄎ粓?zhí)行效率較低的SQL語句。慢查詢?nèi)罩居涗浟怂�有�?zhí)行時間超過long_query_time所設(shè)置的SQL語句。

show variables like 'long_query_time';
set long_query_time=2;

為dept表添加數(shù)據(jù)

desc dept;
ALTER table dept add id int PRIMARY key auto_increment;
CREATE PRIMARY KEY on dept(id);
create INDEX idx_dptno_dptname on dept(deptno,dname);
INSERT into dept(deptno,dname,loc) values(1,'研發(fā)部','康和盛大廈5樓501');
INSERT into dept(deptno,dname,loc) values(2,'產(chǎn)品部','康和盛大廈5樓502');
INSERT into dept(deptno,dname,loc) values(3,'財務(wù)部','康和盛大廈5樓503');UPDATE emp set deptno=1 where empno=100002;

****測試語句***[對emp表的記錄可以為3600000 ,效果很明顯慢]

select * from emp where empno=(select empno from emp where ename='研發(fā)部')

如果帶上order by e.empno 速度就會更慢，有時會到1min多.

測試語句

select * from emp e,dept d where e.empno=100002 and e.deptno=d.deptno;

查看慢查詢?nèi)罩荆耗�認為數(shù)據(jù)目錄data中的host-name-slow.log。低版本的mysql需要通過在開啟mysql時使用- -log-slow-queries[=file_name]來配置

SQL語句優(yōu)化-explain分析問題

Explain select * from emp where ename=“wsrcla”

會產(chǎn)生如下信息：

select_type:表示查詢的類型。

table:輸出結(jié)果集的表

type:表示表的連接類型

possible_keys:表示查詢時，可能使用的索引

key:表示實際使用的索引

key_len:索引字段的長度

rows:掃描出的行數(shù)(估算的行數(shù))

Extra:執(zhí)行情況的描述和說明

explain select * from emp where ename='JKLOIP'

如果要測試Extra的filesort可以對上面的語句修改

explain select * from emp order by ename\G

EXPLAIN詳解

id

SELECT識別符。這是SELECT的查詢序列號

id 示例

SELECT * FROM emp WHERE empno = 1 and ename = (SELECT ename FROM emp WHERE empno = 100001) \G;

select_type

PRIMARY :子查詢中最外層查詢

SUBQUERY : 子查詢內(nèi)層第一個SELECT，結(jié)果不依賴于外部查詢

DEPENDENT SUBQUERY:子查詢內(nèi)層第一個SELECT，依賴于外部查詢

UNION :UNION語句中第二個SELECT開始后面所有SELECT，

SIMPLE

UNION RESULT UNION 中合并結(jié)果

Table

顯示這一步所訪問數(shù)據(jù)庫中表名稱

Type

對表訪問方式

ALL：

SELECT * FROM emp \G

完整的表掃描 通常不好

SELECT * FROM (SELECT * FROM emp WHERE empno = 1) a ;

system：表僅有一行(=系統(tǒng)表)。這是const聯(lián)接類型的一個特

const：表最多有一個匹配行

Possible_keys

該查詢可以利用的索引，如果沒有任何索引顯示 null

Key

Mysql 從 Possible_keys 所選擇使用索引

Rows

估算出結(jié)果集行數(shù)

Extra

查詢細節(jié)信息

No tables ：Query語句中使用FROM DUAL 或不含任何FROM子句

Using filesort ：當Query中包含 ORDER BY 操作，而且無法利用索引完成排序，

Impossible WHERE noticed after reading const tables: MYSQL Query Optimizer

通過收集統(tǒng)計信息不可能存在結(jié)果

Using temporary：某些操作必須使用臨時表，常見 GROUP BY ; ORDER BY

Using where：不用讀取表中所有信息，僅通過索引就可以獲取所需數(shù)據(jù);

以上就是Mysql數(shù)據(jù)庫性能優(yōu)化你知道嗎？的詳細內(nèi)容，更多請關(guān)注php中文網(wǎng)其它相關(guān)文章！