【译】 8 个 MySQL 性能提示

红薯 发布于 2011/03/16 23:13
阅读 443
收藏 8

当你开发的应用上线以后,一切都很美好,用户持续不断的涌进来,每个人都很happy。可突然发现,大量的访问用户让 MySQL  数据库崩溃,让网站崩溃,到底是什么问题呢,该如何解决呢?

本文列出 8 个关于 MySQL 数据库的性能提示或许可以帮你摆脱烦恼:

想象更大的用户量

在开发的早期,你需要意识到将有大量的用户会使用你的应用,如果你希望这个数量非常大,那应该未雨绸缪早做安排,包括复制、伸缩性以及性能。

但是如果你优化你的SQL语句、数据库结构和索引策略,或许这些已经足够应用很大的访问量了。你必须要知道的是,性能和伸缩性是两码事,你必须在充分挖掘性能潜力后再去考虑伸缩性的问题。

总是使用 EXPLAIN 来分析SQL

MySQL 的 EXPLAIN 语句可让你充分了解 MySQL 是如何执行你指定的 SELECT 查询语句,包括如何使用索引以及索引是否起效,以及表之间是如何进行联合查询的等等,如果你使用 EXPLAIN EXTENDED 还可以获取更多附加信息。

为你的每个 SELECT 语句进行 EXPLAIN 并观察分析结果,确保每个查询都是最优的。性能总是从点滴做起!

选择最合适的数据类型

典型的数据库数据都是纯粹在磁盘中(尽管也有内存中数据库的东西),因此当数据库为你查询数据的时候,它需要从磁盘中读取数据并返回,而磁盘IO是很慢的,因此尽可能使用最小的数据类型,例如能使用 tinyint 的就不用 int。

保持连接(使用连接池)

连接数据库是耗费是很大的,连接上数据库并保持这个连接,这也就是连接池的功能。

了解 Query Cache

The query cache stores the text of a SELECT statement together with the corresponding result that was sent to the client. If an identical statement is received later, the server retrieves the results from the query cache rather than parsing and executing the statement again. The query cache is shared among sessions, so a result set generated by one client can be sent in response to the same query issued by another client.

The query cache can be useful in an environment where you have tables that do not change very often and for which the server receives many identical queries. This is a typical situation for many Web servers that generate many dynamic pages based on database content.

The query cache does not return stale data. When tables are modified, any relevant entries in the query cache are flushed.

How do you find out my MySQL query cache is working or not?
MySQL provides the stats of same just type following command at mysql> prompt:

1 mysql> show variables like 'query%';

在索引列上使用函数将使索引失效

Index on a column can be great performance gain, but if you use that column in a function, index is never used.

Always try to rewrite the query to not use the function with indexed column.

1 WHERE TO_DAYS(CURRENT_DATE) - TO_DAYS(event_date) <= 7

could be

1 WHERE event_date >= '2011/03/15' - INTERVAL 7 DAYS

and today’s date is generated from PHP. This way, index on column event_date is used and the query can be stored inside Query Cache.

学习 SQL 编写之道

SQL code is the foundation for optimizing database performance. Master SQL coding techniques like rewriting subquery SQL statements to use JOINS, eliminating cursors with JOINS and similar.

By writing great SQL code your database performance will be great.

使用 ON DUPLICATE KEY UPDATE

If you specify ON DUPLICATE KEY UPDATE, and a row is inserted that would cause a duplicate value in a UNIQUE index or PRIMARY KEY, an UPDATE of the old row is performed.

1 INSERT INTO wordcount (word, count)
2 VALUES ('a_word',1)
3 ON DUPLICATE KEY UPDATE count=count+1;

You are saving one trip to the server (SELECT then UPDATE), cleaning you code up removing all if record_exists insert else update.

如果以上这8条性能提示你都遵循了,那么数据库将为你提供非常好的表现。

英文原文:http://www.codeforest.net/8-great-mysql-performance-tips

加载中
返回顶部
顶部