1、使用索引来更快地遍历表。
缺省情况下建立的索引是非群集索引,但有时它并不是最佳的。在非群集索引
下,数据在物理上随机存放在数据页上。合理的索引设计要建立在
对各种查询的分析和预测上。一般来说:
a.有大量重复值、且经常有范围查询( > ,< ,> =,< =)和order by、group by发生的列,可考
虑建立群集索引;
b.经常同时存取多列,且每列都含有重复值可考虑建立组合索引;
c.组合索引要尽量使关键查询形成索引覆盖,其前导列一定是使用最频繁的列。索引虽有助于提高性能但不是索引越多越好,恰好相反过多的索引会导致系统低效。用户在表中每加进一个索引,维护索引集合就要做相应的更新工作。
2、在海量查询时尽量少用格式转换。
3、ORDER BY和GROPU BY使用ORDER BY和GROUP BY短语,任何一种索引都有助于SELECT的性能提高。
4、任何对列的操作都将导致表扫描,它包括数据库教程函数、计算表达式等等,查询时要尽可能将操作移至等号右边。
5、IN、OR子句常会使用工作表,使索引失效。如果不产生大量重复值,可以考虑把子句拆开。拆开的子句中应该包含索引。
Mysql的优化原则2:
1、只要能满足你的需求,应尽可能使用更小的数据类型:例如使用MEDIUMINT代替INT
2、尽量把所有的列设置为NOT NULL,如果你要保存NULL,手动去设置它,而不是把它设为默认值。
3、尽量少用VARCHAR、TEXT、BLOB类型
4、如果你的数据只有你所知的少量的几个。最好使用ENUM类型
5、正如graymice所讲的那样,建立索引。
方法二
优化前:A表数据造成冗余
SELECT `T`.`img_id`, `T`.`thumb_path`
FROM `gallery_photofiles` P
LEFT JOIN `gallery_thumbs` T ON `T`.`img_id`=`P`.`img_id` and T.thumb_type='11'
WHERE `P`.`owner_user_id` = '1'
AND P.img_id in (select A.img_id from `gallery_album_img_link` A WHERE A.img_id)
优化后:count(*)大大提升速度
SELECT `T`.`img_id`, `T`.`thumb_path`
FROM `gallery_photofiles` P
LEFT JOIN `gallery_thumbs` T ON `T`.`img_id`=`P`.`img_id` and T.thumb_type='11'
WHERE `P`.`owner_user_id` = '1'
AND (select count(*) from `gallery_album_img_link` A WHERE A.img_id=P.img_id)<1
一直以为mysql教程随机查询几条数据,就用
SELECT * FROM `table` ORDER BY RAND() LIMIT 5
就可以了。
但是真正测试一下才发现这样效率非常低。一个15万余条的库,查询5条数据,居然要8秒以上
查看官方手册,也说rand()放在ORDER BY 子句中会被执行多次,自然效率及很低。
You cannot use a column with RAND() values in an ORDER BY clause, because ORDER BY would evaluate the column multiple times.
搜索Google,网上基本上都是查询max(id) * rand()来随机获取数据。
SELECT *
FROM `table` AS t1 JOIN (SELECT ROUND(RAND() * (SELECT MAX(id) FROM `table`)) AS id) AS t2
WHERE t1.id >= t2.id
ORDER BY t1.id ASC LIMIT 5;
但是这样会产生连续的5条记录。解决办法只能是每次查询一条,查询5次。即便如此也值得,因为15万条的表,查询只需要0.01秒不到。
上面的语句采用的是JOIN,mysql的论坛上有人使用
SELECT *
FROM `table`
WHERE id >= (SELECT FLOOR( MAX(id) * RAND()) FROM `table` )
ORDER BY id LIMIT 1;
我测试了一下,需要0.5秒,速度也不错,但是跟上面的语句还是有很大差距。总觉有什么地方不正常。
于是我把语句改写了一下。
SELECT * FROM `table`
WHERE id >= (SELECT floor(RAND() * (SELECT MAX(id) FROM `table`)))
ORDER BY id LIMIT 1;
这下,效率又提高了,查询时间只有0.01秒
最后,再把语句完善一下,加上MIN(id)的判断。我在最开始测试的时候,就是因为没有加上MIN(id)的判断,结果有一半的时间总是查询到表中的前面几行。
完整查询语句是:
SELECT * FROM `table`
WHERE id >= (SELECT floor( RAND() * ((SELECT MAX(id) FROM `table`)-(SELECT MIN(id) FROM `table`)) + (SELECT MIN(id) FROM `table`)))
ORDER BY id LIMIT 1;SELECT *
FROM `table` AS t1 JOIN (SELECT ROUND(RAND() * ((SELECT MAX(id) FROM `table`)-(SELECT MIN(id) FROM `table`))+(SELECT MIN(id) FROM `table`)) AS id) AS t2
WHERE t1.id >= t2.id
ORDER BY t1.id LIMIT 1;
分享到:
相关推荐
在开发初期,由于数据量较小,可能无法明显察觉到SQL语句的性能差异,但随着数据的增长,优化SQL的重要性日益凸显。优化SQL不仅可以加快查询速度,还能有效减少系统资源的消耗,提高系统可用性和响应速度。 一、SQL...
### SQL语句优化原则 在IT领域中,数据库性能优化是一项极为重要的工作,而SQL查询作为数据检索的主要手段,其优化对于提升整个系统的响应速度和处理能力至关重要。下面将详细介绍SQL查询条件优化的30条原则,帮助...
**基于索引的SQL语句优化**是提升数据库查询效率的有效手段之一,它通过合理设计和利用索引,减少数据检索的时间消耗,从而提升整体系统性能。 #### 核心概念与原则 **降龙十八掌**,这里被比喻成18条关键的优化...
#### SQL语句优化原则 - 减少资源消耗:优化的目的在于减少CPU、内存、磁盘I/O等资源的消耗。 - 缩短响应时间:优化后的语句应能够提高响应速度,缩短用户等待时间。 - 数据访问模式优化:优化数据的读写方式,减少...
文章《基于Oracle数据库的SQL语句优化》的作者张学琴来自北方民族大学,该文发表在《电脑知识与技术》期刊上,ISSN为1009-3044,详细介绍了针对Oracle数据库的SQL语句优化技术,以及如何通过优化查询语句来提升性能...
ORACLE SQL 语句优化技术分析 一、问题的提出 在应用系统开发初期,由于开发数据库数据比较少,对于查询 SQL 语句,复杂视图的编写等体会不出 SQL 语句各种写法的性能优劣,但是如果将应用系统提交实际应用后,...
SQL语句优化是数据库性能调优的关键环节,尤其在面临系统性能问题时,优化SQL语句往往是提升系统效率的有效手段。本文主要探讨了如何通过简化SQL语句来提高执行效率,并强调了在系统设计阶段应考虑的问题。 首先,...
"SQL性能优化解决方案"着重关注如何发现并解决数据库系统的性能问题,这不仅涉及查询效率,还包括数据处理、存储和并发控制等多个方面。下面将详细介绍如何进行SQL性能优化,并以DMV(动态管理视图)脚本为例,阐述...
在软件开发过程中,尤其是在系统规模逐渐扩大,数据库数据量激增的情况下,SQL语句的性能优化成为了提升系统响应速度的关键因素。虽然在开发初期,由于数据量较小,SQL语句的效率差异体现得并不明显,但一旦系统投入...
在应用系统开发初期,由于开发数据库数据比较少,对于查询SQL语句,复杂视图的的编写等体会不出SQL语句各种写法的性能优劣,但是如果将应用系统提交实际应用后,随着数据库中数据的增加,系统的响应速度就成为目前...
文章指出,许多程序员错误地认为查询优化是数据库管理系统(DBMS)的任务,而与他们编写的SQL语句关系不大。实际上,编写高效的SQL语句对于优化查询计划至关重要。DBMS在接收到查询语句后,会进行词法、语法检查,...
【SQL语句优化】 在数据库管理系统中,SQL(Structured Query Language)是用于管理和处理数据库的主要工具。编写高效的SQL语句对于提升系统性能至关重要,尤其是在处理大量数据时。优化SQL语句能够减少查询时间,...
### SQL Server 性能优化的原则 #### 一、SQL Server 自调整特性 SQL Server 的版本迭代不断优化其自我管理和自我调整的能力。特别是从 SQL Server 7.0 开始,这一趋势更加明显。此版本提供了更高级别的自配置与自...
### 基于索引的SQL语句优化之降龙十八掌 #### 1. 前言 在大规模的应用场景中,一个未优化的SQL语句可能会导致数据库性能显著下降,进而影响整个系统的稳定性和用户体验。为了应对这种挑战,我们需要掌握一系列针对...
在应用系统开发初期,由于开发数据库数据比较少,对于查询 SQL 语句,索引的运用与复杂视图的编写等体会不出 SQL 语句各种写法的性能优劣,但是应用系统实际应用后,随着数据库中数据的增加,系统的响应速度就成为...
SQL数据库优化是提升系统性能的关键环节,特别是在处理百万级乃至更大规模数据的场景下。本文主要针对Oracle数据库,但很多原则同样适用于其他SQL数据库系统,如MySQL和SQL Server。以下是一些关键的优化策略: 1. ...
《SQL Server学习手册》与T-SQL优化方案详解 SQL Server 2008作为Microsoft公司的一款企业级数据库管理系统,广泛应用于数据存储、处理和分析。对于任何希望深入理解和掌握SQL Server的人来说,全面了解其基础知识...
2. SQL语句优化策略 - 索引优化:确保经常用于查询的字段上有合适的索引,可以显著提高查询速度。避免在索引列上使用不等式操作符或函数,因为这可能导致无法使用索引。 - JOIN优化:合理设计JOIN条件,避免全表...
在IT领域,特别是数据库管理与优化方面,SQL语句的性能调整是一项至关重要的技能。良好的SQL语句不仅能够提高数据检索的速度,还能显著提升系统的响应时间和整体效率。以下是对SQL语句性能调整原则的详细解析,旨在...
优化SQL查询语句应遵循以下原则: 1. 合理使用索引,避免索引浪费空间。 2. 保证返回值是查询所需要的,减少数据库访问次数。 3. 通过搜索参数减少访问行数,最小化结果集。 4. 使用索引时,将它们放在选择的首列。 ...