索引

索引分類

mysql有普通索引、空間索引、主鍵索引、唯一索引、組合索引和全文索引。

• 普通索引：使用字段關(guān)鍵字建立的索引，主要目的是提高查詢速度。
• 空間索引：前只有MyISAM引擎支持，用于空間類型的字段，不允許為空。
• 唯一索引：索引列中的值必須是唯一的，但允許為空值。
• 主鍵索引：是一種特殊的唯一索引，不允許有空值
• 組合索引：在表中的多個字段組合上創(chuàng)建的索引，只有在查詢條件中使用了這些字段的左邊字段時，索引才會被使用，遵循最左前綴集合原則。
• 全文索引：一種特殊類型的索引，主要用于全文搜索。

索引未命中的原因

1. 沒有查詢條件，或查詢條件中沒有索引
2. 條件中有or，并且不是所有的條件都有索引
3. like左匹配或者int字段
4. 索引列是字符串，但是sql中未加引號
5. 條件中在索引列使用函數(shù)
6. 采用not in/not exist查詢
7. B-tree索引 is null不走索引，is not null才走索引
8. 聯(lián)合索引不滿足最左原則

性能調(diào)優(yōu)命令Explain

explain用于解析sql的執(zhí)行計劃，執(zhí)行計劃是MySQL為了執(zhí)行查詢語句而制定的優(yōu)化方案，包括數(shù)據(jù)的讀取順序、表的訪問方式、索引的使用情況等信息。
以下是使用EXPLAIN指令的基本語法：

EXPLAIN SELECT column1, column2 FROM table_name WHERE condition;

使用EXPLAIN指令后，MySQL會返回一個表格，其中包含以下信息：

• id：查詢的標識符，id越大的越先執(zhí)行。
• select_type：查詢的類型，如SIMPLE、PRIMARY、SUBQUERY等。
• table：被查詢的表名。
• partitions：被查詢的分區(qū)信息。
• type：訪問表的方式，如ALL、INDEX、RANGE等。
  – ALL: 全表掃描；
  – INDEX： 索引全掃描。
  – range: 范圍掃描。
  – ref: 使用非唯一索引，或唯一索引的前綴掃描，返回匹配某個單獨值得記錄行。
  – eq_ref: 多表連接中使用，主鍵或唯一建作為關(guān)聯(lián)條件。
  – const/system: 單表中最多有一個匹配行。主要用于比較primary key 或unique索引，因為數(shù)據(jù)都是唯一的，所以性能最優(yōu)。
  – null: 不用訪問表或索引，直接就能得到結(jié)果。
• possible_keys：可能使用的索引。
• key：實際使用的索引。
• key_len：使用的索引長度。
• ref：用于比較的值。
• rows：MySQL估計需要讀取的行數(shù)，數(shù)值越大越不好，說明沒用好索引。
• filtered：MySQL估計返回的行數(shù)占總行數(shù)的比例。
• Extra：附加信息，如使用了臨時表、文件排序等。

回表

回表指當查詢條件（where條件和返回列）不能被索引索引鎖覆蓋時，需要訪問索引中沒有的數(shù)據(jù)，需要通過回表技術(shù)訪問數(shù)據(jù)所在頁，獲取具體得數(shù)據(jù)。
與回表相對應(yīng)的是覆蓋查詢，即查詢條件能夠被索引所覆蓋，不需要回表查詢數(shù)據(jù)，這樣查詢效率會更高。因此，在設(shè)計數(shù)據(jù)庫時，需要根據(jù)具體的情況選擇合適的索引策略，以提高查詢性能。

mysql性能優(yōu)化

1. 優(yōu)化sql
   1.1 使用explain命令解析sql執(zhí)行計劃
   1.2 盡量命中索引
   1.3 合理設(shè)計索引，不是越多越好
2. 優(yōu)化表結(jié)構(gòu)
   2.1 經(jīng)常查詢的表盡量避免過大。
   2.2 多余常用的字段盡量冗余，避免大表進行聯(lián)合查詢。
3. 分庫分表進行表瘦身
   3.1 水平分庫：以字段為依據(jù)，將一個庫中的數(shù)據(jù)進行拆分到多個庫中。比如按年份進行分庫。
   3.2 水平分表：以字段為依據(jù)，將一個表中的數(shù)據(jù)進行才分到多張表中。
   3.3 垂直分庫：以表為依據(jù)，按業(yè)務(wù)將表分到不同的庫中。
   3.4 垂直分表：以字段為依據(jù)，按字段中的活躍度將表中的字段拆分到不同的表，比如主表和擴展表。

事務(wù)

四大特性

事務(wù)的四大特性ACID,ACID是原子性（Atomicity [??t??m?s?ti]）、一致性（Consistency [k?n?s?st?nsi]）、隔離性（Isolation [?a?s??le??n]）、持久性（Durability [d?r??b?l?ti]）的縮寫，是數(shù)據(jù)庫事務(wù)的基本屬性，用于確保事務(wù)的可靠性和一致性。

