mysql如何查看索引 mysql创建索引并验证效果步骤-mysql教程-PHP中文网

查看索引使用show index和show create table；2. 创建索引用create index或alter table；3. 验证索引使用explain分析查询计划；4. 索引失效原因包括数据类型不匹配、函数操作、模糊查询以%开头、or条件复杂、优化器判断选择性低等；5. 常见索引类型有b-tree（默认，适合等值和范围查询）、哈希（仅memory引擎，适合等值查询）、全文索引（适合文本搜索）、空间索引（适合地理空间查询）；6. 索引策略应优先高选择性列、考虑查询模式、合理使用复合索引并遵循最左前缀原则、避免过度索引、利用覆盖索引减少回表；7. 索引维护包括optimize table整理碎片、analyze table更新统计信息、监控索引使用情况并删除冗余索引。

mysql如何查看索引 mysql创建索引并验证效果步骤

MySQL中查看索引主要通过SHOW INDEX或SHOW CREATE TABLE命令，创建索引则使用CREATE INDEX或ALTER TABLE，验证效果最直接的方式是使用EXPLAIN语句分析查询计划。这几个操作是数据库性能优化的基石，理解它们至关重要。

解决方案

要查看MySQL中的索引，最常用的方式是：

SHOW INDEX FROM your_table_name;

登录后复制

这条命令会列出指定表的所有索引信息，包括索引名、列名、索引类型（如BTREE）、是否唯一、基数（Cardinality）等。基数是一个很重要的指标，它表示索引列中不重复值的数量，通常越高代表索引选择性越好。

另一种查看方式是：

SHOW CREATE TABLE your_table_name;

登录后复制

这条命令会显示创建表的完整SQL语句，其中包含了所有索引的定义。这在需要快速复制表结构时特别有用。

至于创建索引，我们通常有两种主要方式：

-- 方式一：直接创建索引
CREATE INDEX idx_your_column ON your_table_name (your_column);

-- 方式二：通过ALTER TABLE添加索引
ALTER TABLE your_table_name ADD INDEX idx_another_column (another_column);

登录后复制

这里idx_your_column和idx_another_column是索引的名称，你可以根据实际情况命名，通常建议以idx_开头，后面跟上表名和列名，方便识别。如果你需要创建唯一索引，只需将INDEX替换为UNIQUE INDEX。

验证索引效果的核心工具是EXPLAIN。在执行任何你认为可能受益于索引的查询前加上EXPLAIN：

EXPLAIN SELECT * FROM your_table_name WHERE your_column = 'some_value';

登录后复制

EXPLAIN的输出会告诉你MySQL在执行查询时是如何使用索引的。关注type、key、key_len、ref、rows和Extra这几列。

type列通常是const、eq_ref、ref、range这些值时表示索引被有效利用，而ALL则意味着全表扫描，通常是需要优化的信号。
key列会显示实际使用的索引名称。
rows列预估了MySQL需要检查的行数，这个数字越小越好。
Extra列如果出现Using index（覆盖索引）或Using where（索引用于过滤），都表明索引发挥了作用。

这是一个迭代的过程：创建索引，然后用EXPLAIN验证，如果效果不佳，可能需要调整索引策略或查询语句。

为什么索引有时会失效？

这个问题，我想是很多数据库优化新手会遇到的“陷阱”。你明明建了索引，可查询就是跑不快，这感觉挺挫败的。索引失效的原因其实挺多的，有些是显而易见的，有些则比较隐蔽。

一个常见的情况是数据类型不匹配。比如你的user_id列是INT类型，但你在查询时却写成了WHERE user_id = '123'（字符串形式）。MySQL在比较时可能会进行隐式类型转换，导致无法使用索引。再比如，对索引列进行函数操作，像WHERE DATE(create_time) = '2023-01-01'，create_time即使有索引，也会因为DATE()函数而失效，因为函数会作用于每一行数据，使得索引无法直接定位。

另一个经典场景是模糊查询，特别是LIKE '%keyword'这种以通配符开头的模式。索引通常是按B-tree结构存储，它擅长处理范围查询和前缀匹配，但对于任意位置的匹配就无能为力了。如果你的业务确实需要这种全模糊匹配，可能需要考虑使用全文索引（Full-Text Index）或其他搜索技术。

OR条件有时也会让索引“犯迷糊”。当OR连接的条件中，有一个条件没有索引，或者优化器认为使用索引的成本高于全表扫描时，它就可能放弃使用索引。

还有就是优化器自身的“判断”。MySQL的查询优化器非常智能，它会根据统计信息、数据分布等因素来决定是否使用索引。如果它觉得某个索引的选择性太低（比如在一个只有几个不同值的列上建索引），或者查询返回的结果集太大（比如要返回表中90%的数据），它可能会认为全表扫描反而更快，从而放弃使用索引。我曾遇到过一个场景，一个status字段只有0和1两个值，即使建了索引，查询WHERE status = 1也经常不走索引，因为优化器觉得扫半张表和扫全表没多大区别，甚至全表扫描更直接。

此外，索引列上允许NULL值，且查询条件是IS NULL或IS NOT NULL时，索引的行为可能会比较复杂，有时会走，有时不走，这取决于优化器对数据分布的判断。总之，索引失效并不是索引“坏了”，而是它在当前查询上下文中的“效用”不足以被优化器采纳。

索引类型与选择策略

MySQL支持多种索引类型，每种都有其适用场景，理解这些差异是高效建索引的前提。

最常见的是B-Tree索引，这也是MySQL（尤其是InnoDB存储引擎）默认和最常用的索引类型。它适用于各种等值查询、范围查询、排序和分组操作。几乎所有你在数字、字符串、日期列上创建的索引，如果不特别指定，都是B-Tree索引。它的优势在于平衡树结构，能保证查询效率相对稳定，且支持前缀匹配。

