存儲引擎不能使用索引中范圍條件右邊的列

❶ 高性能Mysql：字元串類型（2）

字元串類型（ ）

與CHAR 和VARCHAR 類似的類型還有BINARY 和VARBINARY 它們存儲的是二進制字元串 二進制字元串跟常規字元串非常相似 但是二進制字元串存儲的是位元組碼而不是字元 填充也不一樣 MySQL 填充BINARY 採用的是 （零位元組）而不是空格 在檢索時也不會去掉填充值

當需要存儲二進制數據 並且希望MySQL 使用位元組碼而不是字元進行比較時 這些類型是非常有用的 二進制比較的優勢並不僅僅體現在大小寫敏感上 MySQL 比較BINARY 字元串時 每次按一個位元組 並且根據該位元組的數值進行比較 因此 二進制比較比字元比較簡單很多 所以也就更快

慷慨是不明智的

使用VARCHAR（ ） 和VARCHAR（ ） 存儲 hello 的空間開銷是一樣的 那麼使用更短的列有什麼優勢嗎？

事實證明有很大的優勢 更大的列會消耗更多的內存 因為MySQL 通常會分配固定大小的內存塊來保存內部值 尤其是使用內存臨時表進行排序或操作時會特別糟糕 在利用磁碟臨時表進行排序時也同樣糟糕

所以最好的策略是只分配真正需要的空間

BLOB 和TEXT 類型

BLOB 和TEXT 都是為存儲很大的數據而設計的字元串數據類型 分別採用二進制和字元方式存儲

實際上 它們分別屬於兩組不同的數據類型家族 字元類型是TINYTEXT SMALLTEXT TEXT MEDIUMTEXT LONGTEXT ;對應的二進制類型是TINYBLOB SMALLBLOB BLOB MEDIUMBLOB LONGBLOB BLOB 是SMALLBLOB 的同義詞 TEXT 是SMALLTEXT 的同義詞

與其他類型不同 MySQL 把每個BLOB 和TEXT 值當作一個獨立的對象處理 存儲引擎在存儲時通常會做特殊處理 當BLOB 和TEXT 值太大時 InnoDB 會使用專門的 外部

存儲區域來進行存儲 此時每個值在行內需要 ~ 個位元組存儲一個指針 然後在外部存儲區域存儲實際的值

BLOB 和TEXT 家族之間僅有的不同是BLOB 類型存儲的是二進制數據 沒有排序規則或字元集 而TEXT 類型有字元集和排序規則

MySQL 對BLOB 和TEXT 列進行排序與其他類型是不同的 它只對每個列的最前max_sort_length 位元組而不是整個字元串做排序 如果只需要排序前面一小部分字元 則可以減小max_sort_length 的配置 或者使用ORDER BY SUSTRING（column length）

MySQL 不能將BLOB 和TEXT 列全部長度的字元串進行索引 也不能使用這些索引消除排序 （關於這個主題下一章會有更多的信息 ）

磁碟臨時表和文件排序

因為Memory 引擎不支持BLOB 和TEXT 類型 所以 如果查詢使用了BLOB 或TEXT列並且需要使用隱式臨時表 將不得不使用MyISAM 磁碟臨時表 即使只有幾行數據也是如此（Percona Server 的Memory 引擎支持BLOB 和TEXT 類型 但直到本書寫作之際 同樣的場景下還是需要使用磁碟臨時表）

這會導致嚴重的性能開銷 即使配置MySQL 將臨時表存儲在內存塊設備上（RAMDisk） 依然需要許多昂貴的系統調用

最好的解決方案是盡量避免使用BLOB 和TEXT 類型 如果實在無法避免 有一個技巧是在所有用到BLOB 欄位的地方都使用SUBSTRING（column length） 將列值轉換為字元串（在ORDER BY 子句中也適用） 這樣就可以使用內存臨時表了 但是要確保截取的子字元串足夠短 不會使臨時表的大小超過max_heap_table_size 或tmp_table_size 超過以後MySQL 會將內存臨時表轉換為MyISAM 磁碟臨時表

