架构师系列-MYSQL调优（八）- 索引多表优化案例

  • 用户手机认证表

    • 该表约有11万数据,保存的是通过手机认证后的用户数据
    • 关联字段: user_id
CREATE TABLE `mob_autht` (`id` int(11) NOT NULL AUTO_INCREMENT COMMENT '标识',`user_id` int(11) NOT NULL COMMENT '用户标识',`mobile` varchar(11) NOT NULL COMMENT '手机号码',`seevc_pwd` varchar(12) NOT NULL COMMENT '服务密码',`autht_indc` varchar(1) NOT NULL DEFAULT '0' COMMENT '认证标志',`verson` int(11) NOT NULL DEFAULT '0' COMMENT '版本',`create_by` varchar(64) DEFAULT NULL COMMENT '创建者',`create_date` datetime NOT NULL COMMENT '创建时间',`update_by` varchar(64) DEFAULT NULL COMMENT '更新者',`update_date` datetime NOT NULL COMMENT '更新时间',`remarks` varchar(255) DEFAULT NULL COMMENT '备注信息',`del_flag` char(1) NOT NULL DEFAULT '0' COMMENT '删除标识',PRIMARY KEY (`id`)
) ;

  • 紧急联系人表

    • 该表约有22万数据,注册成功后,用户添加的紧急联系人信息.
    • 关联字段: user_id
CREATE TABLE `ugncy_cntct_psn` (`id` int(11) NOT NULL AUTO_INCREMENT COMMENT '标识',`psn_info_id` int(11) DEFAULT NULL COMMENT '个人信息标识',`user_id` int(11) NOT NULL COMMENT '向钱用户标识',`cntct_psn_name` varchar(10) NOT NULL COMMENT '联系人姓名',`cntct_psn_mob` varchar(11) NOT NULL COMMENT '联系手机号',`and_self_rltn_cde` char(2) NOT NULL COMMENT '与本人关系代码 字典表关联',`verson` int(11) NOT NULL DEFAULT '0' COMMENT '版本',`create_by` varchar(64) DEFAULT NULL COMMENT '创建者',`create_date` datetime NOT NULL COMMENT '创建时间',`update_by` varchar(64) DEFAULT NULL COMMENT '更新者',`update_date` datetime NOT NULL COMMENT '更新时间',`remarks` varchar(255) DEFAULT NULL COMMENT '备注信息',`del_flag` char(1) NOT NULL DEFAULT '0' COMMENT '删除标识',PRIMARY KEY (`id`)
) ;

 

  • 借款申请表

    • 该表约有11万数据,保存的是每次用户申请借款时 填写的信息.
    • 关联字段: user_id
