mysql 建表联合索引

基础概念

MySQL中的联合索引（也称为复合索引或多列索引）是指在多个列上创建的索引。联合索引可以显著提高多条件查询的性能，因为它允许数据库引擎在一个索引中同时查找多个列的值。

优势

1. 提高查询效率：对于多条件查询，联合索引可以减少数据库引擎需要扫描的数据量，从而提高查询速度。
2. 减少磁盘I/O操作：通过联合索引，数据库引擎可以更快地定位到所需的数据行，减少磁盘I/O操作。
3. 优化排序和分组：如果查询涉及到多个列的排序或分组，联合索引可以提供更好的性能。

类型

MySQL中的联合索引主要有以下几种类型：

1. 普通索引：最基本的索引类型，没有特殊限制。
2. 唯一索引：索引列的值必须唯一，但允许有空值。
3. 主键索引：主键列上的索引，主键列的值必须唯一且非空。
4. 全文索引：用于全文搜索的索引，适用于文本数据。

应用场景

联合索引适用于以下场景：

1. 多条件查询：当查询条件涉及多个列时，使用联合索引可以提高查询效率。
2. 排序和分组：当查询需要对多个列进行排序或分组时，联合索引可以提供更好的性能。
3. 覆盖索引：当查询的所有列都在索引中时，可以避免回表查询，提高查询效率。

示例代码

假设有一个用户表 users，包含以下列：id、name、age、city。现在需要创建一个联合索引，以提高基于 name 和 city 的查询效率。

CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50),
    age INT,
    city VARCHAR(50)
);

CREATE INDEX idx_name_city ON users(name, city);

遇到的问题及解决方法

问题1：为什么联合索引不按预期提高查询效率？

原因：

1. 查询条件顺序不匹配：联合索引的顺序很重要，查询条件必须按照索引列的顺序来使用。
2. 数据分布不均匀：如果索引列的数据分布不均匀，可能会导致索引效果不佳。
3. 查询条件使用了函数或计算：如果查询条件中使用了函数或计算，可能会导致索引失效。

解决方法：

1. 调整查询条件顺序：确保查询条件按照索引列的顺序来使用。
2. 优化数据分布：通过数据分区和分表等方式优化数据分布。
3. 避免使用函数或计算：尽量避免在查询条件中使用函数或计算。

问题2：如何选择联合索引的列？

解决方法：

1. 分析查询模式：根据实际的查询模式，选择最常用的查询条件列。
2. 考虑选择性：选择具有较高选择性的列，即列中不同值的数量较多。
3. 避免过度索引：过多的索引会增加写操作的开销，并占用更多的存储空间。

参考链接

MySQL联合索引详解

MySQL索引优化

希望以上信息对你有所帮助！