Ubuntu MariaDB索引使用注意事项

Ubuntu环境下MariaDB索引使用的关键注意事项

1. 选择高区分度列创建索引

索引的核心价值在于快速定位数据，而区分度（列中不同值数量/总行数）是判断列是否适合建索引的关键指标。优先选择区分度高的列（如用户ID、订单号等），避免在区分度低的列（如性别、状态码等）上建索引——低区分度列会导致索引过滤效果差，甚至不如全表扫描。

2. 控制索引数量，避免过度索引

索引虽能提升查询速度，但会显著增加写操作（INSERT、UPDATE、DELETE）的开销（每次写操作都需要维护索引结构），同时占用更多存储空间。建议单张表的索引数量不超过5个，避免为每一列都创建单独索引——设计良好的联合索引（复合索引）比多个单列索引更高效。

3. 合理设计联合索引的列顺序

联合索引的列顺序直接影响查询效率，需遵循两个原则：

区分度优先：将区分度高的列放在联合索引的最左侧（如WHERE user_id=1 AND status=1中，user_id的区分度远高于status，应放在前面）；
长度优先：字段长度小的列放在左侧（如INT类型比VARCHAR(255)更适合放在前面），减少索引占用的存储空间，提升IO性能。

4. 利用覆盖索引减少IO操作

覆盖索引是指索引中包含了查询所需的所有列（包括WHERE、SELECT、ORDER BY等子句中的列），无需回表查询数据行。例如，若查询只需要user_id和username，可创建联合索引(user_id, username)，避免访问表数据，显著提升查询速度。

5. 避免在索引列上使用函数或模糊查询

在索引列上使用函数（如WHERE YEAR(create_time)=2025）或前导模糊查询（如WHERE username LIKE '%john%'）会导致索引失效，因为数据库无法利用索引的结构快速定位数据。若需模糊查询，建议使用后置模糊（如WHERE username LIKE 'john%'），此时索引仍可生效。

6. 定期维护索引，保持性能

随着数据的增删改，索引会逐渐碎片化，导致查询性能下降。需定期执行OPTIMIZE TABLE命令重建索引，整理表碎片；同时，监控索引的使用情况（如通过SHOW INDEX FROM table_name查看索引的Cardinality值，判断索引的选择性），删除不再使用的冗余索引。

7. 为外键列建立索引

若表之间存在外键约束，建议在外键列上创建索引。外键约束用于保证数据的参照完整性，而索引能加速关联查询（如JOIN操作）的性能，避免全表扫描。

8. 主键选择原则

InnoDB表必须要有主键，且主键的选择需遵循以下规则：

选择顺序增长的列（如自增INT或BIGINT），避免使用UUID、MD5等随机字符串（随机主键会导致数据页分裂，降低插入性能）；
不使用频繁更新的列作为主键（如update_time），避免索引频繁维护；
主键应尽量短（如INT比BIGINT更节省空间），减少索引占用的存储空间。