插入逻辑
image.png
插入28
image.png
插入70
image.png
插入95
image.png
插入70，旋转
image.png
- 索引列尽量小，可以使B+树阶数增加，层数减少
- 主键尽量单调递增
删除逻辑
B+树使用填充因子（fill factor）来控制树的删除变化，50%是填充因子可设的最小值。B+树的删除操作同样必须保证删除后叶节点中的记录依然排序，同插入一样，B+树的删除操作同样需要考虑下图所示的三种情况，与插入不同的是，删除根据填充因子的变化来衡量。
image.png
删除70
image.png
删除25
image.png
删除60
- 尽可能不要删除，使用deleted字段代替
复合索引
image.png
- 可以从这个图理解为什么复合索引使用时需要遵守最左原则
锁
http://hedengcheng.com/?p=771
我们要知道对InnoDB来说，“行锁”是不够准确的，因为InnoDB分为聚簇索引和二级索引，两种树上都能分别加锁。
对于一个sql语句delete from t1 where id = 10;，我们分几种情况分析
-
id是主键：在聚簇索引上的id=10这一行加X锁
image.png
-
id是非主键唯一索引：如果主键是name，则会在二级索引上id=10的一行加X锁，并在聚簇索引上的name=d的一行上加X锁（假设从二级索引上查到id=10的记录name=d）
image.png
-
id是非主键非唯一索引，需要区分隔离级别
-
当隔离级别是RC（Read Committed）时，对二级索引上id=10的2行加X锁，并在聚簇索引上的name=d,name=d的2行上加X锁（假设从二级索引上查到id=10的记录name=d，name=d两行）
image.png
-
当隔离级别是RR（Repeatable Read）时，除了跟RC一样加X锁之外，还要加上GAP锁（间隙锁），是为了防止幻读，也就是在第一次读和第二次读之间，其他事务无法插入新的满足条件的记录。
image.png
-
-
id非索引，需要区分隔离级别
-
当隔离级别是RC（Read Committed）时,对聚簇索引的所有行加X锁
image.png
-
当隔离级别是RR（Repeatable Read）时，对聚簇索引的所有行加X锁，并给所有间隙加GAP锁
-
JOIN
- join查询的原理：
select tbl1.col1,tbl2.col2 from tbl1\
inner join tbl2 on tbl1.col3 = tbl2.col3 \
where tbl1.col1 in (5,6,7)
上述sql语句大概执行如下伪代码：
result = []
select rows1 from tbl1 where tbl.col1 in (5,6,7)
for row in rows1:
select rows2 from tbl2 where tbl2.col3 = row.col3
for row2 in rows2:
result.append(row+row2)
可以看到在上述过程中，可以使用到tbl1里的col1索引，tbl2里的col3索引
对于left outer join这一过程也成立，但full join则不行，所以mysql是不支持全外连接的
- 但是，mysql优化器有时候会选择更好的策略，而不是按上述顺序执行。例：
select tbl_a.id,tbl_b.id
-> from tbl_a
-> inner join ab_relation on ab_relation.aid = tbl_a.id
-> inner join tbl_b on tbl_b.id = ab_relation.bid
按上条的逻辑，会首先查出所有tbl_a，然后根据tbl_a.id查找ab_relation，再根据ab_relation.bid查找tbl_b。但是在tbl_a的行数比tbl_b大的时候，mysql的优化器可能会首先查找tbl_b，再查找ab_relation，最后查找tbl_a。可以思考一下，如果上述语句中不是inner join 而是 left outer join，还能调换关联顺序吗？
- union虽然看起来跟join有点像，但是会生成一个临时表，而临时表里是不能使用索引的，所以使用union的时候要注意尽量把where条件放到union的子句里
网友评论