MySQL思考-索引

最新推荐文章于 2024-12-13 15:44:14 发布
最新推荐文章于 2024-12-13 15:44:14 发布
阅读量322 收藏
点赞数
CC 4.0 BY-SA版权
分类专栏： MySQL 文章标签： 数据库 索引 mysql java redis
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.youkuaiyun.com/flowerAndJava/article/details/114855740
本文详细探讨了MySQL索引的本质、B-Tree和B+Tree知识，重点分析了MyISAM和InnoDB引擎的索引实现。同时，介绍了如何通过慢查询日志进行性能分析，并利用EXPLAIN理解SQL执行计划。最后，文章分享了索引分类、创建与删除方法，以及Filesort、GROUP BY、LIMIT和COUNT等常见查询的优化策略。

摘要生成于 C知道 ，由 DeepSeek-R1 满血版支持， 前往体验 >

一 索引本质

  1. 索引是什么？
索引：有序的数据结构。
  1. 索引目的？
帮助MySQL高效获取数据。
  1. 索引文件目录

MyISAM引擎

-rw-r-----. 1 polkitd input 61 9⽉ 2 11:31 db.opt
-rw-r-----. 1 polkitd input 8668 9⽉ 2 11:36 tb_user2.frm #表结构⽂件
-rw-r-----. 1 polkitd input 0 9⽉ 2 11:36 tb_user2.MYD #MyISAM引擎类型的表数据⽂件
-rw-r-----. 1 polkitd input 1024 9⽉ 2 11:36 tb_user2.MYI #MyISAM引擎类型的索引⽂件

InnoDB

-rw-r-----. 1 polkitd input 8668 9⽉ 2 11:33 tb_user.frm #表结构⽂件
-rw-r-----. 1 polkitd input 114688 9⽉ 2 11:34 tb_user.ibd #InnoDB的表空间⽂件，⽤于存储数据以及索引⽂件

二 MySQL索引前置知识

2.1 B-Tree知识

  1. 概念
度数：在树中，每个节点的⼦节点（⼦树）的个数就称为该节点的度（degree）。
阶数：定义为⼀个节点的⼦节点数⽬的最⼤值。
  1. m阶B-Tree满⾜以下条件
每个节点最多拥有m个⼦树
根节点⾄少有2个⼦树
分⽀节点⾄少拥有m/2颗⼦树（除根节点和叶⼦节点外都是分⽀节点）
所有叶⼦节点都在同⼀层、每个节点最多可以有m-1个key，并且以升序排列
每个⾮叶⼦节点由n-1个key和n个指针组成，key和指针互相间隔，节点两端是指针
  1. 3阶B-Tree的示意图（节点包含键值和数据）
    在这里插入图片描述
  2. 选用B-Tree做索引结构(MongoDB)
⼀次读取⼀个磁盘块数据中包含了多个key数据，这样就可以在内存中做⽐较操作，减少了磁盘IO的操作。

2.2 B+Tree知识

  1. B+Tree示意图
    在这里插入图片描述
  2. B+Tree相对于B-Tree有几点不同
⾮叶⼦节点只存储键值信息。
数据记录都存放在叶⼦节点中。
所有叶⼦节点之间都有⼀个s双向链指针。
  1. InnoDB为什么使用B+树做索引树

B+树的磁盘读写代价更低

内部节点只存储索引不存储数据。所以同一块磁盘中读取的索引数量就越多，减少IO的读写次数。

B+树的查询效率更加稳定

所以任何关键字的查找必须⾛⼀条从根节点到叶⼦节点的路。

三 索引结构与分析

3.1 MyISAM索引实现(B+Tree)

主键索引实例
在这里插入图片描述
索引的解释

MyISAM的索引⽂件仅仅保存数据记录的地址。在MyISAM中，主索引和辅助索引（Secondarykey）在结构上没有任何区别，
只是主索引要求key是唯⼀的，⽽辅助索引的key可以重复。

辅助索引实例
在这里插入图片描述
MyISAM的索引搜索步骤

MyISAM中索引检索的算法为⾸先按照B+Tree搜索算法搜索索引，如果指定的Key存在，则取出其data域的值，
然后以data域的值为地址，读取相应数据记录。

