数据库优化纬度有四个:
硬件升级、系统配置、表结构设计、SQL语句及索引。
image.png

优化选择:

  • 优化成本:硬件升级 > 系统配置 > 表结构设计 > SQL语句及索引
  • 优化效果:硬件升级 < 系统配置 < 标结果设计 < SQL语句及索引

1.保证从内存中读取数据

MySQL会在内存中保存一定的数据,通过LRU算法将不常访问的数据保存在硬盘文件中。
尽可能的扩大内存中的数量,将数据保存在内存中,从内存中读取数据,可以提升MySQL性能
扩大 innodb_buffer_pool_size,能够全然从内存中读取数据。最大限度降低磁盘操作。

确定 innodb_buffer_pool_size 足够大的方法:

show global status like 'innodb_buffer_pool_pages_%'

image.png
innodb_buffer_pool_size 默认为128M,理论上可以扩大到内存的3/4或4/5
修改my.cnf
innodb_buffer_size = 750M
如果是专用的MySQL Server 可以禁用SWAP

#查看swap
cat /proc/swaps

#关闭所有交换设备和文件
swapoff -a

2.数据预热

默认情况,仅仅有某条数据被读取一次,才会缓存在innodb_buffer_pool
所以,数据库刚刚启动,须要进行数据预热,将磁盘上的全部数据缓存到内存中
数据预热能够提高读取速度

1.对于InnoDB数据库,进行数据预热的脚本是:

SELECT DISTINCT
CONCAT('SELECT ',ndxcollist,' FROM ',db,'.',tb,
       ' ORDER BY ',ndxcollist,';') SelectQueryToLoadCache
FROM
(
  SELECT
  engine,table_schema db,table_name tb,
  index_name,GROUP_CONCAT(column_name ORDER BY seq_in_index)
  ndxcollist
  FROM
  (
    SELECT
    B.engine,A.table_schema,A.table_name,
    A.index_name,A.column_name,A.seq_in_index
    FROM
    information_schema.statistics A INNER JOIN
    (
      SELECT engine,table_schema,table_name
      FROM information_schema.tables WHERE
      engine='InnoDB'
    ) B USING (table_schema,table_name)
    WHERE B.table_schema NOT IN ('information_schema','mysql')
    ORDER BY table_schema,table_name,index_name,seq_in_index
  ) A
  GROUP BY table_schema,table_name,index_name
) AA
ORDER BY db,tb;

将该脚本保存为:loadtomem.sql

2.执行命令

mysql -uroot -proot -AN < /root/loadtomem.sql > /root/loadtomem.sql

3.在需要数据预热时,比如重启数据库
执行命令

mysql -uroot < /root/loadtomem.sql > /dev/null 2>&1

3.降低磁盘写入次数

  • 增大redolog,减少落盘次数
    • innodb_log_file_size 设置为 0.25*innodb_buffer_pool_size
  • 通用查询日志、慢查询日志可以不开,bin-log开
    • 生成中不开通用查询日志,遇到性能问题开慢查询日志
  • 写redolog策略 innodb_flush_log_at_trx_commit 设置为0或2
    • 如果不涉及非常高的安全性(金融系统),或者基础架构足够安全,或者事务都非常小,都能够用0或者2来减少磁盘操作

4.提高磁盘读写性能

使用SSD或者内存磁盘

点赞(0) 打赏

评论列表 共有 0 条评论

暂无评论

微信公众账号

微信扫一扫加关注

发表
评论
返回
顶部