mysql索引頁(yè)怎么用 mysql索引的用法

MySQL索引

MySQL的Innodb存儲(chǔ)引擎的索引分為聚集索引和非聚集索引兩大類

成都創(chuàng)新互聯(lián)從2013年成立，是專業(yè)互聯(lián)網(wǎng)技術(shù)服務(wù)公司，擁有項(xiàng)目成都網(wǎng)站設(shè)計(jì)、成都網(wǎng)站建設(shè)網(wǎng)站策劃，項(xiàng)目實(shí)施與項(xiàng)目整合能力。我們以讓每一個(gè)夢(mèng)想脫穎而出為使命，1280元杏花嶺做網(wǎng)站,已為上家服務(wù),為杏花嶺各地企業(yè)和個(gè)人服務(wù),聯(lián)系電話:13518219792

特點(diǎn)：B+樹(shù)葉子節(jié)點(diǎn)存儲(chǔ)行數(shù)據(jù)

一個(gè)表中，必須有一個(gè)聚集索引，只能有一個(gè)聚集索引，Innodb通常把一個(gè)表的主鍵索引作為聚集索引，如果沒(méi)有主鍵InnoDB會(huì)選擇一個(gè)唯一索引代替。如果沒(méi)有這樣的索引，InnoDB會(huì)隱式的定義一個(gè)主鍵來(lái)作為聚集索引，這個(gè)字段為6個(gè)字節(jié)，類型為長(zhǎng)整形。

利用主鍵索引查找行數(shù)據(jù)是最快的，建議使用自增主鍵原因是利于索引樹(shù)的構(gòu)建（主鍵自增寫(xiě)入時(shí)新插入的數(shù)據(jù)不會(huì)影響到原有頁(yè)，插入效率高；但是如果主鍵是無(wú)序的或者隨機(jī)的，那每次的插入可能會(huì)導(dǎo)致原有頁(yè)頻繁的分裂，影響插入效率）

特點(diǎn)：B+樹(shù)葉子節(jié)點(diǎn)存儲(chǔ)主鍵ID

一個(gè)表中可以有多個(gè)非聚集索引，每個(gè)非聚集索引即是一棵B+樹(shù)

通過(guò)非聚集索引查找數(shù)據(jù)時(shí)，需要先在非聚集索引上找到主鍵ID，再?gòu)木奂饕@取行數(shù)據(jù)，這個(gè)過(guò)程就稱之為回表

B樹(shù)索引中的B樹(shù)實(shí)際上是B+樹(shù)，至于為什么使用B+樹(shù)而不使用B樹(shù)或者紅黑樹(shù)的原因在另外的文章中有提及。

特點(diǎn)：

特點(diǎn)：類似JDK中的HashMap，但無(wú)法支持范圍查詢

特點(diǎn)：使用的算法仍然是B樹(shù)索引，不同的就是索引列的值必須唯一

對(duì)于普通索引來(lái)說(shuō)，查找到滿足條件的第一個(gè)記錄后，需要查找下一個(gè)記錄，直到碰到第一個(gè)不滿足條件的記錄。

對(duì)于唯一索引來(lái)說(shuō)，由于索引定義了唯一性，查找到第一個(gè)滿足條件的記錄后，就會(huì)停止繼續(xù)檢索，提升索引性能

另外插入行時(shí)會(huì)構(gòu)建該唯一索引，假如索引值重復(fù)將插入失敗，適合業(yè)務(wù)上做唯一性檢驗(yàn)

通過(guò)建立倒排索引，可以極大的提升檢索效率,解決判斷字段是否包含的問(wèn)題，但是業(yè)務(wù)上一般都不采用這種索引，而是使用ES處理全文搜索需求

僅對(duì)某個(gè)特定字段建立的索引，如(biz_id)

對(duì)多個(gè)字段建立的索引，如(biz_id,type)

Mysql索引

建立索引，要使用離散度（選擇度）更高的字段。

我們先來(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ū)，東大街福樂(lè)小區(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 索引怎么使用

CREATE

[UNIQUE]

INDEX

ON

(字段

[ASC|DESC]);

UNIQUE

--確保所有的索引列中的值都是可以區(qū)分的。

[ASC|DESC]

--在列上按指定排序創(chuàng)建索引。

(創(chuàng)建索引的準(zhǔn)則：

1.如果表里有幾百行記錄則可以對(duì)其創(chuàng)建索引(表里的記錄行數(shù)越多索引的效果就越明顯)。

2.不要試圖對(duì)表創(chuàng)建兩個(gè)或三個(gè)以上的索引。

3.為頻繁使用的行創(chuàng)建索引。

)

示例

create

index

i_1

on

emp(empno

asc);

mysql索引

二叉搜索樹(shù)、N叉樹(shù)

頁(yè)分裂：B+樹(shù)的插入可能會(huì)引起數(shù)據(jù)頁(yè)的分裂，刪除可能會(huì)引起數(shù)據(jù)頁(yè)的合并，二者都是比較重的IO消耗，所以比較好的方式是順序插入數(shù)據(jù)，這也是我們一般使用自增主鍵的原因之一。

頁(yè)分裂逆過(guò)程：頁(yè)合并，當(dāng)刪除數(shù)據(jù)后，相鄰的兩個(gè)數(shù)據(jù)頁(yè)利用率很低的時(shí)候會(huì)做數(shù)據(jù)頁(yè)合并

主鍵索引：key：主鍵，value：數(shù)據(jù)頁(yè)，存儲(chǔ)每行數(shù)據(jù)

非主鍵索引：key：非主鍵索引，value：主鍵key，導(dǎo)致回表

最左匹配：優(yōu)先將區(qū)分度高的列放到前面，這樣可以高效索引，

最左匹配原則遇到范圍查詢就停止匹配，范圍查詢(、、between、like)為什么？因?yàn)槌霈F(xiàn)范圍匹配后，后面的索引字段無(wú)法保證有序，局部有序失去，順序失去則無(wú)法提高查詢效率

