1、mysql引擎簡介：

MyISAM引擎是mysql關(guān)系數(shù)據(jù)庫管理系統(tǒng)的默認(rèn)存儲引擎(mysql 5.5以前)。這種mysql表存貯結(jié)構(gòu)從的舊的ISAM代碼擴(kuò)展出許多有用的功能。在新版本的mysql中，InnoDB引擎由于其對事務(wù)參照的完整性，以及更高的并發(fā)性等優(yōu)點(diǎn)開始逐步的取代MYISAM引擎。

#ENGINE=MyISAM myisam表都對應(yīng)于硬盤上的三個文件。這三個文件有一樣的文件名，但是有不同的擴(kuò)展名指示其類型用途：.frm文件保存表的定義，這個文件并不是myISAM引擎的一部分，而是服務(wù)器的一部分；.MYD保存表的數(shù)據(jù)；MYI是表的索引文件。

MYsql5.5 支持事務(wù)的引擎：innodb、ndb(mysql集群引擎)

2、數(shù)據(jù)庫事務(wù)介紹：

事物務(wù)就是邏輯上的一組sql操作，組成這組操作的各個sql語句，要么全部成功，要么全部失敗；

范例：hiboy給higirl轉(zhuǎn)賬五元

流程：a，從hiboy銀行卡取出五元，計(jì)算式money-5.

b，把上面五元錢打入higirl的賬戶上，higirl收到5塊，money+5.

上述轉(zhuǎn)賬的過程，對應(yīng)的sql語句為

update account set money=money-5 where name="hiboy";

update account set money=money-5 where name="higirl";

上述的兩條sql操作，在事務(wù)中的操作就是要么都執(zhí)行，要么都不執(zhí)行，這就是事物的原子性。

3、事務(wù)的四大特點(diǎn)：

1、原子性(Atomicity)

事物是一個不可分割的單位，事務(wù)中所有sql等操作要么都發(fā)生，要么都不發(fā)生。

2、一致性(Consistency)

事務(wù)發(fā)生前和發(fā)生后，，數(shù)據(jù)的完整行必須保持一直。

3、隔離性(Isolation)

當(dāng)前并發(fā)訪問數(shù)據(jù)時，一個正在執(zhí)行的事務(wù)在執(zhí)行完畢前，對于其它的會話是不可見的，多個并發(fā)事務(wù)之間的數(shù)據(jù)是相互隔離的。

#innodb 事務(wù)參數(shù) --single-transaction

4、持久性(Durability)

一個事務(wù)一旦被提交，她對數(shù)據(jù)庫中的數(shù)據(jù)改變是永久性的。如果出現(xiàn)錯誤，事務(wù)也不允許撤銷，只能通過“補(bǔ)償性事務(wù)”。

4、事務(wù)的開啟：

數(shù)據(jù)庫默認(rèn)事務(wù)是自動提交的，也就是發(fā)一條sql它就執(zhí)行一條。如果想多條sql放在一個事務(wù)中執(zhí)行，則需要使用事務(wù)進(jìn)行處理。當(dāng)我們開啟一個事務(wù)，并且沒有提交，mysql會自動回滾事務(wù)?；蛘呤褂胷ollbak命令手動回滾事務(wù)。

數(shù)據(jù)庫開啟事務(wù)命令：

set autocommit= OFF禁止自動提交

set autocommit=ON 開啟自動提交

SET GLOBAL autocommit= ON; 全局設(shè)置

rollback 回滾事務(wù)

commit?? 提交事務(wù)

查看： show variables like '%auto%';

+-----------------------------+-------+

| Variable_name?????????????? | Value |

+-----------------------------+-------+

| auto_increment_increment??? | 1???? |

| auto_increment_offset?????? | 1???? |

| autocommit???????????????? | OFF?? |

| automatic_sp_privileges???? | ON??? |

| innodb_autoextend_increment | 8???? |

| innodb_autoinc_lock_mode??? | 1???? |

| sql_auto_is_null??????????? | OFF?? |

+-----------------------------+-------+

5、MyISAM引擎的特點(diǎn)：

1、不支持事務(wù)：(事務(wù)就是邏輯上sql語句)

舉例：銀行轉(zhuǎn)賬，保持?jǐn)?shù)據(jù)的一致性；

2、表級鎖定，數(shù)據(jù)跟新時鎖定整個表，其鎖定機(jī)制是表級鎖定，雖然讓鎖定的實(shí)現(xiàn)成本很小但同時大大降低了并發(fā)的性能；

