MySQL性能优化

本文总结一些常见的Mysql优化思路及具体手段

优化方向

• 软件方面
  • 表创建时的考量
  • SQL优化的奇淫技巧
  • 索引层面
  • 分库分表
• Mysql方面
  • 升级
  • 连接数
  • 缓存
• 硬件方面
  • 硬盘
  • 内存
  • CPU
  • 网络

软件方面

软件方面是指我们使用Mysql的代码侧

我们可以从整个软件开发的过程来梳理下, 有哪些方面可以改进;

表创建时

建表是我们开始使用Mysql的开端, 我们要为之后的使用开个好的头

在此阶段我们主要考量有:

• 表的引擎
• 主键及类型
• 每列选择合适的类型及长度, 缺省的默认值
• 索引

表引擎

Mysql的表引擎有InnoDB, MyISAM, Memory…

InnoDB与MyISAM区别如下:

一般来讲, 只有确定业务没有事务要求且主要以select为主才会选择MyISAM, 其它情况都是用InnoDB, 所以我们的选择空间并不大; 下面也以InnoDB为讨论对象.

主键

在建表时, 推荐保证表有主键列, 最好是id bigint unsigned auto increment, 这个是兼顾InnoDB聚集索引的特性, 参考;

阿里发布的Java开发规范里也有提到这点

1
2
3
9. 【强制】表必备三字段:id, gmt_create, gmt_modified。 
    说明:其中id必为主键，类型为unsigned bigint、单表时自增、步长为1。
    gmt_create, gmt_modified 的类型均为 date_time 类型，前者现在时表示主动创建，后者过去分词表示被 动更新

列的类型及长度, 默认值

其次, 每个列尽量选择合适的类型和长度; 合适的长度可以更省空间, 更能提升检索速度; 常见Mysql数据类型占用空间大小

在具体实践中, 比如IP列, 如果明文保存, 使用varchar(15)就行了, 有些人甚至在程序里把ip转成long然后使用bigint存储, 查询的时候用inet_ntoa转换下; 比如最后修改时间列, 一般用TIMESTAMP, 还可自动更新; 一些status, category, type之类的数据, 推荐使用tinyint unsigned;

在建表时, 除了列的类型选择合适之外; 还有一点, 列不要使用null值, 有以下几个理由:

• 有null值的列, mysql索引不好优化; 后期对有null列增加索引不方便, 尤其是唯一索引
• 所有使用null的列都可以通过给个有意义的默认值来实现, 这样业务上更友好, 也不容易出错
• 在使用not in, != 等条件查询时, 统计聚合类查询时, 容易出错 参考
• is null, is not null 等语句是不走索引(待考证), 参考
• null是占用空间, 空值’‘反而不占用空间(待考证)

索引

在后面索引优化里再一起总结

SQL优化

SQL优化主要是重构一些低效的写法, 或者写法没有命中索引; 首先介绍下explain命令

1
2
3
4
5
6
mysql> explain select * from user;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
|  1 | SIMPLE      | user  | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+

我们关注的目标列是type, key, key_len, rows, Extra

type列表示我们这条SQL走的查询类型, 常用类型如下:

• const: 常量级, 一般都是主键查询; 性能非常好, 我们的目标
• ref: 索引, 一般是索引查询命中; 性能很好, 我们的目标
• range: 索引, 一般是索引范围查询命中; 性能还可以, 我们的目标
• index: 全索引查询; 性能渣渣, 尽量避免, 除非索引很小
• ALL: 全表查询; 性能渣渣渣渣, 尽量避免, 除非表很小

key列如果有值, 表示使用的索引名; 当表有多条索引时, 用来判断是否使用了我们期望的索引

key_len列表示索引的长度, 如果有些列的前缀区分度很大, 我们可以使用该列的部分值而非全列

rows列表示该SQL检索的行数; 首先, 这个值应该越小越好; 其次, explain返回的只是个估计值, 只能做参考

Extra列里有些信息对我们优化还是有很大帮助的

• Using filesort: 发生了硬盘或内存排序,一般是由order by 或 group by触发的
• Using temporary :使用了临时表,通常和filesort一起发生
• Using where: 数据库服务层从存储引擎提取了数据又进行了一次条件过滤

SQL的优化依据和方向就是explain命令的结果

1. type值不是const, ref, range
   1. 索引存不存在; 如果索引不存在, 添加索引, 重复步骤1
   2. 索引存在; 分析为什么没有走索引, 修改SQL写法, 重复步骤1; 一般都是SQL写法问题, 下面会通过一些典型案例说明
   3. SQL是不是太复杂, 大SQL, 要不要拆分