最壞情況下的長度分配對於排序的時候也是一樣的 所以這一招對於內存中創建大臨時表和文件排序 以及在磁碟上創建大臨時表和文件排序這兩種情況都很有幫助 例如 假設有一個 萬行的表 佔用幾個GB 的磁碟空間 其中有一個utf 字元集的VARCHAR（ ） 列 每個字元最多使用 個位元組 最壞情況下需要 位元組的空間 如果在ORDER BY 中用到這個列 並且查詢掃描整個表 為了排序就需要超過 GB 的臨時表

這三行數據實際存儲為整數 而不是字元串 可以通過在數字上下文環境檢索看到這個雙重屬性

返回目錄 高性能MySQL

編輯推薦

ASP NET MVC 框架揭秘

Oracle索引技術

ASP NET開發培訓視頻教程

lishixin/Article/program/MySQL/201311/29686

❷ mySQL的存儲引擎

MyISAMMySQL 5.0 之前的默認資料庫引擎，最為常用。擁有較高的插入，查詢速度，但不支持事務
InnoDB事務型資料庫的首選引擎，支持ACID事務，支持行級鎖定, MySQL 5.5 起成為默認資料庫引擎
BDB源 自 Berkeley DB，事務型資料庫的另一種選擇，支持Commit 和Rollback 等其他事務特性
Memory所有數據置於內存的存儲引擎，擁有極高的插入，更新和查詢效率。但是會佔用和數據量成正比的內存空間。並且其內容會在 MySQL 重新啟動時丟失
Merge將一定數量的 MyISAM 表聯合而成一個整體，在超大規模數據存儲時很有用
Archive非常適合存儲大量的獨立的，作為歷史記錄的數據。因為它們不經常被讀取。Archive 擁有高效的插入速度，但其對查詢的支持相對較差
Federated將不同的 MySQL 伺服器聯合起來，邏輯上組成一個完整的資料庫。非常適合分布式應用
Cluster/NDB高冗餘的存儲引擎，用多台數據機器聯合提供服務以提高整體性能和安全性。適合數據量大，安全和性能要求高的應用
CSV： 邏輯上由逗號分割數據的存儲引擎。它會在資料庫子目錄里為每個數據表創建一個 .csv 文件。這是一種普通文本文件，每個數據行佔用一個文本行。CSV 存儲引擎不支持索引。
BlackHole：黑洞引擎，寫入的任何數據都會消失，一般用於記錄 binlog 做復制的中繼
EXAMPLE 存儲引擎是一個不做任何事情的存根引擎。它的目的是作為 MySQL 源代碼中的一個例子，用來演示如何開始編寫一個新存儲引擎。同樣，它的主要興趣是對開發者。EXAMPLE 存儲引擎不支持編索引。
另外，MySQL 的存儲引擎介面定義良好。有興趣的開發者可以通過閱讀文檔編寫自己的存儲引擎。

❸ Mysql高級(五) 索引失效

1.全值匹配

2.最佳左前綴法則
3.不在索引列上做任何操作（計算、函數、（自動or手動）類型轉換），會導致索引失效而轉向全表掃描
4.存儲引擎不能使用索引中范圍條件右邊的列
5.盡量使用覆蓋索引（只訪問索引的查詢（索引列和查詢列一直）），減少select *
6.mysql在使用不等於（!=或者<>）的時候無法使用索引會導致全表掃描
7.is null， is not null也無法使用索引
8.like以通配符開頭（『%abc...』）mysql索引失效會變成全表掃描的操作
9.字元串不加單引號索引失效
10.少用or，用它來連接時索引會失效

❹ 索引失效的情況有哪些

原因有如下：

1、最佳左前綴原則——如果索引了多列，要遵守最左前綴原則。指的是查詢要從索引的最左前列開始並且不跳過索引中的列。

2、不在索引列上做任何操作，會導致索引失效而導致全表掃描。

3、存儲引擎不能使用索引中范圍條件右邊的列，范圍之後索引失效。這寫條件判斷最後放到後面，先定位到小的范圍再開始。

4、mysql使用不等於(!= 或者<>)的時候，無法使用索引，會導致索引失效。

5、mysql中使用is not null 或者 is null會導致無法使用索引。

6、mysql中like查詢是以%開頭，索引會失效變成全表掃描，覆蓋索引。

