mysql索引

索引

聚簇索引

是一种数据存储方式（所有的用户记录都保存在了叶子节点），也就是所谓的数据即索引，索引即数据，“聚簇”是指数据行和相邻的键值聚簇在一起

优点：

（1）访问速度快：因为聚簇索引是将索引和数据保存在同一个B+树中

（2）对主键的排除查找和范围查找速度很快

注意：每个mysql的表只能有一个聚簇索引，一般情况下就是该表的主键

聚簇索引只存在于InnoDB引擎下，只有在搜索条件是主键值时才会发生作用

二级索引

也称为辅助索引、非聚簇索引

同样适用B+树进行存储，但是叶子节点只保存当前二级索引和聚簇索引值，当根据二级索引进行查找数据时，需要先查询到聚簇索引，在根据聚簇索引查询对应的数据，即会经历一次回表操作

联合索引

使用多条表字段进行组合形成一个联合索引，属于非聚簇索引

数据页进行存储数据时，先按照A字段的值进行排序，当A字段的值相同时再根据B字段的值进行排序，在叶子节点上保存A、B和聚簇索引值

B+树索引注意事项

（1）根页面万年不动

1、在第一次存储数据时，开辟一个叶子节点（数据页）空间保存数据

2、当数据足够多需要页分裂的时候，将当前叶子节点进行复制，并将当前叶子节点转换升级成为目录页，复制出的成为新的叶子节点

3、直到最后，初始的叶子节点最终升级为根页面（一直不动）

（2）内节点目录记录的唯一性

1、在二级索引使用中，当内节点二级索引值保持一致时，插入新的数据时不确定数据页地址

2、为了保证新插入记录页能找到对应数据页地址，需要保证B+树的同一层节点内的目录项记录除页号字段以外是唯一的

3、此时二级索引的内节点的目录项记录为索引列的值 + 主键值（聚簇索引值） + 页号

（3）一个页面至少存储两条记录

锁

（1）从数据操作类型划分：

读锁：也叫共享锁，针对同一份数据，允许多个事务的读操作同时进行

写锁：也叫排他锁，当前写操作未执行完的情况下，不允许其他事务进行写操作或者读操作

注意：在InnoDB引擎下，读锁和写锁可以加在表上，也可以加在行上

（2）从数据操作粒度划分：

表锁：在针对某一个表执行例如ALTER TABLE、DROP TABLE等DDL语句时，其他事务对该表执行SELECT、INSERT、UPDATE、DELETE语句时会发生阻塞

其中意向锁属于表锁的一种

1、意向共享锁：SELECT * FROM TABLE …LOCK IN SHARE MODE

2、意向排他锁：SELECT * FROM TABLE …FOR UPDATE

意向锁是存储引擎自己维护的，用户无法手动操作意向锁，在未数据添加共享/排他锁之前，InnoDB会先获取数据行所在表的对应意向锁

行锁：将表中一条记录锁上

当事务获取到共享锁时，其他事务也能获取共享锁，但不能获取排他锁

当食物获取到排他锁时，其他事务既不能获取共享锁，也不能获取排他锁

页锁：针对每一个数据页（包含多条激励）进行加锁，并发度一般，可能会出现死锁

（3）从对待锁的态度划分：

悲观锁：共享资源每次只给一个线程事务使用，其他线程阻塞，用完后再把资源转让给其他线程（如同Java中的Synchronized和ReentrantLock）

乐观锁：不采用数据库的锁机制，一般通过版本号机制和CAS机制来实现。乐观锁适用于多读的应用类型，提高吞吐量

隐藏字段、Undo Log版本链

在InnoDB存储引擎中，聚簇索引记录中存在两个必要的隐藏项：

（1）trx_id：每次一个事务对某条聚簇索引记录进行改动时，都是记录上该事务的事务id

（2）roll_pointer：每次对聚簇索引记录进行改动时，都会把旧版本写入到undo日志中，这个隐藏列相当于一个指针，可以通过它来找到记录修改前的信息

多版本并发控制MVCC

MVCC是通过数据行的多个版本管理来实现数据库的并发控制，提供在InnoDB事务隔离级别下执行一致性读操作的保证

为了查询一些正在被另一个事务更新的行，并且可以看到它们被更新之前的值，这样在做查询的时候就不用等待另一个事务释放锁

MVCC实现依赖：隐藏字段、Undo Log、Read View

Read View

假如另一个事务已经修改了记录但未提交，是不能直接读取最新版本的记录的，核心问题是需要判断一下版本链中哪个版本是当前事务可见的，这就是Read View要解决的问题

重要内容：

（1）creator_trx_id：创建这个Read View的当前事务id

只有在对表中的记录做改动时（执行insert、update、delete等语句）才会为事务分配id

（2）trx_ids：表示生成Read View时当前系统中活跃的读写事务的事务id列表

（3）up_limit_id：活跃的事务中最小的事务ID

（4）low_limit_id：表示生成Read View时系统应该分配给下一个事务的事务id值

在访问某条记录时，只需要按照规则判定记录的某个版本是否可见，规则如下

（1）若被访问版本事务ID（trx_id）值与Read View中的creator_trx_id值相同，说明当前事务在访问自己修改过的记录，则允许访问

（2）若被访问版本事务ID（trx_id）值小于Read View中的up_limit_id值，说明生成该版本的事务在当前事务生成Read View前已经提交，则允许访问

（3）若被访问版本事务ID（trx_id）值大于等于Read View中的low_limit_id值，说明生成该版本的事务在当前事务生成Read View后才开启，则不允许访问

（4）若被访问版本事务ID（trx_id）值在Read View的up_limit_id和low_limit_up值之间，则需要判断事务ID（trx_id）是否在活跃事务id列表（trx_ids）中

​ 若存在trx_ids中，说明创建Read View时生成该版本的事务还是活跃的，则该版本不允许被访问

​ 若不存在trx_ids中，说明创建Read View时生成该版本的事务已经被提交，则该版本允许被访问

整体操作流程：

（1）首先获取事务自己的版本号，也就是事务ID

（2）获取Read View

（3）查询得到的数据，然后与Read View中的事务版本号进行比较

（4）如果不符合Read View规则，就需要从Undo Log中获取历史快照

（5）最后返回符合规则的数据

注意事项：

（1）在隔离级别为读已提交（READ COMMITTED）时，每次读取数据前都生成一个Read View

（2）在隔离级别未可重复读（REPEATABLE READ）时，只会在第一次读取数据时生成一个Read View，之后的查询都继续复用该Read View

主从复制

主从复制的作用：读写分离、数据备份、高可用

原理：Slave会从Master读取binlog来进行数据同步

主从同步数据要保持读库和写库的数据一致（最终一致性）

写数据必须到写库，读数据不一定到读库