文档章节

mysql查询优化以及索引使用

n
 noct1
发布于 2017/07/29 20:43
字数 2144
阅读 4
收藏 0

如果应用程序使用了MySQL,其中包含一些查询速度慢的sql,我们要去优化它们,优化的思路需要如何进行呢?主要是以下两点:

1、应用程序是否在检索大量超过需要的数据(行、列);

2、mysql服务器层是否在分析大量超过需要的数据行。

前者比较好进行,主要看开发者的细心以及缜密逻辑、流程分析;后者就需要一些数据库方面的知识、优化以及实践技巧。对于后一点,首先需要分清一个概念,就是扫描行数与返回行数的区别,后者是我们实际取得的数据,而前者是mysql得出后者所需要扫描的数据量。

如果发现查询大量的数据但只返回少数的行,那么通常可以尝试下面的技巧:1、使用索引覆盖索引;2、改变库表结构,如使用单独的汇总表;3、重写这个复杂的查询。

下面针对查询优化提供一些技巧:

1、分解关联查询。

这个主要针对这种查询:关联查询了多个表,这种情况下可能出现原本可以通过索引实现的order by失效,数据需要在到达mysql服务器后再进行排序;并且多表关联,mysql实现的方式是一次扫描取一个表的数据,最后再处理合并,这些都需要消耗mysql的资源。当然,关联查询也有一些好处,比如只需要访问一次mysql,减少网络请求。当弊大于利时,我们可以采取这样的优化措施,将主表的数据先查询出来,其他一些信息,在代码里拼凑好条件,一次性查询出来,再进行属性合并等操作。

 

2、当表a和表b用列c关联,如果优化器的关联顺序是b、a,那么就不需要在b的对应列上建立索引

3、确保任何group by和order by中的表达式只涉及一个表中的列(最好是优化器扫描的第一个表中的),这样mysql才能使用索引来优化过程

4、group by表达式,如果没有显式的order by表达式,默认会对后面的字段进行排序,如果排序字段没有用上索引,将是一个很大的性能消耗,尤其当有联表时,需要通过临时表(using temporary)实现。一个优化的技巧,是加上order by null。

 

4、mysql总是通过创建并填充临时表的方式来执行union查询。因此很多优化策略在union查询中都没法很好地使用,经常需要手工地将where limit order by 等子句"下推"到union的各子查询中,以便优化器可以充分利用这些条件进行优化。

除非确实需要服务器消除重复的行,否则就一定要使用union all。原因是union操作需要取出两个表的数据,通过排序排除重复的行,会消耗mysql资源,如果数据量大的还要用到磁盘排序。

5、尽量使用update(通过条件过滤来保证数据一致性等)代替先select for update再update的写法,因为事务提交的速度越快,持有的锁时间就越短,可以大大减少竞争和加速串行执行效率。

 

6、有些查询是无法优化的,可以考虑使用别的查询或者策略来实现相同的目的。

7、通过近似计算等方法,先过滤缩小范围(使用索引),然后再精确过滤。(这种是精确过滤用不上索引时的处理策略)

8、需要的时候,尽可能让程序完成一些计算。(比如结果集中字符串的拼拼凑凑)

 

    在优化查询的过程中,索引的建立、使用扮演着非常重要的角色。建立索引时需要全局考虑所有的查询,而不仅仅是当前要处理、优化的查询,不能因为要优化当前的查询而严重影响其他查询的执行效率。建立索引时需要考虑两点:1、出现频率高的查询条件及其顺序,2、索引列的选择性,要讲选择性高的列放到索引的最前列。索引列的选择性是指:不重复的索引值和数据表的记录总数的比值,选择性越高则查询效率越高。通常主要考虑第一点,因为它对查询的效率影响较大。

冗余索引,是指这种情况,index1(a),index2(a,b),index1是index2的最左前缀,它的作用也就可以被index2来代替(在使用b-tree索引的时候)。大多数情况下都不需要冗余索引,应该尽量尽量扩展已有的索引而不是创建新索引。但也有时候处于性能方面的考虑需要冗余索引,因为扩展已有的索引会导致其变得太大,从而影响其他使用该索引的查询性能。

    索引使用中需要注意:

1、只有当索引的列顺序和order by子句的顺序完全一致,并且所有列的排序方向(倒序或正序)都一样时,mysql才能够使用索引对结果做排序。如果查询需要关联多张表,则只有当order by子句引用的字段全部为第一个表(mysql优化器优化后实际执行时的第一个表)时,才能使用索引做排序。

order by子句和查找型查询的限制是一样的,需要满足索引的最左前缀的要求;否则mysql都需要执行排序操作,无法利用索引排序。有一种情况,order by子句可以不满足索引的最左前缀的要求;就是前导列为常量的时候。如果where子句或者join子句对这些列指定了常量,就可以弥补索引的不足。

2、对于一个表的一次扫描中最多只能用到它的一个索引

3、尽量将需要做范围查询的列放到索引的后面,以便优化器能够使用尽可能多的索引列

 

 松散索引扫描与紧凑索引扫描:

