mysql的各种索引总结

mysql优化总结

首先是在创建索引的时候，我们需要注意根据不同的业务特点选择不同的索引，因为不同的索引有各自的优缺点

主键索引：它的叶子节点上是主键和整行数据，非叶子节点上是主键，如果你不需要查询所有的数据的话，使用主键索引就可能导致你需要把整行数据都从磁盘读写到内存中，加大了IO负担，但是使用主键索引肯定是没有回表操作的。因为它是唯一的，不需要像普通索引那样非要找到不满足条件的数据才罢休。

**普通索引：**它的叶子节点是索引的值和主键id，所以如果你在普通索引上查询完之后，还需要去主键索引上再次查询，也就是回表。但是覆盖索引可以解决，也就是你把需要查询的字段全部都放入联合索引中，就不需要回表查询了。

前缀索引：使用某个字段的前缀来做索引，可以减少空间占有，增大IO时候数据页上的有效数据量，

但是如果前缀设置的长度不好的话，会增加扫描的行数。

**联合索引：**几个字段一起做索引，如果建立联合索引之后查询可以用到覆盖索引，那么就不需要回表了，但是联合索引建立的时候顺寻特别重要，尽量使这个索引复用率高，且大的字段尽量靠前，因为这样的话，后期单独字段需要建立索引的话，只需要将后面的小字段建立索引就好，空间利用率就高了。5.6之后还有个索引下推的优化，这样的话，联合索引也可以减少回表查询的次数。

尽量不要使用长事务：锁定太多数据，可能会造成大量阻塞和锁超时，回滚需要的时间比较长，执行时间长，容易造成主从延迟，会造成回滚日志不能及时清理，回滚日志占用大量空间。长事务会一直占着MDL锁，可能会造成整个库的线程很快爆满。

解决：尽量避免一次性处理太多大数据，不必要的select操作从事务中移出来，监控information_schema.Innodb_trx这个表，设置长事务的阈值，超过这个阈值就报警或者kill。

什么字段需要建立索引：比如频繁需要查询的字段，或者频繁需要修改的字段但是不经常查询的字段可以建立普通索引通过change buffer减少持久化到磁盘的次数。

有时候优化器会选错索引：因为索引判断扫描行数的时候是用的统计计数的方式，就是扫描几个数据页是数据，然后看有几个数据页，进行一个统计计数，达到一定条件才会去重新统计，期间就会导致统计不准确而选错索引，我们可以通过explain的row字段进行判断，如果差的太多，可以手动进行重新统计。还有可以通过手动指定索引forcd index（）但是这样并不好，可以作为应急方案，还可以通过删除其他的索引达到最优解。

比如一个服务运行一段时间后查询效率下降了，排查思路：

首先是服务器方面：先看服务器的内存是否满了，cpu是不是过高，io是不是满了。

然后就是数据库方面：可以开启慢查询日志，定位哪些sql执行慢，然后通过explatin去判断是否是没用上索引，索引是否用对了。

mysql突然插入速度变慢

可能是redo_log写满了，正在刷脏页，如果经常出现这中情况的话，可能是数据库设置的刷脏页的速度太小了，这时候可以将速度调大，根据你磁盘的io速度来调，还可以将mysql默认的“连坐机制取消”，“连坐机制”就是如果刷A脏页的时候，旁边的B也是脏页，也就连着一起刷，就会出现“连坐”。