前往小程序,Get更优阅读体验!
立即前往
首页
学习
活动
专区
工具
TVP
发布
社区首页 >专栏 >MySQL性能优化

MySQL性能优化

作者头像
luxixing
发布2019-05-29 17:24:37
1.4K0
发布2019-05-29 17:24:37
举报
文章被收录于专栏:luxixing

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

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

SQL优化

  • 开启慢查询记录日志,查找症状(很多时候都是一些慢查询拖累了整个数据库的性能)
    • 在配置文件中配置(my.cnf),配置完毕需要重启,不适合线上数据库

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

    • mysql命令行下配置

    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,后者更加精确详细

  • 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等 分表带来的挑战:跨分区表数据查询;统计及后台操作。使用汇总表,前后台业务分开

系统配置的优化

  • 修改/etc/sysctl.conf,优化系统网络参数
  • 修改/etc/security/limits.conf 优化打开文件数量
  • 硬件防火墙代替软件防火墙防止网络性能消耗
  • mysql配置文件
    • innodb_buffer_pool_size
    • innodb_buffer_pool_instances mysql 5.5引入,默认一个
    • ... 参数不在一一列举
  • 第三方工具优化mysql配置 http://tools.percona.com/wizard

硬件优化

  • cpu选择 核数不能超过32,mysql对多核的支持并不是特别优秀
  • 磁盘IO(RAID,常用RAID1+RAID0)
本文参与 腾讯云自媒体同步曝光计划,分享自作者个人站点/博客。
如有侵权请联系 cloudcommunity@tencent.com 删除

本文分享自 作者个人站点/博客 前往查看

如有侵权,请联系 cloudcommunity@tencent.com 删除。

本文参与 腾讯云自媒体同步曝光计划  ,欢迎热爱写作的你一起参与!

评论
登录后参与评论
0 条评论
热度
最新
推荐阅读
目录
  • SQL优化
  • 索引优化
  • 数据库(表)结构优化
  • 系统配置的优化
  • 硬件优化
相关产品与服务
云数据库 SQL Server
腾讯云数据库 SQL Server (TencentDB for SQL Server)是业界最常用的商用数据库之一,对基于 Windows 架构的应用程序具有完美的支持。TencentDB for SQL Server 拥有微软正版授权,可持续为用户提供最新的功能,避免未授权使用软件的风险。具有即开即用、稳定可靠、安全运行、弹性扩缩等特点。
领券
问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档