MySQL存儲(chǔ)引擎InnoDB的配置與使用的講解
MyISAM和InnoDB是MySQL最常有的存儲(chǔ)引擎，上一篇我們講述了InnoDB與MyISAM之間的區(qū)別；由于MyISAM不支持事務(wù)，當(dāng)我們需要使用一個(gè)健壯的事務(wù)型存儲(chǔ)引擎的時(shí)候，InnoDB必然是最好的選擇。
innodb 通過多版本并發(fā)控制（MVCC）來獲得高并發(fā)性,并且實(shí)現(xiàn)了SQL標(biāo)準(zhǔn)的4種隔離級別，默認(rèn)為REPEATABLE級別。同時(shí)，使用一種被稱為next-key locking的策略來避免幻讀（phantom）現(xiàn)象的產(chǎn)生。除此之外,InnoDB存儲(chǔ)引擎還提供了插入緩沖（insert buffer)、二次讀寫(double write)、自適應(yīng)哈希索引(adaptive hash index)、預(yù)讀(read ahead)等高性能和高可用的功能。
如何設(shè)置InnoDB為默認(rèn)引擎：
- 1. 查看mysql存儲(chǔ)引擎情況： mysql>show engines。 InnoDB | YES，說明此mysql數(shù)據(jù)庫服務(wù)器支持InnoDB引擎。
- 2. 設(shè)置InnoDB為默認(rèn)引擎：在配置文件my.ini中的 [mysqld] 下面加入default-storage-engine=INNODB
- 3. 重啟mysql服務(wù)器
- 4. 登錄mysql數(shù)據(jù)庫，mysql>show engines。如果出現(xiàn) InnoDB |DEFAULT，則表示設(shè)置InnoDB為默認(rèn)引擎成功。
InnoDB常用配置參數(shù)：
#InnoDB存儲(chǔ)數(shù)據(jù)字典、內(nèi)部數(shù)據(jù)結(jié)構(gòu)的緩沖池，16MB 已經(jīng)足夠大了。 innodb_additional_mem_pool_size = 16M #InnoDB用于緩存數(shù)據(jù)、索引、鎖、插入緩沖、數(shù)據(jù)字典等 #如果是專用的DB服務(wù)器，且以InnoDB引擎為主的場景，通常可設(shè)置物理內(nèi)存的50% #如果是非專用DB服務(wù)器，可以先嘗試設(shè)置成內(nèi)存的1/4，如果有問題再調(diào)整 #默認(rèn)值是8M，非?？覺，這也是導(dǎo)致很多人覺得InnoDB不如MyISAM好用的緣故 innodb_buffer_pool_size = 4G #InnoDB共享表空間初始化大小，默認(rèn)是 10MB，也非?？覺，改成 1GB，并且自動(dòng)擴(kuò)展 innodb_data_file_path = ibdata1:1G:autoextend #如果不了解本選項(xiàng)，建議設(shè)置為1，能較好保護(hù)數(shù)據(jù)可靠性，對性能有一定影響，但可控 innodb_flush_log_at_trx_commit = 1 #InnoDB的log buffer，通常設(shè)置為 64MB 就足夠了 innodb_log_buffer_size = 64M #InnoDB redo log大小，通常設(shè)置256MB 就足夠了 innodb_log_file_size = 256M #InnoDB redo log文件組，通常設(shè)置為 2 就足夠了 innodb_log_files_in_group = 2 #啟用InnoDB的獨(dú)立表空間模式，便于管理 innodb_file_per_table = 1 #啟用InnoDB的status file，便于管理員查看以及監(jiān)控等 innodb_status_file = 1 #設(shè)置事務(wù)隔離級別為 READ-COMMITED，提高事務(wù)效率，通常都滿足事務(wù)一致性要求 transaction_isolation = READ-COMMITTED 其他配置選項(xiàng)也需要注意： #設(shè)置最大并發(fā)連接數(shù)，如果前端程序是PHP，可適當(dāng)加大，但不可過大 #如果前端程序采用連接池，可適當(dāng)調(diào)小，避免連接數(shù)過大 max_connections = 60 #最大連接錯(cuò)誤次數(shù)，可適當(dāng)加大，防止頻繁連接錯(cuò)誤后，前端host被mysql拒絕掉 max_connect_errors = 100000 #設(shè)置慢查詢閥值，建議設(shè)置最小的 1 秒 long_query_time = 1 #設(shè)置臨時(shí)表最大值，這是每次連接都會(huì)分配，不宜設(shè)置過大 max_heap_table_size 和 tmp_table_size 要設(shè)置一樣大 max_heap_table_size = 96M tmp_table_size = 96M #每個(gè)連接都會(huì)分配的一些排序、連接等緩沖，一般設(shè)置為 2MB 就足夠了 sort_buffer_size = 2M join_buffer_size = 2M read_buffer_size = 2M read_rnd_buffer_size = 2M #建議關(guān)閉query cache，有些時(shí)候?qū)π阅芊炊且环N損害 query_cache_size = 0 #如果是以InnoDB引擎為主的DB，專用于MyISAM引擎的 key_buffer_size 可以設(shè)置較小，8MB 已足夠 #如果是以MyISAM引擎為主，可設(shè)置較大，但不能超過4G #在這里，強(qiáng)烈建議不使用MyISAM引擎，默認(rèn)都是用InnoDB引擎 key_buffer_size = 8M #設(shè)置連接超時(shí)閥值，如果前端程序采用短連接，建議縮短這2個(gè)值 #如果前端程序采用長連接，可直接注釋掉這兩個(gè)選項(xiàng)，是用默認(rèn)配置(8小時(shí)) interactive_timeout = 120 wait_timeout = 120
幾個(gè)關(guān)于InnoDB引擎數(shù)據(jù)表設(shè)計(jì)相關(guān)的建議：
- 1. 所有InnoDB數(shù)據(jù)表都創(chuàng)建一個(gè)和業(yè)務(wù)無關(guān)的自增數(shù)字型作為主鍵，對保證性能很有幫助；
- 2. 杜絕使用text/blob，確實(shí)需要使用的，盡可能拆分出去成一個(gè)獨(dú)立的表；
- 3. 時(shí)間戳建議使用 TIMESTAMP 類型存儲(chǔ)；
- 4. IPV4 地址建議用 INT UNSIGNED 類型存儲(chǔ)；
- 5. 性別等非是即非的邏輯，建議采用 TINYINT 存儲(chǔ)，而不是 CHAR(1)；
- 6. 存儲(chǔ)較長文本內(nèi)容時(shí)，建議采用JSON/BSON格式存儲(chǔ)；
InnoDB鎖類型
InnoDB存儲(chǔ)引擎實(shí)現(xiàn)如下兩種標(biāo)準(zhǔn)的行級鎖
- 共享鎖（S Lock）, 允許事務(wù)讀一行數(shù)據(jù)
- 排他鎖（X Lock），允許事務(wù)刪除或更新一行數(shù)據(jù)
共享鎖和排他鎖的兼容
注意：
（1）S鎖和X鎖都是行鎖，兼容是指對同一記錄（row）鎖的兼容性.
（2）事務(wù)T1已經(jīng)獲得行R的共享鎖，另一個(gè)事務(wù)T2可以立即獲得行R的共享鎖，這種情況稱為鎖兼容。事務(wù)T3想獲得行R的排他鎖，則必須等待事務(wù)T1、T2釋放行R上的共享鎖，這種情況成為鎖的不兼容.
總結(jié)
以上就是這篇文章的全部內(nèi)容了，希望本文的內(nèi)容對大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值，謝謝大家對腳本之家的支持。如果你想了解更多相關(guān)內(nèi)容請查看下面相關(guān)鏈接
相關(guān)文章
詳解Windows?Server?2012下安裝MYSQL5.7.24的問題
這篇文章主要介紹了Windows?Server?2012下安裝MYSQL5.7.24的詳細(xì)過程，本文通過圖文并茂實(shí)例代碼相結(jié)合給大家介紹的非常詳細(xì)，對大家的學(xué)習(xí)或工作具有一定的參考借鑒價(jià)值,需要的朋友可以參考下2022-02-02
mysql啟動(dòng)錯(cuò)誤之mysql啟動(dòng)報(bào)1067錯(cuò)誤解決方法
MYSQL啟動(dòng)報(bào)1067錯(cuò)誤，系統(tǒng)日志中是“服務(wù) mysql 意外停止” Mysql日志中則是：Plugin 'FEDERATED' is disabled,下面給出解決方法2014-02-02
mysql優(yōu)化系列 DELETE子查詢改寫優(yōu)化
有個(gè)采用子查詢的DELETE執(zhí)行得非常慢，改寫成SELECT后執(zhí)行卻很快，最后把這個(gè)子查詢DELETE改寫成JOIN優(yōu)化過程2016-08-08
禁止mysql做域名解析(解決遠(yuǎn)程訪問mysql時(shí)很慢)
MySQL多版本并發(fā)控制MVCC深入學(xué)習(xí)
mysql中的utf8與utf8mb4存儲(chǔ)及區(qū)別
MySQL使用binlog日志進(jìn)行數(shù)據(jù)庫遷移和數(shù)據(jù)恢復(fù)
