mysql知识集

索引原理

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操作

4. MySQL分区表暂不支持全局索引，仅支持本地索引。（MySQL分区是全局索引还是本地索引_mysql分区索引和全局索引-CSDN博客）

oracle索引类型

1. 按索引存储的数据结构分：B-tree索引（不指定时默认），位图（bitmap）索引，hash索引等

2. 按索引的唯一性分： 唯一索引（UNIQUE）、普通索引

3. 根据索引的物理存储分（本地索引（local）和全局索引(global)）:一般是对于分区表的，对于普通表也可以建分区索引（不常用）

[参考文章](Oracle 本地索引(local)与全局索引(global)的区别_local索引和global索引-CSDN博客)

        * 分区索引分为本地索引(local index)和全局索引(global index)。

        * 对于local索引，每一个表分区对应一个索引分区，当表的分区发生变化时，索引的维护由Oracle自动进行。

        * 对于global索引，可以选择是否分区，而且索引的分区可以不和表分区相对应。当对分区进行维护操作时，通常会导致全局索引的INVALID,必须在执行完操作后REBULID。

        * 本地索引分为有前缀(PREFIX)的本地索引和无前缀(NOPREFIX)的本地索引。而全局索引目前只支持有前缀的索引。

-- 本地索引创建

create  table student1 (id number,stu_no varchar2(100))
partition by RANGE (id)
(
partition p1 values less than (100) tablespace users1,
partition p2 values less than (200) tablespace users2,
partition p3 values less than (maxvalue) tablespace users3
);

--因为id是分区键，所以这样就创建了一个有前缀的本地索引
create index stu_prefix_index on student1(id) local;

--因为stu_no不是分区键，所以这样就创建了一个无前缀的本地索引
create  index stu_noprefix_index on student1(stu_no) local;

-- 全局索引举例

-- 建表

CREATE  TABLE ORDERS(
       ORDER_NO   NUMBER,
       PART_NO    VARCHAR2(40),
       ORD_DATE   DATE
)
PARTITION BY RANGE(ORD_DATE)
(PARTITION Q1 VALUES LESS THAN (TO_DATE('2014-01-01','YYYY-MM-DD'))
,PARTITION Q2 VALUES LESS THAN (TO_DATE('2014-02-01','YYYY-MM-DD'))
,PARTITION Q3 VALUES LESS THAN (TO_DATE('2014-03-01','YYYY-MM-DD'))
,PARTITION Q4 VALUES LESS THAN (TO_DATE('2014-04-01','YYYY-MM-DD'))
,PARTITION Q5 VALUES LESS THAN (TO_DATE('2014-05-01','YYYY-MM-DD'))
,PARTITION Q6 VALUES LESS THAN (TO_DATE('2014-06-01','YYYY-MM-DD'))
,PARTITION Q7 VALUES LESS THAN (TO_DATE('2014-07-01','YYYY-MM-DD'))
);

-- 创建全局索引，且索引分区键和表分区键相同

CREATE INDEX ORDERS_GLOBAL_1_IDX ON ORDERS(ORD_DATE) GLOBAL
PARTITION BY RANGE(ORD_DATE)
(PARTITION GLOBAL1 VALUES LESS THAN (TO_DATE('2014-01-01','YYYY-MM-DD'))
,PARTITION GLOBAL2 VALUES LESS THAN (TO_DATE('2014-02-01','YYYY-MM-DD'))
,PARTITION GLOBAL3 VALUES LESS THAN (TO_DATE('2014-03-01','YYYY-MM-DD'))
,PARTITION GLOBAL4 VALUES LESS THAN (TO_DATE('2014-04-01','YYYY-MM-DD'))
,PARTITION GLOBAL5 VALUES LESS THAN (TO_DATE('2014-05-01','YYYY-MM-DD'))
,PARTITION GLOBAL6 VALUES LESS THAN (TO_DATE('2014-06-01','YYYY-MM-DD'))
,PARTITION GLOBAL7 VALUES LESS THAN (TO_DATE('2014-07-01','YYYY-MM-DD'))
,PARTITION GLOBAL8 VALUES LESS THAN (MAXVALUE)
);

-- 创建全局索引，索引的分区键和表分区键不相同

CREATE INDEX ORDERS_GLOBAL_2_IDX 
       ON ORDERS(PART_NO) 
       GLOBAL 
       PARTITION BY RANGE(PART_NO)
       (PARTITION IND1 VALUES LESS THAN (555555)
       ,PARTITION IND2 VALUES LESS THAN (MAXVALUE)
       );

