mysql——性能优化

定位优化位置

开启慢查询记录功能

slow_query_log=1 // 开启慢查询
long_query_time=1 // 慢于 1s 的记录,一般访问量大的生产环境要慢于 0.1s
slow_quey_log_file=2019_slow.log // 文件位置
log_queries_not_using_indexes=1 // 记录没有使用索引的语句

使用工具查看慢查询日志

使用的工具

  • mysqldumpslow slowlog_file_name | more
  • pt-query-digest

主要优化的语句:

  • 查询次数多且查询时间长
  • IO 大的 sql // rows examine 扫描行数多
  • 索引效率低 // rows sent/rows examine 比值小的。

使用 explain 分析语句

参数释义:

type : 查询类型

  • const // 常数查找。一般主键,唯一索引会用到。
  • eq_reg // 主键,唯一的范围查找
  • ref // 连接查找
  • range // 索引范围查找
  • index // 索引扫描
  • all // 表扫描

possible_key : 可能用到的索引

key : 实际使用的索引。

key_len : 索引的长度,越小越好

ref : 使用的索引的那一列

rows : 扫描的行数

extra : 如果为 using filesort 或者 using temporary (使用临时表) ,就需要优化查询了。

优化方法

索引

建立索引以及优化

create index index_name on table_name(column_name)

  • where , groupby , order by , on 从句使用的列应该建立索引。
  • 索引字段越小越好。
  • 离散程度大的索引放在联合索引的前面。
  • 删除不常使用的索引。
  • 避免索引重复以及冗余。
  • 模糊匹配的时候,% 不要放头部。
  • 索引列不要参与计算

检查索引的使用情况

  • pt-duplicate-key-checker -uroot -proot // 查找重复以及冗余索引
  • pt-index-usage // 索引维护工具

数据结构优化

字段优化

  • 多用整形,如 int。
  • 使用 not null + 默认值
  • 少用 text ,无法避免的话,分表处理。

表的第三设计范式和反范式

范式:尽量把重复的内容放在一个表里,类似于商品表,分类表,商品_分类表。

反范式:对部分表增加多余字段以减少查询目的(空间换时间)

表的垂直拆分

  • 不常用的字段放入一张表中
  • 大字段放入一张表中
  • 经常使用的字段放入一张表中

表的水平拆分

受限于目前的业务,仅说说我了解到的内容。

对 id 根据数据表的数量取模存入。

系统配置优化

mysql 配置优化

  • 查找配置文件: /usr/sbin/mysqld –verbose –help|grep -A 1 ‘Defautl options’;
  • 如果存在多个配置文件,则后面的覆盖前面。
innodb_file_per_table=On // 不共用表空间
innodb_buffer_pool_instances // 设置缓冲池的个数,一般等于核数
innodb_buffer_pool_size // 一般设置为内存的 75%(当前服务器仅作为数据库且引擎为 innodb)

操作系统优化

系统配置:

网络: /etc/sysctl.conf

net.ipv4.tcp_max_syn_backlog=65535 // 增加 tcp 的数量
// 加快资源回收
net.ipv4_tcp_max_tw_buckets=8000
net.ipv4.tcp_tw_reuse=1
net.ipv4.tcp_tw_recyle=1
net.ipv4.tcp_fim_timeout=10

文件:/etc/security/limits.conf

soft nofile 65535
hard nofile 65535

硬件配置:

  • cpu 主频越高越好,但是 cpu 的核数不一定越多越好。
  • 使用 ssd 加快 IO 访问。
  • 使用更大的内存来保证 mysql 缓冲区更大。

其他:关闭 iptables,selinux 等防护软件,用硬件隔离代替。

扩展

  • desc table_name // 查看表结构
  • count(*) = count(id OR null)
  • distinct // 忽略重复
  • tail -10 test.txt // 查看文件的最后 10 行
  • mysql 控制台中,语句 + \G ,可以将结构选择 90 度变为纵向。
  • | more // 可以翻页查看内容。


做人不可以太低下,因为父亲曾经把你举过头顶;难过时也要记得笑,因为妈妈许的愿是希望你天天开心。

—— 每个孩子都是父母的宝贝

发表评论

电子邮件地址不会被公开。 必填项已用*标注