Mysql高级——索引优化和查询优化（2）

2023-09-20 21:46:50

5. 排序优化

5.1 排序优化

问题：在 WHERE 条件字段上加索引，但是为什么在 ORDER BY 字段上还要加索引呢？

优化建议：

  • SQL 中，可以在 WHERE 子句和 ORDER BY 子句中使用索引，目的是在 WHERE 子句中避免全表扫描，在 ORDER BY 子句避免使用 FileSort 排序。当然，某些情况下全表扫描，或者 FileSort 排序不一定比索引慢。但总的来说，我们还是要避免，以提高查询效率。

  • 尽量使用 Index 完成 ORDER BY 排序。如果 WHERE 和 ORDER BY 后面是相同的列就使用单索引列；如果不同就使用联合索引。

  • 无法使用 Index 时，需要对 FileSort 方式进行调优。

INDEX a_b_c(a,b,c)
order by 能使用索引最左前缀
- ORDER BY a
- ORDER BY a,b
- ORDER BY a,b,c
- ORDER BY a DESC,b DESC,c DESC
如果WHERE使用索引的最左前缀定义为常量，则order by 能使用索引
- WHERE a = const ORDER BY b,c
- WHERE a = const AND b = const ORDER BY c
- WHERE a = const ORDER BY b,c
- WHERE a = const AND b > const ORDER BY b,c
不能使用索引进行排序
- ORDER BY a ASC,b DESC,c DESC /* 排序不一致 */
- WHERE g = const ORDER BY b,c /*丢失a索引*/
- WHERE a = const ORDER BY c /*丢失b索引*/
- WHERE a = const ORDER BY a,d /*d不是索引的一部分*/
- WHERE a in (...) ORDER BY b,c /*对于排序来说，多个相等条件也是范围查询*/

5.2 案例实战

ORDER BY子句，尽量使用Index方式排序，避免使用FileSort方式排序。
执行案例前先清除student上的索引，只留主键：

DROP INDEX idx_age ON student;
DROP INDEX idx_age_classid_stuno ON student;
DROP INDEX idx_age_classid_name ON student;
#或者
call proc_drop_index('atguigudb2','student');

场景:查询年龄为30岁的，且学生编号小于101000的学生，按用户名称排序

mysql> EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age = 30 AND stuno <101000 ORDER BY
    -> NAME ;
+----+-------------+---------+------------+------+---------------+------+---------+------+--------+----------+-----------------------------+
| id | select_type | table   | partitions | type | possible_keys | key  | key_len | ref  | rows   | filtered | Extra                       |
+----+-------------+---------+------------+------+---------------+------+---------+------+--------+----------+-----------------------------+
|  1 | SIMPLE      | student | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 498917 |     3.33 | Using where; Using filesort |
+----+-------------+---------+------------+------+---------------+------+---------+------+--------+----------+-----------------------------+
1 row in set, 2 warnings (0.00 sec)

查询结果如下：

mysql> SELECT SQL_NO_CACHE * FROM student WHERE age = 30 AND stuno <101000 ORDER BY
    -> NAME ;
+-----+--------+--------+------+---------+
| id  | stuno  | name   | age  | classId |
+-----+--------+--------+------+---------+
| 695 | 100695 | bXLNEI |   30 |     979 |
| 322 | 100322 | CeOJNY |   30 |      40 |
| 993 | 100993 | DVVPnT |   30 |     340 |
| 983 | 100983 | fmUNei |   30 |     433 |
| 946 | 100946 | iSPxRQ |   30 |     511 |
| 469 | 100469 | LTktoo |   30 |      69 |
|  45 | 100045 | mBZrKC |   30 |     280 |
| 635 | 100635 | nQnUJL |   30 |     732 |
|  16 | 100016 | NzjxKh |   30 |     539 |
| 363 | 100363 | OMuKtM |   30 |     695 |
| 293 | 100293 | qOYywO |   30 |     586 |
| 169 | 100169 | qUElsg |   30 |     526 |
| 798 | 100798 | rhHPdX |   30 |      71 |
| 749 | 100749 | TCgaJe |   30 |     697 |
| 157 | 100157 | TUQtvY |   30 |      22 |
| 580 | 100580 | UHDUOj |   30 |     423 |
| 532 | 100532 | XvmZkc |   30 |     861 |
| 939 | 100939 | yBlCbB |   30 |     320 |
| 710 | 100710 | yhmRvD |   30 |     219 |
| 266 | 100266 | YueogP |   30 |     524 |
+-----+--------+--------+------+---------+
20 rows in set, 1 warning (0.16 sec)

