一、存儲(chǔ)引擎

? 1、InnoDB

? ? ⑴InnoDB是基于聚簇索引建立的，基于主鍵索引查詢時(shí)，性能較好；它的輔助索引中必須包含主鍵列；因此，若表上的索引較多，為節(jié)約空間，主鍵應(yīng)盡可能小

? ? ⑵InnoDB支持自適應(yīng)hash索引、事務(wù)、行級(jí)鎖、熱備份，采用MVCC支持高并發(fā)；不支持全文索引

? ? ⑶表存儲(chǔ)格式：

? ? ?①將所有innodb表的數(shù)據(jù)放置同一個(gè)表空間中；

? ? ? ? ?表結(jié)構(gòu)定義：tb_name.frm （數(shù)據(jù)庫(kù)目錄下）

? ? ? ? ?數(shù)據(jù)和索引：統(tǒng)一的表空間文件中，默認(rèn)路徑是數(shù)據(jù)目錄下ibdata#

? ? ? ?innodb_data_file_path：表空間文件的文件名稱及特性

? ? ? ? ?可使用相對(duì)(相對(duì)于innodb_data_home_dir而言)或絕對(duì)路徑，且可定義多個(gè)文件；

? ? ? ? ?例：innodb_data_file_path = ibdata1:20G;ibdata2:10G;ibdata3:1G:autoextend

? ? ? ?innodb_data_home_dir：表空間文件的存儲(chǔ)位置，省略時(shí)表示使用數(shù)據(jù)目錄(datadir變量定義的位置)；

? ? ?②每張表使用單獨(dú)表空間；MariaDB中已默認(rèn)啟用此存儲(chǔ)格式 ? ?

? ? ? ? ?表結(jié)構(gòu)定義：tb_name.frm

? ? ? ? ?數(shù)據(jù)和索引：tb_name.ibd

? ? ? ?SET {GLOBAL|SESSION} innodb_file_per_table = 'on';

? ? ? ?優(yōu)點(diǎn)：遷移或備份數(shù)據(jù)更精細(xì)靈活

? ? ? ?缺點(diǎn)：DROP TABLE操作的性能較差

? ? ⑷InnoDB緩沖池：buffer pool，由InnoDB維護(hù)的內(nèi)存空間，用于緩存索引及數(shù)據(jù)；緩沖池如果太大，預(yù)熱會(huì)比較慢。

? ? ? ?innodb_buffer_pool_size

? ? ⑸查看InnoDB存儲(chǔ)引擎的狀態(tài)：SHOW ENGINE INNODB STATUS;

? 2、MyISAM

? ? ⑴MySQL 5.5.5之前的默認(rèn)引擎

? ? ?支持全文索引、壓縮、空間函數(shù)；

? ? ?不支持外鍵約束

? ? ?不支持事務(wù)、行級(jí)鎖、熱備份；

? ? ?讀寫互相阻塞

? ? ?崩潰后無(wú)法安全恢復(fù)；

? ? ?支持延遲更新索引鍵(delay_key_write)：每次修改表后，修改的索引數(shù)據(jù)不會(huì)立即寫入磁盤，而是寫入內(nèi)存的鍵緩沖區(qū)，只有當(dāng)清理鍵緩沖區(qū)或關(guān)閉表時(shí)才會(huì)寫入磁盤，這樣提高了寫性能，但數(shù)據(jù)庫(kù)崩潰時(shí)，易造成索引損壞。

? ? ?MyISAM可以通過(guò) key_buffer_size 緩存索引鍵，但此緩存只會(huì)緩存索引，不會(huì)緩存數(shù)據(jù)

? ? ?讀取數(shù)據(jù)快，占用資源相對(duì)少

? ? ⑵表存儲(chǔ)格式：每張表都有三個(gè)文件（位于數(shù)據(jù)庫(kù)目錄下）

? ? ? ?tb_name.frm：表格式

? ? ? ?tb_name.MYD：數(shù)據(jù)

? ? ? ?tb_name.MYI：索引

? 3、其它存儲(chǔ)引擎

? ? ?Memory：早期叫HEAP表，將數(shù)據(jù)放在內(nèi)存中，因此訪問(wèn)速度快，但無(wú)法持久存儲(chǔ)數(shù)據(jù)，顯式支持hash索引。

? ? ?CSV：將數(shù)據(jù)存儲(chǔ)為文本文件，字段以逗號(hào)分隔；不支持索引，常用于數(shù)據(jù)交換的場(chǎng)景。

