3. 索引和查询优化
最后更新于：2022-04-01 02:51:49
# 索引和查询优化
> 在MySQL中, 有四种类型的索引, `主键, 唯一索引, 全文索引和普通索引`
主键(Primary Key)就是值唯一并且没有值为NULL的域的索引
* 主键不能包含`NULL`
~~~
#创建表, 并设置主键
mysql> create table pk_test(
-> f1 int not NULL,
-> primary key(f1));
#已存在的表上创建主键, 语法: alter table tablename add primary key(fieldname1 [, fieldname2])
mysql> alter table customer modify id int not null, add primary key(id);
~~~
普通索引容许重复的值
~~~
#创建普通索引语法 mysql> create table(fieldname columntype, fieldname2 columntype, index[indexname](fieldname1 [,fieldname2...]));
#创建后添加索引: alter table tablename add index[indexname](fieldname1 [,fieldname2...]);
mysql> alter table sales add index(value);
~~~
全文索引
~~~
#创建全文索引语法 mysql> create table(fieldname columntype, fieldname2 columntype, fulltext[indexname](fieldname1 [,fieldname2...]));
mysql> insert into ft2 values
-> ('Waiting for the barbarians'),
-> ('In the Heart of the Country'),
-> ('The master of Patersbury'),
-> ('Writing and Being'),
-> ('Heart of the Beast');
-> ('Heart of the Beest'),
-> ('The beginning and the End'),
-> ('Master Master'),
-> ('A barBarian at my door');
Query OK, 4 rows affected (0.00 sec)
#创建表后再添加全文索引:alter table tablename add fulltext[indexname](fieldname1 [,fieldname2...]);
mysql> create table ft(f1 varchar(255), f2 text, f3 blob, f4 int);
mysql> alter table ft add fulltext(f1, f2);
~~~
全文索引的用法
> 文本域查找与大小写无关
~~~
# match()匹配属性, against()匹配值
mysql> select * from ft2 where match(f1) against ('master');
~~~
唯一索引除了不容许有重复的记录外, 与普通索引一样
~~~
#创建普通索引语法 mysql> create table(fieldname columntype, fieldname2 columntype, unique(fieldname1 [,fieldname2...]));
mysql> create table ui_test(f1 int, f2 int, unique(f1));
mysql> insert into ui_test values(1, 2);
#f1域不能包含重复值
mysql> insert into ui_test values(1, 3);
ERROR 1062 (23000): Duplicate entry '1' for key 'f1'
#创建后添加索引: alter table tablename add unique[indexname](fieldname1 [,fieldname2...]);
~~~
## 删除或者改变索引
~~~
#删除主键
alter table tablename drop primary key;
#删除普通, 唯一, 全文索引, 需要制定索引名
alter table tablename frop index indexname;
#显示全部索引名
show keys from tablename;
~~~
## 选择索引
* 有查找需要使用索引的时候, 考虑创建索引
* 创建索引返回的行越少越好
* 私用短索引
* 不要创建太多的索引