【数据库】聊聊常见的索引优化-上

数据库对于现有互联网应用来说，其实是非常重要的后端存储组件，而大多数系统故障都是由于存储所导致的，而数据库是重中之重，所以为了比较好掌握SQL的基本优化手段，打算用两篇文章从基本的联合索引优化、group by/order by 优化、以及索引设计原则、分页查询、join 查询、count 统计，以及阿里sql手册进行介绍。

本篇先介绍粗体字的相关内容，下一篇介绍剩余部分。

数据准备

CREATE TABLE `employees` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(24) NOT NULL DEFAULT '' COMMENT '姓名',
`age` int(11) NOT NULL DEFAULT '0' COMMENT '年龄',
`position` varchar(20) NOT NULL DEFAULT '' COMMENT '职位',
`hire_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '入职时间',
PRIMARY KEY (`id`),
KEY `idx_name_age_position` (`name`,`age`,`position`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT='员工记录表';

 INSERT INTO employees(name,age,position,hire_time) VALUES('LiLei',22,'manager',NOW());
 INSERT INTO employees(name,age,position,hire_time) VALUES('HanMeimei', 23,'dev',NOW());
 INSERT INTO employees(name,age,position,hire_time) VALUES('Lucy',23,'dev',NOW());

 ‐‐ 插入一些示例数据
 drop procedure if exists insert_emp;
 delimiter ;;
 create procedure insert_emp()
 begin
 declare i int;
 set i=1;
 while(i<=100000)do
 insert into employees(name,age,position) values(CONCAT('zhuge',i),i,'dev');
 set i=i+1;
 end while;
 end;;
 delimiter ;
 call insert_emp();

联合索引

第一个字段用范围不会走索引
在这里插入图片描述
如上所示，name、age、position是一个联合索引，name使用范围查询后，索引失效。
使用强制索引后，发现减少了查询数据量。

添加强制索引

 EXPLAIN SELECT * FROM employees force index(idx_name_age_position) WHERE  name > 'LiLei' AND age = 22 AND position = 'manager';

在这里插入图片描述
在很多场景下联合索引的返回值，就是我们希望的返回值，所以我们可以直接将* 替换成自己想要的值。其实就是覆盖索引优化

在这里插入图片描述
like ‘%xxx’ 索引失效
在这里插入图片描述
索引下推
其实就是在5.6之前是根据查询的数据按照对应的主键在逐个回表操作。但是5.6之后做了优化，可以先将不符合记录的过滤之后，在进行回表操作，可以有效减少回表的次数。但是并不能减少查询全行数据的效果。

 EXPLAIN SELECT * FROM employees WHERE name LIKE 'LiL%' and age =22 and position = 'manger';

Mysql如何选择合适的索引

尽管通过explain可以分析mysql是否使用了索引，扫描行数等，但是想要获取更多细节，分析mysql如何分析sql的过程，可以借助于trace工具。

  set session optimizer_trace="enabled=on",end_markers_in_json=on; -- 开启trace
  EXPLAIN SELECT * FROM employees WHERE name >'zz';
  SELECT * FROM information_schema.OPTIMIZER_TRACE;
  
  set session optimizer_trace="enabled=off"; -- 关闭trace

执行完毕之后就可以获取到分析过程，整体过程其实就是分成三部分。1.准备sql 2.优化sql 3.执行sql

{
  "steps": [
    {
      "join_preparation": {   // 第一阶段：准备sql
        "select#": 1,
        "steps": [
          {
            "expanded_query": "/* select#1 */ select `employees`.`id` AS `id`,`employees`.`name` AS `name`,`employees`.`age` AS `age`,`employees`.`position` AS `position`,`employees`.`hire_time` AS `hire_time` from `employees` where (`employees`.`name` > 'zz')"
          }
        ] /* steps */
      } /* join_preparation */
    },
    {
      "join_optimization": {  // 第二阶段 SQL优化阶段
        "select#": 1,
        "steps": [
          {
            "condition_processing": {  // 条件处理
              "condition": "WHERE",
              "original_condition": "(`employees`.`name` > 'zz')",
              "steps": [
                {
                  "transformation": "equality_propagation",
                  "resulting_condition": "(`employees`.`name` > 'zz')"
                },
                {
                  "transformation": "constant_propagation",
                  "resulting_condition": "(`employees`.`name` > 'zz')"
                },
                {
                  "transformation": "trivial_condition_removal",
                  "resulting_condition": "(`employees`.`name` > 'zz')"
                }
              ] /* steps */
            } /* condition_processing */
          },
          {
            "substitute_generated_columns": {
            } /* substitute_generated_columns */
          },
          {
            "table_dependencies": [  // 表依赖详情
              {
                "table": "`employees`",
                "row_may_be_null": false,
                "map_bit": 0,
                "depends_on_map_bits": [
                ] /* depends_on_map_bits */
              }
            ] /* table_dependencies */
          },
          {
            "ref_optimizer_key_uses": [
            ] /* ref_optimizer_key_uses */
          },
          {
            "rows_estimation": [   // 预估表的成本
              {
                "table": "`employees`",
                "range_analysis": {
                  "table_scan": {   // 全表扫描情况
                    "rows": 100143,  // 扫描行数
                    "cost": 10104.7  // 查询成本
                  } /* table_scan */,
                  "potential_range_indexes": [ // 查询可能使用的索引
                    {
                      "index": "PRIMARY", // 主键索引
                      "usable": false,
                      "cause": "not_applicable"
                    },
                    {
                      "index": "idx_name_age_position", // 辅助索引
                      "usable": true,
                      "key_parts": [
                        "name",
                        "age",
                        "position",
                        "id"
                      ] /* key_parts */
                    }
                  ] /* potential_range_indexes */,
                  "setup_range_conditions": [
                  ] /* setup_range_conditions */,
                  "group_index_range": {
                    "chosen": false,
                    "cause": "not_group_by_or_distinct"
                  } /* group_index_range */,
                  "skip_scan_range": {
                    "potential_skip_scan_indexes": [
                      {
                        "index": "idx_name_age_position",
                        "usable": false,
                        "cause": "query_references_nonkey_column"
                      }
                    ] /* potential_skip_scan_indexes */
                  } /* skip_scan_range */,
                  "analyzing_range_alternatives": { // 分析各个索引使用成本
                    "range_scan_alternatives": [
                      {
                        "index": "idx_name_age_position",
                        "ranges": [
                          "'zz' < name" // 索引使用范围
                        ] /* ranges */,
                        "index_dives_for_eq_ranges": true,
                        "rowid_ordered": false, //使用该索引获取的记录是否按照主键排序
                        "using_mrr": false,
                        "index_only": false,  //是否使用覆盖索引
                        "in_memory": 1, 
                        "rows": 1,        // 索引扫描行数
                        "cost": 0.61,     // 索引使用成本
                        "chosen": true    // 是否选择该索引
                      }
                    ] /* range_scan_alternatives */,
                    "analyzing_roworder_intersect": {
                      "usable": false,
                      "cause": "too_few_roworder_scans"
                    } /* analyzing_roworder_intersect */
                  } /* analyzing_range_alternatives */,
                  "chosen_range_access_summary": {
                    "range_access_plan": {
                      "type": "range_scan",
                      "index": "idx_name_age_position",
                      "rows": 1,
                      "ranges": [
                        "'zz' < name"
                      ] /* ranges */
                    } /* range_access_plan */,
                    "rows_for_plan": 1,
                    "cost_for_plan": 0.61,
                    "chosen": true
                  } /* chosen_range_access_summary */
                } /* range_analysis */
              }
            ] /* rows_estimation */
          },
          {
            "considered_execution_plans": [  
              {
                "plan_prefix": [
                ] /* plan_prefix */,
                "table": "`employees`",
                "best_access_path": {  // 最优访问路径
                  "considered_access_paths": [ //最终选择的访问路径
                    {
                      "rows_to_scan": 1,
                      "filtering_effect": [
                      ] /* filtering_effect */,
                      "final_filtering_effect": 1,
                      "access_type": "range", //访问类型 scan全表扫描
                      "range_details": {
                        "used_index": "idx_name_age_position"
                      } /* range_details */,
                      "resulting_rows": 1,
                      "cost": 0.71,
                      "chosen": true
                    }
                  ] /* considered_access_paths */
                } /* best_access_path */,
                "condition_filtering_pct": 100,
                "rows_for_plan": 1,
                "cost_for_plan": 0.71,
                "chosen": true
              }
            ] /* considered_execution_plans */
          },
          {
            "attaching_conditions_to_tables": {
              "original_condition": "(`employees`.`name` > 'zz')",
              "attached_conditions_computation": [
              ] /* attached_conditions_computation */,
              "attached_conditions_summary": [
                {
                  "table": "`employees`",
                  "attached": "(`employees`.`name` > 'zz')"
                }
              ] /* attached_conditions_summary */
            } /* attaching_conditions_to_tables */
          },
          {
            "finalizing_table_conditions": [
              {
                "table": "`employees`",
                "original_table_condition": "(`employees`.`name` > 'zz')",
                "final_table_condition   ": "(`employees`.`name` > 'zz')"
              }
            ] /* finalizing_table_conditions */
          },
          {
            "refine_plan": [
              {
                "table": "`employees`",
                "pushed_index_condition": "(`employees`.`name` > 'zz')",
                "table_condition_attached": null
              }
            ] /* refine_plan */
          }
        ] /* steps */
      } /* join_optimization */
    },
    {
      "join_explain": {  // 第三阶段：sql执行阶段
        "select#": 1,
        "steps": [
        ] /* steps */
      } /* join_explain */
    }
  ] /* steps */
}

Order by与Group by优化

case 1
在这里插入图片描述
所以age使用了索引。
case 2
在这里插入图片描述
在这里插入图片描述
同理可以发现 age用了索引，但是position没有使用索引。

case 3
在这里插入图片描述
在这里插入图片描述
可以发现，age \ position 前后顺序不一样，导致一个使用了索引一个没有使用索引，用的file sort。

在这里插入图片描述
其实对比这张图就可以看出，age\position 复合最左匹配原则，position\age不符合。

case 4
在这里插入图片描述
可以看到并没出现file sort。age因为是常量，在排序中被优化。索引没有出现颠倒。

Using filesort文件排序原理详解

case 5
在这里插入图片描述
在这里插入图片描述
在来看这两个案例，一个用的默认排序，另一个是倒序，前者使用索引，后者是filesort。

case 6
在这里插入图片描述
name使用in 因为是范围查询，所以导致直接索引失效

case 7
在这里插入图片描述
在这里插入图片描述
直接查询*，发现查询10W数据，但是用覆盖索引进行优化之后，可以利用索引。数据直接降低到5W。

总结

  • mysql支持两种方式的排序filesort和index，using index是指MySQL扫描索引本身完成排序。index 效率高，file sort效率低
  • order by满足两种情况会使用using index
    • order by 语句使用最左匹配原则
    • where子句与 order by子句条件组合满足索引最左前列
  • 尽量在索引上完成排序，遵循最左匹配原则
  • order by的条件不在索引列上，就会产生filesort
  • 能用覆盖索引就用覆盖索引
  • group by与order by蕾丝，也符合最左匹配原则。对于group by的优化如果不需要排序的可以加上order by null禁止排序。注意，where高于having，能写在where中 的限定条件就不要去having限定

单路和双路排序

filesort的两种排序方式

  • 单路排序，一次性取所有符合条件的数据，将数据拉到sort buffer中进行排序， sort_mode显示为 < sort_key, additional_fields > or < sort_key, packed_additional_fields >
  • 双路排序，先根据条件取出对应的排序字段和可以直接定位数据的行id，在sortbuffer中进行排序。排序完在取其他需要的字段。 < sort_key, rowid >

可以通过 max_length_for_sort_data ( 默认1024字节) 进行设置。 字段总长度小于1024，使用单路排序模式，大于使用双路排序模式。

 EXPLAIN SELECT * FROM employees where name = 'zhuge' ORDER BY position;

单路排序
1.根据索引查询到name='zhuge’的主键ID，通过主键id查询到对应行所有数据，取全部数据，存入sortbuffer中。
2.继续执行查找满足条件的数据。
3.继续执行上述1，2过程。
4.对结果进行整体排序，然后返回结构。

在这里插入图片描述

双路排序
1.根据索引查询到name='zhuge’的主键ID，通过主键id查询到对应行所有数据，取age\position字段，存入sortbuffer中。
2.继续执行查找满足条件的数据。
3.继续执行上述1，2过程。
4.对结果进行整体排序，在从原表中通过主键id查找对应全部字段的值返回。

在这里插入图片描述

可以看出整体的差别，及时排序时是否全部字段参与，以及最后返回结果是否需要在通过主键查找需要的字段。

那么这两种有什么优劣之分？
其实需要结合具体的服务器配置，如果max_length_for_sort_data 配置的比较小，并且不可以在增加，可以使用双路排序，在有限的内存中保存更多的数据，只是需要在通过一次回表操作查询需要的数据。
而如果服务器配置比较富裕，可以通过 max_length_for_sort_data 配置的大一点，选择单路排序，直接返回对应结果集。

所以，结论就是按照场景进行区分。通过调整 max_length_for_sort_data来控制排序，不同场景使用不同的排序模式，提升性能。

索引设计原则

1.代码先上，索引后加

说白了就是，针对新系统来说的话，可以先把基础的功能开发完毕，然后将相关的SQL整体处理，划分出一些字段，平衡是否需要要添加索引。或者对于常用的字段添加索引。很多时候如果是大表，没有索引的话，每次请求都全表查询，严重的话可能直接把服务干跨。

2.联合索引尽量覆盖条件

比如针对，age、name, position，就可以建立一个组合索引，而不要针对每个索引单独在建立。

3.不要在区分度小的字段加索引

对于一些区分度小的字段，性别、省份等，其实没有必要添加

4.长字符串使用前缀索引

对于长字符串，可以使用 KEY index(name(20),age,position) 建立前缀索引，提升查询速度，也可以减少磁盘空间。

5.where与order by冲突时优先where

冲突的时候，先通过where进行选择最少数据，然后在排序。

6.基于慢sql查询做优化

平时除了一些业务研发，还需要监控系统层面的慢SQL。

推荐看看：https://blog.csdn.net/qq_40884473/article/details/89455740

总结

本篇主要从组合索引的使用，优化以及order by 优化，索引的一些设计原则。下一篇我们聊聊分页,count,join优化。

本文来自互联网用户投稿，该文观点仅代表作者本人，不代表本站立场。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如若转载，请注明出处：/a/297760.html

如若内容造成侵权/违法违规/事实不符，请联系我们进行投诉反馈qq邮箱809451989@qq.com，一经查实，立即删除！

相关文章

【Kubernetes】认证授权RBAC （一）

【Kubernetes】认证授权RBAC （一）

认证授权RBAC 一、k8s安全管理&#xff1a;认证、授权、准入控制概述1.1、简介【1】认证基本介绍【2】授权基本介绍【3】准入控制基本介绍 1.2、认证【1】客户端认证【2】Bearertoken【3】Serviceaccount【4】拓展&#xff1a;kubeconfig文件 1.3、授权【1】什么是RBAC&#xf…
阅读更多...
使用 Python 和 wxPython 在图片上添加水印

使用 Python 和 wxPython 在图片上添加水印

创建一个基于wxPython的简单水印生成器应用程序。该应用程序具有一个窗口&#xff0c;用户可以选择要添加水印的图片文件&#xff0c;并在输入框中输入要显示在图片底部的文字。点击"印章"按钮后&#xff0c;应用程序将在选择的图片上添加水印&#xff0c;并将生成的…
阅读更多...
K9、希喂、SC哪款主食冻干适合全体质猫咪？深入对比，真实测评报告

K9、希喂、SC哪款主食冻干适合全体质猫咪？深入对比，真实测评报告

随着越来越多的人开始重视科学养猫的方法&#xff0c;铲屎官们对猫咪主食的营养和健康要求也越来越高。主食冻干作为一种模拟猫咪原始猎物模型配比的食品&#xff0c;因其低温加工工艺而受到广大猫奴的喜爱。这种食品更符合猫咪的饮食天性&#xff0c;相比起高淀粉、碳水化合物…
阅读更多...
【高效视频处理】BMF 项目安装与老视频修复体验全流程及总结

【高效视频处理】BMF 项目安装与老视频修复体验全流程及总结

一、BMF简介 BMF&#xff08;Babit Multimedia Framework&#xff09;是字节跳动开发的跨平台、多语言、可定制的多媒体处理框架。经过 4 年多的测试和改进&#xff0c;BMF 已经过量身定制&#xff0c;能够熟练地应对我们现实生产环境中的挑战。目前广泛应用于字节跳动的视频串…
阅读更多...
C# windows服务程序开机自启动exe程序

C# windows服务程序开机自启动exe程序

我们使用传统的Process.Start(".exe")启动进程会遇到无法打开UI界面的问题&#xff0c;尤其是我们需要进行开启自启动程序设置时出现诸多问题&#xff0c;于是我们就想到采用windows服务开机自启动来创建启动一个新的exe程序&#xff0c;并且是显式运行。 首先是打开…
阅读更多...
对接第三方接口鉴权（Spring Boot+Aop+注解实现Api接口签名验证）

对接第三方接口鉴权（Spring Boot+Aop+注解实现Api接口签名验证）

前言 一个web系统&#xff0c;从接口的使用范围也可以分为对内和对外两种&#xff0c;对内的接口主要限于一些我们内部系统的调用&#xff0c;多是通过内网进行调用&#xff0c;往往不用考虑太复杂的鉴权操作。但是&#xff0c;对于对外的接口&#xff0c;我们就不得不重视这个…
阅读更多...
Java字符串对象池的作用是什么？

Java字符串对象池的作用是什么？

Java字符串对象池的作用是什么&#xff1f; 在 Java 中&#xff0c;字符串池&#xff08;String Pool&#xff09;是字符串常量的存储区域&#xff0c;它位于堆区域中。字符串池的作用是提高字符串的重用性&#xff0c;减少内存消耗。 字符串池的位置&#xff1a; 在堆中&…
阅读更多...
Qt6入门教程 3：创建Hello World项目

Qt6入门教程 3：创建Hello World项目

一.新建一个项目 程序员的职业生涯都是从一声问候开始的&#xff0c;我们的第一个Qt项目也是HelloWorld 首先要说明的是&#xff0c;IDE不一定要用Qt Creator&#xff0c;用Visual Studio、VSCode、CLion也可以搭建Qt开发环境&#xff0c;它们都相应的插件来支持Qt开发。当然这…
阅读更多...
气动凝结水回收机组 浮球机械泵回收机组工作原理动画讲解介绍

气动凝结水回收机组 浮球机械泵回收机组工作原理动画讲解介绍

​ 1&#xff1a;气动凝结水回收浮球机械泵介绍 气动凝结水回收是一种利用气动力转换产生负压的装置&#xff0c;可以将废气中的水分分离出来并回收利用。这种装置主要包含两个关键部件&#xff1a;气水分离器和气动运动控制阀。 气水分离器负责将进入回收装置的废气中的水分…
阅读更多...
transforms图像增强（二）

transforms图像增强（二）

一、图像变换 1、transforms.Pad transforms.Pad是一个用于对图像边缘进行填充的数据转换操作。 参数&#xff1a; padding&#xff1a;设置填充大小。可以是单个整数&#xff0c;表示在上下左右四个方向上均填充相同数量的像素&#xff1b;也可以是一个包含两个整数的元组…
阅读更多...
粉丝投稿：从写下第1个脚本到年薪20W，我的自动化测试心路历程

粉丝投稿：从写下第1个脚本到年薪20W，我的自动化测试心路历程

我希望我的故事能够激励现在的软件测试人&#xff0c;尤其是还坚持在做“点点点”的测试人。 你可能会有疑问&#xff1a;“我也能做到这一点的可能性有多大&#xff1f;”因此&#xff0c;我会尽量把自己做决定和思考的过程讲得更具体一些&#xff0c;并尽量体现更多细节。 每…
阅读更多...
印象笔记02： 笔记本管理系统和空间使用

印象笔记02： 笔记本管理系统和空间使用

印象笔记02&#xff1a; 笔记本管理系统和空间使用 印象笔记新建笔记是一件非常容易的事情。笔记多了&#xff0c;就是归纳到笔记本里。 印象笔记一共有三层的笔记结构&#xff1a;最高层级是笔记本组&#xff0c;其次是笔记本&#xff0c;最后是一个个的笔记。合理的分类能够…
阅读更多...
SpringCloud系列篇：核心组件之注册中心组件

SpringCloud系列篇：核心组件之注册中心组件

&#x1f973;&#x1f973;Welcome Huihuis Code World ! !&#x1f973;&#x1f973; 接下来看看由辉辉所写的关于SpringCloud的相关操作吧 目录 &#x1f973;&#x1f973;Welcome Huihuis Code World ! !&#x1f973;&#x1f973; 一.注册中心组件是什么 二.注册中心…
阅读更多...
一键转换，创新无限：将HTML轻松转化为PDF！

一键转换，创新无限：将HTML轻松转化为PDF！

在数字时代&#xff0c;HTML与PDF已成为信息传递的两大主流格式。然而&#xff0c;在这两者之间转换常常让人感到困扰。现在&#xff0c;有了我们的创新工具&#xff0c;您只需轻点一下&#xff0c;即可一键将HTML转化为PDF&#xff01; 首先&#xff0c;我们要进入首助编辑高…
阅读更多...
(Python + Selenium4)Web自动化测试自学Day2之动手尝试

(Python + Selenium4)Web自动化测试自学Day2之动手尝试

目录 文章声明⭐⭐⭐让我们开始今天的学习吧&#xff01;小试牛刀关于select标签关于弹窗只有一个点击按钮的弹窗需要确认的弹窗用户可以输入的弹窗 文章声明⭐⭐⭐ 该文章为我&#xff08;有编程语言基础&#xff0c;非编程小白&#xff09;的 Python Selenium4 Web自动化测试…
阅读更多...
HttpRunner自动化测试工具之获取响应数据extract提取值到变量

HttpRunner自动化测试工具之获取响应数据extract提取值到变量

获取响应数据 extract: 提取 注&#xff1a;extract 应与request保持同一层级 1、响应行&#xff0c;响应头&#xff1b;通过 extract 提取响应的数据并存储到变量中&#xff0c;如下图&#xff1a; 注&#xff1a;变量名的前面要有 - # 获取响应数据: 响应行&#xff08;…
阅读更多...
【年终总结系列 2023】成长与收获：回顾过去、展望未来，加油2024！

【年终总结系列 2023】成长与收获：回顾过去、展望未来，加油2024！

转眼间加入CSDN已经六年多了&#xff0c;初加入CSDN时&#xff0c;我兴致勃勃地投入到写作中&#xff0c;分享了一些CTF的解题思路和方法&#xff0c;取得了不错的反响。但随着工作忙碌和生活压力的增加&#xff0c;我在CTF方面的写作频率逐渐减少&#xff0c;也很长时间没有更…
阅读更多...
Linux离线安装MySQL（rpm）

Linux离线安装MySQL（rpm）

目录 下载安装包安装MySQL检测安装结果服务启停MySQL用户设置 下载安装包 下载地址&#xff1a;https://downloads.mysql.com/archives/community/ 下载全量包如&#xff1a;(mysql-8.1.0-1.el7.x86_64.rpm-bundle.tar) 解压&#xff1a;tar -xzvf mysql-8.1.0-1.el7.x86_64.…
阅读更多...
国家高等教育智慧教育平台

国家高等教育智慧教育平台

文章目录 1. 网站地址2. 网站简介3. 网站集合的资源与依托平台彩蛋环节a. 考试酷b. 公益学术平台 足不出户&#xff0c;就能免费学习2.7万门大学课程。包含国家精品课程&#xff0c;部分课程由国家级名师 / 院士 授课。 1. 网站地址 国家高等教育智慧教育平台网址&#xff1a;…
阅读更多...
架构的本质是什么？

架构的本质是什么？

最近总是有小伙伴问我&#xff0c;如何成长为一名优秀的架构师&#xff0c;我也不知道该如何去回答&#xff0c;但是我想聊一下架构的本质。 架构不是互联网行业独有的 架构及对应的架构师职位并不是互联网行业独有的&#xff0c;只要存在组织的地方就存在架构。 比如一个木…
阅读更多...
最新文章

Copyright @ 2022~2023