mysql如何选择索引 mysql创建高效索引的策略分析

创建高效mysql索引的核心在于理解数据、查询模式和系统负载，并基于查询优化而非盲目添加。2. 关键策略包括：关注where、join、order by和group by子句中的列，优先为高选择性的列建立索引。3. 选择合适的索引类型，如b-tree适用于等值和范围查询，前缀索引适合长字符串列，覆盖索引避免回表提高性能，联合索引需遵循最左前缀原则。4. 避免常见误区，如过度索引、在索引列上使用函数、or条件导致索引失效、小表不必要的索引。5. 使用explain工具分析索引有效性，重点关注type（all/index/range/ref/eq_ref/const）、key（实际使用的索引）、rows（扫描行数）、extra（using filesort/using temporary/using index）。6. 设计复合索引时应将高频查询的列放在前面，结合选择性、排序和分组需求，并考虑覆盖索引的可能性。7. 覆盖索引适用于查询列较少且可被索引包含、涉及大量行访问代价高的场景，其优势在于减少i/o、避免回表、提高缓存命中率、降低cpu开销、避免文件排序和临时表。

在MySQL中选择和创建高效索引，核心在于深刻理解你的数据、查询模式以及系统负载，然后有策略地应用索引原则。这从来不是一蹴而就的事情，更像是一个需要不断观察、测试和优化的迭代过程。说白了，就是要把索引这把双刃剑用好，既能加速查询，又不会拖慢写入，甚至占用过多存储空间。

解决方案

要创建高效的MySQL索引，我们首先得跳出“加个索引就好了”的思维定式，转而思考：这个索引究竟是为了解决什么问题？

在我看来，最根本的策略是：基于查询优化，而非盲目添加。

1. 理解你的查询：

   
   • WHERE子句：这是索引最常发挥作用的地方。如果你的查询经常在某个或某几个列上进行过滤，那么这些列就是索引的候选者。
   • JOIN子句：连接条件中的列是另一个关键点。在ON子句中涉及的列，尤其是在被连接表上的列，通常需要索引。
   • ORDER BY和GROUP BY子句：如果查询结果需要排序或分组，且这些操作很耗时，那么在这些列上建立索引可以帮助MySQL避免文件排序（Using filesort）和创建临时表（Using temporary），这通常是性能瓶颈的重灾区。
   • 选择性：这是个很重要的概念。如果一个列的值重复率很高（比如性别列），那么在这个列上单独建索引的效率往往不高。索引的价值在于能快速缩小扫描范围。理想情况下，一个索引能将需要扫描的行数减少到总行数的10%以下，甚至更少。

2. 选择合适的索引类型和策略：

   • B-Tree索引：这是MySQL最常用，也是默认的索引类型，适用于等值查询、范围查询、排序和分组。几乎你日常遇到的所有索引需求，B-Tree都能搞定。
   • 前缀索引：当字符串列很长时，可以考虑只对列的前N个字符建立索引。这能显著减小索引大小，提高查询效率，但代价是只能用于前缀匹配（LIKE 'prefix%'）或等值查询，不能用于后缀或中间匹配。你需要权衡选择性和索引大小。
   • 覆盖索引（Covering Index）：如果一个查询所需的所有列都包含在索引中，那么MySQL就不需要回表查询（即访问实际的数据行）。这能大幅提升查询性能，特别是对于IO密集型操作。这是一种非常高效的优化手段，在我看来，很多时候它比单纯的WHERE条件优化更有价值。
   • 联合索引（Composite Index）：当查询涉及多个列时，考虑创建联合索引。但这里的门道在于列的顺序。通常，将选择性最高的列放在最前面，或者将最常用于WHERE子句的列放在最前面，遵循“最左前缀原则”。

