MySql 索引优化

索引的类型

1. 普通索引：最基本的索引，没有任何限制。

2. 主键索引（Key）：特殊的唯一索引，不允许有空值。

3. 唯一索引(Unique)：索引列的值必须唯一，允许有空值。如果是组合索引，则该组合的值必须唯一。

4. 组合索引(Compose)：多个列组合的索引。比如cityId、address、name三列建了组合索引，则相当于建立了三组索引：cityId、address、name, cityId、address, cityId, 这是因为MySQL组合索引“最左前缀”的结果，就是从最左边的列开始组合。

5. 短索引：创建索引时，如果是varchar或char列，索引的length可以小于列的实际长度，比如name(20)，可以建索引name_index(10)，既提高查询速度，也提高写入、更新速度。 

 

索引起作用的SQL语句

=、>、<、>=、<=、like、like 'prefix%'、in、between

 

索引不起作用的SQL语句

1. 有Null值的列

只要列中有一个Null值，就不会被包含在索引中，复合索引中只要有一列含有Null值，那么这一列对于复合索引就是无效的。所以在数据库设计时应避免NULL的存在（用其他方式表达你想表达的NULL，比如 -1？）

 

2. like语句的%在前

 匹配符%在查询关键词前面的like语句，将不使用索引。

select * from city where name like '%南'

 

3. not in、<>、!= 语句。

4. where语句中已经使用了索引，order by后面的列不能再使用索引。 

 

5. 在列上进行函数计算

select * from user where YEAR(update_time)>2012

 

 这将在每行数据进行计算，导致索引失效从而全表扫描。可以将语句改为：

select * from user where update_time>‘2012-01-01’

 

 

6. 存了数值的字符串类型字段（如手机号），查询时记得不要丢掉值的引号，否则无法用到该字段相关索引。反之则没关系，也即：

select * from test where mobile = 13711112222;

可是无法用到mobile字段的索引的哦（如果mobile是char 或 varchar类型的话） 

 

 

哪些列适合建索引？

where、join、order by或group by子句中的列适合建索引。

 

 

什么样的字段不需要建索引？

1. 字段值很少，比如：性别、状态、是否有效等。

2. 太长的列，可以选择只建立部分索引，（如：只取前十位做索引）

3. 更新非常频繁的数据不适宜建索引。

 

 

一次查询只能用到一个索引

多个单列索引与单个多列索引的查询效果不同，因为执行查询时，MySQL只能使用一个索引，会从多个索引中选择一个限制最为严格的索引。

 

 

多列查询建索引

一次查询只能用到一个索引，所以 首先枪毙 a，b各建索引方案

a还是b？ 谁的区分度更高（同值的最少），建谁！

当然，联合索引也是个不错的方案，ab，还是ba，则同上，区分度高者，在前。

 

联合索引（最左前缀索引）

建立一个组合索引：ALTER TABLE article ADD INDEX idx_title_time (title(50),time(10))。建立这样的组合索引，其实是相当于分别建立了下面两组组合索引：

–title,time

–title

where a = “xxx” 可以使用 AB 联合索引
where b = “xxx” 则不可 （再想象一下，这是书的目录？）

所以，大多数情况下，有AB索引了，就可以不用在去建一个A索引了。

 

 

最左前缀：

顾名思义，就是最左优先，比如创建了name,age,address多列索引,相当于创建了(name)单列索引，(name,age)组合索引以及(name,age,address)组合索引。
注：在创建多列索引时，要根据业务需求，where子句中使用最频繁的一列放在最左边。

 

 

查看索引

show index from tablename;
explain select ……;