mysql索引B+树选型 索引 事务 MVCC

哈希索引

哈希索引查找快，但是无序，不能做范围查找
同时存在哈希碰撞的问题，如果值都在某一个哈希，也会导致检索很慢

平衡二叉树 Balance

树越高查找速度越慢
对范围查找也很慢，需要解决回旋查找的问题

平衡二叉树，会动态平衡树结构，当一侧的层级高度高于另外一侧大于1时，则自动去平衡左右关系，来达到平衡。当左右高度相差大时，查询速度时快时慢，不稳定，当左右平衡后，查询速度就会比较稳定。如下图所示：

B树

B树的高度比平衡二叉树高度变矮，一个节点可以存多个数据。数据大小由磁盘最小单位扇区决定。
每个硬盘都会有多个盘片，每个盘片上分割如图，呈同心圆环状，圆圈就是磁道，每个磁道被等分为以512个字节为单位的若干个弧段，这些弧段便是磁盘的扇区，每个扇区可以存放512个字节的信息，扇区就是磁盘的最小单位了。所以，如果要达到最理想查询效果，应该是想办法让每个节点中存储的数据的数据量大小接近512个字节。

B 树

B 树解决了回旋查找的问题

所有叶子节点组成一个链表

联合索引（多键值索引）
最左匹配原则，一定要带上前面的索引

mysqlB 树层高计算

根节点指针数
怕你没认真看上面，我再说一遍，B 树的非叶子节点仅保存索引字段和指针，假设主键为bigint类型，InnoDB 的bigint占用8个字节，指针占用6个字节，8 6=14，所以我们可以得出，一个page能存放的指针个数为16k/(8 6)约等于1170

每个指针对应第二层的行记录数
再来说说一个page能存储多少条行记录，常规的互联网项目单条行记录大小约为1k，那么一个page能存储的行记录数为16k/1k=16

所以一个2层高的b 树能存储的行记录数大约为117016=18720
**3层为11701170*16约等于2190w**

MyIsam和InnoDB引擎区别

外键方面

innodb支持外键，myisam不支持外键，对一个包含外键的innodb表转为myisam会失败

索引层面

myisam是非聚簇索引
非聚簇索引：将数据和索引不放在一起

innodb是聚簇索引，
聚簇索引：将数据和索引放在一起

myisam支持全文索引
inbodb不支持全文索引，可通过sphinx插件支持全文索引，并且效果更好

锁粒度方面

innodb最小支持行锁
myisam最小支持表锁，一个更新语句会导致全表被锁

硬盘存储结构

myisam三个文件
.frm 存表结构定义
.MYD 存数据
.MYI 存索引

inbodb两个文件
.frm 存表结构定义
.Idb 保存数据和索引

辅助索引->聚簇索引

innodb 包含聚簇索引和非聚簇索引，主键或唯一键或row-id那颗索引树为聚簇索引
myISAM 为非聚簇索引

**回表操作：**根据某个非聚簇索引找到id，再根据id去聚簇索引中拿数据
**索引覆盖：**将查询字段作为联合索引，count时指定索引列

