记面试中问到的MySQL的SQL调优问题

那就加个鸡腿吧 2020-11-07 16:43:52
面试 Mysql InnoDB BTree


目录

  • 1、场景还原
  • 2、名词解释
  • 3、实践情况
  • 4、个人观点
  • 5、引申问题

1、场景还原

面试官:小伙子,听说你会SQL调优,那我这里有一条SQL,你来帮我调优一下。SQL如下: SELECT * FROM T LIMIT 899999, 10;

表结构如下:

id int(10) primary key,
其他字段……
我:¿¿¿(缓缓打出反问号),这条SQL要干嘛,随机找10条数据吗?
面试官:哦,不好意思,忘记加上排序了SELECT * FROM T ORDER BY id LIMIT 899999, 10;
我:如果id是连续不中断的话可以这样写。 SQL如下:SELECT * FROM T where id >= 899999 LIMIT 0, 10;
面试官:那假如id不是连续的呢?
我:(略加思考)单从SQL优化层面我优化不了了。
面试官:……那我们进行下一个话题……
我:……
面试官:那最后你有什么问题想问我的吗?
我:刚才那条SQL从SQL层面上该如何优化?
面试官:你可以这样写:SELECT * FROM T t where t.id >= (SELECT f.id FROM T f ORDER BY id DESC LIMIT 899999, 1) LIMIT 0, 10;
我:(假装略加思考)你确定这样写会比图一这条SQL:SELECT * FROM T ORDER BY id LIMIT 899999, 10;更快吗?在图一的SQL里已经是通过主键索引去查询数据了,你图二的SQL并没有改变原有的查找方式。如果你原有SQL是通过其他字段去排序的,用的是非主键索引,例如:SELECT * FROM T ORDER BY createdTime LIMIT 899999, 10;那么你第二条SQL的写法确实会比第一条SQL快得多,因为在MySQL里非主键索引与主键索引在查找上的区别是,非主键索引他存储的是索引列与主键列的值,查找具体的值还需要一次回表过程,而主键索引存储了是整行数据,不需要再次回表[^①]查找,减少了一次查找过程。
面试官:……
我:……

2、名词解释

回表

指查找时通过非主键索引(非聚簇索引)去查找对应的记录的主键后,仍需要根据主键进行再一次的查找,这是MySQL的索引结构引起的。在MySQL中,主键索引(聚簇索引)存储的是一整行的数据,而非主键索引(非聚簇索引)存储的是主键列的值。

假如有这样一张表

