mysql索引优化查询

2019独角兽企业重金招聘Python工程师标准>>>

一：mysql的引擎简介。

1、ISAM:查找速度快，不支持事物，如果硬盘崩溃则无法恢复。

2、MyISAM:是ISAM的扩展，在web中用的很多，读取速度快，，不支持事物。

3、InnoDB:InnoDB：支持事物，但是速度比ISAM和MyISAM慢，稳定可靠性高

查看当前数据库默认引擎，可以看到本人用的是InnoDB；

二：mysql索引简介

1、普通索引

    创建和删除

create index index_myIndex_name on myIndex(name(5));
drop index index_myIndex_name on myIndex;

    name(5)标示若那么字段过长则可以，将字段全部拿出来做索引很浪费空间，此处拿出该字段的前5个字符作为索引，那么这个5的数字怎么确定呢？

此处标示name的前五个字符的辨识度达到99.54%，前四个只有85.30。这里我们选择前五个字符来创建作为索引。此类索引也可以称之为前缀索引

2、唯一索引

    索引列的值必须唯一，但允许空值。

create unique index indexName on myIndex(username(lenght))

3、主键索引

    主键索引mysql默认都会创建，若数据库引擎用的是InnoDB，则若无特许情况请用mysql的自增的id作为主键。因为InnoDB引擎的数据存储是根据主键分页存储，若用自增的id作为主键可以按顺序往下自动存储，否则在存储时为了保证page结构会有不断的插入移动操作，导致消耗数据库性能。

4、组合索引

create index unindex_myIndex_age_info on myIndex(age, info(5));

    组合遵循前缀规则，如上创建的age,info的组合索引，则实际上的是【age】，【age，info】这两对组合索引，我们观察到没有【info】这个组合索引，这就是组合索引的前缀规则。如果我们建的是age,info,name这三个的组合索引则实际上创建的是【age】，【age，info】，【age，info，name】这三组组合索引。

    组合索引的应用场景：如果你经常用到age或者【age，info】这样的条件来查询，则组合索引比单列索引效率更优。若你只是大部分情况都是单独以age和info作为查询条件则可选择单列索引效果更好。但需要注意的是这里的条件是and语句，or语句是不会用到索引的（某些情况下mysql的执行优化会将其转化为union all语句则可以用到两个单列索引）。如果or语句的条件一个有索引另一个无索引则肯定会全表查询。

 

三：mysql用索引来优化sql查找使用注意点

在进行测试之前首先关闭mysql的查询缓存

set session query_cache_type=off;

查看该表所有的索引

1、不要在列上使用函数或计算：

以上两条sql得到的效果一样但是效率差距巨大，因为上面的那个是age/2=12，这里进行的是全表扫描，下面的使用了索引。

2、在使用多条件查找时，and语句时候尽量使用组合索引。

    查看该表的所有索引可以看到【age，info】是一组组合索引，age，name分别有一个单列索引，下面我们来试验：

这是组合索引的查找速率。

这是两个单列索引的查找速率。可以看到明显在这种情况下组合索引效率要高。

下面我们用explain关键字

上面可以看到前者的rows 10656后者则为140，rows表示该sql遍历的函数，越少越好。很明显后者要少很多。再看用到的索引key关键字，前者用到的是unindex_myIndex_age_info，后者也是。但是我们前面说到了【age，info】这组索引实际上是创建了【age】，【age，info】这两组索引。前者的条件为age=12*2 and name like ‘k%’，前者等价于用到的是组合索引的【age】单列索引。而后者的条件是age =12*2 and info like ‘k%’用的是组合索引【age，info】这个组合索引故而速度要快很多。

    有人会疑惑为什么age=12*2 and name like 'k%'没有条用name的索引，通过前面我们能看到我也为name创建了单列索引了的。答案是：mysql对一个select语句只会选择一个它认为最优的一个索引。从上面的例子也能看出来这点。但是or语句会有执行引擎将其优化成union all，会用到两个索引，这里下面再讲。

3、or语句的列都要使用单列索引，如果有一列没有索引就是全表查询。

    其实这点很好理解，or语句如果每列都有索引的话mysql执行优化会将其转化为union all语句（因为我们前面说过每个select语句只能选择一个索引执行，索引要转为union all这样就能用到多个索引），这样就会执行索引。但是如果其中一列没有索引则这列也就需要全表扫描，既然是全表扫描，其他列的索引也就无用了，mysql也就不会执行优化将其改成union all因为这样做一点用都没有，反正都要全表扫描。

4、like语句不要将通配符%放在最前面

    先看看效果差别

    效率差距是巨大的，like语句通配符%在最前面，sql查询时候会放弃索引，进行全表扫描。

5、索引不会包含有NULL值的列

    只要列中包含有NULL值都将不会被包含在索引中，复合索引中只要有一列含有NULL值，那么这一列对于此复合索引就是无效的。所以我们在数据库设计时不要让字段的默认值为NULL。

6、使用短索引

    对串列进行索引，如果可能应该指定一个前缀长度。例如，如果有一个CHAR(255)的列，如果在前10个或20个字符内，多数值是惟一的，那么就不要对整个列进行索引。短索引不仅可以提高查询速度而且可以节省磁盘空间和I/O操作。

 

四：使用索引的

索引可用但不是处处可用，在使用索引时候我们需要评估是否需要。

首先数据量比较小的情况我们不需要使用索引，创建索引是需要消耗硬盘资源的。

组合条件查询多要用组合索引。

如果一列数据量太大比如这里的info varchar(1000)可以用短索引。

主键用自增的id作为索引。

转载于:https://my.oschina.net/u/579493/blog/816068