1. 問(wèn)題場(chǎng)景

一開(kāi)始在某個(gè)字段加了普通索引，SQL語(yǔ)句查找該字段范圍內(nèi)的數(shù)據(jù)。

開(kāi)始加索引的時(shí)候是能使用上索引的，但是過(guò)了幾天，數(shù)據(jù)量增大，發(fā)現(xiàn)檢索語(yǔ)句沒(méi)有走索引了。

2. 準(zhǔn)備測(cè)試

2.1 創(chuàng)建測(cè)試表

CREATE TABLE `test_index` (
`id`  int(10) UNSIGNED NOT NULL AUTO_INCREMENT ,
`name`  varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '' ,
`age`  tinyint(5) UNSIGNED NOT NULL DEFAULT 0 ,
`status`  tinyint(1) UNSIGNED NOT NULL DEFAULT 1 ,
`create_time`  timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`id`)
)

2.2 在age字段上加普通索引

ALTER TABLE `test_index`
ADD INDEX `age` (`age`) USING BTREE

2.3 插入3條測(cè)試數(shù)據(jù)

insert into test_index(name,age,create_time) values('Tom',12,time()),('Tobie',20,time()),('Jack',15,time())

3. 測(cè)試是否走索引（總記錄數(shù)total-t,結(jié)果數(shù)result-r）

3.1 total = 3

測(cè)試一（t=3,r=0,走索引）：

測(cè)試二（t=3,r=1,走索引）：

測(cè)試三（t=3,r=2,走索引）：

測(cè)試四（t=3,r=3,不走索引）：

3.2 total = 10

• t=10,r=0,走索引
• t=10,r=4,走索引
• t=10,r=5,不走索引

3.3 total=100

• t=100,r=15,走索引
• t=100,r=18,走索引
• t=100,r=19,不走索引

3.4 total = 1000

• t=1000,r=100,走索引
• t=1000,r=150,走索引
• t=1000,r=170,走索引
• t=1000,r=171,不走索引

3.5 total = 10000

• t=10000,r=900,走索引
• t=10000,r=940,走索引
• t=10000,r=941,不走索引
• t=10000,r=1000,不走索引

3.6 total = 100000

• t=100000,r=3948,走索引
• t=10000,r=3949,不走索引

4. 結(jié)論

不嚴(yán)謹(jǐn)總結(jié)

自己還測(cè)了更大的數(shù)據(jù)，發(fā)現(xiàn)betweet…and的使用與單純的數(shù)據(jù)量無(wú)關(guān)，而與查找到的數(shù)據(jù)與總數(shù)據(jù)的比有關(guān)。

當(dāng)總數(shù)據(jù)量較小時(shí)，有很大概率會(huì)走索引，此時(shí)查到的結(jié)果數(shù)可以允許比較大

但總數(shù)據(jù)量比較大之后，查找到的結(jié)果數(shù)據(jù)越小時(shí)，越大概率使用上索引

也就是說(shuō)，如果有10w的數(shù)據(jù)，而你需要查的數(shù)據(jù)為200條，此時(shí)是走索引的。但是，如果你查到的結(jié)果有5000條，那么，極大可能是不走索引的

稍嚴(yán)謹(jǐn)一些的總結(jié)

查詢(xún)數(shù)據(jù)時(shí)，如果走普通索引，那么會(huì)產(chǎn)生回表操作，因?yàn)槠胀ㄋ饕龑儆诜蔷奂饕?，葉子節(jié)點(diǎn)存放的是主鍵字段的值，拿到主鍵字段后再去表中根據(jù)主鍵值找到對(duì)應(yīng)的記錄。

因此，當(dāng)數(shù)據(jù)量很大，而查詢(xún)數(shù)據(jù)也很大時(shí)，考慮到回表的消耗，就不走索引；

當(dāng)數(shù)據(jù)量很大，而查詢(xún)數(shù)據(jù)很小，這個(gè)時(shí)候比起全表掃描，回表的消耗相對(duì)少，所以走索引

以上為個(gè)人經(jīng)驗(yàn)，希望能給大家一個(gè)參考，也希望大家多多支持腳本之家。

最新評(píng)論