• 原子性：事務(wù)是一個原子操作單元，事務(wù)的執(zhí)行要么全部成功，要么全部失敗回滾。
  – 由undolog實現(xiàn)
• 一致性：事務(wù)必須保證數(shù)據(jù)庫從一個狀態(tài)改變?yōu)榱硪粋€狀態(tài)，并保持數(shù)據(jù)的一致性和完整性。
  – 由原子性、隔離性、持久性共同實現(xiàn)
• 隔離性：多個事務(wù)并發(fā)執(zhí)行時，一個事務(wù)的執(zhí)行不應(yīng)影響其他事務(wù)的執(zhí)行。
  – 鎖+MVCC
• 持久性：一旦事務(wù)提交，對數(shù)據(jù)庫的修改是永久的，即使系統(tǒng)崩潰或故障也能保持。
  – 由redolog實現(xiàn)

事務(wù)隔離級別

MySQL數(shù)據(jù)庫的事務(wù)隔離級別有四個，分別是：讀未提交(Read Uncommitted)、讀提交(Read Committed)、可重復(fù)讀(Repeatable Read)和串行化(Serializable)。

• 讀未提交：事務(wù)中的修改，即使未提交，對其他事務(wù)也是可見的。
• 讀提交：事務(wù)只能看見已經(jīng)提交的數(shù)據(jù)修改，會產(chǎn)生不可重復(fù)讀。
• 可重復(fù)讀：同一事務(wù)中多次讀取數(shù)據(jù)的結(jié)果是一致的，但是并發(fā)的事務(wù)可能會對數(shù)據(jù)產(chǎn)生影響，會導致導致 幻讀。
• 串行化：最嚴格的事務(wù)隔離級別，通過鎖機制實現(xiàn)，確保事務(wù)串行化順序執(zhí)行，能避免幻讀，但是效率低下。

mysql默認的事務(wù)隔離級別是可重復(fù)讀(Repeatable Read)。

幻讀

幻讀的本質(zhì)，如果事務(wù)中都是快照讀，那么不會產(chǎn)生幻讀，但是快照讀和當前讀一起使用的時候就會產(chǎn)生幻讀。

MySQL中，當前讀和快照讀都是讀取數(shù)據(jù)的方式，具體含義如下：

• 當前讀：也稱鎖定讀（locking read），通過對讀取到的數(shù)據(jù)（索引記錄）加鎖來保證數(shù)據(jù)一致性，當前讀會對所有掃描到的索引記錄進行加鎖，無論該記錄是否滿足WHERE條件都會被加鎖1。
• 快照讀：MySQL使用MVCC（Multiversion Concurrency Control）機制來保證被讀取到數(shù)據(jù)的一致性，讀取數(shù)據(jù)時不需要對數(shù)據(jù)進行加鎖，且快照讀不會被其他事物阻塞1。

在讀提交和可重復(fù)讀兩種事務(wù)隔離級別下，普通的SELECT操作使用"快照讀"，不會對數(shù)據(jù)加鎖，也不會被事務(wù)阻塞。

設(shè)置事務(wù)隔離級別

在MySQL中，可以使用以下SQL語句設(shè)置事務(wù)的隔離級別：

SET TRANSACTION ISOLATION LEVEL <isolation_level>;

存儲引擎

mysql主要的存儲引擎是MyISAM和InnoDB，默認使用的是InnoDB引擎。
使用以下命令查詢當前使用的存儲引擎：

SHOW VARIABLES LIKE 'storage_engine';

使用以下命令查詢所有存儲引擎：

SHOW ENGINES;

• MyISAM：不支持事務(wù)、不支持外鍵，索引采用的是非聚簇索引。
• InnoDB：提供了具有提交、回滾等事務(wù)功能，支持自動增長列，外鍵等功能。采用聚簇索引，索引和數(shù)據(jù)是存儲在同一個文件下。

聚簇索引和非聚簇索引

聚簇索引

1. 數(shù)據(jù)掛載在主索引的葉子節(jié)點上。
2. 輔助索引的葉子節(jié)點指向主索引。

非聚簇索引

主索引和輔助索引的葉子節(jié)點指向數(shù)據(jù)存儲的位置。

最左前綴結(jié)合原則

最左前綴原則就是使用聯(lián)合索引時，查詢條件需要遵循索引中列的順序，從左到右進行匹配。
在創(chuàng)建多列索引時，要根據(jù)業(yè)務(wù)需求，where子句中使用最頻繁的一列放在最左邊。當創(chuàng)建（a，b，c）復(fù)合索引時，想要索引生效的話，只能使用a、ab、ac和abc四種組合。

select a,b,c from table where a = 'v' ; #索引生效，使用索引a
select a,b,c from table where a = 'v' and b='v1'; #索引生效，使用索引ab
select a,b,c from table where a = 'v' and c='v3'; #索引生效，使用索引a
select a,b,c from table where a = 'v' and b='v1' and c='v3'; #索引生效，使用索引abc

