刚入职就出现线上故障 | 一篇总结扭转"风评"

青青486
283 阅读5分钟

前言：这是一个web列表展示的性能问题、定位、原因详解。

一.问题现象

出现问题的系统及具体的描述：

进入应用后台知识图谱模块的实体管理列表界面，实体数据显示为空

查看后台日志，如下图所示：数据库jdbc连接失败，进一步查看原因是数据库连接数被占满

image.png

二.出现问题的sql及原因

联表查询时从表索引失效，导致从表大概90W+的数据每次循环查询时都走了全表扫描，下面是出现问题的sql:

select

    count(*)

    from entity p

    left join standard_entity s on p.standard_uuid=s.entity_uuid

    LEFT JOIN entity_label el ON el.entity_uuid = p.entity_uuid     

   WHERE  p.entity_type='disease'     

    AND p.audit_status <  99

三、SQL问题解析

1.先根据执行计划，分析下这条sql的性能

image.png 执行顺序是从上往下的：

1.1 、第一步是通过entity表，使用where语句过滤，使用“simple”查询：简单的select查询，查询中不包含子查询或者union，查询结果数据为：rows 16.8W条;

  • type：ref是非唯一性索引扫描，返回匹配某个单独值的所有行,应该属于查找和扫描的混合体 ;
  • possible_keys:查询涉及到的字段上存在索引，则该索引将被列出，但不一定被查询实际使用，此处有没有使用接着往下看;
  • key:实际使用的索引，说明这次查询有用到索引，如果为null,则没用到索引;
  • key_len:表示查询中使用的索引的长度（最大可能长度），并非实际使用长度，这个对联合索引比较有用，比如上面第一步中用了entity_uuid varchar(64) 索引，则是64*3+2=195，通过这个可以了解到复合索引中使用到的索引字段;
  • rows:是指这次查找数据所扫描的行数（实际上是内循环的次数，比如第一行中循环一次，扫描数据量是16.8W;第二行中关联查询，是指每次关联standard_entity表时，只需要扫描一行数据）
  • Extra:Using where :使用了where过滤，这个较简单，不展开了，说明是用了查找;

1.2、第二步中entity表的16.8W条数据与standard_entity表进行left join，因为standard_entity表的关联查询字段entity_uuid是唯一索引，所以每个筛选条件只会返回唯一的一条记录

  • type：eq_ref，唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配;
  • extra:using index表示相应的select操作中使用了覆盖索引（就是select列表中的字段，只用从索引中就能获取，不必根据索引再次读取数据文件）;

这次查询结束后数据库表记录还是16.8W条。

1.3、第三步中entity表再left join entity_label表查询，每条记录会返回多条结果，下面是entity_label的设计（重点关注联合唯一索引）：

image.png

  • type:index,type访问类型经常遇见的六种类型，它们分别是all,index,range,ref,eq_ref，const，从左到右，它们的效率依次是增强的，而index这种连接类型也属于全表扫描，只不过它的扫描顺序是按照索引的顺序。这种扫描根据索引然后回表取数据，和all相比，他们都是取得了全表的数据;
  • rows:是指这次查找数据所扫描的行数（实际上是内循环的次数，比如主表是16.8W,每循环一次，要去entity_label表中扫描90W+行的数据，而且还要根据索引再回表取数据（这么说是因为extra中同时包含Using where和 Using index，如果只是Using index，则是属于索引覆盖），所以16.8W*90W这样子的扫描数据量……结果就可想而知，数据库的压力;
  • extra：Using where; Using index; Using join buffer (Block Nested Loop)：其实上面已经说明了

总结：为什么明明建了索引，还是走了全表扫描，下面重点来了！

想搞清楚这个问题，必须弄清楚联合索引（b树索引）的机制：

image.png

从上面图上看出来，因为联合B树索引是需要按顺序执行的，比如合索引uniq_uuid_labeType_Value (label_type,entity_uuid，label_value) USING BTREE,要想在“entity_uuid”上使用索引，必须先在“label_type”上使用索，要想在“label_value”上使用索引，必须先在“entity_uuid”上使用索引，依此类推。

所以上面的sql，因为查询条件中只用到了entity_uuid做为查询条件，但是因为没有对lable_type进行查询，没有用到第一个索引列，所以后面的索引没生效。

四、修改方案：修改联合索引的顺序，因为通常业务限制最多的也是entity_uuid字段，修改后：

image.png

再次查询执行计划：

image.png

修改后，查询用到了联合索引，并且通过key_len能看出来只用到了“entity_uuid"这个索引列，至于rows实际上一条数据会多个返回结果的，因为type类型是：ref(非唯一性索引扫描),从业务层面讲，一个实体关联的标签大概只有几个，所以rows就减少了太多太多，实际上不全是上图中的rows=1.

五、这里借机补充下联合索引中的一些场景：

  • 全值匹配 也就是查询条件全部用到了索引
  • 最佳左前缀法则 查询条件直接用到了最左侧的索引一,索引有效,但也仅仅是用到了一个字段.
  • 不能在索引列上干任何操作(计算,函数,类型转换)，会导致索引失效
  • 存储引擎不能使用索引中范围条件右边的列  
  • 尽量使用覆盖索引(只访问索引的查询),减少select *
  • mysql在使用不等于(!=或者<>)的时候索引失效
  • is null, is not null 也无法使用索引
  •  like以通配符开头("%abc..."),索引会失效，但是在select后面直接过滤索引字段可以避免
avatar
文章
阅读
粉丝