MySQL常见八股：索引

MySQL索引的最左前缀匹配原则是什么一句话概括当MySQL在使用联合索引时查询条件必须从索引的最左列开始匹配。这是因为联合索引在B树中的排列方式是从左到右的顺序。比如联合索引(first_namelast_nameage会先按 first_name 排序first_name 相同再按last_name 排序last_name 相同再按 age 排序。MySQL 查找时会优先用first_name作为匹配依据然后依次用last_name和 age。跳过最左侧字段后面的列在B树中是无序的压根没法利用索引快速定位。特例范围查询当范围查询是,这种会停止匹配wherea1andb2andc3;只有a能用上联合索引因为a经过查询筛选后不同a值之间的b和c是无序的。无法走索引。相反当遇到,,前缀 like xx%时不会停止匹配。因为他们之中包含等值判断脏读/不可重复读/幻读分别代表什么意思这三个都是并发事务带来的数据一致性问题严重程度递减脏读指数据库中一个事务查询了一个还没提交的数据万一那个数据回滚了读到的数据不存在不可重复读指一个事务查询两次同一行数据两次获得的数据不一样。因为中间有别是事务修改并提交了这行数据。强调数据的内容改变幻读指一个事务执行两次同样的范围查询返回的数据行数不一样。因为中间有别是事务执行了插入或删除操作。强调数据的行数变化。不同隔离级别下会出现的问题安全程度读未提交读已提交可重复读串行化性能依次递减InnoDB存储引擎默认的就是可重复读。如何解决的脏读MVCC天然解决读的是快照看不到未提交的数据不可重复读在可重复读隔离级别下ReadView在事务一开始就固定了后续复查读取的是同一个ReadView。幻读快照读走MVCC看不到新插入行当前读用间隙锁所著范围别的事务插不进来。当快照读和当前都混用时还会出现幻读。MySQL的存储引擎有哪些他们之间有什么区别MySQL的存储引擎是可插拔的不同引擎辅助数据的存储与读取。实际场景中95%都是使用的InnoDB面试主要讲InnoDB与MyISAM进行区分就行了MySQL8.4版本一共提供了10个引擎常见的有这几个1InnoDBMySQL5.5 之后的默认引擎支持事务、行级锁、外键MVCC也有适合高并发的OLTP场景。数据按聚簇索引组织主键查询贼快。2MyISAM老版本的默认引擎不支持事务只有表级锁但读性能不错。适合那种写少读多、对一致性要求不高的场景比如早年的一些报表系统。3MEMORY数据全放内存里速度快但MySQL重启数据就没了。一般拿来做时表或者会话级缓存。4Archive专门存归档数据的只支持INSERT 和 SELECT不支持索引I但压缩率高。日志归档、历史订单这种场景用得上。5NDBMySQLCluster用的引擎支持分布式和高可用数据自动分片适合电信级别的大规模集群。MySQL的覆盖索引覆盖索引指的是查询内容包含在了二级索引内部查询时直接从索引里拿数据无需再次执行回表操作。本质是索引中有的数据直接拿减少回表操作。优点减少I/O操作查询直接从索引中拿数据避免访问主键索引的数据页。查询更快索引页小结构紧凑缓存命中率更高减少内存占用Buffer Poll只需加载索引页不用加载更大的数据页设计覆盖索引的哲学主要分四点来设计高频查询优先分析业务哪些查询频率高针对这些设计覆盖索引权衡读写比列覆盖索引不是越多越好。索引列越多索引越大写入维护成本高。利用联合索引把where条件的列与select的列创建为一个联合索引。例如select a,b from t where c1,可以键c,a,b索引覆盖索引和索引下推的区别覆盖索引是完全不回表索引里有查询需要的所有数据。索引下推是减少回表次数把过滤条件下推到引擎层提前过滤但最终还是要回表拿完整数据。两种解决的问题不一样索引类型有哪些MySQL索引可以从三个维度来分类数据结构存储方式索引性质。我们先从数据结构来切入重点讲解B树然后展开讲解聚簇索引和非聚簇索引的区别。数据结构B树索引叶子节点存储数据叶子节点通过链表串起来保证了快速定位单条记录和高效做出范围扫描。哈希索引通过哈希函数直接算出数据位置等值查询O1不支持范围查询和排序。全文索引空间索引存储方式聚簇索引又叫主键索引叶子节点世界存放完整的行数据数据按主键顺序物理存储一张表只能由一个主键索引非聚簇索引也叫二级索引叶子节点只存储索引字段值和主键值。查完二级索引还得拿着主键取主键索引获取具体数据。索引性质1主键索引唯一且非空每张表只能有一个。InnoDB里主键索引就是聚簇索引。2唯一索引保证列值不重复但允许有NULL可以有多个NULL。3普通索引没有唯一约束纯粹为了加速查询。4联合索引多列组合成一个索引遵循最左前缀原则列顺序很重要。5全文索引文本搜索用。6空间索引GIS数据用。衍生B树厉害在哪数据库查询由两大类构成等职查询和范围查询。哈希索引等值查询速度快但是无法进行范围查询。B树两个都行并且叶子节点用双向链表连接范围扫描的时候顺着链表走不用回溯。另外,B树特点是树矮一个三层的B树就能存储2000万左右的数据。查询一条数据最多3次I/O。索引下推索引下推是MySQL 5.6之后引入的技术。核心思路是把部分查询条件从Service层下推到存储引擎层在引擎层就把不符合查询条件的数据过滤不用在将这些数据进行回表操作。作用是减少回表操作提升查询效率。没有索引下推的流程是引擎层用索引定位数据返回主键给Service层Service层回表拿到完整数据再用剩余条件过滤。有了索引下推引擎层能直接用索引里的列来过滤不符合条件的不会回表减少了I/O如何判断索引是否生效在SQL语句的前面加上EXPLAIN如果显示Usingindexcondition说明用上了索引下推。EXPLAINSELECT*FROMpeopleWHEREzipcode95054ANDlastnameLIKE%etrunia%;Extra 显示Usingindex condition就表示 lastname 条件被下推到引擎层了。关于联合索引覆盖索引索引下推的区分联合索引是基础有了联合索引才有覆盖索引和索引下推。索引下推和覆盖索引是技术。这两个优化都能减少I/O但原理不同特性索引下推覆盖索引解决的问题减少回表次数完全避免回表是否回表需要回表但次数少不需要回表EXPLAIN 标识Using index conditionUsing index适用场景select * 且有额外过滤条件select 的列都在索引里两者可以同时存在吗不能。如果走了覆盖索引压根不回表索引下推就没有用武之地了。InnoDB存储引擎中聚簇索引和非聚簇索引有什么区别在 InnoDB 引擎中聚簇索引的叶子节点直接存储完整的数据行一张表只能有一个聚簇索引I默认就是主键索引。非聚簇索引的叶子节点只存储索引列的值和主键要拿到完整数据得先查主键再回表一张表可以有多个非聚簇索引。核心区别就是叶子节点存的东西不一样聚簇索引存完整数据非聚簇索引存主键值。这导致聚簇索引查询能一步到位拿到数据非聚簇索引可能要多一次回表操作。MySQL中使用索引一定有效吗如何排查索引效果索引不一定生效。建了索引不低啊表查询会用用来索引不代表查询就快。MySQL最终是否走索引靠的是优化器的成本计算。优化器会评估走索引和全表扫描各自的I/O成本和cpu成本。优势全表扫描确实成本低比如表就几百行数据走索引不如直接扫有时统计信息不准导致优化器算错账。用EXPLAIN命令来排查索引。在SQL语句前面加上EXPLAIN就能看到MySQL选择的执行计划重点看type访问类型all是全表扫描range是范围扫描ref是等值匹配index是全索引扫描。key实际用的索引名称NULL就是没用上索引rows预估扫描行数这个数字越大说明查询代价越高。索引失效常见场景基本原因分为两类1.查询条件导致索引树的快速查找能力用不上 2.优化器觉得不划算1联合索引不符合最左前缀假设有个联合索引idx_name_age_id查询写成WHERE age 10 AND id 1跳过了 name字段最左缀匹配失败索引树的有序性利用不上退化成全索引扫描甚至全表扫描。2索引列上做了运算WHEREid38这种写法MySQL得把每行的id 拿出来算一遍没法直接在索引|树上定位只能全录扫描。3索引列上用了函数WHERELOWER(name‘cong’对索引列套了函数索引树里存的是原始值函数处理后的值根本不在树里还是得全表扫。MySQL8.0 可以建函数索引来解决这个问题。4LIKE左侧带通配符WHEREnameLIKE%cong%这种写法索引是按字符顺序排的左边不确定就没法定位起始位置只能全扫。LIKE’cong%是可以走索引的。5OR连接了非索引字段WHEREnamecong’ORage 18如果age没索引lMySQL没法通过索引l快速过滤 age 条件整个查询可能退化成全表扫描。6隐式类型转换name是Varchar 类型查询写成 WHERE name 1MySQL会做隐式转换相当于 WHERE CAST(nameAsignedint1索引列上套了函数索引失效。联表查询时两边字段编码不一致也有同样的问题。7优化器认为全表扫描更划算同一个索引查热点数据可能走全表查冷门数据走索引。比如订单表按商品ID 查热门商品占了80%的订单走索引反而要回表几十万次不如直接全表扫描。8ORDER BY没配合好ORDER BY 后面的字段不是主键也不是覆盖索引MySQL 可能选择全表扫描再排序而不是走索引。