高性能MySQL（1）

MySQL常见面试总结

1. 并发事务带来哪些问题？

   脏读(Dirty read)：一个事务读到另一个事务未提交的更新数据。

   丢失修改(Lost to modify)：一个事务访问数据并对其修改时，另外一个事务也访问了该数据并进行了修改。第二次修改覆盖了第一次的修改，导致第一次修改的数据丢失。

   不可重复读(Unrepeatableread)：一个事务两次读取同一行数据，两次读到的数据不一样。（重点在于修改）

   幻读（Phantom read）：一个事务执行两次查询，第二次查询比第一次查询多出了一些数据行。（重点在于数据库中的数据有新增或者删除）

2. 如何解决并发带来的问题呢？四种隔离级别（由低到高）

   数据库系统采用不同的锁类型来实现以上四种隔离级别，具体的实现过程对用户是透明的。
   MySQL隔离级别
   

   read uncommitted：读未提交。就是一个事务可以读取另一个未提交事务的数据，导致脏读（脏读：事务A读取了事务B更新的数据，然后B回滚操作，那么A读取到的数据是脏数据）。
   例：老板要给程序员发工资，程序员的工资是3.6万/月。但是发工资时老板不小心按错了数字，按成3.9万/月，该钱已经打到程序员的户口，但是事务还没有提交，就在这时，程序员去查看自己这个月的工资，发现比往常多了3千元，以为涨工资了非常高兴。但是老板及时发现了不对，马上回滚差点就提交了的事务，将数字改成3.6万再提交。分析：实际程序员这个月的工资还是3.6万，但是程序员看到的是3.9万。他看到的是老板还没提交事务时的数据，这就是脏读。如何解决脏读呢？–》读提交

read committed：读已提交，就是一个事务要等另一个事务提交后才能读取数据，导致不可重复读（不可重复读：事务 A 多次读取同一数据，事务 B 在事务A多次读取的过程中，对数据作了更新并提交，导致事务A多次读取同一数据时，结果 不一致。）
例：程序员拿着信用卡去享受生活（卡里当然是只有3.6万），当他埋单时（程序员事务开启），收费系统事先检测到他的卡里有3.6万，就在这个时候！！程序员的妻子要把钱全部转出充当家用，并提交。当收费系统准备扣款时，再检测卡里的金额，发现已经没钱了（第二次检测金额当然要等待妻子转出金额事务提交完）。程序员就会很郁闷，明明卡里是有钱的。那怎么解决可能的不可重复读问题？–》Repeatable read ！

repeatable read（mysql默认的隔离级别）：同一个事务在执行的不同阶段看到的数据结果一致
例：程序员某一天去消费，花了2千元，然后他的妻子去查看他今天的消费记录（全表扫描FTS，妻子事务开启），看到确实是花了2千元，就在这个时候，程序员花了1万买了一部电脑，即新增INSERT了一条消费记录，并提交。当妻子打印程序员的消费记录清单时（妻子事务提交），发现花了1.2万元，似乎出现了幻觉，这就是幻读。那怎么解决幻读问题？Serializable！
重复读可以解决不可重复读问题。写到这里，应该明白的一点就是，不可重复读对应的是修改，即UPDATE操作。但是可能还会有幻读问题。因为幻读问题对应的是插入INSERT操作，而不是UPDATE操作。

serializable：可串行化。通过强制事务串行执行，避免了幻读的问题。serializable会在读取的每一行数据上都加锁，可能导致大量的超时和锁争用问题。
Serializable 是最高的事务隔离级别，在该级别下，事务串行化顺序执行，可以避免脏读、不可重复读与幻读。但是这种事务隔离级别效率低下，比较耗数据库性能，一般不使用。

3. 乐观锁和悲观锁

