MySQL之主鍵索引排序失效問(wèn)題
主鍵索引排序失效
環(huán)境：MySQL8
有一張用戶(hù)信息表user_info，建表DDL如下：
CREATE TABLE `user_info` ( `id` int(11) NOT NULL COMMENT '用戶(hù)編號(hào)', `age` int(11) NOT NULL COMMENT '用戶(hù)年齡', PRIMARY KEY (`id`), KEY `idx_age` (`age`) USING BTREE COMMENT '年齡索引' ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
只有兩列，第一列用戶(hù)編號(hào)id做主鍵，第二列用戶(hù)年齡age有一個(gè)普通索引idx_age。
現(xiàn)在初始化幾行數(shù)據(jù)
INSERT INTO `user_info` (`id`,`age`) VALUES(1,1),(5,3),(7,8),(11,12);
查一下所有記錄
接著往這個(gè)表里插入一行數(shù)據(jù)(6,1)，猜測(cè)一下這行數(shù)據(jù)最終會(huì)插入在什么位置？
可能大部分人都會(huì)認(rèn)為插入在(5,3)和(7,8)之間，因?yàn)閕d=6的話，5<6<7。
但是再次查看結(jié)果，發(fā)現(xiàn)并符合預(yù)期，而是插入在了(1,1)和(5,3)之間：
看起來(lái)效果就像是age變?yōu)榱酥麈I，根據(jù)age默認(rèn)排序了，或者說(shuō)主鍵索引排序失效。
這是因?yàn)樵贛ySQL底層實(shí)現(xiàn)中，對(duì)于像user_info這種特殊的表，有特殊的處理方式。這張user_info表的特殊點(diǎn)在于，只有兩列，一列是主鍵，另一列也有索引。
這時(shí)候非主鍵的age這一列就是一個(gè)覆蓋索引，因?yàn)閍ge的索引可以查到所有字段。
MySQL內(nèi)部會(huì)認(rèn)為訪問(wèn)數(shù)據(jù)的時(shí)候，覆蓋索引的效率比主鍵索引高，所以維護(hù)默認(rèn)的排序會(huì)優(yōu)先根據(jù)覆蓋索引列來(lái)進(jìn)行。
查看一下執(zhí)行計(jì)劃
type=index，代表只遍歷了索引樹(shù)；key=idx_age，代表真正用到了索引；Extra=Using index，代表覆蓋索引生效，在索引樹(shù)中就可以查到所需數(shù)據(jù)，避免了回表掃描表數(shù)據(jù)文件。
總結(jié)
以上為個(gè)人經(jīng)驗(yàn)，希望能給大家一個(gè)參考，也希望大家多多支持腳本之家。
相關(guān)文章
ubuntu 16.04下mysql5.7.17開(kāi)放遠(yuǎn)程3306端口
這篇文章主要介紹了ubuntu 16.04下mysql5.7.17開(kāi)放遠(yuǎn)程3306端口的相關(guān)資料，具有一定的參考價(jià)值，感興趣的小伙伴們可以參考一下2017-01-01
這篇文章主要為大家詳細(xì)介紹了MySQL的索引，文中示例代碼介紹的非常詳細(xì)，具有一定的參考價(jià)值，感興趣的小伙伴們可以參考一下，希望能夠給你帶來(lái)幫助2022-03-03
一般大家也許會(huì)碰到這個(gè)問(wèn)題，大家可以參考下，講的比較詳細(xì)2008-07-07
MySQL數(shù)據(jù)庫(kù)升級(jí)的一些"陷阱"
這篇文章主要介紹了MySQL數(shù)據(jù)庫(kù)升級(jí)需要注意的地方，幫助大家更好的理解和學(xué)習(xí)，感興趣的朋友可以了解下2020-08-08
一篇文章學(xué)會(huì)SQL中的遞歸用法(Mysql)
這篇文章主要給大家介紹了關(guān)于如何一篇文章學(xué)會(huì)SQL中的遞歸用法,眾所周知目前的mysql版本中并不支持直接的遞歸查詢(xún),但是通過(guò)遞歸到迭代轉(zhuǎn)化的思路,還是可以在一句SQL內(nèi)實(shí)現(xiàn)樹(shù)的遞歸查詢(xún)的,需要的朋友可以參考下2023-10-10
這篇文章主要為大家詳細(xì)介紹了CentOS6.8使用cmake安裝MySQL5.7.18的相關(guān)資料，具有一定的參考價(jià)值，感興趣的小伙伴們可以參考一下2017-04-04
MySQL日期時(shí)間類(lèi)型與字符串互相轉(zhuǎn)換的方法
這篇文章主要介紹了MySQL日期時(shí)間類(lèi)型與字符串互相轉(zhuǎn)換的方法,文中通過(guò)代碼示例和圖文結(jié)合的方式給大家講解的非常詳細(xì),對(duì)大家的學(xué)習(xí)或工作有一定的幫助,需要的朋友可以參考下2024-07-07
MySQL的升級(jí)方式分為兩種：原地升級(jí)和邏輯升級(jí)。這兩種升級(jí)方式，本質(zhì)沒(méi)有什么區(qū)別的,下面通過(guò)本文給大家詳細(xì)介紹Win下Mysql5.6升級(jí)到5.7的方法，需要的的朋友參考下2017-04-04
本文介紹下，在mysql數(shù)據(jù)庫(kù)中，有關(guān)case when語(yǔ)句的用法，介紹了case when語(yǔ)句的基礎(chǔ)知識(shí)，并提供了相關(guān)實(shí)例，供大家學(xué)習(xí)參考，有需要的朋友不要錯(cuò)過(guò)2021-08-08
解決Mysql:ERROR?1045?(28000):Access?denied?for?user?‘roo
最近在我們連接數(shù)據(jù)庫(kù)的時(shí)候遇到個(gè)問(wèn)題,感覺(jué)還挺容易遇到的,所以總結(jié)下,這篇文章主要給大家介紹了關(guān)于解決Mysql:ERROR?1045?(28000):Access?denied?for?user?‘root‘@‘localhost‘?(using?password:?NO)的方法,需要的朋友可以參考下2022-06-06