(mysql5.7)explain工具笔记

原创 已于 2022-09-22 15:14:04 修改 · 2k 阅读 · 32 ·
CC 4.0 BY-SA版权
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
文章标签：

#sql #数据库 #mysql

于 2022-09-21 15:30:08 首次发布
本文介绍MySQL的EXPLAIN工具，帮助理解SQL执行计划，包括各字段含义及其对性能的影响，指导SQL优化实践。

(MySQL5.7)EXPLAIN 工具_笔记

##### EXPLAIN 工具

​ explain是设计mysql的大佬，为我们准备的用于分析当前sql的执行计划的一个工具，他的使用方式非常简单,只需要在你想要分析的sql前面加上一句explain，再执行，你就可以得到mysql将如何执行本条sql的一个"详细介绍"。

例如：

explain select * from player_info where settlement_mark = 3 and level > 1 order by id

这条sql的输出就是以下内容

#explain输出

±—±------------±------------±-----------±------±--------------±-----±--------±-----±-------±---------±------------------------------------------------------------+

| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
±—±------------±------------±-----------±------±--------------±-----±--------±-----±-------±---------±------------------------------------------------------------+
| 1 | SIMPLE | player_info | NULL | range | le | le | 5 | NULL | 105698 | 10.00 | Using index condition; Using where; Using MRR; Using filesort |
±—±------------±------------±-----------±------±--------------±-----±--------±-----±-------±---------±------------------------------------------------------------+

explain和SQL优化

​ explain的输出可以帮助开发人员来优化sql。其中我们需要对explain输出的这两个字段重点关注（其他的也是需要关注的，但是我们第一要关注的还是type和extra）。

​ 1. 第一个是type，这个代表着你这条SQL的连接类型，通过这个类型我们可以知道当前SQL的效率如何，是否需要优化，可不可以优化。如果已经没有优化的空间了，那么是不是就要考虑分库分表。

  1. 第二个是extra，这个是解析SQL得到的附加信息，这些附加信息可以帮助我们分析当前mysql准备怎样执行你的sql，是什么可能导致的你的慢sql出现，并针对这些问题，优化你的SQL。
explain输出详解 (官方文档复制过来的表格)
ColumnJSON NameMeaning
idselect_idThe SELECT identifier
select_typeNoneThe SELECT type(查询的类型)
tabletable_nameThe table for the output row(将要扫描的表)
partitionspartitionsThe matching partitions()
typeaccess_typeThe join type(这个是重点)
possible_keyspossible_keysThe possible indexes to choose
keykeyThe index actually chosen
key_lenkey_lengthThe length of the chosen key
refrefThe columns compared to the index
rowsrowsEstimate of rows to be examined
filteredfilteredPercentage of rows filtered by table condition
ExtraNoneAdditional information
以下会对每个输出的字段做详细解释

#id

​ SELECT 标识符。 这是查询中 SELECT 的序号。 如果该行引用其他行的联合结果，则该值可以为 NULL。 在这种情况下，表格列显示类似 <unionM,N> 的值，以指示该行引用 id 值为 M 和 N 的行的并集,一般来说，这个字段没啥用

#select_type

​ sql语句的类型，可以是下表中显示的任何一种。 JSON 格式的 EXPLAIN 将 SELECT 类型公开为 query_block 的属性，除非它是 SIMPLE 或 PRIMARY。 JSON 名称（如果适用）也显示在表中。一般来说这个也没啥用。下面的表格是从官方文档复制过来的

select_type ValueJSON NameMeaning
SIMPLENone简单查询，没有使用连表查询或者子查询
PRIMARYNone最外层的查询（在子查询时或两表做union的时候的最外层查询）
UNIONNoneunion查询中第二个或者更靠后的查询
DEPENDENT UNIONdependent (true)依赖于外层查询的union查询的第二个或者更靠后的查询
UNION RESULTunion_resultunion查询的结果
SUBQUERYNone子查询(通常情况,我们要避免子查询的使用。多表查询尽量使用连表查询)
DEPENDENT SUBQUERYdependent (true)依赖于外层(用到了外层查询的表的字段作为查询条件)的子查询的第一个select
DERIVEDNone在FROM列表中包含的子查询被标记为DERIVED(衍生);MySQL会递归执行这些子查询, 把结果放在临时表里。
MATERIALIZEDmaterialized_from_subquery物化子查询
UNCACHEABLE SUBQUERYcacheable (false)一个子查询，其结果无法缓存，必须为外部查询的每一行重新计算
UNCACHEABLE UNIONcacheable (false)属于不可缓存子查询的 UNION 中的第二个或更晚选择（请参阅 UNCACHEABLE SUBQUERY）

DEPENDENT 通常意味着使用了关联子查询。

我们在使用explain语句的时候可以指定FORMAT=JSON这样explain返回的将是一个名为query_block的json串。

譬如：

explain FORMAT=JSON select * from player_info where id > 100 
这样返回的就是一个json串：
{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "579848.52"
    },
    "table": {
      "table_name": "player_info",
      "access_type": "range",
      "possible_keys": [
        "PRIMARY"
      ],
      "key": "PRIMARY",
      "used_key_parts": [
        "id"
      ],
      "key_length": "8",
      "rows_examined_per_scan": 2869574,
      "rows_produced_per_join": 2869574,
      "filtered": "100.00",
      "cost_info": {
        "read_cost": "292891.12",
        "eval_cost": "286957.40",
        "prefix_cost": "579848.52",
        "data_read_per_join": "2G"
      },
      "used_columns": [
        "id",
        "name",
        "create_date",
        "modify_date",
        "level",
        "status",
        "settlement_mark"
      ],
      "attached_condition": "(`test_1`.`player_info`.`id` > 100)"
    }
  }
}

