MySQL索引原理以及查询优化

作者: L_又不是不能用 | 来源:发表于2021-02-09 22:09 被阅读0次

一、索引简介

1、索引是什么

索引是个什么东东？

1、MySQL官方对索引的定义为：索引(Index)是帮助MySQL高效获取数据的数据结构。可以得到索引的本质：索引是数据结构
2、你可以简单理解为"排好序的快速查找数据结构"，即索引 = 排序 + 查找
3、一般来说索引本身占用内存空间也很大，不可能全部存储在内存中，因此索引往往以文件形式存储在硬盘上
4、我们平时所说的索引，如果没有特别指明，都是指B树(多路搜索树，并不一定是二叉树)结构组织的索引。
5、聚集索引，次要索引，覆盖索引，复合索引，前缀索引，唯一索引默认都是使用B+树索引，统称索引。当然，除了B+树这种类型的索引之外，还有哈希索引(hash index)等。

2、索引原理

$\color{red}{将索引理解为"排好序的快速查找数据结构"}$
索引的目的在于提高查询效率，与我们查阅图书所用的目录是一个道理：先定位到章，然后定位到该章下的一个小节，然后找到页数。相似的例子还有：查字典，查火车车次，飞机航班等

本质都是：通过不断地缩小想要获取数据的范围来筛选出最终想要的结果，同时把随机的事件变成顺序的事件，也就是说，有了这种索引机制，我们可以总是用同一种查找方式来锁定数据。

1、数据库也是一样，数据库中，在数据之外，数据库系统还维护着满足特定查找算法的数据结构，这些数据结构以某种方式引用（指向）数据，这样就可以在这些数据结构上实现高级查找算法。这种数据结构，就是索引。
2、下图就是一种可能的索引方式示例：
左边是数据表，一共有两列七条记录，最左边的十六进制数字是数据记录的物理地址
为了加快col2的查找，可以维护一个右边所示的二叉查找树，每个节点分别包含索引键值和一个指向对应数据记录物理地址的指针，这样就可以运用二叉查找在一定的复杂度内获取到相应数据，从而快速的检索出符合条件的记录。

索引方式示例

3、MySQL 索引结构

索引搜索过程

1、Btree 索引

【初始化介绍】
1、一颗 b 树，浅蓝色的块我们称之为一个磁盘块，可以看到每个磁盘块包含几个数据项（深蓝色所示）和指针（黄色所示）
2、如磁盘块 1 包含数据项 17 和 35，包含指针 P1、 P2、 P3
3、P1 表示小于 17 的磁盘块， P2 表示在 17 和 35 之间的磁盘块， P3 表示大于 35 的磁盘块
4、真实的数据存在于叶子节点和非叶子节点中，如17、35并不真实存在于数据表中
【查找过程】
1、如果要查找数据项 29，那么首先会把磁盘块 1 由磁盘加载到内存，此时发生一次 IO，在内存中用二分查找确定 29在 17 和 35 之间，锁定磁盘块 1 的 P2 指针，内存时间因为非常短（相比磁盘的 IO）可以忽略不计
2、通过磁盘块 1的 P2 指针的磁盘地址把磁盘块 3 由磁盘加载到内存，发生第二次 IO， 29 在 26 和 30 之间，锁定磁盘块 3 的 P2 指针
3、通过指针加载磁盘块 8 到内存，发生第三次 IO，同时内存中做二分查找找到 29，结束查询，总计三次 IO。

BTree 结构

2、B+tree 索引

