- 存储引擎是基于表的, 而不是数据库.
- Mysql数据库从5.5.8版本开始, InnoDB存储引擎是默认的存储引擎.
- InnoDB通过多版本并发控制(MVCC)来获得高并发, 实现标准的4中隔离级别, 默认为REPEATBLE. 同时, 使用next-key locking的策略来避免幻读现象.
- 缓冲池的设计目的是为了协调CPU速度与磁盘速度的鸿沟.
InnoDB关键特性:
１，插入缓冲(Insert Buffer). 条件:1,辅助索引.2,索引不唯一.
２，两次写(Double Write).
３，自适应哈希索引(Adaptive Hash Index). 要求: 对这个页的连续访问模式必须一致.
４，异步IO(Async IO). Native AIO需要操作系统的支持.
５，刷新领接页(Flush Neighbor Page)
- 通过命令SHOW VARIABLES查看数据库中的所有参数, 也可以通过LIKE来过滤参数名.
- 慢查询日志通过参数long_query_time来设置, sql执行时间大于该值的将会被记录.
- 二进制日志文件默认未开启. 作用: 恢复, 复制, 审计. 格式:STATEMNT, ROW, MIXED.
设置为ROW，可以为数据库的恢复和复制带来更好的可靠性. 可以将隔离级别设置为READ COMMITTED, 可以获得更好的并发性.
- 从InnoDB存储引擎的逻辑结构看, 所有的数被逻辑地存放在一个空间中, 称为表空间(tablespace). 表空间又由段(segment)、区(extent)、页(page)[存放行]组成. 页在一些文档中也称为块(block).
- Mysql的VARCHAR类型最大支持65535, 单位为字节.
１，字符类型为latin1时, varchar类型最大长度为65532.
２，字符类型为GBK时,varchar的最大长度为32767.
３，字符类型为UTF-8时,varchar的最大长度为21845.- 注意: Mysql官方文档中定义的65535长度是指所有varchar列的长度总和, 如果列的长度总和超出了这个度, 将创建失败.
- Mysql的Char(N)中的N表示字符的长度, 而不是字节的长度.
- Mysql数据库本身不支持物化视图, 即Mysql数据库中的数据总是虚拟的.
- 分区的过程是将一个表或索引分解为更小、更可管理的部分.每个分区都是独立的对象, 可以独自处理, 也可以作为一个更大的对象的一部分进行处理.
- Mysql数据库支持的分区类型为水平分区, 是指将同一表中不同行的记录分配到不同的物理文件中.
[不支持]垂直分区, 是指将同一表中不同列的记录分配到不同的物理文件中.- 当前Mysql数据库支持一下集中类型的分区:
１，RANGE分区: 行数据基于一个给定连续区间的列值被放入分区. Mysql5.5开始支持RANGE COLUMNS分区.
２，LIST分区: 和RANGE分区类似, 只是LIST分区面向的是散列的值. Mysql开始支持LIST COLUMNS分区.
３，HASH分区: 根据用户自定义的表达式的返回值来进行分区, 返回值不能为负数.
４，KEY分区: 根据Mysql数据库提供的哈希函数来进行分区.
不论创建那种分区, 如果表中存在主键或者唯一索引时, 分区列必须是唯一索引的一部分组成- 数据库的应用分为两类: 一类是OLTP(在线事务处理), 另一类是OLAP(在线分析处理).
- 插入的自增长ID并发总是连续的, 如果该主键值是因为某种原因被回滚了, 则该值不会再次被自动使用.
- InnoDB存储引擎支持以下几种常见的索引:
１，B+树索引.B+树索引能找到的只是被查找数据行所在的页. 然后数据库通过把页读入到内存, 再在内存中进行行查找, 最后得到要查找的数据[B: Balance. B+树不是二叉树. B+树是为磁盘或其他直接存取辅助设备设计的一种平衡查找树. 树高度一般在2~4层(意味查找一次需要2-4次IO)]
２，全文索引[是将存储于数据库中的整本书或整篇文章中的任意内容信息查找出来的技术.它可以根据需要获得全文中有关章、节、段、句、词等信息, 进行各种统计和分析. 从InnoDB 1.2.x版本开始[mysql 5.6.4], InnoDB存储引擎开始支持全文索引.]
３，哈希索引[根据使用情况自动为表生成哈希索引]- 聚集索引是按照每张表的主键构造一颗B+树, 同时叶子节点中存放的即为整张表的行记录数据, 也将聚集索引的叶子节点称为数据页.
- 联合索引是指对表上的多个列进行索引. 联合索引(a,b)其实是根据列a,b进行排序. 下列语句可以直接使用联合索引:
１，select * from table where a=xxx order by b
在Extra选项中看到Using filesort, 说明需要额外的一次排序操作才能完成查询.- 优化器选择不使用索引的情况:
１，如果要求范围的数据量很小，则优化器还是会选择辅助索引， 但是当访问的数据占整个表中的数据的蛮大一部分时(一般是20％), 优化器选择通过聚集索引来查找数据.- USE INDEX(所用字段)只是告诉优化器可以选择该索引, 实际上优化器还是会根据自己的判断进行选择[如果mysql发现走全表比索引快，会放弃索引走全表].
- InnoDB存储引擎不需要锁升级, 因为一个锁和多个锁的开销是相同的.
- 数据库系统使用锁是为了支持对共享资源进行并发访问, 提供数据的完整性和一致性.
- InnoDB存储引擎提供一致性的非锁定读、行级锁支持. 行级锁没有相关额外的开销, 并可以同时得到并发行(MVCC)和一致性.
- latch一般称为闩锁(轻量级的锁), 要求锁定的时间必须非常端. 在InnoDB存储引擎中,latch又可以分为: mutex(互斥量)和rwlock(读写锁) => 目的是为了用来保证并发线程操作临界资源的正确性, 并且通常没有死锁检查机制.
- lock的对象是事务, 用来锁定的是数据库中的对象, 如表,页,行. 并且lock的对象仅在事务commit或rollback后进行释放(不同隔离级别释放时间可能不同). lock具有死锁机制.
- InnoDB存储引擎实现了两种标准的行级锁:
１，共享锁(S Lock), 允许事务读一行数据.(S和S兼容, 其他都不兼容[对同一记录(row)锁]).
２，排他锁(X Lock), 允许事务删除或更新一行数据.- 一致性非锁定读(consistent nonlocking read)是指InnoDB存储引擎同通过多版本控制(multi versioning)的方式来读取当前执行时间数据库中行的数据.
非锁定读, 不需要等待访问在行上X锁的释放, 此机制极大的提高了数据库的并发行.- 快照数据是当前行数据的历史版本, 每行记录可能都多个版本. 由此带来的并发控制, 称之为多版本并发控制(Mutli Version Concurrency Control, MVCC)
- 在事务隔离级别READ COMMITTED和REPEATABLE READ[默认]下, InnoDB使用非锁定的一致性读.
１，READ COMMITTED: 非一致性读总是读取被锁定行的最新一份快照数据.
２，REPEATABLE READ: 非一致性总是读取事务开始时的行数据版本.- 一致性锁定读, InnoDB存储引擎对于select语句支持两种一致性的锁定读(locking read)操作:
１，SELECT ... FOR UPDATE[对读取的行记录加一个X锁, 其他事务不能对已锁定的行上加任何锁]
２，SELECT ... LOCK IN SHARE MODE[对读取的行记录加一个S锁, 其他事务可以向被锁定的行加S锁, 但是如果加X锁, 会被阻塞]
使用上诉两句SELECT锁定语句时, 必须加上BEGIN, START TRANSACTION或者AUTOCOMMT=0[关闭自动提交].- InnoDB存储引擎有3中行锁算法:
１，Record Lock:单个行记录上的锁.
２，Gap Lock: 间隙锁, 锁定一个范围, 但不包含记录本身.
３，Next-key Lock: Gap Lock + Record Lock, 锁定一个范围, 并且锁定记录本身.区间左开右闭. 目的是为了解决Phantom Problem.
当查询的索引含有唯一属性时, InnoDB存储引擎会对Next-key Lock进行优化, 将其降级为Record Lock. 即仅锁住索引本身, 而不是范围, 从而提高应用的并发性.- 在RR隔离级别下, InnoDB存储引擎采用Next-key Lock机制来避免Phantom Problem(幻想问题)[是指在同一事务下, 连续执行两次同样的SQL语句可能导致不同的结果, 第二次SQL语句可能会返回之前不存在的行].
- 脏读: 指在不同事务下, 当前事务可以读到另外事务未提交的数据, 也就是可以读到脏数据.
- 不可重复度: 是指在一个事务内多次读取到同一数据(已提交的数据)集合, 但是数据已被修改.
- 丢失更新: 是另一个锁导致的问题, 简单来说其就是一个事务的更新操作会被另外一个事务的更新操作所覆盖, 从而导致数据不一致.[操作串行化进行避免].
- 阻塞: 因为不同的锁之间的兼容性关系, 在有些时刻有一个事务中的锁需要等待另外一个事务中的锁释放它所占用的资源, 这就是阻塞.
默认情况下InnoDB存储引擎不会回滚超时引发的错误异常, 但是死锁会回滚- 死锁: 是指两个或两个以上的事务在执行过程中, 因争夺锁资源而造成的一种互相等待的现象.
若死锁存在, 通常InnoDB存储引擎选择回滚undo量最小的事务- 锁升级: 是指将当前锁的粒度降低. InnoDB存储引擎不存在锁升级问题.
- 事务会把数据库从一种一致状态转换为另外一种一致状态.
- InnoDB存储引擎中的事务符合ACID的特性:
１，原子性(atomicity)[整个数据库事务是不可分隔的单位. 都执行成功才算成功, 否则数据库状态回退到事务前的状态]
２，一致性(consistency)[事务将数据库从一种状态转变为下一种一致的状态].
３，隔离性(isolation)[要求每个读写事务的对象对其他的事务的操作对象能相互隔离, 即该事务提交前对其他的事务都不可见, 通常用锁实现].
４，持久性(durability)[事务一旦提交, 其结果是永久性的. 及时发生宕机等故障, 数据库也能将数据恢复].- 事务可以由一条sql语句组成, 也可以由一组复杂的sql语句组成. 事务是访问并更新数据库中各种数据项的一个程序执行单元. [要么都修改, 要么都不做].
- SQL标准定义的4中隔离级别:
１，READ UNCOMMITTED.
２，READ COMMITTED. 除了唯一性的约束检查和外键约束的检查需要gap lock, InnoDB存储引擎不会使用gap lock的锁算法.
３，REPEATABLE READ. 使用Next-key Lock锁避免幻读的产生.
４，SERIALIZABLE. InnoDB会对每个select语句后面自动加上lock in share mode, 即为每个读取操作加一个共享锁.
隔离级别越低, 事务请求的锁越少或保护锁的时间就越短, 并发性就越好.
在使用分布式事务时,InnoDB存储引擎的事务隔离级别必须设置为SERIALIZABLE
- 索引小结:
- 优化器不使用索引及优化:
1.当访问的数据占表数据20%左右时, 不走索引, 使用聚集索引记性全表扫描.
2.查找大量数据列而不能使用到覆盖索引(由机械硬盘顺序读取的特性决定的, 利用顺序读取替换随机读取).
3.表的数量过少, mysql 优化器判定直接全表扫描比使用索引效率更高.- 使用index hint优化(确定非聚集索引能带来更好性能):
1.force index(index_name) : 强制走某个索引
2.use index(index_name) : 告诉优化器可以选择使用该索引, 但实际上优化器会根据自己的判断进行选择, 不一定使用到.
3.ignore index(index_name) : 告诉优化器在选择索引的时候忽略该索引, 该索引不会被使用到.
优秀数据库资源推荐:
