对mysql索引、排序理解

已于 2024-06-06 14:14:00 修改
阅读量808 收藏
点赞数
分类专栏： mysql 文章标签： mysql 数据库 java
于 2022-10-08 15:10:02 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/weixin_47733858/article/details/127205320
版权

节前和同时讨论了一下mysql的索引,发现自己对索引的理解有很大问题,国庆节就看了一下这里做一下记录,目前了解的比较浅如果有理解错的地方还请大家可以提出来一起讨论、学习
再聊索引之前我们一起聊一下表在windows里面的存储,其实库在机器里面就是一个目录
表就是对应库目录下面的以.frm结尾的文件,文件里面就记录了一条条数据

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

什么是索引

  • 自己理解就是存在磁盘里面为了提升查询速度的一棵树,当然在需要的时候会将索引从磁盘加载到内存里

聚簇索引

  • 聚簇索引就是主键索引下面是画的一张主键索引草图,当我们通过索引查询的时候先会将索引树加载到内存里面,之后通过二分法找到值所在磁盘,将指定磁盘加载到内存在通过二分法找到主键下面对应的数据

    非聚簇索引
  • 非聚簇索引也叫二级索引,他跟聚簇索引的区别是不是主键 并且最后的叶子结点存放的不是最终的数据,而是主键的值,在查询到主键的值后在回到主键索引树里面找到最终的数据,这个过程叫做回表 可以看出会标需要查询两次索引树性能较慢,不过我们也可以通过索引覆盖来避免回表.
    在这里插入图片描述

索引覆盖

  • 索引覆盖就是我们创建的非聚簇索引包含了我们需要查询的所有字段这时候我们需要的数据在索引树上面就全部都有了,不需要在回表了,那怎么让我们索引包含多个字段呢?这又引入了另一个中非聚簇索引 联合索引

联合索引

  • 联合索引是将表里面多个字段合在一起作为一个索引树,其实这是创建了三个索引,但是再使用的时候会遵循最左原则,这里的最左原则我们稍后会详细介绍,先看下图了解,
  • 通过下图我们可以发现联合索引树上面的顺序是根据最左边的索引来决定的,而后面的索引会根据前面的索引作为第一排序,然后在对第二、第三…进行排序 可以理解成ascll码值的排序比如下图所展示的 (1,1 1,2 2,1 2,4 3,1)
    在这里插入图片描述
  • 创建一张表包含的字段和设置索引
    在这里插入图片描述在这里插入图片描述

第一段sql通过包含最左边的字段进行查询其匹配规则是先找到索引树上面最左边的字段name进行查询,在通过第二个age进行查询在通过orde进行查询可以看到每一个查询的类型都是const,这里编写sql的时候可以不用关注先后顺序,mysql会自动优化顺序在进行查询
在这里插入图片描述

这句sql没有包含最左边的字段name最终查询的类型是index也就是便利了整课索引树
在这里插入图片描述

如果联合索引是由字段A,B,C组成那么在查询的时候如果是通过where A= ? and C = ?这样的方式就没办法两个索引都使用到,因为联合索引排序是从左到右字段顺序排列,就下下面的例子,name = ‘555’ and orde = 6 通过使用索引的长度可以看出来只是使用了name字段
在这里插入图片描述

索引与排序
filesort: 这个排序准确来说有三种排序方式

  1. 单路排序:查询条件使用了索引但是排序没使用索引(联合索引排除,下面细说)mysql会将查询出来的数据放入到sort buffer进行排序然后返回
  2. 双路排序:还是单路排序的情况但是这时候查询出来的字段特别多sort buffer内存不够了,这时候mysql会将主键id(可以定位到整条数据的字段值)和排序的字段值放入到sort buffer里面进行排序然后拿出来再回一次表将数据返回
  3. 文件排: 这种情况就是查询出来的数据过大双路排序晒出来的字段也超过了sort buffer里面的内存这时候mysql会将所有的数据拆分成多份,每一份单独排序生成一个临时小文件,之后小文件在跟小文件之间排序一次类推生成一个数据总和返回效率极低

