SQL

优化的一些手段#xff0c;而在讲解具体的优化措施之前#xff0c;我想先对

EXPLAIN

进行介绍#xff0c;它是我们在分析查询时必要的操作#xff0c;理解了它输出结果的内容更有利于我们优化

SQL

进行介绍它是我们在分析查询时必要的操作理解了它输出结果的内容更有利于我们优化

key1

进行优化前常用语句它能分析具体的查询计划从而让我们有目的地去进行优化。

本节则主要是让大家看懂

Explain

值相同select_type查询类型table表名partitions匹配的分区信息type针对单表的访问方法possible_keys可能用到的索引key实际使用的索引key_len实际使用的索引长度ref当使用索引列等值查询时与索引列进行等值匹配的对象信息rows预估的需要读取的记录条数filtered针对预估的需要读取的记录经过搜索条件过滤后剩余记录条数的百分比。

在单表查询中没什么意义在连表查询中可以计算出在驱动表执行完查询后还需要对被驱动表执行多少次查询Extra额外的备注信息

其中大部分列在描述信息中已经解释的足够清楚下面我们主要对一些必要的列进行详述

1.1

的大查询来说它是由几个小查询组成的其中除了最左边的那个小查询以外其余小查询的

select_type

一条记录如果是联合索引则只有在索引列的每一个列都与常数进行等值比较时这个

const

使用覆盖索引并扫描全部二级索引的访问。

另外当通过全表扫描对使用

InnoDB

“驱动表中的记录在被驱动表中找不到匹配纪录时对应的被驱动表记录的各个字段使用

NULL

const、ref、ref_or_null、eq_ref、unique_subquery

index_subquery

查询条件做的优化在对二级索引条件进行判断时会将所有该索引相关列的条件都判断完成后符合条件再执行回表操作不符合条件则不再执行回表这样做减少了回表操作的次数从而减少了

如下例子

表示在执行连接查询时被驱动表不能有效地利用索引加快访问速度而是使用内存块来加快查询

Using

语句中的访问类型type如果一个查询的访问类型并不是我们预期的那么最简单直接的解决办法是为搜索条件列

SQL

聚合函数需要扫描的数据可能会有成千上万行这取决于表中数据量总数。

对于这种

扫描大量数据却只返回少数行

进行优化当然这需要在应用层增加相应的逻辑对汇总表的数据进行维护。

重写复杂查询

这是一个值得考虑的问题。

将复杂查询拆成多个简单查询尽可能地减少数据库的工作并将一些处理逻辑拿到应用层处理因为

MySQL

的方法将一个大查询分成小查询每个查询的作用是一样的只不过操作的数据量不同各个小查询执行完毕后大查询的任务也就处理完成了。

一次性结转大量数据可能会锁住很多数据、占满整个事务日志、耗尽系统资源和阻塞很多小的查询等为了避免这种情况通常在一次数据结转任务中只操作

一万条左右

再去执行下一次任务这样做可以将压力分散到一个比较长的时间段中大大降低对服务器的影响和减少持有锁的时间。

优化联结查询

个。

如果联表过多我们需要将其拆成多个查询或多个单表查询单表查询的

缓存效率会更高查询被分解后查询间的锁竞争会减少。

除此之外联表查询还需要注意以下两点

USING

条件时会将列表中的数据先进行排序然后通过二分查找的方式来确定列表中的值是否满足条件这是一个时间复杂度为

O(logn)

如果跳过了联合索引中的列则无法使用索引或只能使用部分索引。

有如下

SQL其中

如果查询中有某列的范围查询则其右边所有列都无法使用索引优化查询或排序。

针对这种情况如果范围查询列值的数量有限那么可以通过

如果在搜索条件中列名不以列名的形式单独出现而是使用了表达式或者函数那么无法使用索引如下

SQL

如果排序列是某个联合索引的索引列但是这些排序列在联合索引中并不连续那么也无法使用到索引。

如下

SQL

操作时索引列不为空可以让它们更高效。

比如要找到某一列的最小值只需要查询对应

B-Tree

索引的最左端记录查询优化器会将这个表达式看做一个常数对待而且能够在

ESPLAIN

重复索引指的是在相同的列上按照相同顺序创建的相同类型的索引如下

SQL

冗余索引通常发生在为表添加新的索引时比如在已有索引column_a再添加一个索引column_a,

column_b这就是发生了冗余索引的情况因为第二个联合索引能够发挥和单列索引一样的作用。

大多数情况下都不需要冗余索引我们应该尽量扩展已有的索引而不是创建新的索引。

MySQL

来定位指定的数据行并将扫描结果进行合并。

索引合并的策略有时候非常不错但更多的时候它说明了表中的

当查询优化器需要对多个索引合并时通常意味着需要一个包含所有相关列的联合索引而不是多个独立的单列索引

当优化器需要对多个索引做合并操作时通常需要在算法的缓存、排序和合并操作上耗费大量

CPU

和内存资源尤其是当其中有些索引列值的选择性不高且需要合并扫描返回的大量数据时

优化器不会将这些操作算在查询成本中这会使得查询的成本被“低估”导致执行计划还不如进行全表扫描

重建索引

语法让优化器来忽略到某些索引从而避免优化器使用包含该索引的索引合并执行计划

select

#{value};除了在发生索引合并时考虑忽略索引也需要在执行查询时因无法形成合适的扫描区间达不到减少扫描记录的数量的目的时考虑忽略索引而使用全表扫描。

下面我们介绍三种索引合并的类型让大家对索引合并有一个更加充分的了解它们分别是

Intersection

b;我们都能清楚的是在索引列值相同的情况下二级索引记录是按照主键值的大小排序的那么可以将

key1

索引合并它可能相比于直接做全表扫描的开销要低。

需要注意的是Union

索引合并要求二级索引筛选出的主键值是有序的如果主键值无序则需要考虑

Sort-Union

b;我们将上述查询条件更改成了范围查询条件现在各索引筛选出的主键值是无序的所以无法使用

Union

COUNT(*)它会忽略所有列而直接统计所有行数。

明白了这两点之后我们做数据统计能够更清晰的传达意图。

查询需要扫描大量的数据行才能获得精确的结果所以比较难优化。

如果业务场景不要求完全精确我们可以

EXPLAIN

的方式记录上次读取数据的“位置”那么下次查询就能直接从该位置开始扫描避免使用

SQL

20;不过这种情况也有不足它没有办法指定页码进行查询比如说我现在想看第

120

删除了很多数据或者在插入数据时不是按照主键的递增顺序插入的很可能会因此产生很多内存碎片影响数据查询的效率。

这是因为在删除数据时MySQL

OPTIMIZE

specific_table;不过多数情况下不需要执行该操作。

本身的缺陷或者操作系统的问题导致索引损坏当然这种问题非常少见我们可以通过如下

SQL