1 前言

在java開(kāi)發(fā)中，最常用的數(shù)據(jù)存儲(chǔ)就是數(shù)據(jù)庫(kù)，其中常用的就是mysql,關(guān)于數(shù)據(jù)庫(kù)的連接配置信息，可能就不是那么的清除了，這里做一個(gè)配置信息的說(shuō)明。

2 數(shù)據(jù)庫(kù)連接配置信息

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 配置信息說(shuō)明

• 1、serverTimezone=Asia/Shanghai,即時(shí)區(qū)設(shè)置，上海時(shí)間即東八區(qū)，高版本的mysql需要設(shè)置為 GMT%2B8(GMT+8)
• 2、useUnicode=true 使用unicode 編碼
• 3、characterEncoding=utf8 數(shù)據(jù)庫(kù)連接字符集
• 4、useSSL=false 是否使用 ssl
• 5、tinyInt1isBit=true 轉(zhuǎn)換為 tinyint(1) 轉(zhuǎn)換為 boolean ,否則為 int
• 6、allowMultiQueries=true 是否允許多行查詢(xún)，在批量更新數(shù)據(jù)時(shí)，update 語(yǔ)句使用分號(hào)(;)進(jìn)行連接，此時(shí)則需要配置運(yùn)行多行查詢(xún)。
• 7、rewriteBatchedStatements=true 批處理時(shí)需要設(shè)置該參數(shù)，在mysql5.1.13以上版本才能使用，對(duì) update/insert/delete 都可以生效。數(shù)據(jù)量大時(shí)，消耗時(shí)間 jdbc batch < mybatis batch < foreach 循環(huán)。
• 8、useAffectedRows=true,默認(rèn)為false。是否使用影響行數(shù)，默認(rèn)為找的所有修改行數(shù)，即false。在update 的情況下，更新3條，如果只有兩條有變化，則返回2(useAffectedRows=true),否則返回3.
• 9、defaultFetchSize=10000 默認(rèn)獲取條數(shù)
• 10、zeroDateTimeBehavior=convertToNull 默認(rèn)時(shí)間轉(zhuǎn)換為null,避免轉(zhuǎn)換報(bào)錯(cuò)。0000-00-00 時(shí)間轉(zhuǎn)換為 null 

4 數(shù)據(jù)庫(kù)sql

# 正常的插入數(shù)據(jù)
insert into table_name
# 如果數(shù)據(jù)存在則忽略，否則進(jìn)行插入
insert ignore into table_name
# 如果數(shù)據(jù)存在則更新
insert into table_name (id,name) values(1,'小明') ON DUPLICATE KEY UPDATE id = 1,name = "xiaoming"
# 如果存在則替換
replace into table_name
# mysql 還有計(jì)算功能
select 1 + 4 # 計(jì)算結(jié)果為 5
# ifnull 如果字段為空則賦默認(rèn)值為 0
select ifnull(age,0) from table_name
# 正則查詢(xún) user_no 不包含 數(shù)字
SELECT * FROM table_name  WHERE user_no REGEXP '[^0-9]'
# 時(shí)間處理
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 性能優(yōu)化的關(guān)注點(diǎn)

其實(shí)，mysql 性能優(yōu)化不是簡(jiǎn)單的知道和使用 explain和show profile就天下無(wú)敵了，這兩個(gè)只是重要的方面，何況作為資深的CRUD工程師還未必經(jīng)常使用，任何的知識(shí)都是了解->熟悉->精通，當(dāng)真正的了解原理或者系統(tǒng)的掌握才是真正的悟道。接下來(lái)開(kāi)始進(jìn)入正題，mysql又能優(yōu)化主要為4個(gè)方面：

• 1 硬件資源和操作系統(tǒng)方面的優(yōu)化
• 2 架構(gòu)設(shè)計(jì)方面的優(yōu)化
• 3 mysql服務(wù)配置項(xiàng)的優(yōu)化
• 4 sql執(zhí)行層面的優(yōu)化

6 硬件和操作系統(tǒng)方面的優(yōu)化

硬件和操作系統(tǒng)就是指mysql服務(wù)所運(yùn)行的服務(wù)器硬件資源情況，影響mysql性能的主要因素分為 cpu 、內(nèi)存大小、磁盤(pán)讀寫(xiě)速度、網(wǎng)絡(luò)帶寬，硬件是指操作系統(tǒng)的網(wǎng)絡(luò)配置和系統(tǒng)文件句柄數(shù)設(shè)置(linux 系統(tǒng)中，一切皆文件，了解過(guò)ES搭建的同學(xué)都知道，ES的搭建就需要修改系統(tǒng)最大打開(kāi)的文件數(shù)) ,這部分的優(yōu)化一般由運(yùn)維工程師和DBA聯(lián)合完成，在項(xiàng)目開(kāi)發(fā)伊始，可以根據(jù)業(yè)務(wù)需要承載的數(shù)據(jù)量和 TPS 要求根據(jù)經(jīng)驗(yàn)，考慮后續(xù)的業(yè)務(wù)擴(kuò)展性，選擇與業(yè)務(wù)相適配的mysql服務(wù)。

7 架構(gòu)設(shè)計(jì)方面的優(yōu)化

mysql是一個(gè)磁盤(pán)io訪問(wèn)頻繁的關(guān)系型數(shù)據(jù)庫(kù)，在高并發(fā)和高性能的應(yīng)用場(chǎng)景中會(huì)承受巨大的壓力，可以采取以下措施來(lái)應(yīng)對(duì)：

