mysql有幾種索引類型？使用索引時都有那些地方要注意？sql優(yōu)化原則是什么？

mysql的索引類型及使用索引時的注意事項(xiàng)有：

站在用戶的角度思考問題，與客戶深入溝通，找到丁青網(wǎng)站設(shè)計(jì)與丁青網(wǎng)站推廣的解決方案，憑借多年的經(jīng)驗(yàn)，讓設(shè)計(jì)與互聯(lián)網(wǎng)技術(shù)結(jié)合，創(chuàng)造個性化、用戶體驗(yàn)好的作品，建站類型包括：成都網(wǎng)站設(shè)計(jì)、網(wǎng)站制作、企業(yè)官網(wǎng)、英文網(wǎng)站、手機(jī)端網(wǎng)站、網(wǎng)站推廣、國際域名空間、虛擬主機(jī)、企業(yè)郵箱。業(yè)務(wù)覆蓋丁青地區(qū)。

一、普通索引。這是最基本的索引，它沒有任何限制。它有以下幾種創(chuàng)建方式：

1、創(chuàng)建索引

代碼如下:

CREATE INDEX indexName ON mytable(username(length));

如果是CHAR，VARCHAR類型，length可以小于字段實(shí)際長度；如果是BLOB和TEXT類型，必須指定 length，下同。

2、修改表結(jié)構(gòu)

代碼如下:

ALTER mytable ADD INDEX [indexName] ON (username(length)) -- 創(chuàng)建表的時候直接指定

CREATE TABLE mytable(?? ID INT NOT NULL,??? username VARCHAR(16) NOT NULL,?? INDEX [indexName] (username(length))?? );?

-- 刪除索引的語法：

DROP INDEX [indexName] ON mytable;

二、唯一索引。它與前面的普通索引類似，不同的就是：索引列的值必須唯一，但允許有空值。如果是組合索引，則列值的組合必須唯一。它有以下幾種創(chuàng)建方式：

代碼如下:

CREATE UNIQUE INDEX indexName ON mytable(username(length))

-- 修改表結(jié)構(gòu)

ALTER mytable ADD UNIQUE [indexName] ON (username(length))

-- 創(chuàng)建表的時候直接指定

CREATE TABLE mytable(?? ID INT NOT NULL,??? username VARCHAR(16) NOT NULL,?? UNIQUE [indexName] (username(length))?? );

三、主鍵索引。它是一種特殊的唯一索引，不允許有空值。一般是在建表的時候同時創(chuàng)建主鍵索引：

代碼如下:

CREATE TABLE mytable(?? ID INT NOT NULL,??? username VARCHAR(16) NOT NULL,?? PRIMARY KEY(ID)?? );

當(dāng)然也可以用 ALTER 命令。記?。阂粋€表只能有一個主鍵。

四、組合索引。為了形象地對比單列索引和組合索引，為表添加多個字段：

代碼如下:

CREATE TABLE mytable(?? ID INT NOT NULL,??? username VARCHAR(16) NOT NULL,?? city VARCHAR(50) NOT NULL,?? age INT NOT NULL? );

為了進(jìn)一步榨取MySQL的效率，就要考慮建立組合索引。就是將 name, city, age建到一個索引里：

代碼如下:

ALTER TABLE mytable ADD INDEX name_city_age (name(10),city,age);[code]

建表時，usernname長度為 16，這里用 10。這是因?yàn)橐话闱闆r下名字的長度不會超過10，這樣會加速索引查詢速度，還會減少索引文件的大小，提高INSERT的更新速度。

如果分別在 usernname，city，age上建立單列索引，讓該表有3個單列索引，查詢時和上述的組合索引效率也會大不一樣，遠(yuǎn)遠(yuǎn)低于我們的組合索引。雖然此時有了三個索引，但MySQL只能用到其中的那個它認(rèn)為似乎是最有效率的單列索引。

建立這樣的組合索引，其實(shí)是相當(dāng)于分別建立了下面三組組合索引：usernname,city,age?? usernname,city?? usernname? 為什么沒有 city，age這樣的組合索引呢？這是因?yàn)镸ySQL組合索引“最左前綴”的結(jié)果。簡單的理解就是只從最左面的開始組合。并不是只要包含這三列的查詢都會用到該組合索引，下面的幾個SQL就會用到這個組合索引：

[code]

