MySQL学习笔记

1. 数据库连接命令：mysql -h$ip-P$port -u$user –p。如果一台服务器上有多个MySQL实例，就需要指定-h ip地址和-P port端口进行连接。
2. MySQL 8.0之后移除了查询缓存功能。
3. MySQL SQL执行流程：客户端-》连接器-》分析器-》优化器-》执行器-》存储引擎。
4. MySQL日志模块：redo log和binlog。
5. redo log：先写日志，再写磁盘。redo log固定大小，日志写到末尾就循环从头开始写。它拥有两个位置：write pos和checkpoint，write pos是当前记录的位置，checkpoint是要擦除的位置，两者之间的距离是还能写日志的空间，如果write pos追上checkpoint，checkpoint就必须向前推进留出空间，同时redo log将推进的这段日志刷磁盘，更新磁盘数据。redo log innodb_flush_log_at_trx_commit设置为1，表示每次事务的 redo log 都直接持久化到磁盘，这样可以保证MySQL异常重启之后数据不丢失。
6. 有了redo log，InnoDB（redo log属于InnoDB引擎）就可以保证数据库即使异常重启，之前提交的记录也不会丢失，这个能力叫做crash-safe。
7. MySQL server层日志：binlog（归档日志）。binlog是追加写的，文件写到一定大小后会切换写下一个，不会覆盖之前的日子。binlog可以用来恢复数据库。binlog sync_binlog参数设置为1，表示每次事务的 binlog 都持久化到磁盘，这样可以保证MySQL异常重启binlog不丢失。
8. MySQL两阶段提交：事务提交时写入redo log，处于prepare（准备）状态；接着写binlog，写完binlog后提交事务，将redo log中该事务的状态改为commit。两阶段提交是为了使数据库状态与日志恢复出来的库的状态保持一致，也就是保持主从一致。
9. MySQL是一个支持多引擎的系统，原生引擎是MyISAM，它不支持事务。这也是MyISAM被InnoDB引擎取代的原因之一。
10. MySQL事物隔离级别：读未提交、读已提交、可重复读、串行化。读未提交：一个事务还未提交时，它做的变更就能被其它事务看到。读已提交：一个事务提交后，它做的变更才能被其它事务看到。可重复读：一个事务执行过程中看到的数据，总是跟这个事务启动时看到的数据一致（通过一致性视图实现）。串行化：对同一行记录，读会加读锁，写会加写锁，如果出现读写锁冲突，后访问的事务必须等到前一个事务执行完成才能访问。
11. MySQL事务默认隔离级别：可重复读，Oracle事务默认隔离级别：读提交。MySQL通过命令：show variables like ‘transaction_isolation’;查看当前事物隔离级别。
12. 尽量不要使用长事务，长事务意味着系统会存在很老的事务视图，带来大量回滚记录，占用内存空间。还会占用锁资源，可能拖垮整个库。
13. 事务启动方式：
    1.begin或start transaction，提交语句：commit，回滚语句：rollback；
    2.设置set autocommit=1，开启事务自动提交。
14. 可以在information_schema 库的 innodb_trx 这个表中查询长事务，查找持续时间超过60s的事务例句：select * from information_schema.innodb_trx where TIME_TO_SEC(timediff(now(),trx_started))>60;
15. binlog备份一周一备和一天一备的区别：最长恢复时间区别，一天一备只需要应用一天的binlog，一周一备需要应用一周的binlog。还有存储空间的消耗，备份频繁需要消耗更多的存储空间。
16. 常见索引模型：哈希表、有序数组和搜索树。

1. 哈希表，以键-值（key-value）形式存储数据的结构。通过输入的key可以快速找到value。哈希思路是将值放入数组，用一个哈希函数把key换算成一个确定的位置，然后把值（value）放在数组的这个位置。不可避免的，多个key经过哈希计算，会出现同一个值的情况。处理这种情况就是在对应位置拉出一个链表存放这些key对应的value。哈希表无序，因此只能适用于等值查询的场景，比如Memcached及其它一些NoSQL引擎。
2. 有序数组在等值查询和范围查询场景中的性能就都非常优秀。但是如果更新数据成本会很大，为了保证数据有序，往中间插入一个记录就必须得挪动后面所有的记录。所以有序数组适用于静态存储引擎，比如一个城市某一年的人口信息，这类不会被修改的数据。
3. 搜索树有二叉树和N叉树，其中二叉树搜索效率最高，但数据库为了减少磁盘访问次数，使用N叉树。因为索引不止存在内存中，还要写在磁盘上。同样大小数据，使用二叉树比使用N叉树深度更深，意味着更多的磁盘访问次数，降低了查询效率。
   每个索引模型都有各自的适用场景，碰到一个新数据库时，先分析它的数据模型，才能从理论上分析它的适用场景。

