一，索引管理
1 索引分类：
2 普通索引INDEX：加速查找
3
4 唯一索引：
5 -主键索引PRIMARY KEY：加速查找+约束（不为空、不能重复）
6 -唯一索引UNIQUE:加速查找+约束（不能重复）
7
8 联合索引：
9 -PRIMARY KEY(id,name):联合主键索引
10 -UNIQUE(id,name):联合唯一索引
11 -INDEX(id,name):联合普通索引
12
13 1 创建索引
14 - 在创建表时就创建
15 create table s1(
16 id int,
17 name char(6),
18 age int,
19 email varchar(30),
20 index(id)
21 );
22 - 在创建表后创建
23 create index name on s1(name);#添加普通索引
24 create unique index age on s1(age);#添加唯一索引
25 alter table s1 add primary key(id);#添加主键索引
26 create index name on s1(id,name);#添加联合普通索引
27 ** 在创表的时候创建只能写在后面单写，因为他不是起约束的作用
28
29 2 删除索引
30 drop index id on s1;
31 drop index name on s1;
32 alter table s1 add primary key(id,name);联合主键索引
33 alter table s1 drop primary key;#删除主键索引
34
35 3 正确使用索引
36 select sql_no_cache * from s1 where email='xxx'; #命中索引，速度很快
37 select sql_no_cache * from s1 where email like '%old%'; #无法使用索引，速度依然很慢
38
39
40
41
42 4 存储过程，主要用来生成多数据，然后按照普通查询和索引查询对比一下查询的时间
43 #1. 准备表
44 create table s1(
45 id int,
46 name varchar(20),
47 gender char(6),
48 email varchar(50)
49 );
50
51 #2. 创建存储过程，实现批量插入记录
52 delimiter $$ #声明存储过程的结束符号为$$
53 create procedure auto_insert1()
54 BEGIN
55 declare i int default 1;
56 while(i<3000000)do
57 insert into s1 values(i,concat('egon',i),'male',concat('egon',i,'@oldboy'));
58 set i=i+1;
59 end while;
60 END$$ #$$结束
61 delimiter ; #重新声明分号为结束符号
62
63 #3. 查看存储过程
64 show create procedure auto_insert1\G
65
66 #4. 调用存储过程
67 call auto_insert1();
68
69 #5. 删除存储过程
70 drop procedure auto_insert1;
71
72 #6. 显示数据库中所有存储的存储过程基本信息，包括所属数据库，存储过程名称，创建时间等
73 show procedure status
二，索引使用
1 ** 索引必须是一个明确的值才能体现其查询速度，例如where id=30521,如果是范围操作（大于，小于，between），就是还是需要循环判断，索引就不起作用
2
3 1 加索引提速
4 无索引
5 mysql> select count(*) from s1 where id=1000;
6 +----------+
7 | count(*) |
8 +----------+
9 | 1 |
10 +----------+
11 1 row in set (0.12 sec)
12
13 mysql> select count(*) from s1 where id>1000;
14 +----------+
15 | count(*) |
16 +----------+
17 | 298999 |
18 +----------+
19 1 row in set (0.12 sec)
20
21 有索引
22 mysql> create index a on s1(id)
23 -> ;
24 Query OK, 0 rows affected (3.21 sec)
25 Records: 0 Duplicates: 0 Warnings: 0
26
27 mysql> select count(*) from s1 where id=1000;
28 +----------+
29 | count(*) |
30 +----------+
31 | 1 |
32 +----------+
33 1 row in set (0.00 sec)
34
35 mysql> select count(*) from s1 where id>1000;
36 +----------+
37 | count(*) |
38 +----------+
39 | 298999 |
40 +----------+
41 1 row in set (0.12 sec)
42 ** 索引必须是一个能查询明确的值才能体现其查询速度，
43
44
45 2 范围
46 #范围小的话，索引有用
47 mysql> select count(*) from s1 where id>1000 and id < 2000;
48 +----------+
49 | count(*) |
50 +----------+
51 | 999 |
52 +----------+
53 1 row in set (0.00 sec)
54
55 #范围大的话，索引没用
56 mysql> select count(*) from s1 where id>1000 and id < 300000;
57 +----------+
58 | count(*) |
59 +----------+
60 | 298999 |
61 +----------+
62 1 row in set (0.13 sec)
63 ** 范围小可以体现索引的作用，大范围还是要逐个循环
64
65
66 3 区分度低的字段不能加索引
67 有索引
68 mysql> create index b on s1(name)
69 -> ;
70 Query OK, 0 rows affected (3.21 sec)
71 Records: 0 Duplicates: 0 Warnings: 0
72
73 mysql> select count(*) from s1 where name='xxx';
74 +----------+
75 | count(*) |
76 +----------+
77 | 0 |
78 +----------+
79 1 row in set (0.00 sec)
80
81 mysql> select count(*) from s1 where name='egon';
82 +----------+
83 | count(*) |
84 +----------+
85 | 299999 |
86 +----------+
87 1 row in set (0.19 sec)
88 ** 表内name都是egon这个值，所以有30万个egon，都是要一行一行去匹配
89
90 mysql> select count(*) from s1 where name='egon' and age=123123123123123;
91 +----------+
92 | count(*) |
93 +----------+
94 | 0 |
95 +----------+
96 1 row in set (0.45 sec)
97
98 mysql> select count(*) from s1 where name='dfsdfdsfdfdsfdsfdsf' and age=123123123123123;
99 +----------+
100 | count(*) |
101 +----------+
102 | 0 |
103 +----------+
104 1 row in set (0.00 sec)
105 ** 同上面结论
106
107 mysql> create index c on s1(age);
108 Query OK, 0 rows affected (3.03 sec)
109 Records: 0 Duplicates: 0 Warnings: 0
110
111 mysql> select count(*) from s1 where name='egon' and age=123123123123123;
112 +----------+
113 | count(*) |
114 +----------+
115 | 0 |
116 +----------+
117 1 row in set (0.00 sec)
118
119 mysql> select count(*) from s1 where name='egon' and age=10;
120 +----------+
121 | count(*) |
122 +----------+
123 | 299999 |
124 +----------+
125 1 row in set (0.35 sec)
126 ** 之所以加速，因为and是同时，也就是age右边可以瞬间定位，再比对nmae
127
128 mysql> select count(*) from s1 where name='egon' and age=10;
129 +----------+
130 | count(*) |
131 +----------+
132 | 999 |
133 +----------+
134 1 row in set (0.00 sec)
135 age=10 区分度高
136
137 mysql> select count(*) from s1 where name='egon' and agw>50;
138 +----------+
139 | count(*) |
140 +----------+
141 | 0 |
142 +----------+
143 1 row in set (0.47 sec)
144
145 mysql> select count(*) from s1 where name='egon' and age>100 and age < 600;
146 +----------+
147 | count(*) |
148 +----------+
149 | 999 |
150 +----------+
151 1 row in set (0.00 sec)
152 ** 区分度高原则，范围原则小，差异就小
153
154
155 4 mysql> create index d on s1(email);
156 Query OK, 0 rows affected (4.83 sec)
157 Records: 0 Duplicates: 0 Warnings: 0
158
159 mysql> select count(*) from s1 where name='egon' and age=10 and id>3000 and email='xxxx';
160 +----------+
161 | count(*) |
162 +----------+
163 | 0 |
164 +----------+
165 1 row in set (0.00 sec)
166
167 mysql> drop index a on s1;
168 Query OK, 0 rows affected (0.10 sec)
169 Records: 0 Duplicates: 0 Warnings: 0
170
171 mysql> drop index b on s1;
172 Query OK, 0 rows affected (0.09 sec)
173 Records: 0 Duplicates: 0 Warnings: 0
174
175 mysql> drop index c on s1;
176 Query OK, 0 rows affected (0.09 sec)
177 Records: 0 Duplicates: 0 Warnings: 0
178
179 mysql> desc s1;
180 +-------+-------------+------+-----+---------+-------+
181 | Field | Type | Null | Key | Default | Extra |
182 +-------+-------------+------+-----+---------+-------+
183 | id | int(11) | NO | | NULL | |
184 | name | char(20) | YES | | NULL | |
185 | age | int(11) | YES | | NULL | |
186 | email | varchar(30) | YES | MUL | NULL | |
187 +-------+-------------+------+-----+---------+-------+
188 4 rows in set (0.00 sec)
189
190 mysql> select count(*) from s1 where name='egon' and age=10 and id>3000 and email='xxxx';
191 +----------+
192 | count(*) |
193 +----------+
194 | 0 |
195 +----------+
196 1 row in set (0.00 sec)
197 ** 区分度高原则，范围原则小，差异就小,email最高
198
199
200 5 增加联合索引，关于范围查询的字段要放到后面
201 select count(*) from s1 where name='egon' and age=10 and id>3000 and email='xxxx';
202 index(name,email,age,id)
203
204 select count(*) from s1 where name='egon' and age> 10 and id=3000 and email='xxxx';
205 index(name,email,id,age)
206
207 select count(*) from s1 where name like 'egon' and age= 10 and id=3000 and email='xxxx';
208 index(email,id,age,name)
209
210 mysql> desc s1;
211 +-------+-------------+------+-----+---------+-------+
212 | Field | Type | Null | Key | Default | Extra |
213 +-------+-------------+------+-----+---------+-------+
214 | id | int(11) | NO | | NULL | |
215 | name | char(20) | YES | | NULL | |
216 | age | int(11) | YES | | NULL | |
217 | email | varchar(30) | YES | | NULL | |
218 +-------+-------------+------+-----+---------+-------+
219 4 rows in set (0.00 sec)
220
221 mysql> create index xxx on s1(age,email,name,id);
222 Query OK, 0 rows affected (6.89 sec)
223 Records: 0 Duplicates: 0 Warnings: 0
224
225 mysql> select count(*) from s1 where name='egon' and age=10 and id>3000 and email='xxxx';
226 +----------+
227 | count(*) |
228 +----------+
229 | 0 |
230 +----------+
231 1 row in set (0.00 sec)
232
233 6. 联合索引，最左前缀匹配，多用于and
234 index(id,age,email,name)
235 #条件中一定要出现id
236 id
237 id age
238 id email
239 id name
240
241 email #不行，最左边匹配
242 mysql> select count(*) from s1 where id=3000;
243 +----------+
244 | count(*) |
245 +----------+
246 | 1 |
247 +----------+
248 1 row in set (0.11 sec)
249
250 mysql> create index xxx on s1(id,name,age,email);
251 Query OK, 0 rows affected (6.44 sec)
252 Records: 0 Duplicates: 0 Warnings: 0
253
254 mysql> select count(*) from s1 where id=3000;
255 +----------+
256 | count(*) |
257 +----------+
258 | 1 |
259 +----------+
260 1 row in set (0.00 sec)
261
262 mysql> select count(*) from s1 where name='egon';
263 +----------+
264 | count(*) |
265 +----------+
266 | 299999 |
267 +----------+
268 1 row in set (0.16 sec)
269
270 mysql> select count(*) from s1 where email='egon3333@oldboy.com';
271 +----------+
272 | count(*) |
273 +----------+
274 | 1 |
275 +----------+
276 1 row in set (0.15 sec)
277
278 mysql> select count(*) from s1 where id=1000 and email='egon3333@oldboy.com';
279 +----------+
280 | count(*) |
281 +----------+
282 | 0 |
283 +----------+
284 1 row in set (0.00 sec)
285
286 mysql> select count(*) from s1 where email='egon3333@oldboy.com' and id=3000;
287 +----------+
288 | count(*) |
289 +----------+
290 | 0 |
291 +----------+
292 1 row in set (0.00 sec)
293 ** 最左匹配，如果没有Id就会有时间得到结果
294
295
296 6.索引列不能参与计算，参与计算，所以就没有意义，保持列“干净”
297 mysql> select count(*) from s1 where id*3;
298 +----------+
299 | count(*) |
300 +----------+
301 | 299999 |
302 +----------+
303 1 row in set (0.16 sec)
304
305 ** 用不上索引
306 like 函数 or 类型不一致 != > < order by
307
308 类型不一致
309 mysql> select count(*) from s1 where id='3000';
310 +----------+
311 | count(*) |
312 +----------+
313 | 1 |
314 +----------+
315 1 row in set
316
317 mysql> select count(*) from s1 where id=3000;
318 +----------+
319 | count(*) |
320 +----------+(0.11 sec)
321 | 1 |
322 +----------+
323 1 row in set (0.00 sec)
324
325 order by 模糊
326 select id from s1 order by id;
327
328 or 只有id是索引，必须是左右2边单独有索引才能提速，叫做索引合并
329 mysql> select count(*) from s1 where id=1000 or email='xxx';
330 +----------+
331 | count(*) |
332 +----------+
333 | 0 |
334 +----------+
335 1 row in set (0.13 sec)
336
337
338 慢查询
339 在Mysqld下面配置
340 slow-query-log=1 开
341 slow-query-log-file=slow.log 存放位置
342 long_query_time=3 超时限制3毫秒就会被记录
343
344 在cmd里面
345 set global slow-query-log=ON;
346 show variables like '%query%';
347 set session long_query_time=3;
