索引原理
myisam存储引擎将一张表分为表结构文件、索引文件、数据文件三个文件存储
innodb存储引擎分两个文件：表结构文件、索引及数据文件
innodb使用b+树作为索引结构，对于主键索引，根节点不存放数据，只存放索引值，叶子节点会存完整的行数据，根节点和叶子节点的数据会冗余，根节点包含索引值和指向下一级的指针，下一级是左闭右开的区间（包含父节点左边的索引值，不包含右边的）；非主键索引，叶子节点存放的值是该行数据的主键
叶子节点的索引的指针会指向下一个相邻的叶子节点，提升范围查找的效率。hash索引精确查找效率高，但范围查询不好。
innodb的最小存储单元为页，一页为16k,假设主键为8Byte的bigint类型，指针长度为6Byte，则一页可以存放的索引数是16*1024/(8+6)，约为1170，为千级的量级，如果为三级索引树，第三级会存完整的行数据，假定一行数据的大小为1k,那么一页大小可以存放16条数据，那么整个三级索引树可以存放的数据为1170*1170*16，约为2千万，为千万量级。
二叉树：如果数据只在根节点的一遍，极端情况下会退化成链表，树的深度很深。
红黑树：平衡二叉树，是一 棵空树或它的左右两个子树的高度差的绝对值不超过1，并且左右两个子树都是一棵平衡二叉树。解决了二叉查找树退化成链表的问题。依然会有很深的深度
skiplist（调表）：多层的有序链表 [参考文章](SkipList(跳跃表)详解_才不是本人的博客-CSDN博客_skiplist)
跳表与B+树优势对比：[参考文章](Mysql 的索引为什么使用 B+ 树而不使用跳表？)
存放相同的记录数（如2千万），B+树三层，skipList 24层。B+树是自平衡的多分树，插入时需要自平衡，旋转树结构，skiplist依赖随机算法，写性能高，读性能差。redis使用跳表来实现ZSET，实现简单，内存操作，不考虑磁盘io。
B-树和B+树都是多分树。
B-树：根节点和叶子节点都存数据，叶子节点的指针为空。每一层能存的数据条数少，存相同行数的数据，B-树需要更深的树结构。
B+树：根节点只存索引值，叶子节点才存数据，相同的树层次可以存放更多行数据。
索引类型
1. 主键索引、唯一索引、普通索引、全文索引、联合索引
2. 聚簇索引、非聚簇索引。innodb引擎一张表只会有一个聚簇索引，通常是主键，叶子节点存放着一行记录的所有字段；非聚簇索引的叶子节点存放的是主键值，非聚簇索引无法命中覆盖索引时会造成两次b+树的搜索
3. 覆盖索引，从辅助索引中就能获取到需要的记录，而不再需要再次遍历聚簇索引。覆盖索引因为不包含像聚簇索引的所有字段，因此可以减少大量io操作
参考文章
https://www.cnblogs.com/nijunyang/p/11406688.html
https://www.cnblogs.com/fanBlog/p/12283567.html
隔离级别原理
[参考文章](MySQL 中实现可重复读（RR）的原理_liu的博客-CSDN博客_mysql rr实现)
MySQL事务隔离级别的实现原理 – 废物大师兄 – 博客园
mysql只有innodb引擎支持事务，支持行级锁。
mysql增删改、for update会基于索引对满足条件的索引加锁，加锁后，其他的会话对已锁定的再做增删改、for update操作时，会等待（select不会阻塞）。（排他锁、悲观锁、当前读）
read repeatable/read commit两种隔离级别依赖MySQL innodb的mvvc机制(多版本并发访问控制)，每行数据存在两个隐藏的逻辑字段，创建时间（创建时的事务号）、过期时间（删除的事务号），没开启一个事务，全局的事务号会递增。
一致性读依赖于mvvc的快照undo log+ read-view实现，查询时：
当一个事务第一次执行查询sql时，会生成一致性视图 read-view（快照），它由执行查询时所有未提交事务 id 数组（数组中最小的 id 为 min_id）和已创建的最大事务 id（max_id）组成，查询时从 undo log 中最新的一条记录开始跟 read-view 做对比，如果不符合比较规则，就根据回滚指针回滚到上一条记录继续比较，直到得到符合比较条件的查询结果。规则为如果当前事务号小于最小未提交事务id,则可见；如果在最小和最大的范围间，则事务提交可见，未提交则不可见；如果大于最大的事务号，则不可见。
rr级别在事务一开始时形成read-view（可重复读）,rc级别在每次查询都时新的read-view,保证可以拿到最新的已提交数据。
间隙锁可以避免幻读。
锁原理
[参考文章](MySQL的锁机制和加锁原理_吴一尘的博客-CSDN博客_mysql锁机制)
表锁
页锁
行锁
record lock
gap lock
next-key lock
优化
开启慢查询日志
explain-查看执行计划
show profile查询sql执行时性能问题（cpu、内存等）
MYSQL性能优化细节：
- 合理创建及使用索引（考虑数据增删）
- 合理冗余字段（建较大的表，考虑数据库设计三范式（1.列不可再分割；2.所有列都要与主键有关系；3.所有列都要与主键有直接关系而不是间接关系，如果是间接关系，则拆成两张表）与业务设计的取舍）
- select尽量不适用*，where尽量不1=1、in与exists的选择(in先执行外层查询，exists先执行内层查询)、组合索引的创建顺序、查询力度大的sql放到左边、尽量建立组合索引
- 合理使用慢查询日志、explain、show profile
发布者：全栈程序员-站长，转载请注明出处：https://javaforall.net/223629.html原文链接：https://javaforall.net
