【mysql篇-进阶篇】索引

我是不贪嘴吖

已于 2022-08-13 14:47:44 修改

阅读量302

点赞数 1

文章标签： mysql java 数据结构

于 2022-08-03 21:00:00 首次发布

本文链接：https://blog.csdn.net/xiaoxiaoxiang1/article/details/125773971

版权

索引概述

概念

是帮助mysql高效获取数据的数据结构（有序）。在数据之外，数据库系统还维护着满足特定查找算法的树结构，这些数据结构以某种方式引用（指向）数据，这样就可以在这些数据结构上实现高级查找算法，这种数据结构就是索引。

优点：

（1）提高查询效率：提高数据检索的效率，降低数据库的IO成本。
（2）提高排序效率：通过索引列对数据进行排序，降低数据排序的成本，降低CPU的消耗。

缺点：

（1）索引列占用空间；
（2）降低更新表的速度，如对表进行insert、update、delete时，效率降低。

索引结构：

mysql的索引是在存储引擎层实现的，不同的存储引擎有不同的结构，主要包含以下几种：

（1）B+Tree索引：最常见的索引类型，大部分引擎都支持B+Tree索引。
（2）hash索引：底层数据结构使用哈希表实现的，只有精确匹配索引列的查询才有效，不支持范围查询。
（3）R-Tree索引：空间索引是MyIsam引擎的一个特殊索引类型，主要用于地理空间数据类型，通常使用较少。
（4）Full-Text索引：是一种通过建立倒排索引，快速匹配文档的方式，类似于Lucene、Solr、ES。

二叉树

特点：左侧比根节点的数值小，右侧比根节点的数值大。
缺点：
（1）顺序插入时，会形成一个链表，查询性能大大降低。
（2）二叉树一个节点下面，最多只能包含两个子节点；大数据情况下，层级较深，检索速度慢。

红黑树

特点：自平衡的二叉树。
解决了二叉树顺序插入时，形成链表的缺点。
红黑树是自平衡的二叉树，所以也会存在，大数据情况下，层级较深，检索速度慢。

Btree

又称多路平衡查找树。
特点：
（1）在根节点、叶子节点、非叶子节点下面都有数据。
（2）在进行分裂后，向上分裂的数据，在叶子节点不会存在。
分裂演变地址：https://www.cs.usfca.edu/~galles/visualization/BTree.html

B+Tree

特点：
（1）非叶子节点只起到索引的作用，叶子节点用来存放数据。
（2）在进行分裂后，向上分裂的数据，在叶子节点也会存在。（所有的元素都会出现在叶子节点）
（3）叶子节点形成一个单向链表。
mysql索引数据结构对经典的B+Tree进行了优化。在原B+Tree的基础上，增加了一个指向相邻叶子节点的链表指针，就形成了带有顺序指针的B+Tree，提高区间访问的性能。（即双向链表）
分裂演变地址：https://www.cs.usfca.edu/~galles/visualization/BTree.html

hash

哈希索引就是采用一定的hash算法，将键值换算成新的hash值，映射到对应的槽位上，然后存储在hash表中。
会出现哈希冲突，哈希冲突通过链表来解决，向链表追加元素即可。
特点：
（1）hash索引只能用于对等比较（=、in）；不支持范围查询（between、>、<…）；
（2）无法利用索引完成排序操作；
（3）查询效率高，通常只需要检索一次就可以了，效率通常高于B+Tree索引。

面试题：

为什么innodb存储引擎选择使用B+Tree索引结构：

（1）相对于二叉树，层级更少，搜索效率高；
（2）对于BTree，无论是叶子节点爱是非叶子节点，都会保存数据，这样导致一页中存储的键值减少，指针跟着减少，要同样保存大量数据，只能增加树的高度，导致性能降低。
（3）相对于hash索引，B+Tree支持范围匹配及排序操作。
（4）B+Tree，只在叶子节点存放数据，搜索效率稳定；叶子节点形成双向链表结构，便于范围搜索和排序。
（一页是16K）

索引分类

在mysql数据库，将索引的具体类型主要分为以下几类：主键索引、唯一索引、常规索引、全文索引。
而在InnoDB存储引擎中，根据索引的存储形式，又可以分为以下两种：
聚集索引选取规则：

（1）如果存在主键，主键索引就是聚集索引；
（2）如果不存在主键，将使用第一个唯一索引作为聚集索引；
（3）如果表没有主键，或没有合适的唯一索引，则InnoDB会自动生成一个rowId作为隐藏的聚集索引。

聚集索引的叶子节点下是这一行的数据。
二级索引的叶子节点下是该字段值对应的主键值。
根据二级索引查找的时候会进行回表查询。
回表查询：

先到二级索引中查找数据，找到主键值，然后再到聚集索引中根据主键值，获取数据的方式，称之为回表查询。

索引的语法：

创建索引：
CREATE {UNIQUE | FULLTEXT} INDEX index_name ON table_name(index_col_name,…);
查看索引：
SHOW INDEX FROM table_name;
删除索引：
DROP INDEX index_name ON table_name;

SQL性能分析

SQL执行频率：

通过show [session/global] status;命令可以查看当前数据库insert、update、delete的访问频次。（session是查看当前会话；global是查看全局数据）
通过上面的命令，可以查看当前数据库是以查询为主，还是以增删改为主。

慢查询日志

慢查询日志记录了所有执行时间超过指定参数（long_query_time,单位：秒，默认10秒）的所有SQL语句的日志；
查看慢查询日志的开关是否开启的命令：slow_query_log;
若要开启慢查询日志，需要在mysql的配置文件（Linux环境下的位置如下：/etc/my.cnf）中配置如下信息：（Windows环境下找到my.ini文件）

#开启mysql慢查询日志开关
slow_query_log=1
#设置慢查询日志的时间为0.002秒，SQL语句执行时间超过0.002秒，就会视为慢查询，记录慢查询日志
long_query_time=0.002

Linux环境查看慢查询日志信息：/var/lib/mysql/localhost-slow.log
配置文件添加完成之后，执行重新启动mysql
（Windows环境考验直接从服务中重启）
重启之后再次查看是否打开慢查询：

profile

show profiles 能够在做SQL优化时，帮助我们了解时间都耗费到了哪里。
通过命令 select @@have_profiling;查看是否支持profile
通过命令 select @@profiling;查看profile开关是否开启（0是未开启，1是已开启。如未开启，就执行命令：SET profiling = 1;进行开启）
查看每一条SQL的耗时基本情况：show profiles;
查看指定query_id的SQL语句各个阶段的耗时情况：show profile for query query_id; ---->show profile for query 13;
查看指定query_id的SQL语句CPU的使用情况：show profile cpu for query query_id; ---->show profile cpu for query 13;