MySQL-索引相关设计原则

最新推荐文章于 2025-11-22 04:58:29 发布
原创 最新推荐文章于 2025-11-22 04:58:29 发布 · 399 阅读 · 4 ·
CC 4.0 BY-SA版权
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
文章标签：

#mysql #数据库

1、适合创建索引的情况

1.1、字段数值具有唯一性的限制

  • 索引本身是具有约束作用的，比如创建唯一索引、主键索引
  • 在数据表中如果某个字段是唯一性的，可以创建唯一性索引或主键索引

在业务场景中具有唯一特性的字段，即使是组合字段也必须建成唯一索引
唯一索引对insert语句的损耗可以忽略不计，但提高查找速度是明显的

1.2、频繁作为where查询条件的字段

  • 某个字段在执行select语句的where条件中经常被使用到，那需要对该字段创建索引
  • 数据量大的情况下，创建普通索引可以大幅度提高查询效率

1.3、经常使用group by 和 order by 的列

  • 索引就是让数据按照某种顺序进行存储或检索，因此当使用 group by 对数据进行分组查询 或 使用 order by 对数据进行排序时，则需要 对分组或者排序的字段进行索引
  • 如果排序或分组的列有多个，可以在这些列上建立组合索引

1.4、update 、 delete 的 where条件列

  • 对于数据按照某个条件查询后在进行 update 或 delete 的操作，如果对于where 字段创建了索引，能大幅度提高检索效率
  • 如果进行更新时，更新的字段为非索引字段，提升的效率更加明显，因为非索引字段更新不需要对索引进行维护。

1.5、distinct 字段创建索引

1.6、多表 join 连接操作时，创建索引的注意事项

  • 连接表的数量尽量不要超过三张，每增加一张表就相当于增加了一次嵌套循环，数量级增长会非常快，严重影响查询效率。
  • 对where 条件创建索引
  • 对于连接的字段创建索引，并且该字段在多张表中的类型必须一致。

1.7、使用列的类型小的列创建索引

  • 此处列的类型大小指 该类型表示的数据范围的大小
  • 数据类型越小，在查询时进行的比较操作越快
  • 数据类型越小，索引占用的存储空间越小，在同一个数据页中存放的记录越多，从而减少磁盘I/O带来的性能损耗

1.8、使用字符串前缀创建索引

1.9、区分度高（散列性高）的列适合作为索引

1.10、使用最频繁的列放到联合索引的左侧

1.11、在多个字段都要创建索引的情况下，联合索引优于单值索引

2、限制索引的数目

  • 单张索引数量不超过6个：
    • 每个索引都需要占用磁盘空间，索引越多，需要的磁盘空间越大
    • 所以会影响增删改语句的性能，表中数据更改的同时，索引也会进行调整和更新
    • 优化器在选择如何查询时会根据同一信息，对每个可以用到的索引进行评估，以生成一个最好的止执行计划，如果同时有多个索引都可用于查询，会增加MySQL优化器生成计划的时间，降低查询性能。

3、不适合创建索引的情况

3.1、在where中使用不到的字段，不要设置索引

  • 索引的价值是快速定位，不起到该作用的字段无需设置索引

3.2、数据量小的表最好不使用索引

  • 表中数据量级过小是否创建索引对查询效率的影响并不大。

3.3、有大量重复数据的列上不要创建索引

  • 该情况下创建索引可能会严重降低数据的更新速度

3.4、避免对经常更新的表创建过多的索引

  • 频繁更新的字段不一定要创建索引，因为在更新数据时还需要更新索引，如果索引过多，在更新索引的造成负担进而影响效率
  • 避免对经常更新的表创建过多的索引，并且索引中的列尽可能的少。虽然提高了查询速度，同时会降低表的更新速度。

3.5、不推荐以无序的值作为索引

3.6、删除不在使用或者很少使用的索引

  • 减少索引对更新操作的影响

3.7、不要定义冗余或重复的索引

