2018年,让你的数据库变更快的十个建议

摘要

大多数网站的内容都存在数据库里,用户通过请求来访问内容。数据库非常的快,有许多技巧能让你优化数据库的速度,使你不浪费服务器的资源。在这篇文章中,我收录了十个优化数据库速度的技巧。

1、小心设计数据库

第一个技巧也许看来理所当然,但事实上大部分数据库的问题都来自于设计不好的数据库结构。

譬如我曾经遇见过将客户端信息和支付信息储存在同一个数据库列中的例子。对于系统和用数据库的开发者来说,这很糟糕。

新建数据库时,应当将信息储存在不同的表里,采用标准的命名方式,并采用主键。

来源: http://www.simple-talk.com/sql/database-administration/ten-common-database-design-mistakes/

2、清楚你需要优化的地方

如果你想优化某个查询语句,清楚的知道这个语句的结果是非常有帮助的。采用 EXPLAIN 语句,你将获得很多有用的信息,下面来看个例子:

EXPLAIN SELECT * FROM ref_table,other_table WHERE ref_table.key_column=other_table.column;

来源: http://dev.mysql.com/doc/refman/5.0/en/using-explain.html

3、最快的查询语句… 是那些你没发送的语句

每次你向数据库发送一条语句,你都会用掉很多服务器资源。所以在很高流量的网站中,最好的方法是将你的查询语句缓存起来。

有许多种缓存语句的方法,下面列出了几个:

AdoDB: AdoDB 是一个 PHP 的数据库简化库。使用它,你可以选用不同的数据库系统 (MySQL, PostGreSQL, Interbase 等等),而且它就是为了速度而设计的。AdoDB 提供了简单但强大的缓存系统。还有,AdoDB 拥有 BSD 许可,你可以在你的项目中免费使用它。对于商业化的项目,它也有 LGPL 许可。

Memcached:Memcached 是一种分布式内存缓存系统,它可以减轻数据库的负载,来加速基于动态数据库的网站。

CSQL Cache: CSQL 缓存是一个开源的数据缓存架构。我没有试过它,但它看起来非常的棒。

4、不要 select 你不需要的

获取想要的数据,一种非常常见的方式就是采用 * 字符,这会列出所有的列。

SELECT * FROM wp_posts;

然而,你应该仅列出你需要的列,如下所示。如果在一个非常小型的网站,譬如,一分钟一个用户访问,可能没有什么分别。然而如果像 Cats Who Code 这样大流量的网站,这就为数据库省了很多事。

SELECT title, excerpt, author FROM wp_posts;

5、采用 LIMIT

仅获得某个特定行数的数据是非常常见的。譬如博客每页只显示十篇文章。这时,你应该使用 LIMIT,来限定你想选定的数据的行数。

如果没有 LIMIT,表有 100,000 行数据,你将会遍历所有的行数,这对于服务器来说是不必要的负担。

SELECT title, excerpt, author FROM wp_posts LIMIT 10;

6、避免循环中的查询

当在 PHP 中使用 SQL 时,可以将 SQL 放在循环语句中。但这么做给你的数据库增加了负担。

下面的例子说明了 “在循环语句中嵌套查询语句” 的问题:

foreach ($display_order as $id => $ordinal){     
     $sql = "UPDATE categories SET display_order = $ordinal WHERE id = $id";
     mysql_query($sql); 
 }

你可以这么做:

UPDATE categories
     SET display_order = CASE id 
         WHEN 1 THEN 3
         WHEN 2 THEN 4
         WHEN 3 THEN 5    
  END WHERE id IN (1,2,3)

来源: http://www.karlrixon.co.uk/articles/sql/update-multiple-rows-with-different-values-and-a-single-sql-query/

7、采用 join 来替换子查询

程序员可能会喜欢用子查询,甚至滥用。下面的子查询非常有用:

SELECT a.id,
         (SELECT MAX(created)
          FROM posts
          WHERE author_id = a.id) 
   AS latest_post FROM authors a

虽然子查询很有用,但 join 语句可以替换它,join 语句执行起来更快。

SELECT a.id, MAX(p.created) AS latest_post

   FROM authors a
    INNER JOIN posts p
      ON (a.id = p.author_id)
   GROUP BY a.id

来源: http://20bits.com/articles/10-tips-for-optimizing-mysql-queries-that-dont-suck/

8、小心使用通配符