• 1 搭建mysql主從集群，單個(gè)mysql服務(wù)一旦宕機(jī)，將會(huì)導(dǎo)致依賴(lài)mysql服務(wù)的應(yīng)用無(wú)法響應(yīng)導(dǎo)致服務(wù)不可用，可以采用采用主從集群或者主主集群避免單點(diǎn)故障，保障mysql服務(wù)的高可用性。
• 2 讀寫(xiě)分離設(shè)計(jì)，在讀多寫(xiě)少的場(chǎng)景中，可以采用讀寫(xiě)分離的方案避免讀寫(xiě)沖突導(dǎo)致的性能問(wèn)題。
• 3 采用分庫(kù)分表的設(shè)計(jì)。通過(guò)分庫(kù)可以降低單個(gè)mysql服務(wù)器的io壓力,通過(guò)分表的機(jī)制可以降低單表數(shù)據(jù)量，提高sql的查詢(xún)效率。
• 4 熱點(diǎn)數(shù)據(jù)引入Nosql。引入Redis或者MongoDB等非關(guān)系型數(shù)據(jù)庫(kù)，可以緩解mysql的訪問(wèn)壓力，提升數(shù)據(jù)的檢索性能。

8 mysql 程序配置優(yōu)化。

mysql配置文件的優(yōu)化，一般通過(guò) mysql配置文件my.cnf來(lái)設(shè)置。配置項(xiàng)一般包括:

• mysql的最大連接數(shù)max_connections(注意這里說(shuō)的不是項(xiàng)目中設(shè)置的數(shù)據(jù)庫(kù)連接池的連接數(shù))
• 最大的傳輸包（max_allowed_packet = 16M）
• 開(kāi)啟 bin log(默認(rèn)不開(kāi)啟，主要用在 mysql 同步的場(chǎng)景中)
• general log(通用日志記錄 sql 的執(zhí)行記錄)來(lái)記錄日志
• 還有就是緩沖區(qū)（buffer pool）, 包括: 關(guān)聯(lián)查詢(xún)緩沖區(qū) join_buffer_size=128M, 讀數(shù)據(jù)緩沖區(qū)read_buffer_size=16M,隨機(jī)讀緩沖區(qū)read_rnd_buffer_size=32M,排序數(shù)據(jù)緩沖區(qū) sort_buffer_size=32M, innodb 數(shù)據(jù)緩沖區(qū)innodb_buffer_pool_size=4G。

這些參數(shù)的配置一般和服務(wù)器的資源和使用場(chǎng)景有關(guān)，需要根據(jù)實(shí)際情況來(lái)設(shè)置。配置項(xiàng)的修改需要關(guān)注兩個(gè)點(diǎn)，一是配置的作用范圍，分為會(huì)話(huà)級(jí)別和全局范圍，全局參數(shù)對(duì)已經(jīng)存在的會(huì)話(huà)不會(huì)生效，會(huì)話(huà)參數(shù)設(shè)置只在當(dāng)前會(huì)話(huà)生效，會(huì)話(huà)結(jié)束則配置失效，全局類(lèi)的配置放在默認(rèn)的配置文件中，否則服務(wù)重啟將會(huì)失效。第二是否支持熱加載

9 sql 執(zhí)行層面優(yōu)化

sql執(zhí)行層面優(yōu)化的要點(diǎn)如下：

• 1 慢sql查詢(xún)分析，通過(guò)慢sql查詢(xún)?nèi)罩竞吐?code>sql查詢(xún)分析工具來(lái)定位有問(wèn)題的sql列表
• 2 執(zhí)行計(jì)劃。通過(guò)explain 關(guān)鍵字 + 慢 sql查看當(dāng)前sql的執(zhí)行計(jì)劃，需要重點(diǎn)關(guān)注select_type、type、 key、 rows、 filterd、 possible_keys來(lái)分析 sql 慢的原因.
• 3 使用show profile工具來(lái)分析，該工具是mysql提供的分析當(dāng)前會(huì)話(huà)中sql語(yǔ)句資源消耗情況的工具，可以用于sql調(diào)優(yōu)的測(cè)量，當(dāng)前會(huì)話(huà)中默認(rèn)是關(guān)閉的狀態(tài)，需要根據(jù)情況打開(kāi)，默認(rèn)保存最近 15 次的分析結(jié)果，針對(duì)慢sql可以通過(guò)show profile工具來(lái)進(jìn)行詳細(xì)分析，得到整個(gè)過(guò)程中所以資源的開(kāi)銷(xiāo)情況，比如io開(kāi)銷(xiāo)cpu開(kāi)銷(xiāo)和內(nèi)存開(kāi)銷(xiāo)等情況

10 最終總結(jié)

• 1sql的查詢(xún)一定要根據(jù)索引來(lái)進(jìn)行數(shù)據(jù)掃描
• 2 避免查詢(xún)列上使用函數(shù)運(yùn)算或者運(yùn)算符，避免索引失效
• 3where條件語(yǔ)句中like %好盡量放置在右邊
• 4 使用索引掃描，聯(lián)合索引的列從左往右，命中的越多越好
• 5sql語(yǔ)句的排序字段盡量是索引字段，否則會(huì)開(kāi)辟空間進(jìn)行結(jié)果排序
• 6 查詢(xún)語(yǔ)句避免使用*號(hào)，使用需要查詢(xún)的列信息即可。
• 7 關(guān)聯(lián)查詢(xún)或者子查詢(xún)使用小結(jié)果集驅(qū)動(dòng)大結(jié)果集。

到此這篇關(guān)于mysql配置連接參數(shù)設(shè)置及性能優(yōu)化的文章就介紹到這了,更多相關(guān)mysql配置設(shè)置內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！