SELECT * FROM mytable WHREE username="admin" AND city="鄭州"? SELECT * FROM mytable WHREE username="admin"

mysql 復(fù)合 索引 怎么選擇

利用索引中的附加列，您可以縮小搜索的范圍，但使用一個具有兩列的索引 不同于使用兩個單獨(dú)的索引。復(fù)合索引的結(jié)構(gòu)與電話簿類似，人名由姓和名構(gòu)成，電話簿首先按姓氏對進(jìn)行排序，然后按名字對有相同姓氏的人進(jìn)行排序。如果您知 道姓，電話簿將非常有用；如果您知道姓和名，電話簿則更為有用，但如果您只知道名不姓，電話簿將沒有用處。

所以說創(chuàng)建復(fù)合索引時，應(yīng)該仔細(xì)考慮列的順序。對索引中的所有列執(zhí)行搜索或僅對前幾列執(zhí)行搜索時，復(fù)合索引非常有用；僅對后面的任意列執(zhí)行搜索時，復(fù)合索引則沒有用處。

如：建立 姓名、年齡、性別的復(fù)合索引。

create table test（

a int,

b int,

c int,

KEY a（a,b,c）

）；

優(yōu)： select * from test where a=10 a

如何為Mysql優(yōu)化選擇最佳索引

我個人建議用explain extended先得到一條簡短的語句，然后用explain看看你的訪問類型，根據(jù)explain進(jìn)行索引添加，然后在執(zhí)行語句。用show status like 'Handler_read%';查看你的索引使用情況，Handler_read_rnd_next很高意味著查詢效率低下，說明你要換一個索引值。然后用show profile看看你的線程到底在哪里消耗的開銷比較大。

MySql 索引（聚集索引，輔助索引，聯(lián)合索引，覆蓋索引..）

引入一個面試問題:

看完以下以后再回顧，會發(fā)現(xiàn)迎刃而解

Mysql 可以為每一張表設(shè)置 存儲引擎 這里我們只說 InnoDB 存儲引擎.

由于實(shí)際情況，數(shù)據(jù)頁只能按照一棵 B+樹 進(jìn)行排序， 因此每張表只能擁有一個 聚集索引（即 主鍵）。

栗子：

每個葉子節(jié)點(diǎn)的索引行中包含了一個書簽（bookmark）. 該書簽是用來告訴 InnoDB存儲引擎哪里可以找到該索引對應(yīng)的數(shù)據(jù)行或者說 行數(shù)據(jù)！ 由于InnoDB存儲引擎表， 是按照主鍵來構(gòu)建的， 所以 ，該書簽內(nèi)其實(shí)包含或者說指向了 數(shù)據(jù)行所對應(yīng)的聚集索引鍵

也就是說 輔助索引的 葉結(jié)點(diǎn)保存了 指向?qū)?yīng)數(shù)據(jù)的 聚集索引， 可以通過該聚集索引 找到對應(yīng)的數(shù)據(jù)行

輔助索引的存在并不影響數(shù)據(jù)在聚集索引中的組織，因?yàn)槊繌埍砩峡梢杂卸鄠€輔助索引。

當(dāng)通過輔助索引來尋找數(shù)據(jù)時，InnoDB 存儲引擎會遍歷輔助索引并通過葉級別的指針獲得指向主鍵索引(聚集索引)的主鍵，然后再通過聚集索引找到一個完整的數(shù)據(jù)行。

例如：

聚集索引輔助索引關(guān)系:

： 又叫做組合索引 ， 輔助索引的一種 ， 和普通創(chuàng)建索引的方式一樣，不同的是 可以同時添加多列來作為索引項(xiàng);

從本質(zhì)上來說，聯(lián)合索引也是一課B+樹

個人理解： 所謂最左原則， 是因?yàn)?存儲引擎構(gòu)建組合索引時 是根據(jù)最左邊的那一列索引項(xiàng)進(jìn)行排序的 ，所以使用組合索引，必須滿足 條件中必須存在 最左邊那一列的索引項(xiàng)，這樣 才可以找到對應(yīng)的索引，繼而 去尋找對應(yīng)的數(shù)據(jù)

： 又叫做 索引覆蓋，InnoDB中支持覆蓋索引，即 從輔助索引中就可以得到查詢的記錄，而不需要查詢聚集索引中的記錄。

比如 這里沒有根據(jù)最左原則使用組合索引，但是 優(yōu)化器依然進(jìn)行選擇

