MySQL的索引优化分析

最新推荐文章于 2022-11-02 22:27:03 发布
最新推荐文章于 2022-11-02 22:27:03 发布
阅读量258 收藏
点赞数
分类专栏： # mysql数据库学习笔记 文章标签： mysql 数据库
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/xiaotai1234/article/details/109462811
版权

MySQL的索引优化分析

一、性能下降SQL慢，执行时间长，等待时间长

1.查询语句写的烂

2.索引失效

3.关联查询太多join（设计缺陷或不得已的需求）

4.服务器调优及各个参数设置（缓冲、线程数等）

二、常见通用的join查询

sql执行顺序

手写

在这里插入图片描述

机读

在这里插入图片描述

总结

在这里插入图片描述

三、索引简介

1.是什么

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

2.优势

在这里插入图片描述

3.劣势

在这里插入图片描述

4.mysql索引分类

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

5.mysql索引结构

在这里插入图片描述
检索原理
在这里插入图片描述
在这里插入图片描述

6.哪些情况下需要创建索引

在这里插入图片描述

7.哪些情况不要创建索引

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

四、性能分析

1.MySql Query Optimizer

在这里插入图片描述

2.MySql常见瓶颈

在这里插入图片描述

3.Explain

是什么（查看执行计划）

在这里插入图片描述

能干嘛

在这里插入图片描述

怎么玩

在这里插入图片描述

各字段解释
id

在这里插入图片描述
三种情况
id相同,执行顺序由上至下
id不同，如果是子查询,id的序号会递增，id值越大优先级越高，越先被执行
id相同不同，同时存在

select_type

有哪些
在这里插入图片描述
查询的类型，主要是用于区别普通查询、联合查询、子查询等的复杂查询
在这里插入图片描述

table

显示这一行的数据是关于哪张表的

type

在这里插入图片描述
访问类型排列
在这里插入图片描述
在这里插入图片描述
在这里插入图片描述

possible_key

在这里插入图片描述

key

在这里插入图片描述

key_len

在这里插入图片描述

ref

在这里插入图片描述

rows

在这里插入图片描述

Extra

包含不适合在其他列中显示但十分重要的额外信息
在这里插入图片描述

五、索引失效（应该避免）

1.各种情况

在这里插入图片描述

六、查询截取分析

1.查询优化

永远小表驱动大表，类似嵌套循环Nested loop

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

order by关键字优化
order by 子句，尽量使用Index方式排序，避免使用FileSort方式排序

在这里插入图片描述

尽可能在索引列上完成排序操作，遵照索引键的最佳左前缀
如果不在索引列上，filesort有两种算法，mysql就要启动双路排序和单路排序

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

从磁盘中取排序字段，在buffer进行排序，再从磁盘取其他字段
取一批数据，要对磁盘进行两次扫描，众所周知，I\O是很耗时的，所以在mysql4.1之后，出现了第二种改进的算法，就是单路排序
单路排序
从磁盘读取查询需要的所有列，按照order by列在buffer对它们进行排序，然后扫描排序后列表进行输出，它的效率更快一些，避免了第二次读取数据。并且把随机IO变成了顺序IO，但是它会使用更多的空间，因为它把每一行都保存在内存中了。
结论及引申出的问题
由于单路是后出的，总体而言好过双路
但是单路有问题
在这里插入图片描述

优化策略

增大sort_buffer_size参数的设置
增大max_length_for_sort_data参数的设置
在这里插入图片描述

小总结

在这里插入图片描述

group by关键字优化

在这里插入图片描述

2.慢查询日志

是什么

在这里插入图片描述

怎么玩
说明

在这里插入图片描述

查看是否开启及如何开启

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

那么开启了慢查询日志后，什么样的sql才会记录到慢查询日志里面呢？

在这里插入图片描述

case
查看当前多少秒算慢

在这里插入图片描述

设置慢的阈值时间

在这里插入图片描述

为什么设置后看不出变化？

在这里插入图片描述

日志分析工具mysqldumpslow

在这里插入图片描述

查看mysqldumpslow的帮助信息

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

3.批量数据脚本

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

4.show profile

在这里插入图片描述

分析步骤
是否支持，看看当前的mysql版本是否支持

在这里插入图片描述

开启功能，默认是关闭，使用前需要开启

在这里插入图片描述

运行sql
查看结果，show profiles
诊断sql，show profile cpu,block io for query 上一步前面的问题sql数字号码

参数备注
在这里插入图片描述

日常开发需要注意的结论

在这里插入图片描述

5.全局查询日志

配置启用

在这里插入图片描述

编码启用

在这里插入图片描述

