mysql的索引

在 MySQL 中，可以在建表语句中使用主键索引、唯一索引和普通索引来定义表的索引

主键索引： 主键索引用于唯一标识表中的每一行数据，通常用于加速根据主键值进行数据检索的操作。在建表时，可以通过 PRIMARY KEY 关键字定义主键索引。主键索引在一张表中只能有一个。

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


SHOW CREATE TABLE employees;

CREATE TABLE `employees` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(50) DEFAULT NULL,
  `age` int DEFAULT NULL,
  `email` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

唯一索引： 唯一索引用于确保表中某一列（或多列）的值唯一，可以加速根据唯一值进行数据检索的操作。在建表时，可以通过 UNIQUE 关键字定义唯一索引。

CREATE TABLE products (
    product_id INT PRIMARY KEY,
    product_name VARCHAR(100) UNIQUE,
    price DECIMAL(10, 2),
    category VARCHAR(50)
);



SHOW CREATE TABLE products;

CREATE TABLE `products` (
  `product_id` int NOT NULL,
  `product_name` varchar(100) DEFAULT NULL,
  `price` decimal(10,2) DEFAULT NULL,
  `category` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`product_id`),
  UNIQUE KEY `product_name` (`product_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

普通索引： 普通索引是最常见的索引类型，用于加速根据索引列的值进行数据检索的操作。在建表时，可以通过 INDEX 关键字定义普通索引。

CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    customer_id INT,
    order_date DATE,
    INDEX idx_customer_id (customer_id),
    INDEX idx_order_date (order_date)
);


SHOW CREATE TABLE orders;

CREATE TABLE `orders` (
  `order_id` int NOT NULL,
  `customer_id` int DEFAULT NULL,
  `order_date` date DEFAULT NULL,
  PRIMARY KEY (`order_id`),
  KEY `idx_customer_id` (`customer_id`),
  KEY `idx_order_date` (`order_date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

在上述例子中，employees 表使用了主键索引 PRIMARY KEY，products 表使用了唯一索引 UNIQUE，orders 表使用了普通索引 INDEX。根据具体的查询需求和表的设计，可以选择合适的索引类型来优化查询性能。需要注意的是，索引的创建和维护会增加写入操作的开销，因此在选择索引时需谨慎，避免过度创建不必要的索引。

---

在MySQL中，为表添加不同类型的索引可以通过ALTER TABLE语句来完成。下面是一些常见索引类型的添加示例：

1. 主键索引（Primary Key）

通常在创建表时指定，但也可以后来添加。如果主键已经存在，需要先删除原有的主键约束。

1-- 添加主键索引（假设id是主键列）
2ALTER TABLE table_name ADD PRIMARY KEY (id);

2. 唯一索引（Unique Index）

确保索引列的值唯一，但允许有NULL值（NULL可以重复）。

1-- 添加唯一索引
2ALTER TABLE table_name ADD UNIQUE INDEX index_name (column_name);

3. 普通索引（Index）

提高查询速度，但不强制唯一性。

1-- 添加普通索引
2ALTER TABLE table_name ADD INDEX index_name (column_name);

4. 全文索引（Full-text Index）

用于全文搜索，适用于大文本内容。

1-- 添加全文索引，注意MySQL的全文索引只能用于CHAR, VARCHAR, TEXT类型
2ALTER TABLE table_name ADD FULLTEXT INDEX index_name (column_name);

5. 空间索引（Spatial Index）

用于地理空间数据类型的索引，如GEOMETRY类型。

1-- 添加空间索引
2ALTER TABLE table_name ADD SPATIAL INDEX index_name (spatial_column);

6. 联合索引（Composite Index）

同时基于多个列创建索引。

1-- 添加联合索引
2ALTER TABLE table_name ADD INDEX index_name (column1, column2, ...);

注意事项：

• 在添加索引之前，应评估索引对查询性能的提升和对插入、更新、删除操作性能的影响。
• 索引的命名（如index_name）应具有描述性，便于理解和维护。
• 确保有足够的磁盘空间，索引会占用额外的存储资源。
• 根据数据分布和查询模式选择合适的索引类型，以达到最佳性能效果。

请根据实际表名、列名及需求调整上述命令。