MySQL數(shù)據(jù)庫(kù)的性能的影響

一. 服務(wù)器的硬件的限制

二. 服務(wù)器所使用的操作系統(tǒng)

三. 服務(wù)器的所配置的參數(shù)設(shè)置不同

四. 數(shù)據(jù)庫(kù)存儲(chǔ)引擎的選擇

五. 數(shù)據(jù)庫(kù)的參數(shù)配置的不同

六. (重點(diǎn))數(shù)據(jù)庫(kù)的結(jié)構(gòu)的設(shè)計(jì)和SQL語(yǔ)句

1). 服務(wù)器的配置和設(shè)置(cpu和可用的內(nèi)存的大小)

1.網(wǎng)絡(luò)和I/O資源

2.cpu的主頻和核心的數(shù)量的選擇

(對(duì)于密集型的應(yīng)用應(yīng)該優(yōu)先考慮主頻高的cpu)

(對(duì)于并發(fā)量大的應(yīng)用優(yōu)先考慮的多核的cpu)

3.磁盤(pán)的配置和選擇

(使用傳統(tǒng)的機(jī)械硬盤(pán):

特點(diǎn)：讀寫(xiě)較慢迅箩、存儲(chǔ)空間大吆倦、最常見(jiàn)育特、使用最多、價(jià)格低;

工作過(guò)程：移動(dòng)磁頭到磁盤(pán)表面上的正確位置;

等待磁盤(pán)的旋轉(zhuǎn)坛怪，使得所得所需的數(shù)據(jù)在磁頭之下;

等待磁盤(pán)旋轉(zhuǎn)過(guò)去，所有所需的數(shù)據(jù)都被磁頭讀出

選擇因素：存儲(chǔ)容量尘盼、傳輸速度偷办、訪(fǎng)問(wèn)時(shí)間、主軸轉(zhuǎn)速匆骗、物理尺寸)

(使用RAID增強(qiáng)傳統(tǒng)的機(jī)器硬盤(pán)的性能：

特點(diǎn)：利用小的磁盤(pán)組成大的磁盤(pán)并提供數(shù)據(jù)的冗余保證數(shù)據(jù)的完整性的技術(shù)

數(shù)據(jù)庫(kù)中所使用的RAID的級(jí)別：

RAID0級(jí)別劳景、RAID1級(jí)別、RAID5級(jí)別[分布式奇偶校驗(yàn)磁盤(pán)陣列]碉就、RAID10[分片的鏡像(數(shù)據(jù)庫(kù)最好的方式)]

RAID級(jí)別選擇：如下圖)

(使用固態(tài)存儲(chǔ)的SSD和PCI-E卡:

特點(diǎn)：相對(duì)于機(jī)械盤(pán)固態(tài)磁盤(pán)有更好的隨機(jī)讀寫(xiě)性能;

相對(duì)于機(jī)械固態(tài)磁盤(pán)能更好的支持并發(fā);

相對(duì)于機(jī)械固態(tài)磁盤(pán)更容易損壞

SSD:使用SATA接口可以替換傳統(tǒng)的磁盤(pán)而不需要任何的改變[受到接口的速度的限制];

SATA接口的SSD同樣支持RAID技術(shù)

PCI-E卡(Fusion-IO卡)：無(wú)法使用在SATA接口[需要使用獨(dú)特的驅(qū)動(dòng)和配置];

價(jià)格貴,使用了cpu的資源和內(nèi)存

使用的場(chǎng)景：適用于存在大量的隨機(jī)I/O的場(chǎng)景;

適用于解決單線(xiàn)程負(fù)載的I/O瓶頸)

(使用網(wǎng)絡(luò)存儲(chǔ)NAS和SAN：

SAN[光纖接入服務(wù)器]:大量順序讀寫(xiě)操作盟广、讀寫(xiě)I/O、緩存瓮钥、I/O合并筋量、隨機(jī)讀寫(xiě)慢（不如本地的RAID）

NAS設(shè)備使用網(wǎng)絡(luò)連接烹吵，基于文件的協(xié)議如NFS或者SMB來(lái)訪(fǎng)問(wèn)

適合場(chǎng)景：數(shù)據(jù)庫(kù)的備份、)

使用RAID增強(qiáng)傳統(tǒng)的機(jī)器硬盤(pán)的性能->RAID0級(jí)別

使用RAID增強(qiáng)傳統(tǒng)的機(jī)器硬盤(pán)的性能->RAID1級(jí)別

使用RAID增強(qiáng)傳統(tǒng)的機(jī)器硬盤(pán)的性能->RAID5級(jí)別

不同REAID級(jí)別的對(duì)比：

