mysql建立索引的核心操作是使用create index或alter table add index语句。1. create index适用于已存在的表添加索引，语法简洁明了，支持unique、fulltext、spatial等索引类型，并可指定索引列长度和索引类型（btree或hash）。2. alter table add index则常用于修改现有表结构时添加索引，与create index功能相似但更整体化。两者在实际效果上一致，选择取决于使用场景和习惯。索引的价值在于提升查询效率、加速排序分组、确保数据唯一性和优化连接操作，但也带来写入性能下降、存储空间占用和维护成本增加的问题。适合建索引的列包括频繁出现在where条件、join、order by和group by中的列，以及基数高、数据类型短小的列；应避免对低基数列、频繁更新列、小数据量表及大字段如text/blob建普通索引。评估和优化索引性能的方法包括：1. 使用explain分析sql执行计划，查看是否命中索引、扫描行数和额外信息；2. 启用慢查询日志并分析耗时sql；3. 通过show index from查看现有索引结构；4. 避免索引失效场景如like前导模糊、函数操作、隐式类型转换等；5. 优化策略包括删除冗余索引、调整复合索引顺序、使用覆盖索引、定期分析表和优化sql语句。
mysql中建立索引，核心操作是使用CREATE INDEX或ALTER TABLE ADD INDEX语句。这不仅仅是敲几行代码那么简单，它关乎数据库查询的效率，是优化系统性能的关键一步，但同时也要警惕它可能带来的写入性能损耗和存储开销。
解决方案
要为MySQL表创建索引，通常有两种主要方式，它们各有侧重，但本质都是为了让数据库更快地找到数据。
1. 使用 CREATE INDEX 语句
这种方式通常用于在已存在的表上创建新索引，或者在创建表后单独添加。它的语法相对直接：
CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name ON table_name (column1 [(Length)], column2 [(length)], ...) [using BTREE | HASH];
- UNIQUE: 创建唯一索引。这意味着索引列的值必须是唯一的，常用于确保数据完整性，比如用户ID、身份证号等。
- FULLTEXT: 创建全文索引，主要用于文本搜索，比如文章内容、产品描述等，只适用于MyISAM和InnoDB的char、VARCHAR、TEXT类型。
- SPATIAL: 创建空间索引，用于地理空间数据，需要MyISAM存储引擎且列为GEOMETRY类型。
- index_name: 你给这个索引起的名字，最好有意义，比如idx_users_name。
- table_name: 要创建索引的表名。
- column1, column2, …: 要建立索引的一个或多个列。对于字符串类型，你可以指定length来只索引前N个字符，这可以节省空间并提高性能。
- USING BTREE | HASH: 指定索引类型，默认是BTREE，这也是最常用的。HASH索引通常用于等值查询，但在范围查询和排序上表现不佳，且只适用于Memory存储引擎。
示例：
-- 为 users 表的 name 列创建普通索引 CREATE INDEX idx_users_name ON users (name); -- 为 products 表的 product_code 列创建唯一索引 CREATE UNIQUE INDEX uniq_products_code ON products (product_code); -- 为 orders 表的 order_date 和 customer_id 创建复合索引 CREATE INDEX idx_orders_date_customer ON orders (order_date, customer_id);
2. 使用 ALTER TABLE ADD INDEX 语句
这种方式更为常见，尤其是在修改现有表结构时。它与CREATE INDEX在功能上非常相似，但它是作为ALTER TABLE命令的一部分执行的。
ALTER TABLE table_name ADD [UNIQUE|FULLTEXT|SPATIAL] INDEX [index_name] (column1 [(length)], column2 [(length)], ...) [USING BTREE | HASH];
这里的所有参数含义与CREATE INDEX相同。
示例：
-- 为 customers 表的 email 列添加唯一索引 ALTER TABLE customers ADD UNIQUE INDEX uniq_customers_email (email); -- 为 books 表的 title 列添加普通索引 ALTER TABLE books ADD INDEX idx_books_title (title);
选择哪种方式，在我看来，更多是个人习惯或特定场景的偏好。ALTER TABLE在表结构变更时更具整体性，而CREATE INDEX则显得更独立。不过，两者在实际效果上并无二致。重要的是，在生产环境中进行这类操作时，务必考虑对业务的影响，尤其是在大数据量表上创建索引，这可能会是一个耗时且阻塞的操作。
为什么mysql索引如此重要？它能解决哪些实际问题？
谈到MySQL索引，我总觉得它就像是图书馆里的图书目录。没有目录，你得一本本翻找；有了目录，你就能迅速定位到想找的书。在数据库的世界里，索引扮演的正是这个“目录”的角色，它极大地提升了数据检索的效率，但凡事都有两面性，它也并非百利而无一害。
索引的核心价值体现在以下几个方面：
- 极速查询： 这是索引最直接、最显著的贡献。当你的WHERE子句中涉及到的列有了索引，MySQL不再需要全表扫描，而是通过索引快速定位到符合条件的行。想象一下，一个百万行的用户表，你要找一个特定用户名的记录，没有索引可能需要扫描百万行，而有了索引，可能只需要几次磁盘I/O就能找到，性能提升是数量级的。
- 加速排序和分组： ORDER BY和GROUP BY操作在没有索引的情况下，往往需要对大量数据进行内存排序或磁盘排序，这非常消耗资源。如果排序或分组的列恰好是索引的一部分，MySQL可以直接利用索引的有序性，避免额外的排序开销，大大加快了查询速度。
- 确保数据唯一性： UNIQUE索引不仅仅是为了查询，它更是一种强大的数据完整性约束。它能强制保证特定列（或列组合）的值在表中是唯一的，有效防止了重复数据的插入。比如，用户注册时，通过唯一索引确保用户名或邮箱不重复，这比在应用程序层面做判断更可靠、更高效。
- 优化连接操作： 在多表JOIN查询中，如果连接条件（ON子句）中的列有索引，MySQL可以更快地找到匹配的行，从而显著提高复杂查询的性能。外键约束通常也要求被引用的列有索引，这本身也是为了保证参照完整性并优化关联查询。
然而，索引也并非万能药，它有其固有的“成本”：
- 写入性能下降： 每次对表进行INSERT、UPDATE或delete操作时，MySQL不仅要修改表中的数据，还需要同步更新相关的索引结构。索引越多，更新操作的开销就越大，因为需要维护的索引B+树越多。这就像你图书馆的目录越多，每次有新书或旧书下架时，你要修改的目录页就越多。
- 占用磁盘空间： 索引本身也是数据，需要存储在磁盘上。特别是对于大型表和多个索引，索引文件可能会占用相当可观的存储空间。
- 维护成本： 索引需要适时地进行维护和优化，比如重建碎片化的索引，或者根据查询模式调整复合索引的列顺序。不合理的索引反而会拖慢系统。
所以，建立索引是一个权衡的过程。我们追求的是查询效率的最大化，同时将写入性能和存储开销控制在可接受的范围内。盲目地为所有列添加索引，往往会适得其反。
哪些列适合建立索引？哪些情况应避免？
在决定给哪些列加索引时，我通常会先问自己几个问题：这列是不是经常出现在WHERE条件里？是不是经常用来做表连接？它的值是不是很“散”，重复度不高？这些问题能帮我快速筛选出潜在的索引候选列。
适合建立索引的列：
- 频繁出现在 WHERE 子句中的列： 这是最主要的考量。比如用户ID、订单号、产品名称等，这些你经常用来筛选数据的列，绝对是索引的优先考虑对象。
- 用于 JOIN 操作的列： 也就是那些作为外键或者连接条件的列。MySQL在执行连接操作时，会利用这些列上的索引来快速匹配不同表中的行。
- 用于 ORDER BY 和 GROUP BY 操作的列： 如果查询结果需要按特定列排序或分组，且该列有索引，MySQL可以直接利用索引的有序性，避免额外的排序开销。
- 基数（Cardinality）高的列： “基数”指的是列中不重复值的数量。基数越高，索引的效果越好。例如，身份证号、邮箱地址、手机号等，它们的值几乎都是唯一的，非常适合建立索引。想象一下，如果一个列只有“男”和“女”两个值，那么索引的区分度就很低，MySQL可能觉得还不如全表扫描来得快。
- 短字符串或整数类型： 索引的存储和比较效率与数据类型和长度密切相关。整数类型通常比字符串类型更小、比较更快。对于字符串，如果只索引前缀就能满足查询需求（比如搜索产品名，通常前几个字就能确定范围），那么可以考虑只索引部分长度，这能节省空间并提高性能。
应避免或谨慎建立索引的情况：
- 基数非常低的列： 就像前面提到的“性别”或“状态”（如“启用/禁用”），这种只有少数几个固定值的列，即使有索引，MySQL也可能选择全表扫描，因为索引带来的额外查找开销可能比直接扫描全表更大。
- 频繁更新的列： 如果一个列的值经常发生变化，那么每次更新都需要同时更新索引。这会增加UPDATE操作的开销，尤其是在高并发写入的场景下，可能会成为性能瓶颈。
- 数据量非常小的表： 对于只有几十、几百行的表，全表扫描的速度可能比通过索引查找还要快。因为索引查找也需要额外的I/O操作来读取索引页。在这种情况下，索引反而成了累赘。
- TEXT 或 BLOB 类型的大字段： 这类字段存储大量文本或二进制数据，直接对其建立普通索引意义不大，且会占用大量存储空间。如果确实需要对这类字段进行文本搜索，应考虑使用FULLTEXT索引，但其适用场景和性能特点与普通索引有所不同。
- 过多的索引： 索引并非越多越好。每个索引都会带来额外的存储和写入开销。过多的索引不仅会拖慢写入速度，还可能让查询优化器在选择索引时“犯迷糊”，反而选错了索引，甚至不使用索引。我的经验是，保持索引的数量精简，只在真正需要的地方建立。
在实际工作中，我发现一个常见的误区是，很多人会把所有WHERE子句中出现的列都加上索引，却忽略了这些列的基数和更新频率。有时候，一个精心设计的复合索引，比多个单列索引更能解决问题。
如何评估和优化现有MySQL索引的性能？
索引建好了，是不是就万事大吉了？当然不是。就像汽车需要定期保养，索引也需要“体检”和“优化”。我常用的方法是结合EXPLAIN、慢查询日志，并辅以一些数据库命令来评估索引的效果，然后针对性地调整。
1. 使用 EXPLAIN 分析SQL执行计划
EXPLAIN是我在优化SQL时最离不开的工具。它能告诉你MySQL是如何执行你的查询的，是否使用了索引，使用了哪个索引，以及扫描了多少行数据。
EXPLAIN select * FROM users WHERE username = 'john_doe';
执行后，你会看到一个表格，其中几个关键列需要特别关注：
- type： 这是最重要的指标之一，表示连接类型。
- key： 实际使用的索引名称。如果这里是NULL，那说明没用上索引。
- key_len： 使用的索引字节长度，可以用来判断复合索引中使用了多少列。
- rows： MySQL估计需要扫描的行数。这个值越小越好。
- Extra： 额外信息，比如Using filesort（需要额外排序，通常表示索引没覆盖排序需求）、Using temporary（需要临时表，通常表示GROUP BY或DISTINCT没用上索引）、Using index（覆盖索引，查询的所有列都在索引中，无需回表，非常高效）。
通过EXPLAIN，我能直观地看到哪些查询没有用到索引，或者用得不好，进而去思考如何调整索引或sql语句。
2. 慢查询日志（Slow Query Log）
MySQL的慢查询日志会记录执行时间超过long_query_time阈值的SQL语句。这是发现性能瓶颈的“雷达”。
- 启用慢查询日志： 在my.cnf或my.ini中配置slow_query_log = 1和long_query_time = 1（表示记录执行时间超过1秒的查询）。
- 分析日志： 日志中会记录SQL语句、执行时间、锁定时间、发送给客户端的行数、扫描的行数等信息。你可以使用mysqldumpslow工具来分析日志，找出出现频率高、执行时间长的SQL。
找到慢查询后，我通常会把它们拿到EXPLAIN里逐一分析，找出索引的问题。
3. 查看现有索引：SHOW INDEX FROM table_name
这个命令可以让你一览表上所有已存在的索引，包括它们的名称、类型、涉及的列、是否唯一等。
SHOW INDEX FROM users;
这有助于我检查是否存在冗余索引（比如在一个复合索引idx_a_b上，又单独建了idx_a，如果idx_a_b能满足idx_a的需求，那么idx_a可能就是冗余的），或者是否有遗漏的索引。
4. 索引失效的常见场景
有时候明明有索引，EXPLAIN却显示没用上，这往往是索引失效了。我遇到过不少这类情况：
- LIKE ‘%keyword’： 前缀模糊匹配（%在前面）会导致索引失效，因为索引是按顺序存储的。如果%在后面（’keyword%’），则可以使用索引。
- 对索引列进行函数操作： 比如WHERE DATE(create_time) = ‘2023-01-01’，create_time上的索引会失效，因为函数DATE()改变了列的值。正确的做法是WHERE create_time >= ‘2023-01-01’ AND create_time
- 隐式类型转换： 如果查询条件的数据类型与索引列的数据类型不匹配，MySQL可能会进行隐式转换，导致索引失效。例如，索引列是int，但查询条件是字符串’123’。
- OR 条件： 某些情况下，OR条件可能会导致索引失效，尤其是在OR连接的两个条件涉及不同列且无法同时使用索引时。
- NOT IN 或 （不等于）： 这些操作通常也难以利用索引，因为它们需要扫描大量不符合条件的数据。
5. 优化策略
- 删除冗余和不常用的索引： 就像我前面说的，索引不是越多越好。通过EXPLAIN和慢查询日志，识别出那些几乎不被使用或效果不佳的索引，果断删除它们，可以减少写入开销和存储空间。
- 调整复合索引的列顺序： 复合索引的列顺序非常重要，遵循“最左前缀原则”。如果你的查询经常用到col1和col2，那么INDEX(col1, col2)比INDEX(col2, col1)更可能被利用。通常把区分度高、使用频率高的列放在前面。
- 使用覆盖索引（Covering Index）： 如果一个索引包含了查询所需的所有列，那么MySQL可以直接从索引中获取数据，而无需回表（即无需访问实际的数据行）。这能显著减少I/O操作。EXPLAIN结果中的Extra列显示Using index就是覆盖索引的标志。
- 定期分析表： ANALYZE TABLE table_name; 命令可以帮助MySQL收集关于表和索引的统计信息，这些信息对于查询优化器选择最佳执行计划至关重要。
- 优化SQL语句： 很多时候，索引没问题，是SQL语句写得不够优化。比如避免全表扫描、减少SELECT *、使用LIMIT限制结果集大小等。
总之，索引的优化是一个持续的过程，需要结合业务场景、数据量、查询模式进行动态调整。没有一劳永逸的方案，只有不断地分析、测试、迭代。
