Mysql目前主要有以下幾種索引類(lèi)型：FULLTEXT钥勋，HASH炬转，BTREE，RTREE算灸。

1. FULLTEXT

即為全文索引扼劈，目前只有MyISAM引擎支持。其可以在CREATE TABLE 菲驴，ALTER TABLE 荐吵，CREATE INDEX 使用，不過(guò)目前只有 CHAR、VARCHAR 先煎，TEXT 列上可以創(chuàng)建全文索引贼涩。

全文索引并不是和MyISAM一起誕生的，它的出現(xiàn)是為了解決WHERE name LIKE “%word%"這類(lèi)針對(duì)文本的模糊查詢效率較低的問(wèn)題薯蝎。

RTREE在MySQL很少使用遥倦，僅支持geometry數(shù)據(jù)類(lèi)型，支持該類(lèi)型的存儲(chǔ)引擎只有MyISAM良风、BDb谊迄、InnoDb、NDb烟央、Archive幾種统诺。

相對(duì)于BTREE，RTREE的優(yōu)勢(shì)在于范圍查找疑俭。

2. HASH

由于HASH的唯一（幾乎100%的唯一）及類(lèi)似鍵值對(duì)的形式粮呢，很適合作為索引。

HASH索引可以一次定位钞艇，不需要像樹(shù)形索引那樣逐層查找,因此具有極高的效率啄寡。但是，這種高效是有條件的哩照，即只在“=”和“in”條件下高效挺物，對(duì)于范圍查詢、排序及組合索引仍然效率不高飘弧。

3. BTREE

BTREE索引就是一種將索引值按一定的算法识藤，存入一個(gè)樹(shù)形的數(shù)據(jù)結(jié)構(gòu)中（二叉樹(shù)），每次查詢都是從樹(shù)的入口root開(kāi)始次伶，依次遍歷node痴昧，獲取leaf。這是MySQL里默認(rèn)和最常用的索引類(lèi)型冠王。

mysql索引實(shí)現(xiàn)原理采用B+Tree

MyISAM 索引實(shí)現(xiàn)

MyISAM 引擎使用 B+Tree 作為索引結(jié)構(gòu),葉節(jié)點(diǎn)的 data 域存放的是數(shù)據(jù)記錄的地址

輔助索引

在 MyISAM 中,主索引和輔助索引(Secondary key)在結(jié)構(gòu)上沒(méi)有任何區(qū)別,只是主索引要求 key 是唯一的,而輔助索引的 key 可以重復(fù)

InnoDB 索引實(shí)現(xiàn)

1.第一個(gè)重大區(qū)別是 InnoDB 的數(shù)據(jù)文件本身就是索引文件赶撰。從上文知道,MyISAM 索引文件和數(shù)據(jù)文件是分離的,索引文件僅保存數(shù)據(jù)記錄的地址

2.第二個(gè)與 MyISAM 索引的不同是 InnoDB 的輔助索引 data 域存儲(chǔ)相應(yīng)記錄主鍵的值而不是地址。換句話說(shuō),InnoDB 的所有輔助索引都引用主鍵作為 data 域柱彻。

而在InnoDB 中,表數(shù)據(jù)文件本身就是按 B+Tree 組織的一個(gè)索引結(jié)構(gòu),這棵樹(shù)的葉點(diǎn)data 域保存了完整的數(shù)據(jù)記錄豪娜。這個(gè)索引的 key 是數(shù)據(jù)表的主鍵,因此 InnoDB 表數(shù)據(jù)文件本身就是主索引。

三绒疗、索引種類(lèi)

普通索引：僅加速查詢

唯一索引：加速查詢 + 列值唯一（可以有null）

主鍵索引：加速查詢 + 列值唯一（不可以有null）+ 表中只有一個(gè)

組合索引：多列值組成一個(gè)索引侵歇，專(zhuān)門(mén)用于組合搜索，其效率大于索引合并

全文索引：對(duì)文本的內(nèi)容進(jìn)行分詞吓蘑，進(jìn)行搜索

索引合并，使用多個(gè)單列索引組合搜索

覆蓋索引，select的數(shù)據(jù)列只用從索引中就能夠取得磨镶，不必讀取數(shù)據(jù)行溃蔫，換句話說(shuō)查詢列要被所建的索引覆蓋

建索引的幾大原則

一般來(lái)說(shuō)，在WHERE和JOIN中出現(xiàn)的列需要建立索引琳猫，但也不完全如此伟叛，因?yàn)镸ySQL只對(duì)，>=脐嫂，BETWEEN统刮，IN，以及某些時(shí)候的LIKE才會(huì)使用索引(mysql在使用like查詢的時(shí)候只有使用后面的%時(shí)账千，才會(huì)使用到索引侥蒙。)

