mysql如何建立适当索引_MYSQL优化－建立适当的索引

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语法都是错的，更不要谈索引了

即：如果列是字符串，一定要用单引号引起来查询。