备注

MyISAM的索引⽅式也叫做“⾮聚集”的，之所以这么称呼是为了与InnoDB的聚集索引区分。

3.2 InnoDB索引实现

  1. 主键索引(聚集索引)

引入

InnoDB的数据⽂件本身就是索引⽂件。

主键索引示意图
在这里插入图片描述

叶子节点包含完整的数据记录。这种索引也叫做聚集索引。

数据是否可以没有主键？

MyISAM可以没有主键，但是InnoDB数据文件按照主键聚集，所以InnoDB必须含有主键。但是如果没有显示指定,
MySQL会自动制定一个唯一标识和不为空的字段做为主键。如果不存在这种类型，MySQL会自动生成一个隐式主键，
字段长度为6个字节，类型为长整型。不能在业务中使用。
  1. 非主键索引
    在这里插入图片描述
这⾥以英⽂字符的ASCII码作为⽐较准则。辅助索引搜索需要检索两遍索引：⾸先检索辅助索引获得主键，然后⽤主键到主索引中检索获得记录。
  1. 联合索引
    在这里插入图片描述
    在这里插入图片描述

联合索引执行流程

执行sql
SELECT * FROM tb_contact WHERE index_code = 'M' AND surname = 'ma' AND name ='yun'

首先从根节点查找(根节点常驻内存中)，第⼀个列为index_code，其值为：M，在L与W之间，会继续
向⼦节点查询。找到子节点后，将⼦节点数据从磁盘加载到内存中，采⽤⼆分法进⾏查找，找到M ma yun
数据符合条件，再继续查找⼦节点，读取到⼦节点中的data数据，其数据就是这条记录的主键，然后再通
过主键索引查询数据，最终将在主键索引中查询到数据。

联合索引使用注意(最左前缀原则)

使用联合索引必须按照索引顺序使用，不然导致索引失效。如下sql：(索引结构如上图)

SELECT * FROM tb_contact WHERE index_code = 'M' AND surname = 'ma' AND name ='yun' --索引会⽣效
SELECT * FROM tb_contact WHERE index_code = 'M' AND surname = 'ma' --索引⽣效
SELECT * FROM tb_contact WHERE index_code = 'M' --索引会⽣效
SELECT * FROM tb_contact WHERE surname = 'ma' AND name = 'yun' --索引不会⽣效
  1. 覆盖索引和回表查询

什么是回表查询？

先根据非主键索引查询到主键值。再扫描主键索引获取行数据。

什么是覆盖索引

只在一颗索引树上就能获取sql查询出所需的所有列数据。

四 慢查询日志

show profiles 能够在做SQL优化时帮助我们了解时间都耗费到哪里去。

4.1 profile开启

  1. 查看MySQL是否支持profile
select @@have_profiling;

在这里插入图片描述
2. 查看profiling是否开启

select @@profiling;

在这里插入图片描述
3. 开启profiling

执行命令   set profiling=1;

4.2 show profile

  1. 准备sql
show databases;
use db01;
show tables;
select * from tb_ksu where id < 5;
select count(*) from tb_ksu;
  1. 执行show profiles(查看sql耗时)
    在这里插入图片描述
  2. 执行show profile for query query_id
查看该sql执行过程中，每个线程状态和执行时间

在这里插入图片描述
备注

Sending data 状态表示MySQL线程开始访问数据行并把结果返回给客户端。由于在Sending data状态下，
MySQL线程往往需要做大量的磁盘读取操作，所以经常是整各查询中耗时最长的状态。
  1. 查看在什么资源上消耗过高时间
MySQL支持进一步选择all、cpu、block io 、context、switch、page faults等明细类型类查看MySQL在使用什么资源上耗费了过高的时

show profiles cpu for query 6;

在这里插入图片描述

4.3 慢查询日志分析

  1. 查询long_query_time 的值
show variables like 'long%'

在这里插入图片描述
sql执行时间超过十秒就会被记录在慢查询日志中。
2. 查看慢查询日志

cat slow_query.log

在这里插入图片描述
3. 慢查询日志分析

