一旦事务提交，则其所做的修改就会永久保存到数据库中。即使系统发生崩溃，事务执行的结果也不能丢失。使用重做日志来保证持久性。
2.请分别举例说明幻读和不可重复读、并描述一下它们之间的区别。
脏读（读取未提交数据）
不可重复读（前后多次读取，数据内容不一致）
幻读（前后多次读取，数据总量不一致）
3.MySQL 的默认隔离级别是什么
4.事务覆盖问题
示例
上面的两种情况就是对于一条数据，多个事务同时操作可能会产生的问题，会出现某个事务的操作被覆盖而导致数据丢失。
解决方案
1.LBCC 解决数据丢失
2.MVCC 解决数据丢失
5.MVCC
概念
MVCC全称Multiple Version Concurrency Control，也就是多版本并发控制，重点在多版本，简单来说，它为每个事务生成了一个快照，保证每个事务只能读到自己的快照数据，不论其他事务如何更新一条记录，这个事务所读到的数据都不会产生变化，也就是说，会为一条记录保留多个版本，多个事务读到的版本不同，MVCC代替了读锁，实现了读-写不阻塞。
举例
使用版本来控制并发情况下的数据问题，在B事务开始修改账户且事务未提交时，当A事务需要读取账户余额时，此时会读取到B事务修改操作之前的账户余额的副本数据，但是如果A事务需要修改账户余额数据就必须要等待B事务提交事务。
原理
MVCC使得数据库读不会对数据加锁，普通的SELECT请求不会加锁，提高了数据库的并发处理能力。借助MVCC，数据库可以实现READ COMMITTED，REPEATABLE READ等隔离级别，用户可以查看当前数据的前一个或者前几个历史版本，保证了ACID中的I特性（隔离性)。
InnoDB的MVCC实现逻辑
版本链
Read View
注意
MVCC只在REPEATABLE READ和READ COMMITIED两个隔离级别下工作。其他两个隔离级别都和 MVCC不兼容 ，因为READ UNCOMMITIED总是读取最新的数据行，而不是符合当前事务版本的数据行。而SERIALIZABLE则会对所有读取的行都加锁。
1.前置知识
1.1Mysql的体系结构
连接层
服务层
1.Management Serveices & Utilities
2.SQL Interface: SQL接口
3.Parser: 解析器
4.Optimizer: 查询优化器
5.Cache和Buffer： 查询缓存
引擎层
存储层
1.2Mysql的查询流程
2.存储引擎概念
3.各种存储引擎特点对比
4.常见存储引擎
InnoDB
概述
InnoDB存储引擎是Mysql的默认存储引擎。InnoDB存储引擎提供了具有提交、回滚、崩溃恢复能力的事务安全。但是对比MyISAM的存储引擎，InnoDB写的处理效率差一些，并且会占用更多的磁盘空间以保留数据和索引。
存储方式
内存架构
InnoDB In-Memory Structures（内存结构）
Buffer Pool(缓冲池)
1.MySQL 不会直接去修改磁盘的数据，因为这样做太慢了，MySQL 会先改内存，然后记录 redo log，等有空了再刷磁盘，如果内存里没有数据，就去磁盘 load。
而这些数据存放的地方，就是 Buffer Pool。
2.我们平时开发时，会用 redis 来做缓存，缓解数据库压力，其实 MySQL 自己也做了一层类似缓存的东西。
3.MySQL 是以「页」（page）为单位从磁盘读取数据的，Buffer Pool 里的数据也是如此，实际上，Buffer Pool 是a linked list of pages，一个以页为元素的链表。
4.Buffer Pool 采用基于 LRU（least recently used） 的算法来管理内存
Change Buffer(写缓冲)
1.上面提到过，如果内存里没有对应「页」的数据，MySQL 就会去把数据从磁盘里 load 出来，如果每次需要的「页」都不同，或者不是相邻的「页」，那么每次 MySQL 都要去 load，这样就很慢了。
2.于是如果 MySQL 发现你要修改的页，不在内存里，就把你要对页的修改，先记到一个叫 Change Buffer 的地方，同时记录 redo log，然后再慢慢把数据 load 到内存，load 过来后，再把 Change Buffer 里记录的修改，应用到内存（Buffer Pool）中，这个动作叫做 merge；而把内存数据刷到磁盘的动作，叫 purge
Adaptive Hash Index
1.我们每次从辅助索引查询到对应记录的主键,然后还要用主键作为search key去搜索主键B+tree才能找到记录.
当这种搜索变多了,innoDB引擎会进行优化.
2.维护索引叶页面中所有记录的索引键值(或键值前缀)到索引叶页面位置的Hash映射关系,
能够根据索引键值(前缀)快速定位到叶页面满足条件记录的Offset，减少了B+树Search Path的代价，将B+树从Root页面至Leaf页面的路径定位，优化为Hash Index的快速查询。
Log Buffer（日志缓冲区）
redo log buffer是一块内存区域，这块区域持有将要写入redo log的数据。
Operating System Cache
不属于 InnoDB 的能力，而是操作系统为了提升性能，在磁盘前面加的一层高速缓存
InnoDB On-Disk Structures（磁盘结构）
表空间（Tablespaces）
我们平时创建的表的数据，可以存放到 The System Tablespace 、File-Per-Table Tablespaces、General Tablespace 三者中的任意一个地方，具体取决于你的配置和创建表时的 sql 语句。
Doublewrite Buffer
1.Doublewrite Buffer 就是保证数据页的可靠性
2.假设在某一次从内存刷新到磁盘的过程中，一个「页」刷了一半，突然操作系统或者 MySQL 进程奔溃了，这时候，内存里的页数据被清除了，而磁盘里的页数据，刷了一半，处于一个中间状态，不尴不尬，可以说是一个「不完整」，甚至是「坏掉的」的页。
3.MySQL 在刷数据到磁盘之前，要先把数据写到另外一个地方，也就是 Doublewrite Buffer，写完后，再开始写磁盘。Doublewrite Buffer 可以理解为是一个备份（recovery），万一真的发生 crash，就可以利用 Doublewrite Buffer 来修复磁盘里的数据。
MyISAM
概述
MyISAM 不支持事务、也不支持外键，其优势是访问的速度快，对事务的完整性没有要求或者以SELECT、INSERT为主的应用基本上都可以使用这个引擎来创建表。
存储方式
MEMORY
MERGE
5.InnoDB、MyISAM存储引擎对比
6.存储引擎的选择
1.索引的本质
索引的本质是一种排好序的数据结构。
2.为什么要使用索引
1.通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性。
2.可以大大加快数据的检索速度，这也是创建索引的最主要的原因。
3.可以加速表和表之间的连接，特别是在实现数据的参考完整性方面特别有意义。
4.在使用分组和排序子句进行数据检索时，同样可以显著减少查询中分组和排序的时间。
5.通过使用索引，可以在查询的过程中，使用优化隐藏器，提高系统的性能。
3.索引这么多优点，为什么不对表中每个字段都创建索引呢
1.创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增加。
2.索引需要占物理空间，除了数据表占数据空间之外，每一个索引还要占一定的物理空间，如果要建立聚簇索引，那么需要的空间就会更大。
3.当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，这样就降低了数据的维护速度。
4.索引是如何提升查询速度的
MySQL的数据是存储在磁盘文件中的，查询处理数据时，需要先把磁盘中的数据加载到内存中，磁盘IO 操作非常耗时，所以我们优化的重点就是尽量减少磁盘 IO 操作。访问二叉树的每个节点就会发生一次IO，如果想要减少磁盘IO操作，就需要尽量降低树的高度。
局部性原理与磁盘预读
1.由于存储介质的特性，磁盘本身存取就比主存慢很多，再加上机械运动耗费，磁盘的存取速度往往是主存的几百分分之一，因此为了提高效率，要尽量减少磁盘 I/O。为了达到这个目的，磁盘往往不是严格按需读取，而是每次都会预读，即使只需要一个字节，磁盘也会从这个位置开始，顺序向后读取一定长度的数据放入内存。这样做的理论依据是计算机科学中著名的局部性原理：当一个数据被用到时，其附近的数据也通常会马上被使用。程序运行期间所需要的数据通常比较集中。
2.由于磁盘顺序读取的效率很高（不需要寻道时间，只需很少的旋转时间），因此对于具有局部性的程序来说，预读可以提高 I/O 效率。
3.预读的长度一般为页（page）的整倍数。页是计算机管理存储器的逻辑块，硬件及操作系统往往将主存和磁盘存储区分割为连续的大小相等的块，每个存储块称为一页（在许多操作系统中，页得大小通常为 4k），主存和磁盘以页为单位交换数据。当程序要读取的数据不在主存中时，会触发一个缺页异常，此时系统会向磁盘发出读盘信号，磁盘会找到数据的起始位置并向后连续读取一页或几页载入内存中，然后异常返回，程序继续运行。
5.常见索引
数据结构知识点补充
1.hash表
哈希表的特点是可以快速的精确查询，但是不支持范围查询
例如
select * from sanguo where name>‘鸡蛋’
这种查询哈希表是无能为力的
适用场景
等值查询的场景，就只有KV（Key，Value）的情况，例如Redis、Memcached等这些NoSQL的中间件。
2.有序数组
有序数组在等值查询的和范围查询时都不错
但是适合静态数据，因为如果我们新增、删除、修改数据的时候就会改变他的结构，成本比较高。
适用场景
可以用来做静态存储引擎，保存静态数据，例如你2019年的支付宝账单，2019年的淘宝购物记录等等都是很合适的，都是不会变动的历史数据。
3.平衡二叉树
有序，支持范围查询。
缺点：如果数据多了，树高会很高，查询的成本就会随着树高的增加而增加。
4.B树
同样的元素，B树的表示要比完全平衡二叉树要“矮”，原因在于B树中的一个节点可以存储多个元素。
5.B+树和B树相比的优势
B+树是B树的升级版，只是把非叶子节点冗余一下，这么做的好处是为了提高范围查找的效率。
提高了的原因也无非是会有指针指向下一个节点的叶子节点。
到这里可以总结出来，Mysql选用B+树这种数据结构作为索引，可以提高查询索引时的磁盘IO效率，并且可以提高范围查询的效率，并且B+树里的元素也是有序的。
6.B+树的一些细节
B+树中一个节点为一页或页的倍数最为合适。
Mysql的基本存储结构是页(记录都存在页里边)
举例
回表的概念
覆盖索引的概念
最左匹配原则
联合索引的概念
索引可以简单如一个列 (a)，也可以复杂如多个列 (a,b,c,d)，即联合索引。
1.如果是联合索引，那么key也由多个列组成，同时，索引只能用于查找key是否存在（相等），遇到范围查询 (>、<、between、like左匹配)等就不能进一步匹配了，后续退化为线性查找。
2.因此，列的排列顺序决定了可命中索引的列数。
使用索引注意事项
6.索引的类型
1.主键索引
索引列中的值必须是唯一的，不允许有空值。
2.普通索引
MySQL中基本索引类型，没有什么限制，允许在定义索引的列中插入重复值和空值。
3.唯一索引
索引列中的值必须是唯一的，但是允许为空值。
4.全文索引
只能在文本类型CHAR,VARCHAR,TEXT类型字段上创建全文索引。字段长度比较大时，如果创建普通索引，在进行like模糊查询时效率比较低，这时可以创建全文索引。MyISAM和InnoDB中都可以使用全文索引。
5.空间索引
MySQL在5.7之后的版本支持了空间索引，而且支持OpenGIS几何数据模型。MySQL在空间索引这方面遵循OpenGIS几何数据模型规则。
6.前缀索引
在文本类型如CHAR,VARCHAR,TEXT类列上创建索引时，可以指定索引列的长度，但是数值类型不能指定。
B树和B+树的主要区别
B树：非叶子节点和叶子节点都会存储数据。
B+树：只有叶子节点才会存储数据，非叶子节点至存储键值。叶子节点之间使用双向指针连接，最底层的叶子节点形成了一个双向有序链表。
MyIsam索引
MyISAM的数据文件和索引文件是分开存储的。MyISAM使用B+树构建索引树时，叶子节点中存储的键值为索引列的值，数据为索引所在行的磁盘地址。
辅助索引
InnoDB索引
主键索引（聚簇索引）
每个InnoDB表都有一个聚簇索引 ，聚簇索引使用B+树构建，叶子节点存储的数据是整行记录。
辅助索引
除聚簇索引之外的所有索引都称为辅助索引，InnoDB的辅助索引只会存储主键值而非磁盘地址。
辅助索引查询过程
根据在辅助索引树中获取的主键id，到主键索引树检索数据的过程称为回表查询。
组合索引
数据扫描方式
全表扫描
索引扫描
7.索引优化方式
1.分页查询优化
业务要根据时间范围查询交易记录，接口原始的SQL如下：
select * from trade_info where status = 0 and create_time >= ‘2020-10-01 00:00:00’ and create_time <= ‘2020-10-07 23:59:59’ order by id desc limit 102120, 20;
select * from trade_info a ,
(select id from trade_info where status = 0 and create_time >= ‘2020-10-01 00:00:00’ and create_time <= ‘2020-10-07 23:59:59’ order by id desc limit 102120, 20) as b //这一步走的是索引覆盖扫描，不需要回表
where a.id = b.id;
2.分而治之
营销系统有一批过期的优惠卷要失效，核心SQL如下：
– 需要更新的数据量500w
update coupons set status = 1 where status =0 and create_time >= ‘2020-10-01 00:00:00’ and create_time <= ‘2020-10-07 23:59:59’;
mysql> explain select min(id) min_id, max(id) max_id from coupons where status =0 and create_time >= ‘2020-10-01 00:00:00’ and create_time <= ‘2020-10-07 23:59:59’;
±—±------------±------±-----------±------±-----------------------±-----------------------±--------±–
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
±—±------------±------±-----------±------±-----------------------±-----------------------±--------±–
| 1 | SIMPLE | users | NULL | range | idx_status_create_time | idx_status_create_time | 6 | NULL | 180300 | 100.00 | Using where; Using index |
current_id = min_id;
for current_id < max_id do
update coupons set status = 1 where id >=current_id and id <= current_id + 1000; //通过主键id更新1000条很快
commit;
current_id += 1000;
done
8.索引失效的几种情况
1.查询条件包含or，可能导致索引失效
2.like通配符可能导致索引失效
3.联合索引，查询时的条件列不是联合索引中的第一个列，索引失效
4.对索引列运算（如，+、-、*、/），索引失效
5.索引字段上使用（！= 或者 < >，not in）时，可能会导致索引失效
6.mysql估计使用全表扫描要比使用索引快,则不使用索引
1.锁的概念
2.锁的分类
3.数据库中的乐观锁和悲观锁
悲观锁的概念
乐观锁的概念
乐观锁和悲观锁的使用场景
乐观锁的实现方式
1.版本号
2.时间戳
4.InnoDB存储引擎锁的算法
1.redo log
概念
记录方式
使用场景
用于系统奔溃恢复(crash-safe)
2.bin log
概念
刷盘机制
使用场景
3.undo log
概念
使用场景
MVCC多版本控制中使用undo log
4.bin log/redo log/undo log区别
1.整体优化思路
2.show status
show status 命令用于查询 Mysql 状态变量相关统计信息，主要关注查询次数、线程连接数和线程运行数。
3.获取需要优化的 SQL 语句
方式一：查看运行的线程
执行命令：
show processlist
mysql> show processlist;
±—±-----±----------±-----±--------±-----±---------±-----------------+
| Id | User | Host | db | Command | Time | State | Info |
±—±-----±----------±-----±--------±-----±---------±-----------------+
| 9 | root | localhost | test | Query | 0 | starting | show processlist |
±—±-----±----------±-----±--------±-----±---------±-----------------+
1 row in set (0.00 sec)
返回的 State 的值是我们判断性能好坏的关键
例如出现以下值，则该行记录的 SQL 语句需要优化
Converting HEAP to MyISAM # 查询结果太大时，把结果放到磁盘，严重
Create tmp table #创建临时表，严重
Copying to tmp table on disk #把内存临时表复制到磁盘，严重
locked #被其他查询锁住，严重
loggin slow query #记录慢查询
Sorting result #排序
方式二：开启慢查询日志
4.explain相关问题
当我们在查询前能否预先估计查询究竟要涉及多少行、使用哪些索引、运行时间呢？答案是能的，mysql提供了相应的功能和语法来实现该功能。
是什么
1.Explain被称为执行计划，在语句之前增加 explain 关键字，MySQL 会在查询上设置一个标记，模拟MySQL优化器来执行SQL语句，执行查询时，会返回执行计划的信息，并不执行这条SQL。
2.Explain可以用来分析SQL语句和表结构的性能瓶颈。通过explain的结果，可以了解到如数据表的查询顺序、数据查询操作的操作类型、哪些索引可以被命中、哪些索引实际会命中、每个数据表有多少行记录被查询等信息。
explain结果列说明
1.id列
1.id列是一个有顺序的编号，是查询的顺序号，有几个select就显示几行。
2.id的顺序是按 select 出现的顺序增长的。
3.id列的值越大执行优先级越高越先执行，id列的值相同则从上往下执行，id列的值为NULL最后执行。
id相同
执行顺序从上至下
例子：
explain select subject.* from subject,student_score,teacher where subject.id = student_id and subject.teacher_id = teacher.id;
读取顺序：subject > teacher > student_score
id不同
如果是子查询，id的序号会递增，id的值越大优先级越高，越先被执行
例子：
explain select score.* from student_score as score where subject_id = (select id from subject where teacher_id = (select id from teacher where id = 2));
读取顺序：teacher > subject > student_score
id相同又不同
id如果相同，可以认为是一组，从上往下顺序执行
在所有组中，id值越大，优先级越高，越先执行
例子：
explain select subject.* from subject left join teacher on subject.teacher_id = teacher.id
-> union
-> select subject.* from subject right join teacher on subject.teacher_id = teacher.id;
读取顺序：2.teacher > 2.subject > 1.subject > 1.teacher
2.select_type列
表示 select 查询的类型，主要是用于区分各种复杂的查询，例如：普通查询、联合查询、子查询等。
3.table列
查询的表名，并不一定是真实存在的表，有别名显示别名，也可能为临时表。
4.type列
查询使用了何种类型，它在 SQL优化中是一个非常重要的指标，以下性能从好到坏依次是：system > const > eq_ref > ref > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL
system
当表仅有一行记录时(系统表)，数据量很少，往往不需要进行磁盘IO，速度非常快。
const
表示查询时命中 primary key 主键或者 unique 唯一索引，或者被连接的部分是一个常量(const)值。这类扫描效率极高，返回数据量少，速度非常快。
mysql> EXPLAIN SELECT * from three where three_id=1;
±—±------------±------±-----------±------±--------------±--------±--------±------±-----±---------±------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
±—±------------±------±-----------±------±--------------±--------±--------±------±-----±---------±------+
| 1 | SIMPLE | three | NULL | const | PRIMARY | PRIMARY | 4 | const | 1 | 100 | NULL |
±—±------------±------±-----------±------±--------------±--------±--------±------±-----±---------±------+
eq_ref
查询时命中主键primary key 或者 unique key索引， type 就是 eq_ref。
mysql> EXPLAIN select o.one_name from one o ,two t where o.one_id = t.two_id ;
±—±------------±------±-----------±-------±--------------±---------±--------±-------------------±-----±---------±------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
±—±------------±------±-----------±-------±--------------±---------±--------±-------------------±-----±---------±------------+
| 1 | SIMPLE | o | NULL | index | PRIMARY | idx_name | 768 | NULL | 2 | 100 | Using index |
| 1 | SIMPLE | t | NULL | eq_ref | PRIMARY | PRIMARY | 4 | xin-slave.o.one_id | 1 | 100 | Using index |
±—±------------±------±-----------±-------±--------------±---------±--------±-------------------±-----±---------±------------+
ref
区别于eq_ref ，ref表示使用非唯一性索引，会找到很多个符合条件的行。
mysql> select o.one_id from one o where o.one_name = “xin” ;
±-------+
| one_id |
±-------+
| 1 |
| 3 |
mysql> EXPLAIN select o.one_id from one o where o.one_name = “xin” ;
±—±------------±------±-----------±-----±--------------±---------±--------±------±-----±---------±------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
±—±------------±------±-----------±-----±--------------±---------±--------±------±-----±---------±------------+
| 1 | SIMPLE | o | NULL | ref | idx_name | idx_name | 768 | const | 1 | 100 | Using index |
±—±------------±------±-----------±-----±--------------±---------±--------±------±-----±---------±------------+
range
针对一个有索引的字段，给定范围检索数据。在where语句中使用 bettween…and、<、>、<=、in 等条件查询 type 都是 range。
举例
three表中three_id为唯一主键，user_id普通字段未建索引。
mysql> EXPLAIN SELECT * from three where three_id BETWEEN 2 AND 3;
±—±------------±------±-----------±------±--------------±--------±--------±-----±-----±---------±------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
±—±------------±------±-----------±------±--------------±--------±--------±-----±-----±---------±------------+
| 1 | SIMPLE | three | NULL | range | PRIMARY | PRIMARY | 4 | NULL | 1 | 100 | Using where |
±—±------------±------±-----------±------±--------------±--------±--------±-----±-----±---------±------------+
从结果中看到只有对设置了索引的字段，做范围检索 type 才是 range。
mysql> EXPLAIN SELECT * from three where user_id BETWEEN 2 AND 3;
±—±------------±------±-----------±-----±--------------±-----±--------±-----±-----±---------±------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
±—±------------±------±-----------±-----±--------------±-----±--------±-----±-----±---------±------------+
| 1 | SIMPLE | three | NULL | ALL | NULL | NULL | NULL | NULL | 3 | 33.33 | Using where |
±—±------------±------±-----------±-----±--------------±-----±--------±-----±-----±---------±------------+
index
Index 与ALL 其实都是读全表，区别在于index是遍历索引树读取，而ALL是从硬盘中读取。
举例
three_id 为主键，不带 where 条件全表查询 ，type结果为index 。
mysql> EXPLAIN SELECT three_id from three ;
±—±------------±------±-----------±------±--------------±--------±--------±-----±-----±---------±------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
±—±------------±------±-----------±------±--------------±--------±--------±-----±-----±---------±------------+
| 1 | SIMPLE | three | NULL | index | NULL | PRIMARY | 4 | NULL | 1 | 100 | Using index |
±—±------------±------±-----------±------±--------------±--------±--------±-----±-----±---------±------------+
all
将遍历全表以找到匹配的行，性能最差。
mysql> EXPLAIN SELECT * from two ;
±—±------------±------±-----------±-----±--------------±-----±--------±-----±-----±---------±------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
±—±------------±------±-----------±-----±--------------±-----±--------±-----±-----±---------±------+
| 1 | SIMPLE | two | NULL | ALL | NULL | NULL | NULL | NULL | 2 | 100 | NULL |
±—±------------±------±-----------±-----±--------------±-----±--------±-----±-----±---------±------+
9.Extra列
顾名思义，这一列表明的是额外信息,这一列的取值对优化SQL非常有参考意义。
1.using index
我们在相应的 select 操作中使用了覆盖索引。
举例
mysql> EXPLAIN SELECT one_id from one ;
±—±------------±------±-----------±------±--------------±-----------±--------±-----±-----±---------±------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
±—±------------±------±-----------±------±--------------±-----------±--------±-----±-----±---------±------------+
| 1 | SIMPLE | one | NULL | index | NULL | idx_two_id | 5 | NULL | 3 | 100 | Using index |
±—±------------±------±-----------±------±--------------±-----------±--------±-----±-----±---------±------------+
one_id为表主键，不需要回表，使用到了覆盖索引，Extra列的值为using index
mysql> EXPLAIN SELECT * from one ;
±—±------------±------±-----------±-----±--------------±-----±--------±-----±-----±---------±------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
±—±------------±------±-----------±-----±--------------±-----±--------±-----±-----±---------±------+
| 1 | SIMPLE | one | NULL | ALL | NULL | NULL | NULL | NULL | 3 | 100 | NULL |
±—±------------±------±-----------±-----±--------------±-----±--------±-----±-----±---------±------+
select * 需要进行回表操作，Extra列的值为NULL
2.using where
查询时未找到可用的索引，进而通过where条件过滤获取所需数据
举例
mysql> EXPLAIN SELECT one_name from one where create_time =‘2020-05-18’;
±—±------------±------±-----------±-----±--------------±-----±--------±-----±-----±---------±------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
±—±------------±------±-----------±-----±--------------±-----±--------±-----±-----±---------±------------+
| 1 | SIMPLE | one | NULL | ALL | NULL | NULL | NULL | NULL | 3 | 33.33 | Using where |
±—±------------±------±-----------±-----±--------------±-----±--------±-----±-----±---------±------------+
create_time 并未用到索引，type 为 ALL，即MySQL通过全表扫描后再按where条件筛选数据。
3.using temporary
表示查询后结果需要使用临时表来存储，一般在排序或者分组查询时用到。
举例
mysql> EXPLAIN SELECT one_name from one where one_id in (1,2) group by one_name;
±—±------------±------±-----------±-----±--------------±-----±--------±-----±-----±---------±------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
±—±------------±------±-----------±-----±--------------±-----±--------±-----±-----±---------±------------+
| 1 | SIMPLE | one | NULL | range| NULL | NULL | NULL | NULL | 3 | 33.33 | Using where; Using temporary; Using filesort |
±—±------------±------±-----------±-----±--------------±-----±--------±-----±-----±---------±------------+
4.usingfilesort
表示无法利用索引完成的排序操作，也就是ORDER BY的字段没有索引，通常这样的SQL都是需要优化的。
举例
mysql> EXPLAIN SELECT one_id from one ORDER BY create_time;
±—±------------±------±-----------±-----±--------------±-----±--------±-----±-----±---------±---------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
±—±------------±------±-----------±-----±--------------±-----±--------±-----±-----±---------±---------------+
| 1 | SIMPLE | one | NULL | ALL | NULL | NULL | NULL | NULL | 3 | 100 | Using filesort |
±—±------------±------±-----------±-----±--------------±-----±--------±-----±-----±---------±---------------+
如果ORDER BY字段有索引就会用到覆盖索引，相比执行速度快很多。
mysql> EXPLAIN SELECT one_id from one ORDER BY one_id;
±—±------------±------±-----------±------±--------------±--------±--------±-----±-----±---------±------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
±—±------------±------±-----------±------±--------------±--------±--------±-----±-----±---------±------------+
| 1 | SIMPLE | one | NULL | index | NULL | PRIMARY | 4 | NULL | 3 | 100 | Using index |
±—±------------±------±-----------±------±--------------±--------±--------±-----±-----±---------±------------+
5.Using join buffer
在我们联表查询的时候，如果表的连接条件没有用到索引，需要有一个连接缓冲区来存储中间结果。
举例
先看一下有索引的情况：连接条件 one_name 、two_name 都用到索引。
mysql> EXPLAIN SELECT one_name from one o,two t where o.one_name = t.two_name;
±—±------------±------±-----------±------±--------------±---------±--------±---------------------±-----±---------±-------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
±—±------------±------±-----------±------±--------------±---------±--------±---------------------±-----±---------±-------------------------+
| 1 | SIMPLE | o | NULL | index | idx_name | idx_name | 768 | NULL | 3 | 100 | Using where; Using index |
| 1 | SIMPLE | t | NULL | ref | idx_name | idx_name | 768 | xin-slave.o.one_name | 1 | 100 | Using index |
±—±------------±------±-----------±------±--------------±---------±--------±---------------------±-----±---------±-------------------------+
接下来删掉 连接条件 one_name 、two_name 的字段索引。发现Extra 列变成 Using join buffer，type均为全表扫描，这也是SQL优化中需要注意的地方。
mysql> EXPLAIN SELECT one_name from one o,two t where o.one_name = t.two_name;
±—±------------±------±-----------±-----±--------------±-----±--------±-----±-----±---------±---------------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
±—±------------±------±-----------±-----±--------------±-----±--------±-----±-----±---------±---------------------------------------------------+
| 1 | SIMPLE | t | NULL | ALL | NULL | NULL | NULL | NULL | 2 | 100 | NULL |
| 1 | SIMPLE | o | NULL | ALL | NULL | NULL | NULL | NULL | 3 | 33.33 | Using where; Using join buffer (Block Nested Loop) |
±—±------------±------±-----------±-----±--------------±-----±--------±-----±-----±---------±---------------------------------------------------+
6.Impossible where
表示在我们用不太正确的where语句，导致没有符合条件的行。
举例
mysql> EXPLAIN SELECT one_name from one WHERE 1=2;
±—±------------±------±-----------±-----±--------------±-----±--------±-----±-----±---------±-----------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
±—±------------±------±-----------±-----±--------------±-----±--------±-----±-----±---------±-----------------+
| 1 | SIMPLE | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Impossible WHERE |
±—±------------±------±-----------±-----±--------------±-----±--------±-----±-----±---------±-----------------+
7.No tables used
我们的查询语句中没有FROM子句，或者有 FROM DUAL子句。
举例
mysql> EXPLAIN select now();
±—±------------±------±-----------±-----±--------------±-----±--------±-----±-----±---------±---------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
±—±------------±------±-----------±-----±--------------±-----±--------±-----±-----±---------±---------------+
| 1 | SIMPLE | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | No tables used |
±—±------------±------±-----------±-----±--------------±-----±--------±-----±-----±---------±---------------+
1.mysql主从复制
概念
优点
原理
主从复制涉及到的文件和线程
原理
2.mysql读写分离
概念
适用场景
读写分离的两个问题
问题一 主从复制延迟
问题二 分配机制
如何将读写操作区分开来，然后访问不同的数据库服务器？
解决方案1 客户端程序代码封装实现
解决方案2 使用数据库中间件
就是有一个独立的系统，专门来实现读写分离和数据库连接管理，业务服务器和数据库中间件之间是通过标准的SQL协议交流的，所以在业务服务器看来数据库中间件其实就是个数据库。
526
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
