当前位置：首页 > 文章列表 > 数据库 > MySQL > 多方面实现MySQL性能优化

多方面实现MySQL性能优化

来源：SegmentFault 2023-01-27 16:52:37 0浏览收藏

编程并不是一个机械性的工作，而是需要有思考，有创新的工作，语法是固定的，但解决问题的思路则是依靠人的思维，这就需要我们坚持学习和更新自己的知识。今天golang学习网就整理分享《多方面实现MySQL性能优化》，文章讲解的知识点主要包括MySQL，如果你对数据库方面的知识点感兴趣，就不要错过golang学习网，在这可以对大家的知识积累有所帮助，助力开发能力的提升。

多方面实现MySQL性能优化可从如下几个方面着手

SQL优化
索引优化
数据库(表)结构优化
系统配置优化
服务器硬件优化

SQL优化

开启慢查询记录日志，查找症状(很多时候都是一些慢查询拖累了整个数据库的性能)

#path可修改为绝对或者相对路径
log-slow-queries=slow-log-path 
#l查询时间超过2s记录
long_query_time=2 
#没有使用索引的查询记录
log-queries-not-using-indexes

sql

#查看log_query_time变量的值
show variables like "%long%";
#如果long_query_time的值不是期望值，重新设定
set global long_query_time=2;
#查询 slow_query_log 和 slow_query_log_file的值
show variables like "%slow%";
#开启慢查询日志 on或者ON都可以，不区分大小写
set global slow_query_log='on';
#慢查询日志文件路径可修改
set global slow_query_log='/data/mysql/slow.log'

慢查询日志分析工具有官方的mysqldumpslow 和pt-query-digest,后者更加精确详细

mysql命令行下配置
在配置文件中配置(my.cnf),配置完毕需要重启，不适合线上数据库

explain 分析sql的执行

table 查询的数据表
type (可能的值 const, eq_reg, ref, range, index,all)
主键或者唯一索引一般是const，性能最好
eq_reg 是一种范围查找，唯一索引，主键可能是此种查找
ref常见于连接查询，一个表基于另外一个索引的查找
range 基于索引的范围查找
index通常是对index的扫描
All 是表扫描
possible_keys 查询中可以使用的索引
key 查询中实际使用到的索引，为null表示没有使用索引
key_len 索引长度，越小越好
ref 显示索引的那一列被使用了，最好是一个常数
rows 扫描的行数
extra
出现using filesort 查询需要优化(group by)，出现using temporary需要优化(order by 时容易出现)

掌握一些sql的优化方法
max, count,子查询，group by，limit

索引优化

选择合适的列建立索引（在where中经常出现的查询条件的列应当创建索引,group by ，order by，on)

索引字段越小越好
离散度大的列放在联合索引的前面（离散度越大，过滤的数据越多)
判断列的离散度可以根据select count(distinct col1), count(distinct col2) from table

索引优化SQL的方法
增加索引会影响写入效率(insert,update,delete)
删除重复和冗余的索引
使用工具pt-duplicate-key-checker分析
使用pt-index-usage 工具配合慢查询日志来分析不再使用的索引(注意主从库的时候无法使用此工具精确判断)

数据库(表)结构优化

选择合适的（列)数据类型

选择可以存下数据的最小的数据类型
选择尽量简单的数据类型
尽可能对列加上not null（Innodb特性),给出default
尽快能不使用text等大的数据类型，如果要用，尽量和其他字段分离，单独成表

表的范式和反范式
表的垂直拆分
把原来有很多列的表拆分成多个表，降低表的宽度
拆分原则：不经常使用的字段放在一个表,很大的字段放在一个表,常用的字段放在一个表
表的水平拆分
水平拆分解决单表数据量过大的问题，水平拆分之后的每一张表结构相同
常用拆分方法：取模，hash等
分表带来的挑战：跨分区表数据查询；统计及后台操作。使用汇总表,前后台业务分开