面试题：MySQL中为搜索列创建索引的合适场景有哪些

Question

在MySQL数据库里，阐述一下在哪些情况下，为搜索列创建索引能显著提升查询性能，举例说明。

Answer 1

1. 在列上进行等值查询的情况
   • 当经常在某一列上使用=进行查询时，创建索引能显著提升性能。
   • 例如，有一个users表，表结构如下：

CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(50),
    age INT,
    email VARCHAR(100)
);

• 如果我们经常执行类似这样的查询：SELECT * FROM users WHERE username = 'john';，为username列创建索引：CREATE INDEX idx_username ON users(username);，可以大大加快查询速度，因为索引可以快速定位到满足username = 'john'条件的记录，而不需要全表扫描。

2. 在列上进行范围查询的情况
   • 当使用<、>、BETWEEN等操作符进行范围查询时，索引也能提升性能。
   • 比如，对于上述users表，如果经常查询年龄在某个范围的用户：SELECT * FROM users WHERE age BETWEEN 18 AND 30;，为age列创建索引：CREATE INDEX idx_age ON users(age);，数据库可以利用索引快速定位到满足年龄范围条件的记录，而不是逐行扫描整个表。
3. 在连接条件中的列
   • 当多个表通过某一列进行连接操作时，在连接列上创建索引能提升连接查询性能。
   • 假设有orders表和customers表，orders表结构如下：

CREATE TABLE orders (
    order_id INT PRIMARY KEY AUTO_INCREMENT,
    customer_id INT,
    order_date DATE,
    amount DECIMAL(10, 2)
);

• customers表结构如下：

CREATE TABLE customers (
    customer_id INT PRIMARY KEY AUTO_INCREMENT,
    customer_name VARCHAR(50),
    address VARCHAR(100)
);

• 当执行连接查询SELECT * FROM orders JOIN customers ON orders.customer_id = customers.customer_id;时，在orders.customer_id和customers.customer_id列上分别创建索引：

CREATE INDEX idx_orders_customer_id ON orders(customer_id);
CREATE INDEX idx_customers_customer_id ON customers(customer_id);

• 这样可以加速连接操作，因为索引能快速定位到匹配的记录，减少了数据匹配时的查找时间。