搜尋
首頁資料庫mysql教程Mysql数据库性能优化一_MySQL

今天,数据库的操作越来越成为整个应用的性能瓶颈了,这点对于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),&#39;SALESMAN&#39;,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 &#39;long_query_time&#39;;
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,&#39;研发部&#39;,&#39;康和盛大厦5楼501&#39;);
INSERT into dept(deptno,dname,loc) values(2,&#39;产品部&#39;,&#39;康和盛大厦5楼502&#39;);
INSERT into dept(deptno,dname,loc) values(3,&#39;财务部&#39;,&#39;康和盛大厦5楼503&#39;);UPDATE emp set deptno=1 where 
empno=100002;

****测试语句***[对emp表的记录可以为3600000 ,效果很明显慢]

select * from emp where empno=(select empno from emp where ename=&#39;研发部&#39;)

如果带上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中文网(www.php.cn)!

陳述
本文內容由網友自願投稿,版權歸原作者所有。本站不承擔相應的法律責任。如發現涉嫌抄襲或侵權的內容,請聯絡admin@php.cn
您可以使用哪些工具來監視MySQL性能?您可以使用哪些工具來監視MySQL性能?Apr 23, 2025 am 12:21 AM

如何有效監控MySQL性能?使用mysqladmin、SHOWGLOBALSTATUS、PerconaMonitoringandManagement(PMM)和MySQLEnterpriseMonitor等工具。 1.使用mysqladmin查看連接數。 2.用SHOWGLOBALSTATUS查看查詢數。 3.PMM提供詳細性能數據和圖形化界面。 4.MySQLEnterpriseMonitor提供豐富的監控功能和報警機制。

MySQL與SQL Server有何不同?MySQL與SQL Server有何不同?Apr 23, 2025 am 12:20 AM

MySQL和SQLServer的区别在于:1)MySQL是开源的,适用于Web和嵌入式系统,2)SQLServer是微软的商业产品,适用于企业级应用。两者在存储引擎、性能优化和应用场景上有显著差异,选择时需考虑项目规模和未来扩展性。

在哪些情況下,您可以選擇SQL Server而不是MySQL?在哪些情況下,您可以選擇SQL Server而不是MySQL?Apr 23, 2025 am 12:20 AM

在需要高可用性、高級安全性和良好集成性的企業級應用場景下,應選擇SQLServer而不是MySQL。 1)SQLServer提供企業級功能,如高可用性和高級安全性。 2)它與微軟生態系統如VisualStudio和PowerBI緊密集成。 3)SQLServer在性能優化方面表現出色,支持內存優化表和列存儲索引。

MySQL如何處理角色集和碰撞?MySQL如何處理角色集和碰撞?Apr 23, 2025 am 12:19 AM

mySqlManagesCharacterSetsetSandCollat​​ionsyutusututf-8asthEdeFault,允許ConfigurationAtdataBase,table和columnlevels,AndrequiringCarefullageLignmentToavoidMismatches.1)setDefeaultCharactersetTercharactersetEtCollacterSeteTandColletationForAdataBase.2)conformentcollecharactersettersetertersetcollat​​ertersetcollat​​ioncollat​​ion

MySQL中有什麼觸發器?MySQL中有什麼觸發器?Apr 23, 2025 am 12:11 AM

MySQL觸發器是與表相關聯的自動執行的存儲過程,用於在特定數據操作時執行一系列操作。 1)觸發器定義與作用:用於數據校驗、日誌記錄等。 2)工作原理:分為BEFORE和AFTER,支持行級觸發。 3)使用示例:可用於記錄薪資變更或更新庫存。 4)調試技巧:使用SHOWTRIGGERS和SHOWCREATETRIGGER命令。 5)性能優化:避免複雜操作,使用索引,管理事務。

您如何在MySQL中創建和管理用戶帳戶?您如何在MySQL中創建和管理用戶帳戶?Apr 22, 2025 pm 06:05 PM

在MySQL中創建和管理用戶賬戶的步驟如下:1.創建用戶:使用CREATEUSER'newuser'@'localhost'IDENTIFIEDBY'password';2.分配權限:使用GRANTSELECT,INSERT,UPDATEONmydatabase.TO'newuser'@'localhost';3.修正權限錯誤:使用REVOKEALLPRIVILEGESONmydatabase.FROM'newuser'@'localhost';然後重新分配權限;4.優化權限:使用SHOWGRA

MySQL與Oracle有何不同?MySQL與Oracle有何不同?Apr 22, 2025 pm 05:57 PM

MySQL適合快速開發和中小型應用,Oracle適合大型企業和高可用性需求。 1)MySQL開源、易用,適用於Web應用和中小型企業。 2)Oracle功能強大,適合大型企業和政府機構。 3)MySQL支持多種存儲引擎,Oracle提供豐富的企業級功能。

與其他關係數據庫相比,使用MySQL的缺點是什麼?與其他關係數據庫相比,使用MySQL的缺點是什麼?Apr 22, 2025 pm 05:49 PM

MySQL相比其他關係型數據庫的劣勢包括:1.性能問題:在處理大規模數據時可能遇到瓶頸,PostgreSQL在復雜查詢和大數據處理上表現更優。 2.擴展性:水平擴展能力不如GoogleSpanner和AmazonAurora。 3.功能限制:在高級功能上不如PostgreSQL和Oracle,某些功能需要更多自定義代碼和維護。

See all articles

熱AI工具

Undresser.AI Undress

Undresser.AI Undress

人工智慧驅動的應用程序,用於創建逼真的裸體照片

AI Clothes Remover

AI Clothes Remover

用於從照片中去除衣服的線上人工智慧工具。

Undress AI Tool

Undress AI Tool

免費脫衣圖片

Clothoff.io

Clothoff.io

AI脫衣器

Video Face Swap

Video Face Swap

使用我們完全免費的人工智慧換臉工具,輕鬆在任何影片中換臉!

熱工具

禪工作室 13.0.1

禪工作室 13.0.1

強大的PHP整合開發環境

DVWA

DVWA

Damn Vulnerable Web App (DVWA) 是一個PHP/MySQL的Web應用程序,非常容易受到攻擊。它的主要目標是成為安全專業人員在合法環境中測試自己的技能和工具的輔助工具,幫助Web開發人員更好地理解保護網路應用程式的過程,並幫助教師/學生在課堂環境中教授/學習Web應用程式安全性。 DVWA的目標是透過簡單直接的介面練習一些最常見的Web漏洞,難度各不相同。請注意,該軟體中

mPDF

mPDF

mPDF是一個PHP庫,可以從UTF-8編碼的HTML產生PDF檔案。原作者Ian Back編寫mPDF以從他的網站上「即時」輸出PDF文件,並處理不同的語言。與原始腳本如HTML2FPDF相比,它的速度較慢,並且在使用Unicode字體時產生的檔案較大,但支援CSS樣式等,並進行了大量增強。支援幾乎所有語言,包括RTL(阿拉伯語和希伯來語)和CJK(中日韓)。支援嵌套的區塊級元素(如P、DIV),

SublimeText3 英文版

SublimeText3 英文版

推薦:為Win版本,支援程式碼提示!

SublimeText3漢化版

SublimeText3漢化版

中文版,非常好用