Mysql 的索引应用

索引　　

数据库的索引就像一本书的目录，能够加快数据库的查询速度。

MYSQL索引有四种PRIMARY、INDEX、UNIQUE、FULLTEXT， 其中PRIMARY、INDEX、UNIQUE是一类，FULLTEXT是一类。

这四种都是单列索引，也就是他们都是作用于单一列，所以也称单列索引；
但是所以一个索引也可以作用于多个列上，称为组合索引或复合索引。

单列索引

新建一张测试表

CREATE TABLE T_USER( ID INT NOT NULL,USERNAME VARCHAR(16) NOT NULL);

　　
（1）PRIMARY：主键索引。索引列唯一且不能为空；一张表只能有一个主键索引（主键索引通常在建表时就被指定）

CREATE TABLE T_USER(ID INT NOT NULL,USERNAME VARCHAR(16) NOT NULL,PRIMARY KEY(ID))

（2）NORMAL：普通索引。索引列没有任何限制；

建表时指定：

CREATE TABLE T_USER(ID INT NOT NULL,USERNAME VARCHAR(16) NOT NULL,INDEX USERNAME_INDEX(USERNAME(16))) //给列USERNAME建普通索引USERNAME_INDEX

ALTER语句指定：

ALTER TABLE T_USER ADD INDEX U_INDEX (USERNAME) //给列USERNAME建普通索引 U_INDEX

删除索引：

DROP INDEX U_INDEX ON t_user  //删除表t_user中的索引U_INDEX

（3）UNIQUE：唯一索引。索引列的值必须是唯一的，但允许有空；

建表时指定：

CREATE TABLE t_user(ID INT NOT NULL,USERNAME VARCHAR(16) NOT NULL,UNIQUE U_INDEX(USERNAME)) //给列USERNAME添加唯一索引T_USER

ALTER语句指定

ALTER TABLE t_user ADD UNIQUE u_index(USERNAME) //给列T_USER添加唯一索引u_index

删除索引：

DROP INDEX U_INDEX ON t_user

　　
（4）FULLTEXT：全文搜索的索引。FULLTEXT 用于搜索很长一篇文章的时候，效果最好。用在比较短的文本，如果就一两行字的，普通的 INDEX 也可以。
索引的新建和删除和上面一致，这里不再列举…

组合索引（复合索引）　　

新建一张表

CREATE TABLE T_USER(ID INT NOT NULL,USERNAME VARCHAR(16) NOT NULL,CITY VARCHAR(10),PHONE VARCHAR(10),PRIMARY KEY(ID) )

组合索引就是把多个列加入到统一个索引中，如新建的表T_USER，我们给USERNAME+CITY+PHONE创建一个组合索引

ALTER TABLE t_user ADD INDEX name_city_phone(USERNAME,CITY,PHONE)  //组合普通索引

ALTER TABLE t_user ADD UNIQUE name_city_phone(USERNAME,CITY,PHONE) //组合唯一索引

这样的组合索引，其实相当于分别建立了（USERANME,CITY,PHONE USERNAME,CITY USERNAME,PHONE）三个索引。

为什么没有（CITY,PHONE）索引呢？这是因为MYSQL组合查询“最左前缀”的结果。简单的理解就是只从最左边开始组合。

并不是查询语句包含这三列就会用到该组合索引：
　　
以下的查询语句才会用到创建的组合索引

SELECT * FROM t_user where USERNAME="parry" and CITY="广州" and PHONE="180"
SELECT * FROM t_user where USERNAME="parry" and CITY="广州"
SELECT * FROM t_user where USERNAME="parry" and PHONE="180"

以下的查询语句是不会用到创建的组合索引

SELECT * FROM t_user where CITY="广州" and PHONE="180"
SELECT * FROM t_user where CITY="广州"
SELECT * FROM t_user where PHONE="180"

索引不足之处

（1）索引提高了查询的速度，但是降低了INSERT、UPDATE、DELETE的速度，因为在插入、修改、删除数据时，还要同时操作一下索引文件；

（2）建立索引未见会占用一定的磁盘空间。

索引的使用注意事项

（1）只要列中包含NULL值将不会被包含在索引中，组合索引只要有一列含有NULL值，那么这一列对于组合索引就是无效的
所以我们在设计数据库的时候最好不要让字段的默认值为NULL;

（2）使用短索引

如果可能应该给索引指定一个长度，例如：一个VARCHAR(255)的列，但真实储存的数据只有20位的话，在创建索引时应指定索引的长度为20，而不是默认不写。如下：

ALTER TABLE t_user add INDEX U_INDEX(USERNAME(16)) 优于 ALTER TABLE t_user add INDEX U_INDEX(USERNAME)

使用短索引不仅能够提高查询速度，而且能节省磁盘操作以及I/O操作。

（3）索引列排序

Mysql在查询的时候只会使用一个索引，因此如果where子句已经使用了索引的话，那么order by中的列是不会使用索引的
所以order by尽量不要包含多个列的排序，如果非要多列排序，最好使用组合索引。