如果慢查询日志内容很多， 直接查看文件，比较麻烦， 这个时候可以借助于mysql自带的mysqldumpslow 工具， 来对慢查询日志进行分类汇总。

在这里插入图片描述

五 EXPLAIN执行结果认识

5.1 查看sql执行计划

explain select * from tb_sku where name like '%HuaWei 手机Meta87384 Pro%';

在这里插入图片描述

5.2 执行计划字段解释

  1. id
select查询的序列号，代表sql执行顺序。iid相同的可以认为一组，从上往下执行。在所有组中，id值越大，越先执行。
  1. select_type(select 类型)
    在这里插入图片描述
  2. type(访问类型)

在这里插入图片描述
备注

一般来说， 我们需要保证查询至少达到 range 级别， 最好达到ref 。
  1. key
possible_keys : 显示可能应用在这张表的索引。
key ： 实际使用的索引， 如果为NULL， 则没有使用索引。
key_len : 表示索引中使用的字节数， 该值为索引字段最大可能长度，并非实际使用长度，在不损失精确性的前提下， 长度越短越好。
  1. rows
扫描行的数量
  1. extra
执行情况的说明和描述。

六 索引

6.1 索引分类

主键索引、唯一索引、普通索引、组合索引、全文索引

6.2 创建和删除索引

create index 索引名字 on表名(字段名) # 新增普通索引
drop index 索引名字 on 表名 # 删除索引
create unique index 索引名字 on 表名(字段名) # 添加唯一索引
create index idx_emp_age_salary on emp(age,salary);  # 添加组合索引

七 常见优化

7.1 Filesort优化

  1. FileSort排序
不是通过返回数据进行排序叫做FileSort排序。

在这里插入图片描述

  1. using index(通过有序索引顺序扫描直接返回结果)
    在这里插入图片描述
  2. 多字段排序
    在这里插入图片描述
Order by 的字段都是升序，或者都是降序。否则肯定需要额外的操作，这样就会出FileSort。优化目标清晰了，通过索引直接返回有序数据，减少额外的排序。
  1. Filesort的优化
某些条件限制不能让Filesort消失。MySQL 通过比较系统变max_length_for_sort_data 的大小和Query语句取出的字段总大小。
如果max_length_for_sort_data更大采用一次扫描算法，否则采用二次扫描算法。

一次扫描算法，一次性取出所有满足条件字段在排序区中排序后直接输出结果。一次排序内存消耗大，但排序效率比二次排序效率高。

因此sort_buffer_size 和 max_length_for_sort_data 系统变量，来增大排序区的大小，提高排序的效率

在这里插入图片描述

7.2 group by优化

  1. 前置
group by 实际也会进行排序操作。但是与order by相比多了排序后的分组操作和分组后的一些聚集函数使用。
使用group by避免排序结果的消耗，可以使用禁止排序，sql加上order by null

优化前

explain select age,count(*) from emp group by age;

在这里插入图片描述
优化后

explain select age,count(*) from emp group by  age order by null;

在这里插入图片描述

7.3 limit优化

一般分页查询时候，通过覆盖索引能更好的提升性能。limit分页越往后性能越差。因为要查询5000000 - 5000010 的记录，
需要先查询出前前5000010，丢弃掉前5千条。

7.4 count优化

  1. 概述
我们进行分页和获取总记录数，需要遍历全表数据，对计数进行累加。如果数据量比较大，cout性能比较低。
  1. 优化方案
