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语句分类
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学习

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,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; 

测试数据
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))
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秒中,就会统计到.
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 on
port=3306
slow-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,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 ,效果很明显慢]
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全面培养人才。

转载请注明本页网址：
http://www.vephp.com/jiaocheng/12374.html