MySQL的常見存儲引擎介紹與參數(shù)設(shè)置調(diào)優(yōu)

MySQL常用存儲引擎之MyISAM

特性：
1、并發(fā)性與鎖級別
2、表損壞修復
check table tablename
repair table tablename
3、MyISAM表支持的索引類型
①、全文索引
②、前綴索引
4、MyISAM表支持數(shù)據(jù)壓縮
myisampack
限制：
版本 MySQL5.0時默認表大小為4G
如存儲達標則要修改MAX_Rows和AVG_ROW_LENGTH
版本 > MySQL5.0時默認支持為256TB

適用場景：
1、非事務形應用
2、只讀類應用
3、空間類應用

MySQL常用存儲引擎之Innodb

Innodb存儲引擎的特征
1、Innodb是一種事務性存儲引擎
2、完全支持事務的ACID特性
3、Redo Log 和 Undo Log
4、Innodb支持行級鎖

Innodb使用表空間進行 數(shù)據(jù)存儲
為每個表獨立創(chuàng)建一個表空間存儲
innodb_file_per_table
ON:獨立表空間:tablename.ibd
OFF:系統(tǒng)表空間:ibdataX（X是個數(shù)字，從1開始的數(shù)字）

系統(tǒng)表空間和獨立表空間要如何選擇
比較：
系統(tǒng)表空間無法撿的收縮文件大小
獨立表空格鍵可以通過optimize table命令收縮系統(tǒng)文件
系統(tǒng)表空間會產(chǎn)生IO瓶頸
獨立表空間可以同時向多個文件刷新數(shù)據(jù)

表轉(zhuǎn)移的步驟
步驟：
1、使用mysqldump到處所有數(shù)據(jù)庫表數(shù)據(jù)
2、停止MySQL服務，修改參數(shù)，并刪除Innodb相關(guān)文件
3、重啟MySQL服務，重建Innodb系統(tǒng)表空間
4、重新導入數(shù)據(jù)

MySQL常見的存儲引擎之CSV

文件系統(tǒng)存儲特點
1、數(shù)據(jù)以文本方式存儲在文本中
2、.csv文件存儲表內(nèi)容
3、.csm文件存儲表的元數(shù)據(jù)如表狀態(tài)和數(shù)據(jù)量
4、.frm文件存儲表結(jié)構(gòu)信息
5、以csv格式進行存儲
6、所有列必須都是不能為Null的
7、不支持索引

適用場景：
適用作為數(shù)據(jù)交換的中間表(電子表格->csv文件->MySQL數(shù)據(jù)庫目錄)

MySQL常用存儲引擎之Archive

文件系統(tǒng)存儲特點

1、以zlib對表數(shù)據(jù)進行壓縮,磁盤I/O更少
2、數(shù)據(jù)存儲在ARZ為后綴的文件中

Archive存儲引擎的特點
1、只支持insert和select操作
2、只允許在自增的ID列上加索引

適用場景：
日志和數(shù)據(jù)采集類應用

MySQL常用存儲引擎之Memory

文件系統(tǒng)存儲特點
1、也成HEAP存儲引擎，所以數(shù)據(jù)保存在內(nèi)存中

功能特點：
1、支持HASH索引和Btree索引
2、所有字段都有固定長度varchar(10)=char(10)
3、不支持BLOG和TEXT等大字段
4、Memory存儲引擎使用表級鎖
5、最大大小由max_heap_table_size參數(shù)決定

適用場景：
1、用于查找或者是映射表，例如郵編和地區(qū)的對應表
2、用于保存數(shù)據(jù)分心中產(chǎn)生的中間表
3、用于緩存周期性聚合數(shù)據(jù)的結(jié)果表

MySQL常用存儲引擎之Federated

特點：
1、提供了訪問遠程MySQL服務器上表的方法
2、本地不存儲數(shù)據(jù)，數(shù)據(jù)全部放到遠程服務器上
3、本地需要保存表結(jié)構(gòu)和遠程服務器的連接信息

如何使用
默認靜止，啟用需要在啟動時增加federated參數(shù)
mysql://user_name[:password]@host_name[:port]/db_name/table_name

適用場景：
偶爾的統(tǒng)計分析及手工查詢

如何選擇正確的存儲引擎

參考條件
1、是否要支持事務
2、定期備份
3、崩潰恢復
4、存儲引擎的特有特性

Mysql的服務器參數(shù)介紹

MySQL獲取配置信息路徑

1、命令行參數(shù)

mysqld_safe --datadir=/data/sql_data

2、配置文件

