柚子快報(bào)邀請(qǐng)碼778899分享：數(shù)據(jù)庫 DBA工作經(jīng)驗(yàn)總結(jié)

http://yzkb.51969.com/

目錄

一、MySQL8.0創(chuàng)建一張規(guī)范的表

1.表、字段全采用小寫

2.int類型不再加上最大顯示寬度

3.每張表必須顯式定義自增int類型的主鍵

4.建表時(shí)增加comment來描述字段和表的含義（防止以后忘記）

5.建議包含create_time和update_time字段

6.核心業(yè)務(wù)增加記錄標(biāo)記字段

7.用盡量少的存儲(chǔ)空間來存儲(chǔ)一個(gè)字段的數(shù)據(jù)

8.存儲(chǔ)精確浮點(diǎn)數(shù)必須使用DECIMAL

9.盡量能不使用TEXT、BLOB類型

10.添加合適的索引增加查詢速度

11.字符集建議使用utf8mb4

12.存儲(chǔ)引擎建議使用InnoDB

13.但表字段數(shù)目建議小于30

二、MySQL8.0需要調(diào)整的參數(shù)

1.緩沖池大小和數(shù)量

2.最大連接客戶端數(shù)（默認(rèn)151，通常不夠用）

3.Binlog相關(guān)參數(shù)

4.數(shù)據(jù)目錄

5.服務(wù)ID

6.只讀參數(shù)

7.雙一參數(shù)

8.開啟GTID

9.采用獨(dú)立表空間

10.不區(qū)分大小寫

11.IO操作數(shù)

12.重做日志相關(guān)

13.慢查詢相關(guān)參數(shù)

14.連接超時(shí)參數(shù)

15.事務(wù)隔離級(jí)別

16.InnoDB行鎖等待時(shí)間

17.死鎖信息記錄在錯(cuò)誤日志里

一、MySQL8.0創(chuàng)建一張規(guī)范的表

例：

CREATE TABLE student_info (

`id` INT NOT NULL AUTO_INCREMENT COMMENT '主鍵',

`stu_name` VARCHAR(10) NOT NULL DEFAULT '' COMMENT '姓名',

`stu_class` VARCHAR(10) NOT NULL DEFAULT '' COMMENT '班級(jí)',

`stu_num` INT NOT NULL DEFAULT '0' COMMENT '學(xué)號(hào)',

`stu_score` SMALLINT UNSIGNED NOT NULL DEFAULT '0' COMMENT '總分',

`tuition` DECIMAL(5, 2) NOT NULL DEFAULT '0' COMMENT '學(xué)費(fèi)',

`phone_number` VARCHAR(20) NOT NULL DEFAULT '0' COMMENT '電話號(hào)碼',

`create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '記錄創(chuàng)建時(shí)間',

`update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '記錄更新時(shí)間',

`status` TINYINT NOT NULL DEFAULT '1' COMMENT '1代表記錄有效，0代表記錄無效',

PRIMARY KEY (`id`),

UNIQUE KEY uniq_stu_num (`stu_num`),

KEY idx_stu_score (`stu_score`),

KEY idx_update_time_tuition (`update_time`, `tuition`)

) ENGINE = INNODB charset = utf8mb4 COMMENT '學(xué)生信息表';

1.表、字段全采用小寫

（0 區(qū)分大小寫，1 不區(qū)分大小寫）

2.int類型不再加上最大顯示寬度

3.每張表必須顯式定義自增int類型的主鍵

如果innodb沒有顯示定義主鍵，可能會(huì)選擇唯一索引作為主鍵，唯一索引在寫入的過程可能不是遞增的，寫入數(shù)據(jù)時(shí)可能會(huì)導(dǎo)致數(shù)據(jù)頁頻繁分裂，從而導(dǎo)致寫入效率低和頁空間浪費(fèi)。另外主鍵能保證每一行的唯一性，在日常使用也會(huì)很方便，提升查詢效率、歸檔處理。

4.建表時(shí)增加comment來描述字段和表的含義（防止以后忘記）

5.建議包含create_time和update_time字段

用于審計(jì)和跟蹤，方便查看創(chuàng)建更新、時(shí)間，分析問題方便。

數(shù)據(jù)歸檔也可以根據(jù)這兩個(gè)字段處理。

6.核心業(yè)務(wù)增加記錄標(biāo)記字段

7.用盡量少的存儲(chǔ)空間來存儲(chǔ)一個(gè)字段的數(shù)據(jù)

例：能用int就不varchar/char, 能用tinyint就不用int.

8.存儲(chǔ)精確浮點(diǎn)數(shù)必須使用DECIMAL

9.盡量能不使用TEXT、BLOB類型

浪費(fèi)磁盤空間，影響性能

如果某一個(gè)字段必須使用，建議單獨(dú)建一張表

10.添加合適的索引增加查詢速度

11.字符集建議使用utf8mb4

如果應(yīng)用程序使用多種語言，使用utf8mb4可以防止亂碼，支持多種語言

12.存儲(chǔ)引擎建議使用InnoDB

絕大多數(shù)情況使用：?主持事務(wù)、行鎖、在線備份、支持MVCC多版本并發(fā)控制

13.但表字段數(shù)目建議小于30

列越多，越影響性能，結(jié)構(gòu)變得越復(fù)雜，不利于維護(hù)和管理

二、MySQL8.0需要調(diào)整的參數(shù)

###配置文件舉例

[client]

port = 3306

socket = /tmp/mysql.sock

[mysqld]

port = 3306

