数据库专题(一) ——数据库优化

数据库专题(一)

——数据库优化

(原创内容,转载请注明来源,谢谢)

一、概述

数据库的优化通常分为三个方面:数据库DML、DQL的优化(即增删改查等SQL语句优化);数据库设计优化(如索引设置、索引类型、表引擎、冗余字段、主键外键等);数据库服务器和配置优化(如主从分离、读写分离等)。

根据不同的业务场景,需要进行不同的优化措施。

二、数据库语句优化

程序对数据库的操作,绝大部分来自查询,因此查询的优化至关重要,而大部分情况下,查询的优化在于索引命中率。网络上有很多查询优化的例子,在此主要说几点。

1、limit

limit主要有两个注意事项。

1)当仅需要1条(或几条,而不是整个查询结果)内容时,建议使用limit 1或需要的条数,这样当数据库在查询到满足limit要求的条数后就会停止查找。这在大表、多个join等情况下效果较好。

2)使用分页通常都会用到limit,但是当limit的基数较大时,建议使用between。例如limit100000 , 10可以改成:

a. between 100000 and 100010

b. where id > 100000 limit 10

原因是limit是从前面往后数,如果limit100000,会从前往后扫描100000个数据,between可以直接定位,速度更快。但是between存在当数字不连续时,无法正好取到10个的问题。因此如果条件允许使用情况b速度更快。

2、运算

避免在要查询的列进行运算,避免在要查询的列进行类型转换,否则无法使用索引。

例如where year(d)> 2017 改为where d > ‘2017-01-01’,where num/2>5改为where num>10

同理,如果需要查询过程中进行字符串拼接,如concat(col1, col2),除非要对拼接结果进行where筛选,否则可以将查询结果在编程语言(如PHP)中进行拼接。

3、*

很多时候为了方便,使用select*,但是如果表的字段非常多,且还有连表的情况下,如果需要的字段不多,建议将*改为具体的字段。这样会节省内存。

4、避免使用数据库的rand()函数

当需要查询表中的随机x条数据,避免使用rand,因为其无法使用索引。可以使用PHP将需要查询的随机条件提取出来,则在数据库中只要执行查询即可。

5、避免频繁和数据库连接

当需要批量插入数据、查询多个表信息时,在条件允许情况下,都是一次性执行完。对于insert,可以用数据库的批量插入方法 insert into table (name, status) values(‘a’,1),(‘b’,2)…;对于查询,可以使用join、union等。

6、缓存

同样条件的查询使用同样的查询语句,空格、where条件顺序、select字段顺序等,都应完全一致,这样数据库可以使用缓存,快速查询出结果。

因此,通常可以将sql语句的拼接方式定义成一个类,这样每次都调用那个类来生成sql,则每次的语句也就可以一模一样。

三、数据库设计优化

数据库设计阶段非常重要,当数据库表里面有大量数据,且有大量的程序对此表有操作时,再进行修改,就是一个灾难。因此,在设计阶段,就需要根据业务场景进行详细设计,并且需要考虑今后可能达到的数据量级。

1、映射表还是冗余字段

当两个表有关联时,两个表的关联方式可以使用新建映射表,也可以使用冗余字段,两者的适用场景不同。现假设两个关联表名称为A和B。

新建映射表即新建一个表C,该表用于存放表A与表B有对应关系的id,即表C的结构是 id, aid,bid,其每一行表示表A的id为aid的数据与表B的id为bid的数据具有对应关系。

冗余字段即在表A中加入一个字段bid,则很容易从表a中确定每一行和表b的对应关系。

映射表单独使用,便于单独查询与维护,但是当表A、B中任何一行被删除时,都需要同步删除相应的映射表;冗余字段查询方便,查询A可以附带把B的信息查到,但是其维护复杂,当表B删除一行时,则还需要考虑是将A的此行置空还是不允许B删除。

通常A和B是多对多的情况下,必须建映射表,例如书本和其详细分类,每本书都可以分在好几类,而每一类又有好多书;但A和B是一对一或者一对多的情况下,则需要在“多”的那个表建立冗余字段,如城市和省份关系表,需要在城市表里冗余省份字段。

2、冗余

上述的冗余情况之外,还需要考虑,例如还是提到城市和省份,当查询到城市时,如果大量的需要也需要查询省份的名字,而不仅是省份ID,则除了在城市表里冗余省份的ID,也可以再冗余省份的名字。这样,可以避免连表查询,加快查询速度。

3、索引

索引的建立大部分情况下可以加快查询速度(满足索引命中条件为前提),但是由于索引本身也需要数据库去维护,也就意味着增加、删除需要消耗额外的时间去维护。

当表的新增、删除次数远大于查询时,如用于消息队列的表,有新内容则存放于表内,表内的内容处理完则删除本行数据,则不建议建立索引。

另外,索引要建立在区分度较高的字段中。因为索引大多采用B+Tree结构,如果一个字段区分度极低,如性别字段,只有两个值,则丧失索引的意义。

4、数据库引擎

mysql常用的引擎是myisam和innodb。