注意事項(xiàng)：

1.64位數(shù)據(jù)庫(kù)的版本使用32位的服務(wù)器的版本

2.內(nèi)存的主頻的選擇主板所能支持的最大內(nèi)存的頻率

總結(jié)：

對(duì)于cpu：

1.64位的cpu一定能夠要工作在64位的系統(tǒng)下

2.對(duì)于并發(fā)比較高的場(chǎng)景cpu的數(shù)量比頻率重要

3.對(duì)于cpu密集型的場(chǎng)景和復(fù)雜SQL則頻率越高越好

對(duì)于內(nèi)存：

1.選擇主板所能使用的最高頻率的內(nèi)存

2.內(nèi)存的大小對(duì)性能很重要桨武，所以盡可能的大

I/O子系統(tǒng)：

1.PCIe -> SSD -> RAID10 -> 磁盤(pán) -> SAN

2). 操作系統(tǒng)對(duì)性能的影響

Windows肋拔、FreeBSD、Solaris呀酸、Linuxcentos的參數(shù)優(yōu)化的設(shè)置：? ? (1)內(nèi)核相關(guān)的參數(shù)（/etc/sysctl.conf）? ? ? ? net.core.somaxconn = 65535? ? ? ? net.core.netdev_max_backlog = 65535? ? ? ? net.ipv4.tcp_max_syn_backlog = 65535? ? ? ? net.ipv4.tcp_fin_timeout = 10? ? ? ? net.ipv4.tcp_tw_reuse = 1? ? ? ? net.ipv4.tcp_tw_recycle = 1? ? ? ? net.core.wmem_defaullt = 87380? ? ? ? net.core.wmem_max = 16777216? ? ? ? net.core.rmem_defaullt = 87380? ? ? ? net.core.rmem_max = 16777216? ? ? ? net.ipv4.tcp_keepalive_time = 120? ? ? ? net.ipv4.tcp_keepalive_intvl = 30? ? ? ? net.ipv4.tcp_keepalive_probes = 3? ? ? ? kernel.shmmax = 4294967295? ? ? ? vm.swappiness = 0? ? (2)增加資源限制（/etc/security/limit.conf）? ? ? ? * soft nofile 65535? ? ? ? * hard nofile 65535? ? ? ? ? ? * 表示對(duì)所有的用戶(hù)有效? ? ? ? ? ? soft 指的是當(dāng)前系統(tǒng)的生效的設(shè)置? ? ? ? ? ? hard 表明系統(tǒng)中所能設(shè)定的最大值? ? ? ? ? ? nofile 表示所限制的資源是打開(kāi)文件的最大數(shù)目? ? ? ? ? ? 65535 就是限制的數(shù)量? ? (3).磁盤(pán)調(diào)度策略(/sys/block/devname/queue/scheduler)? ? ? ? noop(電梯式調(diào)度策略）凉蜂、deadline（截止時(shí)間調(diào)度策略）、anticipatory(預(yù)料I/O調(diào)度策略)? ? ? ? cat /sys/block/sda/queue/scheduler? ? ? ? noop anticipatory deadline [cfq]echodeadline > /sys/block/sda/queue/scheduler

3).MySQl的數(shù)據(jù)庫(kù)的體系

MySQl的數(shù)據(jù)庫(kù)的體系

4).MySQl的數(shù)據(jù)庫(kù)的存儲(chǔ)引擎

