性能优化
MySQL性能优化
MySQL 性能指标都有哪些?如何得到这些指标?
MySQL 的性能指标如下:
① TPS(Transaction Per Second) 每秒事务数,即数据库每秒执行的事务数。
MySQL 本身没有直接提供 TPS 参数值,如果我们想要获得 TPS 的值,只有我们自己计算了,可以根据 MySQL 数据库提供的状态变量,来计算 TPS。
需要使用的参数:
- Com_commit :表示提交次数,通过命令
show global status like 'Com_commit';
获取; - Com_rollback:表示回滚次数,通过命令
show global status like 'Com_rollback';
获取。
我们定义第一次获取的 Comcommit 的值与 Comrollback 值的和为 c_r1,时间为 t1;
第二次获取的 Comcommit 的值与 Comrollback 值的和为 cr2,时间为 t2,t1 与 t2 单位为秒。 那么 TPS = ( cr2 - c_r1 ) / ( t2 - t1 ) 算出来的就是该 MySQL 实例在 t1 与 t2 生命周期之间的平均 TPS。
② QPS(Query Per Second) 每秒请求次数,也就是数据库每秒执行的 SQL 数量,包含 INSERT、SELECT、UPDATE、DELETE 等。 QPS = Queries / Seconds Queries 是系统状态值—总查询次数,可以通过 show status like 'queries';
查询得出,如下所示:
Seconds 是监控的时间区间,单位为秒。 比如,采样 10 秒内的查询次数,那么先查询一次 Queries 值(Q1),等待 10 秒,再查询一次 Queries 值(Q2),那么 QPS 就可以通过,如下公式获得:
QPS = (Q2 - Q1) / 10
③ IOPS(Input/Output Operations per Second) 每秒处理的 I/O 请求次数。
IOPS 是判断磁盘 I/O 能力的指标之一,一般来讲 IOPS 指标越高,那么单位时间内能够响应的请求自然也就越多。理论上讲,只要系统实际的请求数低于 IOPS 的能力,就相当于每一个请求都能得到即时响应,那么 I/O 就不会是瓶颈了。
注意:IOPS 与磁盘吞吐量不一样,吞吐量是指单位时间内可以成功传输的数据数量。
可以使用 iostat 命令,查看磁盘的 IOPS,命令如下:
yum install sysstat iostat -dx 1 10
执行效果如下图所示:
IOPS = r/s + w/s 其中:
- r/s:代表每秒读了多少次;
- w/s:代表每秒写了多少次。
什么是慢查询?
慢查 询是 MySQL 中提供的一种慢查询日志,它用来记录在 MySQL 中响应时间超过阀值的语句,具体指运行时间超过 longquerytime 值的 SQL,则会被记录到慢查询日志中。longquerytime 的默认值为 10,意思是运行 10S 以上的语句。默认情况下,MySQL 数据库并不启动慢查询日志,需要我们手动来设置这个参数,如果不是调优需要的话,一般不建议启动该参数,因为开启慢查询日志会给 MySQL 服务器带来一定的性能影响。慢查询日志支持将日志记录写入文件,也支持将日志记录写入数据库表。
使用 mysql> show variables like '%slow_query_log%';
来查询慢查询日志是否开启,执行效果如下图所示:
slowquerylog 的值为 OFF 时,表示未开启慢查询日志。
如何开启慢查询日志?
开启慢查询日志,可以使用如下 MySQL 命令:
mysql> set global slowquerylog=1
不过这种设置方式,只对当前数据库生效,如果 MySQL 重启也会失效,如果要永久生效,就必须修改 MySQL 的配置 文件 my.cnf,配置如下:
slowquerylog =1 slowquerylogfile=/tmp/mysqlslow.log
如何定位慢查询?
使用 MySQL 中的 explain 分析执行语句,比如:
explain select * from t where id=5;
如下图所示:
其中:
- id — 选择标识符。id越大优先级越高,越先被执行。
- select_type — 表示查询的类型。
- table — 输出结果集的表
- partitions — 匹配的分区
- type — 表示表的连接类型
- possible_keys — 表示查询时,可能使用的索引
- key — 表示实际使用的索引
- key_len — 索引字段的长度
- ref— 列与索引的比较
- rows — 大概估算的行数
- filtered — 按表条件过滤的行百分比
- Extra — 执行情况的描述和说明
其中最重要的就是 type 字段,type 值类型如下:
- all — 扫描全表数据
- index — 遍历索引
- range — 索引范围查找
- index_subquery — 在子查询中使用 ref
- uniquesubquery — 在子查询中使用 eqref
- refornull — 对 null 进行索引的优化的 ref
- fulltext — 使用全文索引
- ref — 使用非唯一索引查找数据
- eq_ref — 在 join 查询中使用主键或唯一索引关联
- const — 将一个主键放置到 where 后面作为条件查询, MySQL 优化器就能把这次查询优化转化为一个常量,如何转化以及何时转化,这个取决于优化器,这个比 eq_ref 效率高一点
MySQL 的优化手段都有哪些?
MySQL 的常见的优化手段有以下五种:
① 查询优化
- 避免 SELECT *,只查询需要的字段。
- 小表驱动大表,即小的数据集驱动大的数据集,比如,当 B 表的数据集小于 A 表时,用 in 优化 exist,两表执行顺序是先查 B 表,再查 A 表,查询语句:select * from A where id in (select id from B) 。
- 一些情况下,可以使用连接代替子查询,因为使用 join 时,MySQL 不会在内存中创建临时表。
② 优化索引的使用
- 尽量使用主键查询,而非其他索引,因为主键查询不会触发回表查询。
- 不做列运算,把计算都放入各个业务系统实现
- 查询语句尽可能简单,大语句拆小语句,减少锁时间
- 不使用 select * 查询
- or 查询改写成 in 查询
- 不用函数和触发器
- 避免 %xx 查询
- 少用 join 查询
- 使用同类型比较,比如 '123' 和 '123'、123 和 123
- 尽量避免在 where 子句中使用 != 或者 <> 操作符,查询引用会放弃索引而进行全表扫描
- 列表数据使用分页查询,每页数据量不要太大
- 用 exists 替代 in 查询
- 避免在索引列上使用 is null 和 is not null
- 尽量使用主键查询
- 避免在 where 子句中对字段进行表达式操作
- 尽量使用数字型字段,若只含数值信息的字段尽量不要设计为字符型
③ 表结构设计优化
- 使用可以存下数据最小的数据类型。
- 使用简单的数据类型,int 要比 varchar 类型在 MySQL 处理简单。
- 尽量使用 tinyint、smallint、mediumint 作为整数类型而非 int。
- 尽可能使用 not null 定义字段,因为 null 占用 4 字节空间。
- 尽量少用 text 类型,非用不可时最好考虑分表。
- 尽量使用 timestamp,而非 datetime。
- 单表不要有太多字段,建议在 20 个字段以内。