Mysql入门Mysql数据库性能优化一

概述介绍《Mysql入门Mysql数据库性能优化一》开发教程，希望对您有用。

《MysqL入门MysqL数据库性能优化一》要点：
本文介绍了MysqL入门MysqL数据库性能优化一，希望对您有用。如果有疑问，可以联系我们。

本日,数据库的 *** 作越来越成为整个应用的性能瓶颈了,这点对于Web应用尤其明显.关于数据库的性能,这并不只是DBA才需要担心的事,而这更是我们程序员需要去关注的事情.当我们去设计数据库表结构,对 *** 作数据库时(尤其是查表时的SQL语句),我们都需要注意数据 *** 作的性能.这里,我们不会讲过多的SQL语句的优化,而只是针对MysqL这一Web应用最多的数据库.MysqL学习

MysqL的性能优化无法一蹴而就,必需一步一步慢慢来,从各个方面进行优化,最终性能就会有大的提升.
MysqL学习

MysqL数据库的优化技术
MysqL学习

对MysqL优化是一个综合性的技术,主要包含
MysqL学习

•表的设计合理化(符合3NF)
MysqL学习

•添加适当索引(index) [四种: 普通索引、主键索引、唯一索引unique、全文索引]
MysqL学习

•分表技术(程度分割、垂直分割)
MysqL学习

•读写[写: update/delete/add]分离
MysqL学习

•存储过程 [模块化编程,可以提高速度]
MysqL学习

•对MysqL配置优化 [配置最大并发数my.ini,调整缓存大小 ]
MysqL学习

•MysqL服务器硬件升级
MysqL学习

•定时的去清除不必要的数据,定时进行碎片整理(MyISAM)
MysqL学习

数据库优化工作
MysqL学习

对于一个以数据为中心的应用,数据库的好坏直接影响到法式的性能,因此数据库性能至关重要.一般来说,要保证数据库的效率,要做好以下四个方面的工作：
MysqL学习

① 数据库设计
MysqL学习

② SQL语句优化
MysqL学习

③ 数据库参数配置
MysqL学习

④ 恰当的硬件资源和 *** 作系统
MysqL学习

此外,使用适当的存储过程,也能提升性能.
MysqL学习

这个顺序也表示了这四个工作对性能影响的大小
MysqL学习

数据库表设计
MysqL学习

通俗地理解三个范式,对于数据库设计大有好处.在数据库设计中,为了更好地应用三个范式,就必需通俗地理解三个范式(通MysqL学习

俗地理解是够用的理解,并不是最科学最准确的理解)：
MysqL学习

第一范式：1NF是对属性的原子性约束,要求属性(列)具有原子性,弗成再分解；(只要是关系型数据库都满足1NF)
MysqL学习

第二范式：2NF是对记录的惟一性约束,要求记录有惟一标识,即实体的惟一性；
MysqL学习

第三范式：3NF是对字段冗余性的约束,它要求字段没有冗余. 没有冗余的数据库设计可以做到.
MysqL学习

但是,没有冗余的数据库未必是最好的数据库,有时为了提高运行效率,就必须降低范式标准,适当保存冗余数据.具体做法是： 在概念数据模型设计时遵守第三范式,降低范式标准的工作放到物理数据模型设计时考虑.降低范式就是增加字段,允许冗余.
MysqL学习

☞ 数据库的分类
MysqL学习

关系型数据库: MysqL/oracle/db2/informix/sysbase/sql server
MysqL学习

非关系型数据库: (特点: 面向对象或者集合)
MysqL学习

Nosql数据库: MongoDB(特点是面向文档)
MysqL学习

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

MysqL学习

上面这个便是不合适的冗余,原因是:
MysqL学习

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

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

MysqL学习

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

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

1---n 冗余应当产生在1这一方.
MysqL学习

SQL语句优化
MysqL学习

sql优化的一般步调
MysqL学习

1.通过show status命令了解各种sql的执行频率.
MysqL学习

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

3.通过explain阐发低效率的sql
MysqL学习

4.确定问题并采取相应的优化步伐MysqL学习

-- select语句分类SelectDml数据 *** 作语言(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学习

MysqL客户端连接成功后,通过使用show [session|global] status 命令可以提供服务器状态信息.其中的session来表现当前的连接的统计结果,global来表现自数据库上次启动至今的统计结果.默认是session级别的.
MysqL学习

下面的例子：
MysqL学习

show status like 'Com_%';
MysqL学习

其中Com_XXX表现XXX语句所执行的次数.
MysqL学习

重点注意：Com_select,Com_insert,Com_update,Com_delete通过这几个参数,可以容易地了解到当前数据库的应用是以插入更新为主还是以查询 *** 作为主,以及各类的sql大致的执行比例是多少.
MysqL学习

还有几个常用的参数便于用户了解数据库的基本情况.
MysqL学习

Connections：试图连接MysqL服务器的次数
MysqL学习

Uptime：服务器工作的时间(单位秒)
MysqL学习

Slow_querIEs：慢查询的次数 (默认是慢查询时间10s)
MysqL学习

show status like 'Connections'show status like 'Uptime'show status like 'Slow_querIEs' 

如何查询MysqL的慢查询时间
MysqL学习

Show variables like 'long_query_time'; 

修改MysqL 慢查询时间
MysqL学习

set long_query_time=2 

SQL语句优化-定位慢查询
MysqL学习

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

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

select/update/delete.. / 当前连接)
MysqL学习

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

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

修改MysqL的慢查询.
MysqL学习

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

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

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,/*红利*/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,hisal DECIMAL(17,2) NOT NulL)ENGINE=MyISAM DEFAulT CHARSET=utf8; 

