Mysql資料庫效能優化你知道嗎？

今天，資料庫的操作越來越成為整個應用的效能瓶頸了，這點對於Web應用尤其明顯。關於資料庫的效能，這不只是DBA需要擔心的事，而這更是我們程式設計師需要去關注的事情

今天，資料庫的操作越來越成為整個應用的效能瓶頸了，這點對於Web應用尤其明顯。關於資料庫的效能，這不只是DBA需要擔心的事，而這更是我們程式設計師需要去關注的事情。當我們去設計資料庫表格結構，對操作資料庫時（尤其是查表時的SQL語句），我們都需要注意資料操作的效能。這裡，我們不會講過多的SQL語句的最佳化，而只是針對MySQL這一Web應用最多的資料庫。

mysql的效能最佳化無法一蹴而就，必須一步一步慢慢來，從各個方面進行最佳化，最終效能就會有大的提升。

Mysql資料庫的最佳化技術

對mysql優化是一個綜合性的技術，主要包括

•表的設計合理化(符合3NF)

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

•分錶技術(水平分割、垂直分割)

#•讀寫[寫入: update/delete/add]分離

•預存程序 [模組化編程，可以提高速度]

#•對mysql配置最佳化[配置最大並發數my.ini, 調整快取大小]

•mysql伺服器硬體升級

•定時的去清除不需要的資料,定時進行碎片整理(MyISAM)

資料庫最佳化工作

對於一個以資料為中心的應用，資料庫的好壞直接影響到程式的效能，因此資料庫效能至關重要。一般來說，要確保資料庫的效率，要做好以下四個面向的工作：

① 資料庫設計

② sql語句最佳化

③ 資料庫參數配置

④ 恰當的硬體資源和作業系統

此外，使用適當的預存程序，也能提升效能。

這個順序也表現了這四個工作對效能影響的大小

#資料庫表設計

