MySQL索引

欢迎访问我的博客，专注于Java技术分享，共同进步。❤

1、创建表和索引

create TABLE user_index(
	id int auto_increment primary key,
	first_name varchar(16),
	last_name VARCHAR(16),
	id_card VARCHAR(18),
	information text
);

-- 更改表结构
alter table user_index
-- 创建一个first_name和last_name的复合索引，并命名为name
add key name (first_name,last_name),
-- 创建一个id_card的唯一索引，默认以字段名作为索引名
add UNIQUE KEY (id_card),
-- 鸡肋，全文索引不支持中文
add FULLTEXT KEY (information);

2、查看建表语句

show create table user_index;

3、创建表同时建立索引

CREATE TABLE `user_index` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `first_name` varchar(16) DEFAULT NULL,
  `last_name` varchar(16) DEFAULT NULL,
  `id_card` varchar(18) DEFAULT NULL,
  `information` text,
  PRIMARY KEY (`id`),
  UNIQUE KEY `id_card` (`id_card`),
  KEY `name` (`first_name`,`last_name`),
  FULLTEXT KEY `information` (`information`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4

4、删除索引

alter table user_index drop KEY name;
alter table user_index drop KEY id_card;
alter table user_index drop KEY information;

5、EXPLAIN索引执行计划

CREATE TABLE innodb1 (
	id INT auto_increment PRIMARY KEY,
	first_name VARCHAR (16),
	last_name VARCHAR (16),
	id_card VARCHAR (18),
	information text,
	KEY name (first_name, last_name),
	FULLTEXT KEY (information),
	UNIQUE KEY (id_card)
);

insert into innodb1 (first_name,last_name,id_card,information) values ('张','三','1001','华山派');
insert into innodb1 (first_name,last_name,id_card,information) values ('张','三','1002','华山派');

EXPLAIN SELECT * FROM innodb1 WHERE id < 20

EXPLAIN解读