mysql 常见优化手段及死锁优化整理

自律给你自由

已于 2022-04-18 09:02:43 修改

阅读量955

点赞数 1

分类专栏： mysql 文章标签：索引优化查询优化事务处理死锁避免性能调优

于 2022-04-17 11:57:45 首次发布

本文链接：https://blog.csdn.net/zzy916853616/article/details/124112014

版权

mysql 专栏收录该内容

1 篇文章 0 订阅

订阅专栏

索引的优化：

查询条件要建立索引，否则会走全表扫描，更新数据时则会上升到表锁。创建索引时要看区分度。区分度越高越好。因为区分度越高，意味着重复的键值越少。
直接创建完整索引，这样可能比较占用空间；但是可以利用覆盖索引优化避免回表。
创建前缀索引，节省空间，但会增加查询扫描次数，并且不能使用覆盖索引。order by无法使用前缀索引。索引越长，占用磁盘就越大，相同数据页能放下的索引就越少，搜索的效率也就会越低。
倒序存储，再创建前缀索引，用于绕过字符串本身前缀的区分度不够的问题。
创建 hash 字段索引，查询性能稳定，有额外的存储和计算消耗不支持范围扫描。
使用自增字段作主键优化查询。使用自增主键每次插入的新数据就会按顺序添加到当前索引节点的位置，不需要移动已有的数据，当页面写满，就会自动开辟一个新页面。效率非常高。使用非自增主键，每次插入主键的索引值都是随机的，因此插入新的数据时，就可能会插入到现有数据页中间的某个位置，这将移动其它数据来满足新数据的插入，甚至需要从一个页面复制数据到另外一个页面，我们通常将这种情况称为页分裂。页分裂还有可能会造成大量的内存碎片，导致索引结构不紧凑，从而影响查询效率。
对条件字段使用函数操作，会让索引失效。
避免隐式函数转换。比如编码集不一样会导致隐式转换；在使用字符串索引时，记得带引号，不然也会涉及隐式转换
避免使用null作为默认值。因为null值会导致索引失效。默认可以设置为0、空串，如果是时间则可以设置为‘1970-01-01 00:00:00’。语句中使用!=null、<>、is null、is not null 会是所用失效。
整型比字符操作代价更低，字符集和字符校对规则使字符比较比整型复杂。尽量使用整型做类型字段。
join优化：

select * from A a inner join B b on a.id=b.id;

A是驱动表，走全表扫描，B是被驱动表走的是搜索树。如果使用 join 语句的话，需要让小表做驱动表。前提是可以使用被驱动表的索引。在决定哪个表做驱动表的时候，应该是两个表按照各自的条件过滤，过滤完成之后，计算参与 join 的各个字段的总数据量，数据量小的那个表，就是小表，应该作为驱动表。
limit优化：当limit 越大时效率越低。比如limit10000,20这种。可以采用id>上页最大id limit 20这种方式。但是建议页面就控制只能依次往后翻，不能上来就选很后面。这样很少会翻到这么后面。
减少查询返回的值，尽量不要直接select *,即可减少返回数据会降低网络开销，可能避免回表操作。
组合索引记得是左匹配原则，不然索引会失效。

查询条件中使用 or，且 or 的前后条件中有一个列没有索引，那么涉及的索引都不会被使用。使用UNION ALL来替换or操作。

SELECT a,b,c from table a ='a' or b='b' c='c'
改为如下：
SELECT a,b,c from table a ='' UNION ALL
SELECT a,b,c from table b ='b' UNION ALL
SELECT a,b,c from table c ='c' UNION ALL

避免写很复杂的大sql，应该拆为小sql，放到代码里面去计算。
exists和in的选择：外面查询的表大，子查询的表小，用IN；外面查询表小，子查询的表大，选择EXISTS；若两表数据差不多大则区别不大。

避免优化器选错索引：

使用force index来强制使用某个索引。
优化语句，引导mysql使用索引。一般order by 走索引效率会更高效，因为引擎层直接有排好的数据可直接使用，否则将会到server层去做排序，性能会降低。但是有一种特殊情况，比如where name='xx' order by orgcode;实际上使用name才是最优选择，可以使用order by orgcode+0让orgcode索引失效，从而会走name索引。
建立更合适的索引，或者删除选择错的索引。

插入优化：

插入数据时多条语句转换为一条语句：

INSERT INTO `name_list` (`type`, `name`, `gmt_create`, `gmt_modified`)
VALUES(0, NULL, '2022-04-16 09:07:30', '2022-04-16 09:07:30'), (0, NULL, '2022-04-16 09:07:30', '2022-04-16 09:07:30');

事务优化：

避免长事务。更新前的数据准备工作尽量放事务外，核心的几个语句才开启事务处理。因为长事务意味着系统里面会存在很老的事务视图。由于这些事务随时可能访问数据库里面的任何数据，所以这个事务提交之前，数据库里面它可能用到的回滚记录都必须保留，这就会导致大量占用存储空间。而且事务中使用的锁必须等事务提交才会释放，也增加了死锁的可能性。
结合业务场景使用低级别的数据隔离级别。隔离级别越高并发性能就越低。比如RC相比RR就不会有间隙锁，并发性就好些。
避免行锁升级为表锁。在 InnoDB 中，行锁是通过索引实现的，如果不通过索引条件检索数据，行锁将会升级到表锁，影响性能。

死锁优化：

在编程中尽量按照固定的顺序来处理数据库记录，假设有两个更新操作，分别更新两条相同的记录，但更新顺序不一样，有可能导致死锁；如下图所示（引用极客时间图片）
在允许幻读和不可重复读的情况下，尽量使用 RC 事务隔离级别，可以避免 gap lock 导致的死锁问题；
更新表时，尽量使用主键更新；
避免长事务，尽量将长事务拆解，可以降低与其它事务发生冲突的概率；
设置锁等待超时参数，我们可以通过 innodb_lock_wait_timeout 设置合理的等待超时阈值，特别是在一些高并发的业务中，我们可以尽量将该值设置得小一些，避免大量事务等待，占用系统资源，造成严重的性能开销。

死锁生产实践：

语句如下：

delete from label_message where waybill_id =xxxx67693 and code = 11
delete from label_message where waybill_id =xxxx70549 and code = 11

waybill_id 参数没有加引号，导致隐士函数转字符串。所以并没有走到 uniq_waybillId_code索引。而是走到了idx_code_value或者idx_code_time 这样就会把所有code=11的数据都锁住，其次应该是走了索引合并，导致锁住这两个索引，且加锁顺序还不一样导致的。事务1锁住idx_code_value上一堆数据，再去锁idx_code_time上一堆数据，事务2反向加锁。这样就导致了死锁。

以上内容参考大神文章，参考文献：

《Java 性能调优实战》Java性能调优实战_Java_调优-极客时间

《MySQL实战45讲》MySQL实战45讲_MySQL_数据库-极客时间