-
Notifications
You must be signed in to change notification settings - Fork 1
/
Mysql Pr14
253 lines (209 loc) · 10.1 KB
/
Mysql Pr14
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
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
DROP TABLE user_login;
CREATE TABLE user_login(
pid INT NOT NULL,
login_time DATETIME NOT NULL
);
INSERT INTO user_login(pid, login_time) VALUES(1,'2016-11-25 13:30:45');
INSERT INTO user_login(pid, login_time) VALUES(1,'2016-11-24 13:30:45');
INSERT INTO user_login(pid, login_time) VALUES(1,'2016-11-24 10:30:45');
INSERT INTO user_login(pid, login_time) VALUES(1,'2016-11-24 09:30:45');
INSERT INTO user_login(pid, login_time) VALUES(1,'2016-11-23 09:30:45');
INSERT INTO user_login(pid, login_time) VALUES(1,'2016-11-10 09:30:45');
INSERT INTO user_login(pid, login_time) VALUES(1,'2016-11-09 09:30:45');
INSERT INTO user_login(pid, login_time) VALUES(1,'2016-11-01 09:30:45');
INSERT INTO user_login(pid, login_time) VALUES(1,'2016-10-31 09:30:45');
INSERT INTO user_login(pid, login_time) VALUES(2,'2016-11-25 13:30:45');
INSERT INTO user_login(pid, login_time) VALUES(2,'2016-11-24 13:30:45');
INSERT INTO user_login(pid, login_time) VALUES(2,'2016-11-23 10:30:45');
INSERT INTO user_login(pid, login_time) VALUES(2,'2016-11-22 09:30:45');
INSERT INTO user_login(pid, login_time) VALUES(2,'2016-11-21 09:30:45');
INSERT INTO user_login(pid, login_time) VALUES(2,'2016-11-20 09:30:45');
INSERT INTO user_login(pid, login_time) VALUES(2,'2016-11-19 09:30:45');
INSERT INTO user_login(pid, login_time) VALUES(2,'2016-11-02 09:30:45');
INSERT INTO user_login(pid, login_time) VALUES(2,'2016-11-01 09:30:45');
INSERT INTO user_login(pid, login_time) VALUES(2,'2016-10-31 09:30:45');
INSERT INTO user_login(pid, login_time) VALUES(2,'2016-10-30 09:30:45');
INSERT INTO user_login(pid, login_time) VALUES(2,'2016-10-29 09:30:45');
mysql> select * from world.user_login;
+-----+---------------------+
| pid | login_time |
+-----+---------------------+
| 1 | 2016-11-25 13:30:45 |
| 1 | 2016-11-24 13:30:45 |
| 1 | 2016-11-24 10:30:45 |
| 1 | 2016-11-24 09:30:45 |
| 1 | 2016-11-23 09:30:45 |
| 1 | 2016-11-10 09:30:45 |
| 1 | 2016-11-09 09:30:45 |
| 1 | 2016-11-01 09:30:45 |
| 1 | 2016-10-31 09:30:45 |
| 2 | 2016-11-25 13:30:45 |
| 2 | 2016-11-24 13:30:45 |
| 2 | 2016-11-23 10:30:45 |
| 2 | 2016-11-22 09:30:45 |
| 2 | 2016-11-21 09:30:45 |
| 2 | 2016-11-20 09:30:45 |
| 2 | 2016-11-19 09:30:45 |
| 2 | 2016-11-02 09:30:45 |
| 2 | 2016-11-01 09:30:45 |
| 2 | 2016-10-31 09:30:45 |
| 2 | 2016-10-30 09:30:45 |
| 2 | 2016-10-29 09:30:45 |
+-----+---------------------+
21 rows in set (0.01 sec)
mysql> select * from world.user_login limit 1;
+-----+---------------------+
| pid | login_time |
+-----+---------------------+
| 1 | 2016-11-25 13:30:45 |
+-----+---------------------+
1 row in set (0.00 sec)
mysql> select * from world.user_login limit 12;
+-----+---------------------+
| pid | login_time |
+-----+---------------------+
| 1 | 2016-11-25 13:30:45 |
| 1 | 2016-11-24 13:30:45 |
| 1 | 2016-11-24 10:30:45 |
| 1 | 2016-11-24 09:30:45 |
| 1 | 2016-11-23 09:30:45 |
| 1 | 2016-11-10 09:30:45 |
| 1 | 2016-11-09 09:30:45 |
| 1 | 2016-11-01 09:30:45 |
| 1 | 2016-10-31 09:30:45 |
| 2 | 2016-11-25 13:30:45 |
| 2 | 2016-11-24 13:30:45 |
| 2 | 2016-11-23 10:30:45 |
+-----+---------------------+
12 rows in set (0.00 sec)
mysql> select * from world.user_login limit 6;
+-----+---------------------+
| pid | login_time |
+-----+---------------------+
| 1 | 2016-11-25 13:30:45 |
| 1 | 2016-11-24 13:30:45 |
| 1 | 2016-11-24 10:30:45 |
| 1 | 2016-11-24 09:30:45 |
| 1 | 2016-11-23 09:30:45 |
| 1 | 2016-11-10 09:30:45 |
+-----+---------------------+
6 rows in set (0.01 sec)
mysql 创建索引
在执行CREATE TABLE语句时可以创建索引，也可以单独用CREATE INDEX或ALTER TABLE来为表增加索引。
ALTER TABLE
ALTER TABLE用来创建普通索引、UNIQUE索引或PRIMARY KEY索引。
ALTER TABLE table_name ADD INDEX index_name (column_list)
ALTER TABLE table_name ADD UNIQUE (column_list)
ALTER TABLE table_name ADD PRIMARY KEY (column_list)
其中table_name是要增加索引的表名，column_list指出对哪些列进行索引，多列时各列之间用逗号分隔。
索引名index_name可选，缺省时，MySQL将根据第一个索引列赋一个名称。另外，ALTER TABLE允许在单个语句中更改多个表，因此可以在同时创建多个索引。
作者：kang_KK
链接：https://www.jianshu.com/p/e109aea6eb7c
來源：简书
简书著作权归作者所有，任何形式的转载都请联系作者获得授权并注明出处。
索引并不一定就是给主键或是唯一的字段。如果在你的表中，有某个字段你总要会经常用来做搜索，那么，请为其建立索引吧。
从上图你可以看到那个搜索字串 “last_name LIKE ‘a%'”，一个是建了索引，一个是没有索引，性能差了4倍左右。
另外，你应该也需要知道什么样的搜索是不能使用正常的索引的。
例如，当你需要在一篇大的文章中搜索一个词时，如： “WHERE post_content LIKE ‘%apple%'”，索引可能是没有意义的。
你可能需要使用MySQL全文索引 或是自己做一个索引（比如说：搜索关键词或是Tag什么的）
mysql> alter table world.user_login add index (pid);
Query OK, 0 rows affected (0.12 sec)
Records: 0 Duplicates: 0 Warnings: 0
| Query_ID | Duration | Query
| 14 | 0.00178875 | select * from world.user_login limit 6
| 22 | 0.00043850 | select * from world.user_login limit 6
删除索引
可利用ALTER TABLE或DROP INDEX语句来删除索引。
类似于CREATE INDEX语句，DROP INDEX可以在ALTER TABLE内部作为一条语句处理，语法如下。
DROP INDEX index_name ON talbe_name;
mysql> DROP INDEX pid ON world.user_login;
Query OK, 0 rows affected (0.12 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> show index from world.user_login;
Empty set (0.00 sec)
mysql> alter table world.user_login add index (pid);
Query OK, 0 rows affected (0.10 sec)
Records: 0 Duplicates: 0 Warnings: 0
28 | 0.11875700 | DROP INDEX pid ON world.user_login
mysql> show index from world.user_login;
+------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| user_login | 1 | pid | 1 | pid | A | 2 | NULL | NULL | | BTREE | | | YES | NULL |
+------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
1 row in set (0.02 sec)
mysql> select * from world.user_login limit 10;
+-----+---------------------+
| pid | login_time |
+-----+---------------------+
| 1 | 2016-11-25 13:30:45 |
| 1 | 2016-11-24 13:30:45 |
| 1 | 2016-11-24 10:30:45 |
| 1 | 2016-11-24 09:30:45 |
| 1 | 2016-11-23 09:30:45 |
| 1 | 2016-11-10 09:30:45 |
| 1 | 2016-11-09 09:30:45 |
| 1 | 2016-11-01 09:30:45 |
| 1 | 2016-10-31 09:30:45 |
| 2 | 2016-11-25 13:30:45 |
+-----+---------------------+
10 rows in set (0.01 sec)
31 | 0.01674850 | show index from world.user_login |
32 | 0.00718100 | select * from world.user_login limit 10
mysql> SHOW INDEX FROM world.user_login \G;
*************************** 1. row ***************************
Table: user_login
Non_unique: 1
Key_name: pid
Seq_in_index: 1
Column_name: pid
Collation: A
Cardinality: 2
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
Visible: YES
Expression: NULL
1 row in set (0.00 sec)
ERROR:
No query specified
mysql>
mysql> SHOW INDEX FROM world.user_login;
+------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| user_login | 1 | pid | 1 | pid | A | 2 | NULL | NULL | | BTREE | | | YES | NULL |
+------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
1 row in set (0.00 sec)
mysql>
mysql> select * from world.user_login where login_time >'2016-11-11';
+-----+---------------------+
| pid | login_time |
+-----+---------------------+
| 1 | 2016-11-25 13:30:45 |
| 1 | 2016-11-24 13:30:45 |
| 1 | 2016-11-24 10:30:45 |
| 1 | 2016-11-24 09:30:45 |
| 1 | 2016-11-23 09:30:45 |
| 2 | 2016-11-25 13:30:45 |
| 2 | 2016-11-24 13:30:45 |
| 2 | 2016-11-23 10:30:45 |
| 2 | 2016-11-22 09:30:45 |
| 2 | 2016-11-21 09:30:45 |
| 2 | 2016-11-20 09:30:45 |
| 2 | 2016-11-19 09:30:45 |
+-----+---------------------+
12 rows in set (0.00 sec)
mysql>
mysql> select * from world.user_login where login_time >'2016-11-11' and login_time < '2016-11-21';
+-----+---------------------+
| pid | login_time |
+-----+---------------------+
| 2 | 2016-11-20 09:30:45 |
| 2 | 2016-11-19 09:30:45 |
+-----+---------------------+
2 rows in set (0.00 sec)
mysql>