Mysql索引优化

为了提升数据检索速度，降低查询延时和IO消耗，在建表或者编写SQL语句时，应该首先设计及检查索引和数据分布情况，下面是常见的索引设计或使用原则：

1.尽量选择惟一性索引；

2.为经常需要排序、分组和联合操作的字段建立索引；

3.为常作为查询条件的字段建立索引；

4.限制索引的数目，索引越多，更新和插入的效率越低；

5.尽量使用数据量少的索引；

6.组合索引中的尽量把能过滤掉更多数据的字段放在前面；

7.尽量使用前缀来索引，针对长文本，TEXT等类型；

8.删除不再使用或者很少使用的索引；

9.数据是动态变化中的，索引的使用也需要根据数据的变化而变化。

下面来对加索引与没有加索引的性能对比

首先我往数据库插入了10万条数据

先用普通没有索引的字段查询

可以看到时间为41毫秒

下面使用主键查询,主键默认是索引

可以看到执行时间几乎为0

这就是使用索引的查询速度,大大优于不使用索引的查询速度

现在给name字段加上索引

ALTER TABLE `t_user` ADD INDEX index_name ( `name` ) ;

再次执行查询语句,这次的事件比不加索引事件降低了很多.

下面测试范围查找

SELECT * FROM t_user WHERE age > 100 AND age < 200;

然后给age字段加上索引再次执行sql语句

这也可以看出事件大大降低

下面测试排序的索引效果

SELECT * FROM t_user ORDER BY address DESC LIMIT 100;     

下面就不附图了

通过排序查看,加索引比不加索引速度也大大提升了不少.

高性能的索引策略

索引虽然查询速度很快,但是也会有弊端,正确使用好索引能给我们开发带来很大好处.

1.独立的列：

如果查询中的列不是独立的，则Mysql就不会使用索引。独立的含义是指索引列不能是表达式的一部分，也不能是函数的参数。

/*使用了表达式，不是独立的列，不会命中索引*/
SELECT * FROM t_user WHERE id + 0 = 1;

上面的SQL等价于id = 1但是Mysql的索引就会因此失效，执行时间大约在0.4秒左右。

2、like查询不能以%开头

如果查询中包含like查询以%为开头，则索引会失效。

3.列类型是字符串，一定要在条件中将数据使用引号引用起来

4.最左匹配原则

在mysql建立联合索引时会遵循最左前缀匹配的原则，即最左优先，在检索数据时从联合索引的最左边开始匹配，

比如我们建立索引时:

ALTER TABLE `t_user` ADD INDEX index_combile ( `name`, `address`,`phone`);

联合索引 index_combile实际建立了(name)、(name,address)、(name,address,phone)三个索引

扩展一点:

使用explain语句查看索引的命中情况

explain SELECT * FROM t_user WHERE address = 'KPSTOrpBMf' AND phone = 'pjZvejKYKF' AND NAME = 'myGKHeCwAm';

EXPLAIN列的解释：

table：显示这一行的数据是关于哪张表的

type：这是重要的列，显示连接使用了何种类型。从最好到最差的连接类型为const、eq_reg、ref、range、indexhe和ALL

possible_keys：显示可能应用在这张表中的索引。如果为空，没有可能的索引。可以为相关的域从WHERE语句中选择一个合适的语句

key： 实际使用的索引。如果为NULL，则没有使用索引。很少的情况下，MYSQL会选择优化不足的索引。这种情况下，可以在SELECT语句中使用USE INDEX（indexname）来强制使用一个索引或者用IGNORE INDEX（indexname）来强制MYSQL忽略索引

key_len：使用的索引的长度。在不损失精确性的情况下，长度越短越好

ref：显示索引的哪一列被使用了，如果可能的话，是一个常数

rows：MYSQL认为必须检查的用来返回请求数据的行数

Extra：关于MYSQL如何解析查询的额外信息。

 

type

以下排序从上到下，性能由坏到好。

a.ALL：Full Table Scan， MySQL将遍历全表以找到匹配的行

b.index：Full Index Scan，index与ALL区别为index类型只遍历索引树

c.range：索引范围扫描，对索引的扫描开始于某一点，返回匹配值域的行，常见于between、<、>等的查询

d.ref：非唯一性索引扫描，返回匹配某个单独值的所有行。常见于使用非唯一索引即唯一索引的非唯一前缀进行的查找

e.eq_ref：唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配。常见于主键或唯一索引扫描

f.const、system：当MySQL对查询某部分进行优化，并转换为一个常量时，使用这些类型访问。如将主键置于where列表中，MySQL就能将该查询转换为一个常量

g.NULL：MySQL在优化过程中分解语句，执行时甚至不用访问表或索引

所以由type可以进行分析，如果是ref、eq_ref那么索引命中且性能较好。