3. 避免常见的索引误区：

   • 过度索引：索引不是越多越好。每个索引都会增加写入操作（INSERT, UPDATE, DELETE）的开销，因为数据修改时索引也需要同步更新。同时，过多的索引会占用更多磁盘空间，并可能导致优化器选择错误索引。
   • 索引列上进行函数操作：如果在索引列上使用了函数（如LEFT(column, 5)、YEAR(date_column)），或者进行了隐式类型转换，那么索引很可能失效。MySQL将不得不进行全表扫描。
   • OR条件与索引：在WHERE子句中使用OR连接多个条件时，如果每个条件都能使用不同的索引，MySQL可能无法有效利用这些索引，或者只能利用其中一个。有时，拆分成多个UNION查询可能更优。
   • 小表不加索引：对于行数非常少（比如几百行）的表，全表扫描可能比走索引更快，因为索引查找本身也有开销。

4. 利用EXPLAIN工具：

   • 这是MySQL自带的“透视镜”，可以让你看到查询是如何执行的，有没有用到索引，以及用得好不好。理解EXPLAIN的输出，特别是type、key、rows和Extra字段，是优化索引的关键。

在MySQL中，如何判断一个索引是否真的有效？

判断一个MySQL索引是否有效，最直接且权威的方式就是使用EXPLAIN命令来分析你的SQL查询语句。这就像给你的查询做一次“体检”，看看它到底是怎么跑的。

当你执行EXPLAIN SELECT ... FROM table WHERE ...时，会得到一张表格，其中几个关键列需要我们重点关注：

1. type列：

   • ALL: 这是最差的情况，表示全表扫描。如果你的查询结果是ALL，那么索引很可能没有生效，或者根本没有合适的索引。
   • index: 表示全索引扫描。虽然比ALL好，但仍然扫描了整个索引。如果索引很小，这可能可以接受，但对于大索引来说，效率依然不高。
   • range: 范围扫描，比如WHERE id > 100 AND id < 200。这是比较理想的情况，表示索引被有效利用来缩小了扫描范围。
   • ref: 表示非唯一性索引扫描，通常用于等值查询，且可能找到多行。效率不错。
   • eq_ref: 通常用于JOIN操作，表示前一个表的每一行，在当前表中只有一行匹配。这是连接查询中最好的类型之一。
   • const, system: 这是最好的情况，表示查询优化器直接将查询转换为一个常量。通常发生在查询主键或唯一索引的等值条件时，且结果只有一行。
   • 我的经验是，能达到range、ref、eq_ref，甚至const，说明索引基本是有效的。 如果是ALL或index，那就得好好审视了。

2. key列：

   • 显示MySQL实际使用的索引名称。如果这里是NULL，那显然索引没被用上。

3. rows列：

   • MySQL估计的需要扫描的行数。这个值越小越好。它直接反映了索引的过滤能力。

4. Extra列：

   • 这个列提供了额外的信息，非常重要。
   • Using filesort: 表示MySQL需要对结果进行外部排序，通常意味着没有合适的索引来满足ORDER BY子句，性能会受影响。
   • Using temporary: 表示MySQL需要创建临时表来处理查询，通常发生在GROUP BY或DISTINCT操作没有合适索引时，或者复杂的子查询。这通常是严重的性能瓶颈。
   • Using index: 这是一个非常好的标志，表示查询所需的所有数据都可以在索引中找到，不需要回表查询（即使用了覆盖索引）。
   • Using where: 表示MySQL将使用WHERE子句来过滤结果。这本身不是坏事，但如果同时出现Using filesort或Using temporary，就可能需要进一步优化。

除了EXPLAIN，你还可以通过监控MySQL的慢查询日志（Slow Query Log）来发现那些执行时间过长的查询。对这些查询进行EXPLAIN分析，并根据分析结果调整索引，是持续优化性能的有效闭环。

复合索引的列顺序应该如何设计才能最大化其效率？

设计复合索引（也称联合索引）的列顺序，是索引优化中最需要“动脑筋”的地方，因为它直接关系到索引的适用性和效率。核心原则就是那个经典的“最左前缀原则”。