#table

​ 此行信息对应的表

#partitions

​ 匹配的分区。就是对表进行分区后，查询时所匹配到的分区。

#type

​ 联接类型，这是我们平时优化sql需要重点关注的一个部分,用于描述表是如何连接的（一定程度体现出这条sql的性能），有以下类型（性能由高到低）；

system >const>eq_ref>ref>fulltext>ref or null>

index_merge>unique_subquery>index_subquery>range>index>ALL

​ 一般来说，一条sql至少要达到range甚至ref级别，才能达到效率需求。而在上面列出的index_merge虽然排在range前面，但是大部分时候它的效率甚至不如index ，而且index级别的sql查询效率也不一定高。mysql提升读写效率最主要的手段就是使用索引，索引使用恰当(真正使用上了索引并且使用方法合理)，SQL的速度就会很快。

#system

表里只有一行数据（等于系统表），这是const类型的特例。这是性能最好的类型，不过一般情况下是不会出现。

#const

表里面最多有一个匹配项。const非常快，因为只需要读取一次。

当您将 PRIMARY KEY 或 UNIQUE 索引的所有部分与常量值进行比较时，将使用 const。比如下面的sql就会使用const

select * from table_1 where id = 3;
select * from table_1 where unique_key = 1231;
select * from table_2 where primary_key1 = 12 and primary_key2 = 34

比如：显示比较乱，但是可以看出来这条sql是const类型

explain select * from player_info where id = 100 
得到的就是：
+----+-------------+-------------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table       | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | player_info | NULL       | const | PRIMARY       | PRIMARY | 8       | const |    1 |   100.00 | NULL  |
+----+-------------+-------------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
#eq_ref

当连表查询使用索引的所有部分且索引为PRIMARY KEY或UNIQUE NOT NULL索引时使用,这是除const和system以外最快的

触发条件：联表查询情况下，使用索引的全部部分,并且，索引是primary key或者unique not null 的时候会使用

比如下面的sql就会使用eq_ref:

select * from ref_table,other_table where ref_table.key_column = other_table.column;
// id是主键，status是常规字段
select * from jok j,player_info p where j.id = p.status

SELECT * FROM ref_table,other_table
WHERE ref_table.key_column_part1=other_table.column
AND ref_table.key_column_part2=1;

我们对这条sql进行explain就可以得到以下输出

explain select * from jok j inner join player_info p on j.id = p.status

alt

#ref

对于先前表中的每个行组合，从该表中读取具有匹配索引值的所有行。 如果连接仅使用键的最左侧前缀，或者如果键不是 PRIMARY KEY 或 UNIQUE 索引（换句话说，如果连接不能基于键值选择单行），则使用 ref。 如果使用的键只匹配几行，这是一个很好的连接类型。

比如下列的sql就会使用ref

select * from table_1 where not_unique_key = 1;
select * from table_1,table_2 where table_1.key_column = table_2.column 
select * from ref_table,other_table where ref_table.key_column_part1 = other_table_column
and ref_table.key_column_part2=1

比如下面的sql的explain输出type就是ref：

explain select * from player_info p where p.status = 1

alt

#fulltext

使用了FULLTEXT索引(全文索引)来连接表。

如果希望通过关键字的匹配来进行查询过滤，那么就需要基于相似度的查询，而不是原来的精确数值比较。全文索引就是为这种场景设计的。模糊匹配时，我们虽然可以使用like+%来实现，但是全文索引的效率比like+ % 高很多。

特别注意，全文索引的优先级特别高，全文索引和普通索引同时存在的时候，mysql会不管代价，使用全文索引

全文索引的实际使用较少

#ref_or_null

这个类型类似于ref，但是这种情况下，mysql对包含null值的行做额外的搜索。

触发条件：一条ref级别的sql它的where子句包含IS NULL

对于下面的示例，mysql可以使用ref or null连接来处理：

SELECT * FROM ref_table
WHERE key_column=expr OR key_column IS NULL;
#index_merge

​ 索引合并。

适用于对于子句中包含使用and/or组合的,在不同的键上的,每个键对应不同的扫描范围的SQL。

它会检索具有多个扫描范围的的行的结果合并成一个,不过它只能合并单表的索引扫描，并且深层的and/or嵌套也不能使用这个。

​ 触发条件:对于单独一张表的查询，使用两个以上的索引，并且and和or条件又使用了不同的索引的SQL就可能触发

​ 对于以下的这些sql，mysql都可能采用index_merge（只是可能，为什么说可能呢？因为这是取决于你数据库的实际存储的数据）

# 先得到key1的有序集合，再得到key2的有序集合，再就并集
SELECT * FROM tbl_name WHERE key1 = 10 OR key2 = 20;

SELECT * FROM tbl_name
WHERE (key1 = 10 OR key2 = 20) AND non_key = 30;

SELECT * FROM t1, t2
WHERE (t1.key1 IN (1,2) OR t1.key2 LIKE 'value%')
AND t2.key1 = t1.some_col;

SELECT * FROM t1, t2
WHERE t1.key1 = 1
AND (t2.key1 = t1.some_col OR t2.key2 = t1.some_col2);

​ 对于index_merge有已知的限制：

  1. 仅限单表
  2. 它并不适用于全文索引（也就是全文索引不能使用到索引合并）
  3. 它的效率极其不稳定，因为它有时候要做多个求并集然后求交集的动作，很多时候，他的效率甚至不如range。

​ 如下的sql就会是index_merge

explain select * from player_info where id < 20 and status = 4

alt


