单机数据库优化

数据库优化有很多可以讲,按照支撑的数据量来分可以分为两个阶段:单机数据库和分库分表,前者一般可以支撑500W或者10G以内的数据,超过这个值则需要考虑分库分表。另外,一般大企业面试往往会从单机数据库问起,一步一步问到分库分表,中间会穿插很多数据库优化的问题。本文试图描述单机数据库优化的一些实践,数据库基于mysql,如有不合理的地方,欢迎指正。

1、表结构优化

在开始做一个应用的时候,数据库的表结构设计往往会影响应用后期的性能,特别是用户量上来了以后的性能。因此,表结构优化是一个很重要的步骤。

1.1、字符集

一般来说尽量选择UTF-8,虽然在存中午的时候GBK比UTF-8使用的存储空间少,但是UTF-8兼容各国语言,其实我们不必为了这点存储空间而牺牲了扩展性。事实上,后期如果要从GBK转为UTF-8所要付出的代价是很高的,需要进行数据迁移,而存储空间完全可以用花钱扩充硬盘来解决。

1.2、主键

在使用mysql的innodb的时候,innodb的底层存储模型是B+树,它使用主键作为聚簇索引,使用插入的数据作为叶子节点,通过主键可以很快找到叶子节点,从而快速获取记录。因此在设计表的时候需要增加一个主键,而且最好要自增。因为自增主键可以让插入的数据按主键顺序插入到底层的B+树的叶子节点中,由于是按序的,这种插入几乎不需要去移动已有的其它数据,所以插入效率很高。如果主键不是自增的,那么每次主键的值近似随机,这时候就有可能需要移动大量数据来保证B+树的特性,增加了不必要的开销。

1.3、字段

1.3.1、建了索引的字段必须加上not null约束,并且设置default值

1.3.2、不建议使用float、double来存小数,防止精度损失,建议使用decimal

1.3.3、不建议使用Text/blob来保存大量数据,因为对大文本的读写会造成比较大的I/O开销,同时占用mysql的缓存,高并发下会极大的降低数据库的吞吐量,建议将大文本数据保存在专门的文件存储系统中,mysql中只保存这个文件的访问地址,比如博客文章可以保存在文件中,mysql中只保存文件的相对地址。

1.3.4、varchar类型长度建议不要超过8K。

1.3.5、时间类型建议使用Datetime,不要使用timestamp,虽然Datetime占用8个字节,而timestamp只占用4个字节,但是后者要保证非空,而且后者是对时区敏感的。

1.3.6、建议表中增加gmt_create和gmt_modified两个字段,用来记录数据创建的修改时间。这两个字段建立的原因是方便查问题。

1.4、索引创建

1.4.1、这个阶段由于对业务并不了解,所以尽量不要盲目加索引,只为一些一定会用到索引的字段加普通索引。

1.4.2、创建innodb单列索引的长度不要超过767bytes,如果超过会用前255bytes作为前缀索引

1.4.3、创建innodb组合索引的各列索引长度不要超过767bytes,一共加起来不要超过3072bytes

本文来自企鹅号 - 尚学堂java学院媒体

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏desperate633

浅谈数据库查询优化的几种思路

应尽量避免全表扫描,首先应考虑在 where 及 order by ,group by 涉及的列上建立索引

63110
来自专栏java一日一条

你不得不知道的 MySQL 优化原理(一)

说起MySQL的查询优化,相信大家收藏了一堆奇淫技巧:不能使用SELECT *、不使用NULL字段、合理创建索引、为字段选择合适的数据类型….. 你是否真的理解...

16920
来自专栏杨建荣的学习笔记

关于MySQL极限值的初步验证纠错

晚上从珠江边回来之后,看到一篇文章说,MySQL有几个极限值,一个表的字段最多只有1017个,我看了以后表示怀疑。怎么快速验证呢,我看到文章的时候已经11点了,...

35250
来自专栏芋道源码1024

MySQL 大表优化方案(长文)

|原文链接:https://segmentfault.com/a/1190000006158186

18850
来自专栏吴伟祥

mysql 自增id和UUID做主键性能分析,及最优方案

UUID 是 通用唯一识别码(Universally Unique Identifier)的缩写,是一种软件建构的标准,亦为开放软件基金会组织在分布式计算环境领...

65120
来自专栏杨建荣的学习笔记

通过定制orabbix监控分析潜在的Oracle问题 (r6笔记第32天)

在之前的博客中分享过 简单定制Orabbix监控项 http://blog.itpub.net/23718752/viewspace-1769773/ 定...

28840
来自专栏杨建荣的学习笔记

使用序列的问题ORA-02287(r5笔记第19天)

今天一个开发的同事问我一个问题,说在执行一条sql语句的时候报了ORA错误,脑海中删除了各种权限的问题之后,他提供给我的错误还是在我预料之外。 ERROR at...

36460
来自专栏数据和云

高危防范:巧用触发器,实现DDL监控

在数据运维过程中,常常因为DBA的疏忽而使数据安全面临威胁,有些威胁来自数据库外部,如rm操作,而有些威胁则来自数据库内部,如Truncate操作.因此对于数据...

30340
来自专栏Rgc

项目中记录影响性能的缓慢数据库查询

如果程序性能随着时间推移不断降低,那很有可能是因为数据库查询变慢了,随着数据库规模的增长,这一情况还会变得更糟。优化数据库有时很简单,需要在程序和数据库之间加入...

387110
来自专栏小怪聊职场

MySQL(五)|《千万级大数据查询优化》第二篇:查询性能优化(1)

33980

扫码关注云+社区

领取腾讯云代金券