结论：type 是 ALL，即最坏的情况。Extra 里还出现了 Using filesort,也是最坏的情况。优化是必须的。

优化思路：

方案一: 为了去掉filesort我们可以把索引建成

#创建新索引
CREATE INDEX idx_age_name ON student(age,NAME);

方案二: 尽量让where的过滤条件和排序使用上索引

建一个三个字段的组合索引：

DROP INDEX idx_age_name ON student;
CREATE INDEX idx_age_stuno_name ON student (age,stuno,NAME);
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age = 30 AND stuno <101000 ORDER BY NAME;
mysql> EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age = 30 AND stuno <101000 ORDER BY NAME;
+----+-------------+---------+------------+-------+--------------------+--------------------+---------+------+------+----------+---------------------------------------+
| id | select_type | table   | partitions | type  | possible_keys      | key                | key_len | ref  | rows | filtered | Extra                                 |
+----+-------------+---------+------------+-------+--------------------+--------------------+---------+------+------+----------+---------------------------------------+
|  1 | SIMPLE      | student | NULL       | range | idx_age_stuno_name | idx_age_stuno_name | 9       | NULL |   20 |   100.00 | Using index condition; Using filesort |
+----+-------------+---------+------------+-------+--------------------+--------------------+---------+------+------+----------+---------------------------------------+
1 row in set, 2 warnings (0.00 sec)

mysql> SELECT SQL_NO_CACHE * FROM student
    ->  WHERE age = 30 AND stuno <101000 ORDER BY NAME ;
+-----+--------+--------+------+---------+
| id  | stuno  | name   | age  | classId |
+-----+--------+--------+------+---------+
| 695 | 100695 | bXLNEI |   30 |     979 |
| 322 | 100322 | CeOJNY |   30 |      40 |
| 993 | 100993 | DVVPnT |   30 |     340 |
| 983 | 100983 | fmUNei |   30 |     433 |
| 946 | 100946 | iSPxRQ |   30 |     511 |
| 469 | 100469 | LTktoo |   30 |      69 |
|  45 | 100045 | mBZrKC |   30 |     280 |
| 635 | 100635 | nQnUJL |   30 |     732 |
|  16 | 100016 | NzjxKh |   30 |     539 |
| 363 | 100363 | OMuKtM |   30 |     695 |
| 293 | 100293 | qOYywO |   30 |     586 |
| 169 | 100169 | qUElsg |   30 |     526 |
| 798 | 100798 | rhHPdX |   30 |      71 |
| 749 | 100749 | TCgaJe |   30 |     697 |
| 157 | 100157 | TUQtvY |   30 |      22 |
| 580 | 100580 | UHDUOj |   30 |     423 |
| 532 | 100532 | XvmZkc |   30 |     861 |
| 939 | 100939 | yBlCbB |   30 |     320 |
| 710 | 100710 | yhmRvD |   30 |     219 |
| 266 | 100266 | YueogP |   30 |     524 |
+-----+--------+--------+------+---------+
20 rows in set, 1 warning (0.00 sec)

结果竟然有 filesort的 sql 运行速度， 超过了已经优化掉 filesort的 sql ，而且快了很多，几乎一瞬间就出现了结果。

结论：

  1. 两个索引同时存在，mysql自动选择最优的方案。（对于这个例子，mysql选择idx_age_stuno_name）。但是， 随着数据量的变化，选择的索引也会随之变化的。
  2. 当【范围条件】和【group by 或者 order by】的字段出现二选一时，优先观察条件字段的过滤数量，如果过滤的数据足够多，而需要排序的数据并不多时，优先把索引放在范围字段上。反之，亦然。

5.3 filesort算法：双路排序和单路排序

双路排序 （慢）

  • MySQL 4.1之前是使用双路排序，字面意思就是两次扫描磁盘，最终得到数据， 读取行指针和order by列，对他们进行排序，然后扫描已经排序好的列表，按照列表中的值重新从列表中读取对应的数据输出

  • 从磁盘取排序字段，在buffer进行排序，再从磁盘取其他字段。