当你的SQL的type是index_merge的时候很不幸，你又得优化你的SQL了。

#unique_subquery

对于以下形式的in子查询（子查询返回不重复的唯一值），这个类型可能会替换eq_ref

value IN (SELECT primary_key FROM single_table WHERE some_expr)
#index_subquery

这个和unique_subquery类似，在以下类型sql中，可能会替换index。

value IN (SELECT key_column FROM single_table WHERE some_expr)
#range

这是一个比较常见的类型，它表示sql只扫描给定范围内的行，并使用索引去选择行。此类型的ref列是NULL.。

当我们使用=、<>、>、>=、<、<=、IS NULL、<=>、BETWEEN、LIKE 或 IN()这些运算符中的任意一个并将键列与常量进行比较的时候，就可能使用range

SELECT * FROM tbl_name
 WHERE key_column = 10;

SELECT * FROM tbl_name
 WHERE key_column BETWEEN 10 and 20;

SELECT * FROM tbl_name
 WHERE key_column IN (10,20,30);

SELECT * FROM tbl_name
 WHERE key_part1 = 10 AND key_part2 IN (10,20,30);
#index

index的连接类型和all是一模一样的，但是他不是全表扫，而是去扫描索引树。

它有两种方式：


  1. 如果index查询使用了覆盖索引，那么只需要扫描索引树就可以查到需要的数据，那么这种情况下，index是要比all快很多的，因为索引的大小通常都会小于表数据。在这种情况下，extra会包含Using Index

  1. 使用从索引中读取到的索引顺序去实现全表数据扫描。extra 不会包含 Using index
#ALL

全表扫描，没啥好说的

#覆盖索引

覆盖索引是select的数据列只用从索引中就能够取得，不必读取数据行，换句话说查询列 要被所建的索引覆盖。

#possible_keys

​ 此条SQL可能会用到的索引。

#key

​ 实际选择的索引

#key_len & ref

  1. key_len:实际选择的索引的长度。

    它表示了mysql实际决定使用的键的长度。它告诉我们，mysql实际使用的键的多少部分。


    这东西也没必要太在意。

  2. ref:与索引进行比较的列

    不用太在意这东西

#rows

​ MySQL认为它查询必须扫描的行数，对于InnoDB表，这个数值只是一个估计值，它并不总是准确的。


#filtered

​ 按表条件过滤的行百分比(表示符合查询条件得数据百分比),100.00表示没有过滤行，不用对这个值过于关注。

#Extra

​ 这个也是我们平时sql优化需要重点关注的信息，Extra包含有关 MySQL 如何解析查询的附加信息。

这些附加信息非常多，我们简单记录一些常见的。其余的的可以参考官方文档：

MySQL :: MySQL 5.7 Reference Manual :: 8.8.2 EXPLAIN Output Format


Using filesort

MySQL必须执行额外的传递才能了解如何按排序顺序检索行。排序是通过根据联接类型遍历所有行并存储排序键和指向与子句匹配的所有行的行的指针来完成的。然后对键进行排序，并按排序顺序检索行。


触发条件：在mysql查询中，一个子句或者其他条件使得mysql需要执行额外的排序来返回结果集

怎么优化呢?

常规情况下，我们只要让order by和where子句使用同样的索引就行了，如果无法使用同一个，我们可以把order by给去掉，mysql在默认情况下会使用升序排序

其实根本目的在于去除额外的排序

譬如：（status是普通索引）
explain select * from  player_info where id > 0 order by `status`

它的输出就是：


在这里插入图片描述


我们只需要把order by子句去掉：

explain select * from  player_info where id > 0

它的explain输出就没有using filesort了

在这里插入图片描述


Using index

出现这个，意味着你的这条查询，mysql将仅使用索引树中的信息从表中检索列信息，而无需执行额外的查找来读取实际行。而通常情况下，索引是比数据小非常多的，所以会快很多。

  • 如果同时出现了using where, 表明索引被用来执行索引键值的查找,数据在索引列表就能找到，不需要回表再查

  • 如果没有同时出现using where, 表明索引用来读取数据而非执行查找动作

对于具有用户定义的聚集索引的 InnoDB 表，即使 Extra 列中没有 Using index，也可以使用它。 如果 type 是 index 并且 key 是 PRIMARY 就是这种情况。

比如：

explain select * from  player_info where settlement_mark > 0  order by id desc

它的输出就是：

在这里插入图片描述


不要在意 Backward index scan,它是mysql8加入的，mysql5.7还没有这个优秀的策略


Using index condition

这就意味着，mysql会先按条件过滤索引，过滤完索引后找到所有符合索引条件的数据行，随后用 WHERE 子句中的其他条件去过滤这些数据行。（Explain 输出的extra信息显示using index condition 而不是using index是因为当我们必须去读取全表的时候using index并不适用，就是这种情况下用using index效率会降低）


它做了两件事：先用索引去过滤行(找)再回表查询数据（拿）


出现这一条信息说明这是一个好消息，mysql选择了一种更优化的方法去实现查询。


Using temporary

这意味着mysql将创建一个临时表来进行查询。这是会有损查询效率的。我们在实际应用中这是要尽量去避免的。


（Using temporary通常会发生在group by或者order by里面）


当你的explain输出的extra信息中出现这个的时候，就要去考虑优化你的SQL了


像这条sql就会using temporary

explain select p.id from  player_info p,jok j where p.settlement_mark > 0 and j.settlement_mark>0  group by `status`,id

在这里插入图片描述


Using index for group-by