两者的区别:在松散索引扫描方式下,分组操作和范围预测(如果有的话)一起执行完成。在紧凑索引扫描下,先对索引执行范围扫描(range search),再对结果元组进行分组。

松散索引扫描的条件:

1)、查询在单一表上

2)、group by指定的列是索引的一个最左前缀,并且没有其他的列

3)、如果在选择列表select list中存在聚合函数,只能使用min()和max()两个聚合函数,并且指定的是同一列

4)、如果查询中存在除了group by指定的列之外的索引其他部分,那么必须以常量的形式出现

5)、索引中的列必须索引整个数据列的值,而不是一个前缀索引(注意不是索引前缀,前缀索引是指索引中的某些列不是某个字段,而是某个字段的前缀部分)

从5.5开始,松散索引的扫描条件放宽了:

1)、select中的聚合函数除了min()和max()之外,还支持avg(distinct)、sum(distinct)、count(distinct)

2)、查询中没有group by和distinct条件

判断一个查询是否使用松散索引扫描的方法:执行计划中有using index for group-by

 

紧凑索引扫描起作用的条件:

在查询中存在常量相等等where条件字段(索引中的字段),且该字段在group by指定的字段的前面或者中间。来自相等条件的常量能够填充搜索keys中的gaps,因而能够构成一个索引的完整前缀。索引前缀能够用户索引查找。如果要求对group by的结果进行排序,并且查找字段组成一个索引前缀,那么mysql同样可以避免额外的排序操作。

查询使用了紧凑索引扫描的判断方法:执行计划中有using index

© 著作权归作者所有

共有 人打赏支持
n
粉丝 0
博文 6
码字总数 2915
作品 0
MySQL 索引选择原则

目的 MySQL查询优化器是基于代价(cost-based)的查询方式。因此,在查询过程中,最重要的一部分是根据查询的SQL语句,依据多种索引,计算查询需要的代价,从而选择最优的索引方式生成查询计...

tara_qri
2015/09/24
62
0
SQL-SQL优化-索引

图文并茂详解 SQL JOIN Join 是关系型数据库系统的重要操作之一,一般关系型数据库中包含的常用 Join:内联接、外联接和交叉联接等。如果我们想在两个或以上的表获取其中从一个表中的行与另一...

掘金官方
2017/12/25
0
0
详解MySQL中EXPLAIN解释命令

explain显示了mysql如何使用索引来处理select语句以及连接表。可以帮助选择更好的索引和写出更优化的查询语句。 使用方法,在select语句前加上explain就可以了: 如: explain select surna...

Lydialyd
06/26
0
0
MySQL中EXPLAIN命令详解

EXPLAIN显示了MySQL如何使用索引来处理SELECT语句以及连接表。可以帮助选择更好的索引和写出更优化的查询语句。 使用方法,在select语句前加上EXPLAIN就可以了。 EXPLAIN列的解释: 列 描述 ...

Jackin
2015/03/30
0
0
mysql使用索引优化order排序

个人原创地址:http://www.phpthinking.com/archives/433 在数据库中我们一般都会对一些字段进行索引操作,这样可以提升数据的查询速度,同时提高数据库的性能像order by ,group by前都需要索...

PHP开发学习门户
2014/09/25
0
0

没有更多内容

加载失败,请刷新页面

加载更多

可爱的python测试开发库(python测试开发工具库汇总)

欢迎转载,转载请注明来源: github地址 谢谢点赞 本文地址 相关书籍下载 测试开发 Web UI测试自动化 splinter - web UI测试工具,基于selnium封装。 链接 selenium - web UI自动化测试。 链...

python测试开发人工智能安全
今天
2
0
Shiro | 实现权限验证完整版

写在前面的话 提及权限,就会想到安全,是一个十分棘手的话题。这里只是作为学校Shiro的一个记录,而不是,权限就应该这样设计之类的。 Shiro框架 1、Shiro是基于Apache开源的强大灵活的开源...

冯文议
今天
1
0
linux 系统的运行级别

运行级别 运行级别 | 含义 0 关机 1 单用户模式,可以想象为windows 的安全模式,主要用于修复系统 2 不完全的命令模式,不含NFS服务 3 完全的命令行模式,就是标准的字符界面 4 系统保留 5 ...

Linux学习笔记
今天
2
0
学习设计模式——命令模式

任何模式的出现,都是为了解决一些特定的场景的耦合问题,以达到对修改封闭,对扩展开放的效果。命令模式也不例外: 命令模式是为了解决命令的请求者和命令的实现者之间的耦合关系。 解决了这...

江左煤郎
今天
3
0
字典树收集(非线程安全,后续做线程安全改进)

将500W个单词放进一个数据结构进行存储,然后进行快速比对,判断一个单词是不是这个500W单词之中的;来了一个单词前缀,给出500w个单词中有多少个单词是该前缀. 1、这个需求首先需要设计好数据结...

算法之名
昨天
15
0

没有更多内容

加载失败,请刷新页面

加载更多

返回顶部
顶部