性能调优之MySQL优化--覆盖索引对性能的提升测试_在mysql中,以下哪种情况下使用覆盖索引会提高查询性能?a数据表中的行数很少b-CSDN博客

本文链接：https://blog.csdn.net/lk0328/article/details/112645459

接上一篇文章，其实性能优化除了之前所列的那么多以外，对于MySQL也是需要优化的。之前有想过MySQL的优化，无非就是看看是否使用索引，把select * 改成需要的字段以减少内存使用等等，当时也就觉得这样足够了。不过最近在看了极客时间的《MySQL实战45讲》后才发现原来MySQL的优化还是有很多很多地方需要考虑。

先安利一下这个课程，强烈推荐大家都来看一看。它比较系统的讲了MySQL的原理，常见问题，调优手段等等，更有优质的评论与解答。不管是开发，运维还是DBA都值得一看。

在学习中我也做了一些笔记，主要是针对开发者角度优化方面的，这里简单列出来分享一下。由于下面有太多的纯文字，也可以跳过这些到最后来看测试效果。

概念分享

1、使用覆盖索引，尽量避免回表。前缀索引不能避免回表。为避免排序，可以使用联合索引，如果可以，最好使用覆盖索引。

概念就不做说明了。这个是说两点：a、索引建立要合适，b、查询尽量少使用select * ，查询的字段最好索引都有。当然这也不是说无限制的去建索引，还是要考虑一个平衡点的

怎么样证明使用了覆盖索引呢，可以使用explain语句执行一个SQL，主要看最后的Extra的值。大致有如下几个类型：

Using index：这个就是说明使用了覆盖索引

Using index condition：这个是说使用了索引,但仍然需要回表查询，经常和下面这个一并出现

Using where：where条件中的一些字段没有走索引，但是需要根据这些字段值进行过滤

2、行锁是两阶段锁协议，所以建议可能存在锁竞争的数据行在最后一步操作。

这个点我们平常很少注意，认为不同的sql的执行顺序无所谓的。其实行锁是在需要的时候才会加锁。如果一个业务会频繁操作同一条记录的话，那这条更新语句就建议放到最后一步执行。

3、行锁/死锁优化。

对于同一行数据的频繁写入，极易产生行锁的锁等待，甚至是死锁。

解决这种场景的方案有两个：

A、在执行执行加入更新队列，保证不会竞争。

B、把一行操作拆成多行，减少单行并发量。

对于库存扣减来说，核心是防止超卖，所以采用了B方案比较好(只针对本文的一些说明，秒杀、超卖等概念并不是仅仅通过这样就能实现)。假如是一个统计某个功能使用次数之类的需求，就可以采用A方案。

4、写多读少的场景最好使用普通索引而不是唯一索引，尽量使用change buffer，对于机械硬盘来说，change buffer利用越高越好。

普通索引和唯一索引在查询上的性能差异很小，主要是更新上。

MySQL的更新是可以直接更新内存数据的，当下一次查询的时候将硬盘数据和内存数据做合并即可。但是由于唯一索引需要确定唯一性，所以每次更新都必须查一次硬盘数据才能确保唯一。这样就会导致写多读少的场景下，唯一索引的性能要远差于普通索引。

5、在mysql优化器选择错误的索引时，有几种处理方式。

A、在条件允许的情况下，删除该索引

B、增加条件诱导优化器选择新的索引，比如

i、order by 正确的索引

ii、增加新的where条件

iii、有时limit 1会使MySQL选择了一个它认为合适的索引，但却不是我们想要的。这时可以将limit语句加大一些，增加该索引的开销迫使优化器认为该索引不合适主动换成另一个我们想要的索引，然后再limit 1

vi、使用force index强制指定索引，但是这个不太建议，万一有一天索引被删改，有可能造成系统错误。

6、全字段排序

全字段排序会将查出的所有字段放入内存然后排序，如果数据量过大,内存一次性放不下，就会采用外部排序，就数据分割成N份，这样就导致了SQL执行过慢。为避免这种情况，可以设置SET max_length_for_sort_data = 16;mysql认为字段总长度超过这个阈值以后，就要换一个算法进行排序了，就是rowid排序，只在内存中存放排序字段，排序完成后再回表查一次数据即可。

7、如果可以使用被驱动表的索引，那么是可以使用join语句的，性能比两次单独查询略好一些。尽量使用小表驱动大表。小表指的是经过where条件过滤后数据量小，而不是表数据本身小。