​ 这意味着MySQL找到了一个索引，该索引可用于检索或查询的所有列，而无需对实际表进行任何额外的磁盘访问。此外，索引以最有效的方式使用，以便对于每个组，只读取几个索引条目。（这是mysql对group by子句的一种优化）


这意味着mysql选择了松散索引扫描（Loose Index Scan）的策略来执行这条sql

Using join buffer (Block Nested Loop)，

将部分读取早期联接中的表，然后从缓冲区中使用它们的行来执行与当前表的联接。 指示使用块嵌套循环算法，并指示使用批处理密钥访问算法。也就是说，对 EXPLAIN 输出前一行的表中的键进行缓冲，并从出现的行所表示的表中批量读取匹配的行。

比如这种sql：

select a.id,b.mec_no,c.des,d.suibiansha 
		from table_a a inner join table_b 
			inner join table_c c 
				inner join table_d d 
					on a.in_trade_id = b.in_trade_id = c.in_trade_id = d.in_trade_id 
		where 1
#index_merge_extra

Using sort_union(...)；Using intersect(...)；Using union(...)；


当extra出现这些的时候，你的type也会是index_merge,而index_merge也是我们要尽量避免的。


这些指出的特定算法，表示了inde_merge这个连接类型是如何进行合并索引扫描的。


主要有以下三个算法(他们之间是可以互相嵌套的)：

  • 索引合并交叉访问算法(Index Merge Intersection Access Algorithm)

    适用于子句包含 由and组合的不同键（键和索引是同一种东西）上的多个范围的条件


    什么场景下会使用这个算法呢:

    -- 这个条sql共有两个扫描范围，一个是id<20的范围，一个是status=4的范围，mysql先把他们分别找到，再求交集
explain select * from player_info where id < 20 and  status = 4

    alt

  • 索引合并联合访问算法(Index Merge Union Access Algorithm)

    适用于子句包含 由or组合的不同键（键和索引是同一种东西）上的多个范围的条件

    -- 同前一条sql一致，一共两个扫描范围，一个是id<20的，一个是status = 4的，mysql先把他们分别找到，然后求并集。
explain select * from player_info where id < 20 or  status = 4

    在这里插入图片描述


    或者嵌个套

    -- 先找到三个扫描范围：a：id<20; b:status =4;c: level = 3 然后对b和c求交集再把他们的结果和a求并集。
explain select * from player_info where id < 20 or  status = 4  and `level` = 3

    alt

  • 索引合并排序联合访问算法(Index Merge Sort-Union Access Algorithm)

    对索引合并联合访问算法的结果集做排序再返回

    -- 依然是找id<20和status>2这两个范围的，对他们进行求并集的操作得到结果集，对结果集进行排序，返回结果集（排序一定是在返回结果集之前）
explain select * from player_info where id < 20 or  status > 2

    alt


    这个算法和前一个算法的区别就在于排序这两个字:


    索引合并排序联合访问算法必须首先获取所有行的行 ID 并在返回任何行之前对它们进行排序。

Using where

就是代表你的SQL里面有where子句

子句用于限制哪些行要与下一个表匹配或发送到客户端。除非您特别打算从表中读取或检查所有行，否则如果值不是，并且表连接类型为 ALL 或 index，则查询中可能存在问题。

​ 其余的还有很多，想要了解的不妨去mysql官网深入学习一下。

#关于SQL优化

​ 上面记录了很多东西，但是都没有真正去说到怎么优化慢SQL。为什么呢？因为笔者也不会,。其实对于我们日常的慢SQL整改中，很难去说是明确的告诉你某个type下或者extra下，这条SQL要怎么优化，更多的还是遇到问题解决问题，SQL优化是没有绝对的真理的。而最常用的优化手段就是合理地使用索引，或者建立新的索引。最根本的目的就是在查询需要的数据的时候，让MySQL扫描更少的行


​ 关于SQL优化，我们可以这样做：根据explain输出的type来决定它是不是需要优化，还有没有优化的空间，如果有那么再结合extra信息去辅助分析造成这条sql慢的原因。


​ 是因为mysql没有选择最优的查询方案？为什么没有？需不需要建立索引建立索引需要考虑区分度(后面会提到),有没有用上索引？为什么没用上？



​ 还是因为数据量实在过大，在最优的情况下依然无法快速查询，那么在这种情况下是不是要去考虑做分库分表。


​ 其次,一条慢SQL需不需要去优化还需要结合现有的应用场景，在现有的场景下，需不需要去做优化，有没有影响到实际的应用，影响大不大？调用时间的分布，频率高不高？一般来说，影响较大，调用频繁，在业务高峰期的慢sql是要优先去处理的。而想要知道这些信息，我们可以借助pt-query-digest(在本文最后会提到)这个工具来辅助分析。

一些SQL优化的建议（这个真的是复制过来的）

!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!仅仅是建议，可以尝试，但是不一定有用！！！现实中一定要结合实际情况做处理!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!


  1. 对查询进行优化，应尽量避免全表扫描，首先应考虑在 where 及 order by 涉及的列上建立索引。

  1. 应尽量避免在 where 子句中使用!=或<>操作符，否则将引擎放弃使用索引而进行全表扫描。

  1. 应尽量避免在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进行全表扫描，如：
select id from t where num is null

可以在num上设置默认值0，确保表中num列没有null值，然后这样查询：

select id from t where num=0

  1. 应尽量避免在 where 子句中使用 or 来连接条件，否则将导致引擎放弃使用索引而进行全表扫描，如：
select id from t where num=10 or num=20

可以这样查询：

select id from t where num=10 

union all 

select id from t where num=20

  1. 下面的查询也将导致全表扫描：
select id from t where name like '%abc%'

