3. 索引和查询优化

索引和查询优化

在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;

选择索引

• 有查找需要使用索引的时候, 考虑创建索引
• 创建索引返回的行越少越好
• 私用短索引
• 不要创建太多的索引