mysql 索引概念及其分类、使用及设计方法

mysql索引概念：

mysql的任何字段都可以添加索引，索引是提高select搜索性能的最佳办法，索引就像是一本书的目录，就像根据一本书的目录可以找到特定的页数一样，根据索引可以快速的找到索引字段的某个记录。

目前mysql暂不支持函数索引。而最常用的mysiam和innodb存储引擎默认是BTREE索引。

mysql索引分类：

一：普通索引INDEX，这个索引是最常用的普通索引，可以为一个或多个列创建普通索引。

创建普通索引的方法有多种：

语法1：create index 【indexname】 on 【tablename(column(length 这里可选)）)】;

语法2：alter table 【tablename】 add index 【indexname(column(length 这里可选))】;

还有就是创建表的时候就创建好索引，方式上图的show create table t1 \G下面就显示了具体的创建方式。

二：唯一索引UNIQUE,唯一索引的值必须是唯一的，可以为null，可以为一个或多个列创建唯一索引。

创建唯一索引的方法与普通索引类似，只是上面语法的index改为unique index即可，可以理解成在索引关键词index前加上修饰关键词unique。

语法一：create unique index 【indexname】 on 【tablename(column(length 这里可选))】;

语法二：alter table 【tablename】 add unique index 【indexname(column(length 这里可选))】;

以下用create作为例子，alter也是如此，建表的方式在下图中也有显示。

三：主键索引PRIMARY KEY，每个表只有一个主键索引，主键索引的值为not null，也就是不能为空。

四：全文索引FULLTEXT,mysql里支持FULLTEXT仅有mysiam存储引擎，可以创建全文索引的只有char、varchar、text字段，创建全文索引后，再insert 数据会有性能的损耗，相较没有创建全文索引的列insert数据要慢上许多，不过如果在已有大量数据的表字段里添加全文索引会占用大量硬盘空间，也会消耗大量时间。

创建全文索引fulltext同唯一索引的语法一样，只是将修饰关键词unique改为fulltext

如果用alter语法则是：alter table full add fulltext index txt_index(txt);

五：组合索引，在sql查询时通常要根据多个字段进行查询，为了提高效率，可以创建组合索引，例如学生表有年级和班级，要根据年级和班级找出学生的记录，就可以将年级和班级做一个组合索引进行查询，这样会比单个索引要快很多。

组合索引创建语法:

语法一：create index 【indexname】 on 【tablename(column_left(length 这里可选),column_right1(length 这里可选))...】;

语法二：alter table 【tablename】add index 【indexname(column_left(length 这里可选),column_right1(length 这里可选))...】;

创建表时直接创建：

注意组合索引中以左边的为主，实际上上图所示创建的组合索引相当于两个索引，一个是grade和class的索引，一个是grade的索引，因此可以看出组合索引中的右侧的字段并没有被包含在内，因此在用组合索引搜索时要么组合查询右侧字段和左侧字段的条件，要么查询左侧字段的条件，不要单独查询右侧字段的条件，这样组合索引无效。

组合索引起效的情况：

select name from stu where grade=1 and class=1;

select name from stu where grade=1;

组合索引不起效的情况：

select name from stu where class=1;

六：单列索引多列索引，多个单列索引和单个多列索引在查询时效果会根据mysql选择的索引不同而效果不同，因为mysql在查询时只能用一个索引，所以它会自动选择限制最为严格的索引。

删除索引的方法：

语法一：drop index 【indexname】 on 【tablename】;

语法二：alter table 【tablename】drop index 【indexname】;

删除主键索引：alter table 【tablename】 drop primary key;

索引设计原则

一：选择搜索的字段创建索引，因为where后进行搜索的字段是索引才能提高select性能，这就像要根据搜索的目录才能快速利用目录找到自己要的页数一样，如果搜索的不是该目录，自然目录也没有作用。

二：尽可能使用唯一索引，即便字段的值有不是唯一的，也最好没有大量重复的值，因为索引的字段不同的值基数越大，提高性能的效果越大，如果重复的值过多，索引的效果会大大降低，甚至没有什么意义，例如商品订单号作为索引，订单号都是唯一的，在这里建立索引可以大大提高搜索速度，如果用的性别做索引，性别只有男和女，索引并不能提高多少搜索速度，这个可以想象成一本书的目录是男和女，大量的重复目录，根据目录找的时候照样要花大量的时间。

三：多利用组合索引的最左前缀，组合索引可以起到多个索引的作用,创建为n列的索引，那么该索引将会起到n个索引的作用，最左前缀应该选取重复值最低的字段，然后后面的依次类推，这样可以最大限度的提高搜索速度，将索引的好处最大化利用。

例如索引为名字，学号，班级，年级三列，那么这个索引相当于下面四个单独的索引：

名字，学号，班级，年级的一个索引

名字，学号，班级索引

名字，学号索引

名字索引

四：只最小限度的创建必要的索引，过多的索引不仅不会提高性能，还会拖慢操作性能，因为索引要占用磁盘空间，一是占空间，而且更改和写入以及删除时都要对索引进行更新操作，这样会拖慢操作速度。

五：适当的建立短索引，需要对字符串进行索引的时候，如果可以的话尽可能的指定一个前缀长度，例如如果varchar(100)里前30个字符都是唯一的，可以建立前30位字符的短索引，这样既可以起到索引的作用，也可以节省磁盘空间，具体如下图，下图的like也是一个要注意的地方，xxxx%是可以使用索引的，但是%xxxx是无法使用索引的。

六：建立索引的字段最好是设置not null，虽然说有些索引是可以有null值的，但是这对索引来讲并没有好处，因为null值的这行记录将不能列入索引中，无法利用索引对这行记录进行搜索。