MySQL知识点
2021-12-01 09:33:44 0 举报AI智能生成
MySQL知识点与复习
作者其他创作
大纲/内容
搜索引擎和索引
MyISAM
MyISAM 使用的是B+Tree 作为索引结构
同时MyISAM 的B+Tree也是非聚集索引
同时MyISAM 的B+Tree也是非聚集索引
非聚集索引：指的是索引文件和数据分开存储
MyISAM 的 B+Tree 叶子节点的data域里存放的是数据记录的地址，
也就是数据在磁盘中的地址（例如：0x07）
也就是数据在磁盘中的地址（例如：0x07）
MyISAM 的主索引和辅助索引结构是一样的
InnerDB
InnerDB 使用的也是 B+Tree 作为索引结构，
InnerDB 的 B+Tree 和 MyISAM 的 B+Tree 结构非常不同，
InnerDB 的 B+Tree 是聚集索引结构
InnerDB 的 B+Tree 和 MyISAM 的 B+Tree 结构非常不同，
InnerDB 的 B+Tree 是聚集索引结构
聚集索引：相对于MyISAM，聚集索引的索引和数据是在一起的
InnerDB 的 B+Tree 叶子节点的data域里面直接存放了索引对应的数据，
InnerDB 索引的 key 用的是索引的主键（primary key）
InnerDB 索引的 key 用的是索引的主键（primary key）
InnerDB 的辅助索引，其索引结构上的key是数据，而data域是主键索引的值
索引相关问题
索引的使用顺序，检索顺序
1：首先使用辅助索引检索，获取主键
2：使用主键检索主键索引，或者具体数据
索引长度问题
1：索引字段不要过长，因为辅助索引使用的是数据做索引，字段过长索引也会过长
2：主键不要过长，与上面的同理，过长的主键会直接影响辅助索引和主键索引，
直接导致索引过大
直接导致索引过大
InnerDB 可不可以没有主键
不可以没有主键，
主键所以直接使用主键作为索引，
辅助索引最终指向的也是主键
主键所以直接使用主键作为索引，
辅助索引最终指向的也是主键
InnoDB 没有主键会怎么样
- 如果定义了主键，那么InnoDB会使用主键作为聚簇索引
- 如果没有定义主键，那么会使用第一非空的唯一索引（NOT NULL and UNIQUE INDEX）作为聚簇索引
- 如果既没有主键也找不到合适的非空索引，那么InnoDB会自动生成一个不可见的名为ROW_ID的列名为GEN_CLUST_INDEX的聚簇索引，该列是一个6字节的自增数值，随着插入而自增
- 如果没有定义主键，那么会使用第一非空的唯一索引（NOT NULL and UNIQUE INDEX）作为聚簇索引
- 如果既没有主键也找不到合适的非空索引，那么InnoDB会自动生成一个不可见的名为ROW_ID的列名为GEN_CLUST_INDEX的聚簇索引，该列是一个6字节的自增数值，随着插入而自增
索引优化
最左前缀
当查询条件精确匹配索引的左边连续一个或几个列时，索引可以被用到
全列匹配
当按照索引中所有列进行精确匹配（这里精确匹配指“=”或“IN”匹配）时，索引可以被用到
避免
查询条件用到了索引中列的精确匹配，但是中间某个条件未提供
查询条件没有指定索引第一列
匹配某列的前缀字符串
范围查询
查询条件中含有函数或表达式
索引和锁的关系
mysql innodb的锁是通过锁索引来实现的。
典型的排它锁：select for update
如果字段没有索引，即使使用wehre条件也会进行表级锁
如果有索引，会锁定对应where条件中索引值的所有行，
可理解为对该索引值进行了索引（所以即使另一事务查询的是其他行，但因为索引值形同们也会被锁住。）
可理解为对该索引值进行了索引（所以即使另一事务查询的是其他行，但因为索引值形同们也会被锁住。）
有索引，而且使用了不同的索引值查数据，但是查询 的结果是同一行，可以理解为真正的数据行锁。
锁
按锁粒度分类
行级锁
表示只针对当前操作的行进行加锁
行级锁分为共享锁和排他锁
间隙锁（范围锁）也是行锁的一种，
为了防止幻读，而扩大了行锁的锁定范围
为了防止幻读，而扩大了行锁的锁定范围
删除不存在的数据时会出现间隙锁
数据存在时删除，数据被找到被锁，这是普通行锁。
数据不存时，数据库会向左扫描扫到第一个比给定参数小的值，
向右扫描扫描到第一个比给定参数大的值，
然后以此为界，构建一个区间， 锁住整个区间内的数据，
一个特别容易出现死锁的间隙锁诞生了。
向右扫描扫描到第一个比给定参数大的值，
然后以此为界，构建一个区间， 锁住整个区间内的数据，
一个特别容易出现死锁的间隙锁诞生了。
表级锁
表示对当前操作的整张表加锁
表级锁定分为表共享读锁（共享锁）与表独占写锁（排他锁）
页级锁
页级锁是 MySQL 中锁定粒度介于行级锁和表级锁中间的一种锁
一次锁定相邻的一组记录
一次锁定相邻的一组记录
锁级别分类
共享锁
用法：SELECT … LOCK IN SHARE MODE;
共享锁又称读锁，是读取操作创建的锁。
其他用户可以并发读取数据，但任何事务都不能对数据进行修改（获取数据上的排他锁），
直到已释放所有共享锁。
其他用户可以并发读取数据，但任何事务都不能对数据进行修改（获取数据上的排他锁），
直到已释放所有共享锁。
如果事务T对数据A加上共享锁后，则其他事务只能对A再加共享锁，
不能加排他锁。获准共享锁的事务只能读数据，不能修改数据。
不能加排他锁。获准共享锁的事务只能读数据，不能修改数据。
排他锁
SELECT … FOR UPDATE;
排他锁又称写锁、独占锁，如果事务T对数据A加上排他锁后，
则其他事务不能再对A加任何类型的封锁。获准排他锁的事务既能读数据，又能修改数据。
则其他事务不能再对A加任何类型的封锁。获准排他锁的事务既能读数据，又能修改数据。
意向锁
意向共享锁（IS）：表示事务准备给数据行加入共享锁，也就是说一个数据行加共享锁前必须先取得该表的IS锁；
意向排他锁（IX）：类似上面，表示事务准备给数据行加入排他锁，说明事务在一个数据行加排他锁前必须先取得该表的IX锁。
意向锁是 InnoDB 自动加的，不需要用户干预。
隔离级别
事务问题
脏读（Dirty Read）
一个事务读到了另一个未提交事务修改过的数据
幻读（Phantom）
表锁
不可重复读（Non-Repeatable Read）
行锁
隔离级别
读未提交（READ UNCOMMITTED）
在读未提交隔离级别下，事务A可以读取到事务B修改过但未提交的数据。
可能发生脏读、不可重复读和幻读问题，一般很少使用此隔离级别。
可能发生脏读、不可重复读和幻读问题，一般很少使用此隔离级别。
读已提交（READ COMMITTED）
在读已提交隔离级别下，事务B只能在事务A修改过并且已提交后才能读取到事务B修改的数据。
读已提交隔离级别解决了脏读的问题，但可能发生不可重复读和幻读问题，一般很少使用此隔离级别。
可重复读（REPEATABLE READ）
在可重复读隔离级别下，事务B只能在事务A修改过数据并提交后，自己也提交事务后，才能读取到事务B修改的数据。
可重复读隔离级别解决了脏读和不可重复读的问题，但可能发生幻读问题。
提问：为什么上了写锁（写操作），别的事务还可以读操作？
因为InnoDB有MVCC机制（多版本并发控制），可以使用快照读，而不会被阻塞。
可串行化（SERIALIZABLE）
各种问题（脏读、不可重复读、幻读）都不会发生，通过加锁实现（读锁和写锁）。
mvcc
多版本并发控制，数据库层面的设计，用于控制事务在多线程下的安全
MVCC的实现是通过undo log和read view来实现的
redo log负责记录修改数据
undo log负责记录修改步骤
innodb引擎下的表，每个数据行都有隐藏的两列
trx_id：事务id
roll_pointer：指针，指向上一次的修改，用于回滚操作
如果不存在主键的话，还会有第三列row_id，在没有主键的情况下默认生成的主键；
0 条评论
下一页
