看见新力量NO.13｜独家专访车麦&超电创始人孙泽锋
06-18
在日常工作中，我们难免会遇到SQL慢的问题。例如，当我在以前的公司时，我会定期收到DBA飚的电子邮件。
Oracle AWR报告并特别提醒我，某条SQL语句近阶段明显很慢，可能需要优化。通常大家遇到这样的问题第一反应就是看看SQL是否写的不合理，比如：“避免使用in和not in，否则可能会导致全表扫描”“避免对where子句中的字段执行函数操作” “ 等等。
另一个常见的反应是这个表是否有索引？大多数情况下，只需添加索引就足够了。既然题目是《从千万级数据查询来聊一聊索引结构和数据库原理》，我们先构建一个千万级别的表来直观感受一下。
我们创建了一张user表，然后插入了10000条数据，查询：image.png花了近30秒。这还是单表查询，关联查询显然会更难以忍受。
接下来，我们只需为 id 添加索引并再次验证即可：image.png 从 30s 增加到了 0.02s，这是整整 1 倍的改进。为什么添加索引后速度突然变快了？我们从【索引数据结构】和【Mysql原理】两个方面入手。
1、索引数据结构我们首先看一下MySQL官方对索引的定义：索引（Index）是一种帮助MySQL高效获取数据的数据结构。这里有两个关键词：高效搜索和数据结构。
对于数据库来说，查询是我们最重要的功能，而且查询速度越快越好。最基本的搜索是顺序搜索。
为了更高效的搜索，我们自然会想到二叉树、红黑树、哈希表、BTree等。 1.1 二叉树大家都很熟悉。
它有一个很重要的特点：左节点的键值小于根的键值，右节点的键值大于根的键值。例如图1，它确实可以显着提高我们的搜索性能。
但如果将其用作数据库中的索引，显然存在很大的缺陷。但对于像图2这样递增的ID，存储后的索引近似成为一个单边链表，这肯定是不合适的。
image.pngimage.png1.2 红黑树也称为平衡二叉树。 JDK1.8之后，HashMap还将底层链表优化为红黑树（我们可以在后续文章中讲到Hashmap1.8之后的调整）。
平衡二叉树结构使树结构更加完善，显着提高了搜索操作的速度。但缺点也很明显。
插入和删除操作变得复杂，从而降低了操作速度。对大数据量的支持很差。
当数据量很大时，树的高度太高。如果搜索的数据是叶子节点，仍然会超级慢。
image.png1.3 BTreeB-Tree 是专为磁盘等外部存储设备设计的平衡搜索树。系统从磁盘读取数据到内存时，基本单位是磁盘块（block）。
位于同一磁盘块的数据将被一次性读入内存。 Mysql存储引擎中有页的概念，页是其磁盘管理的最小单位。
Mysql存储引擎中每个页面的默认大小是16KB。查看方法： 代码语言：txt 复制 mysql> show variables like 'innodb_page_size';image.png 我们也可以修改为4K、8K、16K。
系统中一个磁盘块的存储空间往往不是16K，因此Mysql每次申请磁盘空间时，都会将几个地址串联成磁盘块，达到16KB的页面大小。 Mysql将磁盘数据读入磁盘时以页为基本单位。
在查询数据时，如果一页中的每条数据都可以帮助定位数据记录的位置，这样会减少磁盘I/O的次数。提高查询效率。
如上图所示，B树包含键值、用于存储子节点的指针信息以及主键以外的数据。与普通树相比，BTree增加了水平节点的容量，可以存储更多的索引。
1.4 B+Tree 在B-Tree的基础上，专家们开发了很多变体，其中最常见的是B+Tree。 MySQL一般使用B+Tree来实现其索引结构。
image.png 与B-Tree相比，B+Tree做了以下改进： 1、非叶子节点只存储键值信息，大大增加了索引存储的数据量。 2.所有叶子节点之间都有一个链接指针。
对于区间查询，不需要从根节点开始，直接定位数据即可。 3. 数据记录存储在叶子节点中。
根据二叉树的特点，这是一个顺序访问指针，提高了区间访问的性能。通过这样的设计，千万级的表只需要最多3次磁盘交互就能找到数据。
2.Mysql原理讲解。这一部分我们选取日常采访或者使用过程中常见的几个问题，以问答的形式进行讲解。
2.1.数据库引擎MyISAM和InnoDB有什么区别？ MyISAM：Mysql8之前，默认引擎是MyISAM，其目标是快速读取。特点： 1、阅读速度非常快。
如果插入和更新频繁，效率不高，因为涉及到数据的全表锁。 2. 保存数据库行数。
执行count时，不需要扫描全表； 3. 不支持数据库事务； 4、不支持行级锁和外键； 5. 不支持故障恢复。 6.支持全文检索FullText、压缩索引。
推荐使用场景： 1. 做大量的count计算（如果count计算后有where，仍然会扫描全表） 2. 插入和更新较少，查询较频繁的InnoDB：在Mysql8中，默认存储引擎更改为 InnoDB 。特性 1. 支持事务处理和 ACID 事务特性 2. 实现 SQL 标准的四种隔离级别 3. 支持行级锁和外键约束 4. 可以使用事务日志进行数据恢复 5. 不支持 FullText 类型索引，不支持保存数据库行数和计算count(*)需要全局扫描 6.支持列属性自动递增auto_increment 7.最后也是非常重要的一点：InnerDB是为了处理大量数据时的最大性能而设计的，它的CPU效率可能比其他基于磁盘的更高。
关系数据库无法比拟的。场景一、可靠性高或者需要事务处理时，建议指定InnerDB存储引擎。
2、表更新和查询相当频繁，表锁的几率比较高。 2.2 Mysql中表和数据是如何存储的。
我们新建一个数据库mds_demo，其中有两个表：order_info、userimage.png。我们找到mysql存放数据的数据目录。
里面有一个mds_demo文件夹。同时我们还找到了order_info和user文件。
image.png 为什么两个表生成不同的文件？原因很简单，因为创建这两个表使用了不同的引擎 image.pngimage.png MyISAM 引擎创建表时，会创建三个文件。 MYD文件：存放表中的数据。
MYI文件：存放索引数据。 .sdi 文件：序列化词典信息的缩写。
Mysql5中没有sdi文件，但是有一个FRM文件，用户存储表结构信息。 MySQL8.0中重新设计了数据字典，改为sdi。
MyISAM的索引和数据分离，并且索引被压缩，所以存储文件会小很多。 MyISAM 可以非常快速地恢复由错误代码引起的数据。
InnerDB引擎创建表时只有一个file.ibd，既存储索引又存储文件。参见B+树。
所以也称为聚集索引，即叶子节点包含完整的索引和数据，对应的MyISAM是非聚集索引。补充说明：存储引擎是针对表的，不是针对数据库的。
同一库中的不同表可以使用不同的引擎。 2.3 为什么InnoDB必须有主键，建议使用整数自增主键？这个问题其实从上面的解释就已经很清楚了。
为了满足MySQL的索引数据结构B+树的特点，必须使用索引作为主键，这样可以有效提高查询效率。有的小朋友可能会说我可以创建一个没有主键的表。
这其实和Oracle的rownum是一样的。如果不指定主键，InnoDB会从插入的数据中查找唯一的列作为主键索引。
如果没有找到，则不会使用。对于重复的列，InnoDB会在后台添加一个rowId列作为主键索引。
那么为什么不自己创建一个主键呢？将索引的数据类型设置为整数会占用更少的磁盘空间或内存空间。另一方面，整数类型比字符串要快，字符串需要先转换为ASCII码，然后再逐个处理。
比较。请参阅 B+ 树的图。
它本质上是一个多路多叉树。如果主键索引不是自增的，那么后续插入的索引会导致B+树中其他节点的分裂和重新平衡，影响数据插入的效率。
如果要自增主键，只需要添加到尾节点即可。最后，我想强调一点：无论当前是否有性能需求或数据量，绝对不要使用UUID作为索引。
2.4 为什么Mysql存储引擎中每页的默认大小是16KB？假设我们一行数据的大小为1K，那么一页可以存储16条数据，包括指针+数据+索引。假设一行数据大小为1K，那么一页（一个叶子节点）可以存储16条数据；对于非叶子节点，假设ID为bigint类型，长度为8B，Innodb源码中指针大小为6B，总共14B。
，那么一页可以存储16K/14=（主键+指针），所以高度为3的B+树可以存储的数据为：16=2000万级。所以我们的前10000条数据只有0.02s。
2.5 HASH算法的使用场景 image.png Hash算法是一种哈希算法，计算某个字段的哈希值，然后将其存储到对应的地址中。查找数据时，只需定位一次，而不是像BTree那样从根节点开始。
查找叶子节点需要多次IO操作，因此查询效率非常高。但也有很多缺点。
我们来谈谈最重要的两个。 1.显然hash只支持=、IN等查询，不支持范围查询。
2、哈希索引任何时候都无法避免表扫描。所以使用时要小心。
感谢您关注公众号“码叔”。您也可以添加我的个人微信“qiaojs”，我们一起交流、学习Java！微信公众号：码大叔十年“码”，老“大叔”开花了 码大叔.jpg 图片：本文部分图片来源于网络，版本归原作者所有。
版权声明：本文内容由互联网用户自发贡献，本站不拥有所有权，不承担相关法律责任。如果发现本站有涉嫌抄袭的内容，欢迎发送邮件 举报，并提供相关证据，一经查实，本站将立刻删除涉嫌侵权内容。
标签：
相关文章
06-17
06-18
06-18
06-18
06-18
最新文章
【玩转GPU】ControlNet初学者生存指南
【实战】获取小程序中用户的城市信息（附源码）
包雪雪简单介绍Vue.js：开学
Go进阶：使用Gin框架简单实现服务端渲染
线程池介绍及实际案例分享
JMeter 注释 18 - JMeter 常用配置组件介绍
基于Sentry的大数据权限解决方案
【云+社区年度征文集】GPE监控介绍及使用