2.索引优化分析

最新推荐文章于 2022-08-07 02:43:08 发布
最新推荐文章于 2022-08-07 02:43:08 发布
阅读量90 收藏
点赞数
分类专栏： 高性能MySQL
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/weixin_41282486/article/details/96564273
版权

1.常见通用的Join查询

在这里插入图片描述
建表SQL

CREATE TABLE `t_dept` (
 `id` INT(11) NOT NULL AUTO_INCREMENT,
 `deptName` VARCHAR(30) DEFAULT NULL,
 `address` VARCHAR(40) DEFAULT NULL,
 PRIMARY KEY (`id`)
) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
 
CREATE TABLE `t_emp` (
 `id` INT(11) NOT NULL AUTO_INCREMENT,
 `name` VARCHAR(20) DEFAULT NULL,
  `age` INT(3) DEFAULT NULL,
 `deptId` INT(11) DEFAULT NULL,
 PRIMARY KEY (`id`),
 KEY `fk_dept_id` (`deptId`)
 #CONSTRAINT `fk_dept_id` FOREIGN KEY (`deptId`) REFERENCES `t_dept` (`id`)
) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
 
 
 
INSERT INTO t_dept(deptName,address) VALUES('华山','华山');
INSERT INTO t_dept(deptName,address) VALUES('丐帮','洛阳');
INSERT INTO t_dept(deptName,address) VALUES('峨眉','峨眉山');
INSERT INTO t_dept(deptName,address) VALUES('武当','武当山');
INSERT INTO t_dept(deptName,address) VALUES('明教','光明顶');
 INSERT INTO t_dept(deptName,address) VALUES('少林','少林寺');
 
INSERT INTO t_emp(NAME,age,deptId) VALUES('风清扬',90,1);
INSERT INTO t_emp(NAME,age,deptId) VALUES('岳不群',50,1);
INSERT INTO t_emp(NAME,age,deptId) VALUES('令狐冲',24,1);
 
 INSERT INTO t_emp(NAME,age,deptId) VALUES('洪七公',70,2);
INSERT INTO t_emp(NAME,age,deptId) VALUES('乔峰',35,2);
 
INSERT INTO t_emp(NAME,age,deptId) VALUES('灭绝师太',70,3);
INSERT INTO t_emp(NAME,age,deptId) VALUES('周芷若',20,3);
 
 
 
INSERT INTO t_emp(NAME,age,deptId) VALUES('张三丰',100,4);
 
INSERT INTO t_emp(NAME,age,deptId) VALUES('张无忌',25,5);
 
INSERT INTO t_emp(NAME,age,deptId) VALUES('韦小宝',18,null);

7种JOIN

t_emp表

select * from t_emp;

在这里插入图片描述
t_dept表
在这里插入图片描述

1.1 A、B两表共有

在这里插入图片描述

select * from t_emp inner join t_dept on t_emp.deptId = t_dept.id;

在这里插入图片描述

1.2 A、B两表共有+A的独有

在这里插入图片描述

select * from t_emp left join t_dept on t_emp.deptId = t_dept.id;

在这里插入图片描述

1.3 A、B两表共有+B的独有

在这里插入图片描述

select * from t_emp right join t_dept on t_emp.deptId = t_dept.id;

在这里插入图片描述

1.4 A的独有

在这里插入图片描述

select * from t_emp left join t_dept on t_emp.deptId = t_dept.id where t_dept.id is null;

在这里插入图片描述

1.5 B的独有

在这里插入图片描述

select * from t_emp right join t_dept on t_emp.deptId = t_dept.id where t_emp.deptId is null;

在这里插入图片描述

1.6 AB全有

在这里插入图片描述

select * from t_emp full outer join t_dept on t_emp.deptId = t_dept.id;

在这里插入图片描述

注意：MySQL不支持FULL OUTER JOIN
mysql> select * from t_emp left join t_dept on t_emp.deptId = t_dept.id
    -> union
    -> select * from t_emp right join t_dept on t_emp.deptId = t_dept.id where t_emp.deptId is null;