7、mysql中，如果條件中有or，即使其中有條件帶索引也不會使用(這也是為什麼盡量少用or的原因)。要想使用or，又想讓索引生效，只能將or條件中的每個列都加上索引。

8、如果mysql使用全表掃描要比使用索引快,則不會使用到索引。

注意事項

1、索引列有函數處理或隱式轉換，不走索引。

2、索引列傾斜，個別值查詢時，走索引代價比走全表掃描高，所以不走索引。

3、索引列沒有限制 not null，索引不存儲空值，如果不限制索引列是not null，oracle會認為索引列有可能存在空值，所以不會按照索引計算。

❺ 如何高效地利用MySQL索引

1、要想高效利用索引，我們首先要考慮如何正確建立索引。

（1）在經常做搜索的列上，也就是WHERE子句里經常出現的列，考慮加上索引，加快搜索速度。

（2）唯一標識記錄的列，應該加上唯一索引，強制該列的唯一性並且加快按該列查找記錄的速度。

（3）在內連接使用的列上加上索引，最好是在內連接用到欄位都加上，因為MySQL優化器會自動地選擇連接順序，然後觀察索引的使用情況，將沒用的索引刪除即可。

（4）在需要排序的列上加上索引，因為索引本身是按順序的組織的，它可以避免 filesort，要知道，Server層在進行排序時是在內存中進行的，非常消耗資源。

（5）可以考慮實現覆蓋索引，即根據 SELECT 的所有欄位上創建聯合索引，這樣存儲引擎只用讀取索引而不用去回表查詢，極大地減少了對數據表的訪問，大大地提高了性能。

（6）對於那些選擇性很小的列，比如性別列，增加索引並不能明顯加快查詢速度，反而該索引會成為表的累贅。

（7）對於那些定義為text, image和bit數據類型的列不應該增加索引。這是因為，這些列的要麼數據量相當大，要麼取值很少。

（8）當對寫性能的要求遠遠大於讀性能時，不應該創建索引。寫性能和讀性能是互相矛盾的。這是因為，維護一個 B+Tree 成本是非常大的，對索引的寫會涉及到頁的分裂等。

（9）復合索引的幾個欄位是否經常同時以AND方式出現在Where子句中？單欄位查詢是否極少甚至沒有？如果是，則可以建立復合索引，否則考慮單欄位索引。這還是說明，滿足查詢性能的前提下，索引越少越好。

（10）如果復合索引所包含的欄位超過3個，那麼仔細考慮其必要性，考慮減少復合的欄位。

（11）在用於GROUP BY的列上加上索引，避免使用臨時表。

（12）對於較長的字元列，如 char、varchar等，由於字元串的比較相對來說非常耗時，因此考慮使用前綴索引減少索引長度，或者創建自定義哈希索引，將字元串映射成整數，然後以該整數作為索引，同時以字元串的值作為過濾條件。

我們在創建索引時，可以根據下面原則進行簡單判斷：索引是否將相關記錄集合到了一起，從未減少了磁碟I/O，加快搜索速度？索引中數據的排列順序是否和查找的數據的排列順序一致，從而避免了Server層的排序？索引中的列是否包含了查詢中需要的全部列從而實現了覆蓋索引？ 這幾個條件層層遞進，滿足得越多越好。

2、索引正確地建立了，我們還需要正確地使用它們：

（1）使用了運算符 !=，以及關鍵字not in，not exist，>，<等，總之產生的結果集很大時（也在where條件進行大范圍的選擇時），往往導致引擎不使用索引而是走全盤掃描。因為如果使用索引會造成大量的隨機I/O，得不償失。

（2）如果對索引列進行運算，如 WHERE substr(name, 1, 3)=『mark』，存儲引擎並不能聰明地判斷哪些索引滿足等式，因此不能使用到索引。

（3）使用到了LIKE，並且通配符在最前面時，不能使用索引。

（4）對於聯合索引 (a, b, c)，如果沒用到最左列，那麼一般情況下都使用不到索引。但是，比如統計操作 count(*) where a > xxx，是可以使用到該聯合索引的。畢竟統計這類操作，它不是檢索，並不需要索引完全有序。