? ? ?Merge：MyISAM的變種，將多個(gè)MyISAM表合并表示為一個(gè)虛擬表；

? ? ?Federated：訪問(wèn)其它MySQL服務(wù)上數(shù)據(jù)的代理；MariaDB上用的是FederatedX

? ? ?Blackhole：沒(méi)有任何存儲(chǔ)機(jī)制，所以會(huì)丟棄所有的插入的數(shù)據(jù)；

? ? ?NDB：Cluster：mysql集群的存儲(chǔ)引擎

? ? ?第三方存儲(chǔ)引擎：

? ? ? ?OLTP類：

? ? ? ? ?XtraDB：InnoDB的改進(jìn)版

? ? ? ? ?PBXT：支持ACID和MVCC

? ? ? ? ?TokuDB：支持使用分形樹的索引結(jié)構(gòu)，適用存儲(chǔ)大數(shù)據(jù)

? ? ? ?面向列的存儲(chǔ)的引擎：按列為單位進(jìn)行存儲(chǔ)，適合壓縮等，適用于存儲(chǔ)大數(shù)據(jù)

? ? ? ? ?Infobright, InfiniDB, LucidDB

? ? ? ?社區(qū)引擎：

? ? ? ? ?Aria：MyISAM的改進(jìn)版，支持崩潰后安全恢復(fù)

? ? ? ? ?OQGraph：支持圖操作

? ? ? ? ?SphinxSE：為sphinx全文搜索引擎提供了SQL接口；

? ? ? ? ?Spider：可以將數(shù)據(jù)切分成不同的分區(qū)，較透明實(shí)現(xiàn)分片功能；

? 4、查看默認(rèn)存儲(chǔ)引擎：

? ? ? show global variables like '%storage_engine';

? ? ? mysql 5.5之后默認(rèn)存儲(chǔ)引擎為innodb

? 5、存儲(chǔ)引擎的選擇

? ? ? 選擇標(biāo)準(zhǔn)：是否支持事務(wù)，熱備份，崩潰后恢復(fù)等

? ? ? 數(shù)據(jù)倉(cāng)庫(kù)建議使用MyISAM或Aria，在線事務(wù)處理建議使用InnoDB

MariaDB?[testdb]>?show?engines; +--------------------+---------+----------------------------------------------------------------------------+--------------+------+------------+ |?Engine?????????????|?Support?|?Comment????????????????????????????????????????????????????????????????????|?Transactions?|?XA???|?Savepoints?| +--------------------+---------+----------------------------------------------------------------------------+--------------+------+------------+ |?PERFORMANCE_SCHEMA?|?YES?????|?Performance?Schema?????????????????????????????????????????????????????????|?NO???????????|?NO???|?NO?????????| |?CSV????????????????|?YES?????|?CSV?storage?engine?????????????????????????????????????????????????????????|?NO???????????|?NO???|?NO?????????| |?MRG_MyISAM?????????|?YES?????|?Collection?of?identical?MyISAM?tables??????????????????????????????????????|?NO???????????|?NO???|?NO?????????| |?BLACKHOLE??????????|?YES?????|?/dev/null?storage?engine?(anything?you?write?to?it?disappears)?????????????|?NO???????????|?NO???|?NO?????????| |?MEMORY?????????????|?YES?????|?Hash?based,?stored?in?memory,?useful?for?temporary?tables??????????????????|?NO???????????|?NO???|?NO?????????| |?Aria???????????????|?YES?????|?Crash-safe?tables?with?MyISAM?heritage?????????????????????????????????????|?NO???????????|?NO???|?NO?????????| |?ARCHIVE????????????|?YES?????|?Archive?storage?engine?????????????????????????????????????????????????????|?NO???????????|?NO???|?NO?????????| |?MyISAM?????????????|?YES?????|?MyISAM?storage?engine??????????????????????????????????????????????????????|?NO???????????|?NO???|?NO?????????| |?InnoDB?????????????|?DEFAULT?|?Percona-XtraDB,?Supports?transactions,?row-level?locking,?and?foreign?keys?|?YES??????????|?YES??|?YES????????| +--------------------+---------+----------------------------------------------------------------------------+--------------+------+------------+ 9?rows?in?set?(0.04?sec)MariaDB?[(none)]>?show?global?variables?like?'%storage_engine'; +------------------------+--------+ |?Variable_name??????????|?Value??| +------------------------+--------+ |?default_storage_engine?|?InnoDB?| |?storage_engine?????????|?InnoDB?| +------------------------+--------+ 2?rows?in?set?(0.00?sec)MariaDB?[(none)]>?select?@@global.innodb_file_per_table; +--------------------------------+ |?@@global.innodb_file_per_table?| +--------------------------------+ |??????????????????????????????1?| +--------------------------------+ 1?row?in?set?(0.00?sec)MariaDB?[(none)]>?\!?ls?/mydata/data aria_log.00000001??hellodb??ib_logfile0??multi-master.info??mysql-bin.000001??mysql-bin.000003 mysql-bin.000005??node1.err??performance_schema??testdb aria_log_control???ibdata1??ib_logfile1??mysql ????mysql-bin.000002??mysql-bin.000004 mysql-bin.index???node1.pid??test MariaDB?[(none)]>?\!?ls?/mydata/data/testdb db.opt students.frm??students.ibd??wuxia.frm??wuxia.ibd