取一批数据，要对磁盘进行两次扫描，众所周知，IO是很耗时的，所以在mysql4.1之后，出现了第二种改进的算法，就是单路排序。

单路排序 （快）

从磁盘读取查询需要的所有列，按照order by列在buffer对它们进行排序，然后扫描排序后的列表进行输出， 它的效率更快一些，避免了第二次读取数据。并且把随机IO变成了顺序IO，但是它会使用更多的空间， 因为它把每一行都保存在内存中了。

结论及引申出的问题

  • 由于单路是后出的，总体而言好过双路
  • 但是用单路有问题

6. GROUP BY优化

  • by 使用索引的原则几乎跟order by一致 ，group by 即使没有过滤条件用到索引，也可以直接使用索引。
  • group by 先排序再分组，遵照索引建的最佳左前缀法则
  • 当无法使用索引列，增大max_length_for_sort_data 和sort_buffer_size 参数的设置
  • where效率高于having，能写在where限定的条件就不要写在having中了
  • 减少使用order by，和业务沟通能不排序就不排序，或将排序放到程序端去做。Order by、groupby、distinct这些语句较为耗费CPU，数据库的CPU资源是极其宝贵的。
  • 包含了order by、group by、distinct这些查询的语句，where条件过滤出来的结果集请保持在1000行以内，否则SQL会很慢。

7. 优化分页查询

优化思路一

在索引上完成排序分页操作，最后根据主键关联回原表查询所需要的其他列内容

mysql> EXPLAIN SELECT * FROM student t,(SELECT id FROM student ORDER BY id LIMIT 2000000,10)
    -> a
    -> WHERE t.id = a.id;
+----+-------------+------------+------------+--------+---------------+---------+---------+------+--------+----------+-------------+
| id | select_type | table      | partitions | type   | possible_keys | key     | key_len | ref  | rows   | filtered | Extra       |
+----+-------------+------------+------------+--------+---------------+---------+---------+------+--------+----------+-------------+
|  1 | PRIMARY     | <derived2> | NULL       | ALL    | NULL          | NULL    | NULL    | NULL | 498917 |   100.00 | NULL        |
|  1 | PRIMARY     | t          | NULL       | eq_ref | PRIMARY       | PRIMARY | 4       | a.id |      1 |   100.00 | NULL        |
|  2 | DERIVED     | student    | NULL       | index  | NULL          | PRIMARY | 4       | NULL | 498917 |   100.00 | Using index |
+----+-------------+------------+------------+--------+---------------+---------+---------+------+--------+----------+-------------+
3 rows in set, 1 warning (0.00 sec)

优化思路二

该方案适用于主键自增的表，可以把Limit 查询转换成某个位置的查询 。

mysql> EXPLAIN SELECT * FROM student WHERE id > 2000000 LIMIT 10;
+----+-------------+---------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| id | select_type | table   | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra       |
+----+-------------+---------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | student | NULL       | range | PRIMARY       | PRIMARY | 4       | NULL |    1 |   100.00 | Using where |
+----+-------------+---------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

8. 优先考虑覆盖索引

8.1 什么是覆盖索引？

理解方式一：索引是高效找到行的一个方法，但是一般数据库也能使用索引找到一个列的数据，因此它不必读取整个行。毕竟索引叶子节点存储了它们索引的数据；当能通过读取索引就可以得到想要的数据，那就不需要读取行了。一个索引包含了满足查询结果的数据就叫做覆盖索引。

理解方式二：非聚簇复合索引的一种形式，它包括在查询里的SELECT、JOIN和WHERE子句用到的所有列
（即建索引的字段正好是覆盖查询条件中所涉及的字段）。简单说就是， 索引列+主键 包含 SELECT 到 FROM之间查询的列。

8.2 覆盖索引的利弊

好处：

  1. 避免Innodb表进行索引的二次查询（回表）

  2. 可以把随机IO变成顺序IO加快查询效率

弊端：
索引字段的维护总是有代价的。因此，在建立冗余索引来支持覆盖索引时就需要权衡考虑了。这是业务DBA，或者称为业务数据架构师的工作。

更多推荐

Learn Prompt-ChatGPT 精选案例:代码助理

