mysql索引

很多时候，我们在mysql中创建了索引，但是某些查询还是很慢，根本就没有使用到索引！一般来说，可能是某些字段没有创建索引，或者是组合索引中字段的顺序与查询语句中字段的顺序不符。

看下面的例子：
假设有一张q币表(cdb_qb)，包含state和value二个字段，没有额外建索引。
一共有15条数据。符合下面语句的数据有2条。

select * from cdb_qb where state = 1 and value in(10);

符合下面语句的数据有3条　

select * from cdb_qb where value in(10);

符合下面语句的数据有8条　

select * from cdb_qb where value state = 1;

　　

无索引

执行下面的sql语句：

select * from cdb_qb where state = 1 and value in(10);

此时由于没有任何索引被用到，所以会进行全表逐行扫描，rows=15。

单索引

接下来我们创建单索引value

再次执行以上sql

可以看到用到了索引value，并且rows=3，证明只需要扫描3行就可以确定数据。

此处有个地方要注意以上，以上的sql应该换成

select * from cdb_qb where value in(10) and state = 1;

由于mysql的设计，多个where条件时，应遵循左前缀规则（针对where有索引的情况而已），左边的条件最好能过滤掉最大量的数据 。虽然在mysql5.6版本以上，系统会自动优化where的顺序，但是mysql的每个操作都会损耗时间，还是要遵从索引设计来。

多索引

接下来我们继续创建单索引state

继续执行以上sql，结果如下

此时rows变成了1，比单索引的3和4都要低，实际上我们的结果是2条符合，这也说明了rows只是系统约估需要扫描的行数，并不是真实的行数。

同时也说明了利用各自索引找到数据 然后 index merge，扫描的行数，比单索引要低。

此处同样有个地方需要注意，就是把value in(10)条件放前面，过滤掉最大量的数据。虽然在mysql5.6版本以上，系统会自动优化where的顺序，但是mysql的每个操作都会损耗时间，还是要遵从索引设计来。

组合索引

我们删除单索引value和state，创建组合索引，value,state，新增字段name。

组合索引的最左优先原则：组合索引的第一个字段必须出现在查询组句中，这个索引才会被用到。

执行以下sql

select * from cdb_qb where name = "ab" and state = 1;

我们可以看到并没有用到索引，因为我们的组合索引的第一个字段是value，但是value没有出现在where组句中，导致索引失效。

下面的情况都会用到这个索引：

where  value = "some value";
where  value  = "some value" and state= "some value";
where  value= "some value" and state= "some value" and name= "some value";
where  state= "some value" and value= "some value" and name= "some value";

对于最后一条语句，mysql会自动优化成第三条的样子,但是还是那句话，不要让系统自动帮你做，还是要遵循索引的设计原则来，效果最快。

下面的情况就不会用到索引：

state= "aaaaaa";
state= "aaaa" and name= "cccccc";

建立索引的时机

 

到这里我们已经学会了建立索引，那么我们需要在什么情况下建立索引呢？一般来说，在WHERE和JOIN中出现的列需要建立索引，但也不完全如此，因为MySQL只对<，<=，=，>，>=，BETWEEN，IN，以及某些时候的LIKE才会使用索引。例如：

SELECT t.Name FROM mytable t LEFT JOIN mytable m ON t.Name=m.username WHERE m.age=20 AND m.city='郑州'

此时就需要对city和age建立索引，由于mytable表的userame也出现在了JOIN子句中，也有对它建立索引的必要。

刚才提到只有某些时候的LIKE才需建立索引。因为在以通配符%和_开头作查询时，MySQL不会使用索引。例如下句会使用索引：

SELECT * FROM mytable WHERE username like'admin%'

下句就不会使用：

SELECT * FROM mytable WHEREt Name like'%admin'

因此，在使用LIKE时应注意以上的区别。

 

使用索引的注意事项

1、索引不会包含有NULL值的列，所以我们在数据库设计时不要让字段的默认值为NULL。

2、不要在列上进行运算

3、在mysql中执行查询时，只能使用一个索引，如果我们在lname,fname,age上分别建索引,执行查询时，只能使用一个索引，mysql会选择一个最严格(获得结果集记录数最少)的索引。

4、因为MySQL只对<，<=，=，>，>=，BETWEEN，IN，以及某些时候的LIKE才会使用索引，不使用NOT IN和<>操作。

5、MySQL查询只使用一个索引，因此如果where子句中已经使用了索引的话，那么order by中的列是不会使用索引的。因此数据库默认排序可以符合要求的情况下不要使用排序操作；尽量不要包含多个列的排序，如果需要最好给这些列创建复合索引。

例如执行

select * from cdb_qb where value = 10 and state =1 order by name

由于where已经使用了索引(value,state)，所以name就算是单独建立了索引，也不会执行索引排序的，using filesort，是使用算法在 内存中进行排序，一种速度比较慢的外部排序，如果能避免是最好的了。

我们可以把组合索引优化成(value,state,name)

using index，证明使用了索引排序。

 

索引的不足之处

上面都在说使用索引的好处，但过多的使用索引将会造成滥用。因此索引也会有它的缺点：

1、虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE。因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件。

2、建立索引会占用磁盘空间的索引文件。一般情况这个问题不太严重，但如果你在一个大表上创建了多种组合索引，索引文件的会膨胀很快。索引只是提高效率的一个因素，如果你的MySQL有大数据量的表，就需要花时间研究建立最优秀的索引，或优化查询语句。