（5）對於聯合索引，如果某個列使用了范圍查找，那麼其右邊的列都無法作為索引優化查詢，但是由於 ICP（Index Condition Pushdown），這些列能作為過濾條件在存儲引擎中對數據進行過濾。

（6）如果條件中有 OR，則必須每個OR用到的欄位都有索引，否則不能使用任何索引。

（7）想在聯合查詢中使用索引來避免 filesort，則關聯查詢中的ORDER BY用到的欄位必須全部是第一張表（驅動表）上的。

❻ MySQL資料庫存儲引擎詳解

存儲引擎是什麼？

MySQL中的數據用各種不同的技術存儲在文件(或者內存)中 這些技術中的每一種技術都使用不同的存儲機制 索引技巧 鎖定水平並且最終提供廣泛的不同的功能和能力 通過選擇不同的技術 你能夠獲得額外的速度或者功能 從而改善你的應用的整體功能

例如 如果你在研究大量的臨時數據 你也許需要使用內存存儲引擎 內存存儲引擎能夠在內存中存儲所有的表格數據 又或者 你也許需要一個支持事務處理的資料庫(以確保事務處理不成功時數據的回退能力)

這些不同的技術以及配套的相關功能在MySQL中被稱作存儲引擎(也稱作表類型) MySQL默認配置了許多不同的存儲引擎 可以預先設置或者在MySQL伺服器中啟用 你可以選擇適用於伺服器 資料庫和表格的存儲引擎 以便在選擇如何存儲你的信息 如何檢索這些信息以及你需要你的數據結合什麼性能和功能的時候為你提供最大的靈活性

選擇如何存儲和檢索你的數據的這種靈活性是MySQL為什麼如此受歡迎的主要原因 其它資料庫系統(包括大多數商業選擇)僅支持一種類型的數據存儲 遺憾的是 其它類型的資料庫解決方案採取的 一個尺碼滿足一切需求 的方式意味著你要麼就犧牲一些性能 要麼你就用幾個小時甚至幾天的時間詳細調整你的資料庫 使用MySQL 我們僅需要修改我們使用的存儲引擎就可以了

在這篇文章中 我們不準備集中討論不同的存儲引擎的技術方面的問題(盡管我們不可避免地要研究這些因素的某些方面) 相反 我們將集中介紹這些不同的引擎分別最適應哪種需求和如何啟用不同的存儲引擎 為了實現這個目的 在介紹每一個存儲引擎的具體情況之前 我們必須要了解一些基本的問題

如何確定有哪些存儲引擎可用

你可以在MySQL(假設是MySQL伺服器 以上版本)中使用顯示引擎的命令得到一個可用引擎的列表

mysql>showengines; + + + + |Engine|Support|Comment| + + + + |MyISAM|DEFAULT|DefaultengineasofMySQL withgreatperformance| |HEAP|YES|AliasforMEMORY| |MEMORY|YES|Hashbased storedinmemory usefulfortemporarytables| |MERGE|YES|| |MRG_MYISAM|YES|AliasforMERGE| |ISAM|NO|Obsoletestorageengine nowreplacedbyMyISAM| |MRG_ISAM|NO|Obsoletestorageengine nowreplacedbyMERGE| |InnoDB|YES|Supportstransactions row levellocking andforeignkeys| |INNOBASE|YES|AliasforINNODB| |BDB|NO|Supportstransactionsandpage levellocking| |BERKELEYDB|NO|AliasforBDB| |NDBCLUSTER|NO|Clustered fault tolerant memory basedtables| |NDB|NO|AliasforNDBCLUSTER| |EXAMPLE|NO|Examplestorageengine| |ARCHIVE|NO|Archivestorageengine| |CSV|NO|CSVstorageengine| + + + + rowsinset( sec)

這個表格顯示了可用的資料庫引擎的全部名單以及在當前的資料庫伺服器中是否支持這些引擎

對於MySQL 以前版本 可以使用mysql> show variables like have_% (顯示類似 have_% 的變數):

mysql>showvariableslike have_% ; + + + |Variable_name|Value| + + + |have_bdb|YES| |have_crypt|YES| |have_innodb|DISABLED| |have_isam|YES| |have_raid|YES| |have_symlink|YES| |have_openssl|YES| |have_query_cache|YES| + + + rowsinset( sec)

