MySql架構圖

MySql文件類型

參數文件，啟動時候需要的參數

日志文件，二進制文件(bin log)，慢查詢日志，查詢日志，錯誤日志，重做日志(undo log)

socket文件，使用域socket連接時候用的

pid文件，進程的pid文件

表結構文件，存儲表結構的定義

存儲引擎文件，存放最終數據的

參數查詢和修改

查詢的參數在 INFORMATION_SCHEMA庫中的GLOBAL_STATUS中，5.6之后的mysql默認是關閉的

set global show_compatibility_56=on;

可以使用上面命令打開

顯示innodb相關的參數

Mysql代碼??

SHOW?VARIABLES?LIKE?'innodb%'??

有些參數默認是修改會話級別的

Java代碼??

--修改當前會話的參數??

SET?read_buffer_size=524288;??

--查詢當前會話的參數值??

SELECT?@@session.read_buffer_size;??

--查詢這個值的全局值??

SELECT?@@global.read_buffer_size;??

--如果要修改全局值，加上@@global前綴??

SET?@@global.read_buffer_size=65535;??

靜態(tài)變量是只讀的不可以修改

Mysql代碼??

--修改靜態(tài)變量會報錯??

SET?GLOBAL?datadir='sdff';??

Error?Code?:?1238??

Variable?'datadir'?is?a?read?only?variable??

(0?ms?taken)??

MySql常見的日志文件有

1.錯誤日志

2.二進制日志bin log

3.慢查詢日志 slow log

4.查詢日志

查看錯誤日志

Mysql代碼??

SHOW?VARIABLES?LIKE?'log_error';??

慢查詢日志

Mysql代碼??

--慢查詢日志是否開啟??

SHOW?VARIABLES?LIKE?'slow_query_log';??

--慢查詢日志存放位置??

SHOW?VARIABLES?LIKE?'slow_query_log_file';??

--查詢超過多長時間就算是慢查詢??

SHOW?VARIABLES?LIKE?'long_query_time';??

--查詢相關的參數??

SHOW?VARIABLES?LIKE?'%query%';??

--如果查詢沒有使用索引就在慢查詢日志中記錄這條sql??

SHOW?VARIABLES?LIKE?'log_queries_not_using_indexes%'??

--允許每分鐘多少條沒有使用索引的sql被記錄到慢查詢日志文件中??

SHOW?VARIABLES?LIKE?'log_throttle_queries_not_using_indexes%'??

慢查詢命令

mysqldumpslow

通過數據庫表查看慢查詢日志

Mysql代碼??

--查詢日志保存的方式，默認為FILE??

SHOW?VARIABLES?LIKE?'log_output';??

--將查詢日志保存到表中??

SET?GLOBAL?log_output='TABLE';??

--執(zhí)行一個慢查詢??

SELECT?sleep(11);??

--通過表?mysql.slow_log可以查看剛才執(zhí)行的慢查詢??

--修改mysql.slow_log表存儲引起為MyISAM??

SET?GLOBAL?slow_query_log=off;??

ALTER?TABLE?mysql.slow_log?ENGINE=MyISAM;??

查詢日志

所有對主機進行查詢的操作都會記錄，甚至對Access denied的請求都會記錄

二進制日志

二級制日志bin log記錄所有對mysql更改的操作，加入一個update沒有對任何行做修改，也會記錄這條sql

Mysql代碼??

mysql>?SHOW?MASTER?STATUS\G??

***************************?1.?row?***************************??

?????????????File:?mysql2-bin.000010??

?????????Position:?625203964??

?????Binlog_Do_DB:??

?Binlog_Ignore_DB:?mysql,information_schema,sys,performance_schema??

Executed_Gtid_Set:??

1?row?in?set?(0.08?sec)??

binlog相關的參數

Mysql代碼??

SHOW?VARIABLES?LIKE?'binlog%';??

bin log的位置

Mysql代碼??

SHOW?VARIABLES?LIKE?'datadir';??

bin log相關的參數

Mysql代碼??

binlog_cache_size???32768??

binlog_checksum?CRC32??

binlog_direct_non_transactional_updates?OFF??

binlog_error_action?ABORT_SERVER??