测试数据
MysqL学习

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 $$
创建函数,该函数会返回一个指定长度的随机字符串MysqL学习

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 

创建一个存储过程MysqL学习

create procedure insert_emp(in start int(10),in max_num int(10))begindeclare i int default 0; #set autocommit =0 把autocommit设置成0set autocommit = 0; repeatset i = i + 1;insert into emp values ((start+i),rand_string(6),'SALESMAN',0001,curdate(),2000,400,rand());until i = max_numend repeat;commit;end #调用刚刚写好的函数,1800000条记录,从100001号开始call insert_emp(100001,4000000);

这时我们如果出现一条语句执行时间跨越1秒中,就会统计到.
MysqL学习

如果把慢查询的sql记录到我们的一个日志中
MysqL学习

在默认情况下,低版本的MysqL不会记录慢查询,必要在启动MysqL时候,指定记录慢查询才可以
MysqL学习

bin\MysqLd.exe - -safe-mode - -slow-query-log [MysqL5.5 可以在my.ini指定]
MysqL学习

bin\MysqLd.exe Clog-slow-querIEs=d:/abc.log [低版本MysqL5.0可以在my.ini指定]
MysqL学习

该慢查询日志会放在data目录下[在MysqL5.0这个版本中时放在 MysqL安装目录/data/下],在 MysqL5.5.19下是必要查看
MysqL学习

my.ini 的 datadir="C:/documents and Settings/All Users/Application Data/MysqL/MysqL Server 5.5/Data/“来确定.
MysqL学习

在MysqL5.6中,默认是启动记录慢查询的,my.ini的所在目录为：C:\ProgramData\MysqL\MysqL Server 5.6,其中有一个配置项
MysqL学习

slow-query-log=1
MysqL学习

针对 MysqL5.5启动慢查询有两种办法
MysqL学习

bin\MysqLd.exe - -safe-mode - -slow-query-log
MysqL学习

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

[MysqLd]# The TCP/IP Port the MysqL Server will Listen onport=3306slow-query-log 

通过慢查询日志定位执行效率较低的SQL语句.慢查询日志记录了所有执行时间跨越long_query_time所设置的SQL语句.
MysqL学习

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

为dept表添加数据MysqL学习

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

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

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

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

测试语句
MysqL学习

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]来配置
MysqL学习

SQL语句优化-explain阐发问题
MysqL学习

Explain select * from emp where ename=“wsrcla”

会发生如下信息：
MysqL学习

select_type:表现查询的类型.
MysqL学习

table:输出成果集的表
MysqL学习

type:表现表的连接类型
MysqL学习

possible_keys:表现查询时,可能使用的索引
MysqL学习

key:表现实际使用的索引
MysqL学习

key_len:索引字段的长度
MysqL学习

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

Extra:执行情况的描述和阐明
MysqL学习

MysqL学习

explain select * from emp where ename='JKLOIP'
MysqL学习

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

explain select * from emp order by ename\G 

EXPLAIN详解
MysqL学习

ID
MysqL学习

SELECT辨认符.这是SELECT的查询序列号
MysqL学习

ID 示例
MysqL学习

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

select_type
MysqL学习

PRIMARY :子查询中最外层查询
MysqL学习

SUBquery : 子查询内层第一个SELECT,成果不依赖于外部查询
MysqL学习

DEPENDENT SUBquery:子查询内层第一个SELECT,依赖于外部查询
MysqL学习

UNION :UNION语句中第二个SELECT开始后面所有SELECT,
MysqL学习

SIMPLE
MysqL学习

UNION RESulT UNION 中合并成果
MysqL学习

table
MysqL学习

显示这一步所拜访数据库中表名称
MysqL学习

Type
MysqL学习

对表拜访方式
MysqL学习

ALL：
MysqL学习

SELECT * FROM emp \G

完整的表扫描 通常欠好
MysqL学习

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

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

const：表最多有一个匹配行
MysqL学习

Possible_keys
MysqL学习

该查询可以利用的索引,如果没有任何索引显示 null
MysqL学习

Key
MysqL学习

MysqL 从 Possible_keys 所选择使用索引
MysqL学习

Rows
MysqL学习

估算出成果集行数
MysqL学习

Extra
MysqL学习

查询细节信息
MysqL学习

No tables ：query语句中使用FROM DUAL 或不含任何FROM子句
MysqL学习

Using filesort ：当query中包括 ORDER BY *** 作,而且无法利用索引完成排序,
MysqL学习

Impossible WHERE noticed after reading const tables: MysqL query Optimizer
MysqL学习

通过收集统计信息弗成能存在结果
MysqL学习

Using temporary：某些 *** 作必需使用临时表,常见 GROUP BY ; ORDER BY
MysqL学习

Using where：不消读取表中所有信息,仅通过索引就可以获取所需数据;
MysqL学习

以上所述是小编给大家介绍的MysqL数据库性能优化一,下篇文章继续给大家介绍MysqL数据库性能优化二,希望大家连续关注本站最新内容!MysqL学习

内存溢出PHP培训学院每天发布《MysqL入门MysqL数据库性能优化一》等实战技能，PHP、MysqL、liNUX、APP、Js,CSS全面培养人才。

总结

以上是内存溢出为你收集整理的Mysql入门Mysql数据库性能优化一全部内容，希望文章能够帮你解决Mysql入门Mysql数据库性能优化一所遇到的程序开发问题。

如果觉得内存溢出网站内容还不错，欢迎将内存溢出网站推荐给程序员好友。