自学内容网 自学内容网
自学内容网 > 正文

SQL优化，我就用了这几招

🕗 发布于 2024-10-15 00:01 sql  数据库  后端  java  

先赞后看，Java进阶一大半

阿里巴巴社区博客最近发表了一篇探究MySQL索引策略的博客，下图是一条查询SQL的执行过程。

在这里插入图片描述

我是南哥，相信对你通关面试、拿下Offer有所帮助。

敲黑板：本文总结了MySQL语句优化、索引优化常见的面试题！

⭐⭐⭐收录在《Java学习/进阶/面试指南》：https://github/JavaSouth

1. SQL优化

1.1 慢查询

面试官：知道MySQL慢查询吗？

MySQL的慢查询日志可以记录执行时间超过阈值的SQL查询语句，所以我们可以利用该日志查找出哪些SQL语句执行效率差，从而对SQL语句进行优化。

MySQL5.7以上版本可以通过SET命令来开启慢查询日志。

     SET GLOBAL slow_query_log=ON;
     SET GLOBAL long_query_time=2;
     SET SESSION long_query_time=2;

开启完慢查询日志，我们找到该日志的位置，打开文件即可查询慢查询的SQL。

     ＃　查询慢查询日志文件位置
     SHOW VARIABLES LIKE '%slow_query_log_file%';

在这里插入图片描述

打开DESKTOP-ALU4BOC-slow.log文件，找到慢查询SQL为：select sleep(11)。

D:\MySQL\bin\mysqld, Version: 5.5.40 (MySQL Community Server (GPL)). started with:
TCP Port: 3306, Named Pipe: MySQL
Time                 Id Command    Argument
# Time: 220828 21:40:28
# User@Host: root[root] @ localhost [127.0.0.1]
# Query_time: 11.004454  Lock_time: 0.000000 Rows_sent: 1  Rows_examined: 0
use mysql;
SET timestamp=1661694028;
select sleep(11);

1.2 表设计优化

面试官：在工作中你怎么优化SQL的？

业务开发中涉及数据库的第一步是表设计，要优化SQL就要从第一步开始做起。

MySQL表设计要尽可能满足数据库三大范式，帮助大家回顾下：

  1. 第一范式：数据库表中的每一列都是不可再分的属性，属性相近或相同的列应该合并。

  2. 第二范式：满足第一范式的条件下，一个表只能描述一个对象。如果某些列经常出现数据重复，应该把这些列作为另一个表。

  3. 第三范式：满足第二范式的条件下，表中的每一列都只能依赖于主键，即直接与主键相关。

我们在业务开发中遇到反第二范式的情况是最多的，例如以下订单明细表的设计，每一个订单明细都包含了重复的商品名称、商品单位、商品价格，这三个字段属于字段冗余存储。如果表的数据量级很大，那造成的冗余存储量是可想而知的，而且最要命的问题是如果要修改某一个商品名称，那所有的订单明细数据都要修改。

在这里插入图片描述

我们可以遵循第三范式，把冗余的字段抽出一个新的商品表，当要查询订单明细时只需要把两表通过商品id进行连接即可。

在这里插入图片描述

在这里插入图片描述

面试官：遵循第二范式就一定最优？

遵循第二范式的表设计不一定是最优的情况，还是那句话，要根据实际的业务场景权衡利弊。

虽然把冗余数据抽离出去了，但却增加了表的数量，也意味着查询数据时表之间的join连接操作也会变多。而join连接的性能是比较低的，有可能join操作会成为数据库性能的瓶颈。

1.3 SQL语句优化

面试官：还有呢？

SQL优化除了做好表设计的优化工作，还需要对SQL语句进行优化。而SQL查询语句的优化主要从覆盖索引、避免索引失效、减少不必要的查询三个方面入手。

一、从覆盖索引的角度。

order by排序的字段要尽量覆盖索引。如果使用非索引字段进行排序，MySQL会进行额外的文件排序，将查询结果根据非索引列在磁盘中再排序一次。当我们使用explain关键字分析SQL时会发现Extra会出现Using filesort。

group by分组要尽量覆盖索引。如果使用非索引字段进行分组，MySQL只能进行全表扫描后建立临时表才能得出分组结果。

另外我们可以使用explain关键字来分析SQL语句的效率，查看SQL语句是否覆盖索引。

二、从避免索引失效的角度。

关于如何避免索引失效，大家可以阅读我出版的《JavaGetOffer》专栏关于【MySQL索引】的文章。

三、从减少不必要的查询的角度。

如果只需要查询部分列，尽量不要使用select *查询，防止造成不必要的资源消耗、占用过多的网络带宽。

1.4 索引如何设计

面试官：在工作中，表索引你怎么设计的？

