CREATE TABLE user_info ( id int(11) NOT NULL AUTO_INCREMENT COMMENT '用户ID', name varchar(20) NOT NULL COMMENT '用户名称', gender char(1) NOT NULL COMMENT '性别', PRIMARY KEY (id), KEY gender_idx (gender) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用户信息表';
以上是一个简单的用户信息表,其中包含用户ID、用户名称和性别信息。在gender字段上建立了gender_idx索引。一些开发人员会在实际使用中发现,这样建立索引并不会对查询性别的SQL语句产生明显的性能提升。
原因在于,MySQL认为,当一个字段的不同取值数量很少(如在性别中只有男和女两个取值),那么即使该字段建立了索引,MySQL也是不会使用该索引的。因为MySQL认为扫描整个表比使用索引更加高效。在有些情况下,即使有索引也不一定会用到,这就是MySQL的优化器在生产环境中自作主张做的选择。
同时,如果在性别字段中出现了错误数据(如null、未知等),那么就算你在这个字段上加了索引,查询时索引就不会被使用到。因为查询对这些不规范的数据会过滤掉使用了索引的查询结果。
虽然在性别中建立索引没有明显的性能提升,但是在实际应用中,如果数据中还有其他取值比较多的字段(如是年龄、省份等),建立索引能够极大提升查询的性能,从而优化了网站系统整体的性能。