请选择 进入手机版 | 继续访问电脑版
点击联系客服
客服QQ:509006671 客服微信:mengfeiseo

广州老站长门户

 找回密码
 立即注册
查看: 75|回复: 11

Headline:为什么我的数据库那么慢?教我将MySQL性能提高10倍

[复制链接]

1

主题

1

帖子

5

积分

新手上路

Rank: 1

积分
5
发表于 2021-1-21 12:10:38 | 显示全部楼层 |阅读模式
为什么我的数据库那么慢,教你提升10倍MySQL性能

前言

经常会出现这种情况,如果项目访问量上升一点,发现最先不能胜任的是数据库,这时很多学生就会想到使用Redis缓存等。但是实际上,增加缓存会增加项目的复杂性。如果不必要,最好先优化数据库,然后在考虑时增加缓存。

本文从版本选择、存储引擎、参数优化、慢查询分析等方面入手,有助于显着提高MySQL数据库性能。内容有点多,请收藏。

选择更高效的MySQL版本

每次更新大版本时,MySQL的性能都会有很大的提高,如果实际上可以建议使用更高版本的MySQL来提高性能,则可以考虑使用优化的分支来代替默认的MySQL。

MySQL  8.0

2018-04-19 MySQL官方发行版本8.0.11 GA,到目前为止,近两年来可以说非常稳定,帮助了很多“船队”踩坑。因此,如果允许的话,建议升级MYSQL版本8.0,充分利用新功能,提高整体性能。

以下是MySQL  8.0的主要特性摘要。

1. MySQL  8.0性能是MySQL  5.7的2倍





2. 比MySQL  5.7更加强大的NoSQL支持





3. 隐藏索引(invisible  indexes)

通过隐藏设置、显示索引、观察相关查询情况、比较慢的查询日志分析等,识别和删除不必要的索引。

设置为隐藏:

alter  table  table  _ name  alter  index  idx  _ name  invisible;

更改为可见:

alter  table  table  _ name  alter  index  idx  _ name  visible;

4. 降序索引

Select  col1,col  2 from  table  _ name  order  by  col  _ time  desc  limit  10;

要创建降序索引,请执行以下操作:

create  index  idx  _ coltime  _ desc  on  table  _ name(col  _ time  desc);

降序索引仅支持InnoDB引擎,必须为BTREE。
5. 全局变量
MySQL 8.0提供更为方便的全局变量设置,方便DBA在没有服务器权限的情况下修改数据库配置(包括云数据库等)。通过SET PERSIST命令设置全局变量,并在datadir目录下生成配置文件持久化保存。

比如:

SET PERSIST ql_mode='STRICT_TRANS_TABLES';

