关于mysql表的索引数量过多问题
来源:4-1 什么是SPU?

LayLowMay
2021-04-23
老师我这样的索引会不会太过丧心病狂影响性能
DROP TABLE IF EXISTS `sys_user`; CREATE TABLE sys_user( id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT COMMENT "主键", username VARCHAR(50) NOT NULL COMMENT "用户名", `password` VARCHAR(250) NOT NULL COMMENT "密码(AES加密)", mobile INT(20) NULL COMMENT "手机号码", email VARCHAR(100) NULL COMMENT "电子邮箱", `name` VARCHAR(50) NULL COMMENT "姓名", avatar VARCHAR(2000) NULL COMMENT "用户头像", enabled TINYINT UNSIGNED NOT NULL COMMENT "是否可用: 1可用,0禁用", account_non_expired TINYINT UNSIGNED NOT NULL COMMENT "帐户没过期: 1没过期,0过期", account_non_locked TINYINT UNSIGNED NOT NULL COMMENT "帐户没锁定: 1没锁定,0锁定", credentials_non_expired TINYINT UNSIGNED NOT NULL COMMENT "密码没过期: 1没过期,0过期", using_mfa TINYINT UNSIGNED NOT NULL COMMENT "是否启用多因子认证: 1是,0否", mfa_key VARCHAR(250) NULL COMMENT "多因子验证码", create_time TIMESTAMP NOT NULL DEFAULT NOW() COMMENT "添加时间", last_update_time TIMESTAMP NOT NULL DEFAULT NOW() ON UPDATE NOW() COMMENT "最后修改时间", UNIQUE unq_username(username), UNIQUE unq_mobile(mobile), UNIQUE unq_email(email), INDEX idx_username(username), INDEX idx_mobile(mobile), INDEX idx_email(email), INDEX idx_enabled(enabled), INDEX idx_account_non_expired(account_non_expired), INDEX idx_account_non_locked(account_non_locked) ) COMMENT = "系统用户表";
写回答
1回答
-
神思者
2021-04-23
你要是业务简单的话,同一个字段UNIQUE和INDEX索引保留唯一性索引就够了,但是查询条件复杂的话,保留两种索引,将来可以修改INDEX索引的类型从BTREE变成HASH
252021-12-30
相似问题