佛山企业网站建设策划,惠州seo排名公司,黑龙江省建设厅官网查询,网站联系方式要素数据库调优策略 文章目录 数据库调优策略1. 数据库调优的措施1.1 调优目标1.2 如何定位调优问题1.3 调优的维度和步骤第1步#xff1a;选择合适的DBMS第2步#xff1a;优化表设计第3步#xff1a;优化逻辑查询第4步#xff1a;优化物理查询第5步#xff1a;使用 Redis 或 …数据库调优策略 文章目录 数据库调优策略1. 数据库调优的措施1.1 调优目标1.2 如何定位调优问题1.3 调优的维度和步骤第1步选择合适的DBMS第2步优化表设计第3步优化逻辑查询第4步优化物理查询第5步使用 Redis 或 Memcached 作为缓存第6步库级优化 2. 优化MySQL服务器2.1 优化服务器硬件2.2 优化MySQL的参数 3. 优化数据库结构3.1 拆分表:冷热数据分离3.2 中间表3.3 增加冗余字段3.4 优化数据类型3.5 优化插入记录的速度3.6 使用非空约束3.7 分析表、检查表与优化表1. 分析表2. 检查表3. 优化表 3.8 小结 4. 大表优化5. 其它调优策略5.1 服务器语句超时处理5.2 创建全局通用表空间 1. 数据库调优的措施
1.1 调优目标
尽可能 节省系统资源 以便系统可以提供更大负荷的服务。吞吐量更大合理的结构设计和参数调整以提高用户操作 响应的速度 。响应速度更快减少系统的瓶颈提高MySQL数据库整体的性能
1.2 如何定位调优问题
用户的反馈 (主要)日志分析 (主要)服务器资源使用监控数据库内部状况监控活动会话(Active Session)监控 是一个重要的指标。通过它可以了解数据库当前是否处于非常繁忙的状态是否存在 SQL 堆积等其它除了活动会话监控以外我们也可以对 事务、 锁等待 等进行监控这些都可以帮助我们对数据库的运行状态有更全面的认识。
1.3 调优的维度和步骤
第1步选择合适的DBMS
如果对 事务性处理 以及 安全性要求高 的话可以选择商业的数据库产品。这些数据库在事务处理和查询性能上都比较强比如采用 SQL Server、Oracle那么 单表存储上亿条据 是没有问题的。如果数据表设计得好即使不采用 分库分表 的方式查询效率也不差
除此以外还可以采用开源的 MySQL 进行存储它有很多存储引擎可以选择如果进行事务处理的话可以选择 InnoDB非事务处理可以选择 MyISAM。
NOSQL 阵营包括 键值型数据库、 文档型数据库、搜索引擎、列式存储和 图形数据库。这些数据库的优缺点和使用场景各有不同比如列式存储数据库可以大幅度降低系统的 I/O适合于分布式文件系统但如果数据需要频繁地增删改那么列式存储就不太适用了。
第2步优化表设计
选择了 DBMS 之后我们就需要进行表设计了。数据表的设计方式也直接影响了后续的 SQL 查询语句。如果用的是 MySQL可以根据不同表的使用需求选择不同的存储引擎。除此以外还有一些优化的原则可以参考
表结构要尽量 遵循三范式的原则如果 查询 应用比较多尤其是需要进行 多表联查 的时候可以采用 反范式 进行优化表字段的数据类型 选择一般来说如果字段可以采用数值类型就不要采用字符类型字符长度要尽可能设计得短一些字符型固定长度用char
第3步优化逻辑查询
SQL 查询优化可以分为 逻辑查询优化 和 物理查询优化。逻辑查询优化就是通过改变 SQL 语句的内容让 SQL 执行效率更高效采用的方式是对 SQL 语句进行等价变换对查询进行重写。SQL 的查询重写包括了子查询优化、等价谓词重写、视图重写、条件简化、连接消除和嵌套连接消除等
比如在 EXISTS 子查询和 IN 子查询的时候会根据 小表驱动大表 的原则选择适合的子查询。
第4步优化物理查询
物理查询优化是在确定了逻辑查询优化之后采用物理优化技术(比如索引等)通过计算代价模型对各种可能的访问路径进行估算从而找到执行方式中代价最小的作为执行计划。在这个部分中需要掌握的重点是对索引的创建和使用在前几篇文章中已经进行了细致的剖析。
第5步使用 Redis 或 Memcached 作为缓存
通常我们对于常用的数据以及查询响应要求高的场景(响应时间短吞吐量大) 可以考虑内存数据库毕竟术业有专攻。
第6步库级优化
库级优化是站在数据库的维度上进行的优化策略比如控制一个库中的数据表数量。另外单一的数据库总会遇到各种限制不如取长补短利用外援的方式。通过 主从架构 优化我们的读写策略通过对数据库进行垂直或者水平切分突破单一数据库或数据表的访问限制提升查询的性能。
读写分离主从架构读写分离数据分片分库分表
2. 优化MySQL服务器
优化MySQL服务器主要从两个方面来优化一方面是对 硬件 进行优化;另一方面是对MySQL 服务的参数 进行优化。这部分的内容需要较全面的知识一般只有 专业的数据库管理员 才能进行这一类的优化。对于可以定制参数的操作系统也可以针对MySQL进行操作系统优化
2.1 优化服务器硬件
服务器的硬件性能直接决定着MySQL数据库的性能。
配置较大的内存配置高速磁盘系统合理分布磁盘I/O把磁盘I/O分散在多个设备上以减少资源竞争提高并行操作能力配置多处理器MySQL是多线程的数据库多处理器可同时执行多个线程
2.2 优化MySQL的参数
MySQL服务的配置参数都在 my.cnf 或者 my.ini 文件的[mysqld]组中。配置完参数以后,需要重新启动MySQL服务才会生效下面对几个对性能影响比较大的参数进行详细介绍 innodb_buffer_pool_size表示lnnoDB类型的表和索引的最大缓存默认128MB。它不仅仅缓存 索引数据还会缓存 表的数据。这个值越大查询的速度就会越快。但是这个值太大会影响操作系统的性能。 key_buffer_size表示索引缓冲区的大小。索引缓冲区是所有的 线程共享 。它的大小取决于内存的大小。如果这个值大大就会导致操作系统频繁换页也会降低系统性能。对于内存在 4GB 左右的服务器该参数可设置为 256M 或 384M。 table_cache表示 同时打开的表的个数。这个值越大能够同时打开的表的个数越多。物理内存越大设置就越大。默认为2402调到512-1024最佳。这个值不是越大越好因为同时打开的表太多会影响操作系统的性能。 sort_buffer_size表示 每个需要进行排序的线程分配的缓冲区的大小。增加这个参数的值可以提高 ORDER BY 或 GROUP BY 操作的速度。默认数值是2 097 144字节(约2MB)。对于内存在4GB左右的服务器推荐设置为6-8M如果有100个连接那么实际分配的总共排序缓冲区大小为100 * 6 600MB。 join_buffer_size表示联合查询所能使用的缓冲区大小默认8M和sort_buffer_size一样该参数对应的分配内存也是每个连接独享 read_buffer_size表示 每个线程连续扫描时为扫描的每个表分配的缓冲区的大小(字节) 。当线程从表中连续读取记录时需要用到这个缓冲区。SET SESSION read_buffer_size可以临时设置该参数的值。默认为 64K可以设置为 4M。 innodb_flush_log_at_trx_commit表示 何时将缓冲区的数据写入日志文件并目将日志文件写入磁盘中。该参数对于innoDB引擎非常重要。默认1 值为0时表示 每秒1次 的频率将数据写入日志文件并将志文件写入磁盘。每个事务的commit并不会触发前面的任何操作。该模式速度最快但不太安全mysqld进程的崩溃会导致上一秒钟所有事务数据的丢失值为1时表示 每次提交事务时 将数据写入日志文件并将日志文件写入磁盘进行同步。该模式是最安全的但也是最慢的一种方式。因为每次事务提交或事务外的指令都需要把日志写入 (flush) 硬盘值为2 时表示 每次提交事务时 将数据写入日志文件每隔1秒 将日志文件写入磁盘。该模式速度较快也比0安全只有在操作系统崩溃或者系统断电的情况下上一秒钟所有事务数据才可能丢失 innodb_log_buffer_size 这是 InnoDB 存储引擎的 事务日志所使用的缓冲区 。为了提高性能也是先将信息写入 Innodb Log Buffer 中当满足 innodb_flush_log_trx_commit 参数所设置的相应条件或者日志缓冲区写满之后才会将日志写到文件或者同步到磁盘中。 max_connections 表示 允许连接到MySQL数据库的最大数量 默认值是 151 。如果状态变量connection_errors_max_connections 不为零并且一直增长则说明不断有连接请求因数据库连接数已达到允许最大值而失败这是可以考虑增大max_connections 的值。在Linux 平台下性能好的服务器支持 500-1000 个连接不是难事需要根据服务器性能进行评估设定。这个连接数 不是越大越好 因为这些连接会浪费内存的资源。过多的连接可能会导致MySQL服务器僵死。 back_log 用于 控制MySQL监听TCP端口时设置的积压请求栈大小 。如果MySql的连接数达到max_connections时新来的请求将会被存在堆栈中以等待某一连接释放资源该堆栈的数量即back_log如果等待连接的数量超过back_log将不被授予连接资源将会报错。5.6.6 版本之前默认值为 50 之后的版本默认为 50 max_connections / 5 对于Linux系统推荐设置为小于512的整数但最大不超过900。 如果需要数据库在较短的时间内处理大量连接请求 可以考虑适当增大back_log 的值。 thread_cache_size 线程池缓存线程数量的大小 当客户端断开连接后将当前线程缓存起来当在接到新的连接请求时快速响应无需创建新的线程 。这尤其对那些使用短连接的应用程序来说可以极大的提高创建连接的效率。那么为了提高性能可以增大该参数的值。默认为60可以设置为120。 可以通过如下几个MySQL状态值来适当调整线程池的大小 mysql show global status like Thread%;
--------------------------
| Variable_name | Value |
--------------------------
| Threads_cached | 2 |
| Threads_connected | 1 |
| Threads_created | 3 |
| Threads_running | 2 |
--------------------------
4 rows in set (0.01 sec)当 Threads_cached 越来越少但 Threads_connected 始终不降且 Threads_created 持续升高可适当增加 thread_cache_size 的大小。 wait_timeout指定 一个请求的最大连接时间 默认28800对于4GB左右内存的服务器可以设置为5-10。 interactive_timeout表示服务器在关闭连接前等待行动的秒数 innodb_buffer_pool_instances默认为1这个参数可以将lnnoDB 的缓存区分成几个部分这样可以提高系统的 并行处理能力因为可以允许多个进程同时处理不同部分的缓存区。
这里给出一份my.cnf的参考配置注意只是参考配置实际情况还需具体情况具体分析
[mysqld]
port 3306
serverid 1
socket /tmp/mysql.sock skip-locking #避免MySQL的外部锁定减少出错几率增强稳定性。
skip-name-resolve #禁止MySQL对外部连接进行DNS解析使用这一选项可以消除MySQL进行DNS解析的时间。但需要注意如果开启该选项则所有远程主机连接授权都要使用IP地址方式否则MySQL将无法正常处理连接请求
back_log 384
key_buffer_size 256M
max_allowed_packet 4M
thread_stack 256K
table_cache 128K
sort_buffer_size 6M
read_buffer_size 4M
read_rnd_buffer_size16M
join_buffer_size 8M
myisam_sort_buffer_size 64M
table_cache 512
thread_cache_size 64
query_cache_size 64M
tmp_table_size 256M
max_connections 768
max_connect_errors 10000000
wait_timeout 10
thread_concurrency 8 #该参数取值为服务器逻辑CPU数量*2在本例中服务器有2颗物理CPU而每颗物理CPU又支持H.T超线程所以实际取值为4*28
skipnetworking #开启该选项可以彻底关闭MySQL的TCP/IP连接方式如果WEB服务器是以远程连接的方式访问MySQL数据库服务器则不要开启该选项否则将无法正常连接 table_cache1024
innodb_additional_mem_pool_size4M #默认为2M
innodb_flush_log_at_trx_commit1
innodb_log_buffer_size2M #默认为1M
innodb_thread_concurrency8 #你的服务器CPU有几个就设置为几。建议用默认一般为8
tmp_table_size64M #默认为16M调到64-256最挂
thread_cache_size120
query_cache_size32M3. 优化数据库结构
一个好的 数据库设计方案 对于数据库的性能常常会起到 事半功倍 的效果。合理的数据库结构不仅可以使数据库占用更小的磁盘空间而且能够使查询速度更快。数据库结构的设计需要考虑 数据冗余、 查询和更新的速度、字段的数据类型 是否合理等多方面的内容
3.1 拆分表:冷热数据分离
拆分表的思路是把 1个包含很多字段的表拆分成 2个或者多个相对较小的表。原因是这些表中某些字段的操作频率很高(热数据)经常要进行查询或者更新操作而另外一些字段的使用频率却很低(冷数据)冷热数据分离可以减小表的宽度。如果放在一个表里面每次查询都要读取大记录会消耗较多的资源
MySQL限制每个表最多存储 4096 列并且每一行数据的大小不能超过 65535 字节。表越宽把表装载进内存缓冲池时所占用的内存也就越大也会消耗更多的IO。 冷热数据分离的目的 是:
减少磁盘IO保证热数据的内存缓存命中率更有效的利用缓存避免读入无用的冷数据
3.2 中间表
对于需要经常联合查询的表可以建立中间表以提高查询效率。通过建立中间表把需要经常联合查询的数据插入中间表中然后将原来的联合查询改为对中间表的查询以此来提高查询效率。该方式仅限于不经常变化的数据使用因为很容易导致数据不一致的问题 注意如果其中表信息有修改可能会导致 数据不一致 的问题可通过以下方式进行数据同步 方式1清空数据-重新添加数据 3.3 增加冗余字段
参考前面文章反范式化
3.4 优化数据类型
优先选择符合存储需要的最小的数据类型 情况一整形数据类型优化 在不清楚使用哪种整形类型的情况下考虑使用 int;对于非负型数据考虑使用 unsigend 情况二既可以使用文本类型又可以使用整形类型的字段优先选择使用整形类型 情况三避免使用TEXT、BLOB数据类型 MysQL 内存临时表 不支持TEXT、BLOB这样的大数据类型如果查询中包含这样的数据在排序等操作时就不能使用内存临时表必须使用 磁盘临时表 进行。并且对于这种数据MySQL还是要进行 二次查询会使SQL性能变得很差但不是说一定不能使用这样的数据类型 如果一定要使用建议把BLOB或是TEXT列 分离到单独的扩展表 中查询时一定不要使用select*而只需要取出必要的列不需要TEXT列的数据时不要对该列进行查询 情况四避免使用ENUM类型可以使用tinyint代替
总之遇到数据量大的项目一定要在充分了解业务需求的前提下合理优化数据类型这样才能充分发挥资源的效率使系统达到最优
3.5 优化插入记录的速度
该案例主要针对大数据量插入分两个不同存储引擎分析
首先对于使用MyISAM引擎的表
禁用索引插入前禁用索引数据插入完毕再开启索引禁用唯一性检查插入前禁用唯一性检查数据插入完毕再开启唯一性检查使用批量插入使用 load data infile 批量插入
其次对于使用InnoDB引擎的表
禁用唯一性检查禁用外键检查禁止自动提交
3.6 使用非空约束
进行比较和计算时省去对NULL值的字段判断是否为空的开销提高存储效率节省存储空间对应索引列不需要NULL列每个字段一个bit
3.7 分析表、检查表与优化表
分析表 主要是分析关键字的分布检查表 主要是检查表是否存在错误优化表 主要是消除删除或者更新造成的空间浪费。
1. 分析表
MySQL中提供了ANALYZE TABLE语句分析表ANALYZE TABLE语句的基本语法如下
ANALYZE [LOCAL | NO_WRITE_TO_BINLOG] TABLE tbl_name[,tbl_name]…默认的MySQL服务会将 ANALYZE TABLE语句写到binlog中以便在主从架构中从服务能够同步数据。可以添加参数LOCAL 或者 NO_WRITE_TO_BINLOG取消将语句写到binlog中。
使用 ANALYZE TABLE 分析表的过程中数据库系统会自动对表加一个 只读锁 。在分析期间只能读取表中的记录不能更新和插入记录。ANALYZE TABLE语句能够分析InnoDB和MyISAM类型的表。
ANALYZE TABLE分析后的统计结果会反应到 cardinality 的值该值统计了表中某一键所在的列不重复的值的个数。该值越接近表中的总行数则在表连接查询或者索引查询时就越优先被优化器选择使用。也就是索引列的cardinality的值与表中数据的总条数差距越大即使查询的时候使用了该索引作为查询条件存储引擎实际查询的时候使用的概率就越小。cardinality可以通过 SHOW INDEX FROM 表名查看
2. 检查表
了解即可该部分内容也可跳过有时候表会因为一些原因损坏但一般不会。MySQL中可以使用 CHECK TABLE 语句来检查表。CHECK TABLE语句能够检查InnoDB和MyISAM类型的表是否存在错误。CHECK TABLE语句在执行过程中也会给表加上 只读锁 。
对于MyISAM类型的表CHECK TABLE语句还会更新关键字统计数据。而且CHECK TABLE也可以检查视图是否有错误比如在视图定义中被引用的表已不存在。该语句的基本语法如下
CHECK TABLE tbl_name [, tbl_name] ... [option] ...
option {QUICK | FAST | MEDIUM | EXTENDED | CHANGED}option只对MyISAM类型的表有效对InnoDB类型的表无效不想研究可跳过下面部分内容。
其中tbl_name是表名option参数有5个取值分别是QUICK、FAST、MEDIUM、EXTENDED和CHANGED。各个选项的意义分别是
QUICK 不扫描行不检查错误的连接。FAST 只检查没有被正确关闭的表。CHANGED 只检查上次检查后被更改的表和没有被正确关闭的表。MEDIUM 扫描行以验证被删除的连接是有效的。也可以计算各行的关键字校验和并使用计算出的校验和验证这一点。EXTENDED 对每行的所有关键字进行一个全面的关键字查找。这可以确保表是100%一致的但是花的时间较长。
该语句对于检查的表可能会产生多行信息。最后一行有一个状态的 Msg_type 值Msg_text 通常为 OK。如果得到的不是 OK通常要对其进行修复是 OK 说明表已经是最新的了。表已经是最新的意味着存储引擎对这张表不必进行检查。
3. 优化表
MySQL中使用 OPTIMIZE TABLE 语句来优化表。但是OPTILMIZE TABLE语句只能优化表中的 VARCHAR 、 BLOB 或 TEXT 类型的字段。一个表使用了这些字段的数据类型若已经 删除 了表的一大部分数据或者已经对含有可变长度行的表含有VARCHAR、BLOB或TEXT列的表进行了很多 更新 则应使用OPTIMIZE TABLE来重新利用未使用的空间并整理数据文件的 碎片 。
OPTIMIZE TABLE 语句对InnoDB和MyISAM类型的表都有效。该语句在执行过程中也会给表加上 只读锁。
OPTIMIZE [LOCAL | NO_WRITE_TO_BINLOG] TABLE tbl_name [, tbl_name] ...在MyISAM中是先分析这张表然后会整理相关的MySQL datafile之后回收未使用的空间在InnoDB中回收空间是简单通过Alter table进行整理空间。在优化期间MySQL会创建一个临时表优化完成之后会删除原始表然后会将临时表rename成为原始表。 说明 在多数的设置中根本不需要运行OPTIMIZE TABLE。即使对可变长度的行进行了大量的更新也不需要经常运行 每周一次 或 每月一次 即可并且只需要对 特定的表 运行。 3.8 小结
上述这些方法都是有利有弊的。比如
修改数据类型节省存储空间的同时要考虑到数据不能超过取值范围增加冗余字段的时候不要忘了确保数据一致性把大表拆分也意味着你的查询会增加新的连接从而增加额外的开销和运维的成本
因此一定要结合实际的业务需求进行权衡。
4. 大表优化
当MySQL单表记录数过大时数据库的CURD性能会明显下降一些常见的优化措施如下
限定查询的范围读/写分离垂直拆分当数据量级达到 千万级 以上时有时候我们需要把一个数据库切成多份放到不同的数据库服务器上减少对单一数据库服务器的访问压力 如果数据库中的数据表过多可以采取 垂直分库 的方式将关联的数据表部署在同一个数据库上如果数据表中的列过多可以采用 垂直分表的方式将一张数据表拆分成多张数据表把经常一起使用的列放到同一张表里垂直拆分的优点 可以使得列数据变小在查询时减少读取的Block数减少I/O次数。此外垂直分区可以简化表的结构易于维护垂直拆分的缺点 主键会出现冗余需要管理冗余列并会引起 JOIN 操作。此外垂直拆分会让事务变得更加复杂 水平拆分尽量控制单表数据量的大小在 1000万以内 1000万并不是MySQL数据库的限制过大会造成修改表结构、备份、恢复都会有很大的问题。此时可以用 历史数据归档应用与日志数据水平分表应用于业务数据等手段来控制数据量大小这里我们主要考虑业务数据的水平分表策略。将大的数据表按照 某个属性维度 分成不同的小表每张小表保持相同的表结构。比如可以按照年份来划分把不同年份的数据放到不同的数据表中。2017 年、2018 年和 2019 年的数据就可以分别放到三张数据表中水平分表仅是解决了单一表数据过大的问题但由于表的数据还是在同一台机器上其实对于提升MySQL并发能力没有什么意义所以 水平拆分最好分库从而达到分布式的目的
下面补充一下数据库分片的两种常见方案
客户端代理 分片逻辑在应用端封装在jar包中通过修改或者封装JDBC层来实现。 当当网的Sharding-JDBC 、阿里的TDDL是两种比较常用的实现。中间件代理 在应用和数据中间加了一个代理层。分片逻辑统一维护在中间件服务中。Mycat 、360的Atlas、网易的DDB等等都是这种架构的实现。
5. 其它调优策略
5.1 服务器语句超时处理
在MySQL 8.0中可以设置 服务器语句超时的限制 单位可以达到 毫秒级别 。当中断的执行语句超过设置的毫秒数后服务器将终止查询影响不大的事务或连接然后将错误报给客户端。
设置服务器语句超时的限制可以通过设置变量 MAX_EXECUTION_TIME 来实现。默认情况下MAX_EXECUTION_TIME的值为0代表没有时间限制。
5.2 创建全局通用表空间
MySQL 8.0使用 CREATE TABLESPACE 语句来创建一个 全局通用表空间。全局表空间可以被所有的数据库的表共享而且相比于独享表空间使用手动创建共享表空间可以节约元数据方面的内存。可以在创建表的时候指定属于哪个表空间也可以对已有表进行表空间修改等
下面创建名为 a 的共享表空间SQL语句如下
CREATE TABLESPACE a ADD datafile a.ibd file_block_size16k;指定表空间SQL语句如下
CREATE TABLE test(id int,name varchar(10)) engineinnodb default charset utf8mb4 tablespace a;