若要提高效率，也可以考虑使用全文索引。

  1. in 和 not in 也要慎用，否则可能会导致全表扫描，如：
select id from t where num in(1,2,3)

对于连续的数值，能用 between 就不要用 in 了：

select id from t where num between 1 and 3

  1. 应尽量避免在 where 子句中对字段进行表达式操作，这将导致引擎放弃使用索引而进行全表扫描。如：
select id from t where num/2=100 

#应改为: 

select id from t where num=100*2

  1. 应尽量避免在where子句中对字段进行函数操作，这将导致引擎放弃使用索引而进行全表扫描。如：
select id from t where substring(name,1,3)='abc'--name以abc开头的id 

select id from t where datediff(day,createdate,'2005-11-30')=0--'2005-11-30'生成的id 

#应改为: 

select id from t where name like 'abc%' 

select id from t where createdate>='2005-11-30' and createdate<'2005-12-1'

  1. 不要在 where 子句中的“=”左边进行函数、算术运算或其他表达式运算，否则系统将可能无法正确使用索引。 同第7条
比如这一条：
explain select * from player_info where status = -16

explain输出就是ref：

alt


但是，如果我们在“=” 的左边使用表达式运算

explain select * from player_info where status/2= 2

那怕是这样一个简单的除，也会导致全表查询：

alt


  1. 在使用索引字段作为条件时，如果该索引是复合索引，那么必须使用到该索引中的第一个字段(联合索引最左匹配原则)作为条件时才能保证系统使用该索引，否则该索引将不会被使用，并且应尽可能的让字段顺序与索引顺序相一致。

  1. 很多时候用 exists 代替 in 是一个好的选择：
select num from a where num in(select num from b) 

用下面的语句替换： 

select num from a where exists(select 1 from b where num=a.num)

  1. 要控制所建立的索引的区分度并不是所有索引对查询都有效，SQL是根据表中数据来进行查询优化的，当索引列有大量数据重复时，SQL查询可能不会去利用索引，如一表中有字段sex，male、female几乎各一半，那么即使在sex上建了索引也对查询效率起不了作用,。


  1. 要控制索引的数量索引并不是越多越好，索引固然可以提高相应的 select 的效率，但同时也降低了 insert 及 update 的效率，因为 insert 或 update 时有可能会重建索引，所以怎样建索引需要慎重考虑，视具体情况而定。一个表的索引数最好不要超过6个，若太多则应考虑一些不常使用到的列上建的索引是否有必要。


  1. 实际开发中尽量避免使用 select * from t ，用具体的字段列表代替“*”，不要返回用不到的任何字段。


  1. 从后端开发的角度，大部分逻辑都由代码来实现，而数据库只负责简单的读写，把一些逻辑，事务都放到代码中去实现。


  1. 尽量避免向客户端返回大数据量，若数据量过大，应该考虑相应需求是否合理(比如进行一些分页)。


  1. 尽量避免大事务操作，提高系统并发能力。



  2. 一般来说，实际开发中，我们要避免使用子查询，使用连接查询会好很多。



  1. 对于我们的联表查询，我们要尽量使用小的结果集去驱动大的结果集，

    这是为了减少非必要的循环次数，使用小的驱动结果集，循环次数会相对较少；

就像这样

select * from smaller_result_col_table s left join bigger_result_col_table b on s.busiId = b.busiId

一定要注意，是用结果集去驱动，并不意味着是用小的表去驱动大的表，比如我们有两张表，一张A有100w的数据，一张B只有10w的数据。我们使用B表去驱动A表，表面上看没问题，但是如果我们过滤条件得当,从B表查到的结果有2w条，从A表查询到的结果只有5k条，那么这个时候我们使用B表去驱动A表就会导致循环次数变多。


所以实际开发中,一定是根据每张表大概返回数据来判断谁驱动谁。

#索引的区分度


​ 首先我们要清楚，对于一个索引他的区分度越高，他的查询效率就越高。并且索引的区分度也是一个`平衡的艺术`。

​ 那什么是区分度呢？

举个例子： （张，张三，张三哥），如果索引长度取1的话，那么每一行的索引都是 张 这个字，完全没有区分度，你让他怎么排序？结果这样三行完全是随机排的，因为索引都一样；

如果长度取2，那么排序的时候至少前两个是排对了的，如果取3，区分度达到100%，排序完全正确；

那是不是说索引越长越好？  答案肯定是错的，比如 (张,李,王)  和 （张三啦啦啦，张三呵呵呵，张三呼呼呼）；前者在内存中排序占得空间少，排序也快，后者明显更慢更占内存，在大数据应用中这一点点都是很恐怖的（当然我并不是大数据开发人员）；

​

在一定层面上，对于我们的索引而言，索引长度和索引的区分度之间是互相矛盾的，索引长度长，区分度就越高，但是索引是要占内存的，索引过大查询效率也不一定会高(最主要还是会占用太多内存)，其次，索引长度越低，区分度也越低，查询效率也会低，所以我们要找一个中间的平衡点。


​ 但是，这一切都是基于数据库中实际存储的数据，索引长度和区分度之间并不是强关联，通过下面的公式计算出来的区分度仅仅具有参考价值，而在实际应用中，(除了刚建表的时候)我们建立索引都必须根据表中实际存储的数据来考虑。根据实际的数据，我们的索引长度可能就会有不同的选择。

比如我们有一个32位的业务id，我们对他的前10位建立索引，但是这个业务id前16位都是固定的值，那么我们算出来的区分度依然会很低。

​ 那么怎么计算我们索引的区分度呢？可以用以下的sql来进行计算：

