下面，我们来详细看看每一部分的具体操作。

🔧 索引设计优化

设计高效且合适的索引是优化查询性能的基石。

1. 联合索引与最左前缀原则 创建联合索引时，字段的顺序至关重要。必须遵循最左前缀原则，即查询条件从联合索引的最左列开始。例如，创建 (category_id, comments, views) 的联合索引：

   • 有效使用索引的查询：WHERE category_id=1 AND comments>1、WHERE category_id=1。

   • 无法使用索引的查询：如果跳过category_id，直接使用 WHERE comments>1，则该联合索引不会被使用。

2. 利用覆盖索引 覆盖索引是指一个索引包含了查询所需要的所有字段。它的好处是：

   • 避免回表：数据库引擎只需扫描索引即可获取数据，无需根据主键ID再次回表查找，减少I/O操作。

   • 提升性能：可以将随机的I/O转变为顺序的I/O，加快查询。 实现方法是在查询中只选取必要的字段，而不是使用SELECT *。

3. 注意索引选择性

   • 选择高选择性字段：索引的选择性指索引列中不同值的数量与表记录总数的比例。选择性越高（即不同值越多），索引效率越高。例如，身份证字段比性别字段更适合建索引。

   • 避免冗余与重复索引：重复索引（如 primary key(id), index(id)）和冗余索引（如 index(a,b,c) 与 index(a,b)）会增加优化器选择负担和存储空间，应避免。

4. 考虑前缀索引 对于长字符串字段（如varchar(255)），如果整个字段建立索引占用空间过大，可以考虑只对字段的前N个字符建立索引，即前缀索引（例如 KEY index(name(20))）。使用时需要平衡索引大小和查询精度。

✍️ SQL编写优化

SQL优化
1、查询语句中不要使用select *
2、尽量减少子查询，使用关联查询（left join,right join,inner join）替代
3、减少使用IN或者NOT IN ,使用exists，not exists或者关联查询语句替代
4、or 的查询尽量用 union或者union all 代替(在确认没有重复数据或者不用剔除重复数据时，union all会更好)
5、应尽量避免在 where 子句中使用!=或<>操作符，否则将引擎放弃使用索引而进行全表扫描。6、应尽量避免在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进行全表扫描，如： select id from t where num
is null 可以在num上设置默认值0，确保表中num列没有null值，然后这样查询： select id from t where num=0 

即使有了合适的索引，糟糕的SQL写法也可能导致索引失效。

1. 避免索引列操作 在WHERE子句中，应避免对索引列使用函数、计算或类型转换，这会导致索引失效，进行全表扫描。例如，WHERE YEAR(create_time) = 2024 会导致create_time上的索引失效。

2. 慎用 SELECT \**\* 查询时明确指定需要查询的字段列表**，而不是使用SELECT *。这样做：

   • 可以减少网络传输和数据加载的开销。

   • 为使用覆盖索引创造了条件。

3. LIKE 查询的使用 使用 LIKE 进行模糊查询时，前导通配符（如 %关键字）会导致索引失效。尽量使用后导的模糊查询（如 关键字%），这样索引可以有效使用。如果必须使用 %关键字%，可以考虑使用覆盖索引尝试优化。

4. JOIN 关联查询的索引 多表JOIN时，确保关联字段（ON后面的条件）上有索引。

   • 对于LEFT JOIN，索引通常建在右表的关联字段上。

   • 对于RIGHT JOIN，索引通常建在左表的关联字段上。

   • 记住一个原则：“小表驱动大表”。

🛠️ 维护与工具使用

优化是一个持续的过程，需要借助工具进行分析和定期维护。

1. 使用 EXPLAIN 分析执行计划 这是优化慢SQL的核心工具。在SQL语句前加上 EXPLAIN 关键字，可以查看MySQL的执行计划，重点关注以下几列：

   • type：表示连接类型，从优到劣常见的有 const、ref、range、index、ALL（全表扫描）。应尽量避免ALL。

   • key：表示实际使用的索引。检查是否使用了预期的索引。

   • rows：表示预估需要扫描的行数。通常行数越少越好。

   • Extra：包含额外信息。如果出现 Using filesort（文件排序）或 Using temporary（使用临时表），通常需要优化。

2. 开启慢查询日志 启用MySQL的慢查询日志功能，可以自动记录执行时间超过指定阈值（如 long_query_time）的SQL语句。通过定期分析慢查询日志（可以使用 mysqldumpslow 或 pt-query-digest 等工具），可以系统地发现和定位性能瓶颈。

3. 定期维护与索引重建 随着数据频繁增删改，索引会产生碎片，导致性能下降。可以定期执行 OPTIMIZE TABLE table_name 命令来重建索引、整理碎片。

💡 实战优化案例

假设我们有一个 orders 表，并存在这样一条慢查询：

sql

SELECT * FROM orders WHERE customer_id = 12345 ORDER BY order_date DESC LIMIT 10;

优化步骤可能如下：

1. 分析：使用 EXPLAIN 分析该SQL，发现 type 为 ALL，进行了全表扫描，且 Extra 包含 Using filesort。

2. 优化索引：考虑查询条件为 customer_id，排序字段为 order_date，可以创建一个联合索引：ALTER TABLE orders ADD INDEX idx_customer_id_order_date (customer_id, order_date);。

   • 这里将等值查询的 customer_id 放在前面，范围查询和排序的 order_date 放在后面，符合最左前缀原则且能避免 filesort。

3. 优化查询：将 SELECT * 替换为具体的字段，例如 SELECT order_id, order_amount, ...，尝试利用覆盖索引进一步减少回表。

4. 验证：再次使用 EXPLAIN 分析，确认 type 变为 ref 或 range，key 显示使用了新索引，并且 Using filesort 消失。查询速度应有显著提升。

5. SQL**优化**

   1、查询语句中不要使用select *

   2、尽量减少子查询，使用关联查询（left join,right join,inner join）替代

   3、减少使用IN或者NOT IN ,使用exists，not exists或者关联查询语句替代

   4、or 的查询尽量用 union或者union all 代替(在确认没有重复数据或者不用剔除重复数据时，union all会更好)

   5、应尽量避免在 where 子句中使用!=或<>操作符，否则将引擎放弃使用索引而进行全表扫描。6、应尽量避免在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进行全表扫描，如： select id from t where num

   is null 可以在num上设置默认值0，确保表中num列没有null值，然后这样查询： select id from t where num=0

希望这些具体的操作指南能帮助你有效地进行MySQL索引优化！数据库优化是一门实践的艺术，多使用 EXPLAIN 分析，多结合业务场景思考，你就会越来越得心应手。