3. InnoDB vs MyISAM
😀InnoDB
-
索引分为聚簇索引与二级索引
-
聚簇索引:主键值作为索引数据,叶子节点还包含了所有字段数据,索引和数据是存储在一起的
-
二级索引:除主键外的其它字段建立的索引称为二级索引。被索引的字段值作为索引数据,叶子节点还包含了主键值
-
-
支持事务
-
通过 undo log 支持事务回滚、当前读(多版本查询)
-
通过 redo log 实现持久性
-
通过两阶段提交实现一致性
-
通过当前读、锁实现隔离性
-
-
支持行锁、间隙锁
-
支持外键
😀MyISAM
-
索引只有一种
-
被索引字段值作为索引数据,叶子节点还包含了该记录数据页地址,数据和索引是分开存储的
-
-
不支持事务,没有 undo log 和 redo log
-
仅支持表锁
-
不支持外键
-
会保存表的总行数
InnoDB 索引特点
聚簇索引:主键值作为索引数据,叶子节点还包含了所有字段数据,索引和数据是存储在一起的
◉主键即 7369、7499、7521 等
二级索引:除主键外的其它字段建立的索引称为二级索引。被索引的字段值作为索引数据,叶子节点还包含了主键值
-
上图中 800、950、1100 这些是工资字段的值,根据它们建立了二级索引
-
上图中,如果执行查询
select empno, ename, sal from emp where sal = 800
,这时候可以利用二级索引定位到 800 这个工资,同时还能知道主键值 7369 -
但 select 字句中还出现了 ename 字段,在二级索引中不存在,因此需要根据主键值 7369 查询聚簇索引来获取 ename 的信息,这个过程俗称回表
MyISAM 索引特点
被索引字段值作为索引数据,叶子节点还包含了该记录数据页地址,数据和索引是分开存储的
4. 索引
索引基础
常见索引
-
哈希索引
-
理想时间复杂度为 $O(1)$
-
适用场景:适用于等值查询的场景,内存数据的索引
-
典型实现:Redis,MySQL 的 memory 引擎
-
-
平衡二叉树索引
-
查询和更新的时间复杂度都是 $O(log_2(n))$
-
适用场景:适用于等值查询以及范围查询;适合内存数据的索引,但不适合磁盘数据的索引,可以认为树的高度决定了磁盘 I/O 的次数,百万数据树高约为 20
-
-
BTree 索引
-
BTree 其实就是 n 叉树,分叉多意味着节点中的孩子(key)多,树高自然就降低了
-
分叉数由页大小和行(包括 key 与 value)大小决定
-
假设页大小为 16k,每行 40 个字节,那么分叉数就为 16k / 40 ≈ 410
-
而分叉为 410,则百万数据树高约为3,仅 3 次 I/O 就能找到所需数据
-
-
局部性原理:每次 I/O 按页为单位读取数据,把多个 key 相邻的行放在同一页中(每页就是树上一个节点),能进一步减少 I/O
-
-
B+ 树索引
-
在 BTree 的基础上做了改进,索引上只存储 key,这样能进一步增加分叉数,假设 key 占 13 个字节,那么一页数据分叉数可以到 1260,树高可以进一步下降为 2
-
树高计算公式
$log{10}(N) / log{10}(M)$ 其中 N 为数据行数,M 为分叉数
BTree vs B+Tree
-
无论 BTree 还是 B+Tree,每个叶子节点到根节点距离都相同
-
BTree key 及 value 在每个节点上,无论叶子还是非叶子节点
-
-
B+Tree 普通节点只存 key,叶子节点才存储 key 和 value,因此分叉数可以更多
-
不过也请注意,普通节点上的 key 有的会与叶子节点的 key 重复
-
-
B+Tree 必须到达叶子节点才能找到 value
-
B+Tree 叶子节点用链表连接,可以方便范围查询及全表遍历
注:这两张图都是仅画了 key,未画 value
B+Tree 新增 key
假设阶数(m)为5
-
1. 若为空树,那么直接创建一个节点,插入 key 即可,此时这个叶子结点也是根结点。例如,插入 5
-
2.插入时,若当前结点 key 的个数小于阶数,则插入结束
-
3.依次插入 8、10、15,按 key 大小升序
-
4.插入 16,这时到达了阶数限制,所以要进行分裂
-
5.叶子节点分裂规则:将这个叶子结点分裂成左右两个叶子结点,左叶子结点包含前 m/2 个(2个)记录,右结点包含剩下的记录,将中间的 key 进位到父结点中。注意:中间的 key 仍会保留在叶子节点一份
-
6.插入 17
-
7.插入 18,这时当前结点的 key 个数到达 5,进行分裂
-
8.分裂成两个结点,左结点 2 个记录,右结点 3 个记录,key 16 进位到父结点中
-
-
9.插入 19、20、21、22、6、9
-
10.插入 7,当前结点的 key 个数到达 5,需要分裂
-
11.分裂后 key 7 进入到父结点中,这时父节点 key 个数也到达 5
-
12.非叶子节点分裂规则:左子结点包含前 (m-1)/2 个 key,将中间的 key 进位到父结点中(不保留),右子节点包含剩余的 key
B+Tree 查询 key
以查询 15 为例
B+Tree 删除叶子节点 key
-
初始状态
-
删完有富余。即删除后结点的key的个数 > m/2 – 1,删除操作结束,例如删除 22
-
删完没富余,但兄弟节点有富余。即兄弟结点 key 有富余( > m/2 – 1 ),向兄弟结点借一个记录,同时替换父节点,例如删除 15
-
兄弟节点也不富余,合并兄弟叶子节点。即兄弟节点合并成一个新的叶子结点,并删除父结点中的key,将当前结点指向父结点,例如删除 7
-
也需要删除非叶子节点中的 7,并替换父节点保证区间仍有效
-
左右兄弟都不够借,合并
B+Tree 删除非叶子节点 key
接着上面的操作
-
1.非叶子节点 key 的个数 > m/2 – 1,则删除操作结束,否则执行 2
-
2.若兄弟结点有富余,父结点 key 下移,兄弟结点 key 上移,删除结束,否则执行 3
-
3.若兄弟节点没富余,当前结点和兄弟结点及父结点合并成一个新的结点。重复 1
命中索引
准备数据
修改 MySQL 配置文件,在 [mysqld] 下添加 secure_file_priv= 重启 MySQL 服务器,让选项生效
执行 db.sql 内的脚本,建表
执行
LOAD DATA INFILE 'D:\\big_person.txt' INTO TABLE big_person;
注意实际路径根据情况修改
测试表 big_person(此表数据量较大,如果与其它表数据一起提供不好管理,故单独提供),数据行数 100 万条,列个数 15 列。为了更快速导入数据,这里采用了 load data infile 命令配合 *.txt 格式数据
索引用于排序
/* 测试单列索引并不能在多列排序时加速 */ create index first_idx on big_person(first_name); create index last_idx on big_person(last_name); explain select * from big_person order by last_name, first_name limit 10; /* 多列排序需要用组合索引 */ alter table big_person drop index first_idx; alter table big_person drop index last_idx; create index last_first_idx on big_person(last_name,first_name); /* 多列排序需要遵循最左前缀原则, 第1个查询可以利用索引,第2,3查询不能利用索引 */ explain select * from big_person order by last_name, first_name limit 10; explain select * from big_person order by first_name, last_name limit 10; explain select * from big_person order by first_name limit 10; /* 多列排序升降序需要一致,查询1可以利用索引,查询2不能利用索引*/ explain select * from big_person order by last_name desc, first_name desc limit 10; explain select * from big_person order by last_name desc, first_name asc limit 10;
最左前缀原则
若建立组合索引 (a,b,c),则可以利用到索引的排序条件是:
order by a
order by a, b
order by a, b, c
索引用于 where 筛选
/* 模糊查询需要遵循字符串最左前缀原则,查询2可以利用索引,查询1,3不能利用索引 */ explain SELECT * FROM big_person WHERE first_name LIKE 'dav%' LIMIT 5; explain SELECT * FROM big_person WHERE last_name LIKE 'dav%' LIMIT 5; explain SELECT * FROM big_person WHERE last_name LIKE '%dav' LIMIT 5; /* 组合索引需要遵循最左前缀原则,查询1,2可以利用索引,查询3,4不能利用索引 */ create index province_city_county_idx on big_person(province,city,county); explain SELECT * FROM big_person WHERE province = '上海' AND city='宜兰县' AND county='中西区'; explain SELECT * FROM big_person WHERE county='中西区' AND city='宜兰县' AND province ='上海'; explain SELECT * FROM big_person WHERE city='宜兰县' AND county='中西区'; explain SELECT * FROM big_person WHERE county='中西区'; /* 函数及计算问题,一旦在字段上应用了计算或函数,都会造成索引失效。查询2可以利用索引,查询1不能利用索引 */ create index birthday_idx on big_person(birthday); explain SELECT * FROM big_person WHERE ADDDATE(birthday,1)='2005-02-10'; explain SELECT * FROM big_person WHERE birthday=ADDDATE('2005-02-10',-1); /* 隐式类型转换问题 * 查询1会发生隐式类型转换等价于在phone上应用了函数,造成索引失效 * 查询2字段与值类型相同不会类型转换,可以利用索引 */ create index phone_idx on big_person(phone); explain SELECT * FROM big_person WHERE phone = 13000013934; explain SELECT * FROM big_person WHERE phone = '13000013934';
最左前缀原则(leftmost prefix)
若建立组合索引 (a,b,c),则可以利用到索引的查询条件是:
where a = ?
where a = ? and b = ? (注意与条件的先后次序无关,也可以是 where b = ? and a = ?,只要出现即可)
where a = ? and b = ? and c = ? (注意事项同上)
不能利用的例子:
where b = ?
where b = ? and c = ?
where c = ?
特殊情况:
where a = ? and c = ?(a = ? 会利用索引,但 c = ? 不能利用索引加速,会触发索引条件下推)
索引条件下推
/* 查询 1,2,3,4 都能利用索引,但 4 相当于部分利用了索引,会触发索引条件下推 */ explain SELECT * FROM big_person WHERE province = '上海'; explain SELECT * FROM big_person WHERE province = '上海' AND city='嘉兴市'; explain SELECT * FROM big_person WHERE province = '上海' AND city='嘉兴市' AND county='中西区'; explain SELECT * FROM big_person WHERE province = '上海' AND county='中西区';
索引条件下推
MySQL 执行条件判断的时机有两处:
服务层(上层,不包括索引实现)
引擎层(下层,包括了索引实现,可以利用)
上面查询 4 中有 province 条件能够利用索引,在引擎层执行,但 county 条件仍然要交给服务层处理
在 5.6 之前,服务层需要判断所有记录的 county 条件,性能非常低
5.6 以后,引擎层会先根据 province 条件过滤,满足条件的记录才在服务层处理 county 条件
我们现在用的是 5.6 以上版本,所以没有体会,可以用下面的语句关闭索引下推优化,再测试一下性能
SET optimizer_switch = 'index_condition_pushdown=off'; SELECT * FROM big_person WHERE province = '上海' AND county='中西区';
二级索引覆盖
explain SELECT * FROM big_person WHERE province = '上海' AND city='宜兰县' AND county= '中西区'; explain SELECT id,province,city,county FROM big_person WHERE province = '上海' ANDcity='宜兰县' AND county='中西区';
根据查询条件查询 1,2 都会先走二级索引,但是二级索引仅包含了 (province, city, county) 和 id 信息
-
查询 1 是 select *,因此还有一些字段二级索引中没有,需要回表(查询聚簇索引)来获取其它字段信息
-
查询 2 的 select 中明确指出了需要哪些字段,这些字段在二级索引都有,就避免了回表查询
其它注意事项
-
表连接需要在连接字段上建立索引
-
不要迷信网上说法,具体情况具体分析
例如:
create index first_idx on big_person(first_name); /* 不会利用索引,因为优化器发现查询记录数太多,还不如直接全表扫描 */ explain SELECT * FROM big_person WHERE first_name > 'Jenni'; /* 会利用索引,因为优化器发现查询记录数不太多 */ explain SELECT * FROM big_person WHERE first_name > 'Willia'; /* 同一字段的不同值利用 or 连接,会利用索引 */ explain select * from big_person where id = 1 or id = 190839; /* 不同字段利用 or 连接,会利用索引(底层分别用了两个索引) */ explain select * from big_person where first_name = 'David' or last_name = 'Thomas'; /* in 会利用索引 */ explain select * from big_person where first_name in ('Mark', 'Kevin','David'); /* not in 不会利用索引的情况 */ explain select * from big_person where first_name not in ('Mark', 'Kevin','David'); /* not in 会利用索引的情况 */ explain select id from big_person where first_name not in ('Mark', 'Kevin','David');
-
以上实验基于 5.7.27,其它如 !=、is null、is not null 是否使用索引都会跟版本、实际数据相关,以优化器结果为准