一、Mysql索引类型产品建模
1.mysql索引类型
1、 B+Tree索引
单列索引主键索引（不能为空）设定为主键后数据库会自动建立索引唯一索引（可为空）索引列的值必须唯一，但允许有空值普通索引（可重复）可以为空，可以重复前缀索引前缀索引只考虑列值的前几个字符组合索引（一个索引包含多个列）最左前缀全文索引全文索引和B+Tree的LIKE查询功能类似，LIKE语句通常会导致全表扫描，特别是在大型表中可能效率较低MySQL中的全文索引是通过构建倒排索引来实现的，将词映射到包含这些单词的文档的索引结构倒排索引可以快速高效的全文搜索和模糊查询2、聚簇索引
MyISAM引擎通常用于支持非聚簇索引InnoDB默认是支持聚簇索引（也支持非聚簇索引）聚簇索引聚簇就是按照每张表的主键构造一颗B+树，叶子节点中存放的就是整张表的行记录数据缺点：插入速度严重依赖于插入顺序非聚簇索引（数据是分离的）辅助索引叶子节点存储的不再是行的物理位置，而是主键值通过赋值索引首先找到的是主键值，再通过主键值找到数据行的数据页关键区别聚簇索引确定实际物理存储位置，非聚簇索引仅包含索引列的值和指向实际数据行的指针一张表只能有一个聚簇索引，但可以有多个非聚簇索引性能影响对于范围查询或按索引排序，聚簇索引通常具有更好的性能非聚簇索引对于单列的查找和特定的连接操作可能更高效（范围和排序查询性能差）3、hash索引
Hash索引通过将索引列的值通过哈希函数映射到一个哈希表的桶中，从而实现快速的索引查找在MySQL中，Hash索引的使用场景相对有限，在范围查询、排序等场景喜爱表现不佳4、空间索引
空间索引支持对空间数据进行快速的空间查询和分析空间索引可用于存储和查询地理位置信息，如移动设备的实时位置、商家的地理分布等通过空间索引，可以在地理空间中快速找到相关的数据2、组合索引
1、复合索引特点
联合索引可以为多个字段创建一个索引比如，我们在（a,b,c）字段上创建一个联合索引，则索引记录会首先按照A字段排序，然后再按照B字段排序然后再是C字段其实联合索引的查找就跟查字典是一样的，先根据第一个字母查，然后再根据第二个字母查或者只根据第一个字母查，但是不能跳过第一个字母从第二个字母开始查。这就是所谓的最左前缀原理联合索引的特点就是：第一个字段一定是有序的当第一个字段值相等的时候，第二个字段又是有序的，比如下表中当A=2时所有B的值是有序排列的，依次类推，当同一个B的所有C字段是有序排列的2、最左前缀原理
'''最左前缀原理'''
-- 1、以下的查询方式都可以用到索引
select * from table where a=1；
select * from table where a=1 and b=2；
select * from table where a=1 and b=2 and c=3；
-- 上面三个查询按照 （a ）, （a，b ）,（a，b，c ）的顺序都可以利用到索引，这就是最左前缀匹配。
-- 2、如果查询语句是（只会用到索引a）
select * from table where a=1 and c=3；
-- 3、这样不会用的索引产品建模
select * from table where b=2 and c=3；
-- 因为没有用到最左前缀a，所以这个查询是用户到索引的
3、mysql索引失效
产品建模1、like语句失效
like以%开头，索引无效；当like前缀没有%，后缀有%时，索引有效-- 不能命中索引
select https://www.kaidanwy.cn * from table where name like "%Snial%"
select * from table where name like "%Snail"
--可以命中索引
select * https://www.honxinguu.cn from table where name like "Snail%"
2、or语句失效
当or左右查询字段只有一个 是索引，该索引无效，只有当or左右查询字段均为索引时，才会生效-- 命中索引
select * from table where uid=24 or uid=18;
-- 无法命中索引
select * https://www.kuailewang.cn from table where uid=24 or name=Snail;
3、组合索引失效
组合索引，不是使用第一列索引，索引失效-- 只会用到索引a
select * from table wher a=1 and c=3;产品建模
--因为没有用到最左前缀a，所以无法使用索引
select * from table where b=2 and c=3;
--命中索引
select * https://www.cpdpmdd.cn from table where a=1 and b=2 and c=3;
二、B-tree/B+tree
1、B-Tree
每个节点占用一个盘块的磁盘空间，一个节点上有两个升序排序的关键字和三个指向子树根节点的指针，指针存储的是子节点所在磁盘块的地址两个关键词划分成的三个范围域对应三个指针指向的子树的数据的范围域以根节点为例，关键字为17和35，P1指针指向额子树的数据范围为小于17，P2指针指向的子树的数据范围为17-35，P3指针指向的子树的数据范围为大于35.img
模拟查找关键字29的过程```python '''模拟查找关键字29的过程：'''根据根节点找到磁盘块1，读入内存。【磁盘I/O操作第1次】比较关键字29在区间（17,35），找到磁盘块1的指针P2。根据P2指针找到磁盘块3，读入内存。【磁盘I/O操作第2次】比较关键字29在区间（26,30），找到磁盘块3的指针P2。根据P2指针找到磁盘块8，读入内存。【磁盘I/O操作第3次】在磁盘块8中的关键字列表中找到关键字29。```2、B+tree
B+Tree是在B-Tree基础上的一种优化，使其更适合实现外存储索引结构，InnoDB存储引擎就是用B+Tree实现其索引结构B-Tree结构图中可以看到每个节点中不仅包含数据的key值，还有data值而每一个页的存储空间是有限的，如果data数据较大时将会导致每个节点(即一个页)能存储的key的数量很小当存储的数据量很大时同样会导致B-Tree的深度较大，增大查询时的磁盘I/O次数，进而影响查询效率在B+Tree中，所有根节点只存储键和指针，只有叶子节点才存放数据img
因为B+树索引的所有数据均存储在叶子节点，而且数据是按照顺序排列的那么B+树使得范围查找，排序查找，分组查找以及去重查找变得异常简单B+树各个页之间是通过双向链表连接的，叶子节点中的数据是通过单向链表连接的3、Mysql底层索引存储
InnoDB存储引擎中页的大小为16KB（B+树非叶子节点存储键+指针）索引指针在InnoDB设置为6个字节，而ID类型bigint（long）占8个字节，即14字节那么一页存储数据：16*1024/14=1170行3层深度B+树存储最大行数约为2000万条数据16*1170*1170 = 21902400行（约为两千万数据）B+Tree叶子节点存放的数据而不是键+指针所以江湖流传的2000万数据是把每行数据的大小当做1kb计算的所以每个页（叶子节点）有16kb，能存16条数据4层深度B+树存储最大行数https://www.vwiuisa.cn约为2000万条数据16*1170*1170*1170 = 25625808000行（约为256 亿数据）说明：实际情况中每个节点可能不能填充满，因此在数据库中，B+Tree的高度一般都在2-4层InnDB根节点常驻内存，查找某一键值的行记录时最多只需要1-3次磁盘I/O操作三、聚集索引&非聚集索引
1、回表和索引覆盖
回表在InnoDB存储引擎里，利用辅助索引查询，先通过辅助索引找到主键索引的键值再通过主键值查出主键索引里面没有符合要求的数据它比基于主键索引的查询多扫描一颗索引树，这个过程就叫回表索引覆盖在辅助索引里面，不管是单列索引还是联合索引如果select的数据列只用辅助索引中就能够取得，不用去查主键索引这时候使用的索引就叫索引覆盖，避免了回表2、聚集&非聚集区别
在MySQL中，B+树索引按照存储方式的不同分为聚集索引和非聚集索引
InnoDB（聚集索引）主键索引的叶子节点包含实际的数据行（InnoDB每张表必须有一个主键）以主键作为B+树索引的键值而构建的B+树索引，我们称之为聚集索引MyISAM（非聚集索引）主键索引和数据是分开存储的，叶子节点存储该列对应的主键（不存储表中的数据）需要根据主键再去聚集索引中进行查找（我们称为回表）存储方式InnoDB使用聚簇索引，数据行存储在聚簇索引中而MyISAM使用非聚簇索引，数据行和索引分开存储索引结构InnoDB的B+Tree索引中，叶子节点存储的是https://www.usyhgkq.cn 数据行非聚簇索引的B+Tree索引中，叶子节点存储的是指向数据行的指针索引维护InnoDB的B+Tree索引需要维护聚簇索引和辅助索引（secondary index），因此维护代价较高MyISAM的B+Tree索引只需要维护辅助索引，兼职美工因此维护代价较低性能InnoDB的B+Tree索引在高并发情况下，由于需要维护聚簇索引和辅助索引，写入性能较差MyISAM的B+Tree索引只需要维护辅助索引，写入性能较好，读取大量数据是性能差于Innodb3、InnoDB和MyISAM存储区别
存储引擎是InnoDB,在data目录下会看到2类文件：.frm、.ibd*.frm --表结构的文件*.ibd--表数据文件存储引擎是MyISAM，在data目录下会看到3类文件：.frm、.myi、.myd*.frm--表定义，是描述表结构的文件。*.MYD--"D"数据信息文件，是表的数据文件。*.MYI--"I"索引信息文件，是表数据文件中任何索引的数据树1、存储区别比较
InnoDB的主键必须使用自增的字段，根据B+树的性质，如果字段自增，那么索引值是顺序添加的如果不是自增而是随机的，那么索引值增加的时候需要移动和分页的插入数据，造成不必要的开销而MyISAM没有这个限制，当然自增是最好的InnoDB非聚簇索引而Innodb的辅助索引的data存的是主键的值，因此每次根据辅助索引查询都要先查询辅助索引，拿到主键值，再去查主键索引MyISAM非聚簇索引MyISAM的索引和数据是分开的，它的主键索引和辅助索引结构是一样的，都是B+树而叶子节点的data存储的是指向具体数据存储的位置地址，而不是具体数据所以它的查询是先查了索引，然后回行IO读取数据2、InnoDB索引存储
在InnoDB中，系统为每张表依据主键建立一颗B+树，将数据直接存放在叶子节点上如果没有这样的索引，InnoDB会隐式的定义一个主键来作为聚簇索引如果根据id主键查找，只需要查找主键索引这一颗B+Tree即可非数字主键查找需要借助辅助索引如果对Name作为主键进行索引，那么就需要有一个辅助结构，先找到该取值对应的行再在另外一个辅助B+树上进行检索，读取行数据，这样的话就需要一个添加一颗辅助键索引的B+树img
3、MyISAM索引存储
非聚簇索引的B+树结构基本和聚簇索引一致只是非聚簇索引的数据是单独存储的，所以在叶子结点只存了定位数据的指针img
4、InnoDB索引查询
1、聚簇索引查询
假设我们要查找id>=18并且id<40的用户数据我们从页1找到id=18的键值，根据P2指针定位到页3在页3我们可以找到键值18，根据页3和p1指针定位到页8将页8读取到内存中后，因为页中的数据是链表进行有序链接的，可以二分法查找键值18下面是范围查找过程因为是范围查找，页8中找到键值22的数据，然后页8中就没有数据了此时我们需要拿着页8中的P指针去读取页9的数据（注意读取页9的指针是页指针）因为页9不在内存中，就又会加载页9到内存中，直到找到18到40之间的所有数据结束注：聚簇索引将数据直接存放在叶子结点上，直接就可以取出整个行数据了img
2、非聚簇索引查询
InnoDB的辅助索引的data存的是主键的值因此每次根据辅助索引查询，都要先查询辅助索引，拿到主键值，再去查主键索引假如我们有一张表包含两个字段id主键索引，user_id辅助索引下面要根据辅助索引查找用户 user_id=33的用户这行数据所有信息和聚簇索引查找流程一致，先找到 user_id=33 的用户主键 id=47再到主键索引 B+Tree 中查询行数据（这个流程就是上面查聚簇索引的流程）img
四、更新sql是如何执行的
1、更新语句写入过程
更新语句的执行是Server层和引擎层配合完成，数据除了要写入表中，还要记录相应的日志img
执行器先找引擎获取ID-这一行ID是主键，存储引擎检索数据，找到这一行如果ID=2这一行所在的数据页本来就在内存中，就直接返回给执行器否则，需要先从磁盘读入内存，然后再返回执行器拿到引擎给的行数据，按照sql需求修改数据，再调用引擎接口写入这行新数据引擎更新数据到内存和redo log中引擎将这行新数据更新到内存中同时将这个更新操作记录到redo log里面，此时redolog 处于oreoare状态然后告知执行器执行完成了，随时可以提交事务执行器生成这个操作的binlog，并把binlog写入磁盘执行器调用引擎的提交事务接口，引擎吧刚刚写出的redo log改成提交（commit）状态，更新完成为什么要两阶段提交呢？直接提交不行吗？先写入 redo log,后写入binlogredo log用于崩溃恢复和确保数据持久性，写入redo log系统崩溃会恢复这条数据如果binlog没有对上面的更新语句进行保存，导致mysql从库没有更新先写入binlog，后写入redo logbinlog用于主从同步如果binlog中有，redolog没有，崩溃导致主库没有写入，但是从库写入了2、创建sql写入过程产品建模
执行器先通过解析SQL语句，确定需要在那个表中创建新的数据行执行器根据SQL语句中的内容，生成新的行数据执行器调用存储引擎的接口，将新的行数据写入到存储引擎中如果新的行数据所在的数据页本来就在内存中，就直接写入到内存中否则，需要先分配新的数据页到内存中，然后再写入新的行数据引擎插入数据到内存和redo log中存储引擎将新的行数据写入到内存中并同时将这个插入操作记录到redo log里面，此时redo log处于prepare状态然后告知执行器执行完成了，随时可以提交事务执行器生成这个操作的binlog，并把binlog写入磁盘执行器调用存储引擎的提交事务接口，存储引擎把刚刚写入的redo log 改成提交（commit）状态，插入操作完成注意：在上述过程中，如果表中定义了主键或者唯一索引那么在插入新的行数据时，需要检查新的行数据，是否满足主键或者唯一索引的约束条件如果不满足，那么插入操作会失败发布于：河南省特别声明：以上内容（如有图片或视频亦包括在内）来源于网络，不代表本网站立场。本网站仅提供信息存储服务。如因作品内容、版权和其他问题需要同我们联系的，请联系我们及时处理。联系方式：451255985@qq.com，进行删除。