单机数据库优化的一些实践(mysql)

本文涉及的产品
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
云数据库 RDS MySQL,集群系列 2核4GB
推荐场景:
搭建个人博客
RDS MySQL Serverless 高可用系列,价值2615元额度,1个月
简介:

数据库优化有很多可以讲,按照支撑的数据量来分可以分为两个阶段:单机数据库和分库分表,前者一般可以支撑500W或者10G以内的数据,超过这个值则需要考虑分库分表。另外,一般大企业面试往往会从单机数据库问起,一步一步问到分库分表,中间会穿插很多数据库优化的问题。本文试图描述单机数据库优化的一些实践,数据库基于mysql,如有不合理的地方,欢迎指正。

1、表结构优化

在开始做一个应用的时候,数据库的表结构设计往往会影响应用后期的性能,特别是用户量上来了以后的性能。因此,表结构优化是一个很重要的步骤。

1.1、字符集

一般来说尽量选择UTF-8,虽然在存中午的时候GBK比UTF-8使用的存储空间少,但是UTF-8兼容各国语言,其实我们不必为了这点存储空间而牺牲了扩展性。事实上,后期如果要从GBK转为UTF-8所要付出的代价是很高的,需要进行数据迁移,而存储空间完全可以用花钱扩充硬盘来解决。

1.2、主键

在使用mysql的innodb的时候,innodb的底层存储模型是B+树,它使用主键作为聚簇索引,使用插入的数据作为叶子节点,通过主键可以很快找到叶子节点,从而快速获取记录。因此在设计表的时候需要增加一个主键,而且最好要自增。因为自增主键可以让插入的数据按主键顺序插入到底层的B+树的叶子节点中,由于是按序的,这种插入几乎不需要去移动已有的其它数据,所以插入效率很高。如果主键不是自增的,那么每次主键的值近似随机,这时候就有可能需要移动大量数据来保证B+树的特性,增加了不必要的开销。

1.3、字段

1.3.1、建了索引的字段必须加上not null约束,并且设置default值

1.3.2、不建议使用float、double来存小数,防止精度损失,建议使用decimal

1.3.3、不建议使用Text/blob来保存大量数据,因为对大文本的读写会造成比较大的I/O开销,同时占用mysql的缓存,高并发下会极大的降低数据库的吞吐量,建议将大文本数据保存在专门的文件存储系统中,mysql中只保存这个文件的访问地址,比如博客文章可以保存在文件中,mysql中只保存文件的相对地址。

1.3.4、varchar类型长度建议不要超过8K。

1.3.5、时间类型建议使用Datetime,不要使用timestamp,虽然Datetime占用8个字节,而timestamp只占用4个字节,但是后者要保证非空,而且后者是对时区敏感的。

1.3.6、建议表中增加gmt_create和gmt_modified两个字段,用来记录数据创建的修改时间。这两个字段建立的原因是方便查问题。

1.4、索引创建

1.4.1、这个阶段由于对业务并不了解,所以尽量不要盲目加索引,只为一些一定会用到索引的字段加普通索引。

1.4.2、创建innodb单列索引的长度不要超过767bytes,如果超过会用前255bytes作为前缀索引

1.4.3、创建innodb组合索引的各列索引长度不要超过767bytes,一共加起来不要超过3072bytes

2、SQL优化

一般来说sql就那么几种:基本的增删改查,分页查询,范围查询,模糊搜索,多表连接

2.1、基本查询

一般查询需要走索引,如果没有索引建议修改查询,把有索引的那个字段加上,如果由于业务场景没法使用这个字段,那么需要看这个查询调用量大不大,如果大,比如每天调用10W+,这就需要新增索引,如果不大,比如每天调用100+,则可以考虑保持原样。另外,select * 尽量少用,用到什么字段就在sql语句中加什么,不必要的字段就别查了,浪费I/O和内存空间。

2.2、高效分页

limit m,n其实质就是先执行limit m+n,然后从第m行取n行,这样当limit翻页越往后翻m越大,性能越低。比如

select * from A limit 100000,10,这种sql语句的性能是很差的,建议改成下面的版本:

selec id,name,age from A where id >=(select id from A limit 100000,1) limit 10

2.3、范围查询

范围查询包括between、大于、小于以及in。Mysql中的in查询的条件有数量的限制,若数量较小可以走索引查询,若数量较大,就成了全表扫描了。而between、大于、小于等,这些查询不会走索引,所以尽量放在走索引的查询条件之后。

2.4、模糊查询like

使用 like %name%这样的语句是不会走索引的,相当于全表扫描,数据量小的时候不会有太大的问题,数据量大了以后性能会下降的很厉害,建议数据量大了以后使用搜索引擎来代替这种模糊搜索,实在不行也要在模糊查询前加个能走索引的条件。

2.5、多表连接

