Q07 - MySQL索引与优化
一、B+树与索引原理
为什么MySQL索引使用B+树而不是B树?
难度:★★★★★
- B+树非叶子节点只存键值不存数据,单个节点能容纳更多键值,树更矮(通常3-4层),磁盘IO次数更少。
- B+树所有数据都在叶子节点,查询性能稳定,每次查询都要走到叶子节点,时间复杂度恒为O(logN)。
- B+树叶子节点通过双向链表连接,支持高效的范围查询和排序,B树需要中序遍历。
- B树每个节点都可能存储数据,查询性能不稳定(最好O(1),最差O(logN)),且范围查询效率低。
为什么MySQL索引不用Hash索引?
难度:★★★★
- Hash索引只支持等值查询(=、IN),不支持范围查询(>、<、BETWEEN)和排序(ORDER BY)。
- Hash索引无法利用索引排序,也不支持最左前缀匹配规则。
- Hash冲突多时性能退化,且无法避免全表扫描的问题。
- MySQL的Memory引擎支持Hash索引,InnoDB的自适应哈希索引(AHI)是内部自动优化,非用户可选。
为什么MySQL索引不用红黑树?
难度:★★★
- 红黑树是二叉树,每个节点最多两个子节点,在数据量大时树的高度远大于B+树。
- 树越高意味着磁盘IO次数越多(每个节点可能对应一次磁盘IO),查询效率差。
- 红黑树的平衡操作(旋转)成本高,不适合磁盘存储场景。
- B+树通过多路分支大幅降低树高,一个千万级表的B+树通常只需3-4层。
B+树的结构特点是什么?
难度:★★★
- 非叶子节点只存储索引键值和子节点指针,不存储实际数据,使得每个节点能容纳更多索引项。
- 所有数据记录都存储在叶子节点,叶子节点之间通过双向链表连接。
- 一个B+树节点通常对应一个磁盘页(InnoDB默认16KB),保证一次IO读取一个完整节点。
- InnoDB中每个页的大小通过innodb_page_size设置,默认16KB。
InnoDB一棵B+树大概能存多少行数据?
难度:★★★★
- 假设非叶子节点每个索引项占12字节(8字节主键+4字节指针),16KB页可存约1365个索引项。
- 假设叶子节点每行数据约1KB,16KB页可存约16行。
- 三层B+树:1365 * 1365 * 16 ≈ 2980万行;两层:1365 * 16 ≈ 2.1万行。
- 实际行数取决于行大小,以上是估算值,说明B+树三层即可支撑千万级数据。
二、聚簇索引与二级索引
什么是聚簇索引?它的特点是什么?
难度:★★★★
- 聚簇索引将索引和数据存储在一起,叶子节点就是完整的数据行(InnoDB的"索引即数据")。
- 一张表只能有一个聚簇索引,InnoDB默认使用主键作为聚簇索引。
- 如果没有定义主键,InnoDB选择第一个非空唯一索引;如果没有,则自动生成一个6字节的隐藏主键(ROW_ID)。
- 聚簇索引的叶子节点按主键顺序物理存储,主键范围查询和排序效率高。
什么是二级索引(非聚簇索引)?
难度:★★★
- 二级索引的叶子节点存储索引列的值和对应的主键值,不存储完整数据行。
- 通过二级索引查找数据时,先在二级索引树中找到主键值,再回到聚簇索引树查找完整数据(回表)。
- 一张表可以有多个二级索引,每次建索引都会创建一棵独立的B+树。
- 二级索引占用额外存储空间,且在INSERT/UPDATE/DELETE时需要同步维护。
什么是回表?如何减少回表次数?
难度:★★★★
- 回表:通过二级索引找到主键值后,再用主键到聚簇索引查找完整数据行,涉及两次索引树查找。
- 回表会增加IO次数,特别是查询大量行时,每次回表可能产生一次随机IO。
- 减少回表的方法:使用覆盖索引(查询列都在索引中)、使用联合索引、避免SELECT *。
- 如果查询只需要索引列和主键,二级索引可以直接返回结果,无需回表。
什么是覆盖索引?
难度:★★★★
- 覆盖索引是指查询所需的所有列都包含在索引中,不需要回表查询聚簇索引。
- EXPLAIN的Extra列显示Using index表示使用了覆盖索引。
- 例如索引(a,b),查询SELECT a,b FROM t WHERE a=1只需扫描索引即可获取结果。
- 覆盖索引是性能优化的常用手段,特别适合查询只涉及少数列的高频场景。
聚簇索引和二级索引在查询时的性能差异?
难度:★★★
- 聚簇索引查询直接获取数据行,无需回表,等值查询和范围查询都高效。
- 二级索引查询需要回表,增加了IO开销,如果结果集大则性能明显下降。
- 主键范围查询利用聚簇索引的物理有序性,顺序IO效率高。
- 二级索引范围查询每条记录都可能回表(随机IO),性能不如聚簇索引。
三、最左前缀与索引下推
什么是最左前缀原则?
难度:★★★★★
- 联合索引(a,b,c)相当于创建了(a)、(a,b)、(a,b,c)三个索引,查找时必须从最左列开始匹配。
- 查询条件中必须包含联合索引的最左列,否则无法使用该索引(如只有b=1无法使用索引)。
- 匹配遇到范围查询(>、<、BETWEEN、LIKE前缀)时停止,后续列无法使用索引。
- WHERE a=1 AND b>2 AND c=3 中,a和b会走索引,c无法利用索引排序和查找。
索引下推(Index Condition Pushdown,ICP)是什么?
难度:★★★★★
- MySQL 5.6引入的优化,将部分WHERE条件下推到存储引擎层在索引扫描时直接过滤。
- 没有ICP时:存储引擎通过索引找到所有满足最左前缀的主键,逐个回表后再在Server层应用WHERE过滤。
- 有ICP时:存储引擎在索引扫描阶段就根据WHERE条件(涉及索引列的部分)过滤,减少回表次数。
- EXPLAIN的Extra列显示Using index condition表示使用了ICP。
联合索引(a,b,c),WHERE a=1 AND c=3能用到索引吗?
难度:★★★★
- a=1可以用到索引,因为满足最左前缀原则。
- c=3无法在索引中直接查找(跳过了b列),但ICP优化可以在索引扫描时利用c列过滤。
- 即索引定位到a=1的范围后,扫描时检查c列是否等于3,满足条件的才回表。
- 如果没有ICP,需要回表后再在Server层判断c=3,回表次数更多。
联合索引的列顺序如何选择?
难度:★★★★
- 将等值查询的列放在前面,范围查询的列放在后面,最大化索引利用率。
- 将选择性高的列(区分度大)放在前面,可以更快缩小扫描范围。
- 考虑查询频率:频繁查询的列组合优先建索引。
- 如果存在多个查询模式,可能需要建立多个联合索引或调整列顺序做权衡。
ORDER BY能用到索引吗?什么条件下可以?
难度:★★★★
- ORDER BY的列与索引列顺序一致且方向一致(都ASC或都DESC)时,可以利用索引的有序性避免排序。
- 联合索引(a,b)下,WHERE a=1 ORDER BY b可以利用索引,因为a确定后b是有序的。
- ORDER BY a DESC, b ASC(方向不一致)无法完全利用索引排序,MySQL 8.0支持降序索引后可解决。
- EXPLAIN的Extra列没有Using filesort说明ORDER BY使用了索引。
四、索引失效场景
索引失效的常见场景有哪些(列举10种)?
难度:★★★★★
- 1.对索引列使用函数:WHERE YEAR(create_time)=2024 → 改为WHERE create_time>='2024-01-01'。
- 2.对索引列使用表达式:WHERE id+1=10 → 改为WHERE id=9。
- 3.隐式类型转换:varchar列用整型查询 WHERE phone=13800000000 → 改为WHERE phone='13800000000'。
- 4.使用OR连接非索引列:WHERE a=1 OR b=2(b无索引)→ 导致全表扫描。
- 5.使用!=或<>:WHERE status!=0,优化器可能放弃索引选择全表扫描。
- 6.使用NOT IN / NOT EXISTS:在某些场景下优化器选择全表扫描。
- 7.LIKE以通配符开头:WHERE name LIKE '%张' → 索引失效;LIKE '张%'可走索引。
- 8.联合索引未遵循最左前缀:索引(a,b)但只查WHERE b=1。
- 9.IS NULL / IS NOT NULL:取决于数据分布,如果NULL值过多优化器可能放弃索引。
- 10.数据量太小:优化器认为全表扫描代价更低,主动放弃索引。
隐式类型转换导致索引失效的原理?
难度:★★★★
- MySQL在比较不同类型时会进行隐式转换,规则是"将字符串转为数字"。
- 当varchar列与数字比较时,会对varchar列应用CAST函数转换,等价于对索引列使用了函数。
- 例如phone是varchar,WHERE phone=13800000000会被转为WHERE CAST(phone AS SIGNED)=13800000000。
- 索引列被函数包裹后无法利用B+树的有序性,导致索引失效。
LIKE查询什么时候能用索引?
难度:★★★
- LIKE '张%'(前缀匹配):可以使用索引,因为B+树按前缀有序。
- LIKE '张_'(前缀+定长):也可以使用索引,与前缀匹配等效。
- LIKE '%张'(后缀匹配):无法使用索引,因为前缀不确定。
- LIKE '%张%'(包含匹配):无法使用索引,只能全表扫描。可考虑全文索引或ES。
使用!=或<>时索引一定失效吗?
难度:★★★
- 不一定。是否走索引取决于优化器的成本估算。
- 如果不满足条件的数据量很小(如status!=0中0的记录占绝大多数),优化器可能仍然选择索引。
- 反之,如果!=过滤后仍然需要扫描大量数据,优化器会认为全表扫描成本更低。
- 实际应通过EXPLAIN验证,而不是凭经验判断。
OR条件如何优化以使用索引?
难度:★★★★
- 方式一:确保OR两边的列都有索引,MySQL可能使用index_merge优化(合并多个索引结果)。
- 方式二:用UNION ALL改写:SELECT * FROM t WHERE a=1 UNION ALL SELECT * FROM t WHERE b=2。
- 方式三:使用覆盖索引,让OR条件都能在各自索引中完成查询。
- 方式四:如果OR条件涉及同一索引的不同值,可以用IN替代:WHERE a IN (1,2)代替WHERE a=1 OR a=2。
五、EXPLAIN执行计划
EXPLAIN的type字段有哪些重要取值?性能从好到差排序?
难度:★★★★★
- system:表中只有一行数据(const的特例),性能最好。
- const:通过主键或唯一索引精确匹配一行。
- eq_ref:JOIN时被驱动表通过主键/唯一索引匹配,每次匹配一行。
- ref:使用非唯一索引或唯一索引的前缀匹配,可能匹配多行。
- range:索引范围扫描(BETWEEN、>、<、IN等)。
- index:全索引扫描,扫描整棵索引树但不需要回表。
- ALL:全表扫描,性能最差,需要优化。
EXPLAIN的Extra字段有哪些重要取值?
难度:★★★★★
- Using index:覆盖索引,查询所需数据都在索引中,无需回表(最优)。
- Using index condition:使用了索引下推(ICP),在索引扫描阶段过滤。
- Using where:Server层进行WHERE过滤,说明存储引擎返回了多余的行。
- Using filesort:额外排序,未利用索引排序,需要优化。
- Using temporary:使用了临时表(常见于GROUP BY、DISTINCT、UNION),需要关注。
- Using join buffer:JOIN时没有可用索引,使用连接缓冲区,应加索引优化。
EXPLAIN的key和rows字段分别表示什么?
难度:★★★
- key:实际使用的索引名称。如果为NULL说明没有使用索引(全表扫描)。
- key_len:使用的索引字节长度,可用于判断联合索引使用了几个列。
- rows:MySQL估算的需要扫描的行数,是基于统计信息的估算值,不精确。
- rows越小越好,但它是估算值,实际行数可能不同,需结合实际情况分析。
如何通过key_len判断联合索引使用了哪些列?
难度:★★★★
- key_len表示索引使用的字节数,通过计算各列占用的字节数可以推断使用了几个列。
- 计算规则:字段长度 + 是否允许NULL(+1字节)+ 字符集额外开销(如utf8mb4每个字符4字节)。
- 例如联合索引(varchar(20), int),utf8mb4下varchar(20)占用20*4+2+1=83字节。
- 如果key_len=83说明只用到了第一列;如果key_len=83+4+1=88说明用到了两列。
EXPLAIN中的filtered字段含义?
难度:★★★
- filtered表示在存储引擎返回的数据基础上,Server层WHERE条件过滤后剩余行的百分比。
- 例如rows=1000,filtered=10%,表示存储引擎扫描1000行,Server层过滤后剩余约100行。
- filtered越低说明无用数据越多,需要优化索引或查询条件。
- 该值也是估算值,基于统计信息,可能不够准确。
六、慢SQL优化
如何定位慢查询SQL?
难度:★★★★
- 开启慢查询日志:set global slow_query_log=ON; set global long_query_time=1(单位秒)。
- 使用mysqldumpslow工具分析慢查询日志文件,按查询时间或出现次数排序。
- 使用pt-query-digest(Percona工具)做更专业的分析,输出SQL指纹和执行统计。
- 查询information_schema.processlist或使用performance_schema实时监控正在执行的SQL。
pt-query-digest工具如何使用?
难度:★★★
- 基本用法:pt-query-digest slow.log > report.txt,分析慢查询日志并输出报告。
- 报告按总执行时间排序,展示SQL指纹(参数化为通配符的SQL模板)。
- 显示每个SQL的执行次数、总时间、平均时间、锁等待时间、扫描行数等。
- 可以配合--since/--until过滤时间范围,或直接从processlist采集。
慢SQL优化的通用步骤是什么?
难度:★★★★
- 1.通过慢查询日志或监控找到耗时SQL。
- 2.使用EXPLAIN分析执行计划,关注type、key、rows、Extra字段。
- 3.确定是否走了合适的索引,是否存在全表扫描、filesort、临时表等问题。
- 4.优化索引:添加缺失索引、调整联合索引列顺序、使用覆盖索引。
- 5.优化SQL:避免SELECT *、减少子查询改用JOIN、分页优化。
- 6.验证优化效果:再次EXPLAIN确认,在生产灰度验证。
深分页查询如何优化?
难度:★★★★★
- 问题:LIMIT 1000000,10 需要扫描1000010行再丢弃前100万行,效率极低。
- 方案一:延迟关联,先通过子查询/覆盖索引获取主键,再回表查数据:SELECT * FROM t JOIN (SELECT id FROM t LIMIT 1000000,10) tmp ON t.id=tmp.id。
- 方案二:游标分页,WHERE id>上次最大id ORDER BY id LIMIT 10,每次记住上一页的最后一条记录。
- 方案三:业务限制,不允许跳转到很深的页码,只提供上一页/下一页功能。
子查询与JOIN的性能对比?
难度:★★★★
- MySQL 5.6之前子查询优化较差,IN子查询可能被优化为依赖子查询(DEPENDENT SUBQUERY),效率低。
- MySQL 5.6+对子查询做了大量优化(物化、半连接),多数场景性能接近JOIN。
- 通常建议将子查询改写为JOIN,执行计划更可控。
- EXISTS子查询在某些场景下优于IN子查询(特别是子查询结果集大时),取决于具体数据分布。
COUNT查询如何优化?
难度:★★★
- COUNT(*)和COUNT(1)在InnoDB中性能相同,都是统计所有行数。
- InnoDB的COUNT需要遍历二级索引或聚簇索引来计数(因为MVCC),不像MyISAM有精确行数缓存。
- 优化:使用较小的二级索引代替聚簇索引计数(扫描页数更少)。
- 大表建议维护一个计数表或使用Redis缓存,避免频繁COUNT。
如何优化ORDER BY?
难度:★★★★
- 优先让ORDER BY使用索引排序,避免Using filesort。
- 确保ORDER BY的列有索引,且顺序和方向与索引一致。
- 联合索引(a,b)下WHERE a=1 ORDER BY b可利用索引;ORDER BY a,b也可以。
- 如果必须filesort,增大sort_buffer_size和max_length_for_sort_data参数,尽量使用单次排序(一次IO)。
七、分库分表
什么是分库分表?为什么要分库分表?
难度:★★★
- 分库分表是将数据按照一定规则分散到多个数据库或多张表中,解决单库单表的数据量和访问量瓶颈。
- 单表数据量超过千万行时,B+树层级增加、索引维护成本高、查询性能下降。
- 单库连接数、QPS、IO等也有上限,垂直和水平拆分可以突破单机瓶颈。
- 分库解决并发压力(QPS),分表解决数据量压力(存储和查询)。
垂直分库与水平分库的区别?
难度:★★★
- 垂直分库:按业务模块拆分,如将用户、订单、商品分到不同的数据库,每个库独立部署。
- 水平分库:同一个表的数据按规则分散到多个库,如按用户ID取模分布到4个库。
- 垂直分表:将大表按列拆分,热点列和非热点列分开存储(如商品基础信息和详情分离)。
- 水平分表:同一个库中将一张大表拆分为多张结构相同的小表,如order_0、order_1、order_2。
常用的分片策略有哪些?
难度:★★★★
- 取模分片:shard = user_id % N,数据分布均匀,但扩容需要迁移数据。
- Range分片:按ID范围或时间范围分,如1-100万在表1,100万-200万在表2,扩容方便但可能热点。
- 一致性Hash分片:数据映射到Hash环上,扩缩容只影响相邻节点,数据迁移量小。
- 枚举分片:按地域、类型等业务字段映射到指定分片,适合业务特征明显的场景。
分布式主键生成方案有哪些?
难度:★★★★
- UUID:全局唯一,但无序、长度长(36字符),作为主键会导致B+树频繁分裂,索引性能差。
- 数据库自增(步长法):每个分片设置不同的auto_increment_increment和offset,简单但依赖数据库。
- Snowflake雪花算法:64位ID(时间戳+机器ID+序列号),趋势递增、性能高,最常用。
- Redis INCR:利用Redis原子递增生成ID,性能好但依赖Redis可用性。
- 号段模式(Leaf-segment):每次从数据库获取一个ID号段缓存在本地,减少数据库访问。
分库分表后如何处理跨库分页查询?
难度:★★★★★
- 全局排序分页:在各分片上分别执行查询+排序,合并结果后取全局分页,性能差。
- 禁止深分页:限制最大页码,只允许查看前N页。
- 二次查询法:先在各分片取前N条,找到最小值后再各分片查询大于该值的数据,减少数据传输。
- 使用ES宽表:将需要搜索和排序的字段同步到ES,通过ES做分页查询,再回主库取详情。
分库分表后如何处理跨库JOIN?
难度:★★★★★
- 冗余字段:将关联字段冗余到本表中,避免JOIN。如订单表中冗余用户名称。
- 全局表:每个库都存储一份小表(如字典表、配置表),避免跨库JOIN。
- ER分片:将有关联关系的表按相同分片键分布到同一库中,如订单和订单明细按order_id分到同一库。
- 应用层JOIN:在代码中分别查询后手动关联,适合简单的关联场景。
分库分表后如何做数据迁移和扩容?
难度:★★★★
- 成倍扩容法:2个库扩到4个库,每个旧库的数据按新规则分裂到2个新库(取模倍数关系)。
- 双写方案:新数据同时写入新旧库,历史数据通过脚本迁移,迁移完成后切换读取到新库。
- Canal监听binlog:通过Canal监听旧库binlog实时同步到新库,保证数据一致性。
- 灰度切换:先切换部分流量到新库验证,确认无误后全量切换。
ShardingSphere的核心架构和特性?
难度:★★★★
- ShardingSphere包含Sharding-JDBC(客户端方案)、Sharding-Proxy(代理方案)和Sharding-Sidecar(云原生方案,规划中)。
- Sharding-JDBC以JAR包方式嵌入应用,直连数据库,无需额外部署,性能损耗小。
- 支持数据分片、读写分离、分布式事务(XA和BASE)、数据加密、影子库等功能。
- 通过配置分片规则(分片键、分片算法、分片数量)实现透明的分库分表。
八、主从复制
MySQL主从复制的原理是什么?
难度:★★★★★
- 主库将数据变更写入binlog(二进制日志),记录所有DDL和DML操作。
- 从库的IO线程连接主库,请求binlog并写入本地的relay log(中继日志)。
- 从库的SQL线程读取relay log,重放(replay)SQL语句,使从库数据与主库一致。
- 默认是异步复制,主库不等从库确认即返回客户端,存在数据延迟风险。
MySQL主从复制有哪几种模式?
难度:★★★★
- 异步复制(默认):主库写binlog后立即返回,从库异步拉取,主库崩溃可能丢数据。
- 半同步复制:主库写binlog后等待至少一个从库确认收到binlog(未必要执行完)才返回。
- 全同步复制:所有从库都执行完才返回,性能差,实际很少使用。
- MySQL 5.7+增强半同步(After Sync):先写binlog再等从库ACK,再提交事务,比旧版After Commit更安全。
binlog有哪几种格式?区别是什么?
难度:★★★★
- STATEMENT:记录SQL语句,日志量小,但某些函数(NOW()、UUID())在从库重放时可能不一致。
- ROW:记录行级变更(修改前后的数据),数据一致性最好,但日志量大(特别是批量UPDATE)。
- MIXED:默认STATEMENT,遇到不安全的语句自动切换为ROW,是折中方案。
- 推荐使用ROW格式,虽然日志量大但保证主从数据一致性,是生产环境的常用选择。
主从延迟的原因和解决方案?
难度:★★★★★
- 原因:从库单线程重放(MySQL 5.6之前)、主库大事务、从库机器性能差、网络延迟。
- 主库并行写但从库单线程串行重放,导致从库跟不上主库速度。
- 解决方案:开启从库多线程复制(slave_parallel_workers)、拆分大事务、使用组提交(GTID)。
- 从库使用更高配置机器、使用半同步复制减少数据丢失、业务层做读延迟兜底。
GTID是什么?有什么优势?
难度:★★★★
- GTID(Global Transaction ID)= server_uuid:transaction_id,全局唯一标识每个事务。
- 优势:主从切换时不需要手动指定binlog位置,自动根据GTID找到同步点。
- 简化了主从复制搭建和故障恢复流程,新从库自动拉取缺失的事务。
- 配合多线程复制和组提交,提高复制性能和可靠性。
九、读写分离与组合方案
读写分离的实现方式有哪些?
难度:★★★★
- 代码层面:通过AbstractRoutingDataSource(Spring)或自定义数据源切换AOP,根据操作类型选择主库或从库。
- 中间件代理:使用Sharding-Proxy、MyCat、ProxySQL等代理层,对应用透明。
- 框架支持:Sharding-JDBC配置读写分离规则,自动将写操作路由到主库、读操作路由到从库。
- 主从复制+读写分离是经典组合,写操作走主库,读操作分散到从库,降低主库压力。
读写分离后如何处理主从延迟导致的数据不一致?
难度:★★★★★
- 方案一:强制走主库,对于写后立即读的场景(如创建订单后查看),标注该读请求走主库。
- 方案二:延迟检测,应用层记录写入时间戳,读取时判断从库是否已同步到该时间点。
- 方案三:中间件方案,如Sharding-JDBC的hintManager强制路由到主库。
- 方案四:业务容忍,对于实时性要求不高的查询允许短暂延迟(如报表、统计)。
分库分表+读写分离的组合架构是怎样的?
难度:★★★★★
- 主库按业务垂直拆分为多个库(用户库、订单库、商品库),每个主库配1-N个从库。
- 水平方向按分片键继续拆表,如订单库拆为order_0到order_15。
- 写操作路由到对应分片的主库,读操作路由到对应分片的从库。
- 通过Sharding-JDBC统一管理分片规则和读写分离规则,应用层无感知。
分库分表后如何做分布式事务?
难度:★★★★★
- XA事务:两阶段提交(2PC),强一致性但性能差,适合对一致性要求极高的场景。
- 基于消息的最终一致性:将操作封装为消息,通过MQ保证最终一致(本地消息表/事务消息)。
- TCC(Try-Confirm-Cancel):业务层面实现三个阶段,柔性事务,性能好但开发复杂。
- Seata框架:支持AT(自动补偿)、TCC、Saga、XA四种模式,与ShardingSphere集成方便。
读写分离+分库分表架构下如何保证高可用?
难度:★★★★
- 主库高可用:MHA(Master High Availability)自动故障转移、MySQL Group Replication多主模式。
- 从库高可用:多从库部署,单个从库故障不影响读服务,负载均衡切换。
- 分片高可用:每个分片的主从架构独立,某分片故障不影响其他分片。
- 监控告警:监控主从延迟、连接数、QPS、慢查询,异常及时告警。
十、索引设计与实战
如何设计一个好的索引?
难度:★★★★
- 选择区分度高的列作为索引列(区分度=不同值数/总行数),区分度太低(如性别)不适合单独索引。
- 联合索引遵循最左前缀原则,将等值查询列放在前面,范围查询列放在后面。
- 尽量使用覆盖索引,将查询需要的列都包含在索引中,避免回表。
- 避免冗余索引:索引(a)是索引(a,b)的前缀,单独存在是冗余的。
什么是索引合并(Index Merge)?
难度:★★★
- 索引合并是MySQL对多个索引的查询结果取交集(AND)或并集(OR)的优化策略。
- Index Merge Intersection:WHERE a=1 AND b=2,分别用a和b的索引查找,取主键交集后回表。
- Index Merge Union:WHERE a=1 OR b=2,分别用a和b的索引查找,取主键并集后回表。
- EXPLAIN的type显示index_merge表示使用了索引合并。
前缀索引的使用场景和限制?
难度:★★★
- 对长字符串列(如VARCHAR(255))只索引前N个字符,减少索引存储空间。
- 语法:ALTER TABLE t ADD INDEX idx_name(name(10)),只索引name的前10个字符。
- 限制:无法用于ORDER BY和GROUP BY,无法作为覆盖索引(因为索引中没有完整值)。
- 前缀长度选择:通过SELECT COUNT(DISTINCT LEFT(name,N))/COUNT(*)计算不同N值的选择性。
唯一索引与普通索引在写入性能上的差异?
难度:★★★★
- 唯一索引在插入时需要检查唯一性,需要将数据页读入内存判断是否有冲突(change buffer不适用)。
- 普通索引可以通过change buffer缓存写入操作,减少随机IO,写入性能更好。
- 如果业务层已保证唯一性(如先查再插),可以考虑用普通索引替代唯一索引提升写入性能。
- 读性能方面,唯一索引找到一条记录即停止扫描,普通索引需要继续扫描到下一条不满足条件的记录。
什么是Change Buffer?
难度:★★★★
- Change Buffer是InnoDB的一种优化机制,缓存对非唯一二级索引的DML操作(INSERT/DELETE/UPDATE)。
- 当索引页不在Buffer Pool中时,不立即从磁盘读取,而是将变更记录到Change Buffer中。
- 后续读取该索引页时,再合并Change Buffer中的变更,减少随机IO。
- 只适用于非唯一二级索引(因为唯一索引需要立即检查唯一性)。
如何判断一个索引是否应该删除?
难度:★★★
- 通过sys.schema_unused_indexes视图查看自MySQL启动以来从未被使用的索引。
- 通过performance_schema监控索引使用频率,低频索引考虑删除。
- 评估冗余索引:如果索引(a)是索引(a,b)的前缀,且没有查询单独使用a列,则(a)可删除。
- 删除前在测试环境验证,确认不影响任何查询的执行计划。
Online DDL对索引创建的影响?
难度:★★★
- MySQL 5.6+支持Online DDL,创建索引时不锁表(允许并发DML),但会短暂锁元数据。
- ALGORITHM=INPLACE + LOCK=NONE是理想的在线创建索引方式。
- 创建索引期间需要扫描全表构建索引树,大表耗时长,建议在低峰期执行。
- pt-online-schema-change工具通过创建影子表+触发器同步方式实现更平滑的DDL。
什么是全文索引?适用场景?
难度:★★★
- 全文索引(FULLTEXT INDEX)是MySQL内置的全文搜索功能,基于倒排索引实现。
- 支持自然语言搜索和布尔搜索,适合文本搜索场景(如文章内容搜索)。
- InnoDB和MyISAM都支持全文索引,但只支持CHAR、VARCHAR、TEXT类型。
- 复杂搜索场景建议使用Elasticsearch,MySQL全文索引功能有限。
十一、SQL优化实战
一条SQL从发出到返回结果的完整执行过程?
难度:★★★★★
- 1.客户端发送SQL到服务端,检查查询缓存(MySQL 8.0已移除)。
- 2.解析器进行词法分析和语法分析,生成解析树。
- 3.预处理器检查表和列是否存在、解析别名和通配符。
- 4.优化器选择执行计划(选择索引、JOIN顺序、子查询优化等),生成执行计划。
- 5.执行器根据执行计划调用存储引擎接口,通过索引或全表扫描获取数据。
- 6.存储引擎在Buffer Pool中查找数据页,未命中则从磁盘加载,返回数据行给执行器。
- 7.执行器对结果做WHERE过滤、排序、分组、LIMIT等处理,返回客户端。
MySQL的查询缓存为什么在8.0中被移除?
难度:★★★
- 查询缓存在每次表数据变更时都要失效所有相关的缓存项,高并发写入时成为性能瓶颈。
- 缓存命中率在实际业务中往往很低(数据频繁变更),维护缓存的成本超过收益。
- 查询缓存对InnoDB的MVCC机制不友好,不同事务可能看到不同版本的数据。
- 推荐使用外部缓存(Redis)或应用层缓存替代查询缓存。
共 60 道题目