@[TOC]目录标题)
1. 问题场景
一开始在某个字段加了普通索引,SQL语句查找该字段范围内的数据。
开始加索引的时候是能使用上索引的,但是过了几天,数据量增大,发现检索语句没有走索引了。
2. 准备测试
- 创建测试表
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`)
)
- 在age字段上加普通索引
ALTER TABLE `test_index`
ADD INDEX `age` (`age`) USING BTREE
- 插入3条测试数据
insert into test_index(name,age,create_time) values('Tom',12,time()),('Tobie',20,time()),('Jack',15,time())
3. 测试是否走索引(总记录数total-t,结果数result-r)
3.1 total = 3
测试一(t=3,r=0,走索引):
测试二(t=3,r=1,走索引):
测试三(t=3,r=2,走索引):
测试四(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. 结论
不严谨总结:
自己还测了更大的数据,发现betweet…and的使用与单纯的数据量无关,而与查找到的数据与总数据的比有关。
当总数据量较小时,有很大概率会走索引,此时查到的结果数可以允许比较大
但总数据量比较大之后,查找到的结果数据越小时,越大概率使用上索引
也就是说,如果有10w的数据,而你需要查的数据为200条,此时是走索引的。
但是,如果你查到的结果有5000条,那么,极大可能是不走索引的
稍严谨一些的总结:
查询数据时,如果走普通索引,那么会产生回表操作,因为普通索引属于非聚集索引,叶子节点存放的是主键字段的值,拿到主键字段后再去表中根据主键值找到对应的记录。
因此,当数据量很大,而查询数据也很大时,考虑到回表的消耗,就不走索引;
当数据量很大,而查询数据很小,这个时候比起全表扫描,回表的消耗相对少,所以走索引
来源:https://www.cnblogs.com/xinxinmifan/p/mysql_index_bewteen_and.html