a 在redis定义一个计数器，增加和删除对计数动态更新。
b 在数据库定义一个计数器，删除和插入进行动态更新。
【第14天】MYSQL进阶-索引的函数索引（SQL 小虚竹)
小虚竹的专栏
09-28 1万+
函数索引
【第13天】MYSQL进阶-索引的隐藏索引（SQL 小虚竹)
小虚竹的专栏
09-26 1万+
隐藏索引
MySQL思考-存储引擎(InnoDB)
flowerAndJava的博客
03-15 227
一 MySQL体系架构 MySQL Server组成部分 Connection Pool :连接池组件 Management Services & Utilities :管理服务和工具组件 Parser： 查询分析器组件 Optimizer: 优化器组件 Caches & Buffers： 缓冲池组件 Pluggable Storage Engines：存储引擎 File System : 文件系统 1.1 各层之间解释 连接层 完成一些连接处理、授权认证。 服
Mysql问题解决思路
eafun_888的博客
03-08 310
数据库层面 一：检查问题常用工具 1：msyqladmin：MySQL客户端，可进行管理操作 2：mysqlshow：功能强大的查看shell命令 3：show [SESSION | GLOBAL] variables：查看数据库参数信息 4：SHOW [SESSION | GLOBAL] STATUS：查看数据库的状态信息 5：information_schema：获取元数据的方法 ...
基于docker的mysql使用
何止七八的博客
12-25 695
搭建一些个人项目时，使用 docker 能够非常方便的进行一些基础服务的创建。 这是 mysql 的官方镜像地址 https://hub.docker.com/_/mysql/ 下载镜像 冒号后面跟的是数据库版本，我选择的版本是 mysql 5.7 #docker pull mysql:5.7 使用 docker images 确认镜像是否下载成功 # docker images REPOSITORY TAG IMAGE ID CRE
20-【kubernetes】Kubernetes 存储卷
qq_42303254的博客
10-13 595
一、前言 1、根据应用本身是否需要持久存储数据，以及每一次的请求跟此前的请求之间是否有关联性，可以分为四类应用： 有状态、要存储 有状态、无存储 无状态、有存储 无状态、无存储 2、持久化数据的方式 节点级：节点提供存储卷。此方式在k8s上，不太适用。这是因为一旦Pod被删除了，Pod是要被Scheduler组件重新调度的，调度之后的Pod并不一定还能运行在原来的节点上。 脱离节点而存在的共享存储设备。k8s使用这种方式。 3、Pod的特点： 同一个Pod内的多个容器，可共享访问同一个存储卷
Docker 安装mysql
xcbeyond|疯狂源自梦想，技术成就辉煌
09-01 1212
目录 1、拉取镜像 2、配置mysql 3、mysql连接验证 1、拉取镜像 从docker仓库拉取mysql 5.7版本的镜像。 [docker@xcbeyond ~]$ docker pull mysql:5.7 5.7: Pulling from library/mysql be8881be8156: Pull complete c3995dabd1d7: Pull compl...
MySQL-索引
m0_73902080的博客
07-31 1257
MySQL的索引是在存储引擎层实现的，不同的存储引擎有不同的结构，主要包含以下几种：不同的存储引擎对索引结构的支持情况：注意：平常所说的索引，如果没有特别指明，都是指B+树索引。
MYSQL---索引---B+树
qq_31331965的博客
10-03 1254
本文将探讨以下几个问题。 一、思考 问题一：B树和B+树的区别？ 问题二：为什么InnoDB要采用B+树数据结构？ 二、分析 B树的结构 本文大量参考了其他比较优秀的博文，具体地址，请看文末。 B即balance，意为平衡，B树又称为多路平衡查找树，也称B-树。多路即允许每个节点有多个子节点，称为阶。当阶数为2时，又称为平衡二叉查找树。 M阶B树的定义： 每个节点至多有M个...
MYSQL---索引---物理结构
qq_31331965的博客
10-03 851
本文意图理解以下几个问题。 一、思考 问题一：索引存放在哪里？ 问题二：B+树是如何以文件形式存储，如何读取？ 二、分析 索引文件位置 索引可以理解为一种数据结构，我们按照这种数据结构能快速定位到数据。大家会很容易想到这种类似字典的概念性东西，但是大家有没有思考过索引自身也是数据，其存放在哪里。如何将这种数据结构存放？多个索引如何存放？如何将存放的数据又转换为内存中的数据结构以便...
docker 实战练习1
weixin_34315485的博客
01-23 308
docker search mysql --搜索镜像 docker pull docker.io/mysql --下载镜像 docker images --查看下载的镜像 docker tag docker.io/mysql mysql8:new --给镜像加标签 docker inspect d06c3c789716 --查看镜像的详细信息 docke...
「架构师必学」数据库中查询性能的提升--索引的深度剖析
a6636656的博客
03-29 1128
本文为博学谷狂野架构师团队原创内容，转载请标明出处。 课程说明 索引的本质 数据结构与算法 B+树深入剖析 索引的使用 1、索引的本质 在生产环境中，随着数据量不断的增长，SQL执行速度会越来越慢，常见的手段就是通过索引来提升查询速度，那么究竟为什么要添加索引？应该如何正确添加索引？本套课程将以MySQL为例，从索引的底层进行分析，一起探究索引相关的一系列问题。 MySQL官方对索引的定义为：索引（Index）是帮助MySQL高效获取数据的数据结构（有序）。所以，就可以得到索引的本质：...
MySQL死锁成因及解决方案
m0_53926113的博客
12-13 5212
在 MySQL 的并发事务处理中，虽然完全避免死锁不现实，但可以通过优化设计和合理操作，大幅降低死锁发生的概率。在上面死锁图中的示例中，如果事务1和事务2按照相同的顺序获取锁，死锁就不会发生。死锁是数据库并发事务处理中常见的问题，通过合理的设计和优化，可以有效降低死锁发生的概率。通过这个命令，可以查看死锁的原因、涉及的事务、被锁住的行和死锁的图形化表现等。这种行为也由隐式锁实现。InnoDB 的隐式锁由存储引擎在后台完成，用户看不到这些锁的具体表现形式，通常它的加锁过程伴随事务语句的执行自动发生。
MySQL死锁
2401_89221704的博客
11-29 1296
死锁是指两个或者两个以上的进程在执行过程中，由于竞争资源或者由于彼此通信而造成的一种阻塞的现象，若无外力作用，它们都将无法推进下去。此时称系统处于死锁状态或系统产生了死锁，这些永远在互相等待的进程称为死锁进程。
MySQL(二):InnoDB和MyISAM
qq_43398345的博客
02-17 622
即：InnoDB 如果添加其他辅助索引，辅助索引查询就需要两次查询，先查询到主键，然后再通过主键查询到数据。因此，主键不应该过大，因为主键太大，其他索引也相应都会很大。（比如表中有id(主键),name,age 字段，我们创建一个主键索引，再来创建一个name索引，层级关系如下图所示）InnoDB是聚集索引，使用B+Tree作为索引结构，数据文件是和（主键）索引绑在一起的；MyISAM是非聚集索引，它也是使用B+Tree作为索引结构，但是索引和数据文件是分离的，索引保存的是数据文件的指针。
MySQL产生死锁的根本原因及解决方法
热门推荐
Kevinvcc200的博客
09-16 3万+
一、 什么是死锁 死锁是指两个或两个以上的进程在执行过程中,因争夺资源而造成的一种互相等待的现象,若无外力作用,它们都将无法推进下去.此时称系统处于死锁状态或系统产生了死锁,这些永远在互相等的进程称为死锁进程. 二、 死锁产生的四个必要条件 •互斥条件：指进程对所分配到的资源进行排它性使用，即在一段时间内某资源只由一个进程占用。如果此时还有其它进程请求资源，则请求者只能等待，直至
常见的表死锁情况及解决方法
LzwGlory的专栏
03-25 2795
1、死锁的第一种情况 一个用户A 访问表A(锁住了表A),然后又访问表B；另一个用户B 访问表B(锁住了表B)，然后企图访问表A；这时用户A由于用户B已经锁住表B，它必须等待用户B释放表B才能继续，同样用户B要等用户A释放表A才能继续，这就死锁就产生了。 解决方法 这种死锁比较常见，是由于程序的BUG产生的，除了调整的程序的逻辑没有其它的办法。仔细分析程序的逻辑，对于
MySQL的事务与锁
qq_56370103的博客
08-23 1273
在现实生活中，人们通过银行互相转账和汇款，从数据的角度来看，这实际上就是数据库中两个不同账户之间的数据操作。例如，用户A向用户B转账了1000元，则A账户的余额减去1000元，B账户的余额加上1000元，整个过程需要使用两条SQL语句来完成操作，若其中一条语句出现异常没有被执行，则会导致两个账户的金额不同步，从而使数据出现错误。为了避免上述情况的发生，MySQL中可以通过开启事务来进行数据操作。用户转账过程中，只有事务执行成功后数据才会变更，如果事务执行失败，数据库中的值将不会变更。
[MySQL FAQ]系列 — 为什么InnoDB表要建议用自增列做主键
huyangg的专栏
09-11 1132
原文地址：http://imysql.com/2014/09/14/mysql-faq-why-innodb-table-using-autoinc-int-as-pk.shtml 我们先了解下InnoDB引擎表的一些关键特征： InnoDB引擎表是基于B+树的索引组织表(IOT)； 每个表都需要有一个聚集索引(clustered index)； 所有的行记录都存储在B+树
mysql数据库 - 查询
最新发布
03-15
<think>好的，我现在需要帮助用户解决如何在MySQL中执行数据库查询的问题。首先，我需要回顾用户提供的引用内容，确保回答准确。用户提到了引用[3]中的内容，其中提到使用Python执行MySQL查询，使用%s作为占位符，以及游标的遍历方法。 接下来，我需要按照用户的要求，结构清晰地分步骤解释。首先应该提到连接到数据库，然后创建游标，接着执行查询语句，处理结果，最后关闭连接。同时，用户要求行内数学表达式用$...$，但这里可能用不上数学公式，但需要确保代码块正确。 需要注意的是，用户希望回答中使用中文，并且生成相关问题。引用的引用标记需要自然添加在相关段落末尾。例如，在讲执行查询时，引用[3]提到使用占位符和遍历游标，所以需要在对应部分加上[^3]。 可能还需要检查是否有其他引用相关的内容，比如引用[2]提到的执行计划，可能在优化查询性能时提到，但用户的问题主要是如何执行查询，所以可能不需要深入。但生成的相关问题中可以包括性能优化，这样可以利用引用[2]。 然后，生成相关问题时要考虑用户可能感兴趣的后续问题，例如如何优化查询、防止SQL注入、使用连接查询等。确保每个问题都与主题相关，并且有对应的引用支持。 最后，确保代码示例正确，使用Python和MySQL的连接方法，包括导入库、建立连接、创建游标、执行查询、获取结果和关闭连接。同时，提醒用户使用预处理语句来防止SQL注入，引用[3]中的内容支持这一点。 现在需要将这些思考整理成一个连贯的回答，分步骤说明，并附上代码示例和相关问题。同时，确保引用标记正确放置，符合用户的要求。</think>在MySQL中执行数据库查询需要遵循以下步骤，结合代码示例和最佳实践进行说明： ### 一、建立数据库连接 使用Python操作MySQL前，需要先安装mysql-connector-python库： ```bash pip install mysql-connector-python ``` 建立连接的代码实现： ```python import mysql.connector # 创建数据库连接 conn = mysql.connector.connect( host="localhost", user="your_username", password="your_password", database="your_database" ) ``` ### 二、创建游标对象 游标（Cursor）是执行查询和获取结果的核心工具： ```python cursor = conn.cursor() ``` ### 三、执行查询语句 #### 1. 基础查询 ```python query = "SELECT * FROM employees WHERE department = %s" params = ("IT",) cursor.execute(query, params) # 使用参数化查询防止SQL注入[^3] ``` #### 2. 获取查询结果 ```python # 获取单条记录 result = cursor.fetchone() # 获取前N条记录 results = cursor.fetchmany(size=5) # 获取全部记录 all_results = cursor.fetchall() # 直接遍历游标（适合大数据量） for (id, name, department) in cursor: print(f"员工ID：{id}, 姓名：{name}") ``` ### 四、处理查询结果 建议使用字典游标获取更易读的结果： ```python dict_cursor = conn.cursor(dictionary=True) dict_cursor.execute("SELECT * FROM products") for row in dict_cursor: print(row["product_name"], row["price"]) ``` ### 五、关闭连接 ```python cursor.close() conn.close() ``` ### 六、性能优化建议 1. 使用`EXPLAIN`分析执行计划[^2]： ```sql EXPLAIN SELECT * FROM orders WHERE total_amount > 1000; ``` 2. 合理使用索引 3. 限制返回字段，避免`SELECT *`
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值