mysql索引的认识

最新推荐文章于 2022-11-21 23:15:00 发布

Risk_

最新推荐文章于 2022-11-21 23:15:00 发布

阅读量106

点赞数

分类专栏：数据库sql 文章标签：索引

本文链接：https://blog.csdn.net/weixin_39225655/article/details/102679783

版权

数据库sql 专栏收录该内容

5 篇文章 0 订阅

订阅专栏

Sql优化之索引认识

问题

(1)你一般怎么建索引的？

(2)讲讲索引的分类？你知道哪些？

(3)如何避免回表查询?什么是索引覆盖?

(4)现在我有一个列，里头的数据都是唯一的，需要建一个索引，选唯一索引还是普通索引？

(5)mysql索引是什么结构的？用红黑树可以么？

(6)mysql某表建了多个单索引，查询多个条件时如何走索引的？

查询慢sql 确认要添加的索引

打开慢查询日志
slow_query_log=1
慢查询日志存储路径
slow_query_log_file=/var/log/mysql/log-slow-queries.log
SQL执行时间大于3秒，则记录日志
long_query_time=3

监控到慢SQL后，就马上开始建索引?例如，当只要一行数据时使用 limit 1

然而大多数情况下，业务SQL十分复杂，没法优化。所以就要建立索引了。这个时候，参照如下规则建立索引

(1)索引并非越多越好，大量的索引不仅占用磁盘空间，而且还会影响insert,delete,update等语句的性能

(2)避免对经常更新的表做更多的索引，并且索引中的列尽可能少；对经常用于查询的字段创建索引，避免添加不必要的索引

(3)数据量少的表尽量不要使用索引，由于数据较少，查询花费的时间可能比遍历索引的时间还要短，索引可能不会产生优化效果

(4)在条件表达式中经常用到不同值较多的列上创建索引，在不同值很少的列上不要建立索引。比如性别字段只有“男”“女”俩个值，就无需建立索引。如果建立了索引不但不会提升效率，反而严重减低数据的更新速度

(5)在频繁进行排序或者分组的列上建立索引，如果排序的列有多个，可以在这些列上建立联合索引。

讲讲索引的分类？你知道哪些？从物理存储角度:从数据结构角度:从逻辑角度:

主键索引：主键索引是一种特殊的唯一索引，不允许有空值

普通索引或者单列索引

多列索引（复合索引）：复合索引指多个字段上创建的索引，只有在查询条件中使用了创建索引时的第一个字段，索引才会被使用。使用复合索引时遵循最左前缀集合

唯一索引或者非唯一索引

空间索引：空间索引是对空间数据类型的字段建立的索引，MYSQL中的空间数据类型有4种，分别是GEOMETRY、POINT、LINESTRING、POLYGON。

如何避免回表查询?什么是索引覆盖?

这里简单说一下。

例如此时有一张表table1,有一个联合索引(a,b)

执行如下SQL

select a,b from table1

在索引上就能找到结果，就不用回表去查询！

select a,b,c from table2

c列在索引上不存在，就需要回表查询。

需要说明的是覆盖索引必须要存储索引列的值，而哈希索引、空间索引和全文索引不存储索引列的值，所以mysql只能用B+ tree索引做覆盖索引。

现在我有一个列，里头的数据都是唯一的，需要建一个索引，选唯一索引还是普通索引？

	【强制】业务上具有唯一特性的字段，即使是多个字段的组合，也必须建成唯一索引

说明：不要以为唯一索引影响了 insert 速度，这个速度损耗可以忽略，但提高查找速度是明显的；另外，即使在应用层做了非常完善的校验控制，只要没有唯一索引，根据墨菲定律，必然有脏数据产生。

为什么唯一索引的插入速度比不上普通索引？为什么唯一索引的查找速度比普通索引快？

这么做的优点:能将多个插入合并到一个操作中，就大大提高了非聚簇索引的插入性能。

InnoDB 从 1.0.x 版本开始引入了 Change Buffer，可以算是对 Insert Buffer 的升级。从这个版本开始，InnoDB 存储引擎可以对 insert、delete、update 都进行缓存。

唯一速度的插入比普通索引慢的原因就是:

唯一索引无法利用Change Buffer

普通索引可以利用Change Buffer

为什么唯一索引的更新不使用 Change Buffer？

最后回答一下，唯一索引的搜索速度比普通索引快的原因就是:

普通索引在找到满足条件的第一条记录后，还需要判断下一条记录，直到第一个不满足条件的记录出现。

唯一索引在找到满足条件的第一条记录后，直接返回，不用判断下一条记录了。

mysql索引是什么结构的？用红黑树可以么?

那为啥不用B Tree,而选择B+ tree呢？

在这里插入图片描述

注意一下B tree的两个明显特点
树内存储数据

叶子节点上无链表

而B+ tree长下面这样的
在这里插入图片描述

注意一下B+ tree的两个明显特点

数据只出现在叶子节点

所有叶子节点增加了一个链指针

接下来就可以开始编了～～比如数据库索引采用B+ tree的主要原因是B Tree在提高了磁盘IO性能的同时并没有解决元素遍历的效率低下的问题。正是为了解决这个问题，B+ tree应运而生。B+ tree只要遍历叶子节点就可以实现整棵树的遍历。而且在数据库中基于范围的查询是非常频繁的，如果使用B Tree，则需要做局部的中序遍历，可能要跨层访问，效率太慢。