4. B+树与Hash索引：表是根据主键顺序以索引形式存放。每一个索引在InnoDB中对应一个B+树。
   二叉树
   <>
   如图所示，有一个user表的数据有id和name字段，id是主键，主键是聚集索引。如果用二叉树存储这个user表的聚集索引，结构就如右图所示。二叉树的特点是左侧的子节点的值都比父节点小，右侧的子节点的值都比父节点大。如果我们要查找某一个值，就从顶节点开始查找，这样不会超过数的高度的次数，就能查找到指定的值，效率比全表扫描是要高。
   <>
   B树
   使用平衡二叉树存储索引有两个缺点
   平衡二叉树每个节点只存储一个键值，当数据量特别的大的时候，树的高度也必然很高，查找次数也会很多，影响效率，毕竟每次查找都是一次读盘的操作。
   从磁盘读取数据时是每次读取一个页的数据，如果每次读取只读取一个键值，也是对磁盘的浪费
   <>
   B 树相对于平衡二叉树，有两个改进。基于这两点改进，高度变小了，读取磁盘的次数变少了，索引查找效率就高了。
   
   <>
   每个节点（页）存储了更多的键值（key）和数据（data）。
   每个节点拥有更多的子节点，并不只是二叉树了。

   B+树
   B树缺点
   非叶子节点既存key也存数据，而每个页大小是固定的，所以每个页中存储的key比较少，数据量大的时候树依然比较高。
   数据不是按顺序存储的，当范围查找、排序查找、去重查找的时候，需要读取多个级别的多个页，效率比较低。
   
   特点
   非叶子节点只存key不存数据。
   B+树中，非叶子节点是不存储数据的，只存储key，这样每个页能够存储更多的key，使得树更胖更矮，所以读取磁盘次数更少。假如B+树一个节点可以存储 1000 个键值，那么 3 层 B+ 树可以存储 1000×1000×1000=10 亿个数据，一般根节点是常驻内存的，所以一般我们查找 10 亿数据，只需要 2 次磁盘 IO。 所有数据都存在叶子节点
   所有的数据都存在叶子节点，所以数据是按顺序存储的，使得范围查找、排序查找更加方便。
   B+树的页之间用双向链表连接，数据间用单项链表链接

   页之间有双向链表链接，使得扫描数据更加快捷。

聚集索引
上面说的B+树在叶子节点存储数据，这样的索引实际上就是聚集索引，像MySQL里会默认根据主键创建的索引就是聚集索引，根据主键构建一棵B+树，主键所对应的值直接存在叶子节点中。
根据主键查找的意义图如下所示：select * from user where id>=18 and id <40

<>
非聚集索引
根据主键意外的字段创建的索引一般都是非聚集索引，非聚集索引也是用B+树构建的，他和聚集索引的唯一不同就是叶子节点中保存的值不是实际的值，而是主键值，找到主键值后再去聚集索引中查找。
举例：有这样一张表，id是主键，我们在luckyNum字段上创建的非聚集索引 select * from user where luckNum=33

非聚集索引是否一定要会表查询
不一定，当查询语句的字段全部命中了索引，就不必回表查询。例如在员工表的年龄上建立了索引，当进行select age from employee where age<20的查询时，在索引的叶子节点上，已经包含了age信息，不会在回表查询。

如何避免回表查询
索引覆盖被查询的字段 例如：select age_1 from employee where age<20。此时age_1是被查询的字段，age是索引。索引覆盖了被查询的字段。但select age_1, name from employee where age<20，此时的索引age并没有覆盖被查询的字段age,name.
覆盖索引
查询的索引覆盖了所有需要查询的字段值
优势：覆盖索引可以减少树的搜索次数（避免回表），显著提高查询性能，是一个常用的优化手段。

Hash索引
索引只存储哈希码及行指针，而不是字段值，所以查找到对应的指针后同样需要读取数据中的行。
哈希索引不支持索引列的部分查找，因为哈希索引始终使用索引列的全部内容来计算哈希码。
哈希索引不是按照索引值的顺序存储的，所以无法用于排序。
只支持等值比较查询，包括=，IN()，<=>，不支持范围查询。
哈希冲突会影响查询速度，此时需遍历索引中的行指针，逐行进行比较。