6. 其他
  • GIS:
    MySQL 8.0提供地理支持。这包括对空间参考系统(SRS)的元数据支持,以及SRS感知空间数据类型,空间索引和空间函数。简而言之,MySQL 8.0可以理解地球表面的纬度和经度坐标,例如,可以在大约5000个支持的空间参考系统中的任何一个中正确计算地球表面上两点之间的距离。

  • UUID:
    MySQL的8.0通过实现三个新的SQL函数提高UUID操作的易用性:UUID_TO_BIN(),BIN_TO_UUID(),和IS_UUID()。

  • 其他:
    还有许多新的特性,包括安全方面的改进,与本篇关系不大不在此一一例举,感兴趣的同学可以查看官方文档。

    Percona Server
    Percona Server在原生MySQL的基础上进行的大量的优化,使用XtraDB引擎代替InnoDB,并且继承了TokuDB、RocksDB引擎进来,在实际项目使用当中,性能确实得到很大的提升。Percona分支从5.5到8.0版本都有,使用对应的大版本基本不需要任何操作就可以切换到Percona。

    同时Percona还提供了percona-toolkit、xtrabackup、pmm等MySQL分析、备份、监控的工具,即使是使用原生MySQL也一样可以使用。

    他们还有MongoDB、PostgreSQL数据库,也是做了相当多的优化。

    MariaDB
    MariaDB同Percona一样,也是MySQL的一个分支,同样适用XtraDB、Tokudb等引擎。这个我了解不多,感兴趣的同学可以自行上官网查询相关资料。

    使用恰当的表引擎
    我们前面提到了使用更好的MySQL版本/分支来提升整体性能,但是到了具体的业务时候,我们就应该使用不同的表引擎来存储不同的数据。比如订单相关数据,我们应当使用InnoDB来存储,并使用事务提交。而对于日志、历史数据等批量写入,少量修改删除的数据,我们则应当选择使用TokuDB、RocksDB等引擎来存储,以提升磁盘的使用率。

    InnoDB
    使用MySQL都知道InnoDB,网上还有一些关于InnoDB与MyISAM对比并罗列各自优缺点的文章,这些一般都是早期的文章。这里要说的是通通不要看不要信,这里没有二选一,全部使用InnoDB代替MyISAM,MySQL 8.0开始系统表也全部使用InnoDB了,把古老的MyISAM丢到垃圾桶并忘记他吧!

    InnoDB包括但不限于以下特点:

    [ol]
  • 支持事务
  • 行级锁
  • 辅助索引
  • 支持分区、表空间
  • 支持外键约束
  • 支持全文检索[/ol]
    TokuDB
    InnoDB使用BTREE存储数据及索引,并且索引及数据保存在相同的文件。每次写入都需要用一次IO来对索引重排,所以当数据量较大时候,CPU全部被IO占用,导致性能严重下降。

    TokuDB 是一个支持事务的“新”引擎,有着出色的压缩能力,如果您的数据写多读少,而且数据量比较大,强烈建议您使用TokuDB,以节省空间成本,并大幅度降低存储使用量和IOPS开销,不过相应的会增加 CPU 的压力。

    TokuDB 的特性:
  • 丰富的索引类型以及索引的快速创建
  • (第二)集合索引
    CREATE TABLE table (
    column_a INT,
    column_b INT,
    column_c INT,
    PRIMARY KEY index_a (column_a),
    CLUSTERING KEY index_b (column_b)) ENGINE = TokuDB;
    SELECT column_c FROM table WHERE column_b BETWEEN 10 AND 100;
  • 索引在线创建(Hot Index Creation)****
    SET tokudb_create_index_online=ON;
    CREATE INDEX index ON table (field_name);
    以上操作会直接返回成功,数据库在后台执行索引的创建,并且不影响数据表的增删改查,不会锁表。还可以通过show processlist命令查看执行进度。
  • 在线更改列(Add, Delete, Expand, Rename)
    TokuDB 可以在轻微阻塞更新或查询语句的情况下, 允许实现以下操作:

      
  • 增加或删除表中的列
  • 扩充字段: char, varchar, varbinary 和 int 类型的列
  • 重命名列, 不支持字段类型: TIME, ENUM, BLOB, TINYBLOB, MEDIUMBLOB, LONGBLOB 这些操作通常是以表锁级别阻塞(几秒钟时间)其他查询的执行, 当表记录下次从磁盘加载到内存的时候, 系统就会随之对记录进行修改操作(add, delete 或 expand), 如果是 rename 操作, 则会在几秒钟的停机时间内完成所有操作。所有的这些操作不是立即执行, 而是放到后台中由 Fractal Tree 完成, 操作包括主键和非主键索引。
  • 数据压缩
    TokuDB通过创建或修改表时指定ROW_FORMAT来设置压缩方式,支持以下几种压缩方式:

      
  • tokudb_zlib: 使用 zlib 库的压缩模式,提供了中等级别的压缩比和中等级别的CPU消耗。
  • tokudb_quicklz: 使用 quicklz 库的压缩模式, 提供了轻量级的压缩比和较低基本的CPU消耗。
  • tokudb_lzma: 使用lzma库压缩模式,提供了高压缩比和高CPU消耗。
  • tokudb_uncompressed: 不使用压缩模式。
    MyRocksDB
    MyRocks是基于Facebook开发的RocksDB封装的MySQL存储引擎,可以在原生MySQL的基础上安装,也可以选择Percona Server或者MariaDB分支,直接继承了。



    MyRocks有以下优势:

    [ol]
  • 更少的存储空间,节省存储空间
  • 更多的存储耐久性,写入性能提升
  • 提供更好的IO容量,查询效率更高
  • 复制速度更快。[/ol]
    但同时也有一下缺点:

    [ol]
  • 不支持分区表,Online ddl,外键,全文索引,空间索引,表空间transpor
  • gap lock支持不健全(仅primary key上支持), 使用statement方式复制会导致不一致
  • 不支持select … in share mode
  • 大小写敏感,不支持*_bin collation
  • binlog与RocksDB之间没有xa,异常crash可能丢数据。所以,MyRocks一般开启semi-sync.
  • 不支持savepoint
  • order by 比较慢
  • 不支持MRR
  • innodb和RocksDB混合使用还不稳定[/ol]
    根据Fackbook的测试数据,Rocksdb可以大幅压缩空间:



    同时可以获得更高的QPS:



    更高的写入性能:



    核心参数优化
    接触过很多MySQL项目,很多时候性能不够好仅仅是因为没有进行任何参数调优。之前有某网页游戏项目上线前测试时候性能不好找我,发现同时在线1千人左右服务器就看不住了,合作游戏运营商说1千人在线服务器成本都赚不回来。

    我拿过来服务器一看,发现Linux系统和MySQL都是默认参数,原来这家初创游戏同时技术人员都是纯代码流,数据库和运维能力都是渣渣。后来通过基本的优化,同时在线达到6千人还不会卡。技术上的短板决定了下限,长板决定了上限,所以好的技术团队应该面面俱到。

    这里例举几个常见的参数,更深入的调优还是需要根据实际项目调整。

    MySQL参数优化:

    #ib_logfile的刷新方式,取值:0/1/2(默认1),若非数据极其重要一定不用设置1,性能极差。
    innodb_flush_log_at_trx_commit = 2
    #设置参数为1启用InnoDB的独立表空间模式
    innodb_file_per_table = 1
    #InnoDB redo log文件组,通常设置为 2 就足够了
    innodb_log_files_in_group = 2
    #如果是非专用DB服务器,可以先尝试设置成内存的1/4, 专用服务器可以到80%
    innodb_buffer_pool_size = 4G
    #针对所有的账号所有的客户端并行连接到MYSQL服务的最大并行连接数
    max_connections = 500
    #针对某一个账号的所有客户端并行连接到MYSQL服务的最大并行连接数
    max_user_connections = 50
    #开启mysql慢sql的日志
    slow_query_log = 1
    #设置慢查询阀值,单位为秒
    long_query_time = 0.5
    #满查询日志文件存放位置
    slow_query_log_file = /var/lib/mysql/mysql-slow.log
    #默认使用InnoDB,忘记MyISAM吧
    default-storage-engine = InnoDB

    Linux系统参数优化:

    ulimit -SHn 51200
    echo '* soft nofile 51200
    * hard nofile 51200' >> /etc/security/limits.conf
    #其他参数如ip_conntrack_max、ip_conntrack_tcp_timeout_established根据实际情况优化设置

    后语
    本文仅仅是从大的方面介绍提升MySQL数据库性能的几个点,每一个点细化后都可以单开一篇来写。做任何的优化之前都一定要先了解自己项目的实际情况,然后根据实际情况一个点进行优化,观察一段时间的效果并且不断调优后,再进入另一个点的的优化。所有的操作都一定在测试环境先演练并查看效果,再试试到线上环境。

    数据无价,请一定记得备份!!!

    数据无价,请一定记得备份!!!

    数据无价,请一定记得备份!!!


    微信、知乎、简书、CSDN平台搜索“道听真说”,点点关注不迷路!

    如果本文对你有一点点帮助,请点个赞支持一下,谢谢啦❤️



    近期更新文章:

  • 安利Markdown文档编写利器Typora
  • 高可用MySQL数据库之PXC集群
  • 2020年一文看懂数据库发展
  • 回复

    使用道具 举报

    1

    主题

    396

    帖子

    182

    积分

    注册会员

    Rank: 2

    积分
    182
    发表于 2021-1-21 12:33:01 | 显示全部楼层
    不错
    回复

    使用道具 举报

    0

    主题

    368

    帖子

    201

    积分

    中级会员

    Rank: 3Rank: 3

    积分
    201
    发表于 2021-1-21 12:53:17 | 显示全部楼层
    找到好贴不容易,我顶你了,谢了
    回复

    使用道具 举报

    0

    主题

    363

    帖子

    196

    积分

    注册会员

    Rank: 2

    积分
    196
    发表于 2021-1-21 13:14:59 | 显示全部楼层
    不错不错,楼主您辛苦了。。。
    回复

    使用道具 举报

    0

    主题

    358

    帖子

    163

    积分

    注册会员

    Rank: 2

    积分
    163
    发表于 2021-1-21 13:40:44 | 显示全部楼层
    不错,支持下楼主
    回复

    使用道具 举报

    0

    主题

    346

    帖子

    207

    积分

    中级会员

    Rank: 3Rank: 3

    积分
    207
    发表于 2021-1-21 14:01:25 | 显示全部楼层
    前排支持下
    回复

    使用道具 举报

    0

    主题

    366

    帖子

    158

    积分

    注册会员

    Rank: 2

    积分
    158
    发表于 2021-1-21 14:21:34 | 显示全部楼层
    LZ真是人才
    回复

    使用道具 举报

    0

    主题

    369

    帖子

    134

    积分

    注册会员

    Rank: 2

    积分
    134
    发表于 2021-1-21 14:43:12 | 显示全部楼层
    好帖,来顶下
    回复

    使用道具 举报

    1

    主题

    356

    帖子

    160

    积分

    注册会员

    Rank: 2

    积分
    160
    发表于 2021-1-21 15:03:29 | 显示全部楼层
    我抢、我抢、我抢沙发~
    回复

    使用道具 举报

    1

    主题

    378

    帖子

    135

    积分

    注册会员

    Rank: 2

    积分
    135
    发表于 2021-1-21 15:27:13 | 显示全部楼层
    不错,支持下楼主
    回复

    使用道具 举报

    您需要登录后才可以回帖 登录 | 立即注册

    本版积分规则

    QQ|无图版|手机版|小黑屋|广州@IT精英团

    GMT+8, 2021-3-4 15:12 , Processed in 0.096266 second(s), 40 queries .

    Powered by Discuz! X3.4

    Copyright © 2001-2020, Tencent Cloud.

    快速回复 返回顶部 返回列表