共勉，歡迎指導(dǎo)謝謝~

mysql建索引的幾大原則

1．選擇唯一性索引

唯一性索引的值是唯一的，可以更快速的通過該索引來確定某條記錄。例如，學(xué)生表中學(xué)號是具有唯一性的字段。為該字段建立唯一性索引可以很快的確定某個學(xué)生的信息。如果使用姓名的話，可能存在同名現(xiàn)象，從而降低查詢速度。

2．為經(jīng)常需要排序、分組和聯(lián)合操作的字段建立索引

經(jīng)常需要ORDER BY、GROUP BY、DISTINCT和UNION等操作的字段，排序操作會浪費(fèi)很多時間。如果為其建立索引，可以有效地避免排序操作。

3．為常作為查詢條件的字段建立索引

如果某個字段經(jīng)常用來做查詢條件，那么該字段的查詢速度會影響整個表的查詢速度。因此，為這樣的字段建立索引，可以提高整個表的查詢速度。

4．限制索引的數(shù)目

索引的數(shù)目不是越多越好。每個索引都需要占用磁盤空間，索引越多，需要的磁盤空間就越大。修改表時，對索引的重構(gòu)和更新很麻煩。越多的索引，會使更新表變得很浪費(fèi)時間。

5．盡量使用數(shù)據(jù)量少的索引

如果索引的值很長，那么查詢的速度會受到影響。例如，對一個CHAR(100)類型的字段進(jìn)行全文檢索需要的時間肯定要比對CHAR(10)類型的字段需要的時間要多。

6．盡量使用前綴來索引

如果索引字段的值很長，最好使用值的前綴來索引。例如，TEXT和BLOG類型的字段，進(jìn)行全文檢索會很浪費(fèi)時間。如果只檢索字段的前面的若干個字符，這樣可以提高檢索速度。

7．刪除不再使用或者很少使用的索引

表中的數(shù)據(jù)被大量更新，或者數(shù)據(jù)的使用方式被改變后，原有的一些索引可能不再需要。數(shù)據(jù)庫管理員應(yīng)當(dāng)定期找出這些索引，將它們刪除，從而減少索引對更新操作的影響。

8 . 最左前綴匹配原則，非常重要的原則。

mysql會一直向右匹配直到遇到范圍查詢(、、between、like)就停止匹配，比如a 1=”” and=”” b=”2” c=”“ 3 and d = 4 如果建立(a,b,c,d)順序的索引，d是用不到索引的，如果建立(a,b,d,c)的索引則都可以用到，a,b,d的順序可以任意調(diào)整。

9 .=和in可以亂序。

比如a = 1 and b = 2 and c = 3 建立(a,b,c)索引可以任意順序，mysql的查詢優(yōu)化器會幫你優(yōu)化成索引可以識別的形式

。

10 . 盡量選擇區(qū)分度高的列作為索引。

區(qū)分度的公式是count(distinct col)/count(*)，表示字段不重復(fù)的比例，比例越大我們掃描的記錄數(shù)越少，唯一鍵的區(qū)分度是1，而一些狀態(tài)、性別字段可能在大數(shù)據(jù)面前區(qū)分度就 是0，那可能有人會問，這個比例有什么經(jīng)驗(yàn)值嗎？使用場景不同，這個值也很難確定，一般需要join的字段我們都要求是0.1以上，即平均1條掃描10條 記錄

11 .索引列不能參與計(jì)算，保持列“干凈”。

比如from_unixtime(create_time) = ’2014-05-29’就不能使用到索引，原因很簡單，b+樹中存的都是數(shù)據(jù)表中的字段值，但進(jìn)行檢索時，需要把所有元素都應(yīng)用函數(shù)才能比較，顯然成本 太大。所以語句應(yīng)該寫成create_time = unix_timestamp(’2014-05-29’);

12 .盡量的擴(kuò)展索引，不要新建索引。

比如表中已經(jīng)有a的索引，現(xiàn)在要加(a,b)的索引，那么只需要修改原來的索引即可

注意：選擇索引的最終目的是為了使查詢的速度變快。上面給出的原則是最基本的準(zhǔn)則，但不能拘泥于上面的準(zhǔn)則。讀者要在以后的學(xué)習(xí)和工作中進(jìn)行不斷的實(shí)踐。根據(jù)應(yīng)用的實(shí)際情況進(jìn)行分析和判斷，選擇最合適的索引方式。