1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
/*索引*/
/*索引设计原则
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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
--修改表创建唯一性索引
--替作者填坑，视频里面有的表竟然在书上不定义就直接引用。。。。
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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
--替作者填坑，视频里面有的表竟然在书上不定义就直接引用。
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