mysql索引原理及優(yōu)化
點(diǎn)擊上方藍(lán)色字體，選擇“標(biāo)星公眾號(hào)”
優(yōu)質(zhì)文章，第一時(shí)間送達(dá)
作者 | Nooooone
來(lái)源 | urlify.cn/NB3Yrq
mysql索引結(jié)構(gòu)：
mysql索引使用B+tree，為什么使用B+tree呢，首先，使用索引是為了加快查找的速度，B+tree的查找時(shí)間復(fù)雜度為log(n).那為什么不用o(1)的hashMap呢。mysql是有使用hashMap結(jié)構(gòu)的hash索引的，但大部分情況下，我們使用的索引并不是hash索引，主要是hash索引這種結(jié)果在處理 !=, > ，< 這種范圍查詢時(shí)，需要全表掃描，時(shí)間復(fù)雜度為o(n).
為什么不使用B tree？BTree和B+Tree的區(qū)別是，B+Tree的非葉子結(jié)點(diǎn)只保存索引，不保存數(shù)據(jù)，這樣一個(gè)節(jié)點(diǎn)保存的數(shù)據(jù)更多，樹的高度更低，在讀取索引時(shí)，可以省IO(其實(shí)，這里降低樹高度基本沒啥用，因?yàn)橥覀兊腡ree的每個(gè)節(jié)點(diǎn)的度都很大，BTree和B+Tree高度基本差不多)。另一個(gè)主要的作用是，由于數(shù)據(jù)節(jié)點(diǎn)都在葉子結(jié)點(diǎn)上，而每個(gè)葉子結(jié)點(diǎn)又使用雙向鏈表鏈接，這樣，在處理范圍查詢時(shí)，只需要查定一個(gè)下界，然后在葉子結(jié)點(diǎn)上遍歷即可，且天然有序。
主鍵索引和二級(jí)索引
以Innodb為例：主鍵索引是和數(shù)據(jù)文件放在一塊的，即數(shù)據(jù)文件在葉子結(jié)點(diǎn)上。對(duì)于主鍵索引，找到了主鍵索引，主鍵索引對(duì)應(yīng)的value即為數(shù)據(jù)row。而非主鍵索引被稱為二級(jí)索引的原因是，非主鍵索引的value存放的是主鍵的值，我們?cè)谑褂梅侵麈I索引查找時(shí)，需要先根據(jù)索引找到主鍵，然后根據(jù)主鍵去找數(shù)據(jù)row。根據(jù)主鍵再去找數(shù)據(jù)row的過程稱為回表。因?yàn)閷?duì)于這樣的索引，不直接和數(shù)據(jù)關(guān)聯(lián)，所以稱為二級(jí)索引。對(duì)于二級(jí)索引，如果我們查找的列，已經(jīng)全部在索引列里了，這時(shí)候就不需要回表了，這種索引成為覆蓋(covered)索引.
主鍵索引也叫聚簇索引，二級(jí)索引也叫非聚簇索引
explain索引優(yōu)化。
use index：僅需要使用索引，不需要回表。
use where：通過where子句過濾，where子句過濾存儲(chǔ)引擎返回的結(jié)果。
use filesort 需要使用排序
const：用主鍵匹配
eq_ref: 兩個(gè)表join的時(shí)候，join的key是兩個(gè)表的主鍵。這時(shí)候，對(duì)于前一個(gè)表的每一行，后一張表只需要掃描一行 One row is read from this table for each combination of rows from the previous tables. Other than the system and const types, this is the best possible join type. It is used when all parts of an index are used by the join and the index is a PRIMARY KEY or UNIQUE NOT NULL index.
ref: 通過非唯一索引掃描，通常不需要進(jìn)行排序時(shí)，只要通過ref或者最左前綴匹配就可以了
range: range can be used when a key column is compared to a constant using any of the =, <>, >, >=, <, <=, IS NULL, <=>, BETWEEN, LIKE, or IN() operators.
index：使用索引，但比如在掃描之后 還需要order by. 這時(shí)候，需要掃描整個(gè)索引樹。
all：全表掃。
通常情況下，我們優(yōu)化的目標(biāo)到ref就可以了
當(dāng)我們想對(duì)一個(gè)sql語(yǔ)句進(jìn)行優(yōu)化時(shí)，可以用explain查看當(dāng)前sql的執(zhí)行計(jì)劃。對(duì)于explain的輸出，幾個(gè)重要的如下。
Type：
extra：
建立索引和索引匹配的原則
對(duì)于聯(lián)合索引，比如(row1,row2,row3)這種，mysql按照最左前綴匹配的時(shí)候，相當(dāng)于給我們建了(row1),(row1,row2),(row1,row2,row3)三個(gè)索引。因此當(dāng)有聯(lián)合索引的時(shí)候，不再需要單獨(dú)建立額外的單列索引。
數(shù)據(jù)查詢時(shí)，where自己后面的順序無(wú)所謂，mysql會(huì)自動(dòng)幫你優(yōu)化。
where 后面在使用or 查詢的時(shí)候，大部分情況下不會(huì)走到索引。所以，對(duì)于這種查詢，可以使用union來(lái)優(yōu)化In many cases, MySQL won't be able to use an index to apply an OR condition, and as a result, this query is not index-able.Therefore, we recommend to avoid such OR conditions and consider splitting the query to two parts, combined with a UNION DISTINCT (or even better, UNION ALL, in case you know there won't be any duplicate results）
建索引時(shí)，范圍字段放在聯(lián)合索引的最后，因?yàn)榘凑兆钭笄熬Y匹配原則，碰到范圍字段就終止匹配了，后面的字段不會(huì)去匹配。
區(qū)分度大的字段在建索引時(shí)放在前面。區(qū)分度公式：count(distinct col)/count(*)，就是一個(gè)字段當(dāng)選擇了一個(gè)值時(shí)，要能過濾掉大部分字段。
mysql NULL
NULL is not data type
NULL is a value place holder for optional table fields.
MySQL treats the NULL value differently from other data types. The NULL values when used in a condition evaluates to the false Boolean value.
Performing arithmetic operations on NULL values always returns NULL results.
The comparison operators such as [, =, etc.] cannot be used to compare NULL values.
‘+ - * / = != ’這些操作在作用于NULL時(shí)，永遠(yuǎn)返回NULL，在返回NULL做條件判斷時(shí)返回false。
對(duì)于NULL的判斷，使用is NULL 和is not NULL來(lái)判斷，使用= ， !=,都不會(huì)得到你想要的結(jié)果。
建表時(shí)，盡量所有字段都設(shè)置為非NULL，設(shè)為NULL時(shí)，mysql還需要額外使用字段來(lái)標(biāo)記是否為NULL。