查看配置文件的命令：
[root@localhost ~]# mysqld --help --verbose | egrep -A 1 'Default options'
配置文件的有效路徑
/etc/my.cnf /etc/mysql/my.cnf /usr/etc/my.cnf ~/.my.cnf

MySQL配置參數(shù)的作用域

1、全局參數(shù)
set global 參數(shù)名=參數(shù)值;
set @@global.參數(shù)名:=參數(shù)值;
2、會話參數(shù)
set [session] 參數(shù)名=參數(shù)值;
set @@session.參數(shù)名:=參數(shù)值;

內(nèi)存配置相關(guān)參數(shù)
1、確定可以使用的內(nèi)存的上限
2、確定MySQL的每個連接使用的內(nèi)存
sort_buffer_size
join_buffer_size
read_buffer_size
read_rnd_buffer_size
3、確定需要為操作系統(tǒng)保留多少內(nèi)存
4、如何為緩存池分配內(nèi)存
Innodb_buffer_pool_size
注：設(shè)置緩存池的大小的考量標準為:總內(nèi)存-(每個編程所以需要的內(nèi)存*連接數(shù))-系統(tǒng)保留內(nèi)存
key_buffer_size
select sum(index_length) from information_schema.tables where engines='myisam'

I/O相關(guān)配置參數(shù)
Innodo I/O相關(guān)配置
Innodb_log_file_size 單個事務日志的大小
Innodb_log_files_in_group 控制文件日子的個數(shù)
事務日志總大小 = Innodb_log_files_in_group * Innodb_log_file_size
Innodb_log_buffer_size = (32M or 128M)
Innodb_flush_log_at_trx_commint
0:每秒進行一次log寫入cache,并flush log到磁盤
1[默認]:在每次事務提交執(zhí)行l(wèi)og寫入cache，并flush log到磁盤
2[建議]:每次事務提交，執(zhí)行l(wèi)og數(shù)據(jù)寫入到cache中，每秒執(zhí)行一次flush log到磁盤
Innodb_flush_method=O_DIRECT
Innodb_file_per_table = 1
Innodb_doublewrite = 1

MyISAM I/O相關(guān)配置
delay_key_write
OFF:每次寫操作后刷新鍵緩沖中的臟塊到磁盤
ON:只對在鍵表時指定了delay_key_write選項的表使用延遲刷新
ALL:對所有的MyISAM表都使用延遲建寫入

安全相關(guān)配置參數(shù)
expire_logs_days 指定自動清理binlog的天數(shù)
max_allowed_packet 控制MySQL可以連接的包大小，建議設(shè)置為32M，如果使用了主從復制，參數(shù)應該設(shè)置成一致的
skip_name_resolve 禁用DNS查找
sysdate_is_now 確保sysdate()返回確保性日期
read_only 禁止非super權(quán)限的用戶寫操作 注：建議在主從復制中的從庫開啟此功能。以確保不能修改從庫中的操作，只能從主庫同步過來
skip_slave_start 禁用Salve自動恢復(從庫中的設(shè)置使用)
sql_mode 設(shè)置MySQL所使用的SQL模式 （謹慎操作，可能會造成MySQL無法執(zhí)行）
① strict_trans_tables 給定的數(shù)據(jù)如果不能插入到數(shù)據(jù)庫中，對事務引擎會終端操作，對非事務引擎是沒有影響的
② no_engine_subitiution 在create table中指定engines的時候，如果引擎不可用，不會使用默認引擎建立表
③ no_zero_date 不能再表中插入0年0月0日的日期
④ no_zero_in_date 不接受一部分的為0的日期
⑤ noly_full_group_by

其他常用的配置參數(shù)
sync_binlog 控制MySQL如何向磁盤刷新binlog
tmp_table_size 和 max_heap_table_size 控制內(nèi)存臨時表大小(不宜設(shè)置的太大，以避免內(nèi)存的溢出)
max_connections 控制允許的最大連接數(shù)(默認為100，有點小，根據(jù)自己的業(yè)務適當?shù)恼{(diào)整大小)

什么影響了性能

數(shù)據(jù)庫設(shè)計對性能的影響
1、過分的反范式化為表建立太多的列
2、過分的范式化造成太多的表關(guān)聯(lián)（關(guān)聯(lián)的表盡可能的控制在10個之內(nèi)）
3、在OLTP環(huán)境中使用不前擋的分區(qū)表
4、使用外鍵保證數(shù)據(jù)的完整性

總結(jié)

性能優(yōu)化的順序
1、數(shù)據(jù)庫結(jié)構(gòu)設(shè)計和SQL語句
2、數(shù)據(jù)庫存儲引擎的選擇參數(shù)配置
3、系統(tǒng)選擇及優(yōu)化
4、硬件升級