mysql-索引设计原则
学会改变自己——才能突破
08-07 1392
1、代码先行，索引后上 不要一上来就进行索引的过度建立，而是根据业务的主体功能开发完毕后，再进行对相关sql的统计并分析之后再进行索引的合理建立。 2、联合索引尽量覆盖条件 由于存储索引也会占用空间，并进数据变更也会进行维护更新，而且每次查询只能用到一个索引，因此尽量建立联合索引，尽量少建立单值索引，让每一个联合索引都尽量去包含sql语句里的where、order by、group by的字段，还要确保这些联合索引的字段顺序尽量满足sql查询的最左前缀原则。 3、不要在小...
Mysql - 索引
m0_69594200的博客
07-23 1294
存储引擎、SQL优化工具、SQL优化，索引的分类、语法和使用规则等【笔记】内容
mysql索引使用技巧及注意事项
2301_78145658的博客
06-14 738
如果分别给LOGIN_NAME,CITY,AGE建立单列索引，让该表有3个单列索引，查询时和组合索引的效率是大不一样的，甚至远远低于我们的组合索引。建表时，LOGIN_NAME长度为100，这里用16，是因为一般情况下名字的长度不会超过16，这样会加快索引查询速度，还会减少索引文件的大小，提高INSERT，UPDATE的更新速度。一般的应用系统，读写比例在10:1左右，而且插入操作和一般的更新操作很少出现性能问题，遇到最多的，也是最容易出问题的，还是一些复杂的查询操作，所以查询语句的优化显然是重中之重。
mysql-适合创建索引的11种情况
ven_ture的博客
06-13 2549
客户端把sql语句交给数据库后，需要经过数据库的优化器生成查询计划，在此期间决定是走索引查询还是进行全表扫描。本文将展示字段添加索引前后查询效率的对比，来指出哪些情况适合创建索引
mysql索引设计原则
Lprivate的博客
08-14 249
虽然索引可以提高查询性能，但过多的索引会增加写操作的开销，并占用额外的存储空间。：对于复合索引（多列索引），最左前缀原则指的是索引的前缀子集可以用于查询，但是如果查询不使用索引的最左前缀，索引将不会被使用。定期进行索引维护，如重新构建索引、优化查询语句等，可以保持索引的高效性能。综合考虑这些原则，根据具体的应用场景和查询需求来设计合适的MySQL索引，以提高数据库的性能和响应速度。根据查询需求选择适当的索引类型。：索引的数据类型和排序规则需要与查询中的条件和排序保持一致，以确保索引能够被有效使用。
MYSQL索引的种类
JmuziA的博客
04-08 890
如果使用联合索引，那么where条件也要尽量根据联合索引的顺序来，如果不按照顺序来，索引也同样会用到，但是在执行前，SQL优化器也会将条件调整为联合索引的顺序，既然可以直接避免这种情况，就没必要再让SQL优化器去处理，毕竟处理也是有开销的。(索引的存储和数据的存储是分开的，也就是说找到了索引，但是没有找到数据，需要根据索引上的值在回表查询，所以非聚簇索引也叫辅助索引。创建主键的时候会自动给主键添加索引，且该索引为唯一索引，即主键一定是唯一索引，但是一张表里可以有多个唯一索引，所以唯一索引不一定是主键。
MySQL索引的设计原则
swadian2008的博客
05-07 2529
目录 1. 选择唯一性索引 2. 为经常需要排序、分组和联合操作的字段建立索引 3. 为常作为查询条件的字段建立索引 4. 限制索引的数目 5. 尽量使用数据量少的索引 6. 数据量小的表最好不要使用索引 7. 尽量使用前缀来索引 8. 删除不再使用或者很少使用的索引 9. 代码现行，索引后上 10. where 与 Order by 冲突时优先 where 11. 基于慢查询做优化 索引的设计可以遵循一些已有的原则，创建索引的时候应尽量考虑符合这些原则，便于提升索引的使用效率，更高
MySQL 高级 - 索引 - 索引设计原则
Leon_Jinhai_Sun的博客
11-29 240
索引设计原则 ​ 索引的设计可以遵循一些已有的原则，创建索引的时候请尽量考虑符合这些原则，便于提升索引的使用效率，更高效的使用索引。 对查询频次较高，且数据量比较大的表建立索引。 索引字段的选择，最佳候选列应当从where子句的条件中提取，如果where子句中的组合比较多，那么应当挑选最常用、过滤效果最好的列的组合。 使用唯一索引，区分度越高，使用索引的效率越高。 索引可以有效的提升查询数据的效率，但索引数量不是多多益善，索引越多，维护索引的代价自然也就水涨船高。对于插
mysql - 索引设计原则
weixin_38376791的博客
08-17 214
索引设计原则 1. 代码先行，索引后上 应该根据主体业务的sql语句设计对应的索引。 2. 联合索引尽量覆盖条件 比如可以设计一个或者两三各联合索引（尽量减少单值索引），where，order by, group by的字段，确保这些联合索引的字段尽量满足sql查询的最左匹配原则。 3.不要在小基数字段上建立索引 比如性别字段。 4.长字符串可以采用前缀索引 varchar(255)的大字段可能会比较占用磁盘空间，可以针对此类字段的前n个字符建立索引。注意此类索引支持等值索引，不支持order by和gro
Mysql 索引设计原则
lianghl_wb的博客
08-17 918
比如某些字段已经存在于联合索引中了，就不在需要单独创建索引。又或者某个字段已经创建了唯一索引，则无需在定义一个普通索引。
MySQL-数据库-索引详解
08-30
索引设计原则 ---------------- ###惟一性索引的值是惟一的，可以更快速通过索引确定某条记录。 ###为经常需要排序、分组、联合操作的字段建立索引 ###为常作为查询条件的字段建立索引 ###限制索引的数目 索引...
6-MySQL原理-索引匹配原则
Java架狗师知识框架速查表
06-13 1172
离散性最好的列：越大离散型越好 count(distinct col):count(col) 理解为差异性。结论：离散性越高选择性就越好，比如有一个性别的字段的索引，假设男为1，女为0：就会生成一下一个索引树：这个时候要搜索女的数据，那么在根节点触发，可以由两条路可以走，从中间走下去的话发现可以选择的线路太多了，这样会导致搜索引擎懵逼，优化器觉得既然要搜索这么多数据，还不如全表扫描呢，这就导致离散型降低。不利于性能。对索引中关键字进行计算（对比），一定是从左往右依次进行，且不可跳过，在创建数据库的时候需要选
MySQL-索引
m0_73902080的博客
07-31 1320
MySQL的索引是在存储引擎层实现的，不同的存储引擎有不同的结构，主要包含以下几种：不同的存储引擎对索引结构的支持情况：注意：平常所说的索引，如果没有特别指明，都是指B+树索引。
MySQLGraphQLAPI
2509_93945719的博客
11-22 318
另外，权限控制也得注意，GraphQL默认不限制查询，如果公开暴露，可能被恶意请求拖垮服务器。首先，你得安装必要的包，比如、，还有MySQL的连接器，比如。还有，如果数据库表结构复杂，有联表查询，GraphQL的schema也得相应设计成嵌套类型，resolver里用JOIN语句处理。这里，类型对应MySQL表的字段，定义了可用的查询操作，比如获取所有用户或根据ID查单个用户。注意，GraphQL的字段名可以用camelCase风格，和MySQL的snake_case不一样，这点在映射时要处理好。
MySQL视频
最新发布
2509_93946396的博客
11-22 251
现在回头看，当初连brew install mysql都能报错一整天的小白，现在也能熟练做分库分表方案了。视频学习最大的价值就是降低入门门槛，但想真正消化知识，还是得在本地环境多折腾。后来偶然刷到某位老师的安装教程，才发现原来从MySQL 8.0开始默认认证方式变了，视频里连修改my.ini配置文件的坑位都标得明明白白。别看时长吓人，但讲师把每个知识点都拆成10分钟左右的片段，通勤路上都能看几集。去年我跟着视频做订单管理系统项目时，曾遇到个典型问题：视频里讲师创建的复合索引查询速度很快，我的却完全没生效。
MySQL 函数
2509_94006710的博客
11-21 1037
MySQL 函数是服务器端定义的程序，可以接收输入参数，进行处理后返回一个结果值。函数的主要特点是可以在 SQL 语句中直接调用，通常用于计算、数据转换或返回某种特定的结果。与存储过程不同，函数必须返回一个值，并且可以在 SQL 语句的SELECT列表、WHERE子句、ORDER BY子句等位置使用。
MySQL案例分析
2509_93941777的博客
11-22 313
事务A先锁住了product_id=100的记录准备更新状态，同时事务B锁住了product_id=101的记录要减库存，但接着事务A试图去获取product_id=101的锁，事务B又反过来抢product_id=100的锁——典型的循环等待。这个案例给我的教训是：MySQL的锁机制远比想象中复杂，特别是在非主键索引上操作时，间隙锁容易成为性能杀手。另外还把事务隔离级别降到了READ-COMMITTED，虽然可能引发幻读，但对我们这个业务场景影响不大，毕竟促销活动都是短时爆发，数据一致性要求没那么严苛。
DBeaver连接本地MySQL、创建数据库表的基础
u013157943的博客
11-19 392
DBeaver连接本地MySQL、创建数据库表的基础
MySQL自然语言处理应用
2509_93942886的博客
11-22 317
总的来说，虽然MySQL不是专业的自然语言处理工具，但它提供的文本处理功能已经能够解决很多实际业务中的文本分析需求。对于大多数中小型项目来说，充分利用数据库层的文本处理能力，往往能在保证功能实现的同时获得更好的性能表现。最近在优化某个电商平台的订单系统时，我就遇到了这样的需求：要从用户填写的非结构化地址信息中自动提取区县级地名。在实践中我发现几个值得注意的地方：首先，MySQL的文本处理功能虽然方便，但不适合处理海量文本数据，那种情况还是应该用专门的文本搜索引擎。字符串处理：基础中的基础。
mysql-索引的数据结构是怎样的
07-26
MySQL索引的核心实现依赖于高效的数据结构，具体根据存储引擎的不同而有所差异。在InnoDB存储引擎中，索引主要采用 **B+树** 作为其数据结构。B+树是一种平衡的多路搜索树，它能够确保数据的快速检索、插入和删除操作的时间复杂度保持在对数级别 $$^3$$。 ### B+树的特点 - **叶子节点包含数据**：在InnoDB中，聚簇索引（Clustered Index）的叶子节点存储了完整的行数据，这意味着主键索引的B+树最终指向的是实际的数据存储位置。 - **非叶子节点仅包含键值**：非叶子节点用于导航，仅存储索引键值和指向子节点的指针，这使得每个节点能够容纳更多的键值，从而减少树的高度。 - **所有查询最终落到叶子节点**：无论查询是否命中某个键值，最终都会遍历到叶子节点，保证了查询性能的稳定性。 此外，MySQL也支持 **哈希索引（Hash Index）**，尤其是在Memory存储引擎中，默认使用哈希索引。哈希索引适用于等值查询（如 `WHERE column = value`），但在范围查询（如 `WHERE column > value`）中效率较低，因为哈希索引不支持排序操作 $$^1$$。 对于MyISAM存储引擎，虽然它也使用B+树作为索引结构，但与InnoDB不同的是，MyISAM的索引是 **稀疏索引（Non-clustered Index）**，即索引节点中仅包含指向数据文件中行位置的指针，而不是直接包含完整的行数据 $$^3$$。 ### 示例：创建索引的表结构 以下是一个简单的InnoDB表定义，其中 `id` 是主键，`name` 字段上建立了二级索引： ```sql CREATE TABLE `user` ( `id` int(11) unsigned NOT NULL AUTO_INCREMENT, `name` varchar(64) DEFAULT NULL, PRIMARY KEY (`id`), KEY `name` (`name`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; ``` 在此结构中，`id` 的主键索引构建了一个聚簇索引，而 `name` 字段上的索引是一个二级索引，其叶子节点中存储的是主键值，而不是完整的行数据。查询时，MySQL会通过二级索引找到主键值，再通过聚簇索引定位到实际的数据行 $$^4$$。 ### 其他索引类型 - **联合索引（Composite Index）**：多个字段组成的索引，遵循最左前缀原则，适用于多条件查询。 - **全文索引（Full-text Index）**：用于文本内容的模糊匹配，支持自然语言搜索。 - **空间索引（Spatial Index）**：用于地理数据类型（如 `GEOMETRY`），适用于空间查询。 MySQL索引的设计目标是通过高效的数据结构提升查询性能，同时在写入操作时保持良好的维护效率。不同的数据结构适用于不同的查询场景，因此选择合适的索引类型和结构对于数据库性能优化至关重要 $$^1$$。
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值