简单来说，如果你的复合索引是(col1, col2, col3)，那么这个索引可以用于以下几种查询：

• col1
• col1, col2
• col1, col2, col3

但它不能直接用于col2、col3，或者col2, col3的查询（除非有其他索引或者优化器能找到特殊路径）。这是因为B-Tree索引的数据是按照从左到右的顺序进行排序的。

那么，如何设计列顺序才能最大化效率呢？

讯飞开放平台

科大讯飞推出的以语音交互技术为核心的AI开放平台

152

查看详情

1. 将最常用于WHERE子句的列放在最前面：

   • 如果你的查询经常使用WHERE col1 = 'value'，那么col1就应该放在复合索引的最左边。
   • 如果查询经常是WHERE col1 = 'value1' AND col2 = 'value2'，那么(col1, col2)的顺序通常是好的。

2. 考虑列的选择性：

   • 在满足查询条件的前提下，通常建议将选择性最高的列放在复合索引的最前面。选择性高意味着该列的值重复率低，能更快地缩小查询范围。例如，一个用户ID列通常比一个状态列（如active或inactive）的选择性高得多。
   • 但要注意，这并不是绝对的。如果你的查询总是先过滤一个低选择性的列，再过滤高选择性的列，那么即使低选择性列在前，索引也能发挥作用。例如，WHERE status = 'active' AND user_id = 123，如果status在前，索引(status, user_id)依然有效。

3. 满足ORDER BY和GROUP BY的需求：

   • 如果你的查询经常需要对结果进行排序或分组，并且这些列与WHERE子句的列有关联，那么将它们也纳入复合索引并考虑顺序就非常关键了。
   • 例如，查询是WHERE col1 = 'value' ORDER BY col2，那么建立索引(col1, col2)可以同时满足WHERE条件和ORDER BY的排序需求，避免Using filesort。
   • 如果查询是WHERE col1 = 'value' ORDER BY col2 DESC，那么索引(col1, col2 DESC)（如果支持的话，MySQL 8.0支持降序索引）或者(col1, col2)也可以利用。

4. 考虑覆盖索引的可能性：

   • 如果你希望实现覆盖索引，那么查询中所有需要返回的列都应该包含在复合索引中。这些列通常放在索引的末尾，因为它们不需要参与排序或过滤，只是为了避免回表。例如，SELECT col1, col2, col3 FROM table WHERE col1 = 'value'，如果索引是(col1, col2, col3)，那么这就是一个覆盖索引。

举个例子： 假设你有一个orders表，包含user_id, order_status, order_date, amount等列。

• 场景1：最常见的查询是根据user_id查找某个用户的订单，并按order_date排序： SELECT * FROM orders WHERE user_id = 123 ORDER BY order_date DESC;推荐索引：(user_id, order_date)。这个索引可以先通过user_id快速定位，然后利用order_date的顺序避免文件排序。

• 场景2：查询某个用户的所有已完成订单： SELECT * FROM orders WHERE user_id = 123 AND order_status = 'completed';推荐索引：(user_id, order_status)。user_id通常选择性更高，放在前面更合理。

• 场景3：查询某个日期区间内所有已完成的订单，并返回订单ID和金额： SELECT order_id, amount FROM orders WHERE order_date BETWEEN '2023-01-01' AND '2023-01-31' AND order_status = 'completed';推荐索引：(order_date, order_status, order_id, amount)。这里order_date是范围查询，放在前面。order_status是等值查询。而order_id和amount可以作为覆盖索引的列，避免回表。

总的来说，设计复合索引的顺序是一个艺术与科学的结合，你需要深入了解你的业务查询模式，并结合EXPLAIN进行反复测试和调整。没有一劳永逸的方案，只有最适合当前业务负载的方案。

什么时候应该考虑使用覆盖索引，它能带来哪些性能提升？

