MySQL复习过程中的查漏补缺

这篇的内容是自己复习MySQL过程中觉得比较难，自己还没有完全掌握的部分，今天刚整理完，分享出来希望对一些人有帮助

MySQL

1 数据类型相关

1.1 varchar

1. 在MySQL5.0后，varchar(20)指的是20个字符【根据编码不同占的字节数不同】，最大大小65532字节

   字符若为gbk，每个字符最多占2个字节，最大长度【即括号里的值】不超过32766

   字符若为utf8，每个字符最多占3个字节，最大长度不超过21845

1.2 limit语句

limit 5,10 //检索6-15
limit 95,-1  //检索96-last
limit 5 //检索前5个

2 索引

2.1 MySQL索引底层是怎样的？

1. MySQL索引其实就是当我们插入数据时，对于插入的数据，根据我们建立的索引进行排序，并通过指针将数据连接起来；同时为了进一步优化查询效率，采用B+树的结构，对数据进行分页存储，每一页大小16KB；并且，顶层索引常驻内存，对于3层B+树结构，进行一次查询最多进行2次磁盘IO。

2.2 聚簇索引和非聚簇索引？

1. 聚簇索引：聚簇索引就是叶子节点中将数据和索引放到一起。

2. 非聚簇索引：在聚簇索引基础之上创建。叶子节点存储的是辅助索引和对应数据的聚簇索引键。【即需要二次查找】

   为什么不记录内存地址，记录聚簇索引键？

   1. 如果存地址，增删改时由于内存变化还需要维护非聚簇索引，成本高

   像复合索引、前缀索引、唯一索引都是属于辅助索引

2.3 innoDB和MyISAM

1. innoDB中默认为主键建立聚簇索引，若没定义主键，innoDB会选择唯一且非空的索引代替，若不存在，则会隐式定义一个主键作为聚簇索引【如果设置了主键为聚簇索引又希望单独设置聚簇索引，需要先删除主键，后添加我们想要的聚簇索引，再恢复设置主键即可】
2. MyISAM使用非聚簇索引，叶子节点存储的都是索引和一个指向真正表数据的指针。【因此通过辅助索引也不需要二次查找】

2.4 innoDB的优势在哪里？

1. 由于聚簇索引采用分页存储，当我们访问同一页的不同数据时，页已经提前加载到缓冲区，可以减少磁盘IO操作，提高访问速度
2. 维护成本低，innoDB中辅助索引叶子节点存储的是主键值，当发生数据的增删改时，我们只需要维护聚簇索引。同时，由于存储的是主键值，减少了辅助索引占用的空间大小

2.5 使用聚簇索引要注意什么？

1. 不建议使用uuid，太过离散，当我们插入时可能要对页中数据进行移动，维护成本高；建议使用雪花算法【在分布式系统中产生全局唯一且层递增趋势的ID】

int 不利于分布式。

2.6 索引失效

1. 使用模糊查询like且第一个字符串为%，索引会失效
2. 使用or关键字时，如果**or前后有一个条件的列不是索引**，索引失效
3. 不要对索引进行操作（计算、函数、自动或者手动的类型转换），否则索引失效
4. 范围查询之后的索引全部失效

• 针对复合索引：

  1. 不要跨列或者无序使用；

  2. 不要使用不等号（!=和<>）或者is null，否则自身及其右侧的索引全部失效

3 事务

3.1 如何理解innoDB中的事务？

1. 事务可以使得一组操作要么都成功，要么都失败
2. 而事务有四大特性

3.2 事务的四大特性？

1. 原子性（Atomic）：事务的原子性就是指当前事务中的的全部操作要么都成功要么都失败；

   原子性通过undo log日志来保证，undo log记录着数据的版本变更记录；可以简单理解成比如我们要执行insert操作，undolog会记录一条对应的delete日志，我们要执行update操作，undolog会记录对应的旧值的update操作。

   如果事务执行过程中出现了异常，就利用undolog记录的数据来进行回滚