$\color{red}{\small{【B+Tree 与 BTree 的区别】}}$
$\color{red}{\small{B树的关键字（数据项）和记录是放在一起的；非叶子节点上也有记录}}$
$\color{red}{\small{ B+树的非叶子节点中只有关键字和指向下一个节点的索引，记录只放在叶子节点中。}}$
【B+Tree 与 BTree 的查找过程】
1、在 B 树中，越靠近根节点的记录查找时间越快，只要找到关键字即可确定记录的存在；而 B+ 树中每个记录的查找时间基本是一样的，都需要从根节点走到叶子节点，而且在叶子节点中还要再比较关键字。
2、从这个角度看 B 树的性能好像要比 B+ 树好，而在实际应用中却是 B+ 树的性能要好些。因为 B+ 树的非叶子节点不存放实际的数据，这样每个节点可容纳的元素个数比 B 树多，树高比 B 树小，这样带来的好处是减少磁盘访问次数。
3、尽管 B+ 树找到一个记录所需的比较次数要比 B 树多，但是一次磁盘访问的时间相当于成百上千次内存比较的时间，因此实际中B+ 树的性能可能还会好些，而且 B+树的叶子节点使用指针连接在一起，方便顺序遍历（范围搜索），这也是很多数据库和文件系统使用 B+树的缘故。
【性能提升】
真实的情况是， 3 层的 B+ 树可以表示上百万的数据，如果上百万的数据查找只需要三次 IO，性能提高将是巨大的，如果没有索引，每个数据项都要发生一次 IO，那么总共需要百万次的 IO，显然成本非常非常高。
【思考：为什么说 B+树比 B-树更适合实际应用中操作系统的文件索引和数据库索引？】
1、B+树的磁盘读写代价更低：B+树的内部结点并没有指向关键字具体信息的指针。因此其内部结点相对 B 树更小。如果把所有同一内部结点的关键字存放在同一盘块中，那么盘块所能容纳的关键字数量也越多。一次性读入内存中的需要查找的关键字也就越多。相对来说 IO 读写次数也就降低了。
2、B+树的查询效率更加稳定：由于非终结点并不是最终指向文件内容的结点，而只是叶子结点中关键字的索引。所以任何关键字的查找必须走一条从根结点到叶子结点的路。所有关键字查询的路径长度相同，导致每一个数据的查询效率相当。

B+Tree 结构

索引树性质

1、索引字段要尽量的小：通过上面的分析，我们知道IO次数取决于b+数的高度h，假设当前数据表的数据为N，每个磁盘块的数据项的数量是m，则有h=㏒(m+1)N，当数据量N一定的情况下，m越大，h越小；而m = 磁盘块的大小 / 数据项的大小，磁盘块的大小也就是一个数据页的大小，是固定的，如果数据项占的空间越小，数据项的数量越多，树的高度越低。这就是为什么每个数据项，即索引字段要尽量的小，比如int占4字节，要比bigint8字节少一半。这也是为什么b+树要求把真实的数据放到叶子节点而不是内层节点，一旦放到内层节点，磁盘块的数据项会大幅度下降，导致树增高。当数据项等于1时将会退化成线性表。
2、索引的最左匹配特性（即从左往右匹配）：当b+树的数据项是复合的数据结构，比如(name,age,sex)的时候，b+数是按照从左到右的顺序来建立搜索树的，比如当(张三,20,F)这样的数据来检索的时候，b+树会优先比较name来确定下一步的所搜方向，如果name相同再依次比较age和sex，最后得到检索的数据；但当(20,F)这样的没有name的数据来的时候，b+树就不知道下一步该查哪个节点，因为建立搜索树的时候name就是第一个比较因子，必须要先根据name来搜索才能知道下一步去哪里查询。比如当(张三,F)这样的数据来检索时，b+树可以用name来指定搜索方向，但下一个字段age的缺失，所以只能把名字等于张三的数据都找到，然后再匹配性别是F的数据了，这个是非常重要的性质，即索引的最左匹配特性。

4、索引优劣势

优点

索引大大减小了服务器需要扫描的数据量
索引可以帮助服务器避免排序和临时表
索引可以将随机IO变成顺序IO
索引对于InnoDB（对索引支持行级锁）非常重要，因为它可以让查询锁更少的元组。在MySQL5.1和更新的版本中，InnoDB可以在服务器端过滤掉行后就释放锁，但在早期的MySQL版本中，InnoDB直到事务提交时才会解锁。对不需要的元组的加锁，会增加锁的开销，降低并发性。 InnoDB仅对需要访问的元组加锁，而索引能够减少InnoDB访问的元组数。但是只有在存储引擎层过滤掉那些不需要的数据才能达到这种目的。一旦索引不允许InnoDB那样做（即索引达不到过滤的目的），MySQL服务器只能对InnoDB返回的数据进行WHERE操作，此时，已经无法避免对那些元组加锁了。如果查询不能使用索引，MySQL会进行全表扫描，并锁住每一个元组，不管是否真正需要。
关于InnoDB、索引和锁：InnoDB在二级索引上使用共享锁（读锁），但访问主键索引需要排他锁（写锁）

缺点

虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE。因为更新表时，MySQL不仅要保存数据，还要保存索引文件。
建立索引会占用磁盘空间的索引文件。一般情况这个问题不太严重，但如果你在一个大表上创建了多种组合索引，索引文件的会膨胀很快。
如果某个数据列包含许多重复的内容，为它建立索引就没有太大的实际效果。
对于非常小的表，大部分情况下简单的全表扫描更高效；

索引只是提高效率的一个因素，如果你的MySQL有大数据量的表，就需要花时间研究建立最优秀的索引，或优化查询语句。

因此应该只为最经常查询和最经常排序的数据列建立索引。

MySQL里同一个数据表里的索引总数限制为16个。

5、MySQL 索引分类

参考资料：https://www.cnblogs.com/luyucheng/p/6289714.html
1、普通索引：是最基本的索引，它没有任何限制，即一个索引只包含单个列，一个表可以有多个单列索引；建议一张表索引不要超过5个，优先考虑复合索引
2、唯一索引：与前面的普通索引类似，不同的就是：索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一
3、主键索引：是一种特殊的唯一索引，一个表只能有一个主键，不允许有空值。一般是在建表的时候同时创建主键索引：
4、复合索引：指多个字段上创建的索引，只有在查询条件中使用了创建索引时的第一个字段，索引才会被使用。使用组合索引时遵循最左前缀集合
5、全文索引：主要用来查找文本中的关键字，而不是直接与索引中的值相比较。fulltext索引跟其它索引大不相同，它更像是一个搜索引擎，而不是简单的where语句的参数匹配

6、MySQL 索引语法

建立索引的 SQL 语句
创建索引：
如果是CHAR和VARCHAR类型，length可以小于字段实际长度；
如果是BLOB和TEXT类型，必须指定length。
CREATE [UNIQUE] INDEX  indexName ON mytable(columnname(length));
' or '
ALTER mytable ADD [UNIQUE]  INDEX [indexName] ON(columnname(length));
删除索引
DROP INDEX [indexName] ON mytable;
查看索引（\G表示将查询到的横向表格纵向输出，方便阅读）
SHOW INDEX FROM table_name\G

使用 ALTER 命令，有四种方式来添加数据表的索引：

alter table tbl_name add primary key (column_list)：该语句添加一个主键，这意味着索引值必须是唯一的，且不能为NULL。
alter table tbl_name add unique index_name(column_list)：这条语句创建索引的值必须是唯一的（除了null外，null可能会出现多次）。
alter table tbl_name add index index_name(column_list)：添加普通索引，索引值可出现多次。
alter table tbl_name add fulltext index_name(column_list)：该语句指定了索引为FULLTEXT，用于全文索引。

带你看看 mysql 索引：Index_type 为 BTREE
mysql> show index from tbl_emp;
+---------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table   | Non_unique | Key_name   | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+---------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| tbl_emp |          0 | PRIMARY    |            1 | id          | A         |           8 |     NULL | NULL   |      | BTREE      |         |               |
| tbl_emp |          1 | fk_dept_Id |            1 | deptId      | A         |           8 |     NULL | NULL   | YES  | BTREE      |         |               |
+---------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
2 rows in set (0.00 sec)

7、何时需要建索引

哪些情况下适合建立索引

主键自动建立唯一索引
频繁作为查询的条件的字段应该创建索引
查询中与其他表关联的字段，外键关系建立索引
频繁更新的字段不适合创建索引
Where 条件里用不到的字段不创建索引
单间/组合索引的选择问题，Who？（在高并发下倾向创建组合索引）
查询中排序的字段，排序字段若通过索引去访问将大大提高排序的速度
查询中统计或者分组字段

哪些情况不要创建索引

表记录太少
经常增删改的表
数据重复且分布平均的表字段，因此应该只为经常查询和经常排序的数据列建立索引。注意，如果某个数据列包含许多重复的内容，为它建立索引就没有太大的实际效果。

案例分析：

1、假如一个表有10万行记录，有一个字段A只有T和F两种值，且每个值的分布概率大约为50%，那么对这种表A字段建索引一般不会提高数据库的查询速度。
2、索引的选择性是指索引列中不同值的数目与表中记录数的比。如果一个表中有2000条记录，表索引列有1980个不同的值，那么这个索引的选择性就是1980/2000=0.99。
3、一个索引的选择性越接近于1，这个索引的效率就越高。