1、简述
MySQL 索引是提高查询性能的重要手段，优化索引的设计和使用对数据库性能至关重要。在设计索引时，靠左原则（Leftmost Prefix Principle）是需要特别注意的。本文将详细介绍 MySQL 索引优化的关键点，并重点讲解靠左原则及其实际应用。
2、索引
索引是数据库中一种数据结构，旨在加快查询速度。常见的索引类型包括：
普通索引：加速数据的检索，允许重复值。
唯一索引：确保列中的所有值唯一，加速查询。
复合索引：由多个列组成的索引，提升多列查询的效率。
全文索引：用于快速全文搜索。
2.1 索引的优点：
加速查询：减少数据扫描的行数，从而提高查询速度。
加速排序和分组：索引可以帮助优化 ORDER BY 和 GROUP BY 操作。
2.2 索引的缺点：
占用空间：索引会增加磁盘空间的使用。
影响写性能：插入、删除、更新操作需要维护索引，可能会导致性能下降。
2.3 优化的原则
选择合适的列：优先考虑查询频繁的列作为索引列，特别是 WHERE、JOIN、ORDER BY 和 GROUP BY 中使用的列。
减少冗余索引：尽量避免创建相似功能的索引，避免索引重复。
索引覆盖：通过覆盖索引减少回表操作，在索引中包含所有查询的列。
3、靠左原则（Leftmost Prefix Principle）
靠左原则，即在使用复合索引时，查询条件必须包含索引中最左边的列才能触发索引的使用。如果查询条件没有包含最左边的列，则索引无法生效。
例如，一个包含 (A, B, C) 的复合索引，可以支持以下查询：
WHERE A = ?
WHERE A = ? AND B = ?
WHERE A = ? AND B = ? AND C = ?
但以下查询无法充分利用索引：
WHERE B = ? AND C = ? （不包含最左边的列 A）
WHERE C = ? （不包含 A 和 B）
原因：MySQL 只能从复合索引的最左列开始使用索引。如果不从最左列开始，索引将无法生效。
详细样例解析
假设有一个包含用户信息的表 users：
CREATE TABLE users (id INT AUTO_INCREMENT PRIMARY KEY,first_name VARCHAR(50),last_name VARCHAR(50),age INT,email VARCHAR(100));
为 first_name、last_name 和 age 列创建复合索引：
CREATE INDEX idx_name_age ON users(first_name, last_name, age);
根据靠左原则，以下查询能够充分利用 idx_name_age 索引：
-- 利用了索引的第一列（first_name）SELECT * FROM users WHERE first_name = 'John';-- 利用了索引的第一列和第二列（first_name 和 last_name）SELECT * FROM users WHERE first_name = 'John' AND last_name = 'Doe';-- 利用了索引的所有列（first_name, last_name, age）SELECT * FROM users WHERE first_name = 'John' AND last_name = 'Doe' AND age = 30;
以下查询无法充分利用 idx_name_age 索引：
-- 没有使用第一列（first_name），无法使用索引SELECT * FROM users WHERE last_name = 'Doe';-- 跳过了第一列（first_name）和第二列（last_name），无法使用索引SELECT * FROM users WHERE age = 30;
在这些查询中，由于没有使用索引的第一列 first_name，MySQL 无法利用 idx_name_age 索引进行优化，查询性能将受到影响。
4、索引优化
除了靠左原则，MySQL 索引优化还有许多其他技巧和注意事项，以下列举一些常见的优化策略及其示例。
4.1 单列索引与复合索引的选择
在设计索引时，需要根据查询的需求选择单列索引还是复合索引。假设有一个 orders 表，用于存储订单信息：
CREATE TABLE orders (order_id INT AUTO_INCREMENT PRIMARY KEY,user_id INT,product_id INT,order_date DATE,status VARCHAR(20));
单列索引：如果查询中常用 user_id 作为条件，可以为 user_id 创建单列索引。
CREATE INDEX idx_user_id ON orders(user_id);
该索引在以下查询中会有效：
SELECT * FROM orders WHERE user_id = 1001;
复合索引：如果查询中常用 user_id 和 order_date 作为条件，可以创建复合索引。
CREATE INDEX idx_user_date ON orders(user_id, order_date);
此复合索引不仅可以优化以下查询：
SELECT * FROM orders WHERE user_id = 1001 AND order_date = '2024-08-15';
还能优化使用 user_id 作为条件的查询：
SELECT * FROM orders WHERE user_id = 1001;
注意：复合索引可以替代单列索引，但前提是查询中符合靠左原则。
4.2 使用索引覆盖查询
索引覆盖查询 是指查询的所有数据都可以通过索引获取，避免回表操作，从而提高查询效率。在 orders 表中，如果经常查询 status 和 order_date，可以创建一个覆盖索引：
CREATE INDEX idx_status_date ON orders(status, order_date);
然后，查询只需从索引中读取数据，而不需要访问表中的数据行：
SELECT status, order_date FROM orders WHERE status = 'shipped';
由于查询所需的列已经在索引中，MySQL 可以直接从索引中获取数据，减少 I/O 操作。
4.3 避免索引失效的常见情况
有时索引可能会由于一些操作而失效，导致全表扫描。以下是一些常见情况及其优化方法：
函数操作导致索引失效
当查询条件中对索引列使用了函数，索引将无法被使用：
SELECT * FROM orders WHERE DATE(order_date) = '2024-08-15';
此查询使用了 DATE() 函数，导致索引 idx_user_date 失效。优化方法：将函数应用于常量，而不是列。
SELECT * FROM orders WHERE order_date = '2024-08-15';
隐式类型转换导致索引失效
当查询中的数据类型与索引列的数据类型不一致时，MySQL 可能会进行隐式类型转换，导致索引失效。
假设 user_id 是 INT 类型，但查询中传递的是字符串：
SELECT * FROM orders WHERE user_id = '1001'; -- 字符串类型
这种情况下，MySQL 可能会进行类型转换，导致索引失效。优化方法：确保查询中的数据类型与索引列一致。
SELECT * FROM orders WHERE user_id = 1001; -- 整数类型
前导模糊查询导致索引失效
在使用 LIKE 查询时，如果模式以 % 开头，索引将无法使用。
SELECT * FROM orders WHERE status LIKE '%shipped';
由于模式以 % 开头，索引 idx_status_date 将无法使用。优化方法：尽量避免前导 %，例如：
SELECT * FROM orders WHERE status LIKE 'shipped%';
此查询可以利用 idx_status_date 索引。
4.4 避免在高基数列上创建索引
索引在高基数列（如用户的性别字段）上的效果可能不明显，因为数据分布不均，索引无法显著减少扫描行数。假设在 users 表中，gender 列只有 M 和 F 两种值，创建索引可能不会显著提高查询性能。
CREATE INDEX idx_gender ON users(gender);
由于 gender 列的值分布均匀，即使创建索引，查询性能的提升也有限。此时可以考虑不为此类列创建索引。
4.5 利用唯一索引提高查询效率
对于需要确保唯一性的字段（如用户邮箱、订单号等），可以使用唯一索引，不仅提高查询效率，还能确保数据的一致性。假设 email 列需要唯一性约束：
CREATE UNIQUE INDEX idx_email ON users(email);
此唯一索引可以在查询时显著提高性能，例如：
SELECT * FROM users WHERE email = 'example@example.com';
同时，插入操作时，数据库会自动检查唯一性，避免重复数据。
4.6 避免重复和冗余索引
在实际开发中，避免为同一列或功能相似的列创建多个索引。冗余索引会占用存储空间，并影响写操作的性能。假设已经为 first_name 和 last_name 创建了复合索引 idx_name：
CREATE INDEX idx_name ON users(first_name, last_name);
此时，再为 first_name 创建单独的索引 idx_first_name 就属于冗余索引
CREATE INDEX idx_first_name ON users(first_name); -- 冗余索引，不推荐
因为复合索引 idx_name 已经覆盖了 first_name 列，所以不需要额外的单列索引。
4.7 慎用全局锁和表锁
全局锁或表锁可能会导致索引无法生效，影响查询性能。在大数据量操作中，可能会使用全局锁或表锁来保证数据一致性：
LOCK TABLES orders WRITE;
5、使用分析工具优化索引
MySQL 提供了一些工具可以帮助分析索引的使用情况，并提供优化建议。
EXPLAIN：可以帮助分析查询计划，查看索引是否被使用。
EXPLAIN SELECT * FROM tb_user WHERE user_id = 1001;
SHOW INDEX FROM：查看表的索引情况。
SHOW INDEX FROM users;
慢查询日志：开启 MySQL 的慢查询日志，分析执行时间较长的查询，优化相关索引。
SET GLOBAL slow_query_log = 'ON';
6、总结
MySQL 索引优化是提高数据库性能的关键，但需要根据实际情况进行合理设计。创建正确的索引可以加速查询，减少系统开销；而不合理的索引设计则会导致性能下降。以下是索引优化的关键点：
为查询频繁的列添加索引。
遵循最左前缀匹配原则，合理设计复合索引。
使用覆盖索引，减少回表查询。
避免在低基数列、函数操作中使用索引。
定期检查并删除冗余或重复的索引。
通过以上这些策略，您可以在实际项目中显著提升 MySQL 查询性能。
