文章目录
当建索引还是不当建索引:数据库索引的智慧选择
在数据库的世界里,索引就像是一个图书管理员,它能够帮助我们迅速找到所需的数据。但是,和现实生活中的图书管理员一样,索引并不是越多越好,有时候它们可能会变成我们查找数据的负担。那么,究竟什么时候该建立索引,什么时候又该让它们靠边站呢?让我们通过几个例子,用SQL的语境来具体讲解一下。
索引的缺点
索引最大的好处是提高查询速度,但是索引也是有缺点的,比如:
- 需要占用物理空间,数量越大,占用空间越大;
- 创建索引和维护索引要耗费时间,这种时间随着数据量的增加而增大;
- 会降低表的增删改的效率,因为每次增删改索引,B+ 树为了维护索引有序性,都需要进行动态维护。
所以,索引不是万能钥匙,它也是根据场景来使用的。
何时该建索引
- 字段有唯一性限制的,比如商品编码;
- 经常用于
WHERE
查询条件的字段,这样能够提高整个表的查询速度,如果查询条件不是一个字段,可以建立联合索引。 - 经常用于
GROUP BY
和ORDER BY
的字段,这样在查询的时候就不需要再去做一次排序了,因为我们都已经知道了建立索引之后在 B+Tree 中的记录都是排序好的。
何时不该建索引
- WHERE 条件,GROUP BY,ORDER BY 里用不到的字段,索引的价值是快速定位,如果起不到定位的字段通常是不需要创建索引的,因为索引是会占用物理空间的。
- 字段中存在大量重复数据,不需要创建索引,比如性别字段,只有男女,如果数据库表中,男女的记录分布均匀,那么无论搜索哪个值都可能得到一半的数据。在这些情况下,还不如不要索引,因为 MySQL 还有一个查询优化器,查询优化器发现某个值出现在表的数据行中的百分比很高的时候,它一般会忽略索引,进行全表扫描。
- 表数据太少的时候,不需要创建索引;
- 经常更新的字段不用创建索引,比如不要对电商项目的用户余额建立索引,因为索引字段频繁修改,由于要维护 B+Tree的有序性,那么就需要频繁的重建索引,这个过程是会影响数据库性能的。
何时该建索引举例
1. 字段有唯一性限制的
查询语句:
SELECT * FROM products WHERE product_code = 'P12345';
建立索引:
CREATE UNIQUE INDEX idx_product_code ON products (product_code);
分析:
在此情况下,由于产品编码是唯一的,创建唯一性索引能够确保查询的准确性,同时提高查询效率。
2. 经常用于 WHERE 查询条件的字段
查询语句:
SELECT * FROM products WHERE category_id = 10;
建立索引:
CREATE INDEX idx_category_id ON products (category_id);
分析:
对于经常作为查询条件的字段,创建索引能够加快查询速度,因为索引可以帮助数据库快速定位到需要查询的数据。
3. 经常用于 GROUP BY 和 ORDER BY 的字段
查询语句:
SELECT product_id, SUM(quantity) FROM sales GROUP BY product_id ORDER BY SUM(quantity) DESC;
建立索引:
CREATE INDEX idx_sales_date ON sales (sales_date);
分析:
对于需要经常进行分组和排序的字段,创建索引可以避免数据库在执行查询时进行排序操作,从而提高查询效率。
何时不该建索引举例
1. WHERE 条件,GROUP BY,ORDER BY 里用不到的字段
查询语句:
SELECT product_id, price FROM products;
不需要建立索引。
分析:
在这种情况下,由于查询中没有三个条件字段,创建索引将是浪费资源,因为索引不仅不会提高查询效率,反而会占用额外的存储空间。
2. 字段中存在大量重复数据
查询语句:
SELECT gender FROM users;
不需要建立索引。
分析:
由于 gender
字段只有两个可能的值,并且分布均匀,创建索引不会提高查询效率,反而会增加存储空间的占用。
3. 表数据太少时
查询语句:
SELECT * FROM orders;
不需要建立索引。
分析:
当表中的数据量非常少时,索引可能提供不了显著的性能提升,因为索引的维护成本可能比查询收益更高。
4. 经常更新的字段
查询语句:
UPDATE users SET balance = balance - 100 WHERE user_id = 1;
不需要建立索引。
分析:
对于经常进行更新的字段,创建索引会降低更新操作的性能,因为索引也需要维护。在这种情况下,不创建索引可以提高更新操作的性能。