binlog_format???MIXED??

binlog_group_commit_sync_delay??0??

binlog_group_commit_sync_no_delay_count?0??

binlog_gtid_simple_recovery?ON??

binlog_max_flush_queue_time?0??

binlog_order_commits????ON??

binlog_row_image????FULL??

binlog_rows_query_log_events????OFF??

binlog的用途

1.數據恢復

2.復制，mysql的主從同步

3.審計，用戶可以通過二進制日志中的信息來進行審計判斷是否有sql注入

MySQL的bin-log日志備份有三種模式，分別是：ROW、Statement、Mixed

一、Row

日志會記錄成每一行數據被修改成的形式，然后再slave端再對相同的數據進行修改，只記錄要修改的數據，只有value，不會有sql多表關聯的情況。

優(yōu)點：在row模式下，bin-log中可以不記錄執(zhí)行的sql語句的上下文相關信息，僅僅需要記錄哪一條記錄被修改了，修改成什么信樣了，所以row的日志內容會非常清楚的記錄下每一行數據修改的細節(jié)，非常容易理解。而且不會出現在某些特定情況下的存儲過程和function，以及trigger的調用和處罰無法被正確復制問題。

缺點：在row模式下，所有執(zhí)行的語句當記錄到日志中的時候，都將以每行記錄的修改來記錄，這樣可能會產生大量的日志內容。

二、Statement

每一條會修改數據的sql都會記錄到master的binlog中，slave在復制的時候sql進程會解析成和原來master端相同的sql再執(zhí)行。

優(yōu)點：在Statement模式下首先就是解決了row模式下的缺點，不需要記錄記錄每一行日志的變化，減少了bin-log日志量，節(jié)省了I/O以及存儲資源，提高性能。因為它們只需要激勵在master上所執(zhí)行的語句的細節(jié)以及執(zhí)行語句時候的上下文信息。

缺點：在Statement模式下，由于它記錄的執(zhí)行語句，所以，為了讓這些語句在slave端也能正確執(zhí)行，那么它還必須記錄每條語句在執(zhí)行的時候的一些相關信息，也就是上下文信息，以保證所有語句在slave端被執(zhí)行的時候能夠得到和在master端執(zhí)行時候的結果。另外，由于MySQL現在發(fā)展較快，很多的新功能不斷的加入，使MySQL的復制遇到了不小的挑戰(zhàn)，自然復制的時候涉及到越復雜的內容，bug也就越容易出現。在Statement中，目前已經發(fā)現不少情況會造成MySQL的復制出現問題，主要是修改數據的時候使用了某些特定的函數或者功能的時候會出現。

三、Mixed

從官方文檔中看到，之前的MySQL一直都只有基于Statement的復制模式，知道5.1.5版本的MySQL才開始支持row模式。從5.0開始，MySQL的復制已經解決了大量老版本中出現的無法正確復制的問題。但是由于存儲過程的出現，給MySQL replication又帶來了更大的挑戰(zhàn)。另外，看到官方文檔說，從5.1.8版本開始，MySQL提供了除Statement和row之外的第三種模式：mixed，實際上就是前兩種模式的結合。在mixed模式下，MySQL會根據執(zhí)行的每一條具體的sql語句來區(qū)分對待記錄的日志形式，也就是在Statement和row之間選擇一種。新版本中的Statement還是和以前一樣，僅僅記錄執(zhí)行的語句。而新版本的MySQL中對row模式也做了優(yōu)化，并不是所有的修改都會以row模式來記錄，比如遇到表結構變更的時候就會以Statement模式來記錄，如果sql語句確實是update或者delete等修改數據的語句，那么還是會記錄所有行的變更。

套接字和pid文件

UNIX可以使用域套接字，存放地址

Java代碼??

SHOW?VARIABLES?LIKE?'socket';??

pid文件的存放位置

Mysql代碼??

SHOW?VARIABLES?LIKE?'pid_file';??

InnoDB存儲引擎文件

Mysql代碼??

--表空間的存儲位置，12M表示文件ibdata1的大小，autoextend為可以自動增長??

SHOW?VARIABLES?LIKE?'innodb_data_file_path';??

--ibdata1:12M:autoextend??