覆盖索引（Covering Index），在我看来，是MySQL索引优化中的“高级技巧”之一，它能带来非常显著的性能提升。它的核心思想很简单：如果一个查询所需的所有列（包括SELECT列表中的列，以及WHERE、ORDER BY、GROUP BY等子句中涉及的列）都包含在同一个索引中，那么这个索引就是覆盖索引。

当MySQL能够使用覆盖索引时，它就不需要再回到主数据文件（即表数据）去查找额外的列数据。这种“不用回表”的特性，正是覆盖索引性能提升的关键。

何时应该考虑使用覆盖索引？

1. 查询的SELECT列表只包含少量列，且这些列可以被现有或新建的索引包含。

   • 例如，你只需要查询某个用户的email和username，而你有一个包含user_id, email, username的索引。 SELECT email, username FROM users WHERE user_id = 123; 如果索引是(user_id, email, username)，那么这个查询就可以完全通过索引来完成，而不需要访问实际的数据行。

2. 查询的WHERE、ORDER BY、GROUP BY子句中的列，加上SELECT列表中的列，共同构成了可以被索引覆盖的集合。

   • 例如，你需要查询某个状态下，最近100个订单的order_id和order_date，并按日期倒序排列。 SELECT order_id, order_date FROM orders WHERE order_status = 'pending' ORDER BY order_date DESC LIMIT 100; 如果你的索引是(order_status, order_date, order_id)，那么这个查询就可以是覆盖索引。

3. 当查询涉及大量行，且这些行的数据文件访问（回表）代价很高时。

   • 例如，在大型表中进行范围查询，如果需要回表，每次回表都是一次随机IO，这在机械硬盘上尤为昂贵。覆盖索引能够将随机IO转换为顺序IO（读取索引），或者完全避免IO到数据文件，从而大幅提升性能。

4. 当你的查询经常出现Using index在EXPLAIN的Extra列中时，这表明你的查询已经在使用覆盖索引了，或者说，你有潜力通过调整索引来使其成为覆盖索引。

它能带来哪些性能提升？

1. 减少I/O操作：这是最显著的优势。

   • 避免回表（Row Lookup）：传统的非聚集索引查询，在找到符合条件的索引条目后，还需要根据索引条目中存储的主键值（或行指针）回到主数据文件去查找完整的行数据。这个“回表”操作是随机I/O，非常耗时。覆盖索引则完全避免了这一步，所有数据都从索引中获取，大大减少了磁盘I/O。
   • 索引通常比数据行小：索引通常只包含部分列的数据，并且是紧凑存储的。这意味着相同数量的I/O操作，可以从索引中读取更多的数据，或者说，读取相同数量的数据，需要的I/O操作更少。

2. 提高缓存命中率：

   • 由于索引通常比数据表小得多，并且查询所需的所有数据都在索引中，因此索引页更容易被完整地缓存到MySQL的缓冲池（Buffer Pool）中。这意味着后续的查询可以直接从内存中获取数据，而不需要访问磁盘，从而大幅提升查询速度。

3. 减少CPU开销：

   • 减少了回表操作，也意味着减少了MySQL服务器在处理数据行、解析数据、组装结果集等方面的CPU开销。

4. 避免文件排序和临时表：

   • 如果索引的列顺序恰好与ORDER BY或GROUP BY的列顺序一致，并且查询所需的列都在这个索引中，那么MySQL就可以直接利用索引的有序性来满足排序或分组需求，从而避免了昂贵的文件排序（Using filesort）和创建临时表（Using temporary）操作。

当然，覆盖索引也不是万能药。它的缺点是，索引会变大，因为需要包含更多的列。这意味着写入操作（INSERT, UPDATE, DELETE）的开销会增加，并且会占用更多的磁盘空间。所以，是否使用覆盖索引，仍然需要权衡查询性能提升与写入性能下降以及存储成本之间的关系。但对于那些高频、对性能要求极高的读操作，覆盖索引绝对是值得投入的优化手段。

以上就是mysql如何选择索引 mysql创建高效索引的策略分析的详细内容，更多请关注php中文网其它相关文章！