mysql优化（五）

 上篇优化的文章主要是从分析的角度来分析，一个SQL的优化可以从哪些层面来做，有从架构、中间件、服务端客户端，然后也讲解了怎么去找一条性能慢的SQL，有从命令的方式查找，也讲了用PMM监控，但这些分析都是从理论上分析然后找出问题，下面我们接着上一篇幅来解决如何解决慢SQL的问题及分析他到底慢在哪里。

一、EXPLAIN执行计划

 官网介绍https://dev.mysql.com/doc/refman/5.7/en/explain-output.html在开始前先建好表

DROP TABLE IF EXISTS course;
CREATE TABLE course(
cid int(3) DEFAULT NULL,
cname varchar(20) DEFAULT NULL,
tid int(3) DEFAULT NULL
) ENGINE=InnoDB  DEFAULT CHARSET=utf8mb4;


DROP TABLE IF EXISTS teacher;
CREATE TABLE teacher(
tid int(3) DEFAULT NULL,
tname varchar(20) DEFAULT NULL, 
tcid int(3) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

DROP TABLE IF EXISTS teacher_contact;
CREATE TABLE teacher_contact(
tcid int(3) DEFAULT NULL,
phone varchar(200) DEFAULT NULL
) ENGINE=InnoDB  DEFAULT CHARSET=utf8mb4;

INSERT INTO course VALUES  (1,'mysql',1); 
INSERT INTO course VALUES  (2, 'mysql2', 1); 
INSERT INTO course VALUES  (3,'mysql3', 3); 
INSERT INTO course VALUES  (4,'mysql4', 1); 
INSERT INTO course VALUES  (5,'mysql5', 2); 

INSERT INTO teacher VALUES  (1,'zs', 1); 
INSERT INTO teacher VALUES  (2,'ls', 2); 
INSERT INTO teacher VALUES  (3,'ww', 3); 

INSERT INTO teacher_contact VALUES  (1, 12345678); 
INSERT INTO teacher_contact VALUES  (1, 13467890); 
INSERT INTO teacher_contact VALUES  (1, 13567803);

 

 

下面将以官网的表格及描述说明下条字段的意思

     1.1、id

  id是查询序列编号，每张表都是单独访问的，一个select就会有一个序号；当查询出来的id值不同时，我们EXPLAIN会发现他的查询顺序是先查询值大的（执行顺序是先执行查询id大的数据后查询Id小的数据）b-a-c

EXPLAIN SELECT
    c.phone 
FROM
    teacher_contact c 
WHERE
    tcid = ( SELECT tcid FROM teacher a WHERE a.tid = ( SELECT b.tid FROM course b WHERE b.cname = 'mysql3' ) )

 当查询出来的id值相同时，表的查询顺序和我们看到的列表顺序一样，是从上往下的顺序执行b-a-c

EXPLAIN SELECT
    a.tname,
    b.cname,
    c.phone 
FROM
    teacher a,
    course b,
    teacher_contact c 
WHERE
    a.tid = b.tid 
    AND a.tid = c.tcid 
    AND ( b.cid = 2 OR c.tcid = 3 )

 

 

 在连接查询中，先查询的叫做驱动表，后查询的叫做被驱动表，所以我们在日常查询中应该学会小表驱动大表的思想，这样中间结果少些，性能也能提升，在这里还有一个问题，可能有些人会问，有些时候你的执行计划id也有相同也有不同的情况，这时候执行计划的先后顺序是怎么样的，这个问题有兴趣的大家可以去玩玩，但答案可以告诉你：如果ID有相同也有不同的情况下，ID不同的先大后小，ID相同的从上往下。

1.2、select type查询类型

         可以是下表中显示的任何类型。JSON格式的EXPLAIN公开 SELECT类型为a的属性 query_block，除非它为 SIMPLE或PRIMARY。表格中还显示了JSON名称（如果适用）。

 

 

 上图是官网介绍的所有类型，下面就日常中我们比较常见的几种类型说明下：

    SIMPLE

     表示简单查询，不包含子查询及关联查询union

 

 

跟官网表对比是我们想要的结果，那么为验证官网上的表格说明我们再来查一个有子查询的

PRIMARY

    子查询SQL语句中的主查询，也就是最外面的那层查询

SUBQUERY

    子查询中所有 内层查询都是SUBQUERY类型

EXPLAIN SELECT
    c.phone 
FROM
    teacher_contact c 
WHERE
    tcid = ( SELECT tcid FROM teacher a WHERE a.tid = ( SELECT b.tid FROM course b WHERE b.cname = 'mysql3' ) )

 

 

DERIVED

衍生查询，表示在得到最终查询结果之前会用到临时表

EXPLAIN SELECT
    a.cname 
FROM
    ( SELECT * FROM course WHERE tid = 1 UNION SELECT * FROM course WHERE tid = 2 ) a

 

 

 对于关联查询，先执行右边的table(UNION),再执行左边的table，类型为DERIVED

UNION：表示用到了UNION查询

UNION RESULT

    主要是显示哪些表之间存在UNION查询上图中的<union2，3>表示id=2和3的查询存在UNION查询

1.3、type连接类型

          该type列 EXPLAIN输出介绍如何联接表。在JSON格式的输出中，这些作为access_type属性的值找到。以下列表描述了连接类型，从最佳类型到最差类型：

         在常用 的链接类型中：system>const>eq_ref>ref>range>index>all(fulltext、ref_or_null、index_merger、unique_subquery、index_subquery这几个没有说明)上面列举的类型中除了all外其它所有类型都能用到索引。

  •   const

         主键索引或者唯一索引，只能查到一条数据的SQL

DROP TABLE IF EXISTS single_data;
CREATE TABLE single_data(
id int(3)PRIMARY KEY,
content VARCHAR(20)
);
insert into single_data VALUES(1,'a');
EXPLAIN SELECT * FROM single_data where id =1;

 

 

  •  system

      system是const的一种特例，只有一行满足条件，对于MyISAM、Memory的表，只查询到一条记录，也是system。比如系统表的这张表

 

 

对于先前表中的每行组合，从此表中读取一行。除了 system和 const类型，这是最好的联接类型。通常出现在多表的  join 查询 ，被驱动表通过唯一性索引 （ UNIQUE PRIMARY KEY） 进行访问，此时被驱动表的访问方式就是 eq_ref。

先删除 teacher 表中多余的数据，teacher contact 有 3 条数据，teacher 表有 3条数据。为 teacher_contact 表的tcid （第一个字段） 创建主键索引。

 

ALTER TABLE teacher_contact ADD PRIMARY KEY(tcid);

 

 

 上面讲的三种都是可遇而不可求的，基本上很难优化到这个状态。

  • ref

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

ref可以用于使用=或<=> 运算符进行比较的索引列 。在以下示例中，MySQL可以使用tcid上的普通索引查询：

ALTER TABLE teacher ADD INDEX idx_tcid(tcid);

 

 

  •  range

索引范围扫描。如果 where 后面是 between and 或 ＜或 ＞ 或 ＞＝或 ＜ 或 in 这些，type 类型就为 range。不走索引一定是全表扫描 （ALL） ，所以先加上普通索引。

ALTER TABLE teacher ADD INDEX idx_tid(tid);

  •  index

      Full Index Scan 查询全部索引中的数据 （ 比不走索引要快） 。

该index联接类型是一样的 ALL，只是索引树被扫描。这发生两种方式：

    • 如果索引是查询的覆盖索引，并且可用于满足表中所需的所有数据，则仅扫描索引树。在这种情况下，Extra列显示为 Using index。仅索引扫描通常比索引扫描更快， ALL因为索引的大小通常小于表数据。

    • 使用对索引的读取执行全表扫描，以按索引顺序查找数据行。 Uses index没有出现在 Extra列中。

当查询仅使用属于单个索引一部分的列时，MySQL可以使用此联接类型。

 

  •  all

    Full Index Scan 如果没有索引或者没有用到索引，type就是ALL。代表全表扫描

  • NULL

    不用访问表或者索引 就能得到结果

 

 一般来说，需要保证查询的type至少达到range级别，最好能达到ref.ALL和index都是要优化的

 1.4、possible_key、key

  

可能用到的索引和实际用到的索引。如果是 NULL 就代表没有用到索引。 possible_key 可以有一个或者多个 ，可能用到索引不代表一定用到索引。 反过来，possible_key  为空，key 可能有值吗？

表上创建联合索引：

ALTER TABLE teacher add INDEX comidx_name_tcid(tname,tcid);
EXPLAIN SELECT tname,tcid from teacher where tcid='1'

 

 这里用到的是覆盖索引的情况

1.5、key_len

    索引的长度 （使用的字节数） 。跟索引字段的类型、长度有关、 表上有联合索引：

 

 key_len=83,这里的长度是可以计算出来的，我定义的这个字段长度是20，UTFMB4编码1个字符4个字节，所以20*4=80；使用变长字段varchar需要增加2个字节，允许NULL需要再增加1个字节，所以就83字节了，所以说，吃的没事，字段能限NULL就限了吧，能固定长度也固定了吧，这样少浪费点空间；

1.6、rows

MySQL  认为自描多少行才能返回请求的数据，是一个预估值。一般来说行数越少越好。

1.7、filtered

这个字段表示存储引擎返回的数据在 server 层过滤后，剩下多少满足查询的记录数 量的比例，是一个百分比。如果比例很低，说明存储引擎层返回的数据需要经过大量过滤，这个是会消耗性能 的，需要关注。

 1.8、ref

使用哪个列或者常数和索引一起从表中筛选数据。

1.9、Extra

执行计划给出的额外的信息说明。

  • using index
  • 用到了覆盖索引不需要回表

 

  •  using where

      使用了where  过滤，表示存储引擎返回的记录并不是所有的都满足查询条件，需要在 server 层进行过滤 （跟是否使用索引没有关系） 。

 

  •  Using index condition(索引条件下推)

前面文章有说过，这个下推不是人为可以控制的

  • using filesort

     不能使用索引来排序，用到了额外的排序 （眼磁盘或文件没有关系） 。需要优化。（复合索引的前提）

 

  •  using temporary

用到临时表，下面列几种情况

1.distinct 非索引列 （确定 tid 字段是否有索引）

EXPLAIN select DISTINCT(tcid) from teacher

2.grout by非索引列

EXPLAIN select tid from teacher group by tid

3、使用join 的时候，group 任意列 （b 表的结果）

EXPLAIN select a.tid from teacher a join course b on a.tid=b.tid GROUP BY a.tid

上面这几个情况就是需要优化的，可以创建复合索引进行优化；

 

 

posted @ 2020-12-07 23:29  童话述说我的结局  阅读(170)  评论(0)    收藏  举报