你可以通過修改設置腳本中的選項來設置在MySQL安裝軟體中可用的引擎 如果你在使用一個預先包裝好的MySQL二進制發布版軟體 那麼 這個軟體就包含了常用的引擎 然而 需要指出的是 如果你要使用某些不常用的引擎 特別是CSV RCHIVE(存檔)和BLACKHOLE(黑洞)引擎 你就需要手工重新編譯MySQL源碼

使用一個指定的存儲引擎

你可以使用很多方法指定一個要使用的存儲引擎 最簡單的方法是 如果你喜歡一種能滿足你的大多數資料庫需求的存儲引擎 你可以在MySQL設置文件中設置一個默認的引擎類型（使用storage_engine 選項）或者在啟動資料庫伺服器時在命令行後面加上 default storage engine或 default table type選項

更靈活的方式是在隨MySQL伺服器發布同時提供的MySQL客戶端時指定使用的存儲引擎 最直接的方式是在創建表時指定存儲引擎的類型 向下面這樣:

CREATE TABLE mytable (id int title char( )) ENGINE = INNODB

你還可以改變現有的表使用的存儲引擎 用以下語句:

ALTER TABLE mytable ENGINE = MyISAM

然而 你在以這種方式修改表格類型的時候需要非常仔細 因為對不支持同樣的索引 欄位類型或者表大小的一個類型進行修改可能使你丟失數據 如果你指定一個在你的當前的資料庫中不存在的一個存儲引擎 那麼就會創建一個MyISAM(默認的)類型的表

各存儲引擎之間的區別

為了做出選擇哪一個存儲引擎的決定 我們首先需要考慮每一個存儲引擎提供了哪些不同的核心功能 這種功能使我們能夠把不同的存儲引擎區別開來 我們一般把這些核心功能分為四類:支持的欄位和數據類型 鎖定類型 索引和處理 一些引擎具有能過促使你做出決定的獨特的功能 我們一會兒再仔細研究這些具體問題

欄位和數據類型

雖然所有這些引擎都支持通用的數據類型 例如整型 實型和字元型等 但是 並不是所有的引擎都支持其它的欄位類型 特別是BLOG（二進制大對象）或者TEXT文本類型 其它引擎也許僅支持有限的字元寬度和數據大小

這些局限性可能直接影響到你可以存儲的數據 同時也可能會對你實施的搜索的類型或者你對那些信息創建的索引產生間接的影響 這些區別能夠影響你的應用程序的性能和功能 因為你必須要根據你要存儲的數據類型選擇對需要的存儲引擎的功能做出決策

鎖定

資料庫引擎中的鎖定功能決定了如何管理信息的訪問和更新 當資料庫中的一個對象為信息更新鎖定了 在更新完成之前 其它處理不能修改這個數據(在某些情況下還不允許讀這種數據)

鎖定不僅影響許多不同的應用程序如何更新資料庫中的信息 而且還影響對那個數據的查詢 這是因為查詢可能要訪問正在被修改或者更新的數據 總的來說 這種延遲是很小的 大多數鎖定機制主要是為了防止多個處理更新同一個數據 由於向數據中插入信息和更新信息這兩種情況都需要鎖定 你可以想像 多個應用程序使用同一個資料庫可能會有很大的影響

不同的存儲引擎在不同的對象級別支持鎖定 而且這些級別將影響可以同時訪問的信息 得到支持的級別有三種:表鎖定 塊鎖定和行鎖定 支持最多的是表鎖定 這種鎖定是在MyISAM中提供的 在數據更新時 它鎖定了整個表 這就防止了許多應用程序同時更新一個具體的表 這對應用很多的多用戶資料庫有很大的影響 因為它延遲了更新的過程

頁級鎖定使用Berkeley DB引擎 並且根據上載的信息頁( KB)鎖定數據 當在資料庫的很多地方進行更新的時候 這種鎖定不會出現什麼問題 但是 由於增加幾行信息就要鎖定數據結構的最後 KB 當需要增加大量的行 也別是大量的小型數據 就會帶來問題

行級鎖定提供了最佳的並行訪問功能 一個表中只有一行數據被鎖定 這就意味著很多應用程序能夠更新同一個表中的不同行的數據 而不會引起鎖定的問題 只有InnoDB存儲引擎支持行級鎖定