子查询和join都可以实现在多张表之间取数据,但是子查询性能较差,建议将子查询改成join。对于mysql的join,它用的是Nested Loop Join算法,也就是通过前一个表查询的结果集去后一个表中查询,比如前一个表的结果集是100条数据,后一个表有10W数据,那么就需要在100*10W的数据集合中去过滤得到最终的结果集。因此,尽量用小结果集的表去和大表做join,同时在join的字段上建立索引,如果建不了索引,就需要设置足够大的join buffer size。如果以上的技巧都无法解决join所带来的性能下降的问题,那干脆就别用join了,将一次join查询拆分成两次简单查询。另外,多表连接尽量不要超过三张表,超过三张表一般来说性能会很差,建议拆分sql。

 

3、数据库连接池优化

数据库连接池本质上是一种缓存,它是一种抗高并发的手段。数据库连接池优化主要是对参数进行优化,一般我们使用DBCP连接池,它的具体参数如下:

3.1  initialSize

初始连接数,这里的初始指的是第一次getConnection的时候,而不是应用启动的时候。初始值可以设置为并发量的历史平均值

3.2、minIdle

最小保留的空闲连接数。DBCP会在后台开启一个回收空闲连接的线程,当该线程进行空闲连接回收的时候,会保留minIdle个连接数。一般设置为5,并发量实在很小可以设置为1.

3.3、maxIdle

最大保留的空闲连接数,按照业务并发高峰设置。比如并发高峰为20,那么当高峰过去后,这些连接不会马上被回收,如果过一小段时间又来一个高峰,那么连接池就可以复用这些空闲连接而不需要频繁创建和关闭连接。

3.4、maxActive

最大活跃连接数,按照可以接受的并发极值设置。比如单机并发量可接受的极值是100,那么这个maxActive设置成100后,就只能同时为100个请求服务,多余的请求会在最大等待时间之后被抛弃。这个值必须设置,可以防止恶意的并发攻击,保护数据库。

3.5、maxWait

获取连接的最大等待时间,建议设置的短一点,比如3s,这样可以让请求快速失败,因为一个请求在等待获取连接的时候,线程是不可以被释放的,而单机的线程并发量是有限的,如果这个时间设置的过长,比如网上建议的60s,那么这个线程在这60s内是无法被释放的,只要这种请求一多,应用的可用线程就少了,服务就变得不可用了。

3.6、minEvictableIdleTimeMillis

连接保持空闲而不被回收的时间,默认30分钟。

3.7、validationQuery

用于检测连接是否有效的sql语句,一般是一条简单的sql,建议设置

3.8、testOnBorrow

申请连接的时候对连接进行检测,不建议开启,严重影响性能

3.9、testOnReturn

归还连接的时候对连接进行检测,不建议开启,严重影响性能

3.10、testWhileIdle

开启了以后,后台清理连接的线程会没隔一段时间对空闲连接进行validateObject,如果连接失效则会进行清除,不影响性能,建议开启

3.11、numTestsPerEvictionRun

代表每次检查链接的数量,建议设置和maxActive一样大,这样每次可以有效检查所有的链接。

3.12、预热连接池

对于连接池,建议在启动应用的时候进行预热,在还未对外提供访问之前进行简单的sql查询,让连接池充满必要的连接数。

 

4、索引优化

当数据量增加到一定程度后,靠sql优化已经无法提升性能了,这时候就需要祭出大招:索引。索引有三级,一般来说掌握这三级就足够了,另外,对于建立索引的字段,需要考虑其选择性。

4.1、一级索引

在where后面的条件上建立索引,单列可以建立普通索引,多列则建立组合索引。组合索引需要注意最左前缀原则。

4.2、二级索引

如果有被order by或者group by用到的字段,则可以考虑在这个字段上建索引,这样一来,由于索引天然有序,可以避免order by以及group by所带来的排序,从而提高性能。

4.3、三级索引

如果上面两招还不行,那么就把所查询的字段也加上索引,这时候就形成了所谓的索引覆盖,这样做可以减少一次I/O操作,因为mysql在查询数据的时候,是先查主键索引,然后根据主键索引去查普通索引,然后根据普通索引去查相对应的记录。如果我们所需要的记录在普通索引里都有,那就不需要第三步了。当然,这种建索引的方式比较极端,不适合一般场景。

4.4、索引的选择性

在建立索引的时候,尽量在选择性高的字段上建立。什么是选择性高呢?所谓选择性高就是通过这个字段查出来的数据量少,比如按照名字查一个人的信息,查出来的数据量一般会很少,而按照性别查则可能会把数据库一半的数据都查出来,所以,名字是一个选择性高的字段,而性别是个选择性低的字段。

5、历史数据归档

当数据量到了一年增加500W条的时候,索引也无能为力,这时候一般的思路都是考虑分库分表。如果业务没有爆发式增长,但是数据的确在缓慢增加,则可以不考虑分库分表这种复杂的技术手段,而是进行历史数据归档。我们针对生命周期已经完结的历史数据,比如6个月之前的数据,进行归档。我们可以使用quartz的调度任务在凌晨定时将6个月之前的数据查出来,然后存入远程的hbase服务器。当然,我们也需要提供历史数据的查询接口,以备不时之需。

