`
日光倾城。
  • 浏览: 85648 次
  • 性别: Icon_minigender_1
  • 来自: 南京
社区版块
存档分类
最新评论

sql语句优化原则与百万数据优化方案

阅读更多
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不仅可以加快查询速度,还能有效减少系统资源的消耗,提高系统可用性和响应速度。 一、SQL...

    SQL语句优化原则

    ### SQL语句优化原则 在IT领域中,数据库性能优化是一项极为重要的工作,而SQL查询作为数据检索的主要手段,其优化对于提升整个系统的响应速度和处理能力至关重要。下面将详细介绍SQL查询条件优化的30条原则,帮助...

    索引的SQL语句优化

    **基于索引的SQL语句优化**是提升数据库查询效率的有效手段之一,它通过合理设计和利用索引,减少数据检索的时间消耗,从而提升整体系统性能。 #### 核心概念与原则 **降龙十八掌**,这里被比喻成18条关键的优化...

    Oracle数据库的SQL语句优化 (1).pdf

    #### SQL语句优化原则 - 减少资源消耗:优化的目的在于减少CPU、内存、磁盘I/O等资源的消耗。 - 缩短响应时间:优化后的语句应能够提高响应速度,缩短用户等待时间。 - 数据访问模式优化:优化数据的读写方式,减少...

    基于Oracle数据库的SQL语句优化 (3).pdf

    文章《基于Oracle数据库的SQL语句优化》的作者张学琴来自北方民族大学,该文发表在《电脑知识与技术》期刊上,ISSN为1009-3044,详细介绍了针对Oracle数据库的SQL语句优化技术,以及如何通过优化查询语句来提升性能...

    ORACLE-SQL语句优化技术分析.docx

    ORACLE SQL 语句优化技术分析 一、问题的提出 在应用系统开发初期,由于开发数据库数据比较少,对于查询 SQL 语句,复杂视图的编写等体会不出 SQL 语句各种写法的性能优劣,但是如果将应用系统提交实际应用后,...

    再谈SQL语句的优化技术.pdf

    SQL语句优化是数据库性能调优的关键环节,尤其在面临系统性能问题时,优化SQL语句往往是提升系统效率的有效手段。本文主要探讨了如何通过简化SQL语句来提高执行效率,并强调了在系统设计阶段应考虑的问题。 首先,...

    SQL性能优化解决方案

    "SQL性能优化解决方案"着重关注如何发现并解决数据库系统的性能问题,这不仅涉及查询效率,还包括数据处理、存储和并发控制等多个方面。下面将详细介绍如何进行SQL性能优化,并以DMV(动态管理视图)脚本为例,阐述...

    sql语句性能调整原则

    在软件开发过程中,尤其是在系统规模逐渐扩大,数据库数据量激增的情况下,SQL语句的性能优化成为了提升系统响应速度的关键因素。虽然在开发初期,由于数据量较小,SQL语句的效率差异体现得并不明显,但一旦系统投入...

    SQL 优化原则

     在应用系统开发初期,由于开发数据库数据比较少,对于查询SQL语句,复杂视图的的编写等体会不出SQL语句各种写法的性能优劣,但是如果将应用系统提交实际应用后,随着数据库中数据的增加,系统的响应速度就成为目前...

    基于索引的SQL语句查询优化方法.pdf

    文章指出,许多程序员错误地认为查询优化是数据库管理系统(DBMS)的任务,而与他们编写的SQL语句关系不大。实际上,编写高效的SQL语句对于优化查询计划至关重要。DBMS在接收到查询语句后,会进行词法、语法检查,...

    编写优化的sql语句.

    【SQL语句优化】 在数据库管理系统中,SQL(Structured Query Language)是用于管理和处理数据库的主要工具。编写高效的SQL语句对于提升系统性能至关重要,尤其是在处理大量数据时。优化SQL语句能够减少查询时间,...

    SQL_Server_性能优化的原则

    ### SQL Server 性能优化的原则 #### 一、SQL Server 自调整特性 SQL Server 的版本迭代不断优化其自我管理和自我调整的能力。特别是从 SQL Server 7.0 开始,这一趋势更加明显。此版本提供了更高级别的自配置与自...

    基于索引的sql语句优化之降龙十八掌

    ### 基于索引的SQL语句优化之降龙十八掌 #### 1. 前言 在大规模的应用场景中,一个未优化的SQL语句可能会导致数据库性能显著下降,进而影响整个系统的稳定性和用户体验。为了应对这种挑战,我们需要掌握一系列针对...

    SQL语句查询性能优化[借鉴].pdf

    在应用系统开发初期,由于开发数据库数据比较少,对于查询 SQL 语句,索引的运用与复杂视图的编写等体会不出 SQL 语句各种写法的性能优劣,但是应用系统实际应用后,随着数据库中数据的增加,系统的响应速度就成为...

    SQL数据库优化大总结之百万级数据库优化方案

    SQL数据库优化是提升系统性能的关键环节,特别是在处理百万级乃至更大规模数据的场景下。本文主要针对Oracle数据库,但很多原则同样适用于其他SQL数据库系统,如MySQL和SQL Server。以下是一些关键的优化策略: 1. ...

    sql server学习手册以及t-sql优化方案.zip

    《SQL Server学习手册》与T-SQL优化方案详解 SQL Server 2008作为Microsoft公司的一款企业级数据库管理系统,广泛应用于数据存储、处理和分析。对于任何希望深入理解和掌握SQL Server的人来说,全面了解其基础知识...

    基于Informix数据库典型低效率SQL语句的分析及优化方法.pdf

    2. SQL语句优化策略 - 索引优化:确保经常用于查询的字段上有合适的索引,可以显著提高查询速度。避免在索引列上使用不等式操作符或函数,因为这可能导致无法使用索引。 - JOIN优化:合理设计JOIN条件,避免全表...

    SQL语句性能调整原则

    在IT领域,特别是数据库管理与优化方面,SQL语句的性能调整是一项至关重要的技能。良好的SQL语句不仅能够提高数据检索的速度,还能显著提升系统的响应时间和整体效率。以下是对SQL语句性能调整原则的详细解析,旨在...

    关系数据库SQL查询语句优化应用.pdf

    优化SQL查询语句应遵循以下原则: 1. 合理使用索引,避免索引浪费空间。 2. 保证返回值是查询所需要的,减少数据库访问次数。 3. 通过搜索参数减少访问行数,最小化结果集。 4. 使用索引时,将它们放在选择的首列。 ...

Global site tag (gtag.js) - Google Analytics