MySQL大表优化方案之引擎优化

引擎

目前广泛使用的是MyISAM和InnoDB两种引擎:

MyISAM

MyISAM引擎是MySQL 5.1及之前版本的默认引擎,它的特点是:

  • 不支持行锁,读取时对需要读到的所有表加锁,写入时则对表加排它锁
  • 不支持事务
  • 不支持外键
  • 不支持崩溃后的安全恢复
  • 在表有读取查询的同时,支持往表中插入新纪录
  • 支持BLOBTEXT的前500个字符索引,支持全文索引
  • 支持延迟更新索引,极大提升写入性能
  • 对于不会进行修改的表,支持压缩表,极大减少磁盘空间占用
InnoDB

InnoDB在MySQL 5.5后成为默认索引,它的特点是:

  • 支持行锁,采用MVCC来支持高并发
  • 支持事务
  • 支持外键
  • 支持崩溃后的安全恢复
  • 不支持全文索引

总体来讲,MyISAM适合SELECT密集型的表,而InnoDB适合INSERTUPDATE密集型的表

系统调优参数

可以使用下面几个工具来做基准测试:

  • sysbench:一个模块化,跨平台以及多线程的性能测试工具
  • iibench-mysql:基于 Java 的 MySQL/Percona/MariaDB 索引进行插入性能测试工具
  • tpcc-mysql:Percona开发的TPC-C测试工具

具体的调优参数内容较多,具体可参考官方文档,这里介绍一些比较重要的参数:

  • back_log:back_log值指出在MySQL暂时停止回答新请求之前的短时间内多少个请求可以被存在堆栈中。也就是说,如果MySql的连接数据达到max_connections时,新来的请求将会被存在堆栈中,以等待某一连接释放资源,该堆栈的数量即back_log,如果等待连接的数量超过back_log,将不被授予连接资源。可以从默认的50升至500
  • wait_timeout:数据库连接闲置时间,闲置连接会占用内存资源。可以从默认的8小时减到半小时
  • max_user_connection: 最大连接数,默认为0无上限,最好设一个合理上限
  • thread_concurrency:并发线程数,设为CPU核数的两倍
  • skip_name_resolve:禁止对外部连接进行DNS解析,消除DNS解析时间,但需要所有远程主机用IP访问
  • key_buffer_size:索引块的缓存大小,增加会提升索引处理速度,对MyISAM表性能影响最大。对于内存4G左右,可设为256M或384M,通过查询show status like 'key_read%',保证key_reads / key_read_requests在0.1%以下最好
  • innodb_buffer_pool_size:缓存数据块和索引块,对InnoDB表性能影响最大。通过查询show status like 'Innodb_buffer_pool_read%',保证 (Innodb_buffer_pool_read_requests – Innodb_buffer_pool_reads) / Innodb_buffer_pool_read_requests越高越好
  • innodb_additional_mem_pool_size:InnoDB存储引擎用来存放数据字典信息以及一些内部数据结构的内存空间大小,当数据库对象非常多的时候,适当调整该参数的大小以确保所有数据都能存放在内存中提高访问效率,当过小的时候,MySQL会记录Warning信息到数据库的错误日志中,这时就需要该调整这个参数大小
  • innodb_log_buffer_size:InnoDB存储引擎的事务日志所使用的缓冲区,一般来说不建议超过32MB
  • query_cache_size:缓存MySQL中的ResultSet,也就是一条SQL语句执行的结果集,所以仅仅只能针对select语句。当某个表的数据有任何任何变化,都会导致所有引用了该表的select语句在Query Cache中的缓存数据失效。所以,当我们的数据变化非常频繁的情况下,使用Query Cache可能会得不偿失。根据命中率(Qcache_hits/(Qcache_hits+Qcache_inserts)*100))进行调整,一般不建议太大,256MB可能已经差不多了,大型的配置型静态数据可适当调大.
    可以通过命令show status like 'Qcache_%'查看目前系统Query catch使用大小
  • read_buffer_size:MySql读入缓冲区大小。对表进行顺序扫描的请求将分配一个读入缓冲区,MySql会为它分配一段内存缓冲区。如果对表的顺序扫描请求非常频繁,可以通过增加该变量值以及内存缓冲区大小提高其性能
  • sort_buffer_size:MySql执行排序使用的缓冲大小。如果想要增加ORDER BY的速度,首先看是否可以让MySQL使用索引而不是额外的排序阶段。如果不能,可以尝试增加sort_buffer_size变量的大小
  • read_rnd_buffer_size:MySql的随机读缓冲区大小。当按任意顺序读取行时(例如,按照排序顺序),将分配一个随机读缓存区。进行排序查询时,MySql会首先扫描一遍该缓冲,以避免磁盘搜索,提高查询速度,如果需要排序大量数据,可适当调高该值。但MySql会为每个客户连接发放该缓冲空间,所以应尽量适当设置该值,以避免内存开销过大。
  • record_buffer:每个进行一个顺序扫描的线程为其扫描的每张表分配这个大小的一个缓冲区。如果你做很多顺序扫描,可能想要增加该值
  • thread_cache_size:保存当前没有与连接关联但是准备为后面新的连接服务的线程,可以快速响应连接的线程请求而无需创建新的
  • table_cache:类似于thread_cache_size,但用来缓存表文件,对InnoDB效果不大,主要用于MyISAM

