MySQL 核心知识与索引优化全解析

本文涉及的产品
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
RDS MySQL Serverless 高可用系列,价值2615元额度,1个月
云数据库 RDS PostgreSQL,高可用系列 2核4GB
简介: 本文系统梳理了 MySQL 的核心知识与索引优化策略。在基础概念部分,阐述了 char 与 varchar 在存储方式和性能上的差异,以及事务的 ACID 特性、并发事务问题及对应的隔离级别(MySQL 默认 REPEATABLE READ)。索引基础部分,详解了 InnoDB 默认的 B+tree 索引结构(多路平衡树、叶子节点存数据、双向链表支持区间查询),区分了聚簇索引(数据与索引共存,唯一)和二级索引(数据与索引分离,多个),解释了回表查询的概念及优化方法,并分析了 B+tree 作为索引结构的优势(树高低、效率稳、支持区间查询)。索引优化部分,列出了索引创建的六大原则

一、MySQL 基础概念解析

  1. char 与 varchar 的区别两者都是存储字符串的类型,但核心差异体现在存储方式和性能上:
  • char是定长字符串,无论实际存储的字符串长度如何,都会占用固定长度的存储空间。例如定义char(10),即使只存 “abc” 3 个字符,也会占用 10 个字符的空间。这种特性让 char 的操作速度更快,但可能造成磁盘空间浪费。
  • varchar是变长字符串,占用的空间与实际存储的字符串长度相关。例如varchar(10)存储 “abc” 时,仅占用 3 个字符的空间(额外加 1-2 字节记录长度)。它的操作速度略慢,但能节省空间。
  1. 实际使用中,固定长度的字段(如手机号char(11)、身份证号char(18))适合用 char;长度不固定的字段(如用户名varchar(50)、备注varchar(200))适合用 varchar。
  2. 事务及四大特性事务是一组不可分割的数据库操作,要么全部成功,要么全部失败,以此保证数据的正确性和完整性。其四大特性(ACID)如下:
  • 原子性:事务中的操作是最小单元,无法拆分,要么全部执行成功,要么全部回滚。
  • 一致性:事务执行前后,数据必须处于合法状态(如转账后两人总金额不变)。
  • 隔离性:多个事务并发执行时,彼此不受干扰,仿佛在独立环境中运行。
  • 持久性:事务提交或回滚后,对数据库的修改永久生效,不会因系统故障丢失。
  1. 并发事务问题及解决
    并发事务可能引发三类问题:
  • 脏读:事务 A 读取到事务 B 未提交的数据(若 B 回滚,A 读到的数据无效)。
  • 不可重复读:事务 A 两次读取同一记录,期间事务 B 修改并提交,导致两次结果不同。
  • 幻读:事务 A 按条件查询无数据,事务 B 插入符合条件的数据后,A 插入时发现数据已存在。
  1. 数据库通过隔离级别解决这些问题:
隔离级别 脏读 不可重复读 幻读
READ UNCOMMITTED
READ COMMITTED
REPEATABLE READ
SERIALIZABLE
  1. MySQL 默认隔离级别是REPEATABLE READ,它平衡了安全性和性能。隔离级别并非越高越好,例如 SERIALIZABLE 虽解决所有问题,但会导致并发性能急剧下降。

