-
mysql索引笔记
1、二叉树：即当索引为递增时，会导致结构为链表形式（缺点，mysql不推荐）
2、红黑数：完全平衡二叉树，当数据量很大时会导致高度h很高，依然影响查询效率（mysql不推荐）
3、B-tree：
1）、叶子节点具有相同的深度
2）、所有的索引元素都不重复
3）、节点的数据索引从左往右递增排序（但是所有的叶子节点没有链表指向，因为每个节点的索引是不重复的）
注：空白节点表示指向下一个磁盘文件地址（6Byte）
4、B+tree ：每个非叶子节点（页）默认大小为16KB，mysql初始化将非叶子节点的索引加到内存，遍历到之后再将叶子节点对应的数据加载到内存就很快
1）、非叶子节点不存储data数据，只存储对应的索引，这样非叶子节点就可以存储更多的索引
2）、叶子节点包含所有索引的字段
3）、叶子节点用指针连接，区间查询就更快
注：空白节点表示指向下一个磁盘文件地址（6Byte）
5、存储引擎
存储引擎是形容数据表的
6、MyISAM索引引擎
myisam表存储是由三个文件组成，即：
XX_.frm文件：表语句结构
XX_.MYI文件：存放当前表中对应的索引
XX_.MYD文件：存放当前表中的所有数据
即索引文件和数据文件是分离的（非聚集）
7、Innodb存储引擎
innodb推荐主键：主键索引也是聚簇索引，B+树会根据主键字段的值比较大小建立顺序索引，所有推荐使用自增形式的主键
非主键索引：innodb下的非主键索引会依赖于主键索引，即非主键索引的叶子节点存放的data是对应主键索引的键值（再次带着主键值去主键索引查找对应索引的所有数据）
不推荐使用UUID：因为innodb本身主键索引会将对应的值进行大小比较建立索引，在这个过程中使用数字比较大小要比字符串比较大小来得快；另一个原因是使用UUID会促使索引节点分裂（构建难度加大，耗费性能）
8、什么是聚集索引与非聚集索引
聚集索引：B + 树中叶子节点包含完整的数据记录
非聚簇索引：叶子节点中索引与数据文件是分开的，及查找的时候先在内存查找到对应的索引，再将对应节点存放的磁盘地址数据再去查找
9、hash索引
inodb默认索引结构为B+树，但是对应hash索引的存储为：
hash表是基于数据加链表的存储结构，数组部分经过自己的hash算法算出对应的hash值，然后将对应的元素值（索引以及索引所在行的磁盘地址）放入到链表中
10、联合索引（多字段索引）
联合索引比多个单字段索引查询效率要高，多个字段按左依次进行大小排序索引（当所有的字段都相等时叶子节点data数据存放的主键索引值再次去主键索引中获取对应的所有值）
最左前缀原则：不可跳过前面的索引使用后面的索引（即a and c 也是不走索引的，注意联合索引与where 之后的顺序是没有关系的，即只要满足最左前缀的数据存在即可，不用考虑where 顺序，例如where c and b and a是走索引的）
注：联合索引表中还有主键索引的话，不管怎么排序都会走主键索引
联合索引又分为：
普通联合索引：数据可以重复
唯一联合索引：数据不能重复
语法不同，普通联合索引 以
create index ……开头，而 唯一联合索引 以create unique index ……开头 create index 索引名 on 表名(‘字段1’，‘字段2’)
create unique index 索引名 on 表名(‘字段1’，‘字段2’)
查看索引的语法：show index from 表名
删除索引语法： drop index 索引名 on 表名; 或者采用：alter table 表名 drop index 索引名
11、innodb数据结构（B+树结构）
一个叶子节点的数据存储结构如下图:
一页默认的大小为16kb，超过则进行加页；一个节点就是一页（page），非叶子节点存储的是页目录中对应的值；一个节点的页目录是在用户数据区域按照主键进行分组，存储主键分组后的最小值
注：插入数据时会根据主键自动按升序排序更新对应的所有节点的数据（所有不建议使用UUID作为主键）
多个节点联合起来
最后的B+树在MySQL中是结构如下图：
B+树在叶子节点的指针是双向指针（方便范围查找）
12、innodb中buffer pool缓存池
buffer pool一块内存区域，当数据库操作数据的时候，把硬盘上的数据加载到buffer pool，不直接和硬盘打交道，操作的是buffer pool里面的数据 ；
数据库的增删改查都是在buffer pool上进行，和undo log/redo log/redo log buffer/binlog一起使用，后续会把数据刷到硬盘上
默认大小为128M
修改buffer pool缓存池大小（ SET GLOBAL innodb_buffer_pool_size= ； ）
-
缓冲池(buffer pool)是一种降低磁盘访问的机制
-
缓冲池通常以页(page)为单位缓存数据；
-
缓冲池的常见管理算法是LRU（最近最少使用）
-
InnoDB对普通LRU进行了优化：
将缓冲池分为新生代（热点数据区）和老生代（冷数据区），入缓冲池的页，优先进入老生代，页被访问，才进入新生代，以解决预读失效的问题 ；
页被访问，且在老生代停留时间超过配置阈值的，才进入新生代，以解决批量数据访问，大量热数据被淘汰的问题
13、redo log（默认48M）
缓存池数据持久化一般为两种：
1、第一种（效率低）
update修改了buffer pool中的数据，此时将buffer pool修改为脏页
修改磁盘中的页数据
最后提示修改成功
2、第二种（速度快）
update修改了buffer pool中的数据，此时将buffer pool修改为脏页
生成一个redo log 然后将数据存到 log buffer 内存中
redo log进行持久化（事务提交的时候）
innodb提供三种持久化方式，默认为1
为2时会将log buffer中的数据提交的操作系统缓冲池中（Operating System Cache ）
事务执行过程中还会生生成binlog、redolog
提示成功（当mysql服务挂了，再次重启时会将redo log中对应的页与磁盘中进行比较更新）
redo log文件为两个文件进行循环，当所有的redo log文件都到达最大存储空间时会触发检查点；检查redo log中文件与buffer pool缓存页中数据是一致时，先将缓存池对应的页数据写到磁盘中（为了减少触发检查点，可以将redo log文件调大）
14、redo log、binlog、 undo log在mysql中的操作
innodb一般在更新数据时有如下操作：
-
修改buffer pool里面的页数据为脏页
-
update语句—>生成一个redo log —>再走log buffer（log 缓存中走对应的规则）
log 缓存中走对应的规则（0，1，2规则）
-
redo log持久化（当事务提交的时候）
redolog存储的是当前缓存页某一条修改的数据中对应在磁盘中的地址（恢复速度块）
-
binlog 持久化（这个是mysql的持久化并不是innodb的，一般用于主从日志）
binlog记录的都是一些sql语句（恢复速度慢）
-
undo log 即反向操作 （当操作失败时用于回滚buffer pool缓存区）
性能调优时：允许情况下buffer pool与redo log文件越大越好
15、数据库中分库分表有哪些场景与手段？
数据库优化一般为：
1、换数据库
2、sql语句优化、字段优化、索引优化
3、读写分离
主表写，从表读；（读多写少的表时）
当主表使用binlog日记对从表进行更新时，网络出现延迟，查询时可以用强制路由进行
实现读写分离或者分库分表都是使用两种方式：基于proxy代理模式（atlas强制路由解决binlog同步延迟强制路由走主库 ），jbdc增强
4、分库分表
分库分表更多的是对关系型数据库数据存储和访问机制的补充，而不是颠覆
1、垂直分库
将一个全量数据量按照不同的业务进行拆分，不同的业务拆分为不同的数据库
2、垂直分表
将一个大表中经常使用的字段拆分为一个表，不经常使用的拆分为另一张表
总结：
1、垂直分库分表：
1、每个库（表）结构都是不一样的
2、每个库（表）数据（至少有一列）是一样的
3、每个库（表）数据并集为全量数据
优点：拆分后业务清晰（专库专用按照业务拆分），数据维护简单，按业务不同业务放在不同机器上
缺点：如果单表数据量大，读写压力依然很大
受某种业务来决定，或者限制；也就是说一个业务可以会影响到数据库的瓶颈（性能问题）
部分业务无法关联join，只能通过java接口去调用，提高开发难度
3、水平分库
根据对应数据进行取模插入或访问对应数据（例如userid取模之后放到不同的库中）
4、水平分表
根据对应表数据进行取模插入或访问对应数据（例如userid取模之后放到不同的表中）
总结：
水平分表：
1、每个库（表）结构都一样
2、每个库（表）的数据都不一样
3、切分后的表结构相同，程序改造较小
优点：
单库（表）的数据保持在一定量的减少，有助于性能提高
提高了系统稳定性与负载能力
切分的表结构相同，程序改动较小
缺点：
数据扩容非常困难（数据量达到所有数据库的零界点，需要再次新增库时由于是取模存取数据， 会导致之前所有的数据库表中的数据都需要变动按对应的新取模重新迁移）
拆分规则很难抽象出来
分片事务的一致性问题部分业务无法join，只能通过java程序去调用
总结：不管是垂直分库分表还是水平分库分表都会引发：
1、ACID分布式事务
2、增加维护成本
3、跨库查询问题
4、确定分布式全局唯一ID
16、分库分表中间键
1、proxy代理层（性能不高，但是可以跨语言）
mycat、atlas、mysql-porxy（mysql自带）
2、jdbc应用层（性能要高）
shardingsphere、TTDL
17、Redis与mysql怎么保证数据一致性
1、先更新Mysql，再更新Redis；如果Redis失败了，可能依然不一致
2、先删除Redis缓存数据，再更新Mysql，再次查询时将数据添加到Redis缓存中；这种方案解决了方法1，但是在高并发的情况下性能较低，而且任然会出现数据不一致问题；比如线程1删除了Redis缓存数据，正在更新Mysql，此时另一个线程再查询会将以前老数据查询到Redis中
3、延时删除，步骤是：先删除Redis中的数据，再更新Mysql,延迟几百毫秒再删除Redis缓存数据，这样就算在更新Mysql时，有其他线程读了Mysql，把老数据读到Redis中，那么也会被删除掉，从而保证数据一致性
18、Explain语句结果中各个字段分别表示什么
19、索引覆盖
索引覆盖就是一个SQL在执行时，可以利用索引来快速查询，并且此SQL所要查询的字段在当前的索引对应的字段中都包含了，那么就表示此SQL走完索引就不用回来了，所需要的字段在当前索引的叶子节点都存在，可以作为结果返回。
20、innodb是如何实现事务的
innodb通过buffer pool、redo log、undo log、logBuffer来实现事务，以update为例：
1、innodb在收到一个update语句后，会先根据条件找到数据所在的页，并将该页存到buffer pool中
2、执行update语句，修改Buffer pool中的数据（内存中的数据）
3、针对Update语句生成redo log日志对象，并存到LogBuffer中
4、针对update语句生成undo log日志，用于事务回滚
5、如果事务提交，则把RedoLog对象进行持久化，后续还有其它机制将Buffer Pool中所修改的数据页持久化到磁盘中
6、如果事务回滚，则利用undo log日志进行回滚
21、Mysql锁有哪些，如何理解
按锁粒度分类：
1、行锁：锁某行数据，锁粒度最小，并发度高
2、表锁：锁整张表，锁粒度最大，并发度低
3、间隙锁（页锁）：锁的是一个区间（介于表锁与行数之间）
还可以分为：
1.共享锁：也就是读锁，一个事务给某行加了读锁，其它事务也可以读，但是不能写
2.排它锁：也就是写锁，一个事务给某行加了写锁，其它事务就不能读，也不能写
还可以分为：
1、乐观锁：并不会真正去锁某行记录，而是通过一个版本号来实现
2、悲观锁：上面说的行锁、表锁等都是悲观锁（认为很悲观，每个事务来了都需要对其进行加锁操作）
在事务的隔离实现中利用锁机制解决幻读。
22、Mysql慢查询该如何优化
1、检查是否走了索引，如果没有则优化SQL利用索引
2、检查所利用的索引是否是最优索引
3、检查所查询的字段是否是必须的，是否查出多余的字段
4、检查表中的数据是否过多，是否应该进行分库分表
5、检查所在数据库实例所在的机器性能配置，是否太低，是否可以适当增加资源
23、mysql有哪几种数据存储引擎
使用命令：show engines
-
-
相关阅读:
如何低门槛开发有趣实用的ZigBee产品？
AI算法工程师的寒冬？我们完全可以反向思维。。。
会议OA（会议排座&送审）
钟汉良日记：改变心态了，回武平待3年
【C++笔记】第三篇 关键字和标识符
python解决循环依赖
node+websocket数据通信（发送和接收数据）
Fabric.js 图形标注
Mysql 18式
废水含镍如何处理
- 原文地址：https://blog.csdn.net/wuhui2100/article/details/126145323