Mysql索引创建方式

mysql索引创建方式

1. 在建表时创建

CREATE TABLE user(id int primary key,
	name varchar(55),
	gender tinyint(1),
	index idx_name(name) --此时可以指定多行作为组合索引
	);

使用 SHOW CREATE TABLE user; 命令可以查看建表语句

CREATE TABLE `user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(50) NOT NULL,
  `gender` char(2) DEFAULT NULL,
  `age` tinyint(4) DEFAULT NULL,
  `class_id` int(11) DEFAULT NULL,
  `birthday` date DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `class_id` (`class_id`),
  KEY `idx_name_class_gender` (`name`,`class_id`,`gender`),
  CONSTRAINT `user_ibfk_1` FOREIGN KEY (`class_id`) REFERENCES `class` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8

2. 在建表时没有创建，在需要时也可用create语句再次创建

格式为：CREATE INDEX [index name] ON table name(colum, ...)

. 创建组合索引 idx_name_class_gender,用SHOW INDEX FROM ‘user’ 命令可查看到user表中的索引如下：
3. 用alert语句修改表结构，语句如下：

ALERT TABLE user ADD INDEX [索引名字] (name,...);

注意：可创建单行索引也可创建组合索引，要使用组合索引，记得遵守最左匹配原则。

4. 删除索引
   既然创建了，那必须可以删除，方法如下

方法一: DROP INDEX index_name ON table_name 
方法二：ALERT TABLE table_name DROP INDEX  index_name ;

使用 EXPLAIN 语句可以查询到sql语句执行的情况

EXPLAIN SELECT id,name,gender,class_id FROM `user` WHERE name = 'zhangsan'

执行结果如下：

可以看到，在extra列中显示 using index，表示用到了索引；如果查询速度慢，用explain来做分析是个好办法。

小知识：CASE WHEN THEN 语句用法