二、查詢緩存 ?

? ⑴查詢的執(zhí)行流程：

? ? SELECT→[QUERY CACHE(查詢緩存)→]PARSER(解析器)→OPTIMIZER(優(yōu)化器)→EXECUTING ENGINE(執(zhí)行引擎)→STORAGE ENGINE

? ? 開啟MySQL查詢緩存功能后，查詢緩存會(huì)保存查詢返回的完整結(jié)果，當(dāng)查詢命中該緩存，MySQL會(huì)立刻返回結(jié)果，而跳過(guò)分析、優(yōu)化和執(zhí)行階段。

??⑵緩存的內(nèi)容是key-value格式

? ? ? key：查詢語(yǔ)句的hash碼

? ? ? value：查詢語(yǔ)句的執(zhí)行結(jié)果

??⑶查詢緩存的優(yōu)缺點(diǎn)：

? ? ? 優(yōu)點(diǎn)：當(dāng)查詢命中緩存時(shí)，就能直接返回結(jié)果，跳過(guò)了后續(xù)一系列繁瑣的過(guò)程，因此速度大大提升

? ? ? 缺點(diǎn)：因?yàn)橐彺嬷械逆I比對(duì)，因此，當(dāng)查詢未命中緩存時(shí)，實(shí)際上是給整個(gè)查詢過(guò)程增加了一項(xiàng)開銷；此外，當(dāng)并發(fā)量較大時(shí)，緩存成了資源爭(zhēng)用點(diǎn)，有可能成為性能瓶頸。

??⑷什么樣的語(yǔ)句不會(huì)緩存？

? ? ? 查詢語(yǔ)句中有不確定數(shù)據(jù)時(shí)不會(huì)緩存,比如current_time()；

? ? ? 一般來(lái)說(shuō)，如果查詢中包含用戶自定義的函數(shù)、存儲(chǔ)函數(shù)、用戶變量、臨時(shí)表、mysql庫(kù)中表、或者任何包含權(quán)限信息表，都不會(huì)緩存；

??⑸緩存什么場(chǎng)景下會(huì)比較有效？

? ? ? 對(duì)于需要牽扯大量資源的查詢非常適合啟用緩存；

? ? ? 不適宜數(shù)據(jù)更新頻繁的場(chǎng)景，因?yàn)槟菢泳彺媸Ш芸?/p>

??⑹與緩存功能相關(guān)的服務(wù)器變量：

? ? ? ? SHOW GLOBAL VARIABLES LIKE 'query_cache%';

? ? ? query_cache_limit: MySQL能夠緩存的最大查詢結(jié)果；如果某查詢的結(jié)果大于此值，則不會(huì)被緩存；

? ? ? query_cache_min_res_unit: 查詢緩存中分配內(nèi)存的最小單位；

? ? ? ? 計(jì)算公式：(query_cache_size-Qcache_free_memory)/Qcache_queries_in_cache，即(總空間-剩余空間)/查詢的個(gè)數(shù)

? ? ? query_cache_size: 查詢緩存的總體可用空間；其必須為1024的倍數(shù)，0表示關(guān)閉緩存

? ? ? query_cache_type: ON, OFF, DEMAND

? ? ? ? DEMAND：按需進(jìn)行緩存，意思是只有明確寫明要緩存的SELECT語(yǔ)句的結(jié)果才會(huì)進(jìn)行緩存； SQL_CACHE | SQL_NO_CACHE

