mysql數(shù)據(jù)庫(kù)索引是一種能夠讓mysql數(shù)據(jù)查詢更加快速的數(shù)據(jù)結(jié)構(gòu)，我們?cè)谛陆〝?shù)據(jù)庫(kù)的時(shí)候，如果設(shè)置了某個(gè)字段的Primary key主鍵，那么數(shù)據(jù)庫(kù)會(huì)默認(rèn)為我們的主鍵字段創(chuàng)建一個(gè)唯一索引(Unique Index)的東西，所以你就不需要再為此字段創(chuàng)建mysql數(shù)據(jù)庫(kù)索引了，當(dāng)然了，如果你想設(shè)置別的字段索引，那么就要額外加入該字段的數(shù)據(jù)庫(kù)索引了。
為永寧等地區(qū)用戶提供了全套網(wǎng)頁(yè)設(shè)計(jì)制作服務(wù)，及永寧網(wǎng)站建設(shè)行業(yè)解決方案。主營(yíng)業(yè)務(wù)為網(wǎng)站制作、成都做網(wǎng)站、永寧網(wǎng)站設(shè)計(jì)，以傳統(tǒng)方式定制建設(shè)網(wǎng)站，并提供域名空間備案等一條龍服務(wù)，秉承以專業(yè)、用心的態(tài)度為用戶提供真誠(chéng)的服務(wù)。我們深信只要達(dá)到每一位用戶的要求，就會(huì)得到認(rèn)可，從而選擇與我們長(zhǎng)期合作。這樣，我們也可以走得更遠(yuǎn)！
一：Mysql數(shù)據(jù)庫(kù)索引的創(chuàng)建
1)如何為我們的mysql數(shù)據(jù)庫(kù)添加索引呢？很簡(jiǎn)單，請(qǐng)看下面的創(chuàng)建mysql普通索引格式：
CREATE INDEX [index name] ON [table name]([column name]);
參數(shù)說(shuō)明：NameDescription
index name索引名稱.
table name需要添加索引的表名稱.
column name需要添加索引的列名稱.
例如我們要?jiǎng)?chuàng)建一個(gè)索引，就可以使用如下的mysql命令運(yùn)行即可：CREATE INDEX myindex ON mytable(aut_id);
2)唯一索引的創(chuàng)建，和mysql普通索引差不多，只需要在INDEX關(guān)鍵詞前面加入U(xiǎn)NIQUE關(guān)鍵詞即可，mysql命令如下：CREATE UNIQUE INDEX myindex ON mytable(aut_id);
我們也可以使用USING BTREE關(guān)鍵字，B-tree算法減少定位記錄時(shí)所經(jīng)歷的中間過(guò)程，從而加快存取速度，mysql命令如下：CREATE UNIQUE INDEX myindex ON mytable(aut_id) USING BTREE;
二：查看mysql數(shù)據(jù)庫(kù)索引
我們可以使用以下命令查詢剛剛新建的mysql數(shù)據(jù)庫(kù)索引，“from mytable”代表查詢?cè)摂?shù)據(jù)庫(kù)表里面有哪些索引，mysql命令如下：show index from mytable;
創(chuàng)建成功之后，會(huì)在“key_name”字段中顯示剛剛創(chuàng)建的數(shù)據(jù)庫(kù)索引名稱，如圖所示：
三：刪除mysql數(shù)據(jù)庫(kù)索引
我們也可以使用mysql命令刪除某張表的索引，命令如下：drop index english on mytable
四：查詢sql語(yǔ)句是否使用了mysql索引
我們上面常見好mysql數(shù)據(jù)庫(kù)索引之后，如果想要知道我們?cè)趫?zhí)行sql語(yǔ)句時(shí)是否使用了數(shù)據(jù)庫(kù)索引，就要在sql語(yǔ)句前面加入“EXPLAIN”關(guān)鍵詞，命令如下所示：EXPLAIN SELECT * FROM `allword` where english='America';
如果使用到了我們創(chuàng)建的字段索引，就會(huì)看到如圖所示的字段都不會(huì)是NULL空值，如下：
在有些情況下mysql索引會(huì)失效，也就是在執(zhí)行到sql語(yǔ)句時(shí)沒有使用到我們創(chuàng)建的數(shù)據(jù)庫(kù)表字段索引，可能有以下這些情況：
1：使用了OR關(guān)鍵字查詢
2：或者LOWER()，UPPER()函數(shù)，
3：還有一種就是使用了LIKE關(guān)鍵字查詢，像(like '%XX'或者like '%XX%')這樣的語(yǔ)句，但是像(like 'XX%')這種情況的mysql表索引是不會(huì)失效的。
說(shuō)明：不要以為唯一索引影響了 insert 速度，這個(gè)速度損耗可以忽略，但提高查找速度是明顯的。
某個(gè)字段在SELECT語(yǔ)句的 WHERE 條件中經(jīng)常被使用到，那么就需要給這個(gè)字段創(chuàng)建索引了。尤其是在
數(shù)據(jù)量大的情況下，創(chuàng)建普通索引就可以大幅提升數(shù)據(jù)查詢的效率。
比如student_info數(shù)據(jù)表（含100萬(wàn)條數(shù)據(jù)），假設(shè)我們想要查詢 student_id=123110 的用戶信息。
索引就是讓數(shù)據(jù)按照某種順序進(jìn)行存儲(chǔ)或檢索，因此當(dāng)我們使用 GROUP BY 對(duì)數(shù)據(jù)進(jìn)行分組查詢，或者
使用 ORDER BY 對(duì)數(shù)據(jù)進(jìn)行排序的時(shí)候，就需要 對(duì)分組或者排序的字段進(jìn)行索引 。如果待排序的列有多
個(gè)，那么可以在這些列上建立 組合索引 。
對(duì)數(shù)據(jù)按照某個(gè)條件進(jìn)行查詢后再進(jìn)行 UPDATE 或 DELETE 的操作，如果對(duì) WHERE 字段創(chuàng)建了索引，就
能大幅提升效率。原理是因?yàn)槲覀冃枰雀鶕?jù) WHERE 條件列檢索出來(lái)這條記錄，然后再對(duì)它進(jìn)行更新或
刪除。如果進(jìn)行更新的時(shí)候，更新的字段是非索引字段，提升的效率會(huì)更明顯，這是因?yàn)榉撬饕侄胃?/p>
新不需要對(duì)索引進(jìn)行維護(hù)。
有時(shí)候我們需要對(duì)某個(gè)字段進(jìn)行去重，使用 DISTINCT，那么對(duì)這個(gè)字段創(chuàng)建索引，也會(huì)提升查詢效率。
比如，我們想要查詢課程表中不同的 student_id 都有哪些，如果我們沒有對(duì) student_id 創(chuàng)建索引，執(zhí)行
SQL 語(yǔ)句：
運(yùn)行結(jié)果（600637 條記錄，運(yùn)行時(shí)間 0.683s ）：
如果我們對(duì) student_id 創(chuàng)建索引，再執(zhí)行 SQL 語(yǔ)句：
運(yùn)行結(jié)果（600637 條記錄，運(yùn)行時(shí)間 0.010s ）：
你能看到 SQL 查詢效率有了提升，同時(shí)顯示出來(lái)的 student_id 還是按照 遞增的順序 進(jìn)行展示的。這是因
為索引會(huì)對(duì)數(shù)據(jù)按照某種順序進(jìn)行排序，所以在去重的時(shí)候也會(huì)快很多。
首先， 連接表的數(shù)量盡量不要超過(guò) 3 張 ，因?yàn)槊吭黾右粡埍砭拖喈?dāng)于增加了一次嵌套的循環(huán)，數(shù)量級(jí)增
長(zhǎng)會(huì)非?？欤瑖?yán)重影響查詢的效率。
其次， 對(duì) WHERE 條件創(chuàng)建索引 ，因?yàn)?WHERE 才是對(duì)數(shù)據(jù)條件的過(guò)濾。如果在數(shù)據(jù)量非常大的情況下，
沒有 WHERE 條件過(guò)濾是非常可怕的。
最后， 對(duì)用于連接的字段創(chuàng)建索引 ，并且該字段在多張表中的 類型必須一致 。比如 course_id 在
student_info 表和 course 表中都為 int(11) 類型，而不能一個(gè)為 int 另一個(gè)為 varchar 類型。
舉個(gè)例子，如果我們只對(duì) student_id 創(chuàng)建索引，執(zhí)行 SQL 語(yǔ)句：
運(yùn)行結(jié)果（1 條數(shù)據(jù)，運(yùn)行時(shí)間 0.189s ）：
這里我們對(duì) name 創(chuàng)建索引，再執(zhí)行上面的 SQL 語(yǔ)句，運(yùn)行時(shí)間為 0.002s 。
創(chuàng)建一張商戶表，因?yàn)榈刂纷侄伪容^長(zhǎng)，在地址字段上建立前綴索引
問(wèn)題是，截取多少呢？截取得多了，達(dá)不到節(jié)省索引存儲(chǔ)空間的目的；截取得少了，重復(fù)內(nèi)容太多，字
段的散列度(選擇性)會(huì)降低。 怎么計(jì)算不同的長(zhǎng)度的選擇性呢？
先看一下字段在全部數(shù)據(jù)中的選擇度：
通過(guò)不同長(zhǎng)度去計(jì)算，與全表的選擇性對(duì)比：
公式：
例如：
引申另一個(gè)問(wèn)題：索引列前綴對(duì)排序的影響
拓展：Alibaba《Java開發(fā)手冊(cè)》
【 強(qiáng)制 】在 varchar 字段上建立索引時(shí)，必須指定索引長(zhǎng)度，沒必要對(duì)全字段建立索引，根據(jù)實(shí)際文本
區(qū)分度決定索引長(zhǎng)度。
說(shuō)明：索引的長(zhǎng)度與區(qū)分度是一對(duì)矛盾體，一般對(duì)字符串類型數(shù)據(jù)，長(zhǎng)度為 20 的索引，區(qū)分度會(huì) 高達(dá)
90% 以上 ，可以使用 count(distinct left(列名, 索引長(zhǎng)度))/count(*)的區(qū)分度來(lái)確定。
這樣也可以較少的建立一些索引。同時(shí)，由于"最左前綴原則"，可以增加聯(lián)合索引的使用率。
結(jié)論：在數(shù)據(jù)表中的數(shù)據(jù)行數(shù)比較少的情況下，比如不到 1000 行，是不需要?jiǎng)?chuàng)建索引的。
舉例1：要在 100 萬(wàn)行數(shù)據(jù)中查找其中的 50 萬(wàn)行（比如性別為男的數(shù)據(jù)），一旦創(chuàng)建了索引，你需要先
訪問(wèn) 50 萬(wàn)次索引，然后再訪問(wèn) 50 萬(wàn)次數(shù)據(jù)表，這樣加起來(lái)的開銷比不使用索引可能還要大。
舉例2：假設(shè)有一個(gè)學(xué)生表，學(xué)生總數(shù)為 100 萬(wàn)人，男性只有 10 個(gè)人，也就是占總?cè)丝诘?10 萬(wàn)分之 1。
學(xué)生表 student_gender 結(jié)構(gòu)如下。其中數(shù)據(jù)表中的 student_gender 字段取值為 0 或 1，0 代表女性，1 代
表男性。
如果我們要篩選出這個(gè)學(xué)生表中的男性，可以使用：
運(yùn)行結(jié)果（10 條數(shù)據(jù)，運(yùn)行時(shí)間 0.696s ）：
結(jié)論：當(dāng)數(shù)據(jù)重復(fù)度大，比如 高于 10% 的時(shí)候，也不需要對(duì)這個(gè)字段使用索引。
例如身份證、UUID(在索引比較時(shí)需要轉(zhuǎn)為ASCII，并且插入時(shí)可能造成頁(yè)分裂)、MD5、HASH、無(wú)序長(zhǎng)字
符串等。
① 冗余索引
舉例：建表語(yǔ)句如下
我們知道，通過(guò) idx_name_birthday_phone_number 索引就可以對(duì) name 列進(jìn)行快速搜索，再創(chuàng)建一
個(gè)專門針對(duì) name 列的索引就算是一個(gè) 冗余索引 ，維護(hù)這個(gè)索引只會(huì)增加維護(hù)的成本，并不會(huì)對(duì)搜索有
什么好處。
② 重復(fù)索引
另一種情況，我們可能會(huì)對(duì)某個(gè)列 重復(fù)建立索引 ，比方說(shuō)這樣：
我們看到，col1 既是主鍵、又給它定義為一個(gè)唯一索引，還給它定義了一個(gè)普通索引，可是主鍵本身就
會(huì)生成聚簇索引，所以定義的唯一索引和普通索引是重復(fù)的，這種情況要避免。
歡迎共同進(jìn)步：
QQ群：1007576722
建立索引，要使用離散度（選擇度）更高的字段。
我們先來(lái)看一個(gè)重要的屬性列的 離散度，
count(distinct(column_name)) : count(*) -- 列的全部不同值個(gè)數(shù)：所有數(shù)據(jù)行行數(shù)
數(shù)據(jù)行數(shù)相同的情況下，分子越大，列的離散度就越高。簡(jiǎn)單來(lái)說(shuō)，如果列的重復(fù)值越多，離散度就越低，重復(fù)值越少，離散度就越高。
當(dāng)字段值比較長(zhǎng)的時(shí)候，建立索引會(huì)消耗很多的空間，搜索起來(lái)也會(huì)很慢。我們可以通過(guò)截取字段的前面一部分內(nèi)容建立索引，這個(gè)就叫前綴索引。
創(chuàng)建一張商戶表，因?yàn)榈刂纷侄伪容^長(zhǎng)，在地址字段上建立前綴索引
create table shop(address varchar(120) not null);
alter table shop add key(address(12));? // 截取12個(gè)字符作為前綴索引是最優(yōu)的嗎？
問(wèn)題是，截取多少呢？截取得多了，達(dá)不到節(jié)省索引存儲(chǔ)空間的目的，截取得少了，重復(fù)內(nèi)容太多，字段的散列度（選擇性）會(huì)降低。怎么計(jì)算不同的長(zhǎng)度的選擇性呢？
先看一下字段在全部數(shù)據(jù)中的選擇度計(jì)算公式：
select count(distinct address) / count(*) from shop;
select count(distinct left(address, n)) / count(*) as subn from shop;
count(distinct left(address,n)) / count(*) 的結(jié)果是會(huì)隨著 n 的變大而變大。舉個(gè)例子，現(xiàn)在有兩個(gè)address（東大街長(zhǎng)興小區(qū)，東大街福樂小區(qū)），那么 distinct(address，2) distinct(address，3）
==所以，截取的長(zhǎng)度越長(zhǎng)就會(huì)越接近字段在全部數(shù)據(jù)中的選擇度
==所以，我們要權(quán)衡索引大小和查詢速度。
舉個(gè)例子，通過(guò)不同長(zhǎng)度去計(jì)算，與全表的選擇性對(duì)比：
SELECT? COUNT(DISTINCT(address))/COUNT(*) sub,? ? ? ? ? ? -- 字段在全部數(shù)據(jù)中的選擇度
COUNT(DISTINCT(LEFT(address,5)))/COUNT(*) sub5,? -- 截取前5個(gè)字符的選擇度
COUNT(DISTINCT(LEFT(address,7)))/COUNT(*) sub7,?
COUNT(DISTINCT(LEFT(address,9)))/COUNT(*) sub9,
COUNT(DISTINCT(LEFT(address,10)))/COUNT(*) sub10,? -- 截取前10個(gè)字符的選擇度
COUNT(DISTINCT(LEFT(address,11)))/COUNT(*) sub11,
COUNT(DISTINCT(LEFT(address,12)))/COUNT(*) sub12,
COUNT(DISTINCT(LEFT(address,13)))/COUNT(*) sub13,
COUNT(DISTINCT(LEFT(address,15)))/COUNT(*) sub15
FROM shop;
+--------+--------+--------+--------+--------+--------+--------+--------+--------+
| sub? ? | sub5? | sub7? | sub9? | sub10? | sub11? | sub12? | sub13? | sub15? |
+--------+--------+--------+--------+--------+--------+--------+--------+--------+
| 0.9993 | 0.0225 | 0.4663 | 0.8618 | 0.9734 | 0.9914 | 0.9943 | 0.9943 | 0.9958 |
+--------+--------+--------+--------+--------+--------+--------+--------+--------+
可以看到在截取 11 個(gè)字段時(shí) sub11(0.9993) 就已經(jīng)很接近字段在全部數(shù)據(jù)中的選擇度 sub(0.9958)了，而且長(zhǎng)度也相較后面更短一些， 綜合考慮比較合適。
ALTER TABLE shop ADD KEY (address(11));
1.索引的個(gè)數(shù)不要過(guò)多（浪費(fèi)空間，更新變慢）
2.在用于 where 判斷 order 排序和 join 的（on）字段上創(chuàng)建索引
3.區(qū)分度低的字段，例如性別，不要建索引（離散度太低，導(dǎo)致掃描行數(shù)過(guò)多）
4.更新頻繁的值，不要作為主鍵或者索引（頁(yè)分裂）
5.不建議用無(wú)序的值作為索引，例如身份證、UUID（在索引比較時(shí)需要轉(zhuǎn)為ASCII，并且插入時(shí)可能造成頁(yè)分裂）
6.若在多個(gè)字段都要?jiǎng)?chuàng)建索引的情況下，聯(lián)合索引優(yōu)于單值索引
7.聯(lián)合索引把散列性高（區(qū)分度高）的值放在前面
MySQL索引類型包括：
一、普通索引
這是最基本的索引，它沒有任何限制。有以下幾種創(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的效率，就要考慮建立組合索引。
重慶分公司
重慶分公司
