文档章节

MySQL应用优化

lonelydawn
 lonelydawn
发布于 2016/06/16 21:37
字数 1478
阅读 4138
收藏 477
点赞 6
评论 20

一、基本语句优化原则

(1).尽量避免在索引列上进行运算或函数操作,这样会导致索引失效

如:

select * from t where Year(d)>=2016;

可以优化为:

select * from t where d>='2016-01-01';

(2).使用join语句时,应用小结果集驱动大结果集。因为在join多表时,可能会导致更多的锁定和拥塞

(3).注意模糊查询时避免%%,%开头的查询条件会使索引失效

(4).仅列出需要查询的字段,这对效率没有影响,但会影响内存

如:

select * from t;

可以优化为:

select name from t;

(5).使用批量交互插入语句以节省交互
如:

insert into t(id,name) values(1,"a");

insert into t(id,name) values(2,"b"); 

可以优化为:

insert into t(id,name) values(1,"a"),(2,"b");

这里也有博友质疑,贴结果:

(6).limit的基数比较大时使用between

如:

select * from article order by id limit 100000,10;

可以优化为:

select * from article between 100000 and 100010 order by id;

这里需要注意的是,如果id不连续的话,使用between获得的数据量会少于预计的数据量。

(7).避免使用NULL,这样会使mysql先进行一次是否为NULL的判定

(8).(这里颇有争议,经测试并查阅一下资料修改如下)

如果id作为非主键字段,不要使用count(id),而是count(*),因为id未作非空约束时,会先进行NULL值判定

id作为主键时,在效率上,count(id)>count(*),若id作为自增主键,count(id)的效率会更高

(9).不要做不必要的排序,尽量在索引中进行排序

 

二、Mysql的存储引擎分析

  MyISAM Memory InnoDB
用途 快读 内存数据 完整的事务支持
全表锁定 全表锁定 多重隔离级别的行锁
持久性 基于表恢复 无磁盘I/O,无可持久性 基于日志的恢复
事务特性 不支持 不支持 支持
支持索引类型 B-tree/FullText/R-tree Hash/B-tree Hash/B-tree

在介绍存储引擎的选择原则之前,先介绍一下读写比。读写比,即读取和写入语句执行次数的比,一般理想的读写比在100:1左右。

当读写比达到10:1的时候,即认为其是以写为主的数据库。

(1).采用MyISAM引擎(关键是快读,最简版的MySQL数据库)

R/W>100:1,且update较少

并发不高,不需要事务

表数据量小,硬件资源差

(2).采用InnoDB引擎(功能完备的MySQL数据库)

R/W比较小,数据更新频繁

海量数据,高并发

安全性、可用性高

(3).采用Memory引擎

内存充足

对数据一致性要求不高

定期归档(将过时的历史数据存入文件系统)

 

最常用的两种引擎是MyISAM和InnoDB,MyISAM注重效率,InnoDB注重事务。

 

三、数据库设计

1、范式与反范式

在数据库理论发展的过程中,逐渐形成五大范式,从第一范式到第五范式,数据库冗余降低,但查询效率也会随之降低。

上世纪硬件设备并不发达,空间成本比较高,所以设计理念是提高范式等级,减少冗余,利用时间换取空间,平衡点基本落在第三范式上。那什么又是反范式呢?随着硬件设备的发展,空间成本大幅度降低,而更多的是对时间和效率的要求,所以范式等级可以适当降低,增加冗余,最低可把范式降到第一范式。

反范式示例,由于一条记录被分到多张表中进行记录,查询需要进行多表关联,当要查询的数据量很大时,连表查询的时间成本就会很高,更严重的情况会引起数据库服务器宕机。这时候就需要建立冗余表将数据集中到一个表中记录。冗余表一般符合低等级范式。如何减少冗余表的空间成本呢?一般是定期转储。将一段时间之前的数据从数据库服务器导出,存储到其他地方,这些数据应是现在无需使用的数据。

 

2.数据库分区

讲一个数据表的文件和索引分散存储在不同的物理文件中,这样在查找的时候就不需要在整个大文件中搜索,而在固定范围中查找。

假设要存储某一地区1900-2000年之间出生的孩子信息,按年份分区,代码如下:

create table child (

id int AUTO_INCREMENT,

name varchar(12) not null,

birth date not null,
primary key(id,birth)
) engine=innoDB partition by range (year(birth))

