MySQL之主鍵索引排序失效問(wèn)題
主鍵索引排序失效
環(huán)境：MySQL8
有一張用戶信息表user_info，建表DDL如下：
CREATE TABLE `user_info` ( `id` int(11) NOT NULL COMMENT '用戶編號(hào)', `age` int(11) NOT NULL COMMENT '用戶年齡', PRIMARY KEY (`id`), KEY `idx_age` (`age`) USING BTREE COMMENT '年齡索引' ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
只有兩列，第一列用戶編號(hào)id做主鍵，第二列用戶年齡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)文章
MySQL查詢表中重復(fù)數(shù)據(jù)的實(shí)現(xiàn)
在數(shù)據(jù)庫(kù)中,我們經(jīng)常需要查詢重復(fù)字段來(lái)確保數(shù)據(jù)的準(zhǔn)確性,如果數(shù)據(jù)中有重復(fù)字段,則可能會(huì)導(dǎo)致查詢結(jié)果錯(cuò)誤,本文就想詳細(xì)的介紹了MySQL查詢表中重復(fù)數(shù)據(jù),感興趣的可以了解一下2023-08-08
在本篇文章里小編給大家整理了關(guān)于mysql設(shè)置不區(qū)分大小寫(xiě)的方法，需要的朋友們跟著學(xué)習(xí)下。2020-06-06
MySQL多版本并發(fā)控制MVCC深入學(xué)習(xí)
這篇文章主要介紹了MySQL多版本并發(fā)控制MVCC，小編覺(jué)得挺不錯(cuò)的，現(xiàn)在分享給大家，也給大家做個(gè)參考。一起跟隨小編過(guò)來(lái)看看吧2021-11-11
這篇文章主要介紹了MySQL 聲明變量及存儲(chǔ)過(guò)程的相關(guān)內(nèi)容，小編覺(jué)得挺不錯(cuò)的，這里分享給大家，需要的朋友可以參考下。2017-10-10
通過(guò)案例分析MySQL中令人頭疼的Aborted告警
這篇文章通過(guò)案例跟大家分析了MySQL中令人頭疼的Aborted告警的相關(guān)資料，文中將Aborted告警介紹的非常詳細(xì)，對(duì)大家具有一定的參考學(xué)習(xí)價(jià)值，需要的朋友們下面來(lái)一起看看吧。2017-06-06
這篇文章主要介紹了mysql 定時(shí)備份任務(wù)的相關(guān)資料，文中示例代碼非常詳細(xì)，幫助大家更好的理解和學(xué)習(xí)，感興趣的朋友可以了解下2020-07-07
MySQL報(bào)錯(cuò)Lost connection to MySQL server&n
在確保網(wǎng)絡(luò)沒(méi)有問(wèn)題的情況下,服務(wù)器正常運(yùn)行一段時(shí)間后,數(shù)據(jù)庫(kù)拋出了異常"Lost connection to MySQL server during query",本文將給大家介紹MySQL報(bào)錯(cuò)Lost connection to MySQL server during query的解決方案,需要的朋友可以參考下2024-01-01
window下mysql 8.0.15 安裝配置方法圖文教程
這篇文章主要為大家詳細(xì)介紹了window下mysql 8.0.15 安裝配置方法圖文教程，具有一定的參考價(jià)值，感興趣的小伙伴們可以參考一下2019-02-02
在處理大量數(shù)據(jù)時(shí)如果一條一條地插入會(huì)極大地影響效率,因此批量插入是一個(gè)更好的選擇,下面這篇文章主要給大家介紹了關(guān)于mysql快速插入大量數(shù)據(jù)的正確方法,需要的朋友可以參考下2024-01-01
這篇文章主要介紹了mysql charset=utf8你真的弄明白意思了嗎?文中通過(guò)示例代碼介紹的非常詳細(xì)，對(duì)大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值，需要的朋友們下面隨著小編來(lái)一起學(xué)習(xí)學(xué)習(xí)吧2020-01-01