MySQL-索引

B+树索引的优点：
1、索引按照顺序存储数据，可以用来做ORDER BY和GROUP BY操作
2、索引中存储了实际的索引列值，所以某些査询只使用索引就能够完成全部査询（非一级索引的叶子节点存储主键）
3、索引大大减少了服务器需要扫描的数据量
4、索引可以帮助服务器避免排序和临时表
5、索引可以将随机I/O变为顺序I/O
B+树索引的缺点：
1、如果不是按照索引的最左列开始査找，则无法使用索引
2、不能跳过索引中的列（只能使用跳过前的列）
3、如果查询中有某个列的范围査询，则其右边所有列都无法使用索引优化査找(但是可以作为值返回)；如果范围査询列值的数量有限，那么可以使用多个等于条件来代替范围条件
索引策略
1、独立的列：索引列不能是表达式的一部分，也不能是函数的参数，因此要始终将索引列单独放在比较符号的一侧
2、前缀索引和索引选择性：前缀越长，选择性越好
3、聚合（多列）索引：
当出现服务器对多个索引做相交操作时（通常有多个AND条件）或对多个索引做联合操作时（通常有多个OR条件），通常意味着需要一个包含所有相关列的多列索引，而不是多个独立的单列索引（需要合并）
多列索引中索引的顺序（需要兼顾排序和分组）：当不需要考虑排序和分组时，将选择性最高的列放在前面通常是很好的
4、聚簇索引：当表有聚簇索引时，它的数据行实际上存放在索引的叶子页中，因为无法同时把数据行存放在两个不同的地方，所以一个表只能有一个聚簇索引（覆盖索引可以模拟多个聚簇索引的情况）
5、二级索引：访问需要两次索引査找，而不是一次，因为二级索引叶子节点保存的不是指向行的物理位置的指针，而是行的主键值，这意味着通过二级索引查找行，存储引擎需要找到二级索引的叶子节点获得对应的主键值，然后根据这个值去聚簇索引中査找到对应的行，这里做了重复的工作：两次B+树査找而不是一次（回表查询）
6、普通索引和唯一索引：普通索引在查找到一条记录后会继续查找，而唯一索引会终止查找
使用索引排序
1、只有当索引的列顺序和ORDER BY子句的顺序完全一致，并且所有列的排序方向（倒序或正序)都一样时，MySQL才能够使用索引来对结果做排序
2、如果査询需要关联多张表，则只有当ORDER BY子句引用的字段全部为第一个表时，才能使用索引做排序
3、ORDER BY子句和查找型查询的限制是一样的：需要满足索引的最左前缀的要求；否则MySQL都需要执行排序操作，而无法利用索引排序
有一种情况下ORDER BY子句可以不满足索引的最左前缀的要求，就是前导列为常量的时候；即如果WHERE子句或者JOIN子句中对这些列指定了常量，就可以“弥补”索引的不足

B树与B+树的区别

1、B树可能在非叶子节点命中返回（非叶子节点指针也指向数据，叶子节点不会包含非叶子节点的数据）；B+不可能在非叶子结点命中
2、B+树叶子节点存放所有数据；B+树叶子节点之间又是一个链表（适合范围查询）

MySQL-事务

事务的特性
1、原子性：一个事务必须被视为一个不可分割的最小工作单元，整个事务中的所有操作要么全部提交成功，要么全部失败回滚
2、一致性：数据库总是从一个一致性的状态转换到另外一个一致性的状态
3、隔离性：通常来说（涉及到隔离级别），一个事务所做的修改在最终提交以前，对其他事务是不可见的
4、持久性：通常来说（涉及到持久级别），一旦事务提交，则其所做的修改就会永久保存到数据库中，此时即使系统崩溃，修改的数据也不会丢失
事务的隔离级别
1、读未提交：一个事务可以读取到其他事务未提交的数据（脏读）
2、读已提交（不可重复读）：一个事务只能读取到其他事务此刻已提交的数据
3、可重复读：一个事务只能读取到其他事务在该事务开启时已提交的数据（快照读），但是无法避免幻读（单指插入，两次读取的结果不一致）
可重复读是MySQL的默认事务隔离级别，InnoDB通过MVCC（多版本并发控制）和next-key lock解决了幻读
4、串行读：强制事务串行执行，解决了幻读问题，在读取的每一行数据上都加锁，所以可能导致大量的超时和锁争用的问题
幻读：
1、幻读指的是一个事务在前后两次查询同一个范围的时候，后一次查询看到了前一次查询没有看到的行（单指插入）；在可重复读隔离级别下，普通的查询是快照读，是不会看到别的事务插入的数据的（MVCC)，幻读在“当前读”下仍会出现（加锁读时，只锁当前满足条件的行）
2、通过加间隙锁解决当前读导致的幻读，跟间隙锁存在冲突关系的，是跟 “往这个间隙中插入一个记录 ”这个操作，间隙锁之间都不存在冲突关系；间隙锁和行锁合称next-key lock，每个next-key lock是前开后闭区间
3、间隙锁是在可重复读隔离级别下才会生效的
4、一个并发问题：任意锁住一行，如果这一行不存在的话就插入，如果存在这一行就更新它的数据；因为锁的是间隙锁，并发时会锁竞争，发生死锁

