多列索引生效规则

最新推荐文章于 2024-08-31 23:40:08 发布
原创 最新推荐文章于 2024-08-31 23:40:08 发布 · 2.4k 阅读 · 0 ·
CC 4.0 BY-SA版权
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。

本文解析了BTree索引常见的使用误区，例如在复合查询中仅能利用其中一个索引的情况，并详细介绍了多列索引的左前缀原则，帮助读者理解如何更高效地使用数据库索引。

btree索引的常见误区
在where条件常用的列上都加上索引
比如：where cat_id=3 and price>100 #查询第3个栏目，100以上的商品
只能用上cat_id或price索引，因为独立的索引同时只能用上1个。

多列索引生效规则
多列索引发挥作用，需要满足左前缀要求。

以index(a,b,c)为例：

语句                                    |     索引是否发挥作用
-   -   -   -   -   -   -   -   -   -   -   -   -   -   -   
where a=3                             |     是
where a=3 and b=5                     |     是
where a=3 and b=5 and c=4             |     是
where b=3                             |     否
where c=4                             |     否
where a=3 and c=4                     |     a列能用到索引,c不能
where a=3 and b>10 and c=7            |     a能,b能,c不能
where a=3 and b like 'xxx%' and c=7   |     a能,b能,c不能
MySQL进阶【四】—— MySQL索引优化实战
shehuinidaye的博客
09-24 651
示例表 CREATE TABLE `employees` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(24) NOT NULL DEFAULT '' COMMENT '姓名', `age` int(11) NOT NULL DEFAULT '0' COMMENT '年龄', `position` varchar(20) NOT NULL DEFAULT '' COMMENT '职位', `hire_time` timestamp NO
怎样优化 PostgreSQL 中对多列索引的使用？
2401_86074221的博客
07-12 1268
我们要理解其工作原理，选择合适的列和顺序，避免过度创建，结合查询语句进行优化，并定期评估和调整。假设一个表有 10 个列，我们为其中的 5 个列都创建了多列索引，那么在进行数据操作时，数据库需要同时更新这 5 个索引，这会大大降低数据操作的性能。将其包含在多列索引中，不仅增加了索引维护的成本，还降低了写入数据的效率。然而，经过一段时间的运行，发现数据库的性能并没有得到明显的提升，反而在写入数据时变得非常缓慢。所以说，选择合适的列创建多列索引，就像是在挑选精兵强将，要选那些真正能在战场上发挥作用的。
MySQL 多列索引的生效规则
weixin_30920853的博客
02-10 784
mysql中 myisam，innodb默认使用的是 Btree索引，至于btree的数据结构是怎样的都不重要，只需要知道结果，既然是索引那这个数据结构最后是排好序；就像新华字典他的目录就是按照a,b,c..这样排好序的；所以你在找东西的时候才快，比如你找 “中” 这个字的解释，你肯定就会定位到目录的 z 开头部分；组合索引可以这样理解，比如（a,b,c），abc都是排好序的，在任意一段a的下面b...
mysql 3列索引_MySQL 多列索引的生效规则
weixin_42100188的博客
01-27 397
mysql中 myisam，innodb默认使用的是 Btree索引，至于btree的数据结构是怎样的都不重要，只需要知道结果，既然是索引那这个数据结构最后是排好序；就像新华字典他的目录就是按照a,b,c..这样排好序的；所以你在找东西的时候才快，比如你找 “中” 这个字的解释，你肯定就会定位到目录的 z 开头部分；组合索引可以这样理解，比如(a,b,c)，abc都是排好序的，在任意一段a的下面b...
mysql联合索引
weixin_34320159的博客
10-19 737
命名规则：表名_字段名1、需要加索引的字段，要在where条件中2、数据量少的字段不需要加索引3、如果where条件中是OR关系，加索引不起作用4、符合最左原则 https://segmentfault.com/q/1010000003984016/a-1020000003984281 联合索引又叫复合索引。对于复合索引:Mysql从左到右的使用索引中的字段，一个查询可以只使用索引中的一部份，...
MySQL联合索引生效规则
优快云_LGY_Java的博客
12-23 1923
最近项目中一张表数量测试达到千万级别，考虑加索引，对于单列索引及联合索引（复合索引）简单学习了下，做一下笔记。 联合索引生效前提： 假设你有联合索引 idx1 (col1,col2,col3) 如果你基于 1、where col1=123; 2、where col1=123 and col2=321; 3、where col1=123 and col2=321 and col3=111; 则考虑使用这个索引。 如果你基于 1、where col2=456; 2、where col2=456 an
【MySQL】索引使用规则——（覆盖索引，单列索引，联合索引，前缀索引，SQL提示，数据分布影响，查询失效情况）
YYDsis的博客
08-31 1822
一.索引使用规则 ※.验证索引效率提升 1.覆盖索引——查询使用了索引，并且需要返回的列，在该索引中已经全部能够找到2.单列索引＆联合索引3.前缀索引——解决冗长字符串与索引问题【1】前缀索引＆索引选择性的介绍【2】前缀索引创建演示：5.SQL提示——指定某个索引/忽略索引/强制索引 6.数据分布影响——MySQL自我评估7.查询失效的几种情况【1】违背——最左前缀法则(联合索引)【2】范围查询右侧失效【3】用or分割开的条件，or后面没索引，所有索引失效【4】索引列上进行运算操作，索引
一次添加多列索引和多次添加单列索引有什么区别
06-24
多列索引|>⚠️**注意**：SQLite对多列索引的支持有限，需确保查询条件**从索引的最左列开始连续使用**才能生效[^2]。---###性能对比示例假设表`orders`有100万行数据，测试以下两种索引策略：1.**多列索引**：`...
查询优化（TTFB过长）left join索引未生效
qibulemingzi的博客
08-02 649
查询优化（TTFB过长）left join索引未生效
数据库索引的生效条件
qq_41612990的博客
08-14 2913
一、索引分为【聚集索引】和【非聚集索引】 聚集索引：字段值在磁盘上连续存储，也可以理解是栈式存储（数组）。 非聚集索引：字段值在磁盘上乱序存储，但是值的地址在磁盘上是连续存储的，即：值是堆式存储（Dictionary），值得地址是栈式存储（数组）。 二、在数据表里，可能会把多个字段设为索引，这些索引会同时出现在where里，这些索引统称为【组合索引】，但是在查询时，【组合索引】中的索引不一定会生效，常见的，需要符合以下条件 1.左侧原则：where里使用了多个索引字段时，必须先使用最早建立的那个索引
btree索引生效原则
webbc的博客
03-14 1738
btree索引的常见误区 错误1 现在有一个商品表，有cat_id类别字段，price价格字段。假设我们给cat_id和price分别加上各自的所有，那么当我们使用sql：select * from goods where cat_id = 3 and price > 100;//查询第3个栏目,100元以上的商品这句sql只能用上cat_id或price索引,因为它们两是独立的索引,同时只能用上1
7.联合索引（最左前缀原则）
热门推荐
c1776167012的博客
10-15 2万+
1）什么是联合索引？ 联合索引指的是对一张表上的多个列进行索引。也就是说，表上多个列加起来组成一个索引，供快速查询使用。 2）如何使用联合索引？ 首先，给表添加索引 创建表时给表添加： CREATE TABLE t( a int, b int, primary key(a), key idx_a_b(a,b) ) 创建表后给表添加： CREATE TABLE t( a int, b int, primary key(a) ) 给表添加索引语句： A
MySQL - 多列索引
骆驼胡杨的博客
07-22 1961
多列索引，是指在创建索引时所关联的字段不是一个字段，而是多个字段，虽然可以通过所关联的字段进行查询，但是。在上述语句中，关键字CREATEINDEX表示用来创建多列索引。
数据库索引规则
W313997791的博客
11-30 998
单索引目前为以上几种规则失效，联合索引遵循最左前缀规则，但是如果联合索引中的左侧的索引失效了，则当前索引与后续索引全部失效。即联合索引遵循之前先遵循单索引规则。
索引使用规则以及注意失效的场景
rnnf_yyds的博客
08-14 1203
一模一样的SQL语句，先后执行了两次，只是传入的字段值不同，结果查询计划是不一样的，这种现象是和数据库的数据分布有关系，如果MySQL认为我们使用索引反而比全表慢，那么就不使用索引。如果索引了多列（联合索引），要遵守最左前缀法则。最左前缀法则中指的最左边的列，是指在查询时，联合索引的最左边的字段(即是 第一个字段)必须存在，与我们编写SQL时，条件编写的先后顺序无关。id作为主键，即聚集索引，主键聚集索引，叶子节点挂载的是row（行数据），所以即使我们使用的是select *，是可以一次性直接返回数据的。
多列索引分析
baidu_36588266的博客
03-05 596
建表语句：create table rental(...primary key (rental_id),unique key rental_date(rental_date,inventory_id,customer_id),key idx_fk_inventory_id(inventory_id),key idx_fk_customer_id(costmer_id),...)
联合索引
一个普通码农的总结
07-06 486
建立规则 1.等值查询中，查询条件a,b分别查询时返回的条目比较多，但是同时查询时返回条目比价少，可以建立联合索引； 2.对于有等值查询的列和范围查询的列，等值查询的列建在前，范围查询的列建在后比较实用； 3.如果联合索引的前置列和索引单列一致，则可以用到索引，因此尽量将联合索引的前置列和常用的单列一致； 4.超过3列的索引不合适，会导致索引块过多，查询是会遍历更多的索引块； 5.索引建立
38.联合索引查询原理以及索引使用规则
qq_30635523的博客
04-06 469
联合索引查询原理以及全值匹配规则 之所以讲解联合索引，那是因为平时我们设计系统的时候一般都是设计联合索引，很少用单个字段做索引，原因之前讲过，我们还是要尽可能的让索引数量少一些，避免磁盘占用太多，增删改性能太差。 咱们有一个表是存储学生成绩的，这个表当然有id了，这个id是一个自增主键，默认就会基于他做一个聚簇索引，这个就不用多说了。 然后呢，就是包含了学生班级、学生姓名、科目名称、成绩分数...
mysql order by 多个条件的生效规则
最新发布
09-12
<think> 我们讨论了MySQL中判断字段是否为中文的方法，现在用户的新问题是关于MySQL中ORDER BY多个条件的生效规则。我需要根据用户提供的引用和之前的知识来回答。 用户提供的引用中： 引用[1]：介绍了ORDER BY的基础语法，包括单列、多列排序，以及ASC/DESC。 引用[2]：提到在子查询中的ORDER BY可能不生效，并举例说明通过子查询排序后再分组来获取最小创建时间的数据。 引用[3]：展示了一个使用FORCE INDEX来优化ORDER BY查询慢的示例。 引用[4]：指出ORDER BY中文排序错乱的问题，原因是字符集使用ISO-8859导致中文比较出现问题。 用户的问题：想了解MySQL中ORDER BY多个条件的生效规则。 回答思路： 1. 解释多列排序的基本规则：按顺序应用排序条件。 2. 说明每个排序条件如何影响结果（优先级、相同值时的后续排序）。 3. 结合引用[4]提醒注意字符集对排序的影响（尤其是中文）。 4. 结合引用[3]提到索引对多列排序的影响（索引的使用可能优化排序性能）。 5. 结合引用[2]提到在子查询中ORDER BY的注意事项（可能不生效，因为子查询的排序可能被外部查询覆盖）。 详细解答： ### MySQL中ORDER BY多个条件的生效规则 在MySQL中，使用`ORDER BY`子句对查询结果进行排序时，可以指定多个排序条件。这些条件按照从左到右的顺序逐个应用，类似于字典排序规则。具体规则如下： 1. **基础排序规则**： - 排序条件按书写顺序依次生效。 - 先按第一个排序条件排序，当第一个条件值相同时，再按第二个条件排序，依此类推。 - 每个条件可单独指定升序(`ASC`，默认)或降序(`DESC`)。 示例： ```sql SELECT * FROM employees ORDER BY department ASC, salary DESC; ``` 含义：先按部门升序排列（A到Z），同一部门的员工再按工资降序排列（高到低）[^1]。 2. **相同值处理**： - 若前一列的值不同，则后续列的排序条件不会生效。 - 若前一列的值相同，则继续应用后续列的排序条件。 3. **NULL值处理**： - 默认情况下，`NULL`值在升序排序中被视为最小值（排在最前），在降序排序中被视为最大值（排在最后）。 - 可通过`IS NULL`/`IS NOT NULL`预处理或使用`NULLS FIRST`/`NULLS LAST`（MySQL 8.0+支持）调整： ```sql -- MySQL 8.0以上支持 ORDER BY department ASC NULLS LAST, salary DESC; ``` 4. **索引对排序的影响**： - 如果排序条件与索引列顺序匹配（如联合索引`(department, salary)`），MySQL可能直接使用索引避免额外排序（`Using index`）。 - 当索引列顺序与`ORDER BY`顺序不一致或排序方向不同时（如`ASC`和`DESC`混合），可能导致无法使用索引（除非使用降序索引）。 - 在极端情况下，可强制使用索引（如引用[3]中的`FORCE INDEX`）优化性能[^3]。 5. **字符集与排序规则**： - 中文排序错乱问题（引用[4]）通常由字符集不兼容引起（如使用`latin1`而非`utf8mb4`）。 - 解决方案： ```sql -- 确保表字段使用utf8mb4字符集和中文排序规则（如utf8mb4_zh_0900_as_cs） ALTER TABLE employees MODIFY name VARCHAR(100) COLLATE utf8mb4_zh_0900_as_cs; -- 或在排序时指定排序规则 ORDER BY name COLLATE utf8mb4_zh_0900_as_cs ASC; ``` 6. **子查询中的排序限制**（引用[2]）： - 在子查询中使用`ORDER BY`时，若外部查询有`GROUP BY`，子查询的排序可能被覆盖（因为分组后顺序可能重置）。 - 若需保留子查询排序结果，需在外层查询再次排序： ```sql -- 错误：子查询排序被忽略 SELECT * FROM ( SELECT * FROM employees ORDER BY join_date DESC ) AS tmp GROUP BY department; -- 正确：先排序后分组需结合聚合函数 SELECT department, MIN(salary) FROM (SELECT * FROM employees ORDER BY join_date DESC) AS tmp GROUP BY department; -- 但注意：这样并不能保证其他列来自最新记录，需要其他方法（如使用窗口函数） ``` 7. **性能优化建议**： - 为常用排序字段创建联合索引（顺序与`ORDER BY`一致），避免全表扫描后排序（`Using filesort`）。 - 避免对长文本字段（如`TEXT`）排序，可改用数值或时间字段替代。 - 当数据量极大时，分页查询`LIMIT n, m`可能因排序全量表而变慢，可改用游标分页（`WHERE id > xxx ORDER BY id`）[^3]。 ### 总结排序生效优先级 多个排序条件的生效优先级按照从左到右的顺序递减： $$ \text{优先级}：\text{条件}_1 > \text{条件}_2 > \cdots > \text{条件}_n $$ 即： - 数据首先按条件$_1$排序。 - 当条件$_1$的值相同时，再按条件$_2$排序。 - 依此类推，直到所有条件应用完毕。 ### 示例解析 假设表`students`数据如下： | id | name | class | score | |----|------|-------|-------| | 1 | 张三 | A | 85 | | 2 | 李四 | A | 90 | | 3 | 王五 | B | 80 | | 4 | 赵六 | B | 80 | 执行： ```sql SELECT * FROM students ORDER BY class DESC, score ASC; ``` 步骤： 1. 先按`class`降序：B > A，得到： - B班：王五、赵六 - A班：张三、李四 2. 再按`score`升序排序同班级的数据： - B班：王五(80)、赵六(80) -> 顺序不变（相同则按存储顺序） - A班：张三(85)、李四(90) -> 升序排列 最终结果： | id | name | class | score | |----|------|-------|-------| | 3 | 王五 | B | 80 | | 4 | 赵六 | B | 80 | | 1 | 张三 | A | 85 | | 2 | 李四 | A | 90 | ### 注意事项 - 排序方向一致性：若索引是`(class ASC, score ASC)`，但查询为`ORDER BY class DESC, score ASC`，索引无法完全利用（仅能用`class`部分）[^3]。 - 表达式排序：支持`ORDER BY LENGTH(name)`等表达式，但无法使用索引。 §§ 相关问题 §§ 1. 如何优化MySQL多列排序的性能？ 2. MySQL中NULL值在排序中的默认行为是什么？如何调整？ 3. 在GROUP BY之后如何对分组结果进行排序？ 4. 如何解决ORDER BY中文排序乱码问题？ </think> ### MySQL ORDER BY 多个条件的生效规则详解 在 MySQL 中，使用 `ORDER BY` 指定多个排序条件时，**排序规则按照从左到右的优先级顺序生效**。以下是具体规则和注意事项： --- #### 一、核心排序规则（优先级顺序） 1. **第一排序条件**：优先按第一个列/表达式排序 2. **同级排序条件**：当第一列值相同时，按第二列排序 3. **后续条件**：依次类推，直到所有条件应用完毕 ```sql SELECT * FROM employees ORDER BY department ASC, -- 第一优先级：部门升序 salary DESC, -- 第二优先级（部门相同时）：工资降序 hire_date ASC; -- 第三优先级（部门工资相同时）：入职时间升序 ``` --- #### 二、排序方向混合规则 1. **独立方向控制**：每个条件可单独指定 `ASC`（升序，默认）或 `DESC`（降序） ```sql ORDER BY col1 DESC, col2 ASC -- 先col1降序，再col2升序 ``` 2. **混合方向索引限制**： - ✓ 有效索引：`(col1 ASC, col2 ASC)` 用于 `ORDER BY col1 ASC, col2 ASC` - ✗ 无效索引：`(col1 ASC, col2 ASC)` 用于 `ORDER BY col1 DESC, col2 DESC` - 💡 解决方案：MySQL 8.0+ 支持[降序索引](https://dev.mysql.com/doc/refman/8.0/en/descending-indexes.html) --- #### 三、NULL 值处理规则 1. **默认行为**： - `ASC` 排序：NULL 值排在最前 - `DESC` 排序：NULL 值排在最后 2. **显式控制**（MySQL 8.0+）： ```sql ORDER BY col1 NULLS LAST, col2 NULLS FIRST ``` --- #### 四、性能优化要点 1. **索引匹配原则**： - 多列排序需使用**联合索引**，且顺序必须与 `ORDER BY` 一致 - 示例：索引 `(department, salary)` 可优化 `ORDER BY department, salary` 2. **文件排序避免**： - 当索引不匹配时，触发 `Using filesort`（全表扫描+临时排序） - 解决方案：通过 `FORCE INDEX` 强制索引（如引用[3]案例）[^3] ```sql SELECT ... FORCE INDEX (your_index) ORDER BY ... ``` --- #### 五、特殊场景注意事项 1. **中文排序错乱**（引用[4]问题）： - 原因：字符集非 UTF-8（如 `latin1`） - 解决方案： ```sql ALTER TABLE table CONVERT TO utf8mb4; -- 转换字符集 ORDER BY column COLLATE utf8mb4_zh_0900_as_cs; -- 指定中文排序规则 ``` 2. **子查询排序失效**（引用[2]问题）： - 子查询中的 `ORDER BY` 可能被外层覆盖 - 解决方案：在外层查询执行最终排序[^2] --- #### 六、多条件失效案例分析 ```sql -- 案例：期望先按状态排序，再按时间排序（但实际未生效） SELECT * FROM orders ORDER BY FIELD(status, 'pending', 'processing', 'shipped'), -- 状态自定义排序 order_date DESC; -- 时间降序 ``` **失效原因**： 当 `status` 值全相同时，`order_date` 排序正常；若 `status` 不同，则优先按 `status` 排序。 --- ### 总结：多条件排序优先级模型 $$ \text{排序优先级} = \begin{cases} \text{条件}_1 & \text{最高} \\ \text{条件}_2 & \text{当条件}_1\text{值相等时生效} \\ \vdots \\ \text{条件}_n & \text{最低} \end{cases} $$ ---
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值