建站
咨詢
售后
建站咨詢
新聞中心
mysql之普通索引和唯一索引
常見的索引類型：哈希表、有序數(shù)組、搜索樹。
網(wǎng)站的建設(shè)創(chuàng)新互聯(lián)公司專注網(wǎng)站定制,經(jīng)驗豐富,不做模板,主營網(wǎng)站定制開發(fā).小程序定制開發(fā),H5頁面制作!給你煥然一新的設(shè)計體驗!已為咖啡廳設(shè)計等企業(yè)提供專業(yè)服務(wù)。
mysql之普通索引和唯一索引。
執(zhí)行查詢的語句是 select id from T where k=5
這個查詢語句在索引樹上查找的過程，先是通過 B+ 樹從樹根開始，按層搜索到葉子節(jié)點，也就是圖中右下角的這個數(shù)據(jù)頁，然后可以認(rèn)為數(shù)據(jù)頁內(nèi)部通過二分法來定位記錄。
InnoDB的索引組織結(jié)構(gòu)：
change buffer:持久化的數(shù)據(jù)。InnoDB將更新操作緩存在 change buffer中,也就是說,change buffer 在內(nèi)存中有拷貝，也會被寫入到磁盤，主要節(jié)省的則是隨機讀磁盤的IO消耗。
change buffer 只限于用在普通索引的場景下，而不適用于唯一索引.
merge:將 change buffer 中的操作應(yīng)用到原數(shù)據(jù)頁，得到最新結(jié)果的過程。
merge執(zhí)行流程：
1、從磁盤讀入數(shù)據(jù)頁到內(nèi)存
2、從change buffer里找出這個數(shù)據(jù)頁的change buffer記錄，依次應(yīng)用，得到新版數(shù)據(jù)頁
3、寫redo log,這個redo log包含了數(shù)據(jù)的變更和change buffer的變更。
change buffer 用的是 buffer pool 里的內(nèi)存，因此不能無限增大。change buffer 的大小，可以通過參數(shù) innodb_change_buffer_max_size=50 表示 change buffer 的大小最多只能占用 buffer pool 的 50%。
如果要在這張表中插入一個新記錄 (4,400) 的話，InnoDB 的處理流程是怎樣的。
第一種情況是，這個記錄要更新的目標(biāo)頁在內(nèi)存中
這時，InnoDB 的處理流程如下：
第二種情況是，這個記錄要更新的目標(biāo)頁不在內(nèi)存中
這時，InnoDB 的處理流程如下：
mysql insert into t(id,k) values(id1,k1),(id2,k2); 當(dāng)前 k 索引樹的狀態(tài)，查找到位置后，k1 所在的數(shù)據(jù)頁在內(nèi)存 (InnoDB buffer pool) 中，k2 所在的數(shù)據(jù)頁不在內(nèi)存中。
分析這條更新語句，你會發(fā)現(xiàn)它涉及了四個部分：內(nèi)存、redo log（ib_log_fileX）、 數(shù)據(jù)表空間（t.ibd）、系統(tǒng)表空間（ibdata1）。這條更新語句做了如下的操作（按照圖中的數(shù)字順序）：
帶change buffer的更新過程：
select * from t where k in (k1, k2) ,如果讀語句發(fā)生在更新語句后不久，內(nèi)存中的數(shù)據(jù)都還在，那么此時的這兩個讀操作就與系統(tǒng)表空間（ibdata1）和 redo log（ib_log_fileX）無關(guān)了.
mysql：更新唯一索引的值
order為關(guān)鍵字，作為字段使用是需要加`引上，如下：
update test_t set `order`=5 where id=15;
Mysql索引
建立索引，要使用離散度（選擇度）更高的字段。
我們先來看一個重要的屬性列的 離散度，
count(distinct(column_name)) : count(*) -- 列的全部不同值個數(shù)：所有數(shù)據(jù)行行數(shù)
數(shù)據(jù)行數(shù)相同的情況下，分子越大，列的離散度就越高。簡單來說，如果列的重復(fù)值越多，離散度就越低，重復(fù)值越少，離散度就越高。
當(dāng)字段值比較長的時候，建立索引會消耗很多的空間，搜索起來也會很慢。我們可以通過截取字段的前面一部分內(nèi)容建立索引，這個就叫前綴索引。
創(chuàng)建一張商戶表，因為地址字段比較長，在地址字段上建立前綴索引
create table shop(address varchar(120) not null);
alter table shop add key(address(12));? // 截取12個字符作為前綴索引是最優(yōu)的嗎？
問題是，截取多少呢？截取得多了，達(dá)不到節(jié)省索引存儲空間的目的，截取得少了，重復(fù)內(nèi)容太多，字段的散列度（選擇性）會降低。怎么計算不同的長度的選擇性呢？
先看一下字段在全部數(shù)據(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)衡索引大小和查詢速度。
舉個例子，通過不同長度去計算，與全表的選擇性對比：
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ù)不要過多（浪費空間，更新變慢）
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ū)分度高）的值放在前面
分享標(biāo)題：mysql索引怎么更新 mysql索引升序
URL分享：http://www.dlmjj.cn/article/ddocipc.html
