mysql组件及查询流程、索引原理

mysql的内部组件构成如下 ：

客户端首先通过连接器(默认3306端口)与mysql服务器建立连接，连接器将数据库中客户端使用的用户的权限相关的表在连接建立时加载到内存中， 然后通过内存中的权限校验用户的身份、各种操作权限。

客户端在提交各种sql语句并且权限校验通过之后， 如果是查询， 先查询缓存区(内存中)是否有缓存(sql语句为key， 查询结果为value， 这个在后续的版本中被移除了)。没查到则进入 词法分析器 中， 词法分析器对语句的合法性 等做出校验， 然后再将分析后的sql拿到缓存区去查。

如果词法分析器这里没返回， 则进入优化器， 优化器会对查询等做出优化。

优化器优化完成后将语句提交给执行器。

执行器则取操作存储引擎上(磁盘上)存储的数据。

其中存储引擎层就是我们数据最终存储在磁盘上的那一步。

常见的存储引擎有 innodb、 myisam、 memory。

而我们的索引是存储引擎的一部分。

索引有 B+树 和 hash表 两种。

【分块读取】：当我们的内存不足以一次性将磁盘上的文件读取出来的时候， 可能就要

每次1G1G的读(分治思想)

【磁盘预读】：内存跟磁盘在交互的时候有一个最小的逻辑单位，这个单位称之为“页”， 或者datapage，一般是4k或者8k，由操作系统决定， 我们在进行数据读取的时候， 一般读取页的整数倍， 也就是4k、8k、16k。innodb存储引擎在进行数据加载的时候默认读取16k的数据。

为什么不使用哈希表？

Memory存储引擎支持哈希， innodb存储引擎支持自适应哈希。

1、如果我们的哈希算法不好， 会导致哈希碰撞，而哈希表用拉链的方式解决哈希碰撞， 最终导致数据散列不均匀，造成存储空间的浪费。

2、哈希表是无序的，当需要进行范围查找的时候，需要挨个遍历，效率比较低。

什么是B树？什么是B+树？有什么区别？

想象一下， 我们最终要将数据存储到磁盘上， 要进行各种增删改查，我们的索引是为了提高查询效率而存在的， 他既要保证可以高效的进行查询， 又要方便增删改。

如果仅仅是满足查询， 我们直接使用有序数组， 然后使用二分查找是最简单高效的。

但是使用数组在我们增删改的时候， 为了维持数组的顺序可能需要一次挪动大量数据。 效率非常低。

而我们使用链表的话改动是方便，但是查询又会非常低效。

最终我们的索引选择了树这种既能高效查找， 又能方便增删改的数据结构。

那为什么不选择二叉树呢？

使用二叉树的话， 查询效率是没问题的(速度快、次数少)，但是如果数据量比较大可能会有性能上会有问题，因为使用二叉树有一个前提是所有的数据都必须在内存中查找。而索引是存储在磁盘上的，数据量不大是可以全部加载到内存中，但是数据量很大的时候，内存可能就不足了。就需要逐一的加载每一个磁盘页，磁盘页对应着搜索树的节点，使用二叉树的话， 我们的节点会非常多，则使用的磁盘页也就非常多， 需要对磁盘进行的读写次数也非常多。

【树的查询指针每挪动一步，就需要加载一次磁盘，最坏情况下，磁盘的读写次数等于树的高度】。综上可以总结出， 想要提高索引的查询效率， 必须减少磁盘的读写， 而减少对磁盘的读写， 必须要减少所使用的树的高度。

我们可以使用多路平衡树(高度低)， 其中比较常见的就是B树，

B树的特征是 【每个节点最多可以包含 k 个孩子， k被称为树的阶， k的值取决于磁盘页的大小。】

如上， 他这样串起来之后其实是一个有序链表， 而且一个节点可以存储多个值，这样既保持了树的高效(次数跟二叉树差不多)， 又减少了树的高度。 进而减少了磁盘读写次数， 提高了索引的性能。

而B+树如下：

他将树分成了两部分， 所有的数据都顺序的存储在叶子节点上，叶子节点连成一个链表， 每个叶子节点可以存储多个元素。

他的其他非叶子节点则相当于链表的索引。

B+树每个节点上的元素个数是有上下限的，插入数据导致超过上限， 则叶子节点会分裂， 随之对应的 索引可能也会跟着改变。删除数据的时候节点会合并， 随着索引也可能跟着改变。

B树和B+树的区别 ：

1. B树的所有节点都保存数据， 而B+树只有叶子节点顺序保存数据(所以B+树在做范围查询的时候比B树好得多)
2. B树查询数据的做多次数为树的高度， 最少次数为1， 而B+树每次查询都要经过树的高度次。
3. 相同高度B+树能容纳更多元素(内节点不存储data， 所以能够存储更多的key)。