在这里插入图片描述

1.7 A的独有+B的独有

在这里插入图片描述

mysql> select * from t_emp left join t_dept on t_emp.deptId = t_dept.id where t_dept.id is null
    -> union
    -> select * from t_emp right join t_dept on t_emp.deptId = t_dept.id where t_emp.deptId is null;

在这里插入图片描述

2.索引简介

2.1 基本语法

在这里插入图片描述
在这里插入图片描述
在这里插入图片描述

3.性能分析

3.1 Explain（查看执行计划）

使用EXPLAIN关键字可以模拟优化器执行SQL查询语句，从而知道MySQL是如何处理你的SQL语句的。分析你的查询语句或是表结构的性能瓶颈
使用：Explain + SQL语句
执行计划包含的信息

在这里插入图片描述

3.1.1 各字段解析

type

type显示的是访问类型，是较为重要的一个指标，结果值从最好到最坏依次是：

system > const > eq_ref > ref > range > index > ALL

system

1.表中只有一条数据
2.通过常量值匹配主键或者唯一索引

const

1.主键或者唯一索引
2.常量匹配

eq_ref

1.主键或者唯一索引扫描

ref

1.非主键或者唯一索引扫描
2.返回的结果不止一个

range

1.检索给定范围的行
2.关键字：between，< >,in

index

1.全索引扫描

ALL

1.全表扫描

possible_keys(理论上可能被用到的索引)

key（实际用到的索引）

key_len(索引中使用的字节数)

ref（显示索引的哪一列被使用了，如果可能的话，是一个常数。哪些列或常量被用于查找索引列上的值）

4.查询优化

4.1 使用索引

建表语句

 
CREATE TABLE staffs (
  id INT PRIMARY KEY AUTO_INCREMENT,
  NAME VARCHAR (24)  NULL DEFAULT '' COMMENT '姓名',
  age INT NOT NULL DEFAULT 0 COMMENT '年龄',
  pos VARCHAR (20) NOT NULL DEFAULT '' COMMENT '职位',
  add_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '入职时间'
) CHARSET utf8 COMMENT '员工记录表' ;
 
 
INSERT INTO staffs(NAME,age,pos,add_time) VALUES('z3',22,'manager',NOW());
INSERT INTO staffs(NAME,age,pos,add_time) VALUES('July',23,'dev',NOW());
INSERT INTO staffs(NAME,age,pos,add_time) VALUES('2000',23,'dev',NOW());
INSERT INTO staffs(NAME,age,pos,add_time) VALUES(null,23,'dev',NOW());
SELECT * FROM staffs;
 
ALTER TABLE staffs ADD INDEX idx_staffs_nameAgePos(name, age, pos);
索引失效

1.全值匹配我最爱

索引  idx_staffs_nameAgePos 建立索引时 以 name ， age ，pos 的顺序建立的。全值匹配表示 按顺序匹配的
EXPLAIN SELECT * FROM staffs WHERE NAME = 'July';
EXPLAIN SELECT * FROM staffs WHERE NAME = 'July' AND age = 25;
EXPLAIN SELECT * FROM staffs WHERE NAME = 'July' AND age = 25 AND pos = 'dev';

在这里插入图片描述
2.最佳左前缀法则
如果索引了多列，要遵守最左前缀法则。指的是查询从索引的最左前列开始并且不跳过索引中的列

 and 忽略左右关系。既即使没有没有按顺序 由于优化器的存在，会自动优化。
经过试验结论  建立了 idx_nameAge 索引  id 为主键
    1.当使用覆盖索引的方式时，(select name/age/id from staffs where age=10 (后面没有其他没有索引的字段条件))，即使不是以 name 开头，也会使用 idx_nameAge 索引。
    既 select 后的字段 有索引，where 后的字段也有索引，则无关执行顺序。
    2.除开上述条件 才满足最左前缀法则。
 
EXPLAIN SELECT * FROM staffs WHERE age = 25 AND pos = 'dev';
 
