给开发人员的SQL Database调优方案

背景

作为一个Web开发者,我们经常会和数据库打交道,比如MySQL等,写出正常的SQL语句实现功能很容易,然而随着公司规模的扩大已经数据量的暴增,对数据表做一个最优的设计是大势所趋也是起好头将会影响接下来几年甚至几十年,所以设计出一个优秀的表对于开发人员是很重要的。 然而,开发人员的整体水平是有限的,而且作为Web开发人员,很难做到与专业的数据库管理员(DBA)对数据库的方方面面做到了如指掌。

多数公司的解决方案有

1.公司的DBA会对总体研发人员做定期的分享或培训。

2.在项目上线前由DBA对研发人员的主要表设计和SQL语句做一个review,以保证这样的SQL上线后是最优的,确保不会出现问题。

But,对于小公司而言,可能公司只会有一个DBA,版本迭代也会超快,So,完全由DBA来控制这件事已经变得难上加难。所以,最根本的解决方案是:提高研发人员对SQL语句的调优和数据表设计的本领,所以就有了本文。

本文的目的

1.提供给研发人员一些SQL和数据库本身调优的技术

2.让研发人员与DBA能更加有效的沟通

数据库调优

1.Index(索引优化)
索引是数据库中很重要的数据结构,它大幅度的提升了数据的查询速度,主键也常被定义为主键索引(Primary-Key)和唯一索引(Unique-Key)。索引是一个很有趣的话题,篇幅有限这里就不赘述了,这里,只说明一些建索引的原则和基本原则,先说下基本原则,可以按照下图来考量是否需要建索引

索引创建步骤

然而光考量基本原则还不可以,是否要建索引还需要与实际场景为准,这就引申出了如下原则。

    如果你的表会经常执行Insert、Update、Delete等操作的话,就需要做好衡量,是否真的要去建立索引?前面说了索引是一种数据结构,Insert、Update、Delete等操作在结束后,还需要重建索引(这样就需要一定的开销),以获得Select上的优势。 所以,做好衡量很重要。 建索引的基本原则

  • 表要足够大,如果不够大,建索引的效率还不如全表扫描
  • 索引字段的选择性要强(不重复的索引值)
  • 作为where查询的条件(但并不是说只要出现在where条件中就要建索引)
  • 作为order by或group by的字段
  • 基本不会再发生更改/删除操作
  • blob、text、varchar太长的字段要建立哈希索引或者前缀索引,但前缀索引无法作为order by和group by

如何更快的插入百万条数据?

1.删除掉表的所有索引

2.执行插入

3.重建索引

避免子查询

先看一个容易犯的错误,这个SQL语句中的子查询会扫描全表,开销可想而知,通常是因为它很容易理解而且很容易写出来。
[cc lang=”sql” theme=”twitlight” width=”100%” height=”700″ lines=”40″ noborder=”true”]
SELECT c.Name, c.City,
(SELECT CompanyName FROM Company WHERE ID = c.CompanyID)
AS CompanyName FROM Customer c
[/cc]

然而,稍微重构下上面的查询,即可很大程度上解决开销问题

[cc lang=”sql” theme=”twitlight” width=”100%” height=”700″ lines=”40″ noborder=”true”]
SELECT c.Name, c.City, co.CompanyName FROM Customer c
LEFT JOIN Company co ON c.CompanyID = co.CompanyID
[/cc]

节俭地查询

需要什么字段就拿什么字段,需要哪些字段就取哪些字段 NO SELECT * !!!

研发人员与DBA如何高效地工作?

To Developer

1.如果应用突然不工作了,那或许并不是数据库的问题,先确定是不是数据库的问题后,再去找DBA。

2.如果你正在设计一张表,并且无法把握表的设计是否合理,那么最好先试着设计出来,然后再和DBA一起改进表的设计。

3.不要请求DBA更改生产环境的数据库,如果一定要更改,你应该对此负责人。

To DBA

1.给研发人员一个数据库的实时状态面板,研发人员通常会怀疑数据库是否正常工作了。

2.数据库变化太快了,总有一天你要准备将数据移植到一个新版本上,研发人员会更看重于新版本的新功能,试着去升级到新版本吧。

结语

篇幅有限,其实并不过瘾,需要补充的内容还是太多太多,会慢慢完善。

参考网址:

http://www.toptal.com/sql/sql-database-tuning-for-developers

给开发人员的SQL Database调优方案
Tags:             

发表评论

电子邮件地址不会被公开。 必填项已用*标注