我要评论一、创建索引
mysql支持多种方法在单个或多个列上创建索引：
- 在创建表的定义语句create table中指定索引列
- 使用alter table语句在存在的表上创建索引
- 或者使用create index语句在已存在的表上添加索引
1、创建表时创建索引
使用create table创建表时，除了可以定义列的数据类型外，还可以定义主键约束、外键约束或者唯一性约束，而不论创建哪种约束，在定义约束的同时，相当于在指定列上创建了一个索引。
举例：
create table dept( dept_id int primary key auto_increment, dept_name varchar(20) ); create table emp( emp_id int primary key auto_increment, emp_name varchar(20) unique, dept_id int, constraint emp_dept_id_fk foreign key(dept_id) references dept(dept_id) )
但是，如果显式创建表时，创建索引的话，基本语法格式如下：
create table table_name [col_name data_type] [unique | fulltext | spatial] [index | key] [index_name] (col_name [length]) [asc | desc]
- unique 、 fulltext 和 spatial 为可选参数，分别表示唯一索引、全文索引和空间索引；
- index 与 key 为同义词，两者的作用相同，用来指定创建索引；
- index_name 指定索引的名称，为可选参数，如果不指定，那么mysql 默认 col_name 为索引名；
- col_name 为需要创建索引的字段列，该列必须从数据表中定义的多个列中选择；
- length 为可选参数，表示索引的长度，只有字符串类型的字段，才能指定索引长度；
- asc 或 desc 指定升序或者降序的索引值存储。
（1）创建普通索引
在book表中的book_name字段上建立普通索引，sql语句如下：
index idx_bname (book_name)
#显式的方式创建
#1创建普通的索引
create table book (
book_id int ,
book_name varchar (100) ,
authors varchar (100) ,
info varchar(100) ,
comment varchar (100) ,
year_publication year,
#声明索引
index idx_bname (book_name))
;查看索引
#通过命令查看索引
#方式l:
mysql> show create table book \g
*************************** 1. row ***************************
table: book
create table: create table `book` (
`book_id` int(11) default null,
`book_name` varchar(100) default null,
`authors` varchar(100) default null,
`info` varchar(100) default null,
`comment` varchar(100) default null,
`year_publication` year(4) default null,
key `idx_bname` (`book_name`)
) engine=innodb default charset=utf8
1 row in set (0.00 sec)
# 方式2：
show index from book;（2）创建唯一索引
举例：
# 创建唯一索引
create table book (
book_id int ,
book_name varchar (100) ,
#声明索引，声明有唯一索引的字段，在添加数据时，要保证唯一性，但是可以添加nu1l
unique index uk_idx_bname (book_name))
;
show index from book;该语句执行完毕之后，使用show create table查看表结构
（3）主键索引
设定为主键后，数据库会自动建立主键索引，innodb为聚簇索引，语法：
create table book (
# 创建主键索引
book_id int primary key,
book_name varchar (100)
;
create table student (
id int(10) unsigned auto_increment ,
student_no varchar(200),
student_name varchar(200),
primary key(id)
);删除主键索引：
alter table student drop primary key ;
修改主键索引：
必须先删除掉(drop)原索引，再新建(add)索引
针对salaries表emp_no字段创建索引idx_emp_no，查询emp_no为10005, 使用强制索引。
create table salaries (
emp_no int(11) not null,
salary int(11) not null,
from_date date not null,
to_date date not null,
primary key (emp_no,from_date)
);
create index idx_emp_no on salaries(emp_no);
select * from salaries force index(idx_emp_no)
where emp_no = 10005;（4）创建单列索引
引举:
create table test2( id int not null, name char(50) null, index single_idx_name(name(20)) );
该语句执行完毕之后，使用show create table查看表结构：
show index from test2 \g
（5）创建组合索引
举例：创建表test3，在表中的id、name和age字段上建立组合索引，sql语句如下：
create table test3( id int(11) not null, name char(30) not null, age int(11) not null, info varchar(255), index multi_idx(id,name,age) );
该语句执行完毕之后，使用show index 查看：
show index from test3 \g
在test3表中，查询id和name字段，使用explain语句查看索引的使用情况：
explain select * from test3 where id=1 and name='songhongkang' \g
可以看到，查询id和name字段时，使用了名称为multiidx的索引，如果查询 (name, age) 组合或者单独查询name和age字段，会发现结果中possible_keys和key值为null, 并没有使用在t3表中创建的索引进行查询。
针对如下表actor结构创建索引： (注:在sqlite中,除了重命名表和在已有的表中添加列,alter table命令不支持其他操作，mysql支持alter table创建索引) create table actor ( actor_id smallint(5) not null primary key, first_name varchar(45) not null, last_name varchar(45) not null, last_update datetime not null); 对first_name创建唯一索引uniq_idx_firstname，对last_name创建普通索引idx_lastname alter table actor add unique index uniq_idx_firstname(first_name); alter table actor add index idx_lastname(last_name) 或 create unique index uniq_idx_firstname on actor(first_name); create index idx_lastname on actor(last_name);
（6）创建全文索引
fulltext全文索引可以用于全文检索，并且只为 char 、varchar 和 text 列创建索引。索引总是对整个列进行，不支持局部 (前缀) 索引。
举例1：创建表test4，在表中的info字段上建立全文索引，sql语句如下：
create table test4( id int not null, name char(30) not null, age int not null, info varchar(255), fulltext index futxt_idx_info(info(50)) ) engine=myisam;
在mysql5.7及之后版本中可以不指定最后的engine了，因为在此版本中innodb支持全文索引。
语句执行完毕之后，使用show create table查看表结构：
show index from test4 \g
由结果可以看到，info字段上已经成功建立了一个名为futxt_idx_info的fulltext索引。
举例2：
create table articles ( id int unsigned auto_increment primary key, title varchar (200), body text, fulltext index (title, body) ) engine = innodb;
创建了一个给title和body字段添加全文索引的表。
举例3：
create table `papers` ( `id` int(10) unsigned not null auto_increment, `title` varchar(200) default null, `content` text, primary key (`id`), fulltext key `title` (`title`,`content`) ) engine=myisam default charset=utf8;
不同于like方式的的查询：
select * from papers where content like ‘%查询字符串%’;
全文索引用match+against方式查询：
select * from papers where match(title,content) against (‘查询字符串’);
明显的提高查询效率。
注意点
- 使用全文索引前，搞清楚版本支持情况；
- 全文索引比 like + % 快 n 倍，但是可能存在精度问题；
- 如果需要全文索引的是大量数据，建议先添加数据，再创建索引。
6.创建空间索引
空间索引创建中，要求空间类型的字段必须为 非空 。
举例：创建表test5，在空间类型为geometry的字段上创建空间索引，sql语句如下：
create table test5( geo geometry not null, spatial index spa_idx_geo(geo) ) engine=myisam;
该语句执行完毕之后，使用show create table查看表结构：
show index from test5 \g
可以看到，test5表的geo字段上创建了名称为spa_idx_geo的空间索引。注意创建时指定空间类型字段值的非空约束，并且表的存储引擎为myisam。
2、在已经存在的表上创建索引
在已经存在的表中创建索引，可以使用alter table语句或者create index语句。
1）使用alter table语句创建索引
alter table语句创建索引的基本语法如下：
alter table table_name add [unique | fulltext | spatial] [index | key] [index_name] (col_name[length],...) [asc | desc] alter table book add index index_name(book_name); alter table book add unique uk_idx_bname(book_name); alter table book add unique mul_bid_na(book_name,author);
2）使用create index创建索引
create index语句可以在已经存在的表上添加索引，在mysql中，create index被映射到一个alter table语句上，基本语法结构为：
create [unique | fulltext | spatial] index index_name on table_name (col_name[length],...) [asc | desc] create 索引类型 索引名称 on 表名(字段); create index idx_cmt on book(comment); create unique index idx_cmt on book(comment); create index idx_cmt on book(comment,author);
二、删除索引
1）使用alter table删除索引
alter table删除索引的基本语法格式如下：
alter table table_name drop index index_name;
2）使用drop index语句删除索引
drop index删除索引的基本语法格式如下：
drop index index_name on table_name;
在需要大量删除表数据，修改表数据时，可以考虑先删除索引。等修改完数据之后再插入
auto_increment 约束字段的唯一索引不能被删除
提示：删除表中的列时，如果要删除的列为索引的组成部分，则该列也会从索引中删除。
如果组成索引的所有列都被删除，则整个索引将被删除。
到此这篇关于mysql创建、删除索引的操作代码的文章就介绍到这了,更多相关mysql创建、删除索引内容请搜索代码网以前的文章或继续浏览下面的相关文章希望大家以后多多支持代码网！
发表评论