--每個表使用獨立的表空間，命名規(guī)則是??

--表名.fm為表定義文件??

--表明.idb為數據文件??

SHOW?VARIABLES?LIKE?'innodb_file_per_table';??

重做日志與二進制日志的區(qū)別

4.1 ?記錄的范圍不同：二進制日志會記錄MySQL的所有存儲引擎的日志記錄（包括InnoDB、MyISAM等)，而InnoDB存儲引擎的重做日志只會記錄其本身的事務日志。

4.2 記錄的內容不同：二進制日志文件記錄的格式可以為STATEMENT或者ROW也可以是MIXED，其記錄的都是關于一個事務的具體操作內容。InnoDB存儲引擎的重做日志文件記錄的關于每個頁的更改的物理情況。

4.3 寫入的時間也不同：二進制日志文件是在事務提交前進行記錄的，而在事務進行的過程中，不斷有重做日志條目被寫入到重做日志文件中。

重做日志相關的參數

Mysql代碼??

innodb_api_enable_binlog????OFF??

innodb_flush_log_at_timeout?1??

innodb_flush_log_at_trx_commit??1??

innodb_locks_unsafe_for_binlog??OFF??

innodb_log_buffer_size??1048576??

innodb_log_checksums????ON??

innodb_log_compressed_pages?ON??

innodb_log_file_size????50331648??

innodb_log_files_in_group???2??

innodb_log_group_home_dir???.\\??

innodb_log_write_ahead_size?8192??

innodb_max_undo_log_size????1073741824??

innodb_online_alter_log_max_size????134217728??

innodb_undo_log_truncate????OFF??

innodb_undo_logs????128??

重做日志文件組結構圖

重做日志寫入過程

查看innodb的版本和文件格式

Mysql代碼??

--查看版本??

SHOW?VARIABLES?LIKE?'innodb_version';??

--查看文件格式??

SHOW?VARIABLES?LIKE?'innodb_file_format';??

Innodb表存儲引擎文件架構

添加索引

Mysql代碼??

1.PRIMARY??KEY（主鍵索引）??

mysql>ALTER??TABLE??`table_name`??ADD??PRIMARY??KEY?(??`column`??)???

2.UNIQUE(唯一索引)??

????????mysql>ALTER??TABLE??`table_name`??ADD??UNIQUE?(??

`column`?)???

3.INDEX(普通索引)??

mysql>ALTER??TABLE??`table_name`??ADD??INDEX?index_name?(??`column`??)??

4.FULLTEXT(全文索引)??

mysql>ALTER??TABLE??`table_name`??ADD??FULLTEXT?(?`column`?)??

5.多列索引??

mysql>ALTER??TABLE??`table_name`??ADD??INDEX?index_name?(??`column1`,??`column2`,??`column3`??)??

自適應hash索引

只能用于類似這樣的語句

Mysql代碼??

SELECT?*?FORM?table_name?WHERE?index_col='xxx'??

自適應hash相關的信息，通過 SHOW ENGINE INNODB STATUS查看

Mysql代碼??

-------------------------------------??

INSERT?BUFFER?AND?ADAPTIVE?HASH?INDEX??

-------------------------------------??

Ibuf:?size?1,?free?list?len?0,?seg?size?2,?94?merges??

merged?operations:??

?insert?280,?delete?mark?0,?delete?0??

discarded?operations:??

?insert?0,?delete?mark?0,?delete?0??

Hash?table?size?4425293,?node?heap?has?1337?buffer(s)??

174.24?hash?searches/s,?169.49?non-hash?searches/s??

可以通過下面參數來禁用或啟動此特性，默認是開啟

Mysql代碼??

innodb_adaptive_hash_index??

全文索引

Mysql代碼??

MATCH?(列名1,?列名2,…)?AGAINST?(搜索字符串?[搜索修飾符])??

search_modifier:??

{??

IN?NATURAL?LANGUAGE?MODE??

|?IN?NATURAL?LANGUAGE?MODE?WITH?QUERY?EXPANSION??

|?IN?BOOLEAN?MODE??

|?WITH?QUERY?EXPANSION??

}??

幾種全文索引類型

1)IN NATURAL LANGUAGE MODE