socket = /tmp/mysql.sock

## dir set

datadir = /data/mysql/data

innodb_data_home_dir = /data/mysql/data

innodb_log_group_home_dir = /data/mysql/data

log-bin = /data/mysql/binlog/mysql-bin

max_binlog_size = 1G

log_bin_index = /data/mysql/binlog/mysql-bin.index

relay-log = /data/mysql/binlog/mysql-relay-bin

tmpdir = /data/mysql/tmpdir

slow_query_log_file = /data/mysql/log/mysql-slow.log

general_log_file = /data/mysql/log/mysql-general.log

log-error = /data/mysql/log/mysql.err

## slave and binlog

server_id = 6666

skip-slave-start = 0

read_only = 0

binlog_format = row

log_slave_updates = 1

master_info_repository = table

relay_log_info_repository = table

relay_log_purge = 1

relay_log_recovery = 1

sync_binlog = 100

binlog_cache_size = 1M

binlog_expire_logs_seconds = 2592000

log_bin_trust_function_creators = 1

slave_net_timeout=60

#binlog_error_action="IGNORE_ERROR"

innodb_autoinc_lock_mode=1

##

back_log = 200

bulk_insert_buffer_size = 8M

#character-set-server = utf8

lower_case_table_names = 1

## 基線

local-infile = off

skip-networking = off

skip-name-resolve = on

## connect

max_allowed_packet = 32M

max_connect_errors = 1000

max_connections = 1000

wait_timeout = 3600

interactive_timeout = 3600

table_open_cache = 4096

thread_cache_size = 64

thread_stack = 192K

transaction_isolation = REPEATABLE-READ #

pid-file = mysql.pid

## slow

slow_query_log = 1

long_query_time = 1

log_slow_admin_statements

log_queries_not_using_indexes = 0

slow_launch_time = 1

read_buffer_size = 4M

read_rnd_buffer_size = 8M

sort_buffer_size = 8M

join_buffer_size = 32M

tmp_table_size = 128M

max_heap_table_size = 128M

default_storage_engine = innodb

explicit_defaults_for_timestamp = on

## innodb

innodb_buffer_pool_size = 1G

innodb_max_dirty_pages_pct = 80

innodb_thread_concurrency = 8

innodb_buffer_pool_instances = 2

innodb_flush_log_at_trx_commit = 2

innodb_read_io_threads = 8

innodb_write_io_threads = 4

innodb_io_capacity = 1000

innodb_io_capacity_max = 2000

innodb_lru_scan_depth = 1024

innodb_use_native_aio = 1

innodb_flush_neighbors = 1

innodb_buffer_pool_load_at_startup = 1

innodb_buffer_pool_dump_at_shutdown = 1

innodb_data_file_path=ibdata:1G:autoextend

innodb_log_files_in_group = 3

innodb_log_file_size = 2G

innodb_file_per_table = 1

innodb_flush_method = O_DIRECT

innodb_strict_mode = 1

innodb_lock_wait_timeout = 30

innodb_log_buffer_size = 16M

innodb_adaptive_flushing = 1

innodb_change_buffering = all

innodb_purge_threads = 4

innodb_purge_batch_size = 300

innodb_old_blocks_time = 1

innodb_fast_shutdown = 0

performance_schema = 1

innodb_print_all_deadlocks = 1

innodb_sort_buffer_size = 4M

innodb_page_size = 16k

gtid_mode=on

enforce_gtid_consistency=on

table_open_cache_instances=16

binlog_rows_query_log_events=1

## pasword

default_password_lifetime=0

[mysqldump]

quick

max_allowed_packet = 32M

[mysql]

no-auto-rehash

prompt=\p@\d>\_

[mysqld_safe]

open-files-limit = 28192

[mysqlhotcopy]

interactive-timeout

1.緩沖池大小和數(shù)量

innodb_buffer_pool_size = 1G? 在機(jī)器內(nèi)存的60%~80%

需要留一部分內(nèi)存給系統(tǒng)。

2.最大連接客戶端數(shù)（默認(rèn)151，通常不夠用）

max_connections = 1000

3.Binlog相關(guān)參數(shù)

bin_log路徑

binlog_format = row? ?行模式

binlog_expire_logs_seconds = 2592000? ? ? (30天自動(dòng)清除）

log_slave_updates = 1??????主從庫開啟復(fù)制，建議全部開啟

4.數(shù)據(jù)目錄

配置單獨(dú)的數(shù)據(jù)盤，提高查詢效率

5.服務(wù)ID

server_id =???????????(??????????建議設(shè)備IP后兩段，防止同一機(jī)房ID沖突）

6.只讀參數(shù)

read_only = 0? ? ?(一般主庫關(guān)閉0，從庫開啟1）

7.雙一參數(shù)

sync_binlog = 100? ?執(zhí)行100個(gè)事務(wù)再落盤? (一致性要求高就減小數(shù)值）

8.開啟GTID

9.采用獨(dú)立表空間

10.不區(qū)分大小寫

11.IO操作數(shù)

12.重做日志相關(guān)

13.慢查詢相關(guān)參數(shù)

14.連接超時(shí)參數(shù)

15.事務(wù)隔離級(jí)別

16.InnoDB行鎖等待時(shí)間

17.死鎖信息記錄在錯(cuò)誤日志里

柚子快報(bào)邀請(qǐng)碼778899分享：數(shù)據(jù)庫 DBA工作經(jīng)驗(yàn)總結(jié)

http://yzkb.51969.com/

參考閱讀