你可以使用ChatGPT进行代码生成、生成测试用例、注释、审查和漏洞检测。代码生成​我们可以让ChatGPT自动生成一个排序算法中的快速排序的Python代码。简单的代码生成对于ChatGPT来说小事一桩。测试用例​用例来源出自StuGRua在待测函数函数定义清晰的情况下，单元测试可以通过ChatGPT来生成。写下列代

C语言学习：16、C语言指针

指针是C语言的精髓，很多人都觉得指针难学，是因为内心对指针有所恐惧，把自己吓退了。一种应用语言能有多难，只要了解了指针的本质，学习起来就超级简单。一、什么是指针1.1、指针就是一种变量，一种特殊的变量，就这么简单。这个特殊体现在，指针中保存的值是内存中的地址。前面了解过int型变量，char型变量，float型变量，它

C#-WinForm-发送邮件

登录QQ邮箱——设置——开启“POP3/SMTP服务”登陆QQ邮箱→打开设置→开启“POP3/SMTP服务”，获取“授权码”简单总结一下：1、使用SmtpClient发送电子邮件是很简单的，只要正确创建了MailMessage对象和SmtpClient就可以很容易的发送出去电子邮件。2、如果电子邮件服务器需要身份验证【

CH2--x86系统架构概览

2.1OVERVIEWOFTHESYSTEM-LEVELARCHITECTURE图中的实线箭头表示线性地址，虚线表示段选择器，虚线箭头表示物理地址2.1.1GlobalandLocalDescriptorTables全局描述符表(GDT)GDT是一个全局的段描述符表，它存储在系统内存中的一个固定位置。通常，GDT被加载

【kafka】kafka命令大全

概述本文将分享一些kafka经常使用的一些命令，不断完善中。管理创建主题，3个分区，2个副本对使用了zookeeper的kafkakafka-topics.sh--create--zookeeper192.168.2.140:2181,192.168.2.141:2181,192.168.2.142:2181--rep

iPhone 15秋季发布会召开，媒介盒子多家媒体持续报道

现如今互联网引流成本越来越高不说,难度越来越大,大多数都是投入巨大,收效甚微。因此,用有限的成本带来高回报的效果成为企业共同的追求。当然,企业想要产品服务引流绝非易事。为什么你的品牌营销不见效?新产品上市要怎么做宣传?盒子以新发布的苹果15为例分析,给你一些启发。1、时机很重要9月13日,苹果秋季发布会如期而至,各大媒

AERMOD模型在大气环境影响评价中的应用

随着我国经济快速发展，我国面临着日益严重的大气污染问题。近年来，严重的大气污染问题已经明显影响国计民生，引起政府、学界和人们越来越多的关注。大气污染是工农业生产、生活、交通、城市化等方面人为活动的综合结果，同时气象因素是控制大气污染的关键自然因素。大气污染问题既是局部、当地的，也是区域的，甚至是全球的。本地的污染物排放

【web开发】8、Django（3）

提示：文章写完后，目录可以自动生成，如何生成可参考右边的帮助文档文章目录前言一、管理员1.表结构2.layout.html文件下添加管理员账号的导航3.urls.py文件（POST请求传递nid）4.form.py文件(密码加密，确认密码，重置密码与原密码要求不一致)5.admin.py文件二、中间件（实现登录校验）1

【JAVA】idea初步使用+JDK详细配置

1、官方下载idea官网：DownloadIntelliJIDEA–TheLeadingJavaandKotlinIDE(1)、下载教程我下载没截屏，详细教程请看原文：手把手教你JDK+IDEA的安装和环境配置_idea配置jdk_快到锅里来呀的博客-CSDN博客2、启动项目时候需要配置JDK环境(1)、下载JDK官网

Jmeter安装与测试

目录一：JMeter简介：二：JMeter安装与配置三：JMeter主要原件一：JMeter简介：JMeter，一个100％的纯Java桌面应用，由Apache组织的开放源代码项目，它是功能和性能测试的工具。具有高可扩展性、支持Web(HTTP/HTTPS)、SOAP、FTP、JAVA等多种协议的特点。官方网站：htt

方案：浅析利用AI智能识别与视频监控技术打造智慧水产养殖监管系统