(partition foo01 values less than(1991), partition foo02 values less than(1992),

partition foo03 values less than(1993), partition foo04 values less than(1994),

partition foo05 values less than(1995), partition foo06 values less than(1996),

partition foo07 values less than(1997), partition foo08 values less than(1998),

partition foo09 values less than(1999), partition foo10 values less than(2000));

 

3.数据库分表

分表原理和分区类似,只不过分区是在不同文件中存储数据,而分表是将一张数据库表拆分成多张数据库表。

如:
 

create table child (

id int primary key AUTO_INCREMENT,

name varchar(12) not null,

birth date not null) engine=innoDB;

可分为:
 

create table child_xxxx(

id int primary key AUTO_INCREMENT,

name varchar(12) not null,

birth date not null) engine=innoDB;

 

child_xxxx代表出生年份,如child_1900保存1900年出生的孩子信息,child_1901保存1901年出生的孩子信息,以此类推。

 

总之,数据库应用设计还得根据具体的环境来选择适当的方案。空间和时间的平衡,根据需要具体情况来把握。

 

 

 

© 著作权归作者所有

共有 人打赏支持
lonelydawn
粉丝 41
博文 50
码字总数 52905
作品 0
闵行
前端工程师
加载中

评论(20)

skyim
skyim
一句话,实践才能出真知,这些有实际操作过吗
墨空云
墨空云
概括的好全。
ylxs90
ylxs90
id是主键时,count(1) 和count(id)呢?
松吖
松吖
你试过吗
透过玻璃的光
透过玻璃的光
InnoDB 对于读也要分情况,不是说完全就低于MyISAM.
而且就单表锁这一特性,MyISAM就已经不适用大部分业务场景。
相信你自己
相信你自己

引用来自“前世疯狂”的评论

“(8).不要使用count(id),应该是count(*) ” 这句是不是说反了?
对的
milin
milin
分表那个语句学习了,有空儿试下
milin
milin
用的最多的是count(1)
Raynor1
Raynor1

引用来自“AngusXer”的评论

我记得MySQL表分区索引键不是必须是主键吗?
你说得对。
xforgchen
xforgchen
select * from t where name like '%a%';等价于select * from t where name>='a' and name<'b';??? 若name = 'cccaccc'是否能查到?
「mysql优化专题」本专题总结终章(13)

一个月过去了,【mysql优化专题】围绕着mysql优化进行了十三篇的优化文章,下面进行一次完整的总结!我尝试用最简短最通俗易懂的话阐述明白每篇文章,让本专题画上完美的句号!坚持到文末,留...

java进阶架构师
01/04
0
0
2014年,什么工作最吃香?数据存储架构师!

大会介绍: 2014年,什么工作最吃香?数据存储架构师! 无论是Oracle还是MySQL数据库,还是百度、腾讯、阿里巴巴,现在都需要闪存(如SSD固态盘)来优化数据存储架构,1、2年内你的企业也必然...

向南是大海
2014/10/11
0
0
2014年,什么工作最吃香?数据存储架构师!

大会介绍: 2014年,什么工作最吃香?数据存储架构师! 无论是Oracle还是MySQL数据库,还是百度、腾讯、阿里巴巴,现在都需要闪存(如SSD固态盘)来优化数据存储架构,1、2年内你的企业也必然...

向南是大海
2014/10/09
0
0
2014年,什么工作最吃香?数据存储架构师!

大会介绍: 2014年,什么工作最吃香?数据存储架构师! 无论是Oracle还是MySQL数据库,还是百度、腾讯、阿里巴巴,现在都需要闪存(如SSD固态盘)来优化数据存储架构,1、2年内你的企业也必然...

向南是大海
2014/10/09
0
0
OSC 第 136 期高手问答 — MySQL 开发和运维规范

OSCHINA 本期高手问答(2016 年 12 月 6 日 — 12 月 13 日)我们请来了@叶金荣和@吴炳锡为大家解答 MySQL 开发和运维规范相关的问题。 叶金荣,知数堂培训联合创始人,Oracle MySQL ACE,A...

局长
2016/12/06
8.9K
53
性能调优概述

大纲: 一、概述 二、什么是性能调优?(what) 三、为什么需要性能调优?(why) 四、什么时候需要性能调优?(when) 五、什么地方需要性能调优?(where) 六、什么人来进行性能调优?(who) 七、怎...

陈明乾
2014/07/14
0
0
美图秀秀DBA谈MySQL运维及优化