通配符非常好用,在搜索数据的时候可以用通配符来代替一个或多个字符。我不是说不能用,而是,应该小心使用,并且不要使用全词通配符 (full wildcard),前缀通配符或后置通配符可以完成相同的任务。

事实上,在百万数量级的数据上采用全词通配符来搜索会让你的数据库当机。

#Full wildcard

   SELECT * FROM TABLE WHERE COLUMN LIKE '%hello%';    #Postfix wildcard

  SELECT * FROM TABLE WHERE COLUMN LIKE  'hello%';  #Prefix wildcard

  SELECT * FROM TABLE WHERE COLUMN LIKE  '%hello';

来源: http://hungred.com/useful-information/ways-optimize-sql-queries/

9、采用 UNION 来代替 OR

下面的例子采用 OR 语句来:

SELECT * FROM a, b WHERE a.p = b.q or a.x = b.y;

UNION 语句,你可以将 2 个或更多 select 语句的结果拼在一起。下面的例子返回的结果同上面的一样,但是速度要快些:

SELECT * FROM a, b WHERE a.p = b.q 
    UNION 
    SELECT * FROM a, b WHERE a.x = b.y

来源: http://www.bcarter.com/optimsql.htm

10. 使用索引

数据库索引和你在图书馆中见到的索引类似:能让你更快速的获取想要的信息,正如图书馆中的索引能让读者更快的找到想要的书一样。

可以在一个列上创建索引,也可以在多个列上创建。索引是一种数据结构,它将表中的一列或多列的值以特定的顺序组织起来。

下面的语句在 Product 表的 Model 列上创建索引。这个索引的名字叫作 idxModel

CREATE INDEX idxModel ON Product (Model);

今天的分享就到这里,谢谢大家!

本文转载自:http://geek.csdn.net/news/detail/249705

原文发布于微信公众号 - IT大咖说(itdakashuo)

原文发表时间:2018-01-08

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

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

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

关于primary key和unique index的奇怪问题 (58天)

今天一个dba交给我一个问题,让我帮忙查一下。说有个脚本运行的时候有错,让我看看是什么原因。 脚本的思路如下: 先drop PK,FK之类的constraint...

304120
来自专栏性能与架构

MySQL 8.0 将结束 MyISAM 引擎

MyISAM 存储引擎已经有了20年的历史,在1995年时,MyISAM 是 MySQL 唯一的存储引擎,服务了20多年,即将退居二线 MySQL 5.7 中...

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

MySQL中的自增列

当然基于MySQL自增列的实现,确实是不够优雅,在新的版本还在持续引入新的特性。比如MGR里面,自增列的步长大了许多,默认是7了,这是在设计的时候考虑了MGR的...

24520
来自专栏互联网技术栈

MySQL 锁机制——必知必会

MyISAM表的读和写是串行的,但这是就总体而言的。在一定条件下,MyISAM表也支持查询和插入操作的并发进行。

15560
来自专栏ImportSource

锁系列-Mysql中的锁

在计算机科学中,锁是在执行多线程时用于强行限制资源访问的同步机制,即用于在并发控制中保证对互斥要求的满足。 目录: 1、行级锁、表级锁、页级锁 2、共享锁和排它...

376150
来自专栏禅林阆苑

mysql学习总结08 — 优化(设计层)-索引与分区分表

普通索引:(index) 对关键字没有要求,如果一个索引在多个字段提取关键字,称为复合索引

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

MySQL中的derived table(r12笔记第47天)

初始MySQL中的derived table还是在一个偶然的问题场景中。 下面的语句在执行的时候抛出了错误。 UPDATE payment_data rr ...

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

通过shell脚本模拟MySQL自增列的不一致问题

MySQL的自增列问题其实很有意思,在重启数据库之后,会按照max(id)+1的方式来计算,这样一个看起来有些别扭的实现方式在早期版本就饱受诟病,在MyS...

35540
来自专栏L宝宝聊IT

Mysql性能优化——索引

对于没有索引的表,单表查询可能几十万数据就是瓶颈,而通常大型网站单日就可能会产生几十万甚至几百万的数据,没有索引查询会变的非常缓慢。

9320
来自专栏FreeBuf

如何对已损坏的SQLite数据库取证分析?

SQLite是当今最流行的数据库之一,许多移动应用台式计算机以及便携式笔记本上都用它来存储数据(例如桌面工具、浏览器以及社交媒体软件等),因此SQLite在电子...

46390

扫码关注云+社区

领取腾讯云代金券