MySQL基本概念

参考书籍《高性能MySQL》

存储引擎架构：将查询处理及其他系统任务和数据的存储/提取相分离。

• InnoDB：MySQL的默认事务引擎。
• MyISAM：MySQL5.1之前的默认存储引擎，不支持事务和行级锁。

并发控制

MySQL在两个层面上实现了并发控制：服务器层（事务）与存储引擎层（加锁）

锁

• 读写锁（共享锁——可读不可写、排它锁——不可读写）
• 锁粒度（表锁和行级锁）

乐观锁与悲观锁

• 乐观锁（ Optimistic Locking ） 相对悲观锁而言，乐观锁机制采取了更加宽松的加锁机制。悲观锁大多数情况下依靠数据库的锁机制实现，以保证操作最大程度的独占性。但随之而来的就是数据库性能的大量开销，特别是对长事务而言，这样的开销往往无法承受。 而乐观锁机制在一定程度上解决了这个问题。乐观锁，大多是基于数据版本（ Version ）记录机制实现。何谓数据版本？即为数据增加一个版本标识，在基于数据库表的版本解决方案中，一般是通过为数据库表增加一个 “version” 字段来实现。读取出数据时，将此版本号一同读出，之后更新时，对此版本号加一。此时，将提交数据的版本数据与数据库表对应记录的当前版本信息进行比对，如果提交的数据版本号大于数据库表当前版本号，则予以更新，否则认为是过期数据。

事务

• 事务定义：是一组原子性的SQL查询，或者说是一个独立的工作单元。事务内的语句要么全部执行成功，要么全部执行失败。

• 事务的ACID特性

  • atomicity：一个事务必须被视为一个不可分割的最小单元，要么全部执行，要么全部不执行。
  • consistency：数据库总是从一个一致性的状态转移到另一个一致性的状态。例如银行转账之后钱的总和应该不变。
  • isolation：通常来说，一个事务所做的修改在最终提交之前，对其他事务是不可见的。
  • durability：一旦事务提交，其所作的修改就永久保存到数据库中。此时即使系统崩溃，修改的数据也不会丢失。

名词定义：

• 脏读(Dirty Read)：事务可以读取未提交的数据
• 不可重复读(Nonrepeatable Read)：同一个事务，两次执行结果不相同。如A事务执行查询工资后，执行B事务修改工资，再次执行A事务得到的值和第一次不同。重点是修改
• 幻读(Phantom Read):当某个事务在读取某个范围内的记录时，另一个事务又在该范围内插入了新的记录，当之前的事务再次读取该范围内的记录时，会产生换行(Phantom Row)。重点在于新增或者删除
• 多版本并发控制(MVCC)：通过保存数据在某个时间点的快照来实现。也就是说，不管需要执行多长时间，每个事务看到的数据都是一致的。

可参考幻读和不可重复读的区别

• 隔离级别

  • 未提交读(read uncommiteed)：事务中修改，即使没有提交，对其他事务也是可见的。导致Dirty Read。
  • 提交读(read committed)：一个事务开始时，只能“看见”已经提交的事务所做的修改。换句话说，一个事务从开始到提交之前，所做的任何修改对其他事务都不可见。有时也叫做不可重复读。
  • 可重复读(repeatable read)：解决了不可重复读，理论上无法解决幻读问题。InnoDB存储引擎通过多版本并发控制(MVCC)解决了幻读。可重复读(repeatable read)是MySQL的默认事务隔离级别。
  • 可串行化(serializable)：通过强制事务串行化执行，避免了幻读。

  

• 死锁：两个或多个事务在同一资源上相互占用，并请求锁定对方占用的资源，从而导致恶性循环的现象。通过死锁检测和死锁超时机制等解决。
  

• 事务日志：存储引擎在修改表的数据时只需要修改内存拷贝，再把修改行为记录到持久硬盘上的事务日志中，而不用每次都将修改的数据本身持久到磁盘。事务日志采用的是追加的方式，因此写日志的操作是磁盘上一小块区域内的顺序I/O，而不像随机I/O需要在磁盘的多个地方移动磁头，所有采用事务日志的方式相对来说要快得多。事务日志持久以后，内存中被修改的数据在后台可以慢慢地刷回到磁盘。目前大多数存储引擎都是这样实现的，我们通常称之为预写式日志(Write-Ahead Logging),修改数据需要写两次磁盘。

sql 语句查询模式

select : 用于列出查询结果中所需要的属性；使用聚集函数——以值的一个集合为输入、返回单个值的函数(avg,min,max,sum,count)from ：查询求值中需要访问的关系列表；通常定义关系上的笛卡尔乘积，也可为自然连接(natural join)不同于笛卡尔积把第一个关系的每个元组与第二个关系的所有元组都进行连接，自然连接只考虑哪些在两个关系中都出现的属性上取值相同的元组对；where ：作用在from语句中关系的属性上的谓语，限制笛卡尔积所建立的集合，基本运算和扩展的字符串运算等；group by ： 把单个元组分为多个元组，再分别对每个元组使用聚集函数；任何没有出现在group by子句中的属性如果出现在select子句中的话，它只能出现在聚集函数内部，否则这样的查询就是错误的having : 对group by拆分的元组使用限定条件，而非对整个元组；order by ： 让查询中元组按排序显示

数据库优化思路

• 数据库设计：第三范式(传递依赖)。
• 表字段选择。
• 索引，B-Tree索引。
• 查询优化