联合索引的基本知识

本文深入讲解了数据库中联合索引的概念、创建语法及其优势。通过实例解析了如何合理设计联合索引以提升查询效率,同时探讨了不同数据操作对联合索引性能的影响。

摘要生成于 C知道 ,由 DeepSeek-R1 满血版支持, 前往体验 >

0.预备                                                                                                           

假设我们有表 user (id,name)列

  1.联合索引是个什么东西                                                                                  

  我们知道,对于表的单列(如id)数据,是可以建立索引的,对于多列(id和name组合,或者,name和id组合),也可以建立索引。联合索引,也称之为组合索引。

      先来看单列索引的逻辑结构。

      

  由此得出索引的三个特征:索引有序+高度较低+存储列值

  联合索引也满足这三个特征,但这里的逻辑图就不画了,而是以更直观的方式来展现其查找逻辑,这里应注意,联合索引的列有前后,以id列在前、name列在后为例。

     建联合索引前的情况

    

    建联合索引后的情况(先按ID排序+再按Name排序)

  

 

  2.建立联合索引的语法                                                                                      

 create index indexName on  tableName(column1,column2,...,columnN),如create idx_un_userid_username on user(id,name)

 联合索引可以建立多列(列数大于2)的索引,建议列数最多不要越过3列,超过3列,应重新设计表。

  3.联合索引的好处                                                                                            

   A:避免回表

      在执行计划中,table access by index rowid代表是回表动作。

      如在user的id列建有索引,select id from user这个不用回表,直接从索引中读取id的值,而select id,name from user中,不能返回除id列其他的值,所以必须要回表。

      如果建有了id和name列的联合索引,则可以避免回表。

      另外,建立了id和name的联合索引(id列在在前),则select id from user可以避免回表,而不用单独建立id列的单列索引。

   B:两个单列查询返回行较多,同时查返回行较少,联合索引更高效。

     如果select * from user where id=2 和select * from user where name='tom' 各自返回的行数比较多,而select * from user where id=2 and name='tom'返回的行数比较少,那么这个时候使用联合索引更加高效。

  4.什么时候该用联合索引以及如何设计组合索引更高效                              

   A:等值查询中,查询条件a返回的条目比较多,查询条件b返回的条目比较多,而同时查询a、b返回的条目比较少,那么适合建立联合索引;

   B:对于有等值查询的列和范围查询的列,等值查询的列建在前、范围查询的列建在后比较实用;

   C:如第3点A中的另外说到,如果联合索引列的前置列与索引单列一致,那么单列查询可以用到索引,这样就避免了再建单列索引,因此联合索引的前置列应尽量与单列一致;

  5.联合索引在DML时候的性能分析                                                               

   Insert:索引越多插入明显慢得多,这是因为记录必须与索引同时更新,而要维护索引那种有序排列的结构,就必须把新增的索引键值插入到特定的位置,而不是随机排放,这里就涉及到重                 组数据的动作,如果索引块存不下,如果索引块存不下,则还要涉及到扩展索引块的动作,这都需要很大的开销。

             相对于有序插入,无序插入时索引的影响更加惊人,因为有序插入时,由于插入的数据有一定的顺序,可以在准备工作后快速扩展新块和批量重组,而对于无序操作,批量则不可能。

  Delete:删除影响所有的索引,在海量数据库定位删除少量记录时,这个条件列是索引列显然是必要的,但过多的索引还是会影响明显,因为其他列的索引也要更新。在经常要删除大量记录的               时候,危害加剧。另外,delete删除索引后,索引块中的相关需要删除记录只是被打上了一个删除标志而已,并没有真正删除。

  Update:更新的影响最小,如果是更新整条记录则与delete类似,如果是修改某列时,则不会触及到其他索引列的维护。

  6.使用联合索引需要注意的地方                                                                      

   A:超过3个列的联合索引不合适,否则虽然减少了回表动作,但索引块过多,查询时就要遍历更多的索引块了;

   B:建索引动作应谨慎,因为建索引的过程会产生锁,不是行级锁,而是锁住整个表,任何该表的DML操作都将被阻止,在生产环境中的繁忙时段建索引是一件非常危险的事情;

   C:对于某段时间内,海量数据表有频繁的更新,这时可以先删除索引,插入数据,再重新建立索引来达到高效的目的。

