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

数据库专题(一)

——数据库优化

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

一、概述

数据库的优化通常分为三个方面:数据库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 条评论
登录 后参与评论

相关文章

来自专栏java一日一条

Java 理论与实践: 正确使用 Volatile 变量

Java 语言中的 Volatile 变量可以被看作是一种 “程度较轻的 synchronized”;与 synchronized 块相比,volatile 变...

522
来自专栏codingforever

构建高性能服务器 -- 缓存篇

说到缓存,相信大家都不陌生。缓存的目的都在于避免重复的慢速计算,比如数据库访问。相对于慢速计算,缓存将会大大提高数据存取的速率,当然同时将会缩短用户每次请求处理...

424
来自专栏Java技术交流群809340374

分布式锁的3种实现(数据库、缓存、Zookeeper)

目前几乎很多大型网站及应用都是分布式部署的,分布式场景中的数据一致性问题一直是一个比较重要的话题。

740
来自专栏牛肉圆粉不加葱

为什么 Spark Streaming + Kafka 无法保证 exactly once?

结合文章 揭开Spark Streaming神秘面纱④ - job 的提交与执行我们画出了如下 job 调度执行流程图:

711
来自专栏「3306 Pai」社区

有爱有恨的MDL锁

MySQL 5.5 中就引入了metadata lock(元数据锁)。用于对管理 database objects(数据库对象)的并发访问,保证数据的一致性。

1110
来自专栏Spark学习技巧

Hive高级优化

1,FetchTask 不执行mapreduce,提高速度 设置的三种方式: 方法一: set hive.fetch.task.conversion=mo...

3147
来自专栏「3306 Pai」社区

有爱有恨的MDL锁

MySQL 5.5 中就引入了metadata lock(元数据锁)。用于对管理 database objects(数据库对象)的并发访问,保证数据的一致性。

472
来自专栏IT技术精选文摘

十分钟入门RocketMQ

本文首先引出消息中间件通常需要解决哪些问题,在解决这些问题当中会遇到什么困难,Apache RocketMQ作为阿里开源的一款高性能、高吞吐量的分布式消息中间件...

3337
来自专栏美图数据技术团队

Spark on Yarn | Spark,从入门到精通

欢迎阅读美图数据技术团队的「Spark,从入门到精通」系列文章,本系列文章将由浅入深为大家介绍 Spark,从框架入门到底层架构的实现,相信总有一种姿势适合你,...

800
来自专栏码匠的流水账

聊聊replication的方式

replication和partition/sharding是分布式系统必备的两种能力。具体详见复制、分片和路由. 对于海量数据来说,replication一方...

401

扫码关注云+社区