转:http://www.codeceo.com/article/database-optimization-practice.html

文章可以转载,必须以链接形式标明出处。


本文转自 张冲andy 博客园博客,原文链接: http://www.cnblogs.com/andy6/p/5901767.html   ,如需转载请自行联系原作者
相关实践学习
如何快速连接云数据库RDS MySQL
本场景介绍如何通过阿里云数据管理服务DMS快速连接云数据库RDS MySQL,然后进行数据表的CRUD操作。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助     相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
目录
打赏
0
0
0
0
100
分享
相关文章
PolarDB开源数据库进阶课14 纯享单机版
PolarDB不仅支持基于“共享存储+多计算节点”的集群版,还提供类似开源PostgreSQL的单机版。单机版部署简单,适合大多数应用场景,并可直接使用PostgreSQL生态插件。通过Docker容器、Git克隆代码、编译软件等步骤,即可完成PolarDB单机版的安装与配置。具体操作包括启动容器、进入容器、克隆代码、编译软件、初始化实例、配置参数及启动数据库。此外,还有多个相关教程和视频链接供参考,帮助用户更好地理解和使用PolarDB单机版。
35 0
PolarDB开源数据库进阶课13 单机版转换为集群版
本文介绍如何将“本地存储实例”转换为“共享存储实例”,依赖于先前搭建的实验环境。主要步骤包括:准备PFS二进制文件、格式化共享盘为pfs文件系统、启动pfsd服务、停库并拷贝数据到pfs内、修改配置文件,最后启动实例。通过这些操作,成功实现了从本地存储到共享存储的转换,并验证了新实例的功能。相关系列文章和视频链接提供了更多背景信息和技术细节。
21 0
Redis原理—2.单机数据库的实现
本文概述了Redis数据库的核心结构和操作机制。
Redis原理—2.单机数据库的实现
MySQL生产环境迁移至YashanDB数据库深度体验
这篇文章是作者将 MySQL 生产环境迁移至 YashanDB 数据库的深度体验。介绍了 YashanDB 迁移平台 YMP 的产品相关信息、安装步骤、迁移中遇到的各种兼容问题及解决方案,最后总结了迁移体验,包括工具部署和操作特点,也指出功能有优化空间及暂不支持的部分,期待其不断优化。
如何排查和解决PHP连接数据库MYSQL失败写锁的问题
通过本文的介绍,您可以系统地了解如何排查和解决PHP连接MySQL数据库失败及写锁问题。通过检查配置、确保服务启动、调整防火墙设置和用户权限,以及识别和解决长时间运行的事务和死锁问题,可以有效地保障应用的稳定运行。
135 25
基于SQL Server / MySQL进行百万条数据过滤优化方案
对百万级别数据进行高效过滤查询,需要综合使用索引、查询优化、表分区、统计信息和视图等技术手段。通过合理的数据库设计和查询优化,可以显著提升查询性能,确保系统的高效稳定运行。
35 9
云数据库实战:基于阿里云RDS的Python应用开发与优化
在互联网时代,数据驱动的应用已成为企业竞争力的核心。阿里云RDS为开发者提供稳定高效的数据库托管服务,支持多种数据库引擎,具备自动化管理、高可用性和弹性扩展等优势。本文通过Python应用案例,从零开始搭建基于阿里云RDS的数据库应用,详细演示连接、CRUD操作及性能优化与安全管理实践,帮助读者快速上手并提升应用性能。
MySQL和SQLSugar百万条数据查询分页优化
在面对百万条数据的查询时,优化MySQL和SQLSugar的分页性能是非常重要的。通过合理使用索引、调整查询语句、使用缓存以及采用高效的分页策略,可以显著提高查询效率。本文介绍的技巧和方法,可以为开发人员在数据处理和查询优化中提供有效的指导,提升系统的性能和用户体验。掌握这些技巧后,您可以在处理海量数据时更加游刃有余。
80 9
云数据库:从零到一,构建高可用MySQL集群
在互联网时代,数据成为企业核心资产,传统单机数据库难以满足高并发、高可用需求。云数据库通过弹性扩展、分布式架构等优势解决了这些问题,但也面临数据安全和性能优化挑战。本文介绍了如何从零开始构建高可用MySQL集群,涵盖选择云服务提供商、创建实例、配置高可用架构、数据备份恢复及性能优化等内容,并通过电商平台案例展示了具体应用。
图解MySQL【日志】——磁盘 I/O 次数过高时优化的办法
当 MySQL 磁盘 I/O 次数过高时,可通过调整参数优化。控制刷盘时机以降低频率:组提交参数 `binlog_group_commit_sync_delay` 和 `binlog_group_commit_sync_no_delay_count` 调整等待时间和事务数量;`sync_binlog=N` 设置 write 和 fsync 频率,`innodb_flush_log_at_trx_commit=2` 使提交时只写入 Redo Log 文件,由 OS 择机持久化,但两者在 OS 崩溃时有丢失数据风险。
39 3
AI助理

你好,我是AI助理

可以解答问题、推荐解决方案等