（4）Like 语句

一般情况下不是鼓励使用like,如果非使用，那么需要注意 like”%aaa%”不会使用索引；但like“aaa%”会使用索引。

（5）不要使用 NOT IN 和 <> 操作

索引方式 HASH 和 BTREE 的比较

（1）HASH

用于对等比较，如 "=" 和 "<=>"

（2）BTREE

BTREE索引看名字就知道索引以树形结构存储，通常用在像 "=，>，>=，<，<=、BETWEEN、Like" 等操作符查询效率较高；
我们通用的就是BTREE索引方式，因为这是Mysql默认的。

索引的优缺点

为什么要创建索引呢？

这是因为，创建索引可以大大提高系统的性能。

第一、通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性。

第二、可以大大加快 数据的检索速度，这也是创建索引的最主要的原因。

第三、可以加速表和表之间的连接，特别是在实现数据的参考完整性方面特别有意义。

第四、在使用分组和排序子句进行数据检索时，同样可以显著减少查询中分组和排序的时间。

第五、通过使用索引，可以在查询的过程中，使用优化隐藏器，提高系统的性能。

也许会有人要问：增加索引有如此多的优点，为什么不对表中的每一个列创建一个索引呢？这种想法固然有其合理性，然而也有其片面性。
虽然，索引有许多优点， 但是，为表中的每一个列都增加索引，是非常不明智的。

这是因为，增加索引也有许多不利的一个方面:

第一、创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增加。

第二、索引需要占物理空间，除了数据表占数据空间之外，每一个索引还要占一定的物理空间。如果要建立聚簇索引，那么需要的空间就会更大。

第三、当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，这样就降低了数据的维护速度。

什么样的字段适合创建索引

索引是建立在数据库表中的某些列的上面。因此，在创建索引的时候，应该仔细考虑在哪些列上可以创建索引，在哪些列上不能创建索引。

一般来说，应该在这些列上创建索引，例如：

第一、在经常需要搜索的列上，可以加快搜索的速度；

第二、在作为主键的列上，强制该列的唯一性和组织表中数据的排列结构；

第三、在经常用在连接的列上，这些列主要是一些外键，可以加快连接的速度；

第四、在经常需要根据范围进行搜索的列上创建索引，因为索引已经排序，其指定的范围是连续的；

第五、在经常需要排序的列上创建索引，因为索引已经排序，这样查询可以利用索引的排序，加快排序查询时间；

第六、在经常使用在WHERE子句中的列上面创建索引，加快条件的判断速度。

建立索引，一般按照select的where条件来建立，比如：

select的条件是where f1 and f2，那么如果我们在字段f1或字段f2上建立历索引是没有用的，只有在字段f1和f2上同时建立索引才有用等。

什么样的字段不适合创建索引

同样，对于有些列不应该创建索引。一般来说，不应该创建索引的的这些列具有下列特点：

第一，对于那些在查询中很少使用或者参考的列不应该创建索引。这是因为，既然这些列很少使用到，因此有索引或者无索引，
并不能提高查询速度。相反，由于增加了索引，反而降低了系统的维护速度和增大了空间需求。

第二，对于那些只有很少数据值的列也不应该增加索引。这是因为，由于这些列的取值很少，例如人事表的性别列，
在查询的结果中，结果集的数据行占了表中数据行的很大比 例，即需要在表中搜索的数据行的比例很大。
增加索引，并不能明显加快检索速度。

第三，对于那些定义为text, image和bit数据类型的列不应该增加索引。这是因为，这些列的数据量要么相当大，要么取值相当少。

第四，当修改性能远远大于检索性能时，不应创建索引。这是因为，修改性能和检索性能是互相矛盾的。
当增加索引时，会提高检索性能，但是会降低修改性能。当减少索引时，会提高修改性能，降低检索性能。
因此，当修改性能远远大于检索性能时，不应创建索引。

创建索引的方法:

1、创建索引，例如 create index <索引的名字> on table_name (列的列表);

2、修改表，例如 alter table table_name add index[索引的名字] (列的列表);

3、创建表的时候指定索引，例如create table table_name ( […], INDEX [索引的名字] (列的列表) );

查看表中索引的方法:

show index from table_name; 查看索引

索引的类型及创建例子:

1.PRIMARY KEY （主键索引）

MySQL> alter table table_name add primary key ( column )

2.UNIQUE 或 UNIQUE KEY (唯一索引)

mysql> alter table table_name add unique (column)

3.FULLTEXT (全文索引)

mysql> alter table table_name add fulltext (column )

4.INDEX (普通索引)

mysql> alter table table_name add index index_name ( column )

5.多列索引 (聚簇索引)

mysql> alter table table_name add index index_name ( column1, column2, column3 )

修改表中的索引：

alter table tablename drop primary key,add primary key(fileda,filedb)