MySQL 使用 B树的好处B-树和B树最重要的一个区别就是B树只有叶子节点存放数据其余节点用来索引。而B-树是每个索引节点都会存数据。所以B树更适合用来存储磁盘数据B树的关键信息存储量更多B树的非叶子节点不存储数据只存储关键字和指针关键信息存储量比B树更多磁盘IO更少B树是用来做索引的数据量非常大索引也会存储在磁盘上。层数不高B树的查询效率更加稳定。B树任何关键字的查找从根节点到叶子节点的查询路径长度相同每个数据的查询效率相当B树更适合范围区间查询所有的叶子节点用指针按顺序串起来这样遍历叶子节点就能获得全部数据并且是有序的更加适合范围查询一MySQL的索引是什么?有什么作用和优缺点1. 介绍辅助数据结构 目录mysql的索引是存储引擎为了加快数据检索而创建的一种辅助数据结构本质上是对数据表中的一列或者多列数据的值进行排序的结构类似于我们查字典可以根据目录直接定位到我们想要查找的内容而不需要进行翻页查询可以加快数据访问的效率2. 索引数据结构 B树从底层实现的维度来说mysql的索引数据结构一般情况下是B树不同的存储引擎对于索引的实现是有区别的3. 存储引擎innodb 和 myisam 聚簇/非聚簇比如:mysql的innodb存储引擎中有聚簇索引也有非聚簇索引聚簇索引的叶子节点中存储的是整行的数据而非聚簇索引的叶子节点中存储的是聚簇索引的值查询的过程中可能会涉及到回表的操作mysql的myisam存储引擎中是非聚簇索引所有的索引的叶子节点都存储的是数据物理的行号需要通过行号再去数据文件中找到对应的数据4. 本质作用提升检索效率 有和没有索引的核心作用是提升数据检索的效率大幅度降低查询的IO开销加快select语句的执行比如有一张千万级别的订单表没有索引的时候where order id12345,需要全表扫描遍历所有的数据页IO的开销是极大的当创建了orderid的索引之后可以直接通过B树的多路平衡结构最多3-4次IO操作就可以直接定位到对应的数据查询的效率提升了几个数量级5. 额外作用帮助排序 分组 数据完整性除此之外还有一些额外的作用比如:1、当sql语句中包含order bygroup by操作的时候如果排序或者分组的字段中添加了索引因为索引本身是有序的存储引擎可以直接利用索引的有序性完成排序不需要在内存中做文件排序了(查看执行计划的时候extra会出现usingfilesot使用了索引的有序性之后这个信息就会消失)这种情况可以大幅的提高排序的性能2、mysql中主键索引和唯一索引本质上是通过索引的唯一性来保证数据的完整性的比如用户表里出现了phone这个字段可以在当前字段上创建唯一索引这样就可以避免重复的手机号这是数据库层面的约束比java层面的约束要更加可靠6. 优点查询性能 负载低 接口快体验好 数据完整性当然索引的使用也会有优缺点:先说优点:1、查询性能出现大量提升尤其是大数据量高频查询的场景索引是解决查询慢的核心手段2、降低数据库的整体负载减少全表扫描的次数降低CPU和IO的消耗3、优化业务体验查询速度快了接口的响应时间也会缩短提升用户的体验度4、强化数据完整性通过唯一的特性实现业务规则的约束减少脏数据7. 缺点: 大开销 占空间 索引多不行1、增加写入操作的开销数据在写入的时候不仅要操作数据还要维护索引的结构会出现页分裂以及页合并的操作这叫做索引的维护性可能会降低操作的效率2、占用额外的空间索引并不是越多越好索引是独立于数据文件的当一种表中索引越多的时候那么存储空间也会增大很多3、过渡的索引会导致优化器选择索引的开销增加sql语句在执行的时候要经过分析器优化器执行器优化器在执行的时候会选择合适的索引当表的索引更多的时候会增加索引选择的开销索引失效模糊在左 字段有表达式 类型不匹配4、部分场景下索引可能会失效那么效果可能不如全表扫描比如模糊查询%在左侧比如索引字段出现了表达式-- 对索引字段 age 做运算1索引失效触发全表扫描SELECT*FROMuserWHEREage126;-- 等价逻辑但写法不同结果一样索引失效SELECT*FROMuserWHEREage26-1;-- 这个不会失效重点区分比如字段类型不匹配等情况都会导致索引失效此时索引不仅没有提高效率反而会导致性能降低因此需要分析执行计划来选择合适的索引甚至可以使用forceindex来强制指定索引二mysql的索引的数据结构为什么使用B树MyISAM 和 InoDB 表文件表名.frm 是表结构两种存储引擎都会有表数据 存在 安装 的 data 目录下test_myisam表如下test_myisam.MYD 这是数据 MY 就是 MyISAMD是数据test_myisam.MYI 就是索引test_innodb 表如下test_innodb.ibd 数据和索引 都是放在这个文件的叶子节点MyISAM 存的是 磁盘文件地址。 MyISAM 就是 非聚集索引innodb存的是这个索引列 所在行的 其他所有列的记录。聚集索引聚集索引 不用回表速度快1. 解释索引提高查询的效率 全固态贵索引存在的意义是为了加快数据的访问提高查询的效率在mysql中无论是真实的行记录还是索引的数据都是存储在磁盘中那么在进行数据读取的时候必然会涉及到IO的问题我们所谓的提高查询效率其实本质是提高IO的性能IO的问题其实是硬件层面的问题比如很多同学会把个人PC上的机械硬盘换成固态硬盘也是为了加快数据的访问在实际的生产环境中你很难将所有的机械硬盘都换成固态的成本太高了那么就要求我们在进行软件设计的时候要想办法提高IO的性能2. 提高IO的性能维度次数 量那么应该如何提高呢?有两个维度:1、减少IO的次数2、减少IO的量其实我们之前在进行SQL查询的时候为什么不让用select * 这样的操作本质上来说也是减少IO的量所以我们需要把这个当做索引的设计原则3. 磁盘预读4K内存更贵 分而治之 innodb16Kmysql的表的数据量理论上来说是可以无限制的正常情况下在公司的业务系统中可能是百万甚至千万级别但是一旦达到千万级别数据的查询会非常慢而且对应的数据文件会非常大那么就会出现一个问题很难一次性将全量的数据文件都从磁盘加载到内存中内存是非常宝贵的资源既然无法一次性全量读取那么可以考虑分块读取的方式这里使用的是分而治之的设计思想当确定好数据分块读取之后就要确定块大小为多少合适?考虑到操作系统中有一个基础的概念叫做磁盘预读用大白话解释就是内存跟磁盘在进行交互的时候有一个最基本的逻辑单位称之为页也叫做datapage页的大小跟操作系统相关一般情况下是4KB或者8KB我们在进行数据读取的时候可以读取页的整数倍在mysql的innodb存储引擎中每次读取的是16KB的数据页的大小确定好之后下面开始考虑每个页里的数据格式是什么样子此时要跟我们的sq语句做关联比如:select * from table where id 10;这条SQL语句的查询其实就是根据id来获取整行的记录可以抽象一下把id当做key值value就是整行的记录也就是说可以抽象成K-V格式的数据4. 读取3条件filenameoffsetlength 2个文件此处有一个思考点:抛开mysql不说只是想从文件系统中找到自己想要的行数据的话应该如何处理?需要知道文件名称打开文件之后要知道从哪个位置开始读读到哪个位置结束当知道这样的信息之后就可以读取数据了总结下就是filenameoffsetlength这三个字段是索引的信息需要一个单独的文件来存储还需要一个额外的数据文件来存储数据在读取数据的时候需要先读取索引文件然后根据索引文件中的数据再去对应的数据文件中读取真实的行记录5. innodb存储引擎作为默认2次IO 效率不高假设一个文件是一次IO操作的话此时至少需要2次I0效率不高所以要考虑如何减少IO的次数此时就要考虑把索引跟数据放在一起了像mysql的myisam存储引擎数据文件有两个:myd和myi,是分开存储的而innodb只有一个ibd文件索引数据和真实数据是存储在一起的可以减少IO的次数这也是为什么innodb存储引擎作为默认的存储引擎的原因mysql 5.1 之前是 MyISAM6. 存哈希表 二叉树 BST二叉查找AVL自平衡) 红黑 B树要使用什么样的数据结构在存储K-V格式的数据呢?支持的数据结构有很多比如哈希表二叉树BSTAVL树红黑树都可以支持K-V格式的数据存储为什么选择B树BST树,英文全称:Binary Search Tree,被称为二叉查找树或二叉搜索树AVL树是计算机科学中由G. M. Adelson-Velsky和E. M. Landis于1962年发明的自平衡二叉查找树AVL树是最先发明的自平衡二叉查找树7. hash表排除范围查询哈希表是无序散列表当进行范围查询的时候效率极低需要挨个对比性能会有问题同时哈希表需要性能优良的哈希算法否则会出现大量的哈希冲突问题8. 二叉树 二叉查找树 排除树高而二叉树BSTAVL红黑树有一个共同的特性叫做至多只有2个分支想要存储更多的数据的话会导致树的高度变高同样会增加IO的次数所以所有具备二叉特性的树都不符合要求9. B树 都会存储实际的数据树高 排除这个时候需要考虑B-树B-树的叶子节点和非叶子节点都会存储实际的数据那么会导致数据占用了大量的存储空间想要存储更多数据的时候也会导致数据的高度变高10. B树 中间不存储数据 双向链表 能存储更多因此引入了B树在B树中叶子节点存储数据非叶子节点不存储数据同时叶子节点中有双向链表可以更好支持范围的查询在相同树高的情况下B树比B-树能存储更多的数据所以mysql最终选择了B树11. 3-4层千万 key的字段占用的空间要越小越好一般情况下3-4层的B树足以支撑千万级别的数据量存储同时在选择索引列的时候key的字段占用的空间要越小越好这样可以在相同树高的情况下存储更多的数据三什么是聚簇索引什么是非聚簇索引?1. B树 叶子节点 行记录 存一份mysql的索引底层的数据结构是B树在存储数据的时候叶子节点中存储的是真实的行记录当时一张表中可能存在多个索引每一个索引都是独立的一棵树那么不可能所有的B树的叶子节点中都存储真实的行记录这样的话就会导致增加一个索引数据的存储量就增加一倍假设一个表的存储是2G那么增加一个索引变成4G,再增加一个索引变成6G这种设计明显是不合理的所以无论一张表中有多少个索引数据只存储一份那么其他索引的叶子节点中存储什么呢?2. 数据插入 必绑索引 6字节的rowidmysql的表在进行数据插入的时候必须要跟某一个索引列绑定在一起:如果表中有主键那么跟主键绑定在一起如果表中没有主键那么会选择第一个非空的唯一键绑定在一起如果表中既没有主键也没有唯一键那么系统会生成一个6字节的rowid来进行存储3.聚簇索引跟数据绑定。其他聚簇索引的值跟数据绑定存储的索引叫做聚簇索引没有跟数据绑定存储的索引叫做非聚簇索引因此在innodb存储引擎中只有一个聚簇索引其他索引的 叶子节点中 存储的是聚簇索引的值四什么是回表?1. 回表无法非聚簇查到全部字段回表是innodb存储引擎中使用非聚簇索引查询数据时因为无法直接从 非聚簇索引中获取到 要查询的全部字段需要再次去 聚簇索引中 查找数据的过程简单描述的话就是先通过非聚簇索引拿到主键值然后再根据主键值去查找整行数据的过程在innodb存储引擎中分为聚簇索引和非聚簇索引聚簇索引的叶子节点存储的是整行记录非聚簇索引的叶子节点存储的是聚簇索引的值因此在进行非聚簇索引不包含的字段查询中需要去聚簇索引中进行二次查找2. 回表举例比如有一张表表中有id,name,age,gender,address等字段id是主键name是普通索引或者二级索引有下sql句:select *from table where name ‘lian’;查找过程如下:1、先根据name的值去name的B树上进行数据检索返回的是id的值2、再根据id的值去id的B树上检索结果返回整行的记录这个过程我们称之为回表3. 回表造成磁盘IO 数据库的负载回表的本质是进行2次B树的检索所以效率上肯定会有影响:1、增加磁盘IO2、增加数据库的负载在进行数据查询的时候如果返回的主键过多那么查找B树的次数也会过多会增加数据库的负载情况因此在实际的场景中我们要尽量的避免回表操作有如下方案:4. 返回的主键过多 回表次数多“返回的主键过多” 本质是 “需要回表的记录数过多”会导致大量的 B 树检索操作进而显著增加数据库负载。首先明确回表的触发条件当查询的字段不全在索引中即不是 “覆盖索引”MySQL 会先通过二级索引非主键索引的 B 树找到主键值再通过主键索引的 B 树聚簇索引找到完整的行数据。整个过程的核心是第 1 次 B 树检索二级索引 → 拿到主键值1 个 / 多个第 2 次 B 树检索主键索引 → 拿到完整行数据每一个主键值都要做一次主键索引的 B 树检索。理解 “返回的主键过多会增加数据库负载”这里的 “返回的主键过多”本质是 “通过二级索引筛选后得到的主键数量多”比如查询条件太宽泛比如where age 18命中了 10 万条记录拿到了 10 万个主键值核心影响体现在 3 个层面1B 树检索次数呈 “倍数级” 增加2数据库资源被大量占用5. 避免回表因此在实际的场景中我们要尽量的避免回表操作有如下方案:1、使用索引覆盖查询的字段在索引中select id,name from table where name ‘lian’;2、尽量直接使用主键查询3、查询时 尽量少的返回字段信息五什么是索引覆盖?1. 都能在某个索引中找到索引覆盖也叫做覆盖索引是指在查询语句中需要的所有字段都能在某个索引中找到不需要回表查询聚簇索引的过程简单描述就是一次索引查询就能够拿到所有需要的数据不需要二次查找聚簇索引2. 消除回表 两次B树索引覆盖的核心价值是消除回表带来的两次B树的查询 带来的IO开销在大数据量高频查询的场景中能够让查询的效率呈现 倍数级的提升也是解决回表问题的常用手段3. 回表举例比如在我们之前的业务系统中有一个订单表:表中有如下字段:order_id,user_id,craete_time,status,amount主键是order_id普通索引是user_id现在有如下的SQL语句需要进行查询:select order id, create time from order where user id 12345;当前SQL语句的查找过程是:先根据user id的数据值去user id的B树中检索数据然后返回order id然后再根据order id的值获取到create time的字段信息这个查找过程中其实就涉及到了回表的操作遍历了2棵B树导致性能降低对于这样的情况我新增了一个组合索引索引中包含的字段信息有user_id和create_time那么此时查找的过程变为直接根据userid的值去userid的B树中进行数据的检索可以直接获取到我们想要的所有的字段信息不需要再次进行聚簇索引的查询从而提高查询的效率-- 删除无效单列索引创建组合覆盖索引 CREATE INDEX idx_userid_createtime ON order(user_id, create_time);单独新增create_time单列索引无效该索引是独立 B 树仅按create_time排序存储数据SQL 过滤条件是user_idMySQL不会走create_time索引依旧走原有user_id索引依然需要回表查询聚簇索引性能零提升。六什么是最左匹配原则?1. 必须要从索引的最左侧开始进行匹配最左匹配原则是mysql的组合索引生效的核心规则具体是指组合索引在查询过程中查询条件必须要从索引的最左侧开始进行匹配且中间不能跳过字段如果查询条件不满足最左前缀那么索引将无法使用导致索引失效2. 先按照最左列字段进行排序然后再按照次字段这个原则的底层原因是因为组合索引的B树的存储结构我们都知道在实际的数据存储中需要遵循有序性也就是说在创建索引的时候必须要先按照最左列字段进行排序然后再按照次字段进行排序以此类推那么在查询的时候也必须要遵循这个有序性的要求就和 购物网 上写地址一样3. 举例省市区索引 查询区 不能跳过条件 市比如我们之前的业务系统中有一张表叫做user表中有nameagegendercity等字段信息我们创建了一个组合索引包含的agegendercity三个字段selectfromuserwhereage10andgendermaleandcitybeijing;这条语句可以用到组合索引因为查询的条件严格的遵循组合索引的顺序要求所以可以直接使用同样的如果变换条件的顺序的话也可以使用到selectfromuserwherecitybeijingandage10andgendermale;这条语句也会用到因为变换where条件的顺序之后对于最终的查询结果没有影响所以mysql的优化器会帮助我们调整SQL语句条件的顺序来满足最左匹配原则select * from user where age 10 and gender ‘male’;这条SQL语句虽然没有city字段但是前面的2个字段符合最左匹配的原则因此也会使用到只不过只会使用组合索引的前两个字段select * from user where age 10;这条SQL语句虽然没有gender和city字段但是第一个条件是age字段因此也会用到只不过使用的是组合索引的age字段而已4. 举例 中间是in 或 范围 后面的索引用不到select * from user where age 10 and gender in (male,famale) //因为还可能 未知性别里面 也有 city 北京 and city beijing;这条SQL语句中age是等值查询gender是范围查询他们前面两个字段是符合最左匹配原则的所以可以用到前两个字段但是city字段就无法直接根据有序性来定位数据了所以city字段用不到select*fromuserwhereage10andgendermaleandcitybeijing;这条SQL语句只能使用组合索引的age字段gender和city用不到因此age10是范围查询无法根据有序性直接定位后面的字段数据所以只会用到第一列5. 设计原则频率高 等值 基数高因此我们在设计组合索引的时候需要注意以下几点:1、将查询频率较高的字段放在组合索引的最左侧2、将等值查询的字段放到最左侧范围查询放到右侧3、将基数高的字段放在左侧唯一值 越高的字段放在最左侧。重复值越高的比如sex放在最右侧可以更加有效的 过滤 每一个数据6. 表都是索引 打破 最左匹配原则createtableabc(idintprimarykey,aint,bint,cint);altertableabcaddindexidx_abc(a,b,c);showindexfromabc;explainselect*fromabcwherea1andb1andc1;explainselect*fromabcwhereb1andc1;explainselect*fromabcwherec1;//打破最小匹配原则都会用索引都会触发索引覆盖。因为 a b c 联合 索引存的是 idcreate table abc2 (id int primary key, a int, b int,c int,d int);alter table abc2 add index idx_abc2(a,b,c); show index from abc2 explain select * from abc2 where a 1 and b 1 and c 1; explain select * from abc2 where b 1 and c 1; //不能用索引。无法触发联合覆盖 d。 索引是 a b c 索引值为id 。缺少 d 字段需要回表。 回表可能低于全表扫描所以用联合索引 explain select * from abc2 where c 1;七什么是索引下推?1. 服务器层执行 下推到存储引擎层索引下推简称ICP,index condition pushdown,是mysql5.6版本之后引入的查询优化技术核心是当使用二级索引进行数据查询的时候mysql会将原本来服务器层执行的条件筛选过程下推到存储引擎层开始执行在存储引擎中遍历数据的同时完成数据的筛选的过程这个过程可以减少回表的次数从而提高查询的效率2. 连接 分析 优化 执行分析器任何一个sql 最终 执行的时候都会变成 一个 ASM 叫抽象语法树通过树形结构 来进行执行优化器CBOcost 基于成本的优化绝大部分数据库都是这个RBOrule 基于规则的优化存储引擎 原来只会根据 第一个字段 来进行数据的筛选放回到server层执行器 在进行 条件判断。现在所有的条件判断都放到存储引擎中3. 原来非聚簇找聚簇 在server层 条件筛选它的核心价值是减少回表的次数能够实现这个特性的原因是因为mysq!整体架构的分层在二级索引中存储的是索引列的值和聚簇索引的值能够支持条件字段的筛选在没有索引下推之前具体的执行过程如下:1、根据二级索引的最左匹配原则遍历索引拿到所有符合第一个字段值的所有数据以及对应的聚簇索引的值2、根据返回的聚簇索引的值 再去聚簇索引中回表查询查询到字段之后再server层进行条件的筛选当符合第一个索引字段的数据过多的时候会造成大量的回表影响查询的性能比如有一张user表表中有如下字段:id, name, age, gender, addressid是主键联合索引是age,name现在有如下SQL语句:select*fromuserwhereage10//存储引擎层执行andnamelikeli%;//server层执行在没有索引下推之前具体的执行过程如下:1、遍历 组合索引找到所有符合age10的记录拿到对应的主键id(比如有1000个)那么此时并不会处理name的模糊查询遍历 组合索引 就是存储引擎层2、拿到这1000个id值之后去聚簇索引中进行回表查询将所有查询到的结果进行name的模糊匹配这个匹配的过程是在server层执行的并不是在存储引擎层执行最终符合条件的数据可能很少但是造成了大量的IO操拿到这1000个id值 回表会返回到 server 层(交互的时候会产生大量的IO)server层的 执行器 在 模糊 name4. 有了后server层 执行器 只取结果有了索引下推之后具体的执行过程如下:1、在存储引擎中先根据组合索引进行筛选筛选的时候除了会查询age10的数据之外同样会把name的模糊查询给做了也就是说在存储引擎成完成了所有条件的筛选操作2、在server层只需要拿到存储引擎层返回的数据即可比如有10条那么可以根据这10条数据的id值进行回表查询找到所有的数据返回给客户端索引下推默认情况下是开启的这个过程不需要用户来进行干预在进行SQL语句编写的时候稍微注意即可八. 索引有哪些设计原则?0. 基于业务或者需求 增删改操作索引的设计是没有什么明确的标准的更多的是基于业务或者需求来合理的进行设计索引虽然可以加快数据的访问但是对于索引的维护或者进行增删改操作的时候会带来比较大的性能损耗因此在设计索引的时候需要注意以下几点:1. 不要超过5个1、索引并不是越多越好千万不要给每一个列都添加索引一般情况下一个表中的索引不要超过5个2. 高频查询的字段2、只给高频查询的字段创建索引低频查询的字段不要创建索引一天 一月查一次的没意义3. 高基数3、优先给高基数的列创建索引(基数字段唯一值/记录总数)一般情况下基数大于80%的列适合创建索引比如name创建 有意义。 比如sex创建无意义4. 字段空间越小越好4、索引的字段占用的空间越小越好在满足业务系统的情况下相同树高的情况下就能存更多的数据5. 不要给允许nul值的列创建索引5、尽量不要给允许nul值的列创建索引null值在数据库中是一个非常特殊的存在判断的时候也会使用is null或者isnotnul的方式来判断nul值太多的话也会导致索引的效率降低6. 非频繁更新6、对于频繁更新的字段不要创建索引可能触发页合并 页分裂7. 尽量使用组合索引7、在业务场景允许的情况下尽量使用组合索引而不是使用多个单列索引组合索引可以更多的触发索引覆盖提高查询的效率8. 遵循最左匹配原则8、在创建组合索引的时候一定要遵循最左匹配原则高频字段在左等值字段在左基数高的字段在左9. 主键索引 尽量自增9、创建索引的时候如果有主键索引那么尽量自增如果不自增的话在插入数据的时候会触发页分裂导致索引的效率极低先 删索引 在迁移数据 在插入索引额外的注意点:大家如果在公司中做过数据迁移的话会发现先删除索引再迁移数据然后最后构建索引的方式比边迁移数据边构建索引要快的多这其实就是索引的维护带来的效率降低九什么情况下会导致索引失效?表达式函数操作模糊在左边1. 索引失效结构性 成本性索引失效指的是mysql的表中存在索引但是执行具体的SQL语句的过程中并没有利用到索引的情况索引失效的本质原因有2个:1、结构性原因:对索引字段的操作破坏了B树的有序性导致mysql无法根据B树直接定位到数据只能放弃索引进行全表扫描2、成本性原因:mysql的架构中存在一个组件叫做优化器当执行一条SQL语句的过程中优化器会基于一些规则来进行优化使用效率最高的方式来执行当mysql的优化器认为走索引比全表扫描效率更低的时候就会放弃索引执行全表扫描的操作这种优化大概率是正确的但是也可能存在特殊情况所以在mysql中提供了一种强制索引的方式force index1、包含表达式或者函数操作索引失效的具体场景:1、索引字段中包含表达式或者函数操作(date(),sub()),,)B树存储的是索引字段的原始数据比如有一个字段叫做Create_time,原始值是2025-11-1101:00:00并且按照原始值进行排序操作如果对字段进行了函数操作使用date函数那么最终得到的值是2025-11-11B树中并 没有新值的有序序列那么无法匹配到对应的值就会导致索引失效2、索引字段发生隐式转换2、索引字段发生隐式转换mysql中对字段会进行隐式的转换这个操作不需要用户来触发当有了隐式转换之后那么会导致字段的类型不匹配在B树的原始值中进行匹配的时候也无法找到对应的结果所以会导致索引失效比如使用varchar类型来存储phone我们使用数字进行查询的时候mysql内部会进行隐式转换操作相当.于做了函数操作破坏了有序性进行查询的时候使用的是 等于 比如186就会失效使用 ‘186’带引号 不失效3、最左边是% 模糊匹配3、索引字段中出现模糊匹配的时候导致索引失效比如我们使用like操作如果ike后面的字段最左边是%的话那么就意味着要进行全部字段的遍历没办法根据索引来进行筛选自然就会导致索引失效4、违反组合索引的最左匹配原则4、违反组合索引的最左匹配原则创建组合索引之后一定要按照从左向右的方式进行匹配如果中间跳过了字段或者最左边的条件没有匹配上字段那么就意味着这个组合索引无法使用索引失效5、用or链接非索引字段5、用or链接 非索引字段当需要进行表关联操作的时候如果or的一侧使用了索引另外一侧没有使用索引那么会导致字段匹配不上无法高效的进行查询当然这块跟join的实现方式也有关系后续有时间我们详细讲解join的实现原理在 name ‘zhangsan’ OR age 11 这个查询中即使 name 是索引字段整个查询也会导致 name 索引失效最终走全表扫描。OR 连接的多个条件必须全部能利用索引优化器才会选择走索引只要有一个条件无法利用索引比如 age 无索引优化器会认为即使走 name 索引查到了一部分数据还需要再扫描全表找 age 11 的数据两次操作的成本远高于直接全表扫描一次因此会放弃使用所有索引直接走全表扫描。6、低基数 字段给低基数字段创建索引导致索引失效比如有一个字段叫做gender只有男女两个值那么就算使用上索引也无法高效的进行数据筛选那么mysql就会认为全表扫描效率更高再比如常用status012几个值此时也无法进行高效的数据筛选也会导致索引失效7、表的数据量比较小如果一张表中只有10条记录那么在进行SQL查询的时候mysq认为全表扫描的效率会更高此时也会放弃使用索引8、查询的结果集过大如果查询的表的结果集大于几万条那么就需要进行多次回表操作之后才能得到需要的字段此时mysql的server可能会认为全表扫描的效率要远远的高于回表的效率也会选择放弃索引此处的返回结果的条数没有任何官方文档的说明依据经验的话只要返回的数据不超过总记录的30%一般不会导致索引失效9、mysql的优化器失效mysq的优化器在执行的时候是根据某些统计数据来进行的推测如果你进行了大量的增删改操作但是表的统计信息没有变化的话也会导致索引失效因为根据历史的数据统计不需要使用索引此时就需要通过analyse table tablename 来更新统计的信息先理解MySQL 优化器是 “靠数据决策” 的不是 “靠规则”MySQL 的查询优化器Optimizer的核心目标是选择成本最低的执行方案比如走索引还是全表扫描。但它不是 “死记规则”比如 “有索引就必须用”而是基于「表的统计信息」做成本估算最终选 “看起来更划算” 的方案。关键概念什么是 “表的统计信息”MySQL 会为每个表维护一套统计数据核心包括表的总行数rows索引字段的基数Cardinality即字段唯一值的数量比如 name 字段有 1000 个不同值基数就是 1000数据页的分布、字段的空值比例等。优化器会用这些数据计算走索引的成本需要读取多少个索引页 回表读取多少个数据页全表扫描的成本需要读取多少个数据页。如果优化器计算后认为 “全表扫描成本更低”即使字段有索引也会主动放弃索引也就是 “索引失效”。总之表达式函数 %左 or 隐式转换 违反最左匹配 低基 量小 结果大 优化器十innodb存储引擎和myisam存储引擎区别innodb存储引擎和myisam存储引擎是mysql中最经典的两个存储引擎他们之间的核心差异在于设计的定位不同:1. innodb 事务 高并发 行级锁 聚簇索引 redolog undologinnodb存储引擎是mysql5.5版本之后的默认存储引擎面向事务处理高并发写操作的业务场景支持事务行级锁通过聚簇索引的方式来组织索引并且通过redologundolog日志来保证数据的一致性和性能2. myisam只读 统计报表 日志 读性能高myisam是最早期的默认存储引擎面向只读统计报表日志存储的业务场景不支持行锁和事务优势是读性能高3. Redo Log重做日志Undo Log回滚日志Redo Log重做日志是 MySQL InnoDB 存储引擎的崩溃恢复日志记录数据页的物理修改比如 “哪个数据页的哪个位置改了什么值”保证事务提交后数据不丢失崩溃安全。事务提交时会先写 Redo Log刷盘再异步更新磁盘数据提升写入性能。Undo Log回滚日志是 InnoDB 实现事务回滚和MVCC多版本并发控制 的核心记录数据修改前的原始版本逻辑日志比如 “把 id1 的 name 从 zhangsan 改回 lisi”。事务回滚时通过 Undo Log 恢复数据到修改前状态读操作时通过 Undo Log 构建历史版本实现读已提交、可重复读等隔离级别。总结Redo Log 保障数据持久性崩溃后恢复已提交事务是物理日志Undo Log 保障事务原子性 / 隔离性回滚、MVCC是逻辑日志。1、从事务的维度来分析: 事务 日志 外键特性innodbmyisam事务支持支持事务有事务的隔离级别不支持事务所有的操作都是原子操作事务日志innodb 中有 redologundolog 事务日志没有事务日志外键支持支持外键不支持外键2、从锁的维度来分析: 类型 触发 并发控制特性innodbmyisam锁类型支持行级锁也支持表级锁只支持表级锁锁的触发行级锁的触发是基于索引的没有索引的话就是表级锁任何操作都会触发表级锁并发控制支持 MVCC读写可以互不阻塞读阻塞写写阻塞读3、从索引的维度来分析: 类型 存储 回表 主键特性innodbmyisam索引的类型有聚簇索引也有非聚簇索引只有非聚簇索引索引存储聚簇索引的叶子节点存储整行记录非聚簇索引存储聚簇索引的值所有的索引的叶子节点存储的都是数据的物理行号回表使用二级索引的时候会触发回表没有回表直接根据行号读取数据偏移量主键要求必须有主键如果不设置的话会生成默认的 6 字节的 rowid 当做主键没有主键的要求主键索引也是普通索引使用场景不同的存储引擎有不同的应用场景:innodb:电商金融支付等场景中需要innodb存储引擎因为要支持事务、锁等相关的操作保证数据的一致性和高并发myisam:日志报表只读的场景适合使用myisam原视频【精准突击2026年金三银四Java面试题程序员跳槽涨薪或升职加薪首选大厂offer拿到手软【码士集团-马士兵】】https://www.bilibili.com/video/BV1bZiuBwEaP?p4vd_source2c92acaf5656395eb01aba7ca4ef6267