簡介：默認的搜索形式（不加任何搜索修飾符或者修飾符為 IN NATURAL LANGUAGE MODE 的情況）

特點：

對于搜索字符串中的字符都解析為正常的字符，沒有特殊意義

對屏蔽字符列表中的字符串進行過濾

當記錄的選擇性超過50%的時候，通常被認為是不匹配。

返回記錄按照記錄的相關性進行排序顯示

Mysql代碼??

SELECT?*?FROM?product?WHERE?match(name)?against(‘auto’)??

2)IN BOOLEAN MODE

簡介：布爾模式搜索（搜索修飾符為IN BOOLEAN MODE的情況）

特點：

會按照一定的規(guī)則解析搜索字符串中的特殊字符的含義，進行一些邏輯意義的規(guī)則。如：某個單詞必須出現，或者不能出現等。

這種類型的搜索返回的記錄是不按照相關性進行排序的

Mysql代碼??

SELECT?*?FROM?articles?WHERE?MATCH?(title,body)?AGAINST?(‘+MySQL?-YourSQL’?IN?BOOLEAN?MODE);??

3)WITH QUERY EXPANSION

簡介：一種稍微復雜的搜索形式，實際上是進行了2次自然搜索，可以返回記錄直接簡介性關系的記錄，修飾詞IN NATURAL LANGUAGE MODE WITH QUERY EXPANSION 或者WITH QUERY EXPANSION modifier

特點：這種類型的搜素，實際上提供了一種間接的搜索功能，比如：我搜索某個詞，而且返回的第一行中卻不包含搜索詞中的任意字符串?？梢愿鶕谝淮嗡阉鹘Y果的記錄詞進行第二次匹配，從而可能找到一些間接關系的匹配記錄。

AuxiliaryTable是持久的表，存放在磁盤上，然而在InnoDB存儲引擎的全文索引中，還有另外一個重要的概念FTSIndexCache(全文檢索索引緩存)，其用來提高全文檢索的性能

InnoDB存儲引擎會批量對AuxiliaryTable進行更新.而不是每次插入后更新一次AuxiliaryTable.當全文檢索進行查詢時,AuxiliaryTable首先會將在FTSIndexCache中對應的word字段合并到AuxiliaryTable中，然后進行查詢。這種merge操作非常類似之前的InsertBuffer功能。不同的是InsertBuffer是個持久性的對象，并且是B+樹結構，然后FTSIndexCache的作用又和InsertBuffer類似，它提高了InnoDB存儲引擎的性能，并且由于其根據紅黑樹排序后進行批量插入，其產生的AuxiliaryTable相對較小

可以通過設置innodb_ft_aux_table來觀察倒排索引的AuxiliaryTable下面的SQL語句設置查看test架構下表fts_a的AuxiliaryTable:

Mysql代碼??

SET?GLOBAL?innodb_ft_aux_table='test/fts_a';??

B+樹索引

聚集索引

任何一個表都會有一個主鍵如果沒有就默認創(chuàng)建一個，然后順序寫入磁盤，主鍵的存儲方式是物理上順序的，通過主鍵的根和非根節(jié)點找到葉子節(jié)點，也就是數據節(jié)點。

輔助索引

這種索引的page類型跟數據節(jié)點是一樣的，存儲到磁盤上當時也是一個連續(xù)的B+樹結構，但是邏輯上不是連續(xù)的，通過掃描輔助索引就可以找到聚集索引，然后再找到真實數據

聯合索引

內部也是B+樹結構，聯合索引的第一個索引可能是排序好的，第二個索引就不是有序的了

SHOW INDEX FROM [table_name] 后顯示的每個字段含義

1.Table ? ? ? ? ? ? ? ? ? 表的名稱。

2.Non_unique ? ? ? 如果索引不能包括重復詞，則為0。如果可以，則為1。

3.Key_name ? ? ? ? ?索引的名稱。

4.Seq_in_index ? ? 索引中的列序列號，從1開始。

5.Column_name ? ?列名稱。

6.Collation ? ? ? ? ? ? ? ?列以什么方式存儲在索引中。在MySQL中，有值‘A’（升序）或NULL（無分類）。

7.Cardinality?

