mysql引擎，BTree和B+Tree和锁

1.MySQL存储引擎

引擎的概念

• 生活中，引擎就是整个机器运行的核心，不同的引擎具备不同的功能。（五菱宏光和法拉利的引擎）

MySQL存储引擎的概念

• MySQL数据库使用不同的机制存取表文件,差别在于不同的存储方式、索引技巧、锁定水平,不同的技术及配套的功能称为存储引擎.
• 在关系型数据库中数据的存储是以表的形式存进行储的,存储引擎也可以称为表类型.
• Oracle , SqlServer等数据库只有一种存储引擎,MySQL支持的存储引擎有多种，最常用的三种是InnoDB、MyISAM、MEMORY。
• 通过选择不同的引擎 ,能够获取最佳的方案 , 也能够获得额外的速度或者功能，提高程序的整体效果。所以了解引擎的特性 , 才能贴合我们的需求 , 更好的发挥数据库的性能。

-常用的存储引擎

• MyISAM存储引擎

  • 访问快,支持表锁，不支持事务。应用是以查询操作和插入操作为主

• 数据对安全性要求不高，不要重要；例如：日志,评论
  Eg:日志,评论…

  • InnoDB存储引擎(MySQL5.5版本后默认的存储引擎)，支持事务 ,支持外键，支持行级锁（表锁），占用磁盘空间大 ,支持并发控制。所以用于对事务的完整性有比较高的要求，比如BBS、计费系统、充值转账等

• MEMORY存储引擎

  • 支持表锁，数据存储在内存中, 速度快 ,不安全 ,适合小量快速访问的数据。它将所有数据保存在RAM中，在需要快速定位记录和其他类似数据环境下，可以提供更快的访问。MEMORY的缺陷就是对表的大小有限制，太大的表无法缓存在内存中，其次是要确保表的数据可以恢复，数据库异常终止后表中的数据是可以恢复的。MEMORY表通常用于更新不太频繁的小表，用以快速得到访问结果。

• 总结：针对不同的需求场景，来选择最适合的存储引擎即可！如果不确定、则使用数据库默认的存储引擎！

MySQL索引

MySQL数据库中的索引：是帮助MySQL高效获取数据的一种数据结构！所以，索引的本质就是数据结构。

结构分类

• B+Tree索引 ：MySQL使用最频繁的一个索引数据结构，是InnoDB和MyISAM存储引擎默认的索引类型。
• Hash索引 : MySQL中Memory存储引擎默认支持的索引类型。

磁盘存储

• 系统从磁盘读取数据到内存时是以磁盘块（block）为基本单位的
• 位于同一个磁盘块中的数据会被一次性读取出来，而不是需要什么取什么。
• InnoDB存储引擎中有页（Page）的概念，页是其磁盘管理的最小单位。InnoDB存储引擎中默认每个页的大小为16KB。
• InnoDB引擎将若干个地址连接磁盘块，以此来达到页的大小16KB，在查询数据时如果一个页中的每条数据都能有助于定位数据记录的位置，这将会减少磁盘I/O次数，提高查询效率。

1.BTree

• 如图，以根节点为例，关键字为17和35，磁盘块1重的P1指针指向的子树的数据范围为小于17，P2指针指向的子树的数据范围为17~35，P3指针指向的子树的数据范围为大于35。

模拟查找15的过程 : 
1.根节点找到磁盘块1，读入内存。【磁盘I/O操作第1次】
	比较关键字15在区间（<17），找到磁盘块1的指针P1。
2.P1指针找到磁盘块2，读入内存。【磁盘I/O操作第2次】
	比较关键字15在区间（>12），找到磁盘块2的指针P3。
3.P3指针找到磁盘块7，读入内存。【磁盘I/O操作第3次】
	在磁盘块7中找到关键字15。
	
