mysql要点
2019-03-06 19:35:51 227 举报AI智能生成
sql
作者其他创作
大纲/内容
MyISAM与InnoDb
1.支持事务和行级锁，是innodb的最大特色（5.6以后也支持了FULLTEXT）
2.MyISAM支持 FULLTEXT类型的全文索引
3.innodb属于索引组织表，维护需要更多空间，能应对更加复杂的场景
4.innodb通过事务日志恢复很快能恢复故障
索引
innodb
索引使用的结构为【顺序访问指针B+树】
1.内节点不存储data，只存储key和指针；
2.叶子节点不存储指针，存储具体数据
3.个叶子节点增加一个指向相邻叶子节点的指针，就形成了带有顺序访问指针的B+Tree。做这个优化的目的是为了提高区间访问的性能
优点：1.平均三层树，随机查询稳定2，顺序指针能支持范围，且减少磁盘IO次数
聚集索引
B+树叶子节点为一列数据
非聚集索引
B+树叶子节点为聚集索引
myisam
聚集索引
B+树叶子节点为一列数据的地址
非聚集索引
B+树叶子节点为一列数据的地址，区别就是非聚集索引可以重复不唯一
优化
1.创建索引
可以提高查询的效率
类型
联合索引
最左前缀原理（左边的出现有效，后面的才会有效）
我们在（a,b,c）字段上建了一个联合索引，所以这个索引是先按a 再按b 再按c进行排列的
有效，where可乱序
select * from table where a=1；
select * from table where a=1 and b=2；
select * from table where a=1 and b=2 and c=3；
select * from table where a=1 and b=2；
select * from table where a=1 and b=2 and c=3；
无效
select * from table where a=1 and c=3； 那么只会用到索引a。
select * from table where b=2 and c=3； 因为没有用到最左前缀a，所以这个查询是用户到索引的。
唯一
普通（index）
全文（fulltext）
主键
使用原则
1.创建的索引是否能够满足绝大多数的查询
2.索引的区分度是否足够大
3.创建的索引是否过多
explain
system：表只有一行
const ：表中的一个记录的最大值能够匹配这个查询（索引可以是主键或惟一索引）
eq_ref：查询使用了索引为主键或惟一键
ref：查询使用了索引
range：ref：查询使用了索引，查询出来一个范围
index：查询全部索引
ALL:全表查询
建议<= range
事务机制
事务四个特性
1.原子性（Atomicity）：原子性是指事务包含的所有操作要么全部成功，要么全部失败回滚
2.一致性（Consistency）：一致性是指事务必须使数据库从一个一致性状态变换到另一个一致性状态，也就是说一个事务执行之前和执行之后都必须处于一致性状态。户A和用户B两者的钱加起来一共是5000，那么不管A和B之间如何转账，转几次账，事务结束后两个用户的钱相加起来应该还得是5000，这就是事务的一致性
3.隔离性（Isolation）：操作同一张表时，数据库为每一个用户开启的事务，不能被其他事务的操作所干扰，多个并发事务之间要相互隔离
4.持久性（Durability）：持久性是指一个事务一旦被提交了，那么对数据库中的数据的改变就是永久性的，即便是在数据库系统遇到故障的情况下也不会丢失提交事务的操作。
不事务隔离带来的问题
更新丢失：两事务同时更新，一个失败回滚覆盖另一个事务的更新。或事务1执行更细操作，在事务1结束前事务2也更新，则事务1的更细结果被事务2的覆盖了。
脏读：事务T2读取到事务T1修改了但是还未提交的数据，之后事务T1又回滚其更新操作，导致事务T2读到的是脏数据。
不可重复读：事务T1读取某个数据后，事务T2对其做了修改，当事务T1再次读该数据时得到与前一次不同的值。
虚读（幻读）：事务T1读取在读取某范围数据时，事务T2又插入一条数据，当事务T1再次数据这个范围数据时发现不一样了，出现了一些“幻影行”。
事务隔离级别
●读未提交（Read Uncommitted）
含义解释：只限制同一数据写事务禁止其他写事务。解决”更新丢失”。（一事务写时禁止其他事务写）
名称解释：可读取未提交数据
所需的锁：排他写锁
含义解释：只限制同一数据写事务禁止其他写事务。解决”更新丢失”。（一事务写时禁止其他事务写）
名称解释：可读取未提交数据
所需的锁：排他写锁
●读提交（Read Committed）
含义解释：只限制同一数据写事务禁止其它读写事务。解决”脏读”，以及”更新丢失”。（一事务写时禁止其他事务读写）
名称解释：必须提交以后的数据才能被读取
所需的锁：排他写锁、瞬间共享读锁
含义解释：只限制同一数据写事务禁止其它读写事务。解决”脏读”，以及”更新丢失”。（一事务写时禁止其他事务读写）
名称解释：必须提交以后的数据才能被读取
所需的锁：排他写锁、瞬间共享读锁
可重复读（Repeatable Read）
含义解释：限制同一数据写事务禁止其他读写事务，读事务禁止其它写事务(允许读)。解决”不可重复读”，以及”更新丢失”和”脏读”。（一事务写时禁止其他事务读写、一事务读时禁止其他事务写）
注意没有解决幻读，解决幻读的方法是增加范围锁（range lock）或者表锁。
名称解释：能够重复读取
所需的锁：排他写锁、共享读锁
含义解释：限制同一数据写事务禁止其他读写事务，读事务禁止其它写事务(允许读)。解决”不可重复读”，以及”更新丢失”和”脏读”。（一事务写时禁止其他事务读写、一事务读时禁止其他事务写）
注意没有解决幻读，解决幻读的方法是增加范围锁（range lock）或者表锁。
名称解释：能够重复读取
所需的锁：排他写锁、共享读锁
串行化（Serializable）
含义解释：限制所有读写事务都必须串行化实行。它要求事务序列化执行，事务只能一个接着一个地执行，但不能并发执行。如果仅仅通过“行级锁”是无法实现事务序列化的，必须通过其他机制保证新插入的数据不会被刚执行查询操作的事务访问到。（一事务写时禁止其他事务读写、一事务读时禁止其他事务读写）
所须的锁：范围锁或表锁
含义解释：限制所有读写事务都必须串行化实行。它要求事务序列化执行，事务只能一个接着一个地执行，但不能并发执行。如果仅仅通过“行级锁”是无法实现事务序列化的，必须通过其他机制保证新插入的数据不会被刚执行查询操作的事务访问到。（一事务写时禁止其他事务读写、一事务读时禁止其他事务读写）
所须的锁：范围锁或表锁
注意项：
1.脏读和不可重复读的区别：脏读是某一事务读取了另一个事务未提交的脏数据，而不可重复读则是读取了前一事务提交的数据。
2.不可重复读和幻读的异同：都是读取了另一条已经提交的事务（这点就脏读不同），所不同的是不可重复读查询的都是同一个数据项，而幻读针对的是一批数据整体（比如数据的个数）。
3.此外，MySQL的Repeatable Read隔离级别也解决了幻读问题（通过Next-key lock加锁方法即范围锁解决不可重复读和幻读问题，如select * from t where a>10会对key为[10,infinite）范围的行加锁，这样其他事务就不能对此范围内key对应的行更改）达到了SQL、SQL2标准中的Serializable级别
事务日志
数据故障恢复
子主题
实现
事务日志是通过redo和innodb的存储引擎日志缓冲(Innodb log buffer)来实现的，当开始一个事务的时候，会记录该事务的lsn(log sequence number)号; 当事务执行时，会往InnoDB存储引擎的日志。当事务提交时，必须将存储引擎的日志缓冲写入磁盘(通过innodb_flush_log_at_trx_commit来控制)，也就是写数据前，需要先写日志。这种方式称为“预写日志方式”
锁
死锁
主要是事物之间由于隔离级别最高，开启行锁，易造成相互持有对方的锁
悲观锁
通常所说的“一锁二查三更新”即指的是使用悲观锁。通常来讲在数据库上的悲观锁需要数据库本身提供支持，即通过常用的select … for update操作来实现悲观锁。
乐观锁
一般的做法是在需要锁的数据上增加一个版本号，或者时间戳，类似CAS
0 条评论
下一页