<think>好的,我需要回答用户关于MySQL联合索引实际应用案例或示例的题。首先,我要确保理解联合索引的基本概念。联合索引指的是在多个列上创建的索引,能够优化涉及这些列的查询条件。 用户可能想了解具体的应用场景,比如哪些情况下使用联合索引比较有效。我需要回忆一下常见的例子,比如多条件查询、排序操作或者覆盖索引的情况。记得联合索引遵循最左匹配原则,所以示例中需要体现这一点。 接下来,我应该举几个实际的例子。比如电商平台的订单查询,根据用户ID和订单状态来筛选,这时候在(user_id, status)上建立联合索引可以提升查询速度。另外,在商品表中按分类和价格范围查询,联合索引(category_id, price)也会有帮助。 还要考虑排序的情况,比如根据用户ID和注册时间排序,联合索引(user_id, register_time)可以避免额外的排序操作。覆盖索引的例子,比如查询用户姓名和年龄,如果索引包含这三个字段,就能直接从索引中获取数据,减回表。 另外,需要提到MySQL 8.0的函数索引,虽然用户的是联合索引,但可以补充说明一下,比如按年份建立的索引,不过这可能属于表达式索引的范畴。 要确保每个示例都说明为什么联合索引有效,如何利用最左匹配原则,以及可能的性能提升。同时,要引用相关的资料,比如用户提供的引用[1]、[2]、[3]中的内容,特别是最左匹配原则和查询优化器的选择。 最后,检查是否有遗漏的应用场景,比如多列排序或分组操作,以及避免回表的覆盖索引。确保回答结构清晰,分点说明,每个案例都有具体的SQL示例和索引创建语句。 可能还需要提醒用户注意索引的顺序选择,根据查询条件的频率和选择性来调整列的顺序。同时,注意避免创建过多索引,以免影响写操作的性能。 总结一下,回答应包括多个实际应用案例,每个案例的详细说明、SQL示例、索引创建语句,以及引用相关的知识点。这样用户能够全面了解联合索引的实际应用,并且知道如何在自己的项目中应用这些示例。</think>### MySQL联合索引的实际应用示例 #### 1. **多条件查询优化** **场景**:电商系统中需要根据用户ID和订单状态联合查询订单。 **示例**: ```sql -- 创建联合索引 CREATE INDEX idx_user_status ON orders(user_id, status); -- 查询语句 SELECT * FROM orders WHERE user_id = 1001 AND status = 'paid'; ``` **说明**: - 该索引能同时覆盖`user_id`和`status`的查询条件,利用最左匹配原则[^2]。 - 若查询仅包含`user_id`,仍会走索引;但仅查询`status`则无法使用此索引[^3]。 --- #### 2. **范围查询与排序组合** **场景**:商品表中按分类筛选价格区间并排序。 **示例**: ```sql -- 创建联合索引 CREATE INDEX idx_category_price ON products(category_id, price); -- 查询语句 SELECT * FROM products WHERE category_id = 5 AND price BETWEEN 100 AND 500 ORDER BY price DESC; ``` **说明**: - 索引`(category_id, price)`同时支持`WHERE`条件和`ORDER BY`排序,避免额外排序操作[^1]。 - 若查询仅涉及`price`范围,索引可能不生效(需满足最左匹配原则)[^2]。 --- #### 3. **覆盖索引回表** **场景**:高频查询用户姓名和年龄时,避免回表查询。 **示例**: ```sql -- 创建覆盖索引 CREATE INDEX idx_user_info ON users(user_id, name, age); -- 查询语句 SELECT name, age FROM users WHERE user_id = 2003; ``` **说明**: - 索引包含所有查询字段(`user_id`、`name`、`age`),直接从索引中返回数据,无需访主表[^1]。 --- #### 4. **联合索引支持多列排序** **场景**:按用户ID和注册时间联合排序。 **示例**: ```sql -- 创建联合索引 CREATE INDEX idx_user_register ON users(user_id, register_time); -- 查询语句 SELECT * FROM users WHERE user_id = 3001 ORDER BY register_time DESC; ``` **说明**: - 索引`(user_id, register_time)`既支持`WHERE`条件,又支持`ORDER BY`,避免文件排序(filesort)。 --- #### 5. **函数索引联合索引结合(MySQL 8.0+)** **场景**:按订单年份和用户ID联合统计。 **示例**: ```sql -- 创建基于表达式的联合索引 CREATE INDEX idx_year_user ON orders((YEAR(order_date)), user_id); -- 查询语句 SELECT COUNT(*) FROM orders WHERE YEAR(order_date) = 2023 AND user_id = 4001; ``` **说明**: - MySQL 8.0支持函数索引,此处通过`YEAR(order_date)`和`user_id`联合优化查询[^4]。 ---
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

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

抵扣说明:

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

余额充值