MYSQL优化之建立适当的索引
1、主键索引
关键字:primary key
主要作用是确定数据库里一条特点数据记录的位置
最好为每一张表定义一个主键索引,一个表只能有一个主键。主键值不能为空,
有两种指定方式
第一种:
CREATE TABLE user (
id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
);
第二种:
CREATE TABLE user (
id INT UNSIGNED NOT NULLAUTO_INCREMENT,
PRIMARY KEY(id)
)
方式三:如果在建表时没有指定主键,还可以在之后用下面的命令生成主键。
alter table 表名 add primary key(列名)
2、唯一索引
关键字:unique
可以防止创建重复的值,每个表都可以有多个唯一索引,当表的某列被指定为“unique”时,它就默认成了了唯一索引。
CREATE TABLE user (
id INT UNSIGNED NOT NULL AUTO_INCREMENT,
name VARCHAR(30) NOT NULL DEFAULT ''UNIQUE,
PRIMARY KEY(id)
);
在建完表之后加唯一索引
create unique index 索引名 on 表名 (列名)
如:create uniqueindex myunique on `users` (id);
注意:unique索引的数据可以有多个null,但不可以有多个’’空字符串。
3、常规索引
最重要的技术,提升数据库的性能,快速查找,但会减缓插入、修改、删除的速度。
可以单独使用,也可以在表中创建。
单独使用:
创建:CREATE INDEX 索引名 ON 表名(字段名);
删除:DROP INDEX 索引名 ON 表名;
在表中使用:
KEY 索引名(字段名)
KEY 和 INDEX 是同义词
CREATE TABLE user (
id INT UNSIGNED NOT NULL AUTO_INCREMENT,
name VARCHAR(30) NOT NULL DEFAULT '' UNIQUE,
PRIMARY KEY(id),
KEY uid(id)
);
4、全文索引
fulltext 类型索引只能在myISAM表类型使用,只有在varchar char text 文本字符串上使用。
相当于是关键字搜索
create table books(
id int,
bookname varchar(30),
price double(5,2),
primary key(id),
key ind(price),
fulltext(bookname)
);
原来的SQL语句:
select * from books wherebookname like '%php%';
用了全文索引后:
select bookname,price from bookswhere MATCH(bookname) AGAINST('php');
注意:
现在只针对MYISAM引擎;
而且只对英文起效,中文不起效,对中文中用到sphinx技术去处理
要用MATCHT和AGAINST去调用;
全文索引中有一个“停止词”的概念,因为在一个文本中,创建索引量个无穷大擞,因此,才一些常用词和字符,就不会创建,这些词称为停止词。
查询索引:
方式一:show indexesfrom 表名\G;
方式二:show indexesfrom 表名\G;
注:\G只是为了让输出的格式更好看
方式三:desc 表名。缺点,这种方式不能看到索引的名称。
删除索引
一般方法:alter table 表名 drop index 索引名
如:alter table `users`drop index myunique;
删除主键索引:alter table 表名 drop primary key
如:alter table `users`drop primary key;
修改索引
这个就很简单了,先删除再创建
索引使用的注意事项
1、 建立索引,它会有索引文件,它会占用磁盘空间
2、 它只会提高查询速度,会使插入、更新、删除变慢。但这点代价是值得付出的。因为一个网站用的最多的都是查询语句,占到了所有语句的9成以上。
3、 在那些列上适合添加索引
a) 较频繁的作为查询条件的字段应该创建索引;
b) 唯一性太差的字段不适合单独创建索引,即使频繁作为查询条件
c) 更新非常频繁的字段不适合作为索引
d) 不会出现在WHERE子句中的字段不应该创建索引
e) 总结:常用于WHERE条件,唯一性较好,变化不会太频繁的字段的列可以创建索引。
4、 联合索引时,只要查询条件使用了最左边的列,索引一般都会被使用
如:在users表的name和addr添加了一个联合索引。
alter table user add indexmyindex(name,addr);
下面的查询语句中:
select * from user where name='xxx'用到了索引
select * from user whereaddr='xxx' 没有用到索引
5、 对于使用LIKE查询时,查询如果是“%aaa”不会使用到索引,“aaa%”会使用到索。即在LIKE查询时,在关键字的最前面不能使用“%”或“_”这种字符,如果一要在前面用%,则要考虑用全文索引。
select * from user where namelike 'xxx%'用到索引
select * from user where namelike '%xxx'用到了索引
6、 如果列类型是字符串,那一定要在条件中用引号引起来,否则不会使用索引。
如:
select * fromuser where name=111没有用到索引
select * fromuser where name=’111’用到了索引
select * fromuser where name=xxx语法都是错的,更不要谈索引了
即:如果列是字符串,一定要用单引号引起来查询。