SELECT * FROM table WHERE a IN (1,2,3) and b 1;

如何建立索引？

還是對(duì)(a，b)建立索引，因?yàn)镮N在這里可以視為等值引用，不會(huì)中止索引匹配，所以還是(a,b)!

索引組織表

索引用頁(yè)存儲(chǔ)：key【10】-point【6】，通過(guò)調(diào)整key大小，當(dāng)頁(yè)大小固定的情況下，通過(guò)調(diào)整key大小，使得N叉樹(shù)變化；

如key 10, point 6則單個(gè)索引16字節(jié)，頁(yè)大小為16k，則頁(yè)面總共可以存儲(chǔ)1024個(gè)索引，即N大小

覆蓋索引: 二級(jí)索引的信息已經(jīng)存在想要的列，例如主鍵

如果現(xiàn)在有一個(gè)高頻請(qǐng)求，要根據(jù)市民的身份證號(hào)查詢他的姓名，這個(gè)聯(lián)合索引就有意義了。它可以在這個(gè)高頻請(qǐng)求上用到覆蓋索引，不再需要回表查整行記錄，減少語(yǔ)句的執(zhí)行時(shí)間。

索引下推優(yōu)化：可以在索引遍歷過(guò)程中，對(duì)索引中包含的字段先做判斷，直接過(guò)濾掉不滿足條件的記錄，減少回表次數(shù)。

整理索引碎片，重建表：alter table T engine=InnoDB

??首先是看key的大小，另外是數(shù)據(jù)頁(yè)的大小，如果需要改變N，則需要從這兩個(gè)方面做改動(dòng)；

一個(gè)innoDB引擎的表，數(shù)據(jù)量非常大，根據(jù)二級(jí)索引搜索會(huì)比主鍵搜索快，文章闡述的原因是主鍵索引和數(shù)據(jù)行在一起，非常大搜索慢，我的疑惑是：通過(guò)普通索引找到主鍵ID后，同樣要跑一邊主鍵索引，對(duì)于使用覆蓋索引的情況下，使用覆蓋索引可以直接解決問(wèn)題

mysql數(shù)據(jù)庫(kù)，索引是怎么使用的

MySQL支持很多數(shù)據(jù)類型，選擇合適的數(shù)據(jù)類型存儲(chǔ)數(shù)據(jù)對(duì)性能有很大的影響。通常來(lái)說(shuō)，可以遵循以下一些指導(dǎo)原則：

(1)越小的數(shù)據(jù)類型通常更好：越小的數(shù)據(jù)類型通常在磁盤(pán)、內(nèi)存和CPU緩存中都需要更少的空間，處理起來(lái)更快。

(2)簡(jiǎn)單的數(shù)據(jù)類型更好：整型數(shù)據(jù)比起字符，處理開(kāi)銷(xiāo)更小，因?yàn)樽址谋容^更復(fù)雜。在MySQL中，應(yīng)該用內(nèi)置的日期和時(shí)間數(shù)據(jù)類型，而不是用字符串來(lái)存儲(chǔ)時(shí)間；以及用整型數(shù)據(jù)類型存儲(chǔ)IP地址。

(3)盡量避免NULL：應(yīng)該指定列為NOT NULL，除非你想存儲(chǔ)NULL。在MySQL中，含有空值的列很難進(jìn)行查詢優(yōu)化，因?yàn)樗鼈兪沟盟饕?、索引的統(tǒng)計(jì)信息以及比較運(yùn)算更加復(fù)雜。你應(yīng)該用0、一個(gè)特殊的值或者一個(gè)空串代替空值。

標(biāo)題名稱：mysql索引頁(yè)怎么用 mysql索引的用法
分享地址：http://muchs.cn/article34/hgippe.html

成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供定制開(kāi)發(fā)、外貿(mào)建站、動(dòng)態(tài)網(wǎng)站、建站公司、手機(jī)網(wǎng)站建設(shè)、網(wǎng)站營(yíng)銷(xiāo)

廣告

聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶投稿、用戶轉(zhuǎn)載內(nèi)容為主，如果涉及侵權(quán)請(qǐng)盡快告知，我們將會(huì)在第一時(shí)間刪除。文章觀點(diǎn)不代表本網(wǎng)站立場(chǎng)，如需處理請(qǐng)聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載，或轉(zhuǎn)載時(shí)需注明來(lái)源： 創(chuàng)新互聯(lián)