索引中唯一值的數目的估計值。通過運行ANALYZE TABLE或myisamchk -a可以更新?；鶖蹈鶕淮鎯檎麛档慕y計數據來計數，所以即使對于小型表，該值也沒有必要是精確的?；鶖翟酱螅斶M行聯合時，MySQL使用該索引的機會就越大。

8.Sub_part ? ? ? 如果列只是被部分地編入索引，則為被編入索引的字符的數目。如果整列被編入索引，則為NULL。

9.Packed ? ? ? ? ? ?指示關鍵字如何被壓縮。如果沒有被壓縮，則為NULL。

10.Null ? ? ? ? ? ? ? ? 如果列含有NULL，則含有YES。如果沒有，則該列含有NO。

11.Index_type ? ?用過的索引方法（BTREE, FULLTEXT, HASH, RTREE）。

12.Comment ? ? ? 多種評注。

Cardinality

Cardinality統計信息的更新發(fā)生在兩個操作中：insert和update。InnoDB存儲引擎內部對更新Cardinality信息的策略為:

1)表中1/16的數據已發(fā)生了改變(如果全部都是update則需要第二個操作配合使用)

2)stat_modified_counter>2000 000 000

默認的InnoDB存儲引擎對8個葉子節(jié)點Leaf Page進行采用。采用過程如下

取得B+樹索引中葉子節(jié)點的數量，記為A

隨機取得B+樹索引中的8個葉子節(jié)點，統計每個頁不同記錄的個數，即為P1，P2....P8

通過采樣信息給出Cardinality的預估值:Cardinality=(P1+P2+...+P8)*A/8

索引統計相關參數

索引優(yōu)化器會根據Cardinality的結果決定是否使用索引，不是加了索引就會一定使用索引的

可以建議MySql引擎使用索引

Mysql代碼??

--增加一個?USE?INDEX的語句，提示引擎使用索引??

SELECT?*?FROM?t?USE?INDEX(a)?WHERE?a=1?AND?b=2;??

但是仍然不能保存最終一定會使用索引，可以強制使用引擎用某個索引

Mysql代碼??

SELECT?*?FROM?t?FORCE?INDEX(a)?WHERE?a=1?AND?b=2;??

Multi-Range Read

MRR原理，MySQL 將根據輔助索引獲取的結果集根據主鍵進行排序，將亂序化為有序，可以用主鍵順序訪問基表，將隨機讀轉化為順序讀，多頁數據記錄可一次性讀入或根據此次的主鍵范圍分次讀入，以減少IO操作，提高查詢效率。

首先根據輔助索引得到主鍵，然后再查詢最終數據，因為輔助索引在邏輯上不是連續(xù)的所以得到的主鍵都是離散的，這樣查詢就變成隨機IO了

改變的策略是將獲取到的主鍵放到內存中進行一次排序，因為主鍵的順序就是數據存放的順序，所以排序玩之后的讀取就變成順序IO了，效率會提高很多

Index Condition Pushdown

使用ICP時，where的條件可以再一次過濾減少不必要的IO提高效率，可以跟MMR一起使用

注意一下ICP的使用條件：

只能用于二級索引(secondary index)。

explain顯示的執(zhí)行計劃中type值（join 類型）為range、 ref、 eq_ref或者ref_or_null。且查詢需要訪問表的整行數據，即不能直接通過二級索引的元組數據獲得查詢結果(索引覆蓋)。

參考

Antelope 和Barracuda區(qū)別

MyISAM和InnoDB的行格式ROW_FORMAT?

mysql innoDB重做日志文件?

MySQL全文索引應用簡明教程

理解B+樹算法和Innodb索引

MySQL 和 B 樹的那些事

倒排索引原理和實踐

搜索引擎-倒排索引基礎知識

MySQL5.6新特性之Multi-Range Read?

MySQL5.6之Index Condition Pushdown(ICP,索引條件下推)

MySql優(yōu)化參數

---------------------

作者：hixiaoxiaoniao

來源：CSDN

原文：https://blog.csdn.net/hixiaoxiaoniao/article/details/80983709

版權聲明：本文為博主原創(chuàng)文章，轉載請附上博文鏈接！