我们创建一个 id 主键索引 age,sex 联合索引的一张测试表
在这里插入图片描述
在这里插入图片描述

  1. mysql默认排序貌似是有自己的算法来决定的,这里的sql可以看出来他是通过age的索引进行顺序排列,并不一定默认是主键,通过explain来看根据索引排序的话无论是正序还是倒叙都不会触发filesort联合索引排除
select * from t_test;

在这里插入图片描述

  1. 这里详细说一下联合索引,我们都知道最左原则这里通过最左原则的age先定位到部分数据也就是age=1的数据后面排序通过联合索引里面的sex进行倒叙或者顺序都不会触发filesort
EXPLAIN
select * from t_test where age = 1 order by sex desc;
EXPLAIN
select * from t_test where age = 1 order by sex;

在这里插入图片描述

  1. 如果最左原则查到的是一个范围而不是相同数据的值那么可以看出来无论联合索引里面的另一个字段是顺序还是倒叙都会触发filesort这个我们通过上面的联合索引存储方式很容易理解,因为联合索引默认会对数据进行排序存储,(1,1) (1,2) (2,1) (2,2)这里where使用到了索引age但是因为是联合索引所以没有办法对age进行单独排序在对sex记性排序的时候就没办法通过索引了
EXPLAIN
select * from t_test where age in (1,2) order by sex asc;
EXPLAIN
select * from t_test where age in (1,2) order by sex desc;

在这里插入图片描述

  1. 还是上面的情况,联合索引最左原则筛选出范围数据,因为联合索引的排序是多字段的所以排序的时候需要加上最左边的即可不不触发filesort,但是还有触发的情况继续往下看
EXPLAIN
select * from t_test where age in (1,2) order by age asc,sex asc;
EXPLAIN
select * from t_test where age in (1,2) order by age desc,sex desc;

在这里插入图片描述

  1. 如果联合索引里面的排序加上了最左边的,但是两个字段一个是正序一个是降序也会触发filesort这个原因结合联合索引的存储方式就很容易理解上面也有解释
EXPLAIN
select * from t_test where age in (1,2) order by age asc,sex desc;
EXPLAIN
select * from t_test where age in (1,2) order by age desc,sex asc;

在这里插入图片描述

  1. 需要注意的点:如果我查询的语句没有使用到索引 那么order by 即使使用了索引也会二次排序 举个例子 一张表 id:主键索引 age:普通索引 sex和subject:联合索引sex在左边 name:没有索引

示例一: 会进入sort file,查询条件没有走索引 所以这里order by只能二次排序
示例二: 会进入sort file,虽然where条件走了索引但是排序并没有通过age的索引树去排而是通过id所以需要二次排序
示例三: 不会进入sort file,虽然没有where条件但是查询的数据是age有索引树,所以这里扫的是age的整棵树order by的也是这个字段所以不需要二次排序
示例四: 不会进入sort file,这里能看出来mysql非常强大mysql的优化器会根据我查询的字段和条件在联合索引里面,所以会全表扫我的联合索引树根据sex进行排序不会二次排序但是这里的排序如果换成了subject那么就需要进入二次排序了,因为最左原则这个大家都知道就不多赘述

示例一:
select name from 表名 order by id;
示例二:
select name from 表名 where age in (1,2) order by id;
示例三:
select age from 表名 order by age desc;
示例四:
select id from 表名 where subject = '数学' order by sex desc;

索引树怎么保证顺序

