mysql 索引外键优化_数据库优化篇---MySql索引优化详解

1、索引

索引在数据库中用来提高查询的效率(类似新华字典的偏旁部首检索)，可以避免全表扫描查询;

索引的缺点:

创建索引会占用磁盘空间,尤其是表很大且创建索引的字段值比较多且内容比较长的话更是如此

对于写入操作,如insert、update、delete等操作,索引会降低它们的速度

2、索引建立的原则

1、索引要建在使用较多的字段上

2、尽量不要在相同值较多的字段上建立索引，比如姓名

3、对于经常进行数据存取的字段不要建立索引(数据库会将索引数据根据算法排序，数据量大之后重新排序会占用过多资源)

4、对于有外键引用的表，在主键和外键上建立索引

3、复合索引的建立以及最左前缀原则

唯一性太差又经常作为查询条件的字段可以建立复合索引，假设在USER表的name、salary和city数据列上建立了复合索引。索引中的数据行按照name/salary/city次序排列，所以即使你在查询中只指定了name值，或者指定name和salary值，MySQL也可以使用这个索引。因此，这个索引可以被用于搜索如下所示的数据列组合：

name,salary,city

name,city

name,salary

name

在实际使用中发现：

1)2,1也是用到了索引的，这里可能大家会疑惑违背了最左前缀原则。其实这是因为把where后条件反过来变成name='aa' and salary=300得到的查询结果是一样的。这时MySql的查询优化器会判断这条语句执行效率最高的执行顺序，最后生成真正的执行计划，所以最后MySql会以name='aa' and salary=300这种顺序查询，这就用到了索引。

2)这两种情况都用到了索引，似乎有点出乎预料。这里是因为可以直接从索引里返回查询记录，所以用到了索引全扫描，实际扫描的行数为全部行数。但并没有进行全表扫描，还是用到了索引。

4、聚集索引和非聚集索引

聚集索引：数据行的物理顺序与列值(一般是主键的那一列)的逻辑顺序相同，一个表中只能拥有一个聚集索引。

注：第一列的地址表示该行数据在磁盘中的物理地址，后面三列才是我们SQL里面用的表里的列，其中id是主键，建立了聚集索引。

结合上面的表格就可以理解这句话了吧：数据行的物理顺序与列值的顺序相同，如果我们查询id比较靠后的数据，那么这行数据的地址在磁盘中的物理地址也会比较靠后。而且由于物理排列方式与聚集索引的顺序相同，所以也就只能建立一个聚集索引了。

从上图可以看出聚集索引的好处了，索引的叶子节点就是对应的数据节点(MySQL的MyISAM除外，此存储引擎的聚集索引和非聚集索引只多了个唯一约束，其他没什么区别)，可以直接获取到对应的全部列的数据，而非聚集索引在索引没有覆盖到对应的列的时候需要进行二次查询。因此在查询方面，聚集索引的速度往往会更占优势。

非聚集索引：该索引中索引的逻辑顺序与磁盘上行的物理存储顺序不同，一个表中可以拥有多个非聚集索引。

其实按照定义，除了聚集索引以外的索引都是非聚集索引，只是人们想细分一下非聚集索引，分成普通索引，唯一索引，全文索引。如果非要把非聚集索引类比成现实生活中的东西，那么非聚集索引就像新华字典的偏旁字典，他结构顺序与实际存放顺序不一定一致。

非聚集索引的二次查询问题

非聚集索引叶节点仍然是索引节点，只是有一个指针指向对应的数据块，此如果使用非聚集索引查询，而查询列中包含了其他该索引没有覆盖的列，那么他还要进行第二次的查询，查询节点上对应的数据行的数据。

有表t1：

其中有 聚集索引clustered index(id), 非聚集索引index(username)。

使用以下语句进行查询，不需要进行二次查询，直接就可以从非聚集索引的节点里面就可以获取到查询列的数据。

select id, username from t1 where username = '小明'

select username from t1 where username = '小明'

但是使用以下语句进行查询，就需要二次的查询去获取原数据行的score：

select username, score from t1 where username = '小明'

在SQL Server里面查询效率如下所示，Index Seek就是索引所花费的时间，Key Lookup就是二次查询所花费的时间。可以看的出二次查询所花费的查询开销占比很大，达到50%。

总结如下：动作描述使用聚集索引使用非聚集索引

列经常被分组排序应应

返回某范围内的数据应不应

一个或极少不同值不应不应

小数目的不同值应不应

大数目的不同值不应应

频繁更新的列不应应

外键列应应

主键列应应

频繁修改索引列不应应