1.mysql执行计划
可以使用explain+sql语句来模拟优化器执行sql查询语句，从而知道mysql是如何处理sql语句的。
官网地址：MySQL :: MySQL 8.0 Reference Manual :: 8.8.2 EXPLAIN Output Format
(1)执行计划中包含的信息
EXPLAIN Output Columns
| Column | JSON Name | Meaning |
|---|---|---|
| id | select_id | The SELECT identifier |
| select_type | None | The SELECT type |
| table | table_name | The table for the output row |
| partitions | partitions | The matching partitions |
| type | access_type | The join type |
| possible_keys | possible_keys | The possible indexes to choose |
| key | key | The index actually chosen |
| key_len | key_length | The length of the chosen key |
| ref | ref | The columns compared to the index |
| rows | rows | Estimate of rows to be examined |
| filtered | filtered | Percentage of rows filtered by table condition |
| Extra | None | Additional information |
id：select查询的序列号，包含一组数字，表示查询中执行select子句或者操作表的顺序。
select_type：用来分辨查询的类型，是普通查询还是联合查询还是子查询。
table：对应行正在访问哪一张表，表名或者别名，可能是临时表或者union合并结果集。
type：显示的是访问类型，访问类型表示以何种方式访问我们的数据，最容易想到的是全表扫描，直接暴力的遍历一张表去寻找需要的数据，效率非常低下，访问的类型有很多，效率从最好到最坏依次为：
system->const->eq_ref->ref->fulltext->ref_or_null->index_merge->unique_subquery->index_subquery->range->index->All
一般情况下，得保证查询至少达到range级别，最好能达到ref。
possible_keys：显示可能应用在这张表中的索引，一个或多个，查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询实际使用。
key：实际使用的索引，如果为null，则没有使用索引，查询中若使用了覆盖索引，则该索引和查询的select字段重叠。
key_len：表示索引中使用的字节数，可以通过key_len计算查询中使用的索引长度，在不损失精度的情况下长度越短越好。
ref：显示索引的哪一列被使用了。
rows：根据表的统计信息及索引使用情况，大致估算出找出所需记录需要读取的行数，此参数很重要，直接反应sql找了多少数据。
extra：包含额外的信息。
2.B+Tree结构
（1）B+Tree每个节点可以包含更多的节点，做这个的原因有两个，第一个原因是为了降低树的高度，第二个是将数据范围变为多个区间，区间越多，数据检索越快。
（2）非叶子节点存储key，叶子节点存储key和数据。
（3）叶子节点两两指针相互连接（符合磁盘的预读属性），顺序查询性能更高。
3.聚簇索引、非聚簇索引
（1）数据跟索引存储在一起的叫聚簇索引，没有存储在一起的叫非聚簇索引
（2）innodb存储引擎在进行数据插入的时候，数据必须要跟某一个索引列存储在一起，这个索引列可以是主键，如果没有主键，选择唯一键，如果没有唯一键，选择6字节的rowid来进行存储。
（3）数据必定是跟某一个索引绑定在一起的，绑定数据的索引叫做聚簇索引，其它索引的叶子节点中存储的数据不再是整行的记录，而是聚簇索引的ID值。
（4）innodb中既有聚餐索引也有非聚簇索引，myisam中只有非聚餐索引；innodb生成的文件包含.frm(表结构框架信息）、.ibd(索引和数据）；myisam中包含.frm(表结构和框架信息）、.MYI(索引）、.MYD(数据）。
4.索引相关其它概念
（1）回表
某个表字段：id、name、age；id主键，name配置索引；select * from table where name='zhangsaan';sql执行过程：先根据name索引B+树匹配到对应的叶子节点，查询到对应行记录的id值，再根据id去B+树中检索整行记录，这个过程就称之为回表，要尽量避免回表操作。
（2）索引覆盖
某个表字段：id、name、age；id主键，name配置索引；select id,name from table where name='zhangsaan';sql执行过程：先根据name索引B+树匹配到对应的叶子节点，能获取到id的属性值，索引的叶子节点中包含了查询的所有列，此时不需要进行回表，这个过程叫做索引覆盖。
（3）最左匹配
创建索引的时候，可以选择多个字段共同组成索引，此时叫做组合索引或者联合索引，查询的时候需要遵循最左原则；例一name+age作为组合索引，此时索引中name是有序的，age是无序的。查询的时候where后面跟的条件顺序name、age走索引；age、name也走索引，mysql有优化器；只有name也是走索引的；只有age不走索引。
（4）索引下推
select * from table where name='zhangsan' and age=12;
没有索引下推之前：先根据name从存储引擎中拉取数据到server层，再在server层中进行age数据的过滤。
有索引下推之后：根据name和age两个条件从存储引擎中拉取数据到server层。
5.MVCC多版本并发控制
（1）当前读、快照读
当前读：读取的是数据的最新版本，总是读取到最新的数据。执行的语句包含：
select ... lock in share mode
select ...for update
update
delete
insert
快照读：读取的是历史版本的记录。执行的语句包含
select...
（2）事务隔离级别
①读未提交
②读已提交RC
③可重复读RR（mysql默认隔离级别）
④串行化
能否读取到B事务修改的记录？
RC：可以读取到最新的结果值记录
RR：不可以读取到最新的结果值记录
（3）MVCC实现，包含的部分
①第一部分：隐藏字段
数据的每一行记录都会包含三个用户不可见的字段：
DB_TRX_ID：创建或者最后一次修改改记录的事务id；
DB_ROW_ID：隐藏主键id；
DB_ROLL_PTR：回滚指针，指向的是回滚日志undolog。
②第二部分：undolog回滚日志
当不同的事务对同一条记录进行修改时，该记录的undolog会形成一个线性表，即链表，链首是最新的历史记录，链尾是最早的历史记录。
③第三部分：readview事务在进行快照读的时候产生的读视图，包含：
trx_list：系统活跃的事务id集合；
up_limit_id：活跃事务id集合中最小的id；
low_limit_id：系统尚未分配的下一个事务id。
（4）可见性分析算法
①首先比较DB_TRX_ID<up_limit_id，若果小于，则当前事务能看到DB_TRX_ID所在的记录，如果大于进入下一个判断；
②判断DB_TRX_ID>=low_limit_id，若是大于等于，则代表当前DB_TRX_ID所在的记录，在Read view生成之后才产生，所以当前事务不可见，如果小于则进行下一个判断；
③判断DB_TRX_ID是否在活跃事务集合trx_list中，若果在，则表示在生成read view的时候，此事务还没有commit提交，则此事务修改的东西，当前事务是看不到的；若是不在，则表示在生成read view的时候，此事务已经commit，则修改的结果是能看到的。
上图的这种方式，经过可见性算法，可以得出结果，可以看到修改后的记录。
（5）不同的隔离级别生成快照的时机不同
RC：每次在进行快照读的时候都会生成新的快照，所以其它事务提交的记录，在当前事务中是可以读取到的；
RR：只有在第一次进行快照读的时候才会生成readview，之后的读操作都会使用第一次的readview，其它事务对某条记录进行了修改，记录的DB_TRX_ID为修改的事务id，此修改的事务id在当前事务的快照中还是活跃状态，所以读取不到。
（5）ACID保障
为了保证数据的一致性，可以先将数据通过顺序读写的方式写到日志文件中，然后再将数据写入到对应的磁盘文件中，只要日志文件保存成功，数据就不会丢失，可以根据日志来进行数据的恢复。
（6）binlog、redolog日志
binlog：mysql自带的日志文件；
redolog：innodb插件引擎带的日志文件，支持磁盘顺序写日志，性能大大提供。
因为两种日志属于不同的组件，所以为了保证数据的一致性，要保证binlog和redolog一致，所以有了二阶段提交的概念。
（7）二级段提交redolog
（8）同一个事务里面产生幻读的情况
当第一次执行select...时，会生成当前的快照记录，然后执行insert、update、delete、select ...for update语句后，此时的readview会失效，再执行select...时，会进行当前读，读取到最新的readview。
（9）长事务的影响
并发情况下，数据库连接池容易被撑爆；
锁定太多的数据，造成大量的阻塞和锁超时；
执行时间长，容易造成主从延迟；
回滚所需要的时间比较长；
undo log膨胀；
容易导致死锁。
6.锁相关
（1）从是否独占分为：读锁、写锁、意向锁
读锁（共享锁，S锁）：select ... lock in share mode;读锁是共享的，多个事务可以同时读取同一个资源，但不允许其他事务修改。
写锁（排它锁，X锁）;select ... for update;写锁是排他的，会阻塞其他的写锁和读锁，update、insert、delete都会加写锁。
意向锁（相当于表层面的全局变量）：分为意向共享锁、意向排他锁，当事务对某行加锁时，会在粒度更高的表锁上也加上意向共享锁或者意向排他锁，这样在其它事务需要获取表锁的时候，只有获取表的意向锁即可得到是否能获取到表锁；若是当前表为意向排他锁，则其它事务来获取表锁时，不用再去逐行判断是否有加排他锁，大大提升了获取表锁的效率。
（2）从锁粒度分为：表锁、行锁、记录锁、间隙锁、临键锁
表锁：上锁锁住的是整个表，当下一个事务访问该表时，必须等前一个事务释放了锁后才能进行对表的访问；特点：粒度大，加锁简单，容易冲突。
行锁：上锁锁住的是某一行或者多行记录，其它事务访问同一张表时，只有被锁住的记录不能访问，其它的记录可正常访问；特点：粒度小，加锁比表锁麻烦，不容易冲突，相比表锁支持的并发要高。
记录锁：记录锁也属于行锁的一种，只不过记录锁的范围只是表中的一行记录。
间隙锁：间隙锁是行锁的一种，间隙锁是在事务加锁后锁住的是记录的某一个区间，当表的相邻id之间出现空隙则会形成一个区间，遵循左开右闭原则，在innodb的可重复读隔离级别下，解决幻读问题产生的。
临键锁：临键锁也是行锁的一种，并且它是innodb的行锁默认算法，总结来说它就是记录锁和间隙锁的组合，临键锁会把查询出来的记录锁住，同时也会把该范围查询内的所有间隙空间也会锁住。
（3）从性能分为：悲观锁、乐观锁
悲观锁：就是对数据的处理持悲观态度，总认为会发生并发冲突，获取和修改数据时，别人会修改数据，所以在整个数据处理过程中，需要将数据锁定。悲观锁的实现，通常依靠数据库提供的锁机制实现，比如mysql的排他锁，select ... for update来实现悲观锁。
乐观锁：对数据的处理持乐观态度，乐观的认为数据一般情况下不会发生冲突，只有提交数据更新时，才会对数据是否冲突进行检测。如果发现冲突了，则返回错误信息给用户，让用户自己决定如何操作。乐观锁不会刻意使用数据库本身的锁机制，而是依据数据本身来保证数据的准确性。乐观锁的实现：
①CAS（Compare and Swap）：比较和替换，在进行数据修改的时候，先比较之前获取的值跟当前的值是否一样，一样则刻意执行更新，不一样怎会导致cas失败，失败之后，进入一个无限循环while（true），再次获取值，接着执行cas操作，此种自旋的方式，会浪费一定的服务器性能
②版本号控制：数据表中加上一个版本号version字段，表示数据被修改的次数，当数据被修改时，version值会+1。当线程A要修改数据时，在读取数据的同时也会读取到version值，在提交更新时，若是刚才读取到的version值与当前数据库中的version值相等时才更新，否则重试更新操作，直到更新成功。
之所以会使用版本号控制是因为，cas的方式会存在ABA 问题，即某个记录的值，两个线程都加载到工作空间后，经过某个线程更新为新的值，然后再把值更新还原回去，而第二个线程进行更新时，比较值时发现还是等于一开始的值，虽然值没有变，但是已经不是最开始的版本了；使用版本号控制即可解决此问题。
7.分库分表
数据库和表的拆分都可以分为垂直拆分、水平拆分。
（1）垂直拆分
垂直分表示意图
特点：
①每个库（表）的结构都不一样；
②垂直拆分的每个库（表）的数据都（至少有一列）一样；
③每个库（表）的并集是全量数据。
优点：
①拆分后业务清晰（专库专用按业务拆分）；
②数据维护简单，按业务不同放到不同的机器上。
缺点：
①如果单表的数据量大，写读压力大；
②部分业务无法关联join，因为需要关联的表放在不同的库下（ip不同），只能通过程序接口去调用，提高了开发复杂度。
（2）水平拆分
水平分表示意图
特点：
①每个库（表）的结构都一样；
②每个库（表）的数据都不一样；
③每个库（表）的并集是全量数据。
优点：
①单库（表）的数据保持在一定的量（减少），有助于性能提高；
②提供了系统的稳定性和负载能力，不同的记录存在不同的库（表），若是有部分服务器挂了，但是正常的服务器还是能提供能力的；
③拆分的表结构相同，程序改造较少。
缺点：
①数据的库容很有难度，维护量大；
②拆分规则很难抽象出来；
③分片事务的一致性问题，部分业务无法关联join，只能通过程序接口去调用；例如用户订单这个功能，可以按用户id来分表，方便用户查询我的订单，但是若是想看某个商品的销售去向，则需要遍历所有的订单表来获取值。
（3）分库分表带来的问题
①分布式事务，acid
②跨库join查询
③分布式全局唯一ID
④开发成本高，对程序员要求高
8.mysql如何数据同步
（1）主从模式
主从可以保证读写分离，主节点master负责写，从节点slave负责读。主节点需要开启binlog，并设置一个全局唯一的server-id，从节点也设置此server-id，binlog记录了master上所有的操作，会被复制到从节点的relaylog中继日志。主从模型需要保证安装的mysql版本一致，因为主从之间复制数据可能会有数据的短暂不一致。
（2）集群模式
集群模式可以实时同步数据，每次写操作，都会在所有节点之间同步，数据节点之间采用同步复制来保证数据一致性，使用两阶段提交协议，master执行提交语句时，事务会发送到slave，slave开始准备事务提交，都想master发送OK(或ABORT)消息，表明事务已经准备好，master节点收到所有slave发送的OK(或ABORT)，再向所有的slave发送OK（或ABORT）,从节点根据master发送的OK来提交事务，或ABORT终止事务，再向master节点发送OK(ABORT)，同步复制需要4此消息传递。
337
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