我们都知道磁盘里面保存的数据不会存在顺序,是按照页的概念去存储一页存储16k数据这里面是没有任何顺序可言的,那么我们在查询的时候有默认的排序,其实这种都是通过索引树来实现的.

  • 在创建索引的时候会将指定的索引查出来进行排序,也就是我们上面说的单路排序、多路排序以及文件排序,排序结束后会插入到B+tree上这时候树是有顺序的类似下图
    在这里插入图片描述

  • 假设下面的叶子结点都已经处于饱满的状态我要在往里面赛数据,这时候就会出现页分裂的情况也就是最先出来的中间点页分裂大概就是我现在要插入索引数据9 那么会new一个page 然后将上一个<=9那一页的50%的数据移动到new的page里面然后再插入数据9如下图,但是这会有个问题如果我是顺序插入那么是不是意味着永远有碎片没有使用到类似红框里面的空间
    在这里插入图片描述

  • 所以上面的问题又进一步进行了优化 插入点页分裂就是如果我本次插入的数据是递增的数据就不将上一页50%的数据进行移动而是直接插入到下一页入下图1,但是这样又会有个问题就是如果这时候我插入11是new出来的新页那我在插入10是不是又需要new新页插入9也是需要new新页如图2
    在这里插入图片描述
    在这里插入图片描述

  • 为了解决上面的问题再次进行优化,如果是有序且插入的时候先尝试插入到next page且这时候需要修改上级索引的最小值如下如图所示
    在这里插入图片描述

索引下推

大家都知道联合索引最左匹配原则,但是在mysql5.6之后就出现了一个新的技术叫索引下推假设我现在一张表test_table,有一个联合索引 (product_name,product_code)

  1. 我们先关闭索引下推,这时候可以看到sql是走了索引但是使用了using where,这是因为我们只使用到了product_name这个索引后面的product_code没办法继续时候查询到的主键id回表后给到service进行where条件的过滤
// 关闭索引下推
set optimizer_switch="index_condition_pushdown=off";

explain
select * from product where product_name = '香油' and product_code like '%111xw%'

在这里插入图片描述

  1. 开启索引下推,可以看到直接是使用了using index condition就结束了,这时候的执行流程是匹配到product_name数据之后mysql发现当前索引树可以满足product_code的查询所以将之前product_name查询出来的数据再次进行过滤如果符合 %111xw%就记录不符合就跳过,之后将符合的条件回表数据给到service,且这时候的条件全部都执行完成不需要再次using where了
set optimizer_switch="index_condition_pushdown=on";

explain
select * from product where product_name = '香油' and product_code like '%111xw%'

在这里插入图片描述
隐式转换索引失效

同学们都知道索引失效的很多场景,但是对于隐式转换的索引失效了解可能没有那么多 今天我们来看看隐式转换导致的索引失效先看下表结构和数据,这里创建了一张表有字段id,name,created_at,age四个字段 其中name和created_at以及age都创建了普通索引,并且添加了几条数据

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

  1. 如果出现了隐式转换大类型转小类型会导致索引失效比如字符串转数字
explain
select * from t_order0 where name = '12';

在这里插入图片描述

explain
select * from t_order0 where name = 12;

在这里插入图片描述

  1. 小类型转大类型的隐式转换虽然不会导致索引失效但是也不推荐
explain
select * from t_order0 where age = 1

在这里插入图片描述

explain
select * from t_order0 where age = '1'

在这里插入图片描述

  1. 如果字符串没办法转数字,那么这时候会将数字转字符串
-- 这里等号后超过了数字的最大值导致索引失效(猜测)
explain
select * from t_order0 where age = 1666666666666666666666666;

在这里插入图片描述

-- 这里等号后超过了数字的最大,导致字符串没办法转数字,于是将age字段的数字转字符串 小类型转大类型,不影响索引
explain
select * from t_order0 where age = '1666666666666666666666666';

在这里插入图片描述

隐示转换数据变化

最常见的就是字符串转数字的这种情况,这种情况的规则是

  1. 如果是非数字开头那么就是0 例 abc123 -> 0
  2. 如果是数字开头那么就是从第一个非0的数字到最后一个数字 例 0123abc -> 123

下图目前表里面的数据情况,通过上面的学习分析一下下面的
1、name是varchar类型且有索引的字段
2、where条件后面name = 数字类型那么这时候会先考虑将字符串转成数字不行的话在考虑数字转字符串
那么表里面的所有数据全部转成数字后的值图三有展示分表是0,0,12,12
3、最后图三执行sql的结果出来的条数应该是2条

  • 目前表数据 图一