3、讀寫互相堵塞，不僅會在寫入時候阻塞讀取，但讀本身并會阻塞另外的讀；

4、只會緩存索引，myisam 可以通過key_buffer_size緩存索引，以大大提高性能減少磁盤IO，但是這個緩存只緩存索引，而不會緩存數(shù)據(jù)。

#grep key_buffer my.cnf ????????????key_buffer_size = 16M

5、讀取速度快。占用資源相對少；

6、不支持外鍵約束，但支持全文索引；

6、MyISAM生產(chǎn)場景：

1、讀數(shù)據(jù)比較多的應(yīng)用，讀寫都頻繁的場景不適合，讀多或者寫多的都適合；

2、不需要事物支持的業(yè)務(wù)(例如銀行)

3、讀寫并發(fā)相對較低的業(yè)務(wù)(純讀純寫高并發(fā)的也可以)

4、以讀為主的業(yè)務(wù)，例如：www blog 圖片信息數(shù)據(jù)庫，用戶數(shù)據(jù)庫。商品庫等業(yè)務(wù)

5、對數(shù)據(jù)一致性要求不高的業(yè)務(wù)

6、硬件資源比價差的機(jī)器用myISAM

7、使用讀寫分離的mysql從庫可以使用MYISAM

#小結(jié)：單一數(shù)據(jù)庫的操作都可以適合使用MYISAM ,所謂單一就是盡量純讀或純寫。

7、MyISAM調(diào)優(yōu)精要：

1、設(shè)置合適的索引

2、調(diào)整讀寫優(yōu)先級，根據(jù)實(shí)際需要確保重要操作更優(yōu)先執(zhí)行

3、啟用延遲插入改善大批量寫性能(降低寫入頻率，盡可能數(shù)據(jù)一次性寫入)

4、盡量順序操作讓insert數(shù)據(jù)都能寫入到尾部，減少堵塞；

5、分散大的時間長的操作，降低單個操作的阻塞時間。

6、降低并發(fā)數(shù)(減少對mysql訪問)，某些高并發(fā)場景通過應(yīng)用進(jìn)行排隊(duì)隊(duì)列機(jī)制；

7、(對相對靜態(tài)，更改不頻繁)充分利用query cache 或redis。 memcache緩存服務(wù)

#?grep query my.cnf

query_cache_size = 2M? #緩存的空間大小? query_cache_limit = 1M #緩存的限制? query_cache_min_res_unit = 2k? #最小緩存對象

8、myISAM 的count只有在全表掃描的時候特別高效，帶有其它條件的ccoun都需要進(jìn)行實(shí)際的數(shù)據(jù)訪問；

9、可以把主從同步的主庫使用innodb，從庫使用MYisam引擎； (不推薦)

8、InnoDB：引擎的特點(diǎn)特性：

#InnoDB使用頻率:99%

#對應(yīng)的數(shù)據(jù)庫的文件:innodb_data_file_path= ibddata1:128:autoextend (初始化的時候自動生產(chǎn)這么大，自動擴(kuò)展)

1、支持事務(wù)；支持4個事務(wù)隔離級別，支持多版本讀。

2、行級鎖定(跟新時一般是鎖定當(dāng)前行)：通過索引實(shí)現(xiàn)，全表掃描仍然后是鎖表。注意間隙鎖的影響；

3、讀寫阻塞與事物隔離級別相關(guān)；

4、具有高效的緩存特性：能緩存索引，也能緩存數(shù)據(jù)；

4、支持分區(qū)，表空間，類似oracle數(shù)據(jù)庫；

5、支持外鍵約束，5.5以前不支持全文索引，以后的支持了。

7、和MYISAM 引擎相比，iNNODB對硬件資源要求比較高

### ：? innodb_fuffer_pool_size = 32M 重量級參數(shù) ,不要太大 ， 1/8 差不

9、InnoDB 生產(chǎn)場景：

1、需要事務(wù)支持的業(yè)務(wù)(具有較好的事務(wù)特性)；

2、行級鎖定對高并發(fā)有很好的適應(yīng)能力，但需要確保查詢是通過索引完成

3、數(shù)據(jù)讀與寫都較為頻繁的場景

4、數(shù)據(jù)一致性都要求較高的業(yè)務(wù)

5、設(shè)備硬件內(nèi)存較大，可以利用innodb 較好的緩存能力來提高內(nèi)存利用率，盡可能減少磁盤IO

