/*索引*/
/*索引设计原则
1.尽量选择唯一性索引
2.为经常需要order by,group by,distinct,union的字段设置索引
3.为常作为查询条件的字段设置索引
4.限制索引的数目
5.尽量使用数据量少的索引,索引值长,查询慢
6.尽量使用前缀来索引
7.删除不再使用的索引,或者很少使用的索引
*/
/*一般来说,应该在这些列上创建索引,例如:
       第一、在经常需要搜索的列上,可以加快搜索的速度; 
       第二、在作为主键的列上,强制该列的唯一性和组织表中数据的排列结构; 
       第三、在经常用在连接的列上,这些列主要是一些外键,可以加快连接的速度; 
       第四、在经常需要根据范围进行搜索的列上创建索引,因为索引已经排序,其指定的范围是连续的; 
       第五、在经常需要排序的列上创建索引,因为索引已经排序,这样查询可以利用索引的排序,加快排序查询时间; 
       第六、在经常使用在WHERE子句中的列上面创建索引,加快条件的判断速度。*/
/*创建表时创建索引*/
--普通索引
use test
create table index1(id int,
name varchar(20),
sex boolean,
index(id)--普通索引
)
show create table index1--检查创建表格语句,查看是否有索引存在,且索引名为'id',Ok 
--使用explain语句查看索引是否被引用
explain select * from index1 where id =1--查到possible_keys和key都是‘id’,说明id索引存在,且被使用
--唯一性索引
create table index2(id int unique, --若不进行Unique约束,也可以创建唯一性索引,但起不到提高查询速度的功能!
name varchar(20),
unique index index2_id(id asc) --升序排列
)
--全文索引
create table index3(id int,
info varchar(20),
fulltext index index3_info(info)
)
show create table index3 --验证一下,Innodb引擎的全文索引
--单列索引,可以为普通索引,也可以是唯一性索引,也可以是全文索引
create table index4(id int,
subject varchar(30),
index index4_st(subject(10)) --subject单列10长度前缀索引
)
show create table index4
--多列索引
create table index5(id int,
name varchar(20),
sex char(4),
index index5_ns(name,sex)--多列索引中,只有查询条件使用了第一个字段时,索引才会被使用
)
explain select * from index5 where name='zrz'--possible_keys和key都为index5_ns

wKioL1cz0v6wC1n_AABHJVnQoA4723.png

explain select * from index5 where sex='boy' --possible_keys和key都为null;因为查询没有引用第一个字段
--空间索引,听说用不到,跳过
/*在已经存在的表上创建索引*/
/*直接添加*/
--创建普通索引
use test
--补坑
create table example0(
 id int(11) default null,
 name varchar(20) default null,
 sex tinyint(1) default null
 )engine = innodb default charset = utf8
create index index7_id on example0(id) --创建一个索引,名为Index7_id 在example的id列上
show create table example0

wKioL1cz0sSxwpOXAAAyOg-PEaw413.png

--修改表创建唯一性索引
--替作者填坑,视频里面有的表竟然在书上不定义就直接引用。。。。
create table index8(
id int,
name varchar(20)
);
create unique index index8_id on index8(id) --这里视频与书不同
show create table index8 --成功创建唯一性索引
--替作者填坑,视频里面有的表竟然在书上不定义就直接引用。
create table index9(
id int,
info varchar(50)
)engine=myisam
create fulltext index index9_info on index9(info)--创建全文索引
show create table index9--成功创建
--替作者填坑,视频里面有的表竟然在书上不定义就直接引用。
create table index10(
id int,
address varchar(20)
)
create index index10_addr On index10(address(4) ) --长度为4的单列前缀索引
show create table index10 --成功创建

wKiom1cz0aLTn2O-AAA1aADyDYg686.png

--替作者填坑,视频里面有的表竟然在书上不定义就直接引用。
create table index11(id int,
 name varchar(20),
 address varchar(50)
)
create index index11_na on index11(name,address)--在name,address上创建的多列索引
show create table index11--成功创建
/*使用alter table语句创建索引*//*暂时跳过这个实验,已有create index xx on table_c() 的语句,且未发现与alter table a add index()的区别*/
alter table a add index index1(name(20))
alter table a add index [unique|fulltext] index2(attribute_a)
alter table a add index index3(attribute_a,attribute_b)
/*删除索引*/
show create table index1 --查看要被删除索引的名称,为id
drop index id on index1 --删除名为id,建在index1表上的索引

wKioL1cz1KaT1p5-AAA7dZF4nhw324.png