MYSQL索引笔记

/*
索引
MySQL索引的建立对于MySQL的高效运行是很重要的,索引可以大大提高MySQL的检索速度
普通索引
这是最基本的索引，它没有任何限制。
CREATE INDEX 索引名 ON 表名(列名(length)); 
修改表添加索引
语法:
alter table 表名 add index 索引名(列名(lenght))
创建表时指定索引
语法:
create table 表名(
	列名 类型,
	列名 类型,
	index 索引名 (列名(lenght))
);
删除索引
语法:
drop index 索引名 on 表名
*/

-- 示列为emp3表中的name创建一个索引,索引名为emp3_name_index

create index emp3_name_index on emp3 (name);

-- 查询索引

show index from emp3;

-- 示列修改emp3,为address列添加索引,索引名为emp3_address_index

alter table emp3 add index emp3_address_index (address);

-- 示列创建emp4表,包含emp_id,name,address列,同时为name列创建索引。索引名为emp4_name_index

create table emp4(
	emp_id int,
	name varchar(20),
	address varchar(10) default '未知',
	index emp4_name_index (name)
);

-- 示列删除emp3表中索引名为emp3_address_index的索引

drop index emp3_address_index on emp3;

show index from emp3;

/*
--------------------------------------------------------------------------------------
*/

/*
唯一索引
唯一索引的索引列必须唯一,但可以是空值
创建唯一索引
语法:
create unique index 索引名 on 表名(列名(lenght))
修改添加唯一索引
语法:
alter table 表名 add unique 索引名(列名(lenght))
创建表时指定唯一索引
语法:
create table 表名(
		列名 类型,
		列名 类型,
		.........,
		unique 索引名(列名(lenght))
);
*/

-- 示列为emp表中的name创建一个唯一索引,索引名为emp_name_index

create unique index emp_name_index on emp (name);

show index from emp;

-- 示列修改emp表,为address列添加唯一索引,索引名为emp_address_index

alter table emp add unique emp_address_index (address);

show index from emp;

-- 创建emp5表,包含emp_id,name,address列,同时为name列创建唯一索引。索引名为emp5_name_index

use xpx;

create table emp5(
	emp_id int,
	name varchar(20),
	address varchar(30),
	unique emp5_name_index(name),
	constraint emp5_pk primary key (emp_id)
);

show index from emp5;

/*
主键索引
主键索引是一种特殊的唯一索引,一个表只能有一个主键,
不允许有空值,一般在建表的时候同时创意索引
在修改表时添加主键索引
语法:
alter table 表名 add primary key (列名);
创建表时添加索引
语法:
create table 表名 (
		列名 类型,
		primary key(列名)
);
*/

-- 示列修改emp表为employee_id添加主键索引

alter table emp add primary key (employee_id);

show keys from emp;

-- 示列创建emp7表,包含emp_id,name,address列,同时emp_id列创建主键索引

use xpx;
 
create table emp7(
		emp_id int,
		name varchar(20),
		address varchar(30),
		primary key (emp_id)
);

show index from emp7;

/*
-----------------------------------------------------------------------------------------
*/

/*
组合索引(复合索引)
组合索引是指使用多个字段列创建的索引,
只有在查询条件中使用了创建索引时第一个字段,
索引才会被使用(最左前缀原则)
最左前缀原则:就是做左优先
比如我们使用表中的name,address,salary创建组合索引,那么想要组合索引生效我们只能这样组合
name/address/salary
name/address
name
如果使用address/salary或者是salary则索引不会生效
修改添加组合索引
语法:
alter table 表名 add index 索引名(列名(lemght),列名(lenght),......);
创建表时指定组合索引
语法:
create table 表名(
		列名 类型,
		列名 类型,
		index 索引名(列名(lenght),列名(lenght),.......)
);
*/

-- 示列修改emp7表为name,address列创建组合索引

alter table emp7 add index emp7_nameAddress_index (name,address);

show index from emp7;

-- 示列创建emp9,包含emp_id,name,address列,同时为name,address列创建组合索引

create table emp9(
		emp_id int,
		name varchar(20),
		address varchar(30),
		index emp9_nameAddress_index(name,address)
);

show index from emp9;

/*
---------------------------------------------------------------------------------------
*/

/*
全文索引(fulltext)
全文索引主要用来查找文本中的关键字,而不是直接与索引中的值相比较,
fulltext索引跟其他索引不同,它更像一个搜索引擎,而不是加单的where语句的参数匹配
fulltext索引配合match against操作使用,而不是一般的where语句加like
修改表时添加全文索引
语法:
alter table 表名 add fulltext 索引名(列名)
创建表时指定全文索引
语法:
create table 表名(
		列名 类型,
		列名 类型,
		fulltext 索引名(列名)
);
删除全文索引
语法:
drop index 索引名 on 表名
alter table 表名 drop index 索引名

全文索引的使用
全文索引的使用与其他索引不同,在查询语句需要使用match(全文索引列名)
against(检索内容)来检索数据
语法:
select 投影列 from 表名 where match(全文索引列名) against(检索内容)
注意如果数据包含中文需要更换全文解析器ngram
语法:
alter table 表名 add fulltext 索引名(列名) with parser ngram
*/

-- 示列修改emp7表为content列创建全文索引

alter table emp7 add fulltext emp7_conrent_index(content);

show index from emp7;

-- 示列创建emp1表包含emp_id列,content列该列类型为text,并为该列添加名为emp1_content_fulltext的全文索引

use xpx;

create table emp1(
	emp_id int,
	content text,
	fulltext emp1_content_fulltext(content)
);

show index from emp1;

-- 示列删除emp1表中名为emp1_content_fulltext的索引

drop index emp1_content_fulltext on emp1;

show index from emp1;

alter table emp1 add fulltext emp1_content_fulltext(content);

alter table emp1 drop index emp1_content_fulltext;

show index from emp1;


-- 示列查询emp1表中内容包含xpx的数据

alter table emp1 add fulltext emp1_content_full(content);

insert into emp1 values(1,'xpx');

select * from emp1 where match(content) against('xpx');

-- 添加content2列

alter table emp1 add column content2 text;

-- 将content2设置为全文索引并制定解析器为ngram

alter table emp1 add fulltext emp1_content2_full(content2) with parser ngram; 

-- 插如数据

insert into emp1 values(2,'xpx','数据库是“按照数据结构来组织、存储和管理数据的仓库”。是一个长期存储在计算机内的、有组织的、可共享的、统一管理的大量数据的集合。') 

-- 查询emp1表中包含"数据库"的数据

select * from emp1 where match(content2) against('数据库');

select * from emp1;