文章目录
说明:
此类文章是为小林coding的图解MySQL,所简写,目的在于大家更快抓到小林文章的重点
本文全部由我简化,但是其中有部分引用小林的文章内容
希望大家掌握精髓,构建知识体系和知识框架
索引篇
一、索引常见面试题
- 索引底层使用了什么数据结构和算法?
- 为什么 MySQL InnoDB 选择 B+tree 作为索引的数据结构?
- 什么时候适用索引?
- 什么时候不需要创建索引?
- 什么情况下索引会失效?
- 有什么优化索引的方法?
索引分类
按数据结构
- 按「数据结构」分类:B+tree索引、Hash索引、Full-text索引。
- 按「物理存储」分类:聚簇索引(主键索引)、二级索引(辅助索引)。
- 按「字段特性」分类:主键索引、唯一索引、普通索引、前缀索引。
- 按「字段个数」分类:单列索引、联合索引。
InnoDB存储引擎,B+Tree索引类型,优势:查询效率高,查询一个数据的磁盘I/O依然维持在3-4次
1、B+Tree vs B Tree
B+Tree 的单个节点的数据量更小(只在叶子节点存储数据,而…),在相同的磁盘 I/O 次数下,就能查询更多的节点。
B+Tree 叶子节点采用的是双链表连接,适合 MySQL 中常见的基于范围的顺序查找
2、B+Tree vs 二叉树
搜索复杂度为O(logdN)
(节点允许的最大子节点个数为 d 个),也就是说一次数据查询操作只需要做 3~4 次的磁盘 I/O 操作就能查询到目标数据,
所经历的磁盘I/O次数
3、B+Tree vs Hash
Hash 在做等值查询的时候效率贼快,搜索复杂度为 O(1),但不适合做范围查询
-
主键索引的 B+Tree 的叶子节点存放的是实际数据,所有完整的用户记录都存放在主键索引的 B+Tree 的叶子节点里;
-
二级索引的 B+Tree 的叶子节点存放的是主键值,而不是实际数据。
按物理存储分类
**覆盖索引:**在查询时使用了二级索引,如果查询的数据能在二级索引里查询的到,那么就不需要回表,这个过程就是覆盖索引
**回表:**如果查询的数据不在二级索引里,就会先检索二级索引,找到对应的叶子节点,获取到主键值后,然后再检索主键索引,就能查询到数据了,这个过程就是回表
按字段特性分类
PRIMARY KEY (index_column_1) USING BTREE # 主键索引
UNIQUE KEY(index_column_1,index_column_2,...) # 唯一索引
# 建表后,如果要创建唯一索引
CREATE UNIQUE INDEX index_name
ON table_name(index_column_1,index_column_2,...);
# 普通索引
INDEX(index_column_1,index_column_2,...)
# 前缀索引
column_list,
INDEX(column_name(length))
# 建表后,如果要创建前缀索引
CREATE INDEX index_name
ON table_name(column_name(length));
按字段个数分类
- 建立在单列上的索引称为单列索引,比如主键索引;
- 建立在多列上的索引称为联合索引;
使用联合索引时,存在最左匹配原则
CREATE INDEX index_product_no_name ON product(product_no, name);
索引缺点:
- 需要占用物理空间,数量越大,占用空间越大;
- 创建索引和维护索引要耗费时间,这种时间随着数据量的增加而增大;
- 会降低表的增删改的效率,因为每次增删改索引,B+ 树为了维护索引有序性,都需要进行动态维护。
什么时候适用索引?
- 字段有唯一性限制的;
- 经常用于
WHERE
查询条件的字段,这样能够提高整个表的查询速度,如果查询条件不是一个字段,可以建立联合索引。 - 经常用于
GROUP BY
和ORDER BY
的字段,这样在查询的时候就不需要再去做一次排序了,因为我们都已经知道了建立索引之后在 B+Tree 中的记录都是排序好的
什么时候不需要创建索引?
WHERE
条件,GROUP BY
,ORDER BY
里用不到的字段,索引的价值是快速定位,如果起不到定位的字段通常是不需要创建索引的,因为索引是会占用物理空间的。- 字段中存在大量重复数据,比如性别字段,只有男女
- 表数据太少的时候,不需要创建索引;
- 经常更新的字段不用创建索引,比如不要对电商项目的用户余额建立索引,因为索引字段频繁修改,由于要维护 B+Tree的有序性,那么就需要频繁的重建索引,这个过程是会影响数据库性能的。
常见优化索引的方法:
- 前缀索引优化;
为了减小索引字段大小
order by 就无法使用前缀索引;
无法把前缀索引用作覆盖索引; - 覆盖索引优化;
避免回表的操作
假设我们只需要查询商品的名称、价格
建立一个联合索引,即「商品ID、名称、价格」作为一个联合索引 - 主键索引最好是自增的;
如果我们使用自增主键,每次插入一条新记录,都是追加操作,不需要重新移动数据 - 防止索引失效;
索引最好设置为 NOT NULL,否则,优化器在做索引选择的时候更加复杂,更加难以优化,比如进行索引统计时,count 会省略值为NULL 的行
没意义的值,但是它会占用物理空间
发生索引失效的情况:
- 使用左或者左右模糊匹配的时候,也就是
like %xx
或者like %xx%
这两种方式都会造成索引失效; - 在查询条件中对索引列做了计算、函数、类型转换操作
- 联合索引要能正确使用需要遵循最左匹配原则,否则就会导致索引失效。
- 在 WHERE 子句中, 索引列 OR 不是索引列
执行效率从低到高的顺序为:
- All(全表扫描);
- index(全索引扫描);
- range(索引范围扫描);
- ref(非唯一索引扫描);
- eq_ref(唯一索引扫描);
- const(结果只有一条的主键或唯一索引扫描)。
二、从数据页角度看B+树
B+树节点存放的是数据页
File Header 中有两个指针,双向的链表
采用链表的结构是让数据页之间不需要是物理上的连续的,而是逻辑上的连续。
User Records 是怎么组织数据的
槽
- 第一个分组中的记录只能有 1 条记录;
- 最后一个分组中的记录条数范围只能在 1-8 条之间;
- 剩下的分组中记录条数范围只能在 4-8 条之间。
一张表只能有一个聚簇索引
小林总结:
nnoDB 的数据是按「数据页」为单位来读写的,默认数据页大小为 16 KB。每个数据页之间通过双向链表的形式组织起来,物理上不连续,但是逻辑上连续。
数据页内包含用户记录,每个记录之间用单向链表的方式组织起来,为了加快在数据页内高效查询记录,设计了一个页目录,页目录存储各个槽(分组),且主键值是有序的,于是可以通过二分查找法的方式进行检索从而提高效率。
为了高效查询记录所在的数据页,InnoDB 采用 b+ 树作为索引,每个节点都是一个数据页。
如果叶子节点存储的是实际数据的就是聚簇索引,一个表只能有一个聚簇索引;如果叶子节点存储的不是实际数据,而是主键值则就是二级索引,一个表中可以有多个二级索引。
在使用二级索引进行查找数据时,如果查询的数据能在二级索引找到,那么就是「索引覆盖」操作,如果查询的数据不在二级索引里,就需要先在二级索引找到主键值,需要去聚簇索引中获得数据行,这个过程就叫作「回表」。
三、为什么 MySQL 采用 B+ 树作为索引?
怎样的索引的数据结构是好的?
什么是二分查找?
什么是二分查找树?
什么是B树?
什么是B+树?
MySQL 的数据是持久化的,意味着数据(索引+记录)是保存到磁盘上的,断电,数据不丢失
内存的访问速度是纳秒级别的,磁盘访问的速度是毫秒级别的,磁盘慢上万倍
磁盘读写最小单位是扇区,52B,操作系统最小读写单位是块,linux块大小是4KB,一次磁盘I/O读写8个扇区
二叉查找树的特点是一个节点的左子树的所有节点都小于这个节点,右子树的所有节点都大于这个节点
问题1、当每次插入的元素都是二叉查找树中最大的元素,二叉查找树就会退化成了一条链表,查找数据的时间复杂度变成了 O(n)
问题2、高度是I/O次数,太高了,影响查询性能
问题3、不能范围查询
平衡二叉查找树(AVL 树)
问题1、只要是二叉树,高度都太高
B树
再限制一个节点就只能有 2 个子节点,而是允许 M 个子节点 (M>2),从而降低树的高度,简单说就是多叉树
问题1、每个节点都包含了索引+记录,数据要莫没用上,要莫就要花费更多磁盘I/O次数,
问题2、用来范围查询,需要用中序遍历,设计多个节点的磁盘I/O问题
B+ 树与 B 树差异的点,主要是以下这几点:
- 叶子节点(最底部的节点)才会存放实际数据(索引+记录),非叶子节点只会存放索引;
- 所有索引都会在叶子节点出现,叶子节点之间构成一个有序链表;
- 非叶子节点的索引也会同时存在在子节点中,并且是在子节点中所有索引的最大(或最小)。
- 非叶子节点中有多少个子节点,就有多少个索引;
下面通过三个方面,比较下 B+ 和 B 树的性能区别。
1、单点查询
节点存放索引,可以存放更多索引,可以比B树更加矮胖,查询磁盘I/O次数更少
2、插入和删除效率
删除一个节点,直接删除叶子节点,不用动非叶子节点,结构更稳定,删除更快
会自平衡,因为只涉及一条路径,不需要复杂的算法
3、范围查询
为啥不说等值查询呢,因为基本一样,而范围查询就不一样了
B+树叶子节点有双向链表连接
节点内容是数据页
四、单表不要超过2000W行,一般靠谱
假设
- 非叶子节点内指向其他页的数量为 x
- 叶子节点内能容纳的数据行数为 y
- B+ 数的层数为 z
如下图中所示,Total =x^(z-1) *y 也就是说总数会等于 x 的 z-1 次方 与 Y 的乘积。
X =?
1k存标识,15k存数据,一条数据按12byte,x=15*1024/12≈1280 行
页和索引结构差不多,都会有 File Header (38 byte)、Page Header (56 Byte)、Infimum + Supermum(26 byte)、File Trailer(8byte), 再加上页目录,大概 1k 左右。
索引页中主要记录的是主键与页号,主键我们假设是 Bigint (8 byte), 而页号也是固定的(4Byte), 那么索引页中的一条数据也就是 12byte。
所以 x=15*1024/12≈1280 行。
Y=?
按一条行数据 1k 来算,那一页就能存下 15 条,Y = 15*1024/1000 ≈15。
-
假设 B+ 树是两层,那就是 z = 2, Total = (1280 ^1 )*15 = 19200
-
假设 B+ 树是三层,那就是 z = 3, Total = (1280 ^2) *15 = 24576000 (约 2.45kw)
-
我们刚刚在说 Y 的值时候假设的是 1K ,那比如我实际当行的数据占用空间不是 1K , 而是 5K, 那么单个数据页最多只能放下 3 条数据。
同样,还是按照 z = 3 的值来计算,那 Total = (1280 ^2) *3 = 4915200 (近 500w)
行数据大小不同,最大建议值不同
影响查询性能的还有很多其他因素,比如,数据库版本,服务器配置,sql 的编写等等
五、索引失效有哪些?
1、使用左模糊,|| 左右模糊
因为索引 B+ 树是按照「索引值」有序排列存储的,只能根据前缀进行比较。
2、对索引使用了函数
因为索引保存的是索引字段的原始值,而不是经过函数计算后的值,自然就没办法走索引了
从 MySQL 8.0 开始,索引特性增加了函数索引
3、对索引进行表达式计算
因为索引保存的是索引字段的原始值,而不是 id + 1 表达式计算后的值,所以无法走索引
4、对索引隐式类型转换
索引字段是字符串,但是输入的是整形
但是如果索引字段是整形,输入字段是字符串时候会用索引,因为会自动转化
MySQL 在遇到字符串和数字比较的时候,会自动把字符串转为数字,然后再进行比较。
5、联合索引非最左匹配
6、 WHERE 子句中的 OR
索引 OR 非索引字段,那么就是失效
六、MySQL 使用 like “%x“,索引一定会失效吗?
使用左模糊匹配(like “%xx”)并不一定会走全表扫描,关键还是看数据表中的字段。
数据库表中的字段只有主键+二级索引 == 全扫描二级索引树 type=index
如果数据库表中的字段都是索引的话,即使查询过程中,没有遵循最左匹配原则,也是走全扫描二级索引树(type=index)
七、 count(*) 和 count(1) 有什么区别?哪个性能最好?
count该函数作用是统计符合查询条件的记录中,函数指定的参数不为 NULL 的记录有多少个
count(1)、 count(*)、 count(主键字段)在执行的时候,如果表里存在二级索引,优化器就会选择二级索引进行扫描。尽量建立二级索引
count(字段) 来统计记录个数,效率最差,全表扫描
通常在没有任何查询条件下的 count(*),MyISAM 的查询速度要明显快于 InnoDB
MyISAM,只维护一个 row_count 变量
面对大表的记录统计,解决方法
1、近似值计算
使用 show table status 或者 explain 命令来表进行估算,像谷歌统计的
2、额外表保存计数值
当我们在数据表插入一条记录的同时,将计数表中的计数字段 + 1
新增和删除操作时,我们需要额外维护这个计数表。