17. InnoDB索引模型:B+树索引。索引分为主键索引（聚簇索引）和非主键索引（二级索引）。主键索引叶子节点存放的是整行数据，非主键索引存放的是主键的值。通过二级索引查询数据时，需要先查到主键的值，再通过主键索引查找到数据。这个过程叫回表。
18. B+树索引具有有序性。自增主键语句：NOT NULL PRIMARY KEY AUTO_INCREMENT。主键长度越小，普通索引的叶子节点就越小，普通索引占用的空间也就越小（普通索引的叶子节点存放主键的值）。
19. 覆盖索引：如果索引已经覆盖了我们的查询需求，不需要再回表了，我们称为覆盖索引。比如一个市民信息表，我们将姓名和身份证设为联合索引，如果有通过身份证查询姓名的需求，这个索引就可以满足查询需求，不用回表。
20. B+树最左前缀原则：例如存在联合索引，身份证和姓名。查询条件只有身份证时也会使用上这个联合索引。不只是索引的全部定义，只要满足最左前缀，就可以利用索引来加速检索。这个最左前缀可以是联合索引的最左 N 个字段，也可以是字符串索引的最左 M 个字符。索引下推：通过索引下推排除掉不符合条件的二级索引，减少回表次数，增加查询效率。
21. 数据库设计和使用原则：尽量少的访问资源，减少资源消耗。
22. 索引可能因为删除，或者页分裂导致数据页有空洞，重建索引会创建一个新的索引，把数据顺序插入，这样索引页利用率最高，索引更紧凑、省空间。
23. MySQL锁大致分为全局锁、表级锁和行锁。

1. 全局锁：对整个数据库实例加锁。加锁命令：Flush tables with read lock;使用命令后这个库处于只读状态，数据更新语句（增删改）和数据定义语句（建表、修改表结构）等事务会被阻塞。全局锁的使用场景：做全库逻辑备份。
   官方自带的逻辑备份工具是 mysqldump。当 mysqldump 使用参数–single-transaction 的时候，导数据之前就会启动一个事务，来确保拿到一致性视图。而由于 MVCC 的支持，这个过程中数据是可以正常更新的。
2. 表级锁：表锁，元数据锁（MDL）。
   表锁：表锁的语法是 lock tables … read/write，可以用unlock tables主动释放锁，也可以在客户端断开的时候自动释放。
   元数据锁：MDL不需要显式使用，在访问一个表的时候会被自动加上。对一个表做增删改查操作时，加MDL读锁。要对表做结构变更时，加MDL写锁。读锁之间不互斥，读写锁、写锁之间互斥。
   热点表不得不更新数据结构时，可以在alter table语句设定等待时间，如果在指定时间能拿到MDL写锁最好，如果拿不到也不阻塞后面的业务语句，先放弃。之后开发或者DBA再重试这个过程。
3. 行锁：InnoDB支持行锁，MyISAM不支持行锁。
   在 InnoDB 事务中，行锁是在需要的时候才加上的，但并不是不需要了就立刻释放，而是要等到事务结束时才释放。这个就是两阶段锁协议。如果事务中需要锁多个行，要把最可能造成锁冲突、最可能影响并发度的锁往后放。
   死锁：当并发线程出现循环资源依赖，涉及的线程都在等待别的线程释放资源时，就会导致这几个线程进入无限等待的状态。
   出现死锁以后的策略：1.直接进入等待，直到超时。这个超时时间可以通过参数 innodb_lock_wait_timeout 来设置。2. 发起死锁检测，发现死锁后，主动回滚死锁链条中的某一个事务，让其他事务得以继续执行。将参数 innodb_deadlock_detect 设置为 on，表示开启这个逻辑。控制死锁的主要方向，就是控制访问相同资源的并发事务量。

24. InnoDB 利用了“所有数据都有多个版本”的这个特性，实现了“秒级创建快照”的能力。可重复读事物隔离级别下，一个数据版本，对于一个事务视图来说，除了自己的更新总是可见以外，有三种情况：1.版本未提交，不可见；2.版本已提交，但是是在视图创建后提交的，不可见；3.版本已提交，而且是在视图创建之前提交的，可见。
25. 更新数据都是先读后写的，而这个读，只能读当前的值，称为“当前读”（current read）。