CREATE TABLE `loan_apply` (`id` INT(11) NOT NULL AUTO_INCREMENT COMMENT '借款申请标识',`loan_nbr` VARCHAR(50) NOT NULL COMMENT '借款编号',`user_id` INT(11) NOT NULL COMMENT '用户标识',`idnt_info_id` INT(11) DEFAULT NULL COMMENT '身份信息标识',`psn_info_id` INT(11) DEFAULT NULL COMMENT '个人信息标识',`mob_autht_id` INT(11) DEFAULT NULL COMMENT '手机认证标识',`bnk_card_id` INT(11) DEFAULT NULL COMMENT '银行卡标识',`apply_limit` DECIMAL(16,2) NOT NULL DEFAULT '0.00' COMMENT '申请额度',`apply_tlmt` INT(3) NOT NULL COMMENT '申请期限',`apply_time` DATETIME NOT NULL COMMENT '申请时间',`audit_limit` DECIMAL(16,2) NOT NULL COMMENT '审核额度',`audit_tlmt` INT(3) NOT NULL COMMENT '审核期限',`audit_time` DATETIME DEFAULT NULL COMMENT '审核时间',`cfrm_limit` DECIMAL(16,2) NOT NULL DEFAULT '0.00' COMMENT '确认额度',`cfrm_tlmt` INT(3) NOT NULL COMMENT '确认期限',`cfrm_time` DATETIME DEFAULT NULL COMMENT '确认时间',`loan_sts_cde` CHAR(1) NOT NULL COMMENT '借款状态:0 未提交 1 提交申请(初始) 2 已校验 3 通过审核4 未通过审核 5开始放款 6放弃借款 7 放款成功 ',`audit_mod_cde` CHAR(1) NOT NULL COMMENT '审核模式：1 人工 2 智能',`day_rate` DECIMAL(16,8) NOT NULL DEFAULT '0.00000000' COMMENT '日利率',`seevc_fee_day_rate` DECIMAL(16,8) NOT NULL DEFAULT '0.00000000' COMMENT '服务费日利率',`normal_paybk_tot_day_rate` DECIMAL(16,8) NOT NULL DEFAULT '0.00000000' COMMENT '正常还款总日利率',`ovrdu_fee_day_rate` DECIMAL(16,8) DEFAULT NULL COMMENT '逾期违约金日利率',`day_intr_amt` DECIMAL(16,2) NOT NULL DEFAULT '0.00' COMMENT '日利率金额',`seevc_fee_day_intr_amt` DECIMAL(16,2) NOT NULL DEFAULT '0.00' COMMENT '服务日利率金额',`normal_paybk_tot_intr_amt` DECIMAL(16,2) NOT NULL DEFAULT '0.00' COMMENT '综合日利率金额',`cnl_resn_time` DATETIME DEFAULT NULL COMMENT '放弃时间',`cnl_resn_cde` CHAR(8) DEFAULT NULL COMMENT '放弃原因：关联字典代码',`cnl_resn_othr` VARCHAR(255) DEFAULT NULL COMMENT '放弃的其他原因',`verson` INT(11) NOT NULL DEFAULT '0' COMMENT '版本',`create_by` VARCHAR(64) DEFAULT NULL COMMENT '创建者',`create_date` DATETIME NOT NULL COMMENT '创建时间',`update_by` VARCHAR(64) DEFAULT NULL COMMENT '更新者',`update_date` DATETIME NOT NULL COMMENT '更新时间',`remarks` VARCHAR(255) DEFAULT NULL COMMENT '备注信息',`loan_dst_cde` CHAR(1) NOT NULL DEFAULT '0' COMMENT '0,未分配; 1,已分配',`del_flag` CHAR(1) NOT NULL DEFAULT '0' COMMENT '删除标识',`last_loan_apply_id` INT(11) DEFAULT NULL COMMENT '上次借款申请标识',PRIMARY KEY (`id`),UNIQUE KEY `ind_loan_nbr` (`loan_nbr`) USING BTREE,
) ;

 需求一:

  • 查询所有认证用户的手机号以及认证用户的紧急联系人的姓名与手机号信息

explain select ma.mobile '认证用户手机号',ucp.cntct_psn_name '紧急联系人姓名',ucp.cntct_psn_mob '紧急联系人手机号'
from mob_autht ma left join ugncy_cntct_psn ucp 
on ma.user_id = ucp.user_id;

type 类型都是ALL, 使用了全表扫描

优化: 为mob_autht 表的 user_id字段 添加索引

alter table mob_autht add index idx_user_id(user_id);

  • 根据小结果及驱动大结果集的原则, mob_autht 是驱动表,驱动表即使建立索引也不会生效.

  • 一般情况下: 左外连接左表是驱动表,右外连接右表就是驱动表.

  • explain分析结果的第一行的表,就是驱动表

 继续优化: 为ugncy_cntct_psn表的 user_id字段 添加索引

ALTER TABLE ugncy_cntct_psn ADD INDEX idx_userid(user_id);

  • mob_autht 的type类型为ALL, ugncy_cntct_psn的type类型是ref

 需求二:

 获取所有智能审核的用户手机号和申请额度、申请时间、审核额度

