1 前言
在java开发中,最常用的数据存储就是数据库,其中常用的就是mysql,关于数据库的连接配置信息,可能就不是那么的清除了,这里做一个配置信息的说明。
2 数据库连接配置信息
jdbc:mysql://localhost:3306/database_name?servertimezone=asia/shanghai&useunicode=true& characterencoding=utf8&usessl=false&tinyint1isbit=true&allowmultiqueries=true& rewritebatchedstatements=true&useaffectedrows=true&defaultfetchsize=10000& zerodatetimebehavior=converttonull
3 配置信息说明
- 1、servertimezone=asia/shanghai,即时区设置,上海时间即东八区,高版本的mysql需要设置为 gmt%2b8(gmt+8)
- 2、useunicode=true 使用unicode 编码
- 3、characterencoding=utf8 数据库连接字符集
- 4、usessl=false 是否使用 ssl
- 5、tinyint1isbit=true 转换为 tinyint(1) 转换为 boolean ,否则为 int
- 6、allowmultiqueries=true 是否允许多行查询,在批量更新数据时,update 语句使用分号(;)进行连接,此时则需要配置运行多行查询。
- 7、rewritebatchedstatements=true 批处理时需要设置该参数,在mysql5.1.13以上版本才能使用,对 update/insert/delete 都可以生效。数据量大时,消耗时间 jdbc batch < mybatis batch < foreach 循环。
- 8、useaffectedrows=true,默认为false。是否使用影响行数,默认为找的所有修改行数,即false。在update 的情况下,更新3条,如果只有两条有变化,则返回2(useaffectedrows=true),否则返回3.
- 9、defaultfetchsize=10000 默认获取条数
- 10、zerodatetimebehavior=converttonull 默认时间转换为null,避免转换报错。0000-00-00 时间转换为 null
4 数据库sql
# 正常的插入数据 insert into table_name # 如果数据存在则忽略,否则进行插入 insert ignore into table_name # 如果数据存在则更新 insert into table_name (id,name) values(1,'小明') on duplicate key update id = 1,name = "xiaoming" # 如果存在则替换 replace into table_name # mysql 还有计算功能 select 1 + 4 # 计算结果为 5 # ifnull 如果字段为空则赋默认值为 0 select ifnull(age,0) from table_name # 正则查询 user_no 不包含 数字 select * from table_name where user_no regexp '[^0-9]' # 时间处理 select date_format(create_time, '%y%m%d %t'), -- 20210204 12:00:00 id, date_format(create_time, '%y%m%d%h') as time_str, -- 2021020412 date_format(create_time, '%y%m%d') as day_str, -- 20210204 date_format(create_time, '%h') as hour_str, -- 12 weekday(create_time) as week_str -- 0-6,分别是 周一到周日 from table_name
5 mysql 性能优化的关注点
其实,mysql 性能优化不是简单的知道和使用 explain
和show profile
就天下无敌了,这两个只是重要的方面,何况作为资深的crud
工程师还未必经常使用,任何的知识都是了解->熟悉->精通
,当真正的了解原理或者系统的掌握才是真正的悟道
。接下来开始进入正题,mysql
又能优化主要为4
个方面:
- 1 硬件资源和操作系统方面的优化
- 2 架构设计方面的优化
- 3
mysql
服务配置项的优化 - 4
sql
执行层面的优化
6 硬件和操作系统方面的优化
硬件和操作系统就是指mysql
服务所运行的服务器硬件资源情况,影响mysql
性能的主要因素分为 cpu
、内存大小、磁盘读写速度、网络带宽,硬件是指操作系统的网络配置和系统文件句柄数设置(linux
系统中,一切皆文件,了解过es
搭建的同学都知道,es
的搭建就需要修改系统最大打开的文件数) ,这部分的优化一般由运维工程师和dba
联合完成,在项目开发伊始,可以根据业务需要承载的数据量和 tps
要求根据经验,考虑后续的业务扩展性,选择与业务相适配的mysql
服务。
7 架构设计方面的优化
mysql
是一个磁盘io
访问频繁的关系型数据库,在高并发和高性能的应用场景中会承受巨大的压力,可以采取以下措施来应对:
- 1 搭建
mysql
主从集群,单个mysql
服务一旦宕机,将会导致依赖mysql
服务的应用无法响应导致服务不可用,可以采用采用主从集群或者主主集群避免单点故障,保障mysql
服务的高可用性。 - 2 读写分离设计,在读多写少的场景中,可以采用读写分离的方案避免读写冲突导致的性能问题。
- 3 采用分库分表的设计。通过分库可以降低单个
mysql
服务器的io
压力,通过分表的机制可以降低单表数据量,提高sql
的查询效率。 - 4 热点数据引入
nosql
。引入redis
或者mongodb
等非关系型数据库,可以缓解mysql
的访问压力,提升数据的检索性能。
8 mysql 程序配置优化。
mysql
配置文件的优化,一般通过 mysql
配置文件my.cnf
来设置。配置项一般包括:
mysql
的最大连接数max_connections
(注意这里说的不是项目中设置的数据库连接池的连接数)- 最大的传输包(
max_allowed_packet = 16m
) - 开启
bin log
(默认不开启,主要用在mysql
同步的场景中) general log
(通用日志记录sql
的执行记录)来记录日志- 还有就是缓冲区(
buffer pool
), 包括: 关联查询缓冲区join_buffer_size=128m
, 读数据缓冲区read_buffer_size=16m
,随机读缓冲区read_rnd_buffer_size=32m
,排序数据缓冲区sort_buffer_size=32m
,innodb
数据缓冲区innodb_buffer_pool_size=4g
。
这些参数的配置一般和服务器的资源和使用场景有关,需要根据实际情况来设置。配置项的修改需要关注两个点,一是配置的作用范围,分为会话级别和全局范围,全局参数对已经存在的会话不会生效,会话参数设置只在当前会话生效,会话结束则配置失效,全局类的配置放在默认的配置文件中,否则服务重启将会失效。第二是否支持热加载
9 sql 执行层面优化
sql执行层面优化的要点如下:
- 1 慢
sql
查询分析,通过慢sql
查询日志和慢sql
查询分析工具来定位有问题的sql
列表 - 2 执行计划。通过
explain 关键字 + 慢 sql
查看当前sql
的执行计划,需要重点关注select_type、type、 key、 rows、 filterd、 possible_keys
来分析 sql 慢的原因. - 3 使用
show profile
工具来分析,该工具是mysql
提供的分析当前会话中sql
语句资源消耗情况的工具,可以用于sql
调优的测量,当前会话中默认是关闭的状态,需要根据情况打开,默认保存最近15
次的分析结果,针对慢sql
可以通过show profile
工具来进行详细分析,得到整个过程中所以资源的开销情况,比如io
开销cpu
开销和内存开销等情况
10 最终总结
- 1
sql
的查询一定要根据索引来进行数据扫描 - 2 避免查询列上使用函数运算或者运算符,避免索引失效
- 3
where
条件语句中like %
好尽量放置在右边 - 4 使用索引扫描,联合索引的列从左往右,命中的越多越好
- 5
sql
语句的排序字段尽量是索引字段,否则会开辟空间进行结果排序 - 6 查询语句避免使用
*
号,使用需要查询的列信息即可。 - 7 关联查询或者子查询使用小结果集驱动大结果集。
到此这篇关于mysql配置连接参数设置及性能优化的文章就介绍到这了,更多相关mysql配置设置内容请搜索<计算机技术网(www.ctvol.com)!!>以前的文章或继续浏览下面的相关文章希望大家以后多多支持<计算机技术网(www.ctvol.com)!!>!
需要了解更多数据库技术:mysql配置连接参数设置及性能优化,都可以关注数据库技术分享栏目—计算机技术网(www.ctvol.com)!
本文来自网络收集,不代表计算机技术网立场,如涉及侵权请联系管理员删除。
ctvol管理联系方式QQ:251552304
本文章地址:https://www.ctvol.com/dtteaching/1090895.html