MYSQL索引的建立、删除以及简单使用
一.前期数据准备
1.建表
CREATE TABLE `user` ( `uid` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(50) DEFAULT NULL, `pwd` varchar(50) DEFAULT NULL, `create_time` datetime DEFAULT NULL, `modify_time` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, `rids` varchar(15) DEFAULT NULL, `nickname` varchar(45) DEFAULT NULL, `company` varchar(15) DEFAULT NULL, PRIMARY KEY (`uid`), UNIQUE KEY `name_UNIQUE` (`name`) ) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8
2.插入数据
INSERT INTO `monitor`.`user`(`uid`, `name`, `pwd`, `create_time`, `modify_time`, `rids`, `nickname`, `company`) VALUES (1, 'rocker', 'rocker', NULL, '2019-10-08 11:05:02', '1', 'rocker', 'rocker'); INSERT INTO `monitor`.`user`(`uid`, `name`, `pwd`, `create_time`, `modify_time`, `rids`, `nickname`, `company`) VALUES (2, 'danny', 'danny', NULL, '2019-10-08 11:31:36', '2', 'rocker', 'danny'); INSERT INTO `monitor`.`user`(`uid`, `name`, `pwd`, `create_time`, `modify_time`, `rids`, `nickname`, `company`) VALUES (3, 'tom', 'tom', NULL, '2019-10-08 11:31:39', '1', 'tom', 'rocker'); INSERT INTO `monitor`.`user`(`uid`, `name`, `pwd`, `create_time`, `modify_time`, `rids`, `nickname`, `company`) VALUES (4, 'messi', 'messi', NULL, '2019-10-08 11:31:21', '2', 'messi', 'messi'); INSERT INTO `monitor`.`user`(`uid`, `name`, `pwd`, `create_time`, `modify_time`, `rids`, `nickname`, `company`) VALUES (5, 'wenger', 'wenger', NULL, '2019-10-08 11:29:38', '1', 'wenger', 'rocker'); INSERT INTO `monitor`.`user`(`uid`, `name`, `pwd`, `create_time`, `modify_time`, `rids`, `nickname`, `company`) VALUES (6, 'henry', 'henry', NULL, '2019-10-08 11:30:46', '2', 'henry', 'henry'); INSERT INTO `monitor`.`user`(`uid`, `name`, `pwd`, `create_time`, `modify_time`, `rids`, `nickname`, `company`) VALUES (7, 'ronaldo', 'ronaldo', NULL, '2019-10-08 11:30:49', '1', 'ronaldo', 'ronaldo'); INSERT INTO `monitor`.`user`(`uid`, `name`, `pwd`, `create_time`, `modify_time`, `rids`, `nickname`, `company`) VALUES (8, 'kaka', 'kaka', NULL, '2019-10-08 11:29:45', '2', 'kaka', 'rocker');
二、建立索引、删除索引
1.建立索引
create index idx_nickname on user (nickname) 或 alter table user add index idx_nickname (nickname); PS:nickname为字段名,idx_nickname为索引名,user为表名
可以看到我们刚才为【nickname】字段加的索引。
2、删除索引
alter table user drop index idx_nickname
刚才加入那条索引就删除掉了。
3、建立组合索引
alter table user add index idx_comsition (nickname,company);
三、使用
1、在没加入索引之前执行SQL语句
explain select * from user where nickname = 'rocker' and company = 'rocker';
可以看到,没有走索引,总共查询了8条数据,而表中总共也是8条数据,相当于全表扫描了。
2、在给字段【nickname】加上索引之后,运行SQL语句
alter table user add index idx_nickname (nickname);
explain select * from user where nickname = 'rocker' and company = 'rocker';
可以看出 type 由刚才的ALL提升为了ref,rows也只扫描了2行
3、如果用组合索引会怎么样?
alter table user drop index idx_nickname; 先删掉单独索引
alter table user add index idx_comsition (nickname,company); 建立组合索引
explain select * from user where nickname = 'rocker' and company = 'rocker'; 运行SQL语句
可以看到:加上组合索引后,组合索引起作用,只需查询一条符合结果的数据,效率要比单独索引高。