08-高性能表结构及索引设计最佳实践-02

最新推荐文章于 2025-12-15 21:10:21 发布
原创 最新推荐文章于 2025-12-15 21:10:21 发布 · 192 阅读 · 0 ·
CC 4.0 BY-SA版权
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
文章标签：

#数据库 #sql #mysql

本文详细介绍了MySQL InnoDB存储引擎中的各种索引类型及其工作原理，包括聚集索引、辅助索引、联合索引和自适应哈希索引。通过实际表order_exp的例子，讲解了这些索引如何提高数据库查询效率。

MySQL索引辨析

范例表说明

经常使用到InnoDB表order_exp，基础表结构如下：

在这里插入图片描述
数据量大概在1万多行。
在这里插入图片描述
同时还有个几个派生表如s1，s2，order_exp_cut，表结构基本和order_exp一致，有少许差别，数据量上也比order_exp少：

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

InnoDB中的索引复习

聚集索引/聚簇索引

InnoDB中使用了聚集索引，就是将表的主键用来构造一棵B+树，并且将整张表的行记录数据存放在该B+树的叶子节点中。

辅助索引/二级索引

对于辅助索引(Secondary Index，也称二级索引、非聚集索引)，叶子节点并不包含行记录的全部数据。叶子节点除了包含键值以外，每个叶子节点中的索引行中还包含了相应行数据的聚集索引键。
在这里插入图片描述

回表

辅助索引的存在并不影响数据在聚集索引中的组织，因此每张表上可以有多个辅助索引。当通过辅助索引来寻找数据时，InnoDB存储引擎会遍历辅助索引并通过叶级别的指针获得指向主键索引的主键，然后再通过主键索引（聚集索引）来找到一个完整的行记录。这个过程也被称为回表。也就是根据辅助索引的值查询一条完整的用户记录需要使用到2棵B+树----一次辅助索引，一次聚集索引。

联合索引/复合索引

前面我们对索引的描述，隐含了一个条件，那就是构建索引的字段只有一个，但实践工作中构建索引的完全可以是多个字段。所以，将表上的多个列组合起来进行索引我们称之为联合索引或者复合索引，比如index(a,b)就是将a,b两个列组合起来构成一个索引。

自适应哈希索引

InnoDB存储引擎除了我们前面所说的各种索引，还有一种自适应哈希索引，我们知道B+树的查找次数,取决于B+树的高度,在生产环境中,B+树的高度一般为34层,故需要34次的IO查询。

全文检索之倒排索引

什么是全文检索（Full-Text Search）？它是将存储于数据库中的整本书或整篇文章中的任意内容信息查找出来的技术。它可以根据需要获得全文中有关章、节、段、句、词等信息，也可以进行各种统计和分析。我们比较熟知的Elasticsearch、Solr等就是全文检索引擎，底层都是基于Apache Lucene的。举个例子，现在我们要保存唐宋诗词，数据库中我们们会怎么设计？诗词表我们可能的设计如下：
在这里插入图片描述

