文档章节

MySQL应用优化

lonelydawn
 lonelydawn
发布于 2016/06/16 21:37
字数 1478
阅读 4146
收藏 477

一、基本语句优化原则

(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
粉丝 42
博文 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 5.6正式版 亮点功能盘点

  【IT168 资讯】2013年2月6日消息,甲骨文公司近日宣布,推出开源数据库MySQL 5.6正式版,首个正式版版本号为5.6.10。MySQL5.6通过提升MySQL优化诊断来提供更好的查询执行时间和诊断功能,...

it168网站
2013/02/06
0
0
OSC 第 136 期高手问答 — MySQL 开发和运维规范

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

局长
2016/12/06
8.9K
53
「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

没有更多内容

加载失败,请刷新页面

加载更多

你为什么在Redis里读到了本应过期的数据

一个事故的故事 晚上睡的正香突然被电话吵醒,对面是开发焦急的声音:我们的程序在访问redis的时候读到了本应过期的key导致整个业务逻辑出了问题,需要马上解决。 看到这里你可能会想:这是不...

IT--小哥
今天
2
0
祝大家节日快乐,阖家幸福! centos GnuTLS 漏洞

yum update -y gnutls 修复了GnuTLS 漏洞。更新到最新 gnutls.x86_64 0:2.12.23-22.el6 版本

yizhichao
昨天
5
0
Scrapy 1.5.0之选择器

构造选择器 Scrapy选择器是通过文本(Text)或 TextResponse 对象构造的 Selector 类的实例。 它根据输入类型自动选择最佳的解析规则(XML vs HTML): >>> from scrapy.selector import Sele...

Eappo_Geng
昨天
4
0
Windows下Git多账号配置,同一电脑多个ssh-key的管理

Windows下Git多账号配置,同一电脑多个ssh-key的管理   这一篇文章是对上一篇文章《Git-TortoiseGit完整配置流程》的拓展,所以需要对上一篇文章有所了解,当然直接往下看也可以,其中也有...

morpheusWB
昨天
5
0
中秋快乐!!!

HiBlock
昨天
3
0

没有更多内容

加载失败,请刷新页面

加载更多

返回顶部
顶部