? ? ? query_cache_wlock_invalidate：當(dāng)其它會(huì)話鎖定此次查詢的資源時(shí)，是否不能再?gòu)木彺嬷蟹祷財(cái)?shù)據(jù)；

??⑺與緩存相關(guān)的狀態(tài)變量：

? ? ? ?SHOW GLOBAL STATUS LIKE 'Qcache%';

? ? ? Qcache_free_blocks

? ? ? Qcache_free_memory

? ? ? Qcache_hits: 緩存命中的次數(shù)

? ? ? Qcache_inserts: 插入的緩存的個(gè)數(shù)

? ? ? Qcache_lowmem_prunes: 由于可用緩存空間過(guò)低導(dǎo)致清理緩存的次數(shù)

? ? ? Qcache_not_cached

? ? ? Qcache_queries_in_cache: 仍留在緩存空間中的緩存的個(gè)數(shù)

? ? ? Qcache_total_blocks

? ⑻衡量緩存的有效性：命中率, hit/(hit+miss)

? ? ?常以 Qcache_hits/Com_select 的值作為參考

? ? ?另外，也可參考 Qcache_hits/Qcache_inserts。如果此比值大于3:1, 說(shuō)明緩存也是有效的；如果高于10:1，相當(dāng)理想；

? ⑼緩存優(yōu)化的思路：

? ?①批量寫入比單次寫入對(duì)緩存的影響要小得多；

? ?②緩存空間不宜過(guò)大，大量緩存的同時(shí)失效會(huì)導(dǎo)致MySQL假死；

? ?③必要時(shí)，使用SQL_CACHE或SQL_NO_CACHE手動(dòng)控制緩存；

? ?④對(duì)寫密集型的應(yīng)用場(chǎng)景，禁用緩存反而能提高性能；

? ⑽碎片整理：FLUSH QUERY CACHE;

? ⑾清空緩存：RESET QUERY CACHE;

MariaDB?[(none)]>?show?global?variables?like?'query_cache%'; +------------------------------+----------+ |?Variable_name????????????????|?Value????| +------------------------------+----------+ |?query_cache_limit????????????|?1048576??| |?query_cache_min_res_unit?????|?4096?????| |?query_cache_size?????????????|?16777216?| |?query_cache_strip_comments???|?OFF??????| |?query_cache_type?????????????|?ON???????| |?query_cache_wlock_invalidate?|?OFF??????| +------------------------------+----------+ 6?rows?in?set?(0.00?sec)MariaDB?[testdb]>?show?global?status?like?'Qcache%'; +-------------------------+----------+ |?Variable_name???????????|?Value????| +-------------------------+----------+ |?Qcache_free_blocks??????|?1????????| |?Qcache_free_memory??????|?16757096?| |?Qcache_hits?????????????|?8????????|???#命中8次 |?Qcache_inserts??????????|?41???????| |?Qcache_lowmem_prunes????|?0????????| |?Qcache_not_cached???????|?65???????| |?Qcache_queries_in_cache?|?2????????|???#緩存中仍有兩個(gè)條目 |?Qcache_total_blocks?????|?6????????| +-------------------------+----------+ 8?rows?in?set?(0.00?sec)MariaDB?[testdb]>?reset?query?cache;???#清空查詢緩存 Query?OK,?0?rows?affected?(0.00?sec)MariaDB?[testdb]>?show?global?status?like?'Qcache%'; +-------------------------+----------+ |?Variable_name???????????|?Value????| +-------------------------+----------+ |?Qcache_free_blocks??????|?1????????| |?Qcache_free_memory??????|?16759656?| |?Qcache_hits?????????????|?8????????| |?Qcache_inserts??????????|?41???????| |?Qcache_lowmem_prunes????|?0????????| |?Qcache_not_cached???????|?65???????| |?Qcache_queries_in_cache?|?0????????|???#緩存中已沒(méi)有條目 |?Qcache_total_blocks?????|?1????????| +-------------------------+----------+ 8?rows?in?set?(0.00?sec)

轉(zhuǎn)載于:https://blog.51cto.com/9124573/1750542

總結(jié)

以上是生活随笔為你收集整理的MySQL学习笔记之五：存储引擎和查询缓存的全部?jī)?nèi)容，希望文章能夠幫你解決所遇到的問(wèn)題。

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