clickhouse跳表索引最佳实践--minmax索引+Set索引
lixia0417mul2的博客
04-15 1778
clickhouse跳表索引 set跳表索引 minmax跳表索引
02-VIP-Explain详解与索引最佳实践.pdf
11-30
Mysql Explain 详解与索引最佳实践 Mysql Explain 是 Mysql 中一个非常重要的工具，可以帮助我们分析和优化 SQL 查询语句。在本文中，我们将详细介绍 Mysql Explain 的使用和解释 Explain 输出结果，帮助您更好地...
120. MySQL表结构设计18条最佳实践原则
YouMing_Li的博客
05-22 1925
对于后端开发同学来说，访问数据库，是代码中必不可少的一个环节。等。后端开发的日常工作，需要不断的建库和建表，来满足业务需求。通常情况下，建库的频率比建表要低很多，所以，我们这篇文章主要讨论建表相关的内容。如果我们在建表的时候不注意细节，等后面系统上线之后，表的维护成本变得非常高，而且很容易踩坑。今天就跟大家一起聊聊，数据库建表的18个小技巧。文章中介绍的很多细节，我在工作中踩过坑，并且实践过的，非常有借鉴意义，希望对你会有所帮助。
你还不会表结构设计？三分钟内教你如何做好表结构设计
weixin_71690375的博客
05-29 4328
如何做好表结构设计？大家关心的问题阳哥必须整理出来，希望对大家有帮助。这篇文章介绍了设计数据库表结构应该考虑的4个方面，还有优雅设计的6个原则，举了一个例子分享了我的设计思路，为了提高性能我们也要从多方面考虑缓存问题。首先，一定要先搞清楚业务需求。比如我的例子中，如果不需要灵活设置，完全可以写到配置文件中，并不需要单独设计外键。主表中直接保存各种筛选标签名称（注意维护的问题，要考虑到数据一致性）数据库表结构设计一定考虑数据量和并发量，我的例子中如果数据量小，可以适当做冗余设计，降低业务复杂度。
mysql优化八：高性能表结构及索引设计实践
admin522043032的博客
12-16 1499
数据库表结构设计 良好的表结构设计是高性能的基石，应该根据系统将要执行的业务查询来设计，这往往需要权衡各种因素。糟糕的表结构设计，会浪费大量的开发时间，严重延误项目开发周期，让人痛苦万分，而且直接影响到数据库的性能，并需要花费大量不必要的优化时间，效果往往还不怎么样。在数据库表设计上有个很重要的设计准则，称为范式设计 范式设计 目前关系数据库有六种范式：第一范式（1NF）、第二范式（2NF）、第三范式（3NF）、 巴斯-科德范式（BCNF）、第四范式(4NF）和第五范式（5NF，又称完美范式）。满足最低要求
MySQL高级课程：索引设计与性能优化的最佳实践
2401_85639015的博客
08-30 1856
索引是数据库管理系统中用于快速查找记录的一种数据结构。类似于书籍的目录，索引可以加速查询的速度。它通过减少数据库需要扫描的行数，提高了数据检索的效率。索引不仅能提升查询速度，还能加速排序和分组操作。MySQL索引设计与性能优化是一个复杂且具有挑战性的过程。合理的索引设计可以大幅提升查询性能，但不合理的索引反而会拖慢数据库的响应速度。在实际应用中，需要结合具体的业务场景，灵活运用各种优化技巧，如索引优化、查询优化、表结构优化和缓存策略等。
数据库表结构及索引设计
xjk201的博客
12-20 2万+
数据库表设计 良好的表结构设计是高性能的基石，应该根据系统将要执行的业务查询来设计，这往往需要权衡各种因素。糟糕的表结构设计，会浪费大量的开发时间，严重延误项目开发周期，让人痛苦万分，而且直接影响到数据库的性能，并需要花费大量不必要的优化时间，效果往往还不怎么样。 在数据库表设计上有个很重要的设计准则，称为范式设计。 范式设计 什么是范式？ 范式来自英文Normal Form，简称NF。MySQL是关系型数据库，但是要想设计—个好的关系，必须使关系满足一定的约束条件，此约束已经形成了规范，分成
MySQL 复制表：结构、数据及索引的完整复制
✨ 欢迎来到【Seal ^_^ 的优快云博客】！✨
11-08 1万+
在数据库管理中，复制表是一项常见且重要的操作。MySQL 提供了多种方法来实现表的完整复制，包括表结构、数据、索引、约束等所有元素的复制。fill:#333;color:#333;color:#333;fill:none;MySQL 复制表仅复制结构复制结构和数据复制到不同数据库部分数据复制fill:#333;color:#333;color:#333;fill:none;是否是否需要复制什么?需要完整结构?需要数据?使用CREATE TABLE...SELECT。
Apache Pinot数据建模最佳实践：如何设计高效的表结构和索引
gitblog_00621的博客
12-03 1011
Apache Pinot作为一款开源的分布式实时OLAP分析引擎，在处理PB级别数据时表现出色。要实现Pinot的最佳性能，数据建模是关键环节。本文将为您详细介绍Apache Pinot数据建模的核心原则和最佳实践，帮助您设计出高效的表结构和索引配置。 ## 📊 理解Pinot数据模型基础 Apache Pinot的数据模型基于**表**、**段**和**索引**三个核心概念。每个表由多个段
RAG 架构地基工程-Retrieval 模块的系统设计分享
热门推荐
曾经“等你生日那天”都遥远得像未来，如今却可欢愉的挥手说“下个十年见”
08-24 13万+
Retrieval-Augmented Generation（RAG）架构在大模型时代大放异彩，而其中的 Retrieval 模块往往被低估。本文系统梳理了 Retrieval 模块的关键设计点，包括数据源类型、文档切分策略、向量化与存储结构、语义检索方式，以及多文档融合机制。通过图示与表格对比，揭示不同设计选择对生成效果的深远影响，强调 Retrieval 不仅是召回，更是影响最终答案质量的“地基工程”。本文旨在为研发者构建可控、可调、可解释的 RAG 系统提供结构化参考。
【数据库技术】MySQL高性能数据库设计与优化：从Schema设计到索引原理及实战应用
06-21
内容概要：本文档《MySQL高性能从Schema设计、开发到索引原理与实战.pdf》详细介绍了MySQL数据库的高效设计和优化方法。首先阐述了数据库Schema设计的重要性及其流程，包括需求分析、E-R图设计、物理模型转换等步骤...
SQL Server 索引结构及其使用（一）--深入浅出理解索引结构第1/4页
09-11
在SQL Server数据库管理系统中，索引是提升查询性能的关键组件，它们的工作原理类似于书籍的目录，帮助数据库系统快速定位和访问...在实践中，应结合业务需求和查询模式，合理选择和管理索引，以实现最佳的数据库性能。
从一次增删改操作开始：彻底理解 MySQL Buffer Pool 的地位与作用
soft2001525的博客
12-11 2133
它是 InnoDB 的核心内存引擎，是所有数据读写的唯一入口，是数据库性能与安全性的关键支撑。理解 Buffer Pool 的逻辑，你就理解了 MySQL 的一半。redo logundo log（事务回滚）MVCC脏页、刷盘策略自适应哈希索引B+树索引加载机制查询优化都与 Buffer Pool 有直接关系。如果你想系统搞懂 MySQL，从 Buffer Pool 开始是最佳路径。如果本篇文章对你有帮助，欢迎：✏️ 评论交流。
兰顿蚂蚁——CAD二次开发
最新发布
12-15 298
【代码】兰顿蚂蚁——CAD二次开发。
KingbaseES 面向应用程序的SQL开发：从原理到实战的深度探索
优快云博客专家，领域包括但不限于：AI、大数据、Python、架构师，有合作、课程、问题、疑惑请私信博主
12-14 619
KingbaseES数据库SQL开发实践解析 摘要：本文深入探讨国产数据库KingbaseES在SQL开发中的核心实践，重点分析四大关键技术：1) SQL处理机制，包括游标管理和绑定变量优化；2) 正则表达式的高级应用与性能优化；3) 多类型索引的选择与优化策略；4) 事务控制与锁机制。通过具体代码示例，展示了KingbaseES在金融、政务等关键领域的高性能SQL开发能力，包括事务隔离级别设置、死锁检测等高级特性。文章为开发者提供了从基础语法到高级优化的完整SQL开发指南，助力企业实现高效可靠的数据库应用
基于Java+SpringBoot+Vue的美甲店管理系统【附源码+文档+部署视频+讲解)
小熊的博客
12-11 1251
基于SpringBoot的美甲店管理系统开发案例，涵盖用户、美甲师和管理员三大角色功能模块，采用Vue+SpringBoot+MySQL技术栈实现。系统包含服务预约、订单管理、耗材采购等全流程数字化功能，采用MVC设计模式和B/S架构。包含用户、美甲师、管理员三类角色的功能模块，该系统聚焦美甲店的日常运营场景，通过用户端浏览服务与套餐、美甲师端处理预约与评价、管理员端统筹人员、业务与资源的模式，实现美甲店从服务展示、预约调度到耗材管理的全流程数字化，提升门店运营效率与服务体验。
Qt6.5.3 mingw64 Ninja编译oracle oci驱动
小蝈蝈的博客
12-12 465
2.将sdk和basic的内容复制到一起，basic中的instantclient_xx_x目录下的内容，sdk的instantclient_xx_xx目录下的内容。├── include/ ← 头文件（来自 sdk/include）到 自己的项目的运行路径下的**/plugins/sqldrivers/**└── sdk/ ← 原始目录（可保留，但非必需）3.将oci.dll生成mingw64支持的.a库。4.切换到Qt源码中的路径下，我的路径。
小白安装Redis
2201_75999403的博客
12-11 612
简单亲测无痛安装redis
《Python实战小课：爬虫工具场景——开启数据抓取之旅》导读
2501_93253814的博客
12-15 985
本文介绍了Python爬虫技术在三大场景中的应用：行业资讯爬取、学术文献摘要获取和电商评价收集。针对行业资讯，详细解析了从网页请求到数据存储的全流程；在学术文献方面，重点阐述了如何构建搜索请求和提取关键信息；对于电商评价，则说明了数据定位和清洗方法。文章还探讨了爬虫优化策略及反爬机制应对方案，为数据获取工作提供了实用指南。通过系统学习这些技术，读者可以提升数据采集能力，为商业决策、学术研究和市场分析提供有力支持。
数据库新增菜单表结构设计最佳实践
06-19
<think>我们正在设计一个菜单表结构，需要遵循数据库设计的最佳实践。根据引用内容，我们可以参考数据库设计的基础理论和关键技术，以及实际应用场景。菜单表通常用于存储系统中的菜单项，比如导航菜单、权限菜单等。设计菜单表时，需要考虑以下几点：1.菜单项的层级关系（父子关系）2.菜单的排序3.菜单的元数据（名称、图标、路径等）4.菜单的权限控制（可选，如果与权限关联）常见的菜单表结构设计有两种方式：方式一：邻接表（AdjacencyList）这是最简单的树形结构存储方式，每个菜单项存储其父菜单的ID。优点：结构简单，操作方便。缺点：递归查询效率较低（在MySQL8.0之前需要使用递归查询或多次查询，MySQL8.0支持CTE递归查询）。方式二：嵌套集（NestedSet）每个菜单项存储左值和右值，通过左右值来确定层级关系。优点：查询子树和祖先路径效率高。缺点：插入和删除节点时需要调整多个节点的左右值，操作复杂。在实际应用中，邻接表因为简单而被广泛采用，特别是当菜单层级不深（如3-4级）且数据量不大时。此外，还可以考虑使用闭包表（ClosureTable），它通过一个额外的表来存储节点之间的关系，从而解决邻接表递归查询效率低的问题。但是，根据最佳实践，对于菜单这种通常层级不深且变动不频繁的数据，邻接表是足够且易于维护的。因此，我们可以设计一个使用邻接表方式的菜单表：表名：`menu`字段：-`id`:主键，自增整数-`parent_id`:父菜单ID，外键引用本表的id，可为NULL（表示顶级菜单）-`name`:菜单名称，VARCHAR(50)-`path`:菜单对应的路径（或路由），VARCHAR(255)，可为NULL（如果是分组菜单）-`icon`:菜单图标，VARCHAR(50)，可为NULL-`sort_order`:排序权重，INT，用于同级菜单的排序-`is_enabled`:是否启用，BOOLEAN，默认为1（启用）-`created_at`:创建时间，TIMESTAMP-`updated_at`:更新时间，TIMESTAMP此外，如果需要权限控制，可以关联权限表，比如每个菜单项可以关联一个权限标识（permission_code），或者通过角色-菜单关联表来实现。示例SQL创建语句：```sqlCREATETABLEmenu(idINTAUTO_INCREMENTPRIMARYKEY,parent_idINTNULL,nameVARCHAR(50)NOTNULL,pathVARCHAR(255)NULL,iconVARCHAR(50)NULL,sort_orderINTDEFAULT0,is_enabledBOOLEANDEFAULT1,created_atTIMESTAMPDEFAULTCURRENT_TIMESTAMP,updated_atTIMESTAMPDEFAULTCURRENT_TIMESTAMPONUPDATECURRENT_TIMESTAMP,FOREIGNKEY(parent_id)REFERENCESmenu(id)ONDELETECASCADE);```注意：-外键约束`ONDELETECASCADE`表示当父菜单被删除时，子菜单自动删除。-如果使用InnoDB引擎，外键约束才有效。查询所有菜单并按层级和排序权重展示：在MySQL8.0以上，可以使用递归CTE（CommonTableExpression）查询：```sqlWITHRECURSIVEmenu_treeAS(SELECTid,parent_id,name,path,icon,sort_order,1ASlevelFROMmenuWHEREparent_idISNULLUNIONALLSELECTm.id,m.parent_id,m.name,m.path,m.icon,m.sort_order,mt.level+1FROMmenumINNERJOINmenu_treemtONmt.id=m.parent_id)SELECT*FROMmenu_treeORDERBYlevel,sort_order;```在MySQL8.0以下，通常需要在应用层进行递归查询（多次查询，然后构建树形结构）。另外，如果菜单需要支持多语言，那么还需要设计菜单名称的多语言表，这里不展开。总结：菜单表结构设计采用邻接表模式是常见且实用的做法。同时，通过外键约束保证数据完整性，通过递归查询（或应用层构建树）来获取层级结构。参考引用：[^1]提到数据库设计的基础理论和关键技术，以及实际应用场景。[^2]提供了一个数据库设计的实例（个人博客数据管理系统），其中包含了表结构设计。[^3]介绍了使用MySQLWorkbench进行数据库设计与管理，我们可以使用该工具来设计并生成菜单表。根据以上设计，我们可以满足菜单的基本管理需求。§§相关问题§§1.菜单表如何支持无限层级？2.在菜单表设计中，如何优化递归查询的性能？3.菜单表与权限系统如何关联设计？</think>### 数据库菜单表结构设计最佳实践 在设计菜单表结构时，需考虑**层级关系、权限控制、灵活扩展**等核心需求。以下是经过验证的最佳实践方案： #### 基础表结构设计（邻接表模型） ```sql CREATE TABLE sys_menu ( menu_id INT AUTO_INCREMENT PRIMARY KEY COMMENT '菜单ID', parent_id INT DEFAULT 0 COMMENT '父菜单ID（0表示根节点）', menu_name VARCHAR(50) NOT NULL COMMENT '菜单名称', menu_path VARCHAR(255) COMMENT '路由路径', menu_icon VARCHAR(50) COMMENT '图标类名', sort_order INT DEFAULT 0 COMMENT '排序权重', is_visible TINYINT(1) DEFAULT 1 COMMENT '是否可见', permission_tag VARCHAR(50) COMMENT '权限标识', created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, INDEX idx_parent (parent_id), INDEX idx_sort (sort_order) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; ``` **关键字段说明：** - `parent_id`：实现层级结构的基础字段，0表示顶级菜单 - `sort_order`：控制同级菜单的显示顺序 - `permission_tag`：关联权限系统的标识符（如`system:user:view`） - `is_visible`：动态控制菜单显示状态 #### 高级优化方案 1. **闭包表优化深层级查询** ```sql -- 路径关系表 CREATE TABLE menu_closure ( ancestor INT NOT NULL COMMENT '祖先节点', descendant INT NOT NULL COMMENT '后代节点', depth INT NOT NULL COMMENT '层级深度', PRIMARY KEY (ancestor, descendant), FOREIGN KEY (ancestor) REFERENCES sys_menu(menu_id), FOREIGN KEY (descendant) REFERENCES sys_menu(menu_id) ); ``` **优势**：解决邻接表递归查询性能问题，特别适合超过3层的深菜单树[^1] 2. **多语言支持设计** ```sql CREATE TABLE menu_i18n ( menu_id INT NOT NULL, lang_code CHAR(2) NOT NULL COMMENT '语言代码', display_name VARCHAR(100) NOT NULL COMMENT '本地化名称', PRIMARY KEY (menu_id, lang_code) ); ``` #### 设计原则 1. **层级控制**：建议不超过5级，超过3级时添加面包屑导航字段 2. **权限分离**：菜单表只存储权限标识，具体验证逻辑在业务层实现 3. **类型区分**：添加`menu_type`字段（如：0-菜单项，1-功能按钮） 4. **元数据扩展**：使用JSON字段存储动态配置（如：`extra_config JSON`） #### 查询示例 获取用户可见菜单树（MySQL 8.0+ 递归查询）： ```sql WITH RECURSIVE menu_tree AS ( SELECT * FROM sys_menu WHERE parent_id = 0 AND is_visible = 1 UNION ALL SELECT m.* FROM sys_menu m INNER JOIN menu_tree mt ON m.parent_id = mt.menu_id WHERE m.is_visible = 1 ) SELECT * FROM menu_tree ORDER BY sort_order; ``` #### 性能优化建议 1. 为`parent_id`和`sort_order`创建组合索引 2. 高频访问系统添加菜单缓存层（Redis/Memcached） 3. 定期执行`OPTIMIZE TABLE`维护索引碎片 > 使用MySQL Workbench可视化设计时，可通过EER图建立表关系，并自动生成DDL脚本[^3]。实际部署前建议用工具（如pt-query-digest）进行压力测试。
评论
成就一亿技术人!
拼手气红包6.0元
还能输入1000个字符
 
红包 添加红包
表情包 插入表情
表情包 代码片
 条评论被折叠 查看
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值