Mysql数据库性能优化(一)

时间:2022-09-20 00:01:47

参考 http://www.jb51.net/article/82254.htm

今天,数据库的操作越来越成为整个应用的性能瓶颈了,这点对于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(特点是面向文档)

举例说明什么是适度冗余,或者说有理由的冗余!

Mysql数据库性能优化(一)

上面这个就是不合适的冗余,原因是:

在这里,为了提高学生活动记录的检索效率,把单位名称冗余到学生活动记录表里。单位信息有500条记录,而学生活动记录在

一年内大概有200万数据量。 如果学生活动记录表不冗余这个单位名称字段,只包含三个int字段和一个timestamp字段,只占用了16字节,是一个很小的表。而冗余了一个 varchar(32)的字段后则是原来的3倍,检索起来相应也多了这么多的I/O。而且记录数相差悬殊,500 VS 2000000 ,导致更新一个单位名称还要更新4000条冗余记录。由此可见,这个冗余根本就是适得其反。

Mysql数据库性能优化(一)

订单表里面的Price就是一个冗余字段,因为我们可以从订单明细表中统计出这个订单的价格,但是这个冗余是合理的,也能提升查询性能。

从上面两个例子中可以得出一个结论:

1---n 冗余应当发生在1这一方.

SQL语句优化 

SQL优化的一般步骤

1.通过show status命令了解各种SQL的执行频率。

2.定位执行效率较低的SQL语句-(重点select)

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

4.确定问题并采取相应的优化措施

 
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
-- 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)

1
2
3
show status like 'Connections'
show status like 'Uptime'
show status like 'Slow_queries'

如何查询mysql的慢查询时间

 
1
Show variables like 'long_query_time';

修改mysql 慢查询时间

 
1
set long_query_time=2

SQL语句优化-定位慢查询 

问题是: 如何从一个大项目中,迅速的定位执行速度慢的语句. (定位慢查询)

首先我们了解mysql数据库的一些运行状态如何查询(比如想知道当前mysql运行的时间/一共执行了多少次

select/update/delete.. / 当前连接)

为了便于测试,我们构建一个大表(400 万)-> 使用存储过程构建

默认情况下,mysql认为10秒才是一个慢查询.

修改mysql的慢查询.

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

构建大表->大表中记录有要求, 记录是不同才有用,否则测试效果和真实的相差大.创建:

 
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
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;

测试数据

 
1
2
3
4
5
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 $$
创建函数,该函数会返回一个指定长度的随机字符串

1
2
3
4
5
6
7
8
9
10
11
12
13
14
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

创建一个存储过程

 
1
2
3
4
5
6
7
8
9
10
11
12
13
14
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 文件中配置:

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

通过慢查询日志定位执行效率较低的SQL语句。慢查询日志记录了所有执行时间超过long_query_time所设置的SQL语句。

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

为dept表添加数据

 
1
2
3
4
5
6
7
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 ,效果很明显慢]

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

如果带上order by e.empno 速度就会更慢,有时会到1min多.

测试语句

 
1
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分析问题 

 
1
Explain select * from emp where ename=“wsrcla”

会产生如下信息:

select_type:表示查询的类型。

table:输出结果集的表

type:表示表的连接类型

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

key:表示实际使用的索引

key_len:索引字段的长度

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

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

Mysql数据库性能优化(一)

explain select * from emp where ename='JKLOIP'

如果要测试Extra的filesort可以对上面的语句修改

 
1
explain select * from emp order by ename\G

EXPLAIN详解

id

SELECT识别符。这是SELECT的查询序列号

id 示例

 
1
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:

 
1
SELECT * FROM emp \G