注意:實踐證明，where后面的條件順序并不會影響索引的命中條件，即 ba和ab效果一致。原因是mysql在執(zhí)行前會對where語句進行自行調(diào)優(yōu)。

全文索引

MySQL的全文索引是一種用于搜索文本內(nèi)容的索引方式，它可以通過分析文本內(nèi)容中的關(guān)鍵詞，幫助提高檢索效率。
MySQL使用全文索引主要分為以下步驟：

創(chuàng)建全文索引：

ALTER TABLE table_name ADD FULLTEXT(column_name);

創(chuàng)建全文索引的查詢：

SELECT * FROM table_name WHERE MATCH(column_name) AGAINST('keywords');

在查詢語句中，column_name是要查詢的列名，keywords是要搜索的關(guān)鍵詞。

需要注意的是，只有MyISAM和InnoDB存儲引擎支持全文索引，其中MyISAM引擎默認全文索引，InnoDB引擎需要手動創(chuàng)建全文索引。全文索引的查詢還需要考慮相關(guān)性和閾值，即匹配結(jié)果太少可能會查不到結(jié)果。

日志

slowlog

設(shè)置具體的時間，把執(zhí)行超時的sql記錄，在日志文件中，方便進行優(yōu)化調(diào)整。

binlog

主要用來進行數(shù)據(jù)庫之間的主從同步

errorlog

記錄數(shù)據(jù)庫進程中的一些錯誤信息

relaylog

在slaver機器中暫存同步過來的binlog數(shù)據(jù)

undolog

回滾日志，記錄的是數(shù)據(jù)的歷史版本信息，用來保證原子性和MVCC

redolog

前滾日志，將數(shù)據(jù)從內(nèi)存到磁盤的操作進行順序讀寫到redolog，當需要進行數(shù)據(jù)恢復(fù)的時候通過redolog進行數(shù)據(jù)恢復(fù)。

兩階段提交

DML操作日志寫入redolog和binlog的流程如下：
1.將日志寫入redolog，并標記狀態(tài)為（prepare）；
2.將日志寫入binlog；
3.修改redolog里面的狀態(tài)為commit。
當日志寫入第一步成功后宕機，重啟進行數(shù)據(jù)恢復(fù)時，在redolog中看到記錄是prepare的，于是到binlog中查找有無與之對應(yīng)的數(shù)據(jù)，如果沒有，則將redolog中的該條記錄刪除，如果有則將狀態(tài)改為commit。這樣就保證了redolog和binlog中的數(shù)據(jù)一致性。

MVCC

MVCC(Multi-version concurrency controll),多版本并發(fā)控制，用來解決數(shù)據(jù)并發(fā)場景中并發(fā)讀寫問題的。
并發(fā)場景下，多線程共同讀寫會有線程安全問題，會產(chǎn)生臟讀、幻讀、不可重復(fù)讀的問題。多線程共同寫會產(chǎn)生丟失更新的問題。
MVCC底層實現(xiàn)由三部分組成：隱藏字段、undolog、readview。

• 隱藏字段：對于用戶不可見;
  – DB_TXR_ID：創(chuàng)建這條記錄或最后一次修改該記錄的事務(wù)的id值。
  – DB_ROLL_PTR: 回滾指針，指向的是上一個數(shù)據(jù)的版本。
  – DB_ROW_ID:隱藏主鍵，如果數(shù)據(jù)表沒有設(shè)置主鍵，會生成一個6個字節(jié)的rowid作為隱藏主鍵。
• undolog：回滾日志，表示在insert、delete、update操作產(chǎn)生的方便回滾的日志。undolog中存儲的是通過DB_ROOLL_PTR鏈接起來的記錄歷史版本的一個鏈表，鏈表會有專門的線程負責清理。
• readview：讀視圖是在事務(wù)進行快照讀的時候產(chǎn)生的讀視圖，保存的并不是數(shù)據(jù)的信息，而是事務(wù)的相關(guān)信息。包含以下三個部分：
  – trx_list: 在生成readview時刻，當前系統(tǒng)正在活躍的事務(wù)列表。
  – up_limit_id：當前活躍列表中事務(wù)id最小的值。
  – low_limit_id: 系統(tǒng)尚未分配的笑一個事務(wù)id。

數(shù)據(jù)是否可讀通過可見性算法進行判斷,可見性算法的判斷依據(jù)是readView+DB_TXR_ID,事務(wù)通過控制readView的生成時機來控制數(shù)據(jù)的是否可讀。文章來源地址http://www.zghlxwxcb.cn/news/detail-675619.html

到了這里，關(guān)于Mysql知識梳理的文章就介紹完了。如果您還想了解更多內(nèi)容，請在右上角搜索TOY模板網(wǎng)以前的文章或繼續(xù)瀏覽下面的相關(guān)文章，希望大家以后多多支持TOY模板網(wǎng)！