金华网站建设优化技术,wordpress二次开发视频,做随车吊网站要多大,整站seo怎么做记一次生产慢sql索引优化及思考
问题重现
夜黑风高的某一晚#xff0c;突然收到一条运营后台数据库慢sql的报警#xff0c;耗时竟然达到了60s。看了一下#xff0c;还好不是很频繁#xff0c;内心会更加从容排查问题#xff0c;应该是特定条件下没有走到索引导致#x…记一次生产慢sql索引优化及思考
问题重现
夜黑风高的某一晚突然收到一条运营后台数据库慢sql的报警耗时竟然达到了60s。看了一下还好不是很频繁内心会更加从容排查问题应该是特定条件下没有走到索引导致如果频繁出现慢查询可能会将数据库连接池打满导致数据库不可用从而导致应用不可用。
问题排查
报警自带定位慢sql语句这个是很早就上线的一条sql语句下面对sql语句进行了简化select * from xxx where gear_idxxx and examinexxx order by id desc limit 10这是个简单的根据流量池gear_id查询按照主键id倒序进行分页查询10条数据的语句。在examine2时查询速度很快但是在examine3时查询速度极慢然后分别在不同的examine下查看执行计划得到的执行计划都是一致的查看执行计划发现possible_keys中有idx_gear_id索引但是实际用到的key却是PRIMARY,并且extra中明确用了where条件进行数据过滤。到现在就明白了这个sql是在主键聚簇索引上进行扫描然后用where语句条件进行过滤时间耗费在这了。这个也解释了为什么examine在不同状态下的耗时不一样取决于where过滤扫描的行数扫描行数越多执行越慢但同一个问题是都没走到我们已有的索引idx_gear_id。当单表数量较小时无论有没有索引或者走主键索引扫描或者普通索引都很快很容易忽略这些问题此时的表现就是你好我好大家好然后随着数据量的增大当达到千万级别或者亿级时慢查询的问题就凸显出来了。
原理剖析
为什么mysql会选择这个不合适的主键聚簇索引?以常用的InnoDb存储引擎为例看一下聚簇索引和非聚簇索引查询区别:聚簇索引通常就是按照每张表的主键构造一颗B树叶子节点中存放的就是整张表的行记录数据即数据和主键都在索引上非聚簇索引表的二级索引字段比如唯一索引联合索引等构造的一颗B树叶子节点存储的是Key字段主键值即非聚集索引的叶节仍然是索引节点但它有一个指向最终数据索引的指针。聚簇索引查询原理非聚簇索引查询原理二级索引查询由以上的索引数据结构可以看出因为聚簇索引将索引和数据保存在同一个B树中因此通常从聚簇索引中获取数据比非聚簇索引更快而非聚簇索引在获取到叶子节点的主键后需要再次查询主键索引即回表查询行记录数据。当然如果查询的列只是索引字段比如查询姓名和年龄可以创建联合索引即索引存储的内容即为需要查询的内容这种查询速度往往比主键索引更快这种索引查询又称为覆盖索引。
什么是回表
将以上的索引数据映射成常见的用户表user的索引为例,上面的聚簇索引就是以id字段为主键的索引,name字段为非聚簇索引还有age等其他表字段是非索引字段示例sqlselect * from user where id 1; 这条 SQL 语句就不需要回表。原因是根据主键的查询方式则只需要搜索 id聚簇索引这棵 B 树就可以查到对应的数据。但当我们使用非聚簇索引 name 这个索引来查询 name b 的记录时就要用到回表。原因是通过 name 这个二级索引查询方式则需要先搜索 name 索引树然后得到主键 id即PK的值为 1再到主键id聚簇索引树再搜索一次。这种根据二级索引查询到主键id,再根据主键id查询主键聚簇索引的过程就称为回表。回到为什么mysql会选择这个不合适的主键聚簇索引问题本身mysql执行器认为使用二级索引查出来的数据太多了还需要基于磁盘做临时存储进行排序然后排序取出10条然后进行回表查询字段性能可能会很差所以采用了直接采用了按顺序扫描主键聚簇索引和where条件gear_idxxx and examinexxx进行对比最多放10条即可这种情况就是数量小的时候没问题但是当数据量大的时候就需要一直扫描所有的数据直到查到符合where条件的10条数据为止同时耗时也急剧增长。
解决问题
为了快速解决问题可以采用强制索引force index即在写sql语句时指定使用具体的索引sql示例 select * from xxx force index (idx_gear_id) where gear_idxxx and examine3 order by id desc limit 10,强制使用idx_gear_id这个索引。以下为使用强制索引的执行计划可以看到实际使用的索引key就是idx_gear_id,执行耗时在几百毫秒运营后台的业务人员完全可接受。
长期优化 由于表的数据越来越多查询条件错综复杂还有用json字段查询问题决定将数据异构到es查询将json字段打平es天然支持复杂的查询条件查询响应更快。 es数据同步方案 在ES数据同步链路中通过京东科技中间件DTS监听数据库的binlog将索引字段查询条件字段及业务唯一id写入ES。 在业务运营查询时根据复杂的查询条件先去ES查询将业务唯一id查出再根据业务唯一id去DB中查询业务明细数据同时解决了业务查询的复杂性和查询性能。 转载自https://juejin.cn/post/7313242116770709530
