Mysql之性能优化--索引的使用

Mysql中的常见的索引主要包括主键索引、唯一索引、普通索引、复合索引和全文索引。下面是对这五类索引的应用及总结：

主键索引：

创建

主键索引的创建可以通过两种方式：建表的同时建立主键索引和建表后添加主键索引。

可以在创建表时指定某列为主键(主键只能有一个)

查看索引情况，确实显示id列为主键索引。

使用

为检测主键索引的性能，下面随机生成10万行的记录。

先删除刚刚建立的主键索引，查看查询语句所使用时间：

从上图可知，仅仅10万行数据时，添加索引和不添加索引的时间差异将近2倍。

删除

主键索引的删除非常简单，只需一个alter table语句即可，都不需要指定主键索引的名称,因为一张表只能有一个主键：

alter table t1 drop primary key;

唯一索引：

创建

唯一索引，顾名思义，该索引的值必须是唯一的，且不可以有重复，但该索引可以存储NULL值，而且NULL值可以重复。

从上图可知，NULL值是可以重复插入到唯一索引的。

应用

接下来仍然使用上面的存储过程，创建一个100万行的数据集，看一看唯一索引的性能。

从这100万行的数据中，就显示出索引的高性能了，图中显示使用索引的查询仅使用了0.06秒，而不使用索引查询则使用了1.13秒，之间相差近20倍。

删除

一般使用alter table 命令就可以删除表中的索引了：

alter table t2 drop index id_index;

普通索引

创建

这里使用已建好的t2表，对name列添加普通索引,普通索引可以允许索引列重复，但根据实际情况，不易将重复性高的列或唯一值不多的列设为普通索引：

alter table t2 add index name_index(name);

应用

对于重复的列也可以建立索引，从上图返回的结果可知，使用索引与不使用索引还是存在很大差距的，一个是0.03秒，一个是0.9秒，这就相差了30倍。如果数据量更大的话，这个差距就会更加大！

删除

alter table t2 drop index name_index;

复合索引

创建

复合索引也称为联合索引，即由多个列组成的索引。该索引同样可以在建表时创建，也可以在建表后创建。

应用

下面使用存储过程，重新创建一个包含100万行的t3表，查看复合索引的性能。

同样我们发现使用索引和不使用索引之间的查询效率还是存在很大区别的。

删除

删除索引的方法与前面的一致，都可以使用alter table命令：

alter table t3 drop index name_add_index;

全文索引

创建

全文索引只能应用在MyISAM引擎中。该索引主要实现对文本、文件的检索。目前该索引只针对英文有效。对于中文的全文索引，还需要使用到其他的处理技术。

应用

需要注意的是，全文索引的使用并不同于其他索引，这里必须使用match和against实现全文索引的where条件。

删除

alter table articles drop index title;

对以上几种索引做如下总结：

1、哪些列适合建立索引，哪些列又不适合建立索引？

a）对频繁使用在where语句，order by语句中的字段适合创建索引；

b）唯一性太差的字段不适合单独创建索引；

c）不适合对更新非常频繁的字段添加索引；

d）不会出现在where语句中的字段不适合建立索引。

2、索引使用的注意事项（索引何时生效，何时又不起作用？）

a）对于创建的复合索引，只要查询条件中使用到最左边的字段，索引起作用,最好是将最短的列放在复合索引的最左边；

b）对于模糊查询，like 'xyz%',索引会起作用，而使用like '%xyz'时，索引不会起作用。如果一定要在模糊查询中第一个字符为%，可以考虑全文索引；

c）如果条件中有OR，OR前后必须都为索引。如果条件中有AND,AND前后至少有一个为索引；

d）如果Mysql估计使用全表扫描比使用索引快，则不使用索引；

e）如果列类型为字符串，则在where条件中，该字段值必须加上引号，否则不会使用到索引；

判断某个索引是否起作用，可以使用explain语句事先测试一下：

3、最后强调的一点也是非常重要的一点（非本机数据的索引）：

如果当前的数据是从其他计算机中拷贝过来的，此时表中的所有索引都无效，因为不同的计算机给索引的编码(位置)是不同的，能够使索引起作用的唯一办法是：

索引删除并重建！