# key_col表示你要建立索引的字段，key_length_prepare表示将要建立的索引长度
select count(distinct left(key_col,key_length_prepare))/count(*) from table;

​ 通过这个我们就可以算出我们索引的区分度，就一般情况而言，索引的区分度能达到0.1就已经可以接受了。

#pt-query-digest

​ 这是一个慢sql分析工具，他可以分析mysql的slow.log文件，并输出一个分析文件，我们可以根据这个分析文件去分析我们的sql执行情况，并做出有针对的调整。

​ 接下来让我带大家开始pt-query-digest的从安装到使用到入门，我呸，太多了(劳资也不会)，安装 和怎么使用还是交给DBA吧，我们开发人员只需要能够看懂最终导出来的文件就可以了。


以下就是pt-query-digest导出的文件内容：

没有错就是这么一长串，里面的中文是我的注释，虽然东西很多，但是我们实际上开发人员关注的点并不多，平时优化sql只需要去关注这些点就可以了.

270ms user time, 20ms system time, 26.48M rss, 241.39M vsz
Current date: Wed Sep 14 05:00:01 2022

执行pt-query-digest的主机名
Hostname: xxx-xxxx-10.2.1.13-db

这个Files就是pt-query-digest分析的mysql-slow.log文件。
Files: /data/mysqldata/3306/log/slowlog/mysql-slow-20220913.log

Overall: 603 total, 15 unique, 0.01 QPS, 0.01x concurrency _____________

说明：执行过程中日志记录的时间范围
Time range: 2022-09-12T22:00:03 to 2022-09-13T17:00:13

                  总计      最小值   最大值    平均数 在95%的值 方差  中位数
Attribute          total     min     max     avg     95%  stddev  median
============     ======= ======= ======= ======= ======= ======= =======
执行时间
Exec time           943s      1s     94s      2s      2s      4s      1s
锁占用时间
Lock time             5s    62us      2s     8ms   224us   117ms   113us
实际发送到客户端的行数
Rows sent        869.85k       0   9.77k   1.44k   1.46k  430.97   1.46k
sql执行扫描的总行数
Rows examine     999.61M      12   5.44M   1.66M   2.05M 414.79k   1.61M
查询的字符数
Query size       405.32k      73   5.67k  688.30  685.39  213.19  685.39

Profile
语句id信息               响应时间  本次查询中的时间占比 总计的查询次数 平均每次执行的响应时间  v/m Item
Rank Query ID           Response time  Calls R/Call  V/M   Item
==== ================== ============== ===== ======= ===== =============
   1 0x8AF53585000672A1 768.3194 81.4%   584  1.3156  0.02 SELECT xxxxxx
  2 0xC9D5CB538A6C24FF 129.0243  13.7%     2 64.5122 27.40 SELECT xxxxx
MISC 0xMISC             45.9709  4.9%    17  2.7042   0.0 <13 ITEMS>

 输出的每列查询的详细信息
Query 1: 0.56 QPS, 0.74x concurrency, ID 0x8AF53585000672A1 at byte 532872
This item is included in the report because it matches --limit.
Scores: V/M = 0.02
执行过程中的日志记录的时间范围
Time range: 2022-09-13T16:15:38 to 2022-09-13T16:33:01
                  总数        最小值  最大值   平均值    95%  方差     中位数
Attribute    pct   total     min     max     avg     95%  stddev  median
============ === ======= ======= ======= ======= ======= ======= =======
计数
Count         96     584
执行时间
Exec time     81    768s      1s      2s      1s      2s   178ms      1s
锁占用时间
Lock time      1    71ms    62us   367us   121us   214us    42us   113us
发送给客户端的行数
Rows sent     98 855.46k   1.45k   1.46k   1.46k   1.46k    2.95   1.46k
查询扫描的行数
Rows examine  98 984.97M   1.08M   2.10M   1.69M   2.05M 244.70k   1.69M
查询的字符数
Query size    96 392.95k     689     689     689     689       0     689
String:
使用的数据库名称
Databases    XXXXX
使用的数据库地址
Hosts        xx.x.xxx.xxx
使用的用户名
Users        xxx
查询的时间分布
Query_time distribution
  1us
 10us
100us
  1ms
 10ms
100ms
   1s  ################################################################
 10s+
 执行的慢sql数据
Tables
   SHOW TABLE STATUS FROM `xxxxx` LIKE 'xxxxx'\G
   SHOW CREATE TABLE `xxxxx`.`xxxxx`\G
EXPLAIN /*!50100 PARTITIONS*/
这条慢sql(我先随便写一条放着)：
select * from slow_table where condition = slow

那么这么多东西我们应该关注哪些部分呢？


我们只需要关注于这部分就可以： 
 输出的每列查询的详细信息
Query 1: 0.56 QPS, 0.74x concurrency, ID 0x8AF53585000672A1 at byte 532872
This item is included in the report because it matches --limit.
Scores: V/M = 0.02
执行过程中的日志记录的时间范围
Time range: 2022-09-13T16:15:38 to 2022-09-13T16:33:01
                  总数        最小值  最大值   平均值    95%  方差     中位数
Attribute    pct   total     min     max     avg     95%  stddev  median
============ === ======= ======= ======= ======= ======= ======= =======
计数
Count         96     584
执行时间
Exec time     81    768s      1s      2s      1s      2s   178ms      1s
锁占用时间
Lock time      1    71ms    62us   367us   121us   214us    42us   113us
发送给客户端的行数
Rows sent     98 855.46k   1.45k   1.46k   1.46k   1.46k    2.95   1.46k
查询扫描的行数
Rows examine  98 984.97M   1.08M   2.10M   1.69M   2.05M 244.70k   1.69M
查询的字符数
Query size    96 392.95k     689     689     689     689       0     689
String:
使用的数据库名称
Databases    XXXXX
使用的数据库地址
Hosts        xx.x.xxx.xxx
使用的用户名
Users        xxx
查询的时间分布
Query_time distribution
  1us
 10us
