精华内容
下载资源
问答
  • mysql性能优化
    千次阅读
    2022-03-12 09:50:39

    1. 硬件和操作系统层面的优化

    从硬件层面来说,影响MySQL性能因素主要是CPU,可用内存大小,磁盘读写速度,网络带宽。从操作系统层面来说,应用文件句柄数,操作系统的网络配置,都会影响到MySQL的性能。主要观察服务本身所承载的体量,然后提出合理的指标要求,避免出现资源浪费的一个现象。

    2. 架构设计层面的优化

    MySQL是一个磁盘IO访问,非常频繁的关系型数据库,在高并发和高性能的场景中,MySQL数据库必然会承受巨大的并发压力,在此时我们的优化的方式,主要可以分为几个部分:

    (1) 第一个是搭建MySQL主从集群,单个MySQL服务容易去导致单点故障,一旦服务宕机,将会导致依赖MySQL数据库的应用,全部无法响应,主从集群或者主主集群,都可以去保证服务的高可用性。
    (2) 读写分离设计,在读多写少的场景中,通过读写分离的方案,可以去避免读写冲突,导致的性能问题。
    (3) 引入分库分表的机制,通过分库可以降低单个服务器一个IO压力。通过分表的方式,降低单表数据量,从而去提升sql的查询效率。
    (4) 针对热点数据,可以引入更为高效的分布式数据库,如Redis、MongoDB等,他们可以很好的减轻MySQL的访问压力,同时还能提升数据的检索性能

    3. MySQL程序配置优化

    MYSQL是一个经过互联网大厂检验过的生产级别的成熟数据库,对于MySQL数据库本身的优化,一般可以通过MySQL配置文件my.cnf来完成。
    第一个MySQL 5.7版本默认的最大连接数是151个,这个值可以再my.cnf中去修改。
    第二个binlog日志默认不开启,可以开启。
    第三个是缓存池Bufferpool默认大小配置,而这些配置一般是和用户的安装环境以及使用场景有关系,因此这些配置,官方只会提供一个默认的配置,具体的情况还需要使用者去根据实际情况去修改。
    关于配置项的修改,需要关注两个层面,第一个是配置的作用域,它可以分为会话级别和全局范围。第二个是是否支持热加载,针对这两个点,我们需要注意的是,全局参数的设定,对于已经存在的会话是无法生效的,会话参数的设定,随着会话的销毁而失效。第三个是全局类的统一配置,建议配置在默认配置文件中,否则重启服务会导致配置失效。

    4. SQL执行优化

    (1) 慢SQL的定位和排查,我们可以通过慢查询日志和慢查询日志工具分析,得到有问题的SQL列表。
    (2) 执行计划分析,针对慢SQL我们可以使用关键字explain来去查看当前sql的执行计划,可重点关注type,key,rows,filterd等字段,从而去定位该SQL执行慢的根本原因,再去有的放矢的进行优化。
    (3) 使用show profile工具,这个工具是MySQL提供的可以用来分析当前会话中SQL语句资源消耗情况的工具,可以用于SQL调优的测量,在当前会话中,默认情况下,show profile是关闭状态,打开以后会保存,最近15次的运行结果,针对运行慢的SQL通过profile工具进行详细分析,可以得到SQL执行过程中所有资源的开销情况,比如io开销,cpu开销,内存开销。

    sql优化规则

    1. SQL的查询一定要基于索引来进行数据扫描。
    2. 避免索引列上使用函数或者运算符。
    3. Where字句中like%号尽量放置在右边。
    4. 使用索引扫描,联合索引中的列从左往后,命中越多越好
    5. 尽可能使用SQL语句用到的索引完成排序
    6. 查询有效的列信息即可,少用*代替列信息
    7. 永远要用小的结果集驱动大的结果集
    更多相关内容
  • 1. 为查询缓存优化你的查询 2. EXPLAIN 你的 SELECT 查询 3. 当只要一行数据时使用 LIMIT 1 4. 为搜索字段建索引 5. 在 Join 表的时候使用相当类型的例,并将其索引 6. 千万不要 ORDER BY RAND() 7. 避免 SELECT * 8...
  • MySQL 性能优化

    2020-12-14 10:26:21
    MySQL 性能优化 文章目录MySQL 性能优化SQL优化理论为什么要优化 SQLSQL 执行过程SQL查询语句的执行过程连接器查询缓存分析器优化器执行器数据库索引什么是索引?索引作用Btree 数据结构索引利弊索引的利索引的弊...
  • MySQL性能优化方向

    2020-12-14 21:22:47
    按照从大到小,从主要到次要的形式,分析 mysql 性能优化点,达到终优化的效果。  利用 mindmanger 整理了思路,形成如下图,每个点在网上都能找到说明,并记录下。形成了优化的思路:  1 连接 Connections ...
  • mysql性能优化

    2018-02-27 11:40:21
    mysql 数据库操作 优化性能 提升效率最佳的方法。。。。。
  • 网上有不少mysql 性能优化方案,不过,mysql的优化同sql server相比,更为麻烦,同样的设置,在不同的环境下 ,由于内存,访问量,读写频率,数据差异等等情况,可能会出现不同的结果,因此简单地根据某个给出方案来...
  • mysql 性能优化

    2019-05-29 02:44:05
    mysql性能调优.rar,mysql性能调优与架构设计.doc,mysql性能优化.doc
  • 十三MySQL性能优化详解.pdf
  • MySQL性能优化教学视频: https://www.bilibili.com/video/av29072634/?p=5 对应的笔记和资料
  • mysql性能优化教程.pdf,方面阅读
  • MySQL性能优化详解

    2018-04-24 12:39:15
    MySQL是目前使用最多的开源数据库,但是MySQL数据库的默认设置性能非常的差,仅仅是一个玩具数据库。因此在产品中使用MySQL数据库必须进行必要的优化优化是一个复杂的任务,本文描述MySQL相关的数据库设计和查询...
  • MySQL优化;使用好的硬件更快的硬盘大内存多核CPU专业的存储服务器NASSAN 设计合理架构如果 MySQL 访问频繁考虑 Master/Slave 读写分离数据库分表数据库切片分布式也考虑使用相应缓存服务帮助 MySQL 缓解访问压力 ;...
  • 分享视频教程——MySQL性能优化全过程落地方案(工业界真实数据场景实操),2021新课,本课程基于电商环境测试数据讲解,数据量400w+。最后会基于真实的环境进行MySQL调优讲解,让大家在实战中掌握MySQL性能知识,...
  • MySQL性能优化.xmind

    2020-03-28 13:02:39
    mysql性能优化的总结,从各个方面对sql的优化进行了总结,感觉是很好的性能优化的摘要总结,所以上传共享出来,绝对的硬货。绿色版,解压直接用,方便程序猿
  • 来自Sun Microsystems的MySQL性能优化权威指南,详细介绍了数据库连接池中各个参数的设定!
  • 教程包含mysql优化思路讲解、索引优化、分页优化、主从复制集群配置、分区、事务讲解以及其他常见问题讲解等
  • caoz的mysql性能优化教程 caoz的mysql性能优化教程 caoz的mysql性能优化教程 caoz的mysql性能优化教程 caoz的mysql性能优化教程
  • MySQL 性能优化参数分析

    万次阅读 2022-06-10 15:43:59
    MySQL 性能优化参数分析

    MySQL非缓存参数变量介绍

    #修改back_log参数值:由默认的50修改为500.(每个连接256kb,占用:125M)
    back_log=500

    解释:
    如果MySql的连接数据达到max_connections时,新来的请求被退回、记录即back_log,此时tcp连接就会排队,所以mysql也记录了它,简单理解等待连接的请求个数。
    连接的数量超过back_log,将不被授予连接资源。将会报:unauthenticated user | xxx.xxx.xxx.xxx | NULL | Connect | NULL | login | NULL 的待连接进程时.
    back_log值不能超过TCP/IP连接的侦听队列的大小,若超过则无效。可以把它理解为处在tcp协议层的一个连接统计功能函数。
    查看当前系统的TCP/IP连接的侦听队列的大小命令:
    cat /proc/sys/net/ipv4/tcp_max_syn_backlog目前系统为1024。
    查看当前数量
    show variables like 'back_log'; 

    #修改wait_timeout参数值,由默认的8小时,修改为30分钟。
    wait_timeout=1800

    理解:
    MySQL客户端的数据库连接闲置最大时间值,一般设置为多数用户使用系统从开始到结束所用的时间为合适。
    说得比较通俗一点,就是当你的MySQL连接闲置超过一定时间后将会被强行关闭。MySQL默认的wait-timeout  值为8个小时。
    show variables like 'wait_timeout'查看结果值;。
    show processlist 如果经常发现MYSQL中有大量的进程长时间停留,则需要 修改wait-timeout值了。
    interactive_timeout:服务器关闭交互式连接前等待活动的秒数。这两个参数必须配合使用,当连接时间同时达到这两个参数则被关闭,否则单独设置wait_timeout无效

    #修改max_connections参数值,由默认的151。(它是四层比较靠下级的参数,重度影响性能)
    max_connections=3000

    解释:
    max_connections是指MySql的最大连接数,如果服务器的并发连接请求量比较大,建议调高此值,以增加并行连接数量,当然这建立在机器能支撑的情况下,因为如果连接数越多,介于MySql会为每个连接提供连接缓冲区,就会开销越多的内存,所以要适当调整该值,不能盲目提高设值。可以过'conn%'通配符查看当前状态的连接数量,以定夺该值的大小。
    MySQL服务器允许的最大连接数16384;
    查看系统当前最大连接数:
    show variables like 'max_connections';

    #修改max_user_connections值,由默认的0。(它是四层比较靠上级的参数,轻度影响性能)
    max_user_connections=800

    解释
    max_user_connections是指每个数据库最大连接的用户。
    针对某一个账号的所有客户端并行连接到MYSQL服务的最大并行连接数。简单说是指同一个账号能够同时连接到mysql服务的最大连接数。设置为0表示不限制。
    目前默认值为:0不受限制。
    这儿顺便介绍下Max_used_connections:它是指从这次mysql服务启动到现在,同一时刻并行连接数的最大值。它不是指当前的连接情况,而是一个比较值。如果在过去某一个时刻,MYSQL服务同时有1000个请求连接过来,而之后再也没有出现这么大的并发请求时,则Max_used_connections=1000.请注意与show variables 里的max_user_connections的区别。默认为0表示无限大。
    查看max_user_connections值
    show variables like 'max_user_connections';

    #修改thread_concurrency值,由目前默认的8,修改为64
    thread_concurrency=64

    解释:
    thread_concurrency的值的正确与否, 对mysql的性能影响很大, 在多个cpu(或多核)的情况下,错误设置了thread_concurrency的值, 会导致mysql不能充分利用多cpu(或多核), 出现同一时刻只能一个cpu(或核)在工作的情况。
    thread_concurrency应设为CPU核数的2倍. 
    查看命令:
    show variables like 'thread_concurrency';

    #添加skip-name-resolve,默认被注释掉,没有该参数,一般也不用,都是直接IP连接。
    skip-name-resolve

    skip-name-resolve:禁止MySQL对外部连接进行DNS解析,使用这一选项可以消除MySQL进行DNS解析的时间。但需要注意,如果开启该选项,则所有远程主机连接授权都要使用IP地址方式,否则MySQL将无法正常处理连接请求!

    #skip-networking,默认被注释掉。没有该参数。
    解释:
    skip-networking建议被注释掉,不要开启
    开启该选项可以彻底关闭MySQL的TCP/IP连接方式,无法正常连接!

    MySQL缓存变量介绍

    #key_buffer_size,本系统目前为384M,可修改为400M
    key_buffer_size=400M

    解释:   key_buffer_size是用于索引块的缓冲区大小,增加它可得到更好处理的索引(对所有读和多重写),对MyISAM(MySQL表存储的一种类型,可以百度等查看详情)表性能影响最大的一个参数。如果你使它太大,系统将开始换页并且真的变慢了。严格说是它决定了数据库索引处理的速度,尤其是索引读的速度。对于内存在4GB左右的服务器该参数可设置为256M或384M.

    命令查得:show status like 'key_read%';
    +-------------------+-------+

    | Variable_name     | Value |

    +-------------------+-------+

    | Key_read_requests | 28535 |

    | Key_reads         | 269   |

    +-------------------+-------+
    可知道有28535个请求,有269个请求在内存中没有找到直接从硬盘读取索引.
    未命中缓存的概率为:0.94%=269/28535*100%.  一般未命中概率在0.1之下比较好。目前已远远大于0.1,证明效果不好。若命中率在0.01以下,则建议适当的修改key_buffer_size值。


    #innodb_buffer_pool_size(默认128M)
    innodb_buffer_pool_size=1024M(1G)

    解释:
    innodb_buffer_pool_size:主要针对InnoDB表性能影响最大的一个参数。功能与Key_buffer_size一样。
    另外InnoDB和 MyISAM 存储引擎不同, MyISAM 的 key_buffer_size 只能缓存索引键,而 innodb_buffer_pool_size 却可以缓存数据块和索引键。适当的增加这个参数的大小,可以有效的减少 InnoDB 类型的表的磁盘 I/O 。
    当我们操作一个 InnoDB 表的时候,返回的所有数据或者去数据过程中用到的任何一个索引块,都会在这个内存区域中走一遭。
    命令查得:show status like 'Innodb_buffer_pool_read%';

    比如查看当前系统中系统中

    | Innodb_buffer_pool_read_requests      | 1283826 |

    | Innodb_buffer_pool_reads              | 519     |

    +---------------------------------------+---------+
    其命中率99.959%=(1283826-519)/1283826*100%  命中率越高越好。

    #innodb_additional_mem_pool_size(默认8M)
    innodb_additional_mem_pool_size=20M

    解释:
    innodb_additional_mem_pool_size 设置了InnoDB存储引擎用来缓存数据结构,所以当我们一个MySQL数据类型非常多的时候,是需要适当调整该参数的大小以确保所有数据都能存放在内存中提高访问效率的。
    这个参数大小是否足够还是比较容易知道的,因为当过小的时候,MySQL会记录Warning信息到数据库的error log中,这时候你就知道该调整这个参数大小了。
    查看当前系统mysql的error日志  cat  /var/lib/mysql/机器名.error 发现有很多waring警告。所以要调大为20M.
    根据MySQL手册,对于2G内存的机器,推荐值是20M。
    32G内存的 100M

    #innodb_log_buffer_size(默认8M)
    innodb_log_buffer_size=20M

    解释:
    innodb_log_buffer_size  这是InnoDB存储引擎的事务日志所使用的缓冲区。类似于Binlog Buffer,InnoDB在写事务日志的时候,为了提高性能,也是先将信息写入Innofb Log Buffer中,当满足innodb_flush_log_trx_commit参数所设置的相应条件(或者日志缓冲区写满)之后,才会将日志写到文件 (或者同步到磁盘)中。可以通过innodb_log_buffer_size 参数设置其可以使用的最大内存空间。
    InnoDB 将日志写入日志磁盘文件前的缓冲大小。理想值为 1M 至 8M。大的日志缓冲允许事务运行时不需要将日志保存入磁盘而只到事务被提交(commit)。 因此,如果有大的事务处理,设置大的日志缓冲可以减少磁盘I/O。 在 my.cnf中以数字格式设置。
    默认是8MB,一般来说不建议超过32MB

    #innodb_flush_log_trx_commit
    参数对InnoDB Log的写入性能有非常关键的影响,默认值为1。该参数可以设置为0,1,2

    解释:
    0:log buffer中的数据将以每秒一次的频率写入到log file中,且同时会进行文件系统到磁盘的同步操作,但是每个事务的commit并不会触发任何log buffer 到log file的刷新或者文件系统到磁盘的刷新操作;
    1:在每次事务提交的时候将log buffer 中的数据都会写入到log file,同时也会触发文件系统到磁盘的同步;
    2:事务提交会触发log buffer到log file的刷新,但并不会触发磁盘文件系统到磁盘的同步。此外,每秒会有一次文件系统到磁盘同步操作。
    实际测试发现,该值对插入数据的速度影响非常大,设置为2时插入10000条记录只需要2秒,设置为0时只需要1秒,而设置为1时则需要229秒。因此,MySQL手册也建议尽量将插入操作合并成一个事务,这样可以大幅提高速度。根据MySQL手册,在存在丢失最近部分事务的危险的前提下,可以把该值设为0。

    #query_cache_size(默认32M)
    query_cache_size=40M

    解释:
    query_cache_size: 主要用来缓存MySQL中的ResultSet,也就是一条SQL语句执行的结果集,所以仅仅只能针对select语句。根据MySQL用户手册,使用查询缓冲最多可以达到238%的效率。
    当然,Query Cache也有一个致命的缺陷,那就是当某个表的数据有任何任何变化,都会导致所有引用了该表的select语句在Query Cache中的缓存数据失效。所以,当我们的数据变化非常频繁的情况下,使用Query Cache可能会得不偿失
    Query Cache的使用需要多个参数配合,其中最为关键的是query_cache_size和query_cache_type,前者设置用于缓存 ResultSet的内存大小,后者设置在何场景下使用Query Cache。在以往的经验来看,如果不是用来缓存基本不变的数据的MySQL数据库,query_cache_size一般256MB是一个比较合适的大小。当然,这可以通过计算Query Cache的命中率(Qcache_hits/(Qcache_hits+Qcache_inserts)*100))来进行调整。 query_cache_type可以设置为0(OFF),1(ON)或者2(DEMOND),分别表示完全不使用query cache,除显式要求不使用query cache(使用sql_no_cache)之外的所有的select都使用query cache,只有显示要求才使用query cache(使用sql_cache)。如果Qcache_lowmem_prunes的值非常大,则表明经常出现缓冲. 如果Qcache_hits的值也非常大,则表明查询缓冲使用非常频繁,此时需要增加缓冲大小;
    根据命中率(Qcache_hits/(Qcache_hits+Qcache_inserts)*100))进行调整,一般不建议太大,256MB可能已经差不多了,大型的配置型静态数据可适当调大.
    可以通过命令:show status like 'Qcache_%';查看目前系统Query catch使用大小

    | Qcache_hits             | 1892463  |

    | Qcache_inserts          | 35627  

    命中率98.17%=1892463/(1892463 +35627 )*100

    展开全文
  • 主要内容:MySQL性能介绍,MySQL基准测试,MySQL数据库结构优化, MySQL高可用架构设计, 数据库索引优化, SQL查询优化,数据库的分库分表,数据库监控
  • mysql性能优化资料

    2017-02-07 12:46:17
    分享mysql性能优化资料
  • 对于致力于研究mysql架构的同学非常有帮助,主要内容包括 MySQL 各功能模块组成,各模块协同工作原理,Query 处理的流程等。

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 303,070
精华内容 121,228
关键字:

mysql性能优化

mysql 订阅