❶ mysql使用innodb做緩存，感覺沒有什麼效果，CPU一直都好高，是不是設置那
MySQL資料庫有多種存儲引擎：比如：MyISAM、InnoDB、MERGE、MEMORY(HEAP)、BDB(BerkeleyDB)、EXAMPLE、FEDERATED、ARCHIVE、CSV、BLACKHOLE等等，最常見的也就是MyISAM和InnoDB了，下面主要講解下MyISAM和InnoDB兩種mysql資料庫存儲引擎的區別。 MyISAM引擎是一種非事務性的引擎，提供高速存儲和檢索，以及全文搜索能力，適合數據倉庫等查詢頻繁的應用。MyISAM中，一個table實際保存為三個文件，.frm存儲表定義，.MYD存儲數據,.MYI存儲索引。MyISAM在所有MySQL配置里被支持，它是默認的存儲引擎，除非你配置MySQL默認使用另外一個引擎。 MySQL伺服器中的其他非事務性存儲引擎（如MyISAM）遵從不同的數據完整性範例，稱之為逗原子操作地。按照事務術語，MyISAM表總能高效地工作在AUTOCOMMIT=1模式下。原子操作通常能提供可比較的完整性以及更好的性能。與經過優化調整的最快的事務性表相比，它的速度快3～5倍。由於MySQL伺服器支持兩種範例，因而你能決定是否利用原子操作的速度更好地服務於你的應用程序，或使用事務特性。該選擇可按表進行。 InnoDB則是一種支持事務的引擎。給MySQL提供了具有提交，回滾和崩潰恢復能力的事務安全（ACID兼容）存儲引擎。所以的數據存儲在一個或者多個數據文件中，支持類似於Oracle的鎖機制。一般在OLTP應用中使用較廣泛。如果沒有指定InnoDB配置選項，MySQL將在MySQL數據目錄下創建一個名為ibdata1的自動擴展數據文件，以及兩個名為ib_logfile0和ib_logfile1的日誌文件。 InnoDB鎖定在行級並且也在SELECT語句提供一個Oracle風格一致的非鎖定讀。這些特色增加了多用戶部署和性能。沒有在InnoDB中擴大鎖定的需要，因為在InnoDB中行級鎖定適合非常小的空間。InnoDB也支持FOREIGN KEY強制。在SQL查詢中，你可以自由地將InnoDB類型的表與其它MySQL的表的類型混合起來，甚至在同一個查詢中也可以混合。 InnoDB是為處理巨大數據量時的最大性能設計。它的CPU效率可能是任何其它基於磁碟的關系資料庫引擎所不能匹敵的。InnoDB存儲引擎被完全與MySQL伺服器整合，InnoDB存儲引擎為在主內存中緩存數據和索引而維持它自己的緩沖池。 InnoDB存儲它的表＆索引在一個表空間中，表空間可以包含數個文件。InnoDB表可以是任何尺寸，即使在文件尺寸被限制為2GB的操作系統上。InnoDB也默認被包括在所有MySQL 5.1二進制分發版里。
❷ 資料庫的事務機制是什麼
回答的有點多請耐心看完。
希望能幫助你還請及時採納謝謝
1事務的原理
事務就是將一組SQL語句放在同一批次內去執行，如果一個SQL語句出錯,則該批次內的所有SQL都將被取消執行。MySQL事務處理只支持InnoDB和BDB數據表類型。
1事務的ACID原則
** 1（Atomicity）原子性**： 事務是最小的執行單位，不允許分割。原子性確保動作要麼全部完成，要麼完全不起作用；
2（Consistency）一致性： 執行事務前後，數據保持一致；
3（Isolation）隔離性： 並發訪問資料庫時，一個事務不被其他事務所干擾。
4（Durability）持久性: 一個事務被提交之後。對資料庫中數據的改變是持久的，即使資料庫發生故障。
1緩沖池（Buffer Pool）
Buffer Pool中包含了磁碟中部分數據頁的映射。當從資料庫讀取數據時，會先從Buffer Pool中讀取數據，如果Buffer Pool中沒有，則從磁碟讀取後放入到Buffer Pool中。當向資料庫寫入數據時，會先寫入到Buffer Pool中，Buffer Pool中更新的數據會定期刷新到磁碟中（此過程稱為刷臟）。
2日誌緩沖區（Log Buffer）
當在MySQL中對InnoDB表進行更改時，這些更改命令首先存儲在InnoDB日誌緩沖區（Log Buffer）的內存中，然後寫入通常稱為重做日誌（redo logs）的InnoDB日誌文件中。
3雙寫機制緩存（DoubleWrite Buffer）
Doublewrite Buffer是共享表空間的物理文件的 buffer,其大小是2MB.是一個一分為二的2MB空間。
刷臟操作開始之時,先進行臟頁**『備份』**操作.將臟頁數據寫入 Doublewrite Buffer.
將Doublewrite Buffer(順序IO)寫入磁碟文件中(共享表空間) 進行刷臟操作.
4回滾日誌(Undo Log)
Undo Log記錄的是邏輯日誌.記錄的是事務過程中每條數據的變化版本和情況.
在Innodb 磁碟架構中Undo Log 默認是共享表空間的物理文件的Buffer.
在事務異常中斷,或者主動(Rollback)回滾的過程中 ,Innodb基於 Undo Log進行數據撤銷回滾,保證數據回歸至事務開始狀態.
5重做日誌(Redo Log)
Redo Log通常指的是物理日誌，記錄的是數據頁的物理修改.並不記錄行記錄情況。（也就是只記錄要做哪些修改，並不記錄修改的完成情況） 當資料庫宕機重啟的時候，會將重做日誌中的內容恢復到資料庫中。
1原子性
Innodb事務的原子性保證,包含事務的提交機制和事務的回滾機制.在Innodb引擎中事務的回滾機制是依託 回滾日誌(Undo Log) 進行回滾數據，保證數據回歸至事務開始狀態.
2那麼不同的隔離級別，隔離性是如何實現的，為什麼不同事物間能夠互不幹擾？ 答案是 鎖 和 MVCC。
3持久性
基於事務的提交機制流程有可能出現三種場景.
1 數據刷臟正常.一切正常提交，Redo Log 循環記錄.數據成功落盤.持久性得以保證
2數據刷臟的過程中出現的系統意外導致頁斷裂現象 (部分刷臟成功)，針對頁斷裂情況,採用Double write機制進行保證頁斷裂數據的恢復.
3數據未出現頁斷裂現象,也沒有刷臟成功，MySQL通過Redo Log 進行數據的持久化即可
4一致性
從資料庫層面，資料庫通過原子性、隔離性、持久性來保證一致性
2事務的隔離級別
Mysql 默認採用的 REPEATABLE_READ隔離級別 Oracle 默認採用的 READ_COMMITTED隔離級別
臟讀： 指一個事務讀取了另外一個事務未提交的數據。
不可重復讀： 在一個事務內讀取表中的某一行數據，多次讀取結果不同
虛讀(幻讀)： 是指在一個事務內讀取到了別的事務插入的數據，導致前後讀取不一致。
2基本語法
-- 使用set語句來改變自動提交模式
SET autocommit = 0; /*關閉*/
SET autocommit = 1; /*開啟*/
-- 注意:
--- 1.MySQL中默認是自動提交
--- 2.使用事務時應先關閉自動提交
-- 開始一個事務,標記事務的起始點
START TRANSACTION
-- 提交一個事務給資料庫
COMMIT
-- 將事務回滾,數據回到本次事務的初始狀態
ROLLBACK
-- 還原MySQL資料庫的自動提交
SET autocommit =1;
-- 保存點
SAVEPOINT 保存點名稱 -- 設置一個事務保存點
ROLLBACK TO SAVEPOINT 保存點名稱 -- 回滾到保存點
RELEASE SAVEPOINT 保存點名稱 -- 刪除保存點
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
/*
課堂測試題目
A在線買一款價格為500元商品,網上銀行轉賬.
A的銀行卡余額為2000,然後給商家B支付500.
商家B一開始的銀行卡余額為10000
創建資料庫shop和創建表account並插入2條數據
*/
CREATE DATABASE `shop`CHARACTER SET utf8 COLLATE utf8_general_ci;
USE `shop`;
CREATE TABLE `account` (
`id` INT(11) NOT NULL AUTO_INCREMENT,
`name` VARCHAR(32) NOT NULL,
`cash` DECIMAL(9,2) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8
INSERT INTO account (`name`,`cash`)
VALUES('A',2000.00),('B',10000.00)
-- 轉賬實現
SET autocommit = 0; -- 關閉自動提交
START TRANSACTION; -- 開始一個事務,標記事務的起始點
UPDATE account SET cash=cash-500 WHERE `name`='A';
UPDATE account SET cash=cash+500 WHERE `name`='B';
COMMIT; -- 提交事務
# rollback;
SET autocommit = 1; -- 恢復自動提交
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
3事務實現方式-MVCC
1什麼是MVCC
MVCC是mysql的的多版本並發控制即multi-Version Concurrency Controller，mysql的innodb引擎支持MVVC。MVCC是為了實現事務的隔離性，通過版本號，避免同一數據在不同事務間的競爭，你可以把它當成基於多版本號的一種樂觀鎖。當然，這種樂觀鎖只在事務級別為RR（可重復讀）和RC（讀提交）生效。MVCC最大的好處，相信也是耳熟能詳：讀不加鎖，讀寫不沖突，極大的增加了系統的並發性能。
2MVCC的實現機制
InnoDB在每行數據都增加兩個隱藏欄位，一個記錄創建的版本號，一個記錄刪除的版本號。
在多版本並發控制中，為了保證數據操作在多線程過程中，保證事務隔離的機制，降低鎖競爭的壓力，保證較高的並發量。在每開啟一個事務時，會生成一個事務的版本號，被操作的數據會生成一條新的數據行（臨時），但是在提交前對其他事務是不可見的；對於數據的更新（包括增刪改）操作成功，會將這個版本號更新到數據的行中；事務提交成功，新的版本號也就更新到了此數據行中。這樣保證了每個事務操作的數據，都是互不影響的，也不存在鎖的問題。
3MVCC下的CRUD
SELECT：
當隔離級別是REPEATABLE READ時select操作，InnoDB每行數據來保證它符合兩個條件：
** 1 事務的版本號 大於等於 創建行版本號**
** 2 行數據的刪除版本 未定義 或者大於 事務版本號**
【行創建版本號 事務版本號 行刪除版本號】
INSERT：
InnoDB為這個新行 記錄 當前的系統版本號。
DELETE：
InnoDB將當前的系統版本號 設置為 這一行的刪除版本號。
UPDATE：
InnoDB會寫一個這行數據的新拷貝，這個拷貝的版本為 當前的系統版本號。它同時也會將這個版本號 寫到 舊行的刪除版本里。
————————————————
版權聲明：本文為CSDN博主「@Autowire」的原創文章，遵循CC 4.0 BY-SA版權協議，轉載請附上原文出處鏈接及本聲明。
原文鏈接：https://blog.csdn.net/zs18753479279/article/details/113933252
❸ mysql innodb和myisam區別
InnoDB和MyISAM是很多人在使用MySQL時最常用的兩個表類型，這兩個表類型各有優劣，5.7之後就不一樣了
1、事務和外鍵
InnoDB具有事務，支持4個事務隔離級別，回滾，崩潰修復能力和多版本並發的事務安全，包括ACID。如果應用中需要執行大量的INSERT或UPDATE操作，則應該使用InnoDB，這樣可以提高多用戶並發操作的性能
MyISAM管理非事務表。它提供高速存儲和檢索，以及全文搜索能力。如果應用中需要執行大量的SELECT查詢，那麼MyISAM是更好的選擇
2、全文索引
Innodb不支持全文索引，如果一定要用的話，最好使用sphinx等搜索引擎。myisam對中文支持的不是很好
不過新版本的Innodb已經支持了
3、鎖
mysql支持三種鎖定級別，行級、頁級、表級;
MyISAM支持表級鎖定，提供與 Oracle 類型一致的不加鎖讀取(non-locking read in SELECTs)
InnoDB支持行級鎖，InnoDB表的行鎖也不是絕對的，如果在執行一個SQL語句時MySQL不能確定要掃描的范圍，InnoDB表同樣會鎖全表，注意間隙鎖的影響
例如update table set num=1 where name like 「%aaa%」
4、存儲
MyISAM在磁碟上存儲成三個文件。第一個文件的名字以表的名字開始，擴展名指出文件類型， .frm文件存儲表定義，數據文件的擴展名為.MYD， 索引文件的擴展名是.MYI
InnoDB，基於磁碟的資源是InnoDB表空間數據文件和它的日誌文件，InnoDB 表的大小隻受限於操作系統文件的大小
注意：MyISAM表是保存成文件的形式，在跨平台的數據轉移中使用MyISAM存儲會省去不少的麻煩
5、索引
InnoDB（索引組織表）使用的聚簇索引、索引就是數據，順序存儲，因此能緩存索引，也能緩存數據
MyISAM（堆組織表）使用的是非聚簇索引、索引和文件分開，隨機存儲，只能緩存索引
6、並發
MyISAM讀寫互相阻塞：不僅會在寫入的時候阻塞讀取，MyISAM還會在讀取的時候阻塞寫入，但讀本身並不會阻塞另外的讀
InnoDB讀寫阻塞與事務隔離級別相關
7、場景選擇
MyISAM
不需要事務支持（不支持）
並發相對較低（鎖定機制問題）
數據修改相對較少（阻塞問題），以讀為主
數據一致性要求不是非常高
盡量索引（緩存機制）
調整讀寫優先順序，根據實際需求確保重要操作更優先
啟用延遲插入改善大批量寫入性能
盡量順序操作讓insert數據都寫入到尾部，減少阻塞
分解大的操作，降低單個操作的阻塞時間
降低並發數，某些高並發場景通過應用來進行排隊機制
對於相對靜態的數據，充分利用Query Cache可以極大的提高訪問效率
MyISAM的Count只有在全表掃描的時候特別高效，帶有其他條件的count都需要進行實際的數據訪問
需要事務支持（具有較好的事務特性）
行級鎖定對高並發有很好的適應能力，但需要確保查詢是通過索引完成
數據更新較為頻繁的場景
數據一致性要求較高
硬體設備內存較大，可以利用InnoDB較好的緩存能力來提高內存利用率，盡可能減少磁碟 IO
主鍵盡可能小，避免給Secondary index帶來過大的空間負擔
避免全表掃描，因為會使用表鎖
盡可能緩存所有的索引和數據，提高響應速度
在大批量小插入的時候，盡量自己控制事務而不要使用autocommit自動提交
合理設置innodb_flush_log_at_trx_commit參數值，不要過度追求安全性
避免主鍵更新，因為這會帶來大量的數據移動
InnoDB
8、其它細節
1）InnoDB 中不保存表的具體行數，注意的是，當count(*)語句包含 where條件時，兩種表的操作是一樣的
2）對於AUTO_INCREMENT類型的欄位，InnoDB中必須包含只有該欄位的索引，但是在MyISAM表中，可以和其他欄位一起建立聯合索引， 如果你為一個表指定AUTO_INCREMENT列，在數據詞典里的InnoDB表句柄包含一個名為自動增長計數器的計數器，它被用在為該列賦新值。自動增長計數器僅被存儲在主內存中，而不是存在磁碟
3）DELETE FROM table時，InnoDB不會重新建立表，而是一行一行的刪除
4）LOAD TABLE FROM MASTER操作對InnoDB是不起作用的，解決方法是首先把InnoDB表改成MyISAM表，導入數據後再改成InnoDB表，但是對於使用的額外的InnoDB特性(例如外鍵)的表不適用
5）如果執行大量的SELECT，MyISAM是更好的選擇，如果你的數據執行大量的INSERT或UPDATE，出於性能方面的考慮，應該使用InnoDB表
7、為什麼MyISAM會比Innodb 的查詢速度快
InnoDB在做SELECT的時候，要維護的東西比MYISAM引擎多很多；
1）InnoDB要緩存數據和索引，MyISAM只緩存索引塊，這中間還有換進換出的減少
2）innodb定址要映射到塊，再到行，MyISAM記錄的直接是文件的OFFSET，定位比INNODB要快
3）InnoDB還需要維護MVCC一致；雖然你的場景沒有，但他還是需要去檢查和維護
MVCC ( Multi-Version Concurrency Control )多版本並發控制
InnoDB：通過為每一行記錄添加兩個額外的隱藏的值來實現MVCC，這兩個值一個記錄這行數據何時被創建，另外一個記錄這行數據何時過期（或者被刪除）。但是InnoDB並不存儲這些事件發生時的實際時間，相反它只存儲這些事件發生時的系統版本號。這是一個隨著事務的創建而不斷增長的數字。每個事務在事務開始時會記錄它自己的系統版本號。每個查詢必須去檢查每行數據的版本號與事務的版本號是否相同。讓我們來看看當隔離級別是REPEATABLE READ時這種策略是如何應用到特定的操作的
SELECT InnoDB必須每行數據來保證它符合兩個條件
1、InnoDB必須找到一個行的版本，它至少要和事務的版本一樣老(也即它的版本號不大於事務的版本號)。這保證了不管是事務開始之前，或者事務創建時，或者修改了這行數據的時候，這行數據是存在的。
2、這行數據的刪除版本必須是未定義的或者比事務版本要大。這可以保證在事務開始之前這行數據沒有被刪除。
8、mysql性能討論
MyISAM最為人垢病的缺點就是缺乏事務的支持
InnoDB 的磁碟性能很令人擔心
MySQL 缺乏良好的 tablespace
兩種類型最主要的差別就是Innodb 支持事務處理與外鍵和行級鎖.而MyISAM不支持.所以MyISAM往往就容易被人認為只適合在小項目中使用。
我作為使用MySQL的用戶角度出發，Innodb和MyISAM都是比較喜歡的，但是從我目前運維的資料庫平台要達到需求：99.9%的穩定性，方便的擴展性和高可用性來說的話，MyISAM絕對是我的首選。
原因如下：
1、首先我目前平台上承載的大部分項目是讀多寫少的項目，而MyISAM的讀性能是比Innodb強不少的。
2、MyISAM的索引和數據是分開的，並且索引是有壓縮的，內存使用率就對應提高了不少。能載入更多索引，而Innodb是索引和數據是緊密捆綁的，沒有使用壓縮從而會造成Innodb比MyISAM體積龐大不小。
3、從平台角度來說，經常隔1，2個月就會發生應用開發人員不小心update一個表where寫的范圍不對，導致這個表沒法正常用了，這個時候MyISAM的優越性就體現出來了，隨便從當天拷貝的壓縮包取出對應表的文件，隨便放到一個資料庫目錄下，然後mp成sql再導回到主庫，並把對應的binlog補上。如果是Innodb，恐怕不可能有這么快速度，別和我說讓Innodb定期用導出xxx.sql機制備份，因為我平台上最小的一個資料庫實例的數據量基本都是幾十G大小。
4、從我接觸的應用邏輯來說，select count(*) 和order by 是最頻繁的，大概能佔了整個sql總語句的60%以上的操作，而這種操作Innodb其實也是會鎖表的，很多人以為Innodb是行級鎖，那個只是where對它主鍵是有效，非主鍵的都會鎖全表的。
5、還有就是經常有很多應用部門需要我給他們定期某些表的數據，MyISAM的話很方便，只要發給他們對應那表的frm.MYD,MYI的文件，讓他們自己在對應版本的資料庫啟動就行，而Innodb就需要導出xxx.sql了，因為光給別人文件，受字典數據文件的影響，對方是無法使用的。
6、如果和MyISAM比insert寫操作的話，Innodb還達不到MyISAM的寫性能，如果是針對基於索引的update操作，雖然MyISAM可能會遜色Innodb,但是那麼高並發的寫，從庫能否追的上也是一個問題，還不如通過多實例分庫分表架構來解決。
7、如果是用MyISAM的話，merge引擎可以大大加快應用部門的開發速度，他們只要對這個merge表做一些select count(*)操作，非常適合大項目總量約幾億的rows某一類型(如日誌，調查統計)的業務表。
當然Innodb也不是絕對不用，用事務的項目如模擬炒股項目，我就是用Innodb的，活躍用戶20多萬時候，也是很輕松應付了，因此我個人也是很喜歡Innodb的，只是如果從資料庫平台應用出發，我還是會首選MyISAM。
另外，可能有人會說你MyISAM無法抗太多寫操作，但是我可以通過架構來彌補，說個我現有用的資料庫平台容量：主從數據總量在幾百T以上，每天十多億 pv的動態頁面，還有幾個大項目是通過數據介面方式調用未算進pv總數，(其中包括一個大項目因為初期memcached沒部署,導致單台資料庫每天處理 9千萬的查詢)。而我的整體資料庫伺服器平均負載都在0.5-1左右。
❹ 如何配置innodb啟動參數
[client]
port = 3306
socket = /tmp/mysql.sock
[mysqld]
port = 3306
socket = /tmp/mysql.sock
basedir = /usr/local/mysql
datadir = /data/mysql
pid-file = /data/mysql/mysql.pid
user = mysql
bind-address = 0.0.0.0
server-id = 1 #表示是本機的序號為1,一般來講就是master的意思
skip-name-resolve
# 禁止MySQL對外部連接進行DNS解析，使用這一選項可以消除MySQL進行DNS解析的時間。但需要注意，如果開啟該選項，
# 則所有遠程主機連接授權都要使用IP地址方式，否則MySQL將無法正常處理連接請求
#skip-networking
back_log = 600
# MySQL能有的連接數量。當主要MySQL線程在一個很短時間內得到非常多的連接請求，這就起作用，
# 然後主線程花些時間(盡管很短)檢查連接並且啟動一個新線程。back_log值指出在MySQL暫時停止回答新請求之前的短時間內多少個請求可以被存在堆棧中。
# 如果期望在一個短時間內有很多連接，你需要增加它。也就是說，如果MySQL的連接數據達到max_connections時，新來的請求將會被存在堆棧中，
# 以等待某一連接釋放資源，該堆棧的數量即back_log，如果等待連接的數量超過back_log，將不被授予連接資源。
# 另外，這值（back_log）限於您的操作系統對到來的TCP/IP連接的偵聽隊列的大小。
# 你的操作系統在這個隊列大小上有它自己的限制（可以檢查你的OS文檔找出這個變數的最大值），試圖設定back_log高於你的操作系統的限制將是無效的。
max_connections = 1000
#
MySQL的最大連接數，如果伺服器的並發連接請求量比較大，建議調高此值，以增加並行連接數量，當然這建立在機器能支撐的情況下，因為如果連接數越多，
介於MySQL會為每個連接提供連接緩沖區，就會開銷越多的內存，所以要適當調整該值，不能盲目提高設值。可以過'conn%'通配符查看當前狀態的連接
數量，以定奪該值的大小。
max_connect_errors = 6000
# 對於同一主機，如果有超出該參數值個數的中斷錯誤連接，則該主機將被禁止連接。如需對該主機進行解禁，執行：FLUSH HOST。
open_files_limit = 65535
# MySQL打開的文件描述符限制，默認最小1024;當open_files_limit沒有被配置的時候，比較max_connections*5和ulimit -n的值，哪個大用哪個，
# 當open_file_limit被配置的時候，比較open_files_limit和max_connections*5的值，哪個大用哪個。
table_open_cache = 128
# MySQL每打開一個表，都會讀入一些數據到table_open_cache緩存中，當MySQL在這個緩存中找不到相應信息時，才會去磁碟上讀取。默認值64
# 假定系統有200個並發連接，則需將此參數設置為200*N(N為每個連接所需的文件描述符數目)；
# 當把table_open_cache設置為很大時，如果系統處理不了那麼多文件描述符，那麼就會出現客戶端失效，連接不上
max_allowed_packet = 4M
# 接受的數據包大小；增加該變數的值十分安全，這是因為僅當需要時才會分配額外內存。例如，僅當你發出長查詢或MySQLd必須返回大的結果行時MySQLd才會分配更多內存。
# 該變數之所以取較小默認值是一種預防措施，以捕獲客戶端和伺服器之間的錯誤信息包，並確保不會因偶然使用大的信息包而導致內存溢出。
binlog_cache_size = 1M
# 一個事務，在沒有提交的時候，產生的日誌，記錄到Cache中；等到事務提交需要提交的時候，則把日誌持久化到磁碟。默認binlog_cache_size大小32K
max_heap_table_size = 8M
# 定義了用戶可以創建的內存表(memory table)的大小。這個值用來計算內存表的最大行數值。這個變數支持動態改變
tmp_table_size = 16M
# MySQL的heap（堆積）表緩沖大小。所有聯合在一個DML指令內完成，並且大多數聯合甚至可以不用臨時表即可以完成。
# 大多數臨時表是基於內存的(HEAP)表。具有大的記錄長度的臨時表 (所有列的長度的和)或包含BLOB列的表存儲在硬碟上。
#
如果某個內部heap（堆積）表大小超過tmp_table_size，MySQL可以根據需要自動將內存中的heap表改為基於硬碟的MyISAM表。
還可以通過設置tmp_table_size選項來增加臨時表的大小。也就是說，如果調高該值，MySQL同時將增加heap表的大小，可達到提高聯接查
詢速度的效果
read_buffer_size = 2M
# MySQL讀入緩沖區大小。對表進行順序掃描的請求將分配一個讀入緩沖區，MySQL會為它分配一段內存緩沖區。read_buffer_size變數控制這一緩沖區的大小。
# 如果對表的順序掃描請求非常頻繁，並且你認為頻繁掃描進行得太慢，可以通過增加該變數值以及內存緩沖區大小提高其性能
read_rnd_buffer_size = 8M
# MySQL的隨機讀緩沖區大小。當按任意順序讀取行時(例如，按照排序順序)，將分配一個隨機讀緩存區。進行排序查詢時，
# MySQL會首先掃描一遍該緩沖，以避免磁碟搜索，提高查詢速度，如果需要排序大量數據，可適當調高該值。但MySQL會為每個客戶連接發放該緩沖空間，所以應盡量適當設置該值，以避免內存開銷過大
sort_buffer_size = 8M
# MySQL執行排序使用的緩沖大小。如果想要增加ORDER BY的速度，首先看是否可以讓MySQL使用索引而不是額外的排序階段。
# 如果不能，可以嘗試增加sort_buffer_size變數的大小
join_buffer_size = 8M
# 聯合查詢操作所能使用的緩沖區大小，和sort_buffer_size一樣，該參數對應的分配內存也是每連接獨享
thread_cache_size = 8
# 這個值（默認8）表示可以重新利用保存在緩存中線程的數量，當斷開連接時如果緩存中還有空間，那麼客戶端的線程將被放到緩存中，
# 如果線程重新被請求，那麼請求將從緩存中讀取,如果緩存中是空的或者是新的請求，那麼這個線程將被重新創建,如果有很多新的線程，
# 增加這個值可以改善系統性能.通過比較Connections和Threads_created狀態的變數，可以看到這個變數的作用。(–>表示要調整的值)
# 根據物理內存設置規則如下：
# 1G —> 8
# 2G —> 16
# 3G —> 32
# 大於3G —> 64
query_cache_size = 8M
#MySQL的查詢緩沖大小（從4.0.1開始，MySQL提供了查詢緩沖機制）使用查詢緩沖，MySQL將SELECT語句和查詢結果存放在緩沖區中，
# 今後對於同樣的SELECT語句（區分大小寫），將直接從緩沖區中讀取結果。根據MySQL用戶手冊，使用查詢緩沖最多可以達到238%的效率。
# 通過檢查狀態值'Qcache_%'，可以知道query_cache_size設置是否合理：如果Qcache_lowmem_prunes的值非常大，則表明經常出現緩沖不夠的情況，
# 如果Qcache_hits的值也非常大，則表明查詢緩沖使用非常頻繁，此時需要增加緩沖大小；如果Qcache_hits的值不大，則表明你的查詢重復率很低，
# 這種情況下使用查詢緩沖反而會影響效率，那麼可以考慮不用查詢緩沖。此外，在SELECT語句中加入SQL_NO_CACHE可以明確表示不使用查詢緩沖
query_cache_limit = 2M
#指定單個查詢能夠使用的緩沖區大小，默認1M
key_buffer_size = 4M
#指定用於索引的緩沖區大小，增加它可得到更好處理的索引(對所有讀和多重寫)，到你能負擔得起那樣多。如果你使它太大，
# 系統將開始換頁並且真的變慢了。對於內存在4GB左右的伺服器該參數可設置為384M或512M。通過檢查狀態值Key_read_requests和Key_reads，
# 可以知道key_buffer_size設置是否合理。比例key_reads/key_read_requests應該盡可能的低，
# 至少是1:100，1:1000更好(上述狀態值可以使用SHOW STATUS LIKE 'key_read%'獲得)。注意：該參數值設置的過大反而會是伺服器整體效率降低
ft_min_word_len = 4
# 分詞詞彙最小長度，默認4
transaction_isolation = REPEATABLE-READ
# MySQL支持4種事務隔離級別，他們分別是：
# READ-UNCOMMITTED, READ-COMMITTED, REPEATABLE-READ, SERIALIZABLE.
# 如沒有指定，MySQL默認採用的是REPEATABLE-READ，ORACLE默認的是READ-COMMITTED
log_bin = mysql-bin
binlog_format = mixed
expire_logs_days = 30 #超過30天的binlog刪除
log_error = /data/mysql/mysql-error.log #錯誤日誌路徑
slow_query_log = 1
long_query_time = 1 #慢查詢時間 超過1秒則為慢查詢
slow_query_log_file = /data/mysql/mysql-slow.log
performance_schema = 0
explicit_defaults_for_timestamp
#lower_case_table_names = 1 #不區分大小寫
skip-external-locking #MySQL選項以避免外部鎖定。該選項默認開啟
default-storage-engine = InnoDB #默認存儲引擎
innodb_file_per_table = 1
# InnoDB為獨立表空間模式，每個資料庫的每個表都會生成一個數據空間
# 獨立表空間優點：
# 1．每個表都有自已獨立的表空間。
# 2．每個表的數據和索引都會存在自已的表空間中。
# 3．可以實現單表在不同的資料庫中移動。
# 4．空間可以回收（除drop table操作處，表空不能自已回收）
# 缺點：
# 單表增加過大，如超過100G
# 結論：
# 共享表空間在Insert操作上少有優勢。其它都沒獨立表空間表現好。當啟用獨立表空間時，請合理調整：innodb_open_files
innodb_open_files = 500
# 限制Innodb能打開的表的數據，如果庫里的表特別多的情況，請增加這個。這個值默認是300
innodb_buffer_pool_size = 64M
# InnoDB使用一個緩沖池來保存索引和原始數據, 不像MyISAM.
# 這里你設置越大,你在存取表裡面數據時所需要的磁碟I/O越少.
# 在一個獨立使用的資料庫伺服器上,你可以設置這個變數到伺服器物理內存大小的80%
# 不要設置過大,否則,由於物理內存的競爭可能導致操作系統的換頁顛簸.
# 注意在32位系統上你每個進程可能被限制在 2-3.5G 用戶層面內存限制,
# 所以不要設置的太高.
innodb_write_io_threads = 4
innodb_read_io_threads = 4
# innodb使用後台線程處理數據頁上的讀寫 I/O(輸入輸出)請求,根據你的 CPU 核數來更改,默認是4
# 注:這兩個參數不支持動態改變,需要把該參數加入到my.cnf里，修改完後重啟MySQL服務,允許值的范圍從 1-64
innodb_thread_concurrency = 0
# 默認設置為 0,表示不限制並發數，這里推薦設置為0，更好去發揮CPU多核處理能力，提高並發量
innodb_purge_threads = 1
# InnoDB中的清除操作是一類定期回收無用數據的操作。在之前的幾個版本中，清除操作是主線程的一部分，這意味著運行時它可能會堵塞其它的資料庫操作。
# 從MySQL5.5.X版本開始，該操作運行於獨立的線程中,並支持更多的並發數。用戶可通過設置innodb_purge_threads配置參數來選擇清除操作是否使用單
# 獨線程,默認情況下參數設置為0(不使用單獨線程),設置為 1 時表示使用單獨的清除線程。建議為1
innodb_flush_log_at_trx_commit = 2
# 0：如果innodb_flush_log_at_trx_commit的值為0,log buffer每秒就會被刷寫日誌文件到磁碟，提交事務的時候不做任何操作（執行是由mysql的master thread線程來執行的。
# 主線程中每秒會將重做日誌緩沖寫入磁碟的重做日誌文件(REDO LOG)中。不論事務是否已經提交）默認的日誌文件是ib_logfile0,ib_logfile1
# 1：當設為默認值1的時候，每次提交事務的時候，都會將log buffer刷寫到日誌。
# 2：如果設為2,每次提交事務都會寫日誌，但並不會執行刷的操作。每秒定時會刷到日誌文件。要注意的是，並不能保證100%每秒一定都會刷到磁碟，這要取決於進程的調度。
# 每次事務提交的時候將數據寫入事務日誌，而這里的寫入僅是調用了文件系統的寫入操作，而文件系統是有 緩存的，所以這個寫入並不能保證數據已經寫入到物理磁碟
# 默認值1是為了保證完整的ACID。當然，你可以將這個配置項設為1以外的值來換取更高的性能，但是在系統崩潰的時候，你將會丟失1秒的數據。
# 設為0的話，mysqld進程崩潰的時候，就會丟失最後1秒的事務。設為2,只有在操作系統崩潰或者斷電的時候才會丟失最後1秒的數據。InnoDB在做恢復的時候會忽略這個值。
# 總結
# 設為1當然是最安全的，但性能頁是最差的（相對其他兩個參數而言，但不是不能接受）。如果對數據一致性和完整性要求不高，完全可以設為2，如果只最求性能，例如高並發寫的日誌伺服器，設為0來獲得更高性能
innodb_log_buffer_size = 2M
# 此參數確定些日誌文件所用的內存大小，以M為單位。緩沖區更大能提高性能，但意外的故障將會丟失數據。MySQL開發人員建議設置為1－8M之間
innodb_log_file_size = 32M
# 此參數確定數據日誌文件的大小，更大的設置可以提高性能，但也會增加恢復故障資料庫所需的時間
innodb_log_files_in_group = 3
# 為提高性能，MySQL可以以循環方式將日誌文件寫到多個文件。推薦設置為3
innodb_max_dirty_pages_pct = 90
# innodb主線程刷新緩存池中的數據，使臟數據比例小於90%
innodb_lock_wait_timeout = 120
# InnoDB事務在被回滾之前可以等待一個鎖定的超時秒數。InnoDB在它自己的鎖定表中自動檢測事務死鎖並且回滾事務。InnoDB用LOCK TABLES語句注意到鎖定設置。默認值是50秒
bulk_insert_buffer_size = 8M
# 批量插入緩存大小， 這個參數是針對MyISAM存儲引擎來說的。適用於在一次性插入100-1000+條記錄時， 提高效率。默認值是8M。可以針對數據量的大小，翻倍增加。
myisam_sort_buffer_size = 8M
# MyISAM設置恢復表之時使用的緩沖區的尺寸，當在REPAIR TABLE或用CREATE INDEX創建索引或ALTER TABLE過程中排序 MyISAM索引分配的緩沖區
myisam_max_sort_file_size = 10G
# 如果臨時文件會變得超過索引，不要使用快速排序索引方法來創建一個索引。注釋：這個參數以位元組的形式給出
myisam_repair_threads = 1
# 如果該值大於1，在Repair by sorting過程中並行創建MyISAM表索引(每個索引在自己的線程內)
interactive_timeout = 28800
# 伺服器關閉互動式連接前等待活動的秒數。互動式客戶端定義為在mysql_real_connect()中使用CLIENT_INTERACTIVE選項的客戶端。默認值：28800秒（8小時）
wait_timeout = 28800
# 伺服器關閉非交互連接之前等待活動的秒數。在線程啟動時，根據全局wait_timeout值或全局interactive_timeout值初始化會話wait_timeout值，
# 取決於客戶端類型(由mysql_real_connect()的連接選項CLIENT_INTERACTIVE定義)。參數默認值：28800秒（8小時）
# MySQL伺服器所支持的最大連接數是有上限的，因為每個連接的建立都會消耗內存，因此我們希望客戶端在連接到MySQL Server處理完相應的操作後，
# 應該斷開連接並釋放佔用的內存。如果你的MySQL Server有大量的閑置連接，他們不僅會白白消耗內存，而且如果連接一直在累加而不斷開，
# 最終肯定會達到MySQL Server的連接上限數，這會報'too many connections'的錯誤。對於wait_timeout的值設定，應該根據系統的運行情況來判斷。
# 在系統運行一段時間後，可以通過show processlist命令查看當前系統的連接狀態，如果發現有大量的sleep狀態的連接進程，則說明該參數設置的過大，
# 可以進行適當的調整小些。要同時設置interactive_timeout和wait_timeout才會生效。
[mysqlmp]
quick
max_allowed_packet = 16M #伺服器發送和接受的最大包長度
[myisamchk]
key_buffer_size = 8M
sort_buffer_size = 8M
read_buffer = 4M
write_buffer = 4M
❺ mysql innodb 怎麼緩存數據的原理
1、query_cache_size=0 已經禁用了查詢緩存，但表數據可能緩存了，flush tables試試，不過操作系統還有一個硬碟緩存，想跟第一次查詢之前的狀態一致恐怕只能每次重啟 2、group by與order by不會用索引的，索引最大的用處就是減小磁碟IO，
❻ innodb存儲引擎有多少頁
1、區別：
1） MyISAM管理非事務表。提供高速存儲和檢索，以及全文搜索能力。MyISAM在所有MySQL配置里被支持，是默認的存儲引擎，除非配置MySQL默認使用另外一個引擎。
2）MEMORY存儲引擎提供「內存中」表。MERGE存儲引擎允許集合將被處理同樣的MyISAM表作為一個單獨的表。就像MyISAM一樣，MEMORY和MERGE存儲引擎處理非事務表，這兩個引擎也都被默認包含在MySQL中。
注釋：MEMORY存儲引擎正式地被確定為HEAP引擎。
3）InnoDB和存儲引擎提供事務安全表，默認被包括在所 有MySQL 5.1二進制分發版里，可以按照喜好通過配置MySQL來允許或禁止任一引擎。
2、功能點簡介
1）MyISAM存儲引擎
MyISAM存儲引擎不支持事務，不支持行級鎖，只支持並發插入的表鎖，主要用於高負載的select。
myisam類型的表支持三種不同的存儲結構：靜態型、動態型、壓縮型。
（1）靜態型：就是定義的表列的大小是固定（即不含有：xblob、xtext、varchar等長度可變的數據類型），這樣mysql就會自動使用靜態myisam格式。
使用靜態格式的表的性能比較高，因為在維護和訪問的時候以預定格式存儲數據時需要的開銷很低。但是這高性能是有空間換來的，因為在定義的時候是固定的，所以不管列中的值有多大，都會以最大值為准，占據了整個空間。
（2）動態型：如果列（即使只有一列）定義為動態的（xblob, xtext, varchar等數據類型），這時myisam就自動使用動態型，雖然動態型的表佔用了比靜態型表較少的空間，但帶來了性能的降低，因為如果某個欄位的內容發生改變則其位置很可能需要移動，這樣就會導致碎片的產生。隨著數據變化的怎多，碎片就會增加，數據訪問性能就會相應的降低。
（3）壓縮型：如果在這個資料庫中創建的是在整個生命周期內只讀的表，則這種情況就是用myisam的壓縮型表來減少空間的佔用。
2）MEMORY存儲引擎：
（1）memory存儲引擎相比前面的一些存儲引擎，有點不一樣，其使用存儲在內從中的數據來創建表，而且所有的數據也都存儲在內存中。
（2）每個基於memory存儲引擎的表實際對應一個磁碟文件，該文件的文件名和表名是相同的，類型為.frm。該文件只存儲表的結構，而其數據文件，都是存儲在內存中，這樣有利於對數據的快速處理，提高整個表的處理能力。
（3）memory存儲引擎默認使用哈希（HASH）索引，其速度比使用B-+Tree型要快，如果讀者希望使用B樹型，則在創建的時候可以引用。
（4）memory存儲引擎文件數據都存儲在內存中，如果mysqld進程發生異常，重啟或關閉機器這些數據都會消失。所以memory存儲引擎中的表的生命周期很短，一般只使用一次。
3）innoDB存儲引擎：
（1） innodb存儲引擎該mysql表提供了事務，回滾以及系統崩潰修復能力和多版本迸發控制的事務的安全。
（2）innodb支持自增長列（auto_increment）,自增長列的值不能為空，如果在使用的時候為空的話怎會進行自動存現有的值開始增值，如果有但是比現在的還大，則就保存這個值。
（3）innodb存儲引擎支持外鍵（foreign key） ,外鍵所在的表稱為子表而所依賴的表稱為父表。
（4）innodb存儲引擎最重要的是支持事務，以及事務相關聯功能。
（5）innodb存儲引擎支持mvcc的行級鎖。
❼ Mysql中為什麼使用InnoDB存儲引擎的時候建議關閉回寫緩存
參考答案 少壯不努力，老大徒傷悲。
❽ mysql 如何分配內存
我們仍然使用兩個會話，一個會話 run，用於運行主 SQL；另一個會話 ps，用於進行 performance_schema 的觀察：
主會話線程號為 29，
可以看到寫入的線程是 page_clean_thread，是一個刷臟操作，這樣就能理解數據為什麼是慢慢寫入的。
也可以看到每個 IO 操作的大小是 16K，也就是刷數據頁的操作。
結論：
我們可以看到，
1. MySQL 會基本遵守 max_heap_table_size 的設定，在內存不夠用時，直接將表轉到磁碟上存儲。
2. 由於引擎不同（內存中表引擎為 heap，磁碟中表引擎則跟隨 internal_tmp_disk_storage_engine 的配置），本次實驗寫磁碟的數據量和實驗 05中使用內存的數據量不同。
3. 如果臨時表要使用磁碟，表引擎配置為 InnoDB，那麼即使臨時表在一個時間很短的 SQL 中使用，且使用後即釋放，釋放後也會刷臟頁到磁碟中，消耗部分 IO。
❾ InnoDB自己就有buffer pool，那麼還需要memcached嗎
InnoDB自己的buffer pool是用於讀寫磁碟的，最小單位是頁，InnoDB默認的頁大小是16k。而對於應用層面，面向的是記錄，所以面向記錄的緩存的局部性/效率往往要高於面向頁的緩存。所以memcache還是有必要的，這個可以類比多級cache。
當然，如果InnoDB支持記錄緩存的話，那麼跟memcache的必要性就小很多了。