CREATE TABLE person (
id int(11) NOT NULL,
name varchar(32) NOT NULL,
age int(11) NOT NULL,
PRIMARY KEY (id),
KEY idx_person_age (age)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

那这张表的索引具体表现形式如下:

主键索引:

非主键索引:

所以当我们需要查找age=30的数据时,他的查找过程如下图:

3、实践情况

如今我有这样一张表:
CREATE TABLE file (
id int(11) NOT NULL AUTO_INCREMENT,
name varchar(255) DEFAULT NULL,
created_time datetime NOT NULL,
updated_time datetime NOT NULL,
PRIMARY KEY (id),
KEY idx_file_created_time (created_time) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=954185 DEFAULT CHARSET=utf8mb4;
表的数据数量为:
先来看看面试官说的情况

可以看出,查询时间并没有很大的差距,这是因为这两条SQL都是走的主键索引,方式并没有很大的差别
而我列举的情况,通过时间字段进行排序,让其产生回表操作的过程。

可以看出,查询时间出现了很大的差距,这是由于回表操作所造成的。

4、个人观点

这个场景就发生在我上星期的面试中,我不知道这个面试官出这个题时是故意留坑还是什么情况,但是在后面他给出他那答案时,实在是让我惊讶,希望他是一时的错误,若是他对SQL的本身理解就不熟悉,那就……………………

5、引申问题

(1)在file表中,假如我所查询的数据列只有id、created_time与updated_time,那么还可以怎么做?
(2)当真的只能通过id进行排序分页时,我们该怎么做?
版权声明
本文为[那就加个鸡腿吧]所创,转载请带上原文链接,感谢
https://my.oschina.net/u/4214257/blog/4707518

  1. 【计算机网络 12(1),尚学堂马士兵Java视频教程
  2. 【程序猿历程,史上最全的Java面试题集锦在这里
  3. 【程序猿历程(1),Javaweb视频教程百度云
  4. Notes on MySQL 45 lectures (1-7)
  5. [computer network 12 (1), Shang Xuetang Ma soldier java video tutorial
  6. The most complete collection of Java interview questions in history is here
  7. [process of program ape (1), JavaWeb video tutorial, baidu cloud
  8. Notes on MySQL 45 lectures (1-7)
  9. 精进 Spring Boot 03:Spring Boot 的配置文件和配置管理,以及用三种方式读取配置文件
  10. Refined spring boot 03: spring boot configuration files and configuration management, and reading configuration files in three ways
  11. 精进 Spring Boot 03:Spring Boot 的配置文件和配置管理,以及用三种方式读取配置文件
  12. Refined spring boot 03: spring boot configuration files and configuration management, and reading configuration files in three ways
  13. 【递归,Java传智播客笔记
  14. [recursion, Java intelligence podcast notes
  15. [adhere to painting for 386 days] the beginning of spring of 24 solar terms
  16. K8S系列第八篇(Service、EndPoints以及高可用kubeadm部署)
  17. K8s Series Part 8 (service, endpoints and high availability kubeadm deployment)
  18. 【重识 HTML (3),350道Java面试真题分享
  19. 【重识 HTML (2),Java并发编程必会的多线程你竟然还不会
  20. 【重识 HTML (1),二本Java小菜鸟4面字节跳动被秒成渣渣
  21. [re recognize HTML (3) and share 350 real Java interview questions
  22. [re recognize HTML (2). Multithreading is a must for Java Concurrent Programming. How dare you not
  23. [re recognize HTML (1), two Java rookies' 4-sided bytes beat and become slag in seconds
  24. 造轮子系列之RPC 1:如何从零开始开发RPC框架
  25. RPC 1: how to develop RPC framework from scratch
  26. 造轮子系列之RPC 1:如何从零开始开发RPC框架
  27. RPC 1: how to develop RPC framework from scratch
  28. 一次性捋清楚吧,对乱糟糟的,Spring事务扩展机制
  29. 一文彻底弄懂如何选择抽象类还是接口,连续四年百度Java岗必问面试题
  30. Redis常用命令
  31. 一双拖鞋引发的血案,狂神说Java系列笔记
  32. 一、mysql基础安装
  33. 一位程序员的独白:尽管我一生坎坷,Java框架面试基础
  34. Clear it all at once. For the messy, spring transaction extension mechanism
  35. A thorough understanding of how to choose abstract classes or interfaces, baidu Java post must ask interview questions for four consecutive years
  36. Redis common commands
  37. A pair of slippers triggered the murder, crazy God said java series notes
  38. 1、 MySQL basic installation
  39. Monologue of a programmer: despite my ups and downs in my life, Java framework is the foundation of interview
  40. 【大厂面试】三面三问Spring循环依赖,请一定要把这篇看完(建议收藏)
  41. 一线互联网企业中,springboot入门项目
  42. 一篇文带你入门SSM框架Spring开发,帮你快速拿Offer
  43. 【面试资料】Java全集、微服务、大数据、数据结构与算法、机器学习知识最全总结,283页pdf
  44. 【leetcode刷题】24.数组中重复的数字——Java版
  45. 【leetcode刷题】23.对称二叉树——Java版
  46. 【leetcode刷题】22.二叉树的中序遍历——Java版
  47. 【leetcode刷题】21.三数之和——Java版
  48. 【leetcode刷题】20.最长回文子串——Java版
  49. 【leetcode刷题】19.回文链表——Java版
  50. 【leetcode刷题】18.反转链表——Java版
  51. 【leetcode刷题】17.相交链表——Java&python版
  52. 【leetcode刷题】16.环形链表——Java版
  53. 【leetcode刷题】15.汉明距离——Java版
  54. 【leetcode刷题】14.找到所有数组中消失的数字——Java版
  55. 【leetcode刷题】13.比特位计数——Java版
  56. oracle控制用户权限命令
  57. 三年Java开发,继阿里,鲁班二期Java架构师
  58. Oracle必须要启动的服务
  59. 万字长文!深入剖析HashMap,Java基础笔试题大全带答案
  60. 一问Kafka就心慌?我却凭着这份,图灵学院vip课程百度云