大多数网站的内容都存在数据库里,用户通过请求来访问内容。数据库非常的快,有许多技巧能让你优化数据库的速度,使你不浪费服务器的资源。在这篇文章中,我收录了十个优化数据库速度的技巧。
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://www.sql-tutorial.com/sql-indexes-sql-tutorial/
原文链接 :catswhocode.com 编译:伯乐在线 – 唐小娟

InnoDBBufferPool reduces disk I/O by caching data and indexing pages, improving database performance. Its working principle includes: 1. Data reading: Read data from BufferPool; 2. Data writing: After modifying the data, write to BufferPool and refresh it to disk regularly; 3. Cache management: Use the LRU algorithm to manage cache pages; 4. Reading mechanism: Load adjacent data pages in advance. By sizing the BufferPool and using multiple instances, database performance can be optimized.

Compared with other programming languages, MySQL is mainly used to store and manage data, while other languages such as Python, Java, and C are used for logical processing and application development. MySQL is known for its high performance, scalability and cross-platform support, suitable for data management needs, while other languages have advantages in their respective fields such as data analytics, enterprise applications, and system programming.

MySQL is worth learning because it is a powerful open source database management system suitable for data storage, management and analysis. 1) MySQL is a relational database that uses SQL to operate data and is suitable for structured data management. 2) The SQL language is the key to interacting with MySQL and supports CRUD operations. 3) The working principle of MySQL includes client/server architecture, storage engine and query optimizer. 4) Basic usage includes creating databases and tables, and advanced usage involves joining tables using JOIN. 5) Common errors include syntax errors and permission issues, and debugging skills include checking syntax and using EXPLAIN commands. 6) Performance optimization involves the use of indexes, optimization of SQL statements and regular maintenance of databases.

MySQL is suitable for beginners to learn database skills. 1. Install MySQL server and client tools. 2. Understand basic SQL queries, such as SELECT. 3. Master data operations: create tables, insert, update, and delete data. 4. Learn advanced skills: subquery and window functions. 5. Debugging and optimization: Check syntax, use indexes, avoid SELECT*, and use LIMIT.

MySQL efficiently manages structured data through table structure and SQL query, and implements inter-table relationships through foreign keys. 1. Define the data format and type when creating a table. 2. Use foreign keys to establish relationships between tables. 3. Improve performance through indexing and query optimization. 4. Regularly backup and monitor databases to ensure data security and performance optimization.

MySQL is an open source relational database management system that is widely used in Web development. Its key features include: 1. Supports multiple storage engines, such as InnoDB and MyISAM, suitable for different scenarios; 2. Provides master-slave replication functions to facilitate load balancing and data backup; 3. Improve query efficiency through query optimization and index use.

SQL is used to interact with MySQL database to realize data addition, deletion, modification, inspection and database design. 1) SQL performs data operations through SELECT, INSERT, UPDATE, DELETE statements; 2) Use CREATE, ALTER, DROP statements for database design and management; 3) Complex queries and data analysis are implemented through SQL to improve business decision-making efficiency.

The basic operations of MySQL include creating databases, tables, and using SQL to perform CRUD operations on data. 1. Create a database: CREATEDATABASEmy_first_db; 2. Create a table: CREATETABLEbooks(idINTAUTO_INCREMENTPRIMARYKEY, titleVARCHAR(100)NOTNULL, authorVARCHAR(100)NOTNULL, published_yearINT); 3. Insert data: INSERTINTObooks(title, author, published_year)VA


Hot AI Tools

Undresser.AI Undress
AI-powered app for creating realistic nude photos

AI Clothes Remover
Online AI tool for removing clothes from photos.

Undress AI Tool
Undress images for free

Clothoff.io
AI clothes remover

Video Face Swap
Swap faces in any video effortlessly with our completely free AI face swap tool!

Hot Article

Hot Tools

Dreamweaver CS6
Visual web development tools

SAP NetWeaver Server Adapter for Eclipse
Integrate Eclipse with SAP NetWeaver application server.

MantisBT
Mantis is an easy-to-deploy web-based defect tracking tool designed to aid in product defect tracking. It requires PHP, MySQL and a web server. Check out our demo and hosting services.

Zend Studio 13.0.1
Powerful PHP integrated development environment

PhpStorm Mac version
The latest (2018.2.1) professional PHP integrated development tool