数据库索引优化

前言

数据库的索引是经在项目中常常使用到的，但索引是吧双刃剑，提高了查询但是也拖慢了修改的速度。

索引优化思路

原文翻译如下：

提高 SELECT 操作性能的最佳方法是在查询中测试的一个或多个列上创建索引。索引条目的作用就像指向表行的指针，允许查询快速确定哪些行与 WHERE 子句中的条件匹配，并检索这些行的其他列值。所有 MySQL 数据类型都可以建立索引。尽管为查询中可能使用的每个列创建索引可能很诱人，但不必要的索引会浪费空间，并且会浪费 MySQL 确定要使用哪些索引的时间。索引还会增加插入、更新和删除的成本，因为每个索引都必须更新。您必须找到适当的平衡点，以使用最佳索引集实现快速查询。

常见的小技巧

1. 确定查询语句中的关键字段
   • 识别查询中最频繁使用的字段,这些字段通常是需要建立索引的重点。
   • 关注WHERE、JOIN、ORDER BY、GROUP BY中涉及的字段。
2. 选择合适的索引类型
   • 单列索引: 适用于单个字段的查询。
   • 复合索引: 适用于多个字段的联合查询。
   • 全文索引: 适用于大文本字段的模糊查询。
   • 空间索引: 适用于地理位置信息的查询。
3. 优化索引结构
   • 尽量使用前缀索引,减少索引占用空间。
   • 合理设置索引列的顺序,满足查询需求。
   • 适当调整索引的填充因子,提高索引页的利用率。
4. 维护索引健康
   • 定期进行索引碎片整理,提高索引访问效率。
   • 监控索引使用情况,及时删除冗余或无用索引。
   • 根据数据变化情况,适时调整索引结构。
5. 结合执行计划优化
   • 通过查看执行计划,分析查询瓶颈所在。
   • 根据执行计划的结果,调整索引设计或查询语句。

数据量很大的情况

基本上MySQL的数据量达到了500万，就达到了一个瓶颈期。普通的索引可能已经无法对其进行一个实质性的提升。需要考虑一个业务优化。对于索引的特殊性，它加快了查询，但是拖慢了一些增删改。导致原本有一些频繁更新和查询的业务出现了影响。这就导致一种新的解决方案产生，对数据库进行一个读写分离或者说分库分表，这也是解决方案的一种。

原因是因为当你在对数据进行增删改的时候，会对数据进行上锁，也就是常说的事务隔离级别。默认是隔离级别是不可重复读，也就行级别的控制。

总结

首先要确定查询中的关键字段,选择合适的索引类型,优化索引结构,并定期维护索引健康。同时还要结合执行计划进行优化,找出查询瓶颈所在,进一步优化索引设计或查询语句。总的来说,索引优化需要结合具体的业务需求和数据特点,采取针对性的优化措施。

引用

https://dev.mysql.com/doc/refman/8.4/en/optimization-indexes.html