myisam是表级锁,其不支持事务,简单查询速度较快,索引和数据分开存储,减轻数据占用存量。

innodb是行级锁,其支持事务与各种复杂的查询,对高并发支持好于myisam,但是其索引和数据是在一起存储的,通常表的占用量较大,且仅进行查询的情况下效率不如myisam。

因此,当仅仅查询的比例远高于修改、删除、新增(read/write>100:1),并发不高且不需要事务,存储数据量不多(千万级以内),使用mysiam好于innodb。当需要高并发,或需要频繁更新表数据(即对事务有需求),或需要行级锁时,必须要选择innodb。

四、数据库服务器及配置优化

除了上述条件外,在数据库服务器和配置上也有需要进行优化的地方。

1、读写分离

通常大的系统中,都会进行读写分离操作。因为大量的操作都是读操作,因此可以将读操作单独设定一个数据库服务器,而写操作(包括增删改)另外调用一个服务器。

2、主从配置

配合读写分离,在多个数据库服务器的情况下,通常是一个主服务器,多个从服务器。主服务员用于处理写操作,且发生写操作后将数据同步到各从服务器中。所有的从服务器都设置为读操作,并将多个请求采用不同的策略(例如轮询、权重轮询等)分发给不同的从服务器,以降低服务器压力,减少后面请求的等待时间。

3、双机热备

通常情况下,为了安全,需要避免单点的情况。因此通常会设置两个主服务器,多个从服务器,正常情况下使用其中一个主服务器处理写操作,另外一个主服务器闲置或者也当作从服务器使用。当主服务器接收到写操作,同步给其他的从服务器,包括备用主服务器。当主服务器宕机,则可以立即启用备用主服务器。

4、数据库分表

1)字段拆分

当数据库字段较多,且其中某些字段内容较大又改动量少时,例如图片、大文件等,可以将表拆分,将这些字段另外在一个表存放。

2)分表

当一个表的数据量巨大,且有大量的历史数据,较少的场景会进行查询时,可以使用分表。例如订单表,5年之前的订单几乎不会去查看,则可以将这些历史数据移到别的表中,而保证需要经常查询的表数据量减少,以加快查询速度。

5、使用nosql

当涉及秒杀等高并发场景,需要大吞吐量,而又不需要有大量的数据持久化存储,redis是更好解决方案。另外,在大量改动数据的情况下,可以先暂存于redis或memcache,当确认改动完毕,或设置个定时,将数据再转存到mysql进行持久化。

——written by linhxx 2017.07.27

原文发布于微信公众号 - 决胜机器学习(phpthinker)

原文发表时间:2017-07-27

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏一枝花算不算浪漫

[Linux]Linux下安装和配置solr/tomcat/IK分词器 详细实例二.

373100
来自专栏数据库

MySQL数据库性能优化之一

文章来自:博客 数据库属于 IO密集型的应用程序,其主要职责就是数据的管理及存储工作。而我们知道,从内存中读取一个数据库的时间是微秒级别,而从一块普通硬盘上读取...

619100
来自专栏MYSQL轻松学

MYSQL数据闪回方式

MYSQL官方截止目前还没有出来数据闪回特性,也许后续版本会出现。社区有一些开源工具可以使用,沿用的基本都是彭立勋最早提出的思路,利用binlog对SQL进行反...

45480
来自专栏性能与架构

Mysql优化中Profiling的使用

要想优化一条Query,就须要清楚这条Query的性能瓶颈到底在哪里,是消耗的CPU计算太多,还是需要的IO操作太多?要想能够清楚地了解这些信息,可以通过Que...

31040
来自专栏沃趣科技

“mysqlbinlog”工具做binlog server靠谱吗?

玩过binlog server的同学都知道,它使用mysqlbinlog命令以daemon进程的方式模拟一个slave的IO线程与主库连接,可以很方便地即时同...

57380
来自专栏禁心尽力

关于事务

何为事务? 1.事务是指事务开始到事务结束之间的一组sql语句的操作单元,并且是组内所有sql语句共同完成的业务逻辑。 2.要想使用事务,首先关闭系统自动提交功...

20750
来自专栏Java架构师历程

MYSQL 谈谈各存储引擎的优缺点

1、存储引擎其实就是如何实现存储数据,如何为存储的数据建立索引以及如何更新,查询数据等技术实现的方法。

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

MySQL中的事务和锁简单测试(r10笔记第46天)

一直以来,对于MySQL中的事务和锁的内容是浅尝辄止,没有花时间了解过,在一次看同事排查的故障中有个问题引起了我的兴趣,虽然过去了很久,但是现在简单总结一下还是...

35670
来自专栏大眼瞪小眼

MySQL基础复习

2NF:每一个非主属性完全依赖于候选码(属性组的值能唯一的标识一个元组,但是其子集不可以)。

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

由小见大-MySQL脚本部署中的一些策略

在线上环境中部署脚本,可谓是常在河边走,哪有不湿鞋,所以大大小小的案例总结下来,还是会发现一些有趣的地方,这些可以作为操作时的一些参考,仅供参考而已。 第一类...

35060

扫码关注云+社区

领取腾讯云代金券