MySQL优化思路-后端
基本思路
首先是如何定位性能瓶颈?
一般使用show命令、慢查询日志、explain、profiling等进行分析。
- 打印慢查询日志,找出执行慢的查询
- 当服务器的负载增加时,使用
SHOW PROCESSLIST
来查看有问题的查询 EXPLAIN
查看SELECT
时索引命中的情况,分析查询语句和表结构
根据分析结果,优化索引和查询,优化表结构;这是后端小伙伴可以着手的部分。然后,还可以找DBA优化MySQL配置和硬件。
查询优化
-
为搜索字段建索引,合理使用 (第一条,因为它最关键)
- 在
WHERE
、GROUP BY
和ORDER BY
的列上加上索引 - 尽量保证索引简单,避免在同一列上加多个索引
- 有时
MySQL
会选择错误的索引,可考虑使用USE INDEX
- 查询字段存在索引中,考虑
Cover Index
- 在
-
当只要一行数据时使用
LIMIT 1
-
避免
SELECT *
- 从数据库里读出越多的数据,那么查询就会变得越慢
- 数据多会增加网络传输的负载
-
利用MySQL的查询缓存
- 使用像
NOW()
和RAND()
或是其它类似的SQL函数,都不会开启查询缓存 - 保持查询条件次序一致
第一次查select col from tab where a = 'x' and b = 'y'
下次查是select col from tab where b = 'y' and a = 'x'
,就无法利用查询缓存了
- 使用像
-
WHERE
子句- 避免对于null的判断,否则会导致全表扫描
- 避免使用
!=
或<>
操作符,否则会造成后面的索引字段失效 - 不使用
%
前缀模糊查询- 例如
like “%name”
或者like “%name%”
,这种查询会导致索引失效 - 但是可以使用
like “name%”
- 例如
- 避免进行表达式操作
select uid from user where age*2=36;
会导致索引失效
- 使用同类型进行比较,比如用 ‘123’ 和 ‘123’ 比,123 和 123 比
-
LIMIT M,N
节制使用- 随着表数据量的增加,使用
LIMIT
分页会越来越慢 - 可以采用记录上次查询结果ID的方法实现翻页
- 随着表数据量的增加,使用
-
IN
和OR
- OR 改写成 IN:OR的效率是
O(n)
级别,IN 的效率是log(n)
级别 IN
包含的值不应过多,如果较多,产生的消耗也是比较大的,IN 的个数建议控制在 200 以内
- OR 改写成 IN:OR的效率是
-
COUNT()
- 采用
InnoDB
时避免在整个表上使用count(*)
,它可能会导致整个表hang住,因为count操作太耗时了 InnoDB
是没有保存rows count的,因为在不同transaction中,看到的行可能不一样InnoDB
通过扫描索引来计数,count全表时,需要遍历全部索引;当索引不再内存中时,还需要从磁盘读取,会更慢更消耗资源- 如果只需要一个大概的行数,可以用
SHOW TABLE STATUS
MyISAM
引擎不一样,维护了count值,SELECT COUNT(*)
能马上返回
- 采用
-
JOIN
- 尽可能避免复杂的join和子查询
- 建议减少对大表的 join 查询,
InnoDB
引擎会产生行锁;MyISAM
引擎会产生表锁,会导致其他写操作被阻塞 - 尽量使用inner join,避免left join;没有其他过滤条件时,MySQL默认会自动选择小表作为驱动表
设计优化
-
每张表都设置一个ID做为其主键
- 推荐使用
UNSIGNED INT
,设置上AUTO_INCREMENT
标志
- 推荐使用
-
把IP地址存成
UNSIGNED INT
- 用
VARCHAR(15)
字段的字符串来存IP不划算 - 如果用整形来存放只需要4个字节,并且可以有定长的字段
- 用
-
固定长度的表会更快
- 固定的长度是很容易计算下一个数据的偏移量的,所以读取的自然也会很快
- 有
VARCHAR,TEXT,BLOB字
字段;这个表就不是“固定长度静态表”了
-
越小的列会越快
- 对于大多数的数据库引擎来说,硬盘操作可能是最重大的瓶颈
- 数据变得紧凑会,在一个内存页可以读取更多行数据,可以减少了对硬盘的访问
-
尽可能的使用
NOT NULL
- NULL无法利用索引,且需要额外的空间
- NULL在业务逻辑层需要额外的处理
-
选择正确的存储引擎
- OLTP业界用
InnoDB
比较多,InnoDB
支持行锁 MyISAM
使用Cluster Index, 不支持行锁,适合于一些需要大量查询的应用
- OLTP业界用
-
大表水平分拆
- 降低每个表的行数,加快查询
- 比如可以按
user_id%100
分100个表
-
多字段表垂直分拆
- 把数据库中的表按列变成几张表
- 降低表的复杂度和字段的数目
-
不适合建索引的情况
- 区分度不高的字段,不适合建索引;消耗资源,对性能提升不大
- 乱序的字段不适合作为索引,如
MD5
,UUID
;否则,在插入数据时,更新索引需在B+树中大量移动结点,导致较多硬盘IO - 更新非常频繁的字段不适合创建索引;原因同上
-
适度冗余,让Query尽量减少Join
-
不推荐使用外键
Foreign Key
其他优化
- 重启MySQL时,记得预热数据库,确保将数据加载到内存,提高查询效率
- 使用Redis等分布式缓存或本地缓存,减少对数据库的访问
- 考虑持久连接或连接池,而不是多次建立连接,以减少资源的消耗
- 进行分表时,可以考虑双写,以平稳过渡,防止数据丢失
本文由 络壳 原创或整理,转载请注明出处