在这里插入图片描述

  • name从字符串转成数字后的表数据 图二

在这里插入图片描述

  • 执行的sql
select * from t_order0 where name = 12;
  • sql执行结果 图三
    在这里插入图片描述
渣渣港
关注
专栏目录
Mysql索引两种排序方式分析
qq_42872034的博客
03-13 1169
最新的 Java 面试题，技术栈涉及 Java 基础、集合、多线程、Mysql、分布式、Spring全家桶、MyBatis、Dubbo、缓存、消息队列、Linux…等等，会持续更新。
mysql索引排序（innoDB引擎）
qq_43044376的博客
06-27 819
本文介绍了mysql innodb存储引擎下索引排序规则以及排序优化
mysql索引排序
free361的博客
12-21 804
在建立索引时有时要考虑的排序字段，将排序字段加入索引也可以缩小搜索范围 explain select * from table_name where skin_id=4 order menu_order desc 上图可见，对应没有索引的sql，按menu_order 倒序，使用到了filesort。filesort非常影响效率，在数据量不大的情况下会使用内存排序，将查询结果导入内存，
MySQL入门学习-深入索引.匹配顺序
最新发布
weixin_69763181的博客
07-13 1012
在 MySQL 中，索引的匹配顺序是指在查询执行时，数据库系统根据查询条件中涉及的列和索引的结构，决定如何使用索引来提高查询效率的方式。
MySQL使用索引排序
程序员劝退师的博客
09-03 6072
前言 过去我们只知道使用ORDER BY来做排序，那么本章将来介绍MySQL使用索引进行排序，MySQL实现排序共有两种手段，第一种就是通过排序操作，第二种就是通过索引来排序。(关于查询语句中有没有使用带索引排序我们可以通过EXPLAIN执行计划得到type列的值为index，则说明使用到了索引来排序) 支持 MySQL可以使用同一个索引满足排序(ORDER BY)和查询(WHERE)操作,因此如果可以，设计索引的时候尽可能考虑同时满足两种任务 可能更慢 扫描索引本身就很快，因为只需要从一条索引记录移动到紧
mysql 索引顺序,MySQL学习之索引顺序
weixin_39933895的博客
03-17 341
做为一个PHP开发者，关于MySQL的索引是必不可少的一个问题比如一条SQL查询：select*fromuserstaticwherenicklike"辛巴%"andrip=3726854906;可能问到的问题有：1、如果nick不是索引，rip是索引，这样的条件顺序会不会有问题？这个问题比较简单，MySQL本身会对条件和索引进行判断，这样写可以用到索引，没有问题。2、如果nic...
Mysql —— 索引的使用顺序
qq_40907977的博客
03-11 1221
demo table 创建表 create table test( id int, v1 int, v2 int, primary key(id)) Engine=InnoDB DEFAULT CHARSET=UTF8; 该表的记录如下： 问题一：如果一条sql同时命中两个索引，那实际会用几个索引？ 添加两个索引： ALTER TABLE test ADD INDEX ...
mysql索引对排序的影响实例分析
09-09
MySQL索引在数据库管理系统中扮演着至关重要的角色，它们能够显著提升查询和排序操作的效率。在本实例分析中，我们将深入探讨MySQL索引如何影响排序速度，通过具体的操作步骤和测试来展示其效果。 首先，创建一个名...
MySQL InnoDB 二级索引的排序示例详解
09-09
理解InnoDB的二级索引排序原则对于数据库设计和查询优化至关重要。通过合理利用索引，可以显著提升查询性能，减少不必要的磁盘I/O，从而提高系统整体的响应速度。在实际应用中，应根据查询模式和业务需求来选择合适...
MySQL中利用索引对数据进行排序的基础教程
09-10
本文将介绍如何在MySQL中利用索引来高效地对数据进行排序。 首先，了解MySQL中的两种生成有序结果集的方法至关重要。第一种是使用filesort，这是MySQL在内部使用的排序算法，用于在内存中或者临时文件中对数据进行...
mysql查询优化之基于索引的排序
apache6的专栏
08-21 2029
  MySQL的弱点之一是它的排序。虽然MySQL可以在1秒中查询大约15,000条记录，但由于MySQL在查询时最多只能使用一个索引。因此，如果 WHERE条件已经占用了索引，那么在排序中就不使用索引了，这将大大降低查询的速度。  我们可以看看如下的SQL语句： SELECT * FROM SALES WHERE NAME = "jack" ORDER BY SALE_DATE DESC