索引的设计有以下设计原则，大家在实际业务开发中应该尽量遵循这些原则，可以帮你避开不少坑。

  1. 经常进行order by排序、group by分组、join多表联结查询的字段应该建立索引。

  2. 经常在where子句中出现的字段应该建立索引。

  3. 尽量使用数据量小的字段建立索引。例如对于char(500)和char(10)两个字段类型来说，肯定是以后者进行索引匹配的速度更快。

  4. 如果需要建立索引的字段值比较长，可以使用值的部分前缀来建立索引。

    例如varchar类型的name字段，我们需要根据前三个字符来建立前缀索引，可以使用以下SQL命令：ALTER TABLE example_table ADD INDEX index_name (name(3))。

面试官：那索引建立越多，查询效率就越高吗？

另外大家记住一点，索引不是建立越多越好。合理设计的索引确实能大大提高SQL效率，但每建立一个字段索引，MySQL就要为该索引多维护一棵B-Tree，越多的索引会造成表更新效率变得低下。

2. 索引优化

面试官：索引有什么用？

大家可以把你最近最爱的一本书类比成一个MySQL数据库，你要快速翻到你昨天看到的精彩部分，是不是要先看下书的目录索引，要翻到第几章、第几页。

数据库最主要的就是数据存储，其次就是提供复杂查询服务，而索引就是MySQL作为快速找到记录的一种数据结构。索引类型有多种，像常见的B树索引、哈希索引，这些都需要我们去掌握。

不要和我说你看书都用书签，或者靠手感就能翻出来昨天看到的地方。

我们对比下不采用索引和采用索引的差异。

目前我本机数据库的article表有10w条数据，表结构如下。