EXPLAIN SELECT ma.mobile '用户认证手机号',la.apply_limit '申请额度',la.apply_time '申请时间',la.audit_limit '审核额度'
FROM mob_autht ma inner JOIN loan_apply la ON ma.id = la.mob_autht_id
WHERE la.audit_mod_cde = '2';

优化分析

  • 查询 loan_apply表,使用的条件字段为 audit_mod_cde ,因为该字段没有添加索引,导致 type=ALL 发生全表扫描,

  • 为audit_mod_cde 字段添加索引,来提高查询效率.

ALTER TABLE loan_apply ADD INDEX idx_amc(audit_mod_cde);

添加索引后type的类型确实提升了,但是需要注意的扫描的行还是很高,并且 Extra字段的值为 Using where 表示: 通过索引访问时,需要再回表访问所需的数据.

注意: 如果执行计划中显示走了索引，但是rows值很高，extra显示为using where，那么执行效果就不会很好。因为索引访问的成本主要在回表上.

继续优化

  • audit_mod_cde 字段的含义是审核模式,只有两个值: 1 人工 2 智能 ,所以在根据该字段进行查询时,会有大量的相同数据.

  • 比如: 统计一下 audit_mod_cde = '2' 的数据总条数,查询结果是9万多条,该表的总数接近11万条,查询出的数据行超过了表的总记录数的30%, 这时就不建议添加索引 ( 比如有1000万的数据，就算平均分后结果集也有500万条,结果集还是太大，查询效率依然不高 ).

SELECT COUNT(*) FROM loan_apply; -- 109181条SELECT COUNT(*) FROM loan_apply la WHERE la.audit_mod_cde = '2' ; -- 91630条

总结: 唯一性太差的字段不需要创建索引,即便用于where条件.

继续优化

如果一定要根据状态字段进行查询,我们可以根据业务需求 添加一个日期条件,比如获取某一时间段的数据,然后再区分状态字段.

-- 获取2017年 1月1号~1月5号的数据
EXPLAIN SELECT ma.mobile '用户认证手机号',la.apply_time '申请时间',la.apply_limit '申请额度',la.audit_limit '审核额度'
FROM  loan_apply la  INNER JOIN mob_autht ma  ON la.mob_autht_id = ma.id 
WHERE apply_time BETWEEN '2017-01-01 00:00:00' 
AND  '2017-01-05 23:59:59' AND la.audit_mod_cde = '2';

extra = Using index condition; : 只有一部分索引生效

MRR 算法: 通过范围扫描将数据存入 read_rnd_buffer_size ，然后对其按照 Primary Key（RowID）排序，最后使用排序好的数据进行顺序回表，因为 InnoDB 中叶子节点数据是按照 Primary Key（RowID）进行排列的，这样就转换随机IO为顺序IO了，从而减小磁盘的随机访问.

 

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

如若内容造成侵权/违法违规/事实不符，请联系瞎胡闹网进行投诉反馈，一经查实，立即删除！

相关文章

图像处理技术与应用（一）

图像处理技术与应用（一）

