Mysql:主键索引 唯一索引 普通索引 前缀索引

这四种索引的区别主要在于是否唯一、是否自动建立、是否只索引部分字符串1. 主键索引主键索引由PRIMARY KEY创建CREATE TABLE users ( id BIGINT PRIMARY KEY, name VARCHAR(50) );特点一张表只能有一个主键主键值必须唯一不能为NULLMySQL 会自动创建主键索引InnoDB 中主键索引通常是聚簇索引叶子节点保存完整的行数据可以把主键理解为每一行数据的唯一身份证号2. 唯一索引唯一索引要求索引列的值不能重复CREATE UNIQUE INDEX uk_email ON users(email);例如aexample.com bexample.com aexample.com -- 不允许重复特点一张表可以有多个唯一索引不允许重复值MySQL 中唯一索引通常允许多个NULL值InnoDB 中如果表已经有主键唯一索引一般是二级索引也可以通过表结构定义CREATE TABLE users ( id BIGINT PRIMARY KEY, email VARCHAR(100) UNIQUE );主键和唯一索引都保证唯一但语义不同主键这行数据的主要身份标识 唯一索引某个字段不能重复3. 普通索引普通索引也叫非唯一索引允许索引值重复CREATE INDEX idx_age ON users(age);例如多个用户都可以是18岁18 18 20 25它的主要作用是加快查询SELECT * FROM users WHERE age 18;特点一张表可以有多个普通索引允许重复值不负责保证数据唯一通常属于二级索引4. 前缀索引前缀索引只对字符串字段的前 N 个字符建立索引CREATE INDEX idx_email_prefix ON users(email(10));这表示只索引email的前 10 个字符而不是整个字段。例如abcdef123456example.com abcdef123499example.com如果只建立前 6 个字符的前缀索引它们在索引中都会表现为abcdef特点减少索引占用的磁盘空间降低索引维护成本适合很长的字符串字段前缀太短可能导致重复值很多查询效率下降查询时可能仍需要回表判断完整字符串例如SELECT * FROM users WHERE email LIKE abcdef%;可能使用前缀索引但WHERE email LIKE %example.com通常无法有效使用这种索引因为查询条件不是从字符串开头匹配前缀索引也可以和唯一索引组合但此时约束的是“前 N 个字符不能重复”CREATE UNIQUE INDEX uk_code_prefix ON products(code(8));这不代表完整的code不能重复只代表前 8 个字符不能重复对比总结类型是否唯一一张表可有几个主要作用主键索引必须唯一且非 NULL1 个唯一标识记录唯一索引不允许重复通常允许多个 NULL多个保证字段唯一并加速查询普通索引允许重复多个加速普通查询前缀索引取决于是否和 UNIQUE 组合多个只索引字符串前缀节省空间需要特别注意“前缀索引”描述的是索引范围“主键、唯一、普通”描述的是索引约束类型它们不是完全互斥的分类。比如可以有普通前缀索引也可以有唯一前缀索引