（二）mysql索引操作

索引

　　当我们对一个字段经常查询,而修改很少的时候 ，数据量又很大的情况下，我们可以对该字段建立索引。所谓的索引就像我们在word为一篇文章建立目录，我们可以根据word的页码和目录尽快的查询出我们想要的内容。在mysql中，索引的意义也是如此，mysql有如下几种索引累心。

一、普通索引

没有特殊的含义索引，代码如下:

直接为某个表的某个字段创建索引

CREATE INDEX indexName ON mytable(username(length)); 

修改表结构的时候创建索引

alert mytable ADD INDEX[indexName] ON (username(length))

创建表的时候建立索引

create table mytable (id int not null,username varchar(16) not null, INDEX[indexName](username(length)))

删除索引

DROP INDEX[username] on mytbale;

更新索引
　　mysql在插入数据或者修改数据的时候，会自动的插入或者更新索引行。

　　其中，索引的长度，如果是char、varchar类型，length可以小于字段实际长度；但是如果是text和blob类型，必须指定length，下面类型的所以也是如此。

二、唯一索引

　　它与普通索引的区别是，索引类的值必须唯一，但允许有空值。如果是组合索引，则列的组合也必须唯一，在之前索引关键字前添加关键字 UNIQUE 即可。

三、主键索引

　　它是一种特殊的索引类型，不允许有空值。一般是在建表和修改表的时候同时创建主键索引，一个表只能有一个主键。代码如下：
CREATE TABLE mytable( ID INT NOT NULL, username VARCHAR(16) NOT NULL, PRIMARY KEY(ID) );

四、组合索引

　　组合索引，为表添加多个字段。例如代码如下
　　建表如下
create table mytbale(ID int not null,username varchar(16)not null,city varchar(50)not null,age int not null)

　　建立组合索引如下

alter table mytable ADD index name_city_age(username(10),city,age);

　　username的长度再建表时长度为16，但实际中username长度不会超过10，所以在建立索引的时候设置长度为10.

username, city ,age
username,city
username,age
username

　　所以在查询的时候可以这样, 代码如下所示

select * from mytable where username="admin" and city="北京" 

　　或者代码如下

select * from mytable where username="admin"  and age=20

或者

select * from mytable where username="admin" and city="beijing" and age=20;

而如果是age=20 and city=“beijing” 或者city=“beijing” 则不会用到上述我们建立的组合索引。

五、建立索引的时机

我们一般会在where后出现频繁的字段上建立索引，或者在两个表连接的join上建立所以，且当两个表join的时候，尽可能多的通过join筛选出需要的 内容，再进行where。

但对于某些操作，< , <=, = > >= , between , in 以及某些like才会使用索引。

例如代码如下

join 和where 实例

select t.name form mytable left join mytable m on t.name = m.username where m.age =20 and m.city='beijing'

username出现在join中，所以也有必要对它建立索引。

like建立索引
当使用like建立索引时，当以通配符%和_作为开头查询时，mysql不会使用索引。
like 实例(使用索引)

select * from mytable where username like 'admin%'

like（未使用索引）

select * from mytable where name like '%admin'

或

select * from mytable where name like '_admin'

六、索引的缺点

1 、当对表insert 、update和delete的时候，mysql不仅要insert update delete数据，还要保存一下索引文件。

所以建立优秀的索引很重要，所以在建立索引的时候要注意很多事项，如下。

七、使用索引注意如下事项

1 、索引不会包含有null值的列。
无论单个的索引还是组合文件中的索引，只要有有一列含有null只，那这么这一列对于索引本身是无效的，所以我们建立索引的字段一般会设置为非null。

2、使用短索引。
这里说的是在建立索引的时候设置的索引的长度，当该字段的长度过长时，也会增加磁盘的空间和影响I/O操作，例如文章的id，文章的标题，文章的内容，对于文章的内容字段过长，我们一般不会对该字段建立索引。

3、where和order by索引重复情况
mysql一个sql语句中，只使用一个索引，如果wherer中使用了索引的话，那么order by中的任何列是不会使用索引的，所以在where查询后，尽量不要包含多个列的排序，如果实在需要多个列的排序，最好给这些列建立复合索引。

4、like语句操作
like 中的字段最好不要用索引，因为like ‘%aa’ 和like ‘_aaa’ 都不会使用索引。

5、不要在索引列上进行运算
在索引列上进行运算，索引则会消失，会对全表进行扫描，例如

select * from users where year(mydate)<'2016'

这样会导致索引失效，我们可以改成如下

select * from users where mydate<'2016-02-02'

6 、不要使用not in和< > 操作
索引查询的时候最好是 = 或者between and ，这样提高效率