(1).Mysql之存儲(chǔ)引擎MyISAM? ? 組成的結(jié)構(gòu)：表為MYD和MYI性誉、frm的文件組成? ? 特性：并發(fā)性和鎖級(jí)別? ? ? ? MyISAM表支持索引類(lèi)型? ? ? ? MyISAM表支持?jǐn)?shù)據(jù)的壓縮(命令行：myisampack)? ? ? ? ? ? myisampack -b-fmyIsam.MYI;? ? ? ? ? ? 壓縮后的表不能進(jìn)行寫(xiě)操作窿吩，只能進(jìn)行讀操作? ? 修復(fù):對(duì)數(shù)據(jù)庫(kù)中的表進(jìn)行檢查并修復(fù):? ? ? ? check table mytable;? ? ? ? repair table mytable;? ? ? ? myisamchk工具,修復(fù)時(shí)數(shù)據(jù)庫(kù)服務(wù)必須停止? ? 限制：使用MySQL5.0之前時(shí)默認(rèn)表的大小4G(存儲(chǔ)大表修改MAX_Rows和AVG_ROW_LENGTH)? ? ? ? 使用MySQL5.0之后的版本默認(rèn)支持256TB? ? 適用的場(chǎng)景:非事務(wù)型的應(yīng)用? ? ? ? ? ? ? 只讀類(lèi)的應(yīng)用? ? ? ? ? ? ? 空間類(lèi)的應(yīng)用(GPS的數(shù)據(jù))(2).Mysql之存儲(chǔ)引擎InnoDB? ? mysql5.5.8之后版本默認(rèn)使用的存儲(chǔ)引擎? ? 組成結(jié)構(gòu)：通過(guò)設(shè)置innodb_file_per_table參數(shù)存儲(chǔ)的位置不同? ? ? ? ? ? ? ? ON:獨(dú)立表空間：tablename.ibd? ? ? ? ? ? ? ? OFF:系統(tǒng)表空間:ibdataX? ? 建議：對(duì)于mysql中建議使用InnoDB的獨(dú)立表空間? ? 特性：事務(wù)性存儲(chǔ)引擎? ? ? ? 完全支持事務(wù)的存儲(chǔ)引擎? ? ? ? Redolog(存儲(chǔ)已經(jīng)提交的事務(wù))和Undolog(存儲(chǔ)未提交的事務(wù))? ? ? ? InnoDB支持行級(jí)別鎖? ? ? ? 最大程序的支持并發(fā)? ? ? ? 行級(jí)別的鎖是由存儲(chǔ)引擎層實(shí)現(xiàn)的? ? 鎖：共享鎖(讀鎖)、獨(dú)占鎖(寫(xiě)鎖)? ? ? ? 表級(jí)鎖艾栋、行級(jí)鎖? ? ? ? 阻塞：確保事務(wù)并發(fā)的正常的執(zhí)行? ? ? ? 死鎖：兩個(gè)或者兩個(gè)以上的事務(wù)執(zhí)行過(guò)程中相互等待對(duì)方的資源而產(chǎn)生的一種異常? ? InnoDB狀態(tài)檢查：? ? ? ? show engine innodb status;? ? ? ? 適用場(chǎng)景：InooDB適用于大多數(shù)OLTP應(yīng)用(3).Mysql之存儲(chǔ)引擎CSV? ? 特點(diǎn)：數(shù)據(jù)以文本的方式存儲(chǔ)在文件中? ? ? ? .CSV文件存儲(chǔ)表的內(nèi)容? ? ? ? .CSM文件存儲(chǔ)表的元數(shù)據(jù)如表的狀態(tài)和數(shù)據(jù)量? ? ? ? .frm文件存儲(chǔ)表的結(jié)構(gòu)的信息? ? ? ? 以CSV格式進(jìn)行數(shù)據(jù)的存儲(chǔ)? ? ? ? 所有的列必須不能為NULL的? ? ? ? 不支持索引(不適合大表爆存，不適合在線(xiàn)處理)? ? ? ? 可以對(duì)數(shù)據(jù)文件直接進(jìn)行編輯? ? 適用的場(chǎng)景：適合作為數(shù)據(jù)交換的中間表? ? ? ? ? ? ? mysql數(shù)據(jù)目錄->csv文件->其他web程序? ? ? ? ? ? ? excel電子表格 -> csv文件 -> mysql數(shù)據(jù)目錄(4).Mysql之存儲(chǔ)引擎Archive? ? 特點(diǎn)：以zlib對(duì)表數(shù)據(jù)進(jìn)行壓縮，磁盤(pán)I/O更少? ? ? ? 數(shù)據(jù)存儲(chǔ)在A(yíng)RZ為后綴的文件中? ? ? ? 只支持insert和select操作? ? ? ? 只支持在自增的ID列上加索引? ? 適用場(chǎng)景：? ? ? ? 日志和數(shù)據(jù)采集類(lèi)的應(yīng)用(4).Mysql之存儲(chǔ)引擎Memory? ? 特點(diǎn)：數(shù)據(jù)只保存在內(nèi)存中? ? ? ? Memory存儲(chǔ)引擎的I/O效率特別高? ? ? ? 支持HASH索引和BTree索引? ? ? ? 所有的字段為固定長(zhǎng)度? ? ? ? 不支持BLOG和TEXT等大字段? ? ? ? Memory存儲(chǔ)引擎使用表級(jí)鎖? ? ? ? 表中存儲(chǔ)數(shù)據(jù)的最大值由max_heap_table_size參數(shù)決定? ? 適用場(chǎng)景：用于查找或者映射表蝗砾，例如郵編和地區(qū)? ? ? ? ? ? 用于保存數(shù)據(jù)分析產(chǎn)生的中間表? ? ? ? ? ? 用于緩存周期性聚合數(shù)據(jù)的結(jié)果表

5).MySQl的數(shù)據(jù)庫(kù)的服務(wù)器參數(shù)