MVCC-多版本并发控制（可重复读下）

查询（同时满足下面条件的，才作为结果返回）：
a、査找版本早于当前事务版本的数据行（也就是行的系统版本号小于或等于事务的系统版本号），这样可以确保事务读取的行，要么是在事务开始前已经存在的，要么是事务自身插入或者修改过的
b、行的删除版本要么未定义，要么大于当前事务版本号，这可以确保事务读取到的行，在事务开始之前未被删除
插入：为新插入的每一行保存当前事务版本号作为行版本号
删除：为删除的每一行保存当前事务版本号作为行删除标识
修改：插入一行新记录，保存当前事务版本号作为行版本号，同时保存当前事务版本号到原来的行作为行删除标识
优点：保存这两个额外系统版本号，使大多数读操作都可以不用加锁，这样设计使得读数据操作很简单，性能很好，并且也能保证只会读取到符合标准的行
缺点：每行记录都需要额外的存储空间，需要做更多的行检査工作，以及一些额外的维护工作
MySQL没有完全解决幻读问题
如：事务a先查询（MVCC），事务b插入（next-key），事务a更新（next-key，会加版本号），事务a查询（MVCC），两次查询的结果不同
意向锁（表级锁）：意向锁是由数据库自己维护的，一般来说，给一行数据加上共享锁之前，数据库会自动在这张表上面加一个意向共享锁(IS锁)；给一行数据加上排他锁之前，数据库会自动在这张表上面加一个意向排他锁(IX锁)
意向锁可以认为是共享锁和互斥锁在数据表上的标识，通过意向锁可以快速判断表中是否有记录被上锁，从而避免通过遍历的方式来查看表中有没有记录被上锁，提升加锁效率
如要加表级别的互斥锁，这时候数据表里面如果存在行级别的互斥锁或者共享锁的，加锁就会失败，此时直接根据意向锁就能知道这张表是否有行级别的X锁或者S锁

MySQL-查询性能优化

从下面几点进行优化：
1、减少扫描行数（索引）
2、减少返回的行数或列数（limit或避免*）
如：
1、证件号码、证件名称、证件类型（聚合索引，冗余索引，顺序问题，减少回表）
2、select a.id,a.name from user a inner join (select b.id from user order by a.userId limit 1000,10) b on a.id = b.id
（原始的写法：select a.id,a.name from user a order by a.userId limit 1000,10）
（使用一级索引，减少回表）
join（小表驱动大表，大表用索引）
1、在可以使用被驱动表的索引（join字段）情况下，使用join语句，性能比强行拆成多个单表执行SQL语句的性能要好；如果使用join语句的话，需要让小表（根据条件查询出来少的表）做驱动表
2、在判断要不要使用join语句时，就是看explain结果里面，Extra字段里面有没有出现“Block Nested Loop”字样（出现则不用join）
3、如果用left join的话，左边的表一定是驱动表吗？不是
4、如果两个表的join包含多个条件的等值匹配，是都要写到on里面呢，还是只把一个条件写到on里面，其他条件写到where部分？写到on里面
5、在MySQL里，NULL跟任何值执行等值判断和不等值判断的结果，都是NULL；select NULL =NULL 的结果，也是返回 NULL

MySQL-执行流程

MySQL执行一个査询的过程：
1、客户端发送一条査询给服务器
2、服务器先检査査询缓存，如果命中了缓存，则立刻返回存储在缓存中的结果，否则进入下一阶段
3、服务器端进行SQL解析、预处理，再由优化器生成对应的执行计划
4、MySQL根据优化器生成的执行计划，调用存储引擎的API来执行査询
5、将结果返回给客户端
更新：先找到要更新的数据，从磁盘读入内存；在执行器中执行语句，调用引擎先把记录写到redo log（覆盖写，磁盘中，物理日志）里面（原先的记录会写到undo log中，用于回滚），并更新内存，此时还未提交事务，在适当的时候，再将记录更新到磁盘；执行器写到binlog（不覆盖写，磁盘中，逻辑日志（语句））；引擎将redo log改成提交状态，更新完成，即两阶段提交