# grep -i innodb my.cnf

6、可以獨(dú)立表空間；

#獨(dú)立表空間對應(yīng)物理數(shù)據(jù)文件：

參數(shù)： my.cnf? 【mysqld】

innodb_file_per_table

innodb_data_home_dir =/data/xxx

文件： ll /data/3306/data/ibdatal

10、InnoDB 引擎調(diào)優(yōu)精要：

1、主鍵盡可能小，避免給Secondary index帶來過大的空間負(fù)擔(dān)；

2、避免全表掃描，因?yàn)闀褂帽礞i；

3、盡可能緩存所有的索引和數(shù)據(jù)，提高相應(yīng)速度，減少磁盤IO消耗；

4、在大批量小插入的時候，盡量自己控制事務(wù)而不要使用actocimmit自動提交。有開關(guān)可以控制提交方式；

5、合理設(shè)置innodb_flush_log_at_trx_commit參數(shù)值，不要過度追求安全性；

#如果innodb_lush_log_at_commit的值為0，log buffer每秒就會被刷寫日志文件到磁盤，提交數(shù)據(jù)的時候不做任何操作。

6、避免主鍵跟新，因?yàn)檫@會帶來大量的數(shù)據(jù)移動；

11、如何確定MYsql服務(wù)器有哪些引擎可用：

查看：? show engines ;

mysql> show engines ;

+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+

| Engine???????????? | Support | Comment??????????????????????????????????????????????????????? | Transactions | XA?? | Savepoints |

+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+

| MRG_MYISAM???????? | YES???? | Collection of identical MyISAM tables????????????????????????? | NO?????????? | NO?? | NO???????? |

| CSV??????????????? | YES???? | CSV storage engine???????????????????????????????????????????? | NO?????????? | NO?? | NO???????? |

| MEMORY???????????? | YES???? | Hash based, stored in memory, useful for temporary tables????? | NO?????????? | NO?? | NO???????? |

| BLACKHOLE????????? | YES???? | /dev/null storage engine (anything you write to it disappears) | NO?????????? | NO?? | NO???????? |

| MyISAM???????????? | YES???? | MyISAM storage engine????????????????????????????????????????? | NO?????????? | NO?? | NO???????? |

| FEDERATED????????? | NO????? | Federated MySQL storage engine???????????????????????????????? | NULL???????? | NULL | NULL?????? |

| ARCHIVE??????????? | YES???? | Archive storage engine???????????????????????????????????????? | NO?????????? | NO?? | NO???????? |

|InnoDB??????????? | DEFAULT | Supports transactions, row-level locking, and foreign keys???? | YES????????? | YES? | YES??????? |

| PERFORMANCE_SCHEMA | YES???? | Performance Schema???????????????????????????????????????????? | NO?????????? | NO?? | NO???????? |

+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+

9 rows in set (0.06 sec)

12、Innodb引擎的重要參數(shù):

default_table_type = InnoDB

innodb_additional_mem_pool_size = 4M

innodb_buffer_pool_size = 2048M

innodb_data_file_path = ibdata1:128M:autoextend

innodb_file_io_threads = 4

innodb_thread_concurrency = 8

innodb_flush_log_at_trx_commit = 2

innodb_log_buffer_size = 16M

innodb_log_file_size = 128M

innodb_log_files_in_group = 3

innodb_max_dirty_pages_pct = 90

innodb_lock_wait_timeout = 120

innodb_file_per_table = 0

13、myISAM 引擎重要的參數(shù)： key_buffer_size = 2048M

14、如何修改MySQL引擎：

法1：

創(chuàng)建后引擎的更改，5.0以上

ALTER TABLE TEST ENGINE = INNODB;

ALTER TABLE TEST ENGINE = MyISAM;

查看：

法2：

使用sed對備份內(nèi)容進(jìn)行引擎替換；

mysqldump > back。sql

nohup sed -e 's/MyISAM/InnoDB' back.sql > kong.back.sql &

mysql < kong.back.sql

#不適用數(shù)據(jù)庫過大；

法3:

使用? mysql_convert_table_forma 命令修改

查看：`which mysql_convert_table_format`? --help

范例：mysql_convert_table_format --user=root --password=123456 --socket=/data/3306/mysql.sock --engine=MyISAM kong t2

總結(jié)

以上是生活随笔為你收集整理的mysql数据库引擎博客_mysql 数据库引擎常识全集的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯，歡迎將生活随笔推薦給好友。