建立索引

建立索引在搜索和恢復資料庫中的數據的時候能夠顯著提高性能 不同的存儲引擎提供不同的製作索引的技術 有些技術也許會更適合你存儲的數據類型

有些存儲引擎根本就不支持索引 其原因可能是它們使用基本表索引(如MERGE引擎)或者是因為數據存儲的方式不允許索引(例如FEDERATED或者BLACKHOLE引擎)

事務處理

事務處理功能通過提供在向表中更新和插入信息期間的可靠性 這種可靠性是通過如下方法實現的 它允許你更新表中的數據 但僅當應用的應用程序的所有相關操作完全完成後才接受你對表的更改 例如 在會計處理中每一筆會計分錄處理將包括對借方科目和貸方科目數據的更改 你需要要使用事務處理功能保證對借方科目和貸方科目的數據更改都順利完成 才接受所做的修改 如果任一項操作失敗了 你都可以取消這個事務處理 這些修改就不存在了 如果這個事務處理過程完成了 我們可以通過允許這個修改來確認這個操作

lishixin/Article/program/MySQL/201311/29301

❼ SQL優化（二）

SQL優化一： sql優化（一）

上片文章已經詳細介紹了explain各個欄位的含義，以及什麼情況應該建立索引，什麼情況不需要建立索引以及sql語句性能的判斷依據，接下來我介紹下如何合理的建立索引。

sql語句：select id，author_id from article where category_id = 1 and comments>1 order by views desc limit 1;

分析：首先我們根據where後面的條件建立符合索引，然後根據order by後面的欄位建立索引，因此建立索引idx_article_ccv，即以（category_id，comments，views）數據列建立復合索引，但由於comments是一個范圍，按照BTree索引的原理，先排序category_id,如果遇到相同的category_id則再排序comments，如果遇到相同的comments則再排序views，又因為comments欄位在復合索引里處於中間位置，而comments>1是一個條件（是一個范圍值），在復合索引的一個范圍值的數據列後面的索引全部失效，mysql無法利用索引再對後面的views部分進行檢索，也就是說views無法按照索引排序，所以explain下此sql語句，type為range，extra使用的是Using filesort，這是比較糟糕的。所以我們放棄comments這個范圍欄位，建立索引idx_article_cv，即以（category_id，views）數據列建立復合索引，explain 此sql，type變成了ref，extra的using filesort也變成了using index，這就變得好多了。

索引：idx_article_cv，即以（category_id，views）數據列建立復合索引

前段時間做了一個銷售精細化項目，是公司crm項目的一個大模塊，大致就是為銷售人員制定指標，實現銷售目標從區域到團到業務員到客戶，實時跟蹤業務員所負責客戶的下單量的情況。這就存在許多關聯關系，區域-團，團-業務員，業務員-客戶，這使得sql常常需要關聯多張表。

sql語句：SELECT

tu.fuserid,

tu.faccount,

tu.fphone,

tu.fcertificationtype,

tu.fcertificatename,

tu.fkeyarea,

tu.fkeyareatext,

DATE_FORMAT(tcr.fupdatetime,'%Y-%m-%d %H:%i:%s') as fupdatetime,

tag.forggroupid,

tag.forggroupname,

tug.forguserid,

tug.fusername,

tug.fuserphone,

tag.fcitycode

FROM t_finedt_user AS tu

LEFT JOIN t_finedt_customer_relation AS tcr

ON tu.fuserid = tcr.fuserid

LEFT JOIN t_finedt_usergroup AS tug

ON tcr.forguserid = tug.forguserid

and tcr.forggroupid = tug.forggroupid

LEFT JOIN t_finedt_areagroup AS tag

ON tug.forggroupid = tag.forggroupid

where tu.fkeyarea=? and tu.fuserid=? and tug.forggroupid = ?

分析：上面的sql是左連接，左邊的表一定是全表查詢，所以要建立右邊表對應關聯欄位的索引，在表t_finedt_user上建立tu_fuserid_fkeyarea索引，即以(fuserid，fkeyarea)欄位建立索引，在表t_finedt_customer_relation 上建立tcr_forguserid_forggroupid索引，即以（forguserid，forggroupid）欄位建立索引，在表t_finedt_usergroup 上建立tug_forguserid_forggroupid索引，即以（forguserid,forggroupid)欄位建立索引，在表t_finedt_areagroup上建立tag_forggroupid索引，即以（forggroupid)欄位建立索引。建立索引後，sql查詢速度明顯快了很多