二、索引基础

  1. MySQL 索引的数据结构InnoDB 存储引擎默认使用B+tree作为索引结构,其核心特点如下:
  • 多路平衡搜索树:一个节点可存储多个 key 和指针(而非二叉树的 2 个),数据量越大,树的高度越低(通常 2-3 层),检索效率更高。
  • 数据集中存储:所有数据仅存于叶子节点,非叶子节点仅作为索引,确保检索效率稳定(无论查询哪条数据,都需遍历到叶子节点)。
  • 区间查询友好:叶子节点形成双向链表,可快速定位区间范围(如查询 “id>100 and id<200”)。
  1. 聚簇索引与二级索引
  • 聚簇索引:数据与索引存储在一起,B+tree 的叶子节点保存整行数据。一张表有且仅有一个聚簇索引,默认是主键索引。若表无主键,MySQL 会选择第一个非空唯一索引作为聚簇索引;若仍无,则自动生成隐藏的 rowID 作为聚簇索引。
  • 二级索引(非聚簇索引):数据与索引分离存储,B+tree 的叶子节点仅保存主键(或聚簇索引值)。一张表可创建多个二级索引,查询时需通过主键关联聚簇索引获取完整数据。
  1. 例如,表user(id, name, age)中,id是聚簇索引(叶子节点存id+name+age),name是二级索引(叶子节点存name+id)。
  2. 回表查询基于二级索引查询时,需先通过二级索引获取主键,再到聚簇索引中查询完整数据,这个过程称为回表查询(需扫描两次索引,性能较低)。
  • 示例:通过name查询用户信息时,先在name二级索引中找到id=10,再到聚簇索引中查id=10对应的nameage
  • 优化方式:
  • 直接使用主键查询(避免二级索引)。
  • 创建联合索引(如(name, age)),实现 “索引覆盖”(二级索引已包含所需字段,无需回表)。
  • 启用索引下推(MySQL 5.6+):在二级索引层提前过滤数据,减少回表次数。
  1. B+tree 作为索引结构的原因对比其他结构(如二叉树、B 树),B+tree 的优势如下:
  • 树高更低:非叶子节点仅存 key 和指针(不存数据),一页(16KB)可存储更多 key(如主键为 bigint (8 字节)+ 指针 (6 字节),一页约存 1170 个 key),3 层树可容纳约 2190 万数据(1170×1170×16)。
  • 效率稳定:所有数据存于叶子节点,查询耗时固定。
  • 区间查询高效:叶子节点的双向链表支持快速范围扫描。

三、索引优化

  1. 索引创建原则
  • 为数据量大(单表超 10 万条)、查询频繁的表创建索引。
  • 优先为查询条件(where)、排序(order by)、分组(group by)字段创建索引。
  • 选择区分度高的列(如手机号,而非性别),唯一索引效率更高。
  • varchar 字段建议创建前缀索引(如name(10)),平衡索引大小和区分度。
  • 优先创建联合索引,将区分度高的字段放前面(如(name, age)(age, name)更优),减少单列索引(联合索引可覆盖更多查询场景)。
  • 索引不宜过多(维护成本高,会降低增删改效率)。
  1. 索引失效的常见场景
  • 违反最左前缀法则(联合索引需从最左列开始查询,如(name, age)索引,查询age=20会失效)。
  • 范围查询(如age>20)右侧的列无法使用索引((name, age)中,name='张三' and age>20name生效)。
  • 索引列上使用函数或运算(如substring(name, 1, 3)='张'age+1=20)。
  • 隐式类型转换(如phone=13800138000,而phone是 varchar 类型,需加引号)。
  • like 以 “%” 开头(如name like '%三',索引失效;name like '张%'则生效)。
  1. 可通过explain命令分析 SQL 执行计划,重点关注key(是否使用索引)、type(性能等级,避免all全表扫描)、extra(如Using filesort表示文件排序,需优化)。

四、SQL 性能分析及优化

  1. 定位慢 SQL
  • 工具监控:通过 SkyWalking 等链路追踪工具,查看接口耗时分布,定位慢 SQL。
  • 慢查询日志:开启 MySQL 慢查询日志(slow_query_log=1),设置阈值(如long_query_time=1秒),记录执行超 1 秒的 SQL。
  1. 分析 SQL 执行性能使用explain查看执行计划,核心字段如下:
  • key:实际使用的索引(为NULL表示未使用索引)。
  • type:查询类型(性能从优到差:NULL > system > const > eq_ref > ref > range > index > all)。
  • extra:额外信息(如Using index表示索引覆盖,Using where; Using index表示索引下推生效)。
  1. SQL 优化经验
  • 表设计:选择合适类型(如tinyint存性别,date存生日),主键用递增类型(如自增 ID,避免页分裂)。
  • 索引优化:合理创建索引,避免失效场景,利用联合索引覆盖查询。
  • 架构优化:高并发场景采用读写分离(主库写,从库读);数据量超千万时,考虑分库分表(如按 ID 范围拆分)。