完整的表扫描 通常不好

 
1
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数据库性能优化(一)的更多相关文章

  1. mysql数据库性能优化(包括SQL&comma;表结构&comma;索引&comma;缓存)

    优化目标减少 IO 次数IO永远是数据库最容易瓶颈的地方,这是由数据库的职责所决定的,大部分数据库操作中超过90%的时间都是 IO 操作所占用的,减少 IO 次数是 SQL 优化中需要第一优先考虑,当 ...

  2. MySQL 数据库性能优化之索引优化

    接着上一篇 MySQL 数据库性能优化之表结构,这是 MySQL数据库性能优化专题 系列的第三篇文章:MySQL 数据库性能优化之索引优化 大家都知道索引对于数据访问的性能有非常关键的作用,都知道索引 ...

  3. MySQL数据库性能优化专题

    摘录: 书:<MySQL性能调优与架构设计> 一个系列: (按顺序排一下) MySQL 数据库性能优化之缓存参数优化 http://isky000.com/database/mysql-p ...

  4. MySQL 数据库性能优化之缓存参数优化

    在平时被问及最多的问题就是关于 MySQL 数据库性能优化方面的问题,所以最近打算写一个MySQL数据库性能优化方面的系列文章,希望对初中级 MySQL DBA 以及其他对 MySQL 性能优化感兴趣 ...

  5. MySQL数据库性能优化:表、索引、SQL等

    一.MySQL 数据库性能优化之SQL优化 注:这篇文章是以 MySQL 为背景,很多内容同时适用于其他关系型数据库,需要有一些索引知识为基础 优化目标 减少 IO 次数IO永远是数据库最容易瓶颈的地 ...

  6. MySQL数据库性能优化与监控实战&lpar;阶段四&rpar;

    MySQL数据库性能优化与监控实战(阶段四) 作者 刘畅 时间 2020-10-20 目录 1 sys数据库 1 2 系统变量 1 3 性能优化 1 3.1 硬件层 1 3.2 系统层 1 3.3 软 ...

  7. 浅谈MySQL 数据库性能优化

    MySQL数据库是 IO 密集型的程序,和其他数据库一样,主要功能就是数据的持久化以及数据的管理工作.本文侧重通过优化MySQL 数据库缓存参数如查询缓存,表缓存,日志缓存,索引缓存,innodb缓存 ...

  8. MySQL数据库性能优化

    mysql查询优化: 1.新增字段索引,查询时若使用到or关键字,则两个字段都需建立索引才能生效 2.sql语句包含子查询,mysql会创建临时表查询结束后删除,影响效率,所以应尽可能使用join替代 ...

  9. MySQL数据库性能优化的关键参数&lpar;转&rpar;

    我们在进行数据库管理和开发中经常会遇到性能问题,这就涉及到MySQL的性能优化.通过在网络上查找资料和笔者自己的尝试,我认为以下系统参数是比较关键的: 关键参数一:back_log 要求 MySQL ...

随机推荐

  1. docker安装

    系统要求:需要一个64位的centos7操作系统和版本3.10或更高版本的Linux内核 开始安装: uname -r   //查看内核版本yum -y update //更新系统更新到最新 #安装d ...

  2. 戴文的Linux内核专题:08内核配置(5)

    转自Linux中国 Linux内核拥有许多可以配置的特性,接下来我们还有许多要配置. 下一个可以配置的特性是x86的随机数生成器(x86 architectural random number gen ...

  3. java关键字 &lpar;jdk6&rpar;&comma;各自的含义是什么?

    Abstract 抽象的 一个Java语言中的关键字,用在类的声明中来指明一个类是不能被实例化的,但是可以被其它类继承.一个抽象类可以使用抽象方法,抽象方法不需要实现,但是需要在子类中被实现. bre ...

  4. SharePoint excel service web part 连接到 filter web part

    本文讲述SharePoint excel service web part 连接到 filter web part的一个简单应用场景. SharePoint excel service web par ...

  5. 页面某些特定图标的权限,比如导入导出表格,下载等等,这个权限必须在有某个页面查看的权利的基础上(细粒度)(shiro项目中来的四)

    一,查找按钮权限的设置 第一步:会根据用户的相关信息去查到它的角色表: SELECT * FROM SYS_USER WHERE user_id='eded77bdf35347249b2bacfa18 ...

  6. DropDownList按照Gridview获取数据获取到的是定义格式

    首先需要把DropDownList改成允许服务器返回. 然后绑定的时候需要以下两项. DropDownList1.DataTextField = "name";DropDownLi ...

  7. Python应用【PDF处理-pypdf2】

    概述 Python处理PDF文件需要安装相应的库:[PyPDF2]库 使用场景 工作中可能会涉及处理pdf文件,PyPDF2就是这样一个库, 使用它可以轻松的处理 pdf 文件,它提供了读.写.分割. ...

  8. Python socket网络编程&lpar;通信介绍&rpar;

      socket通信介绍 通信介绍(一) 1.所有网络协议的基础就是:socket 2.socket对TCP与UDP协议封装,让用户进行简单操作. 3.socket只做两件事:发 send,收 rec ...

  9. 2丶利用NABCD模型进行竞争性需求分析

    确定项目:公交查询系统 分析小组:在路上 选择比努力更重要.一个项目成功自然离不开组员们的努力.但是,光努力是不够的.还需要用户有需求,能快速实现. 这些东西,看似很虚,却能让我们少走不少弯路.做项目 ...

  10. web框架--MVC、MTV

    一.MVC框架: MVC 是一种使用 MVC(Model View Controller 模型-视图-控制器)设计创建 Web 应用程序的模式:[1] Model(模型)表示应用程序核心(比如数据库记 ...