MySQL 的索引是如何工作的?10 分钟讲清楚!
程序员的成长之路
共 1451字,需浏览 3分钟
·
2020-12-26 18:34
阅读本文大概需要 5.5 分钟。
一、前言
二、知识补充
一般地,key_len 等于索引列类型字节长度,例如int类型为4 bytes,bigint为8 bytes;
如果是字符串类型,还需要同时考虑字符集因素,例如:CHAR(30) UTF8则key_len至少是90 bytes;
若该列类型定义时允许NULL,其key_len还需要再加 1 bytes;
若该列类型为变长类型,例如 VARCHAR(TEXT\BLOB不允许整列创建索引,如果创建部分索引也被视为动态列类型),其key_len还需要再加 2 bytes;
三、哪些条件能用到索引
exp:
idx_c1_c2_c3(c1,c2,c3)
where c1>=1 and c2>2 and c3=1
first key (c1,c2)
'>=' ,加入下边界界定,继续匹配下一个 c1为
'>',加入下边界界定,停止匹配 c2 为
exp:
idx_c1_c2_c3(c1,c2,c3)
where c1<=1 and c2=2 and c3<3
last key (c1,c2,c3)
'<=',加入上边界界定,继续匹配下一个 c1为
'='加入上边界界定,继续匹配下一个 c2为
'<',加入上边界界定,停止匹配 c3 为
注:这里简单的记忆是,如果比较符号中包含'='号,'>='也是包含'=',那么该索引键是可以被利用的,可以继续匹配后面的索引键值;如果不存在'=',也就是'>','<',这两个,后面的索引键值就无法匹配了。同时,上下边界是不可以混用的,哪个边界能利用索引的的键值多,就是最终能够利用索引键值的个数。
Index Filter
exp:
idex_c1_c2_c3
where c1>=1 and c2<=2 and c3 =1
index key --> c1
index filter--> c2 c3
Table Filter
四、Between 和Like 的处理
Between
Like
五、索引的排序
Make sure it uses index It is very important to have ORDER BY with LIMIT executed without scanning and sorting full result set, so it is important for it to use index – in this case index range scan will be started and query execution stopped as soon as soon as required amount of rows generated.
CREATE TABLE `t1` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`c1` int(11) NOT NULL DEFAULT '0',
`c2` int(11) NOT NULL DEFAULT '0',
`c3` int(11) NOT NULL DEFAULT '0',
`c4` int(11) NOT NULL DEFAULT '0',
`c5` int(11) NOT NULL DEFAULT '0',
PRIMARY KEY (`id`),
KEY `idx_c1_c2_c3` (`c1`,`c2`,`c3`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8mb4
select * from t1;
+----+----+----+----+----+----+
| id | c1 | c2 | c3 | c4 | c5 |
+----+----+----+----+----+----+
| 1 | 3 | 3 | 2 | 0 | 0 |
| 2 | 2 | 4 | 5 | 0 | 0 |
| 3 | 3 | 2 | 4 | 0 | 0 |
| 4 | 1 | 3 | 2 | 0 | 0 |
| 5 | 1 | 3 | 3 | 0 | 0 |
| 6 | 2 | 3 | 5 | 0 | 0 |
| 7 | 3 | 2 | 6 | 0 | 0 |
+----+----+----+----+----+----+
7 rows in set (0.00 sec)
select c1,c2,c3 from t1;
+----+----+----+
| c1 | c2 | c3 |
+----+----+----+
| 1 | 3 | 2 |
| 1 | 3 | 3 |
| 2 | 3 | 5 |
| 2 | 4 | 5 |
| 3 | 2 | 4 |
| 3 | 2 | 6 |
| 3 | 3 | 2 |
+----+----+----+
7 rows in set (0.00 sec)
存在一张表,c1,c2,c3上面有索引,select c1,c2,c3 from t1; 查询走的是索引全扫描,因此呈现的数据相当于在没有索引的情况下select c1,c2,c3 from t1 order by c1,c2,c3; 的结果。
c1=3,c2=2 — > c3 有序
c1 in(1,2) —> c2 无序 ,c3 无序
有个小规律,idx_c1_c2_c3,那么如何确定某个字段是有序的呢?c1 在索引的最前面,肯定是有序的,c2在第二个位置,只有在c1 唯一确定一个值的时候,c2才是有序的,如果c1有多个值,那么c2 将不一定有序,同理,c3也是类似
六、小结
推荐阅读:
SpringCloud中Zuul网关原理及其配置,看它就够了!
微信扫描二维码,关注我的公众号
朕已阅
评论