ToB企服应用市场:ToB评测及商务社交产业平台
标题:
MySQL学习[2] ——MySQL索引
[打印本页]
作者:
半亩花草
时间:
2024-9-18 09:10
标题:
MySQL学习[2] ——MySQL索引
二、MySQL索引
2.1 什么是索引？
索引的界说就是资助存储引擎快速获取数据的一种数据结构
，索引是数据的目录
。利用空间换取时间，能够极大加速数据库的查询服从。
索引和数据一样，位于MySQL的
存储引擎层
。
2.2 索引的分类
按「数据结构」分类：
B+tree索引、Hash索引、Full-text索引
。
按「物理存储」分类：
聚簇索引（主键索引）、二级索引（辅助索引）
。
按「字段特性」分类：
主键索引、唯一索引、平凡索引、前缀索引
。
按「字段个数」分类：
单列索引、联合索引
。
2.2.1 按数据结构分类
索引本身是通过一种数据结构来实现的，根据数据结构的差别，可以分为：
B+树索引、Hash索引、Full-Text索引
等。每种存储引擎支持的索引类型也差别，InnoDB中默认利用
B+树
作为索引的数据结构。
2.2.2 按物理存储分类
从物理存储的角度，索引分为**
聚簇索引（主键索引）、二级索引（辅助索引）
**，这两个的紧张区别在于：
主键索引的B+树叶子节点中存放的是
实际数据
，所有的记载都存放在主键索引的B+树叶子节点中；
二级索引的B+树的叶子节点存放的是
主键值
，必要再通过主键去查找实际数据。
2.2.3 按字段特性分类
从字段特性分类，索引可以分为：
主键索引、唯一索引、平凡索引、前缀索引
。
主键索引
：建立在主键上的索引。一张表只能有一个主键索引（渐渐只能有一个），不能重复，不允许空值NULL
唯一索引
：UNIQUE字段上建立的索引。一张表可以有多个唯一索引，允许存在空值
平凡索引
：在平凡字段上建立的索引，对字段没什么要求
前缀索引
：对字符类型（char、varchar、binary、varbinary）的字段的前几个字符建立的索引，而不是在整个字段上建立的索引
2.2.4 按字段个数分类
从字段个数上来看，索引分为
单列索引、联合索引
。
单列索引
：建立在单个列上的索引，如主键索引；
联合索引
：建立在多个列上的索引
2.3 为什么利用B+树作为索引？
2.3.1 B+Tree 索引的存储和查询的过程
B+树是一种**
多叉搜刮树
，只有
叶子节点才会存放实际数据，非叶子节点只会存放索引**，每个节点中数据是按照主键的顺序存放的。每一层父节点的索引值都会出现在下层子节点的索引值中，因此在**
叶子节点中，包括了所有的索引值信息，并且每一个叶子节点都有两个指针，分别指向下一个叶子节点和上一个叶子节点，形成一个双向链表
**。
数据库的索引和数据都是存储在硬盘的，我们可以把
读取一个节点看成一次磁盘 I/O 操作
。B+Tree 存储千万级的数据只必要 3-4 层高度就可以满足，这意味着从千万级的表查询目标数据最多必要 3-4 次磁盘 I/O，所以**
B+Tree 相比于 B 树和二叉树来说，最大的优势在于查询服从很高，由于纵然在数据量很大的情况，查询一个数据的磁盘 I/O 依然维持在 3-4次。
**
会先检二级索引中的 B+Tree 的索引值，找到对应的叶子节点，然后获取主键值，然后再通过主键索引中的 B+Tree 树查询到对应的叶子节点，然后获取整行数据。
这个过程叫「回表」，也就是说要查两个 B+Tree 才能查到数据
。
不外，
当查询的数据是能在二级索引的 B+Tree 的叶子节点里查询到（说明查询的数据就是索引）
，这时就不消再查主键索引查，
这种在二级索引的 B+Tree 就能查询到效果的过程就叫作「覆盖索引」，也就是只必要查一个 B+Tree 就能找到数据
。
2.3.2 InnoDB为什么利用B+树作为索引？
B+树相对于B树、二叉树、Hash表的优势：
B+树 vs B树
与B树相比，B+树只在叶子节点中存放数据，因此B+树单个节点可以存放更多的索引数据，可以使得
树的层数更小
。即能够
在雷同的I/O次数下，查询到更多的节点
。
另外，B+树的叶子节点通过
双链表连接
，很得当MySQL中的范围查询，B树无法做到这一点。
B+树 vs 二叉树
二叉树的一个父节点只能有两个子节点，搜刮复杂度为O(logN)，当节点许多时会导致二叉树的层数很高，检索到目标数据所经历的磁盘I/O次数更多。
对于B+树最大子节点个数为d个，实际应用中就保证了
纵然数据达到千万级，B+树的高度依然在3~4层左右
。
B+树 vs Hash表
Hash表在做等值查询时服从非常快，搜刮复杂度为O(1)。但是Hash表很难做范围查询。因此B+Tree 索引要比 Hash 表索引有着更广泛的适用场景。
2.4 联合索引是怎么发挥作用的？
2.4.1 什么是联合索引？
通过将
多个字段组合成一个索引
，这个索引就叫做
联合索引
。
联合索引的非叶子节点
用两个字段的值作为 B+Tree 的 key 值
。当在联合索引查询数据时，先按 product_no 字段比较，在 product_no 雷同的情况下再按 name 字段比较。这是**
最左匹配原则
，按照最左优先的方式举行索引的匹配。在利用联合索引举行查询的时间，
假如不遵照「最左匹配原则」，联合索引会失效
**，这样就无法利用到索引快速查询的特性了。
2.4.2 最左匹配原则
创建了一个 (a, b, c) 联合索引，假如查询条件是以下这几种，就可以匹配上联合索引：
where a=1；
where a=1 and b=2 and c=3；
where a=1 and b=2；
复制代码
由于有查询优化器，所以 a 字段在 where 子句的顺序并不紧张，但在理论上必要用尺度化写法：按照顺序。
但是，假如查询条件是以下这几种，由于不符合最左匹配原则，所以就无法匹配上联合索引，联合索引就会失效:
where b=2；
where c=3；
where b=2 and c=3；
复制代码
这是由于**
利用索引的条件是索引里的 key 是有序的
**，对于联合索引，在保证 a 字段相等时，才能使得b有序。
2.4.3 联合索引的范围查询
当联合索引中对某个字段利用了范围查询时，会导致联合索引到这个「范围查询」就会停止匹配。
也就是范围查询的字段（及之前）可以用到联合索引，但是在范围查询字段的后面的字段无法用到联合索引
。这也是由于**
利用索引的条件是索引里的 key 是有序的
**决定的。
联合索引的最左匹配原则，在碰到范围查询（如 >、<）的时间，就会停止匹配，也就是范围查询的字段可以用到联合索引，但是在范围查询字段的后面的字段无法用到联合索引。注意，对于 >=、<=、BETWEEN、like 前缀匹配的范围查询，并不会停止匹配
2.4.4 联合索引的索引下推
由于无法继续联合索引的匹配，在MySQL早期版本，必要
「回表」
。通过联合索引中
范围查询之前的字段
快速定位到符合条件的索引条目对应的主键值（假如是覆盖索引可以直接得到数据）。 利用获取到的主键值
回到原始数据表中，进一步读取必要的其他列数据
。这个步调必要再次访问数据表，称为
“回表”
。
显然，回表会带来肯定的性能损失。因此在 MySQL 5.6 引入了**
索引下推优化
**，
可以在联合索引遍历过程中，对联合索引中包罗的字段先做判定，直接过滤掉不满足条件的记载，减少回表次数
。
2.4.5 联合索引的索引区分度
由于「最左匹配原则」，建立联合索引的顺序对索引服从有很大的影响。实际开辟工作中，必要
把区分度大的字段排在前面
，可以很早地过滤掉大部分不符合的数据。区分度就是
某个字段 column 差别值的个数「除以」表的总行数
，计算公式如下：
2.5 什么时间必要/不必要索引？
索引紧张目标是加快查询，但也不可制止地增长了内存占用和维护数据库表的难度，应有选择的利用。
2.5.1 索引的优缺点
长处
：
可以大幅度加快数据的查询速度（大部分情况下比全表扫描快得多）；
唯一性索引可能保证该字段中数据的唯一性；
缺点
：
带来了额外的空间斲丧。一个索引就是一颗B+树，B+树的每一个节点就是一个16KB的页，占用内存大；
索引的创建和维护会耗费许多时间，对表举行增删改时对应必要修改索引中的内容，降低了服从。
2.5.2 什么时间必要创建索引
表很大，数据许多，全表扫描性能差；
频繁用于 WHERE 查询条件的字段；
频繁利用排序（order by）或分组（group by）的字段；
必要保证唯一性的字段。
2.5.3 什么时间不必要创建索引
表数据比较小，全局扫描也不会有太大延迟；
很少用于 WHERE 查询条件、排序（order by）或分组（group by）的字段；
经常必要更新的字段最好不要创建索引，带来维护的性能瓶颈；
存在大量重复数据的字段（区分度不大，如性别等）。
2.6 优化索引的方法？
前缀索引优化
：对于字符串，可以通过字符串的前几个字符建立索引而不是整个，可以减小索引字段的大小；
覆盖索引优化
：尽量让查询的字段都在索引中，这样可以制止回表，减少大量的I/O操作；
主键索引最好是自增的
：每次插入一条新记载，都是追加操作，不必要移动数据，不会产生页分裂；
索引最好是NOT NULL
：索引列存在NULL值时会导致优化器更加难以优化查询的执行，且NULL值会占用物理空间
防止索引失效
：制止写出索引失效的查询语句
常见扫描类型的
执行服从从低到高的顺序为
：
All（全表扫描）；
index（全索引扫描）；
range（索引范围扫描）；
ref（非唯一索引扫描）；
eq_ref（唯一索引扫描）；
const（效果只有一条的主键或唯一索引扫描）。
2.6 索引失效有哪些？
查询条件用上了索引列，
查询过程不肯定都用上索引
，接下来再一起看看哪些情况会导致索引失效，而发生全表扫描。
2.6.1 对索引利用左模糊或左右模糊匹配
利用左大概左右模糊匹配的时间，也就是 like %xx 大概 like %xx% 这两种方式都会造成索引失效。**
由于索引 B+ 树是按照「索引值」有序分列存储的，只能根据前缀举行比较。
**利用带有左模糊的匹配时，无法确定前缀，则无法根据顺序查找，只能全局扫描。
2.6.2 对索引利用函数或表达式计算
在查询条件中对索引举行表达式计算，也是无法走索引的。例如：
select * from user where length(name)=6; # 函数
selecr * from user where id + 1 = 10; # 表达式，改成 where id = 10 - 1，这样就不是在索引字段进行表达式计算了，于是就可以走索引查询了。
复制代码
原因是建立的**
索引是对字段的原始值的
**，而不是对函数值或表达式计算后的值（id + 1），后面得到的值固然无法利用索引匹配。
2.6.3 对索引隐式类型转换
例如，在表中phone的类型是varchar，但是查询语句如下：
select * from user where phone = 1300000001;
复制代码
这样就**
导致字段phone发生了隐式类型转换，则无法通过索引匹配
**，而是全局扫描。
特例：从字符串转换成整型不会。由于MySQL 在碰到字符串和数字比较的时间，会主动把字符串转为数字，然后再举行比较。所以其作用对象不是字段，而是整型。
2.6.4 利用联合索引但不满足最左匹配
创建了联合索引，在利用时必要满足最左匹配原则，否则索引会失效，举行全局扫描。
2.6.5 WHERE字句中的OR
在 WHERE 子句中，假如在
OR 前的条件列是索引列，而在 OR 后的条件列不是索引列，那么索引会失效
。
这是由于 OR 的含义就是**
两个只要满足一个即可，因此只有一个条件列是索引列是没故意义的
**，只要有条件列不是索引列，就会举行全表扫描。
2.7 MySQL 单表不要超过 2000W 行，靠谱吗？
非叶子节点内指向其他页的数量为 x
叶子节点内能容纳的数据行数为 y
B+ 数的层数为 z
MySQL的
表数据
是以页（记载是行）的形式存放的，页在磁盘中不肯定一连。 页的空间是 16K ,并不是所有的空间都是用来存放数据的，会有一些固定的信息，如，页头，页尾，页码，校验码等等，大概必要1K左右的空间，剩下的15K用来存放数据。
索引页中紧张记载的是主键与页号，主键我们假设是 Bigint (8 byte), 而页号也是固定的（4Byte）, 那么索引页中的一条数据也就是 12byte。
所以 x=15*1024/12≈1280 行
。
叶子节点和非叶子节点的结构是一样的，同理，能放数据的空间也是 15k。但是叶子节点中存放的是真正的行数据，这个影响的因素就会多许多，好比，
字段的类型，字段的数量。每行数据占用空间越大，页中所放的行数量就会越少
。
暂时按一条行数据 1k 来算，那一页就能存下 15 条，
y = 15*1024/1000 ≈15
。
Total =x^(z-1) *y，已知 x=1280，y=15：
假设 B+ 树是两层，那就是 z = 2， Total = （1280 ^1 ）*15 = 19200
假设 B+ 树是三层，那就是 z = 3， Total = （1280 ^2） *15 = 24576000 （约 2.45kw）
一样平常 B+ 数的层级最多也就是 3 层，
能够存储两千多万行的数据
。假如数据过多，导致三层的B+树无法完全存储，就会导致**
B+树的层数增长，则在查询时会导致多一次的I/O操作，造成性能降落
**。
2.8 count(*) 和 count(1) 有什么区别？哪个性能最好？
2.8.1 count()聚合函数比较
count() 是一个聚合函数，函数的参数不但可以是字段名，也可以是其他任意表达式，该函数作用是**
统计符合查询条件的记载中，函数指定的参数不为 NULL 的记载有多少个
**。
count(*) 执行过程跟 count(1) 执行过程基本一样的
，性能没有什么差异。
count(主键字段)必要遍历索引读取主键值
，根据主键值是否为NULL来增长计数；而count(*) 和 count(1) 虽然也遍历索引，不必要读取任何字段的值，所以速度更快。
count(平凡字段)
由于该字段不是索引，所以必须通过全局扫描的方式，性能最差。
2.8.2 为什么必要遍向来计数？
MyISAM 引擎时，执行 count 函数**
只必要 O(1 )复杂度
**，这是由于每张 MyISAM 的数据表都有一个 meta 信息有存储了row_count值，由表级锁保证一致性，所以直接读取 row_count 值就是 count 函数的执行效果。
InnoDB存储引擎是支持事务的，同一个时候的多个查询，由于多版本并发控制（MVCC）的原因，InnoDB 表**“
应该返回多少行”也是不确定的（差别版本效果差别）
**，所以无法像 MyISAM一样，只维护一个 row_count 变量。
2.8.2 怎样优化count(*)？
近似值：利用 show table status 大概 explain 命令来表举行估算
额外维护一个表，来统计计数；
资料参考
内容大多参考自：图解MySQL先容 | 小林coding (xiaolincoding.com)
免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。
欢迎光临 ToB企服应用市场:ToB评测及商务社交产业平台 (https://dis.qidao123.com/)
Powered by Discuz! X3.4
