MySQL系列-优化之再谈索引及explain简介

1.再谈索引

前面我出了一篇关于索引的博客MySQL系列-B+Tree索引详解，索引太重要了可以多谈谈，简单来说索引是排好序的快速查找的数据结构。一般来说一张表建立的索引不要超过5个，数据规模没有达到百万级别，建不建索引也没太大区别。

2.索引的优势

索引具备两个优势

一 、可以快速查找

二、他已经按建立索引的字段排好序了。

3.索引的劣势

一、索引也是存储在磁盘中的，需要占的空间比对该索引的字段数据的总和还要大。建的索引一多很可能索引占的磁盘空间都比存储的数据表占的磁盘空间要大。

二、索引虽然提高了数据的查询速度，但是会降低dml（增 删 改）操作的速度，因为不光要更新数据表还要额外的去维护更新索引。

4.那些情况适合建索引

一、主键索引，每张表都应该设立主键，而且在不影响业务的情况下推荐使用自增的int型做为主键。

二、外键和外键关联的字段会自动建立索引。

三、被用户频繁查询的字段应该建立索引，而且建立索引更倾向于建立复合索引。

四、需要进行排序或者分组的字段应该建立索引。

5.那些情况不适合建索引

一、频繁更新的字段不适合建立索引，因为不光要更新数据表还要额外更新索引，系统效率会受影响。

二、数据重复率高的字段不适合建立索引，比如性别这个字段，建不建索引都没太大区别，但是可以考虑作为复合索引的一部分，就像qq按条件搜索好友一样，里面就有年龄性别等约束，可以按这些搜索条件建立复合索引。

6.索引的建立和删除语句

先准备一张表：

一、查看该表的所有索引

show index form t1;

可以看到现在只有主键索引

二、建立单值索引和复合索引

对t1表的n1字段建立单值索引

create index idx_n1 on t1(n1);

然后重新查看该表索引

可以看到多了刚刚建立的索引

对t1表的n1,n2,n3字段建立复合索引

create index idx_n1_n2_n3 on t1(n1,n2,n3);

可以看到我们现在有三个索引，其中的复合索引包含了三个字段。

也可以建立唯一索引，被索引的字段必须是unique的。

create unique index idx_n2 on t1(n2);

执行上面的SQL之后navicat for mysql 自动把n2自动改为了unique，无法插入重复数据。

三、删除索引

drop index  idx_n1_n2_n3 on t1；

6.explain简介

explain可以查看SQL的执行计划也就是SQL到底是如何执行的，mysql底层有查询优化器，他会对SQL进行优化后执行，当然mysql自带的查询优化器没那么智能，所以很多时候SQL执行的时候我们会强制让优化器使用某个索引，这个等我后面的博客结合具体案例再细说。

explain可以查看SQL执行时候 

一、表的读取顺序

二、数据读取的操作类型

三、可能使用的索引

四、实际使用的索引

五、每张表有多少行被查询

等等...

我们可以先执行看一下，后面的博客会具体说明各个字段的含义。

如果优化器不给力的话可以强制使用索引，但是，影响优化器决策的因素有很多，可能表的数据太少不值得走索引，或者在数据差异性不大的字段上建立了索引即不合理的索引，这个时候mysql可能觉得使用索引还不如全表扫描。具体的可以以SQL的执行时间作为评判标准，但是数据量也要够大，否则优化没太大意义。而且MySQL版本变化又各不一样，有很多优化感觉很玄学。