本文来自投稿,不代表重蔚自留地立场,如若转载,请注明出处https://www.cwhello.com/4599.html

如有侵犯您的合法权益请发邮件951076433@qq.com联系删除

(0)
重蔚的头像重蔚管理团队
上一篇 2018年3月10日 16:33
下一篇 2018年3月10日 16:37

相关推荐

  • 重蔚自留地php学习第三十八天——高级数据操作

    having子句:having子句也是用来判断条件,与where子句基本一致。 区别 where是对数据源的数据进行操作 having是对临时数据(where操作完,group by操作之后)进行操作 对别名的使用:只有having能够使用,而where…

    2018年10月17日 MySQL自学教程
    0320
  • 说说山东虚拟主机租用怎么评测。

    要评测山东虚拟主机租用,主要需要考虑以下几个方面:虚拟主机的价格,包括基础价格、续费价格以及优惠政策等,需要进行比较并选择性价比最高的。稳定性是非常重要的,因为一个网站的用户最基本的要求就是可以顺利…

    2024年7月27日
    00
  • 我来分享centos启动MySQL命令。

    在CentOS系统中启动MySQL,我们需要进行以下步骤: 1. 打开终端。 2. 我们需要检查MySQL服务是否已经安装,如果没有安装,我们可以使用yum命令进行安装。 3. 如果MySQL服务已经安装,我们可以使用systemctl命令来启…

    2024年6月16日
    00
  • 我来教你mysql查询技巧。

    MySQL是一种广泛使用的开源关系型数据库管理系统,它提供了一种高效、可靠的数据存储和检索方式,在MySQL中,查询是最常用的操作之一,通过查询我们可以从数据库中获取所需的数据,本文将详细介绍MySQL中的三种查询…

    2024年6月20日
    00
  • PHP与数据库Docker化的集成。

    随着云计算和容器化技术的飞速发展,越来越多的开发者开始将应用程序Docker化,以提高效率和便捷性。今天,我们将探讨如何将流行的开发语言PHP和数据库Docker化,以实现更高效和可移植的开发流程。 一、什么是Docke…

    2023年5月21日
    02
  • 说说如何构建一个网站,如何快速构建一个网站。

    一、如何构建一个网站 要构建一个网站,首先需要了解网站的基本组成部分,包括前端、后端和数据库,接下来,我们将分别介绍如何搭建这三个部分。 1. 前端:前端是用户直接看到的页面,包括HTML、CSS和JavaScript等…

    2024年6月15日
    00
  • 分享如何修改mysql端口号。

    MySQL是一种广泛使用的开源关系型数据库管理系统,它提供了一种高效、可靠的数据存储和访问解决方案,在使用MySQL时,我们可能需要修改默认的端口号以适应特定的网络配置或安全需求,本文将详细介绍如何修改MySQL的…

    2024年6月30日
    01
  • 今日分享MariaDB管理命令有哪些。

    MariaDB是一种流行的开源关系型数据库管理系统。以下是一些常用的MariaDB管理命令: ,,- 连接到MariaDB服务器:mysql -u -p,- 创建数据库:CREATE DATABASE ;,- 删除数据库:DROP DATABASE ;,- 查看所有数据库…

    2024年7月4日
    00

联系我们

QQ:951076433

在线咨询:点击这里给我发消息邮件:951076433@qq.com工作时间:周一至周五,9:30-18:30,节假日休息