2. type值是const, ref, range; rows很大
   1. 是不是索引列区分度不大; 分析数据
   2. 是不是表数据太大; 分库分表
3. type值是const, ref, range; rows很小; 完美, 结束

SQL这块有太多的坑可以踩, 但是也很零散没有系统性, 现在列举一些典型的, 持续补充中

子查询替换为join方式

分页查询优化

select * from user limit #{start}, #{length}

MySQL分页查询的原理是, 先查出所有的数据集, 然后skip start条, 再取length条出来; 所以当数据集很大时, skip就比较耗时了

我们可以这样优化

select * from user where id>#{last} limit #{length}

其中last 是上一次查询结果的最后一行数据userID; 第一次当然last=0

大SQL分拆成多个小SQL

尽可能单表操作; 除非是管理后台, OA类的应用, 允许有复杂的查询

避免 select *, 需要哪些列明确写出来

1. 语义更明确
2. 返回的数据包更小
3. 如果要返回的列刚好就是索引, 会直接走索引树不用到达叶子结点, 性能更好

索引层面优化

索引是数据库提升检索速度的重要手段; 索引并非是银弹, 索引会降低数据插入性能, 并且索引也占用存储空间; 另外列区分度太低, 不太适合使用索引, 比如性别列;

建立索引的一些经验

1) 尽可能用组合索引代替多个单独索引, 遵从最左匹配原则

比如某表有几个索引, 索引1, 在A, B, C列上; 索引2, 在A, B列上; 索引3, 在A列上, 我们只用保留索引1即可

2) 索引的长度尽可能短

比如索引建在varchar类型的列上, 如果前8个字符的区分度足够大, 那只用对列的前8长度的内容建立索引比较合适

3) 对在where里的列, join的列建立索引; 前提条件是列的区分度够大, sql场景比较重要

常见索引失效的用例

1) 对索引列有计算操作或函数操作

select * from student where age + 1 < 10; – 不会走索引, 有计算操作

select * from student where left(birthday, 4) < 1990; – 不会走索引, 有函数操作

2) 不满足最左匹配原则

select * from student where name like “%明%”;

3) 数据类型不一致

select * from student where student_id=1234;

不会走索引, student_id是varchar(4)类型, 为了存储0001这样的学号;

4) 对于查询条件是or组合, 要每个子条件都建立了索引

1
2
3
4
5
6
mysql> explain select * from comment where author = '0161098' or reply_id=80851404; 
+----+-------------+---------+------------+------+---------------+------+---------+------+---------+----------+-------------+
| id | select_type | table   | partitions | type | possible_keys | key  | key_len | ref  | rows    | filtered | Extra       |
+----+-------------+---------+------------+------+---------------+------+---------+------+---------+----------+-------------+
|  1 | SIMPLE      | comment | NULL       | ALL  | idx_author    | NULL | NULL    | NULL | 9866936 |    10.00 | Using where |
+----+-------------+---------+------------+------+---------------+------+---------+------+---------+----------+-------------+

author列有索引, reply_id没有索引, 所以没走索引

1
2
3
4
5
6
mysql> explain select * from comment where author = '0161098' or article=474568; 
+----+-------------+---------+------------+-------------+------------------------+------------------------+---------+------+------+----------+--------------------------------------------------+
| id | select_type | table   | partitions | type        | possible_keys          | key                    | key_len | ref  | rows | filtered | Extra                                            |
+----+-------------+---------+------------+-------------+------------------------+------------------------+---------+------+------+----------+--------------------------------------------------+
|  1 | SIMPLE      | comment | NULL       | index_merge | idx_article,idx_author | idx_author,idx_article | 30,4    | NULL |   13 |   100.00 | Using union(idx_author,idx_article); Using where |
+----+-------------+---------+------------+-------------+------------------------+------------------------+---------+------+------+----------+--------------------------------------------------+

author, article都有索引, 所以走了索引

从这里的explain结果可以猜测一下or的实现方式, 应该是分别对每个条件分别进行查询, 然后对结果进行merge, 这也符合or的语义; 所以整个语句的性能取决于子条件里最慢的那个

5) 不使用NOT IN, <>、!=操作，但<,<=，=，>,>=,BETWEEN,IN是可以用到索引的