CREATE TABLE `article`  (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `author_id` int(10) NULL DEFAULT NULL,
  `category_id` int(10) NOT NULL DEFAULT 0,
  `views` int(10) NULL DEFAULT NULL,
  `comments` int(10) NULL DEFAULT NULL,
  `title` varbinary(255) NULL DEFAULT NULL,
  `content` text CHARACTER SET utf8 COLLATE utf8_general_ci NULL,
  PRIMARY KEY (`id`, `category_id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1001 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;

没建立索引前，使用explain关键字分析查询SQL。type显示ALL，也就是该SQL执行时对MySQL进行的是全表扫描。

explain select id from article where category_id = 1 order by views desc;

+----+-------------+---------+------+---------------+------+---------+------+------+-----------------------------+
| id | select_type | table   | type | possible_keys | key  | key_len | ref  | rows | Extra                       |
+----+-------------+---------+------+---------------+------+---------+------+------+-----------------------------+
|  1 | SIMPLE      | article | ALL  | NULL          | NULL | NULL    | NULL | 102279 | Using where; Using filesort |
+----+-------------+---------+------+---------------+------+---------+------+------+-----------------------------+

建立索引后。

create index idx_ca_vi on article(category_id,views);

type显示为ref，同时Extra列显示Using where; Using index，Using index代表该SQL执行时使用了索引，而Using index代表了在MySQL服务端再进行了一次views字段的排序。

+----+-------------+---------+------+---------------+-----------+---------+-------+------+-------------+
| id | select_type | table   | type | possible_keys | key       | key_len | ref   | rows | Extra       |
+----+-------------+---------+------+---------------+-----------+---------+-------+------+-------------+
|  1 | SIMPLE      | article | ref  | idx_ca_vi     | idx_ca_vi | 4       | const |    51139 | Using where; Using index |
+----+-------------+---------+------+---------------+-----------+---------+-------+------+-------------+

2.1 B-Tree索引

面试官：B树索引说一下？

在杂乱无章的一堆数字里，我要你快速找到唯一的一个数字66，大家要怎么做？

两种选择，你在一堆数字里一个个地找，就如MySQL全表扫描。或者把所有数都按大小顺序进行排列，找到第66个位置的数字。

我们假设建立的是主键索引，MySQL索引会根据主键id建立起一棵B-Tree。B-Tree类似于二叉搜索树，同样具有快速查找特定值的功能。

（1）但在结构方面，B-Tree又不同于二叉搜索树，它是多子树的。即每一个节点可以有两棵以上的子树。

（2）在值的存储方面，B-Tree所有的值都存储在叶子节点。并且每一个叶子节点可以存储多个元素，这一点也与二叉搜索树不同。两个人想要去湖里打水，一个人拿着手大的碗，一个人拿着一个水桶，拿水桶的不会比拿碗的装的少。每个叶子节点存储的元素多，每次磁盘访问就可以获得更多的数据，从而减少查询的I/O操作。

面试官经常会问你这个问题，叶子节点是什么数据结构？。实际上叶子节点之间用指针链接形成了一串双向链表。这个留到下文解释。

（3）另外大家很容易漏掉一个重要的知识点。如果是二级索引建立的B-Tree，每个叶子节点的值保存的是对应行数据的主键。那一级索引叶子节点保存什么呢？一级索引也就是主键索引，下文我会告诉大家。

在这里插入图片描述

2.2 B-Tree值的存储

面试官：你说值都存储在叶子节点，那有什么好处？

数据库数据都存储在叶子节点，会使得非叶子节点层数更少。从外表来看，很明显整棵B-Tree的层数变少，B-Tree高度变得矮胖。

B-Tree变得矮胖有什么作用？举个爬楼梯的例子，B-Tee的每一层级就像一层楼。相信大家租房都不想租高楼，每次回去都要爬那么多层楼梯，膝盖怎么受得了呢。

B-Tree每一层的搜索可能就代表了一次磁盘I/O操作，B-Tree的层数变少意味着I/O读取的次数就变少，查询的效率也会因此提高。

另外企业业务在查询上更多的是范围查询，你对网页的每一次翻页操作都是对MySQL数据的一次范围查询。B-Tree的元素都存储叶子节点，同时形成双向链表结构，很适合范围查询这种复杂查询操作。

2.3 哈希索引

面试官：知道为什么主流数据库引擎不采用哈希索引吗？

企业业务上一般都是范围查询，而哈希索引由于其底层数据结构，不能够支持任何范围查询。这也难怪主流数据库引擎不青睐它。

但其实哈希索引也有它的闪光灯，哈希索引会为所有的索引列计算一个哈希码。同时在哈希表中保存哈希码和指向每个数据行的指针，这种结构对精确匹配查询的效率极高。

MEMORY数据库引擎底层采用的就是哈希索引。

2.4 聚簇索引

面试官：聚簇索引和二级索引有什么关联？

读到这里，我回答下上文还没回答大家的问题。

首先，聚簇索引和主键索引是等同的，也有一个一般都不提的名称：一级索引。

而B-Tree的二级索引指的是非主键索引，它的叶子节点保存的只是行的主键值，所以需要另外通过主键来找到行数据。

聚簇索引通过主键来建树，它的叶子节点包含了行的全部数据。

这就把两者相关联起来了，通过二级索引查找行，需要先在二级索引建立的B-Tree上找到主键的值，接着再从聚簇索引建立的B-Tree找到行数据。

3. 索引效率

3.1 Explain关键字

面试官：那我一条SQL，我怎么知道它有没使用到索引？

检查是否使用索引可以利用Explain关键字来分析，它会模拟执行sql语句，查询出sql语句执行的相关信息，如哪些索引可以被命中、哪些索引实际被命中。

我说下Explain查询结果的几个关键字段。

  • type

    • cost：通过索引一次查询
    • ref：使用到索引
    • range: 使用到索引
    • all：全表扫描

  • Extra

    • using filesort：使用外部文件排序，发生在无法使用索引的情况下

    • using index：where查询的列被索引覆盖，直接通过索引就可以查询到数据

    • using where：where查询的列，没有全部被索引覆盖

    • using join buffer：使用了连接缓存

  • possible_key

    表示可以使用的索引

  • key

    表示实际使用的索引

如果简历你写了精通MySQL，那问的可就没这么简单。我可以问你在工作中紧急处理了哪些数据库重大事故，优化了哪些业务慢SQL、是怎么优化的、为什么这么做。

3.2 索引失效

面试官：有没索引失效的情况呢？

索引失效一般是这个SQL查询破坏了使用B-Tree查询的条件。也有一种可能出现，如果表数据膨胀得太快，即使建立索引你查询起来也会有索引失效的错觉，这个问题就要另外讨论了。

  1. 如果在where子句中使用not in、!=和＜＞操作，会使索引失效而导致进行全表扫描。

  2. 对索引列进行数学函数处理的话，索引会失效。

  3. 索引是字符串类型，查询值没有添加单引号’'那索引会失效。因为值类型与索引列类型。不一致，MySQL不会使用索引，而是把索引列数据进行类型转换后进行查询。

  4. 对索引列进行模糊查询，%要放在最右侧，否则索引会失效。SELECT * FROM user WHERE name LIKE n%

  5. 在组合索引中，如果前一个索引使用范围查询，后面的索引也会失效。

大家在实际工作切忌乱加索引，此切忌非切记。每加一次索引，MySQL都要多去维护一棵新的B-Tree。增加太多索引，数据查询效率会变得低下。

⭐⭐⭐本文收录在《Java学习/进阶/面试指南》：https://github/JavaSouth

我是南哥，南就南在Get到你的点赞点赞点赞。

在这里插入图片描述

创作不易，不妨点赞、收藏、关注支持一下，各位的支持就是我创作的最大动力❤️


原文地址：https://blog.csdn.net/hdgaadd/article/details/142924992

免责声明：本站文章内容转载自网络资源，如本站内容侵犯了原著者的合法权益，可联系本站删除。更多内容请关注自学内容网（zxcms.com）！

相关文章

自学内容网
Copyright © 2015-2024