1.最左前綴匹配原則，非常重要的原則匀奏，mysql會(huì)一直向右匹配直到遇到范圍查詢(>鞭衩、 3 and d = 4 如果建立(a,b,c,d)順序的索引，d是用不到索引的娃善，如果建立(a,b,d,c)的索引則都可以用到论衍，a,b,d的順序可以任意調(diào)整。

2.=和in可以亂序聚磺，比如a = 1 and b = 2 and c = 3 建立(a,b,c)索引可以任意順序坯台，mysql的查詢優(yōu)化器會(huì)幫你優(yōu)化成索引可以識(shí)別的形式。

3.盡量選擇區(qū)分度高的列作為索引瘫寝，區(qū)分度的公式是count(distinct col)/count(*)蜒蕾，表示字段不重復(fù)的比例，比例越大我們掃描的記錄數(shù)越少矢沿，唯一鍵的區(qū)分度是1滥搭，而一些狀態(tài)、性別字段可能在大數(shù)據(jù)面前區(qū)分度就是0捣鲸，那可能有人會(huì)問(wèn)瑟匆，這個(gè)比例有什么經(jīng)驗(yàn)值嗎？使用場(chǎng)景不同栽惶，這個(gè)值也很難確定愁溜，一般需要join的字段我們都要求是0.1以上，即平均1條掃描10條記錄外厂。

4.索引列不能參與計(jì)算冕象，保持列“干凈”，比如from_unixtime(create_time) = ’2014-05-29’就不能使用到索引汁蝶，原因很簡(jiǎn)單渐扮，b+樹(shù)中存的都是數(shù)據(jù)表中的字段值论悴，但進(jìn)行檢索時(shí)，需要把所有元素都應(yīng)用函數(shù)才能比較墓律，顯然成本太大膀估。所以語(yǔ)句應(yīng)該寫(xiě)成create_time = unix_timestamp(’2014-05-29’)。

5.盡量的擴(kuò)展索引耻讽，不要新建索引察纯。比如表中已經(jīng)有a的索引，現(xiàn)在要加(a,b)的索引针肥，那么只需要修改原來(lái)的索引即可饼记。

1.MySQL數(shù)據(jù)庫(kù)作發(fā)布系統(tǒng)的存儲(chǔ)，一天五萬(wàn)條以上的增量慰枕，預(yù)計(jì)運(yùn)維三年,怎么優(yōu)化具则？

a. 設(shè)計(jì)良好的數(shù)據(jù)庫(kù)結(jié)構(gòu)，允許部分?jǐn)?shù)據(jù)冗余捺僻，盡量避免join查詢乡洼，提高效率。

b. 選擇合適的表字段數(shù)據(jù)類(lèi)型和存儲(chǔ)引擎匕坯，適當(dāng)?shù)奶砑铀饕?/p>

c. mysql庫(kù)主從讀寫(xiě)分離束昵。

d. 找規(guī)律分表，減少單表中的數(shù)據(jù)量提高查詢速度葛峻。

e锹雏。添加緩存機(jī)制，比如memcached术奖，apc等礁遵。

f. 不經(jīng)常改動(dòng)的頁(yè)面，生成靜態(tài)頁(yè)面采记。

g. 書(shū)寫(xiě)高效率的SQL佣耐。比如 SELECT * FROM TABEL 改為 SELECT field_1, field_2, field_3 FROM TABLE.

2.實(shí)踐中如何優(yōu)化MySQL

最好是按照以下順序優(yōu)化：

1.SQL語(yǔ)句及索引的優(yōu)化

2. 數(shù)據(jù)庫(kù)表結(jié)構(gòu)的優(yōu)化

3.系統(tǒng)配置的優(yōu)化

4.硬件的優(yōu)化

3.優(yōu)化數(shù)據(jù)庫(kù)的方法

選取最適用的字段屬性，盡可能減少定義字段寬度唧龄，盡量把字段設(shè)置NOTNULL兼砖，例如’省份’、’性別’最好適用ENUM

使用連接(JOIN)來(lái)代替子查詢

適用聯(lián)合(UNION)來(lái)代替手動(dòng)創(chuàng)建的臨時(shí)表

事務(wù)處理

鎖定表既棺、優(yōu)化事務(wù)處理

適用外鍵讽挟，優(yōu)化鎖定表

建立索引

優(yōu)化查詢語(yǔ)句

4.如何通俗地理解三個(gè)范式？?

