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语句

可以看到：加上组合索引后，组合索引起作用，只需查询一条符合结果的数据，效率要比单独索引高。

参考：https://w.cnblogs.com/rocker-pg/p/11635414.html