100us
  1ms
 10ms
100ms
   1s  ################################################################
 10s+
 执行的慢sql数据
Tables
   SHOW TABLE STATUS FROM `xxxx` LIKE 'xxxxx'\G
   SHOW CREATE TABLE `xxx`.`xxxx`\G
EXPLAIN /*!50100 PARTITIONS*/
这条慢sql(我先随便写一条放着)：
select * from slow_table where condition = slow

在其中，Time range（时间分布），total（总数），95% ，median（中位数）， Exec time（执行时间），Rows sent（发送到客户端的行数），Rows examine（扫描的行数）。通过这些部分，我们就可以分析出这条sql的执行情况。


首先是时间分布，再结合total我们就可以知道这条sql在什么时间段内被调用，调用频率高不高然后就是95%和中位数，通过他们我们可以知道sql执行时间的真实情况。平均数只供参考


最后就是Rows sent和Rows examine，通过这个我们就可以查看这条sql的索引使用情况，一般情况下，索引使用得当，mysql会扫描更少的行数。而发送到客户端的行数就是我们最终查询出来的结果，我们可以使用发送到客户端的行数除以调用次数，就可以得到每次调用返回的行数。

当我们知道这条sql调用频繁与否在什么时间段调用的，以及每次查询返回的行数，我们再结合实际的应用场景，判断这条sql是不是需要优化，在有限的资源的情况下，需不需要优先优化，再结合explain就可以进行sql优化。