--使用全局索引，索引键值必须和分区键值相同，这就是所谓的前缀索引。Oracle不支持非前缀的全局分区索引，
--如果要建立非前缀分区索引，索引必须建成本地索引。

CREATE INDEX ORDERS_GLOBAL_2_IDX 
       ON ORDERS(PART_NO)
       GLOBAL PARTITION BY RANGE(ORDER_NO)
       (PARTITION IND1 VALUES LESS THAN (555555),
       PARTITION IND2 VALUES LESS THAN(MAXVALUE)
       );--执行报错

参考文章

https://www.cnblogs.com/nijunyang/p/11406688.html

https://www.cnblogs.com/fanBlog/p/12283567.html

隔离级别原理

[参考文章](MySQL 中实现可重复读（RR）的原理_liu379702831的博客-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,保证可以拿到最新的已提交数据。

间隙锁可以在一定程度上减少幻读，但不能完全避免。如a事务select i > 10,b插入i=100并提交,后续a再执行select i>10时，依赖mvvc机制读快照版本不会幻读；但如果a事务通过select i > 10 for update，此时进行当前读，可以查到i=100的记录，出现幻读。

[参考文章](MYSQL 事务、事务隔离级别和MVCC，幻读_Yi Ian的博客-CSDN博客)

锁原理

[参考文章](MySQL的锁机制和加锁原理_吴一尘的博客-CSDN博客_mysql锁机制)

表锁

页锁

行锁

        record lock

        gap lock

        next-key lock

优化

开启慢查询日志

explain-查看执行计划

show profile查询sql执行时性能问题（cpu、内存等）

MYSQL性能优化细节：

1. 合理创建及使用索引（考虑数据增删）
2. 合理冗余字段（建较大的表，考虑数据库设计三范式（1.列不可再分割；2.所有列都要与主键有关系；3.所有列都要与主键有直接关系而不是间接关系，如果是间接关系，则拆成两张表）与业务设计的取舍）
3. select尽量不适用*，where尽量不1=1、in与exists的选择(in先执行外层查询，exists先执行内层查询)、组合索引的创建顺序、查询力度大的sql放到左边、尽量建立组合索引
4. 合理使用慢查询日志、explain、show profile
5. MySQL limit 100，10分页查询时，是先把前面100条记录遍历一次，再取后续的10条，当查询业务大时，和全表扫描是类似的效果，会有很多次的磁盘io。有两种优化思路：1. 通过条件过滤掉前面不满足的数据，如select id > 100 limit 10;(有一定的业务局限性)；2. 通过覆盖索引的方式，避免全表扫描，如select id limit 100,10，再通过表关联或者子查询的方式，获取到指定id范围的select * 数据。[参考文章](MySQL分页查询limit优化的两种思路_Victor _Lv的博客-CSDN博客_优化limit分页)
6. 覆盖索引可以避免二次遍历索引数的问题，提高查询效率。（通过普通索引获取记录的过程为    一次遍历普通索引树后，获取目标记录的主键，再遍历聚簇索引树，获取到想到的数据）

mysql explain结果分析

[参考文章](MySQL-Explain执行计划 - 知乎)

1. 多表查询执行计划会转换成单表的多条记录，union 会转化成对合并的虚拟表的操作；一条记录代表对一张表的访问方法/访问类型；

2. type表示对一张表的访问类型：system>const>eq_ref>ref>range>index>ALL，一般得保证查询至少达到range级别，最好能达到ref。

• system:表只有一条记录，并且该存储引擎（如MyISAM、Memory）的统计数据精确。EXPLAIN
  SELECT * FROM s1 WHERE id = 716;
• const: 常数级别，通过主键或者唯一二级索引列来定位一条记录的访问方法。如InnoDB通过B+树聚簇索引与常数进行匹配
• eq_ref: 表连接操作，被驱动表以const的方式（主键或唯一二级索引）访问。（表关联操作，以A表的每条记录去遍历匹配B表的数据，A叫驱动表，B叫被驱动表）EXPLAIN
  SELECT * FROM s1 INNER JOIN s2 ON s1.id = s2.id; （s1 type = ALL,s2 type=eq_ref）
• ref: 通过普通的二级索引列与常量进行等值匹配时来查询某个表，那么对该表的访问方法就可能是ref。
• range:使用索引获取某些范围区间的记录。（< ;> ;between ..and..;in）
• index:覆盖索引查询。（使用索引覆盖，但需要扫描全部的索引记录）。EXPLAIN
  SELECT insert_time FROM s1 WHERE expire_time = '2021-03-22 18:36:47';
• all: 全表扫描

       MySQL常见问题 

[参考文章](MYSQL 必考面试题10道（答案解释）-腾讯云开发者社区-腾讯云)