事务的四大特性ACID :
原子性 undo log 来实现
持久性 redo log 预写日志WAL(write ahead log）内存和磁盘间的交互有一份redo log，如果实际数据没有写入成功可以根据redo log来进行恢复
二阶段提交

一致性
隔离性 MVCC加锁
怎么用理解ACID,理解一致性？
原子性：
强调对数据的状态的描述，要么成功要么失败。不存在部分成功的状态。

一致性：
强调对数据的可见性描述，对于数据的中间态是不可见的。

隔离性：
如果完全按照一致性的定义的话，就只能是通过加排他锁来走完全序列化才能达到强一致性也就是serialization这种隔离机制，引入隔离机制就是为了提高并发，提高可用性，因为完全串行化访问效率太低，现实中很少用到，做工程是以实用为主，所以引入不同等级隔离机制，来有针对性的面向各类场景对一致性达到不同等级破坏，以提高并发提高可用。

持久性：
持久性比较好理解，就是对于存储的各类信息一旦提交，那么无论多久都将不变，这也是存储的意义。

MVCC

多版本并发控制
什么是当前读和快照读？

数据库四中隔离级别：
读未提交
读已提交 (存在脏读和幻读)
可重复读（默认） 可避免脏读（update）不能避免幻读（insert or update）
串行化、序列化

undo log和版本涟

readview

readview决策应该访问版本链中那条数据

read view中四变量。活跃事务（活跃事务就是没提交的事务）集合，当前事务id，集合中的最小值，集合中最大值的下个值。都是select查出的最新数据事务id跟四个变量进行比较判断的。判断方法可以逆推，就是凡是小于最小值，不包含活跃事务里面的，刚好等于当前事务的id，都可以查询最新值。其他任何情况都要一直递归查询undo日志，直到符合这三个条件为止。

能否看到修改的数据，取决于可见性算法，可见性算法比较的时候取决于readview中的值
在RC 和 RR不同隔离级别的时候，生成readview的时机是不同的：
RC：每次执行快照读的时候都会生成新的readview
RR: 只有在事务第一次进行快照读的时候才会生成readview，之后的快照读都沿用第一次生成的readview

如果一个事务中同时存在当前读（update/insert/delete/for update）和快照读，则可能出现幻读问题。
可以使用for update 加锁，其他事务不能再进行插入/删除

mysql InnoDB引擎默认的修改数据语句，update,delete,insert都会自动给涉及到的数据加上排他锁，select语句默认不会加任何锁类型，如果加排他锁可以使用select …for update语句，加共享锁可以使用select … lock in share mode语句。所以加过排他锁的数据行在其他事务种是不能修改数据的，也不能通过for update和lock in share mode锁的方式查询数据，但可以直接通过select …from…查询数据，因为普通查询没有任何锁机制。

读已提交：MVCC实现是在每次select时生成readview
可重复读：MVCC实现是以事务为单位的，此时readview是事务级的

innodb默认开启间隙锁，间隙锁解决了幻读问题，锁一段范围

mysql中的锁

读锁（共享锁）：Shared Locks（S锁），针对同一份数据，多个读操作可以同时进行而不会互相影响
写锁（排它锁）：Exclusive Locks（X锁），当前写操作没有完成前，它会阻断其他写锁和读锁
IS锁：意向共享锁、Intention Shared Lock当事务准备在某条记录上加S锁时，需要先在表级别加一个IS锁。
IX锁：意向排他锁、Intention Exclusive Lock当事务准备在某条记录上加X锁时，需要先在表级别加一个IX锁。
意向锁属于表级锁。
**意向锁的用途：**事务A锁住了表中的一行，让这一行只能读，不能写。之后，事务B申请整个表的写锁。如果事务B申请成功，那么理论上它就能修改表中的任意一行，这与A持有的行锁是冲突的。
数据库需要避免这种冲突，就是说要让B的申请被阻塞，直到A释放了行锁。

行锁
表锁
页级锁
间隙锁（锁一段范围防止幻读）

自增锁（AUTO-INC锁）
自增锁是一种特殊的表级锁，主要用于事务中插入自增字段，也就是我们最常用的自增主键id。通过innodb_autoinc_lock_mode参数可以设置自增主键的生成策略。防止并发插入数据的时候自增id出现异常。

当一张表的某个字段是自增列时，innodb会在该索引的末位加一个排它锁。为了访问这个自增的数值，需要加一个表级锁，不过这个表级锁的持续时间只有当前sql，而不是整个事务，即当前sql执行完，该表级锁就释放了。其他session无法在这个表级锁持有时插入任何记录。

sql 调优

执行计划 explain
type : 查询类型 ALL INDEX RANG REF …至少应该是RANG特别注意ALL
key: 表明用到哪个索引
Extra: 额外信息
extra可能的值有Using filesort，说明mysql对数据使用一个外部的索引排序，而不是按照表内的索引排序进行读取，数据较少时从内存排序，否则从磁盘排序。mysql无法利用索引完成的排序操作成为“文件排序”。出现这种情况是非常危险的，需要优化。
extra可能的值有Using temporary。使用了临时表保存中间结果，mysql在对查询结果排序时使用临时表而没有走索引，常见于排序order by和分组查询group by。这种情况非常危险，这个是拖慢sql的元凶。当mysql需要创建临时表时，选择内存临时表还是硬盘临时表取决于参数tmp_table_size和max_heap_table_size，内存临时表的最大容量为tmp_table_size和max_heap_table_size值的最小值，当所需临时表的容量大于两者的最小值时，mysql就会使用硬盘临时表存放数据。解决方案①想办法让排序走索引②增加内存临时表的空间。
extra可能的值有Using index。表示相应的select操作中使用了覆盖索引，避免了访问表的数据行，效率不错！，如果同时出现using where，表名索引被用来执行索引键值的查找。

mysql自带优化器
连接器 校验
分析器 词法语法分析 AST树
优化器 RBO基于规则优化/CBO基于成本优化-常用
执行器 执行具体sql语句
查询缓存 8.0后已经取消，因为命中率非常低

例：
基于前缀基数，截取前缀作为索引，例：cityName

主从复制

主数据库修改->binlog->IOthread->RelayLog->SQLThread->从库

读写分离

实现读写分离的三种方式：
自己实现 MysqlProxy 进行调度
Mycat
Shardingsphere（推荐，Apache顶级项目）

可配合MHA插件，实现故障转移。

分库分表（亿级以上）

使用读写分离后，数据过大，单库已经不能承担
水平切分：按条数或者时间分表（查询时尽量带上分片键，否则会轮训查询后合并结果集）
分片算法：
① 例主键范围法 数据分布不均匀，易于扩张
② hash法，例取模，是数据均匀，节点扩展困难

垂直切分：
为什么要垂直切分？
innodb引擎内部最小存储单位为页，每页默认16K，页中存储的是行数据，会将数据进行压缩。如果每行数据过大即列很多，会导致每页能放的行数变少。
在查询过程中，会进行将每页的数据进行解压解析，因此涉及到的页越多，效率越低。
所以我们可以将表进行垂直切分，使每页能够存储更多的行数。

索引类型

普通索引
主键索引 只有一个与数据绑定
唯一索引
联合索引
全文索引 - 倒排索引

这篇好文章是转载于：学新通技术网