数据库什么情况使用索引（附MYSQL示例）

1. 提高查询性能

频繁查询的列

假设有一个用户表users，经常需要根据用户名查询用户信息：

CREATE INDEX idx_username ON users(username);

-- 查询
SELECT * FROM users WHERE username = 'john_doe';

排序操作

假设有一个订单表orders，经常需要按订单日期排序：

CREATE INDEX idx_order_date ON orders(order_date);

-- 查询
SELECT * FROM orders ORDER BY order_date DESC;

聚集操作

假设有一个销售表sales，经常需要按产品进行分组统计：

CREATE INDEX idx_product_id ON sales(product_id);

-- 查询
SELECT product_id, COUNT(*) FROM sales GROUP BY product_id;

2. 支持快速数据查找

唯一值查找

假设有一个主键为id的用户表users：

CREATE UNIQUE INDEX idx_user_id ON users(id);

-- 查询
SELECT * FROM users WHERE id = 123;

范围查找

假设有一个订单表orders，需要查找某个时间范围内的订单：

CREATE INDEX idx_order_date ON orders(order_date);

-- 查询
SELECT * FROM orders WHERE order_date BETWEEN '2023-01-01' AND '2023-12-31';

3. 联接操作

外键列

假设有两个表：orders和customers，订单表orders有一个外键customer_id：

CREATE INDEX idx_customer_id ON orders(customer_id);

-- 查询
SELECT * FROM orders o JOIN customers c ON o.customer_id = c.id;

联接列

假设有两个表：employees和departments，联接列为department_id：

CREATE INDEX idx_department_id ON employees(department_id);

-- 查询
SELECT * FROM employees e JOIN departments d ON e.department_id = d.id;

4. 覆盖索引

假设有一个包含列column1、column2和column3的表example_table：

CREATE INDEX idx_covering ON example_table(column3, column1, column2);

-- 查询
SELECT column1, column2 FROM example_table WHERE column3 = 'value';

5. 全文搜索

假设有一个包含文章内容的表articles，需要进行关键词搜索：

CREATE FULLTEXT INDEX idx_content ON articles(content);

-- 查询
SELECT * FROM articles WHERE MATCH(content) AGAINST ('search keyword');

6. 复合索引

假设有一个表products，需要基于category_id和price的组合查询：

CREATE INDEX idx_category_price ON products(category_id, price);

-- 查询
SELECT * FROM products WHERE category_id = 1 AND price < 100;

7. 频繁更新的列

假设有一个包含更新时间last_update的表records，尽量避免在此列上建立索引：

-- 可能会影响性能的索引，不建议在频繁更新的列上使用
CREATE INDEX idx_last_update ON records(last_update);

-- 查询
SELECT * FROM records WHERE last_update > '2023-01-01';

8. 空间索引

假设有一个包含地理位置数据的表locations，需要进行区域查询：

CREATE SPATIAL INDEX idx_location ON locations(coordinates);

-- 查询
SELECT * FROM locations WHERE ST_Contains(coordinates, ST_GeomFromText('POLYGON((...))'));

9. 哈希索引

假设有一个键值对存储的表kv_store，使用哈希索引进行等值查找：

CREATE INDEX idx_key USING HASH ON kv_store(key);

-- 查询
SELECT * FROM kv_store WHERE key = 'some_key';

以上示例展示了不同情况下如何创建和使用索引，以优化查询性能。通过这些示例，可以更好地理解在实际开发中如何运用索引来提升数据库的查询效率。