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 格式的日志记录。