mysql——索引——概念

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
291、普通索引
30
31
322、唯一性索引
33
34
353、全文索引
36
37
384、单列索引
39
40
415、多列索引
42
43
446、空间索引
45
46
47==================================================================================================
48索引的设计原则
49
501、选择唯一性索引
51
522、为经常需要排序、分组和联合操作的字段建立索引
53
543、为常作为查询条件的字段建立索引
55
564、限制索引的数目
57
585、尽量使用数据量少的索引
59
606、尽量使用前缀索引
61
627、删除不再使用或者很少使用的索引
63
64===========================================================================================================
65
66创建索引
67
68一、创建表的时候创建索引
69
70语法格式如下：
71
72create table 表名 (  属性名 数据类型 [完整性约束条件],
73                     属性名 数据类型 [完整性约束条件],
74                     ……
75                     属性名 数据类型
76                     [unique | fulltext | spatial] index | key
77                       [ 别名 ] ( 属性名1 [(长度)]) [ asc | desc ] )
78                   );
79
80unique是可选参数，表示索引为唯一性索引；
81
82fulltext是可选参数，表示索引为全文索引；
83
84spatial是可选参数，表示索引为空间索引；
85
86index 和 key参数用来指定字段为索引的，两者选择其中之一就可以了，作用是一样的；
87
88“别名”是可选参数，用来给创建的索引取的新的名称；
89
90“属性1”参数指定索引对应的字段的名称，该字段必须为前面定义好的字段；
91
92“长度”是可选参数，其指索引的长度，必须是字符串类型才可以使用；
93
94“asc”和“desc”都是可选参数，“asc”表示升序排序，“desc”表示降序排序；
95
96
971、创建普通索引
98
99创建一个普通索引，不需要加任何unique、fulltext、spatial参数。
100
101示例：
102create table index1 ( id int,
103                      name varchar(20),
104                      sex varchar(20),
105                      index index1_id (id)
106                     );
107
108show create table index1;
109
110explain select * from index1 where id = 1; /*possible_keys:index1_id;kye:index1_id ;说明索引被引用了*/
111
112
113
1142、创建唯一性索引
115
116创建唯一性索引时，需要使用unique参数进行约束。
117
118示例：创建一个表名为index2的表，表中的id字段上建立名为index2_id的唯一性索引，且以升序的形式排列。
119
120create table index2 ( id int,
121                      name varchar(20),
122                      sex varchar(20),
123                      unique index index2_id (id asc)
124                     );
125
126show create table index2;
127
128
129
1303、创建全文索引
131
132全文索引，只能创建在char、varchar、text类型的字段上，而且只有myisam存储引擎支持全文索引。
133
134create table index3 ( id  int,
135                      info varchar(20),
136                      fulltext index index3_info ( info )
137                     )engine=myisam;
138
139show create table index3;
140
1414、创建单列索引
142
143单列索引是在表的某一个字段上创建索引
144
145subject字段的长度为20，而index4_st索引的长度只有10，这样做的目的是为了提高查询速度。
146
147对于字符型的数据，可以不用查询全部信息，而只查询前面的若干字符信息。
148
149
150create table index4 ( id  int,
151                      subject varchar(30),
152                      index index4_st ( subject(10) )
153                     );
154
155
156show create table index4;
157
158
1595、创建多列索引
160
161创建多列索引是在表的多个字段上创建一个索引
162
163create table index5 ( id  int,
164                      name varchar(30),
165                      sex varchar(4),
166                      index index5_ns ( name,sex )
167                     );
168
169
170show create table index5;
171
172可以看出，name和sex字段上已经建立了一个名为index5_ns的单列索引。
173
174多列索引里，只有查询条件使用了这些字段中的第一个字段时，索引才会被使用；
175
176如果没有使用索引中的第一个字段，那么这个多列索引就不会起作用。
177
178
1796、创建空间索引
180
181
182创建空间索引时，必须使用spatial参数来设置。而且，创建空间索引时，表的存储引擎必须是myisam类型。而且，索引字段必须有非空约束。
183
184
185create table index6 ( id int,
186                      space geometry not null,
187                      spatial index index6_sp ( space )
188                    )engine=myisam;
189
190show create table index6;
191
192
193注意：space字段必须是非空的，而且数据类型是geometry类型。这个类型是空间数据类型。空间数据类型包括：geometry、point、linestring、polygon类型等
194
195
196==================================================================================================================================
197
198二、在已经存在的表上创建索引
199
200
201在已经存在的表中，可以直接为表上的一个或几个字段创建索引。格式如下：
202
203
204create [unique | fulltext | spatial] index 索引名
205                       
206on 表名 ( 属性名 [(长度)]) [ asc | desc ] );
207
208
209
210unique是可选参数，表示索引为唯一性索引；
211
212fulltext是可选参数，表示索引为全文索引；
213
214spatial是可选参数，表示索引为空间索引；
215
216index 用来指定字段为索引的；
217
218“索引名”参数是给创建的索引取的新名称；
219
220“表名”是指需要创建索引的表的名称，该表必须是已经存在的，如果不存在，需要先创建；
221
222“属性名”参数指定索引对应的字段的名称，该字段必须为前面定义好的字段；
223
224“长度”是可选参数，其指索引的长度，必须是字符串类型才可以使用；
225
226“asc”和“desc”都是可选参数，“asc”表示升序排序，“desc”表示降序排序；
227
228
2291、创建普通索引
230
231
232create table example0 ( id int,
233                        name varchar(20),
234                        sex varchar(40)
235                       );
236
237show create table example0;
238
239
240create index index7_id on example0( id );
241
242
2432、创建唯一性索引
244
245create unique index index8_id on index8( course_id );
246
247
2483、创建全文索引
249
250create fulltext index index9_info on index9( info );
251
2524、创建单列索引
253
254create index index10_addr on index10 ( address(4) );
255
2565、创建多列索引
257
258create index index11_na on index11 ( name , address );
259
2606、创建空间索引
261
262create spatial index index12_line on index12( line );
263
264
265==================================================================================================
266
267三、用alter table语句来创建索引
268
269
270在已经存在的表上，可以通过alter table语句直接为表上的一个或几个字段创建索引
271
272语法格式如下：
273
274
275alter table 表名 add [unique | fulltext | spatial] index 索引名 ( 属性名 [(长度)]) [ asc | desc ] );
276
277
2781、创建普通索引
279
280alter table example0 add index index13_name ( name );
281
282
2832、创建唯一性索引
284
285alter table index14 add unique index index14_id ( course_id );
286
287
2883、创建全文索引
289
290alter table index15 add fulltext index index15_info( info );
291
292
2934、创建单列索引
294
295
296alter table index16 add index index16_addr( address(4) );
297
298
2995、创建多列索引
300
301alter table index17 add index index17_na( name,address );
302
303
3046、创建空间索引
305
306alter table index18 add spatial index index18_line( line );
307
308
309====================================================================================================================
310
311删除索引
312
313指将表中已经存在的索引删除掉。
314
315语法格式如下：
316
317drop index 索引名 on 表名;
318
319========================================================================================================================