通俗地理解三個範式，對於資料庫設計大有好處。在資料庫設計中，為了更好地應用三個範式，就必須通俗地理解三個範式(通

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

第一範式：1NF是對屬性的原子性約束，要求屬性(列)具有原子性，不可再分解；(只要是關係型資料庫都滿足1NF)

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

第三範式：3NF是對字段冗餘性的約束，它要求字段沒有冗餘。 沒有冗餘的資料庫設計可以做到。

但是，沒有冗餘的資料庫可能不是最好的資料庫，有時為了提高運作效率，就必須降低範式標準，適當保留冗餘資料。具體做法是： 在概念資料模型設計時遵守第三範式，降低範式標準的工作放到實體資料模型設計時考慮。降低範式就是增加字段，允許冗餘。

☞ 資料庫的分類

關係型資料庫: mysql/oracle/db2/informix/sysbase/sql server

#非關係型資料庫: (特點: 物件導向或集合)

NoSql資料庫: MongoDB(特點是面向文件)

舉例說明什麼是適度冗餘，或是說有理由的冗餘!

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

在這裡，為了提高學生活動記錄的檢索效率，把單位名稱冗餘到學生活動記錄表裡。單位資訊有500筆記錄，而學生活動記錄在

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

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

從上面兩個例子可以得到一個結論:

1---n 冗餘應當發生在1這一方.

#SQL語句最佳化

#SQL最佳化的一般步驟

#1.透過show status指令了解各種SQL的執行頻率。

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

#3.透過explain分析低效率的SQL

4.確定問題並採取相應的最佳化措施

-- select语句分类
Select
Dml数据操作语言(insert update delete)
dtl 数据事物语言(commit rollback savepoint)
Ddl数据定义语言(create alter drop..)
Dcl(数据控制语言) grant revoke
-- Show status 常用命令
--查询本次会话
Show session status like 'com_%'; //show session status like 'Com_select'
--查询全局
Show global status like 'com_%';
-- 给某个用户授权
grant all privileges on *.* to 'abc'@'%';
--为什么这样授权 'abc'表示用户名 '@' 表示host, 查看一下mysql->user表就知道了
--回收权限
revoke all on *.* from 'abc'@'%';
--刷新权限[也可以不写]
flush privileges;

SQL語句最佳化-show參數

MySQL客戶端連線成功後，透過使用show [session|global] status 指令可以提供伺服器狀態資訊。其中的session來表示目前的連接的統計結果，global來表示自資料庫上次啟動至今的統計結果。預設是session等級的。

下面的範例：

show status like 'Com_%';

其中Com_XXX表示XXX語句所執行的次數。

專注於：Com_select,Com_insert,Com_update,Com_delete透過這幾個參數，可以輕易地了解到目前資料庫的應用是以插入更新為主還是以查詢操作為主，以及各類的SQL大致的執行比例是多少。

還有幾個常用的參數可以讓使用者了解資料庫的基本情況。

Connections：試圖連接MySQL伺服器的次數

Uptime：伺服器工作的時間（單位秒）

Slow_queries：慢查詢的次數(預設是慢查詢時間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語句最佳化-定位慢查詢

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

首先我們了解mysql資料庫的一些運行狀態如何查詢(例如想知道當前mysql運行的時間/總共執行了多少次

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

#為了方便測試，我們建立一個大表(400 萬)-> 使用預存程序建置

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

修改mysql的慢查詢.

show variables like 'long_query_time' ; //可以显示当前慢查询时间
set long_query_time=1 ;//可以修改慢查询时间

建立大表->大表中記錄有要求, 記錄是不同才有用，否則測試效果和真實的相差大.創建:

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;

測試資料

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定义一个变量 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

建立一個預存程序

create procedure insert_emp(in start int(10),in max_num int(10))
begin
declare i int default 0; 
#set 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());
until i = max_num
end repeat;
commit;
end 
#调用刚刚写好的函数, 1800000条记录,从100001号开始
call insert_emp(100001,4000000);

這時我們如果出現一條語句執行時間超過1秒中，就會統計到.

如果把慢查詢的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指定]

該慢查詢日誌會放在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

透過慢查詢日誌定位執行效率較低的SQL語句。慢查詢日誌記錄了所有執行時間超過long_query_time所設定的SQL語句。

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

為dept表新增資料

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,'研发部','康和盛大厦5楼501');
INSERT into dept(deptno,dname,loc) values(2,'产品部','康和盛大厦5楼502');
INSERT into dept(deptno,dname,loc) values(3,'财务部','康和盛大厦5楼503');UPDATE emp set deptno=1 where empno=100002;

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

select * from emp where empno=(select empno from emp where ename='研发部')

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

#測試語句

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

查看慢查詢日誌：預設為資料目錄data中的host-name-slow.log。低版本的mysql需要透過在開啟mysql時使用- -log-slow-queries[=file_name]來設定

SQL語句優化-explain分析問題 

Explain select * from emp where ename=“wsrcla”

會產生以下資訊：

select_type:表示查詢的類型。

table:輸出結果集的表格

type:表示表格的連線類型

possible_keys:表示查询时，可能使用的索引

key:表示实际使用的索引

key_len:索引字段的长度

rows:扫描出的行数(估算的行数)

Extra:执行情况的描述和说明

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 : 子查询内层第一个SELECT，结果不依赖于外部查询

DEPENDENT SUBQUERY:子查询内层第一个SELECT，依赖于外部查询

UNION :UNION语句中第二个SELECT开始后面所有SELECT，

SIMPLE

UNION RESULT UNION 中合并结果

Table

显示这一步所访问数据库中表名称

Type

对表访问方式

ALL：

SELECT * FROM emp \G

完整的表扫描 通常不好

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

system：表仅有一行(=系统表)。这是const联接类型的一个特

const：表最多有一个匹配行

Possible_keys

该查询可以利用的索引，如果没有任何索引显示 null

Key

Mysql 从 Possible_keys 所选择使用索引

Rows

估算出结果集行数

Extra

查询细节信息

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

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

Impossible WHERE noticed after reading const tables: MYSQL Query Optimizer

通过收集统计信息不可能存在结果

Using temporary：某些操作必须使用临时表，常见 GROUP BY ; ORDER BY

Using where：不用读取表中所有信息，仅通过索引就可以获取所需数据;

以上是Mysql資料庫效能優化你知道嗎？的詳細內容。更多資訊請關注PHP中文網其他相關文章！