1
2
3
4
5
6
mysql> explain select * from comment where author!='0342587' and reply_id=63928185 limit 10;
+----+-------------+---------+------------+-------+---------------+------------+---------+------+---------+----------+-----------------------------------------------+
| id | select_type | table   | partitions | type  | possible_keys | key        | key_len | ref  | rows    | filtered | Extra                                         |
+----+-------------+---------+------------+-------+---------------+------------+---------+------+---------+----------+-----------------------------------------------+
|  1 | SIMPLE      | comment | NULL       | range | idx_author    | idx_author | 30      | NULL | 9866936 |    10.00 | Using index condition; Using where; Using MRR |
+----+-------------+---------+------------+-------+---------------+------------+---------+------+---------+----------+-----------------------------------------------+

虽然author列有索引, type也是range, 但是查询的行数是9866936, 基本是全表扫描了;

记住一点, B+树的叶子结点适合范围查询; 但是要进行判断不等, 要把所有叶子都要遍历一次才能知道

6) 还有情况, 当MySQL判断直接全表扫描比使用索引快, 也就不走索引了

分库分表

有些时候当表的数据量很大的时候, 上面的常规手段可能效果并不明显, 而通过物理手段的分库分表优化手段效果可能就很明显

至于什么时候应该分库分表, 可以看看这篇文章的分析

分库分表之后, 还能提升MySQL DDL操作的性能

分库还是分表

• 如果数据增长速度非常快, 在可预见的时间内, 可能会超过单机的硬盘大小, 建议早做分库的准备
• 其它情况, 建议用分表, 毕竟实现起来简单

怎么分表

分库跟分表的思路相同, 在此讨论分表的操作

可以按某列进行hash再取模X的方式把表分成X张; 能这样做的前提是, 表只按此列进行查询, 表的行之间没有关联性; 比如订单表, 如果我们只需要查询我的订单信息, 那只需要按userId进行水平切分即可;

还有时表的查询维度可能有多个, 比如评论表, 既要按文章ID查询, 又要按用户维度查询; 这就可以按冷热分表, 最近1年的评论数据为热数据, 99.99%的查询都落在此表; 1年前的评论为冷数据, 0.01%的查询

上述的分表方式并非是通用, 各个业务场景的分表方式是灵活的, 原则就是优先优化查询最大的场景, 然后尽可能平衡其它场景; 如果平衡不了, 通常通过中间表的方式来解决

比如上面说的订单表, 如果运营想看, 每天的总成交额, 失败订单, 我们可以写个定时任务, 扫描订单表, 把运营需要的数据写入另一个表, 也就是中间表; 这样做会带来数据一致性的问题, 具体场景下具体分析, 然后选择合适的策略

分表扩容操作

• 业务停机, 等扩容操作完成之后再启动
  • 一般选择在凌晨4,5点操作
  • 几年前公司常用方法
• 业务代码兼容
  • 后台运行迁移脚本; 遍历每个分表, 按新路由策略把数据迁移到新表中
  • 业务代码写时, 按新的分表策略写
  • 业务代码读时, 按新旧的分表策略读, 直到数据迁移完成

Mysql方面

升级

一般来讲, 最新稳定版本性能都会比历史版本有所提升, 功能也更加丰富, 安全性也更好; 所以, 能升级Mysql Server就尽可能升级吧

连接数

合理的连接数设置, 默认是100, 当业务量增长很快的时候, 可能连接数会达到瓶颈; 但是连接数太大也是有成本和风险的

当没有慢SQL的前提下, 逐渐加大连接数, 观察效果, 反复操作;

如果有慢SQL, 要还设置更大的连接数, 只会让MySQL负载更大, 死的更快; 我们要重点持续关注优化慢SQL 这个道理跟web接口一样, 接口性能越好, 服务器连接数越少; 反之, 接口性能越差, 连接数积压, 服务器负载上升, 然后恶性循环

缓存

开启MySQL缓存也是能提升一些性能的; 但是要注意缓存击穿情况, 避免负优化

硬件方面

这个没什么好说的, 非常硬核的优化, 有条件的尽量用最高配置的服务器, 不仅省心省力, 而且见效快

硬盘

数据最终是写到硬盘, 查询时是从硬盘读到内存的; 一个高IOPS的硬盘对整个写入查询的提升帮助巨大; 推荐SSD, raid5

内存

查询与写入, 都依赖缓存; 内存一般64G起步

CPU

当有大概聚合操作, 运算类函数时, CPU可能就会成为瓶颈; 性能越强越好, 核越多越好

网络

MySQL与Client端一般都是分别部署在不同的机器, 通过网络来交换数据的; 当QPS上升, 网络也可能会成为瓶颈

不过一般都是走内网, 基本都是千兆网卡, 出问题的概率不大

参考

• mysql 字段定义不要用null的分析
• MySQL中NULL对索引的影响

• Previous
  Java对象内存布局
• Next
  Mysql数据类型的大小及范围