编辑代码

CREATE DATABASE test;
use test;
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;
/*
INSERT INTO user(uid, name, pwd, create_time, modify_time, rids, nickname, company) VALUES (1, 'rocker', 'rocker', DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i:%s'), '2021-02-26 11:05:02', '1', 'rocker', 'rocker');
*/
INSERT INTO user(uid, name, pwd, create_time, modify_time, rids, nickname, company) VALUES (1, 'rocker', 'rocker', NULL, '2021-02-26 11:05:02', '1', 'rocker', 'rocker');
INSERT INTO user(uid, name, pwd, create_time, modify_time, rids, nickname, company) VALUES (2, 'danny', 'danny', NULL, '2021-02-26 11:31:36', '2', 'rocker', 'danny');
INSERT INTO user(uid, name, pwd, create_time, modify_time, rids, nickname, company) VALUES (3, 'tom', 'tom', NULL, '2021-02-26 11:31:39', '1', 'tom', 'rocker');
INSERT INTO user(uid, name, pwd, create_time, modify_time, rids, nickname, company) VALUES (4, 'messi', 'messi', NULL, '2021-02-26 11:31:21', '2', 'messi', 'messi');
INSERT INTO user(uid, name, pwd, create_time, modify_time, rids, nickname, company) VALUES (5, 'wenger', 'wenger', NULL, '2021-02-26 11:29:38', '1', 'wenger', 'rocker');
INSERT INTO user(uid, name, pwd, create_time, modify_time, rids, nickname, company) VALUES (6, 'henry', 'henry', NULL, '2021-02-26 11:30:46', '2', 'henry', 'henry');
INSERT INTO user(uid, name, pwd, create_time, modify_time, rids, nickname, company) VALUES (7, 'ronaldo', 'ronaldo', NULL, '2021-02-26 11:30:49', '1', 'ronaldo', 'ronaldo');
INSERT INTO user(uid, name, pwd, create_time, modify_time, rids, nickname, company) VALUES (8, 'kaka', 'kaka', NULL, '2021-02-26 11:29:45', '2', 'kaka', 'rocker');

CREATE TABLE account (
  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;

INSERT INTO account(uid, name, pwd, create_time, modify_time, rids, nickname, company) VALUES (5, 'rocker', 'rocker', NULL, '2021-02-26 11:05:02', '1', 'rocker', 'rocker');
INSERT INTO account(uid, name, pwd, create_time, modify_time, rids, nickname, company) VALUES (6, 'danny', 'danny', NULL, '2021-02-26 11:31:36', '2', 'rocker', 'danny');
INSERT INTO account(uid, name, pwd, create_time, modify_time, rids, nickname, company) VALUES (7, 'tom', 'tom', NULL, '2021-02-26 11:31:39', '1', 'tom', 'rocker');
INSERT INTO account(uid, name, pwd, create_time, modify_time, rids, nickname, company) VALUES (8, 'messi', 'messi', NULL, '2021-02-26 11:31:21', '2', 'messi', 'messi');
INSERT INTO account(uid, name, pwd, create_time, modify_time, rids, nickname, company) VALUES (9, 'wenger', 'wenger', NULL, '2021-02-26 11:29:38', '1', 'wenger', 'rocker');
INSERT INTO account(uid, name, pwd, create_time, modify_time, rids, nickname, company) VALUES (10, 'henry', 'henry', NULL, '2021-02-26 11:30:46', '2', 'henry', 'henry');
INSERT INTO account(uid, name, pwd, create_time, modify_time, rids, nickname, company) VALUES (11, 'ronaldo', 'ronaldo', NULL, '2021-02-26 11:30:49', '1', 'ronaldo', 'ronaldo');
INSERT INTO account(uid, name, pwd, create_time, modify_time, rids, nickname, company) VALUES (12, 'kaka', 'kaka', NULL, '2021-02-26 11:29:45', '2', 'kaka', 'rocker');






SELECT * FROM user;
select '不用索引:' as ' ';
explain select * from user where nickname = 'rocker' and company = 'rocker';
select '';
explain select * from user where company = 'rocker' or nickname = 'rocker';

alter table user add index idx_nickname (nickname);
alter table user add index idx_company (company);

select '用索引:' as ' ';
explain select * from user where company = 'rocker' and nickname = 'rocker';
select ' ';
explain select * from user where company = 'rocker' or nickname = 'rocker';

/*
alter table user drop index idx_nickname;
alter table user drop index idx_company;
*/
alter table user add index idx_composition (nickname,company);

select '用组合索引:' as ' ';
explain select * from user where company = 'rocker' and nickname = 'rocker';
select ' ';
explain select * from user where company = 'rocker' or nickname = 'rocker';

select '提供参考索引列表：' as 'hint使用:';
explain select * from user use index(idx_company,idx_nickname) where company = 'rocker' and nickname = 'rocker';
select '忽略某个或某几个索引列表：' as '';
explain select * from user ignore index(idx_company,idx_nickname) where company = 'rocker' and nickname = 'rocker';
select '强制使用某个索引：' as '';
explain select * from user force index(idx_company) where company = 'rocker' and nickname = 'rocker';

alter table user add index idx_composition2(company,nickname);

select '组合索引覆盖' as ' ';
explain select uid from user where company = 'rocker' and nickname = 'rocker';
select ' ';
explain select company from user where company = 'rocker' or nickname = 'rocker';


select '模糊查询不走索引' as ' ';
explain select* from user where nickname like '%er';
explain select* from user where nickname like 'e%r';

select '用in/not in不走索引' as ' ';
explain select* from user where uid in (3,7);
explain select* from user where uid between 3 and 7;
select ' ';
explain select* from user where user.uid in(select uid from account);
explain select* from user where exists(select * from account where user.uid=account.uid);
select ' ';