数据库调优及性能问题预防

  • 更新时间:
  • 3242人关注
  • 点击下载

这是一个不错的数据库调优类学习资源,由益鹤轩提供,主要知识点是关于数据库调优、数据库性能、数据库调优的内容,已被193人关注,同类资源中评分为8.2分。

资源详情相关推荐
  • 大小:1.6 MB
  • 类别:数据库调优
  • 格式:PDF
  • 编辑:惠作人
  • 热度:319
  • 数据库系统原理与设计
  • Oracle数据库性能优化方法论和最佳实践
  • 现代数据库管理
  • NoSQL数据库技术实战
  • 数据库系统概念(第5版)
  • 精选笔记:Mysql数据库性能优化一

    6小时44分钟前回答

    今天,数据库的操作越来越成为整个应用的性能瓶颈了,这点对于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.确定问题并采取相应的优化措施

    -- 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:执行情况的描述和说明

    Mysql数据库性能优化一

    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数据库性能优化一 ,下篇文章继续给大家介绍mysql数据库性能优化二,希望大家持续关注本站最新内容!

    展开阅读

    相关资源

    • 数据库技术与应用

      数据库技术与应用

      高等学校中的计算机基础教育是面向所有大学生的计算机教育,在人才培养中起到十分重要的作用,各高校都很重视它的教学研究与实践。通过多年来的共同努力,许多老师对如何开设大学计算机基础和高级语言程序设计课程已渐成共识,但对它们的后续课程计算机软件技术却见仁见智,尚无定论。 华东理工大学从20世纪90年代以来,对计算机软件技术课程的认识与实践,经历了3次改革。第一次改革始于1995年,随着当时PC的普及和网络应用的推广,史济

      大小:698 KB数据库

      立即下载
    • 数据库云平台理论与实践

      数据库云平台理论与实践

      数据库云平台是现如今甚至将来一段時期信息化管理行业广泛关心的一个网络热点行业。云技术的实质是分布式计算,而数据库云平台表明的更是分布式计算在数据处理方法行业的实质难题。

      大小:526 MB数据库

      立即下载
    • 数据库系统概念(第5版)

      数据库系统概念(第5版)

      目录 第1章 导论 1.1 数据库系统应用 1.2 数据库系统的用途 1.3 数据视图 1.4 数据库语言 1.5 关系数据库 1.6 数据库设计 1.7 面向对象和半结构化数据库 1.8 数据存储与查询 1.9 事务管理 1.10 数据挖掘与分析 1.11 数据库体系结构 1.12 数据库用户和管理员 1.13 数据库系统的历史 1.14 小结 练习 文献注解 第1部分 关系数据库 第2章 关系模型 2.1 关系数据库的结构 2.2 基本的关系代数运算 2.3 附加的关系代数运算 2.4 扩展的关系代数

      大小:42.4 MB数据库系统

      立即下载
    • XML数据库技术

      XML数据库技术

      随着大量XML数据的出现,如何有效地存储、管理和查询这些XML数据已成为一个值得研究的重要课题。目前,XMI-数据库技术是数据库领域的研究热点。 XML数据库技术 是在作者博士论文的基础上

      大小:9.79 MBXML

      立即下载
    • Oracle云数据库方案

      Oracle云数据库方案

      Oracle云数据库方案 内容节选 一、 解决方案概览 现在中国移动已经建立了自己基于Openstack标准的底层IaaS云架构,已经可以通过这种架构满足自身的企业级私有云架构需求同时为多种行业的企业级用户提供标准的软件基础架构。但是一直以来都没有一个完整的方案可以为自己和企业级客户提供标准的PaaS服务,主要包括数据库服务和中间件服务。并且希望这种方案是对现有中国移动IaaS架构的一种扩展而不是重新开发部署一套新的系统,并且要求新增加的功

      大小:424 KBOracle

      立即下载
    • Oracle数据库日常维护手册

      Oracle数据库日常维护手册

      Oracle 数据库日常维护手册主要介绍oracle数据库方便的一些日常维护需要检查与注意地方,需要的朋友可以参考一下。 目 录 1.检查数据库基本状况 1.1.检查Oracle实例状态 1.2.检查Oracle服务进程 1.3.检查Oracle监听状态 2.检查系统和oracle日志文件 2.1.检查操作系统日志文件 2.2.检查oracle日志文件 2.3.检查Oracle核心转储目录 2.4.检查Root用户和Oracle用户的email 3.检查Oracle对象状态 3.1.检查Oracle控制文

      大小:491 KBOracle

      立即下载

    学习笔记

    23小时38分钟前回答

    解析MySQL数据库性能优化的六大技巧

    数据库表表面上存在索引和防错机制,然而一个简单的查询就会耗费很长时间。Web应用程序或许在开发环境中运行良好,但在产品环境中表现同样糟糕。如果你是个数据库管理员,你很有可能已经在某个阶段遇到上述情况。因此,本文将介绍对MySQL进行性能优化的技巧和窍门。 1.存储引擎的选择 如果数据表需要事务处理,应该考虑使用InnoDB,因为它完全符合ACID特性。如果不需要事务处理,使用默认存储引擎MyISAM是比较明智的。并且不要尝试同时使用这两个存储引擎。思考一下:在一个事务处理中,一些数据表使用InnoDB,而其余的使用MyISAM。结果呢?整个subject将被取消,只有那些在事务处理中的被带回到……

    20小时34分钟前回答

    Mysql数据库性能优化二

    在上篇文章给大家介绍了mysql数据库性能优化一,今天继续接着上篇文章给大家介绍数据库性能优化相关知识。具体内容如下所示: 建立适当的索引 说起提高数据库性能,索引是最物美价廉的东西了。不用加内存,不用改程序,不用调sql,只要执行个正确的'create index',查询速度就可能提高百倍千倍,这可真有诱惑力。可是天下没有免费的午餐,查询速度的提高是以插入、更新、删除的速度为代价的,这些写操作,增加了大量的I/O。 是不是建立一个索引就能解决所有的问题?ename上没有建立索引会怎样? select * from emp where ename='研发部'; ---测试案例命令如下 (最好以 select * from emp e,dept d where e.empno=123451 )……