图像处理技术与应用入门 使用skimage进行图像读取和显示 skimage库（Scikit-image）提供了一个强大的工具集，用于执行各种图像处理任务。以下是如何使用skimage读取和显示图像的基本示例： from skimage import ioimg io.imread(…
阅读更多...
LLama的激活函数SwiGLU 解释

LLama的激活函数SwiGLU 解释

目录 Swish激活函数 1. Swish函数公式 LLaMA模型中的激活函数 1. SwiGLU激活函数 2. SwiGLU激活函数的表达式 3. SwiGLU激活函数的优势 Swish激活函数 Swish是一种激活函数，其计算公式如下： 1. Swish函数公式 Swish(x) x * sigmoid(x) 其中&am…
阅读更多...
【CMake】CMake从入门到实战系列（十）——CMake中的条件语句和循环语句

【CMake】CMake从入门到实战系列（十）——CMake中的条件语句和循环语句

文章目录 前言一、条件语句1、if 语句【1】基本语法【2】参数含义【3】示例 二、循环语句1、foreach 循环foreach 循环之 基本用法【1】基本语法【2】参数含义【3】示例 foreach 循环之 RANGE 关键字【1】基本语法【2】参数含义【3】示例 foreach 循环之 IN 关键字【1】基本语法…
阅读更多...
什么是大语言模型以及如何构建自己的大型语言模型？

什么是大语言模型以及如何构建自己的大型语言模型？

一、关于大语言模型 LLM 对于无数的应用程序非常有用，如果我们自己从头开始构建一个，那我们可以了解底层的ML技术，并可以根据特定需求定制LLM，但是对资源的需求巨大。大型语言模型是一种 ML 模型，可以执行各种自然语言…
阅读更多...
Quartz - 任务调度

Quartz - 任务调度

文章目录 关于 QuartzQuartz的体系结构核心概念设计模式 常用API基本使用 关于 Quartz 官网：https://www.quartz-scheduler.orggithub : https://github.com/quartz-scheduler/quartz文档：https://www.quartz-scheduler.org/documentation/ 相关教程/博…
阅读更多...
autodesk系列软件打开提示Flexnet License Finder

autodesk系列软件打开提示Flexnet License Finder

错误如图 解决方法如下 打开autoremove，点击扩展功能，输入flex，点击搜索 选择对应的这个按钮，点击，提示修复成功即可尝试打开软件
阅读更多...
深入学习Linux中的“文件系统与日志分析”

深入学习Linux中的“文件系统与日志分析”

目录 1.文件系统的组成 1.1inode和block 1.2inode的内容 1.3inode的号码 ​1.4文件存储小结 ​1.5inode大小 1.6inode的特殊作用 2. 链接文件 3.文件恢复 3.1EXT类型文件恢复 3.2磁盘有空间，但是仍然无法写入新文件 3.3恢复XFS类型的文件 3.3.1xfsdump…
阅读更多...
Unity 中（提示框Tweet）

Unity 中（提示框Tweet）

using UnityEngine; using UnityEngine.UI; using DG.Tweening; using System; public class Message : MonoBehaviour {public float dropDuration 0.5f; // 掉落持续时间public float persisterDuration 1f; // 持续显示时间public float dorpHeight;public static Message…
阅读更多...
vue【vuex状态管理】

vue【vuex状态管理】

1：vuex是什么： vuex是一个状态管理工具，状态就是指的数据，可以将数据存放到vuex中以供其他组件使用时进行调用 2：应用场景： ①：像用户登录客户端，这个用户的数据需要在多个组件中…
阅读更多...
一维递归：递去

一维递归：递去

示例&#xff1a; /*** brief how about recursive-forward-1? show you here.* author wenxuanpei* email 15873152445163.com(query for any question here)*/ #define _CRT_SECURE_NO_WARNINGS//support c-library in Microsoft-Visual-Studio #include <stdio.h>…
阅读更多...
Linux多进程(一)创建进程与进程控制

Linux多进程(一)创建进程与进程控制

一、进程状态 进程一共有五种状态分别为&#xff1a;创建态&#xff0c;就绪态&#xff0c;运行态&#xff0c;阻塞态(挂起态)&#xff0c;退出态(终止态)其中创建态和退出态维持的时间是非常短的&#xff0c;稍纵即逝。主要是就绪态, 运行态, 挂起态三者之间的状态切换。 就绪…
阅读更多...
ABB机器人IRB4400平衡缸维修小技巧

ABB机器人IRB4400平衡缸维修小技巧

ABB平衡缸是机器人是一个不可忽视的重要组成部分。ABB机器人平衡缸通常采用高强度材料制造&#xff0c;设计精巧&#xff0c;方便地集成到IRB4400机器人手臂中。此外&#xff0c;平衡缸还具备良好的密封性能&#xff0c;防止气体或液体泄漏&#xff0c;确保其耐用性和稳定性。因…
阅读更多...
Mac电脑清理微信聊天记录 Mac电脑微信缓存怎么删

Mac电脑清理微信聊天记录 Mac电脑微信缓存怎么删

细心网友突然发现微信已经成为占用电脑存储空间最大的软件&#xff0c;其中包括聊天记录、缓存的文件、图片、视频等等&#xff0c;少则几个G&#xff0c;多则十几个G&#xff0c;让原本就不富裕的Mac存储空间更是雪上加霜。mac电脑清理微信聊天记录成为大家迫切解决的问题&…
阅读更多...
MySQL文件目录结构：表在文件系统中的表示

MySQL文件目录结构：表在文件系统中的表示

以下内容基于Linux系统&#xff0c;MySQL的 /var/lib/mysql/ 目录下的数据文件 &#x1f496; Innodb 引擎 MySQL 5.7 MySQL 8.0 &#x1f31f; 总结 Innodb 是聚簇索引&#xff0c;索引及数据&#xff0c;数据即索引&#xff0c;所以数据和索引是存储在同一个文件中的 MyS…
阅读更多...
Java基本语法（基础部分）

Java基本语法（基础部分）

Java基本语法 文章目录 Java基本语法前言一、准备工作1.1 计算机软件与硬件1.2 计算机编程语言1.3 Java语言概述&程序分析1.4 Java环境搭建&Java API1.5 Java核心机制JVM 二、变量2.1 关键字&标识符2.2 变量2.3 数据类型(基本数据类型)2.3.1 基本数据类型2.3.2 基本…
阅读更多...
【八股文】Spring 谈谈你对AOP的理解

【八股文】Spring 谈谈你对AOP的理解

AOP AOP(Aspect-Oriented Programming&#xff0c;面向切面编程)&#xff1a;是一种新的方法论&#xff0c;是对传统 OOP(Object-Oriented Programming&#xff0c;面向对象编程)的补充。 面向对象是纵向继承&#xff0c;面向切面是横向抽取。 OOP思想是一种垂直纵向的继承体…
阅读更多...
HARDVS: Revisiting Human Activity Recognition with Dynamic Vision Sensors

HARDVS: Revisiting Human Activity Recognition with Dynamic Vision Sensors

标题&#xff1a;HARDVS: 用动态视觉传感器重新审视人类行为识别 原文链接&#xff1a;HARDVS: Revisiting Human Activity Recognition with Dynamic Vision Sensors| Proceedings of the AAAI Conference on Artificial Intelligencehttps://ojs.aaai.org/index.php/AAAI/ar…
阅读更多...
PHP 爬虫如何配置代理 IP（CURL 函数）

PHP 爬虫如何配置代理 IP（CURL 函数）

在 PHP中 配置代理IP&#xff0c;可以通过设置 CURL 库的选项来实现&#xff0c;代码如下&#xff1a; 当然你要有代理ip来源&#xff0c;比如我用的这个 代理商 &#xff0c;如果想服务稳定不建议找开源代理池&#xff0c;避免被劫持。 <?php // 初始化cURL会话 $ch cu…
阅读更多...
数据分析师必备的8个思维框架

数据分析师必备的8个思维框架

什么是框架性思维&#xff1f;它是由一个个的思维框架积累而来。本文介绍分析常用的几个思维框架。 一些职位描述中会要求分析师有框架性思维&#xff0c;能够被考察的是思维框架&#xff0c;通过思维框架判断框架性思维能力。笔者查阅了多篇文章&#xff0c;定义思维框架为&a…
阅读更多...
文件上传漏洞-白名单检测

文件上传漏洞-白名单检测

如何确认是否是白名单检测 上传一张图片与上传一个自己构造的后缀&#xff0c;如果只能上传图片不能上传其它后缀文件&#xff0c;说明是白名单检测。 绕过技巧 可以利用 00 截断的方式进行绕过&#xff0c;包括 %00 截断与 0x00 截断。除此之外如果网站存在文件包含漏洞&…
阅读更多...
最新文章

Copyright @ 2022~2023