MySQL高级特性之：`MySQL`的`EXPLAIN FORMAT=JSON`：其在获取详细执行计划时的应用。
weixin_41455464的博客
09-05 1095
语句虽然能够提供一些关键信息，例如使用的索引、扫描的行数等等，但它输出的信息相对简单，不够结构化，难以进行自动化分析和比较。语句的一个增强版，它以JSON格式提供更详细、更结构化的执行计划信息，为我们更精准地分析和优化SQL语句提供了强大的工具。是一个非常有用的工具，但在不同的MySQL版本中，其输出的JSON结构和包含的信息可能会略有差异。执行上述语句后，MySQL会返回一个JSON格式的字符串，其中包含了详细的执行计划信息。可以帮助我们了解MySQL如何执行我们的SQL语句，从而发现潜在的性能瓶颈。
MySQL-explain执行计划笔记
最新发布
Deep Water Slow
06-14 910
2.6 union： 分union与union all两种，若第二个select出现在union之后，则被标记为union；2.7 dependent union: 当union作为子查询时，其中第一个union为dependent subquery，第二个union为dependent union。表总共5条记录，user_id>=3未走索引，进行的是全表扫描，符合条件的最终结果只有一条，所以rows是5，filered是20（1/5）total_price列无索引，无法通过索引排序。
MYSQL优化
weixin_43374578的博客
05-29 281
一、慢查询日志 MYSQL的满查询日志，用来记录在MYSQL中响应时间超过阈值的语句，具体指运行时间超过long_query_time值的SQL，则会被记录到慢查询日志中 long_query_time默认值是10s 帮助定位执行时间较长的SQL查询，再通过EXPLAIN来分析，从而定位问题 默认MYSQL没有开启慢查询日志，需要手动开启。如果不是调优需要，一般不建议启动该参数，因为开启慢查询日志会对性能带来一定影响 1. 开启慢查询 # 默认是OFF SHOW VARIABLES LIKE '%slo
MySQL索引优化实战&EXPLAIN解析
sunriseYJP的博客
05-24 1147
知识不光要学习，还要应用。用理论指导实践，实践来进一步检验理论。
MySQL 5.7 优化：Explain 执行计划近万字详解
一只奋斗的猪
01-04 5951
目录1. 介绍2. Explain 结果列详解2.1 id2.2 select_type2.3 table2.4 partitions2.5 type（非常重要）2.6 possible_keys2.7 key2.8 key_len2.9 ref3.10 rows2.11 filtered2.12 Extra 【注】 当前系统环境： MySQL 5.7，其他版本略有不同，后期会抽时间单独说明。 只介绍常见的场景，其他少见的场景暂不研究。 文档参考： MySQL 官方 Explain 文档 1. 介绍
MySQL 5.7 EXPLAIN EXTENDED语句说明
hevenue的专栏
08-22 1195
EXPLAIN EXTENDED相比EXPLAIN命令，会额外显示一个filtered字段。这个字段会指示出表的条件所过滤的表中行数的百分比。除此之外，在执行EXPLAIN EXTENDED命令之后，使用SHOW WARNINGS语句可以查看额外的语句信息。在MySQL 5.7.3，EXPLAIN命令会自动带EXTENDED参数。 mysql> EXPLAIN EXTENDED SELE
mysql 5.7 某笔记
03-13
MySQL 5.7是MySQL数据库管理系统的一个重要版本，它提供了许多增强的功能和性能优化，适合初学者学习和专业人士日常查询。以下将详细讲解压缩包文件中涉及的一些关键知识点： 1. **MySQL体系结构及基础管理**： ...
Query Optimization with MySQL 5.7 and MariaDB 10 Even newer tricks
11-30
### Query Optimization with MySQL 5.7 and MariaDB 10: Even Newer Tricks #### 引言 在数据库管理领域，查询优化是一项至关重要的技术，它直接影响到数据处理的效率和性能。随着MySQL 5.7和MariaDB 10等版本的...
精选资源
MySQL大佬姜承尧49完整课程笔记，进阶涨薪必看，内含MySQL配置文件
08-05
- 监控工具：使用如MySQL Enterprise Monitor、Percona Monitoring and Management等工具进行性能监控。 - 查询分析器：使用EXPLAIN分析SQL执行计划，找出性能瓶颈。 - 参数调整：根据系统负载调整MySQL的配置...
《MySQL》超详细笔记
Li的博客
02-05 3717
关系型与非关系型的区别： 显示数据库 (1)显示所有已经存在的数据库 (2)显示创建某个特定数据库的SQL语句 运行结果解读:编辑 删除数据库 将已存在的数据库删除，并同时删除数据库中的数据 使用数据库 查询当前数据库信息 显示数据库中的表 导入数据库脚本 【注】 表 查看表的结构 查看创建某个表的SQL语句 数据库的查询命令 查询字段 运算符操作 1、数值比较/字符比较2、逻辑操作3、范围内比较4、匹配空、
MySQL5.7 explain详解
rojanzhang的专栏
12-24 678
Table8.1EXPLAIN Output Columns Column JSON Name Meaning id select_id TheSELECTidentifier select_type None TheSELECTtype table table_name The table for the output r...
mysql 5.7 explain,《高性能MySQL》之EXPLAIN
weixin_36036852的博客
03-17 158
搜索热词使用explain关键字获取sql执行性能语法如下:explain select * from tableexplain 中的列expain出来的信息有10列，分别是id,select_type,table、type,partitions,possible_keys,key,key_len,ref,rows,Extra,下面对这些字段出现的可能进行解释：1.IDsql执行的顺序的标识,sq...
mysql 5.7 explain,Mysql的Explain详细说明
weixin_35456031的博客
03-17 285
>explain显示了mysql如何使用索引来处理select语句以及连接表。可以帮助选择更好的索引和写出更优化的查询语句。------------使用方法，在select语句前加上explain就可以了：如：`explain select surname,first_name form a,b where a.id=b.id`![](https://blog-1252087744.cos....
mysql5.7-explain 参数详解
Finley丶博客
04-23 961
explain 基于mysql5.7 explain为mysql提供语句的执行计划信息。可以应用在select、delete、insert、update和place语句上。explain的执行计划，只是作为语句执行过程的一个参考，实际执行的过程不一定和计划完全一致，但是执行计划中透露出的讯息却可以帮助选择更好的索引和写出更优化的查询语句。 [外链图片转存失败,源站可能有防盗链机制,建议将图片...
mysql 5.7 explain_MySQL优化之Explain命令解读
weixin_42464317的博客
01-19 348
简述:explain为mysql提供语句的执行计划信息。可以应用在select、delete、insert、update和place语句上。explain的执行计划，只是作为语句执行过程的一个参考，实际执行的过程不一定和计划完全一致，但是执行计划中透露出的讯息却可以帮助选择更好的索引和写出更优化的查询语句。EXPLAIN输出项(可参考mysql5.7文档)备注:当使用FORMAT=JSON， 返回...
mysql 5.7 explain_MySQL 5.7 EXPLAIN EXTENDED语句说明
weixin_34327800的博客
01-19 308
EXPLAIN EXTENDED相比EXPLAIN命令，会额外显示一个filtered字段。这个字段会指示出表的条件所过滤的表中行数的百分比。除此之外，在执行EXPLAIN EXTENDED命令之后，使用SHOW WARNINGS语句可以查看额外的语句信息。在MySQL 5.7.3，EXPLAIN命令会自动带EXTENDED参数。mysql> EXPLAIN EXTENDED SELECT ...
mysql 5.7索引优化之Explain工具介绍
踏雪江南的博客
11-01 516
Explain工具介绍 mysql5.7 explain官方文档 使用EXPLAIN关键字可以模拟优化器执行SQL语句，分析你的查询语句或是结构的性能瓶颈.在 select 语句之前增加 explain 关键字，MySQL 会在查询上设置一个标记，执行查询会返回执行计划的信息，而不是执行这条SQL 注意：如果 from 中包含子查询，仍会执行该子查询，将结果放入临时表中 初始化数据 示例表： DROP TABLE IF EXISTS `actor`; CREATE TABLE `actor` ( `i
Explain详解（mysql 5.7）
tjd5214的博客
02-18 344
Explain详解（mysql 5.7 innodb） Explain的列 sql语句每一个select对应explain的每一行记录。 id列 id列是每个select的序列号。id越大执行的优先级超高，id相等时由上而下执行。 select_type列 select_type表示一条sql语句的类型。 simple：简单查询。sql语句中不包含子查询和union。 primary：最外层的查询语句。当包含子查询或union时。 subquery：select语句中的子查询。 derived：from
MySQL explain详解
qq_33409823的博客
04-06 1690
explain包含的信息十分的丰富, 主要关注以下几个字段信息.id: select子句或表执行顺序, id相同, 从上到下执行, id不同, id值越大, 执行优先级越高.system > const > eq_ref > ref > range > index > all. 保证range, 最好到ref.key: 实际被使用的索引列.ref: 关联的字段, 常量等值查询, 显示为const, 如果为连接查询, 显示关联的字段.
MySQL 5.7执行计划分析：EXPLAIN详解
在MySQL数据库中，`EXPLAIN`是一个强大的工具，它允许开发者查看SQL查询的执行计划，以便于理解数据库如何处理查询，从而进行性能优化。在5.7.18-log版本的MySQL中，`EXPLAIN`输出包含多个字段，这些字段提供了关于...
评论 1
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值