专栏首页码云大作战索引与慢sql剖析

索引与慢sql剖析

一、使用explain语句查看索引使用情况

CREATE TABLE `test_user` (

`user_id` int(9) NOT NULL AUTO_INCREMENT,

`age` int(4) DEFAULT '0',

`phone` varchar(16) DEFAULT '',

`score` int(4) DEFAULT '0',

PRIMARY KEY (`user_id`),

KEY `idx_score` (`score`) USING BTREE

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

创建表testUser,并建立user_id - 主键id、age - 年龄、phone - 手机号、score - 分数字段,并给score建立普通索引。

在表中插入三条语句(1,16,1358,85)、(2,15,1236,88)、(3,16,1387,89)

· EXPLAIN select * from test_user - 查看索引使用情况

运行结果如上图,发现未使用索引,并且sql扫描行数为3行,为全表扫描。说明,不使用索引的情况下数据库会进行全表扫描来查询你所需要的数据。

· EXPLAIN select * from test_user where user_id = 2

运行结果如上图,发现使用了主键索引,并且sql扫描行数为1行。说明在使用索引的情况下,数据库会有效减少扫描行数。

· EXPLAIN select * from test_user where score = 88

运行结果如上图,发现使用了普通索引,并且扫描行数也为1行,有效减少了扫描行数。

· EXPLAIN select * from test_user where user_id != 0

运行结果如上图,虽然使用了主键索引,但是扫描行数为4行,属于全表扫描。

· 结论

虽然加索引可以帮助你减少数据库的扫描行数,但是一些不当的sql语句即使命中了索引,也会导致全表扫描,发生慢sql。索引和慢sql的产生不代表有必然的联系。

在数据库配置中存在long_query_time参数,用于设置sql执行时间,当执行时间超过了设置的该值,则说明执行的sql为慢sql,会被记录到慢sql日志中,在生产环境也有可能会被kill掉。long_query_time参数的默认时间为10s。

二、索引结构

· 主键索引B+树结构

当插入语句时,索引也会被保存起来。索引中也使用了B+树结构来增加查询性能,如上图画了一个简单版本的B+树用来存放主键索引。

当执行select * from test_user where user_id = 2语句时,会根据主键索引定位到id等于2的数据,并且扫描索引树后扫描行数只有1行。

· 普通索引B+数结构

执行select * from test_user where score = 88语句时,根据普通索引idx_score定位到score=88。

执行select * from test_user where score != 0语句时,根据普通索引执行过程,会定位到第一个score不为0的叶节点,然后像右开始扫描索引树,虽然这里用到了索引,但是仍扫描了整个索引树。

三、索引过程中的回表和下推

· 回表

上图为执行select * from test_user where score = 88语句时回表的过程。根据普通索引,会定位到score=88的叶子节点,得到score=88的主键id为2,并根据主键id去主键索引中获取该主键id为2的数据信息并返回。

回表的基本过程就是这样,但是实际上在sql执行中,最耗费时间的就是回表,假设我的这张学生表有几千万的数据,并且分数为88的有1000w人,那么上述的查询sql就会回表1000w次,导致慢sql。

所以在我们平时的sql使用中,也要减少回表的次数,比如可以使用分页等来减少查询行数,减少回表次数。

· 下推

增加用户表的name - 姓名字段,并创建联合索引(name+age)

CREATE TABLE `test_user` (

`user_id` int(9) NOT NULL AUTO_INCREMENT,

`age` int(4) DEFAULT '0',

`phone` varchar(16) DEFAULT '',

`score` int(4) DEFAULT '0',

`name` varchar(16) DEFAULT '',

PRIMARY KEY (`user_id`),

KEY `idx_score` (`score`) USING BTREE,

KEY `idx_name_and_age` (`name`,`age`) USING BTREE

) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4;

联合索引的索引结构B+树也类似,如下图:

现在需要查询出姓为张,并且年龄为15岁的用户。sql语句为:

select * from test_user where name like '张%' and age = 15;

在mySql5.5之前的执行过程如下:

首先会在联合索引中找到第一个姓张的叶子节点,查询出主键id为1,然后回表去主键索引中找到主键id为1的用户数据。判断该主键为1的用户的年龄是否为15,如果是则作为结果集的一行返回,如果不是则查询下一个数据。

如果姓张的用户有1000w数据,那么他们不管年龄是不是15,都需要经过1000w次的回表。

在mySql5.6版本之后,引入了索引下推的优化,提升了联合索引查询的性能。执行过程如下:

首先在联合索引树上找到第一个姓张的叶子节点,并判断该联合索引中的age的值是否为15,如果是则取出id,进行回表操作,并作为结果集返回。如果不是则通过当前叶子节点进行向右遍历进行下一个查询。

如果姓张的用户有1000w,年龄为15的有200人。那么mysql5.5则需要经过1000w次回表查询,而mysql5.6只需要经过200次回表查询。有了下推的优化概念,大大的减少了回表次数,提升了查询性能。

四、总结

· 使用索引了的sql语句也会产生慢查询,慢查询与索引是否使用没有必然联系。

· 上文中导致慢查询发生的情况全表扫描、全索引扫描、频繁回表的开销,如果发生慢sql,优化思路可以考虑如何减少这三种情况的发生,提高索引的过滤性。

本文分享自微信公众号 - 码云大作战(gh_9b06dbcb85f3),作者:Y

原文出处及转载信息见文内详细说明,如有侵权,请联系 yunjia_community@tencent.com 删除。

原始发表时间:2020-05-28

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

我来说两句

0 条评论
登录 后参与评论

相关文章

  • Mybatis源码学习(四)拦截器与插件原理

    回顾前几文加载mybatis时,会通过sqlSessionFactoryBuilder的build方法对xml文件进行解析,解析成document树后,...

    虞大大
  • 多线程应用 - 阻塞队列ArrayBlockingQueue详解

    ArrayBlockingQueue是一个阻塞式的先进先出队列。该结构具有以下三个特点:

    虞大大
  • HashMap源码分析 - jdk8

    HashMap可以说是我们在实际开发中最经常使用到的集合类,并且在面试中也是必问的知识点。这篇文章主要是根据JDK8的HashMap来进行分析。

    虞大大
  • MySQL DBA基本知识点梳理和查询优化

    本文主要是总结了工作中一些常用的操作,以及不合理的操作,在对慢查询进行优化时收集的一些有用的资料和信息,本文适合有MySQL基础的开发人员。

    数据和云
  • mysql基本知识点梳理和查询优化

    本文主要是总结了工作中一些常用的操作,以及不合理的操作,在对慢查询进行优化时收集的一些有用的资料和信息,本文适合有mysql基础的开发人员

    周三不加班
  • 深入剖析 MySQL 索引和 SQL 调优实战(珍藏版)

    MySQL支持诸多存储引擎,而各种存储引擎对索引的支持也各不相同,因此MySQL数据库支持多种索引类型,如BTree索引,哈希索引,全文索引等等。

    lyb-geek
  • 性能评测:MyBatis 与 Hibernate 的性能差异

    核心都是将关系型数据库和数据转成对象型。当前流行的方案有Hibernate与myBatis。

    芋道源码
  • 数据库进阶2 Mysql高并发优化

    如果不能设计一个合理的数据库模型,不仅会增加客户端和服务器段程序的编程和维护的难度,而且将会影响系统实际运行的性能。所以,在一个系统开始实施之前,完备的数据库模...

    范蠡
  • 一文搞定MySQL性能调优

    数据库的操作越来越成为整个应用的性能瓶颈,这对于Web应用尤其明显。关于数据库的性能,这并不只是DBA需要关心的,而更是后端开发需要去关注的事情。

    全菜工程师小辉
  • 聊聊Mysql优化之索引优化

    索引是存储引擎用于快速找到记录的一种数据结构。尤其是当表的数据量越来越大的时候,正确的索引对查询性能的提升尤为明显。但在日常工作中,索引却常常被忽略,甚至被误解...

    黄泽杰

扫码关注云+社区

领取腾讯云代金券