`

MySQL语句优化(转)

阅读更多
1、使用索引来更快地遍历表。

  缺省情况下建立的索引是非群集索引,但有时它并不是最佳的。在非群集索引下,数据在物理上随机存放在数据页上。合理的索引设计要建立在对各种查询的分析和预测上。一般来说:
  a.有大量重复值、且经常有范围查询( > ,< ,> =,< =)和order by、group by发生的列,可考虑建立群集索引;
  b.经常同时存取多列,且每列都含有重复值可考虑建立组合索引;
  c.组合索引要尽量使关键查询形成索引覆盖,其前导列一定是使用最频繁的列。索引虽有助于提高性能但不是索引越多越好,恰好相反过多的索引会导致系统低效。用户在表中每加进一个索引,维护索引集合就要做相应的更新工作。

  2、在海量查询时尽量少用格式转换。
  3、ORDER BY和GROPU BY使用ORDER BY和GROUP BY短语,任何一种索引都有助于SELECT的性能提高。

  4、任何对列的操作都将导致表扫描,它包括数据库函数、计算表达式等等,查询时要尽可能将操作移至等号右边。

  5、IN、OR子句常会使用工作表,使索引失效。如果不产生大量重复值,可以考虑把子句拆开。拆开的子句中应该包含索引。
  6、只要能满足你的需求,应尽可能使用更小的数据类型:例如使用MEDIUMINT代替INT

  7、尽量把所有的列设置为NOT NULL,如果你要保存NULL,手动去设置它,而不是把它设为默认值。
  8、尽量少用VARCHAR、TEXT、BLOB类型
  9、如果你的数据只有你所知的少量的几个。最好使用ENUM类型
  10、正如graymice所讲的那样,建立索引。

  以下是我做的一个实验,可以发现索引能极大地提高查询的效率:
我有一个会员信息表users,里边有37365条用户记录:
在不加索引的时候进行查询:
sql语句A:
select * from users where username like '%许%';
在Mysql-Front中的8次查询时长为:1.40,0.54,0.54,0.54,0.53,0.55,0.54 共找到960条记录
sql语句B:
select * from users where username like '许%';
在Mysql-Front中的8次查询时长为:0.53,0.53,0.53,0.54,0.53,0.53,0.54,0.54 共找到836条记录
sql语句C:
select * from users where username like '%许';
在Mysql-Front中的8次查询时长为:0.51,0.51,0.52,0.52,0.51,0.51,0.52,0.51 共找到7条记录
为username列添加索引:
create index usernameindex on users(username(6));
再次查询:
sql语句A:
select * from users where username like '%许%';
在Mysql-Front中的8次查询时长为:0.35,0.34,0.34,0.35,0.34,0.34,0.35,0.34 共找到960条记录
sql语句B:
select * from users where username like '许%';
在Mysql-Front中的8次查询时长为:0.06,0.07,0.07,0.07,0.07,0.07,0.06,0.06 共找到836条记录
sql语句C:
select * from users where username like '%许';
在Mysql-Front中的8次查询时长为:0.32,0.31,0.31,0.32,0.31,0.32,0.31,0.31 共找到7条记录

  在实验过程中,我没有另开任何程序,以上的数据说明在单表查询中,建立索引的可以极大地提高查询速度。

  另外要说的是如果建立了索引,对于like '许%'类型的查询,速度提升是最明显的。因此,我们在写sql语句的时候也尽量采用这种方式查询。

  对于多表查询我们的优化原则是:
  尽量将索引建立在:left join on/right join on ... +条件,的条件语句中所涉及的字段上。

  多表查询比单表查询更能体现索引的优势。

  11、索引的建立原则:
  如果一列的中数据的前缀重复值很少,我们最好就只索引这个前缀。Mysql支持这种索引。我在上面用到的索引方法就是对username最左边的6个字符进行索引。索引越短,占用的 磁盘空间越少,在检索过程中花的时间也越少。这方法可以对最多左255个字符进行索引。

  在很多场合,我们可以给建立多列数据建立索引。
  索引应该建立在查询条件中进行比较的字段上,而不是建立在我们要找出来并且显示的字段上
  12、一往情深问到的问题:IN、OR子句常会使用工作表,使索引失效。如果不产生大量重复值,可以考虑把子句拆开。拆开的子句中应该包含索引。

这句话怎么理解决,请举个例子
例子如下:
如果在fields1和fields2上同时建立了索引,fields1为主索引
以下sql会用到索引
select * from tablename1 where fields1='value1' and fields2='value2'
以下sql不会用到索引
select * from tablename1 where fields1='value1' or fields2='value2'
  13.索引带来查询上的速度的大大提升,但索引也占用了额外的硬盘空间(当然现在一般硬盘空间不成问题),而且往表中插入新记录时索引也要随着更新这也需要一定时间.
  有些表如果经常insert,而较少select,就不用加索引了.不然每次写入数据都要重新改写索引,花费时间; 这个视实际情况而定,通常情况下索引是必需的.

  14.我在对查询效率有怀疑的时候,一般是直接用Mysql的Explain来跟踪查询情况.
  你用Mysql-Front是通过时长来比较,我觉得如果从查询时扫描字段的次数来比较更精确一些.

 

分享到:
评论

相关推荐

    mysql语句转postgres的工具

    在这个过程中,`MysqlToPostgresUtil`工具扮演着重要的角色,它能帮助我们将MySQL的DDL(Data Definition Language)语句转换为PostgreSQL兼容的格式,从而简化迁移过程。 MySQL和PostgreSQL虽然都是关系型数据库...

    Mysql的表对象Sql语句转换单表,转换成Oracle创建表sql

    标题提到的"‘Mysql的表对象Sql语句转换单表,转换成Oracle创建表sql’"是一个处理这种转换的工具或过程。 首先,让我们理解这个过程的一般步骤: 1. **数据迁移需求分析**:在开始转换之前,了解源MySQL表的结构...

    Effective MySQL之SQL语句最优化.pdf

    10. SQL语句优化的技术手段:技术手段包括但不限于使用子查询优化、使用JOIN代替子查询、避免SELECT *、使用更有效的查询方法(如IN代替OR)、利用数据库提供的存储过程和函数减少网络往返次数等。 11. 经验与实践...

    sql语句优化 mssql优化 mysql优化 oracle优化

    sql语句优化.chmmssql优化 mysql优化 oracle优化

    Mysql转oracle工具

    "mysqltooracle_cn"这个工具可能包含了自动化的转换功能,它能帮助解析MySQL的DDL语句并生成相应的Oracle DDL,同时也可能包含数据迁移的逻辑。 在实际操作中,应先备份源数据库,然后根据工具的使用指南逐步进行...

    【36】最新精选蚂蚁-MySQL语句性能优化视频教程下载 .txt

    根据提供的文件信息,我们可以推断出本教程主要围绕MySQL语句性能优化展开,这是一项非常重要的数据库管理技能,尤其对于那些处理大量数据的应用程序来说至关重要。下面将详细阐述与MySQL语句性能优化相关的知识点。...

    java实体转mysql建表语句

    在Java开发中,将Java实体类转换为MySQL数据库的建表语句是一项常见的任务,它有助于快速构建数据库模型,尤其在使用ORM(对象关系映射)框架如Hibernate、MyBatis时更为便捷。本篇文章将深入探讨这个过程,并提供...

    【No206】最新精选蚂蚁-MySQL语句性能优化视频教程下载

    ### MySQL语句性能优化知识点详解 #### 一、MySQL简介及优化的重要性 MySQL是一种广泛使用的开源关系型数据库管理系统(RDBMS),因其高效性、稳定性和可扩展性而在各种应用场景中备受青睐。随着数据量的增长和...

    mysql优化sql语句的优化(索引,常用小技巧.)

    综上所述,MySQL数据库的优化涉及到多个方面,包括数据库设计、SQL语句优化、数据配置以及硬件与操作系统配置等。通过综合运用这些优化方法,可以有效提升数据库系统的性能和稳定性,为用户提供更好的服务体验。

    Effective MySQL之SQL语句最优化(高清)

    《Effective MySQL之SQL语句最优化》提供了很多可以用于... ●用SQL语句优化的生命周期来识别、确认、分析然后优化SQL语句,并检查优化的结果;  ●学习使用不为常人所知的一些性能技巧来改进索引效率并简化SQL语句。

    mysql 数据库转 oracle

    MySQL到Oracle的数据迁移是一项常见的任务,特别是在企业级应用中,可能因为各种原因需要从MySQL迁移到功能更强大的Oracle数据库系统。下面将详细讲解这个过程涉及的关键知识点。 首先,我们需要了解MySQL和Oracle...

    MySQL数据库优化SQL篇PPT课件.pptx

    二、SELECT语句优化 SELECT语句是MySQL数据库中最常用的语句之一。SELECT语句的优化是非常重要的。通过优化SELECT语句,可以提高数据库的性能和效率。 三、IN和EXIST语句 IN和EXIST语句是MySQL数据库中常用的语句...

    oracle 到mysql转换工具

    用户可以检查并优化这些SQL语句,确保在MySQL环境中正确执行。 6. 导出与执行:最后,用户可以选择将SQL脚本导出为文本文件,或者直接在MySQL服务器上执行,完成数据迁移。 在实际操作中,需要注意的是数据完整性...

    千金良方:MySQL性能优化金字塔法则.docx

    MySQL性能优化是指通过调整MySQL数据库的配置、优化数据库结构和查询语句等方式,提高MySQL数据库的性能和响应速度,以满足应用程序的需求。MySQL作为最流行的开源数据库之一,被广泛应用于各种规模的企业和应用程序...

    百度mysql性能优化ppt

    【标题】:“百度mysql性能优化ppt”所涉及的知识点涵盖了MySQL数据库在性能调优方面的多个重要环节。在MySQL性能优化中,我们关注的核心是提升数据处理速度,减少资源消耗,从而提高系统的整体效率。 【描述】:...

    mysql优化、sql语句

    【MySQL优化和SQL语句详解】 在软件开发中,MySQL是一个不可或缺的数据库管理系统,它以其高效、稳定和开源的特点,广泛应用于各种规模的应用程序。掌握MySQL优化和SQL语句的使用,对于提升应用性能和效率至关重要...

    提升MYSQL查询效率的10个SQL语句优化技巧.doc

    以下是十个 SQL 语句优化技巧来提升 MYSQL 查询效率: 1. 优化 MySQL 查询缓存 MySQL 查询缓存可以启用高速查询缓存,让数据库引擎在后台悄悄的处理是提高性能的最有效方法之一。当同一个查询被执行多次时,如果...

    SQL Server转换为MySQL工具

    有时,由于业务迁移、成本优化或者技术栈的变化,用户可能需要将SQL Server的数据转换到MySQL中。这时,“SQL Server转换为MySQL工具”就显得尤为重要。 这个工具的主要功能就是帮助用户高效、准确地将SQL Server中...

    mysql性能优化.pptx

    MySQL性能优化是一个涵盖广泛的主题,涉及多个层面,包括SQL语句优化、索引优化、数据库表结构优化、系统级配置优化以及服务器硬件优化。以下是对这些方面进行详细说明: 1. **SQL语句优化** - **慢查询日志**:...

    Effective MySQL之SQL语句最优化

    3. **查询语句优化**:理解SQL执行计划至关重要,书中可能会教你如何使用EXPLAIN分析查询执行过程。此外,避免在WHERE子句中使用函数,以及合理处理NULL值,也是优化查询的重要技巧。 4. **存储引擎的选择**:MySQL...

Global site tag (gtag.js) - Google Analytics