2. 一致性（Consistency）：事务的一致性保证数据库的事务执行前后数据库由一种正确状态转移到另一种正确状态。

   比如事务的执行到一半出现了异常，事务就需要回滚，而不是强行提交导致数据不一致。

3. 隔离性（Isolation）：事务的隔离性保证事务在并发执行时，内部操作互不干扰

   因为如果说，多个事务在同一时间操作同一份数据，就可能导致脏读，可重复读，幻读的问题

   因此，在数据库中定义了四种隔离级别给我们使用，分别是：

   1. read uncommited读未提交
   2. read commited读已提交
   3. repeatable read可重复读
   4. serializable串行

   在innoDB中默认的隔离级别是可重复读，事务的隔离性是通过MySQL的各种锁来实现的

4. 持久性（Durability）：事务的持久性保证事务一旦提交，数据就会持久化到硬盘上或者说，事务提交后对于数据库的改变是永久性的。而事务的持久性是通过redo log日志来保证，当我们修改数据时，MySQL会把数据对应的页加载到内存中对其修改【脏页】，当修改完后会将改变写入到redo log，记录了我们的操作，这样即便MySQL在中途宕机，也可以根据redo log进行恢复；redo log是物理日志，文件体积小，恢复快。

   redo log一部分在内存一部分在磁盘，事务提交时刷新到磁盘

   1. 两阶段提交：为了保证redolog和binlog的数据一致；因此才能使用redolog将数据库恢复到crush前，用binlog实现数据备份、恢复以及主从复制；
   2. 两阶段提交细节：写完 redolog，标记为prepare状态，【并在redolog中记录一个XID】；之后写binlog【也有一个XID】，再将redolog的状态标记为commit；

4 锁

明确：之所以把锁和事务分开是因为锁太多内容

1. 在innoDB引擎之下，按锁的粒度大小可以将锁分为行锁和表锁，行锁实际上是作用于索引之上【取决于是否命中索引】

   当我们SQL命中了索引，会把命中条件的索引节点锁住【行锁】，其他线程无法在通过这个索引找到data；如果没有命中索引，我们锁的就是整张表【表锁】

2. 而行锁又可以分为读锁和写锁，读锁是共享锁，多个事务可以同时读取同一资源，但不允许其他事务修改。写锁是排他锁，会阻塞其他写锁和读锁。

3. 在锁的维度而言其实就是在read uncommit隔离级别下，读不加锁，写加排他锁【读不加锁，排他锁就无法排它了】，但这种情况下，写的时候加锁，而读不加锁，导致读到了脏数据，也就是脏读。【脏读在生产环境下是无法接受的，那我们为了解决脏读就需要使用下一个隔离级别read commit。在read commit中，如果说我们采用读锁解决脏读，并发度会极度下降；因此引出了MVCC多版本并发控制的概念，做到读写不阻塞的同时，避免了脏读】

• innoDB对于MVCC的实现：innoDB为每行数据添加三个隐藏字段和ReadView
  • DB_TRX_ID(6字节)：表示最后一次插入或更新该行的事务ID。此外，delete操作也视为更新，只是会在记录头Record header中的deleted_flag字段将其标记为已删除【组织语言：在记录头用一个删除标记标记其为已删除】
  • DB_ROLL_PTR(7字节)：回滚指针，指向该行的undo log
  • DB_ROW_ID(6字节)：没有设置主键且没有唯一且非空索引，innoDB用此id建立索引