(1).Mysql配置參數(shù)作用域? ? 全局參數(shù)setglobal 參數(shù)名=參數(shù)值先较；set@@global.參數(shù)名：=參數(shù)值；? ? 會(huì)話(huà)參數(shù)set[session] 參數(shù)名=參數(shù)值悼粮；set@@session.參數(shù)名：=參數(shù)值闲勺；(2).內(nèi)存配置相關(guān)的參數(shù)? ? ? ? 確定可以使用的內(nèi)存的上限? ? ? ? 確定MySQL的每個(gè)連接使用的內(nèi)存? ? ? ? ? ? sort_buffer_size join_buffer_size? ? ? ? ? ? read_buffer_size read_rnd_buffer_size? ? ? ? 確定需要為操作系統(tǒng)保留多少內(nèi)存? ? ? ? 如何為緩存池分配內(nèi)存? ? ? ? ? ? Innodb_buffer_pool_size? ? ? ? ? ? 總內(nèi)存-（每個(gè)線(xiàn)程鎖需要的內(nèi)存*連接數(shù)）- 系統(tǒng)的保留內(nèi)存? ? ? ? ? ? key_buffer_size(3).I/O相關(guān)配置參數(shù)? ? ? ? InnoDb存儲(chǔ)引擎的I/O參數(shù)設(shè)置：? ? ? ? Innodb_log_file_size? ? ? ? Innodb_log_file_in_group? ? ? ? Innodb_log_buffer_size? ? ? ? Innodb_flush_log_at_trx_commit? ? ? ? Innodb_flush_method = O_DIRECT? ? ? ? Innodb_file_per_table = 1? ? ? ? Innodb_doublewrite = 1? ? ? MySIAM存儲(chǔ)引擎的I/O參數(shù)設(shè)置：? ? ? ? delay_key_write? ? ? ? ? ? OFF:每次操作后刷新鍵緩沖中的臟塊到磁盤(pán)? ? ? ? ? ? ON:只對(duì)在鍵表時(shí)指定了delay_key_write選項(xiàng)的表使用延遲刷新? ? ? ? ? ? ALL:對(duì)所有MYSIAM表都使用延遲鍵寫(xiě)入(4).安全相關(guān)配置參數(shù)? ? ? ? expire_logs_days 指定自動(dòng)清理binlog的天數(shù)? ? ? ? max_allowed_packet 控制MySQL可以接受的包的大小（32M）? ? ? ? skip_name_resolve 禁用DNS查找? ? ? ? sysdate_is_now 確保sysdate()返回確定性的日期? ? ? ? read_only 禁止非super權(quán)限的用戶(hù)寫(xiě)權(quán)限? ? ? ? skip_slave_start 禁止Slave自動(dòng)恢復(fù)? ? ? ? sql_mode 設(shè)置MySQL所使用的SQL模式? ? ? ? ? ? strict_trans_tables? ? ? ? ? ? no_engine_subtitutoion? ? ? ? ? ? no_zero_date? ? ? ? ? ? no_zero_in_date? ? ? ? ? ? only_full_group_by(5).其他相關(guān)配置參數(shù)? ? ? ? sync_binlog = 1控制MySQL如何向磁盤(pán)刷新binlog? ? ? ? tmp_table_size和max_heap_table_size 控制內(nèi)存臨時(shí)表的大小(設(shè)置一致)? ? ? ? max_connections = 2000 控制允許的最大連接數(shù)

6).MySQl的數(shù)據(jù)庫(kù)的結(jié)構(gòu)設(shè)計(jì)和SQL的優(yōu)化

(1).過(guò)分的反范式化為表的建立太多的列

(2).過(guò)分的范式化造成太多的表關(guān)聯(lián)

(3).在OLTP環(huán)境中使用不恰當(dāng)?shù)姆謪^(qū)表

(4).使用外鍵保證數(shù)據(jù)的完整性

?如果你在學(xué)習(xí)Java的過(guò)程中或者在工作中遇到什么問(wèn)題都可以來(lái)群里提問(wèn)扣猫，阿里Java高級(jí)大牛直播講解知識(shí)點(diǎn)菜循，分享知識(shí)，多年工作經(jīng)驗(yàn)的梳理和總結(jié)申尤，帶著大家全面癌幕、科學(xué)地建立自己的技術(shù)體系和技術(shù)認(rèn)知！可以加群找我要課堂鏈接 注意：是免費(fèi)的 沒(méi)有開(kāi)發(fā)經(jīng)驗(yàn)誤入哦! 非喜勿入昧穿！?學(xué)習(xí)交流QQ群：478052716