EXPLAIN SELECT * FROM staffs WHERE pos = 'dev';

在这里插入图片描述
3.不在索引列上做任何操作（计算、函数、(自动or手动)类型转换），会导致索引失效而转向全表扫描
在这里插入图片描述
4.存储引擎不能使用索引中范围条件右边的列
范围 若有索引则能使用到索引，范围条件右边的索引会失效(范围条件右边与范围条件使用的同一个组合索引，右边的才会失效。若是不同索引则不会失效)
在这里插入图片描述
5.尽量使用覆盖索引(只访问索引的查询(索引列和查询列一致))，减少select *
在这里插入图片描述
6.mysql 在使用不等于(!= 或者<>)的时候无法使用索引会导致全表扫描
索引 idx_nameAgeJob
idx_name
使用 != 和 <> 的字段索引失效( != 针对数值类型。 <> 针对字符类型
前提 where and 后的字段在混合索引中的位置比比当前字段靠后 where age != 10 and name=‘xxx’ ,这种情况下，mysql自动优化，将 name=‘xxx’ 放在 age ！=10 之前，name 依然能使用索引。只是 age 的索引失效)
在这里插入图片描述
7.is not null 也无法使用索引,但是is null是可以使用索引的
在这里插入图片描述
8.like以通配符开头(’%abc…’)mysql索引失效会变成全表扫描的操作
解决此问题应该用覆盖索引。

9.字符串不加单引号索引失效
在这里插入图片描述
10.少用or,用它来连接时会索引失效
在这里插入图片描述