随着MySQL应用的不断普及和自身发展,如何更好的优化MySQL和使用MySQL,依然是一个比较有挑战的问题,尤其是在业务快速增长的场景下。本次分享主要介绍一些通用的运维优化实践和问题,以及未...

杨尚刚
2015/12/31
0
0
浅谈MySQL SQL优化

本文首发于个人微信公众号《andyqian》,期待你的关注 前言 有好几天没有写文章了,实在不好意思。之前就有朋友希望我写写MySQL优化的文章。我迟迟没有动笔,主要是因为,SQL优化这个东西,很...

andyqian
01/30
27
2
《高性能MySQL》第三章MySQL服务器性能剖析学习笔记

MySQL性能优化介绍 什么是性能优化呢?其实我们往往从广义的定义是觉得一个MySQL系统的非功能性的优化都会看作是性能优化,比如我们会将数据库服务器的稳定性、每秒执行的SQL查询数目、系统的...

杨武兵
2015/09/24
1K
2
“2017年度十大MVP”入选者干货好文回顾

“2017年度十大MVP评选”开始征集大众投票啦!为了方便大家回顾各位MVP入选者在这一年来的精彩技术贡献,投出你最宝贵的一票,小编在此整理了一份原创文章合集,点击文章标题即可阅读全文! ...

2017/11/10
0
0

没有更多内容

加载失败,请刷新页面

加载更多

下一页

shell中的函数、shell中的数组、告警系统需求分析

shell中的函数 格式: 格式: function f_name() { command } 函数必须要放在最前面 示例1(用来打印参数) 示例2(用于定义加法) 示例3(用于显示IP) shell中的数组 shell中的数组1 定义数...

Zhouliang6
今天
2
0
用 Scikit-Learn 和 Pandas 学习线性回归

      对于想深入了解线性回归的童鞋,这里给出一个完整的例子,详细学完这个例子,对用scikit-learn来运行线性回归,评估模型不会有什么问题了。 1. 获取数据,定义问题     没有...

wangxuwei
今天
1
0
MAC安装MAVEN

一:下载maven压缩包(Zip或tar可选),解压压缩包 二:打开终端输入:vim ~/.bash_profile(如果找不到该文件新建一个:touch ./bash_profile) 三:输入i 四:输入maven环境变量配置 MAVEN_HO...

WALK_MAN
今天
0
0
33.iptables备份与恢复 firewalld的9个zone以及操作 service的操作

10.19 iptables规则备份和恢复 10.20 firewalld的9个zone 10.21 firewalld关于zone的操作 10.22 firewalld关于service的操作 10.19 iptables规则备份和恢复: ~1. 保存和备份iptables规则 ~2...

王鑫linux
今天
2
0
大数据教程(2.11):keeperalived+nginx高可用集群搭建教程

上一章节博主为大家介绍了目前大型互联网项目的系统架构体系,相信大家应该注意到其中很重要的一块知识nginx技术,在本节博主将为大家分享nginx的相关技术以及配置过程。 一、nginx相关概念 ...

em_aaron
今天
1
0
Apache Directory Studio连接Weblogic内置LDAP

OBIEE默认使用Weblogic内置LDAP管理用户及组。 要整理已存在的用户及组,此前办法是导出安全数据,文本编辑器打开认证文件,使用正则表达式获取用户及组的信息。 后来想到直接用Apache Dire...

wffger
今天
2
0
HFS

FS,它是一种上传文件的软件。 专为个人用户所设计的 HTTP 档案系统 - Http File Server,如果您觉得架设 FTP Server 太麻烦,那么这个软件可以提供您更方便的档案传输系统,下载后无须安装,...

garkey
今天
1
0
Java IO类库之BufferedInputStream

一、BufferedInputStream介绍 /** * A <code>BufferedInputStream</code> adds * functionality to another input stream-namely, * the ability to buffer the input and to * sup......

老韭菜
今天
0
0
STM 32 窗口看门狗

http://bbs.elecfans.com/jishu_805708_1_1.html https://blog.csdn.net/a1985831055/article/details/77404131...

whoisliang
昨天
1
0
Dubbo解析(六)-服务调用

当dubbo消费方和提供方都发布和引用完成后,第四步就是消费方调用提供方。 还是以dubbo的DemoService举例 -- 提供方<dubbo:application name="demo-provider"/><dubbo:registry address="z...

青离
昨天
2
0

没有更多内容

加载失败,请刷新页面

加载更多

下一页

返回顶部
顶部