mysql
约束与外键
约束是对数据库表中数据的一些限制条件，目的是为了保证表中数据的完整性和有效性。
- 主键约束：mysql的主键约束可以是一列或者多个列的组合，用于唯一标识表中的每一行，主键约束列的数值不允许重复或为空值。给字段添加primary key属性可以将该字段设置为
- 自增长约束：mysql中可以将主键定义为自增长，这样在插入新数据时就不需要再输入主键，数据库会用自增长的方式为每一条数据自动赋值。通过给主键添加auto_increment属性可以实现。
- 非空约束：定义某一字段不能为空，关键字为not null。
- 唯一约束：设置某一字段不能有重复的值，关键字为unique。
- 默认约束：为某一字段添加默认值，关键字为default。
- 零填充约束：输入数据时如果某一字段的值的长度小于设定的长度，会在值前面补上相应数量的0，关键字为zerofill。
外键是一个表中的一列，它同时又包含在另一个表的主键中，将两个表进行关联。外键可以保证数据的一致性和完整性，减少数据冗余。
- 外键约束：一个表中国的外键如果不为空，则它必须与另一个表中主键的某个值相等。
MySql（B站河北王校长视频学习笔记）
B+树
B+树的存储特点（以聚簇索引为例）
根结点16kb内存页，可以存储161024/(8+6)=1170个（8字节bigint类型的primary key + 6字节指针），则B+树的第二层有1170个节点，每个节点也可以存储1170个（primary key + 指针），则第三层有11701170个节点。如果一条数据的内存是1kb，那么该B+树可以存储1170117016条数据。
mysql每访问一个结点就需要进行加载一次内存页，加载完成之后通过二分查找定位id范围，然后指向下一层。
为什么不用B树？
- B树：每个节点都会存储mysql数据
假设一条mysql 数据的内存是1kb，B树的根结点只能存储16条数据+指针，第二层只能存储16*16条数据。假设mysql需要存储1000万条数据，使用B+树只需要3层，即进行三次IO就能找到，而使用B树需要很多层，也就需要进行很多次IO。
为什么不用HashMap？
- HashMap是依据hashcode进行存储，不支持模糊查询
- HashMap的hashcode是随机的，无法进行范围查询
- 会发生Hash冲突
索引
聚簇索引（聚集索引）
以每张表的primary key为索引构造的索引树
- 每张表只有一个聚集索引B+树
- 叶子节点存储select* 整行数据
- 非叶子节点存储primary key主键+指向下一节点的指针
非聚簇索引（辅助索引）
- 每张表可以有多个辅助索引
- 叶子节点存储所有索引
- 非叶子节点存储索引+6字节指针
当使用如下语句进行辅助索引时，在辅助索引中定位到数据时需要使用叶子节点中的主键id回到聚簇索引进行回表查询。
select* from [table] where [辅助索引]
联合索引
联合索引也是通过B+树创建。
如何给表定义索引？
- 随机度越高，定义为索引时索引的效率越高。例如将性别作为索引就不助于查询（创建索引也会给表带来一定负担），而日期随机性高就很适合作为索引进行定义。
如何判断字段的值是否离散？
show index;
使用该命令查看cardinality的值，如果cardinality的值越接近1说明该字段离散性越高。cardinality的值会随着数据的更新和改变发生变化。
覆盖索引（不属于索引，只是一种效果）
select name from [table] where name = ‘zhangsan’
该查找语句由于name辅助索引的叶子节点包含name字段的索引值，就不需要回调聚簇索引，达到覆盖索引的效果。假设该表的索引树只有三层，那么只需要发生3次IO就能得到数据。
select * from [table] where name = ‘zhangsan’
该查找语句由于需要查找所有字段的值，所以会先在name辅助索引的叶子节点拿到 name=‘zhangsan’ 和id的值，然后回传到聚簇索引通过id找到完整数据并返回。假设该表的索引树只有三层，那么就需要发生6次IO。
什么情况下索引会失效？
王校长记忆法：LOL； ±*/； not null； no method； no convert； 我就是我，不一样的版本不一样的我；
- LIKE：当like语句中的参数以%开头时索引会失效
- OR：当or前后两个字段中有至少一个不为索引的时索引会失效
- 联合查询：创建联合索引(a, b)，如果where条件没有从联合索引的首元素索引开始索引，那么索引会失效（最左匹配原则）
select [] from [table] where a > 3 and b > 5
-- 此时会使用索引
select [] from [table] where b > 5
-- 此时不会使用索引
- ±*/ ：当索引列进行运算时索引失效
- not：当索引列使用 != ，<> ，is not 等时索引会失效
- null：当索引列使用is null，is not null时索引可能会失效（是否失效与版本有关，mysql允许索引为null，但在创建索引树时只对索引不为null的数据进行创建；在创建字段时需要将不可能为空的字段设置为非空字段，对可能为空的字段设置default默认值，以确保在对字段加索引时能够将所有数据构建到B+树的节点）
- no method：当索引列使用到mysql内置函数时索引会失效
- no convert：假设mysql中字段类型为varchar，但进行查询时使用了id=12345，mysql会使用内置函数convert把数字类型的12345转换成varchar类型后再进行索引，此时索引会失效。
- 版本：
select * from [table] where a > 3 -- a是辅助索引
mysql5.6（不包括）之前由于存在离散读的问题，该语句会进行全表扫描，不会使用辅助索引（3次IO扫全表）。还可能根据阈值对查询数据的范围进行判断是否使用辅助索引。
mysql5.6（包括）之后，该语句会通过辅助索引获取主键id，将数据缓存并根据进行排序（在innodb引擎层完成），在使用聚簇索引获取数据时会按照id的顺序进行查询，如果两条数据间的间隔不大，在查询到第一条数据时则会继续向后读取拿到下一条数据，如果间隔较大则重新进行查询。
离散读
mysql5.6之前，在辅助索引返回的结果回到聚簇索引进行查询整行数据时，需要对每一条数据都进行查询，每条数据都进行三次IO，开销较大。
MVCC（一致性非锁定读、多版本并发控制）
MVCC是mysql基于自身的回滚机制（undolog）为并发场景下的读操作提供的多版本控制，为达到读操作不需要被锁定的目的来加快mysql的读取。
不同隔离级别下MVCC的表现
mysql的四种隔离级别：read uncommitted、read committed、repeatable read（默认隔离级别）、serialize
- repeatable read（默认隔离级别）：视图在每次select时创建，事务期间的select都使用这个视图。（此隔离级别下需要通过间隙锁解决幻读问题）
- read committed：视图在每次select时生成，读取的数据是最后一次提交的数据版本；读取的数据受其他事务的影响，违背了事务的隔离性；会造成幻读（相同语句读取数据返回的结果数量不同）和不可重复读（相同语句读取数据返回的结果数据不同）
- read uncommitted：读取的是最后一次修改的数据版本；会读到没有进行持久化的脏数据，发生脏读
- serialize：用加锁的方式让所有操作串行进行，无并发
Mysql锁的三种算法
InnoDB引擎下的行级锁
- 共享锁（S Lock）：允许事务读一行数据
- 排他锁（X Lock）：允许事务删除或更新一行数据
S与S兼容，其他情况均不兼容
InnoDB引擎的3种行锁的算法
- Record Lock：单个行记录上的锁，锁定索引记录（如果没有设置索引则会使用隐式主键进行锁定）
- Gap Lock：间隙锁，锁定一个范围，但不包括记录本身
- Next-Key Lock（默认隔离级别下的锁定算法）：行锁+间隙锁，锁定一个范围，并锁定记录本身
假如一个索引有10，11，13，20这四个值，那么索引可能被Next-Key Lock的区间为：(-§ , 10] , (10 , 11] , (11 , 13] , (13 , 20] , (20 , +§)
当查询的索引含有唯一属性时，会对Next-Key Lock进行优化，降为Record Lock，仅锁住索引本身，而不是范围。
扫一扫
2万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