远腾飞
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
MySQL 索引分析和优化
09-11
MySQL 索引分析和优化
浅谈MySQL索引优化分析
09-09
主要介绍了浅谈MySQL索引优化分析，小编觉得挺不错的，现在分享给大家，也给大家做个参考。一起跟随小编过来看看吧
mysql学习整理
qq_37244676的博客
11-22 140
纯手打，转载请标注！ sql类型  DDL：数据定义语言  包括 create alter drop truncate  不支持事务 DML：数据操作语言  包括insert delete update select （DQL） 支持事务 DQL：数据查询语言 包括select TCL：事务控制语言  包括 commit rollback savepoint  rollback to ...
MySQL索引性能优化分析
热门推荐
张彦峰的博客
04-07 172万+
总结MySQL索引性能优化，主要集中在索引的背景介绍以及索引的高效创建、使用和查询等，最有给出除了索引的一些其他优化思路
尚硅谷MySQL高级学习笔记 -- 2.索引优化分析
exodus3的博客
01-10 1万+
1、慢 SQL 性能下降、 SQL 慢、执行时间长、等待时间长的原因分析 查询语句写的烂索引失效： 单值索引：在user表中给name属性建个索引，create index idx_user_name on user(name)复合索引：在user表中给name、email属性建个索引，create index idx_user_nameEmail on user(name,email) 关联查询太多join(设计缺陷或不得已的需求)服务器调优及各个参数设置(缓冲、线程数等) 2、jo
MYSQL高级篇-----索引优化分析
沉淀、分享、成长，让自己和他人都能有所收获！
08-07 1万+
对于单键索引，尽量选择针对当前query过滤性更好的索引。在选择组合索引的时候，当前Query中过滤性最好的字段在索引字段顺序中，位置越靠前越好。在选择组合索引的时候，尽量选择可以能够包含当前query中的where字句中更多字段的索引。尽可能通过分析统计信息和调整query的写法来达到选择合适索引的目的。假设index(a, b, c)where语句索引是否被使用可以，使用到a可以，使用到a，b可以，使用到a，b，c。...............
SQL优化之索引优化
itbestboy的博客
06-22 2597
索引优化 1、 分析SQL执行的性能 ​ 使用explain关键字可以模拟优化器执行SQL查询语句，从而知道MySQL是如何处理SQL语句的。分析所编写的查询语句或是表结构的性能瓶颈。 具体语法： EXplain DQL语句； 执行的结果一共包括以下几个字段： id select_type table partitions type possible_keys key key_len ref roes filtered extra 字段 描述 id 查询的序列号
MySQL高级知识-查询与索引优化分析
02-25
手写手写SQL顺序机读顺序总结-SQL解析顺序SQL解析SQLJOINs七种JOIN图解实验：练习1、A、B两表共有2、A、B两表共有+A的独有3、A、B两表共有+B的独有4、A的独有5、B的独有6、AB全有MySQLFullJoin的实现因为MySQL不支持...
MySQL索引优化的实际案例分析
12-15
Order by desc/asc limit M是我在mysql sql优化中经常遇到的一种场景，其优化原理也非常的简单，就是利用索引的有序性，优化器沿着索引的顺序扫描，在扫描到符合条件的M行数据后，停止扫描；看起来非常的简单，但是...
大学生创新创业训练计划经验分享.zip
04-25
大学生创新创业训练计划（以下简称为“大创计划”）是一项旨在提升大学生创新能力和创业精神的实践活动。通过这项计划，学生可以在导师的指导下，自主开展研究性学习和创业实践。下面我将分享一些关于大创计划的经验和建议。
node-v12.22.3-x86.msi
最新发布
04-25
Node.js，简称Node，是一个开源且跨平台的JavaScript运行时环境，它允许在浏览器外运行JavaScript代码。Node.js于2009年由Ryan Dahl创立，旨在创建高性能的Web服务器和网络应用程序。它基于Google Chrome的V8 JavaScript引擎，可以在Windows、Linux、Unix、Mac OS X等操作系统上运行。 Node.js的特点之一是事件驱动和非阻塞I/O模型，这使得它非常适合处理大量并发连接，从而在构建实时应用程序如在线游戏、聊天应用以及实时通讯服务时表现卓越。此外，Node.js使用了模块化的架构，通过npm（Node package manager，Node包管理器）,社区成员可以共享和复用代码，极大地促进了Node.js生态系统的发展和扩张。 Node.js不仅用于服务器端开发。随着技术的发展，它也被用于构建工具链、开发桌面应用程序、物联网设备等。Node.js能够处理文件系统、操作数据库、处理网络请求等，因此，开发者可以用JavaScript编写全栈应用程序，这一点大大提高了开发效率和便捷性。 在实践中，许多大型企业和组织已经采用Node.js作为其Web应用程序的开发平台，如Netflix、PayPal和Walmart等。它们利用Node.js提高了应用性能，简化了开发流程，并且能更快地响应市场需求。
毕业设计-The coding solutions of Leetcode and 剑指Offer using .zip
04-25
这里为你收集整理了关于毕业设计、课程设计可参考借鉴的资料一份，质量非常高，如果你投入时间去研究几天相信肯定对你有很大的帮助。到时候你会回来感谢我的。 本资源是经过本地编译测试、可打开、可运行的项目、文件或源码，可以用于毕业设计、课程设计的应用、参考和学习需求，请放心下载。 祝愿你在这个毕业设计项目中取得巨大进步，顺利毕业！ 但还需强调一下，这些项目源码仅供学习和研究之用。在使用这些资源时，请务必遵守学术诚信原则和相关法律法规，不得将其用于任何商业目的或侵犯他人权益的行为。对于任何因使用本资源而导致的问题，包括但不限于数据丢失、系统崩溃或安全漏洞，风险自担哦！
【微信小程序毕业设计】宠物店商城系统开发项目(源码+演示视频+说明).rar
04-25
【微信小程序毕业设计】宠物店商城系统开发项目(源码+演示视频+说明).rar 【项目技术】 微信小程序开发工具+java后端+mysql 【演示视频-编号：282】 https://pan.quark.cn/s/cb634e7c02b5 【实现功能】 本系统实现的是和宠物相关的信息管理和发布，加入了商品销售的功能。操作角色为管理员和用户、商家，管理员的功能为用户管理、商家管理、宠物分类管理、宠物信息管理、商品分类管理、宠物用品管理、项目类型管理、服务项目管理、宠物日志管理、订单管理等；用户的功能为购买宠物、商品、预约服务发表日志管理订单等。商家功能为提供宠物、宠物用品、宠物服务，管理订单和服务预约等。
雷迪森的工具包（95分以上课程大作业）.zip
04-25
Java SSM项目是一种使用Java语言和SSM框架（Spring + Spring MVC + MyBatis）开发的Web应用程序。SSM是一种常用的Java开发框架组合，它结合了Spring框架、Spring MVC框架和MyBatis框架的优点，能够快速构建可靠、高效的企业级应用。 1. Spring框架：Spring是一个轻量级的Java开发框架，提供了丰富的功能和模块，用于开发企业级应用。它包括IoC（Inverse of Control，控制反转）容器、AOP（Aspect-Oriented Programming，面向切面编程）等特性，可以简化开发过程、提高代码的可维护性和可测试性。 2. Spring MVC框架：Spring MVC是基于Spring框架的Web框架，用于开发Web应用程序。它采用MVC（Model-View-Controller，模型-视图-控制器）的架构模式，将应用程序分为模型层、视图层和控制器层，提供了处理请求、渲染视图和管理流程的功能。 3. MyBatis框架：MyBatis是一个持久层框架，用于与数据库进行交互。它提供了一种将数据库操作与Java对象映射起来的方式，避免了手动编写繁琐的SQL语句，并提供了事务管理和缓存等功能，简化了数据库访问的过程
node-v9.10.1.tar.xz
04-25
Node.js，简称Node，是一个开源且跨平台的JavaScript运行时环境，它允许在浏览器外运行JavaScript代码。Node.js于2009年由Ryan Dahl创立，旨在创建高性能的Web服务器和网络应用程序。它基于Google Chrome的V8 JavaScript引擎，可以在Windows、Linux、Unix、Mac OS X等操作系统上运行。 Node.js的特点之一是事件驱动和非阻塞I/O模型，这使得它非常适合处理大量并发连接，从而在构建实时应用程序如在线游戏、聊天应用以及实时通讯服务时表现卓越。此外，Node.js使用了模块化的架构，通过npm（Node package manager，Node包管理器）,社区成员可以共享和复用代码，极大地促进了Node.js生态系统的发展和扩张。 Node.js不仅用于服务器端开发。随着技术的发展，它也被用于构建工具链、开发桌面应用程序、物联网设备等。Node.js能够处理文件系统、操作数据库、处理网络请求等，因此，开发者可以用JavaScript编写全栈应用程序，这一点大大提高了开发效率和便捷性。 在实践中，许多大型企业和组织已经采用Node.js作为其Web应用程序的开发平台，如Netflix、PayPal和Walmart等。它们利用Node.js提高了应用性能，简化了开发流程，并且能更快地响应市场需求。
基于B2C的网上拍卖系统_秒杀与竞价.zip
04-25
基于B2C的网上拍卖系统主要用于帮助人们应用互联网方便快捷买到自己所中意的商品，并参与到秒杀与竞拍当中。 主要功能包括： 1.前台模块 （1）普通用户登录/注册。 （2）分类查看商品(普通商品与促销商品) （3）查看商品详细信息 （4）查看秒杀商品 （5）查看竞拍商品 （6）将商品加入购物车 （7)购买，结算功能 （8）留言 2.后台模块 （1）修改密码 (2)商品管理: -- 编辑/删除 -- 设置/取消促销 （3)秒杀商品：设置/取消秒杀 （4)竞拍商品：设置/取消竞拍 （5)订单管理：查看订单 （5)留言管理：查看/删除留言 项目访问路径: 前台：http://localhost:8080/sale 后台：http://localhost:8080/sale/user/adminlogin
【前端素材】大数据-政务大数据共享交换平台.zip
04-25
大数据技术指的是用于处理和分析大规模数据集的技术和工具。以下是一些常见的大数据技术和工具： Hadoop：Apache Hadoop是一个用于分布式存储和处理大规模数据的开源框架。它包括Hadoop Distributed File System（HDFS）用于数据存储和MapReduce用于数据处理。 Spark：Apache Spark是一个快速、通用的集群计算系统，提供了比MapReduce更快的数据处理能力。它支持内存计算和更多复杂的数据处理流程。 NoSQL数据库：NoSQL数据库（如MongoDB、Cassandra等）则更适用于处理这类数据。 数据仓库：数据仓库是一个用于集成和分析大规模数据的存储系统，一些知名的数据仓库包括Snowflake、Amazon Redshift等。 数据湖：数据湖是一个存储结构化和非结构化数据的存储池，用于支持数据分析和机器学习应用。 机器学习：大数据技术也广泛应用于机器学习领域，支持大规模数据的模型训练和预测分析。 流式处理：针对实时数据处理需求，流式处理技术（如Apache Kafka、Apache Flink）可以实时。
【前端素材】大数据-气象预报大数据平台.zip
04-25
大数据技术指的是用于处理和分析大规模数据集的技术和工具。以下是一些常见的大数据技术和工具： Hadoop：Apache Hadoop是一个用于分布式存储和处理大规模数据的开源框架。它包括Hadoop Distributed File System（HDFS）用于数据存储和MapReduce用于数据处理。 Spark：Apache Spark是一个快速、通用的集群计算系统，提供了比MapReduce更快的数据处理能力。它支持内存计算和更多复杂的数据处理流程。 NoSQL数据库：NoSQL数据库（如MongoDB、Cassandra等）则更适用于处理这类数据。 数据仓库：数据仓库是一个用于集成和分析大规模数据的存储系统，一些知名的数据仓库包括Snowflake、Amazon Redshift等。 数据湖：数据湖是一个存储结构化和非结构化数据的存储池，用于支持数据分析和机器学习应用。 机器学习：大数据技术也广泛应用于机器学习领域，支持大规模数据的模型训练和预测分析。 流式处理：针对实时数据处理需求，流式处理技术（如Apache Kafka、Apache Flink）可以实时。
数学模型算法多目标决策分析方法.pptx
04-25
数学模型算法
MySQL索引优化explain分析
08-13
MySQL的`EXPLAIN`语句可以帮助我们分析查询语句的执行计划，从而优化查询性能。下面是一些常见的优化指标和相关的解释： 1. `type`列：表示访问数据的方式，常见的取值有`ALL`、`index`、`range`、`ref`、`eq_ref`、`const`等。一般来说，访问数据的方式越好，性能越高。 2. `key`列：表示使用的索引。如果该列为NULL，则表示没有使用索引。索引的选择要尽量满足查询条件，并且覆盖需要返回的数据列。 3. `rows`列：表示MySQL估计需要扫描的行数。行数越少，性能越好。 4. `Extra`列：额外的信息，常见取值有`Using where`、`Using index`、`Using temporary`、`Using filesort`等。这些信息可以帮助我们判断是否存在潜在的性能问题。 根据这些指标，我们可以进行索引优化，以下是一些常见的优化策略： 1. 确保表上有适当的索引。通过分析查询语句的WHERE条件和JOIN条件，选择合适的索引。可以使用`CREATE INDEX`语句来创建索引。 2. 尽量避免全表扫描（即type为`ALL`）。可以通过添加适当的索引、优化查询语句或者调整表结构来避免全表扫描。 3. 避免使用临时表（即`Using temporary`）。可以通过优化查询语句，避免使用`GROUP BY`、`DISTINCT`、`UNION`等操作，从而避免使用临时表。 4. 避免使用文件排序（即`Using filesort`）。可以通过添加适当的索引、调整查询语句或者调整排序方式来避免文件排序。 5. 注意使用索引覆盖。索引覆盖是指查询时只使用索引而不需要访问表数据，可以通过合理选择索引和查询列来实现。 以上是一些常见的MySQL索引优化方法，具体的优化策略需要根据具体的查询语句和数据情况进行调整。

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值