一、方案背景针对目前水产养殖集约、高产、高效、生态、安全的发展需求，基于智能传感、智慧物联网、人工智能、视频监控等技术打造智慧水产系统，成为当前行业的发展趋势。传统的人工观察水产养殖方式较为单一，难以及时发现人员非法入侵、偷盗、偷钓、水质污染等管理问题。二、方案概述TSINGSEE青犀视频智慧水产养殖方案主要是围绕视频

热文推荐
  1. 关于安卓SVGA浅尝（二）加载数据

    关于安卓SVGA浅尝（二）加载数据相关链接SVGA官网SVGA-github说明文档背景项目开发，都会和动画打交道，动画的方案选取，就有很多选择。如Json动画，svga动画，gif等等。各有各的优势。目前项目中用到了svga的动画，因此，就有了这一系列的文章。实现对于svga的加载方法，有以下几种：（1）decode

  2. VR全景智慧文旅解决方案，助力文旅产业转型升级

    引言：随着科技的不断发展，虚拟现实（VR）技术正逐渐展露其影响力，改变着旅游业。VR全景智慧文旅解决方案也应运而生，将传统旅游的体验形式从线下扩展到了线上，带来了不一般的文旅体验。一.VR全景技术的基础VR全景技术是通过结合虚拟现实和全景摄影技术来模拟现实世界的方法。通过智能设备，游客可以在任何时间，任何地点进入逼真的

  3. 5个超实用的Python代码片段

    迷途小书童读完需要7分钟速读仅需3分钟本篇收集了5个常用的Python代码片段，非常实用，可以帮助我们更高效的编写代码，实现我们想要实现的功能。1密码生成器importrandomimportstringtotal=string.ascii_letters+string.digits+string.punctuatio

  4. 内网横向移动

    内网横向移动当攻击者在拿下一台内网主机后，通常会利用当前拿下的机器当作跳板，进一步攻击内网其他主机，扩大攻击影响范围。攻击机：KaliLinux靶机：Windowsserver2008WEB10.10.10.20\192.168.XX.XX(跳板机)Windowsserver2012DC10.10.10.10\192.

  5. RS485总线浪涌解决方案！|深圳比创达EMC

    在各种通讯方式当中，RS485总线是较为常见的一种，因其接口简单、系统运行稳定和通讯速率高等特点被广泛应用。但由于RS485总线一般传输距离较长，且经常暴露在外，所以非常容易受到电磁干扰，而浪涌干扰已经成为485通信使用中最常遇到的问题。下面通过一个实际案例，来说明RS485总线在防浪涌实验过程中，常遇到的问题以及需要

  6. shell中[[]]与[]，=、==和-eq的辨析

    1、==、=和-eq在shell中，=和==运算符都可以用于判断两个字符串、两个字符串变量是否相同，==支持模式匹配，而=不支持模式匹配。使用-eq来判断两个整数是否相等。#字符串比较#给变量赋值时，等号前后没有空格，有空格时是条件判断string1=abcstring2=bcd#["$string1"="$strin

  7. 基座向量施密特正交化

    最近再次细细的阅读了向量施密特正交化，重新系统梳理一下一、正交基地与向量的正交分解二、基化成标准正交基，是什么意思将一个向量空间中的基向量通过某种方式转化为一组标准正交基，是指将原有的基向量进行调整，使得它们满足两个条件：彼此之间两两正交（即内积为零）；归一化（长度为1）。这样的转换可以用来简化向量空间中的运算，使得向

  8. Linux 本地Yearning SQL 审核平台远程访问

    文章目录前言1.Linux部署Yearning2.本地访问Yearning3.Linux安装cpolar4.配置Yearning公网访问地址5.公网远程访问Yearning管理界面6.固定Yearning公网地址前言Yearning简单,高效的MYSQL审计平台一款MYSQLSQL语句/查询审计工具，为DBA与开发人员

  9. Redis的主从复制，哨兵和Cluster集群

    一、Redis的高可用1.1redis高可用的概念在web服务器中，高可用是指服务器可以正常访问的时间，衡量的标准是在多长时间内可以提供正常服务(99.9%、99.99%、99.999%等等)。高可用的计算公式是1-（宕机时间）/（宕机时间+运行时间）有点类似与网络传输的参数误码率，我们用9的个数表示可用性：2个9：9