MySql為以下這些操作使用索引:
成都創(chuàng)新互聯(lián)是一家專業(yè)提供潁泉企業(yè)網(wǎng)站建設(shè),專注與成都網(wǎng)站設(shè)計(jì)、做網(wǎng)站、H5場景定制、小程序制作等業(yè)務(wù)。10年已為潁泉眾多企業(yè)、政府機(jī)構(gòu)等服務(wù)。創(chuàng)新互聯(lián)專業(yè)網(wǎng)站建設(shè)公司優(yōu)惠進(jìn)行中。
1、為了快速查找匹配WHERE條件的行。
2、為了從考慮的條件中消除行。如果在多個索引之間選擇一個,正常情況下,MySql使用找到行的最小數(shù)量的那個索引。
3、如果表有一個multiple-column索引,任何一個索引的最左前綴可以通過使用優(yōu)化器來查找行。例如,如果你有一個 three-column索引在(col1, col2, col3),你能搜索索引在(col1), (col1, col2),和 (col1, col2, col3)。
MySQL索引類型包括:
(1)普通索引
這是最基本的索引,它沒有任何限制。它有以下幾種創(chuàng)建方式:
◆創(chuàng)建索引
CREATE INDEX indexName ON mytable(username(length)); 如果是CHAR,VARCHAR類型,length可以小于字段實(shí)際長度;如果是BLOB和TEXT類型,必須指定 length,下同。
◆修改表結(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;
(2)唯一索引
與前面的普通索引類似,不同的就是:索引列的值必須唯一,但允許有空值。如果是組合索引,則列值的組合必須唯一。它有以下幾種創(chuàng)建方式:
◆創(chuàng)建索引
CREATE UNIQUE INDEX indexName ON mytable(username(length))
◆修改表結(jié)構(gòu)
ALTER mytable ADD UNIQUE [indexName] ON (username(length))
建立索引,要使用離散度(選擇度)更高的字段。
我們先來看一個重要的屬性列的 離散度,
count(distinct(column_name)) : count(*) -- 列的全部不同值個數(shù):所有數(shù)據(jù)行行數(shù)
數(shù)據(jù)行數(shù)相同的情況下,分子越大,列的離散度就越高。簡單來說,如果列的重復(fù)值越多,離散度就越低,重復(fù)值越少,離散度就越高。
當(dāng)字段值比較長的時候,建立索引會消耗很多的空間,搜索起來也會很慢。我們可以通過截取字段的前面一部分內(nèi)容建立索引,這個就叫前綴索引。
創(chuàng)建一張商戶表,因?yàn)榈刂纷侄伪容^長,在地址字段上建立前綴索引
create table shop(address varchar(120) not null);
alter table shop add key(address(12));? // 截取12個字符作為前綴索引是最優(yōu)的嗎?
問題是,截取多少呢?截取得多了,達(dá)不到節(jié)省索引存儲空間的目的,截取得少了,重復(fù)內(nèi)容太多,字段的散列度(選擇性)會降低。怎么計(jì)算不同的長度的選擇性呢?
先看一下字段在全部數(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é)果是會隨著 n 的變大而變大。舉個例子,現(xiàn)在有兩個address(東大街長興小區(qū),東大街福樂小區(qū)),那么 distinct(address,2) distinct(address,3)
==所以,截取的長度越長就會越接近字段在全部數(shù)據(jù)中的選擇度
==所以,我們要權(quán)衡索引大小和查詢速度。
舉個例子,通過不同長度去計(jì)算,與全表的選擇性對比:
SELECT? COUNT(DISTINCT(address))/COUNT(*) sub,? ? ? ? ? ? -- 字段在全部數(shù)據(jù)中的選擇度
COUNT(DISTINCT(LEFT(address,5)))/COUNT(*) sub5,? -- 截取前5個字符的選擇度
COUNT(DISTINCT(LEFT(address,7)))/COUNT(*) sub7,?
COUNT(DISTINCT(LEFT(address,9)))/COUNT(*) sub9,
COUNT(DISTINCT(LEFT(address,10)))/COUNT(*) sub10,? -- 截取前10個字符的選擇度
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 個字段時 sub11(0.9993) 就已經(jīng)很接近字段在全部數(shù)據(jù)中的選擇度 sub(0.9958)了,而且長度也相較后面更短一些, 綜合考慮比較合適。
ALTER TABLE shop ADD KEY (address(11));
1.索引的個數(shù)不要過多(浪費(fèi)空間,更新變慢)
2.在用于 where 判斷 order 排序和 join 的(on)字段上創(chuàng)建索引
3.區(qū)分度低的字段,例如性別,不要建索引(離散度太低,導(dǎo)致掃描行數(shù)過多)
4.更新頻繁的值,不要作為主鍵或者索引(頁分裂)
5.不建議用無序的值作為索引,例如身份證、UUID(在索引比較時需要轉(zhuǎn)為ASCII,并且插入時可能造成頁分裂)
6.若在多個字段都要創(chuàng)建索引的情況下,聯(lián)合索引優(yōu)于單值索引
7.聯(lián)合索引把散列性高(區(qū)分度高)的值放在前面