深入MySQL优化:提升数据库性能的关键策略

数据库 MySQL
在现代互联网场景下,其实很少需要在一个事务中对同一行数据读取两次的情况,RR隔离级别实用性不高,而且因为加了间隙锁和Next-Key Lock,性能有所降低,死锁概率也相对较高。

MySQL优化

从设计上,可根据需要:分库分表、读写分离、冷热分离、使用缓存、定期进行数据清理。

从客户端使用上,使用连接池、避免大事务、返回数据多使用物理分页。

从优化MySQL配置文件上,调整MySQL配置文件中的参数,如缓冲区大小、最大连接数等,以适应应用程序的需要。

从优化表结构上,使用合适的存储引擎;避免使用大型或不必要的列,并尽可能使用小型数据类型;尽量把字段设置为NOT NULL;对于某些文本字段来说,例如“省份”或者“性别”,我们可以将他们定义为ENUM(枚举)类型。因为在MySQL中,ENUM类型被当做数值型数据来处理,而数值型数据被处理起来的速度要比文本类型要快得多。

从优化查询上,善用EXPLAIN查看SQL执行计划;使用连接(JOIN)来代替子查询,减少在内存中创建临时表;尽量用union all代替union减少排序;利用小表去驱动大表,减少嵌套循环中的循环次数,以减少 IO总量及CPU运算的次数;善用索引。 

查询在什么时候不走索引、索引失效 

1.不满足走索引的条件,常见的情况有 

1.1 不满足最左匹配原则 

1.2 查询条件使用了函数 

1.3 or操作有一个字段没有索引 

1.4 使用like条件以%开头 

1.5 显式或隐式类型转换导致索引失效 

1.6 存在范围查询,比如between、>、<等条件时,会造成后面的索引字段失效 

2.走索引效率低于全表扫描,常见的情况有 

2.1 查询条件对null做判断,而null的值很多   

2.2 一个字段区分度很小,比如性别、状态 

3.需要回表的查询结果集过大,超过了配置的范围 

Explain解释 

type列,连接类型。一个好的SQL语句至少要达到range级别。杜绝出现all级别。 

key列,使用到的索引名。如果没有选择索引,值是NULL。可以采取强制索引方式。 

key_len列,索引长度

rows列,扫描行数。该值是个预估值。 

extra列,详细说明。注意,常见的不太友好的值,如下:Using filesort,Using temporary。 

如何解决大事务问题? 

解决大事务问题常规的方法有:避免远程调用、避免一次性处理太多数据、覆盖方法粒度尽量小。

具体到解决问题,需要考虑是什么、为什么、怎么办三个方面。

是什么是要界定大事务。比如可以把大事务分成了三个级别,分别是1s以上,500ms以上和100ms以上。1s以上会严重影响数据库性能,有很大概率会造成整体链路超时,是重要紧急的。之前通过事务中不包含外部调用之后,偶尔会有100ms左右的大事务,超过120ms的基本没有。这些我们记录在一个文档中,在涉及其他相关修改时,一起通过梳理逻辑优化解决。

如果把RR改成RC需要注意哪些问题?

在现代互联网场景下,其实很少需要在一个事务中对同一行数据读取两次的情况,RR隔离级别实用性不高,而且因为加了间隙锁和Next-Key Lock,性能有所降低,死锁概率也相对较高。在历史上,RR确实解决了mysql主从复制时的一个问题。在早期的mysql版本中,只有statement这种bin log格式,这时候,如果使用提交读(Read Committed)、未提交读(Read Uncommitted)这两种隔离级别会因为回放顺序而出现与主库数据不一致。MySQL是在5.1.5版本开始支持row的、在5.1.8版本中开始支持mixed,后面这两种可以代替 statement格式。RC 隔离级别只支持row格式的binlog。如果指定了mixed作为 binlog 格式,那么如果使用RC,服务器会自动使用基于row 格式的日志记录。 


责任编辑:武晓燕 来源: 编程一生
相关推荐

2024-07-23 08:08:18

2009-06-30 22:31:23

关键参数MySQL性能优化

2010-11-15 16:13:24

Oracle数据库性能

2010-04-20 10:49:45

Oracle数据库

2012-11-21 17:35:21

Oracle技术嘉年华

2011-03-08 08:49:55

MySQL优化单机

2013-03-29 09:28:41

2024-09-04 14:28:20

Python代码

2023-09-26 12:02:34

C++循环

2013-09-17 10:32:08

Android性能优化数据库

2023-12-14 12:56:00

MongoDB数据库优化

2009-03-30 14:19:26

优化数据库MySQL

2010-05-21 13:48:36

MySQL数据库

2010-06-02 13:58:30

MySQL数据库性能

2010-05-31 16:17:56

MySQL数据库性能

2024-01-18 09:43:11

MySQL数据库

2011-03-28 15:44:45

惠普数据库Oracle数据库

2011-03-03 17:56:52

MySQL数据库优化

2023-03-13 00:01:10

数据库性能MySQL

2021-01-31 17:50:41

数据库查询程序员
点赞
收藏

51CTO技术栈公众号