msql优化--索引优化

╰+攻爆jí腚メ 2022-06-12 03:36 267阅读 0赞

关于数据库优化指标主要为：响应时间，扫描行数，返回行数。对于响应时间，主要体现在服务器级别，服务器性能，网络响应速度等；索引主要为了减少扫描行数；根据limit来限制返回行数。

常用的索引有：B-Tree索引，哈希索引，空间索引，全文索引。除此之外，按照数据存储方式可以分为聚集索引和非聚集索引。

##  ##

# 【索引类型】 #

B-Tree索引

![20170714163416725][]

B-tree索引按照二叉树的方式进行存储，叶子页保存着索引的值，枝节点按照一定范围进行存储索引值。mysql查询通过访问枝节点快速定位到叶子节点，通过叶子节点存储的行地址查找到磁盘中的数据，这样减少扫描数据行。

B-Tree索引的查找方式：全键值查找（遍历所有的索引值），键值范围（in语句查找等），建前缀查找（match匹配方式查找），精确某一列范围匹配另外一列，值访问索引查询（直接定位到某一个值）。

在使用B-Tree索引作为查询条件时有很多限制条件，注意这些限制条件对我们的查询优化有很多帮助，下面通过例子的方式进行描述：

![20170714165402465][]                                                                             ![20170714165414264][]

1. 在多列索引中，必须按照最左列开始查找，不然无法使用索引特性。例如上例：where条件不能使用last\_name,first\_name,dob的顺序进行**索引查找**。

2. 不能跳过索引列。例如：查询条件first\_name,dob的查询将不能发挥索引特性。

3. 查询条件中部使用范围查询，则右边的条件则不能使用索引查询。例如：first\_name使用了in条件查询，则last\_name,dob则不是索引查询。

##    哈希索引 ##

哈希索引只能精确匹配索引列值才能发挥作用，其存储结构是将索引值通过哈希算法将键值进行存储。且哈希索引支持的数据引擎是memory，我们通用的InnoDB数据引擎是无法直接支持哈希索引的，它可以通过自适应哈希索引技术模拟哈希索引的功能。

![20170714170933433][]

哈希索引的存储方式按照键值顺序存储，属于常数查找，存储内容与B-Tree索引相比不会存储索引列的值。因此查询时先访问索引页，然后通过行地址访问数据行，减少扫描数据行。

哈希索引的限制：

1. 索引页只包含哈希值和行指针，不包含数据值。

2. 数据行不是按照索引值的顺序来存储的，这样在排序操作时，索引列顺序和数据行顺序不一致导致性能消耗。

3. 必须匹配索引列的值，不能使用模糊匹配（like，match语句）

4. 只支持等值比较（<,>,<>则不支持）

##     聚簇索引 ##

聚簇索引是InnoDB支持，但MyISAM数据引擎不支持的一种数据存储方式。它将数据行放入到索引页中，存储方式与B-Tree索引及其相似。

InnoDB通过主键来聚集数据，如果没有主键则通过唯一非空索引代替。

![20170714172743645][]

如上图，聚簇索引的索引值和数据保存在一起，这样访问速度更快。它任有一些限制：

1. 聚簇索引将数据行全部放入内存中，访问顺序不在重要了。

2.  插入顺序严重依赖杀入的位置，数据行的移动导致速度降低，因此不适合经常进入插入数据的表应用。

3.  当一个索引页无法存放所有的数据时，出现索引页断裂的问题

4. 二次索引需要扫描全表，速度下降。

#     【高效索引策略】 #

当我们了解了索引的存储特点及限制，则在建立索引的时候就能够有效的避免这些索引限制，下面我们通过如何选择索引列，索引列的数值最好具备什么样的特性，以及应用索引列的技巧进行阐述：

1. 索引要作为单独的列，不能使用表达式一部分或者函数的参数。

2. 索引列要尽可能简短，从性能考虑使用UUID作为索引列是很糟糕的！它在扫描查询，维护顺序上要消耗更多的资源。

3. 选择合适的前缀索引，在每个索引值对应的数据均匀的前提下，根据索引选择性进行判断（去重索引列值个数/总行数）

4. 对于单个索引来说，将查询出最少的数据条件放到前面。

5. 按照索引的顺序进行排序操作（可以使用explain命令，type类型是index）

6. 关联多张表时，on条件都使用索引列，order by子句都为第一个表时，才使用索引做排序。

#    【总结】 #

索引在查询提供了很大的便利，但是在更新和插入操作又带来了负担，我们最好在执行增删改操作的时候禁用索引，然后再启用。

[20170714163416725]: /images/20220612/021d48cbcff34493b9b2eaebe20a370a.png
[20170714165402465]: https://img-blog.csdn.net/20170714165402465
[20170714165414264]: /images/20220612/8a68a43f7d60405dac5b07e9c027f3ee.png
[20170714170933433]: /images/20220612/5dc1149396d3415e810e5f069aa80d1b.png
[20170714172743645]: /images/20220612/57fd55a360ee40fc8946dc78f26d3189.png