索引：tcr_forguserid_forggroupid，tu_fuserid_fkeyarea，tug_forguserid_forggroupid，tag_forggroupid

1、盡可能減少join語句中的NestedLoop的循環次數，永遠用小結果集驅動大結果集

2、優先優化NestedLoop的內層循環

3、保證join語句總被驅動表上的join欄位已經被索引

4、當無法保證被驅動表join條件欄位被索引，且內存資源充足的前提下，不要太吝嗇joinBuffer的設置

1、全值匹配我最愛

2、最佳左前綴原則——如果索引了多列，要遵守最左前綴原則，指的是查詢從索引的最左前列開始並且不跳過索引中的列

3、並在索引列上做任何操作（計算、函數、自動or手動類型轉換），這些會導致索引失效而轉向全表掃描

4、存儲引擎不能使用索引中范圍條件右邊的列，范圍之後的索引全失效

5、盡量使用覆蓋索引（之訪問索引的查詢（索引列和查詢的列一致）），減少select *

6、mysql在使用不等於（！=、>、<）的時候無法使用索引會導致全表掃描。

7、is null、is not null也無法使用索引。

8、like以通配符開頭（"%abc.."），mysql索引失效也會變成全表掃描的操作。

9、字元串不加單引號也會引起索引失效

10、少用or，用它來連接時會索引失效。

1、對於單值索引，盡量選擇針對當前query過濾性更好的索引

2、在選擇組合索引的時候，當前query中過濾性最好的欄位在索引欄位順序中，位置越靠前越好

3、在選擇組合索引的時候，盡量選擇盡可能包含當前query中的where字句中更多欄位的索引

4、盡可能通過分析統計信息和調整query的寫法來達到選擇合適索引的目的。

全值匹配我最愛，最左前綴要遵守

帶頭大哥不能死，中間兄弟不能斷

索引列上少計算，范圍之後全失效

like百分寫最右，覆蓋索引不寫里

不等空值還有or，索引失效要少用

var引號不可丟，sql高級也不難

❽ 【mysql】索引類型的劃分

了解mysql的索引類型的時候，我覺得按照以下4中方式劃分邏輯是比較清晰的。
1.存儲結構 2.物理存儲 3.作用欄位 4.功能

按照數據存儲的結構可以分B樹索引和hash索引。

又稱為 BTREE 索引，目前大部分的索引都是採用 B-樹索引來存儲的。B-樹索引是一個典型的數據結構。
基於這種樹形數據結構，表中的每一行都會在索引上有一個對應值。因此，在表中進行數據查詢時，可以根據索引值一步一步定位到數據所在的行。
查詢必須從索引的最左邊的列開始。
查詢不能跳過某一索引列，必須按照從左到右的順序進行匹配。
存儲引擎不能使用索引中范圍條件右邊的列。

也稱為散列索引或 HASH 索引。MySQL 目前僅有 MEMORY 存儲引擎和 HEAP 存儲引擎支持這類索引。
其中，MEMORY 存儲引擎可以支持 B-樹索引和 HASH 索引，且將 HASH 當成默認索引。
HASH 索引不是基於樹形的數據結構查找數據，而是根據索引列對應的哈希值的方法獲取表的記錄行。
不能使用 HASH 索引排序。
HASH 索引只支持等值比較，如「=」「IN()」或「<=>」。
HASH 索引不支持鍵的部分匹配，因為在計算 HASH 值的時候是通過整個索引值來計算的。

聚集索引是按照所以把數據排好序了，所以一個表只能存在一個聚集索引，其它的都是非聚集索引。
因這個特性，聚集索引是查詢數據范圍的時候有很大的性能優勢。
但是也需要注意的是如果頻繁更新的列不適合設置為聚集索引，
原因很簡單，每次更新都需要從新排序，頻繁的更新給的壓力也大。
如果不指定的話，默認主鍵為聚集索引。