答：第一范式：1NF是對(duì)屬性的原子性約束丸冕，要求屬性具有原子性耽梅，不可再分解；

第二范式：2NF是對(duì)記錄的惟一性約束胖烛，要求記錄有惟一標(biāo)識(shí)眼姐，即實(shí)體的惟一性诅迷；?

第三范式：3NF是對(duì)字段冗余性的約束，即任何字段不能由其他字段派生出來(lái)妥凳，它要求字段沒(méi)有冗余竟贯。答捕。

范式化設(shè)計(jì)優(yōu)缺點(diǎn):

優(yōu)點(diǎn):

可以盡量得減少數(shù)據(jù)冗余逝钥，使得更新快，體積小

缺點(diǎn):對(duì)于查詢需要多個(gè)表進(jìn)行關(guān)聯(lián)拱镐，減少寫(xiě)得效率增加讀得效率艘款，更難進(jìn)行索引優(yōu)化

反范式化:

優(yōu)點(diǎn):可以減少表得關(guān)聯(lián)，可以更好得進(jìn)行索引優(yōu)化

缺點(diǎn):數(shù)據(jù)冗余以及數(shù)據(jù)異常沃琅，數(shù)據(jù)得修改需要更多的成本

5.說(shuō)說(shuō)對(duì)SQL語(yǔ)句優(yōu)化有哪些方法哗咆？（選擇幾條）

（1）Where子句中：where表之間的連接必須寫(xiě)在其他Where條件之前，那些可以過(guò)濾掉最大數(shù)量記錄的條件必須寫(xiě)在Where子句的末尾.HAVING最后益眉。

（2）用EXISTS替代IN晌柬、用NOT EXISTS替代NOT IN。

（3） 避免在索引列上使用計(jì)算

（4）避免在索引列上使用IS NULL和IS NOT NULL

（5）對(duì)查詢進(jìn)行優(yōu)化郭脂，應(yīng)盡量避免全表掃描年碘，首先應(yīng)考慮在 where 及 order by 涉及的列上建立索引。

（6）應(yīng)盡量避免在 where 子句中對(duì)字段進(jìn)行 null 值判斷展鸡，否則將導(dǎo)致引擎放棄使用索引而進(jìn)行全表掃描

（7）應(yīng)盡量避免在 where 子句中對(duì)字段進(jìn)行表達(dá)式操作屿衅，這將導(dǎo)致引擎放棄使用索引而進(jìn)行全表掃描

6、鎖

表鎖：開(kāi)銷(xiāo)小莹弊，加鎖快涤久；不會(huì)出現(xiàn)死鎖；鎖定力度大忍弛，發(fā)生鎖沖突概率高响迂，并發(fā)度最低 行鎖：

行鎖：開(kāi)銷(xiāo)大，加鎖慢细疚；會(huì)出現(xiàn)死鎖蔗彤；鎖定粒度小，發(fā)生鎖沖突的概率低惠昔，并發(fā)度高 頁(yè)鎖：

MyISAM的表級(jí)鎖有兩種模式：表共享讀鎖和表獨(dú)占寫(xiě)鎖幕与，在兼容性方面，除了讀鎖與讀鎖之間互相兼容之外镇防，其余互不兼容啦鸣。此外，MyISAM表的讀操作與寫(xiě)操作之間来氧，以及寫(xiě)操作之間是串行的诫给。

InnoDB實(shí)現(xiàn)了以下類(lèi)型的行鎖香拉，

1）共享鎖（S）：允許一個(gè)事務(wù)去讀一行，阻止其他事務(wù)獲得相同數(shù)據(jù)集的排他鎖中狂。

2） 排他鎖（X）：允許獲得排他鎖的事務(wù)更新數(shù)據(jù)凫碌，阻止其他事務(wù)取得相同數(shù)據(jù)集的共享讀鎖和排他寫(xiě)鎖。

另外胃榕，為了允許行鎖和表鎖共存盛险，實(shí)現(xiàn)多粒度鎖機(jī)制，InnoDB還有兩種內(nèi)部使用的意向鎖（Intention Locks）勋又，這兩種意向鎖都是表鎖苦掘。

? ? 1）意向共享鎖（IS）：事務(wù)打算給數(shù)據(jù)行加行共享鎖，事務(wù)在給一個(gè)數(shù)據(jù)行加共享鎖前必須先取得該表的IS鎖楔壤。

? ? 2）意向排他鎖（IX）：事務(wù)打算給數(shù)據(jù)行加行排他鎖鹤啡，事務(wù)在給一個(gè)數(shù)據(jù)行加排他鎖前必須先取得該表的IX鎖。