如果是 innodb 存储引擎(数据和索引在同一个文件中)。

B树中每个节点的大小为16k， 节点中需要存储 “键值(索引列)”、指针(存储字节的信息)、数据(表记录中除主键外的数据)， 这样就算每个节点能存储16条数据， 则一个三层的B树最多可以存储 ： 16 * 16 * 16 = 4096 条数据。实际上一个节点根本存不了16条， 所以三层的B树能存的数据更少。

相同层高B树虽然比二叉树村的多，但是还是不够，因为他的非叶子节点中data占用了大量存储空间。

我们如果把 data 从里面拿出去则可以存跟多数据。

B+树则有重复数据， 我们在存储的时候，将所有的 data 都存储到叶子节点去， 而非叶子节点就能存储更多的键值和指针。 【在树里面创建索引， 索引和数据是分开的， 叶子节点存数据， 非叶子节点存索引， 索引占用的空间较小】

将一个键值和一个指针分为一组， 假设一组占用10字节， 则一个16k的节点(一个磁盘块)可以存储1600组， 也就是第一层可以存1600个范围， 第二层可以存1600*1600个范围，

第三层每个节点可以存16条数据(叶子节点需要存 data )， 最终算下来一个三层的B+树可以存储 1600*1600*16 = 40960000 条数据， 是三层的B树存4096条的一万倍！！！

选择索引列的时候到底是用 int 还是 varchar 好呢？

因为索引列本身也是占用内存的，而且影响B+树的容量， 所以应该根据业务场景选择占用内存小的列作为索引！！！ 选择索引列的时候， 我们的索引列(key)要尽可能少的占用空间。

前置索引 ： 可以用某个列的前一部分来充当索引(比如varchar20， 有些列用了4个字节， 有些用了16个， 那我可以考虑用这个列的前10个或者多少来充当索引，具体用多少需要统计分析)

聚簇索引和非聚簇索引：

数据跟索引存储在一起(同一棵B+树里面)的叫做聚簇索引， 没有存储在一起(叶子节点中没有存储完整的数据)的叫非聚簇索引。

Innodb在进行数据插入的时候，数据必须跟某一列索引存储在一起(同一棵B+树中)， 这个索引列可以是 主键、唯一键、6字节的rowid， 没有前者则选择后面的。数据必定是跟某一个索引绑定在一起的， 绑定数据的索引叫做聚簇索引。

其他索引中叶子节点存储的数据不再是整行数据， 而是聚簇索引的id值。

Innodb中既有聚簇索引又有非聚簇索引， myisam中只有非聚簇索引 。

所谓回表就是先通过非聚簇索引查找， 没有拿到需要的数据， 只拿到了聚簇索引的id， 然后需要通过这个id再次去聚簇索引中查找数据。

索引下推 ：

select * from table where name=”zhangsan” and age=12;

这个语句指向的时候， 存储引擎查找所有 name=”zhangsan” 的列返回给 server 层， server 层再去过滤 age=12 的列返回给客户端。

而索引下推则是存储引擎查询到 name=”zhangsan” 的列之后再过滤出 age=12的列返回给 server层。

索引下推减少了返回给 server 的数据量， 是一个好的筛选。

索引优化问题 ：

1. 表设计阶段根据业务需求选择合适的列作为索引(需要考虑字段类型、长度等)。
2. 测试阶段有时间的话可以针对日志中出现的每条sql语句通过执行计划去分析优化。
3. 出现问题的时候先去日志中查看慢sql， 然后针对这些慢sql， 通过执行计划去分析具体原因(是没常见索引还是索引失效等)， 根据具体的情况去调整索引或者优化sql。
4. 分库分表。

什么样的列适合做主键？

为什么数据库尽量不用UUID作为主键_wangxuelei036的博客-CSDN博客_为什么不用uuid做主键

B+树是有序的， 我们在往树里面添加元素的时候， 如果是从小到大顺序添加， 直接加在末尾就行了， 页的分裂也只是在末尾分裂，会比较快。 但是如果我们添加元素的时候是无序的， 从树的中间添加， 那么就是无序的， 一个是需要查找元素位置，一个是需要对原来的页进行分裂， 会做很多额外的工作。 所以尽量选择有序的数据作为主键。 而uuid一般都是无序的， 每次都更可能在中间插入，效率就差一些。

往末尾直接追加是顺序i/o操作，不需要过多的寻址， 完中间插入是随机i/o， 需要先寻址再插入数据， 寻址过程比较慢。