mysql索引的排列顺序
ss123mlk的博客
01-15 2527
索引的排序是按照定义索引的顺序来的 索引的顺序要遵循三个规则 要遵循最左前缀 无论是多个还是一个列的索引 都不应该跳过最左列 如果在查询语句当中 没有使用最左前缀的字段 就不会使用索引 index_key1_key2_key3 是我们的索引 即使使用select key_2 ,key_3 from table where 。。。 越过了最左前缀 是不会使用到索引index_key1_key2_key3的 不能跨越索引列 index_key1_key2_key3 不能使用key1 key3字
索引与排序
明海棠
06-10 671
--以下语句没有索引又有order by ，必然产生排序 select * from t where object_id>2 order by object_id;
mysql索引之七：组合索引中选择合适的索引列顺序
weixin_30394333的博客
02-24 257
组合索引(concatenated index)：由多个列构成的索引，如create index idx_emp on emp(col1, col2, col3, ……)，则我们称idx_emp索引为组合索引。 在组合索引中有一个重要的概念：引导列(leading column)，在上面的例子中，col1列为引导列。当我们进行查询时可以使用”where col1 = ? ”，也可以使用”wher...
利用ArrayList对map进行按key（键）和按value (值) 排序
qq_41487251的博客
09-08 4638
利用ArrayList对map进行按key(键)和按value (值) 排序 总感觉按照值得应该比较少，意义好像也不大，感觉还没有真正懂。 使用比较器 按key（键）排序 通过ArrayList将map.entrySet()，变成list 然后在类外写具体如何比较大小的方法compare()，最后调用 Collections.sort(list, new Comparator<Map.Entry<String, String>>() import java.util.*; pub
关于在 MySQL 排序中使用索引这件事！
江南一点雨的专栏
05-12 2138
我们来梳理下：只有当索引的顺序和 order by 子句的顺序完全一致，并且所有列的排序方向也都一致的情况下，MySQL 才能通过索引来对结果进行排序，同时，如果是联合索引，order by 子句也需要满足最左匹配原则。可以看到，在这个查询中，优化器将 ta 表作为了第一张表，tc 表作为了第二张表，那么根据前面的结论，如果使用第一个表中的索引排序，就会用到索引排序，第二张表的则用不了，我们来验证一下。如果是第二张表的索引，就没有用到索引排序，如果两张表的索引都用了，也不会使用索引排序。
【mysql索引】之使用索引扫描做排序
KK_GKD的博客
01-25 655
前言 今天看了《高性能MySQL》的索引扫描做排序章节，并且亲身实践了一下，发现有些结果与原书不一样，个人猜测是MySQL版本不一样造成的，下面分享一下我个人的笔记。 简介 MySQL 有两种方式生成有序结果：通过排序操作或者按索引顺序扫描。 如果EXPLAIN出来type列的值为index，则说明MySQL使用索引扫描来做排序。(这句有疑问，很多情况下都type都不是index,
mysql集合索引顺序_MySQL（一）面试集合（索引）
weixin_39897505的博客
01-27 651
1. 什么是索引?索引是一种排好序的数据结构(存储数据),可以帮助我们快速的进行数据的查找.索引是帮助高效获取数据的数据结构，索引是一个文件1)索引有哪些类型：Hash二叉树红黑树Hash：根据值进行Hash值 Hash(id)，where id >1? 查找，不适合范围查找二叉树(度为二的有序树)二叉树：树的高度不可控IO次数多，深度为h 的非空二叉树最多有2h -1 (二的h次方-1)...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值