建站
咨詢
售后
建站咨詢
新聞中心
mysql有幾種索引類型？使用索引時(shí)都有那些地方要注意？sql優(yōu)化原則是什么？
mysql的索引類型及使用索引時(shí)的注意事項(xiàng)有：
站在用戶的角度思考問(wèn)題，與客戶深入溝通，找到丁青網(wǎng)站設(shè)計(jì)與丁青網(wǎng)站推廣的解決方案，憑借多年的經(jīng)驗(yàn)，讓設(shè)計(jì)與互聯(lián)網(wǎng)技術(shù)結(jié)合，創(chuàng)造個(gè)性化、用戶體驗(yàn)好的作品，建站類型包括：成都網(wǎng)站設(shè)計(jì)、網(wǎng)站制作、企業(yè)官網(wǎng)、英文網(wǎng)站、手機(jī)端網(wǎng)站、網(wǎng)站推廣、國(guó)際域名空間、虛擬主機(jī)、企業(yè)郵箱。業(yè)務(wù)覆蓋丁青地區(qū)。
一、普通索引。這是最基本的索引，它沒(méi)有任何限制。它有以下幾種創(chuàng)建方式：
1、創(chuàng)建索引
代碼如下:
CREATE INDEX indexName ON mytable(username(length));
如果是CHAR，VARCHAR類型，length可以小于字段實(shí)際長(zhǎng)度；如果是BLOB和TEXT類型，必須指定 length，下同。
2、修改表結(jié)構(gòu)
代碼如下:
ALTER mytable ADD INDEX [indexName] ON (username(length)) -- 創(chuàng)建表的時(shí)候直接指定
CREATE TABLE mytable(?? ID INT NOT NULL,??? username VARCHAR(16) NOT NULL,?? INDEX [indexName] (username(length))?? );?
-- 刪除索引的語(yǔ)法：
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)建表的時(shí)候直接指定
CREATE TABLE mytable(?? ID INT NOT NULL,??? username VARCHAR(16) NOT NULL,?? UNIQUE [indexName] (username(length))?? );
三、主鍵索引。它是一種特殊的唯一索引，不允許有空值。一般是在建表的時(shí)候同時(shí)創(chuàng)建主鍵索引：
代碼如下:
CREATE TABLE mytable(?? ID INT NOT NULL,??? username VARCHAR(16) NOT NULL,?? PRIMARY KEY(ID)?? );
當(dāng)然也可以用 ALTER 命令。記?。阂粋€(gè)表只能有一個(gè)主鍵。
四、組合索引。為了形象地對(duì)比單列索引和組合索引，為表添加多個(gè)字段：
代碼如下:
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建到一個(gè)索引里：
代碼如下:
ALTER TABLE mytable ADD INDEX name_city_age (name(10),city,age);[code]
建表時(shí)，usernname長(zhǎng)度為 16，這里用 10。這是因?yàn)橐话闱闆r下名字的長(zhǎng)度不會(huì)超過(guò)10，這樣會(huì)加速索引查詢速度，還會(huì)減少索引文件的大小，提高INSERT的更新速度。
如果分別在 usernname，city，age上建立單列索引，讓該表有3個(gè)單列索引，查詢時(shí)和上述的組合索引效率也會(huì)大不一樣，遠(yuǎn)遠(yuǎn)低于我們的組合索引。雖然此時(shí)有了三個(gè)索引，但MySQL只能用到其中的那個(gè)它認(rèn)為似乎是最有效率的單列索引。
建立這樣的組合索引，其實(shí)是相當(dāng)于分別建立了下面三組組合索引：usernname,city,age?? usernname,city?? usernname? 為什么沒(méi)有 city，age這樣的組合索引呢？這是因?yàn)镸ySQL組合索引“最左前綴”的結(jié)果。簡(jiǎn)單的理解就是只從最左面的開(kāi)始組合。并不是只要包含這三列的查詢都會(huì)用到該組合索引，下面的幾個(gè)SQL就會(huì)用到這個(gè)組合索引：
[code]
SELECT * FROM mytable WHREE username="admin" AND city="鄭州"? SELECT * FROM mytable WHREE username="admin"
mysql 復(fù)合 索引 怎么選擇
利用索引中的附加列，您可以縮小搜索的范圍，但使用一個(gè)具有兩列的索引 不同于使用兩個(gè)單獨(dú)的索引。復(fù)合索引的結(jié)構(gòu)與電話簿類似，人名由姓和名構(gòu)成，電話簿首先按姓氏對(duì)進(jìn)行排序，然后按名字對(duì)有相同姓氏的人進(jìn)行排序。如果您知 道姓，電話簿將非常有用；如果您知道姓和名，電話簿則更為有用，但如果您只知道名不姓，電話簿將沒(méi)有用處。
所以說(shuō)創(chuàng)建復(fù)合索引時(shí)，應(yīng)該仔細(xì)考慮列的順序。對(duì)索引中的所有列執(zhí)行搜索或僅對(duì)前幾列執(zhí)行搜索時(shí)，復(fù)合索引非常有用；僅對(duì)后面的任意列執(zhí)行搜索時(shí)，復(fù)合索引則沒(méi)有用處。
如：建立 姓名、年齡、性別的復(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)化選擇最佳索引
我個(gè)人建議用explain extended先得到一條簡(jiǎn)短的語(yǔ)句，然后用explain看看你的訪問(wèn)類型，根據(jù)explain進(jìn)行索引添加，然后在執(zhí)行語(yǔ)句。用show status like 'Handler_read%';查看你的索引使用情況，Handler_read_rnd_next很高意味著查詢效率低下，說(shuō)明你要換一個(gè)索引值。然后用show profile看看你的線程到底在哪里消耗的開(kāi)銷比較大。
MySql 索引（聚集索引，輔助索引，聯(lián)合索引，覆蓋索引..）
引入一個(gè)面試問(wèn)題:
看完以下以后再回顧，會(huì)發(fā)現(xiàn)迎刃而解
Mysql 可以為每一張表設(shè)置 存儲(chǔ)引擎 這里我們只說(shuō) InnoDB 存儲(chǔ)引擎.
由于實(shí)際情況，數(shù)據(jù)頁(yè)只能按照一棵 B+樹(shù) 進(jìn)行排序， 因此每張表只能擁有一個(gè) 聚集索引（即 主鍵）。
栗子：
每個(gè)葉子節(jié)點(diǎn)的索引行中包含了一個(gè)書(shū)簽（bookmark）. 該書(shū)簽是用來(lái)告訴 InnoDB存儲(chǔ)引擎哪里可以找到該索引對(duì)應(yīng)的數(shù)據(jù)行或者說(shuō) 行數(shù)據(jù)！ 由于InnoDB存儲(chǔ)引擎表， 是按照主鍵來(lái)構(gòu)建的， 所以 ，該書(shū)簽內(nèi)其實(shí)包含或者說(shuō)指向了 數(shù)據(jù)行所對(duì)應(yīng)的聚集索引鍵
也就是說(shuō) 輔助索引的 葉結(jié)點(diǎn)保存了 指向?qū)?yīng)數(shù)據(jù)的 聚集索引， 可以通過(guò)該聚集索引 找到對(duì)應(yīng)的數(shù)據(jù)行
輔助索引的存在并不影響數(shù)據(jù)在聚集索引中的組織，因?yàn)槊繌埍砩峡梢杂卸鄠€(gè)輔助索引。
當(dāng)通過(guò)輔助索引來(lái)尋找數(shù)據(jù)時(shí)，InnoDB 存儲(chǔ)引擎會(huì)遍歷輔助索引并通過(guò)葉級(jí)別的指針獲得指向主鍵索引(聚集索引)的主鍵，然后再通過(guò)聚集索引找到一個(gè)完整的數(shù)據(jù)行。
例如：
聚集索引輔助索引關(guān)系:
： 又叫做組合索引 ， 輔助索引的一種 ， 和普通創(chuàng)建索引的方式一樣，不同的是 可以同時(shí)添加多列來(lái)作為索引項(xiàng);
從本質(zhì)上來(lái)說(shuō)，聯(lián)合索引也是一課B+樹(shù)
個(gè)人理解： 所謂最左原則， 是因?yàn)?存儲(chǔ)引擎構(gòu)建組合索引時(shí) 是根據(jù)最左邊的那一列索引項(xiàng)進(jìn)行排序的 ，所以使用組合索引，必須滿足 條件中必須存在 最左邊那一列的索引項(xiàng)，這樣 才可以找到對(duì)應(yīng)的索引，繼而 去尋找對(duì)應(yīng)的數(shù)據(jù)
： 又叫做 索引覆蓋，InnoDB中支持覆蓋索引，即 從輔助索引中就可以得到查詢的記錄，而不需要查詢聚集索引中的記錄。
比如 這里沒(méi)有根據(jù)最左原則使用組合索引，但是 優(yōu)化器依然進(jìn)行選擇
共勉，歡迎指導(dǎo)謝謝~
mysql建索引的幾大原則
1．選擇唯一性索引
唯一性索引的值是唯一的，可以更快速的通過(guò)該索引來(lái)確定某條記錄。例如，學(xué)生表中學(xué)號(hào)是具有唯一性的字段。為該字段建立唯一性索引可以很快的確定某個(gè)學(xué)生的信息。如果使用姓名的話，可能存在同名現(xiàn)象，從而降低查詢速度。
2．為經(jīng)常需要排序、分組和聯(lián)合操作的字段建立索引
經(jīng)常需要ORDER BY、GROUP BY、DISTINCT和UNION等操作的字段，排序操作會(huì)浪費(fèi)很多時(shí)間。如果為其建立索引，可以有效地避免排序操作。
3．為常作為查詢條件的字段建立索引
如果某個(gè)字段經(jīng)常用來(lái)做查詢條件，那么該字段的查詢速度會(huì)影響整個(gè)表的查詢速度。因此，為這樣的字段建立索引，可以提高整個(gè)表的查詢速度。
4．限制索引的數(shù)目
索引的數(shù)目不是越多越好。每個(gè)索引都需要占用磁盤(pán)空間，索引越多，需要的磁盤(pán)空間就越大。修改表時(shí)，對(duì)索引的重構(gòu)和更新很麻煩。越多的索引，會(huì)使更新表變得很浪費(fèi)時(shí)間。
5．盡量使用數(shù)據(jù)量少的索引
如果索引的值很長(zhǎng)，那么查詢的速度會(huì)受到影響。例如，對(duì)一個(gè)CHAR(100)類型的字段進(jìn)行全文檢索需要的時(shí)間肯定要比對(duì)CHAR(10)類型的字段需要的時(shí)間要多。
6．盡量使用前綴來(lái)索引
如果索引字段的值很長(zhǎng)，最好使用值的前綴來(lái)索引。例如，TEXT和BLOG類型的字段，進(jìn)行全文檢索會(huì)很浪費(fèi)時(shí)間。如果只檢索字段的前面的若干個(gè)字符，這樣可以提高檢索速度。
7．刪除不再使用或者很少使用的索引
表中的數(shù)據(jù)被大量更新，或者數(shù)據(jù)的使用方式被改變后，原有的一些索引可能不再需要。數(shù)據(jù)庫(kù)管理員應(yīng)當(dāng)定期找出這些索引，將它們刪除，從而減少索引對(duì)更新操作的影響。
8 . 最左前綴匹配原則，非常重要的原則。
mysql會(huì)一直向右匹配直到遇到范圍查詢(、、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)化器會(huì)幫你優(yōu)化成索引可以識(shí)別的形式
。
10 . 盡量選擇區(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條 記錄
11 .索引列不能參與計(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’);
12 .盡量的擴(kuò)展索引，不要新建索引。
比如表中已經(jīng)有a的索引，現(xiàn)在要加(a,b)的索引，那么只需要修改原來(lái)的索引即可
注意：選擇索引的最終目的是為了使查詢的速度變快。上面給出的原則是最基本的準(zhǔn)則，但不能拘泥于上面的準(zhǔn)則。讀者要在以后的學(xué)習(xí)和工作中進(jìn)行不斷的實(shí)踐。根據(jù)應(yīng)用的實(shí)際情況進(jìn)行分析和判斷，選擇最合適的索引方式。
網(wǎng)站欄目：mysql怎么選擇索引項(xiàng) mysql索引選擇器
URL網(wǎng)址：http://www.dlmjj.cn/article/hjedpj.html
