面试题：MySQL性能优化之索引认知

Question

在MySQL中，索引对于性能优化至关重要。请阐述普通索引、唯一索引、主键索引在创建方式、功能特性以及对查询性能影响上的区别，并举例说明何时应使用何种索引来优化查询。

Answer 1

创建方式

• 普通索引：使用CREATE INDEX index_name ON table_name (column_name); 或在创建表时使用CREATE TABLE table_name (column1 datatype, INDEX index_name (column_name));。
• 唯一索引：使用CREATE UNIQUE INDEX index_name ON table_name (column_name); 或在创建表时使用CREATE TABLE table_name (column1 datatype, UNIQUE INDEX index_name (column_name));。
• 主键索引：在创建表时使用CREATE TABLE table_name (column1 datatype PRIMARY KEY, column2 datatype);，主键自动创建主键索引。

功能特性

• 普通索引：允许重复值，目的是加快数据的查询速度，通过索引树结构快速定位数据。
• 唯一索引：不允许索引列有重复值，但可以有NULL值（如果列允许为NULL），既保证数据唯一性，又加快查询。
• 主键索引：不允许重复值，也不允许为NULL，一个表只能有一个主键索引，是表中数据的唯一标识。

对查询性能影响

• 普通索引：加速对索引列的查找，可用于范围查询、排序等，但在查询时，可能需要扫描多个索引项来获取完整数据。
• 唯一索引：由于唯一性限制，在查询时可更快定位到具体记录，减少索引扫描范围，性能优于普通索引，尤其在精确查找时。
• 主键索引：是最严格的索引，数据以主键顺序物理存储，在基于主键的查询时，查询性能最优，能快速定位到具体行。

应用场景举例

• 普通索引：当查询条件涉及某个列，但该列数据不要求唯一时，如在employees表中，经常按department查询员工，可创建普通索引。CREATE INDEX idx_department ON employees (department);
• 唯一索引：当列数据需要保证唯一性，如users表中的email字段，创建唯一索引可保证邮箱不重复，且加速登录等查询。CREATE UNIQUE INDEX idx_email ON users (email);
• 主键索引：用于唯一标识表中每一行数据，如orders表中的order_id，基于order_id的查询性能最高。CREATE TABLE orders (order_id INT PRIMARY KEY, order_date DATE, ...);