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

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 your_table_name ADD INDEX idx_another_column (another_column);

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

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

EXPLAIN的输出会显示MySQL执行查询时使用索引的情况，需关注以下列：

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

数据类型不匹配：如user_id列是INT类型，查询时写成WHERE user_id = '123'（字符串形式），MySQL可能进行隐式类型转换，导致无法使用索引。
函数操作：对索引列进行函数操作，如WHERE DATE(create_time) = '2023-01-01'，即使create_time有索引，也会因DATE()函数而失效。
模糊查询：特别是LIKE '%keyword'这种以通配符开头的模式，索引通常无法直接定位。
OR条件：当OR连接的条件中，有一个条件没有索引，或者优化器认为使用索引的成本高于全表扫描时，可能放弃使用索引。
优化器判断：MySQL的查询优化器会根据统计信息、数据分布等因素决定是否使用索引。若索引选择性太低，或者查询返回的结果集太大，优化器可能认为全表扫描更快，从而放弃使用索引。
索引列上允许NULL值：查询条件是IS NULL或IS NOT NULL时，索引行为可能复杂，有时走，有时不走，取决于优化器对数据分布的判断。

B-Tree索引：MySQL（尤其是InnoDB存储引擎）默认和最常用的索引类型，适用于等值查询、范围查询、排序和分组操作。优势在于平衡树结构，能保证查询效率相对稳定，且支持前缀匹配。
哈希索引（HASH）：主要在Memory存储引擎中用到，InnoDB中只能用于自适应哈希索引（由MySQL内部自动创建和管理）。特点是查询速度极快，时间复杂度接近O(1)，但缺点是不支持范围查询、排序和模糊匹配，只适用于等值查询。
全文索引（FULLTEXT）：为文本内容搜索而设计，适用于文章标题、内容等大文本字段的关键词搜索。支持自然语言模式、布尔模式等，但功能和效果与专业搜索引擎相比有差距，通常只适用于简单文本搜索场景。
空间索引（SPATIAL）：用于地理空间数据类型，如POINT、LINESTRING、POLYGON。适用于地图应用、LBS服务中查询某个区域内的点或计算距离，通常结合R-tree算法实现，能够高效处理空间关系查询。

选择索引策略时需注意：

高选择性列优先：选择性高的列（不重复值多的列）更适合创建索引，如用户ID、订单号。
考虑查询模式：根据大部分查询是等值查询、范围查询，还是排序、分组，针对性地创建索引。若经常按某个字段排序，可考虑在该字段上创建索引。
复合索引（联合索引）：当查询条件涉及多个列时，可考虑创建复合索引，如INDEX (col1, col2, col3)。复合索引遵循“最左前缀原则”，只有当查询条件包含索引的最左边列时，索引才可能被使用。合理设计复合索引能显著减少索引数量，提高查询效率。
避免过度索引：索引不是越多越好，每个索引都会占用磁盘空间，且在插入、更新、删除数据时，数据库需要维护这些索引，会带来额外开销。
覆盖索引（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;来强制更新统计信息，确保优化器能做出最准确的判断。

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