-- 分析上面过程，发现需要3次磁盘I/O操作，和3次内存查找操作。
-- 由于内存中的关键字是一个有序表结构，可以利用二分法查找提高效率。
**而3次磁盘I/O操作是影响整个BTree查找效率的决定因素。
  BTree使用较少的节点个数，使每次磁盘I/O取到内存的数据都发挥了作用，
  从而提高了查询效率。**

1.B+Tree

B+Tree是在BTree基础上的一种优化，使其更适合实现外存储索引结构，InnoDB存储引擎就是用B+Tree实现其索引结构。

从的BTree结构图中可以看到每个节点中不仅包含数据的key值，还有data值。而每一个页的存储空间是有限的，如果data数据较大时将会导致每个节点（即一个页）能存储的key的数量很小，当存储的数据量很大时同样会导致B-Tree的深度较大，增大查询时的磁盘I/O次数，进而影响查询效率。
在B+Tree中，所有数据记录节点都是按照键值大小顺序存放在同一层的叶子节点上，而非叶子节点上只存储key，这样可以大大加大每个节点存储的key值数量，降低B+Tree的高度，从而减少IO次数。

B+Tree;
1.非叶子节点，只存储key,不存储数据本身；
2.叶子节点，用来存储数据；
3.叶子节点之间通过链表相连；
索引设计原则：
1.索引适合于大表，表中数据经常被查询；
2.字段如果经常作为查询条件，比较适合创建索引；
3.索引可以提高查询的效率， 但是会降低增删改效率；
4.组合索引生效时机(最左匹配原则)
eg: create index index_name_age on user(name,age);
查询条件中必须包含name字段，组合索引才会生效；
select * from user where age = 12; 索引不会生效。

MySQL锁（面试）
介绍：防止多个事务操作共享数据，由于并发导致的数据安全；
例子：张三到银行取钱；
共享锁（读锁）:（可以读）
select语句 lock in share mode;
如果查询条件中是基于带索引的字段进行查询是行锁；
如果查询条件中不是基于带索引的字段进行查询是表锁；
特点：
A事务加了共享锁，B事务只能对数据进行读操作；B事务对数据进行改操作出现 等待,需要等待A事务结束;
A事务和B事务同时操作共享数据，报错；

排他锁；（不能读写）
select语句 for update;
特点：
A事务加了排他锁，B事务在带锁查询的情况下；B事务对数据不能进行读，也不能进行写，必须等待A事务结束；

表锁
MyISAM;
表锁（读锁）
lock table 表名称 read;
unlock tables;
特点：
A线程加了表锁，A线程只能对表中数据进行读，不能进行写（报错）;
其他线程操作表，其他线程能读，写操作会产生等待…A线程对表进行解锁

乐观锁
1.通常需要给表添加字段，表示该数据的版本号；

2.每次修改数据都是基于上次查询的版本号，修改后数据的版本号+1
start transaction;
select * from account_version where id = 1;
– 如果账户金额>=1000再进行取钱
update account_version set money = money - 1000,VERSION=VERSION+1 where id=1 and VERSION =1;
commit;

6.锁的总结

• 表锁和行锁

  • 行锁：锁的粒度更细，加行锁的性能损耗较大。并发处理能力较高。InnoDB引擎默认支持！
  • 表锁：锁的粒度较粗，加表锁的性能损耗较小。并发处理能力较低。InnoDB、MyISAM引擎支持！

• InnoDB锁优化建议

  • 尽量通过带索引的列来完成数据查询，从而避免InnoDB无法加行锁而升级为表锁。

  • 合理设计索引，索引要尽可能准确，尽可能的缩小锁定范围，避免造成不必要的锁定。

  • 尽可能减少基于范围的数据检索过滤条件。

  • 尽量控制事务的大小，减少锁定的资源量和锁定时间长度。

  • 在同一个事务中，尽可能做到一次锁定所需要的所有资源，减少死锁产生概率。

  • 对于非常容易产生死锁的业务部分，可以尝试使用升级锁定颗粒度，通过表级锁定来减少死锁的产生。