相关文章
|
2月前
|
存储 SQL 关系型数据库
mysql底层原理:索引、慢查询、 sql优化、事务、隔离级别、MVCC、redolog、undolog(图解+秒懂+史上最全)
mysql底层原理:索引、慢查询、 sql优化、事务、隔离级别、MVCC、redolog、undolog(图解+秒懂+史上最全)
mysql底层原理:索引、慢查询、 sql优化、事务、隔离级别、MVCC、redolog、undolog(图解+秒懂+史上最全)
|
2月前
|
存储 关系型数据库 MySQL
MySQL数据库索引的数据结构?
MySQL中默认使用B+tree索引,它是一种多路平衡搜索树,具有树高较低、检索速度快的特点。所有数据存储在叶子节点,非叶子节点仅作索引,且叶子节点形成双向链表,便于区间查询。
91 4
|
3月前
|
SQL 缓存 关系型数据库
MySQL 慢查询是怎样优化的
本文深入解析了MySQL查询速度变慢的原因及优化策略,涵盖查询缓存、执行流程、SQL优化、执行计划分析(如EXPLAIN)、查询状态查看等内容,帮助开发者快速定位并解决慢查询问题。
143 0
|
29天前
|
存储 SQL 关系型数据库
MySQL中binlog、redolog与undolog的不同之处解析
每个都扮演回答回溯与错误修正机构角色: BinLog像历史记载员详细记载每件大大小小事件; RedoLog则像紧急救援队伍遇见突發情況追踪最后活动轨迹尽力补救; UndoLog就类似时间机器可倒带历史让一切归位原始样貌同时兼具平行宇宙观察能让多人同时看见各自期望看见历程而互不干扰.
129 9
|
29天前
|
缓存 关系型数据库 MySQL
降低MySQL高CPU使用率的优化策略。
通过上述方法不断地迭代改进,在实际操作中需要根据具体场景做出相对合理判断。每一步改进都需谨慎评估其变动可能导致其他方面问题,在做任何变动前建议先在测试环境验证其效果后再部署到生产环境中去。
75 6
|
2月前
|
存储 SQL 关系型数据库
MySQL 动态分区管理:自动化与优化实践
本文介绍了如何利用 MySQL 的存储过程与事件调度器实现动态分区管理,自动化应对数据增长,提升查询性能与数据管理效率,并详细解析了分区创建、冲突避免及实际应用中的关键注意事项。
118 0
|
3月前
|
存储 关系型数据库 MySQL
MySQL覆盖索引解释
总之,覆盖索引就像是图书馆中那些使得搜索变得极为迅速和简单的工具,一旦正确使用,就会让你的数据库查询飞快而轻便。让数据检索就像是读者在图书目录中以最快速度找到所需信息一样简便。这样的效率和速度,让覆盖索引成为数据库优化师傅们手中的尚方宝剑,既能够提升性能,又能够保持系统的整洁高效。
107 9
|
2月前
|
存储 SQL 关系型数据库
MySQL 核心知识与性能优化全解析
我整理的这份内容涵盖了 MySQL 诸多核心知识。包括查询语句的书写与执行顺序,多表查询的连接方式及内、外连接的区别。还讲了 CHAR 和 VARCHAR 的差异,索引的类型、底层结构、聚簇与非聚簇之分,以及回表查询、覆盖索引、左前缀原则和索引失效情形,还有建索引的取舍。对比了 MyISAM 和 InnoDB 存储引擎的不同,提及性能优化的多方面方法,以及超大分页处理、慢查询定位与分析等,最后提到了锁和分库分表可参考相关资料。
|
3月前
|
关系型数据库 MySQL
MySQL字符串拼接方法全解析
本文介绍了四种常用的字符串处理函数及其用法。方法一:CONCAT,用于基础拼接,参数含NULL时返回NULL;方法二:CONCAT_WS,带分隔符拼接,自动忽略NULL值;方法三:GROUP_CONCAT,适用于分组拼接,支持去重、排序和自定义分隔符;方法四:算术运算符拼接,仅适用于数值类型,字符串会尝试转为数值处理。通过示例展示了各函数的特点与应用场景。
|
3月前
|
人工智能 运维 关系型数据库
数据库运维:mysql 数据库迁移方法-mysqldump
本文介绍了MySQL数据库迁移的方法与技巧,重点探讨了数据量大小对迁移方式的影响。对于10GB以下的小型数据库,推荐使用mysqldump进行逻辑导出和source导入;10GB以上可考虑mydumper与myloader工具;100GB以上则建议物理迁移。文中还提供了统计数据库及表空间大小的SQL语句,并讲解了如何使用mysqldump导出存储过程、函数和数据结构。通过结合实际应用场景选择合适的工具与方法,可实现高效的数据迁移。
661 1

推荐镜像

更多