`

讲解MySQL建立索引 优化ORDER BY语句

阅读更多

讲解MySQL建立索引 优化ORDER BY语句

作者: qqread,  出处:IT专家网论坛, 责任编辑: 陈子琪, 
2010-03-17 13:00
  当你的程序和数据库结构/SQL语句已经优化到无法优化的程度,而程序瓶颈并不能顺利解决,那就是应该考虑使用诸如memcached这样的分布式缓存系统的时候了。

  关于建立索引的几个准则:

  1、合理的建立索引能够加速数据读取效率,不合理的建立索引反而会拖慢数据库的响应速度。

  2、索引越多,更新数据的速度越慢。

  3、尽量在采用MyIsam作为引擎的时候使用索引(因为MySQL以BTree存储索引),而不是InnoDB。但MyISAM不支持Transcation。

  4、当你的程序和数据库结构/SQL语句已经优化到无法优化的程度,而程序瓶颈并不能顺利解决,那就是应该考虑使用诸如memcached这样的分布式缓存系统的时候了。

  5、习惯和强迫自己用EXPLAIN来分析你SQL语句的性能。

  一个很容易犯的错误:

  不要在选择的栏位上放置索引,这是无意义的。应该在条件选择的语句上合理的放置索引,比如where,order by。

  例子:


  SELECT id,title,content,cat_id FROM article WHERE cat_id = 1;

  上面这个语句,你在id/title/content上放置索引是毫无意义的,对这个语句没有任何优化作用。但是如果你在外键cat_id上放置一个索引,那作用就相当大了。

  几个常用ORDER BY语句的MySQL优化:

  1、ORDER BY + LIMIT组合的索引优化。如果一个SQL语句形如:


  SELECT [column1],[column2],.... FROM [TABLE] ORDER BY [sort] LIMIT [offset],[LIMIT];

  这个SQL语句优化比较简单,在[sort]这个栏位上建立索引即可。

  2、WHERE + ORDER BY + LIMIT组合的索引优化,形如:


  SELECT [column1],[column2],.... FROM [TABLE] WHERE [columnX] = [value] ORDER BY [sort] LIMIT[offset],[LIMIT];

  这个语句,如果你仍然采用第一个例子中建立索引的方法,虽然可以用到索引,但是效率不高。更高效的方法是建立一个联合索引(columnX,sort)

3、WHERE + IN + ORDER BY + LIMIT组合的索引优化,形如:


  SELECT [column1],[column2],.... FROM [TABLE] WHERE [columnX] IN ([value1],[value2],...) ORDER BY[sort] LIMIT [offset],[LIMIT];

  这个语句如果你采用第二个例子中建立索引的方法,会得不到预期的效果(仅在[sort]上是using index,WHERE那里是using where;using filesort),理由是这里对应columnX的值对应多个。

  这个语句怎么优化呢?我暂时没有想到什么好的办法,看到网上有便宜提供的办法,那就是将这个语句用UNION分拆,然后建立第二个例子中的索引:


  SELECT [column1],[column2],.... FROM [TABLE] WHERE [columnX]=[value1] ORDER BY [sort] LIMIT[offset],[LIMIT]
  UNION
  SELECT [column1],[column2],.... FROM [TABLE] WHERE [columnX]=[value2] ORDER BY [sort] LIMIT[offset],[LIMIT]
  UNION
  ……

  但经验证,这个方法根本行不通,效率反而更低,测试时对于大部分应用强制指定使用排序索引效果更好点

  4、不要再WHERE和ORDER BY的栏位上应用表达式(函数),比如:


  SELECT * FROM [table] ORDER BY YEAR(date) LIMIT 0,30;

  5、WHERE+ORDER BY多个栏位+LIMIT,比如


  SELECT * FROM [table] WHERE uid=1 ORDER x,y LIMIT 0,10;

  对于这个语句,大家可能是加一个这样的索引(x,y,uid)。但实际上更好的效果是(uid,x,y)。这是由MySQL处理排序的机制造成的。

  以上例子你在实际项目中应用的时候,不要忘记在添加索引后,用EXPLAIN看看效果。 

分享到:
评论

相关推荐

    mysql数据库以及索引详解.pptx

    - 经常出现在WHERE或ORDER BY语句中的列。 - 用于排序的列。 - 查询中与其他表关联的字段。 - 高并发环境下倾向于使用组合索引。 - 聚合函数的列(例如使用MAX(column_1)或COUNT(column_1)时的column_1)。 ##### ...

    mysql性能优化视频教程

    3. **使用索引辅助分页**:通过在排序字段上建立索引来提高分页效率。 4. **优化分页逻辑**:例如使用基于偏移量的分页或基于游标的分页。 #### 四、主从复制集群配置 1. **选择合适的复制模式**:同步复制可以...

    很详细的mysql课程讲解

    1. SELECT语句:深入学习SELECT语句的使用,包括选择列、分组数据(GROUP BY)、聚合函数(COUNT、SUM、AVG、MAX、MIN)、排序(ORDER BY)、分页(LIMIT)等。 2. 联接查询:理解内连接(INNER JOIN)、外连接...

    mysql MySQL数据库开发优化与管理维护

    - **MySQL如何使用索引**:讲解MySQL内部如何利用索引来优化查询速度。 ##### 第6章 锁机制和事务控制 - **如何加锁**:介绍不同类型的锁及其加锁方法。 - **死锁**:讨论死锁产生的原因及其解决策略。 - **事务...

    SQL语句执行深入讲解(MySQL架构总览-查询执行流程-SQL解析顺序)

    3. 对于SELECT查询,MySQL会使用查询优化器生成执行计划,考虑各种可能的执行路径,如索引选择、连接顺序、子查询优化等,以达到最快的执行速度。 4. 接下来,执行计划会被执行,访问控制模块检查用户权限,表管理...

    第10章_索引优化与查询优化.pdf

    - **使用LIMIT优化分页查询**:在进行分页查询时,使用`LIMIT`配合`ORDER BY`可以限制返回结果的数量,但应尽量避免在`ORDER BY`后的字段上没有索引,这可能导致全表排序。 - **避免在WHERE子句中使用NOT IN、!=、...

    mySQL的常用语句

    本文将深入讲解MySQL的一些常用语句,包括创建表、创建索引、修改表结构、删除数据对象以及执行查询。 1. 创建表 创建表是数据库设计的第一步,用于定义数据的结构。基本语法如下: ```sql CREATE TABLE table_name...

    mysql入门的资料ppt

    MySQL的查询包括SELECT、FROM、WHERE、GROUP BY、HAVING、ORDER BY等。MySQL的索引包括普通索引、唯一索引、全文索引等。MySQL的事务包括事务的ACID属性、事务的隔离级别、事务的提交和回滚等。 MySQL的高级特性 ...

    mysql数据库基础全部课件

    3. 查询语言(SELECT):深入理解查询语句,包括WHERE、ORDER BY、GROUP BY、HAVING等子句的用法。 五、索引与优化 1. 索引的概念:解释索引的作用,分析B-Tree、Hash、Full-text等不同类型索引的工作原理。 2. ...

    mysql入门只是讲解.md

    - **基本查询语句**:熟悉`SELECT`语句及其子句(如`FROM`、`WHERE`、`ORDER BY`等),了解如何选择数据并对其进行排序、分组和筛选。 - **连接操作**:学会在多个表格间建立关联。掌握内连接、左连接、右连接和全...

    mysql数据【mysql必知必会】pdf

    4. **数据操作**:深入理解如何在MySQL中进行数据的增删改查,例如使用WHERE子句筛选数据,使用ORDER BY进行排序,GROUP BY进行分组,以及HAVING子句进行条件过滤。 5. **事务处理**:MySQL支持ACID(原子性、一致...

    MySQL必知必会带书签电子书源码

    这可能涉及到索引优化、查询语句优化、内存配置优化、分区表、存储引擎选择等多个方面。例如,InnoDB存储引擎支持行级锁定,适合并发操作;MyISAM则以快速读取和小文件尺寸为特点,但不支持事务。 通过学习这本书,...

    MySQL.rar_MYSQL_Mysql入门

    7. 数据查询:深入理解JOIN操作,子查询,聚合函数(COUNT、SUM、AVG、MAX、MIN),排序(ORDER BY)、分组(GROUP BY)和筛选(HAVING)。 四、数据库设计 8. 第三方范式:了解第一范式(1NF)、第二范式(2NF)、...

    MySQL数据库基础与实例教程所有资源

    INSERT INTO语句用于插入新记录,DELETE FROM和UPDATE用于删除和修改记录,而SELECT是最常用的查询语句,可以搭配WHERE、GROUP BY、HAVING、ORDER BY和JOIN子句进行复杂查询。在实际应用中,理解这些语句的用法至关...

    MySQL数据库基础实例教程(第2版)(微课版)-教学课件.zip

    第五章 数据查询:深入研究复杂的SQL查询技术,如子查询、聚合函数、排序(ORDER BY)、分页(LIMIT)等。此外,还会涉及连接操作的优化,如使用索引来提升查询性能。 第六章 数据视图:讲解视图的概念,如何创建和...

    MySQL必知必会

    6. 数据查询:这是SQL语言中最为核心的部分,本书会对SELECT语句及其各种子句进行详细讲解,包括WHERE、JOIN、GROUP BY、ORDER BY等子句的使用,以及聚合函数的使用方法。 7. 事务处理:事务是数据库管理系统中非常...

    mysql api学习手册

    6. **查询数据**:讲解如何使用API执行复杂的查询,包括JOIN、WHERE子句、GROUP BY和HAVING子句、ORDER BY和LIMIT子句等。 7. **数据插入与更新**:详述INSERT、INSERT INTO...VALUES和INSERT INTO...SELECT等插入...

    mysql入门自学书籍

    6. **查询与聚合函数**:深入理解SELECT语句的高级用法,如WHERE子句、ORDER BY、GROUP BY、HAVING和JOIN操作。同时,学习使用聚合函数(COUNT、SUM、AVG、MIN、MAX)对数据进行统计分析。 7. **事务处理**:理解...

    某培训机构的Mysql基础教程

    4. **查询数据**:这是SQL的核心,涵盖了`SELECT`语句的使用,包括选择特定列、过滤数据(WHERE子句)、排序结果(ORDER BY子句)、分组数据(GROUP BY子句)和聚合函数(如COUNT、SUM、AVG等)。 5. **更新与删除...

    php Mysql 开发 字典 大全

    3. **SQL基础**:掌握SQL语言的基本语法,如SELECT、INSERT、UPDATE、DELETE等操作,以及JOIN、WHERE、GROUP BY、ORDER BY等子句。 4. **PHP处理SQL结果**:学会使用PHP的fetch_array()函数等方法获取和遍历查询...

Global site tag (gtag.js) - Google Analytics