然后是哈希索引（HASH），主要在Memory存储引擎中用到，InnoDB中只能用于自适应哈希索引（由MySQL内部自动创建和管理）。哈希索引的特点是查询速度极快，因为它直接通过哈希算法定位数据，时间复杂度接近O(1)。但它的缺点也很明显：不支持范围查询，不支持排序，也不支持模糊匹配。它只适用于等值查询。所以，如果你看到Memory表，哈希索引可能是一个好选择，但对于InnoDB，通常我们不直接创建哈希索引。

全文索引（FULLTEXT），顾名思义，是为文本内容搜索而设计的。当你需要在文章标题、内容等大文本字段中进行关键词搜索时，B-Tree索引往往效率低下，全文索引就能派上用场了。MySQL的全文索引支持自然语言模式、布尔模式等，但它的功能和效果与专业的搜索引擎（如Elasticsearch、Solr）相比还是有差距的，通常只适用于一些简单的文本搜索场景。

空间索引（SPATIAL），用于地理空间数据类型，如POINT、LINESTRING、POLYGON。如果你在做地图应用、LBS服务，需要查询某个区域内的点，或者计算距离，那么空间索引就是你的利器。它通常结合R-tree算法实现，能够高效处理空间关系查询。

在选择索引策略时，有几个点我个人觉得特别重要：

高选择性列优先：选择性高的列（即不重复值多的列）更适合创建索引，因为它们能更快地缩小查询范围。比如用户ID、订单号，它们的唯一性很高，非常适合做索引。
考虑查询模式：你的大部分查询是等值查询、范围查询，还是排序、分组？针对性地创建索引。如果经常需要按某个字段排序，可以考虑在该字段上创建索引。
复合索引（联合索引）：当查询条件涉及多个列时，可以考虑创建复合索引。例如INDEX (col1, col2, col3)。复合索引遵循“最左前缀原则”，即只有当查询条件包含索引的最左边列时，索引才可能被使用。比如WHERE col1 = 'A' AND col2 = 'B'会使用，但WHERE col2 = 'B'则不会。合理设计复合索引能显著减少索引数量，提高查询效率。
避免过度索引：索引不是越多越好。每个索引都会占用磁盘空间，并且在插入、更新、删除数据时，数据库需要维护这些索引，这会带来额外的开销。我见过一些系统，为了所谓的“优化”，给每个字段都加了索引，结果写操作性能急剧下降。
覆盖索引（Covering Index）：如果一个查询需要的所有列都在索引中，那么MySQL可以直接从索引中获取数据，而无需回表（即不再去数据行中查找数据），这会大大提高查询效率。EXPLAIN结果中Extra列显示Using index就是覆盖索引的标志。

选择合适的索引类型和策略，需要对业务场景和数据特性有深入的理解，并不是一蹴而就的事情。

索引维护与优化

索引建好了，也跑得不错，是不是就万事大吉了？不，索引也需要“保养”。就像汽车需要定期维护一样，数据库索引也可能因为数据变化而变得不那么高效，甚至需要调整。

一个比较常见的维护操作是重建或优化索引。当表中的数据经过大量的插入、删除、更新操作后，索引可能会出现碎片化，导致其物理存储不再连续，从而降低查询效率。这时，你可以使用OPTIMIZE TABLE命令来整理表和索引：

OPTIMIZE TABLE your_table_name;

登录后复制

OPTIMIZE TABLE会重建表和索引，消除碎片，并更新统计信息。但要注意，这个操作可能会锁定表，对于大表来说，执行时间会比较长，可能会影响线上业务。所以，通常建议在业务低峰期执行，或者考虑使用在线DDL工具（如Percona Toolkit的pt-online-schema-change）来避免长时间锁定。

另一个重要的方面是监控索引使用情况。你创建的索引真的被用到了吗？还是只是躺在那里占用空间？MySQL的performance_schema和information_schema提供了一些视图，可以帮助你了解索引的使用情况。例如，你可以查看information_schema.STATISTICS表中的Index_used列（如果你的MySQL版本支持），或者更细致地通过performance_schema.table_io_waits_summary_by_index_usage等视图来分析。

如果发现某个索引长期没有被使用，或者使用频率极低，那么它可能就是一个冗余索引。冗余索引不仅占用存储空间，还会增加写操作的开销。对于这类索引，果断地将其删除通常是一个明智的选择：

DROP INDEX idx_unused_column ON your_table_name;

登录后复制

删除索引前，务必仔细分析，确保它不是被某个不常运行但至关重要的查询所依赖。

我个人在做索引优化时，还会特别关注索引的基数（Cardinality）。SHOW INDEX FROM your_table_name命令的输出中就有这一列。基数表示索引列中不重复值的数量。理论上，基数越高，索引的选择性越好，查询效率越高。如果一个索引的基数很低（比如在一个只有“男”、“女”两个值的性别字段上），那么这个索引的作用就非常有限，甚至可能不如全表扫描。MySQL的优化器会定期更新索引的统计信息，但如果数据变化非常频繁，有时也需要手动ANALYZE TABLE your_table_name;来强制更新统计信息，确保优化器能做出最准确的判断。

最后，要记住，索引优化是一个持续的过程，它不是一次性工作。随着业务的发展、数据量的增长和查询模式的变化，原有的索引策略可能不再是最优的。定期回顾和调整索引，是保持数据库高性能的关键。

以上就是mysql如何查看索引 mysql创建索引并验证效果步骤的详细内容，更多请关注php中文网其它相关文章！