永远不要在生产环境开启这个功能
xiaotai1234
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
mysql、索引、查询剖析工具
dandanforgetlove的专栏
06-02 299
mysql 查询剖析工具 新版本使用performance_schema show profiles; 显示所有的sql语句。 show profile for query query_id; 对指定query_id sql 进行剖析。 B+ Tree EXPLAIN 分析语句 EXPLAIN select * from product_sale_rank where sales_total > 20 详解：https://blog.csdn.net/why157...
MySQL索引分析及优化.pdf
10-24
讨论在数据库中, 索引是提高速度的一个关键因素, 如果作为搜索条件的列上已经创建了索引, M ySQ L无 需扫描任何记录即可迅速得到目标记录所在的位置.
mysql 索引分析工具_mysqlsla来分析MYSQL的性能及索引
weixin_35133198的博客
02-02 165
— Slow log: mysqlsla -lt slow slow.log— General log: mysqlsla -lt general general.log— Binary log: mysqlbinlog bin.log | mysqlsla -lt binaryhackmysql.com推出的一款MySQL的日志分析工具整体来说, 功能非常强大. 数据报表,非常有利于分析慢查询的...
MySQL索引分析工具EXPLAIN
临江仙我亦是行人
05-20 570
5 EXPLAIN 工具 可以通过EXPLAIN来分析索引的有效性,获取查询执行计划信息，用来查看查询优化器如何执行查询 参考资料:https://dev.mysql.com/doc/refman/5.7/en/explain-output.html 语法 EXPLAIN SELECT clause EXPLAIN输出信息说明 列名 说明 id 执行编号，标识select所属的行。如果在语句中没子查询或关联查询，只有唯一的select，每行都将显示1。否则，内层的select语句一般会顺序
MySQL索引优化分析（下）
一颗洛米
07-04 2691
五、索引优化1、索引分析（1）、单表建表CREATE TABLE IF NOT EXISTS article( id INT(10) UNSIGNED NOT NULL PRIMARY KEY auto_increment, author_id INT(10) UNSIGNED NOT NULL COMMENT'作者id', category_id INT(10) UNSIGNED NOT NUL...
mysql 索引优化策略有哪些_Mysql-索引优化策略
weixin_32349093的博客
01-26 288
本节后边通过实际易懂的举例，来说明索引实际的触发，从而优化自己在加索引时使用的策略。一.索引类型1.1 B-tree索引注: 名叫btree索引,大的方面看,都用的平衡树,但具体的实现上,各引擎稍有不同,比如,严格的说,NDB引擎,使用的是T-tree，Myisam,innodb中,默认用B-tree索引但抽象一下---B-tree系统,可理解为”排好序的快速查找结构”.更多分析，见Mysql-索...
浅谈MySQL索引优化分析
09-09
MySQL索引优化分析是数据库管理员和开发人员必须掌握的关键技能，因为它直接影响到数据库的查询性能和整体效率。在MySQL中，索引是一种特殊的数据结构，用于加速数据检索。当正确地设计和使用时，索引能够显著提高...
MySQL 索引分析和优化
09-11
正确地选择索引类型是索引优化的关键。不同的索引类型适合不同的应用场景。例如，在查询中频繁使用的列上创建索引可以提高查询性能。 ### Optimizing Index Creation 索引的创建需要消耗资源，因此需要合理地创建...
MySQL 索引优化全攻略
12-14
所谓索引是为特定的mysql字段进行... 　EXPLAIN可以帮助开发人员分析SQL问题，explain显示了mysql如何使用索引来处理select语句以及连接表，可以帮助选择更好的索引和写出更优化的查询语句. 　使用方法，在select语句
mysql 索引优化策略有哪些_索引优化策略有哪些
weixin_35128544的博客
01-20 176
一、前言本文基于mysql8.0的innodb测试，建表在做对应的优化策略测试时记得加索引，由于文中太多查询例子不一一针对建立索引了，只挑几个建索引举例。CREATE TABLE `user` (`id`int(11) NOT NULL,`name`varchar(20) DEFAULT NULL,`sex`varchar(5) DEFAULT NULL,`address`varchar(255)...
MySQL(十二)：索引分析
Edwin_Hu的博客
06-13 830
数据库优化是一个很常见的面试题，下面就针对这一问题详细聊聊如何进行索引与sql的分析与优化。
MySQL索引分析
weixin_48268269的博客
05-15 841
首先创建示例表，建立主键索引和联合索引。 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 '职位',
8_MySQL索引的性能分析工具的使用
最新发布
Justinyh的博客
11-02 246
第八章 MySQL系列文章之索引的性能分析工具的使用
mysql索引分析_mysql索引分析
weixin_42510453的博客
01-19 229
前言一直想深入的研究一下mysql的索引原理，奈何工作太忙没有时间，最近数据量过大，做了好多sql优化...终于，是时候研究一波了。索引的本质聊索引之前，我们得先知道索引是什么？有什么用？目前常用的索引是以何种形式呈现的？MySQL官方对索引的定义为：索引(Index)是帮助MySQL高效获取数据的数据结构。提取句子主干，就可以得到索引的本质：索引是数据结构。我们知道，查询在数据库中经常用到，但是...
mysql的索引分析_MySQL 索引分析
weixin_31889705的博客
01-28 142
MySQL复合唯一索引分析关于复合唯一索引(unique key 或 unique index)，网上搜索不少人说：”这种索引起到的关键作用是约束，查询时性能上没有得到提高或者查询时根本没有走索引列“。也有人说：“查询时使用到了索引和普通索引一样“。那么问题到底是怎样的呢？测试准备工作准备建表语句，插入数据等工作：-- 建表(注：a,b是复合唯一索引)create table test0(id b...
Mysql索引的优化分析-索引性能分析
qq_38087346的博客
08-13 402
一、MySql Query Optimizer(查询优化器) mysql中有专门的负责优化select语句的优化器模块，主要功能：通过计算分析系统中收集到的统计信息，为请求的query提供他认为最好的执行方法
MySQL索引优化分析之性能分析（Explain执行计划）
lixinkuan的博客
11-10 666
一、MySQL常见瓶颈 二、性能分析工具Explain（执行计划） 使用EXPLAIN关键字可以模拟优化器执行SQL查询语句，从而知道MySQL是如何处理你的SQL语句的。分析你的查询语句或是表结构的性能瓶颈。查看官网说明： 使用： Explain + SQL语句 作用： 三、各字段解释 3.1、 id 解释：select查询的序列号,包含一组数...
MySQL索引优化：最佳实践
总结来说，MySQL索引最佳实践涉及选择适当的索引类型、合理设计复合索引、避免过度索引以及定期评估和优化索引结构。通过遵循这些原则，可以显著提升数据库的查询效率，同时降低潜在的性能问题。
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值