4. 在read commit隔离级别之下，每次select都会生成一个ReadView；而read repeatable隔离级别下，只在事务开始后第一个select语句生成readview【readview中包含当前活跃事务id列表，当前活跃最小id，即将分配的下一个事务id，生成该readview的id】，有了readview和隐藏字段中的trx_id和rollpointer之后，我们在每次访问数据时，通过比较readview中的字段和隐藏字段就可以解决脏读和不可重复读的问题；具体如下：

   1. readview中的创建事务id若和数据的事务id一致，则表示访问自己修改的数据，可以访问该版本数据
   2. readview中的最小活跃事务id如果比数据的事务id大，则表示修改数据的事务已经提交，可以访问该版本数据
   3. readview中的下一个事务id如果小于等于数据的事务，则表示修改数据的事务在当前事务生成readview后才开启，因此不可访问该版本数据
   4. 数据的事务id在readview的活跃列表里，不可访问；否则表示已提交，可以访问
   • 如果说，不可访问该版本数据，则 通过rollpointer指向的undolog取出快照记录，用快照记录中的trxid重新判断，直到找到满足的快照版本或返回空

5. 因此，我们在read commit隔离级别解决了脏读，在read repeatable隔离级别解决了不可重复读；

6. 而在innoDB下的read repeatable，我们通过事务版本控制，实际上已经解决了快照读的幻读问题；而如果是当前读，我们通过行锁和间隙锁合并的Next-key锁解决幻读问题【行锁防止别的事务删除读行，间隙锁防止别的事务增加行】！！！！！！！！！！！重要！！

7. 最后一个隔离级别是serializable，不允许事务的并发，事务与事务之间的执行是串行的，效率最低，却也最安全

   • 快照读：就是select
     • select * from table ….;
   • 当前读：特殊的读操作，插入/更新/删除操作，属于当前读，处理的都是当前的数据，需要加锁。
     • select * from table where ? lock in share mode;
     • select * from table where ? for update;
     • insert;
     • update ;
     • delete;

5 调优

5.1 关于索引的规范和使用

1. 是否能使用覆盖索引，减少回表消耗时间，同时也意味着在select时要指明对应的列，而不是select *
2. 考虑建立联合索引，要把区分度高的放在尽可能靠左，因为最左匹配原则
3. 不要对索引进行计算或者类型转换
4. 开启事务之后尽可能只进行数据库操作，并有意识减少锁占用时间
5. explain

5.2 事务隔离级别相关

1. MySQL默认使用RR，一般互联网公司采用RC，并发度更高，避免间隙锁导致死锁问题，而MySQL之所以使用RR，是因为在MySQL5.1以前，binlog没有row模式，在RC隔离级别下，使用statemnt模式会出现主从不一致的问题，为了兼容以前只有statement故使用RR为默认隔离级别。

5.3 即使走了索引还很慢

1. 可能数据量太大了，如果可以删就删一些旧数据，不过一般不会删

5.4 读写性能瓶颈

1. 考虑架构，如果是单库，考虑升级主从，实现读写分离；主库接收写请求，从库接收读请求，从库的数据由主库发送的binlog进而更新，实现主从的最终一致性！！

补充面试题

1 什么是回表？

1. 当我们使用非聚簇索引查询数据并想要查询除了索引列的其他列时，由于非聚簇索引叶子结点存储的是当前列和主键值，所以还需要根据主键值去聚簇索引树在进行一遍查找，得到想要的列

2. 想要避免回表，可以使用覆盖索引

2 什么是覆盖索引？

1. 我们为想要查询的字段建立联合索引，这样在非聚簇索引时，我们叶子结点就已经存储了所有我们想要的列，这样就不需要进行回表操作了。
2. 而联合索引存在最左匹配原则

3 最左匹配原则？

1. 比如我们为(a,b,c)建立了联合索引，查询条件是where a=1 and b=2 and c = 3，那么我们在查询时，其底层会优先匹配最左边的索引。
2. 而由于索引的最左匹配原则，在某些情况下我们的索引就会失效

4 redolog与binlog

	redo log	bin log
日志类型	物理日志（记录某个数据也上做了什么修改，恢复快）	逻辑日志（即）
存储格式	页	statement：SQL语句；row：行；mixed：混合
用途	重做数据页	数据复制，
所处层级	innoDB存储引擎	存储引擎上层，因此与引擎类型无关