一個表裡除了一個聚集索引外其他的都是非聚集索引，雖然不能把數據按照索引排序，但是索引數據是可以排序的。
所以非聚集索引查詢范圍的時候是先找索引列的范圍，再通過這個索引查詢行的值。

單列索引即一個索引只包含單個列。

組合索引指在表的多個欄位組合上創建的索引，只有在查詢條件中使用了這些欄位的左邊欄位時，索引才會被使用。使用組合索引時遵循最左前綴集合

Primary Key（聚集索引）：InnoDB存儲引擎的表會存在主鍵（唯一非null），如果建表的時候沒有指定主鍵，則會使用第一非空的唯一索引作為聚集索引，否則InnoDB會自動幫你創建一個不可見的、長度為6位元組的row_id用來作為聚集索引。

Key（普通索引）：是MySQL中的基本索引類型，允許在定義索引的列中插入重復值和空值

Unique（唯一索引）：索引列的值必須唯一，但允許有空值。若是組合索引，則列值的組合必須唯一。
主鍵索引是一種特殊的唯一索引，不允許有空值。

既不是主鍵索引也不是唯一索引的一般索引。

FULLTEXT（全文索引）：全文索引類型為FULLTEXT，在定義索引的列上支持值的全文查找，允許在這些索引列中插入重復值和空值。
全文索引可以在CHAR、VARCHAR或者TEXT類型的列上創建。

空間索引主要用於地理空間數據類型 GEOMETRY。

下面是 mysql官網給出的幾個存儲引擎和索引之間的關系 。

歡迎大家的意見和交流

email: [email protected]

❾ MySQL存儲引擎

InnoDB的數據文件本身就是主索引文件。而MyISAM的主索引和數據是分開的。輔助索引data域存儲相應記錄主鍵的值而不是地址。

innoDB是聚簇索引，數據掛在逐漸索引之下。

是 MySQL 默認的事務型存儲引擎， 只有在需要它不支持的特性時，才考慮使用其它存儲引擎 。

實現了四個標準的隔離級別，默認級別是可重復讀(REPEATABLE READ)。在可重復讀隔離級別下，通過多版本並發控制(MVCC)+ 間隙鎖(Next-Key Locking)防止幻影讀。

主索引是聚簇索引，在索引中保存了數據，從而避免直接讀取磁碟，因此對查詢性能有很大的提升。

內部做了很多優化，包括從磁碟讀取數據時採用的可預測性讀、能夠加快讀操作並且自動創建的自適應哈希索引、能夠加速插入操作的插入緩沖區等。

支持真正的在線熱備份。其它存儲引擎不支持在線熱備份，要獲取一致性視圖需要停止對所有表的寫入，而在讀寫混合場景中，停止寫入可能也意味著停止讀取。

以B+樹作為索引結構，葉節點的數據域存放數據記錄的地址。主索引和輔助索引在結構上沒有區別，只是主索引要求key唯一，而輔助索引的key可以重復。

MyISAM中索引檢索的演算法為首先按照B+Tree搜索演算法搜索索引，如果指定的Key存在，則取出其data域的值，然後以data域的值為地址，讀取相應數據記錄。

設計簡單，數據以緊密格式存儲。對於只讀數據，或者表比較小、可以容忍修復的操作，則依然可以使用它。

提供了大量的特性，包括壓縮表、空間數據索引等。

不支持事務 。

不支持行級鎖，只能對整張表加鎖，讀取時會對需要讀到的所有表加共享鎖，寫入時則對表加排它鎖。但在表有讀取操作的同時，也可以往表中插入新的記錄，這被稱為並發插入(CONCURRENT INSERT)。

可以手工或者自動執行檢查和修復操作，但是和事務恢復以及崩潰恢復不同，可能導致一些數據丟失，而且修復操作是非常慢的。

如果指定了 DELAY_KEY_WRITE 選項，在每次修改執行完成時，不會立即將修改的索引數據寫入磁碟，而是會寫到內存中的鍵緩沖區，只有在清理鍵緩沖區或者關閉表的時候才會將對應的索引塊寫入磁碟。這種方式可以極大地提升寫入性能，但是在資料庫或者主機崩潰時會造成索引損壞，需要執行修復操作。